Spaltenbezeichnungen Pivottabelle auslesen

[Scotty7]

Ein Hallo in die Runde,

gerade entdecke ich die Welt der Pivottabellen und habe heute erstmals davon gehört, dass es auch PowerPivot gibt. Dem werde ich mich erst dann widmen, wenn ich die "NonPowerPivots" etwas besser verstehe.

Im Anhang befindet sich eine EXCEL-Mappe, die Daten [Tabelle1] und eine Pivottabelle [PivotTbl] beinhaltet. Dort wird schnell klar, was mein Ziel ist. Es geht darum, dass ich vorne, sprich in einem Tabellenblattbereich vor der Pivottabelle habe, in dem ich ein paar Infos zu den einzelnen Zeilen der Pivottabelle ablege. So lasse ich aus einer Zeile den Bereich Spalte D bis G (Monat 4 bis 7) den Maximalwert in Spalte A schreiben. Gut, das könnte man auch dynamisch gestalten, sofern noch weitere Monate in Zukunft hinzu kommen oder wenn durch Filterung in der Pivottabelle ein paar Monate nicht dargestellt werden. Das habe ich aber bereits erreicht. Der Einfachheit wegen belasse ich es bei den vier Monatsspalten.

Im Grund ist es eine VERGLEICH und INDEX-Kombi. Aber bei einer Pivottabelle habe ich das noch nicht vollzogen. Bei den Werten, die in Spalte B zu stehen kommen sollen, handelt es sich um gleiche Bezeichnungen, wie die Spaltenbezeichnungen in der Pivottabelle, den Monaten also.

Ich hoffe, es klingt jetzt nicht zu sehr kompliziert. Und bedanke mich herzlich für Unterstützung.

Viele Grüße
Scotty

[scorefun]

Formel in B6

=INDEX($D$5:$G$5;VERGLEICH(A6;D6:G6;0))

und runterziehen

P.S.: Was machst Du, wenn es den Max-Wert mehrfach gibt?

[Scotty7]

Eine berechtigte Frage! Was tun bei Mehrfachvorkommen.

Ich könnte mir vorstellen, dass dann in der Spalte [Monat] dann zwei oder mehrere Zahlen mit Komma getrennt drin stehen. Dazu müsste man das aber irgendwie zählen oder abfangen können. Ich habe nun keine Ahnung, wie man dies wiederum bewerkstelligen würde. Aber die genannte Formel funktioniert schon mal gut bei einfachem Maximum-Vorkommen.

Herzlichen Dank!

Scotty

[scorefun]

Deine Excel-Version 2021 sollte das hier bei Mehrfachtreffern können:

=TEXTVERKETTEN(";";WAHR;FILTER($D$5:$G$5;D6:G6=A6))

   

[Scotty7]

Ganz herzlichen Dank Ralf!

Ich habe beide Varianten zusammengeführt. Dazu habe ich ein Feld angelegt, in der ich zählen lasse, wie oft das MAXIMUM vorkommt. Und dann eine WENN-Funktion mit den beiden Formeln kombiniert. Das Feld mit der Anzahl der MAXIMA ist in Spalte D. Ganz vorne unter Spalte A lasse ich mir noch die MITTELWERTe über alle Monate (Spalte F bis I) ausgeben.

Wie man erkennen kann, läuft da auch eine bedingte Formatierung mit. Das MAXIMUM wird dottergelb markiert.

Soweit so gut! ABER, wie sieht die Sache aus, wenn ich jetzt in der Pivottabelle filtere, wie z.B. den Monat, das Jahr oder Art oder Untergebiet? Dann passen die Formeln nicht mehr und auch die Bedingte Formatierung stimmt evtl. nicht mehr. Es ändern sich die Bereiche. Hmm???

Vielleicht hast Du da eine Idee!

Hier mal meine angepasste Formel fürs Wegschreiben des Monats bzw. der Monate mit dem MAXIMUM:

=WENN(D6=1;INDEX($F$5:$I$5;VERGLEICH(B6;F6:I6;0));TEXTVERKETTEN(";";WAHR;FILTER($F$5:$I$5;F6:I6=B6)))

[runtergezogen]

Klar, wahrscheinlich könnte man die Formel auch so zusammensetzen, dass die Hilfsspalte mit der Anzahl der MAXIMA überflüssig wird. Aber ich finde das gar nicht mal so dumm, dass man die Anzahl der MAXIMA ablesen kann.

Anbei die aktualisierte EXCEL-Datei.

Viele Grüße

Scotty

[scorefun]

Das Kombinieren beider Formeln ist doch überflüssig
Meine FILTER-Formel deckt beide Fälle ab (Einfach-/Mehrfachtreffer)

Bzgl Dynamik der Bereiche melde ich mich noch mal - da hatte ich bereits zuhause angefangen zu basteln

[scorefun]

Formeln dynamische Anpassung an den Pivot-Datenbereich: (Geht bestimmt auch eleganter)

Code:

A6 : =MAX(D6:INDEX(D6:ZZ6;;ANZAHL2($5:$5)-4))

B6 : =TEXTVERKETTEN(";";WAHR;FILTER($D$5:INDEX($D$5:$ZZ$5;;ANZAHL2($5:$5)-4);D6:INDEX(D6:ZZ6;;ANZAHL2($5:$5)-4)=A6))

Dynamische bedingte Formatierung :

Zelle D6 anklicken / Bedingte Formatierung / Neue Regel - und dann gemäß Bild konfigurieren

   

[Scotty7]

Hoi Ralf,

das sieht schon ganz gut aus, trifft aber nicht ganz ins Schwarze, weil es hinten ja die Spalte mit dem Gesamtergebnis gibt. Und die wird natürlich beim MAXIMUM gefunden. Und entsprechend wird bei Monat auch "Gesamtergebnis" eingetragen. Erwünscht ist, dass dynamisch nur bis eine Spalte davor das MAXIMUM ermittelt wird. Ich habe versucht, das selbst zu beheben, bin aber gescheitert. Hast Du da eine Idee? Merci vielmals!

Gruß, Scotty

---

Sorry Ralf. Ich habe - glaube ich - einen Fehler gemacht. Statt per Drag & Drop habe ich die Formel händisch eingegeben und die 4 vergessen abzuziehen. Das scheint für den richtigen Bereich zu sorgen. Ich teste das jetzt mit ein paar Filtern. Und berichte erneut.

[Scotty7]

Hallo Ralf,

mittlerweile sind ein paar Tage vergangen. Ich konnte ausgiebig testen, war aber auch in anderen Projekten involviert. Daher meine verzögerte Rückmeldung. Es läuft gut mit der Exceldatei und den Auswertungen. Ich habe es inzwischen auf einen richtig großen Datenbestand (über 500.000) ausdehnen. Und auch hier funktioniert es reibungslos. Daher bedanke ich mich an dieser Stelle herzlich für Deine Hilfe. Im Zusammenhang mit meinem Projekt hier, habe ich noch Ergänzungen vorgenommen, die erneut Unterstützung benötigen. Aber das eröffne ich in einem neuen Thema, weil das bestimmt für viele interessant sein könnte. MERCI vielmals!

Scotty