Gruppiertes Streudiagramm, kategorische X-Achse in Excel

Question

Hier ist eine Lösung:Excel - Aufzeichnen unterschiedlicher y-Werte über denselben x-Werten

Der Trick scheint darin zu liegen, die Beschriftungen in Zahlen umzuwandeln (wahrscheinlich haben sie noch nie von nominalen und ordinalen Daten zu ms gehört, eh). Das lässt sich ziemlich einfach durch Sortieren und Summenzähler erreichen. Das Problem ist, dass die X-Achse dann numerisch ist und das manuelle Herumspielen mit Abständen, wie oben vorgeschlagen, im großen Maßstab möglicherweise nicht praktikabel ist.

Ein anderer Ansatz besteht darin, die Daten in einer einzigen Zeile pro Kategorie zusammenzuführen – das ist ziemlich unkompliziert, entweder über eine Formel oder VBA – und dann das Streudiagramm auf die Tabelle anzuwenden. Dadurch wird für jede Spalte eine Reihe erstellt, jede mit einer regenbogenfarbenen Form. Ich denke, dass es mit der richtigen Array-Eigenschaft in VBA möglich sein sollte, diese gleichzeitig zu korrigieren (habe die Dokumentation nicht geprüft). Darüber hinaus scheinen Leerzeichen beim Generieren des Diagramms es zu verwirren, also habe ich diese zunächst durch Platzhalterwerte ersetzt (etwa -412613) und dann, nachdem das Diagramm generiert war, alle Platzhalter durch Leerzeichen ersetzt und sie bequem ohne weitere Konsequenzen aus dem Diagramm entfernt. Das ist also ein größerer einmaliger Aufwand für das Codieren und dann 2 Minuten für die Anwendung.

Oder wenn Sie Zugriff auf bessere Plotsoftware haben, nun ja ...

Edit (30.06.19): Auf Anfrage werden die technischen Details weiter unten erläutert. Meine randomisierten Daten liegen ursprünglich in A+C.

Gesamtsumme:

B1=1 ; B2=if(a2=a1,b1,b1+1)

Zusammenführen mithilfe der Formel:

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)

Fügen Sie E entlang aller Zeilen und für so viele Spalten ein, wie die größte Anzahl an Beobachtungen enthält. Sie werden es merken, wenn ein Filter der Spalte nur Platzhalter anzeigt. Filtern Sie nach D=1, fügen Sie die Werte in ein anderes Blatt ein und löschen Sie D. Ich persönlich bevorzuge diese Methode, da sie etwas „plattformübergreifender“ ist und frei funktioniert.

Alternative - Zusammenführen mit 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

Vergessen Sie nicht, ein Diagramm zu erstellen, in dem Sie alle Werte auswählen, und ersetzen Sie dann alle Platzhalter durch Leerzeichen.

ALLE Markierungen ändern: Die Achsenbeschriftungen eines Streudiagramms lassen sich anscheinend nicht so einfach ändern (möglicherweise gibt es dafür gar keine Berechtigung); im Gegensatz dazu ist es ganz einfach, die Linien eines Liniendiagramms zu entfernen. (Beachten Sie, dass dadurch alle Diagramme auf dem Blatt geändert werden. Stellen Sie also sicher, dass es allein steht, oder ändern Sie die Schleife in einen Parameter.)

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

Als nächstes ein paar Bilder zur Demonstration.

Auswahlphase:

Mit diesen Daten generiertes Diagramm (achten Sie auf die untere Zeile der Platzhalter):

Platzhalter durch Leerzeichen ersetzen:

Hier ist das Endergebnis, dem jedes Quäntchen Lebendigkeit aus den Knochen gesaugt wurde.

Bearbeiten (24.04.20): Wie mir zu Ohren gekommen ist, scheinen Excel-Versionen ab 2013 die Liniendiagrammeinstellungen auf Werteachsen geändert zu haben, angeblich ohne dass es eine Möglichkeit gäbe, dies zu ändern. Spalten-/Balkendiagramme verwenden jedoch (immer noch) eine Kategorieachse. Ein möglicher Low-Tech-Hack besteht darin, wie beschrieben ein Streu-/Liniendiagramm und dann ein zweites Balkendiagramm mit den Beschriftungen + 0 Werten zu erstellen. Dadurch wird ein leeres Raster erstellt, das von Legenden, Markierungen usw. bereinigt werden kann, sodass nur die horizontale Achse übrig bleibt. Anschließend ist eine Ausrichtung der beiden Diagramme möglich, wenn auch unansehnlich, indem der numerische Bereich der Streuung (ungefähr von 0/1 bis zur Anzahl der Beschriftungen +) sowie die Breite und Position der Spalte angepasst werden. Es ist möglich, über Worksheets("Merge").ChartObjects(1).Chart.Axes(xlCategory,xlPrimary) einen Verweis auf die (Excel-)Achse zu erhalten, wobei die Unterschiede zwischen Wert und Kategorie ziemlich deutlich sind. Trotzdem reagierte es nicht auf die von mir versuchten Anfragen zur Eigenschaftsänderung.

Answer 1

Hier ist eine Lösung:Excel - Aufzeichnen unterschiedlicher y-Werte über denselben x-Werten

Der Trick scheint darin zu liegen, die Beschriftungen in Zahlen umzuwandeln (wahrscheinlich haben sie noch nie von nominalen und ordinalen Daten zu ms gehört, eh). Das lässt sich ziemlich einfach durch Sortieren und Summenzähler erreichen. Das Problem ist, dass die X-Achse dann numerisch ist und das manuelle Herumspielen mit Abständen, wie oben vorgeschlagen, im großen Maßstab möglicherweise nicht praktikabel ist.

Ein anderer Ansatz besteht darin, die Daten in einer einzigen Zeile pro Kategorie zusammenzuführen – das ist ziemlich unkompliziert, entweder über eine Formel oder VBA – und dann das Streudiagramm auf die Tabelle anzuwenden. Dadurch wird für jede Spalte eine Reihe erstellt, jede mit einer regenbogenfarbenen Form. Ich denke, dass es mit der richtigen Array-Eigenschaft in VBA möglich sein sollte, diese gleichzeitig zu korrigieren (habe die Dokumentation nicht geprüft). Darüber hinaus scheinen Leerzeichen beim Generieren des Diagramms es zu verwirren, also habe ich diese zunächst durch Platzhalterwerte ersetzt (etwa -412613) und dann, nachdem das Diagramm generiert war, alle Platzhalter durch Leerzeichen ersetzt und sie bequem ohne weitere Konsequenzen aus dem Diagramm entfernt. Das ist also ein größerer einmaliger Aufwand für das Codieren und dann 2 Minuten für die Anwendung.

Oder wenn Sie Zugriff auf bessere Plotsoftware haben, nun ja ...

Edit (30.06.19): Auf Anfrage werden die technischen Details weiter unten erläutert. Meine randomisierten Daten liegen ursprünglich in A+C.

Gesamtsumme:

B1=1 ; B2=if(a2=a1,b1,b1+1)

Zusammenführen mithilfe der Formel:

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)

Fügen Sie E entlang aller Zeilen und für so viele Spalten ein, wie die größte Anzahl an Beobachtungen enthält. Sie werden es merken, wenn ein Filter der Spalte nur Platzhalter anzeigt. Filtern Sie nach D=1, fügen Sie die Werte in ein anderes Blatt ein und löschen Sie D. Ich persönlich bevorzuge diese Methode, da sie etwas „plattformübergreifender“ ist und frei funktioniert.

Alternative - Zusammenführen mit 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

Vergessen Sie nicht, ein Diagramm zu erstellen, in dem Sie alle Werte auswählen, und ersetzen Sie dann alle Platzhalter durch Leerzeichen.

ALLE Markierungen ändern: Die Achsenbeschriftungen eines Streudiagramms lassen sich anscheinend nicht so einfach ändern (möglicherweise gibt es dafür gar keine Berechtigung); im Gegensatz dazu ist es ganz einfach, die Linien eines Liniendiagramms zu entfernen. (Beachten Sie, dass dadurch alle Diagramme auf dem Blatt geändert werden. Stellen Sie also sicher, dass es allein steht, oder ändern Sie die Schleife in einen Parameter.)

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

Als nächstes ein paar Bilder zur Demonstration.

Auswahlphase:

Mit diesen Daten generiertes Diagramm (achten Sie auf die untere Zeile der Platzhalter):

Platzhalter durch Leerzeichen ersetzen:

Hier ist das Endergebnis, dem jedes Quäntchen Lebendigkeit aus den Knochen gesaugt wurde.

Bearbeiten (24.04.20): Wie mir zu Ohren gekommen ist, scheinen Excel-Versionen ab 2013 die Liniendiagrammeinstellungen auf Werteachsen geändert zu haben, angeblich ohne dass es eine Möglichkeit gäbe, dies zu ändern. Spalten-/Balkendiagramme verwenden jedoch (immer noch) eine Kategorieachse. Ein möglicher Low-Tech-Hack besteht darin, wie beschrieben ein Streu-/Liniendiagramm und dann ein zweites Balkendiagramm mit den Beschriftungen + 0 Werten zu erstellen. Dadurch wird ein leeres Raster erstellt, das von Legenden, Markierungen usw. bereinigt werden kann, sodass nur die horizontale Achse übrig bleibt. Anschließend ist eine Ausrichtung der beiden Diagramme möglich, wenn auch unansehnlich, indem der numerische Bereich der Streuung (ungefähr von 0/1 bis zur Anzahl der Beschriftungen +) sowie die Breite und Position der Spalte angepasst werden. Es ist möglich, über Worksheets("Merge").ChartObjects(1).Chart.Axes(xlCategory,xlPrimary) einen Verweis auf die (Excel-)Achse zu erhalten, wobei die Unterschiede zwischen Wert und Kategorie ziemlich deutlich sind. Trotzdem reagierte es nicht auf die von mir versuchten Anfragen zur Eigenschaftsänderung.

Gruppiertes Streudiagramm, kategorische X-Achse in Excel

Antwort1

verwandte Informationen