Formeln und Funktionen
In dieser Lerneinheit erlernst du den systematischen Aufbau und die korrekte Syntax von Formeln in Tabellenkalkulationen. Du verstehst die grundlegenden Prinzipien der Formelbildung und kannst einfache bis komplexe Berechnungen selbstständig erstellen. Diese Kenntnisse ermöglichen dir die automatisierte Verarbeitung von Daten und die Erstellung dynamischer Auswertungen für deine tägliche Arbeit.
Einführung
Am Ende des Monats sitzt du vor deiner Tabelle mit Ausgaben. 50 Zeilen, jede einzeln zusammenzählen? Dann noch den Durchschnitt berechnen, das Maximum finden, und am besten alles nochmal, wenn sich ein Wert ändert?
Mit Formeln und Funktionen erledigt die Tabellenkalkulation diese Arbeit automatisch. Du tippst eine Formel einmal ein, und sie aktualisiert sich bei jeder Änderung von selbst.
Ob Gehaltsabrechnung, Lagerbestand oder Projektbudget: Wer Formeln beherrscht, spart Zeit und vermeidet Rechenfehler.
In dieser Lerneinheit erfährst du, wie Formeln aufgebaut sind, welche Funktionen dir zur Verfügung stehen und wie du Zellbezüge richtig einsetzt.
Lernziele
Nach dieser Lerneinheit kannst du:
- den Aufbau und die Syntax von Formeln erklären
- relative, absolute und gemischte Zellbezüge korrekt anwenden
- mathematische, statistische und logische Funktionen für Berechnungen nutzen
- Text- und Datumsfunktionen zur Datenmanipulation einsetzen
Überleitung
Um Berechnungen zu automatisieren, musst du verstehen, wie Formeln in der Tabellenkalkulation funktionieren.
Beginnen wir mit der Grundstruktur: Was macht eine Formel aus und wie gibst du sie ein?
Was ist eine Formel?
Eine Formel ist eine Anweisung, die Excel oder Google Sheets sagt, was berechnet werden soll. Jede Formel beginnt mit einem Gleichheitszeichen (=).
=A1+B1
Diese Formel addiert die Werte aus den Zellen A1 und B1. Das Ergebnis erscheint in der Zelle, in der du die Formel eingegeben hast.
Wie funktioniert das? Sobald du Enter drückst, liest die Tabellenkalkulation die Werte aus den referenzierten Zellen, führt die Berechnung durch und zeigt das Ergebnis an. Ändert sich ein Wert in A1 oder B1, wird das Ergebnis automatisch neu berechnet.
Operatoren in Formeln
In Formeln nutzt du Operatoren, um Berechnungen durchzuführen. Die wichtigsten arithmetischen Operatoren sind:
| Operator | Bedeutung | Beispiel | Ergebnis |
|---|---|---|---|
+ | Addition | =5+3 | 8 |
- | Subtraktion | =10-4 | 6 |
* | Multiplikation | =6*7 | 42 |
/ | Division | =20/4 | 5 |
^ | Potenz | =2^3 | 8 |
Diese Operatoren funktionieren sowohl mit direkten Zahlen als auch mit Zellbezügen wie =A1*B1.
Operator-Priorität
Wie in der Mathematik gilt auch in Excel: Punkt vor Strich. Multiplikation und Division werden vor Addition und Subtraktion ausgeführt.
=5+3*2
Was passiert hier? Zuerst wird 3*2=6 berechnet, dann 5+6=11. Das Ergebnis ist also 11, nicht 16.
Klammern setzen die Priorität außer Kraft:
=(5+3)*2
Jetzt wird zuerst 5+3=8 berechnet, dann 8*2=16.
Tipp: Setze im Zweifel Klammern. Das macht deine Formeln lesbarer und verhindert unerwartete Ergebnisse.
Zellbezüge verstehen
Statt feste Zahlen in Formeln zu schreiben, verweist du auf Zellen. Diese Verweise nennt man Zellbezüge oder Referenzen.
Es gibt drei Arten von Zellbezügen:
- Relativer Bezug (
A1): Passt sich beim Kopieren an - Absoluter Bezug (
$A$1): Bleibt beim Kopieren konstant - Gemischter Bezug (
$A1oderA$1): Teilweise fixiert
Warum ist das wichtig? Wenn du eine Formel in andere Zellen kopierst, entscheidet der Bezugstyp, ob sich die Referenzen anpassen oder gleich bleiben.
Relativer Bezug
Ein relativer Bezug wie A1 passt sich automatisch an, wenn du die Formel kopierst.
Beispiel: Du hast in Zelle C1 die Formel =A1+B1. Wenn du diese Formel nach C2 kopierst, wird sie automatisch zu =A2+B2.
Wie funktioniert das? Excel merkt sich nicht “Zelle A1”, sondern “zwei Spalten nach links”. Beim Kopieren nach unten wird dieser relative Abstand beibehalten.
Wann nutzen? Relativer Bezug ist der Standard und ideal, wenn du dieselbe Berechnung für viele Zeilen durchführen willst, etwa Umsatz pro Zeile berechnen.
Absoluter Bezug
Ein absoluter Bezug wie $A$1 bleibt beim Kopieren immer gleich. Das Dollarzeichen $ fixiert Spalte und Zeile.
Beispiel: Der Mehrwertsteuersatz steht in Zelle B1. Deine Formel in C2 lautet =A2*$B$1. Kopierst du diese nach C3, wird sie zu =A3*$B$1. Der Verweis auf B1 bleibt konstant.
Wann nutzen? Absoluter Bezug ist perfekt für Konstanten wie Steuersätze, Wechselkurse oder feste Prozentsätze, die für alle Berechnungen gleich bleiben sollen.
Gemischter Bezug und der F4-Trick
Bei einem gemischten Bezug ist nur die Spalte oder nur die Zeile fixiert:
$A1: Spalte A ist fixiert, Zeile passt sich anA$1: Zeile 1 ist fixiert, Spalte passt sich an
Wann nutzen? Gemischte Bezüge sind nützlich für Matrizen, etwa wenn du Rabattsätze in Zeile 1 und Preise in Spalte A hast.
Der F4-Trick
Statt Dollarzeichen manuell zu tippen, nutze die F4-Taste:
- Klicke in der Formelleiste auf einen Zellbezug
- Drücke F4 mehrmals: 1x ergibt absolut, 2x Zeile fixiert, 3x Spalte fixiert, 4x relativ
Fehlermeldungen verstehen
Wenn eine Formel nicht funktioniert, zeigt Excel eine Fehlermeldung an. Diese beginnt immer mit einer Raute.
Die wichtigsten Fehlermeldungen
| Fehler | Bedeutung |
|---|---|
| 0 | Division durch Null oder leere Zelle |
| WERT | Falscher Datentyp (z.B. Text statt Zahl) |
| BEZUG | Referenzierte Zelle wurde gelöscht |
| NAME? | Funktionsname falsch geschrieben |
| NV | Suche findet keinen Treffer |
Fehler vermeiden
Bei NAME? prüfe die Schreibweise. In der deutschen Excel-Version heißt es SUMME, nicht SUM.
Bei BEZUG hast du eine Zeile oder Spalte gelöscht, auf die eine Formel verweist.
Fehler abfangen
Mit WENNFEHLER zeigst du einen alternativen Wert:
=WENNFEHLER(A1/B1; 0)
Zeigt 0 statt einer Fehlermeldung wenn B1 leer oder 0 ist.
Was ist eine Funktion?
Eine Funktion ist eine vordefinierte Formel, die komplexe Berechnungen mit einem einfachen Aufruf erledigt. Statt selbst eine Formel zu bauen, nutzt du einen fertigen Baustein.
Wie ist eine Funktion aufgebaut?
Jede Funktion folgt diesem Schema:
=FUNKTIONSNAME(Argument1; Argument2; ...)
| Bestandteil | Bedeutung |
|---|---|
= | Startet die Eingabe |
FUNKTIONSNAME | Name der Funktion (z.B. SUMME, MITTELWERT) |
() | Klammern umschließen die Argumente |
| Argumente | Werte oder Zellbereiche, mit denen gerechnet wird |
; | Semikolon trennt mehrere Argumente |
Was bedeutet das konkret?
Nimm an, du willst die Summe der Zellen A1 bis A10 berechnen. Ohne Funktion müsstest du schreiben:
=A1+A2+A3+A4+A5+A6+A7+A8+A9+A10
Mit der SUMME-Funktion genügt:
=SUMME(A1:A10)
Der Doppelpunkt : definiert einen Bereich von A1 bis A10. Die Funktion addiert automatisch alle Werte in diesem Bereich.
Deutsche vs. englische Funktionsnamen
Excel passt Funktionsnamen an die Sprache des Betriebssystems an. In der deutschen Version heißen die Funktionen anders als in der englischen.
Wichtige Unterschiede
| Deutsch | Englisch | Funktion |
|---|---|---|
| SUMME | SUM | Addiert Werte |
| MITTELWERT | AVERAGE | Berechnet Durchschnitt |
| WENN | IF | Prüft Bedingung |
| SVERWEIS | VLOOKUP | Sucht in Tabelle |
| ANZAHL | COUNT | Zählt Zahlen |
| VERKETTEN | CONCATENATE | Verknüpft Texte |
Was bedeutet das für dich?
Wenn du Tutorials oder Anleitungen liest, achte auf die Sprachversion. Ein englisches =SUM(A1:A10) wird in der deutschen Excel-Version zu =SUMME(A1:A10).
Trennzeichen beachten
In der deutschen Version werden Argumente mit Semikolon getrennt, nicht mit Komma:
| Deutsch | Englisch |
|---|---|
=WENN(A1>10; "Ja"; "Nein") | =IF(A1>10, "Yes", "No") |
Das Semikolon ist wichtig, weil in Deutschland das Komma als Dezimaltrennzeichen verwendet wird (1,5 statt 1.5).
Mathematische Grundfunktionen
Excel bietet zahlreiche mathematische Funktionen. Die wichtigsten für den Einstieg sind:
SUMME - Werte addieren
=SUMME(A1:A10)
Addiert alle Werte im Bereich A1 bis A10. Leere Zellen und Text werden ignoriert.
MITTELWERT - Durchschnitt berechnen
=MITTELWERT(B1:B20)
Berechnet das arithmetische Mittel aller Zahlen im Bereich.
MIN und MAX - Extremwerte finden
=MIN(C1:C100) // Kleinster Wert
=MAX(C1:C100) // Größter Wert
Diese Funktionen durchsuchen den angegebenen Bereich und liefern den kleinsten bzw. größten Zahlenwert.
Praxisbeispiel
Stell dir eine Tabelle mit Verkaufszahlen vor:
| Monat | Umsatz |
|---|---|
| Januar | 1500 |
| Februar | 2300 |
| März | 1800 |
Mit den Funktionen berechnest du:
=SUMME(B2:B4)ergibt 5600 (Gesamtumsatz)=MITTELWERT(B2:B4)ergibt 1866,67 (Durchschnitt)=MAX(B2:B4)ergibt 2300 (bester Monat)
Weitere mathematische Funktionen
Neben den Grundfunktionen bietet Excel weitere nützliche mathematische Werkzeuge.
RUNDEN - Zahlen runden
=RUNDEN(A1; 2)
Rundet den Wert in A1 auf 2 Nachkommastellen. Bei negativen Zahlen wird vor dem Komma gerundet:
=RUNDEN(1234,567; 2)ergibt 1234,57=RUNDEN(1234,567; -2)ergibt 1200
POTENZ und WURZEL
=POTENZ(2; 8) // 2 hoch 8 = 256
=WURZEL(144) // Quadratwurzel = 12
Für Potenzen kannst du auch das Caret-Zeichen verwenden: =2^8 liefert dasselbe Ergebnis wie =POTENZ(2; 8).
ABS - Absolutwert
=ABS(-15)
Gibt den Betrag einer Zahl zurück, also immer den positiven Wert. Das Ergebnis ist 15.
REST - Modulo-Operation
=REST(17; 5)
Berechnet den Rest einer Division. 17 geteilt durch 5 ergibt 3 Rest 2. Die Funktion liefert also 2.
Nützlich zum Beispiel um zu prüfen, ob eine Zahl gerade ist: =REST(A1; 2) ergibt 0 bei geraden Zahlen.
Statistische Funktionen
Excel bietet Funktionen für statistische Auswertungen, die besonders bei größeren Datenmengen nützlich sind.
ANZAHL und ANZAHL2
=ANZAHL(A1:A100) // Zählt nur Zahlen
=ANZAHL2(A1:A100) // Zählt alle nicht-leeren Zellen
Der Unterschied: ANZAHL ignoriert Text, ANZAHL2 zählt alles außer leeren Zellen.
STABW und VAR - Streuungsmaße
=STABW.S(B1:B50) // Standardabweichung (Stichprobe)
=VAR.S(B1:B50) // Varianz (Stichprobe)
Diese Funktionen messen, wie stark die Werte um den Mittelwert streuen. Die Endung .S steht für Stichprobe (sample), .P für Grundgesamtheit (population).
MEDIAN - Zentralwert
=MEDIAN(C1:C100)
Der Median ist der mittlere Wert einer sortierten Liste. Im Gegensatz zum Mittelwert wird er nicht durch Ausreißer verzerrt.
Praxisbeispiel
Bei Gehältern von 2000, 2200, 2100, 2300, 15000 Euro:
=MITTELWERT(...)ergibt 4720 Euro (verzerrt durch 15000)=MEDIAN(...)ergibt 2200 Euro (realistischerer Wert)
Die WENN-Funktion
Mit der WENN-Funktion prüfst du Bedingungen und reagierst unterschiedlich je nach Ergebnis. Sie ist eine der wichtigsten Funktionen in Excel.
Aufbau der WENN-Funktion
=WENN(Bedingung; Dann-Wert; Sonst-Wert)
| Parameter | Bedeutung |
|---|---|
| Bedingung | Der logische Test (wahr oder falsch) |
| Dann-Wert | Wird zurückgegeben wenn Bedingung wahr ist |
| Sonst-Wert | Wird zurückgegeben wenn Bedingung falsch ist |
Praxisbeispiel: Bestandene Prüfung
=WENN(A1>=50; "Bestanden"; "Nicht bestanden")
Wenn die Punktzahl in A1 mindestens 50 ist, erscheint “Bestanden”, sonst “Nicht bestanden”.
Verschachtelte WENN-Funktionen
Du kannst mehrere Bedingungen prüfen, indem du WENN-Funktionen verschachtelst:
=WENN(A1>=90; "Sehr gut";
WENN(A1>=75; "Gut";
WENN(A1>=50; "Bestanden"; "Nicht bestanden")))
Dies erzeugt eine Notenskala mit mehreren Stufen.
Tipp: IFS als Alternative
Ab Excel 2019 gibt es die Funktion IFS, die mehrere Bedingungen eleganter prüft:
=IFS(A1>=90; "Sehr gut"; A1>=75; "Gut"; A1>=50; "Bestanden"; WAHR; "Nicht bestanden")
Logische Verknüpfungen: UND, ODER, NICHT
Mit diesen Funktionen kombinierst du mehrere Bedingungen zu komplexeren Prüfungen.
UND - Alle Bedingungen müssen erfüllt sein
=UND(A1>0; A1<100)
Gibt WAHR zurück, wenn A1 größer als 0 UND kleiner als 100 ist. Sobald eine Bedingung nicht erfüllt ist, ist das Ergebnis FALSCH.
ODER - Mindestens eine Bedingung muss erfüllt sein
=ODER(A1="Ja"; A1="Yes")
Gibt WAHR zurück, wenn A1 “Ja” ODER “Yes” enthält. Es genügt, wenn eine der Bedingungen zutrifft.
NICHT - Kehrt das Ergebnis um
=NICHT(A1="")
Gibt WAHR zurück, wenn A1 NICHT leer ist. Die Funktion dreht WAHR zu FALSCH und umgekehrt.
Kombination mit WENN
Diese Funktionen werden oft mit WENN kombiniert:
=WENN(UND(B1>=18; B1<=65); "Erwerbsfähig"; "Nicht erwerbsfähig")
Dieser Ausdruck prüft, ob das Alter zwischen 18 und 65 liegt.
=WENN(ODER(C1="VIP"; C1="Premium"); 0,1; 0)
Dies gibt 10% Rabatt für VIP- oder Premium-Kunden.
Textfunktionen: Extrahieren und Bearbeiten
Mit Textfunktionen kannst du Zeichenketten zerlegen, bearbeiten und analysieren.
LINKS, RECHTS, TEIL - Text extrahieren
=LINKS(A1; 3) // Erste 3 Zeichen
=RECHTS(A1; 4) // Letzte 4 Zeichen
=TEIL(A1; 2; 5) // 5 Zeichen ab Position 2
Beispiel: Bei A1 = “Excel2024”
=LINKS(A1; 5)ergibt “Excel”=RECHTS(A1; 4)ergibt “2024”=TEIL(A1; 6; 4)ergibt “2024”
LÄNGE - Zeichenanzahl ermitteln
=LÄNGE(A1)
Gibt die Anzahl der Zeichen zurück. Bei “Hallo” ist das Ergebnis 5.
GLÄTTEN - Leerzeichen entfernen
=GLÄTTEN(A1)
Entfernt führende, nachfolgende und doppelte Leerzeichen. Aus ” Hallo Welt ” wird “Hallo Welt”.
GROSS, KLEIN, GROSS2 - Groß- und Kleinschreibung
=GROSS(A1) // ALLES GROSS
=KLEIN(A1) // alles klein
=GROSS2(A1) // Erster Buchstabe Groß
GROSS2 ist nützlich für Namen: Aus “MAX MUSTERMANN” wird “Max Mustermann”.
Textfunktionen: Verknüpfen und Aufteilen
Texte lassen sich in Excel zusammenfügen oder in Teile zerlegen.
VERKETTEN und Kaufmanns-Und
Zum Zusammenfügen von Texten gibt es mehrere Wege:
=VERKETTEN(A1; " "; B1)
=A1 & " " & B1
=TEXTKETTE(A1; " "; B1)
Alle drei Varianten verbinden den Inhalt von A1 und B1 mit einem Leerzeichen dazwischen. Das Kaufmanns-Und & ist oft am kürzesten.
TEXTVERKETTEN - Mit Trennzeichen
=TEXTVERKETTEN("; "; WAHR; A1:A5)
Verbindet alle Zellen von A1 bis A5 mit Semikolon als Trennzeichen. Der Parameter WAHR ignoriert leere Zellen.
TEXTSPLIT - Text aufteilen (Excel 365)
=TEXTSPLIT(A1; ";")
Teilt den Text an jedem Semikolon und verteilt die Teile auf mehrere Zellen. Aus “Apfel;Birne;Kirsche” werden drei separate Zellen.
Hinweis: TEXTSPLIT ist nur in Excel 365 verfügbar. In älteren Versionen nutze stattdessen “Daten” > “Text in Spalten”.
FINDEN und SUCHEN - Position ermitteln
=FINDEN("@"; A1) // Groß-/Kleinschreibung beachten
=SUCHEN("@"; A1) // Groß-/Kleinschreibung ignorieren
Gibt die Position eines Zeichens zurück. Nützlich um z.B. E-Mail-Adressen zu validieren.
Datums- und Zeitfunktionen
Excel speichert Datumsangaben als fortlaufende Zahlen. Der 1. Januar 1900 ist Tag 1. Diese Funktionen helfen beim Arbeiten mit Datumsangaben.
HEUTE und JETZT - Aktuelles Datum/Zeit
=HEUTE() // Aktuelles Datum (ohne Uhrzeit)
=JETZT() // Aktuelles Datum mit Uhrzeit
Diese Funktionen aktualisieren sich bei jeder Neuberechnung der Tabelle.
TAG, MONAT, JAHR - Bestandteile extrahieren
=TAG(A1) // Tag des Monats (1-31)
=MONAT(A1) // Monat (1-12)
=JAHR(A1) // Jahr (z.B. 2024)
Bei A1 = 15.03.2024 ergibt =TAG(A1) den Wert 15.
DATUM - Datum aus Teilen erstellen
=DATUM(2024; 12; 24)
Erstellt ein Datum aus Jahr, Monat und Tag. Das Ergebnis ist der 24.12.2024.
TAGE - Differenz berechnen
=TAGE(B1; A1)
Berechnet die Anzahl Tage zwischen zwei Datumsangaben. Bei A1 = 01.01.2024 und B1 = 15.01.2024 ergibt die Formel 14.
EDATUM - Datum verschieben
=EDATUM(A1; 3) // 3 Monate später
=EDATUM(A1; -6) // 6 Monate früher
Verschiebt ein Datum um eine bestimmte Anzahl Monate.
Zusammenfassung
Du hast in dieser Lerneinheit die Grundlagen von Formeln und Funktionen in Excel kennengelernt.
Das Wichtigste im Überblick
| Thema | Kernpunkte |
|---|---|
| Formelaufbau | Beginnt mit =, verwendet Operatoren und Zellbezüge |
| Zellbezüge | Relativ (A1), absolut (1), gemischt (1) |
| F4-Taste | Wechselt schnell zwischen Bezugsarten |
| Mathematik | SUMME, MITTELWERT, MIN, MAX, RUNDEN |
| Statistik | ANZAHL, MEDIAN, STABW |
| Logik | WENN, UND, ODER, NICHT |
| Text | LINKS, RECHTS, TEIL, VERKETTEN, TEXTSPLIT |
| Datum | HEUTE, DATUM, TAGE, EDATUM |
Praktische Tipps
- Nutze die F4-Taste zum schnellen Umschalten der Bezugsarten
- Achte auf deutsche Funktionsnamen und das Semikolon als Trennzeichen
- Verwende WENNFEHLER um Fehlermeldungen abzufangen
- Prüfe bei Fehlern zuerst die Schreibweise der Funktionsnamen
Ausblick
In der nächsten Lerneinheit Datenanalyse und -aufbereitung lernst du, wie du große Datenmengen sortierst, filterst und mit Pivot-Tabellen aussagekräftig zusammenfasst.