Funktionen

lernfeld funktionen
das sollte ich können ...
Mit Funktionen in die Liga der Champions vorstoßen
Excel verfügt über ca. 350 Funktionen.
Die wichtigsten davon habe ich hier
zusammengestellt. Einige davon haben
Sie ja schon kennen gelernt:
Summe()
Mittelwert()
Max() und Min()
Anzahl()
Wenn()
Wenn, verschachteltes Wenn, Wenn mit
Und- und Oder-Bedingungen
ZählenWenn() und Anzahl()
MittelwertWenn() und SummeWenn()
Runden()
SVerweis()
Bitte bedenken Sie, dass dieses Lernfeld
nicht einfach ist. Sie müssen zwei Fähigkeiten erwerben:
1. Funktionen einsetzen (Funktionsname,
Schreibweise, Syntax)
2. Situationen beurteilen: Wann setze ich
welche Funktion ein?
Index() und Vergleich()
Datumsfunktionen: Wochentag(), Text(),
Tag(), Monat(), Jahr(), Heute(), Jetzt()
Textfunktionen
Häufigkeit()
G-04
mittelwertwenn
...und summewenn
das sollte ich können ...
Situationen erkennen, in
denen MittelwertWenn() und
SummeWenn() einsetzbar sind.
Beispiele für SummeWenn:
Beide Funktionen praktisch
einsetzen.
Der durchschnittliche Umsatz an
Samstagen
Die Summe, wenn eine
Bedingung erfüllt ist!
Wie viele Tage war ein bestimmter Mitarbeiter krank?
=summewenn(A2:A43; “Wallmann“; B2:B43)
Mitarbeiter
Wie viele Tage waren Mitarbeiter höchstens 3 Tage
krank?
=summewenn(B2:B43; “<=3“; B2:B43)
=mittelwertwenn(
A
1
B
Datum
Das Beispiel:
C
Wochentag
Suchbereich
B2:B122
Umsatz
2
Sa, 01.09.
6
12.573
3
Mo, 03.09.
1
1.897
4
Di, 04.09.
2
3.212
5
Mi, 05.09.
3
975
6
Do, 06.09.
4
2.112
7
Fr, 07.09.
5
9.583
;
Kriterium
6
;
Mittelwertbereich
C2:C122
)
Umsatz an bestimmten
Wochentagen
Ich verfüge über eine Liste der einzelnen Tage mit dem jeweiligen
Tagesumsatz von September bis November. Ganz besonders
interessiert mich der durchschnittliche Umsatz an Samstagen.
=mittelwertwenn( B2:B122 ; 6 ; C2:C122 )
In der Spalte B wird nun nach dem Wert 6 (6=Samstag) gesucht.
Wird eine 6 gefunden, dann wird der Umsatz dieser Zeile (Spalte
C) für die Mittelwertberechnung verwendet.
Im Unterschied zu:
Wie oft waren Mitarbeiter höchstens 3 Tage krank?
=zählenwenn(B2:B43; “<=3“)
Krankenstandtage
Wallmann
3
Stadler
2
Bartos
5
Wallmann
4
Welche Frage führt zu SummeWenn?
=summewenn(A2:A5;“Wallmann“,B2:B5)
Ich probiere einfach, ob sich die Frage “Wie viel(e) .....
in Summe?“ stellen lässt.
- Wie viele Tage in Summe?
- Wie viel Umsatz in Summe?
Wenn die Daten des Kriterienbereichs (A2:A5) die Bedingung “Wallmann“ erfüllen, dann werden die Werte aus B2:B5
summiert. Hier lautet das Ergebnis also 3 + 4 = 7.
=summewenn(
Suchbereich
A2:A5
;
Kriterium
“Wallmann“
;
Summenbereich
B2:B5
)
2-15
G-06
die funktion sverweis
das sollte ich können ...
Situationen beschreiben, in
denen ich den SVerweis einsetze.
Den SVerweis anwenden.
Drei Bedingungen beschreiben,
die erfüllt sein müssen, um
den SVerweis einsetzen
zu können.
Was mache ich mit dem
SVerweis?
Der SVerweis zählt zu den stärksten Funktionen in Excel. Ich kann ihn sehr vielfältig
einsetzen. Immer dann, wenn in einer Spalte (hier: Reihe im Kino) ein Wert gesucht
wird und aus einer Spalte daneben (hier: Preise) ein Wert zurückgegeben werden soll,
löse ich das mit dem SVerweis. Das Interessante dabei: Der Wert, den ich suche (hier:
die Reihe, in der ich sitzen möchte), darf auch ein “Zwischenwert“ sein.
Kosten einer Kinokarte:
Abhängig von der Reihe, in der ich
sitzen möchte, kosten die Karten
unterschiedlich viel.
ab Reihe
1
5
12
18
Preise
5,50
6,50
8,00
9,00
€
€
€
€
Ich sitze in der Reihe
5
Der Preis, den ich bezahlen muss,
ist leicht zu finden. Ich suche in
der Spalte 1 (linke Spalte) meine
Reihe ...
... und lese in der Spalte 2 (rechte
Spalte) den Preis ab.
6,50 €
Ich sitze in der Reihe
16
Die Reihe 16 kommt in meiner Aufstellung nicht vor. Daher suche ich
in der Spalte 1 den nächst kleineren
Wert, ...
... finde 12 und lese den Preis in der
Spalte 2 ab.
8,00 €
=sverweis(
Das Beispiel:
Der Preis der
Kinokarte
Entscheider
B7
;
Bereich
A2:B5
A
;
B
ab Reihe
Preise
In der Zelle B8 wird der Preis der Karte berechnet. Der Preis
hängt von der Zelle B7 (also der Reihe) ab, in der sich der
Sitzplatz befindet.
2
1
5,50 €
3
5
6,50 €
Als Bereich übergebe ich A2:B5. Die Funktion SVerweis sucht
den Entscheider in Zelle B7 immer in der ersten Spalte. Hier
also in A2 bis A5.
4
12
8,00 €
5
18
9,00 €
7
Reihe:
15
8
Kartenpreis:
8,00 €
Die Bedingungen für den SVerweis
Damit ich den SVerweis anwenden kann, muss ich 3 Bedingungen herstellen:
2
)
C
1
Die Rückmeldung möchte ich aber aus der Spalte 2 erhalten.
In dieser Spalte stehen ja die Preise. Also übergebe ich als
letzten Wert die 2.
Spalte
6
1
Die Daten der ersten Spalte müssen aufsteigend
sortiert sein. Nur so kann Excel den gesuchten Wert
finden.
2
Bei den Daten der ersten Spalte muss es sich um
Zahlen handeln. (Text nur in Ausnahmefällen!)
3
Bei den Daten der ersten Spalte muss es sich um
“Ab-Werte“ handeln. “Ab der Reihe“, “ab dem Preis“,
“ab der Menge“ usw.
2-19