MS Excel 2010 - Profiwissen Daten auswerten und abfragen€¦ · Excel bietet dazu zwei...

Verwaltungsakademie Berlin

MS Excel 2010 - Profiwissen Daten auswerten und abfragen

SEMINARUNTERLAGE

DDAATTEENN AAUUSSWWEERRTTEENN VVEERRWWAALLTTUUNNGGSSAAKKAADDEEMMIIEE BBEERRLLIINN DDAATTEENNBBAANNKK

Inhaltsverzeichnis 1 Datenbank............................................................................................................................... 4

1.1 Vorüberlegungen - Neue Datenbank............................................................................. 5 1.2 Bereiche in einer Datenbank ......................................................................................... 5 1.3 Filter ............................................................................................................................... 7 1.4 Kriterien eintragen.......................................................................................................... 9 1.5 Sortieren....................................................................................................................... 11 1.6 Zusammenfassung: Datenbank................................................................................... 13

2 Datenbankfunktionen .......................................................................................................... 14 2.1 DBANZAHL.................................................................................................................. 14 2.2 DBMITTELWERT......................................................................................................... 14 2.3 DBANZAHL.................................................................................................................. 14 2.4 DBMAX ........................................................................................................................ 14 2.5 DBMIN.......................................................................................................................... 14 2.6 DBSUMME................................................................................................................... 14

3 Die Pivot-Tabelle .................................................................................................................. 16 3.1 Was ist eine Pivot-Tabelle? ......................................................................................... 16 3.2 Eine Datenliste wird benötigt ....................................................................................... 16 3.3 Die Pivot-Tabellen-Werkzeuge .................................................................................... 16 3.4 Den Pivot-Tabellen-Bericht erstellen ........................................................................... 19 3.5 Detailwissen zur Pivot-Tabelle..................................................................................... 20 3.6 Die Pivot-Tabelle wird verändert.................................................................................. 20 3.7 Zeilen und Spalten vertauschen .................................................................................. 21 3.8 Inhalte ausblenden....................................................................................................... 22 3.9 Extreme Werte anzeigen ............................................................................................. 22 3.10 PowerPivot ................................................................................................................... 23

4 Excel-Daten gliedern............................................................................................................ 24 4.1 Eine Beispieltabelle...................................................................................................... 24 4.2 Zellbereiche ein- und ausblenden................................................................................ 25 4.3 Die Gliederung entfernen............................................................................................. 26 4.4 Ebenen und Bereiche selbst bestimmen ..................................................................... 26

5 Teilsummen .......................................................................................................................... 27 6 Datenimport und -export ..................................................................................................... 28

6.1 Datenaustausch über die Zwischenablage.................................................................. 28 6.2 OLE und DDE .............................................................................................................. 30 6.3 Object Linking and Embedding - OLE ......................................................................... 31 6.4 Dynamischer Datenaustausch - DDE.......................................................................... 35

7 Stichwortverzeichnis ........................................................................................................... 40

3

DDAATTEENNBBAANNKK VVEERRWWAALLTTUUNNGGSSAAKKAADDEEMMIIEE BBEERRLLIINN DDAATTEENN AAUUSSWWEERRTTEENN

1 Datenbank Im Prinzip beschreibt der Begriff Datenbank nichts Neues. Mit großer Wahrscheinlichkeit haben Sie zu Hause oder an Ihrem Arbeitsplatz Zugang zu Datenbanken, auch wenn diese nicht so ge-nannt werden.

Stellen Sie sich einen großen Karteikasten mit vielen Karteikarten vor. Angenommen Sie müss-ten eine Lieferanten-Kartei in Ihrem Büro anlegen und bearbeiten. Dabei fallen viele typische Bearbeitungsvorgänge an. Vergleichen Sie die Vorgänge in der nachfolgenden Gegenüberstel-lung. Sie erkennen sicher, dass die Datenbanken auf den Computern von Ihrem Grundprinzip sehr vieles von den alten Papierdatenbanken übernommen haben.

Karteikasten Datenbank

Einen Karteikasten neu einrichten. Eine Datenbank neu anlegen.

Einen bestehenden Karteikasten öffnen. Eine bestehende Datenbank öffnen.

Für neue Lieferanten neue Karteikarten ausfül-len und der Kartei hinzufügen.

Neue Anschriften hinzufügen.

Lieferanten, mit denen keine Geschäftsbezie-hung mehr besteht, löschen.

Nicht mehr benötigte Anschriften löschen.

Bei Bestellungen den entsprechenden Lieferan-ten suchen.

Bestimmte Lieferanten suchen.

Die Anschrift ändern, wenn ein Lieferant neue Geschäftsräume bezieht.

Anschriften ändern.

Nach getaner Arbeit den Karteikasten schlie-ßen.

Datenbank schließen und Programm beenden.

Alle Tätigkeiten im Karteikasten haben ihre Entsprechung in der Datenbank. Auch der Aufbau der Karteikarten findet sich in den Datenbanken wieder. Auf vielen Karteikarten sind Bereiche oder Felder bereits vorgedruckt. Dort tragen Sie die dazugehörigen Informationen ein.

Eine Access Datenbank-Tabelle

Die Computer-Datenbank legt die Anschriften in einer Liste ab. Die Spalten heißen hier Felder oder besser Daten-Felder und die Zeilen Daten-Sätze. Daten-Felder werden über Namen an-gesprochen, den Feld-Namen.

4


1.1 Vorüberlegungen - Neue Datenbank

Bevor eine neue Datenbank erzeugt wird, ist zu überlegen, wie die Struktur aussehen soll. Wer sich dabei etwas Zeit nimmt, kann vielleicht später viel Zeit sparen.

Zuerst ist zu überlegen:

"Welche Informationseinheiten brauchen Sie?"

"Auf welche Daten müssen Sie später zugreifen?"

"Welche Daten müssen sortiert werden?"

Wenn Sie auf den Nachnamen zugreifen wollen, ist es sinnvoll, für Vor- und Nachnamen zwei getrennte Daten-Felder vorzusehen. Dagegen kann der Straßenname mit der Hausnummer in einem Feld abgespeichert werden; denn es kommt wohl selten vor, dass jemand die Datenbank nach der Hausnummer sortieren möchte. Postleitzahl und Ort wiederum, legt man am besten in getrennten Feldern ab.

In Zweifelsfällen in getrennten Datenfeldern ablegen.

1.2 Bereiche in einer Datenbank

Eine vollständige Datenbank in Excel besteht aus 3 Bereichen:

Listenbereich Hier stehen die Datensätze.

Kriterienbereich Um bestimmte Datensätze herauszufiltern, tragen Sie hier die Bedingungen ein.

Ausgabebereich Die Datensätze, auf die die Suchkriterien (Bedingungen) zutreffen, können in den Ausgabe-bereich kopiert werden.

Erstellen Sie eine neue, leere Arbeitsmappe über das Datei-Menü, Neu…, Leere Arbeitsmap-pe, oder drücken Sie die Tastenkombination (Strg)+(N). Speichern Sie die Mappe unter dem Dateinamen Betrieb, Kunden-Datenbank ab.

Listenbereich

Die ersten 8 Zeilen lassen Sie bitte für den Kriterienbereich frei. Beginnen Sie deshalb erst mit der Zeile 9, und tragen Sie die Feldnamen in den Bereich A9:F9 in getrennten Zellen ein:

VORNAME NACHNAME PLZ ORT GEBDAT UMSATZ

Markieren Sie jetzt diese Feldnamen, also den Bereich A9:F9, und geben Sie diesem Bereich den Namen Datenbank.

Daten-Eingabemaske

Selbstverständlich können Sie die Daten, wie bisher, direkt in die Zellen eintippen. Aber Excel bietet Ihnen auch an, die Daten über eine Maske einzugeben. Voraussetzung ist allerdings, dass in der Symbolleiste für den Schnellzugriff das Symbol Maske vorhan-den ist.

Symbolleiste für den Schnellzugriff mit dem Symbol Maske

5


Lassen Sie den Bereich A9:F9 (Datenbank) noch markiert, und klicken Sie auf das Symbol Maske. Eine leere Eingabemaske erscheint auf dem Bildschirm.

Eine leere Eingabemaske

Falls die nachfolgende Meldung erscheint, ist der Bereich A9:F9 nicht benannt. Kli-cken Sie auf die Schaltfläche [_OK_], dann erscheint die Eingabemaske trotzdem.

Um die Spaltenbeschriftung festzulegen, klicken Sie auf [_OK_].

Füllen Sie jetzt die einzelnen Felder aus. Die Daten entnehmen Sie dem nachfolgenden Bild-schirmfoto. Auf das nächste Eingabefeld in der Maske gehen Sie mit der

(ÿ) Tabtaste.

Wenn ein Datensatz ausgefüllt ist, bekommen Sie mit der (¢)-Taste eine neue, leere Maske für den nächsten Satz.

Datenbank-Liste

Bei der Eingabe von Zahlen gibt es allerdings ein Problem: Wenn die Zahlen mit einer 0 (Null) beginnen, lässt Excel die Null weg. Das ist natürlich bei den Postleitzahlen (PLZ) für Leipzig und

6


Dresden nicht akzeptabel. Das gleiche Problem würde auch bei dem Eintippen einer Telefon-Vorwahl bestehen.

Excel bietet dazu zwei Lösungsmöglichkeiten an:

Wenn die Postleitzahl mit einer Null beginnt, geben Sie in der Eingabemaske im Feld PLZ am Anfang als erstes das Zeichen Hochkomma ' ein ((ª_)+(#)). Ein Beispiel sehen Sie in dem vorherigen Bild bei der Zelle C18 oben in der Bearbeitungsleiste.

Oder: Bevor Sie die Eingabemaske öffnen, markieren Sie den Bereich für die Postleitzahlen (C10:C19) und formatieren die Zellen als Text:

Text-Format

Nachdem Sie den letzten Datensatz in die Maske eingegeben haben, klicken Sie auf die Schaltfläche [Schließen].

Die Eingabemaske mit dem 10. Datensatz

1.3 Filter

Häufig arbeiten Sie nicht mit allen Datensätzen in der Liste. Oft müssen Sie auf ganz bestimmte Informationen zugreifen. Excel bietet Ihnen sehr komfortable Möglichkeiten, Daten auszufiltern:

Auto-Filter

Spezial-Filter.

Auto-Filter

Mit dem AutoFilter können Sie besonders schnell auf bestimmte Datensätze zugreifen:

7


1. Stellen Sie den Zellcursor auf den ersten Feldnamen, auf VORNAME.

2. Wählen Sie: Registerkarte Daten, Sortieren und Filtern, Symbol Filtern.

3. Rechts von jedem Feldnamen steht jetzt ein Listenpfeil .

Registerkarte Daten,

Gruppe Sortieren und Filtern

Autofilter mit Listenpfeilen neben den Feldnamen

4. Klicken Sie bei dem Feld ORT auf den Pfeil , und wäh-len Sie als Filter München aus. Dazu deaktivieren Sie das Kontrollkästchen (Alles auswählen) und aktivieren

München. Klicken Sie auf [_OK_].

5. Jetzt sehen Sie in dem Listenbereich nur noch die gefilter-

ten Datensätze. Der Listenpfeil bei ORT hat sich ge-ändert, damit jeder gleich erkennt, dass bei diesem Feld der Filter gesetzt wurde.

6. Um alle Datensätze wieder anzeigen zu lassen, öffnen

Sie die Filter-Liste ORT und klicken auf den Befehl Fil-ter löschen.

Spezial-Filter

Der Spezial-Filter bietet Ihnen erweiterte Möglichkeiten. Es ist aber ein Kriterienbereich einzurichten, in den Sie die Filter-Bedingungen, die Kriterien eintragen.

Filter löschen

Kriterienbereich Für den Kriterienbereich benötigen Sie eine Zeile mit den Feldnamen und zunächst nur eine leere Zeile darunter. Kopieren Sie den Bereich mit den Feldnamen A9:F9 in die Zelle A3.

Ausgabebereich Die Datensätze, auf den die Kriterien (Bedingungen) zutreffen, können von Excel einschließlich der Zeile mit den Feldnamen in den Ausgabebereich, z.B. I 3:N 3, kopiert werden.

8


Im gesamten Bereich unterhalb des Ausgabebereichs (bis zur letzten Zeile) dürfen keine weiteren Daten stehen. Sonst werden sie gelöscht!

Die 3 Bereiche im vertikal geteilten Fenster

1.4 Kriterien eintragen

Um gezielt auf bestimmte Informationen zuzugreifen, schreiben Sie im Kriterienbereich unter dem entsprechenden Feldnamen das Kriterium hin. Ein Kriterium wird auch Bedingung oder Filter genannt.

Tragen Sie unterhalb des Feldnamens ORT im Kriterienbereich in der Zelle D4 den Filter München ein. Stellen Sie da-nach den Zellcursor auf die linke obere Zelle A9 im Listenbereich, also auf den Feldnamen VORNAME. Jetzt wählen Sie

Eintrag im Kriterienbereich

Registerkarte Daten, Gruppe Sortieren und Filtern, Symbol .

Wählen Sie aus, und tragen Sie ein:

1. An eine andere Stelle kopieren.

2. Der Listenbereich $A$9:$F$19 ist bereits eingetragen.

3. Kriterienbereich: A3:F4

4. Kopieren nach: I3:N3

Wenn Sie die Bereiche im Zeigen-Modus über-nehmen, werden die Adressen absolut gesetzt. Beim Eintippen können Sie den Tabellennamen und die $-Zeichen aber weglassen.

5. Klicken Sie auf die Schaltfläche [_OK_].

Ein Spezialfilter

9


Die Datensätze mit dem ORT München wurden in den Ausgabebereich kopiert:

Ausgabe mit Spezialfilter

Filtern mit einem Operator

1. Bitte löschen Sie wieder den Filter München in der Zelle D4. Jetzt sollen die Datensätze angezeigt werden, in denen im Feld PLZ eine größere Zahl als 60000 steht:

> 6 0 0 0 0

Diese Bedingung tragen Sie im Kriterienbereich in der Spalte (Feld) PLZ in die Zelle C4 ein, und dann lassen Sie die Datensätze in den Ausgabebereich kopieren.

Achtung: Stellen Sie zuerst den Zellcursor auf die linke obere Zelle A9 im Listenbe-reich (VORNAME), bevor Sie das Symbol anklicken.

2. Löschen Sie wieder den Filter im Feld PLZ. Es sind die Datensätze auszuwählen, bei denen im Feld UMSATZ ein Betrag kleiner als 5000 steht:

< 5 0 0 0

Filter mit mehreren Bedingungen

Bei der Abfrage mit mehr als einer Bedingung, sind zwei Möglichkeiten zu unterscheiden:

U N D -Verknüpfung Die gefilterten Datensätze sollen alle Bedingungen erfüllen. Hierzu müssen Sie die Bedin-gungen im Kriterienbereich in eine Zeile schreiben.

Der Kriterienbereich ist eventuell neu zu bestimmen: Er umfasst die Zeile mit den Feldna-men und eine Zeile.

O D E R -Verknüpfung Die gefilterten Datensätze müssen nur eine der Bedingungen erfüllen. Hierzu müssen Sie die Bedingungen im Kriterienbereich in untereinander liegende Zeilen schreiben.

Der Kriterienbereich ist eventuell neu zu bestimmen: Er umfasst die Zeile mit den Feldna-men und alle Zeilen, in denen ein Suchkriterium steht.

10


Das Postleitzahlen- und Telefonvorwahl-Problem

Wenn Sie alle Datensätze ausfiltern möchten, deren PLZ kleiner ist als 60000, werden die Da-tensätze Leipzig und Dresden nicht mitkopiert. Diese Postleitzahlen beginnen ja mit einer 0 (Null) und sind vom Typ Text.

Um dieses Problem zu lösen, fügen Sie noch eine ODER-Verknüpfung in die Zelle C5 ein: >0* Das Stern-Zeichen * fungiert hier als Platzhalter, als Joker, und Sie kennen dieses Zeichen viel-leicht schon von der Suche im Windows-Explorer.

Da der Kriterienbereich sich nun verändert hat, müssen Sie ihn in der Dialogbox Spezialfilter um eine Zeile vergrößern: A3:F5.

Postleitzahlen kleiner als 60000

1.5 Sortieren

Der Befehl für das Sortieren steht zwar auch auf der Daten-Registerkarte, er kann aber auf je-den Bereich verwendet werden. Unsere Datenbank ist nun nach dem UMSATZ aufsteigend zu sortieren. Excel erkennt selbständig eine Liste:

1. Stellen Sie den Zellcursor im Listenbereich auf den Feldnamen UMSATZ.

2. Rufen Sie auf: Registerkarte Daten, Gruppe Sortieren und Filtern, Symbol Sortieren.

3. In dem Dialogfeld Sortieren wählen Sie aus:

- Spalte Sortieren nach: UMSATZ.

- Daten haben Überschriften (Damit werden die Feldnamen nicht mitsortiert).

11


Sortieren nach UMSATZ

4. Klicken Sie auf die Schaltfläche [_OK_].

Für einfache Sortieraufgaben können Sie auch die rechts stehenden Symbole anklicken: Von A bis Z bzw. Von Z bis A sortieren.

12


13

1.6 Zusammenfassung: Datenbank

Vorüberlegungen - Neue Datenbank Bevor eine neue Datenbank erzeugt wird, ist zu überlegen, wie die Struktur mit den Feld-namen aussehen soll.

Bereiche in einer Datenbank Eine vollständige Datenbank in Excel besteht aus den drei Bereichen: Listenbereich, Krite-rienbereich, Ausgabebereich.

Listenbereich In die erste Zeile des Listenbereichs sind in getrennten Zellen nebeneinander die Feldna-men einzutragen.

Kriterienbereich und Ausgabebereich Kopieren Sie den Bereich mit den Feldnamen aus dem Listenbereich in zwei getrennte freie Bereiche im Arbeitsblatt. Unterhalb des Ausgabebereichs dürfen keine Daten stehen.

Filter Wenn Sie gezielt auf bestimmte Daten zugreifen möchten, können Sie den Auto-Filter oder den Spezial-Filter einsetzen.

Auto-Filter Stellen Sie den Zellcursor auf den ersten Feldnamen, und rufen Sie auf: Registerkarte Da-ten, Gruppe Sortieren und Filtern, Symbol Filtern. Bei jedem Feldnamen können Sie jetzt aus einer Liste einen Filter auswählen.

Spezial-Filter 1. Tragen Sie im Kriterienbereich unter dem entsprechendem Feldnamen das Kriterium

(Bedingung) ein, z.B. bei dem Feld ORT das Wort München.

2. Rufen Sie auf: Registerkarte Daten, Gruppe Sortieren und Filtern, Symbol Erweitert.

3. In dem Dialogfeld wählen Sie aus, bzw. tragen Sie ein: An eine andere Stelle kopie-ren, Listenbereich, Kriterienbereich, Ausgabebereich (=Kopieren nach).

UND-Verknüpfung Die gefilterten Datensätze sollen alle Bedingungen erfüllen. Hierzu müssen Sie die Bedin-gungen im Kriterienbereich in eine Zeile schreiben.

ODER-Verknüpfung Die gefilterten Datensätze müssen nur eine der Bedingungen erfüllen. Hierzu müssen Sie die Bedingungen im Kriterienbereich in untereinander liegende Zeilen schreiben.

Sortierung Zum Sortieren einer Liste stellen Sie den Zellcursor in die Spalte, nach der sor-tiert werden soll. Wählen Sie den Weg: Registerkarte Daten, Gruppe Sortie-ren und Filtern, Symbol Sortieren, oder klicken Sie auf die Symbole Von A bis Z bzw. Von Z bis A sortieren.

DDAATTEENNBBAANNKKFFUUNNKKTTIIOONNEENN VVEERRWWAALLTTUUNNGGSSAAKKAADDEEMMIIEE BBEERRLLIINN DDAATTEENN AAUUSSWWEERRTTEENN

2 Datenbankfunktionen

2.1 DBANZAHL

Zählt die Zellen; die in einer Spalte einer Liste oder Datenbank Zahlen enthalten; die den angegebenen Bedingungen entsprechen.

Syntax: DBANZAHL(Datenbank;Feld;Kriterien)

2.2 DBMITTELWERT

Bildet den Mittelwert für die Werte in einer Spalte einer Liste oder Datenbank; die die an-gegebenen Bedingungen erfüllen.

Syntax: DBMITTELWERT(Datenbank;Feld;Kriterien)

2.3 DBANZAHL

Zählt die nicht leeren Zellen in einer Spalte einer Liste oder Datenbank; die die angege-benen Bedingungen erfüllen.

Das Feldargument ist optional. Wenn das Feld weggelassen wird; zählt DBANZAHL alle Datensätze in der Datenbank; die diesen Kriterien entsprechen.

Syntax: DBANZAHL(Datenbank;Feld;Kriterien)

2.4 DBMAX

Gibt den größten Zahlenwert in einer Spalte einer Liste oder Datenbank zurück; der die angegebenen Bedingungen erfüllt.

Syntax: DBMAX(Datenbank;Feld;Kriterien)

2.5 DBMIN

Gibt den kleinsten Zahlenwert in einer Spalte einer Liste oder Datenbank zurück; der die angegebenen Bedingungen erfüllt.

Syntax: DBMIN(Datenbank;Feld;Kriterien)

2.6 DBSUMME

Addiert die Zahlen in einer Spalte einer Liste oder Datenbank; die die angegebenen Be-dingungen erfüllen.

Syntax: DBSUMME(Datenbank;Feld;Kriterien)

Daten-bank

ist der Bereich von Zellen, aus denen die Liste oder Datenbank besteht. Bei einer Datenbank handelt es sich um eine Liste zusammengehöriger Daten, in der Zeilen von zusammengehörigen Informationen als Datensätze und Spalten von Daten als Felder angesehen werden. Die erste Zeile der Liste enthält die Beschriftungen für die einzelnen Spalten.

Feld gibt an, welche Spalte in der Funktion verwendet wird. Geben Sie die Spaltenbe-schriftung in doppelte Anführungszeichen eingeschlossen an, z. B. "Alter" oder "Ertrag," oder geben Sie eine Zahl (ohne Anführungszeichen) ein, die der Position der Spalte in der Liste entspricht: 1 für die erste Spalte, 2 für die zweite Spalte usw.

14

DDAATTEENN AAUUSSWWEERRTTEENN VVEERRWWAALLTTUUNNGGSSAAKKAADDEEMMIIEE BBEERRLLIINN DDAATTEENNBBAANNKKFFUUNNKKTTIIOONNEENN

15

Kriterien sind der Zellbereich, der die von Ihnen angegebenen Bedingungen enthält. Es kann jeder beliebige Bereich für das Argument "Kriterien" angegeben werden, solange das Argument mindestens eine Spaltenbeschriftung und mindestens eine Zeile unter der Spaltenbeschriftung enthält, in der Sie eine Bedingung für die Spal-te angeben.

Beispiel:

Formel Beschreibung (Ergebnis)

=DBANZAHL(A4:E10;"Alter";A1:F2) Diese Funktion durchläuft die Datensätze zu Apfel-bäumen mit einer Höhe zwischen 3,05 m und 4,88 m und zählt, wie viele der Felder Alter in diesen Daten-sätzen Zahlen enthalten. (1)

=DBANZAHL2(A4:E10;"Gewinn";A1:F2) Diese Funktion durchläuft die Datensätze zu Apfel-bäumen mit einer Höhe zwischen 3,05 m und 4,88 m und zählt, wie viele der Felder Gewinn in diesen Da-tensätzen einen Eintrag enthalten. (1)

=DBMAX(A4:E10;"Gewinn";A1:A3) Der maximale Gewinn, der aus Apfelbäumen und Birnbäumen erzielt wurde. (105)

=DBMIN(A4:E10;"Gewinn";A1:B2) Der geringste Gewinn, der aus Apfelbäumen mit einer Höhe von über 3,05 m erzielt wurde. (75)

=DBSUMME(A4:E10;"Gewinn";A1:A2) Der Gesamtgewinn, der aus den Apfelbäumen erzielt wurde. (225)

=DBSUMME(A4:E10;"Gewinn";A1:F2) Der Gesamtgewinn aus Apfelbäumen mit einer Höhe zwischen 10 und 16. (75)

=DBPRODUKT(A4:E10;"Ertrag";A1:B2) Das Produkt der Erträge, die aus den Apfelbäumen mit über 3,05 m Höhe erzielt wurde. (140)

=DBMITTELWERT(A4:E10;"Ertrag";A1:B2) Der durchschnittliche Ertrag der Apfelbäume mit einer Höhe von über 3,05 m. (12)

=DBMITTELWERT(A4:E10;3;A4:E10) Das Durchschnittsalter aller Bäume in der Datenbank. (13)

DDIIEE PPIIVVOOTT--TTAABBEELLLLEE VVEERRWWAALLTTUUNNGGSSAAKKAADDEEMMIIEE BBEERRLLIINN DDAATTEENN AAUUSSWWEERRTTEENN

3 Die Pivot-Tabelle Der Begriff Pivot stammt ursprünglich aus dem Französischen, wird aber auch inzwischen in der englischen Fachsprache verwendet und bedeutet Dreh- und Angelpunkt.

3.1 Was ist eine Pivot-Tabelle?

Pivot-Tabellen sind interaktive Tabellen, die in der Lage sind, auf schnelle Art und Weise um-fangreiche Datenmengen zusammenzufassen. Mit Pivot-Tabellen besteht die Möglichkeit, Daten unter verschiedenen Gesichtspunkten auszuwerten und zu betrachten. Dadurch haben Sie ein Werkzeug zu einer komfortablen Analysemöglichkeit und vielseitigen Auswertung. Durch schlich-tes Umsetzen von Datenmaterial können Sie Daten aus einem völlig anderen Blickwinkel be-trachten. Beim Aufbau einer Pivot-Tabelle stehen Ihnen die Pivot-Tabellen-Werkzeuge hilfreich zur Seite.

3.2 Eine Datenliste wird benötigt

Damit Sie mit einer Pivot-Tabelle arbeiten können, benötigen Sie zunächst eine Datenliste:

Die Datenliste

1. Öffnen Sie die Datei Pivot Rohdaten.xlsx, oder tippen Sie die Datenliste in eine neue, leere Mappe ein.

2. Speichern Sie die Mappe unter dem Namen Pivot ab.

3. Der Gewinn wird in der Zelle H2 durch die Formel

=(G2-F2)*E2 errechnet.

4. Kopieren Sie die Formel in die übrigen Felder der Spalte H.

3.3 Die Pivot-Tabellen-Werkzeuge

Excel stellt Ihnen zum Aufbau einer Pivot-Tabelle die Pivot-Tabellen-Werkzeuge zur Verfügung. Hier heißen Sie PivotTable-Tools. Mit den Werkzeugen wird der Umgang mit Pivot-Tabellen einfacher. Gehen Sie anhand der Beispiel-Datenliste folgendermaßen vor:

16

DDAATTEENN AAUUSSWWEERRTTEENN VVEERRWWAALLTTUUNNGGSSAAKKAADDEEMMIIEE BBEERRLLIINN DDAATTEENNÜÜBBEERRPPRRÜÜFFUUNNGG

1. Klicken Sie im Menüband auf der Registerkarte Einfügen in der Befehlsgruppe Tabellen auf den oberen Teil der Schaltfläche Pi-votTable. Wenn Sie dagegen auf den unteren Teil dieser Schaltflä-che klicken, öffnen Sie ein Untermenü, aus dem Sie den rechts ste-henden Befehl wählen.

2. Bestimmen Sie in dem nachfolgenden Dialogfeld, woher sich Excel die Daten für die Pivot-Tabelle holen soll. Bei einer externen Da-tenquelle bietet das Programm eine ganze Reihe von Verbin-dungsmöglichkeiten an. Wählen Sie die Option Tabelle oder Be-reich auswählen.

Registerkarte Einfügen,

Gruppe Tabellen

3. Überprüfen Sie den Vorschlag von Excel für den Daten-Tabellenbereich. Für das aktuelle Beispiel wird der Bereich A1 bis H11 benötigt:

Daten-Tabellenbereich festlegen

4. Legen Sie fest, wo der PivotTable-Bericht abgelegt werden soll: Neues Arbeitsblatt.

5. Klicken Sie auf die Schaltfläche [_OK_]. Excel hat das Tabellenblatt 4 eingefügt, auf dem einiges Neues zu sehen ist:

Die neue Tabelle4 ist für den PivotTable-Bericht vorbereitet

17


Aufgabenbereich

Auf der rechten Seite ist ein Aufgabenbereich mit der PivotTable-Feldliste geöffnet. In den Excel-Vorgänger-Versionen 2002/XP und 2003 wurden Aufgabenbereiche häufiger eingesetzt, als jetzt in der Version 2010.

Die Trennlinie zwischen dem Aufgaben- und dem Arbeitsbereich können Sie mit der Maus verschieben. Führen Sie dazu den Maus-zeiger auf die senkrechte Trennlinie. Er wird dort zu einem Doppel-pfeil: . Bei gedrückter linker Maustaste ziehen Sie die Trennlinie an die neue Position und lassen die Maustaste wieder los.

Sie können einen Aufgabenbereich auch frei auf dem Bildschirm platzieren. Dazu zeigen Sie mit der Maus auf die Bereichs-Titelleiste und ziehen nun bei gedrückter linker Maustaste den Auf-gabenbereich an eine andere Stelle. Der Mauszeiger hat dabei die Form eines Vierfachpfeils.

Mit einem Mausdoppelklick auf die Titelleiste stellen Sie den Aufga-benbereich wieder zurück an seinen alten Platz im rechten Teil des Excel-Fensters. Wie bei allen Fenstern, blenden Sie mit dem Schließen-Symbol in der Titelleiste den Aufgabenbereich aus.

Das Abschnitt-Symbol unterhalb der Titelleiste öffnet das nachfolgende Untermenü. Hierin werden aber nur die einzelnen Abschnitte innerhalb des Aufgabenbereichs anders angeordnet:

Aufgabenbereich mit der

PivotTable-Feldliste

Über die Titelleiste den Aufgabenbereich an eine

andere Stelle ziehen

Die Abschnitte innerhalb des Aufgabenbereichs anordnen

Der Bereich für den Pivot-Tabellen-Bericht

Bereich für den

Pivot-Tabellen-Bericht

Im linken Teil des neuen Tabellenblatts 4 ist ein Bereich für den Pi-vot-Tabellen-Bericht vorbereitet. Der Zellcursor steht in diesem Be-reich.

Kontextwerkzeug

Jetzt sehen Sie eine Besonderheit von Office 2010: das Kontextwerk-zeug. Im Menüband sind zwei spezielle Registerkarten für das Bear-beiten von Pivot-Tabellen hinzugekommen. Zusätzlich ist oben in der Titelleiste eine neue Schaltfläche positioniert: [PivotTable-Tools]. Falls Sie beim Bearbeiten der Pivot-Tabelle auf eine andere Regis-terkarte klicken, können Sie immer wieder über diese Schaltfläche auf die Registerkarten Optionen und Entwurf (PivotTable-Tools) zurückkehren. Sie werden automatisch geschlossen, wenn der Zell-cursor den Bereich für den Pivot-Tabellen-Bericht verlässt.

18


Das Kontextwerkzeug PivotTable-Tools

Kontextwerkzeuge werden Ihnen in allen Programmen von Office 2010 vielfältig angeboten, in Excel 2010 beispielsweise gibt es auch die Bildtools, wenn ein Bild oder eine Grafik markiert ist.

3.4 Den Pivot-Tabellen-Bericht erstellen

1. Der Zellcursor steht in dem Bereich für den Pivot-Tabellen-Bericht. Damit wird in der Titel-leiste die Kontextwerkzeug-Schaltfläche [PivotTable-Tools] angezeigt.

2. Sollte der Aufgabenbereich mit der PivotTable-Feldliste nicht geöffnet sein, klicken Sie auf der Registerkarten Optionen (PivotTable-Tools) in der Gruppe Anzeigen auf die Schaltfläche Feldliste.

3. Zeigen Sie in der Liste auf das Feld Artikel: . Bei ge-drückter linker Maustaste kopieren Sie den Feldnamen nach unten in den Bereich Zeilenbeschriftungen. Nach dem Loslassen der Maustaste sind die Namen der Arti-kel in der A-Spalte platziert.

4. Genauso ziehen Sie im Aufgabenbereich das Feld Filiale in den Bereich Spaltenbeschriftungen

5. und das Feld Verkaufsmenge in den Bereich Werte.

6. Überprüfen Sie auf Ihrem Bildschirm den Aufgabenbe-reich: In der Liste sind jetzt die Kontrollkästchen dieser drei Felder aktiviert. Der erste Pivot-Tabellen-Bericht ist fertig gestellt:

Aufgabenbereich

Die fertige Pivot-Tabelle

19


3.5 Detailwissen zur Pivot-Tabelle

Im Bereich Werte werden die einzelnen Werte automatisch durch eine Summen-Funktion ver-knüpft.

Anstelle der Summe können Sie auch eine andere Funktion zur Berechnung verwenden. Dazu klicken Sie im Aufgabenbereich im Bereich Werte auf die Schaltfläche . Aus dem sich öffnenden Menü wählen Sie den Befehl Wertfeldeinstellun-gen:

Eine andere Funktion auswählen

Menü

Im Feld Benutzerdefinierter Name können Sie den Namen eines markierten Pivot-Tabellen-Datenfeldes ändern, ohne dass die Quelldaten davon berührt werden.

Unter Werte zusammenfassen nach bestimmen Sie die Zusammenfassungsfunktion. Es ste-hen folgende Funktionen zur Verfügung:

Summe

Anzahl

Mittelwert

Maximum

Minimum

Produkt

Anzahl Zahlen

Standardabweichung (Stichprobe)

Standardabweichung (Grundgesamtheit)

Varianz (Stichprobe)

Varianz (Grundgesamtheit).

Über die Schaltfläche [Zahlenformat] öffnen Sie das Dialogfeld Zellen formatieren, in dem Sie ein anderes Zahlenformat festlegen können.

3.6 Die Pivot-Tabelle wird verändert

Um Felder und Positionen in der Pivot-Tabelle zu verändern, gehen Sie folgendermaßen vor:

1. Stellen Sie den Zellcursor in die Pivot-Tabelle.

2. Sollte der Aufgabenbereich mit der PivotTable-Feldliste jetzt nicht geöffnet sein, klicken Sie auf der Registerkarten Optionen (PivotTable-Tools) in der Gruppe Anzeigen auf die Schaltfläche Feldliste .

3. Ziehen Sie die Schaltfläche Filiale mit gedrückter linker Maustaste aus dem Bereich Spal-tenbeschriftungen heraus, und schieben Sie stattdessen die Schaltfläche Monat dorthin.

20


4. Anstelle der Summe der Verkaufsmenge soll jetzt der Gewinn im Bereich Werte er-scheinen. Entfernen Sie die Schaltfläche Summe von Verkaufsmenge, und schieben Sie den Gewinn in den Bereich Werte:

Die veränderte Pivot-Tabelle

Wenn Sie nun anschließend die Artikel-Nr. auch noch einblenden wollen, ist dies kein Problem mehr: Ziehen Sie die Schaltfläche Artikel-Nr. mit gedrückter linker Maustaste zusätzlich in den Bereich Zeilenbeschriftungen. Jetzt stehen dort zwei Schaltflächen, die Sie auch einfach mit der Maus in der Reihenfolge verändern können.

3.7 Zeilen und Spalten vertauschen

Die Daten in der Pivot-Tabelle sind zeilen- und/oder spaltenweise geordnet. Die Felder können Sie durch Ziehen mit der Maus umstellen:

1. Um die Pivot-Tabelle übersichtlicher zu gestalten, entfernen Sie wieder das Feld Artikel-Nr. aus den Bereich Zeilenbeschriftungen. Dazu ziehen Sie die Schaltfläche Artikel-Nr. bei gedrückter linker Maustaste aus dem Aufgabenbereich heraus nach links in die Tabelle.

2. Zum Vertauschen haben Sie nun zwei Möglichkeiten: a) Ziehen Sie die Schaltfläche im Aufgabenbereich mit der

Maus in den jeweils anderen Bereich, also von Zeilen-beschriftungen nach Spaltenbeschriftungen und umge-kehrt, oder

b) klicken Sie im Aufgabenbereich auf die Schaltfläche Monat bzw. Artikel. Aus dem sich öffnenden Menü wählen Sie den Befehl Wechsel zu Zeilen- bzw. Spal-tenbeschriftungen.

3. In der folgenden Abbildung wurden Zeilen und Spalten ver-tauscht. Um Platz zu sparen, wurde der Text in den Zellen mit den Wörtern Zeilen- bzw. Spaltenbeschriftungen ver-kürzt. Probieren Sie diese Arbeitsschritte aus:

Schaltflächen-Menü

21


Zeilen und Spalten wurden vertauscht

4. Zu Übungszwecken soll anschließend die alte Anordnung wiederhergestellt werden.

3.8 Inhalte ausblenden Sie können auch die Inhalte einzelner Felder ausblenden, zum Beispiel einen kompletten Monat:

1. Klicken Sie auf den Listenpfeil von der Schaltfläche, deren Inhalte ausgeblendet werden sollen, beispielsweise auf Zeilen. Excel öffnet ein Menü, in dem Sie die uner-wünschten Daten ausblenden können.

2. Klicken Sie einfach auf das Häkchen neben dem Monats-namen Juli, um diesen Monat aus der Pivot-Tabelle zu entfernen.

3. Verlassen Sie das Fenster über [_OK_]. Excel zeigt den Monat nicht mehr an.

4. Um den Juli wieder einzublenden, aktivieren Sie wieder das Kontrollkästchen.

Menü zum Ausblenden unerwünschter Daten

3.9 Extreme Werte anzeigen

Die Daten einer Pivot-Tabelle liegen in stark komprimierter Form vor. Sollten Daten vom Durch-schnitt abweichen, gibt es in Excel eine Methode, sie genauer zu betrachten. Diese Möglichkeit steht selbstverständlich für alle Daten zur Verfügung.

1. Klicken Sie doppelt auf die Zelle F9, mit dem Wert 6720 (Gesamtergebnis Mantel).

2. Excel richtet automatisch ein neues Tabellenarbeitsblatt ein und zeigt dort die entsprechen-den Datensätze an:

22


23

Die Mantel-Daten wurden aufgeschlüsselt

3.10 PowerPivot

Mit den hier vorgestellten Pivot-Tabellen-Werkzeugen können Sie bis zu maximal eine Million Datensätze verarbeiten. Für noch größere Datenmenge bietet Microsoft ein kostenfreies Add-In für Excel 2010 mit dem Namen PowerPivot zum Herunterladen an. Damit können bis zu 100 Millionen Datensätze aus unterschiedlichen Datenquellen analysiert werden, z.B. Datensätze aus Access, SQL etc.

EEXXCCEELL--DDAATTEENN GGLLIIEEDDEERRNN VVEERRWWAALLTTUUNNGGSSAAKKAADDEEMMIIEE BBEERRLLIINN DDAATTEENN AAUUSSWWEERRTTEENN

4 Excel-Daten gliedern In Excel gibt es eine Möglichkeit, durch den Einsatz verschiedener Gliederungsebenen eine bes-sere Übersicht in umfangreiche Datenbestände zu bringen.

Die Gliederungsfunktion ist für die Arbeit mit sehr umfangreichen Tabellen wichtig.

Die Aufteilung kann erfolgen in:

horizontaler Richtung und vertikaler Richtung.

4.1 Eine Beispieltabelle

Für die Arbeit mit der Gliederungsfunktion wird wiederum eine kleine Beispieltabelle benötigt:

Die Beispieldaten

1. Öffnen Sie die Datei Gliederung Rohdaten.xlsx, oder tippen Sie die Beispieldaten in eine neue, leere Mappe ein.

2. Bilden Sie die Summen der einzelnen Artikel für das 1. Quartal in der Spalte E und für das 2. Quartal in der Spalte I. Verwenden Sie dazu die Schaltfläche Summe auf der Registerkarte Start in der Gruppe Bearbeiten.

3. Genauso bilden Sie die Summen in der 8. Zeile, also im Bereich B8 bis I8.

4. Speichern Sie die Mappe unter dem Namen Gliederung ab.

5. Markieren Sie den Bereich A1 bis I8.

6. Wählen Sie den rechts stehenden Weg: Auf der Re-gisterkarte Daten klicken Sie in der Gruppe Gliede-rung auf den unteren Teil der Schaltfläche Gruppie-ren und in dem Untermenü auf den Befehl Auto-Gliederung.

Hinweis: Wenn im Menüband auf den Registerkarten für eine Befehlsgruppe nicht genügend Platz vorhan-den ist, wird sie als Schaltfläche verkleinert darge-stellt, wie hier bei der Gruppe Gliederung.

Registerkarte Daten, Gruppe Gliederung

In Ihr Tabellenarbeitsblatt werden Gliederungsebenen eingeblendet:

24

DDAATTEENN AAUUSSWWEERRTTEENN VVEERRWWAALLTTUUNNGGSSAAKKAADDEEMMIIEE BBEERRLLIINN EEXXCCEELL--DDAATTEENN GGLLIIEEDDEERRNN

Ebenenbalken

Die Tabelle mit den Gliederungsebenen

Sie sehen, dass Ihre Tabelle in verschiedene Ebenen eingeteilt wurde. Ober-halb der Spaltenköpfe und links neben den Zeilenköpfen befinden sich die Schaltflächen mit den Nummern der Ebenen.

Die Bereiche der einzelnen Ebenen werden durch Balken (Ebenenbalken) kenntlich gemacht.

4.2 Zellbereiche ein- und ausblenden

Mit den Schaltflächen des Gliederungsbereichs sollen nun einzelne Zellbereiche ein- und ausge-blendet werden.

Ein Klick auf eine Schaltfläche mit den Gliederungsebenen verändert die Bildschirm-anzeige:

1. Wenn Sie zum Beispiel die Schaltfläche mit der anklicken, die zu den Spalten gehört, werden die einzelnen Monate ausgeblendet. Lediglich die Quartalsdaten werden gezeigt.

2. Klicken Sie anschließend auf die Schaltfläche mit der , die zu den Zeilen gehört. Nun werden noch die Artikel ausgeblendet.

Spalten:ausblendeneinblenden

Zeilen:ausblendeneinblenden

Details:einblenden

Spalten und Zeilen wurden ausgeblendet

3. Über die Schaltfläche mit der Nummer können Sie die einzelnen Ebenen wieder anzei-gen.

4. Versuchen Sie den ursprünglichen Zustand Ihrer Tabelle wiederherzustellen.

25

EEXXCCEELL--DDAATTEENN GGLLIIEEDDEERRNN VVEERRWWAALLTTUUNNGGSSAAKKAADDEEMMIIEE BBEERRLLIINN DDAATTEENN AAUUSSWWEERRTTEENN

26

Auch über die Ebenenbalken können Sie Daten ein- und ausblenden. Damit besteht beispielsweise die Möglichkeit, nur einen Teil der Daten der ersten Ebene nicht er-scheinen zu lassen:

Details:ausblendeneinblenden

Die Monate April bis Juni sind eingeblendet

4.3 Die Gliederung entfernen

Sie können eine Gliederung selbstverständlich auch wie-der entfernen. Dadurch ändern sich die Daten im Tabel-lenblatt nicht.

Wählen Sie den rechts stehenden Weg: Auf der Register-karte Daten klicken Sie in der Gruppe Gliederung auf den unteren Teil der Schaltfläche Gruppierung aufheben und in dem Untermenü auf den Befehl Gliederung ent-fernen.

Registerkarte Daten, Gruppe Gliederung

4.4 Ebenen und Bereiche selbst bestimmen

Wenn die Funktion AutoGliederung nicht das gewünschte Gliederungsergebnis bringt, besteht die Möglichkeit, Bereiche und Ebenen selbst zu definieren. Dabei gehen Sie folgendermaßen vor:

1. Markieren Sie den Bereich, den Sie gliedern möchten.

Markieren Sie beim Gruppieren von Zeilen und Spalten lediglich die Detailzeilen oder -spalten, aus denen sich die Gruppe zusammensetzt.

2. Klicken Sie auf der Registerkarte Daten in der Gruppe Gliede-

rung auf den oberen Teil der Schaltfläche Gruppierung. In der rechts stehenden Dialogbox wählen Sie die Option Zei-len oder Spalten aus und klicken auf [_OK_].

2. Die Gliederungssymbole werden neben oder über der Gruppe angezeigt.

3. Wiederholen Sie das Markieren von Detailzeilen oder -spalten und die oben aufgeführten Arbeitsschritte so lange, bis Sie alle gewünschten Gliederungsebenen erstellt haben.

Option auswählen

DDAATTEENN AAUUSSWWEERRTTEENN VVEERRWWAALLTTUUNNGGSSAAKKAADDEEMMIIEE BBEERRLLIINN TTEEIILLSSUUMMMMEENN

5 Teilsummen Vielleicht kennen Sie aus den Vorgänger-Versionen von Excel noch das Add-In Teilsummen-Assistent. Dieser half bei der Erstellung von Formeln, um ausgewählte Daten, die vorher defi-nierten Kriterien entsprachen, zu summieren. Seit der Version 2010 gibt es diesen Assistenten nicht mehr. Das Summieren dieser ausgewählten Daten ist aber dennoch möglich: mit der SUMMEWENN-Funktion.

Die SUMMEWENN-Funktion ist folgendermaßen aufgebaut:

=SUMMEWENN(PrüfBereich;Kriterium;Summe_Bereich)

Zunächst benötigen Sie die nachfolgende Tabelle. Danach rechnen Sie die Summen für die ein-zelnen Kategorien aus. Öffnen Sie diese Datei, oder geben Sie die folgenden Daten ein, und speichern Sie die Tabelle unter dem Namen Haushaltsbuch ab:

Tabelle Haushaltsbuch.xlsx

Tragen Sie den Begriff Lebensmittel in die Zelle E4 ein, Benzin in die Zelle E5 und Kleidung in die Zelle E6. Markieren Sie die Zelle F4, in der die Summe der Kosten für Lebensmittel berech-net werden soll. Da der Prüfbereich dem Bereich B4:B19 entspricht und der Summebereich C4:C19, lautet die Formel zur Berechnung

=SUMMEWENN($B$4:$B$19;E4;$C$4:$C$19)

Durch den absoluten Bezug (die Dollarzeichen) ermöglichen Sie das Kopieren der Funktion mit AutoAusfüllen in die Zellen F5 und F6.

Als Kriterium können Sie anstelle des Zellbezugs E4 auch den Begriff „Lebensmit-tel“ in Anführungszeichen (!) eingeben. Die Funktion ist dann aber nicht kopierfähig.

Ergebnis

27

DDAATTEENNIIMMPPOORRTT UUNNDD --EEXXPPOORRTT VVEERRWWAALLTTUUNNGGSSAAKKAADDEEMMIIEE BBEERRLLIINN DDAATTEENN AAUUSSWWEERRTTEENN

6 Datenimport und -export Die verschiedenen Aufgaben, die man per Computer zu erledigen hat, lassen sich nicht immer mit einem Programm ausführen. Sie können in solchen Fällen Daten von verschiedenen Anwen-dungen miteinander kombinieren.

In Excel kann man Daten aus anderen Anwendungen importieren. Umgekehrt ist es aber auch möglich, Daten aus Excel heraus zu exportieren. Die Bearbeitung von Texten überlässt man möglichst einer Textverarbeitung, zum Beispiel Microsoft Word. Nach dem Motto “Ein Bild sagt mehr als 1000 Worte” kann ein Bild, das aus einem Grafikprogramm stammt, in Excel eingefügt werden und Ihre Aussage untermauern.

Um einen Datenaustausch durchzuführen, gibt es verschiedene Möglichkeiten:

Windows- und Office-Zwischenablagen

Importieren von Daten aus einer Datei

OLE (Object Linking and Embedding)

DDE (Dynamic Data Exchange).

6.1 Datenaustausch über die Zwischenablage

Die Zwischenablage wird beim Kopieren und Ausschneiden verwendet. So kopieren Sie Daten von Excel nach Word:

1. Starten Sie noch zusätzlich das Anwendungsprogramm Word,

2. und öffnen Sie in Excel die Arbeitsmappe Verweis.xlsx.

3. Markieren Sie in Excel den Bereich A1:F6.

Die Daten

4. Klicken Sie auf die Schaltfläche Kopieren (auf der Registerkarte Start in der Gruppe Zwischenablage).

5. Wechseln Sie über die Taskleiste zu Word, und setzen Sie dort den Cursor an die Stelle im Dokument, wo Sie den Zellbereich aus Excel einfügen möchten.

6. Klicken Sie in der Gruppe Zwischenablage auf den oberen Teil der Schaltfläche Einfügen.

28

DDAATTEENN AAUUSSWWEERRTTEENN VVEERRWWAALLTTUUNNGGSSAAKKAADDEEMMIIEE BBEERRLLIINN DDAATTEENNIIMMPPOORRTT UUNNDD --EEXXPPOORRTT

Der kopierte Zellbereich wurde in Word eingefügt. Entsprechend funktioniert auch das Kopieren zwischen anderen Anwendungsprogrammen.

Daten wurden von Excel nach Word kopiert

Um eine Zelle oder einen Zellbereich auszuschneiden, gehen Sie genauso vor. Anstelle der Schaltfläche Kopieren verwenden Sie das Symbol zum Ausschneiden.

Durch einen Klick auf den SmartTag können Sie das Einfügeformat noch ändern.

SmartTag mit Einfügeoptionen

Das Symbol Einfügen

Das Symbol Einfügen ist zweigeteilt. Wenn Sie in dem Symbol unten auf das Dreieck klicken, öffnet sich ein Menü, aus dem Sie eine Ein-fügeoption auswählen. Diese Einfügeoptionen werden Ihnen auch in einem Kontextmenü angeboten (rechte Maustaste).

Einfügeoptionen Als Einfügeoptionen können in dem Auswahlmenü (Symbol Einfügen und SmartTag) ein oder mehrere verschiedene Symbole und weitere Befehle platziert sein. Die Symbole bieten zusätzlich eine Voraus-schau: Wenn Sie mit der Maus nur auf ein Symbol zeigen, ohne zu klicken, wird an der Einfügestelle der Inhalt der Windows-Zwischenablage bereits angezeigt. Sie entscheiden, welche Einfüge-option Sie mit einem Mausklick bevorzugen. Da hier je nach Art und Formatierung des Textes oder anderer Objekte eine ganze Reihe verschiedener Schaltflächen angeboten werden, nachfolgend die häu-igsten Einfügeoptionen:

Symbol Einfügen mit Auswahlmenü

f

Einfügen: Der gesamte Zellinhalt der Quelle wird zusammen mit der Formatierung ein-gefügt. Diese Option entspricht dem Anklicken auf den oberen Teil des Symbols.

Formeln: Der Zellinhalt der Quelle wird ohne Formatierung eingefügt. Die Formatierung am Ziel bleibt erhalten.

Ursprüngliche Formatierung beibehalten: Fügt den Inhalt der Windows-Zwischen-ablage ein, die ursprüngliche Formatierung an der Quelle wird beibehalten. Das ist die

29


Standardeinstellung.

Keine Rahmenlinien: wie der Befehl Einfügen, aber ohne Linien.

Transponieren: Inhalte in Spalten werden zusammen mit der Formatierung zeilenförmig eingefügt und umgekehrt.

Werte: Das Ergebnis einer Formel wird ohne Formatierung eingefügt.

Verknüpfung einfügen: Der Inhalt wird ohne Formatierung als Verknüpfung eingefügt, z.B. =$A$2.

Grafik: Fügt eine Grafik (Bild) aus der Zwischenablage in die Präsentation ein.

Inhalte einfügen: Ein Klick auf diesen Befehl am unteren Rand des Auswahlmenüs öffnet das Dialogfeld Inhalte einfügen. Es kann aber unterschiedlich aussehen. Das nachfolgende Dialog-feld erscheint nur, wenn der Inhalt der Windows-Zwischenablage aus Excel stammt!

Das Einfügen genau festlegen

6.2 OLE und DDE

OLE ist die Abkürzung für Object Linking and Embedding und bedeutet Objekte verknüpfen und einbetten. Hinter diesem Begriff steht eine Technik, die Anwendungen miteinander verbindet.

DDE kommt auch wieder aus dem Englischen und heißt Dynamic Data Exchange, zu Deutsch dynamischer Datenaustausch.

Bei einem dynamischen Datenaustausch (DDE) erzeugt das Programm eine Verbindungsstelle. Mit Hilfe der DDE-Technik können verschiedene Anwendungen gemeinsame Informationen nut-zen. Wenn beispielsweise eine in Excel erstellte Tabelle mit einem Word-Dokument verknüpft wurde, und Sie die Tabelle in Excel ändern, wird diese automatisch aktualisiert.

Möchten Sie hingegen Ihren Text mit Word bearbeiten, ohne Excel zu verlassen, können Sie den Word-Text einbetten, anstatt ihn zu verknüpfen. Wenn Sie ein Objekt in Excel einbetten, verwen-den Sie die Werkzeuge eines anderen Programms, um das Objekt zu erstellen oder zu ändern. Soll dieses Objekt anschließend bearbeitet werden, führen Sie lediglich einen Doppelklick auf dem OLE-Objekt aus, um es aufzurufen und die Werkzeuge zur Bearbeitung wieder zu verwen-den.

Einbetten (OLE) sollten Sie ein Objekt immer dann, wenn es nur in Excel existieren soll, und wenn eine Änderung, die am Objekt vorgenommen wird, nur das Dokument betrifft, in dem es sich befindet.

30


Das eingebettete Objekt (OLE) ist Teil des Excel-Arbeitsblattes. Ein verknüpftes Objekt (DDE) dagegen wird unter einem eigenen Namen als Datei auf der Festplatte gespeichert.

6.3 Object Linking and Embedding - OLE

Wollen Sie ein Objekt mit Hilfe eines anderen Anwendungsprogramms erstellen, gehen Sie fol-gendermaßen vor:

1. Klicken Sie auf der Registerkarte Einfügen in der Gruppe Text auf die Schaltfläche Objekt. Das nachfolgende Dialogfeld wird geöffnet:

Dialogfeld Objekt

2. Auf der Registerkarte Neu erstellen wählen Sie unter Objekttyp das entsprechende Pro-gramm aus, z.B. Word und klicken auf die Schaltfläche [_OK_].

Befindet sich in dem Feld Als Symbol ein Häkchen, wird der eingefügte Objekttyp bei eingebetteten Objekten lediglich als Symbol und nicht als Inhalt sichtbar.

3. Bei diesem Beispiel ist Word das Quell- und Excel das Ziel-Programm. Es wurde ein Word-Dokument in Excel eingefügt. Als aktives Objekt ist es mit einer “Kordel” umrahmt:

31


Aktives OLE-Objekt in Excel (Größe und Position des Objekts geändert)

4. Während das OLE-Objekt aktiv ist, arbeiten Sie auch in der Arbeitsumgebung von Word: Registerkarten und Symbole stammen aus Word.

5. Geben Sie Ihren Text in das Word-Dokument ein. Es stehen Ihnen alle Word-Befehle zur Verfügung.

6. Mit einem Mausklick in die Excel-Tabelle außerhalb des Word-Dokuments beenden Sie die Bearbeitung des OLE-Objekts, und mit einem Doppelklick auf das Objekt wechseln Sie wie-der in den Bearbeitungsmodus.

Objekt verschieben

Sie können das Objekt in der Tabelle auch an eine andere Stelle verschieben. Dazu führen Sie die Maus auf den Rand des Word-Objektes. Der Mauszeiger wird dort zu einem Vierfachpfeil. Ziehen Sie bei gedrückter linker Maustaste das Objekt an eine

eue Position.

n

Größe ändern

Möchten Sie die Größe des Objektes ändern, zeigen Sie mit der Maus auf ei-nen Ziehpunkt (Markierungspunkt). Der Mauszeiger wird zu einem Doppelpfeil

, bzw. . Jetzt können Sie bei ge-drückter linker Maustaste die Größe än-dern.

Oder Sie öffnen das Kontextmenü und wählen den Befehl Objekt formatieren. Das rechts stehende Dialogfeld erscheint. Auf dem Register Größe stellen Sie die gewünschte Größe ein.

Die Größe einstellen

32


Ein OLE-Objekt aus einer Datei erstellen

Um eine bereits fertig erstellte Datei in Ihre Arbeitsmappe zu integrieren, gehen Sie folgendermaßen vor:

1. Klicken Sie wieder auf der Registerkarte Einfügen in der Gruppe Text auf die Schaltflä-che Objekt.

2. In dem Dialogfeld Objekt kli-cken Sie auf die Registerseite Aus Datei erstellen.

Sie können von hier aus eine bestehende Datei in Excel ein-betten oder verknüpfen.

Registerseite Aus Datei erstellen

Wenn Sie die Datei verknüpfen wollen, aktivieren Sie unter Verknüpfen das Häk-chen. Sonst wird die Datei eingebettet.

3. Als Nächstes klicken Sie die Schaltfläche [Durchsuchen] an. Es öffnet sich ein Explorerfens-ter. Dort markieren Sie die gewünschte Datei und klicken anschließend auf die Schaltfläche [Öffnen].

4. Sie kehren zurück in das Dialogfeld Objekt. Dieses verlassen Sie über die Schaltfläche [_OK_]. Die Datei wird je nach Einstellung eingebettet oder verknüpft.

Ein OLE-Objekt über die Zwischenablage einfügen

Ein weiteres Verfahren, ein Objekt eines anderen Programms einzubetten, beziehungsweise zu verknüpfen, führt über den auf der Seite 30 erwähnten Befehl Inhalte einfügen, der durch einen Klick auf den unteren Teil des Symbols Einfügen zur Verfügung steht. Während Sie über das Symbol Objekt immer eine komplette Datei in Excel integrieren, können Sie über Inhalte ein-fügen bestimmte Teile einer geöffneten Datei aus der Zwischenablage einfügen. Voraussetzung ist natürlich, dass Sie vorher in dem Quell-Programm die gewünschten Inhalte in die Zwischen-ablage kopiert haben:

33


Fremdobjekte als Inhalte einfügen

Über den Befehl Inhalte einfügen werden zwei verschiedene Dialogfelder aufgeru-fen. Das oben stehende Fenster erscheint, wenn der Inhalt der Windows-Zwischenablage nicht aus Excel stammt. Haben Sie dagegen den letzten Kopierbe-fehl in Excel ausgeführt, wird das auf der Seite 30 beschriebene Dialogfeld geöffnet.

Eine Klangdatei als Symbol anzeigen

Bei der Windows-Installation werden eine Reihe von Klangdateien auf der Festplatte abgelegt. Sie können eine Klangdatei (*.WAV) oder auch eine Videodatei (*.AVI) als OLE-Objekt in Ihre Excel-Tabelle einbetten und als Symbol anzeigen lassen. Mit einem Doppelklick wird die Datei abgespielt. Voraussetzung ist allerdings, dass im Computer eine Soundkarte installiert ist und Lautsprecher angeschlossen sind. Über den Windows-Audiorecorder und ein Mikrofon können Sie selbst auch Sprachdateien aufnehmen.

Rufen Sie auf: Registerkarte Ein-fügen, Gruppe Text, Schaltfläche Objekt. In dem Dialogfeld Objekt wählen Sie auf der Registerseite Aus Datei erstellen über die Schaltfläche [Durchsuchen] die ge-wünschte Datei aus, z.B. C:\Windows\Media Windows-Eingehender Anruf.wav (=Win-dows Ringing.wav). Falls vorhan-den, können Sie aber auch eine MP3-Datei einbinden.

Ein Klangobjekt einbetten

Aktivieren Sie in dem Dialogfeld Objekt das Kontrollkästchen Als Symbol, und schließen Sie das Fenster über die Schaltfläche [_OK_]. Das Objekt wird als Symbol an der Zellcursor-Position in die Tabelle eingefügt.

Schalten Sie die Lautsprecher ein, und starten Sie mit einem Doppelklick die Wiedergabe.

34


6.4 Dynamischer Datenaustausch - DDE

Zwischen einer Excel-Tabelle und einem anderen Windows-Programm, wie Word, können Sie eine dynamische Verbindung herstellen. Wenn beide Programme mit den verbundenen Dateien geöffnet sind, sehen Sie eine Änderung in der Excel-Tabelle auch sofort in dem Word-Dokument:

1. Starten Sie das Programm Word mit einem neuen, leeren Dokument.

2. Schreiben und gestalten Sie die folgende Interne Mitteilung bis zu dem Satz Nachfolgend die gewünschten Tabellen und ein Diagramm:

Die darunter stehenden Daten werden aus einer Excel-Tabelle übernommen.

Schreiben und gestalten Sie die Interne Mitteilung

3. Speichern Sie das Dokument unter dem Namen Break-Even ab.

Zurück zu Excel

Mit einem Mausklick in das Excel-Fenster, über die Taskleiste oder mit der Tastenkombination

(Alt)+(ÿ) Tabtaste

gehen Sie nach Excel zurück. Eventuell müssen Sie bei gedrückter (Alt) -Taste die Tabtaste (ÿ) öfters drücken, um wieder zurückzukommen. Öffnen Sie die Datei Break-Even-Ana-lyse.xlsx:

35


Break-Even-Analyse mit Diagramm

Stellen Sie den Zellcursor in der Tabelle1 auf die Zelle A1 und markieren Sie den Bereich

A1 : D2.

Bereich in die Zwischenablage kopieren

Den markierten Bereich kopieren Sie in die Zwischenablage .

Bevor Sie wieder zurück zu Word gehen, verkleinern Sie das Excel Programm-Fenster auf das

obere Drittel des Bildschirms. Klicken Sie auf das Verkleinern-(Wiederherstell-)Symbol in der oberen rechten Ecke des Excel-Fensters, und ziehen Sie mit der Maus den Fensterrahmen auf die entsprechende Größe. Aber Excel bitte nicht beenden!

Zurück zu Word

Mit einem Mausklick in das Word-Fenster, über die Taskleiste oder mit der Tastenkombination (Alt)+(ÿ) Tabtaste gehen Sie wieder in das Dokument Break-Even zurück.

Daten verknüpfen

Positionieren Sie den Cursor im Word-Dokument unterhalb der letzten Zeile, und klicken Sie auf den unteren Teil des Symbols Einfügen auf der Registerkarte Start in der Gruppe Zwischenab-lage. In dem Untermenü wählen Sie den Befehl Inhalte einfügen.

1. In dem nun erscheinenden Dialogfeld Inhalte einfügen wählen Sie aus:

Verknüpfung einfügen und

Als: Microsoft Excel-Arbeitsmappe-Objekt:

36


Eine Verknüpfung mit dem Excel-Arbeitsblatt herstellen

2. Schließen Sie das Dialogfeld mit [_OK_]. Die Daten aus dem Excel-Arbeitsblatt stehen jetzt in dem Word-Dokument.

Falls nur ein Teil der Excel-Tabelle sichtbar ist, überprüfen und ändern Sie in Word den Zeilenabstand!

Ihre Aufgabe

Übernehmen Sie auch den Excel-Bereich F1:H7 in das Word-Dokument:

Zwei Excel-Tabellen in einem Word-Dokument

Sie können die Excel-Tabellen wie eine Grafik in der Größe verändern (Seite 32). Das Verschie-ben an eine andere Position ist nur möglich, wenn das Objekt nicht mit dem Textumbruch (Lay-out) Mit Text in Zeile formatiert ist.

37


Word Programm-Fenster verkleinern

Das Programm-Fenster von Word verkleinern Sie so weit, dass es unter dem Excel-Fenster steht. Sie können dazu auf das Verkleinern-(Wiederherstell-)Symbol in der obe-ren rechten Ecke des Word-Fensters klicken.

Bei gedrückter linker Maustaste ziehen Sie das Word-Fenster auf die gewünschte Größe. Jetzt sollten Sie im oberen Bildschirm die Excel-Tabelle Break-Even-Analyse.xlsx und im unteren Teil das Word-Dokument sehen.

Daten in der Excel-Tabelle verändern

Klicken Sie in der Excel-Tabelle beispielsweise auf die Zelle C2. Ändern Sie den Wert auf 200 oder 400 und schließen Sie die Eingabe mit der (¢) Taste ab. Sie sollten danach die Änderung auch sofort in dem Word-Dokument sehen.

2 Fenster mit einer Daten-Verknüpfung

Verändert sich nach dem Drücken der Return-Taste in Excel der Wert im Word-Dokument nicht, können Sie der Aktualisierung etwas nachhelfen: Aktivieren Sie im Word-Dokument die entsprechende Tabelle und drücken Sie die (F9)-Taste. Alternativ können Sie auch im Word-Dokument das Datei-Menü öffnen und auf der Seite Infor-mationen unten rechts den Befehl Verknüpfungen mit Dateien bearbeiten wählen. Dieser Befehl ist aber nur aktiv, wenn die Datei mit ihren Verknüpfungen bereits abge-speichert wurde.

In dem Dialogfeld Verknüpfungen klicken Sie auf die Schaltfläche [Jetzt_aktualisieren]. Da-nach schließen Sie wieder das Fenster:

38


39

Verknüpfungen verwalten

SSTTIICCHHWWOORRTTVVEERRZZEEIICCHHNNIISS VVEERRWWAALLTTUUNNGGSSAAKKAADDEEMMIIEE BBEERRLLIINN UUMMSSTTIIEEGG OOFFFFIICCEE 22001100

7 Stichwortverzeichnis

Sortieren und Filtern .................................... 9 A Text ........................................................... 31

Gruppierung .................................................. 26 Add-In ........................................................... 23 Aufgabenbereich........................................... 18

I Aus Datei erstellen........................................ 33 Ausgabebereich .......................................... 5, 8 Inhalte einfügen............................................. 33 Ausschneiden ............................................... 28

K Auto-Filter ....................................................... 7 AutoGliederung ........................................24, 26 Karteikasten .................................................... 4

Kategorie ....................................................... 27 B Klangdatei ..................................................... 34

Kontextwerkzeug ........................................... 18 Bearbeiten, Inhalte einfügen ......................... 36 Kopieren ........................................................ 28 Bedingungen................................................... 5 Kriterienbereich ........................................... 5, 8 Befehlsgruppe

Sortieren und Filtern.................................... 9 L Text........................................................... 31

Bereich.......................................................... 36 Listenbereich ................................................... 5

D M Daten Maske.............................................................. 5

Ausschneiden ........................................... 29 Mehrere Bedingungen ................................... 10 Export........................................................ 28 Microsoft Excel-Arbeitsblatt-Objekt................ 36 Gliedern .................................................... 24

N Gruppierung und Gliederung..................... 24 Import........................................................ 28 Neue Datenbank........................................ 5, 13 Verändern ................................................. 38

O Verknüpfen................................................ 36 Datenaustausch ............................................ 28 Object Linking and Embedding................ 28, 30 Datenbank....................................................... 4 Objekt Datensatz........................................................ 6 Als Symbol................................................. 31 DDE .............................................................. 28 Aus Datei erstellen .................................... 33 Dialogfeld Einbetten ................................................... 30

Inhalte einfügen ........................................ 30 Formatieren ............................................... 32 Objekt........................................................ 31 Größe ändern ............................................ 32 Sortieren ................................................... 11 Inhalte einfügen ......................................... 33

Dynamic Data Exchange............................... 28 Verknüpfen ................................................ 30 Dynamischer Datenaustausch ...................... 35 Verschieben............................................... 32

Objekttyp ....................................................... 31 E Oder-Verknüpfung......................................... 10 Ebenenbalken ..........................................25, 26 OLE ............................................................... 28 Einfügen OLE-Objekt.................................................... 30

Objekt........................................................ 34 Operator ........................................................ 10 PivotTable-Bericht..................................... 17

P Eingabefeld ..................................................... 6 Eingabemaske ................................................ 5 Pivot Eingebettetes Objekt..................................... 31 Bericht ....................................................... 17

Extreme Werte anzeigen ........................... 22 F Feld ........................................................... 20 Feld-Name ...................................................... 4 Felder umstellen ........................................ 21 Fenster verkleinern ....................................... 38 Inhalte ausblenden .................................... 22 Filter............................................................ 7, 9 Spalten vertauschen.................................. 21 Format, Objekt .............................................. 32 Struktur...................................................... 19

Tabelle....................................................... 16 G Tabelle verändern...................................... 20 Gliederung .................................................... 24 Tabellen-Assistent ..................................... 16

Ebenen.................................................24, 25 Tabellen-Datenfeld .................................... 20 Entfernen .................................................. 26 Tools.......................................................... 18 Zellbereiche ein-/ausblenden .................... 25 Zeilen vertauschen .................................... 21

Gliederungsfunktion ...................................... 24 Zusammenfassungsfunktion...................... 20 Gruppe PowerPivot .................................................... 23

40

DDAATTEENN AAUUSSWWEERRTTEENN VVEERRWWAALLTTUUNNGGSSAAKKAADDEEMMIIEE BBEERRLLIINN SSTTIICCHHWWOORRTTVVEERRZZEEIICCHHNNIISS

R Register

Aus Datei erstellen.................................... 34 Daten .......................................................... 9 Einfügen.................................................... 31

Registerkarte Entwurf...........................................18, 19, 20 Neu erstellen............................................. 31 Optionen ........................................18, 19, 20 Zahlen ....................................................... 20

S Schaltfläche

Objekt...................................................31, 33 PivotTable-Tools ..................................18, 19

Sortieren ....................................................... 11 Spezial-Filter ................................................... 7 Struktur der Pivot-Tabelle ............................. 19 Symbol

Einfügen.................................................... 29

Erweitert ...................................................... 9 Filtern .......................................................... 8 Maske.......................................................... 5 Sortieren.................................................... 11

U Und-Verknüpfung .......................................... 10

V Verknüpfen .................................................... 33 Verknüpfung ............................................ 10, 13 Verknüpfungen .............................................. 38

Einfügen .................................................... 36

Z Zellbereiche ein-/ausblenden......................... 25 Zellen formatieren.......................................... 20 Zusammenfassungsfunktion.......................... 20 Zwischenablage....................................... 28, 36

41

MS Excel 2010 - Profiwissen Daten auswerten und abfragen€¦ · Excel bietet dazu zwei...

Documents

Transcript of MS Excel 2010 - Profiwissen Daten auswerten und abfragen€¦ · Excel bietet dazu zwei...