SVERWEIS Werte ausschließen

LaJulie · 2022-01-03T11:44:18+00:00

Hallo zusammen, ich bin neu hier und wünsche allen ein frohes und gesundes neues Jahr! Für das folgende Problem konnte ich leider trotz mehrfacher Suchen und Anläufe noch keine Lösung finden. Ich habe eine Liste, in der Personen je nach Standort entweder ein Prozentwert oder einer von zwei möglichen Texteinträgen zugeordnet ist. Für eine Auswertung suche ich nun einen Weg, je Standort den Prozentwert auszugeben und die Texteinträge zu ignorieren. D.h. ich suche eine Formel, die für ein Suchkriterium in B den ERSTEN Wert aus Spalte C ausgibt, der NICHT "Wert1" oder "Wert2" ist und die anderen Zeilen für dieses Suchkriterium ignoriert. (In Spalte A stehen die Personen). Hier ein vereinfachtes Beispiel mit Wunschergebnis: https://www.herber.de/bbs/user/150105.xlsx Wie so oft ist der reale Anwendungsfall deutlich komplexer, so muss ich z.B. verschiedene Kriterien verketten, um das eindeutige Suchkriterium zu erhalten. Und die Originaldatei wird per Makro aufgeteilt und nach Autofilter in eine Vorlage kopiert, also in viele kleine Dateien zerschnitten, die dann unterschiedlich viele Zeilen/Datensätze enthalten. Die hier gesuchte Auswertung soll nur in diesen Einzeldateien stattfinden, muss also so in ein Arbeitsblatt der Vorlage integriert werden, dass sie nach Einkopierung der Datensätze in einem anderen Arbeitsblatt ohne weitere Anpassung auch mit unterschiedlicher Zeilenzahl funktioniert. Ich komme im Moment aber gar nicht bis zu diesem potentiellen nächsten Problem... Vielen lieben Dank im Voraus für Eure Hilfe! Viele Grüße, LaJulie

Hallo Werner,
vielen Dank, da hast du in der Tat direkt einen Knackpunkt mit gelöst, auf den ich bei der Umsetzung deiner Aggregatformel gerade gestoßen war: ich hatte die Standorte über eine EINDEUTIG-Funktion ermittelt und damit kam die Aggregatformel nicht zurecht (Fehler #ZAHL).
Nach einigen Fehlversuchen habe ich es jetzt geschafft, deine Formeln in meine Originaltabelle und da in ein separates Arbeitsblatt zu übertragen.
Das scheint super zu klappen, danke!!! Hat mir schon sehr weitergeholfen!
(Ob es mit der Makro-Aufteilung-und-Kopier-Aktion klappt, muss ich noch testen, das dauert noch etwas).
Allerdings: wenn ich die Formeln einige Zeilen weiter runterziehe, als ich sie für die Testdaten brauche (da ich ja später unterschiedliche Zeilenzahlen auch im Ergebnis habe), dann erhalte ich in Spalte B ein Ergebnis "0" für die Zeilen, für die es keinen Standort mehr gibt.
Da es sich nicht um einen Fehler handelt, hilft WENNFEHLER nicht weiter. Zudem soll für einen Fehler das Ergebnis "Kein Wert" ausgegeben werden (s. neues Beispiel "Potsdam" in der überarbeiteten Datei https://www.herber.de/bbs/user/150111.xlsx).


=WENNFEHLER((AGGREGAT(15;6;Daten!C$2:C$300/(Daten!B$2:B$300=A5);1));"Kein Wert")

Ich wollte es mit einer Wenn-Dann-Funktion lösen, das klappt aber nur, wenn ich die WENNFEHLER-Klammer um die Aggregatfunktion weglasse (Beispiel-Datei Arbeitsblatt "Budget", Zelle B6):

=WENN((AGGREGAT(15;6;Daten!C$2:C$300/(Daten!B$2:B$300=A6);1))=0;"";(AGGREGAT(15;6;Daten!C$2:C$300/(Daten!B$2:B$300=A6);1)))

Dann fehlt mir aber für Zelle B5 das Ergebnis "Kein Wert" und ich bekomme da einen #ZAHL-Fehler.
Hast du dafür evtl. auch eine Lösung?
Oh Weh, jetzt sehe ich gerade, dass ich in meiner Originaldatei für die Zeilen ohne Standort doch auch einen #ZAHL-Fehler bekomme in Spalte B und nicht "0". Ich frage mich, was in der Beispieldatei anders ist... welches Ergebnis sollte es denn sein?
Viele Grüße,
LaJulie

Hallo Werner,
sorry, da hatte sich in der Zwischenzeit eine Frage erledigt und ich habe vergessen, den Betreff anzupassen.
Mit der neuen Formel klappt jetzt alles gut, das sieht prima aus!
Nun hatte ich ja schon angedeutet, dass ich in der Realität gar nicht nur 1 Kriterium (Standort) habe, sondern mindestens 2 (Einheit und Standort). Ich habe versucht, das in der INDEX-Formel mit VERKETTEN zu lösen, aber komme nicht dahinter ("Es gibt ein Problem mit der Formel").


=WENNFEHLER(INDEX(VERKETTEN(Daten!B:B;", ";Daten!C:C);AGGREGAT(15;6;ZEILE(VERKETTEN(Daten!B$2:B$300;", ";Daten!C$2:C$300))/
(ZÄHLENWENN(A$1:A1;VERKETTEN(Daten!B$2:B$300;", ";Daten!C$2:C$300))=0)/(VERKETTEN(Daten!B$2:B$300;", ";Daten!C$2:C$300)"");1));"")

S. auch in der Datei, vielleicht findest du den Fehler? https://www.herber.de/bbs/user/150133.xlsx
(Sonst würde ich mir wohl in den Daten eine Hilfsspalte bauen.)
Ich habe darunter auch die EINDEUTIG-Formel einmal zur Illustration eingefügt, weiß aber nicht, ob du das mit einer anderen Excel-Version nachvollziehen kannst. Die Aggregat-Funktion müsste ja einen Bezug zu dem dynamischen Array in A16 bekommen und einen entsprechenden Überlauf erzeugen.
Mit A16# hat das leider nicht funktioniert, das war wohl zu einfach gedacht.
Eine andere Formel zum Test (=ZÄHLENWENN(A16#;"*1*")) funktionierte aber.
Allerdings: nicht nur die Aggregat-Funktion, auch die Mittelwertwenn-Funktion in Spalte C müsste mit dem dynamischen Array funktionieren, damit wirklich alles passt.
Ich nehme an, ohne die Excel-Version wirst du mir da nicht helfen können? Aber es klappt ja auch ohne dynamische Arrays und ist vielleicht sicherer, wenn nicht alle Excel-Versionen damit umgehen können (auch wenn ich denke, dass die Empfänger alle die gleiche Version haben wie ich).
Schön wäre, noch das verkettete Suchkriterium in die Index-Formel zu integrieren.
Viele Grüße,
LaJulie

Arbeitsblatt mit dem Namen 'Budget'
	A	B	C
1	Standort	Budget	Verbrauch
2	Einheit 1, München	2,0%	2,3%
3	Einheit 2, Hamburg	1,5%	2,3%
4	Einheit 3, Hamburg	2,0%	2,0%
5	Einheit 4, Berlin	3,0%	2,5%
6	Einheit 1, Potsdam	Kein Wert
7

Zelle	Formel
A2	=WENNFEHLER(INDEX(Daten!B$1:B$300&", "&Daten!C$1:C$300;AGGREGAT(15;6;ZEILE(Daten!C$2:C$300)/(ZÄHLENWENN(A$1:A1;Daten!B$2:B$300&", "&Daten!C$2:C$300)=0)/(Daten!C$2:C$300>"");1));"")
B2	=WENNFEHLER(AGGREGAT(15;6;Daten!D$2:D$300/(Daten!B$2:B$300&", "&Daten!C$2:C$300=A2)/(A2>"");1);WENN(A2="";"";"Kein Wert"))
C2	{=WENNFEHLER(MITTELWERT(WENN(Daten!B$2:B$300&", "&Daten!$C$2:$C$300=Budget!A2;Daten!$E$2:$E$300));"")}

Arbeitsblatt mit dem Namen 'Budget'
	A	B	C
1	Standort	Budget	Verbrauch
2	Einheit 1, München	2,0%	2,3%
3	Einheit 2, Hamburg	1,5%	2,3%
4	Einheit 3, Hamburg	2,0%	2,0%
5	Einheit 4, Berlin	3,0%	2,5%
6	Einheit 1, Potsdam	Kein Wert
7

Zelle	Formel
A2	=WENNFEHLER(INDEX(Daten!B$1:B$300&", "&Daten!C$1:C$300;AGGREGAT(15;6;ZEILE(Daten!C$6:C$300)/(ZÄHLENWENN(A$1:A1;Daten!B$6:B$300&", "&Daten!C$6:C$300)=0)/(Daten!C$6:C$300>"");1));"")
B2	=WENNFEHLER(AGGREGAT(15;6;Daten!D$6:D$300/(Daten!B$6:B$300&", "&Daten!C$6:C$300=A2)/(A2>"");1);WENN(A2="";"";"Kein Wert"))
C2	{=WENNFEHLER(MITTELWERT(WENN(Daten!B$6:B$300&", "&Daten!$C$6:$C$300=Budget!A2;Daten!$E$6:$E$300));"")}

Forumthread: SVERWEIS Werte ausschließen

12
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Infobox / Tutorial