Der SVERWEIS ist mit Sicherheit eine der wichtigsten Funktionen, die dem versierten Excel-Anwender zur Verfügung stehen und sie gehört natürlich auch zu meinen Favoriten.
Die SVERWEIS-Funktion liefert bekanntermaßen zu einem Suchkriterium in der einen Tabelle den ersten gefundenen Treffer aus einer anderen Tabelle zurück. Was macht man aber, wenn der gesuchte Wert mehrmals vorkommt und man gerne alle Ergebnisse angezeigt bekommen möchte?
Hier ist der SVERWEIS leider hoffnungslos überfordert. Wie man so etwas trotzdem hinbekommt, zeigt der heutige Artikel.
Die Ausgangslage
Ich habe wieder eine lange Tabelle mit Millionenstädten vorbereitet. Jeder Datensatz besteht aus den Feldern Land, Stadt und Einwohneranzahl:
Beispiel: Liste der Millionenstädte
(Die Beispieldatei kannst du dir gerne hier herunterladen)
Nun möchte ich mir für das Land, welches ich in Zelle F1 eingebe, die größte Millionenstadt anzeigen lassen. Da die Städteliste noch Einwohnerzahl absteigend sortiert ist, ist das kein Problem für den SVERWEIS:
SVERWEIS liefert nur ein Ergebnis
Aber du weißt ja schon, was kommt: Es gibt in vielen Ländern natürlich mehrere Millionenstädte und die möchte ich alle gerne aufgelistet bekommen. Für Brasilien soll das Ganze also etwa so aussehen:
Ziel: Alle Städte zu einem Land
Die gute Nachricht: Auch hierfür gibt es eine Lösung, nämlich die INDEX-Funktion
Die nicht ganz so gute Nachricht: INDEX alleine reicht leider nicht aus. Es müssen mehrere Funktionen kombiniert und obendrein in eine Array-Formel gepackt werden. Und um das Endergebnis schon vorwegzunehmen, zeige ich hier die fertige Formel:
{=INDEX($B$2:$B$309;KGRÖSSTE(($A$2:$A$309=$F$1)*(ZEILE($A$2:$A$309)-1);ZÄHLENWENN($A$2:$A$309;$F$1)+1-ZEILE(A1)))}
So, das war’s schon.
Wenn du also faul bist, dann kannst du hier aufhören zu lesen. Kopiere dir einfach diese Formel in deine Arbeitsmappe und vergiss dabei nicht, dass es sich um eine Array-Formel handelt. Das heißt, die geschweiften Klammern dürfen nicht von Hand eingegeben werden. Stattdessen muss die Eingabe mit der Tastenkombination STRG+Umschalt+Eingabe abgeschlossen werden.
Wenn du jedoch ein wenig neugierig bist (und davon gehe ich ehrlich gesagt aus), dann liest du weiter und lernst, was sich hinter diesem komischen Konstrukt verbirgt.
Die Array-Formel wird aufgedröselt
Fangen wir also beim Endergebnis an und bewegen uns Schritt für Schritt durch die einzelnen Elemente der Formel. Im Grunde handelt es sich also um eine spezielle Variante der INDEX-Funktion:
=INDEX(Bezug; Zeile)
Sie gibt als Ergebnis den Wert zurück, der sich in der angegebenen Zeile im Bereich des angegebenen Bezugs befindet. In unserem Beispiel wäre der Bezug also die Liste mit den Städtenamen. Die Schwierigkeit liegt nun darin, die richtige Zeile zu finden. Genauer gesagt, die richtigen Zeilen, denn wir wollen ja alle relevanten Städte finden. Und genau das ist der Grund, warum die INDEX-Funktion hier in einer Array-Formel zum Einsatz kommt:
INDEX-Funktion in der Array-Variante
Nehmen wir uns also den Teil der INDEX-Funktion vor, der die Zeilen bestimmen soll. Dabei handelt es sich um die KGRÖSSTE-Funktion:
=KGRÖSSTE(Matrix; k)
Sie liefert den k-größten Wert einer Matrix oder Liste. Also den 2.-größten, 3.-größten usw.
In unserem Fall sieht die Funktion so aus:
KGRÖSSTE(($A$2:$A$309=$F$1)*(ZEILE($A$2:$A$309)-1);ZÄHLENWENN($A$2:$A$309;$F$1)+1-ZEILE(A1))
Für das erste Argument der Funktion können wir jedoch keinen festen Bezug angeben, denn uns interessieren ja von der ursprünglichen Ausgangsliste nur die Städte, die in dem Land liegen, welches wir in Zelle F1 eingegeben haben. Das macht dieser Teil:
($A$2:$A$309=$F$1)*(ZEILE($A$2:$A$309)-1)
Der relevante Bezug für KGRÖSSTE
Um besser zu verstehen, was diese Formel genau macht, schreiben wir die beiden Elemente in eigene Zellen in den Spalten E und F:
Prüfung: Stimmt das Land überein?
Wichtig:
Wenn du das Beispiel selbst nachvollziehen möchtest, musst du die Eingabe der Formel unbedingt mit STRG+Umschalt+Eingabe abschließen. Das ist wirklich wichtig, ansonsten funktioniert es nicht! Danach kannst du die Formel in alle Zellen bis E311 kopieren.
Damit wird für jedes einzelne Land in Spalte A geprüft, ob es mit dem in Zelle F1 eingegebenen Land übereinstimmt (= WAHR) oder eben nicht (= FALSCH).
In Spalte F verfahren wir mit dem zweiten Teil der Formel genauso:
Bestimme die Zeilennummer
Hier passiert nichts anderes, als dass wir die Position eines jeden einzelnen Landes in der Liste bestimmen. Da in meiner Tabelle in Zeile 1 die Spaltenüberschrift steht, muss bei der ZEILE-Funktion der Wert 1 abgezogen werden. Steht in deiner Tabelle die Überschrift in Zeile 5, ziehst du hier den Wert 5 ab.
In Spalte G werden die beiden Ergebnisse aus E und F noch miteinander multipliziert:
Nur die relevanten Zeilen
Da „FALSCH“ dem Wert 0 (Null) und „WAHR“ dem Wert 1 entspricht, haben nur die Zeilen mit dem von uns gesuchten Land einen Wert ungleich Null.
Und somit haben wir eine Liste aller relevanten Datensätze als Bezug für unsere KGRÖSSTE-Funktion.
Der k-Wert in KGRÖSSTE
Kommen wir jetzt zum zweiten Argument der KGRÖSSTE-Funktion. Diesen bestimmen wir über die ZÄHLENWENN-Funktion, welche ich zur besseren Veranschaulichung in Spalte H eingetragen habe:
ZÄHLENWENN liefert die Anzahl
Dieser Teil ist vielleicht nicht ganz so einfach nachvollziehbar. Die ZÄHLENWENN-Funktion ermittelt die Anzahl der Datensätze in Spalte A, die dem Land in F1 entspricht. Für Brasilien gibt es also insgesamt 13 Einträge. Da wir jeden einzelnen auflisten wollen, brauchen wir für die KGRÖSSTE-Funktion auch alle Werte von 1 bis 13.
Das ist der Grund, warum wir an die ZÄHLENWENN-Funktion noch eine kleine Berechnung anhängen:
=ZÄHLENWENN($A$2:$A$309;$F$1)+1-ZEILE(A1)
Der addierte Wert 1 steht wieder für die Spaltenüberschrift, die sich in Zeile 1 befindet. Dann ziehen wir noch die aktuelle Zeilennummer ab und erhalten somit für jede einzelne Zeile einen niedrigeren Wert.
In Spalte I habe ich an die KGRÖSSTE-Funktion die ermittelten Ergebnisse aus den Spalten G und H übergeben, so dass man sieht, was hier eigentlich passiert:
KGRÖSSTE liefert die jeweilige Position
Ich bekomme eine aufsteigende Liste aller relevanten Zeilennummern. Die Liste enthält genau 13 gültige Einträge, danach wird der Fehler #ZAHL! geliefert, da KGRÖSSTE mit Null bzw. negativen Werten für k nichts anfangen kann.
Und genau diese Liste mit den Zeilennummern wird in der zu Beginn gezeigten INDEX-Funktion verwendet, um die gewünschten Städte zu finden:
INDEX liefert die Stadt zur Position
Die Formel kopiert man einfach so weit nach unten, bis es wieder zu einem #ZAHL!-Fehler kommt. Die Einwohnerzahl in Spalte F lässt sich nun wieder mit einer einfachen SVERWEIS-Funktion ermitteln.
Kosmetik
Da die vielen #ZAHL!-Fehler nicht gerade schön aussehen, umschließen wir die INDEX-Funktion noch mit der WENNFEHLER-Funktion, so dass im Fehlerfall einfach eine leere Zelle angezeigt wird:
Schönheitskorrektur mit WENNFEHLER
Mein Leser Adrian Stöckli hat noch eine flexiblere Variante bereitgestellt, die ich dir nicht vorenthalten möchte. Damit umgeht man das Problem der manuellen Anpassung der Formel, wenn oberhalb der Tabelle zusätzliche Zeilen eingefügt werden müssen. Das Beispiel geht davon aus, dass die Kopfzeile der Tabelle in Zeile 6 steht:
{=WENNFEHLER(INDEX($B$7:$B$314;KGRÖSSTE(($A$7:$A$314=$F$6)*(ZEILE($A$7:$A$314)-ZEILE($F$6));ZÄHLENWENN($A$7:$A$314;$F$6)+ZEILE($F$6)-ZEILE(F6)));"")}
Auch zum Schluss nochmal der Hinweis: Die Eingabe muss mit STRG+Umschalt+Eingabe abgeschlossen werden!
Das war’s! Zugegeben, nicht ganz trivial. Aber schließlich bist du ja hier, um etwas zu lernen 🙂
Update vom April 2020:
Wie man an der Unmenge der Kommentare leicht erkennen kann, beschäftigt dieses Thema sehr viele Anwender. Daher habe ich eine kleine Artikelserie gestartet, die alternative Lösungsansätze vorstellt. Wenn du also mit diesem Problem kämpfst, dann schau dir unbedingt diese Alternativen an:
- Alternative 1: FILTER-Funktion (nur Office 365)
- Alternative 2: Der Spezialfilter
- Alternative 3: Eine Pivot-Tabelle
- Alternative 4: Power Query
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo Martin,
durchaus interessant und vor kurzem hätte ich das sogar benötigt – gelöst hab ichs dann mit einer Pivottabelle. Gibts irgendeinen Vorteil der Formellösung gegenüber Pivottabellen? Auf die schnelle fällt mir nichts ein.
Hallo Andi,
Pivot ist natürlich auch eine sehr gute Lösung. Im Grunde ist es nur eine Geschmacksache. Eine Formellösung würde ich persönlich dann bevorzugen, wenn man seine Ergebnisse z.B. innerhalb eines fest definierten Formats oder Rahmens liefern soll. Also z.B. innerhalb eines Dashboards. Aber möglich ist natürlich beides. Danke für den Tipp.
Schöne Grüße,
Martin
Hallo,
…für weiter Verwendungszwecke ist das natürlich gut. Ansonsten Pivot, obwohl man da ja auch auslesen kann.
Sinn macht es dennoch. Bspw. Welche Einzelteile gehören zu Artikelnummer XY?, dann kann man dann dies wiederum mit weiteren Formeln außerhalb einer Pivot sehr gut kombinieren.
Grundvoraussetzung ist hier die (Absteigende) Sortierung, richtig?
Hallo Ron,
die Sortierung der Ausgangsliste spielt für diese Lösung nur eine untergeordnete Rolle. Sie ist eigentlich nur dann notwendig, wenn die Ergebnisse auch sortiert sein sollen. Die Städte in meinem Beispiel werden auch gefunden, wenn die Ausgangsliste unsortiert ist.
Schöne Grüße,
Martin
Hallo Martin,
da ich mit Pivot-Tabellen ungeübt bin und ist diese Formel echt eine Tolle Angelegenheit.
Ganz lieben Dank mach weiter so Steffi
Hallo Steffi,
gern geschehen 🙂
Schöne Grüße,
Martin
Gibt es für so etwas nicht die Datenbank-Funktionen bzw. den Spezialfilter?
Die kommen ohne Array aus…
Hallo Christian,
es gibt in Excel so gut wie immer mehrere Lösungen. Datenbank-Funktionen sind auf jeden Fall auch ein guter Ansatz.
Schöne Grüße,
Martin
Mach weiter so immer super ?Inspiration
Ist auch für einen Artikelkatalog sergut wenn mann das als suchfunktion benutzt erscheinen alle änlichen Artikel super
Hallo Martin,
vielen Dank schon mal für die super Erklärung!
Wie muss ich die Formel denn aufbauen, wenn die Ergebnisse nicht untereinander, sondern nebeneinander (in Spalten statt Zeilen) aufgelistet werden sollen?
Danke & Gruß,
Caro
Hallo Caro,
Du musst im letzten Teil der Formel die Funktion ZEILE durch SPALTE ersetzen, also:
{=INDEX($B$2:$B$309;KGRÖSSTE(($A$2:$A$309=$F$1)*(ZEILE($A$2:$A$309)-1);ZÄHLENWENN($A$2:$A$309;$F$1)+1-SPALTE(A1)))}
Das war’s.
Grüße,
Martin
Hallo Martin,
ich habe ebenfalls Zeile durch Spalte ersetzt und jetzt funktioniert es auch bei mir. Allerdings habe ich ein Problem, das ich momentan nicht lösen kann. Und so ganz verstehe ich die Formel selbst mit Erklärung nicht ;-).
Und zwar wird mir immer ein Ergebnis weniger angezeigt als verfügbar (bei mir sind es gekaufte Artikel von einer E-Mail). Ich habe also eine E-Mail-Adresse mit 5 Produktkäufen, erhalte als Ergebnis zu dieser E-Mail-Adresse nur 4 Produkte.
Vielleicht kannst du nochmal erklären, warum man dieses „+1-SPALTE“ eintragen muss. Bei mir hat er dann immer den falschen Wert gezogen (Produkt aus darüberligender Zeile). Erst mit „+0-SPALTE“ hat es funktioniert. Vielleicht habe ich dadurch aber auch die Formel zerstört.
So sieht meine Formel aus:
=INDEX(Basis!$P:$P;KGRÖSSTE((Basis!$O:$O=$A2)*(ZEILE(Basis!$O:$O)-1);ZÄHLENWENN(Basis!$O:$O;$A2)+0-SPALTE(A1)))
VG
Max
Hallo Max,
das -1/+1 hängt nur damit zusammen, dass in meinem Beispiel in der ersten Zeile die Überschriften stehen und die Daten erst ab Zeile 2 beginnen. Wenn bei Dir die Überschriften in Zeile 2 stehen, musst Du in der ZEILE-Funktion -2 rechnen und in der ZÄHLENWENN-Funktion +2. Wichtig ist nur, dass in beiden Funktionen der gleiche Wert addiert bzw. subtrahiert wird.
Grüße,
Martin
Hallo Martin, danke für die schnelle Antwort. Ich habe auch Überschriften in Zeile 1. Dennoch funktioniert die Formel bei mir nur wenn ich statt -1/+1 -0/+0 eintrage.
Am Ende gibt er mir aber trotzdem immer einen Treffer zu wenig zurück, als im Datensatz vorhanden. Und zwar immer den letzten. Vielleicht liegt es auch am Aufbau meiner Tabelle, aber ich bin etwas ratlos wo der Fehler liegt.
VG
Max
ich habe jetzt mal meine daten in deine tabelle kopiert und dort funktioniert alles. Problem also gelöst, verstanden allerdings nicht 😉
Danke Dennoch!
Hallo Martin,
kenne mich leider nur sehr limitiert mit excel aus
aber vielen Dank! Sehr schöne Erklärungen der einzelnen schritte. Damit hab konnte ich das ganze nach kurzem Kopfschütteln auch verstehen 😛
Aber:
ich hatte vor das ganze für die arbeit zu verwenden und stoße dabei auf einige Probleme.
ich möchte eine Materialliste mit Materialcodes, Namen, Alternativnamen und Lieferanten mit einer Suchfunktion ausstatten, welche mir dann alle gesuchten teile der liste (also im Prinzip die ganze Zeile) ausspuckt bzw wenn die suche nicht eindeutig ist mir alternativen anzeigt.
eines der Probleme ist, dass die suche nur funktioniert, wenn ich das Suchkriterium genau eingebe.was aber bei teilweise wirren Namen (Chemiekonzern – hahaha) nicht immer möglich ist. mit den Mat.codes funktioniert das aber diese möchte ich mir oft heraussuchen.
weiters kann ich so leider auch nicht entweder/oder suchen sondern bin auf z.B. mat.codes beschränkt.
fällt dir vielleicht eine Lösung dafür ein?
Danke und Gruß,
Mario
Salut Martin
ich konnte dank deiner Seite ein Problem lösen und bin dabei mich mit der Formel auseinanderzusetzen. Ich hab offenbar die Funktionsweise noch nicht durchschaut, denn durch try&error habe ich mit verschiedenen Formeln das gleiche Resultat erhalten. Siehst du wieso beide das gleiche Ergebnis liefern?
{=INDEX(A:A;KGRÖSSTE(($J$3:$J$369=$AD$4)*(ZEILE($J$3:$J$369));ZÄHLENWENN($J$3:$J$369;$AD$4)+1-ZEILE(J1)))}
{=INDEX($A$3:$A$369;KGRÖSSTE(($J$3:$J$369=$AD$4)*(ZEILE($J$3:$J$369))-2;ZÄHLENWENN($J$3:$J$369;$AD$4)+1-ZEILE(J1)))}
Frohe Festtage!
Grüsse aus der Schweiz
Martin
Servus Martin,
die Formeln sind ja fast identisch. Bei der ersten Version verwendest Du die INDEX-Funktion für die komplette Spalte A (A:A). Bei der zweiten Version verwendest Du hingegen den Bereich A3:A369, also erst ab Zeile 3, dafür wird bei der ZEILE-Funktion der Wert 2 abgezogen. Somit kommst Du wieder auf das gleiche Ergebnis.
Schöne Grüße und einen guten Rutsch,
Martin
Hallo Martin,
vielen Dank, diese Formel ist endlich die Lösung auf meine Monatelange Suche!
Ich bin sehr glücklich!
Eine Frage hätte ich noch. Mein Suchkriterium ist ein Datum, es kann aber in zwölf Spalten (Monaten) vorkommen, das Ergebnis zieht der Formel immer aus Spalte B (Projektnummer).
Soll ich in diesem Fall diese Formel 12 mal nacheinander verknüpfen, oder gibt es eine elegantere Lösung?
Kann ich irgendwie angeben, dass die Suche erstmal in Spalte C, dann in Spalte F (in jede dritte Spalte, 12 mal) durchgeführt wird?
Herzlichen Dank für deine Hilfe!
Viele Grüße
Alexa
Hallo Alexandra,
wenn ich es richtig verstehe, kommt das Datum zwar nur einmal vor, aber es kann in einer von 12 Spalten stehen. Dann würde eine zusätzliche 13. Spalte anlegen, in der das Datum aus den 12 Spalten geholt wird und dieses Feld dann als Kriterium verwenden. Wenn tatsächlich nur ein Datum innerhalb der 12 Spalten vorhanden ist, könntest Du das in der 13. Spalte z.B. über die MAX-Funktion abgreifen.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für deine tolle Hilfe.
Ich möchte die Formel so umbauen, dass ich mehrere Suchworte in einer Liste von Namen finde. Geht das ohne weiteres?
In Gedanken bau ich ebenfalls ein Array aus Suchworte die ich nacheinander abarbeite, allerdings bekomme ich das in Excel nicht wirklich aufs Papier.
Danke schon im voraus für deine Hilfe.
Viele Grüße
Alex
Hallo Alexander,
mir ist keine Formellösung bekannt, mit der so etwas funktionieren würde. Ich fürchte, da musst Du auf VBA zurückgreifen.
Schöne Grüße,
Martin
Hallo Martin,
immer wieder faszinierend, was Leute aus einem solchen Programm herausholen können.
Ich versuche mich seit geraumer Zeit an einer Lösung für ein Bestellformular für vorhandene Kunden, die ihre Kundennummer natürlich oft nicht wissen. Eine Suche nach der Postleitzahl wäre das einfachste, eine klare Identifikation habe ich aber nur in der Kundennummer.
Es gibt eine Tabelle mit KD-Nr, Name, Straße, PLZ, Ort…. auf die jeder zugreifen kann.
Jeder hat ein Formular auf seinem Rechner, der mit SVerweis über die Kundennummer die Daten „holt“.
Wie kann ich über die PLZ (mehrere Treffer möglich) den passenden Kunden finden, die Kundennummer erkennen und verwenden in einem Formular auf meinem Rechner?
Vielenb Dank für bisherige Tips und hoffentlich zukünftige 🙂
Hallo Dietmar,
im Grunde genommen könntest Du so vorgehen, wie oben im Artikel beschrieben. Über die Array-Funktion könntest Du im ersten Schritt nach der PLZ suchen und alle Treffer anzeigen lassen. Im zweiten Schritt könnte diese Trefferliste dann die Basis für die Auswahl der Kundennummer sein (z.B. per Dropdown-Feld).
Schöne Grüße,
Martin
Hallo Martin, super gelöst und sehr gut erklärt. ich bin selbst ein absoluter Excelfreak (laut Aussagen meiner Kollegen) und sowas wie eine Allroundlösung bei uns im Unternehmen geworden. Nun stoße ich aber auf meine Grenzen, bzw. komme gedanklich nicht weiter (wahrscheinlich hänge ich immer wieder am gleichen Fehler). Deine hier aufgezeigt Formel war im Ansatz genau das was ich brauchte… nun wollen meine lieben Kollegen das Spiel aber weiter drehen…
Wir haben eine Liste die sehr gut funktioniert mit dieser Formel und einem Suchwert. nun hätten meine Kollegen aber auch gerne die Möglichkeit das ganze zu „verfeinern“ also sprich einen zweiten Suchwert mit einzubauen… alle von mir probierten Lösungen gehen bisher schief, vllt. kannst Du mir kurz helfen?
Danke schon einmal
Hallo Michael,
jetzt werden Deine Kollegen aber langsam ziemlich fordernd 🙂
Bleiben wir beim Beispiel oben. Ich füge in der Liste eine neue Spalte zwischen Land und Stadt, nennen wir sie Kategorie. Jede Stadt wird einer Kategorie A, B, C zugeordnet. Zusätzlich zum Land soll jetzt nach der Kategorie gefiltert werden, die ich in Zelle G2 auswähle. Die neue Formel lautet dann:
{=WENNFEHLER(INDEX($C$2:$C$309;KGRÖSSTE(($A$2:$A$309=$G$1)*($B$2:$B$309=$G$2)*(ZEILE($A$2:$A$309)-1);ZÄHLENWENNS($A$2:$A$309;$G$1;$B$2:$B$309;$G$2)+1-ZEILE(A1)));““)}
Beachte: Statt ZÄHLENWENN verwende ich jetzt ZÄHLENWENNS, um mehrere Kriterien zu berücksichtigen.
Schöne Grüße,
Martin
Hallo Martin,
leider sitzt hier auch das Problem vor dem Computer und kommt nicht weiter.
Ich habe 3 Spalten (Status, Stadt, Alter) und möchte, dass mir als Ergebnis die Anzahl angezeigt wird
die ich zuvor in einem Dropdown auswähle.
Bsp. Aktiv, München+Ingolstadt = 123 gleiche Zeilen
Mein Ansatz war, wobei hier ohne Dropdown
{=SVERWEIS(C1&C2;WAHL({1.2}F:F&U:U);2;0)}
C1&C2 sind die Auswahlfelder, F und U die Spalten die untersucht werden sollen, wie kann ich es
ermöglichen ,dass wenn in Spalte F und U in der gleichen Zeile vorkommt der Zähler um 1 erhöht
wird und am Ende die ganze Zahl zutreffender Zeilen angezeigt wird.
Hallo Daniel,
ich stehe momentan noch ein wenig auf dem Schlauch, was den Aufbau Deiner Tabelle angeht.
Was genau steht in den Spalten C, F und U. In C steht der Status? In F die Stadt und in U das Alter oder auch eine Stadt?
Und was soll in Spalte F und U in der gleichen Zeile vorkommen?
Ich bin verwirrt…
Schöne Grüße,
Martin
Hallo Martin,
Deine Formel ist Spitze! Sie hat mir sehr weitergeholfen. Jetzt habe ich aber ein anderes Problemchen. meine Formel sieht wie folgt aus:
=WENNFEHLER(INDEX(„“&StueliPos_mit_VK_Artikeln!$D$2:$D$154;KGRÖSSTE((„“&StueliPos_mit_VK_Artikeln!$A$2:$A$154=$A$2)*(ZEILE(StueliPos_mit_VK_Artikeln!$A$2:$A$154)-1);ZÄHLENWENN(StueliPos_mit_VK_Artikeln!$A$2:$A$154;$A$2)+1-ZEILE(StueliPos_mit_VK_Artikeln!A1)));““)
Sie funktioniert wie sie soll. Nun möchte ich das Ergebnis dieser Formel als SVERWEIS benutzen, um auf andere Daten zurückzugreifen. Das Ergebnis der Formel ist, je nach Suchkriterium „0173-6“. Die Formel die ich aufgestellt habe sieht wie folgt aus: =SVERWEIS(„“&A3;StueliPos_mit_VK_Artikeln!A2:F156;2;0)
Der SVERWEIS erkennt scheinbar das Ergebnis des Index-Arrays nicht, bisher konnte ich Formatierungen der Zellen mit „“& unberücksichtigt lassen. über einen Vorschlag wäre ich sehr Dankbar 🙂
Die Matrix ist eine SQL-Abfrage, welche ich in VB geschrieben habe, funktioniert auch einwandfrei.
Hallo Matthias,
auf Anhieb kann ich zumindest in den Formeln keinen Fehler entdecken. Allerdings kenne ich den Aufbau Deiner Tabellen und die Inhalte nicht, daher kann ich nur dort den Fehler vermuten.
Schöne Grüße,
Martin
Hallo Martin,
ich habs gelöst bekommen. Ich habe in der Matrix einfach nur die gewünschte Spalte eingegeben und mir so die Daten herausgezogen, wie ich sie brauche. Sprich auf den SVERWEIS kann ich komplett verzichten.
Trotzdem vielen Dank, denn mit dem Index Array kann ich super Arbeiten !!
Hätte hierzu eine Frage:
Kann ich diese Formel auch so einbauen, dass ich sie in den Bezug einer weiteren VERWEIS Funktion verschachtele?
Sprich: In Zeile 1 stehen Werte, manche eben mehrfach. In Zeile 2 stehen ebenfalls verschiedene Werte, auch mehrfach. Nun muss ich also zuerst alle übereinstimmende Werte aus Zeile 1 suchen, und dann die gefundenen Werte mit einem 2. Wert vergleichen. In Zeile 3 stehen letztendlich Die Ergebnisse, von denen das übrig gebliebene ausgegeben werden soll.
Kann mir hier jemand helfen?
Hallo zusammen,
Sollte jemand das Ganze mit der Suche nach Teilstrings benötigen, hier ist die angepasste Formel dafür.
E1: Suchstring, A2:A260:Matrix in der gesucht wird.
{=INDEX($A$2:$A$260;KGRÖSSTE(ISTZAHL(SUCHEN($E$1;$A$2:$A$260))*(ZEILE($A$2:$A$260)-1);ZÄHLENWENN($A$2:$A$260;“*“&$E$1&“*“)+1-ZEILE(A1)))}
Die Suche verwendet Wildcards, so dass mittels „bras“ Brasilien auch gefunden wird wenn in der Zelle z.B. „Brasilien – Rio de Janeiro“ steht. Aber auch „Rio“ wird fündig…
LG
Roger
Hallo Martin,
erst mal vielen Dank für die ausführliche Darstellung. Das ist super, selten eine so gute Erläuterung gelesen, hat mir sehr weiter geholfen.
Leider kommt bei mir nicht das Ergebnis raus, das ich mir erhofft hatte: in jeder Zeile wird das selbe Ergebnis angezeigt (immer den letzten gefundenen Wert). Wenn ich aber den „Zählenwenn“-Teil in jeder Zeile manuell eingeben (1, 2, 3, …), wirft er mir die verschiedenen (korrekten) Ergebnisse aus. Ich nutze Excel 2007. Funktioniert die komfortable Version nur mit neueren Excel-Versionen? Oder gibt es etwas, was man an den Einstellungen (Optionen) hierzu einstellen muss?
Ich fand es irritierend, dass ich das System verstanden habe, meine Software aber nicht so spurt wie sie soll (der Workaround mit der manuellen Eingabe funktioniert, ist aber nicht sehr elegant) 😉
Viele Grüße,
Frank
Hallo Frank,
diese Formel funktioniert auch mit Excel 2007. Zwei „beliebte“ Fehler:
Entweder Du hast die erste Formel nicht mit STRG+Umschalt+Enter abgeschlossen.
Oder Du hast Dich bei den Zellbezügen verzettelt (Stichwort absolute und relative Zellbezüge). Ich tippe mal darauf. Also ggf. nochmal genau prüfen, ob überall die Dollarzeichen korrekt gesetzt sind.
Schöne Grüße,
Martin
Eine richtig tolle Formel! Funktioniert hervorragend.
Was ist, wenn ich noch nach einem zweiten Kriterium suche? Das erste wird ja mit F1 für A2:A309 angegeben. Jetzt habe ich noch ein zweites F2 in der Spalte D2:D309; jetzt möchte ich nur die Städte ausgespuckt bekommen, die beide Kriterien erfüllen.
Ist es möglich noch nach einem zweiten Kriterium zu suchen, zum Beispiel:
{=Summenprodukt(INDEX($B$2:$B$309;KGRÖSSTE(($A$2:$A$309=$F$1)*(ZEILE($A$2:$A$309)-1);ZÄHLENWENN($A$2:$A$309;$F$1)+1-ZEILE(A1)))+{=INDEX($B$2:$B$309;KGRÖSSTE(($D$2:$A$309=$G$1)*(ZEILE($D$2:$A$309)-1);ZÄHLENWENN($D$2:$D$309;$G$1)+1-ZEILE(A1)))}}
Wäre super wenn ihr mir helfen könntet 🙂
Mit freundlichen Grüßen
Giacomo
Hallo Giacomo,
tut mir leid, wenn die Antwort ziemlich spät kommt. Hier wäre meine Lösung dazu. Die Annahme ist, dass in Spalte D das zusätzliche Kriterium steht und in Zelle G1 dieses Kriterium ausgewählt wird:
{=WENNFEHLER(INDEX($B$2:$B$309;KGRÖSSTE(($A$2:$A$309=$F$1)*($D$2:$D$309=$G$1)*(ZEILE($A$2:$A$309)-1);ZÄHLENWENNS($A$2:$A$309;$F$1;$D$2:$D$309;$G$1)+1-ZEILE(A1)));““)}
Schöne Grüße,
Martin
Hallo Martin,
vielleicht kannst du auch mir mit meinem Excel Problem helfen.
Ich möchte prüfen, ob eine Spalte mit Namen (Auszug aus einer Datenbank) einen von mehreren gesuchten Namen aus einem zweitem Sheet enthält. Überall dort, wo ein Name aus der Liste entdeckt wird soll in der Zielzelle ja ansonsten nein stehen.
Ich habe schon etliche Formelkombinationen versucht, komme aber nicht auf das gewünschte Ergebnis.
Hoffe, du kannst mir helfen. Danke!
Hallo Martin,
eine echt tolle Idee, hat mir gerade sehr geholfen.
Ein Fehler produziert die Formel bei mir jedoch, es wird zwar die richtige Anzahl an verschiedenen Konten angezeigt, aber immer nur der erste Eintrag in Spalte B. Mache ich hier etwas falsch?
Beste Grüße
Daniel
Hallo Daniel,
bist Du sicher, dass Du die Formel mit STRG+Umschalt+Eingabe abgeschlossen hast? Und danach die Formel soweit nach unten wie nötig kopiert hast?
Schöne Grüße,
Martin
Hallo Martin,
du hast ja supertolle Formeln, die ich in meine Listen einbaue,
aber wie suche ich aus einer Reihe von Datum´s, die gleichen werte.
Ich will wissen welche Ereignisse (wie z.B. Geburtsage) fallen auf einen Tag.
bestimmt ganz einfach.
Gruß Roland
Hallo Roland,
das machst Du einfach so, wie oben im Artikel beschrieben. Nur eben nicht mit Ländern und Städten, sondern mit Datum und Ereignissen. Funktioniert ganz genauso.
Schöne Grüße,
Martin
Hallo Martin,
erst mal danke für den tollen Artikel und die super Erklärung. Hat mir sehr weitergeholfen.
Prinzipiell funktioniert die Matrixformel bei mir auch. Allerdings habe ich die zu durchsuchenden Werte nicht in der Datei in der die Formel steht, sondern in einer separaten Datei. Solange ich beide Dateien geöffnet habe, funktioniert die Formeln einbahnfrei. Wenn ich allerdings die Datei mit den Ursprungsdaten geschlossen habe, zieht die Formel mit keine Werte.
Hast du hierfür eine Lösung? Dachte eigentlich das Matrixformlen dieses Problem umgehen sollten.?!
Grüße Kevin
Hallo Kevin,
dafür kenne ich leider auch keine Lösung. Manche Dinge funktionieren tatsächlich nur, wenn alle alle verknüpften Dateien geöffnet sind.
Schöne Grüße,
Martin
Sind die Daten auf dem Anderen Blatt zufällig Datenbankauszüge in VB geschrieben oder ein sonstiges Makro, das sich erst beim öffnen aktiviert? Denn dann können auch keine Daten gezogen werden, da sich diese erst beim öffnen Daten beziehen.
Hallo Martin,
Deine Formel war sehr nützlich bei der Lösung eines aktuellen Problems – vielen Dank! Könntest Du die Formel um eine weitere Bedingung erweitern? In meinen Fall wäre das die Prüfung, ob in der jeweils gleichen Zeile bestimmte Zellen leer sind, also z.B. in der Zeile 6 sinngemäß Anzahl2(H6:K6)=0.
Für einen Hinweis wäre ich Dir sehr dankbar.
Viele Grüße
Wolfram
Hallo Martin
Besten Dank für deine Erklärungen.
Ich bin mir aber nicht ganz sicher, ob ich mein Problem mit deiner Lösung auch beheben kann.
In einem Register habe ich eine Sammlung von Artikelnummern.
In einem weiteren Register habe ich eine Übersicht der Lieferanten, welche den entsprechenden Artikeln zugewiesen sind.
Nun möchte ich im ersten Register in einer beliebigen Zelle die möglichen Lieferanten aufgelistet haben.
Am liebsten mit einem Zeichen (/,;:) getrennt. Möglich wäre aber auch, dass der Lieferant in einzelnen Zellen hintereinander aufgelistet werden.
Kannst du mir da auch helfen? Ich habe es auch schon mit Pivot versucht, muss aber die Angaben in der entsprechenden Zeile haben und nicht untereinander.
Danke bestens für deine/eure Hilfe.
Gruss Lukas
Hallo Lukas,
im Grund geht es nur darum, die von mir im Artikel gezeigte vertikale Darstellung in eine horizontale Darstellung zu überführen.
Dazu musst Du nur statt die letzte ZEILE-Funktion in eine SPALTE-Funktion ändern.
Also aus
{=WENNFEHLER(INDEX($B$2:$B$309;KGRÖSSTE(($A$2:$A$309=$F$1)*(ZEILE($A$2:$A$309)-1);ZÄHLENWENN($A$2:$A$309;$F$1)+1-ZEILE(A1)));““)}
wird
{=WENNFEHLER(INDEX($B$2:$B$309;KGRÖSSTE(($A$2:$A$309=$F$1)*(ZEILE($A$2:$A$309)-1);ZÄHLENWENN($A$2:$A$309;$F$1)+1-SPALTE(A1)));““)}
Und dann natürlich die Formel nach rechts in jeweils in eine eigene Spalte kopieren. Wenn Du jetzt ein Land auswählst, werden die passenden Städte nebeneinander angezeigt. Und das kannst Du natürlich auf Deine Artikel und Lieferanten analog anwenden.
Schöne Grüße,
Martin
Ich finde diese Formel für mein Vorhaben echt perfekt. Ist es möglich diese Formel auch Tabellenübergreifend anzuwenden?
Tabelle 1 = Datensatz
Tabelle 2= Suchfunktion
Hallo Florian,
ja, das sollte keine Problem sein. Einfach die Formel entsprechend anpassen, so dass der jeweilige Blattname enthalten ist.
Schöne Grüße,
Martin
Was muss ich machen damit leere Zeilen nicht als 0 angezeigt werden?
=WENNFEHLER(INDEX($B$2:$B$96;KGRÖSSTE(($A$2:$A$96=$J$1)*(ZEILE($A$2:$A$96)-1);ZÄHLENWENN($A$2:$A$96;$J$1)+1-ZEILE(A1)));““)
Wenn ich ein Land eingebe und habe keine Stadt hinterleget wird mir die eigentlich leere Zeile mit einer 0 gefüllt.
Ist es möglich das die leere Zeile auch leer angezeigt wird?
Hallo Florian,
du müsstest dazu denn INDEX-Teil der Formel nochmal mit einer WENN-Funktion umschließen:
=WENNFEHLER(WENN(INDEX($B$2:$B$96;KGRÖSSTE(($A$2:$A$96=$J$1)*(ZEILE($A$2:$A$96)-1);ZÄHLENWENN($A$2:$A$96;$J$1)+1-ZEILE(A1)))=0;““;INDEX($B$2:$B$96;KGRÖSSTE(($A$2:$A$96=$J$1)*(ZEILE($A$2:$A$96)-1);ZÄHLENWENN($A$2:$A$96;$J$1)+1-ZEILE(A1))));““)
Alternativ kannst Du natürlich in den Excel-Optionen die Anzeige von Nullwerten für das betreffende Arbeitsblatt generell unterdrücken:
Excel-Optionen | Erweitert | Optionen für dieses Arbeitsblatt anzeigen | In Zellen mit Nullwert eine Null anzeigen
Schöne Grüße,
Martin
Hallöchen,
ich bin Absoluter Excel-Anfänger, und dein Formel und vor allem die Erklärung hat mir super geholfen, leider macht sie bei mir Fehler.
Ich verwende sie über mehrer Spalten und ab und zu kann es vorkommen, dass in einer Spalte in jeder Zelle das Suchkriterium steht, dann soll er mir einfach alle ausgeben er gibt mir dann aber nur jedes weite Ergebnis. Auch wenn nicht komplette Spalte gefüllt ist, sondern nur recht voll, fehlen auch schon einige Einträge, wie kann ich das Lösen?
Hallo Ric,
die hier vorgestellte Formel ist alles andere als trivial, daher Respekt, wenn Du Dich als Excel-Anfänger daran wagst. Deine Frage ist aber leider ein wenig zu allgemein gehalten, um hier einen konkreten Tipp geben zu können. In vielen Fällen liegt das Problem darin, dass der Wert nicht passt, der zur ZEILE-Funktion addiert und hinterher wieder abgezogen wird (in meinem Beispiel 1). Ist auch weiter oben in den Kommentaren schon mal beschrieben. Ansonsten hilft nur weiterprobieren…
Schöne Grüße,
Martin
Hallo Martin,
ich bin ebenfalls kein Excel-Experte – von daher vorab schon einmal vielen Dank für die ausführlichen Erklärungen! Dadurch hab ich die Formel super verstanden.
Allerdings bin ich bei der Weiterführung meiner Tabelle soeben auf das nächste Problem gestoßen und hoffe du kannst mir hier ebenfalls helfen. Ich habe nun, dank deiner Hilfe, eine Tabelle mit mehreren Spalten (T1, T2, T3, T4,….). Unter diesen Spalten sind Werte aufgelistet S1 bis S20. Hier können einmal nur 2 Werte stehen, manchmal aber auch mehr. Innerhalb der Spalten gibt es keine doppelten Werte. Spaltenübergreifend aber schon. Ich benötige nun eine Auflistung aller Werte ohne Dopplungen mit einer möglichst verständlichen Excelformel. Kannst du mir hier weiterhelfen?
Viele Grüße
Lisa
Hallo Lisa,
eine einfache Formel, wie man spaltenübergreifend alle eindeutigen Werte ausgibt, habe ich spontan leider auch nicht. Vielleicht hat ja ein anderer Leser noch einen Tipp.
Schöne Grüße,
Martin
Hallo,
deine Formel ist ziemlich gut, jedoch habe ich ein Anwendungsbeispiel, bei dem es vorkommt, dass die (in deinem Beispiel „Einwohnerzahl“ = 0 ist. In genau diesem Fall soll die Zeile nicht mit aufgeführt werden (auch nicht als Leerrzeile). Ich habe schon etwas experimentiert, bin der Lösung jedoch noch nicht näher gekommen. Wie kann ich in die Formel integrieren, dass Zeilen, die in deiner C-Spalte leer, oder „0“ sind, im Suchergebnis nicht mit aufgeführt werden?
Besten Dank im Voraus.
Grüße, Ronny
Korrektur:
…die Leerzellen, bzw. 0-Zellen sind natürlich nicht in der C-Spalte sondern in einer weiteren Spalte (Zusatzkriterium).
Grüße, Ronny
Hallo Ronny,
eine Formellösung kann ich hierfür leider auch nicht bieten. Stattdessen würde ich ganz pragmatisch eine Pivot-Tabelle erstellen und einen Wertefilter > 0 setzen.
Schöne Grüße,
Martin
Hallo,
Super Formel! Nur leider scheitert es bei mir bei der Umsetzung.
Ich habe eine Liste in der Rechnungen eingetragen werden mit Fälligkeitsdatum. Das Fälligkeitsdatum habe ich in Kalenderwochen um gewendelt. Ich möchte nun in einen weiteren Tabellenblatt alle Rechnungen sehen welche in der Kalenderwoche z.B.: 28 fällig sind.
Ich habe es soweit geschaft nur habe ich folgendes Problem:
=WENNFEHLER(INDEX(Liste!$A$9:$A$400;KGRÖSSTE((Liste!$E$9:$E$400=Tabelle1!$A$1)*(ZEILE(Liste!$E$9:$E$400)-8);ZÄHLENWENN(Liste!$E$9:$E$400;Tabelle1!$A$1)+8-ZEILE(Liste!8:15)));““)
Die ersten 8 Zeilen sind allgemeine Informationen und Beschriftung. Ab Zeile 9 Beginnen die Eintragungen. So wenn ich jetzt ab Schluss -Zeile(Liste!1:8) eingebe funktioniert es nicht. Es funktioniert erst wie oben in der Formel angezeigt, da bei 15 der erste Wert 28 beginnt.
Ich hoffe du kennst dich soweit aus.
LG Irene
Hallo Irene,
du brauchst eigentlich gar keinen Bereich (1:8 oder 8:15) eingeben, sondern nur
ZEILE(A8)
oder
ZEILE(8:8)
Der Bezug auf das Blatt Liste ist in der ZEILE-Funktion auch nicht notwendig, denn es geht ja nur darum, eine fortlaufende Nummer zu erzeugen.
Schöne Grüße,
Martin
Herzlichen Dank! Es Funktioniert! 🙂
Hallo Martin,
erstmal danke für die gute Erklärung.
Ich habe ein Problem. Ich habe eine Stückliste wo alle Produkte aufgeführt sind und daneben die Unterprodukte.
Ich möchte von einem Material alle Unterprodukte ausgegeben bekommen. Mit einer einfachen INDEX VERGLEICH Formel erhielt ich nur immer das erste Unterprodukt.
Mat1 123
Mat1 234
Mat1 345
Mat2 987
Mat2 654
… …
Ich habe in deine Excel Tabelle auch mal meine Nummern alle eingefügt, aber es kam leider wieder nur „“ (also nichts raus).
Habe darauf geachtet, dass eigentlich alles stimmt.
Deine Formel sollte hier ja klappen, das Problem ist aber es kommt nichts raus. (Wegen der WENNFEHLER kommt immer nur nichts „“ .
Hast du einen Tipp für mich wie ich das hinbekomme?
Hallo Max,
wenn du meine Beispieldatei verwendest, sollte es eigentlich klappen. Du musst nur darauf achten, dass die ganzen Formeln (also die mit WENNFEHLER) immer als Matrixformeln eingegeben sind. Zu erkennen an den umschließenden geschweiften Klammern.
Schöne Grüße,
Martin
Hallo Martin,
erstmal ein Lob für den super Artikel – leider bin ich noch ein ziemlicher Anfänger in Excel und kann dein Beispiel nicht auf meine Anforderung anpassen.
Ich muss Folgendes lösen:
Im Tabellenblatt1 (Basistabelle) habe ich eine Spalte mit Nummern (1-5 in Spalte A) und zu den Nummern gibt es in der Spalte nebenan (Spalte C) eine Beschreibung. Eine Nummer kann öfters auftauchen, hat dann aber eine andere Beschreibung.
Im Tabellenblatt2 möchte ich mir jetzt alle Beschreibungen ausgeben lassen, welche im ersten Tabellenblatt z.B. die Nummer 3 haben.
Klappt auch immer für den ersten Treffer wunderbar:
=INDEX(Basisliste!$C$2:$C$11;VERGLEICH($A$2;Basisliste!$A$2:$A$11;0)) —>ergibt die erste Beschreibung im der Nummer 3
(in A2 (Tabellenblatt2) steht die Nummer 3 als Suchkriterium)
Meine Frage: Im Prinzip ist es ja das selbe Problem wie in deinem Beispiel – nur leider brauche ich hier einen Vergleich und nicht die größte Zahlen. Wie müsste ich denn meine Formel anpassen, dass mir alle Treffer ausgegeben werden? Bzw. ist das überhaupt möglich mit dem Vergleich?
Vielen Dank schon mal!
Ela
Hallo Ela,
vielen Dank für das nette Feedback.
Was dein Problem angeht: In meinem Artikel geht es auch nicht um die größten Zahlen. Die KGRÖSSTE-Funktion wird nur verwendet, damit man auf die unterschiedlichen Zeilen kommt. Hast du dir die Beispieldatei schon heruntergeladen? Und dann probier‘ die Formel doch einfach mal auf deinen Fall bezogen aus.
Schöne Grüße,
Martin
Hallo Martin,
ich hoffe, meine Frage wurde in dieser Form nicht schon gestellt.
Ich möchte aus einer Tabelle sämtliche Maschinen (Maschinen-Nummern) auslesen, die im ausgewählten Jahr (1. Bedingung, zB. 2019) gefertigt wurden und zu einem bestimmten Maschinen-Typ (2. Bedingung, zB. Typ A).
Jedoch muss die Ausgabe nicht unbedingt nach Rang (KGRÖSSTE) sortiert werden, ich brauche nur die richtigen Maschinen-Nr.
Deine Funktion (siehe unten) hat schon funktioniert, wenn ich jedoch die Jahreszahl (Bedingung 1) ändere, hat er mir zwar die richtige Maschinen-Anzahl, jedoch die falschen Nummern (weil GRÖSSTEN zu erst) ausgelesen.
Spalte A=Maschinen-Nr, Spalte B=Jahr, Spalte C=Typ, Tabellen-Überschrift in Zeile 6, $A$5=Typ, $A$4=Jahr
{=WENNFEHLER(INDEX($A$7:A$500;KGRÖSSTE(($C$7:$C$500=$A$5)*(ZEILE($C$7:$C$500)-6);ZÄHLENWENNS($C$7:$C$500;$A$5;$B$7:$B$500;$A$4)+6-ZEILE(C7)));““)}
Kannst du mir bitte helfen?
Vielen Dank!
LG
Alex, H.
Hallo Alex,
ich würde mir an deiner Stelle nicht den Kopf für eine komplexe Formel zermartern, auch wenn ich das oben in meinem Artikel so gemacht habe 🙂
Stattdessen empfehle ich einfach eine Pivot-Tabelle. Damit sparst du dir sämtliche Formeln und kommst schneller ans Ziel. Einfach das Feld Maschinen-Nummer in den Zeilenbereich ziehen und die Felder Maschinen-Typ und Jahr in den Filterbereich. Fertig!
Schöne Grüße,
Martin
Hallo Martin,
mit den neuen dynamischen Arrays löst sich die komplexe Formel in ein relativ überschaubares Konstrukt auf:
=FILTER(B2:$C$309;$A$2:$A$309=F1)
Hallo Robert,
ja, die neuen Array-Funktionen werden das Excel-Leben in vieler Hinsicht vereinfachen. Es wird nur noch eine ganze Weile dauern, bis sie tatsächlich Verbreitung finden.
Schöne Grüße,
Martin
Ich habe ein ähnliches Problem, allerdings funktioniert Ihre Lösung bei mir nicht.
In der Spalte A habe ich Namen, in der Spalte B Vornamen, in der Spalte C w für weiblich oder m für männlich und in der Spalte M dazu passende Summen aus den Spalten E bis J.
Jetzt möchte ich in einer Zelle den Namen mit dem grössten Wert der Spalte M ausgeben unter der Voraussetzung, dass es eine Frau ist (sprich dass in der Spalte C ein w steht und
in einer anderen Zelle den Namen mit dem grössten Wert der Spalte M ausgeben unter der Voraussetzung, dass es ein Mann ist (sprich dass in der Spalte C ein m steht.
Ich bekomme das weder mit einem SVERWEIS in Kombination mit KGRÖSSTE, noch mit einer Kombination aus WENN mit KGRÖSSTE hin.
Können Sie mir helfen
Hallo Ulrich,
dafür gibt es je nach vorhandener Excel-Version unterschiedliche Wege. Wenn Sie Excel 2019 oder Office 365 im Einsatz haben, wäre das eine Variante (ich gehe im Beispiel davon aus, dass die Tabelle bis von Zeile 2 bis Zeile 100 geht, das müssen Sie halt auf Ihre Verhältnisse anpassen):
=INDEX($A$2:$A$100;VERGLEICH(MAXWENNS($M$2:$M$100;$C$2:$C$100;“M“);$M$2:$M$100;0))
Bei Excel 2016 oder älter müsste mit Array-Formeln gearbeitet werden:
{=INDEX($A$2:$A$100;VERGLEICH(MAX(WENN($C$2:$C$100=“M“;$M$2:$M$100));$M$2:$M$100;0))}
oder
{=INDEX($A$2:$A$100;VERGLEICH(KGRÖSSTE(WENN($C$2:$C$100=“M“;$M$2:$M$100);1);$M$2:$M$100;0))}
Die geschweiften Klammen dürfen Sie dabei nicht von Hand eintippen, sondern Sie müssen die Eingabe mit Strg+Umschalt+Eingabe abschließen.
Alle Formeln liefern jeweils den Namen zurück. Wenn Sie den Vornamen auch brauchen, müssen Sie das gleiche nochmal mit bezogen auf Spalte B durchführen.
Schöne Grüße,
Martin
Hallo Martin,
zunächst erstmal vielen Dank für die geniale Anleitung zu genau dem Problem, mit dem ich derzeit kämpfe.
Leider hat es bei mir noch nicht ganz zum Erfolg geführt.
Vorweg gesagt, ich arbeite mit LibreOffice und nicht mit Excel. Ich habe Deine Beispieldatei runtergeladen und im LibreOffice göffnet (ich habe es auch im nativen ods-Format neu gespeichert) und es funktioniert alles so wie es soll.
Wenn ich nun allerdings Deine Array-Formel in meine Buchungstabelle übertrage und alle Bezüge entsprechend anpasse bekomme ich einen #WERT!-Fehler. Ich habe die Formel in verschiede Spalten zerpflückt, um zu sehen, wo das Problem liegt. Ich habe rausgefunden, dass LibreOffice anscheinend mit diesem String: ($B$7:$B$1006=$E$2)*(ZEILE($B$7:$B$1006)-6) hat. Hier kommt eben der besagte #WERT!-Fehler.
Interessant ist allerdings, dass es funktioniert, wenn ich die zwei Operaten trenne und in zwei Spalten aufteile und diese dann in einer dritten Spalte miteinander multipliziere, dann funktioniert es.
Ich hoffe meine Frage ist nicht vollkommen deplaziert, da ich mit LibreOffice und nicht mit Excel arbeite, aber interessant ist ja wie gesagt, dass in Deiner Beispieldatei alles perfekt funktioniert (in LibreOffice).
Ich bin gerade am verzweifeln.
Beste Grüße
Stefan
Hallo Stefan,
deine Vorgehensweise ist grundsätzlich schon richtig: alles in kleine Elemente aufteilen und dann suchen wo der Fehler steckt. Aber da ich LibreOffice nicht nutze, kann ich dir hier leider nicht weiterhelfen. Vielleicht findet sich noch ein anderer Leser, der damit etwas anfangen kann.
Tut mir leid,
Martin
Hallo Martin,
ich habe mittlerweile den Fehler gefunden und konnte das Problem (wenn auch etwas umständlich) lösen.
Das Problem bestand darin, dass LibreOffice beim Suffix …ZEILE(A1) leider nicht automatisch hochzählt, wenn ich die Array-Formel auf die Zellen darunter in der Spalte kopiere. Es bleibt überall bei A1 ;o(
Ich habs dann über einen Umweg probiert: Erstmal aus normale Formel auf die ganze Spalte kopieren und dann Zelle für Zelle in eine Array-Formel umwandeln. Das mochte LibreOffice aber auch nicht. Es hat sich schlicht geweigert die Formel in eine Array-Formel umzuwandeln (ohne Fehlermeldung).
Also bin ich dann zu meiner letztendlichen Lösung gekommen: tatsächlich die Formel Zelle für Zelle einzufügen und den Zeilenbezug per Hand zu ändern … und das ganze natürlich als Array-Formel anzuschließen.
Das war alles etwas zeitaufwendig und sicher nicht im Sinne des Erfinders, aber das Ergebnis zählt.
MEINE LISTE FUNKTIONIERT!!!!! 😀
Vielen Dank nochmal an der Stelle für das geniale Tutorial. Array-Funktionen sich tatsächlich noch totales Neuland für mich … ich dachte eigentlich ich kenne mich aus, aber man lernt eben nie aus.
Beste Grüße
Stefan
Hallo Stefan,
das freut mich sehr, dass du das Problem doch noch lösen konntest. Und am Ende zählt doch vor allem, dass es funktioniert 🙂
Schöne Grüße,
Martin
Hallo und vielen Dank für die vielen Hilfestellungen auf dieser Seite!
Ich habe das Problem, dass es bei mir keine Zahlen sondern nur Text gibt – dadurch funktioniert KGRÖSSTE nicht. Gibt es eine Möglichkeit das anders zu lösen?
Viele Grüße!
Hallo Joachim,
KGRÖSSTE ist hier überhaupt kein Problem. Das Ganze funktioniert auch mit Text. In meinem Beispiel sind die entscheidenden Formeln auch nur auf die Textspalten A und B bezogen. Die Einwohner sind nur schmückendes Beiwerk. Muss also klappen!
Schöne Grüße,
Martin
Hallo Martin.
Super erklärt und beschrieben, danke vielmals! Ohne die gesamte Formel nachzuvollziehen versteht man das Ganze nicht.
Das Nachvollziehen hat mir aber geholfen, meinen „dynamischen Dropdown“ schlussendlich ohne „INDEX“ – jedoch mit „INDIREKT“ – via Hilfsspalten (welche Du zum Nachvollziehen ja aufzeigst) zu generieren. Hammer! Wenn man’s weiss, ist’s einfach 😉
Super, dass es Spezialisten wie Dich gibt, welche ihr Wissen (auf gut nachvollziehbare Weise) weitergeben.
Mit den besten und mit dankbaren Grüssen aus der Schweiz 😀
Hallo Marc,
vielen Dank für das schöne Feedback! Die Formel ist wirklich nicht trivial und es freut mich immer, wenn sich die Leser wirklich reinfuchsen. Und wenn’s einfach wäre, könnte es ja jeder 🙂
Schöne Grüße in die Schweiz,
Martin
Hallo Herr Weiß
Mit großem Interesse habe ich hier den Beitrag gelesen.
Ich bin schon seit Tagen daran so eine Datei zu erstellen, in meinem Fall (Materiallager) suche ich auch nach einer Möglichkeit
zu einer bestimmten Teilenummer mir alle Lagerplätze anzeigen zu lassen (es sind fast 10000 Teile oder Positionen)
Ich werde mich am Wochenende noch mal damit beschäftigen.
Wenn Probleme auftreten oder ich garnicht weiter komme darf ich mich noch mal bei Ihnen melden?
MfG Jochen Bachmann
Hallo Herr Bachmann,
klar, fragen kostet erst mal nichts 🙂
Schöne Grüße,
Martin
Hallo Herr Weiß
kann ich Ihnen die Datei mal senden ?
Jochen
Hallo Herr Bachmann,
ist ist so, dass ich konkrete Fragen hier im Rahmen eines Blogartikels in den Kommentaren beantworte – im Rahmen des Möglichen. Es sollte allerdings schon ein Bezug zum jeweiligen Artikel bestehen. Wenn Sie eine individuelle Lösung brauchen, dürfen Sie mir natürlich Ihre Datei mit den Anforderungen/Fragen auch zusenden (info@tabellenexperte.de) und ich werde Ihnen dann gerne ein Angebot unterbreiten. Aber ich bitte um Verständnis, dass ich keinen generellen kostenlosen Excel-Support anbieten kann.
Schöne Grüße,
Martin
Hallo Martin, ich habe deine interessanten Anmerkungen zu „Besser als SVERWEIS: Alle Werte finden“ gelesen. Mein Problem ist, dass ich keine Zahlen, sondern Wörter (Namen) finden muss. Mit KWERT kann ich da nichts anfangen, oder?
Danke, Thomas
Hallo Thomas,
ich nehme an, du meinst KGRÖSSTE (nicht KWERT). Es funktioniert auf jeden Fall. Auch in meiner Beispielliste suche ich ja nicht nach Zahlen, sondern nach den Städten zu einem Land. Dass zusätzlich die Einwohner ausgegeben werden, ist dabei ja nur ein Nebeneffekt.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für deine Formel und die ausführliche Erläuterung dazu.
Ich habe die Formel vor ca. 1,5 Jahren bereits in einem Sheet erfolgreich umgesetzt. Nun versuche ich ein weiteres Problem zu lösen und bekomme #NV-Meldungen – obwohl 48 Werte in der Tabelle sein müssten.
Ich ziehe mir dabei die Daten aus einem anderen Tabellenblatt – macht das einen Unterschied?
Die Überschriften stehen bei mir in der 3. Zeile – meine Formel sieht folgendermaßen aus:
=INDEX(Auswertung!$B$3:$B$300;KGRÖSSTE((Auswertung!$C$3:$C$300=Report!$B$2)*(ZEILE(Auswertung!$C$3:$C$300)-2);ZÄHLENWENN(Auswertung!$C$3:$C$300;Report!$B$2)+2-ZEILE(A1)))
Über einen kurzen Kommentar wäre ich sehr dankbar 🙂
Viele Grüße
Peter
Hallo Peter,
es spielt keine Rolle, ob die Daten in einem anderen Blatt stehen. Wenn in Zeile 3 die Überschriften stehen, sollte die Formel so aussehen:
=INDEX(Auswertung!$B$3:$B$300;KGRÖSSTE((Auswertung!$C$3:$C$300=Report!$B$2)*(ZEILE(Auswertung!$C$3:$C$300)-3);ZÄHLENWENN(Auswertung!$C$3:$C$300;Report!$B$2)+3-ZEILE(A3)))
Und natürlich als Matrixformel eingeben, also mit Strg+Alt+Eingabe
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für deine schnelle Rückmeldung.
Leider klappts bislang nicht – wo der Fehler voraussichtlich sitzt wissen wir ja beide 🙂
Ich werde es weiter versuchen – irgendwann wirds schon klappen.
Beste Grüße
Peter
Hallo Martin,
Vielen Dank für diesen hilfreichen Tipp. Das ganze hat mir im Arbeitsalltag sehr geholfen. Statt Städte und Einwohnerzahlen habe ich die Formel auf Mitarbeiter und Arbeitstage angewandt. In diesem Zusammenhang tritt auch mein Folgeproblem auf. Bisher bezieht die Formel die Arbeitstage aus dem Monat Januar, sodass ich die Formel in jedem Folgemonat manuell auf die Folgemonate Februar, März etc. anpassen muss. Ich finde mit meinen begrenzten Excel-Kenntnissen bisher auch keine Lösung. Gibt es denn einen Weg, die Monate dynamisch in die Formel einbauen zu können?
Anbei auch meine Ausgangsformel:
=WENN(ISTFEHLER(INDEX(‚IST-PT‘!$D$3:$D$68;KGRÖSSTE((‚IST-PT‘!$A$3:$A$68=$E$8)*(ZEILE(‚IST-PT‘!$A$3:$A$68)-2);ZÄHLENWENN(‚IST-PT‘!$A$3:$A$68;$E$8)+3-ZEILE(‚IST-PT‘!A3))));““;(INDEX(‚IST-PT‘!$D$3:$D$68;KGRÖSSTE((‚IST-PT‘!$A$3:$A$68=$E$8)*(ZEILE(‚IST-PT‘!$A$3:$A$68)-2);ZÄHLENWENN(‚IST-PT‘!$A$3:$A$68;$E$8)+2-ZEILE(‚IST-PT‘!A2)))))
Der Monat Januar befindet sich im Reiter IST-PT in Spalte D, für Februar müsste ich die Formel manuell auf Spalte E abändern. Ein Dropdown-Feld mit allen Monaten gibt es bereits in einem anderen Reiter unter Spalte C7. Kann das ganze so eingebaut werden, dass sich die Tage eigenständig entsprechend ändern, wenn der Monat im Dropdown-Feld abgeändert wird?
Ich weiß, die Frage ist sehr spezifisch. Vielen Dank für Ihre Aufmerksamkeit und Ihre Hilfe
Viele Grüße
R.
Hallo Rene,
eine mögliche Option wäre ein definierter Name. Sie könnten im Namensmanager (Menü Formeln | Namensmanager) einen Namen „Monat“ vergeben, der auf die jeweilige aktuelle Monatsspalte verweist. Und in Ihren Formeln verwenden Sie satt den Spaltenbezügen eben diesen Namen. Dann müssen Sie nur an einer Stelle im Namensmanager den Bezug ändern, wenn es einen neuen Monat gibt. Aus das ließe sich über dynamische Formeln sicherlich noch vereinfachen (Stichwort BEREICH.VERSCHIEBEN).
Schöne Grüße,
Martin
Hallo Martin!
Super Tipp diese Formel, jedoch habe ich ein wenig Probleme damit.
Mein Formel:
=WENNFEHLER(INDEX(‚Datenbasis 2017‘!$G$2:$G$11914;KGRÖSSTE((‚Datenbasis 2017‘!$A$2:$A$11914=Übersicht_Längen!$H$3)*(ZEILE(‚Datenbasis 2017‘!$A$2:$A$11914)-1);ZÄHLENWENN(‚Datenbasis 2017‘!$A$2:$A$11914;Übersicht_Längen!$H$3)+1-ZEILE(‚Datenbasis 2017‘!A2)));““)
Mir wird jeweils der erste Wert, der gefunden wird, nicht angezeigt. Also in deinem Beispiel „Sao Paulo“.
Alle weiteren werden problemlos gefunden und angezeigt.
Hast du dazu eine Idee, welchen Fehler ich bei der Abänderung der Formel gemacht haben könnte?
Herzlichen Dank im Voraus!
Beste Grüße,
Christoph
Hallo Christoph,
ich tippe mal auf das „A2“ im letzten Teil der Formel. Es sollte vermutlich ZEILE(‚Datenbasis 2017‘!A1) heißen.
Schöne Grüße,
Martin
Hallo Martin!
Absolut richtig! Jetzt läuft es wie geschmiert!
Danke!
mfg. Christoph
Hallo Martin,
ich suche etwas ähnliches, aber bei mir gibt es – glaube ich- einen wesentlichen Unterschried.
In einem dienstplanprogramm versuche ich eine Kundenaufstellung zu erstellen, in dem teilweise mehrere Mtiarbeiter am gleichen Tag eingesetzt sind. Für den Kunden sollen alle eingesetzten Mitarbeiter inkl. Anfangs- und endstunden zu sehen sein.
Ich schaffe es mit einer selbst gebastelten SVerweis, in der Matrix den ersten gefundenen Mitarbeiter von Links aufzuzeigen, alle anderen rechts davon werden natürlich nicht gefunden, weil die Suche beim ersten Treffer aufhört. So kann ich für den einen Tag nur 1 Mitarbeiter aufführen.
Meine tabelle, auf die sich diese Kundenaufstellung bezieht ist folgendermassen aufgebaut (könnte ich Dir gerne auch mal senden, was vielleicht besser wäre) :
ganz links ist die Spalte mit Datum untereinander
Dann kommen nebeneinander je 3 Spalten pro Mitarbeiter In der ersten Zelle dieser Tabelle steht immer der Mitarbeiter-Name. In den Zellen trage ich dann ( Einsatzort, anfangszeit, Endzeit ) ein.
kannst du mir hier helfen?
Hallo Tarkan,
ich kann mir aufgrund deiner Beschreibung schon ungefähr vorstellen, wie die Tabelle aussieht. Aber für die Art von Auswertung, die du hier erreichen möchtest, wird es mit einer Formellösung sehr schwierig bis unmöglich. Dieses Problem ist so speziell, dass ich hier auch keinen einfachen Tipp geben kann. Vermutlich müsste man diese Tabelle per Power Query in ein besser auswertbares Format bringen und dann mit der neuen Tabelle weiterarbeiten.
Wenn du möchtest, kannst du mir die Datei an info@tabellenexperte.de schicken. Dann würde ich den Aufwand schätzen und dir dann ein Angebot machen. Mehr kann ich dir hier leider nicht anbieten.
Schöne Grüße,
Martin