Getagged: SVERWEIS

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: