Getagged: AGGREGAT

Excel – Median für Pivot-Tabellen

Medien, bzw. Quantilswert 0,5 oder Quartil 2, sind leider nicht in Pivot vorgesehen. Die Bildung des Medians muss folglich neben der Pivot-Tabelle stattfinden. Wahlweise als Matrixformel MEDIAN oder als AGGREGAT-Funktion ab Excel 2010.

E388_1

Die AGGREGAT-Funktionen 16 bis 19 stehen für Quantils- und Quartilsberechnungen. Quantile werden in Bruchzahlen von 0 bis 1, vom kleinsten Wert bis zum größten Wert angegeben.

Quartilswerte werden von 0 bis 4 angegeben, wobei die 2 dem Median, die 0 dem kleinsten und die 4 dem größten Wert entspricht.

=AGGREGAT(17;6;A:A/((B:B=F10)*(C:C=G10));2)
bildet den Median der Werte, wo die Prüfungen B:B=F10 und C:C=G10 beide Male WAHR ergeben und somit WAHR * WAHR eine 1 ergeben. Würde eine Prüfung FALSCH ergeben, käme hier eine 0 heraus und es würde durch 0 geteilt werden. Damit würde der Fehler DIV/0 resultieren und der Wert würde wegen des Parameters 6 an zweiter Stelle von AGGREGAT nicht berücksichtigt werden.

{=MEDIAN(WENN((B:B=E10)*(C:C=F10);A:A;““))}
oder hier
{=MEDIAN(WENN((tab_Daten[Person]=F10)*(tab_Daten[Monat]=G10);tab_Daten[Werte];““))}

würden ebenfalls eine Matrix erscheinen lassen, in der jeweils nur die Werte berücksichtigt werden, die bei der Prüfung eine 1 bzw. ein WAHR * WAHR ergeben. Hier ist allerdings die Matrixformelschreibweise mit Abschluss der Formel mit STRG + SHIFT + ENTER erforderlich.

Hier die Datei zu Übungszwecken:
E388_Median_Pivot_Thehos<

Excel – SUMME über Bereich – AGGREGAT und INDEX

Es soll die Summe eines Bereichs gebildet werden. Der Bereich wird allerdings über zwei Markierungen neben der Tabelle definiert. Von der Zeile der ersten x-Markierung bis zur Zeile der zweiten x-Markierung soll summiert werden. Der Clou ist also, dass sich der Bereich für die Summenfunktion dynamisch zusammensetzen soll. Statt aber nun zum Beispiel =SUMME(B7:B12) einzugeben, werden die Zeilen für B7, also 7, und B12, also Zeile 12, über die Funktion INDEX ermittelt.

E361

=SUMME(INDEX(B:B;AGGREGAT(15;6;ZEILE(C2:C22)/(C2:C32="x");1);1):INDEX(B:B;AGGREGAT(14;4;ZEILE(C2:C32)*(C2:C32="x");1);1))

Dabei muss die Position, also die Zeile, des oberen x-Wertes ermittelt werden. Dies geschieht durch die Funktion: AGGREGAT(15;6;ZEILE(C2:C22)/(C2:C32="x");1). Nur in den Zellen, in denen ein x steht, wird auch durch WAHR geteilt – also in diesem Fall durch 1. Es werden also alle Zeilennummern des Bereichs genommen und durch 1 oder 0 geteilt. Von diesen Zeilennummern nimmt AGGREGAT(15;6;Array;1) die kleinste. Im Beispiel resultiert die Zahl 7.

Die Zeile des unteren x-Wertes wird durch AGGREGAT(14;4;ZEILE(C2:C32)*(C2:C32="x");1) ermittelt. Auch hier wird geprüft, ob ein x in einer der Zellen steht. Falls dies mit WAHR beantwortet werden kann, wird mit WAHR, also mit 1 multipliziert. Das Ergebnis ist eine List von Zeilennummern, die entweder mit 1 oder mit 0 bei FALSCH multipliziert werden. Von diesen Werten nimmt AGGREGAT(14;4;Array;1) den größten Wert. Im Beispiel resultiert die Zahl 12.

Statt nun B7:B12 in die Funktion SUMME als Bereich einzugeben, werden die Koordinaten über INDEX ermittelt. INDEX(B:B;AGGREGAT(15;6;ZEILE(C2:C22)/(C2:C32="x");1);1) führt also zu INDEX(B:B;7;1) und dies wiederum zur Zelle B7. Es wird die siebte Zelle aus Spalte B genommen. Das gleiche Prinzip gilt für B12. Die beiden INDEX-Funktionen werden über einen Doppelpunkt verbunden, so dass im Endeffekt B7:B12 resultiert.

Sollte kein x gesetzt sein, resultiert die Fehlermeldung #Zahl!
Hier könnte man die Formel noch einmal um die Funktion WENNFEHLER ergänzen:

=WENNFEHLER(SUMME(INDEX(B:B;AGGREGAT(15;6;ZEILE(C2:C22)/(C2:C32="x");1);1):INDEX(B:B;AGGREGAT(14;4;ZEILE(C2:C32)*(C2:C32="x");1);1));"")

Die Datei zum Download:
E361_SUMME_AGGREGAT_BEREICH

Das Video bei YouTube:
Videolink: http://youtu.be/06bTdR0sd-Y

Excel – mehrere Suchkriterien – mehrere Treffer – AGGREGAT

Mehrere Suchkriterien und mehrere potentielle Treffer? Kein Problem für die Funktion AGGREGAT.

E360

Die Formel in Zelle H7 wird nach unten kopiert:
=WENNFEHLER(INDEX(tab_Afrika[Auftrag];AGGREGAT(15;6;ZEILE(tab_Afrika[Auftrag])/((tab_Afrika[Land]=$I$1)*(tab_Afrika[Kunde]=$I$2)*(tab_Afrika[Verkäufer]=$I$3));ZEILE()-6)-1;1);"")

Der Teile dieser Funktion:
((tab_Afrika[Land]=$I$1)*(tab_Afrika[Kunde]=$I$2)*(tab_Afrika[Verkäufer]=$I$3)) sorgt dafür, dass für jede einzelne Zeile geprüft wird, ob Land, Kund und Verkäufer den eingestellten Kriterien in den Zellen I1 bis I3 entsprechen. Falls alle drei Prüfungen stimmen, kommt überall ein WAHR heraus und somit ein 1*1*1, also eine 1. Stimmt nur eine Prüfung nicht, wir diese mit FALSCH beantwortet, und es kommt z.B. ein 0*1*1, ein 0*1*0 oder ein 1*1*0 heraus. Somit hätten wir einen Divisor von entweder Null oder Eins.

ZEILE()-6 ist wichtig, um die unterschiedlichen passenden Einträge zu finden. Gibt es mehrere Treffer – wird also oben durch 1 dividiert – möchte man den erstkleinsten, zweitkleinsten, drittkleinsten Wert haben. Da die Formel hier in Zeile 7 beginnt, wird mit ZEILE() die Zahl Sieben errechnet und dann einfach immer eine 6 abgezogen. Eine Zeile tiefer steht dann eine 8-6, also eine 2.

ZEILE(tab_Afrika[Auftrag]) ermittelt einfach nur, in welchen Zeilen die Daten der Auftragswerte stehen. Die Daten beginnen in Zeile 2 und laufen bis Zeile 320. Da aber diese Zeilennummern durch entweder 1 oder 0 geteilt werden, bleiben hier nur die Zeilennummer übrig, die den drei Kriterien entsprechen.

AGGREGAT(15;6;Array;k) sorgt dafür, dass der k-Kleinste Wert aus dem Array genommen wird. Da aber durch die Division durch Null viele Fehler erzeugt wurden und diese Zeilennummern nicht mitgezählt werden sollen, sorgt das zweite Argument mit dem Wert 6 dafür, dass wirklich nur der k-kleinste Wert aus den zutreffenden Zeilen gezogen wird.

Die Übungsdatei zum Download:
E360_Zwei_Suchkriterien

Das Video bei YouTube:
Videolink: http://youtu.be/He3dblboncw

Excel – Listen dynamisch per Funktion filtern – AGGREGAT und FINDEN

Per Funktion soll eine Liste ausgelesen werden. In der ersten Spalte einer Liste befinden sich Artikelnummern. Diese Nummern beinhalten ein Kürzel, nachdem an anderer Stelle gefiltert werden soll. Regulär wäre dies natürlich an Ort und Stelle per Filter möglich. Wünscht man aber zur weiteren Verwendung eine Liste an anderer Stelle, müsste man eigentlich auf den erweiterten Filter zurückgreifen… oder?

E359_1

Die Funktion in Zelle E5 wird weiter nach unten kopiert:
=WENNFEHLER(INDEX(tab_Artikel[Artikelnummer];AGGREGAT(15;6;ZEILE(tab_Artikel[Artikelnummer])/(FINDEN(c_Suchkriterium;tab_Artikel[Artikelnummer];1)>0);ZEILE()-4)-1;1);"")

Zelle E2 trägt den Namen c_Suchkriterium, die linke Tabelle trägt den Namen tab_Artikel.

Die Funktion sucht Zeile für Zeile ab, ob über die Funktion FINDEN(c_Suchkriterium;tab_Artikel[Artikelnummer];1) ein Zahlenwert größer Null oder ein Fehler auftritt. Bei einem Fehler, kann das c_Suchkriterium nicht in Spalte [Artikelnummer] gefunden werden – es resultiert hier ein Fehlerwert. Bei allen Treffern kommt ein Zahlenwert größer als Null heraus. Durch den Vergleich mit größer als Null resultiert ein WAHR. Es werden also alle Zeilen ermittelt, in denen das c_Suchkriterium steht.

Nun gilt es, die unterschiedlichen Treffer des c_Suchkriteriums aufzulisten. Dazu dient ZEILE()-4. Minus vier deshalb, da die Formel hier in Zeile 5 startet.

Bei den unterschiedlichen untereinander stehenden Werten steht dort also eine 1, eine 2, eine 3 etc.

Als Resultat stehen hier die Zeilennummern aller Zeilen, in denen das c_Suchkriterium vorkommt.

Nun wird in Spalte A mit der Funktion INDEX die Zeile ausgelesen… fertig… fast.

Sollte es keine Treffer mehr geben, man aber die Formel weiter nach unten gezogen haben, stehen hier Fehlermeldungen, diese werden mit WENNFEHLER umgangen.

Für den Namen und die Artikelnummer braucht man nun nur noch mit einem SVERWEIS die Tabelle auslesen.

Download der Übungsdatei:
E359_Teilelisten

Hier geht es zum YouTube-Video:
Videolink: http://youtu.be/L99bbA8W0sU

Excel – Rangliste bei Sportereignis – AGGREGAT

Ganz ehrlich… ich bin noch keinen Marathon gelaufen – aber theoretisch bin ich recht gut vorbereitet.
Nach einem Marathonlauf werden die Zeiten von 23 Läufern ausgewertet.

Die Tabelle mit den Namen, den Zeiten und der Platzierung wurde als Intelligente Tabelle über die Tastenkombination STRG + T formatiert und in tab_Laufzeiten umbenannt.

E358_1

In Spalte C wurde die folgende Formel eingegeben:
=RANG.GLEICH([@Zeit];[Zeit];1)

RANG.GLEICH sucht den aktuellen Zeitwert in der Spalte B [Zeit] und ermittelt den Rang. Bei gleichen Plätzen, haben die Werte alle den gleichen Rang. So kann es einen ersten, einen zweiten und drei dritte Plätze geben, dafür dann erst wieder einen sechsten Platz. Die 1 als Argument zum Schluss sorgt dafür, dass die Werte vom kleinsten zum größten Wert hin gesucht werden. Der kleinste Wert und somit die schnellste Laufzeit hätte somit den Rang 1.

Ohne Intelligente Tabelle hätte die folgende Formel in Zelle C2 geschrieben und nach unten kopierten werden müssen:
=RANG.GLEICH(B2;$B$2:$B$24;1)

Wie man in dem Bild erkennen kann, gibt es zwei dritte und zwei achte Plätze. Und das machte das ganze ein wenig komplizierter, wenn man die Namen der Personen in einer Rangliste ausgegeben haben möchte. Sucht man zum Beispiel mit den Funktionen KKLEINSTE, INDEX und VERGLEICH nach den schnellsten Zeiten, findet VERGLEICH lediglich den ersten, aber nicht die weiteren Treffer. Also muss eine Matrixfunktion her… aber bitte ohne Matrixschreibweise in geschweiften Klammern. Ich nutzte hier die Funktion =AGGREGAT(15;..;..;ZÄHLENWENN(...)), um zum gewünschten Ergebnis zu kommen.

In Zelle F2 steht die Formel:
=AGGREGAT(15;4;tab_Laufzeiten[Zeit];ZEILE()-1)
Hier hätte auch =KKLEINSTE(tab_Laufzeiten[Zeit];Zeile()-1) stehen können.

Allerdings ist eine Veränderung in =AGGREGAT(15;6;tab_Laufzeiten[Zeit];ZEILE()-1) sinnvoll, wenn Fehlerwerte in den Zeiten stehen könnten. Das zweite Argument steuert über den Wert 6, dass Fehlerwerte ignoriert werden sollen.

E358_2

AGGREGAT Typ 15 sucht den k-kleinsten Wert einer List. Da die Formel in Zelle F2 beginnt, also in der zweiten Zeile, suche ich eigentlich nach dem erstkleinsten. Mit der Formel Zeile()-1 ermittle ich in diesem Fall die aktuelle Zeile und ziehe einen ab.
Es wird der k-kleinste Wert in Spalte B gesucht, also [Zeit] der vorderen Tabelle. Diese Funktion führt dazu, dass die Zeiten aufsteigend sortiert angezeigt werden. Dies ist der einfache Teil.

In Zelle G2 steht die Formel:
=INDEX(A:A;AGGREGAT(15;6;ZEILE(B:B)/(B:B=F2);ZÄHLENWENN($F$2:F2;F2));1)

E358_3

Nun müssen zu den Zeiten oder halt Platzierungen die passenden Personen aus Spalte A gesucht werden. Mit (B:B=F2) wird geschaut, in welcher Zeile von Spalte B genau der Zeitwert der aktuellen Zeile liegt. Stimmen Die Zeit in Spalte F und der Wert in Spalte B überein, kommt hier der Wert WAHR heraus. Wenn man einen Wert durch WAHR teilt, bedeutet dies, durch 1 zu teilen. FALSCH ist entsprechend der Divisionsfehler #DIV0. Nun wird über die Formel ZEILE(B:B)/(B:B=F2) geprüft, in welcher Zeile der jeweilige Treffer der aktuellen Zeit vorkommt.

Über ZÄHLENWENN($F$2:F2;F2) wird ermittelt, wie häufig die aktuelle Laufzeit bereits vorkam. Kommt eine doppelte Laufzeit zum ersten mal in Zeile 5 vor, kommt hier eine 1 als Ergebnis heraus. Steht in Zeile 6 dieselbe Zeit, kommt hier eine 2 heraus. Ich suche also in der Formel AGGREGAT(15;6;…;k) den jeweils erstkleinsten, zweitkleinsten usw. Wert, der mit der Spalte F identisch ist.

Als Ergebnis resultieren also immer die Zeilen, in denen die gesuchten Personennamen in Spalte A stehen. Über INDEX(A:A;Ergebnis von AGGREGAT;1) wird also aus Spalte A der entsprechende Name angezeigt.

Download der Datei: E358_Läufer_Rangliste

Zum YouTube-Video:
Videolink: http://youtu.be/XJI_QwFEgbE