Microsoft Excel

Herbers Excel/VBA-Archiv

XVERWEIS / SVERWEIS / SUCHEN

Betrifft: XVERWEIS / SVERWEIS / SUCHEN von: Ella
Geschrieben am: 29.09.2020 13:14:36

Hallo Zusammen,

ich bräuchte mal wieder Hilfe. Ich würde gerne per XVERWEIS zwei Infos verknüpfen. Allerdings sind die Informationen Suchkrieterium und Suchmatrix nicht exakt identisch.

Siehe Datei für meine Überlegungen: https://www.herber.de/bbs/user/140514.xlsx

Kann jemand helfen?

Vielen Dank schonmal und LG

Betrifft: AW: z.B. mit AGGREGAT() von SUCHEN() ...
von: neopa C
Geschrieben am: 29.09.2020 13:49:03

Hallo Ella,

... in B2: =WENNFEHLER(AGGREGAT(15;6;B!B$2:B$25/SUCHEN(B!A$2:A$25;A2)^0;1);"")

und nach unten kopieren.

Gruß Werner
.. , - ...

Betrifft: AW: z.B. mit AGGREGAT() von SUCHEN() ...
von: Ella
Geschrieben am: 29.09.2020 14:00:50

Super, vielen Dank Werner! Ich probiere es gleich mal aus.
Kannst Du mir bei Gelegenheit noch erklären, was geanu hier gemacht wird? Ich würde es gern verstehen.

Betrifft: AW: z.B. mit AGGREGAT() von SUCHEN() ...
von: neopa C
Geschrieben am: 29.09.2020 14:17:15

Hallo Ella,

... mit der Teilformel SUCHEN(B!A$2:A$25;A2) wird nicht A2 in B!A$2:A$25 gesucht sondern die Matrix B!A$2:A$25 sucht in A2 Übereinstimmung. Dessen Ergebnis ist eine Matrix aus Fehlerwerten und evtl. einem Zahlenwert. Im vorliegenden Beispiel immer 1 könnte aber auch mal größer 1 sein. Damit aber der Divisor auch dann eine 1 ergibt potenziere ich dies mit 0.

Da wo der Divisor der Matrix eine 1 ist, ergibt sich für den Quotienten die Zahl aus der Matrix B!B$2:B$25 für alle anderen natürlich auch einen Fehlerwert. Fehlerwerte werden durch das 2. Argument der AGGREGAT()-Funktion in Formel ignoriert, so dass damit der zutreffende Wert gefunden wird, egal ob das 1. Argument der Funktion eine 15 wie verwendet oder eine 14 ist.

Gruß Werner
.. , - ...

Betrifft: AW: z.B. mit AGGREGAT() von SUCHEN() ...
von: Ella
Geschrieben am: 29.09.2020 14:27:17

Wow, mega! Danke dir! Ich werde es Schritt für Schritt nachbauen, um es besser nachvollziehen zu können. Aber es funktioniert auf jeden Fal.

Betrifft: AW: bitteschön, gerne owT
von: neopa C
Geschrieben am: 29.09.2020 14:38:17

Gruß Werner
.. , - ...

Betrifft: 8631 Natural 1 A ist Nr. 1240 - nicht 1239
von: WF
Geschrieben am: 29.09.2020 15:23:35

also:
... AGGREGAT(14;6; ...

WF

Betrifft: man muss nach ZEICHEN(10) suchen
von: WF
Geschrieben am: 29.09.2020 16:17:25

Hi,

folgende Arrayformel in B2:
{=WENNFEHLER(INDEX(B!B:B;VERGLEICH(LINKS(A2;FINDEN(ZEICHEN(10);A2&ZEICHEN(10))-1);TEXT(B!A$1:A$25; "@");0));"") }
runterkopieren

WF

Betrifft: AW: auch das reicht nicht, denn ...
von: neopa C
Geschrieben am: 29.09.2020 16:29:16

Hallo WF,

... hab mir jetzt erst die Daten näher angeschaut als vorhin.

=AGGREGAT(14,6; würde für die vorliegenden Daten mit Leerzeichen ausreichend sein. Aber die zugeordneten Werte könnten ja auch einmal kleiner sein. Auch die Zeile abzufragen reicht nicht, weil die Daten ja auch anders angeordnet sein können.

Der Zeilenumbruch allein reicht nicht, weil ja in A19:A20 teilweise nur Trennung durch Leerzeichen vorliegen. Dies könnte man zwar durch WECHSELN(A19;" ";ZEICHEN(010)) abfangen, aber ...

Es gibt dann noch die gleichen Werte in A!A13:A14 mit unterschiedlichen Werten in A!B13:B14

Das kann jetzt erst einmal nur Ella klären.

Gruß Werner
.. , - ...




Betrifft: AW: auch das reicht nicht, denn ...
von: Ella
Geschrieben am: 29.09.2020 17:48:50

Stimmt, das Formelergebnis war nicht immer richtig.

Du hast Recht, Werner. Im Beispieldokument ist es tatsächlich falsch. Wenn ein Wert in Spalte A mehrfach vorkommt (identische Werte), dann wird er auch immer die gleiche NR (Spalte B / Blatt B) haben. Und ja, es kommt per Leerzeichen oder Umbruch getrennt sind.

Vielen Dank für Eure Mithilfe :)

LG Ella

Betrifft: AW: auch das reicht nicht, denn ...
von: Daniel
Geschrieben am: 29.09.2020 18:03:49

wobei man beachten sollte, dass wenn der Wert am Ende steht, in der Regel kein Umbruch oder Leerzeichen folgt, was dann u.U. zu Problemen führt oder man muss diese innerhalb der Funktion hinzufügen.

der andere Weg ist dass man über die Sortierung der Liste abgestimmt mit der verwendeten Aggregat-Funktion (14 oder 15) dafür sorgt, dass der richitige Wert ausgegeben wird.

Gruß Daniel

Betrifft: AW: hierzu noch ergänzt ...
von: neopa C
Geschrieben am: 30.09.2020 14:21:58

Hallo Ella,

... aus Deiner Beispieldatei ist ersichtlich, dass Du scheinbar immer nur den ersten Wert einer Doppelwertzelle in A!A:A auswerten willst/musst.

Für diesen Fall würde ich dann folgende Formel vorschlagen:

=WENNFEHLER(INDEX(B!B:B;VERGLEICH(LINKS(A2;SUCHEN(ZEICHEN(10);WECHSELN(A2;" ";ZEICHEN(10))&ZEICHEN(10))-1);INDEX(B!A$1:A$99&"";);));"")

und nach unten kopieren.

Gruß Werner
.. , - ...

Betrifft: AW: Datenklärung notwendig ...
von: neopa C
Geschrieben am: 29.09.2020 16:36:19

Hallo Ella,

... WF hat einen berechtigten Einwand gebracht, das AGGREGAT(15; ... für Deinen unzureichend ist.
Nachdem ich mir jetzt Deine Daten näher angeschaut habe bin ich auf Deine identischen Werte in A!A13:A14 gestoßen, denen unterschiedliche Werte in A!B13:B14 zugewiesen sind.

Wenn das geklärt ist, müsstest Du noch aussagen, ob Deine Daten in A!A2:B25 nach Spalte A aufwärts sortiert werden können. In Deiner XL-Version (offensichtlich hast Du Office365) gibt es zwar SORTIEREN(), die steht mir aber in XL2016 nicht zur Verfügung.

Gruß Werner
.. , - ...