Getagged: SVERWEIS

XVERWEIS statt SVERWEIS, INDEX und VERGLEICH !!!

Das ist der HAMMER!

Endlich ist ein würdiger Nachfolger für die Funktionen SVERWEIS bzw. die Funktionkombination INDEX und VERGLEICH veröffentlicht worden.

Mit dem Funktionsupdate der Office 365 Insider Fast-Version stehen die neuen Funktionen XVERWEIS und XVERGLEICH erst einmal einer sehr kleinen Gruppe von Office-Testnutzern zur Verfügung. In welchem Zeitraum die Funktionen allgemein zugänglich gemacht werden, ist noch nicht bekannt.

Die Funktion XVERWEIS benötigt explizit keine Suchspalte, die sich vorne links in der Datenmatrix befindet. Dies war immer eine der Schwachstellen des SVERWEISES, so dass die Datenmatrix immer erst umgebaut werden musste.

Syntax:

=XVERWEIS(Suchkriterium;Suchmatrix;Rückgabematrix;[Vergleichsmodus];[Rückgabemodus])

Suchkriterium: Ist derjenige Wert oder ein nah beim Suchkriterium gelegener Wert, der in der Suchmatrix aufgespürt bzw. identifiziert werden soll. An dieser Stelle kann auch auf einen Datenbereich mit Suchwerten verwiesen werden – es wird dann statt eines Ergebnisses pro Suchkriterium ein Wert in einer Matrix erzeugt (Stichwort dynamische Arrays).

Im Beispiel soll die Rechnungsnummer in Zelle H4 gefunden werden.

XVERWEIS_01

Suchmatrix: Ist die Spalte oder Zeile von Werten, in der das Suchkriterium gefunden werden soll. In der Suchmatrix kann sich der gesuchte Wert auch mehrfach befinden. Die Suchmatrix kann unsortiert, auf- und absteigend sortiert sein. Die Suchmatrix kann sich irgendwo im Datenbereich befinden.

Gesucht wird hier in der Tabelle tabRechnungen in Spalte RECHNUNG.

XVERWEIS_02

Rückgabematrix: Ist ein Bereich aus den Daten, den man bei gefundenem Suchkriterium ausgegeben haben möchte. Dank der dynamischen Arrays kann die Rückgabematrix auch aus mehreren Spalten oder Zeilen bestehen.

Wenn die passende Rechnung gefunden wurde, soll daneben das Zielland aus der Spalte ZIEL wiedergegben werden.

XVERWEIS_03

Vergleichsmodus: Soll der Wert des Suchkriteriums exakt gefunden werden? Oder eventuell ein Wert der etwas größer oder kleiner ist? Oder soll auch mit Platzhaltern wie Sternchen *, Tilde ~ und Fragezeichen ? gesucht werden? Dieses Feld ist optional zu füllen. Standardmäßig wird die exakte Suche gewählt, sollte der Vergleichsmodus frei bleiben.  Als Eingabe wird einer der Werte 0, -1, 1 oder 2 gewählt.

Ich wähle die Zahl 0 für den exakten Treffer nach der Rechnungsnummer und die Zahl eins beim Rückgabemodus für eine komplette Suche in den Daten, die nicht sortiert sein müssen.

 

 

Rückgabemodus: Soll die Liste von Oben nach unten oder unten nach oben durchsucht werden? Liegt eine sortierte Liste vor? Oder ist die Liste unsortiert. Mit 1;-1;2 oder -2 kann man vorab seine Wahl treffen. Wobei ein Sortieren der Liste die Suche schneller machen würde, ein versehentliches Umsortieren aber die Suche eventuell fehlerbehaftet macht. Auch dieser Wert ist optional, per Standard wird oben nach unten durchsucht.

Am besten schaust du ins Video, um dir die Funktionsweise von XVERWEIS anzuschauen.

Ich werde auch am nächsten Excel-Stammtisch in Essen am 18. September 2019 mehr zu dieser neuen Funktion berichten.
Es bleibt aber leider erst einmal abzuwarten, wie groß der Kreis der Personen sein wird, die diese Funktion nutzen können. Bitte also immer noch die alten Lösungen in der Hinterhalt haben 😦 Zumindest stehen mir XVERWEIS und XVERGLEICH auch schon auf meinem Mac zur Verfügung.

Mehr zum Funktionsumfang und den Anwendungsmöglichkeiten erfährst Du hier:

 

Videolink: https://youtu.be/FyDB55xzBUQ

Ich freue mich über Deine Kommentare unterhalb des Videos. Was hälst Du von dieser Funktion? Wäre Dir diese Funktion so wichtig, um auf Office 365 umzusteigen?

Beste Grüße

Andreas

P.S.: Merci an die Kollegen, die meine Amazon-Listen entdeckt haben 😉

https://www.amazon.de/hz/wishlist/ls/VT98VIVR6CX8?&sort=default

https://www.amazon.de/hz/wishlist/ls/2KKWCBR7ZZ96W/ref=nav_wishlist_lists_1?_encoding=UTF8&type=wishlist

Excel-Alternativen zur Funktion WENNS

at_Excel

Die Funktion WENNS steht seit dem aktuellen Office 365-Update in Excel 2016 zur Verfügung. LibreOffice Calc-Nutzer kennen diese Funktion bereits.

Doch wie sollen die Anwender vorgehen, die diese Funktion in Excel noch nicht nutzen können?

Anbei einige Alternativen zu WENNS mit SVERWEIS, VERWEIS, INDEX und VERGLEICH und der verschachtelten WENN-Funktion.

WENNS_Excel.jpg

Natürlich gibt es auch wieder ein Video dazu auf meinem neuen YouTube-Kanal!

Link zum Video https://youtu.be/YKWt08CcuzA

Käufer des USB-Sticks finden die Excel-Datei im Netz unter …\Excel600plus\at_Excel\at0002

Ich freue mich auf euer Feedback
Beste Grüße

Andreas

SVERWEIS und INDIREKT – Dynamischer Zugriff

Ich möchte einen Kunden per Dropdown auswählen und dann passend zum Kunden einen Wert aus einer separaten Kundentabelle auslesen. Die Tabellen heißen tab_Bundesländer und pro Kunde dann entsprechend tab_NRW, tab_BRA, tab_BAY und tab_HES. Der Name des Tabellenblatts ist hier unwichtig.

 

Per Dropdown wird dann ein Kunde ausgewählt. In der Datenüberprüfung wird auf die Bundesländer verwiesen. Ein neuer Eintrag in der Kundenliste wird dann automatisch auch im Dropdown aufgeführt.

 

Anschließend kann lediglich ein Projekt zum Kunden in Spalte C ausgewählt werden.

 

Per SVERWEIS muss ich nun dynamisch auf eine der beliebig vielen Tabellen zugreifen. Ich setze den Tabellennamen immer aus dem Teil „tab_“ und dem Kundennamen zusammen. Erweiterungen innerhalb der Tabellen werden automatisch mit ausgewertet.

thehos_740_3

Ich nutze die Funktion WENNNV, um #NV beim SVERWEIS zu vermeiden (seit Excel 2013) und WENNFEHLER, um generell Fehler abzufangen. Zum Beispiel, wenn die Felder leer sein sollten.

=WENNFEHLER(WENNNV(SVERWEIS($C3;INDIREKT("tab_"&$B3);2;FALSCH);"kein passender Eintrag");"")

Viel Spaß beim Nachbasteln
Andreas

Die Übungsdatei: Excel 740 SVERWEIS und INDIREKT

Hier geht’s zum Video:

Videolink zu YouTube: https://youtu.be/82FalCURBYk

Excel – SVERWEIS und Sway

Hallo,

das Video zum SVERWEIS ist zwar eines meiner ältesten, hier geht es aber über die Möglichkeit, mal schnell etwas mit dem neuen Produkt Sway aufzubereiten.

Link zu Sway – SVERWEIS:
https://sway.com/QM-Oig-MmNLZ1mym

Würde mich freuen, wenn diese Form gefällt. Die Auswahl der Layouts wird noch ausgebaut.

Und hier der linke zum Excel-Klassiker SVERWEIS – einfach und modern:

Videolink: http://youtu.be/DjVVMcaufxk

Excel – SVERWEIS für sortierte Listen – sehr schnell

Wie schnell ist der SVERWEIS, wenn man einen Wert in einer unsortierten bzw. einer sortierten Liste sucht? Beim SVERWEIS Typ FALSCH ist der Unterschied deutlich messbar. Mit der Hilfe einer kleinen WENN-Funktion und SVERWEIS Typ WAHR ist der Unterschied sogar erheblich. Die Messungen wurden mit dem Add-In FastExcel durchgeführt.

Die beiden Listen umfassen jeweils über eine Million Datenzeilen. Die linke Liste ist unsortiert, die rechte wurde sortiert.

533a

Der normale SVERWEIS sucht nun Werte in der ersten Spalte der unsortierten Liste. Da die Werte nicht sortiert sind, muss mit dem Bereich_Verweis-Argument FALSCH gearbeitet werden. Ebenso wird mit INDEX und VERGLEICH (Typ 0) gearbeitet.

533b

Formel für Label aus Tabelle 1:
=SVERWEIS($J3;$A$2:$C$1048576;2;FALSCH)
Rechenzeit: 547,95 ms

Formel aus Label aus Tabelle 2:
=INDEX($A:$C;VERGLEICH($O3;$A:$A;0);2)
Rechenzeit: 543,33 ms

Es resultieren die langsamsten Ergebnisse.
Schon schneller wird der Zugriff auf sortierte Listen.

533c

Formel für Label aus Tabelle 3:
=SVERWEIS($J13;$E$2:$G$1048576;2;FALSCH)
Rechenzeit: 156,30 ms

Formel aus Label aus Tabelle 4:
=INDEX($E:$G;VERGLEICH($O13;$E:$E;0);2)
Rechenzeit: 153,04 ms

Mit einer Prüfung, ob bei SVERWEIS Typ WAHR bei der Suche des Suchkriteriums der gleiche Wert wie das Suchkriterium resultiert, kann dann der SVERWEIS Typ WAHR zur Ermittlung des Wertes aus der Liste gewählt werden. SVERWEIS Typ Bereich_VERWEIS = WAHR ist eine wahre Rakete.

533d

Formel aus Label aus Tabelle 5:
=WENN(SVERWEIS($J23;$E$2:$E$1048576;1)<>$J23;"";SVERWEIS($J23;$E$2:$G$1048576;2))
Rechenzeit: 0,316 ms

Noch schneller erwies sich bei sortierten Listen allerdings die Funktion VERWEIS.

533e

Hier die Formel für Label aus Tabelle 6:
=WENN(VERWEIS($J33;$E$2:$E$1048576)<>$J33;"";VERWEIS($J33;$E$2:$E$1048576;$F$2:$F$1048576))
Rechenzeit: 0,294ms

In dieser Aufstellung können die mit FastExcel gemessenen Geschwindigkeiten verglichen werden.

533x

Und noch ein wenig dynamischer funktioniert alles mit INDEX und VERGLEICH Typ 1:
=WENN(INDEX($E:$E;VERGLEICH($J43;$E:$E;1))<>$J43;"";INDEX($F:$F;VERGLEICH($J43;$E:$E;1)))

Hier gibt es beide Übungsdateien aus dem Video:
http://1drv.ms/1gUnwEV

Hier geht es zum Video:

Videolink: http://youtu.be/lhPizOTt6Fc

Aktuelle Informationen auch auf https://www.facebook.com/AThehos

Excel – mehrere Suchkriterien – mehrere Treffer – AGGREGAT

Mehrere Suchkriterien und mehrere potentielle Treffer? Kein Problem für die Funktion AGGREGAT.

E360

Die Formel in Zelle H7 wird nach unten kopiert:
=WENNFEHLER(INDEX(tab_Afrika[Auftrag];AGGREGAT(15;6;ZEILE(tab_Afrika[Auftrag])/((tab_Afrika[Land]=$I$1)*(tab_Afrika[Kunde]=$I$2)*(tab_Afrika[Verkäufer]=$I$3));ZEILE()-6)-1;1);"")

Der Teile dieser Funktion:
((tab_Afrika[Land]=$I$1)*(tab_Afrika[Kunde]=$I$2)*(tab_Afrika[Verkäufer]=$I$3)) sorgt dafür, dass für jede einzelne Zeile geprüft wird, ob Land, Kund und Verkäufer den eingestellten Kriterien in den Zellen I1 bis I3 entsprechen. Falls alle drei Prüfungen stimmen, kommt überall ein WAHR heraus und somit ein 1*1*1, also eine 1. Stimmt nur eine Prüfung nicht, wir diese mit FALSCH beantwortet, und es kommt z.B. ein 0*1*1, ein 0*1*0 oder ein 1*1*0 heraus. Somit hätten wir einen Divisor von entweder Null oder Eins.

ZEILE()-6 ist wichtig, um die unterschiedlichen passenden Einträge zu finden. Gibt es mehrere Treffer – wird also oben durch 1 dividiert – möchte man den erstkleinsten, zweitkleinsten, drittkleinsten Wert haben. Da die Formel hier in Zeile 7 beginnt, wird mit ZEILE() die Zahl Sieben errechnet und dann einfach immer eine 6 abgezogen. Eine Zeile tiefer steht dann eine 8-6, also eine 2.

ZEILE(tab_Afrika[Auftrag]) ermittelt einfach nur, in welchen Zeilen die Daten der Auftragswerte stehen. Die Daten beginnen in Zeile 2 und laufen bis Zeile 320. Da aber diese Zeilennummern durch entweder 1 oder 0 geteilt werden, bleiben hier nur die Zeilennummer übrig, die den drei Kriterien entsprechen.

AGGREGAT(15;6;Array;k) sorgt dafür, dass der k-Kleinste Wert aus dem Array genommen wird. Da aber durch die Division durch Null viele Fehler erzeugt wurden und diese Zeilennummern nicht mitgezählt werden sollen, sorgt das zweite Argument mit dem Wert 6 dafür, dass wirklich nur der k-kleinste Wert aus den zutreffenden Zeilen gezogen wird.

Die Übungsdatei zum Download:
E360_Zwei_Suchkriterien

Das Video bei YouTube: