☰

Hierarchisches oder rekursives Zählen in Excel (vorzugsweise innerhalb einer Pivot-Tabelle)?

tag-icon • tag-icon microsoft-excel worksheet-function pivot-table
Hierarchisches oder rekursives Zählen in Excel (vorzugsweise innerhalb einer Pivot-Tabelle)?

Ich versuche, eine Möglichkeit zu finden, Daten in einem hierarchischen Datensatz zu aggregieren, vorzugsweise in einer Pivot-Tabelle, aber andere Methoden könnten auch in Ordnung sein. Stellen Sie sich einen Datensatz vor (für das Beispiel stark vereinfacht), der wie der folgende aussieht. Aus diesen Daten versuche ich, einen Satz von Funktionen zu erstellen, die Fragen wie diese beantworten:

„Wie viel Gesamtbestand habe ich an Obst?“

„Wie viele verschiedene Arten von Lebensmitteln verkaufe ich?“

Item     Category
=======  ========
Apples   Fruit
Bacon    Meat
Chicken  Meat
Corn     Veg
Food     
Fruit    Food
Grapes   Fruit
Meat     Food
Squash   Veg
Steak    Meat
Veg      Food

JedeArtikelhat (neben vielen anderen Informationen) eineKategorie, die wir uns wirklich als „Eltern“ vorstellen können. Beachten Sie aber auch, dass innerhalb des Datensatzes alle „Eltern“ auch ihre eigenen Elternkategorien haben. In diesem Datensatz wäre ein Beispiel für einen „Zweig“ der HierarchieLebensmittel->Fleisch->Huhn.

Die Frage „Wie viele verschiedene Obstsorten verkaufe ich?“ zu beantworten, ist nicht schwer, da es sich um eine Kategorie der ersten Ebene handelt. Ich kann einfach die Funktion ZÄHLENWENN verwenden und sagen: „Wie viele Artikel gehören zur Kategorie „Obst“?“ – und ich erhalte eine Tabelle, die folgendermaßen aussieht:

Item    Category    COUNTIF(categories,me)
Apples  Fruit       0
Bacon   Meat        0
Chicken Meat        0
Corn    Veg         0
Food    Food        3
Fruit   Food        2
Grapes  Fruit       0
Meat    Food        3
Squash  Veg         0
Steak   Meat        0
Veg     Food        2

Ganz einfach – in der ersten Zeile sehen Sie einfach, wie oft „Äpfel“ als Kategorie einer anderen Person vorkommt. (Da die Zahl Null ist, weiß ich, dass Äpfel kein übergeordnetes Element sind … das sollte helfen, aber ich bin nicht sicher, wie …) Jetzt erscheint Zeile fünf, „Obst“, zwei Mal als Kategorie einer anderen Person – da die Zahl NICHT Null ist, weiß ich, dass es eine Kategorie und nicht nur ein Element ist. Alles schön und gut für die Mathematik der ersten Ebene, aber …

Dies führt mich zu dem Teil, den ich nicht lösen konnte ...Wie finde ich heraus, wie viele Arten von „Lebensmitteln“ ich INSGESAMT habe? Und da meine tatsächlichen Daten viel mehr Hierarchieebenen haben, muss ich den Baum auf und ab gehen, um herauszufinden, wie viele Kinder insgesamt in jeder Ebene sind. Die Funktion ZÄHLENWENN der ersten Ebene sagt mir, dass es drei Unterkategorien von Lebensmitteln gibt (Obst, Gemüse und Fleisch) – aber was ich wirklich möchte, ist, dass sie irgendwie rekursiv bestimmt, dass Obst, Gemüse und Fleischkönnte auch seinKategorien, und addieren Sie die entsprechenden Zahlen für diese Kinder. In Excel ausgedrückt möchte ich wirklich eine weitere Spalte erstellen können, die rekursiv/iterativ die GESAMTANZAHL der Elemente in diesem gesamten Unterbaum zählt ... in diesem Fall gibt es sieben einzigartige Elemente, die zu Lebensmitteln gehören: 3 Fleisch, 2 Gemüse und 2 Obst.

Einige erschwerende Faktoren:

  • Da ist keinexplizitKennung in den Daten, die uns mitteilt, ob das bestimmte Element auch eine Kategorie ist oder ob es ein Element der untersten Ebene ist.

  • Jedes Element kennt nur seine Kategorie/sein übergeordnetes Element – ​​es gibt keine expliziten Daten, die angeben, ob es untergeordnete Elemente hat oder nicht. Anders gesagt: Alle Elemente gehören zu einer Kategorie, aber nur einige Elemente sind auch Kategorien.

  • In den tatsächlichen Daten kann die übergeordnete Beziehung bis zu 10 Ebenen tief sein, ABER es gibt keine Garantie dafür, dass die Tiefe jedes Zweigs in der Hierarchie konsistent ist: Einige Elemente können 3 Ebenen tief sein, während das nächste 8 Ebenen tief ist.

  • Das Stamm- oder ultimative übergeordnete Element verfügt nicht über eine Kategorie, aber dies ist ein Einzelfall, den ich problemlos manuell handhaben kann.

  • Ich bin mir völlig bewusst, dass dies in jeder „echten“ Programmiersprache (Perl, Python usw.) eine triviale Übung wäre, aber letztendlich muss ich dies an jemanden weitergeben, der keine Programmiererfahrung hat, daher gebe ich mir große Mühe, es in eine „Standard“-Excel-Arbeitsmappe einzufügen.

Antwort1

Zunächst einmal stimme ich @Raystafarian voll und ganz zu, Excel ist dafür nicht das richtige Tool.

Wenn Sie es jedoch unbedingt hier tun möchten, finden Sie hier eine Lösung mit einigen Hilfsspalten:

  • Ebene: Ebene des tatsächlichen Elements in der Hierarchie (Wurzelelemente haben Ebene 1, die Ebene der untergeordneten Elemente wird erhöht)
    =IFERROR(INDEX([level],MATCH([@Category],[Item],0))+1,1)
  • Levelcode: laufender Code für jedes Element, eindeutig INNERHALB DER EBENEN
    =CHAR(CODE("a")+COUNTIF($C$2:C2,[@level])-1)
  • Langer Code: zusammengesetzter Code von übergeordnetem Element und Element
    =IF([@level]>1,INDEX([long code],MATCH([@Category],[Item],0)),"")&[@[level code]]
  • hat Kind: Boolescher Wert, der angibt, ob das Element ein Kind hat
    =COUNTIF([Category],[@Item])>0

Bei diesem Modell enthält eine Kategorie alle Elemente und Unterkategorien, deren Code mit der gleichen Sequenz beginnt wie der Code der Eltern (wenn beispielsweise fruitder Code von ist aa, dann haben alle (Enkel-)Kinder davon einen Code, der mit beginnt aa).

Bildbeschreibung hier eingeben

Antworten auf Ihre Fragen:

„Wie viel Gesamtbestand habe ich an Obst?“

=COUNTIFS(Table1[long code],VLOOKUP(I3,Table1,5,FALSE)&"*",Table1[has child],FALSE)
nur gemäß dem Modell, alle Artikel mit derselben Startreihenfolge. Ich zähle hier nicht nur Artikel, nicht Kategorien (Sie verkaufen zwei Obstsorten applesund grapeshaben kein Produkt, das fruitSie verkaufen möchten). Wenn Sie auch Kategorien zählen möchten, schließen Sie einfach den zweiten Teil der Formel aus.

„Wie viele verschiedene Arten von Lebensmitteln verkaufe ich?“

=SUMIF(Table1[long code],VLOOKUP(I10,Table1,5,FALSE)&"*",Table1[inventory])
Ganz ähnlich mitSUMIF

Warnung

Diese Lösung hat zwei Einschränkungen:

  • Anzahl der Zeichen: Derzeit beginnt es bei a, das den Code 97 hat, und das letzte unterstützte Zeichen in CHARder Funktion ist 255. Wenn Sie also auf einer Ebene mehr als 158 verschiedene Kategorien haben, tritt ein Fehler auf (Sie können es etwas erweitern, indem Sie für das erste Zeichen einen kleineren Code verwenden).
  • mit zunehmender Größe Ihrer Datenbank nimmt die Leistung wahrscheinlich ab (komplexe Berechnungen). Möglicherweise möchten Sie die Berechnungsmethode auf „Automatisch, außer für Datentabellen“ einstellen und die Berechnung nur dann manuell durchführen, wenn Sie sie benötigen.

Antwort2

Ich denke, Máté hat eine gute Antwort. Ich würde es mit Listen machen:

Bildbeschreibung hier eingeben

(Alle Array-Formeln, also Ctrl+ Shft+ Enter)

Also, die Formeln (nach unten ziehen)

D2 =INDEX($A$2:$A$12,MATCH(0,IF(ISBLANK($B$2:$B$12),COUNTIF($D$1:$D1,$A$2:$A$12),""),0))    
E2 =INDEX($A$2:$A$12,MATCH(0,IF($B$2:$B$12=$D$2,COUNTIF($E$1:$E1,$A$2:$A$12),""),0))
F2 =INDEX($A$2:$A$12,MATCH(0,IF($B$2:$B$12=$E$2,COUNTIF($F$1:$F1,$A$2:$A$12),""),0))
G2 =INDEX($A$2:$A$12,MATCH(0,IF($B$2:$B$12=$E$3,COUNTIF($G$1:$G1,$A$2:$A$12),""),0))
H2 =INDEX($A$2:$A$12,MATCH(0,IF($B$2:$B$12=$E$4,COUNTIF($H$1:$H1,$A$2:$A$12),""),0))

Je nachdem, wie Sie diese sortieren möchten, können Sie auf jeden Fall eine Hierarchie erstellen oder eine Pivot-Tabelle verwenden.

Ich würde den Spalten A und B wahrscheinlich Namen geben, damit Sie mit benannten Bereichen arbeiten können.

verwandte Informationen