SVERWEIS kombiniert mit WENN-DANN und UND Funktion

Forumthread: SVERWEIS kombiniert mit WENN-DANN und UND Funktion

30.08.2014 20:47:19

Sabs

Hallo zusammen...
Folgende Aufgabenstellung:
1.Tabellenblatt:
Spalte A: Name: Schreiber
Spalte B: Datum Sportveranstaltung: 21.03.2014
Spalte C: War der Hotelaufenthalt während der Sportveranstaltung?: soll die Auftragsnummer vom 2. Tabellenblatt enthalten sein.
2. Tabellenblatt: - Hotelaufenthalt:
Spalte A: Auftragsnummer: 1
Spalte A: Name: Schreiber
Spalte B: Datum der Ankunft: 20.03.2014
Spalte C: Datum der Abreise: 25.03.2014
ich habe mir schon folgendes überlegt, aber komme zu keinem Ergebnis.
=WENN(UND(B2>=Tabelle2!C2;B2 Bitte um Hilfe!!
Danke
Link: https://www.herber.de/bbs/user/92400.xlsx

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Datum in Intervall mit Bedingung

31.08.2014 00:43:29

Erich
mit VERWEIS() ...

31.08.2014 15:38:29

paneo
AW: mit VERWEIS() ...

01.09.2014 09:58:39

Sabs
eine Erläuterung ...und ...

01.09.2014 16:42:12

neopa

Datum in Intervall mit Bedingung

31.08.2014 00:43:29

Erich

Hi,
meinst du das so?
Tabelle2

	A	B	C	D
1	Nr	Name	Ankunft	Abreise
2	1	Müller	01.04.2014	04.04.2014
3	2	Müller	25.07.2014	27.07.2014
4	3	Muster	21.03.2014	30.03.2014
5	4	Reich	20.05.2014	30.05.2014
6	5	Schreiber	01.03.2014	05.03.2014
7	6	Schreiber	01.01.2014	30.01.2014
8	7	Schuster	18.06.2014	21.06.2014

Tabelle1

	A	B	C
1	Name	Datum	Nr
2	Muster	21.03.2014	3
3	Schuster	21.05.2014	0
4	Schreiber	21.06.2014	0
5	Reich	21.07.2014	0
6	Müller	25.07.2014	2

Formeln der Tabelle

Zelle	Formel
C2	{=MIN(WENN((A2=Tabelle2!B$2:B$8)(Tabelle2!C$2:C$8<=B2)(B2<=Tabelle2!D$2:D$8); Tabelle2!A$2:A$8))}
C3	{=MIN(WENN((A3=Tabelle2!B$2:B$8)(Tabelle2!C$2:C$8<=B3)(B3<=Tabelle2!D$2:D$8); Tabelle2!A$2:A$8))}

Enthält Matrixformel:
Umrandende { } nicht miteingeben,
sondern Formel mit STRG+SHIFT+RETURN abschließen!

Matrix verstehen

Excel Tabellen im Web darstellen >> Excel Jeanie HTML 4
Rückmeldung wäre nett! - Grüße aus Kamp-Lintfort von Erich

mit VERWEIS() ...

31.08.2014 15:38:29

paneo

Hallo Sabs,
... alternativ zu Erichs Vorschlag:
in C2: =WENNFEHLER(VERWEIS(9;1/(Tabelle2!B$1:B$99=A2)/(Tabelle2!C$1:C$99=B2)/(A2>0) ;Tabelle2!A:A);"") Dies ist zwar auch eine Matrixformel, aber diese bedarf keines spez. Eingabeabschlusses.
Gruß Werner
.. , - ...

AW: mit VERWEIS() ...

01.09.2014 09:58:39

Sabs

Hallo Erich und Werner!!
Vielen lieben Dank für eure Antworten - ihr habt meinen Wochnestart vereinfacht :)*
@Erich: Deine Formel funktioniert bei mir nur bei kleiner Datenmenge! Habe über 50.000 Zeilen - leider hängt sich da mein Excel immer wieder auf.
@Werner: Danke für deine Formel, diese funktioniert auch bei größerer Datenmenge (dauert zwar - aber ist okey). Eine bitte noch an dich... könntest du mir die Formel etwas genauer erklären, da ich in Zukunft ev. öfters mit dieser Formel abreiten werde.
zB warum verwendest du diese "9" und die "/" (/= und) ?
Danke
Sabs

eine Erläuterung ...und ...

01.09.2014 16:42:12

neopa

Hallo Sabs,
... zu einer Formel dauert immer ein vielfaches der Zeit, die man braucht (wenn man es kann) eine solche aufzustellen.
Deshalb verweise ich hier nur auf Erläuterungen, die ich zu einer ähnlichen Formel hier
http://www.online-excel.de/fom/fo_read.php?f=1&bzh=78232&h=78168#a123x mal vorgenommen hatte. Im dortigen Thread findest Du hierzu in http://www.online-excel.de/fom/fo_read.php?f=1&bzh=78249&h=78168 auch noch eine Ergänzung meinerseits.
Analoges gilt dies für die Formel hier im Thread, nur das da eben 4 mal "geteilt" eingesetzt _ wird. Wobei


(A2>0)

nur dazu dient für nicht vorhandene Daten einen Fehlerwert zu erzeugen, damit auch dort ="" und nicht 0 erzeugt wird.
Überrascht hat mich Deine Aussage, dass die VERWEIS()-Formel schneller als als die {MIN(WENN(...))Formel auswerten soll. Momentan mangelt es mir jedoch absolut an Zeit um den nachzugehen.
Und nun noch nachgefragt. Wo hast Du die 50.000 Datensätze den zu stehen, in Tabelle 1 oder 2 oder in beiden? Je nachdem könnte oder sollte man noch mal nachdenken, ob mal eine schnellere Auswertung generieren könnte.
Gruß Werner
.. , - ...

Infobox / Tutorial

SVERWEIS mit WENN-DANN und UND Funktion effektiv nutzen

Schritt-für-Schritt-Anleitung
Häufige Fehler und Lösungen
Alternative Methoden
Praktische Beispiele
Tipps für Profis
FAQ: Häufige Fragen

Schritt-für-Schritt-Anleitung

Um die Funktionen SVERWEIS und WENN in Excel effektiv zu kombinieren, gehe wie folgt vor:

Daten vorbereiten: Stelle sicher, dass deine Daten in Tabellenblättern gut strukturiert sind. Zum Beispiel:
- Tabelle1: Spalte A (Name), Spalte B (Datum), Spalte C (Auftragsnummer).
- Tabelle2: Spalte A (Auftragsnummer), Spalte B (Name), Spalte C (Ankunft), Spalte D (Abreise).
Formel eingeben: Gehe zur Zelle, in der du das Ergebnis haben möchtest (z.B. C2 in Tabelle1). Gib die folgende Formel ein:
```
=WENN(UND(B2>=Tabelle2!C2;B2<=Tabelle2!D2);SVERWEIS(A2;Tabelle2!A:B;2;FALSCH);"Kein Aufenthalt")
```
Diese Formel prüft, ob das Datum innerhalb des Aufenthalts liegt und gibt den Namen zurück, wenn dies zutrifft.
Formel anpassen: Je nach Bedarf kannst du die Formel anpassen, um weitere Bedingungen hinzuzufügen oder sie in anderen Zellen zu verwenden.

Häufige Fehler und Lösungen

Fehler: #NV: Dieser Fehler tritt auf, wenn SVERWEIS keinen passenden Wert findet. Überprüfe die Suchkriterien in der Formel.

Lösung: WENNFEHLER verwenden: Um diesen Fehler zu vermeiden, kannst du die Formel anpassen:

=WENNFEHLER(WENN(UND(B2>=Tabelle2!C2;B2<=Tabelle2!D2);SVERWEIS(A2;Tabelle2!A:B;2;FALSCH);"Kein Aufenthalt");"Nicht gefunden")

Fehler: Zellenformatierung: Wenn die Daten in unterschiedlichen Formaten (z.B. Datum als Text) vorliegen, kann das zu Problemen führen. Stelle sicher, dass die Datentypen übereinstimmen.

Alternative Methoden

Neben der Verwendung von SVERWEIS gibt es auch andere Funktionen:

VERWEIS-Funktion: Diese kann ebenfalls verwendet werden, um Daten zu suchen. Zum Beispiel:

=WENNFEHLER(VERWEIS(9;1/(Tabelle2!B$1:B$99=A2)/(Tabelle2!C$1:C$99=B2)/(A2>0);Tabelle2!A:A);"")

INDEX und VERGLEICH kombinieren: Eine weitere Möglichkeit, um flexiblere Suchen zu ermöglichen. Beispiel:
```
=INDEX(Tabelle2!A:A;VERGLEICH(1;(Tabelle2!B:B=A2)*(Tabelle2!C:C<=B2)*(Tabelle2!D:D>=B2);0))
```

Praktische Beispiele

Kombination von WENN und SVERWEIS: Angenommen, du möchtest prüfen, ob ein Hotelaufenthalt in einem bestimmten Zeitraum stattgefunden hat. Nutze die oben genannte Formel, um dies zu überprüfen.
SVERWEIS mit mehreren Bedingungen: Wenn du mehrere SVERWEIS-Formeln kombinieren möchtest, kannst du dies ebenfalls tun:
```
=WENN(SVERWEIS(A2;Tabelle2!A:B;2;FALSCH)="Schreiber";"Aufenthalt bestätigt";"Kein Aufenthalt")
```

Tipps für Profis

Verwende WENNFEHLER: Dies hilft dir, deine Formeln robuster zu machen und unerwünschte Fehler zu vermeiden.
Bedingte Formatierung: Nutze bedingte Formatierung, um die Ergebnisse visuell hervorzuheben.
Matrixformeln: Experimentiere mit Matrixformeln, um komplexere Abfragen zu erstellen. Denke daran, diese mit STRG + SHIFT + RETURN abzuschließen.

FAQ: Häufige Fragen

1. Kann man SVERWEIS und WENN-Funktion kombinieren?
Ja, du kannst die SVERWEIS-Funktion in eine WENN-Bedingung einfügen, um bestimmte Kriterien zu prüfen.

2. Wie gehe ich vor, wenn ich mehrere SVERWEIS-Formeln kombinieren möchte?
Du kannst mehrere SVERWEIS-Formeln in einer WENN-Bedingung verschachteln oder die Ergebnisse in einer Zelle addieren.

3. Was tun, wenn mein Excel bei großen Datenmengen abstürzt?
Versuche, die Formeln zu optimieren oder die Daten in kleinere Abschnitte zu unterteilen. Alternativ kannst du auch die INDEX- und VERGLEICH-Funktionen verwenden, die oft schneller sind.

Beliebteste Forumthreads (12 Monate)

SVERWEIS kombiniert mit WENN-DANN und UND Funktion

Forumthread: SVERWEIS kombiniert mit WENN-DANN und UND Funktion

4 Beiträge zum Forumthread Beiträge zu diesem Forumthread

Forumthreads zu verwandten Themen

VBA Beispiele zu diesem und ähnlichen Themen

Infobox / Tutorial

SVERWEIS mit WENN-DANN und UND Funktion effektiv nutzen

Schritt-für-Schritt-Anleitung

Häufige Fehler und Lösungen

Alternative Methoden

Praktische Beispiele

Tipps für Profis

FAQ: Häufige Fragen

Beliebteste Forumthreads (12 Monate)

4
Beiträge zum Forumthread

Beiträge zu diesem Forumthread