Live-Forum - Die aktuellen Beiträge
Datum
Titel
28.03.2024 21:12:36
28.03.2024 18:31:49
Anzeige
Archiv - Navigation
1788to1792
Aktuelles Verzeichnis
Verzeichnis Index
Übersicht Verzeichnisse
Vorheriger Thread
Rückwärts Blättern
Nächster Thread
Vorwärts blättern
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Inhaltsverzeichnis

"SVERWEIS" mit mehreren Treffern

"SVERWEIS" mit mehreren Treffern
06.11.2020 09:04:11
Kulo
Liebe Excel-Freunde,
ich versuche gerade aus einer Tabelle verschiedene Werte auszulesen. Also einen "Sverweis mit mehreren Treffern".
Die Formel, welche ich mir zusammenrecherchiert habe, tut auch das, was ich möchte:
Zu je einem Datum (immer der erste eines Monats) gibt es mehrere Einträge. Diese werden gefunden und ausgegeben.
Jetzt möchte ich aber die Tabelle mit Daten ergänzen, die nicht auf einen Monatsersten fallen. Der Wert der gesucht werden soll, muss einen Bereich umfassen, der >= dem Suchdatum und Da die Tabelle Daten verschiedener Jahre umfasst, kann ich nicht allein den "Suchmonat" separieren, sondern ich müsste mit zwei Hilfsspalten (Jahr, Monat) einen Sverweis mit zwei Suchkriterien zusammen basteln.
Bei diesem Vorhaben bräuchte ich aber eure Hilfe, da reichen meine "Kenntnisse" bei weitem noch nicht aus.
Ich freue mich, wenn sich mal jemand meine Datei anschauen könnte.
https://www.herber.de/bbs/user/141359.xlsx
Bis dahin
Viele Grüße
Kulo

4
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: "SVERWEIS" mit mehreren Treffern
06.11.2020 09:16:59
Kulo
... oder ich nehme eine Hilfsspalte, in der ich das eingegebene Datum mit =Datum(Jahr(...);Monat(...);1) auf den Monatsersten lege und suche in dieser Spalte.
Aber interessanter wäre eine Lösung ohne Hilfsspalte ...
AW: "SVERWEIS" mit mehreren Treffern
06.11.2020 10:38:18
SF
Hola,
=WENNFEHLER(INDEX(Verrechnungen!D:D;AGGREGAT(15;6;ZEILE(Verrechnungen!$D$3:$D$21) /((Verrechnungen!$B$3:$B$21>=$E$3)*(Verrechnungen!$B$3:$B$21<=MONATSENDE($E$3;0)));ZEILE(A1))); "")
Gruß,
steve1da
AW: "SVERWEIS" mit mehreren Treffern
06.11.2020 13:39:30
Kulo
Hallo Steve,
vielen Dank für die Formel.
Sie hat auf Anhieb funktioniert.
Als ich aber meine "intelligente Tabelle" auf dem Blatt Verrechnungen erweitert habe, hat es die neuen Einträge nicht mit berücksichtigt.
Ich habe die Formel wie folgt geändert:
=WENNFEHLER(INDEX(Tabelle2[Text:];AGGREGAT(15;6;ZEILE(Tabelle2[Text:])-2 /((Tabelle2[Datum:]>=$E$3) *(Tabelle2[Datum:] Scheint in Ordnung zu sein. (Mit dem Teil "ZEILE(Tabelle2[Text:])-2" hab ich etwas experimentieren müssen.)
Ich werde versuchen, die Formel am Wochenende mal "aufzudröseln".
Also vielen Dank nochmal. Alleine hätte ich diese Formel nie herausgefunden.
Ein schönes Wochenende und
viele Grüße
Kulo
Anzeige
AW: bei Nutzung Deiner formatierten Tabelle ...
07.11.2020 09:42:55
neopa
Hallo Kulo,
... und der Annahme, dass Du bei gleichen Namen im vorgegebenen Monat, diese nur einmal gelistet haben möchtest, würde ich AGGREGAT() in Verbindung mit ZÄHLENWENN() und TEXT() nutzen sowie für den Betrag SUMMENPRODUKT() (würde auch mit SUMMEWENNS() funktionieren) einsetzen.
In H22:
=WENNFEHLER(INDEX(Tabelle2[Text:];AGGREGAT(15;6;ZEILE(Tabelle2[Text:])-ZEILE(Tabelle2[#Kopfzeilen])/(TEXT(Tabelle2[Datum:];"M.J")=TEXT(E$3;"M.J"))/(ZÄHLENWENN(H$21:H21;Tabelle2[Text:])=0);1));"")
und in J22:
=SUMMENPRODUKT((Tabelle2[Text:]=H22)*(TEXT(Tabelle2[Datum:];"M.J")=TEXT(E$3;"M.J"));Tabelle2[Betrag:])
Gruß Werner
.. , - ...
Anzeige

309 Forumthreads zu ähnlichen Themen

Anzeige
Anzeige
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige