Excel 中的分组散点图、分类 X 轴

Question

似乎诀窍在于将标签转换为数字（他们可能没有听说过 ms 上的名义和序数数据，嗯）。这很容易通过排序和累计计数器实现。问题是，X 轴是数字，并且手动调整间距（如上面建议的那样）可能在大规模上不可行。

另一种方法是将数据合并到每个类别的一行 - 这非常简单，可以通过公式或 vba 实现 - 然后将散点图应用到表格中。这将为每一列创建一个系列，每列都有彩虹色的形状。我想，同时纠正这些问题应该可以在 vba 中使用正确的数组属性来实现（还没有检查文档）。此外，在生成图时有空白似乎会造成混淆，所以我最初用占位符值替换了这些空白（例如 -412613），然后在生成图后，用空白替换所有占位符，方便地将它们从图表中删除而不会产生进一步的后果。所以这是一笔更大的一次性编码支出，然后需要 2 分钟才能应用。

或者如果您可以使用更好的绘图软件，那么......

编辑（2019 年 6 月 30 日）：根据要求，技术细节将在下文详细说明。我的随机数据最初在 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。我个人更喜欢这种方法，因为它稍微更“跨平台”，可以自由使用。

替代方案-使用 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

接下来是一些用于演示的图像。

选择阶段：

使用此数据生成的图表（注意占位符的底线）：

用空格替换占位符：

这是最终的结果，骨头中的每一分活力都被吸走了。

编辑（20/04/24）：据我所知，excel 2013+ 版本似乎将折线图设置更改为值类型轴，表面上没有任何改变它的方法。但是，列/条形图确实（仍然）使用类别类型轴；一种可能的低技术黑客是创建一个散点图/线图（如所述），然后创建一个仅包含标签 + 0 值的第二个条形图 - 这会创建一个空网格，可以清除其中的图例、刻度等，只留下水平轴。然后，通过调整散点的数值范围（大约从 0/1 到标签数量+）和列的宽度和位置，可以对齐两个图表，尽管不太美观。可以通过 Worksheets("Merge").ChartObjects(1).Chart.Axes(xlCategory,xlPrimary 获取对 (excel.) 轴的引用，其中值/类别差异非常明显；尽管如此，它不会响应我尝试的任何属性更改请求。

Answer 1

这是一个解决方案：Excel - 在相同的 x 值上绘制不同的 y 值