Live-Forum - Die aktuellen Beiträge
Datum
Titel
24.04.2024 11:32:28
Formel für die letzten 1-2 Zeichen
24.04.2024 11:13:07
Eingezeichnete Standardabweichung im Graphen begrenzen
24.04.2024 11:06:38
WENN; zwischen zwei Daten
24.04.2024 09:53:18
Wartungsliste automatisch aktualisieren
Du findest keine passende Antwort auf Deine Frage?
Stelle Deine Frage in unserem Live-Forum
Anzeige
Archiv - Navigation
1204to1208
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
1204to1208
1204to1208
Aktuelles Verzeichnis
Verzeichnis Index
Verzeichnis Index
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Sverweis mit mehreren Suchkriterien

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

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

Betreff
Benutzer
Anzeige
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
Anzeige
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
Anzeige
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.)
 ABCDE
1F1Messwerte!A6:A100Messwerte!B6:B100Messwerte!C6:C1006003

Formeln der Tabelle
ZelleFormel
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
Anzeige
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.
Anzeige
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

Anzeige
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
Anzeige
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
Anzeige

347 Forumthreads zu ähnlichen Themen

Ausgabe zweier Zellen mit SVERWEIS
Andreas

Hallo
Ich möchte mittels SVERWEIS Werte aus Spalte 2 oder wenn sich in dieser Spalte keine Werte befinden aus Spalte 3 ausgeben...
Kann mir da jemand weiterhelfen?
Gruß
Andreas
14.08.2008 11:46:00
Thread-ID: 1001793
4 Beiträge
Anzeige
Summe mit 2 Bedingungen
TobiasM

Hallo Excel-Experten,
ich würde gerne eine Summe mit zwei Bedingungen berechnen:
Wenn Wert in Spalte H gleich "EUR" und Wert in Spalte I gleich ""2005", dann Summe der entsprechenden Werte in Spalte J.
Könnte mir jmd. bitte einen Hinweis zur Formel geben?
Vielen Dank!!!
Tobi...
28.08.2008 10:11:30
Thread-ID: 1005310
3 Beiträge
Daten sortiert aus anderem Tabellenblatt auslesen
Robert

Hallo,
bräuchte eure Hilfe bitte.
Aus einem Tabellenblatt ("FEHLZEITEN") sollen Daten ausgelesen und in einem anderen Tabellenblatt ("EINSATZPLANUNG") ab Zeile 100 eingefügt und bei Änderungen in "FEHLZEITEN" aktualisiert werden.
Habe da mal eine Beispieldatei zur besseren Veransch...
28.08.2008 11:32:00
Thread-ID: 1005356
3 Beiträge
Anzeige
ZÄHLENWENN mit 2 Bedingungen
oblivion

Hallo, hab mal eine Frage zur Funktion ZÄHLENWENN.
Die Funktion lautet ja allgemein so =ZÄHLENWENN(A1:A15;"=19").
Ich würde aber gerne diese Funktion so erweitern, dass er mir alle Werte zählt, die kleiner als 19 sind, aber größer als 0.
Ich bekomme es nicht hin. Gibt es da eine Lö...
04.09.2008 18:07:33
Thread-ID: 1007473
3 Beiträge
Sverweis und mehrere Dateien durchsuchen
Benjamin

Hallo,
ist es möglich mit sverweis oder einer anderen Funktion mehere gleich aufgebaute Dateien zu durchsuchen und ein bestimmten wert (z.b in der zweiten Spalte) auszugeben. Also keine sheets sondern, dass das Makro die dateien eigenständig öffnet z.b. all dateien in diesem Odner öfnnet un...
26.09.2008 02:00:36
Thread-ID: 1012468
2 Beiträge
Zwei Kriterien für Sverweis
sascha76er

Hallo,
ich habe folgende Liste:
https://www.herber.de/bbs/user/55778.xls
Hier möchte ich gerne anhand von zwei Kriterien einen SVERWEIS erstellen für die Spalte P , die Kriterien sollen aus den Werten der Spalten E und M gebildet werden. Der entsprechende Wert muss im Tabellenblatt...
01.10.2008 16:04:31
Thread-ID: 1013672
3 Beiträge
PIVOTDATENZUORDNEN mit zwei Kriterien
Andi

Hallo Experten,
Ich versuch zurzeit eine Auswertung aus einer Pivot Tabelle zu erstellen, komm jedoch nicht weiter da ich zwei Kriterien in der X-achse und eins in der Y-achse berücksichtigen muss. Das heisst ich kann nicht einfach X-Achse und Y-achse mit der Pivot-tabelle abgleichen, sonde...
14.10.2008 12:16:00
Thread-ID: 1016295
5 Beiträge
SVerweis mit 2 Kriterien über Index??
Bernd

Hallo Freaks,
ich habe es wirklich versucht....und auch ein einfaches Bsp. aufgebaut. Nur die Indexformel funktioniert nicht...
Ich bitte Euch um Hilfe.
https://www.herber.de/bbs/user/56079.xls
Danke, Bernd
17.10.2008 15:26:00
Thread-ID: 1017185
4 Beiträge
Anzeige

Links zu Excel-Dialogen

ComboBox

Beliebteste Forumthreads (12 Monate)

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

Beliebteste Forumthreads (12 Monate)

Anzeige
Anzeige
Anzeige
Archiv - Verwandte Themen
Forumthread
Beiträge
3
3
2
2
5
2
1
2
4
3
6
11
3
5
11
6
2
2
2
5
1
3
3
3
6