letzte befüllte Spalte (ohne Gesamtergebnis) einer Pivottabelle

[Scotty7]

Ein Hallo in die Runde!

Angenommen eine ungefilterte Pivottabelle beginnt mit ihren Werten in Zelle H8 und endet inklusive Gesamtergebnis (Spalte und Zeile) bei Zelle N14. Sobald Filter gesetzt sind, kann die Pivottabelle weniger Spalten und/oder weniger Zeilen anzeigen. Vor der Pivottabelle (sprich außerhalb) in der gleichen Zeile wie die erste Zeile in der Pivottabelle sollen nun die Anzahl der Pivotspalten eingetragen werden. Diese Zahl ist logischerweise dynamisch. Denn sie kann variieren, je nach Filterung. Wie lautet dafür die Formel? Eine VBA-Lösung scheidet aus.

Nun kommt noch ein weiteres Anliegen dazu. Wie kann ich ermitteln (ebenfalls ohne VBA), wieviel der Spalten in der Zeile einen Wert beinhalten? Es gibt ja auch Fälle, wo dort kein Eintrag steht.

Hintergrund ist, dass ich den prozentualen Anteil berechnen will, wieviel der vorhandenen Spalten haben echte Werte? Diesen Wert möchte ich ebenfalls außerhalb der Pivottabelle vorne eintragen, Zeile für Zeile der Pivottabelle.

Klingt vielleicht etwas holprig mein Anliegen. Aber ich hoffe, es versteht jemand und kann Formeln nennen.

Eine Beispieldatei könnte aufklärend sein. Darum ist sie hier angehängt. Es ist beabsichtigt das Ganze dann auch bei großen Pivottabellen anzuwenden, die über 50 Spalten haben kann und über 500 Zeilen.

Danke vielmals!

Scotty

[Luschi]

Hallo Scotty,

hier mal mein 1. Versuch.

Gruß von Luschi
aus klein-Paris

[Scotty7]

Hallo Luschi (klingt etwas seltsam die Anrede - sorry) :),

vielen Dank erst mal für die angebotene Lösung. Die Formeln, die darin eingebaut sind, habe ich so noch nicht gesehen und ich verstehe sie auch nicht ansatzweise. Ich benutze Microsoft 2016. Für eine Sekunde werden beim Öffnen der frisch herunter geladenen Exceldatei die richtigen Werte angezeigt. Oben ist ein gelber Balken mit "Bearbeitung aktiveren", den ich bestätige. Danach verschwinden zumindest die Zahlen unter [mit Werten] und folglich von [Prozent] und ein "#Name?". Vorne unter Spalte [Anzahl Spalten] bleibt die Zahl 6 zunächst noch stehen. Aber filtere ich in der Pivottabelle [Jahr] wird ie Anzahl der Spalten nicht mehr angegeben, sondern auch dort erscheint "#Name?". Ob das mit den Begriffen

_xlfn
_xlpm

zusammenhängt, bzw. mit den durch Punkt getrennten REDUCE und LAMBDA-Befehlen?

Vielen Dank für weitere Unterstützung.

Viele Grüße
Scotty

[Luschi]

Hallo Scotty,

mit Excel 2016 kann das natürlich nicht funktionieren; aber in Deinem Anfragekopf steht: Version(en): 2021

Gruß von Luschi
aus klein-Paris

[Scotty7]

Hoi, das mit dem Kopf oben rechts und den Versionen 2021 habe ich aktiv noch gar nicht registriert. Da muss ich mal schauen, wie ich da hin komme, um das zu ändern. Kann mich gar nicht erinnern, dass ich da jemals was eingestellt habe. Hmm?...

Dadurch bleibt natürlich mein Problem mit der Zählerei bestehen, vor allem weil es ja dynamisch sein muss, also auch gefilterte Werte berücksichtigen werden müssen.

Vielleicht erbarmt sich jemand, oder Du, falls Du mit den "älteren" Versionen auch eine Idee hast! Danke tausendfach!

Scotty

---

So, es ist mir nun gelungen die Kopfdaten von 2021 auf 2016 umzuändern. Danke für den Hinweis!!! Das ist natürlich elementar!

[snb]

PHP-Code:

=COUNT(H8:W8)/COUNT(H$7:W$7) 


[Scotty7]

Hallo, vielen Dank!

Ich ersetze das englische "COUNT" durch das deutsche "ANZAHL" und schon klappt das. Aber ich will das dynamisch halten. Dafür versuche ich den Bereich dynamisch anzusprechen, ohne die Spalte mit dem Gesamtergebnis. Je nach gesetztem Filter kann der Bereich ja unterschiedlich enden. Wenn ich selbst eine Lösung finde, poste ich sie hier gerne.

Gruß, Scotty

[Scotty7]

Wahrscheinlich sehe ich vor lauter Bäumen den Wald nicht mehr. Es will mir nicht gelingen, die letzte (gefilterte) Spalte in der Pivottabelle ohne Gesamtergebnis anzusprechen. Ich habe INDEX als Funktion versucht. Aber es klappt nicht. Vielleicht erbarmt sich wer im Netzwerk. Danke vielmals!

Scotty

[Scotty7]

Hallo ins Forum,

ich habe mich jetzt herum gespielt und eine Lösung für mein Anliegen dank Eurer Hilfe gefunden. Ich hänge hier die überarbeitete Exceldatei an. Vielleicht ist sie ja hilfreiche für andere. Es sind noch weitere, kleine Auswertungen mit eingeflossen. Und das Ganze kann einigermaßen dynamisch gehalten werden. Es handelt sich um eine Mischlösung zwischen dynamischen Zellen und Adressierungen an ein zu erwartendes Zellenende, sowohl bei den Zeilen als auch bei den Spalten. Aber seht selbst!

Besten Gruß

Scotty

[snb]

Alternative:

PHP-Code:

=(AGGREGATE(2;1;H9:N9)-1)/AGGREGATE(2;1;H$7:N$7)