Sverweis mit mehreren Suchkriterien

Forumthread: Sverweis mit mehreren Suchkriterien

Martin

Moin zusammen
Vorweggenommen:
Ich hab mich zu dem Tehma ein bisschen schlau gemacht und auch die Seite:
http://www.excelformeln.de/formeln.html?welcher=30
mit großem interesse gelesen.
Für das was ich vorhabe muss (bzw. möchte) ich die Formel erweitern.
=VERWEIS(2;1/(A1:A99&B1:B99="x"&"y");C:C)
Und zwar brauche ich die Bereiche A1:A99 / B1:B99 und C:C Dynamisch/Variabel.
Ich habe dies mit Sverweisen versucht.
Die 3 in die obrige Formel Eingebauten Sverweise haben auf eine Tabelle zugegriffen in denen die zu verwendenen Bereiche angegeben sind.
Das Vorhaben ist natürlich glorreich in die Hose gegeangen. Weil Excel versucht mit dem Inhalt der Cellen die Formel zu lösen anstelle die Formel mit dem Inhalt der Cellen zu füllen und dann zu lösen.
Mit einer WENN verschachtelung kann ich nicht arbeiten weil 20 WENN Funktionen verkraftet Excel nicht ;) (und ich auch nicht)
Ich hoffe das ist halbwegs verständlich ausgedrückt.
Ich befürchte ja das das wider eine VBA aufgabe wird.
Also im Endefeckt brauche ich eine möglichkeit für Fall 1 bis 20 die Suchbereiche für Suchkriterium A und B und den Ergebnisbereich Individuel anzugeben.
Ich danke im vorraus für eure Hilfe
Gruß
Martin

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Benutzer

AW: Sverweis mit mehreren Suchkriterien

07.03.2011 16:27:43

Josef

Hallo Martin,
geht ziemlich sicher ohne VBA, aber eine aussagekräftige Beispieldatei, vielleicht auch mit einer kleinen Beschreibung, wäre schon hilfreich.

Gruß Sepp

AW: Sverweis mit mehreren Suchkriterien

07.03.2011 16:53:07

Florian

Hallo Martin,
bau Dir einen "Superschlüssel", nach dem Du suchst.
Wenn Du z.B. in Spalte A = "c", Spalte B = "b" hast und nach Spalte A und B zuordnen willst:
Spalte C = Spalte A & "-" & Spalte B
Dann kannst Du SVerweis auf Spalte C machen (mit entsprechenden Anpassungen auch in der Suchmatrix)
Gruss Florian

AW: Sverweis mit mehreren Suchkriterien

08.03.2011 07:54:42

Martin

Moin zusammen
Danke Florian und Sepp für die schnelle Antwort.
Ich versuch das mal mit dem Beispiel zu verdeutlichen.
Ich habe eine Ziemlich große Tabelle mit Messwerten aus 20 verschiedenen, na sagen wir mal "Hauptgruppen". Aus dieser Tabelle soll gesucht werden. Und zwar abhängig von der Hauptgruppe im richtigen Bereich und dort nach zwei Suchkriterien.
Die Suchbereiche sind in einer Tabelleverzeichnet:
A B C D
F1 Messwerte!A6:A100 Messwerte!B6:B100 Messwerte!C:C
F2 Messwerte!F6:F100 Messwerte!G6:G100 Messwerte!H:H
F3 Messwerte!K6:K100 Messwerte!L6:L100 Messwerte!M:M
F4 Messwerte!P6:P100 Messwerte!Q6:Q100 Messwerte!R:R
F5 Messwerte!U6:U100 Messwerte!V6:V100 Messwerte!W:W
F6 Messwerte!Z6:Z100 Messwerte!AA6:AA100 Messwerte!AB:AB
F7 Messwerte!AE6:AE100 Messwerte!AF6:AF100 Messwerte!AG:AG
F8 Messwerte!AJ6:AJ100 Messwerte!AK6:AK100 Messwerte!AL:AL
F9 Messwerte!AO6:AO100 Messwerte!AP6:AP100 Messwerte!AQ:AQ
F10 Messwerte!AT6:AT100 Messwerte!AU6:AU100 Messwerte!AV:AV
.
.
.
Nun habe ich die Formel =VERWEIS(2;1/(A1:A99&B1:B99="x"&"y");C:C) so aufgestelt.
=VERWEIS(2;1/(SVERWEIS(A1:D20;"Cellbezug";2)&SVERWEIS(A1:D20;"Cellbezug";3) ="Suchkriterium1"&"Suchkriterium2");SVERWEIS(A1:D20;"Cellbezug";4))
Das funktioniert leider nicht, ich weiß auch warum, aber ich weiß nicht wie ich das gelöst bekomme.
Gruß
Martin

mit INDIREKT

08.03.2011 09:09:38

Erich

Hi Martin,
deiomne Tabelle habe ioch in Spalte D etwas geändert:
Hier steht jetzt Messwerte!C6:C100 statt Messwerte!C:C.
(Sonst würde nachfolgende Formel bei Treffer in Zeile 1 Messwerte!C1 statt Messwerte!C6 ausgeben.
Das kann natürlich auch richtig/gewollt sein - kommt auf deine Daten an.)

	A	B	C	D	E
1	F1	Messwerte!A6:A100	Messwerte!B6:B100	Messwerte!C6:C100	6003

Formeln der Tabelle

Zelle	Formel
E1	=VERWEIS(2;1/(INDIREKT(SVERWEIS("F1";A1:D10;2;0)) &INDIREKT(SVERWEIS("F1";A1:D10;3;0))="a6"&"b6"); INDIREKT(SVERWEIS("F1";A1:D10;4;0)))

Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

AW: mit INDIREKT

08.03.2011 15:00:48

Martin

Moin Erich
Diese "Indirekt" Funktion ist Gold wert.
Ich muste leider die ganze Mappe umschreiben weil Pro Hauptgruppe ein Messwert hinzugekommen ist was sämtliche Zellbezüge verändert hat. Deswegen die Späte antwort.
Auch die Änderung im Ergebnisvektor ist warscheinlich richtig.
Hier mal die Formel im jetzigen Zustand:
=VERWEIS(2;1/(INDIREKT(SVERWEIS($A$12;Zusammenfassung!$A$8:$V$25;17;FALSCH))&INDIREKT(SVERWEIS($A$12; Zusammenfassung!$A$8:$V$25;18;FALSCH))=$A$16&$C$16);INDIREKT(SVERWEIS($A$12; Zusammenfassung!$A$8:$V$25;19;FALSCH)))
Auf alle fälle hat mich das weitergebracht. Die Lösung ist es leider nicht. Als Fehler wir #NV ausgegeben. Daraus schließe ich das die Formel selbst Funktioniert nur das Bezüge nicht funktionieren. Da die Tabellen für Excel alle samt ungeordnet sein müsten habe ich die SVERWEISe mit ;Falsch ergenzt. Daraufhin bleibt die Fehlermeldung bei #NV.
Wie "Verweis" auf eine ungeordnete Tabelle reagiert weiß ich nicht.
Anmerkung:
Bei den Suchkritereien handelt es sich um Variabeln aus Zellen Da du "a6" und "b6" schreibst habe ich dafür natürlich die Zellen angeben (ohne Anführungsstriche ;) ).
Weiterhin handelt es sich bein "a6" um ein Datum und bei "b6" um eine Buchstaben Zahlen Kombination.
Das Datum ist in allen Zellen in DD,MM,YY Formatiert und die Kombination als Text.
Gruß
Martin
PS: Die nächsten zwei Tage bin ich leider Unterwegs also bitte nicht wundern.

zwei neue Versionen

09.03.2011 13:03:25

Erich

Hi Martin,
schau dir bitte mal diese beiden Mappen an:
https://www.herber.de/bbs/user/73908.xls
https://www.herber.de/bbs/user/73909.xls
Ein Teil deiner Fragen hat sich damit wahrscheinlich schon erledigt.
Für den Rest sehe ich hier wieder nach ;-)
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

AW: zwei neue Versionen

11.03.2011 09:07:31

Martin

Moin Erich
Jo durch die beiden datein ist mir das eine oder andere noch klar geworden.
Nachdem ich ein bisschen getrickst und gefuscht habe, habe ich nun auch den fehler Lokalisiert.
Der Fehler wird durch das erste Suchkriterium verursacht das ein Datum ist.
Bevor dieses Satum als Suchkriterium verwendet wird hat es schon ein bisschen was hinter sich.
Erstmal steht es in der Liste ganz Normal als DD,MM,YY Formatiert.
Dann wird es von einer VBA "Case" Funktion in die Listfillrange einer Combobox geladen.
Die Combobox habe ich hiermit im Vorfeld Formatiert.

Private Sub ComboBox2_Change()
Me.ComboBox2.Value = Format(Me.ComboBox2.Value, "DD.MM.YY")
End Sub

Von da aus wird das Datum in die Linkedcell verfrachtet und erst von dort in die Verweis Formel.
Wenn ich die Formatierungen aufhebe und Excel die ganze Prozedur mit der Excelinternen Datumzahl durchlaufen lassen Funktioniert das, aber damit kann dann kein Normalsterblicher etwas anfangen.
Wenn das Datum in der Messwerte Liste als Datumzahl steht ist mir das egal.
Ich habe die VBA Formatierung wider eingefügt und bekomme somit in der Linkedcell ein Datum das wandel ich ein einer anderen Celle mit DATWERT in eine Datumszahl um und benutze es für den VERWEIS.
Das ganze Funktioniert hat aber noch den Hacken das in der Listfillrange der Combobox jetzt nur Datumzahlen zu finden sind die erst nach der Auswahl zu einem Datum werden.
Wenn es dafür noch eine Lösung gibt wie man das in eine "lesbares" Format ändert bin ich Glücklich.
Gruß
Martin
PS:
Fals es hilft hier noch der VBA Code zu der betrefenden Combobox:

Private Sub ComboBox2_Change()
Me.ComboBox2.Value = Format(Me.ComboBox2.Value, "DD.MM.YY")
End Sub

Private Sub ComboBox2_GotFocus()
Select Case (ComboBox1.Value)
Case Sheets("Zusammenfassung").Range("A8"): ComboBox2.ListFillRange = "Messwerte!B6:B105"
Case Sheets("Zusammenfassung").Range("A9"): ComboBox2.ListFillRange = "Messwerte!H6:H105"
Case Sheets("Zusammenfassung").Range("A10"): ComboBox2.ListFillRange = "Messwerte!N6:N105"
Case Sheets("Zusammenfassung").Range("A11"): ComboBox2.ListFillRange = "Messwerte!T6:T105"
Case Sheets("Zusammenfassung").Range("A12"): ComboBox2.ListFillRange = "Messwerte!Z6:Z105"
Case Sheets("Zusammenfassung").Range("A13"): ComboBox2.ListFillRange = "Messwerte!AF6:AF105" _
Case Sheets("Zusammenfassung").Range("A14"): ComboBox2.ListFillRange = "Messwerte!AL6:AL105" _
Case Sheets("Zusammenfassung").Range("A15"): ComboBox2.ListFillRange = "Messwerte!AR6:AR105" _
Case Sheets("Zusammenfassung").Range("A16"): ComboBox2.ListFillRange = "Messwerte!AX6:AX105" _
Case Sheets("Zusammenfassung").Range("A17"): ComboBox2.ListFillRange = "Messwerte!BD6:BD105" _
Case Sheets("Zusammenfassung").Range("A18"): ComboBox2.ListFillRange = "Messwerte!BJ6:BJ105" _
Case Sheets("Zusammenfassung").Range("A19"): ComboBox2.ListFillRange = "Messwerte!BP6:BP105" _
Case Sheets("Zusammenfassung").Range("A20"): ComboBox2.ListFillRange = "Messwerte!BV6:BV105" _
Case Sheets("Zusammenfassung").Range("A21"): ComboBox2.ListFillRange = "Messwerte!CB6:CB105" _
Case Sheets("Zusammenfassung").Range("A22"): ComboBox2.ListFillRange = "Messwerte!CH6:CH105" _
Case Sheets("Zusammenfassung").Range("A23"): ComboBox2.ListFillRange = "Messwerte!CN6:CN105" _
Case Sheets("Zusammenfassung").Range("A24"): ComboBox2.ListFillRange = "Messwerte!CT6:CT105" _
Case Sheets("Zusammenfassung").Range("A25"): ComboBox2.ListFillRange = "Messwerte!CZ6:CZ105" _
Case Else:       ComboBox2.ListFillRange = ""
End Select
End Sub

Format und Wert unterscheiden

11.03.2011 10:04:32

Erich

Hi Martin,
IMHO musst du hier genau unterschieden zwischen dem Wert, dem Format und der Anzeige.
Ein Datum ist einfach eine Zahl, heute ist 40613.
In einer Zelle kann diese Zahl per Format TTT TT.MM.JJ als Fr 11.03.11 angezeigt werden.
In einer Combobox-Liste werden Texte angezeigt und ausgewählt.
Tut man nichts, würde der Wert einer Zelle mit dem Datum 40613 als Text "40613" in der Combobox stehen,
also keineswegs als Datum formatiert.
Damit in der Combobox "11.03.2011" steht, musst du nicht den Wert der Zelle,
sondern einen daraus abgeleiteten Text anzeigen.
Das hast du als "Die Combobox habe ich hiermit im Vorfeld Formatiert." bezeichnet.
Das ist aber keine Formatierung.
In der Combobox stehen Texte (nur das kann sie). Die Texte sind 5 Zeichen lang, sehen z. B. so aus: "40615".
Mit Format(Me.ComboBox2.Value, "DD.MM.YY") machst du aus dem Text ComboBox2.Value="40613"
einen anderen Text, der aus 8 Zeichen besteht: "11.03.11". Sieht besser aus, ist aber auch kein Datum.
Von der Combobox aus wird kein Datum in die LinkedCell verfrachtet,
sondern der ausgewählte Text. Und - logisch - steht dann in dieser Zelle auch nur ein Text, aber kein Datum.
Damit die Umwandlung (Berechnung) der Combobox-Texte aus den Tabellendaten (Zahlen) und dann
wieder zurück funktioniert, solltest du besser nicht mit ListFillRange und LinkedCell arbeiten.
Besser füllst du die Combobox-Liste in einer Schleife über Add (mit Format(cells(y,x),"DD.MM.YY")
oder mit einem String-Array, in das du die Datumstexte aus den Zahlen der Tabelle berechnet hast
(auch in einer Schleife).
Und LinkedCell solltest du ersetzen durch die Ausgabe in die Zelle beim Ereignis ComboBox_Change o. Ä.
Meist schreibt man hier so etwas wie Cells(y,x)=CLng(ComboBox2.Value) oder Cells(y,x)=CDbl(ComboBox2.Value).
Damit hast du die Texte der ComboBox von den Zahlen (Datumsen) in der Tabelle entkoppelt.
Such mal in der Recherche nach Datum oder Uhrzeit und Combobox. Da dürfte es viele Beispiele geben.
Rückmeldung wäre nett! - Grüße von Erich aus Kamp-Lintfort

AW: Format und Wert unterscheiden

11.03.2011 11:08:45

Martin

Moin Erich
Die Information das eine Combobox alles als Text einliest ist gut.
Da ich mich mit Array und String noch viel weniger auskenne würde ich warscheinlich eine weitere Woche brauchen um das zu begreifen.
Verzei mir Bitte das ich nun etwas Fusche.
In der Messwerte Liste ist das Datum als Format Datum und Anzeige DD.MM.YY vorhanden.
Die Combobox wandelt das in einen Text um und gibt einen Text in der Anzeigeform DD.MM.YYYY aus
Diesen text wandel ich mit =DATWERT(Zellbezug) wider in ein Format "Datum" mit der Anzeige "Fordlaufendezahl"
Und das benutze ich dann als Suchkriterium.
Ist ein Umweg der aber funktioniert.
Ich werde einfach die zelle mit weißer Schrift füllen dann merkt das noch nicht mal einer.
Ich danke dir für die Ausführliche Hilfe
Gruß
Martin

Beliebteste Forumthreads (12 Monate)

Sverweis mit mehreren Suchkriterien

Forumthread: Sverweis mit mehreren Suchkriterien

9
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Sverweis mit mehreren Suchkriterien

Forumthread: Sverweis mit mehreren Suchkriterien

9 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Beliebteste Forumthreads (12 Monate)

9
Beiträge zum Forumthread

Beiträge zu diesem Forumthread