Excel: sverweis mit mehreren ergebnissen

Conrad_319e51 · 10. November 2019 um 00:44

Hallo,

ich suche eine Funktion die ähnlich sverweis arbeitet, aber aus einer Tabelle nicht nur eine, sondern alle Entsprechungen zurückgibt.

Es soll also aus der Quellmatrix eine Teilmenge in eine Zielmatrix geschrieben werden.

Beispiel

Quellmatrix (A1:B6)
A1 = 1 B1 = abc
A2 = 2 B2 = def
A3 = 3 B3 = ghi
A4 = 3 B4 = jkl
A5 = 1 B5 = lmn
A6 = 3 B6 = opq

Zielmatrix (D1:smiley:n), soll alle Einträge aus Spalte B enthalten, die in Spalte A = 3 sind, also:
D1 = ghi
D2 = jkl
D3 = opq

Im obigen Beispiel ist die Matrix unsortiert, es wäre aber auch kein Problem, wenn die Funktion eine sortierte Quelle voraussetzt.

Ich könnte mir eine Hilfskonstruktion vorstellen, in der die Quell-Matrix sortiert und nummeriet wird. In Hilfszellen wird die erste und letzte Nummer aus der Quellmatrix abgefragt. Die Zielmatrix wäre dann fixiert und müßte für die maximal mögliche Anzahl von Sätzen sverweis-Formeln enthalten die dann die Nummer abfragen. Aber das wäre sicher keine elegante Lösung …

Vielleicht hat ja jemand eine bessere Idee.
Dank vorab
Conrad

Reinhard_fb8cb7 · 10. November 2019 um 00:44

Hi Conrad,
könnte man deine Anfrage so umformulieren, ich suche eine Möglichkeit, aufgrund der Eingabe eines Wertes (wie soll der eingegeben werden?), daß dieser Wert in Spalte A gesucht wird und dann bei allen Treffern, der Wert der jeweiligen B-Spalte in Spalte D aufgelistet wird?
Dann schau mal bei http://excelformeln.de vorbei, wenn du da nix findest, melde dich noch mal.
Gruß
Reinhard

Conrad_319e51 · 10. November 2019 um 00:44

Danke Reinhard,

super Seite, aber ich fürchte die Lösung, die ich suche gibt’s für Excel nicht - oder man müßte ein VBA-Scipt schreiben, aber damit kenne ich mich gar nicht aus.

Deine Umformulierung ist okay - der Wert steht in einer Zelle außerhalb von Quell und Ziel-Matrix.

Gruß
Conrad

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

Georg_V · 10. November 2019 um 00:44

Hallo Conrad,

kannst Du mal genauer erläutern, warum Du diese Funktion (bzw. Makro) benötigst? Denn das Makro wirst Du bei Reaktion auf Eingaben benötigen, wenn Du nicht mit einer begrenzten Anzahl von Dubletten (bzgl der ID) vorliegen hast. Ebenso wäre eine Mengenangabe sinnvoll, denn eventuell gibt es doch noch Excel-Hausmittel.

MfG Georg V.

Danke Reinhard,

super Seite, aber ich fürchte die Lösung, die ich suche gibt’s
für Excel nicht - oder man müßte ein VBA-Scipt schreiben, aber
damit kenne ich mich gar nicht aus.

Deine Umformulierung ist okay - der Wert steht in einer Zelle
außerhalb von Quell und Ziel-Matrix.

Gruß
Conrad

Reinhard_fb8cb7 · 10. November 2019 um 00:45

super Seite, aber ich fürchte die Lösung, die ich suche gibt’s
für Excel nicht - oder man müßte ein VBA-Scipt schreiben, aber
damit kenne ich mich gar nicht aus.

Hi Conrad,
eine Lösung gibts dort schon:

 A B C D E 
Wert Zahl 3 Wert
abc 1 def
def 3 ghi
ghi 3 lmn
jkl 1 #ZAHL!
lmn 3 
opq 1

Formeln:

D2: {=INDEX(A:A;KKLEINSTE(WENN(B$1:B$1000=C1;ZEILE($1:blush:1000));1))}
D3: {=INDEX(A:A;KKLEINSTE(WENN(B$1:B$1000=C1;ZEILE($1:blush:1000));2))}
D4: {=INDEX(A:A;KKLEINSTE(WENN(B$1:B$1000=C1;ZEILE($1:blush:1000));3))}
D5: {=INDEX(A:A;KKLEINSTE(WENN(B$1:B$1000=C1;ZEILE($1:blush:1000));4))}

Es sind Arrayformeln, also die geschweiften Klammern nicht miteingeben sondern bei der Eingabe zum Abschluß Strg+Shift+Enter benutzen.

Wie man die Fehlermeldung wegbekommt k.A., weiß grad nicht wie man Wenn(Istfehler… in oder um eine Arrayformel einbaut, vielleicht mit einer Hilfsspalte arbeiten.

Gruß
Reinhard

Marion_L_we · 10. November 2019 um 00:46

Hallo Conrad,

die Aufgabe läßt sich auch mit einer PivotTabelle lösen.

Die PivotTabelle erfordert im Quellbereich Überschriften, die habe ich ergänzt. Das sieht dann so aus:

 A B
1 Artikel Farbe
2 1 abc
3 2 def
4 3 ghi
5 3 jkl
6 1 lmn
7 3 opq

Klicke einmal in die Tabelle (oder wenn sich die Zeilenanzahl der Quelldaten ändert und die Abfrage wiederholt erfolgen soll, einen entsprechend größeren Bereich markieren), anschließend
>Daten, &gt:stuck_out_tongue_winking_eye:ivotTable und PivotChartBericht …,
Schritt 1 des Assistenten: >weiter ,
Schritt 2 des Assistenten: - um den (markierten bzw. den tatsächlichen) Quellbereich ist ein Laufrahmen, im Fenster des Assistenten die Adresse des Quellbereiches - evtl. korrigieren, >Weiter
Schritt 3 von 3:
>Layout…, das Feld „Artikel“ auf den Bereich „Zeile“ des Tabellenschemas ziehen, das Feld „Farbe“ in den Bereich „Daten“. Excel wird aufgrund der Einträge in Spalte B die Darstellung in „Anzahl von Farbe“ vorschlagen. Änderung ist möglich durch Doppelklick, hier aber nicht erforderlich, mit >OK das Fenster schließen,
>Optionen (Schritt 3 des Assistenten) klicken, wenn gewünscht „Beim Öffnen aktualisieren“ wählen, evtl. Formatierungen einstellen z. B. "leere Zeillen anzeigen als: "; für den Fall, das ein größerer Datenquellbereich markiert wurde (Optionen können auch später geändert werden), mit OK das Fenster schließen (wieder Schritt 3…),
>„in bestehendem Arbeitsblatt“ wählen und in die Zelle D1 klicken (für dieses Beispiel zur Vereinfachung der Erklärung),
>Fertig stellen klicken

Das Ergebnis sieht dann wie folgt aus:

 A B C D E 
1 Artikel Farbe Anzahl von Farbe 
2 1 abc Artikel Summe 
3 2 def 1 2 
4 3 ghi 2 1 
5 3 jkl 3 3 
6 1 lmn Gesamtergebnis 6 
7 3 opq

In Zelle D3 und den Zellen darunter sind die verschiedenen Einträge der Quelldaten aus Spalte A „Artikel“ aufgeführt, in Spalte E ist die Anzahl der verschiedenen Einträge aus Spalte B „Farbe“ aufgeführt. Mit Doppelklick in die Zelle E5 (der Dateneintrag für Artikel „3“ aus Spalte A) öffnet Excel ein neues Tabellenblatt, dass alle entsprechenden Einträge wie folgt auflistet.

Artikel Farbe
3 opq
3 jkl
3 ghi

Dieser Vorgang kann beliebig oft wiederholt werden, jedesmal öffnet Excel ein neues Blatt mit den Einträgen zu Artikel „3“ (deshalb diese Blätter wieder löschen).

Die Auflistung der Artikel kann auch reduziert werden durch Klick auf den Pfeil in Zelle D2 (Artikel)

Zu beachten ist lediglich, dass eine Veränderung der Daten in Spalte A (Artikel) oder B (Farbe) nicht automatisch die PivotTabelle ändert. Über >Daten, >Daten aktualisieren oder über Klick auf das Ausrufezeichen in der Symbolleiste PivotTable werden die Änderungen übernommen ohne noch einmal die PivotTabelle neu erstellen zu müssen.

Gruß
Marion

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

Conrad_319e51 · 10. November 2019 um 00:47

Danke Reinhard,

aber das ist halt eine statische Lösung. Das Prinzip ist ähnlich der von mir skizzierten Vorgehensweise mit sverweis. Aber trotzdem vielen Dank, so habe ich was neues kenengelernt.

Und funzt übrigens auch wenn mit „istfehler“ in „wenn“ eingeschlossen:
=WENN(ISTFEHLER(INDEX(A:A;KKLEINSTE(WENN(B$1:B$1002=$C$2;ZEILE($1:blush:1002));5)));"";INDEX(A:A;KKLEINSTE(WENN(B$1:B$1002=$C$2;ZEILE($1:blush:1002));5)))

Gruß Conrad

[Bei dieser Antwort wurde das Vollzitat nachträglich automatisiert entfernt]

Conrad_319e51 · 10. November 2019 um 00:47

Hallo George V. (Wer war noch mal George V.? -> http://de.wikipedia.org/wiki/George_V.
Aber hier steht Du bist schon tod … na egal, trotzdem vielen Dank.)

Spaß beiseite:

Also es geht darum einen Entwurf für ein zu erstellendes Programm zu skizzieren. Natürlich muss ich das nicht ausprogrammieren, sondern kann einfach beschreiben, wie ich mir den Ablauf vorstelle und es den Programmierer überlassen wie Sie im späteren Programm das Problem lösen.

Aber mir macht es halt Spaß schon in Excel funktionierende Beispiele zu bringen - außerdem hat das zwei Vorteile:

Man kann selbst schonmal testen ob die theoretische Idee in der Praxis auch noch so gut ist.
Der Programmierer wird dann nicht so schnell behaupten „das geht so nicht“. Welcher Profi möcht schon eingestehen, daß er etwas nicht programmieren kann, was ein Laie in Excel bereits vorgemacht hat.

Im konkreten Fall geht es um einen Katalog mit ca. 200 Einträgen (gefährliche Hobbies und Berufe). Wenn man alle in ein Dropdownfeld packt, wird es unübersichtlich. Die 200 Einträge lassen sich aber in ca. 10 Gruppen von ca. 1 - 50 Einträgen zuordnen.

Ich hatte daher die Idee das ganze in zwei Dropdownfelder aufzuteilen. Im ersten wird aus einer Liste der Gruppen ausgewählt, im zweiten werden dann nur noch die Einträge angezeigt, die zu dieser Gruppe gehören - also max. 50.

Ich habe nun versucht das in Excel vorzuexerzieren.

Dropdown mit Verweis auf eine statische Liste der Gruppen - kein Problem
Dropdown - tja und hier überlege ich nun wie ich die Liste für dieses Dropdown dynamisch aufbauen kann.

In einer Datenbankumgebung mit SQL ist es kein Problem ein Dropdownfeld mit select vorzubelegen - aber wie geht da sin Excel?

Hier ein Auszug aus der Liste:

Gruppe Risiko
Bergsport Bouldern
Kampfsport Bachi-ki-do
Kampfsport Boxen
Kampfsport Burmesisches Boxen
Motorsport Beschleunigungsrennen
Motorsport Bergrennen
Radsport BMX-Fahren
Tauchen bis max. 40m Tiefe mit Pressluftatemgerät
Wintersport Biathlon
Wintersport Bobfahren

Wenn Du natürlich eine ganz andere Idee hast - gern und danke.

Herzlichst
Conrad

Hallo Conrad,

kannst Du mal genauer erläutern, warum Du diese Funktion (bzw.
Makro) benötigst? Denn das Makro wirst Du bei Reaktion auf
Eingaben benötigen, wenn Du nicht mit einer begrenzten Anzahl
von Dubletten (bzgl der ID) vorliegen hast. Ebenso wäre eine
Mengenangabe sinnvoll, denn eventuell gibt es doch noch
Excel-Hausmittel.

MfG Georg V.

Georg_V · 10. November 2019 um 00:47

Also es geht darum einen Entwurf für ein zu erstellendes
Programm zu skizzieren. Natürlich muss ich das nicht
ausprogrammieren, sondern kann einfach beschreiben, wie ich
mir den Ablauf vorstelle und es den Programmierer überlassen
wie Sie im späteren Programm das Problem lösen.

Aber mir macht es halt Spaß schon in Excel funktionierende
Beispiele zu bringen - außerdem hat das zwei Vorteile:

Man kann selbst schonmal testen ob die theoretische Idee in
der Praxis auch noch so gut ist.

Der Programmierer wird dann nicht so schnell behaupten „das
geht so nicht“. Welcher Profi möcht schon eingestehen, daß er
etwas nicht programmieren kann, was ein Laie in Excel bereits
vorgemacht hat.

Im konkreten Fall geht es um einen Katalog mit ca. 200
Einträgen (gefährliche Hobbies und Berufe). Wenn man alle in
ein Dropdownfeld packt, wird es unübersichtlich. Die 200
Einträge lassen sich aber in ca. 10 Gruppen von ca. 1 - 50
Einträgen zuordnen.

Ich hatte daher die Idee das ganze in zwei Dropdownfelder
aufzuteilen. Im ersten wird aus einer Liste der Gruppen
ausgewählt, im zweiten werden dann nur noch die Einträge
angezeigt, die zu dieser Gruppe gehören - also max. 50.

Ich habe nun versucht das in Excel vorzuexerzieren.

Dropdown mit Verweis auf eine statische Liste der Gruppen -
kein Problem

Dropdown - tja und hier überlege ich nun wie ich die Liste
für dieses Dropdown dynamisch aufbauen kann.

In einer Datenbankumgebung mit SQL ist es kein Problem ein
Dropdownfeld mit select vorzubelegen - aber wie geht da sin
Excel?

Hallo Conrad,

ich verstehe Deine Anmerkung auf die Antwort von Reinhard nicht ganz: Die Dropdown-Liste gibt doch einen Wert zurück, den man dann in das Feld C3 eintragen kann. Das Problem ist nur, dass man bei Drop-Downlisten die leeren Elemente nicht so einfach unterdrücken kann. Aber für eine Demo sollte dies doch reichen, oder?

MfG Georg V.

P.S.: Die Lösungsvariante, die mir noch im Kopf verschwirrte, hat sich übrigens erledigt…
P.S.2.: Mit einer Form könnte man das übrigens relativ leicht implementieren, aber mir scheint, Du willst diese auf einem normaler Excel-Blatt implementieren.