Kategorie: Datenlisten

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:

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:

Excel – Letzte beschriebene Zeile ermitteln – AGGREGAT

Die Funktion =ANZAHL2(A:A) würde eigentlich die letzte Zeile zurückgeben, wenn alle Werte in Spalte A durchgängig beschrieben wären und sich unter den sichtbaren Werten nicht noch Formeln oder Funktionen befinden würden, die das Ergebnis „“ liefern.

E309-b

Sollten Sich aber Formeln in Spalte A befinden, welche das Ergebnis =““ liefern, werden diese ebenso mitgezählt.

E309-c

Und leere Zellen machen das Ergebnis von =ANZAHL2(A:A) direkt unbrauchbar. Man kann zwar auch die relative neue Funktion =ANZAHLLEEREZELLEN(A:A) verwenden, stolpert hier aber auch über die „“-Zellen.

E309-d

Es muss also eine Funktion her, die folgende Punkte abdeckt:
– Es soll die letzte beschriebene Zelle einer Spalte ermittelt werden.
– Die letzte Zelle ist die Zeile mit der größten Zeilennummer.
– Leere Zellen sollen nicht berücksichtigt werden.
– Zellen mit dem Formelergebnis =““ sollen auch nicht berücksichtigt werden.

Seit Excel 2010 steht die Funktion AGGREGAT zur Verfügung. AGGREGAT(14;…) ermittelt wie KGRÖSSTE den k-größten Wert eines Bereichs – also den größten, zweitgrößten etc.

AGGREGAT(14) ist zudem eine Matrix- bzw. Arrayfunktion. Das bedeutet, es wird nicht generell über einen Bereich geprüft, sondern es wird jeder Wert des Bereichs einzeln betrachtet und geschaut, ob dieser spezielle Bedingungen erfüllt.

=AGGREGAT(14;4;(A:A<>"")*ZEILE(A:A);1)

Hier wird also geprüft, ob ein Wert in Spalte A ungleich leer ist. Falls ja, wird hier mit WAHR, also mit 1 multipliziert. Zusätzlich wird von jedem Wert die Zeilennummer ermittelt. Als Ergebnis von (A:A<>"")*ZEILE(A:A) resultiert eine Liste von Zeilennummern, der Zellen auch tatsächlich Werte beinhalten.
AGGREGAT(14;..;..;1) sorgt nun nur noch dafür, aus der resultierenden Liste den 1-größten Wert zu ermitteln.

Sollte eine ältere Version von Excel vorliegen, kann man auch eine Matrix-Formel mit {}-Klammern erzeugen. Das Verfahren wird in der Reihe Matrix-Funktion Teil 09 in Video Excel # 281 beschrieben.

Zum YouTube-Video Excel # 309:

Excel – Liste der Einfach- und Mehrfacheinträge

Mit ZÄHLENWENN und AGGREGAT lassen sich Listen der Einfach- und Mehrfacheinträge in einer Liste erzeugen.
Die Funktion AGGREGAT gibt es erst seit Excel 2010. Man kann alternativ auch eine Matrixfunktion in Matrixschreibweise mit geschweiften Klammern erzeugen.

Die Fragestellung hat mich in den Kommentaren zu Excel # 352 erreicht. Wie identifiziert man die Einträge, die nur einmal auftreten und lässt diese gesondert neben den Daten darstellen, die mehrfach vorkommen. Da jeder Mehrfacheintrag auch irgendwann ein erstes Mal auftritt, ergibt sich eine knifflige Aufgabenstellung.

Dazu soll halt jeder Wert nur einmal in einer gesonderten Liste auftreten.
Vielleicht ein wenig unkonventionell gelöst… aber es funktioniert:

Die Formel steht neben jedem Datensatz in einer Intelligenten Tabelle. In Spalte KUNDE gibt es Kundennummern.
=(ZÄHLENWENN([Kunde];[@Kunde])=1)+(ZÄHLENWENN($A$2:[@Kunde];[@Kunde])=1)

Der erste Teil der Formel ermittelt, ob der Wert der aktuellen Zeile überhaupt nur einmal in der ganzen Liste auftritt. Falls der Vergleich mit der Zahl 1 zutrifft, kommt als Ergebnis der Wert WAHR heraus. Falls nicht, der Wert FALSCH.
=(ZÄHLENWENN([Kunde];[@Kunde])=1)+...

Im zweiten Teil der Formel wird ermittelt, ob von A2 bis zur aktuellen Zeile der Wert zum ersten Mal überhaupt auftritt. Alle nachfolgenden Zeilen werden nicht berücksichtigt. Durch den Vergleich mit der Zahl 1 wird auch hier nachher ein WAHR oder FALSCH stehen.
=...+(ZÄHLENWENN($A$2:[@Kunde];[@Kunde])=1)

WAHR erhält bei einer Addition den Wert 1, FALSCH automatisch den Wert 0. Somit ergibt sich eine 2, falls der Wert überhaupt nur einmal überhaupt in der Liste auftritt. Eine 1, falls der Wert mehrfach vorkommt und hier gerade zum ersten Mal auftritt. Eine Null, falls der Wert vorher schon einmal vorgekommen ist.

E354-1

Wie eine Liste von Unikaten anzulegen ist, wird in Video Excel # 352 und in folgendem Blogbeitrag beschrieben https://thehosblog.com/2013/05/02/excel-eintrage-einer-tabelle-ohne-doppelte-auflisten/

Link zum YouTube-Video:

Excel – Einträge einer Tabelle ohne Doppelte auflisten

Ich möchte die Liste aller Einträge einer Spalte haben – das aber bitte ohne Doppelte.
Über Pivot oder den erweiterten Filter kann ich mir einen Schnappschuss holen, der aber ständig aktualisiert werden muss. Bei meinen Formelrecherchen bin ich dann auf viele Lösungen mit Matrixschreibweise gestoßen, muss dafür aber die {}-Klammern mit STRG + SHIFT + ENTER setzen.

Als Nutzer von Excel 2010/2013 steht mir aber auch die Funktion AGGREGAT zur Verfügung. Und mit dieser mächtigen Arrayfunktion (Typ 15), INDEX, ZEILE und WENNFEHLER kann ich mir zuverlässig die einzelnen Werte einer Intelligenten Tabelle oder eines normalen Bereichs ausgeben lassen.

Meine Daten befinden sich in den Spalten A:D und sind als Intelligente Tabelle mit dem Namen tab_Daten formatiert.
Ich möchte die Daten aus Spalte A – tab_Daten[Kunde] in Spalte F aus gewiesen bekommen.

352-1

In Spalte D mit dem Namen tab_Daten[Erstmals] befindet sich folgende Formel:
=ZÄHLENWENN($A$2:[@Kunde];[@Kunde])

Die Formel steht in F2 und wird nachher runter kopiert:
=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;ZEILE(tab_Daten[Kunde])/(tab_Daten[Erstmals]=1);ZEILE()-1);1);"")

Die Formel aus F2 schrittweise aufgelöst:
1. ZEILE(tab_Daten[Kunde]) gibt die Zeilen der Einträge im Bereich tab_Daten[Kunde] zurück. Man hätte auch eine andere Spalte wählen können, da alle gleich lang sind.
=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;{2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18; 19;20;21;22;23}/(tab_Daten[Erstmals]=1);ZEILE()-1);1);"")

2. tab_Daten[Erstmals] gibt die Werte aus Spalte D zurück.
=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;{2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18; 19;20;21;22;23}/({1;1;1;2;1;1;1;1;2;3;2;3;1;4;4;5;3;2;2;5;2;1}=1);ZEILE()-1);1);"")

3. Der Vergleich der Wert aus Spalte D mit dem Wert 1. Bei Übereinstimmung WAHR, falls nicht FALSCH.
=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;{2;3;4;5;6;7;8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23}/{WAHR;WAHR;WAHR;FALSCH;WAHR;WAHR;WAHR;WAHR;FALSCH;FALSCH;FALSCH;FALSCH;WAHR;FALSCH; FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH;WAHR};ZEILE()-1);1);"")

4. Die Zeilennummern der Tabelle werden durch WAHR (1) und FALSCH (0) geteilt. Bei einer Division durch Null kommt die Fehlermeldung #DIV/0. Der Parameter 6 bei AGGREGAT sorgt dafür, dass diese Werte ignoriert werden.
=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;{2;3;4;#DIV/0!;6;7;8;9;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;14;#DIV/0!;#DIV/0!; #DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;23};ZEILE()-1);1);"")

5. Anschließend wird ganz rechts in der Formel die aktuelle Zeile ausgelesen und um 1 reduziert (da F2 in Zeile 2 steht, muss hier bei der obersten Formel eine 1 herauskommen)
.=WENNFEHLER(INDEX(A:A;AGGREGAT(15;6;{2;3;4;#DIV/0!;6;7;8;9;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;14;#DIV/0!; #DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;#DIV/0!;23};{2}-1);1);"")

6. AGGREGAT liefert den kleinsten Wert des Arrays und ignoriert dabei die Fehler.
=WENNFEHLER(INDEX(A:A;{2};1);"")

7. Die Funktion Index nutzt den Zeilenwert, der über AGGREGAT ermittelt wurde, um die entsprechende Zeilenposition aus Spalte A:A auszulesen.
=WENNFEHLER("K-102";"")

8. Bei einem Fehler durch zu weiteres Herunterkopieren der Formel, würde WENNFEHLER die Formel einfach ausblenden. INDEX liefert das Ergebnis sauber ab. Der erste Eintrag steht.
="K-102"

–>
K-102

Datei zum Video:
Excel_03_Datenlisten_Teil_2

Link zu YouTube:

Excel – Tabellennamen in Formeln

Seit Excel 2007 bietet Microsoft Excel verbesserte Intelligente Tabellen. Diese Erweiterung gegenüber den ehemaligen Listen aus Excel 2003 hält auch Einzug in die Formelschreibweise und bietet viele neue Möglichkeiten für lesbarere und dynamischere Formeln und Funktionen.
Um die neuen Tabellennamen angeboten zu bekommen, sollte der Haken unter Datei – Optionen – Formeln – Tabellennamen in Formeln verwenden gesetzt sein.

E03_1
Achtung! Ältere Excel-Versionen kennen diese neue Schreibweise für Tabellenbezüge nicht und werden entsprechend Fehler anzeigen.

Übungsdatei:
Excel_03_Datenlisten_Teil_1
In der ersten Video-Übung wird eine Tabelle als Intelligente Tabelle formatiert, es wird eine berechnete Spalte ergänzt und extern platzierte Funktionen (Zählenwenn und Summewenn) greifen auf die Spalten der Intelligenten Tabelle zurück. Die Übung kann an der Übungsdatei nachvollzogen werden. Die hierauf aufbauenden Videos und Dateien werden in nachfolgenden Blogs angeboten.

Viel Spaß damit
Andreas

Zum YouTube-Video: