Gestern auf dem London Excel Meetup group, Munich, Germany.
Martin Weiß hat einen tollen Vortrag gehalten, wie man mit einer Hilfsspalte und der Funktion AGGREGAT überprüfen kann, ob eine Zeile ausgeblendet ist:
Ich wollte es mal wissen, ob sich AGGREGAT genauso verhält wie TEILERGEBNIS.
Zur Erinnerung: Die Funktionen SUMME, TEILERGEBNIS und AGGREGAT mit Parameter 9, beziehungsweise 109 berechnen die Summe:
Filtert man die Liste, werden nur die gefilterten Werte von TEILERGEBNIS und AGGREGAT summiert:
Blendet man Zeilen aus, werden diese nur von TEILERGEBNIS mit Parameter 109 und AGGREGAT summiert:
Filtert man die Liste UND blendet Zeilen aus, werden sie von TEILERGEBNIS und AGGREGAT summiert:
Das irritiert, weil die Hilfe erläutert, dass der Parameter 109 die ausgeblendeten und gefilterten Daten nicht summiert, der Parameter 9 jedoch nur die gefilterten.
Das heißt: Der Parameter 9 von TEILERGEBNIS summiert Werte von ausgeblendeten Zeilen, wenn kein Filter gesetzt ist, summiert sie jedoch nicht, bei einem eingeschalteten Filter.
AGGREGAT hingegen mit dem Parameter 9 verhält sich wie TEILERGEBNIS mit 109.
ich filtere in dem Kunden-Excel die Werte mittels einer
Pivot-Tabelle. Der Filter zeigt aber nur die tatsächlich vorhandenen Werte an.
Jetzt möchte ich im Pivot einen festen Wertefilter
definieren, unabhängig welche aktuellen Werte vorhanden sind:
Beispiel:
Ich möchte immer alle Einträge < 24 Stunden gefiltert
haben
Aktuelle Werteinträge sind 1 und 2 Stunden. Den Filter 24 Stunden kann ich aber erst auswählen, wenn es mindestens einen Eintrag mit 24 Stunden gibt. Lässt sich dies im Pivot einstellen. Ich habe bislang keine Möglichkeit gefunden.
Hallo Herr H.,
der Gedanke der Pivottabelle ist ja, die vorhandenen Werte zu gruppieren und die Zahlen zusammenzufassen (aggregieren, also: summieren, zählen, …) Wenn Sie andere Werte sehen möchten, müssen diese in der Liste stehen (man müsste sie ausblenden).
Hallo Herr Martin,
es gibt im Office 365 Excel den Befehl FILTER, der genau das
macht, was ich benötige. Nur hat mein Kunde leider eine ältere Version. Lässt
sich das in einem älteren Excel mit einem workaround bauen?
Viele Grüße
Hallo Herr H.,
Nein – bitte nicht die Funktion FILTER verwenden – sonst hat
die Firma ein Problem!
Was würde ich tun?
* entweder die Daten dazwischen verstecken (und die Zeilen ausblenden)
* oder die Daten auf einem anderen Blatt sammeln und dort
alle notwendigen Daten einsammeln.
Hum. Sonst? Müsste mal überlegen
Liebe Grüße
Rene Martin
Hallo Herr Martin,
Filter geht beim Kunden nicht, aber ist die Funktion so
gefährlich?
Ich habe mittels Pivot die Daten auf ein anderes Blatt
ausgelagert und nutze das Ergebnis für die Dropdown-Felder.
Ich habe jetzt einen Dummy-Wert eingefügt, damit die Pivots die Auswahlfelder behalten, auch wenn keine Daten auszuwerten sind. Ggf. wäre eine Lösung ganz ohne Pivots zu arbeiten, aber dazu bräuchte ich so etwas wie die Filter-Funktion.
Hallo Herr H.,
nein, nein: FILTER & co sind klasse – Problem: nicht
jeder hat diese Funktionen. Deshalb: bauen wir den Filter doch nach!
Werfen Sie mal einen Blick in meine Liste: In Spalte H befinden sich die sechs Werte. Einer wird in J2 ausgewählt. In Spalte L ermittle ich die Zeilennummer, falls gefunden. In Spalte M sammle ich diese Nummern ein; gruppiere sie also. Mit BEREICH.VERSCHIEBEN baue ich die Liste ab O1 auf (ich hätte auch INDIREKT oder INDEX / VERGLEICH verwenden können).
Ich könnte es auch mit AGGREGAT aufbauen – aber lassen wir das …
Wenn man in (intelligenten / dynamischen / formatierten) Tabellen eine Ergebniszeile einfügt, kann man dort die aggregierenden Funktionen SUMME, ANZAHL, MAX, … verwenden. Hierfür benutzt Excel die Funktion TEILERGEBNIS, die ausgeblendete Zeilen übergeht:
Warum nicht die Funktion AGGREGAT, fragt man sich und reibt verwundert die Augen. Die Antwort ist einfach: Die Tabellen wurden mit Excel 2007 eingeführt. Damals gab es schon TEILERGEBNIS. Die Funktion AGGREGAT hielt allerdings erst in Excel 2010 Einzug in die Tabellenkalkulation. Und wenn etwas einmal drin ist, dann wird es so schnell nicht wieder geändert.
Wollt ihr wissen, wie man Excel zum Absturz bekommt? Man muss die Funktion AGGREGAT in einem Namen verwenden und diesen in einem Diagramm.
Das Ganze geht so:
Eine Tabelle holt sich Werte aus einer anderen Liste. Da einige Werte nicht gefunden werden, werden diese als #NV angezeigt. In einem Diagramm werden die entsprechenden Kategorien verwendet:
Unschön, denke ich mir. Die Jahreszahlen, die keinen Wert haben, sollen ausgeblendet werden. Und lege vier Namen an: „Bau“, „IT“, „Verwaltung“ und „sonstiges“. Sie haben die Form:
auf der Suche meines Excel-Problems bin ich über Ihre Seite gestolpert und würd mich freuen, wenn Sie mir ggf. helfen könnten: Ich habe hier eine ziemlich umfangreiche Pivot Tabelle und vermisse die Funktion, die es mir erlaubt anstatt des Min. oder Max-Wertes bei den Wertfeldeinstellungen Quartile auszugeben. Aber auch bei den Rohdaten tue ich mir schwer. Ich schaffe es zwar für eine vorgegebene Auswahl die Quartile zu berechnen, aber was ich benötige ist eine dynamische Ausgabe, z.B. bei Auswahl über den Auto-Filter. Auch mit Excel 2016 und Power-Pivot habe ich es bisher nicht geschafft. Haben Sie eine Idee wie so was gehen könnte?
Viele Grüße
#####
Hallo Herr B.,
das ist eine interessante Frage – danke dafür.
Ich habe heute auf dem Nachhauseweg geknobelt, wie man so etwas lösen wollte. Dachte an PowerPivot – aber wenn Sie schreiben, dass es DORT kein Quartil gibt, muss ich dort auch nicht mehr auf die Suche geben.
Wenn Sie eine einfache Pivottabelle hätten, könnten Sie Sie mit Matrixfunktionen oder der Funktion AGGREGAT nachbauen. Ich habe es mal versucht – siehe Anhang. Allerdings schreiben Sie „umfangreiche“ Pivottabelle – ich fürchte das Schlimmste.
Übrigens hat Andreas Thehos ein Video dazu gemacht:
