Inhaltsübersicht
1 Wiederholung und Erweiterung
1.1.1 Das Bezugssystem in Excel:
2.1 Datums- und Zeitfunktionen
2.5 Logische Funktionen: WENN, UND, ODER
3 Arbeiten mit Datenbanken in Excel
4.6 SVERWEIS (Senkrechter Verweis)
4.7 WVERWEIS (Waagrechter Verweis)
5 Pivot-Tabellen und Pivot-Diagramme
5.2 Erstellung einer Pivot-Tabelle
5.2.1 Pivot-Tabellen aus Excel-Daten
5.2.2 Pivot-Tabellen aus anderen Pivot-Tabellen erstellen
5.2.3 Pivot-Tabellen aus externen Daten
5.2.4 Tabellen in Pivot-Tabellen konsolidieren
6 Datenaustausch zwischen Excel und Word
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 (=).
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:
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.
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
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.
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:
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:
Der Funktionsassistent kann auf verschiedene Arten gestartet werden:
Durch verschiedene Eingabemasken und Erklärungen wird das Handhaben der Funktion vereinfacht.
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.
Diese Funktion gibt das heutige Datum an.
Die Syntax für diese Funktion lautet =HEUTE ().
Diese Funktion liefert die fortlaufende Zahl des aktuellen Datums und der aktuellen Uhrzeit.
Die Syntax für diese Funktion lautet =JETZT().
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
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.
Gibt das Jahr eines Datums als fortlaufende Zahl an. Diese Funktion funktioniert nach dem selben Prinzip, wie Tag und Monat.
Argumente können in den folgenden Funktionen jeweils als Bereich, einzeln oder gemischt eingefügt werden. Siehe Beispiel in 4.3.
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.
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.
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.
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.
Hierbei wird der Mittelwert bzw. der Durchschnitt definierter Argumente ermittelt.
=MITTELWERT(B4:B6) ergibt 300.000.
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.
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“.
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“
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“.
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)
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
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
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
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
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.
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.
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
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 |
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,-
Markieren Sie den entsprechenden Datenbereich und wählen Sie [Daten] - [Sortieren]!
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:
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?
Mit Hilfe dieser Funktionen können Datenbanktabellen ausgewertet werden. Alle folgenden Datenbankfunktionen besitzen die gleiche Schreibweise (Syntax).
Funktionsname(Datenbank;Datenbankfeld;Suchkriterien)
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.
Die Ausführungen für DBMIN gelten analog zu denen von DBMAX, nur, dass hier der kleinste Wert ermittelt wird.
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.
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.
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.
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.
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.
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.
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
Eine neue Pivot-Tabelle erstellt man in drei Schritten mit dem PivotTable- und PivotChart-Assistenten.
Pivot-Tabellen können auf verschiedenartigen Datenquellen basieren.
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.
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:
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].
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:
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.
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.
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
Geben Sie die Bereiche an, die Sie konsolidieren wollen.
Siehe vorher!
Durch Anklicken des
Diagramm-Assistenten-Symbols können Sie direkt aus einer Pivot-Tabelle ein
entsprechendes Diagramm erzeugen.
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:
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 |
Einfügen – Als Grafik
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
Verknüpfen – Als Excel Arbeitsblatt-Objekt
Verknüpfen – 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 |
2,3 |
2,9 |
2,3 |
Istambul |
1,2 |
1,5 |
1,4 |
|
5,8 |
6,9 |
6,4 |
Verknüpfen – Als Grafik
Verknüpfte Objekte werden immer aktualisiert, wenn sich die Originaldaten ändern (egal, welcher Objekttyp ausgewählt wurde!)
Arbeiten mit Fenstern
Arbeiten mit Dialogfenstern
Arbeiten innerhalb eines Bearbeitungsfeldes
Arbeiten in Menüs
Sie können alle Befehle direkt mit der Tastatur auswählen:Drücken Sie
zuerst die <Alt><X>Tastenkombination,wobei X für den unterstrichenen
Buchstaben im Menü steht (z.B.<Alt><T> zum Öffen des Menüs Format)
und anschließend den im Befehlsnamen geöffneten Menüs unter-
strichenen Buchstaben (z.B.<S> für Spalte).Der Befehl Format/Spalte
kann also direkt mit der Tastenkombination <Alt><T><S> aufgerufen
werden.
Seitenansicht
Funktionstasten