Live-Forum - Die aktuellen Beiträge
Datum
Titel
24.04.2024 00:20:14
Bestimmte Spalten als xls exportieren per Makro
23.04.2024 22:08:10
Auflisten in Spalte VBA
23.04.2024 21:33:46
Nur farbige/nicht farbige Zellen markieren
23.04.2024 21:29:56
Bestimmte Zellen auf andere Arbeitsblätter kopieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1860to1864
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
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1860to1864
1860to1864
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

SVERWEIS Werte ausschließen

SVERWEIS Werte ausschließen
03.01.2022 11:44:18
LaJulie
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

12
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
AW: z.B. einfach mit AGGREGAT() ...
03.01.2022 12:04:03
neopa
Hallo LaJulie,
... für Deine Beispieldatei in B17: =AGGREGAT(15;6;C$2:C$13/(B$2:B$13=A17);1) und nach unten kopieren. Bei Bedarf noch mit WENNFEHLER() klammern.
Gruß Werner
.. , - ...
AW: sollen auch die Standorte ermittelt werden ...
03.01.2022 12:45:37
neopa
Hallo LaJulie,
... dann würde ich zu folgender nach unten kopierbarer Formel in A17 raten:
=WENNFEHLER(INDEX(B:B;AGGREGAT(15;6;ZEILE(B$2:B$13)/(ZÄHLENWENN(A$16:A16;B$2:B$13)=0)/(B$2:B$13"");1));"")
Diese ermittelt alle unterschiedlichen Standorte unabhängig von deren Listung in Spalte B2:B###.
Gruß Werner
.. , - ...
Genau richtig erkannt und noch zwei Fragen
03.01.2022 16:12:44
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
Anzeige
AW: bitteschön; gerne ...
03.01.2022 16:26:10
neopa
Hallo LaJulie,
... ich kann momentan nur eine Frage entnehmen und zwar zur Formel in Spalte B.
Die Antwort dazu, erweitere die Formel in B2 wie folgt:
=WENNFEHLER(AGGREGAT(15;6;Daten!C$2:C$300/(Daten!B$2:B$300=A2)/(A2"");1);WENN(A2="";""; "Kein Wert"))
Da Du Excel365 im Einsatz hast (wie ich eben erst festgestellt habe) solltest Du in Spalte A einfacher mit EINDEUTIG() arbeiten können. ich hab die Excelversion nicht.
Gruß Werner
.. , - ...
AW: DANKE und noch ein Problem
04.01.2022 11:20:07
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
Anzeige
AW: bitteschön, ja geht auch mit XL vor XL365 ...
04.01.2022 13:25:51
neopa
Hallo LaJulie,
... aber eben da nur aufwendiger. Nachfolgende Formeln nach unten kopieren.
Arbeitsblatt mit dem Namen 'Budget'
 ABC
1StandortBudgetVerbrauch
2Einheit 1, München2,0%2,3%
3Einheit 2, Hamburg1,5%2,3%
4Einheit 3, Hamburg2,0%2,0%
5Einheit 4, Berlin3,0%2,5%
6Einheit 1, PotsdamKein Wert 
7   

ZelleFormel
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));"")}
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...
Anzeige
AW: bitteschön, ja geht auch mit XL vor XL365 ...
05.01.2022 16:58:42
LaJulie
Hallo Werner,
erst mal Danke für deine Geduld. Die Umsetzung der neuen Formeln in meiner Originaltabelle hat leider nur bedingt geklappt. Das Problem konnte ich immerhin jetzt identifizieren, kann es aber nicht beheben:
Die Formel für A2 funktioniert scheinbar nur, wenn die Datentabelle tatsächlich mit Zeile 1 beginnt? Eine Anpassung der Formel auf Zeile 5 (Spaltenüberschriften) hat partout nicht funktionieren wollen: der 1. Wert "Einheit, Standort" wurden noch richtig ermittelt, dann aber nur der 2. immer wiederholt.
Als ich testhalber die Zeilen 1-4 aus der Datentabelle gelöscht habe, klappte es automatisch, ich hatte die Formel also im Prinzip richtig angepasst.
Sicherheitshalber wollte ich auch noch fragen, ob es richtig ist, dass nur die 3. Formel eine Matrixformel sein soll. Es scheint allerdings am Ergebnis nichts zu ändern, ob ich alle 3 Formeln zu Matrixformeln mache oder nur die 3. oder keine. Erklären kann ich mir das als Matrixlaie natürlich nicht ;-)
Was schon gut funktioniert und auch den Makro-Kopier-Test bestanden hat, ist die Variante mit der Hilfsspalte "Einheit, Standort" in Daten. Das wäre also auf jeden Fall schon eine Lösung, wenn wir hier nicht noch zu lange grübeln wollen!
Viele Grüße,
LaJulie
Anzeige
AW: dazu ...
05.01.2022 19:16:46
neopa
Hallo LaJulie,
... wenn sich die Überschriftszeile der Datentabelle in Zeile 5 befindet und die Daten ab Zeile 6 vorhanden sind, dann ist nur eine dementsprechende minimale Formelanpassung notwendig.
Siehe:
Arbeitsblatt mit dem Namen 'Budget'
 ABC
1StandortBudgetVerbrauch
2Einheit 1, München2,0%2,3%
3Einheit 2, Hamburg1,5%2,3%
4Einheit 3, Hamburg2,0%2,0%
5Einheit 4, Berlin3,0%2,5%
6Einheit 1, PotsdamKein Wert 
7   

ZelleFormel
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));"")}
Achtung, Matrixformel enthalten!
Die geschweiften Klammern{} werden nicht eingegeben.
Verlassen Sie den Zelleneditor mit Strg+Shift + Enter, statt Enter alleine.
Verwendete Systemkomponenten: [Windows (32-bit) NT 6.02] MS Excel 2010
Diese Tabelle wurde mit Tab2Html (v2.6.0) erstellt. ©Gerd alias Bamberg

Gruß Werner
.. , - ...
Anzeige
mit 2 Arrayformeln
03.01.2022 12:17:41
WF
Hi,
in A17:
{=INDEX(B:B;KKLEINSTE(WENN(ISTZAHL(C$1:C$13);ZEILE(X$1:X$13));ZEILE(X1)))}
in B17:
{=INDEX(C:C;KKLEINSTE(WENN(ISTZAHL(C$1:C$13);ZEILE(X$1:X$13));ZEILE(X1)))}
beide runterkopieren
WF
AW: mit 2 Arrayformeln
03.01.2022 16:20:49
LaJulie
Hallo WF,
vielen Dank für deine Hilfe! Leider bekomme ich das nicht umgesetzt, da ich beim Runterkopieren ab Zeile 4, also nachdem alle 3 Beispiel-Standorte einmal gelistet sind, erst eine Wiederholung des Standortes München und danach einen #ZAHL-Fehler bekomme.
Da ich keine Erfahrung mit Arrays habe, ist es wahrscheinlich mein Fehler in der Umsetzung. Ich habe trotzdem erst mal mit dem Lösungsansatz von Werner weitergearbeitet. Der funktioniert scheinbar auch.
Viele Grüße,
LaJulie
Anzeige
Für xl365 prädestiniert
03.01.2022 18:37:01
{Boris}
Hi,
in Deiner Excelversion brauchst Du nur eine einzige Formel (musst nix runterkopieren oder so):
Trag diese Formel mal in G1 ein und sieh was passiert:
=EINDEUTIG(FILTER(B2:C9;ISTZAHL(C2:C9)))
VG, Boris
AW: Für xl365 prädestiniert
04.01.2022 11:44:06
LaJulie
Hallo Boris,
sorry, das sehe ich jetzt erst: inzwischen hat sich die Fragestellung etwas erweitert.
https://www.herber.de/bbs/user/150133.xlsx
Die EINDEUTIG-Formel in A16 hatte ich vorher schon mal verwendet, um die Standorte zu ermitteln, bin aber daran gescheitert, dann die Spalten B (Budgetermittlung) und C (Mittelwertwenn) mit Bezug auf den dynamischen Array hinzubekommen. In den Zeilen 2 bis 5 klappt es dank Werner im Prinzip schon gut, nur mit der Verkettung der Suchkriterien gerade noch nicht.
Falls du es mit dynamischen Arrays hinbekommst, wäre das natürlich super.
Viele Grüße,
LaJulie
Anzeige

Beliebteste Forumthreads (12 Monate)

Anzeige
Jetzt das Excel-Portal bewerten: Deine Stimme zählt!

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige