Я пытаюсь создать сгруппированную диаграмму рассеяния с категориальной осью X. Мои данные и моя текущая попытка построения графика:
Я пытаюсь сгруппировать все буквы как точки с одинаковым значением x, при этом ни одна буква не повторяется... например, все буквы D вертикально распределены:
Как мне этого добиться?
решение1
Вот одно из решений:Excel — Построение графиков различных значений Y поверх одинаковых значений X
Кажется, что трюк заключается в преобразовании меток в числа (они, вероятно, не слышали о номинальных и порядковых данных в мс, ага). Этого довольно легко добиться с помощью сортировки и счетчика cumsum. Проблема в том, что ось X тогда числовая, и ручная игра с интервалами, как предлагается там, может оказаться нежизнеспособной в больших масштабах.
Другой подход — объединить данные в одну строку для каждой категории — это довольно просто, либо через формулу, либо через vba — и затем применить диаграмму рассеяния к таблице. Это создаст ряд для каждого столбца, каждый с радужной формой. Я полагаю, что исправление их одновременно должно быть выполнимо в vba с правильным свойством массива (не изучал документацию). Более того, наличие пробелов при создании диаграммы, кажется, сбивает ее с толку, поэтому я изначально заменил их значениями-заполнителями (что-то вроде -412613), а затем, как только диаграмма была создана, заменил все заполнители пробелами, удобно удалив их из диаграммы без дальнейших последствий. Так что это более крупные единовременные затраты на код, а затем 2 минуты на применение.
Или, если у вас есть доступ к более качественному программному обеспечению для построения графиков, ну...
Редактировать (30/06/19): По запросу технические детали будут предоставлены ниже. Мои рандомизированные данные изначально находятся в A+C.
Сумма:
B1=1 ; B2=if(a2=a1,b1,b1+1)
Объединить по формуле:
D1=1 ; D2 = if(a2=a1,0,1)
E1=if(index($a2:$a$999,column(a1),1)=$a1, _
value(index($c2:$c$999,column(a1),1)),-0.413612)
Вставьте E вдоль всех строк и для стольких столбцов, сколько есть наибольших наблюдений — вы узнаете, когда фильтр столбца покажет только заполнитель. Фильтруйте по D=1, вставьте значения на другой лист и удалите D. Лично я предпочитаю этот метод, так как он немного более «кроссплатформенный», работает в libre.
Альтернатива — объединение с помощью vba:
Const BASESHEET As String = "Base"
Const MERGESHEET As String = "Merge2"
Sub MergePairs()
Dim rowcnt As Long
Dim srcsht
Dim trgsht
Dim pid As String
Dim stidx As Long
Dim trgidx As Long
Set srcsht = Sheets(BASESHEET)
Set trgsht = Sheets(MERGESHEET)
' Sort the data.
srcsht.Sort.SortFields.Clear
srcsht.Sort.SortFields.Add Key:=Range("$A:$A"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With srcsht.Sort
.SetRange Range("$A:$C")
.Header = xlGuess
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
' Copy unique ids.
rowcnt = srcsht.Cells(srcsht.Rows.Count, 1).End(xlUp).Row
srcsht.Range(srcsht.Cells(1, 1), srcsht.Cells(rowcnt, 1)).Copy
trgsht.Range("A1").PasteSpecial
trgsht.Range("$A:$A").RemoveDuplicates Columns:=1, Header:=xlNo
' Copy each set of points.
pid = ""
For i = 1 To rowcnt + 1
If pid = "" Then
pid = srcsht.Cells(i, 1).Value
stidx = i
trgidx = 1
ElseIf pid <> srcsht.Cells(i, 1).Value Then
srcsht.Range(srcsht.Cells(stidx, 3), srcsht.Cells(i - 1, 3)).Copy
trgsht.Cells(trgidx, 2).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True
pid = srcsht.Cells(i, 1).Value
stidx = i
trgidx = trgidx + 1
End If
Next i
End Sub
Не забудьте создать диаграмму, выбрав все значения, а затем заменить все заполнители пробелами.
Измените ВСЕ маркеры: похоже, что метки осей на точечной диаграмме не так-то просто изменить (возможно, на это вообще нет разрешения); напротив, достаточно просто избавиться от линий на линейной диаграмме. (Обратите внимание, что это изменяет все диаграммы на листе, поэтому убедитесь, что это одиночный параметр, или измените цикл на параметр.)
Sub XChart()
Dim chrt As ChartObject
Dim ser As Excel.Series
For Each chrt In Worksheets("Merge1").ChartObjects
chrt.Chart.ChartType = xlLine
For Each ser In chrt.Chart.SeriesCollection
ser.Format.Line.Visible = msoFalse
ser.MarkerStyle = xlMarkerStyleX
ser.MarkerForegroundColor = RGB(0, 0, 0)
ser.MarkerBackgroundColor = RGB(255, 255, 255)
ser.MarkerSize = 7
Next ser
Next chrt
End Sub
Далее несколько изображений для демонстрации.
Фаза отбора:
Диаграмма, созданная на основе этих данных (обратите внимание на нижнюю строку заполнителей):
Замена заполнителей пробелами:
Вот конечный результат, из которого буквально высосали всю живость.
Редактировать (24/04/20): Как мне сообщили, похоже, версии Excel 2013+ изменили настройки линейной диаграммы на ось типа значений, якобы без каких-либо средств ее изменения. Однако символы столбцов / полос (все еще) используют ось типа категории; один возможный низкотехнологичный прием - создание диаграммы рассеяния / линии, как описано, а затем второй гистограммы только с метками + 0 значений - это создает пустую сетку, которую можно очистить от легенд, отметок и т. д., чтобы оставить только горизонтальную ось. Затем выравнивание двух диаграмм выполнимо, хотя и некрасиво, путем регулировки числового диапазона рассеивания (примерно от 0 / 1 до количества меток +) и ширины и положения столбца. Можно получить ссылку на ось (excel.) через Worksheets("Merge").ChartObjects(1).Chart.Axes(xlCategory,xlPrimary), где различия между значением и категорией довольно очевидны; тем не менее, это не отреагировало ни на один из запросов на изменение свойств, которые я пробовал.