Live-Forum - Die aktuellen Beiträge
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender

Forumthread: Letztes Vorkommen eines Begriffes in Matrix finden

Letztes Vorkommen eines Begriffes in Matrix finden
06.03.2019 11:29:42
Benny
Hallo zusammen,
bei dem folgenden Problem finde ich einfach keine Lösung:
Ich möchte in einer Matrix die genaue Position der letzten Vorkommens eines gesuchten Begriffs ausgeben.
Mit der genauen Postion ist die Zeile und Spalte gemeint, z.B. D4.
Mit dem letzten Vorkommen ist die größte Spalte gemeint, z.B. D.
Folgende Eigenschaften hat die Matrix und lassen mich gleichzeitig verzweifeln:
- Die Zellen enthalten Texte, keine Zahlenwerte
- Die Spaltenanzahl ist recht groß
- Die Suchbegriffe können mehrfach an beliebiger Steller in der Matrix vorkommen, jedoch höchstens einmal pro Spalte
Hier ein Beispiel:
Userbild
Ich hoffe einer von euch hat eine Idee, ich komme mit den Funktionen Vergleichen(), Index(), Verweis, SVerweis, WVerweis() nicht weiter. Am liebsten wäre mir eine Lösung ohne VBA.
Anzeige

5
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: Letztes Vorkommen eines Begriffes in Matrix finden
06.03.2019 11:51:53
Daniel
Hi
beispielsweise mit folgenden beiden Formeln (in H3 ein Zwischenergebnis):
H3: =AGGREGAT(14;6;(SPALTE(A1:E5)*100+ZEILE(A1:E5))/(A1:E5=H2);1)
H4: =ADRESSE(REST(H3;100);GANZZAHL(H3/100);4)
wobei du die 100 in beiden Formeln an die Anzahl der Zeilen des durchsuchten Zellbereichs anpassen musst.
hier solltest du immer die nächste 10er-Potzen verwenden, dh:
bis 99 Zeilen: 100
bis 999 Zeilen: 1000
bis 9999 Zeilen: 10000
usw
das dient dazu, um die Zeilennummer von der Spaltennummer in einem Wert darstellen zu können.
Gruß Daniel
Anzeige
AW: Letztes Vorkommen eines Begriffes in Matrix finden
06.03.2019 12:54:39
Benny
Super, vielen Dank Daniel, da wäre ich alleine nie drauf gekommen!
Könntest du (oder sonst jemand) vielleicht noch ein wenig erläutern, was hier passiert? Ich denke das wäre für mich und andere wirklich etwas zum Lernen.
AW: Letztes Vorkommen eines Begriffes in Matrix finden
06.03.2019 13:07:10
Daniel
die Formel funktioniert bei dir?
ich habe nicht auf die Version geachtet, in Excel 2007 funktioniert Aggregat noch nicht, das gibt es erst ab 2010.
in 2007 müsste man die Formel für H3 verwenden und die Eingabe immer mit STRG+SHIFT+ENTER abschließen:
=Max(Wenn(A1:E5=H2;Spalte(A1:E5)*100+Zeile(A1:E5)))

diese Formel ist vielleicht etwas selbsterklärender als das Aggregat, das Prinzip ist aber das gleiche:
zunächst wird für jede Zelle im Bereich der Gesamtzahlenwert aus Zeilen- und Spaltennummer gebildet, für diejenigen Zellen, in denen der gesuchte Wert steht.
in der MAX-Version geschieht das über die WENN-Funktion. in der Aggregat-Variante dadurch, dass man das Zahlergebnis durch den Wahrheitswert der Prüfung teilt. ein WAHR wird wie 1 gewertet und ein FALSCH wie 0, dh im Fall das der gesuchte Wert nicht in der Zelle steht, bekommst du einen Div/0-Fehler und in der Aggregat-Funktion kannst du angeben dass Fehlerwerte ignoriert werden sollen. (2. Parameter)
im ersten Parameter der Aggregatfunktion legst du fest, dass die KGrößte-Funktion angewendet werden soll, was im Prinzip die MAX-Funktion ist.
Gruß Daniel
Anzeige
letzter Begriff in 2 Schritten
06.03.2019 12:46:50
WF
Hi,
Suchbegriff steht in J1
Die Spaltenzahl soll dominieren (in J2) mit folgender Arrayformel:
{=MAX(WENN(A1:G99=J1;SPALTE(A1:G1)))}
die Adresse ist dann:
=ADRESSE(VERWEIS(2;1/(INDEX(A1:G1;J2):INDEX(A99:G99;J2)"");ZEILE(X1:X99));J2)
Salut WF
Kopierfehler
06.03.2019 12:53:28
WF
die zweite Formel lautet natürlich:
=ADRESSE(VERWEIS(2;1/(INDEX(A1:G1;J2):INDEX(A99:G99;J2)=J1);ZEILE(X1:X99));J2)
WF
Anzeige
;
Anzeige
Anzeige

Infobox / Tutorial

Letztes Vorkommen eines Begriffs in einer Matrix finden


Schritt-für-Schritt-Anleitung

Um das letzte Vorkommen eines Begriffs in einer Excel-Matrix zu finden, kannst Du die folgenden Schritte befolgen. Diese Anleitung gilt für Excel-Versionen ab 2010, da die Funktion AGGREGAT in Excel 2007 nicht verfügbar ist.

  1. Matrix Erstellen: Erstelle Deine Matrix mit den gesuchten Begriffen. Zum Beispiel in den Zellen A1 bis E5.

  2. Suchbegriff Eingeben: Gib den Suchbegriff, dessen letztes Vorkommen Du finden möchtest, in eine Zelle ein, z.B. H2.

  3. Formel für das letzte Vorkommen: Verwende die folgende Formel in Zelle H3, um das letzte Vorkommen zu finden:

    =AGGREGAT(14;6;(SPALTE(A1:E5)*100+ZEILE(A1:E5))/(A1:E5=H2);1)
    • Hinweis: Passe die 100 an die Anzahl der Zeilen in Deiner Matrix an.
  4. Adresse des letzten Vorkommens: Um die Adresse des letzten Vorkommens zu erhalten, nutze die folgende Formel in Zelle H4:

    =ADRESSE(REST(H3;100);GANZZAHL(H3/100);4)

Häufige Fehler und Lösungen

  • Fehler in der Formel: Wenn Du einen #DIV/0!-Fehler bekommst, überprüfe, ob der Suchbegriff in der Matrix vorhanden ist. Das könnte bedeuten, dass der gesuchte Begriff nicht gefunden wurde.

  • Falsche Excel-Version: Wenn Du Excel 2007 verwendest, nutze folgende Array-Formel in H3:

    =MAX(WENN(A1:E5=H2;SPALTE(A1:E5)*100+ZEILE(A1:E5)))

    Diese Formel muss mit STRG+SHIFT+ENTER eingegeben werden.


Alternative Methoden

Falls Du die Verwendung von Formeln vermeiden möchtest, kannst Du auch VBA nutzen, um das letzte Vorkommen eines Begriffs zu finden. Allerdings, da die Anfrage nach einer Lösung ohne VBA war, fokussieren wir uns hier auf die Formellösungen.


Praktische Beispiele

Angenommen, Du hast folgende Matrix in den Zellen A1 bis E5:

A B C D E
Apfel Banane Kirsche Apfel Traube
Kiwi Banane Apfel
Apfel Kirsche
Traube Apfel

Wenn Du "Apfel" in Zelle H2 eingibst, gibt die Formel in H3 den Wert der letzten Position zurück, und H4 zeigt die Adresse (z.B. D4) an.


Tipps für Profis

  • Experimentiere mit der WENN- und MAX-Funktion, um komplexere Suchvorgänge durchzuführen.
  • Verwende absolute Zellreferenzen, wenn Du die Formeln kopieren möchtest, um konsistente Ergebnisse zu erhalten.
  • Halte die Matrix übersichtlich, um Fehler zu vermeiden und die Lesbarkeit der Formeln zu verbessern.

FAQ: Häufige Fragen

1. Welche Excel-Version benötige ich, um die AGGREGAT-Funktion zu nutzen? Die AGGREGAT-Funktion ist ab Excel 2010 verfügbar. In Excel 2007 kannst Du alternative Formeln verwenden.

2. Kann ich die Formeln auch für Zahlen verwenden? Ja, die Formeln können auch für Zahlen verwendet werden, solange der Suchbegriff entsprechend angepasst wird.

3. Was mache ich, wenn mein Suchbegriff mehrere Male in einer Spalte vorkommt? Die Formeln, die hier beschrieben sind, berücksichtigen das letzte Vorkommen pro Spalte. Wenn Du nur das letzte Vorkommen in der gesamten Matrix benötigst, musst Du die Formeln entsprechend anpassen.

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige