Unterrichtmaterialien
8. Funktionen
Beispiel: Budgetkontrolle eines Klimaschutzprojekts
Wir vergleichen geplante (Soll) und tatsächliche (Ist) Kosten für verschiedene Maßnahmen und bewerten, ob es eine Überschreitung oder Einsparung gibt.
1. Beispielhafte Tabelle (A1:D6)
Eine Kleinstadt führt an öffentlichen Gebäuden Energiesparmaßnahmen durch und plant dafür 1,4 Millionen € (Soll-Kosten). Nach Durchführung der Maßnahmen werden die tatsächlichen Kosten (Ist-Kosten) bestimmt.
| Maßnahme | Soll-Kosten (€) | Ist-Kosten (€) | Abweichung (€) |
|---|---|---|---|
| PV-Anlagen | 500.000 | 480.000 | ? |
| Dämmung | 300.000 | 350.000 | ? |
| LED-Beleuchtung | 200.000 | 180.000 | ? |
| Wärmepumpen | 400.000 | 420.000 | ? |
2. Berechnung der Abweichung mit WENN-Funktion
In Zelle D2 (erste Zeile für Abweichungen) können Sie folgende Formel nutzen:
= C2 - B2
Das zieht die Ist-Kosten von den Soll-Kosten ab.
- Positives Ergebnis = Kostenüberschreitung
- Negatives Ergebnis = Einsparung
Ziehen wir die Formel in D3:D5 nach unten (Autoausfüllen).
3. Bewertung der Abweichung mit WENN-Funktion
In Zelle E2 fügen wir eine Bewertung ein:
=WENN(D2>0;"Kostenüberschreitung";"Einsparung")
Diese Funktion sagt:
- Falls die Abweichung positiv ist, steht dort „Kostenüberschreitung“.
- Falls sie negativ ist, wird „Einsparung“ angezeigt.
Beziehungsweise allgemein: =WENN(Prüfung;Dann-Wert;Sonst-Wert)
Diese drei Bereiche werden durch Semikolon „;“ abgetrennt.
Dies kann man auch grafisch darstellen:
Ziehen wir die Formel in E3:E5 nach unten.
Zeichnerisch lassen sich WENN-Funktionen in einem Struktogramm darstellen:
Wahr und Falsch stehen in Tabellenkalkulationsprogrammen für Dann-Wert und Sonst-Wert.
Die Dann- bzw. Sonst-Seite lassen sich auch weiter aufgliedern. Man nennt das dann verschachtelte WENN-Funktion.
4. Nutzung von SVERWEIS für schnelle Analyse
Falls wir eine bestimmte Maßnahme analysieren möchten, kann SVERWEIS helfen.
Angenommen, in Zelle G1 gibt die Benutzerin eine Maßnahme ein (z. B. „Wärmepumpen“). In Zelle H1 möchten wir die geplanten Kosten automatisch anzeigen:
=SVERWEIS(G1;A2:D5;2;FALSCH)
Falls wir die tatsächlichen Kosten wollen, ändern wir die Spaltennummer auf 3:
=SVERWEIS(G1;A2:D5;3;FALSCH)
Falls wir die Abweichung wollen, nutzen wir Spalte 4:
=SVERWEIS(G1;A2:D5;4;FALSCH)
Allgemein:
Was wird gesucht? Suchkriterium
Wo wird gesucht? Matrix oder Tabelle
In welcher Spalte wird gesucht? Spaltenindex
Wird ungenau gesucht? Bereich-Verweis Standard oder 1: Ja, also ungenau
0: Nein, also genaue Suche!
=SVERWEIS(Suchkriterium; Matrix; Spaltenindex; Bereich-Verweis)
Auch der genaue SVERWEIS kann logisch dargestellt werden:
Ergebnis
- Die Tabelle zeigt automatisch, wo Kosten überschritten oder eingespart wurden.
- Mit SVERWEIS können einzelne Maßnahmen schnell überprüft werden.
- Die WENN-Funktion macht Abweichungen sofort sichtbar.
Bedingte Formatierung in Excel kann die Abweichungen farblich hervorheben!
Beispiel bei obiger Tabelle:
Falls Ist > Soll => Abweichung (Soll-Ist) kleiner 0: roter Hintergrund, ansonsten grüner Hintergrund
Zwei Regeln: 1. Wert <0 roter Hintergrund. 2. Wert >=0 grüner Hintergrund.
Falsche Regel, zuviele Regeln: Unter „Regeln verwalten“ kann man löschen oder bearbeiten.