☰

Serien im Streudiagramm automatisch auswählen

tag-icon • tag-icon microsoft-excel charts
Serien im Streudiagramm automatisch auswählen

Verwenden von Excel 365

Hier ist ein Beispiel, an dem ich gearbeitet habe und das meinem Ziel ziemlich nahe kommt:https://docs.google.com/spreadsheets/d/1zZiFDaUhuZAhyHm3HZkcZrUl9KEoV97N/edit?usp=sharing&ouid=108210493177158563166&rtpof=true&sd=true

Im Grunde habe ich die Daten A, B und C und für jedes eindeutige A mit mehr als einem Eintrag in der Tabelle möchte ich B gegenüber C in einem Streudiagramm mit einer Linie darstellen. Ich entwerfe eine Vorlage und sie muss mit allen Permutationen von Daten funktionieren.

Was ich habe, kommt dem ziemlich nahe, mit ein paar Problemen, die ich nicht beheben konnte:

  1. Ich muss genügend Platz für alle möglichen Werte von A und B in den Tabellen reservieren. Das ist kein großes Problem, da die Datensätze, mit denen ich im Moment arbeite, ziemlich klein sind, aber es wäre schön, wenn es vollständig dynamisch wäre und eine beliebige Anzahl von Werten verarbeiten könnte.

  2. Die Legende zeigt leere Einträge für alle nicht verwendeten Spalten der Tabelle. Ich möchte nur die Einträge anzeigen, für die ich Daten habe.

  3. Die Linie ist für nicht benachbarte Werte von B nicht verbunden (siehe Zeile 3 als Beispiel). Ich möchte, dass sie innerhalb jeder Reihe verbunden ist.

Ich hoffe, das ist verständlich. Sie können gern weitere Fragen stellen.

Dank im Voraus.

Antwort1

Folgendes müssen Sie tun, um jedes kleine bisschen dynamisch zu gestalten (siehe Animation):

Bildbeschreibung hier eingeben

  • Erstens, anstatt leere Bereiche zu verwenden, um zu iterieren, verwenden Sie Structured Referencesaka Tables--> Tabelle mit dem NamenTable1
  • Für Spalten verwendete Formeln F3:G16, J2:O8die nach und nach erweitert werden.

• In der Zelle verwendete FormelF3

=LET(
     _Data, Table1,
     _A, TAKE(_Data,,1),
     _Uniq, UNIQUE(_A),
     HSTACK(_Uniq, COUNTIF(_A,_Uniq)))

• In der Zelle verwendete FormelJ2

=LET(
     _Data, Table1,
     _A, TAKE(_Data,,1),
     _B, INDEX(_Data,,2),
     _C, TAKE(_Data,,-1),
     _UniqA, TOROW(UNIQUE(_A)),
     _UniqB, UNIQUE(_B),
     _ArrayB, VSTACK("",_UniqB),
     _DataBody, VSTACK(_UniqA&"A", MAKEARRAY(ROWS(_UniqB), COLUMNS(_UniqA), LAMBDA(r,c, 
                FILTER(_C, (INDEX(_UniqA,1,c)=_A)*(INDEX(_UniqB,r)=_B),NA())))),
     HSTACK(_ArrayB, _DataBody))

Die obige Formel kann auch mit LAMBDA()--> definiert werden. Sie müssen die obige Formel also in LAMBDA()--> einschließen.parameter_or_calculationas arrayund ersetzen Sie das Table1as array. So wird es und einzutragen inrefers tobei der Definition des Namens als

=LAMBDA(array, LET(_Data, array, the rest continues as is)

Bildbeschreibung hier eingeben

Geben Sie im ExcelBlatt Folgendes ein:

=SCATTER_PLOT(Table1)

Bildbeschreibung hier eingeben

  • MAKEARRAY()Beachten Sie, dass ich anstelle von eine LAMBDA()Hilfsfunktion verwendet habe REDUCE(). Sie können auch verwenden REDUCE(), aber dies ist eine ressourcenintensive Funktion und nicht so effizient wie MAKEARRAY()das Stapeln von Daten.
  • Achten Sie beim Einfügen darauf, den Datenbereich „Von “ und „Bis“ Scatter with smooth lines and markersauszuwählen, indem Sie das Kontrollkästchen „Leere Zellen“ aktivieren.J2:O8Connect data points with lineHidden and
  • Ich habe auch Conditional Formattingfür die Grenzen verwendet. hier ist dieExcel.

Antwort2

Mögliche Modifikationen:

  1. So schaffen Sie Platz für mehr Daten:

    F3: =UNIQUE(FILTER(B:B,ISNUMBER(B:B)))
    G3: =COUNTIF(B:B,F3#)
    K3: =TRANSPOSE(F3#)
    K4: : =K3# & " A"
    J5: in den erforderlichen Bereich kopieren. Es ist besser, hier wirklich benötigte Bereiche zu verwenden, zum =UNIQUE(FILTER(C:C,ISNUMBER(C:C)))
    K5Beispiel =FILTER($D:$D,($B:$B=K$3)*($C:$C=$J5),#N/A)

    =FILTER($D$1:$D$100,($B$1:$B$100=K$3)*($C$1:$C$100=$J5),#N/A)

  2. Es hängt davon ab Chart data range. Ich kenne keine Methode, es dynamisch zu machen (ohne VBA). Wir können eine Formel definieren, die diesen Datenbereich dynamisch definiert. Wenn wir sie jedoch hier verwenden, wird sie sofort berechnet und als statische Adresse gespeichert.

  3. Um eine Verbindung zwischen Punkten herzustellen, sollten Sie eine Diagrammoption ändern:
    Select Data Source > Hidden and Empty Cells > Connect data points with line

verwandte Informationen