Grundfunktionen: 3. Formeln

Formeln sind die grosse Zauberkiste von Calc und Excel. Man kann beinahe alles aus beliebigen Daten herausholen, wenn man die Formeln im Griff hat. Mit den wichtigsten 8 Formeln kommt man bereits sehr weit!Mit den Formeln kann man bestehende Daten weiterverarbeiten, verändern und untereinander kombinieren. Beide Office-Pakete stellen einen Funktions-Assistenten zur Verfügung, mit dem man bereits früh zu einem guten Ergebnis kommt – später kann man diese Formeln direkt von Hand eingeben. Der Funktions-Assistent findet man links neben der Eingabezeile (das kleine f mit einem x). Beachtet nach den wichtigen Funktionen auch nocht die Stabilität am Schluss!

Wichtige Funktionen
  • Mathematische Berechnungen
    Man kann die typischen Berechnungen der Mathematik natürlich auch mit Calc durchführen. Zum Beispiel =53+(6/3). Anstelle der Werte kann man selbstverständlich auch auf Zellen verweisen, also =A1+(B1/B2). In diesen Bereich gehören für mich auch die Basisfunktionen Mittelwert, also z.B. =MITTELWERT(A1:A15), wie auch SUMME, ABS (für den Absolutbetrag), RUNDEN (bzw. AUF- oder ABRUNDEN), MAXIMUM, MINIMUM und weitere.
  • WENN
    Wenn-Bedingungen sind meine kleine Helden :). Sie bestehen aus drei Teilen: Erstens die Prüfung, also was soll getestet werden, bzw. was ist also dieses Wenn. Danach folgt der Wert „Dann“ – also was ausgegeben werden soll, wenn die Prüfung korrekt ist, und am Schluss, was ausgegeben werden soll, falls die Prüfung nicht korrekt ist. Separiert werden die Teile mit dem Semikolon (;) – wie dies immer üblich ist in Calc. Im Beispiel: =WENN((4+1)=5;“Korrekt“;“Falsch“). Solange die Grundpfeiler unserer Mathematik stehen bleiben und 4+1 auch weiterhin 5 ergibt, solange wird diese Formel „Korrekt“ zurück geben. Sobald die mathematischen Gesetze geändert werden, würde ein „Falsch“ zurück kommen. Auch hier kann man natürlich mit Feldbefehlen arbeiten, wie immer in Calc, also =WENN(A1=5;B1;““). Nun wird geprüft, ob der Wert in A1 tatsächlich 5 entspricht. Falls dem so ist, wird der Wert aus B1 zurückgegeben, ansonsten wird das Feld leer bleiben (symbolisiert durch „“). Genau mit diesen Leerzellen arbeite ich ziemlich oft, indem ich prüfe =WENN(A1=““;““;“Hier steht die Funktion“). Es wir also geschaut, ob A1 leer ist – wenn es tatsächlich leer ist, bleibt auch das Formelfeld leer. Ansonsten wird die Funktion im DANN Bereich ausgeführt. Natürlich kann man genauso prüfen, ob A1 nicht leer ist, das Ungleichzeichen ist <>, somit lautet die Formel =WENN(A1<>““;“nicht leer“;“leer“).
    Es kommt regelmässig vor, dass man mehrere Bedingungen prüfen will – dazu existieren die Funktionen UND und ODER. Damit kann man mehrere Bedingungen – auch in Kombination – prüfen. Bei UND müssen selbstverständlich alle Bedingungen korrekt sein, dass am Schluss auch tatsächlich „WAHR“ zurückgegeben wird, damit also der Bereich DANN ausgelöst wird. Bei ODER reicht es, wenn eine einzelne Bedingung korrekt wird. In einem Beispiel: =UND((4+1)=5;(6-1)=5). Es wird also geprüft, ob 4+1 genau so 5 ergibt, wie 6-1. Etwas komplexer wäre dies =UND(ODER(A1=1;B1=1;C1=1);A3=“ja“). In diesem Fall muss mindestens A3 einem „ja“ entsprechen – zudem muss entweder A1, B1 oder C1 der Zahl 1 entsprechen. Nur in dieser Konstellation wird die Funktion WAHR.
  • SUMMEWENN
    Mit dieser Funktion werden nur Werte aufsummiert, wenn zusätzlich eine Bedingung erfüllt ist. Angenommen man hat eine Tabelle mit folgendem Inhalt: Spalte A ist das Produkt, Spalte B die Kategorie und Spalte C der Preis. So würde dies so aussehen:

    Apfel Frucht 2
    Birne Frucht 3
    Vollkornbrot Brot 2
    Kirsche Frucht 1
    Weissbrot Brot 1.5

    Mit SUMMEWENN kann nun die Preise sämtlicher Früchte addieren: =SUMMEWENN(B1:B5;“Frucht“;C1:C5). Es wird also in Spalte B gesucht, ob der Wert dem Wort Frucht entspricht – falls dem so ist, wird der Wert aus Spalte C in dieser Zeile zusammenaddiert – es wird also 6 ausgegeben.

  • ZÄHLENWENN
    Diese Funktion macht etwas sehr ähnliches, jedoch zählt lediglich die Werte einer Bedingung – es wird also geschaut, wie viele Früchte überhaupt in der Aufzählung sind. =ZÄHLENWENN(B1:B5;“Frucht“) führt also zu 3, da es drei Früchte in der Liste hat.
    In der Kombination kann man also den Durchschnittspreis einer Frucht erfahren, indem man den Wert von SUMMEWENN durch ZÄHLENWENN dividiert.
  • DATUM
    Diese Funktion scheint auf den ersten Blick etwas unnötig, denn man kann das Jahr, den Monat und den Tag eingeben. =DATUM(2015;03;21) führ also zum 21. März 2015. Dennoch wird die Funktion am Ende hilfreich sein, da man z.B. den Monat fortlaufend machen möchte, der erste Tag aber immer einem bestimmten Wert entsprechen soll.
    Zu dieser Kategorie gehören für mich auch die Funktionen JAHR, MONAT, TAG, KALENDERWOCHE, HEUTE oder NETTOARBEITSTAGE. Mit der letzten Funktion kann man berechnen, wie viele Arbeitstage (also exkl. Wochenende, und auf Wunsch auch exkl Feiertage) zwischen zwei Daten liegen. In einem Beispiel =NETTOARBEITSTAGE(HEUTE();B1). Nun wir geschaut, wie viele Arbeitstage zwischen heute und einem bestimmten Tag, welchen man in B1 eintragen kann, liegen.
  • VERKETTEN
    Mit dieser Funktion kann man dynamischen Text generieren – so kann man ein berechnetes Resultat in einen Satz einfügen. Dies erlaubt eine schöne Darstellung, zudem setze ich die Funktion speziell gern bei Diagrammen ein. So möchte man eventuell das Resultat einer Berechnung bei der Legende direkt mitgeben, sodass man nicht nur den Wert grafisch dargestellt hat, sondern auch direkt als absoluten Wert.
    Angenommen, man hat vorhin die Formel für SUMMEWENN im Feld D1 hineingeschrieben, und ZÄHLENWENN in das Feld D2, so könnte die Verkettungsfunktion lauten =VERKETTEN(„Es gibt „;D2;“ Früchte im Sortiment, die zusammen „;D1;“ CHF kosten.“). Damit wird nun direkt der vollständige Satz „Es gibt 3 Früchte im Sortiment, die zusammen 6 CHF kosten“ generiert.
  • Fortgeschritten: SVERWEIS, VERGLEICH & INDIREKT
    Auch diese Befehle können unheimlich hilfreich sein, sind aber etwas schwieriger zu verstehen.
    Mit SVERWEIS kann man einen Wert in einer Spalte suchen (analog dazu gibt es den WVERWEIS für die Suche in einer Zeile) und sich einen dazugehörenden Wert ausgeben lassen. Im Fruchtbeispiel kann man so eine Abfrage gestalten, in der ich das gesuchte Nahrungsmittel eingeben kann, und mit dem SVERWEIS wird der Preis des Produkts ausgegeben. Es wäre also =SVERWEIS(„Apfel“;A1:C5;3;FALSCH) wenn ich den Preis des Apfels wissen möchte. Nun wird im Bereich A1 bis C5 gesucht (und zwar in der ersten Spalte, also A1 bis A5), und falls der Wert genau gefunden wird (dazu das FALSCH an letzter Stelle), wird die dritte Spalte ausgegeben (dazu die Nummer 3). Nun kann man natürlich nicht konkret nach Apfel suchen, sondern die Eingabe auch dem Benutzer überlassen.
    Der VERGLEICH erreicht etwas ganz ähnliches, gibt aber die Position zurück, anstatt dem Preis (wo in der Liste steht das gesuchte Wort). Konkret bedeutet dies =VERGLEICH(„Apfel“;A1:A5;0). Es wird also wieder der Apfel gesucht, im Bereich A1 bis A5, und das Wort muss exakt übereinstimmen (dazu die 0 am Schluss). Das Ergebnis lautet 1, da der Apfel in der ersten Zeile steht.
    Mit INDIREKT kann man dynamische Formeln generieren – da man möglicherweise das auszulesende Feld nicht fix kennt. Wenn man z.B. für jede Kalenderwoche eine Zeile in einem Excel hat, und das Formular automatisch den Wert der aktuellen Kalenderwoche auslesen soll, so ändert sich die auszulesende Zeile wöchentlich. In diesem Fall hilft INDIREKT. So kann man sich in einer Zelle die aktuelle Kalenderwoche berechnen lassen: =KALENDERWOCHE(HEUTE()). Nehmen wir an, der gewünschte Wert steht in Spalte B, so kann man dank der VERKETTEN-Funktion =VERKETTEN(„B“;KALENDERWOCHE(HEUTE())) das dynamische Feld generieren lassen (B18). Nun lässt man dieses Feld mit dem INDIREKT Befehl auslesen =INDIREKT(VERKETTEN(„B“;KALENDERWOCHE(HEUTE()))).
Stabilität

Solange man eine Formel für ein einziges Feld schreibt, wird sie sicherlich korrekt sein – doch sobald man eine Formel kopieren will, ist Stabilität wichtig. Jeder Bezug besteht aus einer Angabe zur Spalte, wie auch zur Zeile. Diese beiden Werte werden beim kopieren immer entsprechend angepasst – das heisst, der Bezug ist standardmässig relativ – nicht absolut. Wenn ich in das Feld B3 hinein schreibe =B1+B2, so wird dies also auf die Zellen direkt darüber bezogen. Wenn ich dies nun in die Zelle A5 kopiere, so lautet die Formel automatisch =A3+A3.

Mittels Stabilität kann man dies verhindern – das Zeichen dazu ist das Dollar-Symbol $. Man kann nun die Spalte, die Zeile, oder beides stabilisieren. Man hat nun also =B1+B2 als Ausgangsformel. Im ersten Wert verweist man auf B1 – also auf Spalte B und Zeile 1. Das Dollarzeichen wird für die Stabilität vorangestellt – das heisst =$B1 verweist immer auf die Spalte B – die Zeile passt sich aber an. Entsprechend verweist =B$1 immer auf die Zeile 1 – die Spalte passt sich an. Und mit =$B$1 wird die Zelle immer konstant gehalten.

Tipps
  • Schreibe Formeln bereits von Beginn weg stabil, so dass man sie kopieren kann
  • Mach bei komplexen Formeln Zwischenschritte, die man u.U. später zusammenführen kann.
  • Berücksichtige immer, dass ein Feld leer sein kann, oder die Formel eventuell einen Fehler zurückgeben kann. (Prüfung via ISTLEER (bzw. A1=““) oder ISTFEHLER).
Aufgabe

Wer möchte, darf gerne kleine Aufgaben zu den Formeln machen. Ich habe dazu eine Vorlage mit drei Aufgaben erstellt. Das Ziel ist, dass ihr die Formeln für die gelben Felder schreibt, so dass man die grauen Felder ausfüllen kann. Bei Fragen am besten einen Kommentar schreiben:) – Lösungen natürlich ebenfalls gerne. Die Vorlage findet ihr hier: https://excel-lernen.ch/Formel_Uebungen_0_0_1.ots (für Excel hier: https://excel-lernen.ch/Formel_Uebungen_0_0_1.xltx). Aufgabe 3 ist eher schwierig – hier kann man gut mit Hilfsfeldern arbeiten.

 

Weiter zu Lektion 4 →