Kategorie: Matrixfunktion

SVERWEIS statt verschalteter WENN-Funktion – wirklich WAHR

 

Häufig finde ich eine verschachtelte WENN-Funktion vor, wenn Werte in Listen abgeglichen werden sollen. Es geht nicht um exakte Treffer, sondern ein Wert soll auf die Zugehörigkeit in einem Zahlenbereich abgeprüft werden.

Im folgenden Beispiel unterstützt ein Unternehmen seine Mitarbeiter durch reduzierte Beiträge für ein Fitnesscenter. Statt 80 Euro pro Monat werden z.B. nur EUR 10 für Spitzenverdiener fällig und z.B. EUR 40 für Mitarbeiter, die mehr als 4.200 aber weniger als 4.800 Euro verdienen.

Jetzt kann man mit der WENN-Funktion natürlich jeden Bereich abprüfen.

E806a

=WENN([@Gehalt]<$G$5;$I$4;WENN([@Gehalt]<$G$6;$I$5;WENN([@Gehalt]<$G$7;$I$6;WENN([@Gehalt]<$G$8;$I$7;WENN([@Gehalt]<$G$9;$I$8;WENN([@Gehalt]<$G$10;$I$9;WENN([@Gehalt]<$G$11;$I$10;WENN([@Gehalt]<$G$12;$I$11;WENN([@Gehalt]<$G$13;$I$12;$I$13)))))))))

Alles klar?

Macht wirklich keinen Spaß, oder? Vor allem ist die Formel lang, schwer zu lesen, schwer zu überarbeiten und wirklich nur sehr schwer auf Richtigkeit hin zu überprüfen.

Wesentlich kürzer ist da der SVERWEIS Typ WAHR:

E806b.jpg

=SVERWEIS([@Gehalt];tabBeitrag;3;WAHR)

Der SVERWEIS Typ WAHR prüft, ob ein Wert in der Suchspalte der Matrix eingeordnet werden kann. Immer zu lesen als z.B. „von 0 bis unter 600“, „von 600 bis unter 1.200“ bzw. ganz unten „5.400 und größer“. Dafür müssen die Klassenuntergrenzen zwingend aufsteigend sortiert sein. Und statt Zahlen könnte man auch Texte in Buchstabenbereichen einsortieren lassen.

Bei meinen Geschwindigkeitstests war die verschachtelte WENN-Funktion nur ein wenig langsamer. Alternativ könnten auch die Funktionen VERWEIS bzw. INDEX und VERGLEICH genutzt werden.

Hier die Datei für eigene Übungszwecke:

E806 SVERWEIS Typ WAHR für Bereichssuche

Die Datei befindet sich auch auf dem USB-Stick „Excel700“ im Verzeichnis Excel\E806.

Hier geht es zum Video:

Videolink: https://youtu.be/VJlWdERSam4

Viel Spaß beim Nachbasteln und einen schönen Feiertag
Andreas

Einfacher SVERWEIS – Teil 1

Die Excel-Funktion SVERWEIS (senkrechter Verweis) gehört mit zu den meist genutzten Funktionen in Excel und wird auch in vielen kaufmännischen Lehrgängen behandelt.

Syntax:

=SVERWEIS(Suchkriterium; Matrix; Spaltenindex; Bereich_Verweis)

Beim einfachen SVERWEIS wird geprüft, ob ein Wert (das Suchkriterium) in genau der gesuchten Schreibweise in einer Datenliste (die Matrix) vorkommt. Gesucht wird dabei in Spalte 1 dieser Matrix (dem Suchvektor). Gibt es eine exakte Übereinstimmung (Die exakte Suche muss mit FALSCH definiert werden), kann der Wert einer bestimmten Spalte der Matrix (der Spaltenindex) ausgegeben werden. Alternativ kommt die Meldung #NV.

E797.jpg

Formel in Zelle M9: Das Suchkriterium in Zelle M5 wird in der ersten Spalte (blau) der Matrix B5:J236 gesucht. Da genau das Suchkriterium gefunden werden soll, muss Bereich_Verweis mit FALSCH hinterlegt werden. Das Alter steht dann hier in Spalte 4 der Matrix.

Dieser einfache SVERWEIS hat noch ein paar Schwachstellen:

  • Was passiert, wenn die Spaltensortierung verändert wird?
  • Was passiert, wenn neue Daten unter die Liste kopiert werden?
  • Was passiert, wenn das Suchkriterium nicht gefunden werden kann?
  • Was passiert, wenn das Suchkriterium mehrfach im Suchvektor vorkommt?
  • Was muss man machen, wenn der Suchvektor nicht ganz links in der Matrix steht?
  • Wie muss die Formel umgestellt werden, wenn das Suchkriterium aus zwei oder mehr Zellinhalten erstellt wird?
  • Wie muss die Formel modifiziert werden, wenn es nicht einen, sondern zwei oder mehr Suchvektoren gibt?
  • Was könnte die Fehlerursache dafür sein, wenn z.B. nach der Zahl 10000 gesucht wird, diese zwar im Suchvektor steht aber doch nicht gefunden wird?
  • Was muss man machen, wenn mehrere Treffer möglich sind und diese auch aufgezeigt werden sollen?

Alle diese Punkte werden in den kommenden SVERWEIS-/INDEX-/AGGREGAT-Videos demonstriert.

Hier die Datei zum Download:

E797 SVERWEIS einfach

Hier geht es zum Video:

Videolink: https://youtu.be/qkES78Q6XIw

Viel Spaß beim Nachbasteln
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

Mit XODER Binärzahlen addieren

Mit der Hilfe der Funktionen XODER, SUMMENPRODUKT, TEIL und DEZINBIN kann ich 2 Binärzahlen (hier zweimal 8 Bit) addieren.

728_Thehos_01

Die folgenden Formeln habe ich dafür eingesetzt. Die Funktionen mit geschweiften Klammern (Matrixformeln) müssen mit STRG + SHIFT + ENTER bestätigt werden, also die Klammern nicht manuell eingeben.

728_thehos_02

Hier ist die Übungsdatei: Excel 728 XODER 2

Käufer des USB-Sticks finden die Datei im Ordner E728.

Hier geht es zum YouTube-Video:

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

Excel – Bedingte Formatierung mit SUMMENPRODUKT

Mit Hilfe der Bedingten Formatierung können Zellen hervorgehoben werden, deren eigene Werte oder deren benachbarte Werte bestimmte Eigenschaften erfüllen.

Meist prüft man jedoch nur ab, ob ein Wert lediglich in einem Wertebereich liegt oder einen bestimmten Wert hat. Mit Hilfe der Funktion SUMMENPRODUKT und einem kleinen um 90 Grad versetzten Bereich / Array können jedoch auch mehrere Werte abgeprüft werden.

Insofern wirklich ein Array in der Form {1.4.11.14} genutzt wird, verweigert sich allerdings die Bedingte Formatierung mit dem Hinweis, dass Arraykonstanten nicht genutzt werden dürfen.

Mein Tipp: Die Berechnung einfach in einen Namen auslagern und dann in der Bedingten Formatierung einfach das Ergebnis des Namens überprüfen.

Insofern einer der rot umrandeten Zahlen in Spalte B auftritt und der Name „Bea“ in Spalte A, soll die ganze Zeile markiert werden.

Excel474a

Hat man die Hilfszellen also im Blatt vorliegen, könnte man die folgende Formel in der Bedingten Formatierung nutzen.

=SUMMENPRODUKT(($A2=$H$2)*($B2=$H$1:$K$1))=1

Wichtig sind die $-Zeichen vor A2 und vor B2, damit die Formel sich in jeder Zelle des Bereichs A2:Cxxx jeweils auf die eigenen Werte der Spalten A und B beziehen kann.

Excel474b

Doch hier müssen die Werte zum Vergleich noch in den Zellen H2 und H1:K1 vorgehalten werden.
Klickt man die Zelle A2 an und wechselt auf Formeln – Definierte Namen – Namens-Manager, kann ein Name mit einer Berechnung (z.B. Prüfung) angelegt werden. In diesem Namen lassen sich statt Bezüge auch Konstanten anlegen.

Excel474c

=SUMMENPRODUKT(('SUMMENPRODUKT ARRAY'!$A2="Bea")*('SUMMENPRODUKT ARRAY'!$B2={7011.7100.7400.7700}))

Anschließend muss die Formel in der Bedingten Prüfung auf
=Prüfung=1
geändert werden… Fertig!

Hier ist die Übungsdatei:
Excel 474 Bedingte Formatierung Summenprodukt

Hier geht es zum Video:

Videolink: http://youtu.be/EgcWIyE54TI

Excel – Wechselnde Zeilenformatierung bei wechselnden Inhalten

In einer Liste sollen alle Zeilen mit identischen Inhalten aus Spalte A gleich formatiert werden. Wechselt der Inhalt, wechselt auch automatisch die Farbe. Dann soll so lange die gleiche Farbe pro Zeile genommen werden, wie sich dieser Inhalt in Spalte wiederholt.

Gelöst wird das Problem mit zwei Formeln in der bedingten Formatierung. Bei YouTube wird noch einmal das Problem beschrieben.

467_a

Hier ist die Fragestellung auf YouTube:

Videolink: http://youtu.be/67CIoTslVBY

Von den vielen Lösungsmöglichkeiten möchte ich die geniale Lösung von Andreas Entenmann (Excel MVP) beschreiben.
Für den kompletten Wertebereich A2:Cxxx wird eine Prüfung auf das Ergebnis einer Formel in der bedingten Formatierung hinterlegt.

1. Zunächst wird der Bereich markiert
2. Anschließend START – Formatvorlagen – Bedingte Formatierung – Neue Regel
3. Dann auf Formel zur Ermittlung der zu formatierenden Zelle verwenden klicken
4. Folgende Formel hinterlegen:
=REST(SUMMENPRODUKT(N($A$1:$A1$A$2:$A2));2)=1
5. Eine Formatierung für den Fall hinterlegen, dass bei der Prüfung der Wert WAHR resultiert.
6. Dialog schließen

Den gleichen Vorgang für =REST(SUMMENPRODUKT(N($A$1:$A1$A$2:$A2));2)=0 mit einer anderen Formatierung wiederholen.

467_2

Doch was passiert hier in der Formel?
Es werden die Funktionen N(), REST() und SUMMENPRODUKT() genutzt. Dazu noch durch den Bereichsbezug $A$1:$A1 bzw. $A$2:$A2 ein mit jeder Zeile anwachsender Bereich.

Die Funktion REST(Zahl;2) teilt eine Zahl durch 2. Es wird aber nicht geschaut, wie oft die Zahl 2 in die Zahl passt, sondern welcher Rest nach der Division übrig bleibt. Bei geraden Zahlen resultiert ein Rest von 0, bei ungeraden Zahlen ein Rest von 1. Das Ergebnis von Rest wird in der bedingten Formatierung genutzt.

N() wandelt den Wert WAHR in eine 1, Text und den Wert FALSCH in den Wert 0 um, Zahlen behalten Ihren Wert. Befindet sich eine Prüfung in den Klammern von N() so z.B. N(8>=4), dann wird N(WAHR) = 1 gerechnet. Hier wird geprüft, ob der Wert der Vorzelle aus Spalte A anders ist als der Wert der aktuellen Zelle in Spalte A aus der gleichen Zeile. Also z.B. N(A4A5) oder N(A1A2). Immer dann, wenn also praktisch eine Zeile höher der gleiche Wert steht, kommt bei dieser Funktion der Wert 0 heraus, ansonsten eine 1.

SUMMENPRODUKT(Array1;Array2;…) bildet die Summe seiner einzelnen Produkte. Da aber nur ein einziges Array existiert, gibt es nichts zu Multiplizieren, sondern lediglich zu Addieren. Und was wird addiert? Der Wert der jeweiligen Prüfungen eines Bereichs, ob die Werte eines Bereichs mit den Werten eines anderen Bereichs verschieden sind. Und zwar jeweils beim Vergleich von einer Zelle in Spalte A zur Zelle darüber. Das nur in Spalte A geschaut wird, wird durch die $-Zeichen vor dem jeweiligen A garantiert.

Bei der Formatierung wird also pro Zelle geprüft, wie viele unterschiede von aktueller Zeile Spalte A gibt es bis zur Zelle A1 zurück. Liegen identische Werte untereinander, wird dieser Wert nicht erhöht. Wechselt aber ein Eintrag in der aktuellen Zeile, wird um 1 erhöht und die Funktion REST wird letztendlich zu einem anderen Ergebnis kommen.

Hier ist die Lösungsdatei:
Excel 467 – Excel Lösung wechselnde Zeilenfarbe

Und hier ist die Lösung auf YouTube:

Videolink: http://youtu.be/qv7fjEmuMcA

Excel – Groß- und Kleinbuchstaben getrennt zählen

Auf den Seiten von Andreas Entenmann (Excel MVP) werden herrliche Fragen und Lösungen diskutiert. Eine wirklich geniale Lösung von Thomas Ramel (Excel MVP) möchte ich nicht vorenthalten.

Das Problem… In Spalte B befinden sich kleine und große U’s. Ein großes U steht für einen vollen Tag Urlaub und ein kleines u für einen halben Tag. Wie zählt man nun allein durch die U’s und ohne Hilfsspalte die Anzahl der genommenen Urlaubstage?

461

Die Formel:
=SUMMENPRODUKT(IDENTISCH(Tabelle1[Urlaub];"U")+IDENTISCH(Tabelle1[Urlaub];"u")/2)

IDENTISCH prüft, ob sich der gesuchte Buchstabe in einer Zelle befindet. IDENTISCH unterscheidet Groß- und Kleinschreibung. Bei IDENTISCH resultiert ein WAHR oder FALSCH.

Wenn man WAHR- und FALSCH-Werte addiert, wird ein WAHR als 1 und ein FALSCH als 0 behandelt. Die IDENTISCH werte für das kleine u werden durch 2 geteilt.

SUMMENPRODUKT prüft nun für alle Zellen eines Bereichs zunächst den Inhalt der jeweiligen Formeln und addiert dann die Produkte. Es gibt hier allerdings nichts zu multiplizieren, da SUMMENPRODUKT kein zweites Array enthält. Somit wird nur addiert.

Ein paar alternative Lösungsvorschläge (die bereits genannte ist unser Favorit):
{=SUMME(WENN(IDENTISCH(Tabelle1[Urlaub];"U")=WAHR;1;0))+SUMME(WENN(IDENTISCH(Tabelle1[Urlaub];"u")=WAHR;1;0))/2}
oder
{=summe(wennfehler(--(unicode(Tabelle1[Urlaub])=85);0))+summe(wennfehler(--(unicode(Tabelle1[Urlaub])=117);0))/2}

von Mourad Louha (Excel MVP):
SUMMENPRODUKT(1*(CODE(Tabelle1[Urlaub]&"$")=85)+0,5*(CODE(Tabelle1[Urlaub]&"$")=117))
oder
{=SUMME(WENN(CODE(Tabelle1[Urlaub]&"$")=85;1;0)+WENN(CODE(Tabelle1[Urlaub]&"$")=117;0,5;0))}

Allein an der Vielzahl der Lösungen – und dies ist nur eine Auswahl – kann man erkennen, wie vielfältig Aufgaben in Excel bewältigt werden können.

Die Übungsdatei:
Excel_461_Urlaubstage

Hier geht es zum Video:

Videolink: http://youtu.be/On6vMr64DDw