Live-Forum - Die aktuellen Beiträge
Datum
Titel
17.10.2025 17:57:35
Zellen-Name aus A1 nach H1 übertragen mit Prä- + Suffix
17.10.2025 11:36:58
verschachteltes Dropdown-Menü
17.10.2025 10:28:49
Call Anweisung
16.10.2025 18:38:17
Zusammenfassung nach Personalnummer
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1368to1372
1368to1372
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

=VERWEIS(2;1/...

Forumthread: =VERWEIS(2;1/...

=VERWEIS(2;1/...
23.07.2014 09:05:39
Ralf
Hallo Formelkünstler,
ich bin eher die Makro-Coder, daher hier mal die Frage:
=VERWEIS(2;1/(C12=$C$2:$C$4795)/($F$2:$F$4795=1);$F$2:$F$4795)
Gibt es zu dieser Art der Datensuche eine alternative?
Bitte mit Erklärung, wie die Syntax sich auswirkt.
Es nenne diese Methode immer doppelter SVERWEIS.
Es werden 2 (oder mehr) übereinstimmende Kriterien gesucht, was mit dem SVERWEIS leider nicht geht.
Der Nachteil des Verweises sind die langen Berechnungszeiten bei großen Matrizen.
Danke!
Gruß
Ralf

Anzeige

11
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
es ist eine MATRIXFormel ... allerdings ...
23.07.2014 09:19:05
der
Hallo Ralf,
... ergibt Deine =VERWEIS(2;1/(C12=$C$2:$C$4795)/($F$2:$F$4795=1);$F$2:$F$4795) für mich keinen Sinn, weil das Ergebnis immer 1 oder #NV ist.
Gruß Werner
.. , - ...

ja richtig, ist auch nur ein Bsp...
23.07.2014 09:25:37
Ralf
... die Ergebnisspalte kann ja irgendwas anderes sein..
Ralf

dann nachgefragt ...
23.07.2014 09:29:56
der
Hallo Ralf,
... geht es Dir um eine Erklärung oder eine Formelalternative oder eine alternative VBA-Lösung. Bei letzterem halte ich mich außen vor.
Gruß Werner
.. , - ...

Anzeige
AW: dann nachgefragt ...
23.07.2014 09:48:46
Ralf
Hallo Werner,
wie die VERWEIS-Formel funktioniert, weiß ich.
Mit geht es um eine Alternative, die schneller aber doch keine Monsterformel ist.
Wenn du mir diese noch erklären könntest, wäre das prima.
Weil ich sowas häufiger mal brauche, wäre eine einfache Syntax von Vorteil.
Ein universelles Makro dafür zu bauen, wäre natürlich auch gut, aber bestimmt recht komplex.
In VBA könnte ich das selbst machen, mit Formeln kann ich nicht so gut um.
Gruß
Ralf

Anzeige
Alternativen sind entweder auch Matrixformeln ...
23.07.2014 09:54:47
der
Hallo Ralf,
... z.B. ähnlich der Gestalt {INDEX(VERGLEICH(...&...))} oder einfache Formeln mit Hilfe von Hilfsspaltenformeln. Letztere sind bis zu einem gewissen Maß viel schneller.
Was meinst Du mit Erklärung, wenn Du doch schreibst: "wie die VERWEIS-Formel funktioniert, weiß ich"?
Gruß Werner
.. , - ...

Anzeige
AW: Alternativen sind entweder auch Matrixformeln ...
23.07.2014 10:20:40
Ralf
Werner, genau diese Matrix-Formeln verursachen bei mir Knoten im Gehirn, dafür die Erklärungen..
VERWEIS ist ja an sich keine Matrix-Formel in Gänze, es verwendet einzelne Matrizen als Einzelbestandteile.. denen kann ich noch folgen gedanklich..
Ralf

AW: Alternativen sind entweder auch Matrixformeln ...
23.07.2014 10:39:28
Daniel
Die Hirn- und Performancefreundliche Alternative ist das Arbeiten mit Hilfsspalten.
Fasse in der Suchmatrix die Spalten zu einem Suchbegriff zusammen (=a1&b1&c1), dann kannst du normal mit dem SVerweis arbeiten.
Zur Performancesteigerung in großen Tabellen wird die Suchmatrix aufsteigend sortiert, so dass man den SVerweis mit 4. Parameter = Wahr verwenden kann.
Gruß Daniel

Anzeige
AW: Alternativen sind entweder auch Matrixformeln ...
23.07.2014 12:13:15
Ralf
Danke Daniel, diese Variante kenne ich..
warum sortieren, wenn man auch = FALSE nehmen kann? wo ist der Unterschied im Ergebnis oder auch Geschwindigkeit?
Ralf

AW: Alternativen sind entweder auch Matrixformeln ...
23.07.2014 12:28:20
Daniel
Nehmen wir an, die Datenmenge sei:
N = 2^x
dann ist die Suchzeit für den SVerweis mit 4. Parameter = 0 (unsortiert) im Durschnitt proportional N/2
für den SVerweis mit 4. Parameter = 1 (sortiert) ist die Suchzeit proportional x
mal ein paar Beispiele:
1000 Zeilen = 2^10 : 500 zu 10 (Faktor 50)
10000 Zeilen = 2^14 : 5000 zu 14 (Faktor 357)
100000 Zeilen = 2^17 : 50000 zu 17 (Faktor 3000)
du kannst den Unterschied auch selbst ausprobieren:
1. nimm dir ein Telefonbuch (noch ein klassisches aus Papier)
2. denke dir einen Namen aus und suche diesen
3. denke dir eine Telefonnummer aus und suche diese
4. Vergleiche die Zeit, die du benötigts.
5. Stelle dir vor, du würdest das nicht mit dem Telefonbuch von KleinKleckersdorf machen, sondern mit dem von Berlin oder Köln.
dann hast du ungefähr eine Vorstellung davon, wie gross die Zeitunterschiede in Relation sind.
Der SVerweis verwendet nämlich die gleichen Suchmethoden wie du es wahrscheinlich machen wirst (wenn du nicht total planlos an die Aufgabe herangehst)
.
Gruß Daniel

Anzeige
zu VERWEIS(#;1/( ) ...)
23.07.2014 11:54:55
der
Hallo Ralf,
... hierzu sieh z.B. mal hier: http://www.online-excel.de/fom/fo_read.php?f=1&bzh=78232&h=78168#a123x
Die Formel ist zwar anders aber es geht Dir offensichtlich nur um eine Prinziperklärung (eine solche dauert schon immer ein Vielfaches an Zeit, als ich sie erstelle und allgemein dann nochmal länger)
Zu einer alternativen Formellösung hatte ich ja bereits allgemeine Hinweis gegeben. Daniel hat es nun konkreter hinterlegt.
Gruß Werner
.. , - ...

Anzeige
;
Anzeige

Infobox / Tutorial

Datensuche in Excel mit VERWEIS und SVERWEIS meistern

Schritt-für-Schritt-Anleitung

Um die Formel =VERWEIS(2;1/(C12=$C$2:$C$4795)/($F$2:$F$4795=1);$F$2:$F$4795) zu verwenden, folge diesen Schritten:

  1. Daten vorbereiten: Stelle sicher, dass Deine Daten in den Zellen C2 bis C4795 und F2 bis F4795 korrekt eingegeben sind.
  2. Formel eingeben: Gehe in die Zelle, in der das Ergebnis angezeigt werden soll, und gebe die Formel ein.
  3. Matrixformel aktivieren: Drücke Strg + Shift + Enter, um die Formel als Matrixformel zu aktivieren. Dies ist wichtig, da die Formel auf mehrere Kriterien basiert.
  4. Ergebnisse überprüfen: Achte darauf, dass die Formel die gewünschten Ergebnisse liefert, wie z.B. den Wert aus Spalte F, der den Kriterien entspricht.

Häufige Fehler und Lösungen

  • Fehler #NV: Wenn die Formel #NV zurückgibt, könnte das bedeuten, dass es keinen passenden Wert für die angegebenen Kriterien gibt. Überprüfe die Werte in den Zellen C12 und F2 bis F4795.
  • Ergebnis 1: Ein Ergebnis von 1 deutet darauf hin, dass die Formel nicht korrekt aufgebaut ist. Stelle sicher, dass die Kriterien genau übereinstimmen.
  • Leistungsprobleme: Bei großen Datenmengen kann die Berechnung lange dauern. Überlege, die Daten zu sortieren oder Hilfsspalten zu verwenden.

Alternative Methoden

  1. SVERWEIS verwenden: Wenn Du nur ein Kriterium benötigst, benutze die Formel =SVERWEIS(Suchkriterium; Suchmatrix; Spaltenindex; FALSCH).
  2. Hilfsspalten: Kombiniere mehrere Spalten in einer Hilfsspalte, z.B. =A1&B1&C1, um mit SVERWEIS zu arbeiten. Dies macht die Suche effizienter, besonders bei großen Datenmengen.
  3. INDEX und VERGLEICH: Eine weitere Möglichkeit ist die Kombination von INDEX und VERGLEICH, z.B. =INDEX(F2:F4795;VERGLEICH(C12&C13;C2:C4795&D2:D4795;0)).

Praktische Beispiele

  • Beispiel 1: Angenommen, Du hast ein Telefonbuch mit Namen in Spalte A und Telefonnummern in Spalte B. Um die Telefonnummer für einen bestimmten Namen zu finden, kannst Du =SVERWEIS("Max Mustermann"; A:B; 2; FALSCH) verwenden.
  • Beispiel 2: Wenn Du nach einer Kombination aus Vorname und Nachname suchst, erstelle eine Hilfsspalte, die beide Namen kombiniert: =A1&B1. Verwende dann die Formel =SVERWEIS("MaxMustermann"; C:D; 2; FALSCH).

Tipps für Profis

  • Daten sortieren: Wenn Du SVERWEIS mit dem vierten Parameter auf Wahr setzt, sortiere Deine Daten aufsteigend, um die Suchgeschwindigkeit erheblich zu steigern.
  • Dynamische Benennung: Verwende benannte Bereiche für Deine Daten, um die Formeln übersichtlicher zu gestalten.
  • Fehlerüberprüfung: Nutze die Funktion WENNFEHLER, um unerwünschte Fehler in Deinen Formeln zu vermeiden, z.B. =WENNFEHLER(SVERWEIS(...); "Nicht gefunden").

FAQ: Häufige Fragen

1. Was ist der Unterschied zwischen VERWEIS und SVERWEIS?
Der VERWEIS kann mit mehreren Kriterien arbeiten, während der SVERWEIS auf ein einzelnes Suchkriterium beschränkt ist.

2. Wie kann ich die Berechnungszeit bei großen Datenmengen reduzieren?
Überlege, Hilfsspalten zu verwenden oder Deine Daten zu sortieren, um die Effizienz der Suche zu erhöhen.

3. Kann ich die Formel auch in älteren Excel-Versionen verwenden?
Ja, die beschriebenen Formeln sind in den meisten Excel-Versionen verfügbar, jedoch können sich einige Funktionen in den neuesten Versionen verbessert haben.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Entdecke mehr
Finde genau, was du suchst

Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

Suche nach den besten Antworten
Unsere beliebtesten Threads

Entdecke unsere meistgeklickten Beiträge in der Google Suche

Top 100 Threads jetzt ansehen
Anzeige