5 Profitipps für trickreiche Excel-Funktionen
Wenn Sie die richtigen Abkürzungen kennen, lassen sich viele Aufgaben in Excel deutlich schneller und zuverlässiger lösen. Dieser Workshop macht Sie mit trickreichen Excel-Funktionen vertraut, die nicht jeder kennt, die dafür aber umso nützlicher sind.
Mit dem Namenfeld können Sie einzelne Zellen des Arbeitsblattes mit einem Namen versehen.
TIPP 1: Wichtige Werte in "Variablen" speichern
In vielen Formeln tauchen immer wieder die gleichen Werte auf. Zu solchen Zahlen gehören zum Beispiel der Mehrwertsteuersatz, Zinssätze, Kilometerpauschalen und so weiter. Anstatt nun diese Zahlen immer wieder aufs Neue einzutippen, können Sie sie besser in einer separaten Zelle auf dem Arbeitsblatt eintragen und diese Zelle mit einem Namen versehen. Diesen Namen können Sie dann anstelle der Zahl in Ihren Formeln verwenden. Das hat nicht nur den Vorteil, dass Ihre Formeln leichter lesbar sind (z.B. "=B2 * MwSt"), sondern Sie können die betreffende Zahl auch sehr einfach - nämlich zentral an einer Stelle - ändern.
Legen Sie in Ihrer Arbeitsmappe einen Bereich für die benötigten Stammdaten fest. Dazu können Sie zum Beispiel ein eigenes Tabellenblatt verwenden.
Tragen Sie die Daten als kleine Tabelle ein - zum Beispiel in der einen Spalte den Wert und in der anderen Spalte eine Beschreibung. Markieren Sie die Zelle, die den ersten Wert enthält.
Tippen Sie oben links im Namenfeld (das ist das Feld, in dem Excel die Koordinaten der markierten Zelle anzeigt) einen Namen ein. Diesen Namen können Sie dann in Zukunft anstelle des Wertes in Ihren Formeln verwenden.
Legen Sie auch für die anderen Stammdaten einen Namen fest. Diese sollten dabei kurz und prägnant sein, damit sie sich schnell eingeben und gut merken lassen.
Hier ein Beispiel für eine beschriftete Tabelle.
2. TIPP: Umgangssprachliche Formeln verwenden
Wenn Sie in Excel eine Summe berechnen wollen, sieht das in der Regel etwa so aus: "=SUMME(F2:F12)". Solche Formeln sind natürlich im Nachhinein nur schlecht nachvollziehbar. Viel schöner wäre es, wenn Sie anstelle von "=SUMME(F2:F12)" einfach schreiben könnten: "=SUMME(Mai)". Und noch schöner ist: Das klappt mit Excel viel einfacher als Sie glauben.
Damit dieser Trick funktioniert, muss die Option "Beschriftungen in Formeln zulassen" eingeschaltet sein. Sie finden sie unter "Extras/Optionen" auf dem Register "Berechnungen". Die Einstellung bezieht sich immer auf die aktuelle Arbeitsmappe. Standardmäßig ist die Option ausgeschaltet.
Legen Sie nun eine Tabelle an, deren Spalten und Zeilen beschriftet sind (ähnlich wie in der Abbildung). Um jetzt zum Beispiel die Werte einer Tabellenspalte zu summieren, müssen Sie nicht die üblichen Zellbezüge a la "=SUMME(F2:F12)" verwenden, sondern können einfach "SUMME(Mai)" schreiben, wobei "Mai" die Beschriftung der gewünschten Spalte ist.
Auch mehrere Spalten oder Zeilen lassen sich mit dieser Methode problemlos ansprechen. Um etwa die Summe der ersten Quartale zu berechnen, verwenden Sie die Formel "=SUMME(Jan:Mrz)". Und auch wenn Sie auf eine bestimmte Zelle der Tabelle zugreifen möchten, lässt sich das dank ihrer Beschriftungen komfortabel und lesefreundlich lösen. Verwenden Sie dazu einfach die Syntax "Spaltenbeschriftung + Leerzeichen + Zeilenbeschriftung". Um also in der hier abgebildeten Tabelle auf die Umsätze für Januar 2008 zuzugreifen, schreiben Sie " = Jan ,2008'". (Die Apostrophe sind notwendig, da es sich hier um Jahresangaben, also um Zahlen handelt).
Sie können die Bereiche Ihrer Tabellen, die Sie als Beschriftung verwenden, auch per Hand festlegen. Markieren Sie dazu die gewünschten Zellen und rufen dann mit "Einfügen/Namen/Beschriftung"das Dialogfeld "Beschriftungsbereiche" auf. Teilen Sie Excel mit, ob es sich um eine Zeilen- oder eine Spaltenbeschriftung handelt und klicken Sie auf "Hinzufügen". Anschließend erkennt Excel auch Zahlen und Datumsangaben als Spaltenbeschriftung und ergänzt in Formeln automatisch die erforderlichen Apostrophe. Wenn Sie die Zellen, die Sie als Beschriftungsbereiche definiert haben, sichtbar machen wollen, stellen Sie die Vergrößerung des Tabellenblattes einfach auf einen Wert kleiner als 40% ein. Excel rahmt die betreffenden Zellen dann Blau ein.
Hier sehen sie eine zweizeilige Beschriftung zur Kennzeichnung von Quartal und Verkaufsgebiet.
3. TIPP: Gestapelte Beschriftungen
Der Trick mit den umgangssprachlichen Formeln funktioniert auch bei so genannten "gestapelten" Beschriftungen. Mit diesem hochgestochenen Begriff sind ganz einfach zweizeilige Beschriftungen gemeint, bei der in der oberen Zeile ein Begriff steht, der dann in der unteren Zeile weiter untergliedert wird (siehe Abbildung).
Erstellen Sie eine Tabelle mit einer zweizeiligen Beschriftung (zum Beispiel wie in der Abbildung in der ersten Zeile das Quartal und in der zweiten die Verkaufsgebiete). Damit Excel den inhaltlichen Zusammenhang zwischen den beiden Zeilen erkennt, müssen Sie in der ersten Zeile jeweils so viele Zellen verbinden, wie in der Zeile darunter inhaltlich zusammengehören.
Zum Verbinden markieren Sie die Zellen und klicken dann in der Symbolleiste "Format" auf die Schaltfläche "Zusammenführen und zentrieren". In der Abbildung können Sie gut erkennen, dass die Zellen für die Beschriftung der einzelnen Quartale aus jeweils drei Zellen entstanden sind (da es jeweils drei Verkaufsgebiete gibt).
Anschließend können Sie zum Beispiel die durchschnittlichen Verkäufe im 1. Quartal für das Verkaufsgebiet "West" mit folgender Formel berechnen: "=MITTEL WERT(,1. Quartal' West)". Wenn Sie auf die Verkäufe eines bestimmten Jahres zugreifen wollen, verwenden Sie dementsprechend den Ausdruck "= ,1. Quartal' West ,2008'".
Mit "Strg+#" lassen sich alle Formeln sichtbar machen.
TIPP 4: Tabellen mit SVERWEIS durchsuchen
Die Funktionen SVERWEIS und WVERWEIS sind zwei weithin unbekannte Perlen aus dem Excel-Portfolio, mit denen vorgegebene Werte in Tabellen nachgeschlagen werden können. Was sich auf den ersten Blick sehr abstrakt anhört, wird an einem einfachen Beispiel schnell klar: Sie verkaufen ein Produkt und bieten Ihren Kunden eine Rabattstaffel an. Nun wollen Sie eine Formel erstellen, die Ihnen den Gesamtpreis zu einer vorgegebenen Anzahl von Produkten liefert. Kompliziert? Nein, keineswegs!
Erstellen Sie zunächst eine kleine Tabelle, die Ihre Rabattstaffel enthält (siehe Abbildung). In unserem Beispiel ist der Rabatt in Stufen von 10, 25, 50 und 100 unterteilt.
Markieren Sie die Daten der Tabelle (ohne die Spaltenüberschriften). Tippen Sie dann oben links im Namenfeld - dort wo gerade die Koordinate der linken oberen Ecke der Markierung steht - das Wort "Rabattstaffel" ein. Mit diesem Namen können Sie nun in Zukunft den soeben markierten Bereich ansprechen.
Legen Sie eine Zelle fest, in der Sie die Menge der verkauften Produkte eintragen wollen und geben Sie der Zelle entsprechend dem gerade beschriebenen Verfahren den Namen "Menge".
Unterhalb dieser Zelle soll der zu der gewählten Menge passende Einzelpreis angezeigt werden. Geben Sie dieser Zelle den Namen "Einzelpreis".
In diese Zelle geben Sie nun folgende Formel ein: "=SVERWEIS(Menge;Rabattstaffel; 2;WAHR)". Die Funktion SVERWEIS sucht das angegebene Suchkriterium (hier: "Menge") in der ersten Spalte eines Bereichs (hier: "Rabattstaffel"). Der letzte Parameter steuert, ob SVERWEIS dabei eine exakte Suche durchführt ("WAHR") oder denjenigen Wert als Treffer erkennt, der möglichst nah am Suchkriterium liegt, es jedoch nicht überschreitet ("FALSCH").
Als Resultat liefert SVERWEIS nicht den gefundenen Wert selbst, sondern den Inhalt der Zelle, die sich in der Trefferzeile in der durch den dritten Parameter angegebenen Spalte (hier: 2) befindet. Mit anderen Worten: SVERWEIS wandert in der Zeile, in der es das Suchkriterium gefunden hat, nach rechts, bis es die angegebene Spalte erreicht hat und liefert dann deren Wert als Ergebnis.
Um nun den Gesamtpreis zu berechnen, brauchen Sie nur noch den gefundenen Einzelpreis mit der Mengenanzahl zu multiplizieren, das heißt also : "=Menge * Einzelpreis".
Tabellen mit WVERWEIS durchsuchen
Wenn Sie die Funktion von SVERWEIS verstanden haben, werden Sie auch mit ihrem Gegenstück WVERWEIS keine Schwierigkeiten haben. Der einzige Unterschied ist, dass WVERWEIS nicht die erste Spalte, sondern die erste Zeile des Bereichs durchsucht und dass der dritte Parameter nicht die Spalte, sondern die Zeile angibt, in der sich der zurückzugebende Wert befindet.
Die Funktion BEREICH.VERSCHIEBEN erwartet insgesamt fünf Parameter.
TIPP 5: Bereiche dynamisch ändern
Viele Excel-Funktionen, darunter auch SVERWEIS und WVERWEIS, erwarten als Parameter einen Bereich. Wenn sich diese Bereiche auf Tabellen beziehen, die wohl noch erweitert werden, sollten Sie mit dynamischen Bereichen arbeiten. Das sind Bereiche, deren Größe sich automatisch an eine Tabelle anpassen kann.
Für diesen Komfort benötigen Sie die beiden Funktionen BEREICH.VERSCHIEBEN und ANZAHL. Der Name der Funktion BEREICH.VERSCHIEBEN ist dabei leider recht irreführend, denn verschoben wird von der Funktion überhaupt nichts.
Um mit BEREICH.VERSCHIEBEN einen Bereich festzulegen, müssen Sie der Funktion mitteilen, wo dessen linke obere Ecke liegt und welche Ausdehnung der Bereich hat. Das kann dann zum Beispiel so aussehen: "BEREICH.VERSCHIEBEN(B2;0;0;2;3)". Die ersten drei Parameter geben die Lage der linken oberen Ecke an, die beiden letzten die Anzahl der Zeilen und Spalten.
Mit "B2;0;0" ist in diesem Fall gemeint, dass man die linke obere Ecke des Bereichs erreicht, indem man von der Zelle B2 null Zeilen nach unten und null Spalten nach rechts geht. Auf diese "Verschiebung" der ersten Koordinate ist vermutlich der sonderbare Name der Funktion zurückzuführen.
Diese Art der Positionsangabe hat den Vorteil, dass sich die Lage des Bereichs dynamisch verändern lässt, indem man anstelle der beiden Nullen einen Zellverweis oder eine Funktion einfügt, die dann die aktuelle "Verschiebung" des Startpunkts liefern. Diese Möglichkeit soll uns aber hier nicht weiter interessieren.
Damit sich die Größe des Bereichs an die Tabelle anpassen kann, müssen wir die Anzahl der Zeilen und/oder Spalten ermitteln. Dazu eignet sich die Funktion ANZAHL, mit der Sie feststellen, wie viele Zellen eines Bereichs einen Inhalt besitzen.
Nehmen wir an, Ihre Tabelle besitzt drei Spalten und steht ganz links oben auf dem Arbeitsblatt, das heißt, sie belegt die Spalten A, B und C. Dann liefert "ANZAHL(A:A)" die Zeilenanzahl Ihrer Tabelle, wobei sich natürlich unterhalb der Tabelle keine anderen Daten auf dem Arbeitsblatt befinden dürfen (mit "A:A" durchsuchen Sie die gesamte Spalte A).
Der Bereich Ihrer Tabelle lässt sich also mit dem Ausdruck "BEREICH.VERSCHIEBEN(A1;0;0;ANZAHL(A:A);3)" beschreiben. Wenn Sie die Tabelle um neue Zeilen erweitern, passt sich der Bereich automatisch an, da ja dann "ANZAHL(A:A)" automatisch einen höheren Wert zurückgibt.