Getagged: WENN

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

Excel – Elemente einer sortierten Liste ausgeben – WENN und INDIREKT

Das folgende Bild sagt eigentlich fast alles…

441_a

Abhängig von der Auswahl in Zelle D2 soll das entsprechende Team aus den Spalten A:B aufgelistet werden. Als Hilfsberechnungen zähle ich in Zelle F2, wie oft es den Eintrag gibt und in Zelle E2 wird ermittelt, in welcher Zelle zum ersten mal das in D2 ausgewählte Team auftritt.

Nun muss also der Bereich von Zeile E2 an eine Höhe von F2 Zellen haben. Im Beispiel läuft der Bereich von Zeile 2 bis Zeile 7, weil sich dort die Werte von Team 1 befinden.

=AGGREGAT(15;6;ZEILE(A:A)/(A:A=D2);1)
ermittelt dabei, welches die kleinste Zeilenzahl ist, in der zum ersten mal der in D2 eingestellte Wert steht.

Und nun die Herausforderung mit der Funktion INDIREKT. Mit INDIREKT können Zell- oder Bereichsbezüge konstruiert werden. Zum Beispiel in der Form =INDIREKT(„A“&ANZAHL2(A:A)).

Die Formel =WENN($F$2+4<ZEILE();"";INDIREKT("B"&($E$2+ZEILE()-5))) steht im Bereich E5:E18.

=WENN($F$2+4<ZEILE();"";... sorgt dafür, dass tatsächlich nur so viele Werte übernommen werden, wie in Zelle F2 ermittelt werden. Nämlich im Bereich der Zellen 5 bis 10 (hier 6 Werte). Alle übrigen Zellen sollen mit „“ leer bleiben.

Und nun wird mit ...;INDIREKT("B"&($E$2+ZEILE()-5))) dynamisch auf den Bereich verwiesen, der sich ab dem ersten Treffer aus E2 weiter nach unten befindet. Da der Bereich der Einträge hier erst in Zeile 5 startet, wird mit Zeile()-5 zunächst nichts hinzu addiert oder abgezogen. Bei Zeile 6 ergibt ZEILE() dann eine 6 und es wird 1 addiert usw.

Hier ist die Übungsdatei
Excel_441_Namen_auflisten_INDIREKT

Hier geht es zum Video

Excel – Formel mit WAHL und VERWEIS auswählen

Je nach Zahlenlage können unterschiedliche Formeln greifen. Diese lassen sich zum Beispiel über eine verschachtelte WENN-Funktion abgreifen, können aber auch aus eine Kombination von WAHL und VERWEIS bestimmt werden.

Folgendes konstruiertes Szenario:

Eine Behörde möchte einen Zuschuss abhängig der Kinderzahl der Beschäftigten auszahlen.
– ohne Kinder gibt es pauschal 40 Euro
– bei einem bis zwei Kindern gibt es pro Kind 150 Euro
– bei drei bis sieben Kindern gibt es 140 Euro abzüglich einer Gebühr von 25 Euro
– ab acht Kindern gibt es pauschal 1000 Euro

E394

Die Formeln in Spalte B könnten nun natürlich diese Logik über ein verschachteltes WENN abbilden. Aber wer mich kennt weiß, bei VERWEIS versus WENN gewinnt meist der Verweis. Hier die Formel in B2 zum 4:0 für den VERWEIS …

=WAHL(VERWEIS(A2;{0.1;1.2;3.3;8.4});40;A2*150;A2*140-25;1000)

E394_1

Wie ist die Formel zu lesen? Zunächst ein Blick in die geschweifte Klammer. Dort befinden sich mit einem Punkt getrennte Pärchen. Die Zahlen 0, 1, 3 und 8 sind zu lesen als von Null bis unter 1, von 1 bis unter 3, von 3 bis unter 8 und alles ab 8. Daneben sind die jeweiligen Ergebnisse, die resultieren sollen… hier die Werte 1 bis 4. Es wird also beim VERWEIS der Wert der Zelle A2 geprüft und in die Zahlenreihe 0,1,3,8 einsortiert.
Das Ergebnis wird nun als erster Parameter in die Funktion WAHL eingesetzt. Hier stehen also nun die Werte 1 bis 4 und dahinter stehen mit Semikolon getrennt genau vier unterschiedliche Formeln. WAHL legt nun fest, die wievielte Formel gewählt werden soll.

Hier geht es zum YouTube-Video: