1. Zellenbezüge
Mit Excel wird (meistens) nicht mit Zahlen, sondern mit Zellenbezügen (Wert in einer Zelle) gerechnet. Wir unterscheiden zwischen variablem, absolutem und gemischtem Bezug.
1.1 Absoluter Bezug
Beim absoluten Bezug bezieht sich die Formel auf eine Zelle (Zeile und Spalte werden mit dem $ Zeichen fixiert).
Spalte A – Grundgehalt
A3 – Name – A4 – Steiner – A5 – Frank – A6 Müller – A7 – Hofer
Spalte B – 3000
B3 – Provision – B4– 300 – B5 -280 – B6 – 240 – B7 – 150
Spalte C
C3 – Grundgehalt und Provision
1.2 Gemischter Bezug
In diesem Beispiel fixieren wir als Erstes die Spalte $B mit den Prozenten. Beim Kopieren der Formel in C3 werden die 40% auf die Spalten D, E und F angepasst. Mit $2 fixieren wir den Gesamtwert des Projektes. Beim Kopieren wird die Formel in C3 auf alle Zellen angepasst.
Spalte A
A2 – Teilhaber – A3 – Fischer – A4 – Wagner – A5 – Bauer – A6 – Steiner – A7 – Kontrolle
Spalte B
B2 – Anteil – B3 – 40% – B4 – 25% – B5 – 20% – B6 – 15%
Spalte C – Projekt 1
Spalte D – Projekt 2
Spalte E – Projekt 3
Spalte F – Projekt 4
4 Funktionen
Mit den Funktionen =SUMME, =PRODUKT und =MITTELWERT haben wir uns schon befasst. Wir wollen noch einmal ein Beispiel mit WENN lösen.
4.1 WENN
Bei WENN wird der Wert einer Zelle mit einem anderen Wert verglichen. Erfüllt der Wert in der Zelle eine Bedingung, z.B. grösser, kleiner oder gleich als ein anderer Wert, wir eine Aussage gemacht.
In diesem Beispiel wird der Umsatz eines Kunden innerhalb eines festgelegten Zeitraums mit dem Basisumsatz CHF 10’000 verglichen. Ist der Wert grösser/gleich 10’000 soll in der Tabelle die Aussage «Ja» erscheinen. Ansonsten wird «Nein» angezeigt. Erstellen Sie die Formel mit dem Basisumsatz 10’000 in der Zelle B7 (absoluter Bezug).
Spalte A – Kunde
A2 – Wagner – A3 – Frisch – A4 – Gubser – A5 – Heller – A7 – Basisumsatz
Spalte B – Umsatz in CHF
B2 – 15000 – B3 – 9800 – B4 – 10800 – B5 – 7500 – B7 – 10000
Spalte C – Gutschein Ja / Nein
- Die Formel in C2 lautet: ___________________________________________
4.2 ZÄHLENWENN
Bei ZÄHLENWENN werden WERTE, die bestimmte Kriteren erfüllen, zusammenengezählt. Es kann sich dabei um Zahlen oder auch Auswertungen einer WENN Funktion handeln.
In diesem Beispiel soll angezeigt werden, bei wie vielen Produktgruppen, grösser/gleich 50 Kg, und bei wie vielen weniger als 50 Kg verkauft wurden.
Spalte A – Produkte
A2 – Äpfel – A3 – Orangen – A4 – Pfirsiche – A5 – Birnen – A7 – Mehr oder gleich 50 Kg – A9 – Weniger als 50 Kg
Spalte B – Verkauf in Kg
- Die Formel in B7 lautet: =ZÄHLENWENN(B2:B5;“>=50″)
Fügen Sie eine weitere Zeile ein: Produkt Zwetschgen, Verkauf in Kg 49. Wie verändern sich die Resultate? Wenn Sie den Wert bei Orangen auf 50 setzen müssen Sie eine Änderung in der Formel vornehmen?
5 Statistik
Im nächsten Beispiel verwenden wir die Funktionen =MITTELLWERT und =ZÄHLENWENN, um eine Statistik über das Wetter zu erstellen. Zusätzlich rechnen wir noch mit Prozenten.
Anfang Juli 2020 wurden folgende Temperaturen notiert.
Erstellen Sie eine Tabelle, die folgende Informationen anzeigt:
- An wie vielen Tagen wurde eine Temperatur von über 26° C gemessen? (=ZÄHLENWENN)
- Tage mit einer Temperatur über 26° C sollen in der Tabelle als «Ja» angezeigt werden.
- Temperaturen bis 26° C sollen als «Nein» angezeigt werden. (=WENN)
- Wie lautet die Formel in C2?
- Erweitern Sie die Tabelle.
- Wie hoch sind die prozentualen Abweichungen der Durchschnittstemperaturen von 2017 bis 2019 in Zeile 9 im Vergleich mit der Durchschnittstemperatur (100%) in 2020?
- Die Formeln in D10 und C11 lauten?
- Wenden Sie in der Spalte C die Funktion =ZÄHLENWENN für die Anzahl Hitzetage «Ja» an.
6. SUMMEWENN
Bei SUMMEWENN arbeiten wir mit einem Kriterium und 2 Bereichen. Am einfachsten lässt sich das mit einem Beispiel erklären. Es geht darum, die Anzahl einzelner Produktgruppen anzuzeigen.
Die Formel in E3 wird folgendermassen erstellt:
- =SUMMEWENN(
- der Bereich Artikel von B2:B10 wird markiert
- jetzt folgt das Kriterium „T-Shirt„
- wir geben den Summe Bereich C2:C10 an
- die ganze Formel lautet: =SUMMEWENN(B2:B10;“T-Shirt“;C2:C10)
- für Jeans, Kleid und Sneaker muss die Formel angepasst werden
- in der Zeile 12 kontrollieren wir in der Spalte C und E mit =SUMME
