Bezüge, Formeln und Funktionen stellen den eigentlichen Kern der Tabellenkalkulation dar. Die Ergebnisse automatisch zu berechnen und Kalkulationen mit verschieden Varianten durchzuspielen erledigt Excel mit großer Leichtigkeit.

Formeln eingeben:

Formeln beginnen immer mit dem Ist-Gleich-Zeichen (=).

Markieren der Zelle in der das Ergebnis stehen soll

Eintippen der Formel: =2+3
Bestätigen mit der Eingabetaste

1.1.1 Das Bezugssystem in Excel:

Man schreibt die Zahlen nicht direkt in die Formel, sondern in eine andere Zelle und bezieht die Formel auf die Adressen dieser Zellen.

Alle Bezüge, Formeln und Funktionen werden automatisch neu berechnet, sobald sich der Wert in der Tabelle ändert oder eine neue Eingabe gemacht wird.

Ein Bezug ist die Adresse einer Zelle oder einer Gruppe von Zellen in einer Tabelle. Wir unterscheiden relative und absolute Bezüge:

1.1.2 Relative Bezüge:

Ein relativer Bezug berechnet immer Zellen relativ zu seiner Position, in diesem Fall wird also die Zelle „eins ober mir“ mit der Zelle „zwei ober mir“ addiert.

Ein relativer Bezug berechnet immer Zellen relativ zu seiner Position. Wird diese Formel kopiert, trägt Excel wieder für diese Formel ein.

1.1.3 Absolute Bezüge:

Ein absoluter Bezug entspricht einer bestimmten Adresse. Zur Verdeutlichung: Hozweg 7 ist eine feste Adresse.

Absolute Bezüge werden durch folgende Schreibweise dargestellt:

=$A$1

Weder durch Kopieren noch durch Verschieben wird dieser Bezug geändert

Das Umwandeln eines relativen Bezuges in einen absoluten Bezug oder umgekehrt lässt sich schnell mit der Funktionstaste F4 erledigen.

Beispiel „Umsatzsteuerberechnung“:

Erstellen Sie in einer neuen Excel Datei folgende Tabelle und berechnen Sie die fehlenden Werte:

Artikel	Preis	MwSt	MwSt (öS)	Endpreis
Luftpumpe	249	20%
Reserveschlauch	389	20%
Helm	790	20%
Handschuhe	200	20%
Gesamt

Beispiel „Umsatzberechnung“:

Firma	Umsatz 1996	Umsatz 1997	Umsatzsteigerung	Steigerung in %	Gesamt
Elcotec	3456000	4000000
Schmidt & Co	2990002	3700000
Hausfreund GmbH	3000000	2500000

Berechnen Sie die fehlenden Spalten und formatieren Sie die Tabelle:

a) Zahlen mit Tausendertrennpunkte, rechtsbündig

b) Umsatzsteigerung in % in % Format auf eine Kommastelle genau

c) Tabellenrahmen wie oben

d) Zentrieren Sie die Spaltenüberschriften

1.1.4 Gemischte Bezüge:

Gemischte Bezüge sind Bezüge bei denen der Bezug zur Zeile absolut und zur Spalte relativ ist oder umgekehrt.

Beispiel :

=B$2 oder $B2

Achtung: Beim Kopieren ändert sich der relative Anteil und der absolute bleibt unverändert.

1.2 Namen

Eine weitere Möglichkeit, sich auf eine bestimmte Zelle bzw. Zellbereich zu beziehen, ist die Verwendung von Namen. Der große Vorteil dabei ist, dass Formeln leichter verständlich werden.

Bezüge über Namen sind standardmäßig absolute Bezüge.

Beispiel:

2 Erweiterte Funktionen

Unter einer Funktion versteht man in Excel einen vordefinierten Ausdruck, der einen konstanten Aufbau besitzt.

Funktionen können dazu verwendet werden, einfache oder komplexe Berechnungen durchzuführen.

Man beginnt in der Zelle deren Inhalt zu berechnen ist mit einem Gleichheitszeichen, dem der Funktionsname folgt. In Klammern werden anschließend so genannte Argumente (notwendige Zusatzangaben) eingefügt, die durch Semikola getrennt sind. Als Argumente können Zahlen, Texte, logische Werte (z.B. wahr/falsch) oder eine Funktion eingetragen werden.

Funktionen können auf drei Arten eingegeben werden:

manuell
durch Eintippen des Funktionsnamens und der dazugehörigen Attribute
durch den Funktions-Assistenten, dessen Symbol in der Symbolleiste zu finden ist

Der Funktionsassistent kann auf verschiedene Arten gestartet werden:

Über das Menü: [Einfügen] – [Funktion]
Über die Symbolleiste: Schaltfläche
Über die Tastatur: Shift + F3

Durch verschiedene Eingabemasken und Erklärungen wird das Handhaben der Funktion vereinfacht.

2.1 Datums- und Zeitfunktionen

Microsoft Excel speichert Datumsangaben als fortlaufende Zahlen, damit sie für Berechnungen verwendet werden können. Standardmäßig ist der 1. Januar 1900 die fortlaufende Zahl 1 und der 1. Januar 2008 die fortlaufende Zahl 39448, da dieses Datum 39448 Tage hinter dem 01.01.1900 liegt.

2.1.1 HEUTE

Diese Funktion gibt das heutige Datum an.

Die Syntax für diese Funktion lautet =HEUTE ().

2.1.2 JETZT

Diese Funktion liefert die fortlaufende Zahl des aktuellen Datums und der aktuellen Uhrzeit.

Die Syntax für diese Funktion lautet =JETZT().

2.1.3 TAG

Gibt den Tag eines Datums als fortlaufende Zahl an. Dieser wird im Bereich von 1 bis 31 dargestellt.

Der Syntax für diese Funktion lautet =TAG(Zahl).

Beispiele:

=TAG(“5. März 2001“) ergibt 5

=TAG(“13.11.1999) ergibt 13

=TAG(“2002/02/02“) ergibt 02

2.1.4 MONAT

Gibt den Monat eines Datums als fortlaufende Zahl an. Diese Zahl kann in einem Bereich von 1 (Januar) bis 12 (Dezember) angegeben werden.

Diese Funktion ist gleich aufgebaut wie TAG.

2.1.5 JAHR

Gibt das Jahr eines Datums als fortlaufende Zahl an. Diese Funktion funktioniert nach dem selben Prinzip, wie Tag und Monat.

2.2 Statistische Funktionen

Argumente können in den folgenden Funktionen jeweils als Bereich, einzeln oder gemischt eingefügt werden. Siehe Beispiel in 4.3.

2.2.1 ANZAHL

Mit der Funktion ANZAHL werden jene Zellen des definierten Bereiches gezählt, die eine Zahl enthalten.

Bezogen auf das Beispiel in Punkt 3 würde =ANZAHL(B3:B15) den Wert 11 liefern.

2.2.2 ANZAHL2

Im Gegensatz zu der Funktion ANZAHL wird hier die Anzahl der Zellen ermittelt, die nicht leer sind.

Also würde =ANZAHL2(B3:B15) bei unserem Beispiel 12 ergeben.

2.2.3 MAX

Aus einem definierten Bereich wird der maximale Wert ausgegeben.

Die Zahlen können einzeln (z.B. =MAX(250.000;350.000;300.000)) oder als Bereich (z.B. =MAX(B4:B6)) in die Funktion eingegeben werden.

Beide Möglichkeiten würden den Wert 350.000 ausgeben.

Als Beispiel für eine gemischte Eingabe ergäbe =MAX(B4:B6;450000) den Wert 450.000.

2.2.4 MIN

Diese Funktion liefert den kleinsten Wert eines Wertbereiches bzw. einzelner Werte.

Die Ausführungen zu MAX gelten hier analog.

=MIN(B4:B6) würde den Wert 250.000 ergeben.

2.2.5 MITTELWERT

Hierbei wird der Mittelwert bzw. der Durchschnitt definierter Argumente ermittelt.

=MITTELWERT(B4:B6) ergibt 300.000.

2.2.6 ZÄHLENWENN

Als erweiterte Ausführung der Funktion ANZAHL2 erlaubt ZÄHLENWENN die Eingabe zusätzlicher Kriterien.

Nur jene Zellen, die diese erfüllen werden gezählt.

=ZÄHLENWENN(C4:C15;11%) würde 2 ergeben, da nur die Zellen C4 und C10 die Kriterien erfüllen.

2.3 Text-Funktionen

2.3.1 GROSS

Diese Funktion wandelt einen Text in Grossbuchstaben um.

Ein Text kann in diesem Zusammenhang entweder eine Zeichenfolge, also beispielsweise ein Wort, oder ein Zellbezug, beispielsweise A3, sein. Bei der zweiten Form wird der Inhalt der angesprochenen Zelle in Grossbuchstaben umgewandelt.

Beispiel: Ist der Inhalt der Zelle G8 „Fachhochschule“, so ergibt der Befehl =GROSS(G8) „FACHHOCHSCHULE“.

2.3.2 GROSS2

Diese Funktion wandelt den ersten Buchstaben aller Wörter einer Zeichenfolge in Grossbuchstaben um, während alle folgenden Buchstaben in Kleinbuchstaben umgewandelt werden.

Auch hier kann ein Bezug auf eine Zelle gemacht werden.

Beispiel: Ist der Inhalt der Zelle F17 „Excel ist super“, so ergibt der Befehl =GROSS2(F17) „Excel Ist Super“

2.3.3 KLEIN

Diese Funktion ist wie die Funktion GROSS zu sehen, wobei hier der entsprechende Text nicht in Gross-, sondern in Kleinbuchstaben umgewandelt wird.

Beispiel: Ist der Inhalt der Zelle G8 „ExcEL“, so ergibt der Befehl =KLEIN(G8) „excel“.

2.3.4 VERKETTEN

Mit Hilfe dieser Funktion ist es möglich, einzelne Textelemente zu einer Zeichenkette, beispielsweise einem zusammengesetzten Wort oder einem Satz, zu verknüpfen.

Der Syntax für diese Funktion lautet =VERKETTEN(Text1;Text2;…;Text30).

Wie aus der Syntaxbeschreibung bereits hervorgeht, ist es möglich, bis zu 30 Argumente zu verketten. Auch auf Zellen kann mit dieser Funktion Bezug genommen werden.

Es ist möglich, an Stelle der Funktion VERKETTEN den Operator „&“ zwischen den einzelnen Texten zu verwenden (=(Text1&Text2&…&Text30), siehe Abbildung 4)

2.4 ArithmetischeFunktionen

2.4.1 RUNDEN

Diese Funktion rundet eine Zahl auf eine bestimmte Anzahl an Dezimalstellen.

=RUNDEN (Zahl;Anzahl der Dezimalstellen)

Unter Zahl ist der Wert zu verstehen, der auf- oder abgerundet wird.

Anzahl der Dezimalstellen gibt an, auf wie viele Dezimalstellen die Zahl auf- bzw. abgerundet werden soll.

Ist die Anzahl der Dezimalstellen > 0, wird der Wert auf die angegebene Anzahl der Nachkommastellen gerundet.

Ist die Anzahl der Dezimalstellen = 0, wird der Wert auf die nächste ganze Zahl gerundet.

Ist die Anzahl der Dezimalstellen < 0, wird der Wert auf die Anzahl der eingegebenen Vorkommastellen gerundet.

Beispiel:

=Runden(12,78;1) ergibt 12,8

=Runden(-25,489;2) ergibt –25,49

=Runden(21,5;-1) ergibt 20

2.4.2 ABRUNDEN

Diese Funktion rundet die gewählte Zahl immer auf die gewünschten Dezimalstellen ab. Die Eingabe und die Reaktion sind gleichwertig der Funktion des Runden.

=Abrunden(Zahl;Anzahl der Dezimalstellen)

Beispiel:

=Abrunden(12,78;1) ergibt 12,7

2.4.3 AUFRUNDEN

Rundet die Zahl auf die gewünschten Dezimalstellen immer auf. Diese Funktion ist auch gleich der des Runden.

=Aufrunden(Zahl;Anzahl der Dezimalstellen)

Beispiel:

=Aufrunden(25,711;2) ergibt 25,72

2.4.4 WURZEL

Berechnet die Quadratwurzel der gewünschten Zahl.

=Wurzel(Zahl)

Zahl gibt an, aus welchem Wert man die Wurzel ziehen möchte. Bei einer negativen Zahl erhält man kein Ergebnis, man müsste erst den Betrag daraus errechnen, dies kann mit dem Befehl abs realisiert werden.

Beispiele:

=Wurzel(16) ergibt 4

=Wurzel(-16) ergibt #ZAHL!

=Wurzel(abs(-16)) ergibt 4

2.5 Logische Funktionen: WENN, UND, ODER

2.5.1 WENN-Funktion

Mit der WENN-Funktion werden Bedingungen abgefragt. Abhängig von dieser Bedingung erfolgt meist die Ausgabe eines bestimmten (festgelegten) Ergebnisses.

Wichtig: Die WENN-Funktion wird in der Zelle erzeugt, in der das Ergebnis stehen soll

Syntax:

WENN(Prüfung;Dann-Wert;Sonst-Wert)

Prüfung	Zelle X > Wert Y	Prüfung, ob WAHR oder FALSCH
Dann-Wert	Wert Y oder Text Y	ergibt die Prüfung WAHR, so wird der Wert Y oder Text Y eingetragen
Sonst-Wert	Wert Z oder Text Z	wenn die Prüfung FALSCH ergibt, wird der Wert Z oder Text Z eingetragen

Beispiel 1:

Jeder Vertreter, der für das Unternehmen Aufträge mit einem Wert von über 30.000 EUR beschafft, bekommt 300 EUR Prämie.

Vertreter	Aufträge im Wert von...
Huber	42.000 EUR
Kamliz	13.000 EUR
Meier	26.000 EUR
Meirhofer	23.400 EUR
Müller	39.000 EUR
Swoboda	28.300 EUR

Mittels Funktionsassistent:

Eingabe in die Befehlszeile:

=WENN(B7>30000;300;0)

Verschachtelte WENN-Funktionen:

Damit es möglich ist, komplizierte Bedingungen zu formulieren, können bis zu sieben WENN-Funktionen als Dann-Wert- und Sonst-Wert-Argumente hintereinander geschachtelt werden.

In solchen Fällen wird die zweite Wenn-Anweisung das Argument „Sonst-Wert“ der ersten Wenn-Anweisung. Dies gilt auch für die darauffolgenden; die dritte Wenn-Anweisung ist das Argument „Sonst-Wert“ der zweiten Wenn-Anweisung. Ergibt also die erste Prüfung das Ergebnis WAHR, wird die der Dann-Wert ausgewiesen. Ergibt die Prüfung FALSCH, wird die zweite Wenn-Anwesiung ausgeführt usw.

Beispiel 2:

Vertreter, die Aufträge über 40.000 EUR beschaffen, arbeiten „ausgezeichnet“, von 30.000 bis 40.000 EUR arbeiten diese Vertreter „gut“, 20.000 bis 30.000 EUR ist für das Unternehmen „ausreichend“ und bei einem Gesamtwert der Verträge von unter 20.000 EUR wird der Vertreter gemahnt.

Vertreter	Aufträge im Wert von...
Huber	42.000 EUR
Kamliz	13.000 EUR
Meier	26.000 EUR
Meirhofer	23.400 EUR
Müller	39.000 EUR
Swoboda	28.300 EUR

Mittels Funktionsassistent:

Eingabe in die Befehlszeile:

=WENN(B5>40000;"ausgezeichnet";WENN(B5>30000;"gut";WENN(B5>20000;"ausreichend";WENN(B5<20000;"bitte sofort feuern"))))

Funktionsschema:

Wenn	B5>40000
Dann	„ausgezeichnet“
Sonst	Wenn	B5>30000
	Dann	„gut“
	Sonst	Wenn	B5>20000
		Dann	„ausreichend“
		Sonst		Wenn	B5<20000
				Dann	„bitte sofort feuern“
				Sonst

Beispiel 3:

Blutdruckmessungen:

Messungen	Ergebnisse
Jan	200
Feb	210
Mrz	196
Apr	187
Mai	234
Jun	221
Jul	179
Aug	188
Sep	200
Okt	209
Nov	245
Dez	231

Hat der Patient im Durchschnitt einen Blutdruck von über 210, so ist er in akuter Herzinfarkt-Gefahr!

Eingabe in die Befehlszeile:

=WENN(Mittelwert(B4:B15)>210;“Gefahr“;“im grünen Bereich“)

Eine Formel kann verschachtelte Funktionen in bis zu sieben Ebenen enthalten. Wenn eine Funktion 2 als Argument in Funktion 1 verwendet wird, ist die Funktion 2 eine Funktion zweiter Ebene.

Im vorigen Beispiel ist der „Mittelwert“ eine Funktion zweiter Ebene, weil sie Argumente der Funktion WENN darstellt. Eine weitere Verschachtelung innerhalb der Funktion Mittelwert, wäre dann eine Funktion dritter Ebene usw.

2.5.2 ODER

Um mehrere Bedingungen gleichzeitig aufstellen zu können, werden Logische Funktionen (BOOLEsche Funktionen) verwendet. Diese Funktionen haben die gemeinsame Eigenschaft, dass sie nur zwei Werte als Ergebnis liefern können: TRUE (intern durch den Wert -1 dargestellt) und FALSE (intern durch den Wert 0 dargestellt).l

Logische Operatoren sind: UND, ODER, NICHT

Liefert WAHR, wenn (mindestens) ein Argument WAHR ist; liefert FALSCH, wenn alle Argumente FALSCH sind.

Syntax:

ODER(Wahrheitswert1;Wahrheitswert2;...)

Wahrheitswert1;Wahrheitswert2;... sind bis zu 30 Bedingungen, die überprüft werden sollen und jeweils WAHR oder FALSCH sind.

2.5.3 UND

Liefert WAHR, wenn alle Argumente WAHR sind. Sind die Aussagen eines oder mehrerer Argumente FALSCH, liefert diese Funktion den Wert FALSCH.

Syntax:

UND(Wahrheitswert1;Wahrheitswert2;...)

Wahrheitswert1;Wahrheitswert2;... sind bis zu 30 Bedingungen, die überprüft werden sollen und jeweils WAHR oder FALSCH sein können.

Angenommen wir möchten den Inhalt der Zelle B5 nur dann anzeigen lassen, wenn eine Zahl zwischen 1 und 100 drinnen steht. Steht eine andere Zahl drin möchten wird, dass die Meldung „außerhalb des Bereichs“ angezeigt wird.

=WENN(UND(1<B5;B5>100);B5;“außerhalb des Bereichs“)

® steht 103 drin => „außerhalb des Bereichs“ wird angezeigt

® steht die Zahl 67 in der Zelle B5 => 67 wird ausgewiesen

2.5.4 NICHT

Kehrt den Wert des Arguments um. NICHT wird dann eingesetzt, wenn sicherzustellen ist, dass ein Wert NICHT mit einem bestimmten Wert übereinstimmt.

SYNTAX:

NICHT(Wahrheitswert)

Ergibt die Bedingung den Wahrheitswert WAHR, ist das Ergebnis der Funktion FALSCH und umgekehrt.

Überblick:

Operator	Bedingung	Ist WAHR, wenn...
ODER	ODER(B2=3;B2=13;B2=23)	...in der Zelle B2 der Wert 3, 13 ODER 23 steht
	ODER(Ein<>0;Aus<>0)	...im Bereich „Ein“ ODER „Aus“ ein beliebiger Wert steht
UND	UND(B4=3;C3=“ja“)	...in der Zelle B4 die Zahl 3 steht UND in der Zelle C3 der text „ja“ steht
	UND(Menge<>0;Preis<>0)	...im Bereich „Menge“ UND gleichzeitig im Bereich „Preis“ ein Wert steht
NICHT	NICHT(A2=3)	...in der Zelle A2 nicht der Wert 3 steht

2.5.5 SUMMEWENN

Addiert Zahlen, die mit den Suchkriterien übereinstimmen.

SYNTAX:
SUMMEWENN(Bereich;Kriterien;Summe-Bereich)

Angenommen die Zellen A1 bis A5 enthalten Preise zu denen 4 Häuser verkauft werden sollen. 10000, 200000, 300000, 400000, 500000. Die Zellen B1 bis B5 enthalten zu jedem dieser Immobilienpreise die entsprechenden Verkaufsprovisionen. 7000, 14000, 21000, 28000, 35000.

SUMMEWENN(A1:A5;”>160000”;B1:B5)

® alle Provisionen der verkauften Häuser mit über 16000 Wert werden zusammengezählt.

® das Ergebnis also: 98000,-

3 Arbeiten mit Datenbanken in Excel

3.1 Sortieren

Markieren Sie den entsprechenden Datenbereich und wählen Sie [Daten] - [Sortieren]!

3.2 Autofilter

Mit Hilfe der Filterfunktion suchen Sie bestimmte Datensätze in Ihrer Liste die bestimmte Kriterien erfüllen.

Beispiel:

Sie planen eine Verkaufsreise nach Wien und wollen dafür die Umsätze aller Wiener Kunden heraussuchen.

Im Menü [Daten] – [Filter] – [AutoFilter] starten sie die Filterfunktion:

3.3 Teilergebnisse

Eine sehr praktische Funktion im Zusammenhang mit umfangreicherem Datenmaterial ist die Berechnung von Teilergebnissen.

Achten Sie auf die Syntax der Funktion TEILERGEBNIS(), die nach jeder Gruppierung eingefügt wird! Welche Bedeutung haben die einzelnen Argumente?

Ist es möglich, auch die Mittelwerte der Ausgaben in den einzelnen Gebieten zu berechnen?

4 Datenbankfunktionen

Mit Hilfe dieser Funktionen können Datenbanktabellen ausgewertet werden. Alle folgenden Datenbankfunktionen besitzen die gleiche Schreibweise (Syntax).

Funktionsname(Datenbank;Datenbankfeld;Suchkriterien)

4.1 DBMAX

Liefert den größten Wert aus einem Zellbereich, der den definierten Kriterien entspricht.

Diese Funktion wird in unserem Beispiel für die Ermittlung des höchsten Gehaltes eingesetzt.

=DBMAX(A3:D28;“Gehalt“; H3:H4)

Das bedeutet, dadurch, dass als Kriterium Abteilung und Einkauf (ABT u. EK bzw. H3:H4) gewählt wurde, wird der Inhalt der Zelle mit dem maximalen Wert, der in der Spalte ABT den Text EK enthält, in die Zelle „höchstes Gehalt“ eingetragen.

4.2 DBMIN

Die Ausführungen für DBMIN gelten analog zu denen von DBMAX, nur, dass hier der kleinste Wert ermittelt wird.

4.3 DBSUMME

Bei DBSUMME wird, wie der Name schon sagt, die Summe jener Felder im gewählten Bereich gebildet, die die Kriterien erfüllen.

Um in unserem Beispiel die Summe der Gehälter der gewählten Abteilung zu berechnen, bedient man sich der DBSUMME.

=DBSUMME(A3:D28;“Gehalt“;H3:H4)

Hierbei werden die Gehälter, die das Kriterium EK erfüllen einfach aufsummiert.

4.4 DBANZAHL

Gibt die Anzahl der Zellen in einer Spalte einer Datenbank zurück, welche die angegebenen Bedingungen erfüllen. In unserem Fall wird die Anzahl der Mitarbeiter der Abteilung EK ausgegeben.

Wird das Datenbankfeld nicht angegeben (im Beispiel H3:H4), berücksichtigt DBANZAHL alle zu der Datenbank gehörenden Datensätze, die den Suchkriterien entsprechen.

4.5 DBMITTELWERT

Liefert den Mittelwert von Einträgen einer Spalte bzw. Liste, die die angegebenen Suchkriterien erfüllen.

In unserem Beispiel wäre das Durchschnittsgehalt der Mitarbeiter der Abteilung EK zu ermitteln.

=DBMITTELWERT(A3:D28;“Gehalt“;H3:H4)

Zuerst wird wieder die Datenbank (A3:D28) definiert, anschließend die Überschrift jener Spalte, aus deren Inhalt der Mittelwert errechnet werden soll („Gehalt“), als Datenbankfeld eingegeben. Als Suchkriterium wird EK aus der Spalte Abt gewählt, was durch die Zellen H3 und H4 realisiert wird.

4.6 SVERWEIS (Senkrechter Verweis)

Die Funktion SVERWEIS sucht anhand bestimmter Merkmale einen dazugehörigen Wert.

Syntax:

SVERWEIS(Suchkriterien;Matrix;Spaltenindex;Bereich_Verweis)

Anhand des Beispiels soll für jeden Umsatz ein Provisionssatz ermittelt werden.

Da sich der Provisionssatz aus dem jeweilig erzielten Umsatz ergibt, wird die Spalte Umsatz (B4) als Suchkriterium eingegeben.

Die Provisionstabelle stellt hier die konstante Matrix (F5:G15) dar, mithilfe derer der Provisionssatz ermittelt werden soll.

SVERWEIS sucht nun in der 1. Spalte der Matrix nach dem Suchkriterium und gibt bei der ersten Möglichkeit einen Wert aus, den er aus der Spalte, die mit dem Spaltenindex (in unserem Fall 2) exakt oder ungefähr übereinstimmt.

Wenn das Suchkriterium nur ungefähr mit dem Element der ersten Matrix-Spalte übereinstimmen soll, so muss diese aufsteigend sortiert sein um keinen falschen Wert zurück zu bekommen.

In diesem Fall muss Bereich_Verweis als wahr definiert werden.

Wenn SVERWEIS dann keine exakte Übereinstimmung findet, wird der nächstkleinere Wert herangezogen.

Falsch würde hier bedeuten, dass das Suchkriterium einem Element der ersten Matrix-Spalte genau entspricht und so muss die erste Matrix-Spalte auch nicht sortiert sein.

=SVERWEIS(B4;$F$5:$G$15;2;WAHR)

in die Zelle C4 eingegeben liefert den ersten Provisionssatz.

4.7 WVERWEIS (Waagrechter Verweis)

Angaben für SVERWEIS gelten hier analog, jedoch die Funktion WVERWEIS sucht im Gegensatz zu SVERWEIS waagerecht in der ersten Zeile der Matrix nach dem Suchwert.

5 Pivot-Tabellen und Pivot-Diagramme

5.1 Einführung

Begriffsdefinition „PIVOT“:

Pivot [pi'vo:] der od. das; -s, -s <aus gleichbed. fr. pivot, weitere Herkunft ungeklärt>: Schwenkzapfen an Drehkränen u. a. (Quelle: DUDEN – Deutsches Universalwörterbuch )

pivotieren <nach fr. pivoter «sich drehen»>: sich um das Standbein drehen (Basketball) (Quelle: DUDEN – Das große Fremdwörterbuch)

Pivot-Tabellen stellen eine besondere, interaktive Tabellenansicht dar. Mit ihnen können große Datenmengen mittels verschiedener Filter- und Berechnungsmethoden übersichtlich dargestellt werden. Durch automatisch erzeugte Schaltflächen kann die Ansicht der Tabelle auch verändert werden. Bei diesen Änderungen bleiben die Tabelleninhalte unberührt, die aber auch beliebig verändert, gelöscht oder ergänzt werden könnten.

Zweck:

Mit Pivot-Tabellen können Sie folgende Auswertungen durchführen:

· Gesamtergebnisse von Spalten und Zeilen ermitteln

· Teilergebnisse für einzelne Datenfelder ermitteln

· Spezielle Berechnungen durchführen

· Daten zusammenfassen

· Wichtige Datenbereiche einblenden und Daten filtern

Durch Änderung des Layouts können Daten aus verschiedenen Perspektiven betrachtet werden

5.2 Erstellung einer Pivot-Tabelle

Eine neue Pivot-Tabelle erstellt man in drei Schritten mit dem PivotTable- und PivotChart-Assistenten.

Pivot-Tabellen können auf verschiedenartigen Datenquellen basieren.

auf Excel-Datenbanken bzw. Excel-Listen
auf externen Datenquellen
auf mehreren Konsolidierungsbereiche
auf anderen Pivot-Tabellen

5.2.1 Pivot-Tabellen aus Excel-Daten

Wir gehen von der Datei PIVOT1.XLS aus, deren Arbeitsblatt Tabelle1 folgendes Aussehen hat:

Sie sehen hier eine Liste mit Einträgen einer Immobiliengesellschaft, die verschiedene Objekte in Deutschland betreut:

Beachten Sie, dass in der ersten Zeile Spaltenüberschriften nötig sind!

Die Aufgabenstellung: Es ist zu ermitteln, welcher Verantwortliche wie viele Mietverträge abschließen konnte, und zwar gruppiert nach Gebiet.

Solche Aufgaben sind typischerweise mit Pivot-Tabellen bzw. Diagrammen zu lösen.

Schritt 1: Datenquelle bestimmen und Berichtsauswahl

Zuerst muss entweder die gesamte Tabelle oder ein Feld in der Tabelle markiert werden.

Anschließend wird im Menüpunkt [Daten] unter dem Namen [PivotTable- und PivotChart-Bericht] der Assistent aufgerufen.

In diesem Fenster kann zunächst ausgewählt werden, ob die Daten aus einer Excel-Liste oder -Datenbank, einer externen Datenquelle, mehreren Excel-Tabellenblättern oder einer anderen Pivot-Tabelle genommen werden sollen.

Im selben Dialog steht die Erstellung einer Pivot-Tabelle oder einer Pivot-Grafik zur Auswahl.

Schritt 2: Datenbereich angeben bzw. überprüfen:

Hier wird der Datenbereich eingeben, oder die Daten der Tabelle markiert.

Man gibt zusätzlich auch die Spaltenüberschrift mit an.

Schritt 3: Feldauswahl, Layout und Fertigstellung:

Im 3 Schritt können Sie scheinbar nur entscheiden, ob die Pivot-Tabelle auf einem neuen oder im bestehenden Tabellenblatt erstellt werden soll.

Wir empfehlen aber an dieser Stelle, die Schaltfläche „Layout“ anzuklicken, bevor Sie den Assistenten beenden. Hier verbirgt sich nämlich die entscheidende Konfiguration der Pivot-Tabelle.

Durch Klicken auf die Schaltfläche Layout erscheint ein Dialog, in dem man durch Ziehen mit der Maus die Feldnamen in den Zeilen-, Spalten-, Daten- oder Seitenbereich schieben kann, um das Layout der Pivot-Tabelle festzulegen. Man kann Felder in jeden gewünschten Pivot-Tabellenbereich ziehen (Drag & Drop).

Nach dem Ziehen der Felder sollte Ihr Dialogfenster so aussehen:

Für die korrekte Erstellung von Pivot-Tabellen sind also mindestens drei Angaben nötig:

Zeilenüberschrift
Spaltenüberschrift
Datenfeld mit einer Aggregatfunktion

Zum Begriff Aggregatfunktion (das Wort „aggregieren“ bedeutet „zusammenfassen“): Mit Pivot-Tabellen können nicht nur Summen gebildet werden. Klicken Sie doppelt auf das Datenfeld (hier: Summe von Mietverträge), dann erhalten Sie folgendes Fenster:

Hier können durch das Betätigen der Schaltfläche „Ausblenden“ unerwünschte Felder ausgeblendet werden. Es bestehen auch noch erweiterte Optionen (Anzeige von Prozentanteilen, Differenzen usw.).

Über die Schaltfläche „Optionen“ ist es möglich, spezielle Einstellungen zur Anzeige der Daten vorzunehmen:

Die fertige Pivot-Tabelle könnte dann in etwa so aussehen:

Sie sehen den Arbeitsbereich „PivotTable-Feldliste“, mit dem Sie nachträglich Felder zu einem der Bereiche hinzufügen können.

Klicken Sie doppelt auf das Feld „Zeitraum“, so wird als zusätzliche Gliederung in den Zeilen die Quartalsgliederung eingefügt:

Sie können Gruppierungsdetails anzeigen und ausblenden. Wählen Sie dazu im Kontextmenü des Feldes „Verantwortlicher“ den Menüpunkt [Gruppierung und Detail anzeigen] - [Detail ausblenden].

5.2.2 Pivot-Tabellen aus anderen Pivot-Tabellen erstellen

Pivot-Tabellen, die aus anderen Pivot-Tabellen entstehen, können Auswertungen der Daten vereinfachen bzw. beschleunigen.

Wenn eine Arbeitsmappe bereits eine Pivot-Tabelle enthält und auf Basis der gleichen Daten noch eine weitere Pivot-Tabelle erstellt werden soll, blendet Excel automatisch ein Dialogfenster ein, in welchem auf diese Möglichkeit hingewiesen wird.

Der Zugriff einer Pivot-Tabelle auf eine bestehende Pivot-Tabelle bietet folgende Vorteile:

Unterschiedliche Ansichten einer Pivot-Tabelle können gleichzeitig betrachtet werden
Zeitaufwändige Abfragen aus externen Datenquellen können vermieden werden
Bei Datenänderungen in einer Pivot-Tabelle, kann durch Aufrufen des Menüpunktes „Daten“ – „Daten aktualisieren“ eine automat. Aktualisierung der Daten in einer anderen Pivot-Tabelle bewirkt werden

Wir wollen diese Variante auf unser Beispiel anwenden. Erstellen Sie zunächst mit [Einfügen] - [Tabellenblatt] ein leeres weiteres Tabellenblatt.

Rufen Sie dann wieder mit [Extras] - [PivotTable und PivotChart-Assistent] den Pivot-Tabellen-Assistenten auf:

Wählen Sie dieses Mal „Anderen PivotTable Bericht oder PivotChart-Bericht“ als Datenquelle. Klicken Sie auf die Schaltfläche „Weiter“.

Klicken Sie dieses Mal nicht auf die Schaltfläche „Layout“, damit Sie sehen, welche Optionen Sie in diesem Fall haben:

Auch hier haben Sie die Möglichkeit, die gewünschten Felder in die passenden Bereiche zu ziehen.

5.2.3 Pivot-Tabellen aus externen Daten

Um Daten von außerhalb Microsoft Excel zusammenzufassen und zu analysieren, wie z. B. die Absatzzahlen eines Unternehmens in einer Datenbank, können Daten aus externen Quellen abgerufen werden, einschließlich Datenbanken, Textdateien und Websites im Internet.

Schritt 1: Datenquelle bestimmen und Berichtsauswahl

Nachdem der Assistent gestartet wurde, wird festgelegt, dass die Daten aus einer externen Datenquelle stammen sollen.

Schritt 2: Quelldaten auswählen

Hier wird ausgewählt, woher die externen Daten abgerufen werden.

Es kann ausgewählt werden zwischen relationalen Datenbanken (z.b.: MS Access, MS SQL-Server, Excel-Tabellen, Textdatei-Datenbanken), Abfragen und OLAP-Datenbanken (On-Line Analytical Processing). OLAP-Datenbanken stellen ihre Daten nicht in Tabellen, sondern dreidimensional (cubes) dar. Die Daten von OLAP-Datenbanken werden von speziellen Servern aufgrund spezieller Abfragen berechnet, bevor sie an Excel weitergegeben werden. Das ermöglicht die Verarbeitung sehr großer Datenmengen.

Schritt 3: Feldauswahl, Layout und Fertigstellung:

Dies wurde bereits ausführlich vorher beschrieben.

5.2.4 Tabellen in Pivot-Tabellen konsolidieren

Beim Konsolidieren von Tabellen in eine Pivot-Tabelle, können Daten beliebig vieler, ähnlicher Tabellen in einer übersichtlich zusammengefasst werden. Excel erstellt nämlich eine Pivot-Tabelle aufgrund der Zeilen- und Spaltenbeschriftungen der verschiedenen Tabellenbereiche.

Schritt 1: Datenquelle bestimmen und Berichtsauswahl

Um mehrere Konsolidierungsbereiche auszuwählen muss im Menüpunkt „Daten“ unter „Pivot-Tabelle und Pivot-Chart-Bericht“ die dritte Auswahlmöglichkeit angeklickt werden.

Schritt 2: Einfache oder benutzerdefinierte Seitenfelderstellung

einfache Seitenfelderstellung: Hierbei wird ein Seitenfeld mit genau einem Eintrag für jeden Tabellenbereich von Excel automatisch erstellt

benutzerdefinierte Seitenfelderstellung: Zu jedem Tabellenbereich des Berichts können Einträge für maximal vier Seitenfelder manuell festgelegt werden, um nur bestimmte Daten aus einzelnen Tabellen zu analysieren.

Geben Sie die Bereiche an, die Sie konsolidieren wollen.

Schritt 3: Fertigstellung

Siehe vorher!

5.3 Pivot-Diagramme

Durch Anklicken des Diagramm-Assistenten-Symbols können Sie direkt aus einer Pivot-Tabelle ein entsprechendes Diagramm erzeugen.

6 Datenaustausch zwischen Excel und Word

Beispiel: Ein Teil einer Excel-Tabelle soll in ein Word-Dokument eingefügt werden.

Dazu wird in der Excel-Tabelle der zu übertragende Zellbereich markiert und - zum Beispiel mit der Tastenkombination Strg-C – in die Zwischenablage kopiert.

Nun wechselt man ins Word-Dokument und wählen [Bearbeiten] – [Inhalte einfügen].

Dabei bestehen folgende Möglichkeiten:

Einfügen – Als Excel Arbeitsblatt-Objekt

Doppelklick:

Es bildet sich ein schraffierter Rahmen, innerhalb dessen die Excel-Funktionalität voll zur Verfügung steht. Einfacher Mausklick außerhalb des Rahmens: Rückkehr zum normalen Word-Bildschirm.

Einfügen – Als formatierter RTF-Text

Umsätze 1. Quartal 2001:
	Jän.01	Feb.01	Mär.01
Paris	2,3	2,5	2,7
Rom	3	2,9	2,3
Istambul	1,2	1,5	1,4
	6,5	6,9	6,4

Die Excel-Daten werden in eine Word-Tabelle umgewandelt.

Einfügen – Als Grafik

Es wird nur ein „Screenshot“ erstellt, quasi ein „Foto“ der Excel-Daten.
Doppelklick startet das Word-Grafikprogramm, in welchem grafische Änderungen durchgeführt werden können.

Einfügen – Unformatierter Text:

Umsätze 1. Quartal 2001:

Jän.01 Feb.01 Mär.01

Paris 2,3 2,5 2,7

Rom 3 2,9 2,3

Istambul 1,2 1,5 1,4

6,5 6,9 6,4