Microsoft Excel 2013 · 1.2 ODER © Claudio Cannatella Microsys IT AG 9 1.2 ODER Mit der Funktion...
Transcript of Microsoft Excel 2013 · 1.2 ODER © Claudio Cannatella Microsys IT AG 9 1.2 ODER Mit der Funktion...
Microsys Information
Technology AG
Microsoft
Excel 2013
Fortgeschrittene
© Claudio Cannatella Microsys IT AG 2
Microsoft Excel 2013 Fortgeschrittene
Autor: Claudio Cannatella
Inhaltliches Lektorat: Christine Zbinden / Joshua Grütter
© Microsys Information Technology AG, Aarau, 062 823 70 90
Internet: www.microsys.ch
Alle Rechte vorbehalten. Kein Teil des Werkes darf in irgend einer Form
(Druck, Fotokopie, Mikrofilm oder einem anderen Verfahren) ohne schriftliche
Genehmigung des Herausgebers reproduziert oder unter Verwendung elekt-
ronischer Systeme verarbeitet, vervielfältigt oder verbreitet werden.
Dieses Buch wurde mit grosser Sorgfalt erstellt und geprüft. Trotzdem kön-
nen Fehler nicht vollkommen ausgeschlossen werden. Verlag, Herausgeber
und Autoren können für fehlerhafte Angaben und deren Folgen weder eine
juristische Verantwortung, noch irgendeine Haftung übernehmen.
© Claudio Cannatella Microsys IT AG 3
1 Logische Funktionen ........................................................................................................................................... 8
1.1 UND .................................................................................................................................................................................. 8
1.2 ODER ............................................................................................................................................................................... 9
1.3 XODER ........................................................................................................................................................................... 10
1.4 WENN ............................................................................................................................................................................. 11
1.5 WENN Verschachtelung ........................................................................................................................................... 14
1.6 ISTFEHLER/WENNFEHLER ....................................................................................................................................... 16
2 Statistische Funktionen II ................................................................................................................................... 18
2.1 ZÄHLENWENN............................................................................................................................................................ 18
2.2 ZÄHLENWENNS ......................................................................................................................................................... 19
2.3 SUMMEWENN ............................................................................................................................................................. 20
2.4 SUMMEWENNS .......................................................................................................................................................... 21
2.5 KGRÖSSTE .................................................................................................................................................................... 23
2.6 KKLEINSTE ....................................................................................................................................................................24
2.7 RANG.GLEICH ............................................................................................................................................................. 25
2.8 RANG.MITTELW .......................................................................................................................................................... 26
3 3D-Bezüge .......................................................................................................................................................... 28
3.1 Grundlagen .................................................................................................................................................................. 28
4 Diagramme .......................................................................................................................................................... 31
4.1 Balken aus Kreis .......................................................................................................................................................... 31
4.2 Verbunddiagramm und Diagramme mit Sekundärachse .............................................................................. 33
4.3 Spezielle Diagramme ................................................................................................................................................ 38
4.3.1 Diagramme mit speziellen Symbolen oder Füllflächen .................................................................................. 38
4.3.2 Diagramme mit logarithmischer Skalierung ....................................................................................................... 41
4.3.3 Diagramm mit fehlenden Werten .........................................................................................................................43
4.3.4 Diagramm mit variabler Achsenskalierung ........................................................................................................ 44
4.3.5 XY-Diagramme ...........................................................................................................................................................45
4.3.6 Blasendiagramm .........................................................................................................................................................48
4.3.7 Kursdiagramme........................................................................................................................................................... 50
© Claudio Cannatella Microsys IT AG 4
5 Schutz anwenden .............................................................................................................................................. 52
5.1 Excel-Datei schützen und verschlüsseln .............................................................................................................. 52
5.2 Excel-Datei verschlüsseln .........................................................................................................................................54
5.3 Excel-Datei als abgeschlossen kennzeichnen .................................................................................................... 55
5.4 Excel-Datei Zugriff einschränken ........................................................................................................................... 56
5.5 Excel-Datei digital signieren .................................................................................................................................... 57
5.6 Tabelle schützen ......................................................................................................................................................... 58
5.7 Arbeitsmappe schützen ........................................................................................................................................... 61
6 Spezielles Kopieren ........................................................................................................................................... 62
6.1 Transponieren ............................................................................................................................................................. 62
6.2 Leerzellen überspringen ..........................................................................................................................................64
7 Was-wäre-wenn-Analysen ............................................................................................................................... 65
7.1 Zielwertsuche ............................................................................................................................................................... 65
7.2 Szenario-Manager ..................................................................................................................................................... 68
7.3 Datentabelle................................................................................................................................................................. 72
8 Externe Daten abrufen...................................................................................................................................... 75
8.1 Textdateien importieren ........................................................................................................................................... 75
8.2 Webinformationen importieren ............................................................................................................................. 79
9 Konsolidieren ....................................................................................................................................................... 81
10 Pivot ...................................................................................................................................................................... 83
10.1 PivotTable ..................................................................................................................................................................... 83
10.1.1 Gruppierung ................................................................................................................................................................ 85
10.1.2 Erweiterung der PivotTable ..................................................................................................................................... 87
10.1.3 Layout ............................................................................................................................................................................ 87
10.1.4 Details ein- und ausblenden ................................................................................................................................... 89
10.1.5 Feldelemente verschieben ....................................................................................................................................... 89
10.1.6 Wertfeldeinstellungen ............................................................................................................................................... 90
10.1.7 Felder umbenennen .................................................................................................................................................. 91
10.1.8 Berechnetes Feld ........................................................................................................................................................ 91
10.1.9 Berechnetes Element................................................................................................................................................. 95
10.1.10 PivotTable verschieben ............................................................................................................................................. 96
© Claudio Cannatella Microsys IT AG 5
10.1.11 PivotTable löschen ..................................................................................................................................................... 96
10.2 PivotChart ..................................................................................................................................................................... 97
11 Matrixformel und Matrixfunktion .................................................................................................................... 99
11.1 Grundlagen Matrix ..................................................................................................................................................... 99
11.1.1 Was ist eine Matrix? ................................................................................................................................................... 99
11.2 Erweiterte Techniken ............................................................................................................................................... 105
11.2.1 SVERWEIS / WVERWEIS ......................................................................................................................................... 108
11.2.2 INDEX ............................................................................................................................................................................ 113
11.2.3 VERGLEICH .................................................................................................................................................................. 115
11.2.4 INDIREKT ...................................................................................................................................................................... 117
12 PowerPivot ......................................................................................................................................................... 119
12.1 Grundlagen PowerPivot .......................................................................................................................................... 119
12.1.1 Installation von PowerPivot .................................................................................................................................... 119
12.1.2 Erste Schritte ............................................................................................................................................................... 121
12.2 Erweiterte Techniken ............................................................................................................................................... 124
12.2.1 Beziehungen .............................................................................................................................................................. 124
12.2.2 Daten zusammenführen ........................................................................................................................................ 127
13 Statistische Funktionen III ............................................................................................................................... 135
13.1 MITTELWERTA ........................................................................................................................................................... 135
13.2 MINA ............................................................................................................................................................................ 136
13.3 MAXA ........................................................................................................................................................................... 137
13.4 MITTELWERTWENN ................................................................................................................................................ 138
13.5 MITTELWERTWENNS .............................................................................................................................................. 139
13.6 HÄUFIGKEIT ............................................................................................................................................................... 140
13.7 TREND ......................................................................................................................................................................... 142
13.8 RMZ .............................................................................................................................................................................. 144
13.9 BW ................................................................................................................................................................................ 146
13.10 ZW ................................................................................................................................................................................ 148
© Claudio Cannatella Microsys IT AG 6
14 Mathematische Funktionen II ........................................................................................................................ 149
14.1 ABS ............................................................................................................................................................................... 149
14.2 PRODUKT ................................................................................................................................................................... 149
14.3 TEILERGEBNIS ............................................................................................................................................................. 151
14.4 ZUFALLSBEREICH ..................................................................................................................................................... 153
14.5 ZUFALLSZAHL ........................................................................................................................................................... 154
15 Informationsfunktionen .................................................................................................................................. 155
15.1 ISTLEER ........................................................................................................................................................................ 155
15.2 ZELLE ........................................................................................................................................................................... 156
16 Datum & Zeit Funktionen II ........................................................................................................................... 160
16.1 EDATUM ..................................................................................................................................................................... 160
16.2 MONATSENDE ........................................................................................................................................................... 161
16.3 TAGE360 ..................................................................................................................................................................... 162
16.4 ARBEITSTAG ............................................................................................................................................................... 163
16.5 ARBEITSTAG.INTL ..................................................................................................................................................... 164
16.6 NETTOARBEITSTAGE .............................................................................................................................................. 167
16.7 NETTOARBEITSTAGE.INTL..................................................................................................................................... 168
17 Stichwortverzeichnis ......................................................................................................................................... 170
© Claudio Cannatella Microsys IT AG 7
Symbole im Lehrmittel
Dieses Symbol kennzeichnet Aufgaben, welche das Gelernte
vertiefen.
Dieses Symbol kennzeichnet Übungen, welche die Erklärungen
mit einem Praxisbeispiel unterstützen.
1.1 UND
© Claudio Cannatella Microsys IT AG 8
1 Logische Funktionen
1.1 UND
Mit der Funktion UND können Sie mehrere Kriterien überprüfen. Excel gibt Ihnen den
Wert WAHR oder FALSCH zurück. Die Funktion UND wird häufig verwendet, um den Nut-
zen anderer Funktionen zu erhöhen, mit denen Wahrheitsprüfungen durchgeführt wer-
den. Bei dieser Funktion müssen alle Prüfungen WAHR sein, nur dann wird Excel den Wert
WAHR ausgeben.
Syntax
UND(Wahrheitswert1;[Wahrheitswert2];…)
Erklärung
Wahrheitswert1 Erforderlich. Die erste zu prüfende Bedingung, die entwe-
der als WAHR oder als FALSCH bewertet werden kann.
Wahrheitswert2 Optional. Zusätzlich zu prüfende Bedingungen, die entwe-
der als WAHR oder als FALSCH bewertet werden können.
Es können maximal 255 Bedingungen geprüft werden.
Beispiel
Erstellen Sie die unten dargestellte Tabelle. Erfassen Sie in der Spalte Prüfung eine Funk-
tion, welche die folgenden Bedingungen prüft: Sind die Werte in der Spalte A grösser als
die Werte aus der Spalte B und sind zusätzlich die Werte aus der Spalte C grösser als die
Werte aus der Spalte D, soll Excel dies mit WAHR quittieren.
Funktion in der Zelle E2:
=UND(A2>B2;C2>D2)
1.2 ODER
© Claudio Cannatella Microsys IT AG 9
1.2 ODER
Mit der Funktion ODER können Sie mehrere Kriterien überprüfen. Excel gibt Ihnen den
Wert WAHR oder FALSCH zurück. Die Funktion ODER wird häufig verwendet, um den
Nutzen anderer Funktionen zu erhöhen, mit denen Wahrheitsprüfungen ausgeführt wer-
den. Bei dieser Funktion genügt es, wenn eine der beiden Prüfungen WAHR ist, dann wird
Excel den Wert WAHR ausgeben.
Syntax
ODER(Wahrheitswert1;[Wahrheitswert2];…)
Erklärung
Wahrheitswert1 Erforderlich. Die erste zu prüfende Bedingung, die entwe-
der als WAHR oder als FALSCH bewertet werden kann.
Wahrheitswert2 Optional. Zusätzlich zu prüfende Bedingungen, die entwe-
der als WAHR oder als FALSCH bewertet werden können.
Es können maximal 255 Bedingungen geprüft werden.
Beispiel
Verwenden Sie die oben erstellte Tabelle. Erfassen Sie in der Spalte Prüfung eine Funk-
tion, welche die folgenden Bedingungen prüft: Sind die Werte in der Spalte A grösser als
die Werte aus der Spalte B oder sind die Werte aus der Spalte C grösser als die Werte aus
der Spalte D, soll Excel dies mit WAHR quittieren. Erkennen Sie den Unterschied zur Funk-
tion UND?
Funktion in der Zelle E2:
=ODER(A2>B2;C2>D2)
1.3 XODER
© Claudio Cannatella Microsys IT AG 10
1.3 XODER
Eine neue Funktion in Excel 2013 ist die Funktion XODER. Bei einer ungeraden Anzahl an
WAHR Aussagen wird diese als WAHR dargestellt.
Syntax
XODER(Wahrheitswert1;[Wahrheitswert2];…)
Erklärung
Wahrheitswert1 Erforderlich. Die erste zu prüfende Bedingung, die entwe-
der als WAHR oder als FALSCH bewertet werden kann.
Wahrheitswert2 Optional. 1 bis 254 zu prüfende Bedingungen, die entwe-
der WAHR oder FALSCH sein können und Wahrheitswerte,
Matrizen oder Bezüge darstellen können.
Beispiel
Geben Sie in einem leeren Tabellenblatt die folgende Funktion ein:
=XODER(6>8;7>9;8>5)
Bei den drei Wahrheitswerten, die Sie soeben eingeben haben, ist nur der Wahrheitswert
3, also 8 grösser 5, korrekt. Das heisst, es ist nur eine ungerade Anzahl an WAHR-Werten
vorhanden. Somit wird Excel den Wert WAHR ausgeben. Korrigieren Sie den ersten Wahr-
heitswert auf 9>8. Nun sind zwei Bedingungen WAHR. Zwei ist ein gerader Wert und so-
mit wird der Wert FALSCH ausgegeben.
1.4 WENN
© Claudio Cannatella Microsys IT AG 11
1.4 WENN
Die Funktion WENN überprüft, ob Werte oder Formeln bestimmte Bedingungen erfüllen.
Aufgrund des ermittelten Ergebnisses können verschiedene Anweisungen ausgeführt wer-
den.
Syntax
WENN(Prüfung;[Dann_Wert];[Sonst_Wert])
Erklärung
Prüfung Erforderlich. Ein beliebiger Wert oder Ausdruck, der WAHR
oder FALSCH sein kann. Beispielsweise ist A10=100 ein logi-
scher Ausdruck. Wenn der Wert in der Zelle A10 gleich 100
ist, ist der Ausdruck WAHR. Sonst ist der Ausdruck FALSCH.
Dieses Argument kann einen beliebigen Vergleichsberech-
nungsoperator verwenden.
Dann_Wert Optional. Der Wert, der zurückgegeben werden soll, wenn
das Argument Prüfung WAHR ergibt. Wenn der Wert die-
ses Arguments z. B. die Zeichenfolge "Im Budget" ist und
die Prüfung mit WAHR bewertet wird, gibt die WENN-
Funktion den Text "Im Budget" zurück. Wenn Prüfung
WAHR ergibt und das Argument Dann_Wert ausgelassen
wird (also nach dem Argument Prüfung nur ein Semikolon
angegeben wird), gibt die WENN-Funktion den Wert 0
(Null) zurück. Um den Begriff WAHR anzuzeigen, geben Sie
den Wahrheitswert WAHR für das Argument Dann_Wert
an.
Sonst_Wert Optional. Der Wert, der zurückgegeben werden soll, wenn
das Argument Prüfung FALSCH ergibt. Wenn der Wert die-
ses Arguments z. B. "Budget überschritten" lautet und die
Prüfung mit FALSCH bewertet wird, gibt die WENN-Funk-
tion den Text "Budget überschritten" zurück. Wenn Prüfung
FALSCH ergibt und das Argument Sonst_Wert ausgelassen
wird (also kein Semikolon auf das Argument Sonst_Wert
folgt), gibt die WENN-Funktion den Wahrheitswert FALSCH
zurück. Wenn Prüfung FALSCH ergibt und das Argument
Sonst_Wert einen leeren Wert enthält (d. h., dass nur ein
Semikolon auf das Argument Sonst_Wert folgt), gibt WENN
den Wert 0 (Null) zurück. Um den Begriff FALSCH anzuzei-
gen, geben Sie den Wahrheitswert FALSCH für das Argu-
ment Sonst_Wert an.
1.4 WENN
© Claudio Cannatella Microsys IT AG 12
Beispiel
Erstellen Sie die unten dargestellte Tabelle: Sie möchten in der Spalte C den Rabatt anzei-
gen, der für den entsprechenden Betrag zu gewährleisten ist.
Wenn der bestellte Wert mehr als 5‘000 beträgt, erhält der Kunde 5 % Rabatt, ansonsten
gibt es 4 %.
Formatieren Sie den Bereich C2 bis C6 in Prozent.
Funktion in der Zelle C2:
=WENN(A2*B2>5000;5%;4%)
In den Kapiteln UND und ODER auf Seite 8/9 haben Sie interessante Funktionen kennen-
gelernt, die Sie in die Funktion WENN einbinden können.
Verwenden Sie für die folgenden Beispiele die oben erstellte Tabelle:
Beispiel 1 (UND-Funktion)
Sie möchten nur 5 % Rabatt gewährleisten, wenn der Kunde mehr als 5 Stück bestellt hat
und der Umsatz grösser als 5‘000 ist.
Es müssen zwei Bedingungen erfüllt werden.
Funktion in der Zelle C2:
=WENN(UND(A2>5;A2*B2>5000);5%;4%)
Sie haben die Funktion UND für die Wahrheitsprüfung 1 verwendet.
Beispiel 2 (ODER-Funktion)
Sie möchten, dass 5 % Rabatt gewährleistet wird, wenn der Kunde entweder mehr als 5
Stück bestellt hat oder der Umsatz grösser als 5‘000 ist.
Es muss nur eine der beiden Bedingungen erfüllt werden.
Funktion in der Zelle C2:
=WENN(ODER(A2>5;A2*B2>5000);5%;4%)
1.4 WENN
© Claudio Cannatella Microsys IT AG 13
Beispiel 3 (ODER- / UND-Funktion)
Sie möchten nur 5 % Rabatt gewährleisten, wenn der Kunde mehr als 5 Stück bestellt hat
und der Umsatz grösser als 5‘000 ist. Sollte eines der Kriterien nicht erfüllt werden, soll die
Möglichkeit bestehen, dies mit einem Code zu übersteuern. Als Code verwenden Sie den
Buchstaben "X".
Passen Sie die Tabelle wie folgt an:
Funktion in der Zelle D2:
=WENN(ODER(UND(A2>5;A2*B2>5000);C2="X");5%;4%)
Erklärung
Die Klammern werden von innen nach aussen aufgelöst. Zuerst beachtet Excel die Funk-
tion UND. Wird eine der beiden Prüfungen (A2>5 und A2*B2>5000) nicht erfüllt, wird der
nächsten Klammer Beachtung geschenkt. In diesem Fall der Funktion ODER. Sollte in C2
ein X stehen, wird 5 % ausgegeben, sonst 4 %.
1.5 WENN Verschachtelung
© Claudio Cannatella Microsys IT AG 14
Aufgabe 1
1. Öffnen Sie die Datei Wennaufgabe.xlsx.
2. Die Datei besteht aus einem Tabellenblatt mit fünf Aufgaben.
3. Die Aufgabenstellung ist in den verschiedenen Textfeldern ersichtlich.
Aufgabe 2
1. Öffnen Sie die Datei Terminüberwachung.xlsx.
2. In der Spalte B soll mit einer Funktion nur dann das aktuelle Datum angezeigt wer-
den, wenn in der Spalte A die entsprechende Zelle nicht leer ist.
3. In der Spalte C wird die Differenz in Tagen berechnet, wenn die entsprechende Zelle
in der Spalte A nicht leer ist.
4. In der Spalte D geben Sie das Datum der Zahlung ein. Erfassen Sie 2 Zahlungen.
5. In der Spalte E sollen zwei Ausrufezeichen erscheinen, wenn der Wert in der Spalte C
grösser als 30 ist und die Zelle in der Spalte D leer ist.
6. Berechnen Sie die Spalten B, C und E. Kopieren Sie die Funktion bis zur Zeile 20.
7. In der Spalte A wurde das Rechnungsdatum erfasst. Passen Sie dieses zur Überprü-
fung an.
1.5 WENN Verschachtelung
Bevor Sie bei einer WENN-Funktion den Sonst-Wert ausgeben, können Sie weitere Prü-
fungen vornehmen und diese einen Dann-Wert ausgeben lassen.
Sie können bis zu 64 WENN-Funktionen ineinander verschachteln. Anstelle der Sonst-An-
weisung wird eine neue WENN-Funktion eingefügt.
WENN(Wahrh.;Dann_Wert;Sonst_Wert)
WENN(Wahrh.;Dann_Wert;Sonst_Wert)
WENN(Wahrh.;Dann_Wert;Sonst_Wert)
WENN(…
1.5 WENN Verschachtelung
© Claudio Cannatella Microsys IT AG 15
Beispiel
Erstellen Sie die folgende Tabelle:
In der Zelle D3 wird die Zelle B3 mit der Zelle C3 multipliziert.
Ist das Resultat der Multiplikation zwischen 60 und 70 (71 und 80, 81 und 90, 91 und 100)
soll in der Zelle B5 folgender Text angezeigt werden:
Die Zahl liegt zwischen 60 und 70
Die Zahl liegt zwischen 71 und 80
Die Zahl liegt zwischen 81 und 90
Die Zahl liegt zwischen 91 und 100
Bei einem anderen Resultat soll folgender Text erscheinen:
Die Zahl ist kleiner als 60 oder grösser als 100
Funktion in B5:
=WENN(UND(D3>=60;D3<=70);"Die Zahl liegt zwischen 60 und
70";WENN(UND(D3>70;D3<=80);"Die Zahl liegt zwischen 71 und
80";WENN(UND(D3>80;D3<=90);"Die Zahl liegt zwischen 81 und
90";WENN(UND(D3>90;D3<=100);"Die Zahl liegt zwischen 91 und 100";"Die Zahl ist klei-
ner als 60 oder grösser als 100"))))
Aufgabe
1. Öffnen Sie die Datei Abgestufter Rabatt.xlsx.
2. In der Zelle D4 multiplizieren Sie die Zelle B4 mit der Zelle C4.
3. In der Zelle B7 lassen Sie sich nach den folgenden Kriterien den Rabatt in Prozent
anzeigen:
Anzahl grösser oder gleich 100 erhält er 5 %
Anzahl grösser oder gleich 500 erhält er 7.5 %
Anzahl grösser oder gleich 1000 erhält er 10 %
Anzahl grösser oder gleich 5000 und weniger als 100'000 erhält er 12.5 %
Alle anderen Werte erhalten keinen Rabatt.
4. Berechnen Sie anschliessend die Zelle D8.
1.6 ISTFEHLER/WENNFEHLER
© Claudio Cannatella Microsys IT AG 16
1.6 ISTFEHLER/WENNFEHLER
Es kann durchaus vorkommen, dass Sie in einer Funktion eine Berechnung haben, die je
nach Situation zu einer Fehlermeldung führen kann. Sie möchten allerdings nicht, dass die
Fehlermeldung dargestellt wird. Dazu stehen Ihnen zwei Funktionen zur Verfügung: Es
handelt sich dabei um die Funktionen ISTFEHLER und WENNFEHLER. Der Unterschied der
beiden Funktionen wird nachstehend erläutert.
ISTFEHLER Syntax
ISTFEHLER(Wert)
Erklärung
Wert Erforderlich. Wert bezieht sich auf einen beliebigen Fehler-
wert (#NV, #WERT!, #BEZUG!, #DIV/0!, #ZAHL!, #NAME?
und #NULL!).
Beispiel
Erstellen Sie die unten dargestellte Tabelle.
In der Zelle D3 erfassen Sie die folgende Formel: =B3/C3.
Anschliessend ändern Sie den Wert in der Zelle C3 von 3 auf 0. Sie können keine Zahl
durch Null dividieren und somit erhalten Sie korrekterweise die Fehlermeldung #DIV/0!.
Wenn Sie in der Zelle D3 die folgende Funktion eingeben…
=ISTFEHLER(B3/C3)
…wird Excel den Wert WAHR ausgeben. Wenn Sie den Wert in der Zelle C3 von 0 auf 1
ändern, wird der Wert FALSCH erscheinen. Excel prüft ob die Prüfung, die in der Klammer
der Funktion ISTFEHLER vorkommt, korrekt ist.
Statt den Wert WAHR oder FALSCH auszugeben, möchten Sie, dass der Text "Ungültige
Berechnung" bei WAHR erscheint. Bei FALSCH soll die Zelle B3 mit der Zelle C3 dividiert
werden.
Die korrekte Funktion in D3 lautet:
=WENN(ISTFEHLER(B3/C3)=WAHR;"Ungültige Berechnung";B3/C3)
Wie Sie sehen können, benötigen Sie für die Prüfung die WENN-Funktion.
Sollte Ihnen dies zu aufwändig sein, könnte Ihnen die folgende Funktion entgegenkom-
men:
1.6 ISTFEHLER/WENNFEHLER
© Claudio Cannatella Microsys IT AG 17
WENNFEHLER Syntax
WENNFEHLER(Wert;Wert_falls_Fehler)
Erklärung
Wert Erforderlich. Das Argument, das auf einen Fehler geprüft
wird.
Wert_falls_Fehler Erforderlich. Der Wert, der zurückgegeben wird, wenn die
Formel einen Fehler ergibt. Die folgenden Fehlertypen wer-
den ausgewertet: #NV, #WERT!, #BEZUG!, #DIV/0!,
#ZAHL!, #NAME? und #NULL!
Beispiel
Erfassen Sie in der Zelle D4 die folgende Funktion:
=WENNFEHLER(B3/C3;"Ungültige Berechnung")
Haben Sie den Unterschied bemerkt? Bei der Funktion WENNFEHLER müssen Sie im Ge-
gensatz zur Funktion ISTFEHLER kein weiteres Argument für die Berechnung eingeben.
Unterschied
Unten sehen Sie noch einmal beide Funktionen, die zum gleichen Resultat führen.
=WENN(ISTFEHLER(B3/C3)=WAHR;"Ungültige Berechnung";B3/C3)
=WENNFEHLER(B3/C3;"Ungültige Berechnung")
2.1 ZÄHLENWENN
© Claudio Cannatella Microsys IT AG 18
2 Statistische Funktionen II
2.1 ZÄHLENWENN
Mit der Funktion ZÄHLENWENN besteht die Möglichkeit, nur die Werte aus einem Be-
reich zu zählen, die eine bestimmte Bedingung erfüllen. Die Bedingung können Sie defi-
nieren.
Syntax
ZÄHLENWENN(Bereich;Suchkriterien)
Erklärung
Bereich Erforderlich. Eine oder mehrere zu zählende Zellen, ein-
schliesslich Zahlen oder Namen, Arrays oder Bezügen mit
Zahlen. Leere Zellen werden ignoriert.
Suchkriterien Erforderlich. Eine Zahl, ein Ausdruck, ein Zellbezug oder
eine Textzeichenfolge durch die bzw. den definiert wird,
welche Zellen gezählt werden. Zum Beispiel kann ein Such-
kriterium als 32, "32", ">32", "Äpfel" oder B4 formuliert wer-
den.
Beispiel
Öffnen Sie die Datei Zählenwenn.xlsx. In der Spalte A sind die Namen erfasst worden, die
in der Spalte C aufgelistet werden. Sie sind mehrfach vorhanden. Sie möchten nun ermit-
teln, wie oft die einzelnen Namen vorkommen.
Erfassen Sie in der Zelle D2 die folgende Funktion:
=ZÄHLENWENN(A$2:A$44;C2)
Der Wert aus der Zelle C2 wird im Bereich A2 bis A44 gesucht. Excel ermittelt die Anzahl
Ergebnisse. Kopieren Sie die Funktion bis zur Zelle D6.
Statt die Zelle C2 als Suchkriterium anzugeben, können Sie auch den Namen erfassen.
Beachten Sie dabei, dass Text immer in Anführungszeichen erfasst werden muss.
=ZÄHLENWENN(A$2:A$44;"Müller")
Es besteht auch die Möglichkeit, nach Zahlen zu suchen und diese zu zählen. Dabei kön-
nen auch Operatoren wie beispielsweise kleiner (<) oder grösser (>) eingesetzt werden.
Sobald Operatoren verwendet werden, gilt auch hier zu beachten, dass das Suchkriterium
in Anführungszeichen erfasst werden muss.
2.2 ZÄHLENWENNS
© Claudio Cannatella Microsys IT AG 19
2.2 ZÄHLENWENNS
In Excel 2007 wurde die ZÄHLENWENN mit der ZÄHLENWENNS-Funktion erweitert.
Mit der Funktion ZÄHLENWENN können Sie lediglich einen Bereich und ein Suchkriterium
definieren. Mit ZÄHLENWENNS sind nun bis zu 127 Bereiche/Suchkriterien definierbar.
Syntax
ZÄHLENWENNS(Kriterienbereich1; Kriterien1; [Kriterienbereich2, Kriterien2]…)
Erklärung
Kriterienbereich1 Erforderlich. Der erste Bereich, in dem die entsprechenden
Kriterien ausgewertet werden.
Kriterien1 Erforderlich. Die Kriterien in Form einer Zahl, eines Aus-
drucks, Zellbezugs oder Textes, mit denen definiert wird,
welche Zellen gezählt werden. Kriterien können beispiels-
weise als 32, ">32", B4, "Äpfel" oder "32" angegeben wer-
den.
Kriterien_Bereich2;
Kriterien2;…
Optional. Zusätzliche Bereiche und deren zugehörige Krite-
rien. Es sind bis zu 127 Bereich/Kriterien-Paare zulässig.
Beispiel
Öffnen Sie die Datei Zählenwenns.xlsx.
Sie sehen eine Tabelle mit fünf Verkäufern, die fünf verschiedene Artikel verkauft haben.
Sie berechnen in den Zellen E5 bis E9, wie oft der Artikel A verkauft wurde.
In der Zelle E5 erfassen Sie die folgende Funktion:
=ZÄHLENWENNS(A$2:A$44;D5;B$2:B$44;"A")
Kopieren Sie die Funktion bis zur Zelle E9.
Das Resultat sollte wie folgt aussehen:
10.1 PivotTable
© Claudio Cannatella Microsys IT AG 83
10 Pivot
Mit Pivot-Tabellen und Pivot-Charts können Datenbanken ausgewertet werden. Der
Unterschied zwischen Pivot-Tabellen und Pivot-Charts liegt darin, dass bei den Pivot-
Tabellen eine Tabelle als Resultat dargestellt wird, während bei den Pivot-Charts ein
Diagramm abgebildet wird. Um die Daten auszuwerten, sollte die Datenbank über einige
Gemeinsamkeiten verfügen.
Den Befehl PivotTable und PivotChart finden Sie in der Registerkarte Einfügen, Befehls-
gruppe Tabellen bzw. Diagramme.
10.1 PivotTable
Öffnen Sie die Datei Pivot01.xlsx. Einige Personen haben während drei Monaten mehrmals
Geld bezogen. Wir möchten von jeder Person den bezogenen Betrag und die Anzahl Be-
züge wissen.
1. Klicken Sie mit dem Cursor in den Datenbereich und wählen Sie aus der Register-
karte Einfügen, Befehlsgruppe Tabellen den Befehl PivotTable.
2. Das Dialogfenster erkennt den Bereich, in dem sich die gewünschten Daten befin-
den. Falls der Bereich nicht korrekt ist, kann dieser neu markiert werden. Es besteht
ebenfalls die Möglichkeit, eine externe Datenquelle anzugeben.
Legen Sie fest, wo der PivotTable-Bericht platziert werden soll. Zur besseren Über-
sicht empfiehlt es sich, die Pivot-Tabelle auf ein neues Arbeitsblatt zu platzieren.
Klicken Sie auf OK.
10.1 PivotTable
© Claudio Cannatella Microsys IT AG 84
3. Excel erstellt ein neues Tabellenblatt. Das unten dargestellte Bild stellt das Resultat
dar, nachdem die Felder in die entsprechenden Bereiche geschoben worden sind.
Ziffer Erklärung
Filter:
Ziehen Sie das Feld Datum aus der PivotTable-Feldliste in den Bereich
Filter. In der Zelle B1 können Sie nun nach Datum filtern.
Spaltenbeschriftung:
Ziehen Sie das Feld Artikel aus der PivotTable-Feldliste in den Bereich
Spalten. Alle Artikel, die in der Tabelle1 vorkommen, werden nun im Be-
reich B4 bis F4 waagrecht (Spalten) aufgeführt.
Zeilenbeschriftung:
Ziehen Sie das Feld Verkäufer aus der PivotTable-Feldliste in den Bereich
Zeilen. Alle Verkäufer, die in der Tabelle1 vorkommen, werden nun im
Bereich A5 bis A9 senkrecht (Zeilen) aufgeführt.
Werte:
Ziehen Sie das Feld Betrag aus der PivotTable-Feldliste in den Bereich
Werte. Es wird automatisch die Summe aller einbezahlten Beträge be-
rechnet.
Weiter wird das Kontexttool PivotTable-Tools mit den beiden Registern Analysieren
und Entwurf dargestellt. Diese umfassen die wesentlichen Befehle für das Verwalten
und Konfigurieren der PivotTable.
Felder
11.2 Erweiterte Techniken
© Claudio Cannatella Microsys IT AG 108
11.2.1 SVERWEIS / WVERWEIS
Stellen Sie sich vor, Sie möchten aus einer Datenbank bestimmte Informationen auslesen
und diese an einer bestimmten Stelle darstellen. Dies kann auch eine andere Excel-Datei
oder ein anderes Tabellenblatt sein.
Dazu stehen Ihnen die Funktionen SVERWEIS und WVERWEIS zur Verfügung. In welcher
Situation wird welche Funktion angewendet?
Schauen Sie sich die Quellmatrix an und beachten Sie, wie die Daten dargestellt sind.
Im unten dargestellten Bild werden die Daten senkrecht dargestellt und somit wird für das
Auslesen der Datensätze der SVERWEIS verwendet. Achten Sie auf den ersten Buchstaben
der Funktion: S = Senkrecht!
Im unten dargestellten Bild werden die Daten waagrecht dargestellt und somit wird für
das Auslesen der Datensätze der WVERWEIS verwendet. Achten Sie auf den ersten Buch-
staben der Funktion: W = Waagrecht!
11.2 Erweiterte Techniken
© Claudio Cannatella Microsys IT AG 109
SVERWEIS
Syntax
SVERWEIS(Suchkriterium; Matrix; Spaltenindex; [Bereich_Verweis])
Erklärung
Suchkriterium Erforderlich. Der in der ersten Spalte der Tabelle oder im
entsprechenden Bereich zu suchende Wert. Bei Suchkrite-
rium kann es sich um einen Wert oder einen Bezug han-
deln.
Matrix Erforderlich. Der Zellbereich, in dem die Daten enthalten
sind. Sie können einen Bezug auf einen Bereich (z. B.
A2:D8) oder einen Bereichsnamen verwenden. Die Werte in
der ersten Spalte von Matrix sind die vom Suchkriterium
gesuchten Werte. Diese Werte können Text, Zahlen oder
Wahrheitswerte darstellen. Bei Zeichenfolgen (Texten) wird
nicht zwischen Klein- und Grossbuchstaben unterschieden.
Spaltenindex Erforderlich. Die Spaltennummer im Argument Matrix, aus
welcher der entsprechende Wert zurückgegeben werden
muss. Wenn das Argument Spaltenindex den Wert 1 auf-
weist, wird der Wert in der ersten Spalte von Matrix zurück-
gegeben. Wenn Spaltenindex den Wert 2 aufweist, wird der
Wert in der zweiten Spalte von Matrix zurückgegeben usw.
Bereich_Verweis Optional. Ein Wahrheitswert, der angibt, ob SVERWEIS eine
genaue oder eine ungefähre Entsprechung suchen soll.
Wenn Bereich_Verweis entweder WAHR oder nicht belegt
ist, wird eine genaue oder ungefähre Entsprechung zurück-
gegeben. Wird keine genaue Entsprechung gefunden, wird
der nächstkleinere Wert zurückgegeben.
Wichtig: Wenn Bereich_Verweis entweder WAHR oder nicht
belegt ist, müssen die Werte in der ersten Spalte von Mat-
rix in aufsteigender Sortierreihenfolge geordnet sein, an-
dernfalls gibt SVERWEIS möglicherweise nicht den richtigen
Wert zurück.
Wenn Bereich_Verweis FALSCH ist, müssen die Werte in der
ersten Spalte von Matrix nicht sortiert sein.
Ist das Argument Bereich_Verweis mit FALSCH angegeben,
kann SVERWEIS nur nach einer genauen Entsprechung su-
chen. Wenn in der ersten Spalte von Matrix mindestens
zwei Werte vorhanden sind, die dem Suchkriterium ent-
sprechen, wird der erste gefundene Wert verwendet. Wird
keine genaue Entsprechung gefunden, wird der Fehlerwert
#NV zurückgegeben.
11.2 Erweiterte Techniken
© Claudio Cannatella Microsys IT AG 110
Beispiel
Öffnen Sie die Datei Verweise.xlsx.
Die Datei besteht aus vier Tabellenblättern. Wählen Sie das Tabellenblatt Artikel. Hier se-
hen Sie alle Artikel, die bestellt werden können.
Wechseln Sie zum Tabellenblatt Formular. In der Zelle A4 soll die Artikelnummer, die be-
stellt werden muss, eingegeben werden. Die Artikelbezeichnung soll in der Zelle B4 aus-
gegeben werden.
Erfassen Sie in der Zelle A4 eine Artikelnummer aus dem Tabellenblatt Artikel. Anschlies-
send erfassen Sie die folgende Funktion:
Erklärung der Funktion
=SVERWEIS(A4)
Der Wert der Zelle A4 wird eingelesen.
=SVERWEIS(A4;Artikel!A2:D22)
Im angegebenen Bereich A2 bis D22 der Tabelle Artikel wird in der ersten Spalte
nach dem eingelesenen Wert (Zelle A4) gesucht.
=SVERWEIS(A4;Artikel!A2:D22;2)
Wird im Bereich A2 bis D22 in der ersten Spalte der gesuchte Wert gefunden, wird
der Wert in der 2. Spalte (gleiche Zeile) eingelesen und in der Zelle mit der
Funktion ausgegeben.
=SVERWEIS(A4;Artikel!A2:D22;2;FALSCH)
Der gesuchte Wert kann auch eingelesen werden, wenn die erste Spalte der Tabelle
Artikel nicht aufsteigend (d.h. falsch) sortiert ist.
11.2 Erweiterte Techniken
© Claudio Cannatella Microsys IT AG 111
WVERWEIS
Syntax
WVERWEIS(Suchkriterium; Matrix; Zeilenindex; [Bereich_Verweis])
Erklärung
Suchkriterium Erforderlich. Der Wert, der in der ersten Zeile der Tabelle
gefunden werden soll. Suchkriterium kann ein Wert, ein Be-
zug oder eine Zeichenfolge sein.
Matrix Erforderlich. Eine Tabelle mit Informationen, in der Daten
gesucht werden. Verwenden Sie einen Bezug auf einen Be-
reich oder einen Bereichsnamen.
Bei den Werten in der ersten Zeile der Matrix kann es sich
um Text, Zahlen oder Wahrheitswerte handeln.
Wenn Bereich_Verweis WAHR ist, müssen die Werte in der
ersten Zeile von Matrix in aufsteigender Reihenfolge ange-
ordnet werden: ..., -2, -1, 0, 1, 2, ..., A-Z, FALSCH, WAHR;
andernfalls gibt WVERWEIS möglicherweise nicht den rich-
tigen Wert aus. Wenn Bereich_Verweis FALSCH ist, muss
Matrix nicht sortiert werden.
Bei Zeichenfolgen (Texten) wird nicht zwischen Klein- und
Grossbuchstaben unterschieden.
Sortieren Sie die Werte in aufsteigender Reihenfolge von
links nach rechts.
Zeilenindex Erforderlich. Die Nummer der Zeile in der Matrix, aus wel-
cher der entsprechende Wert zurückgegeben wird. Ein Zei-
lenindex von 1 gibt den ersten Zeilenwert aus der Matrix
zurück, ein Zeilenindex von 2 gibt den zweiten Zeilenwert
aus der Matrix zurück usw. Wenn der Zeilenindex kleiner als
1 ist, gibt WVERWEIS den Fehlerwert #WERT! zurück. Wenn
der Zeilenindex grösser als die Anzahl der Zeilen in Matrix
ist, gibt WVERWEIS den Fehlerwert #BEZUG! zurück.
Bereich_Verweis Optional. Ein Wahrheitswert, der angibt, ob WVERWEIS
eine genaue Entsprechung oder eine ungefähre Entspre-
chung suchen soll. Wenn dieser Parameter WAHR ist oder
weggelassen wird, wird eine ungefähre Entsprechung zu-
rückgegeben. Anders ausgedrückt, wird der nächstkleinere
Wert zurückgegeben. Ist der Parameter FALSCH, sucht
WVERWEIS eine genaue Entsprechung. Wenn keine gefun-
den wird, wird der Fehlerwert #NV zurückgegeben.
11.2 Erweiterte Techniken
© Claudio Cannatella Microsys IT AG 112
Beispiel
Öffnen Sie die Datei Verweise.xlsx.
Die Datei besteht aus vier Tabellenblättern. Wählen Sie das Tabellenblatt Rabattsatz. In
der Zelle F4 soll aufgrund der Rabattstaffelung, die im Tabellenblatt Rabatt angegeben ist,
der korrekte Rabattsatz ausgelesen werden.
Wie Sie sehen können, wurde der Bruttobetrag (Zelle E4) bereits berechnet. Geben Sie in
der Zelle F4 die folgende Funktion ein:
Erklärung der Funktion
=WVERWEIS(E4)
Der Wert in Zelle E4 wird eingelesen.
=WVERWEIS(E4;Rabatt!B1:G2)
Im angegebenen Bereich B1 bis G2 der Tabelle Rabatt wird in der ersten Zeile nach
dem eingelesenen Wert (Zelle E4) gesucht.
=WVERWEIS(E4;Rabatt!B1:G2;2)
Wurde im Bereich B1 bis G2 in der ersten Zeile der gesuchte Wert gefunden, wird
der Wert in der 2. Zeile (gleiche Spalte) eingelesen und in der Zelle mit der Funktion
ausgegeben. Wurde der gesuchte Wert nicht gefunden, wird der Zeilenindex
berücksichtigt.
=WVERWEIS(E4;Rabatt!B1:G2;2;WAHR)
Da der gesuchte Wert von Excel nicht gefunden wurde, wird eine ungefähre
Übereinstimmung ausgegeben. Wie bereits weiter oben in der Syntax erklärt, wird
der nächstkleinere Wert ausgegeben.
Aufgabe
1. Öffnen Sie die Datei FlughafenCodes.xlsx
2. Die Datei besteht aus vier Tabellenblättern. Im Tabellenblatt Passagiere finden Sie
alle Passagiere inklusive ihrer Reiserouten und Sitzklassen. Im Tabellenblatt Flugha-
fenListe sind sämtliche Angaben zu den weltgrössten Flughäfen aufgeführt. Im Ta-
bellenblatt Sitzordnung sind die möglichen Sitzordnungen ersichtlich.
3. Wechseln Sie zum Tabellenblatt Ticket. Erfassen Sie in der Zelle B1 eine existierende
Passagiernummer (Tabellenblatt Passagiere).
4. Die restlichen grauen Zellen sollen mittels der entsprechenden VERWEIS-Funktion
ausgefüllt werden.
16.1 EDATUM
© Claudio Cannatella Microsys IT AG 160
16 Datum & Zeit Funktionen II
16.1 EDATUM
Die Funktion EDATUM dient dazu, dass Sie zu einem bestimmten Datum eine gewisse
Anzahl Monate addieren oder subtrahieren können.
Syntax
EDATUM(Ausgangsdatum;Monate)
Erklärung
Ausgangsdatum Erforderlich. Ein Datum, das dem Anfangsdatum entspricht.
Datumsangaben sollten mithilfe der Funktion DATUM oder
aber als Ergebnis anderer Formeln oder Funktionen einge-
geben werden. Verwenden Sie beispielsweise die Funktion
=DATUM(2008;5;23), um den 23.05.2008 festzulegen.
Probleme können auftreten, wenn Sie Datumsangaben als
Text eingeben.
Monate Erforderlich. Gibt an, wie viele Monate vor oder nach dem
Ausgangsdatum liegen sollen. Ein positiver Wert für Mo-
nate ergibt ein Datum in der Zukunft, ein negativer Wert
ein in der Vergangenheit liegendes Datum.
Beispiel
Erstellen Sie ein leeres Tabellenblatt und erfassen Sie in der Zelle A1 ein beliebiges Datum.
Formatieren Sie die Zelle B1 als Datum und erfassen Sie die folgende Funktion:
=EDATUM(A1;3)
Nun werden drei Monate zum bestehenden Datum addiert. Passen Sie die Funktion an, in
dem Sie für das Argument Monate einen negativen Wert festlegen.
=EDATUM(A1;-4)
Nun werden vier Monate vom bestehenden Datum subtrahiert.
16.2 MONATSENDE
© Claudio Cannatella Microsys IT AG 161
16.2 MONATSENDE
Die Funktion MONATSENDE dient dazu, dass Sie zu einem bestimmten Datum eine ge-
wisse Anzahl Monate addieren. Dabei wird der letzte Tag vom entsprechenden Monat
ausgegeben.
Syntax
MONATSENDE(Ausgangsdatum;Monate)
Erklärung
Ausgangsdatum Erforderlich. Ein Datum, das dem Anfangsdatum entspricht.
Datumsangaben sollten mithilfe der Funktion DATUM oder
aber als Ergebnis anderer Formeln oder Funktionen einge-
geben werden. Verwenden Sie beispielsweise DA-
TUM(2008;5;23), um den 23.05.2008 festzulegen. Probleme
können auftreten, wenn Sie Datumsangaben als Text ein-
geben.
Monate Erforderlich. Gibt an, wie viele Monate vor oder nach dem
Ausgangsdatum liegen sollen. Ein positiver Wert für Mo-
nate ergibt ein in der Zukunft, ein negativer Wert ein in der
Vergangenheit liegendes Datum.
Beispiel
Erstellen Sie ein leeres Tabellenblatt und erfassen Sie in der Zelle A1 das Datum
01.06.2013. Formatieren Sie die Zelle B1 als Datum und erfassen Sie die folgende Funktion:
=MONATSENDE(A1;3)
Nun werden zum 1. Juni drei Monate addiert. Dies ergibt den 1. September. Da die Funk-
tion MONATSENDE den letzten Tag des Monates ausgibt, wird in der Zelle B1 der
30.09.2013 zurückgegeben.
Passen Sie die Funktion an, in dem Sie für das Argument Monate einen negativen Wert
festlegen.
=MONATSENDE(A1;-3)
Nun werden die drei Monate subtrahiert und der letzte Tag des Monates angezeigt.
16.3 TAGE360
© Claudio Cannatella Microsys IT AG 162
16.3 TAGE360
Die Funktion TAGE360 wird vor allem im Rechnungswesen benötigt, da dort jeder Monat
30 Tage umfasst. Es spielt keine Rolle, ob der Monat 28, 30 oder 31 Tage hat. Es wird im-
mer mit 30 Tagen gerechnet.
Syntax
TAGE360(Ausgangsdatum;Enddatum;[Methode])
Erklärung
Ausgangsdatum und
Enddatum
Erforderlich. Die beiden Datumsangaben, für die Sie die
dazwischenliegenden Tage berechnen möchten. Wenn das
Ausgangsdatum nach dem Enddatum liegt, gibt die Funk-
tion TAGE360 eine negative Zahl zurück. Datumsangaben
sollten mithilfe der Funktion DATUM eingegeben oder aus
den Ergebnissen anderer Formeln oder Funktionen abge-
leitet werden. Verwenden Sie beispielsweise DA-
TUM(2008;5;23), um den 23.05.2008 zurückzugeben. Prob-
leme können auftreten, wenn Sie Datumsangaben als Text
eingeben.
Methode Erforderlich. Ein Wahrheitswert, der angibt, ob in der Be-
rechnung die amerikanische oder die europäische Me-
thode verwendet werden soll.
Methode Definition
FALSCH oder nicht ange-
geben
US-Methode (NASD). Ist
das Ausgangsdatum der
letzte Tag eines Monats,
wird dieses Datum zum 30.
Tag desselben Monats. Ist
das Enddatum der letzte
Tag eines Monats und das
Ausgangsdatum ein Da-
tum vor dem 30. Tag eines
Monats, wird das Endda-
tum zum 1. Tag des da-
rauffolgenden Monats. In
allen anderen Fällen wird
das Enddatum zum 30.
Tag desselben Monats.
WAHR Europäische Methode. Je-
des Ausgangs- und End-
datum, das auf den 31. Tag
eines Monats fällt, wird
zum 30. Tag desselben
Monats.
16.4 ARBEITSTAG
© Claudio Cannatella Microsys IT AG 163
Beispiel
Erstellen Sie die unten dargestellte Tabelle:
Erfassen Sie in der Zelle C1 die folgende Funktion:
=TAGE360(A1;B1)
Als Resultat erhalten Sie 30 Tage, obwohl der Monat Februar im Jahr 2013 28 Tage um-
fasste.
16.4 ARBEITSTAG
Sie möchten zu einem Datum eine gewisse Anzahl Tage addieren. Die Tage, die addiert
werden, beinhalten diverse Feiertage. Als Feiertage werden alle arbeitsfreien Tage be-
zeichnet. Die Funktion ARBEITSTAG addiert ausschliesslich Arbeitstage. Feiertage werden
nicht berücksichtigt.
Syntax
ARBEITSTAG(Ausgangsdatum;Tage;[Freie_Tage])
Erklärung
Ausgangsdatum Erforderlich. Ein Datum, das dem Anfangsdatum entspricht.
Tage Erforderlich. Die Anzahl der nicht auf ein Wochenende o-
der auf einen Feiertag fallenden Tage vor oder nach dem
Ausgangsdatum. Ein positiver Wert für Tage bedeutet ein
zukünftiges Datum, und ein negativer Wert ergibt ein zu-
rückliegendes Datum.
Freie_Tage Optional. Eine optionale Liste einer oder mehrerer Datums-
angaben, die alle Arten von arbeitsfreien Tagen repräsen-
tieren kann, die aus dem Arbeitskalender ausgeschlossen
werden sollen, beispielsweise staatliche oder regionale Fei-
ertage und Freischichten. Bei der Liste kann es sich um ei-
nen Zellbereich, der die Datumsangaben enthält, oder um
eine Matrixkonstante der fortlaufenden Zahlen handeln, die
die Datumsangaben darstellen.
16.5 ARBEITSTAG.INTL
© Claudio Cannatella Microsys IT AG 164
Beispiel
Erstellen Sie die unten dargestellte Tabelle:
Formatieren die Zelle D2 als Datum und erfassen Sie die folgende Funktion:
=ARBEITSTAG(A2;B2;C2:C4) ergibt den 13.03.2014.
Bei der obigen Funktion wurden alle Wochenenden und die drei Ferientage aus dem Be-
reich C2 bis C4 berücksichtigt.
16.5 ARBEITSTAG.INTL
Die Funktion ARBEITSTAG.INTL unterscheidet sich von der Funktion ARBEITSTAG nur da-
rin, dass die Möglichkeit besteht, das Wochenende zu bestimmen. Somit muss das Wo-
chenende nicht immer Samstag und Sonntag sein, sondern auch andere Kombinationen
sind möglich.
Syntax
ARBEITSTAG.INTL(Ausgangsdatum;Tage;[Wochenende];[Freie_Tage])
Erklärung
Ausgangsdatum Erforderlich. Ein Datum, das dem Anfangsdatum entspricht
oder das auf eine ganze Zahl gekürzte Startdatum.
Tage Erforderlich. Die Anzahl der Arbeitstage vor oder nach Aus-
gangsdatum. Ein positiver Wert bedeutet ein zukünftiges
Datum, ein negativer Wert ergibt ein zurückliegendes Da-
tum und ein Nullwert ergibt das Ausgangsdatum. Tages-
Offset (Beispiel: Tages-Offset beträgt 6 Stunden. D. h. der
Tag beginnt um 06:00:00 Uhr und endet um 05:59:59 Uhr
des Folgetages) wird auf eine ganze Zahl gekürzt.
16.5 ARBEITSTAG.INTL
© Claudio Cannatella Microsys IT AG 165
Wochenende Optional. Gibt die Wochentage an, die als Wochenendtage
behandelt und nicht als Arbeitstage betrachtet werden.
Wochenende ist eine Wochenendnummer oder eine Zei-
chenfolge, die den Fall von Wochenenden angibt.
Mit Wochenendnummernwerten werden die folgenden
Wochenendtage angegeben:
Wochenendnummer Wochenendtage
1 oder nicht angegeben Samstag, Sonntag
2 Sonntag, Montag
3 Montag, Dienstag
4 Dienstag, Mittwoch
5 Mittwoch, Donnerstag
6 Donnerstag, Freitag
7 Freitag, Samstag
11 Nur Sonntag
12 Nur Montag
13 Nur Dienstag
14 Nur Mittwoch
15 Nur Donnerstag
16 Nur Freitag
17 Nur Samstag
Freie_Tage Optional. Eine optionale Gruppe von Datumsangaben, die
aus dem Arbeitskalender ausgeschlossen werden sollen.
Bei den freien Tagen muss es sich entweder um einen Zell-
bereich handeln, der die Datumsangaben enthält, oder um
eine Matrixkonstante der seriellen Werte, die diese Da-
tumsangaben darstellen. Datumsangaben oder serielle
Werte können in Freie_Tage willkürlich sortiert sein.
16.5 ARBEITSTAG.INTL
© Claudio Cannatella Microsys IT AG 166
Beispiel
Erstellen Sie die die folgende Tabelle:
Das Ausgangsdatum der 1. Juli 2013. Nun werden 12 Arbeitstage addiert. Versuchen Sie
die folgenden Szenarien:
Formatieren Sie vorgängig die Resultatzelle als Datum, lang
Szenario 1
Feiertage: Samstag und Sonntag
Szenario 2
Feiertage: Nur Sonntag
Szenario 3
Feiertage: Samstag, Sonntag und der 08.07.2013
Szenario 4
Feiertage: Samstag, Sonntag, 08.07.2013 und der 12.07.2013
Lösung
Szenario 1
=ARBEITSTAG.INTL(A2;B2;1) Mittwoch, 17. Juli 2013
Szenario 2
=ARBEITSTAG.INTL(A2;B2;11) Montag, 15. Juli 2013
Szenario 3
=ARBEITSTAG.INTL(A2;B2;1;"8.7.2013") Donnerstag, 18. Juli 2013
Szenario 4
=ARBEITSTAG.INTL(A2;B2;1;{"8.7.2013";"12.7.2013"}) Freitag, 19. Juli 2013
16.6 NETTOARBEITSTAGE
© Claudio Cannatella Microsys IT AG 167
16.6 NETTOARBEITSTAGE
Mit der Funktion NETTOARBEITSTAGE können Sie die Differenz zwischen zwei Daten in
Tagen ermitteln. Auch hier werden die von Ihnen eingegebenen Feiertage und Wochen-
enden berücksichtigt.
Syntax
NETTOARBEITSTAGE(Ausgangsdatum;Enddatum;[Freie_Tage])
Erklärung
Ausgangsdatum Erforderlich. Ein Datum, welches das Anfangsdatum angibt.
Enddatum Erforderlich. Ein Datum, welches das Enddatum angibt.
Freie_Tage Optional. Ein optionaler Bereich von einer oder mehreren
Datumsangaben, die alle Arten von arbeitsfreien Tagen re-
präsentieren kann, die aus dem Arbeitskalender ausge-
schlossen werden sollen, also z. B. staatliche oder regionale
Feiertage und Freischichten. Bei der Liste kann es sich ent-
weder um einen Zellbereich, der die Datumsangaben ent-
hält, oder eine Matrixkonstante der fortlaufenden Zahlen
handeln, welche die Datumsangaben darstellen.
Beispiel
Erstellen Sie die folgende Tabelle:
Berechnen Sie nun die Anzahl Tage. Sowohl die Feiertage als auch die Wochenenden
werden dabei nicht berücksichtigt.
Erfassen Sie in der Zelle D2 die folgende Funktion:
=NETTOARBEITSTAGE(A2;B2;C2:C4)
16.7 NETTOARBEITSTAGE.INTL
© Claudio Cannatella Microsys IT AG 168
16.7 NETTOARBEITSTAGE.INTL
Die Funktion NETTOARBEITSTAGE.INTL unterscheidet sich von der Funktion NETTOAR-
BEITSTAGE nur darin, dass die Möglichkeit besteht, das Wochenende zu bestimmen. So-
mit muss das Wochenende nicht immer Samstag und Sonntag sein, sondern auch andere
Kombinationen sind möglich.
Syntax
NETTOARBEITSTAGE.INTL(Ausgangsdatum;Enddatum;[Wochenende];[Freie_Tage])
Erklärung
Ausgangsdatum und
Enddatum
Erforderlich. Die Datumsangaben, für welche die dazwi-
schenliegenden Arbeitstage berechnet werden sollen. Das
Ausgangsdatum kann vor oder nach dem Enddatum liegen
und auch mit diesem identisch sein.
Wochenende Optional. Gibt die Tage der Woche an, die als Wochenend-
tage behandelt werden und damit nicht zur Anzahl der
vollen Arbeitstage zwischen Ausgangsdatum und Endda-
tum zählen. Wochenende ist eine Wochenendnummer oder
eine Zeichenfolge, die den Fall von Wochenenden angibt.
Wochenendnummer Wochenendtage
1 oder nicht angegeben Samstag, Sonntag
2 Sonntag, Montag
3 Montag, Dienstag
4 Dienstag, Mittwoch
5 Mittwoch, Donnerstag
6 Donnerstag, Freitag
7 Freitag, Samstag
11 Nur Sonntag
12 Nur Montag
13 Nur Dienstag
14 Nur Mittwoch
15 Nur Donnerstag
16 Nur Freitag
17 Nur Samstag
Freie_Tage Optional. Eine optionale Gruppe aus einer oder mehreren
Datumsangaben, die arbeitsfreie Tage bezeichnen.
Freie_Tage kann als Bereich von Zellen mit den entspre-
chenden Datumsangaben oder als Arraykonstante aus den
seriellen Werten angegeben werden, die diese Datumsan-
gaben repräsentieren. Die Reihenfolge der Datumsanga-
ben bzw. seriellen Werte in Freie_Tage ist beliebig.
16.7 NETTOARBEITSTAGE.INTL
© Claudio Cannatella Microsys IT AG 169
Beispiel
Erstellen Sie die folgende Tabelle:
Das Startdatum ist der 1. Juli 2013 und das Enddatum der 31. Juli 2013. Berechnen Sie die
Differenz in Tagen. Versuchen Sie die folgenden Szenarien:
Szenario 1
Feiertage: Samstag und Sonntag
Szenario 2
Feiertage: Nur Sonntag
Szenario 3
Feiertage: Samstag, Sonntag und der 9. Juli 2013
Szenario 4
Feiertage: Samstag, Sonntag, 9. Juli 2013 und der 16. Juli 2013
Lösung
Szenario 1
=NETTOARBEITSTAGE.INTL(A2;B2;1) 23
Szenario 2
=NETTOARBEITSTAGE.INTL(A2;B2;11) 27
Szenario 3
=NETTOARBEITSTAGE.INTL(A2;B2;1;"9.7.2013") 22
Szenario 4
=NETTOARBEITSTAGE.INTL(A2;B2;1;{"9.7.2013";"16.7.2013"}) 21
Aufgabe
1. Öffnen Sie die Datei Arbeitstage.xlsx.
2. Die Datei besteht aus drei Tabellenblättern. Im Tabellenblatt Berechnungen ist eine
Liste mit Personen aufgeführt, die während einem bestimmten Zeitraum (Spalte C
und D) an einem Projekt gearbeitet haben.
Im Tabellenblatt Wochenendnummern sind die benötigten Wochenenden für die
entsprechende Funktion aufgeführt.
Im Tabellenblatt Feiertage sind die Feiertage aufgeführt.
3. In der Zelle C11 sehen Sie, dass bei Marianne Hürlimann das Anfangsdatum unbe-
kannt ist. Berechnen Sie dieses aufgrund der angegebenen Anzahl Tage in der Zelle
F11.
4. Berechnen Sie in der Spalte F die Anzahl Tage für jeden Mitarbeiter, der am Projekt
gearbeitet hat. Verwenden Sie für die Berechnung der Wochenendtage das entspre-
chende Tabellenblatt. Überlegen Sie sich, welche Funktion geeignet ist.
© Claudio Cannatella Microsys IT AG 170
17 Stichwortverzeichnis
ABS ................................................................................. 149
ARBEITSTAG ................................................................ 163
ARBEITSTAG.INTL ...................................................... 164
Berechnetes Element (PivotTable) .......................... 95
Berechnetes Feld (PivotTable) ................................... 91
Berichtslayout (PivotTable) ........................................ 88
Beziehungen (PowerPivot) ...................................... 124
BW .................................................................................. 146
Daten zusammenführen (PowerPivot) ................. 127
Datentabelle .................................................................. 72
Datum & Zeit Funktionen ........................................ 160
Details ein- und ausblenden (PivotTable) ............. 89
Diagramme ..................................................................... 31
Diagramme (Spezielle) ............................................... 38
Dimensionen (Matrix)................................................. 101
EDATUM ....................................................................... 160
Erweiterte Techniken (Matrix) ................................. 105
Erweiterte Techniken (PowerPivot) ....................... 124
Erweiterung der PivotTable ....................................... 87
Externe Daten abrufen ............................................... 75
Feldelemente verschieben (PivotTable)................. 89
Felder umbenennen (PivotTable) ............................. 91
Gesamtergebnisse (PivotTable) ............................... 88
Gruppierung (PivotTable) .......................................... 85
HÄUFIGKEIT ................................................................. 140
INDEX ............................................................................. 113
INDIREKT ....................................................................... 117
Informationsfunktionen ............................................ 155
Installation von PowerPivot ...................................... 119
ISTLEER .......................................................................... 155
Konsolidieren .................................................................. 81
Layout (PivotTable) ...................................................... 87
Leere Zeilen (PivotTable) ........................................... 88
Leerzellen überspringen ............................................ 64
Matrixformel und Matrixfunktion ............................. 99
MAXA ............................................................................ 137
MINA .............................................................................. 136
MITTELWERTA ............................................................. 135
MITTELWERTWENN ................................................... 138
MITTELWERTWENNS ................................................. 139
MONATSENDE ............................................................ 161
NETTOARBEITSTAGE ................................................. 167
NETTOARBEITSTAGE.INTL ....................................... 168
PivotChart ...................................................................... 83
PivotTable ...................................................................... 83
PivotTable-Tools .......................................................... 84
PowerPivot .................................................................... 119
PRODUKT ......................................................................149
RMZ ............................................................................... 144
Spezielles Kopieren ..................................................... 62
SVERWEIS ...................................................................... 109
Szenariobericht ............................................................. 71
Szenario-Manager ....................................................... 68
TAGE360 ........................................................................ 162
TEILERGEBNIS .............................................................. 151
Teilergebnisse (PivotTable) ....................................... 87
Textdateien importieren ............................................ 75
Transponieren ............................................................... 62
TREND ............................................................................142
Verbunddiagramm ...................................................... 33
VERGLEICH ................................................................... 115
Was-wäre-wenn-Analysen ........................................ 65
Webinformationen importieren .............................. 79
Weitere Berechnungsmöglichkeiten (Matrix) .....104
Wertfeldeinstellungen (PivotTable) ........................ 90
WVERWEIS ..................................................................... 111
ZELLE .............................................................................. 156
Zielwertsuche ................................................................ 65
ZUFALLSBEREICH ....................................................... 153
ZUFALLSZAHL .............................................................154
ZW ...................................................................................148