Zuerst kann es nicht schaden, ein paar Worte über die Terminologie zu verlieren: Viele Excel-Anwender unterscheiden nicht zwischen Formeln und Funktionen – dabei ist der Unterschied elementar. Excel selbst bietet nämlich keine fertigen Formeln, sondern lediglich Funktionen, die alle einen klar definierten Zweck erfüllen.
Eine Funktion wird immer in einem einzigen Wort mit voranstehendem Gleichzeichen abgehandelt, dem meistens ein oder mehrere Argumente in Klammern folgen. Wenn Sie =SUMME(A3:A5) in eine Zelle eintippen, dann rufen Sie die Funktion SUMME auf. In der Klammer folgt die Information, dass sich die Berechnung auf die Inhalte der Zellen A3 bis A5 bezieht. Erst wenn Sie mehrere Funktionen aneinanderreihen und eine ganze Kette von Berechnungen auslösen, sprechen wir von einer Formel.
Hinweis: Die folgenden Beispiele beziehen sich alle auf Excel 2013, funktionieren aber ähnlich in anderen Programmversionen.
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 Start -> Sortieren und Filtern -> 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: Das Gesamttotal einer Summe wird zum Beispiel nicht auf die gefilterten Werte reduziert.
Diese Einschränkung lässt 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.
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 Euro liegen.
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 Euro 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
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 Euro 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
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 ” & C14 & ” 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 C14 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. 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 Start-> Format -> Zellen formatieren -> 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.
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 Deutschland alle Postleitzahlen fünfstellig sind, liegt die Antwort auf der Hand. Die fertige Funktion sieht so aus: =TEIL(B5;1;5) Jetzt können Sie die Formel nach unten auffüllen, um alle Postleitzahlen zu separieren.
Jetzt geht es darum, die Ortschaft von der Postleitzahl zu trennen. Dazu beginnen wir immer beim sechsten Zeichen (die fünfstellige 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, und ist mit nur einem Argument sehr simpel: =LÄNGE(Text) Mit ihr berechnen wir die Gesamtlänge der Zelle und ziehen sechs Zeichen ab (Postleitzahl plus Leerzeichen). Das Resultat zeigt, wie viele Zeichen der Ortsname umfasst. Die Funktion zum Extrahieren der Ortschaft lautet damit: =TEIL(B5;7;LÄNGE(B5)-6)
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!
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: =KALENDERWOCHE(Datum;Wochenstart)
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 Wochenstart, 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.
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 “Formeln -> Aus Auswahl erstellen” definieren 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 “Formeln -> Funktion einfügen”, 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 2015 gerade einmal 20 Tage lang gearbeitet werden musste.
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 C1 geschrieben werden soll, lautet die Funktion: =ZELLE(“Dateiname”;C1) “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 (Formeln -> Funktion einfügen) und sich ein wenig umsehen, welche anderen Funktionen Ihren Arbeitsalltag bereichern könnten.