Eingangs ein kurzer Hinweis über die Terminologie: Viele Excel-Anwender machen zwischen Formeln und Funktionen keinen Unterschied, obwohl genau diese Differenzierung wichtig ist. Von Haus aus bringt Microsofts Tabellenkalkulation keine fertigen Formeln mit, sondern bietet lediglich Funktionen, die einem zuvor klar definierten Zweck dienen.
Das Programm handelt einen Funktion immer in einem einzigen Wort mit voranstehendem Gleichzeichen (=) ab, dem meist ein oder mehrere Argumente in Klammern folgen. So rufen Sie mit =SUMME(A3:A5) beispielsweise die Funktion SUMME auf und beziehen Ihre Berechnung dabei auf die Inhalte der Zellen A3 bis A5. Erst wenn Sie nun mehrere solche Funktionen hintereinander schalten und damit eine ganze Kette von Rechenoperationen auslösen, sprechen wir von einer Formel.
Ein Hinweis: Die auf den folgenden Seiten vorgestellten Beispiele beziehen sich allesamt auf Microsoft Excel 2003, funktionieren in neuen oder älteren Programmversionen aber analog oder ähnlich.
Vor Beginn der zehn besten Excel-Funktionen noch ein kurzer Griff in die Trickkiste:
Excel-Funktion SUMMEWENN
Filter beschränken einen Tabellenbereich auf jene Zeilen, in denen ein bestimmter Wert vorkommt. Die Anwendung ist simpel: Markieren Sie die Titelzeile einer Auflistung und wählen Sie den Befehl Daten/Filter/Auto-Filter. Greifen Sie sich nun im Einblendmenü am oberen Ende der Liste einen der Werte, um nur die entsprechenden Zeilen anzuzeigen. Diese Filter sind bestens geeignet, um sich schnell einen Überblick zu verschaffen. Der Nachteil: Es kann nur ein Begriff gefiltert werden. Zudem wird das Gesamttotal nicht auf die gefilterten Werte reduziert.
Beide Einschränkungen lassen sich mit der Funktion SUMMEWENN umgehen. Sie arbeitet genau wie die Funktion SUMME, bietet aber zusätzlich die Möglichkeit, nur Werte innerhalb eines definierten Bereiches zu addieren. Die Funktion ist folgendermaßen aufgebaut: =SUMMEWENN(Bereich;Suchkriterium;Summe_Bereich)
Wenn wir in unserer kleinen Spesenabrechnung prüfen möchten, wie viel Geld für Benzin ausgegeben wurde, verwenden wir zum Beispiel folgende Argumente: =SUMMEWENN(C6:C19;F6;D6:D19) C6:C19 umfasst den Bereich, der überprüft werden soll – also die Beschreibung der Auslagen. F6 steht für das Wort Benzin, das zum Suchkriterium wird. F6 könnte auch durch "Benzin" ersetzt werden (wichtig sind bei Texteingaben die Anführungszeichen). Und zu guter Letzt umfasst D6:D19 den Bereich, dessen Werte bei einer Übereinstimmung aufaddiert werden sollen. So können Sie beliebig viele Posten auswerten, ohne auf Filter angewiesen zu sein.
Excel-Funktion ZÄHLENWENN
Die Funktion ZÄHLENWENN arbeitet sehr ähnlich wie SUMMEWENN, beantwortet jedoch andere Fragen. Zum Beispiel: "Wie häufig wurde übernachtet?" Dazu zählt die Funktion einfach jedes Vorkommen des Eintrags Hotel.
Die Funktion lautet exakt: =ZÄHLENWENN(Bereich;Suchkriterium) Um unsere Frage nach der Anzahl der Übernachtungen zu beantworten, wird die Funktion mit folgenden Argumenten bestückt: =ZÄHLENWENN(C6:C19;F6) Der Bereich umfasst alle Ausgabenpositionen, die durchsucht werden sollen – und zwar nach dem Wort, das in Zelle F6 steht. Genauso gut könnte man auch direkt nach dem Begriff "Hotel" suchen oder nach >100, um alle Auslagen zu zählen, die über 100 Franken liegen.
Excel-Funktion WENN
WENN gehört zu jener Sorte von Funktionen, an der früher oder später kaum ein Excel-Anwender vorbeikommt. Mit ihr prüfen Sie Resultate und treffen anschließend eine Entscheidung, wie es weitergehen soll.
Ein Beispiel: Bei der Fakturierung gilt die Regel, dass bei Einkäufen über 10000 Franken automatisch drei Prozent Rabatt gewährt werden. Die Formel lautet also sinngemäß: "Addiere alle Posten zusammen. Wenn das Total 10000 Franken erreicht, ziehe drei Prozent Rabatt ab; ansonsten gilt das errechnete Resultat ohne Rabatt." Diese Funktion enthält für diese Berechnungen drei Argumente: =WENN(Prüfung;Dann_Wert;Sonst_Wert) Die Funktion in Zelle C15 ("Rabatt") unseres Beispiels hat damit folgendes Muster: =WENN(C14<10000;0;3)
Oder ausgedeutscht: "Wenn der Wert in C14 unter 10000 liegt, trage eine 0 ein; andernfalls trage eine 3 ein." Danach kann mit dem Resultat in C14 weitergearbeitet werden. Doch es kommt noch besser, denn mehrere WENN-Funktionen lassen sich verschachteln. Nehmen wir an, bei Umsätzen ab 20000 Franken erhöht sich der Rabattsatz auf fünf Prozent. In diesem Fall prüfen wir am einfachsten, ob der Betrag für fünf Prozent reicht. Wenn nicht, wird das Argument Sonst_Wert durch eine zusätzliche WENN-Abfrage ersetzt, um zu prüfen, ob es für drei Prozent reicht. Schlägt auch diese Überprüfung fehl, wird kein Rabatt gewährt. Und so sieht die fertige Formel aus: =WENN(C14>=20000;5;WENN(C14>=10000;3;0))
Dieses Spiel lässt sich beliebig fortsetzen. Anstelle von Zahlen können auch Texte als Resultate ausgegeben werden, die jedoch in Anführungszeichen gesetzt werden müssen. Ein Beispiel dazu sieht folgendermaßen aus: =WENN(C14<10000;"Zahlungsziel: 30 Tage netto.";"Wir gewähren Ihnen 3% Rabatt.")
Excel-Funktion TEXT
Das vorherige Beispiel führt uns nahtlos zu einem anderen Problem: Häufig werden die Resultate einer Excel-Berechnung in Worte verpackt wie: "Auf Ihre Bestellsumme von 13354.55 gewähren wir einen Rabatt von 3%." Für einen solchen Satz müsste man sich eine Formel basteln wie: = "Auf Ihre Bestellung von " & C13 & " gewähren wir einen Rabatt von " & C15 & "%."
Das würde funktionieren – und wird deshalb auch täglich in unzähligen Büros dieser Welt praktiziert. Bei dieser Methode besteht das Resultat jedoch aus Text, sodass sich die Zahlendarstellung nur bedingt über die Zellenformate verändern lässt. Sobald mehr als ein Wert formatiert werden soll, ist Schluss. Zwar könnte die Zelle C13 formatiert werden, doch das wirkt sich nicht auf die Darstellung innerhalb des formulierten Textes aus.
Wenn Sie zum Beispiel den Wert 13354,55 als 13'354,55 darstellen möchten, ist die Funktion TEXT der Retter in Not. Zwar besteht das Resultat dieser Funktion ebenfalls aus Text, doch zuvor können die Zahlen beliebig formatiert werden. Der Aufbau lautet: =TEXT(Wert;Textformat)
Der Wert steht in unserem Beispiel in Zelle C14 – also dort, wo das Subtotal berechnet wird, Screen 4. Das Argument Textformat legt fest, wie die Zahl formatiert werden soll, bevor sie zu Text wird, also zum Beispiel "#'##0,00". Beachten Sie bitte, dass die Formatierung der Zahl in Anführungszeichen steht. Die fertige Funktion lautet demnach wie folgt: = "Auf Ihre Bestellung von " & TEXT(C14;"#'##0,00") & " gewähren wir einen Rabatt von " & C15 & "%."
Auf diese Weise lassen sich nicht nur Beträge, sondern auch beliebige Datums- und Zeitangaben oder andere Werte formatieren. Doch wo lässt sich nachschlagen, wie das Argument Textformat auszusehen hat? Ganz einfach: Wählen Sie in der Menüleiste von Excel Format/Zellen/Zahlen und klicken Sie in der Auflistung am linken Rand auf den untersten Eintrag Benutzerdefiniert. Wählen Sie die gewünschte Formatierung. Sobald das Beispiel im Eingabefeld "Typ" Ihre Erwartungen erfüllt, kopieren Sie es und setzen es in die Funktion TEXT ein.
Excel-Funktion TEIL
Manchmal müssen Texte nicht zusammengefügt, sondern zerpflückt werden – eine wahre Knochenarbeit, wenn Sie Datensätze aus einer Datenbank importiert haben, deren Felder nicht sauber getrennt wurden. Nehmen wir an, Sie haben einen Adressstamm eingelesen, bei dem sich die Postleitzahl und der Ort in derselben Zelle befinden. Die Funktion TEIL kann in einem ersten Schritt dabei helfen, die Postleitzahl zu separieren. Anschließend sehen wir uns an, wie auch die Ortschaft in einer eigenen Zelle untergebracht wird. Die Formel dazu lautet: =TEIL(Text;Erstes_Zeichen;Anzahl_Zeichen)
In unserem Beispiel befindet sich der Text in Zelle B5. Danach müssen wir festlegen, ab welcher Stelle die Zeichen extrahiert werden sollen: in unserem Fall ab dem ersten Zeichen. Nun möchte Excel wissen, wie viele Zeichen es extrahieren soll. Da in der Schweiz alle Postleitzahlen vierstellig sind, liegt die Antwort auf der Hand. Die fertige Funktion sieht so aus: =TEIL(B5;1;4) Jetzt können Sie die Formel nach unten auffüllen, um alle Postleitzahlen zu separieren.
Excel-Funktion LÄNGE
Jetzt geht es darum, die Ortschaft von der Postleitzahl zu trennen. Dazu beginnen wir immer beim sechsten Zeichen (die vierstellige Postleitzahl plus das Leerzeichen werden übersprungen). Allerdings stellt sich die Frage, wie viele Zeichen extrahiert werden sollen, denn hier gibt es keine festen Werte, mit denen wir arbeiten könnten.
Die Funktion LÄNGE hilft aus der Patsche und mit nur einem Argument ist sie sehr simpel: =LÄNGE(Text) Mit ihr berechnen wir die Gesamtlänge der Zelle und ziehen fünf Zeichen ab (Postleitzahl plus Leerzeichen). Das Resultat zeigt, wie viele Zeichen der Ortsname umfasst. Die Funktion zum Extrahieren der Ortschaft lautet damit: =TEIL(B5;6;LÄNGE(B5)-5)
Excel-Funktion SÄUBERN & GLÄTTEN
Wenn die letzten Beispiele zum Aufteilen von schlecht erfassten Daten Ihren Nerv getroffen haben, werden Sie folgende Funktionen lieben: =SÄUBERN(Text) =GLÄTTEN(Text) Beide verlangen lediglich, dass Sie eine Zelle mit Textinhalt angeben. Anschließend vernichtet SÄUBERN sämtliche Steuerzeichen, unbrauchbare Sonderzeichen und ähnliches Unkraut, während GLÄTTEN alle Vorkommnisse von mehr als einem Leerschlag auf einen einzigen Leerschlag reduziert – unbezahlbar!
Excel-Funktion KALENDERWOCHE
Das Phänomen Kalenderwoche: Keiner kennt sie auswendig und doch wird sie immer wieder für die Terminplanung hinzugezogen. Natürlich bietet auch Excel eine Funktion, um die Kalenderwoche zu berechnen – auch wenn sie standardmäßig nicht installiert ist. Um das nachzuholen, wählen Sie das Menü Extras/Add-Ins und markieren danach "Analyse-Funktionen". Klicken Sie auf OK, um die Installation abzuschließen. Ab jetzt steht die Funktion KALENDERWOCHE zur Verfügung: =KALENDERWOCHE(Datum;Rückgabe)
Das Argument Datum besteht aus der Zelle, die ein Datum enthält, für das Sie die Woche wissen möchten. Besondere Beachtung verdient vor allem das Argument Rückgabe, das aus dem Wert 1 oder 2 besteht. Es bestimmt, an welchem Tag eine Woche beginnt.
* Wenn Sie eine 1 eingeben, beginnt die Woche an einem Sonntag. Nach diesem Muster wird zum Beispiel in den USA verfahren.
* Wie in den meisten europäischen Ländern startet die Woche auch in Deutschland am Montag. Sie müssen also eine 2 eintippen, um zu einem korrekten Resultat zu kommen.
Excel-Funktion NETTOARBEITSTAGE
Verweilen wir einen Augenblick bei den Datumsfunktionen, von denen es in Excel rund 20 Stück gibt – je nach installierten Add-Ins. Für Produktionsbetriebe dürfte NETTOARBEITSTAGE von besonderem Interesse sein. Sie berechnet, wie viele Arbeitstage zwischen zwei Daten verfügbar sind. Dabei werden die Wochenenden automatisch abgezogen und auf Wunsch auch die Feiertage oder betriebsinterne Brückentage berücksichtigt. So sieht die Funktion aus: =NETTOARBEITSTAGE(Ausgangsdatum;Enddatum;Freie_Tage)
Doch bevor die Funktion ihre volle Wirkung entfalten kann, müssen die arbeitsfreien Tage (außer den Wochenenden) in einer Liste aufgeführt werden. Dazu schreiben Sie irgendwo auf demselben oder auf einem anderen Arbeitsblatt alle arbeitsfreien Tage der nächsten Zeit untereinander auf. Geben Sie der Liste einen Titel wie "Freie_Tage" und markieren Sie die Auflistung inklusive Titel. Wählen Sie nun den Menüpunkt Einfügen/Namen/Erstellen und markieren Sie die Option "Oberster Zeile", damit die Bezeichnung "Freie_Tage" automatisch übernommen wird.
Nun zur Funktion selbst: Klicken Sie in die Zelle, in der das Resultat erscheinen soll. Wählen Sie den Punkt Einfügen/Funktion, um den Funktionsassistenten aufzurufen. Nehmen Sie im Einblendmenü die Option Datum & Zeit und doppelklicken Sie auf den Eintrag NETTOARBEITSTAGE. Im folgenden Dialogfeld positionieren Sie die Einfügemarke im Eingabefeld "Ausgangsdatum" und klicken auf die Zelle, die das Startdatum der Berechnung enthält. Setzen Sie auf dieselbe Weise den letzten Tag in das Eingabefeld "Enddatum" ein. Klicken Sie zum Schluss in das Feld «Freie Tage» und drücken Sie die Taste F3, um eine Liste mit allen definierten Namen zu erhalten. Klicken Sie auf den Eintrag Freie_Tage, damit unsere Ausnahmen wirksam werden, und bestätigen Sie mit OK. Die fertige Funktion sieht so aus: =NETTOARBEITSTAGE(C3;C4;Freie_Tage) Jetzt erfahren wir zum Beispiel, dass im Dezember 2008 gerade einmal 19 Tage lang gearbeitet werden muss.
Excel-Funktion ZELLE
Die Funktion ZELLE ist so nützlich, dass ihr Einsatz in einigen Firmen sogar zur Vorschrift geworden ist – was vor allem daran liegt, dass sie den kompletten Pfad zu einer Datei anzeigen kann. Wer in einer Umgebung arbeitet, in der sich Hunderte von Excel-Dateien auf einem halben Dutzend Server tummeln, wird diese Funktion mit Gold aufwiegen. Sie zeigt genau, wo eine Datei zu finden ist. Die Anwendung ist denkbar einfach: Wenn der Pfad der geöffneten Excel- Tabelle mitsamt Dateinamen in die Zelle C3 geschrieben werden soll, lautet die Funktion: =ZELLE("Dateiname";C3) "Dateiname" gibt den richtigen Dateinamen aus. Der Pfad wird übrigens bei jedem Speichern der Datei aktualisiert. Dass überhaupt eine Zelle als Ziel angegeben werden muss, liegt daran, dass die Funktion ZELLE noch weitere Argumente kennt. =ZELLE("Schutz";B12) würde z.B. zum Ergebnis 0 führen, wenn die Zelle B12 nicht geschützt ist – ansonsten wäre das Resultat eine 1. Die Excel-Hilfe enthält eine Übersicht über die möglichen Argumente, die zusammen mit ZELLE angewendet werden können.
Lust auf mehr?
Das war nur eine kleine Auswahl an Excel-Funktionen, die einem den Arbeitsalltag erleichtern. Einige Funktionen verfolgen sehr ähnliche Ziele; die Funktion ARBEITSTAG ist zum Beispiel ein naher Verwandter der NETTOARBEITSTAGE, sodass Sie mit deren Verwendung keine Mühe haben sollten. Am einfachsten erweitern Sie Ihren Horizont, wenn Sie ab und zu in einer freien Minute den Funktionsassistenten von Excel aufrufen (Einfügen/Funktion) und sich ein wenig umsehen, welche anderen Funktionen Ihren Arbeitsalltag bereichern könnten.
Dieser Artikel basiert auf einem Beitrag unserer Schwesterpublikation PCtipp. (sh)