☰

Групповая диаграмма рассеяния, категориальная ось X в Excel

tag-icon • tag-icon microsoft-excel charts
Групповая диаграмма рассеяния, категориальная ось X в Excel

Я пытаюсь создать сгруппированную диаграмму рассеяния с категориальной осью 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), где различия между значением и категорией довольно очевидны; тем не менее, это не отреагировало ни на один из запросов на изменение свойств, которые я пробовал.

Связанный контент