Excel für Fortgeschrittene

Transcrição

Landratsamt Dachau
Schulungsunterlagen
Excel 2007
Excel für
Fortgeschrittene
© 2012 Landratsamt Dachau, EDV
CS Version: 1.0 vom 01.03.2012 Rev. 12
Seite 2
Inhaltsverzeichnis
1
ARBEITEN MIT ERWEITERTEN FUNKTIONEN ................................................................................................. 3
1.1 WENN – DANN – SONST BEDINGUNG (LOGIK) .......................................................................................................... 3
1.2 FUNKTIONEN UND UND ODER(LOGIC) ................................................................................................................... 5
1.3 FUNKTION GANZZAHL (MATHE) ............................................................................................................................. 5
1.4 FUNKTION REST (MATHE) ..................................................................................................................................... 5
1.5 FUNKTION ABS (MATHE) ...................................................................................................................................... 5
1.6 FUNKTION ANZAHL (STATISTIK) .............................................................................................................................. 5
1.7 FUNKTION ANZAHL2 (STATISTIK) ............................................................................................................................ 6
1.8 FUNKTION TEILERGEBNIS (MATHE) ......................................................................................................................... 6
1.9 FUNKTION ZÄHLENWENN (STATISTIK) ...................................................................................................................... 6
1.10
RECHNEN MIT DATUM UND ZEIT (DATUM & ZEIT) ................................................................................................ 7
1.10.1
Funktion Wochentag(Zahl;Typ) ........................................................................................................ 7
1.10.2
Funktion Heute() ............................................................................................................................... 7
1.10.3
Funktionen Tag(Zahl), Monat(Zahl), Jahr(Zahl) ................................................................................ 7
1.10.4
Funktion Datedif(Anfangsdatum;Endedatum;“Intervall“) ................................................................ 7
1.10.5
Funktion Brteiljahre(Anfangsdatum;Endedatum;“Basis“) ................................................................ 7
1.10.6
Funktion Arbeitstag(Datum;Anzahl Werktage) ................................................................................ 8
1.10.7
Funktion Stunde(Zahl) ...................................................................................................................... 8
1.10.8
Funktion Minute(Zahl) ...................................................................................................................... 8
1.10.9
Funktion Sekunde(Zahl) .................................................................................................................... 8
1.11
VERWEISE...................................................................................................................................................... 8
1.11.1
Funktion VERWEIS ............................................................................................................................ 8
1.11.2
Funktion SVERWEIS(Suchkriterium;Matrix;Spaltenindex;Bereich_Verweis) ..................................... 9
1.11.3
Funktion ISTNV(Wert) ....................................................................................................................... 9
1.11.4
Funktion WVERWEIS(Suchkriterium;Matrix;Zeilenindex;Bereich_Verweis) ..................................... 9
1.12
FUNKTION BEREICH.VERSCHIEBEN(MATRIX) ....................................................................................................... 10
2
FORMELÜBERWACHUNG ............................................................................................................................10
2.1
2.2
2.3
2.4
SPUR ZUM VORGÄNGER ...................................................................................................................................... 10
SPUR ZUM NACHFOLGER ..................................................................................................................................... 10
SPURENBESEITIGUNG .......................................................................................................................................... 11
FORMELAUSWERTUNG ........................................................................................................................................ 11
3
BEDINGTE FORMATIERUNG ........................................................................................................................12
4
ZIELWERTSUCHE..........................................................................................................................................14
5
FILTER .........................................................................................................................................................15
5.1
5.2
AUTOFILTER ..................................................................................................................................................... 15
SPEZIALFILTER ................................................................................................................................................... 16
6
GÜLTIGKEITSPRÜFUNG ...............................................................................................................................17
7
DATEN STRUKTURIEREN ..............................................................................................................................19
7.1
7.2
7.3
NAMEN ........................................................................................................................................................... 19
PIVOTTABLE UND PIVOTCHART-BERICHT (MENÜ DATEN) ......................................................................................... 20
KONSOLIDIERUNG (MENÜ DATEN) ........................................................................................................................ 22
8
TRANSPONIEREN .........................................................................................................................................26
9
HYPERLINKS ................................................................................................................................................26
10
MUSTERVORLAGEN.....................................................................................................................................27
ÜBUNGEN ............................................................................................................................................................29
Seite 3
1
Arbeiten mit erweiterten Funktionen
Im Grundlagenkurs wurde bereits die Anwendung von einfachen Funktionen erläutert,
z.B.
=SUMME(A1:A22),
=A3+B3+E3,
=A3*(B3+C3) (Klammer erforderlich, da sonst “Punkt vor Strich“)
usw.
Die Funktionen sind gegliedert in
 Finanzmathematik,
 Datum & Zeit,
 Mathe und Trigonometrie,
 Statistik,
 Matrix,
 Datenbank,
 Text,
 Logik,
 Information
 Konstruktion
 Cube
1.1
Wenn – Dann – Sonst Bedingung (Logik)
Mappe: Fortg_1.xls, Tabelle Wenn-Dann
Diese Funktion wird eingesetzt, um in Abhängigkeit des Zutreffens einer Bedingung
(WAHR oder 1) die danach folgende Anweisung auszuführen oder nicht (FALSCH oder
0).
Beispiel 1:
Bei einem Bearbeitungsvorgang fallen Gebühren an. Übersteigen die Gebühren den
Wert 100,00 € wird ein Nachlass von 10 % gewährt.
Funktion für den Nachlass:
WENN(D10>B11;D10*C11;0))
wenn
dann
sonst
Seite 4
Vergleichsoperatoren:
kleiner <
größer >
kleiner gleich <=
größer gleich >=
ungleich <>
Beispiel 2:
Sind die Gebühren höher als 50,00 € werden 10 %, ab 100,01 € 20 % Nachlass gewährt. Dazu muss die Abfrage ineinander verschachtelt werden. Zweckmäßigerweise
löst man das Problem von der Wertigkeit von oben nach unten auf.
Geb.
>100 €
Geb.
> 50 €
j
20 %
j
10 %
Ein Programmablaufplan (PAP)
kann für eine komplexere Entwicklung der Funktion sehr von Nutzen
sein.
0%
Die Funktion in D26 lautet somit:
1. Bedingung
wenn
dann
sonst
WENN(D23>B25;D23*C25;WENN(D23>B24;D23*C24;0))
wenn
dann
2. Bedingung
sonst
Seite 5
1.2
Funktionen UND und ODER(Logic)
Diese beiden Funktionen liefern ebenfalls entweder den Wert WAHR oder FALSCH zurück.
In manchen Fällen ist es einfacher zwei oder mehr Bedingungen, die zutreffen müssen,
zusammenzufassen. Hierzu kann man die Funktion UND benutzen:
=WENN(UND(A3>0;B3>50;C3<1000);“alles wahr“;“mind. 1 Bedingung falsch“).
Merke: Bei einer UND-Anweisung müssen alle Bedingungen zutreffen
Im Gegensatz zur ODER-Funktion, bei der nur eine der gelisteten Bedingungen zutreffen muss, um in den „DANN-Zweig“ zu gelangen:
=WENN(ODER(A3>0;B3>50;C3<1000;“mind. 1 Bedingung wahr“;“alles falsch“
Merke: Bei einer ODER-Anweisung muss mindestens 1 Bedingung zutreffen
Tabelle: Wenn-Dann, Beispiel 3
In diesem Beispiel kommt es zur Leistungsprämie, wenn der MA mindestens
300 Punkte erzielt und sein Jahresbrutto unter 50.000,- € liegt.
1.3
Funktion Ganzzahl (Mathe)
Tabelle Verschiedenes, „Kunsthandel“
Ist das Ergebnis einer Rechenoperation keine ganze Zahl, so ist es oft notwendig nur
die Stellen vor dem Komma weiter zu verarbeiten ohne Rundungsverfälschung. Dazu
dient die Funktion =GANZZAHL(zahl). Sie schneidet das Ergebnis vor dem Komma einfach ab. In Kombination mit der eigentlichen Division könnte die Anweisung lauten
=GANZZAHL(A3/B3).
1.4
Funktion Rest (Mathe)
Ist das Ergebnis einer Division keine ganze Zahl, so kann der Rest mit der Funktion
=REST(zahl;divisor) ermittelt werden.
1.5
Funktion Abs (Mathe)
Die Funktion ABS(zahl) gibt immer eine positive Zahl zurück, unabhängig vom Vorzeichen.
1.6
Funktion Anzahl (Statistik)
zählt den markierten Bereich auf das Vorkommen von Zahlen. Leere Zellen werden
nicht mitgezählt.
Beispiel aus Tabelle Listen:
Seite 6
Spalte Org enthält i.d.R. Zahlen für die Darstellung der Sachgebiete (10, 13, 23, 32,60).
LR, ARGE und 3/6 sind keine Zahlen, sondern Zeichen bzw. Text und werden daher
nicht gezählt.
=ANZAHL(J2:J104) bringt als Ergebnis 90
1.7
Funktion Anzahl2 (Statistik)
zählt den markierten Bereich auf das Vorkommen von Werten (Zahlen oder Text):
Leere Zellen werden nicht mitgezählt.
Beispiel aus Tabelle Listen:
=ANZAHL2(J2:J104) bringt als Ergebnis 97.
1.8
Funktion Teilergebnis (Mathe)
Tabelle: Listen
Diese Funktion ermittelt das Ergebnis einer Rechenoperation auch abhängig davon, ob
Zeilen/Spalten ausgeblendet oder gefiltert wurden (siehe auch Kapitel 5.). Welche Rechenoperation verwendet werden soll bestimmt das Argument Funktion. Funktion kann
einen Wert aus folgenden haben:
Argument Funktion
Incl.
Ohne
ausgebl.
ausgebl.
Daten
Daten
1
101
2
102
3
103
4
104
5
105
6
106
7
107
8
108
9
109
10
110
11
111
Formel Funktion
Mittelwert
Anzahl
Anzahl2
Max
Min
Produkt
StAbw
StAbwN
Summe
Varianz
Varianzen
Im Beispiel wird in einem Auszug des Telefonverzeichnisses nach Gebühren für Privatgespräche summiert.
1.9
Funktion Zählenwenn (Statistik)
Eine Liste kann auf das Vorkommen bestimmter Werte hin überprüft werden. Leere Zellen werden nicht mitgezählt. Soll also in der Tabelle Listen die Anzahl Nebenstellen des
Sachgebietes 32 ermittelt werden, so muss die Spalte Org auf das Vorkommen von „32“
durchsucht werden. Die Anweisung lautet: =ZÄHLENWENN(J2:J104;32)
Seite 7
1.10
Rechnen mit Datum und Zeit (Datum & Zeit)
Excel speichert das Datum als auch die Uhrzeit als eine „serielle“ Zahl, die sich auf den
01.01.1900 00:00 Uhr bezieht, wobei das Datum links vom Komma, die Uhrzeit rechts
davon abgebildet wird. 1 Stunde entspricht 1/24 Tag (0,041666667 oder im Zeitformat
01:00)
Tabelle: Datum
In diesem Beispiel werden von einem Geburtsdatum ausgehend verschiedene Berechnungen angestellt. Die genaue Altersbestimmung in Jahren ist in den Excel Version vor
2007 etwas schwierig, da nur mit Tagen gerechnet wird und die Schaltjahre mit einbezogen werden müssen, ab 2007 gibt es neue Funktionen.
Folgende Funktionen sind bedeutsam:
1.10.1 Funktion Wochentag(Zahl;Typ)
Zahl = serielle Zahl (Datum)
Typ = Werte von 1 bis 3, wobei
1
= Rückgabewert 1 für Sonntag bis 7 für Samstag
2
= Rückgabewert 1 für Montag bis 7 für Sonntag
3
= Rückgabewert 0 für Montag bis 6 für Sonntag
1.10.2 Funktion Heute()
Liefert das heutige Datum
1.10.3 Funktionen Tag(Zahl), Monat(Zahl), Jahr(Zahl)
Liefert aus einem Datum den Tag, den Monat oder das Jahr zurück
Weder im Funktionsassistenten noch in der Excel-Hilfe ist unverständlicherweise die
Funktion
1.10.4 Funktion Datedif(Anfangsdatum;Endedatum;“Intervall“)
zu finden. Sie berechnet die Differenz zwischen zwei Datumsangaben:
Anfangsdatum
= älteres Datum (kleinere serielle Zahl)
Endedatum
= jüngeres Datum (größere serielle Zahl)
Intervall
= y, m, d, ym, yd, md, wobei
“y”
= Differenz in Jahren
„m“ = Differenz in Monaten
„d“
= Differenz in Tagen
„ym“ = Differenz in Monaten minus Jahre
„yd“ = Differenz in Tagen minus Jahre
„md“ = Differenz in Tagen minus Monate
1.10.5 Funktion Brteiljahre(Anfangsdatum;Endedatum;“Basis“)
Liefert die Jahre zwischen Anfangs- und Endedatum dezimal mit Kommastellen, bei
Basis 1 taggenau, andere Basis-Werte legen Zinstage zugrunde.
Seite 8
1.10.6 Funktion Arbeitstag(Datum;Anzahl Werktage)
Berechnet das Datum an dem eine Frist in Tagen ausläuft ohne Berechnung der Wochenenden.
Tabelle: Zeit
Hier sind einige Beispiele mit Darstellungen und Zeitberechnungen. Für ein Stundenkonto bleibt, da Excel keine negativen Datums- oder Zeitwerte ausgeben kann, nur die
Berechnungen im Dezimalsystem durchzuführen und ggf. das Ergebnis im Uhrzeitformat mit einem Vorzeichen auszugeben.
Die wichtigsten Funktionen sind:
1.10.7 Funktion Stunde(Zahl)
Liefert die Stunden aus einer Datum-/Zeitangabe
1.10.8 Funktion Minute(Zahl)
Liefert die Minuten aus einer Datum-/Zeitangabe
1.10.9 Funktion Sekunde(Zahl)
Liefert die Sekunden aus einer Datum-/Zeitangabe
1.11
Verweise
Tabelle: Verweise
1.11.1 Funktion VERWEIS
Die Funktion VERWEIS kennt zwei verschiedene Varianten für die Argumente:
Eine Matrix ist in der Mathematik eine zweidimensionale Anordnung von Zahlenwerten,
in der Informatik ein n-dimensionales Feld und bedeutet schlicht: Eine Tabelle!
Ein Vektor ist eine Matrix (also Tabelle), die nur aus 1 Zeile oder 1 Spalte besteht.
a) VERWEIS(Suchkriterium;Suchvektor;Ergebnisvektor)
Der Suchvektor ist demnach ein Zellbereich, entweder 1 Zeile oder 1 Spalte, welche die zu suchenden Daten enthält. Dies können Zahlen oder Zeichen sein, sie
müssen jedoch aufsteigend sortiert vorliegen.
Seite 9
Der Ergebnisvektor ist ebenfalls 1 Zeile oder 1 Spalte, welche die zurückzugebenden Daten enthält.
b) VERWEIS(Suchkriterium;Matrix)
das Argument Matrix ist ein Zellbereich, der entweder Text oder Zahlen enthält, die
mit dem Suchkriterium verglichen werden. Hat dieser Bereich mehr Spalten als Zeilen, wird die 1. Zeile durchsucht. Hat er mehr oder gleich viele Zeilen als Spalten wird die 1. Spalte durchsucht. Zurückgegeben wird die zweite Zeile bzw. Spalte.
Zum Einschränken der Suchbegriffe können Gültigkeitsprüfungen vorgenommen werden (Siehe Kapitel 6)
1.11.2 Funktion SVERWEIS(Suchkriterium;Matrix;Spaltenindex;Bereich_Verweis)
SVERWEIS - (Senkrecht)
Mit dieser Funktion kann also aus einer Matrix (Tabellenbereich von-bis) über einen
Suchbegriff (legt die Zeile fest) die Spalte (Spaltenindex und damit Zelle) ermittelt werden, deren Zellinhalt zurückgegeben werden soll. Der zu suchende Wert muss immer
in der 1. Spalte stehen.
Das Argument Bereich_Verweis kann nur zwei Inhalte haben: wahr (1)oder falsch (0).
 Wird als Argument wahr gewählt und der Suchbegriff nicht gefunden, so wird der
nächst kleinere Wert zurückgegeben. Die Tabelle muss sortiert vorliegen.
 Bei falsch wird nur der genau passende Begriff gefunden und die Matrix muss
nicht sortiert vorliegen.
1.11.3 Funktion ISTNV(Wert)
Wenn Excel-Funktionen auf unbekannte Suchkriterien stoßen geben diese „#NV“ (nicht
vorhanden) zurück. Dies ist dann hinderlich, wenn mit den Ergebnissen gerechnet werden soll. Die Funktion =ISTNV(Wert) gibt wahr (1) zurück, wenn „#NV“ auftritt und
falsch (0), wenn die Prüfung in Ordnung ist. In Kombination mit der Bedingung
=WENN(Prüfung;dann;sonst) werden Ausgaben nur dann angezeigt, wenn gültige Werte zurückliefert werden, z.B.
=WENN(ISTNV(SVERWEIS(E30;$A$19:$C$22;3;0));0; SVERWEIS(E30;$A$19:$C$22;3;0))
Ein Beispiel für eine Verweis-Anwendung ist das Buchstaben-/Zahlenrätsel
1.11.4 Funktion WVERWEIS(Suchkriterium;Matrix;Zeilenindex;Bereich_Verweis)
WVERWEIS (Waagrecht)
Analog zur vorgenannten Funktion sucht WVERWEIS in der 1. Spalte einer Matrix die
Zeile und gibt den Inhalt zurück.
Seite 10
1.12
Funktion Bereich.verschieben(Matrix)
BEREICH.VERSCHIEBEN(Bezug;Zeilen;Spalten;[Höhe];[Breite])
Mit dieser Anweisung wird der Zellinhalt einer Matrix zurückgegeben, der um Zeilen und
Spalten von Bezug aus versetzt ist. Die Argumente Höhe und Breite sind optional.
2
Formelüberwachung
Wir kennen bereits die Formelansicht (Strg + #), bei der alle Formeln bzw. Funktionen
angezeigt werden. Bei größeren Projekten verliert man hier schnell den Überblick. Hierbei kann die Formelüberwachung gute Hilfestellung
leisten, zu erreichen über die MFL
Register „Formeln“ Gruppe „Formelüberwachung“
2.1
Spur zum Vorgänger
Im Beispiel (Tabelle: Wenn-Dann, Beispiel 3) sollen für die Zelle E34 alle Zellbezüge,
die in der Funktion verwendet werden, angezeigt werden. Dazu wählen wir „Spur zum
Vorgänger“
2.2
aus und erhalten eine graphische Darstellung der benutzten Zellen.
Spur zum Nachfolger
Umgekehrt kann mit der Auswahl „Spur zum Nachfolger“
wo aus die betreffende Zelle bezuggenommen wird:
angezeigt werden, von
Seite 11
Hier wird gezeigt, dass auf das Feld D23 von D26 und D27 Bezug genommen wird.
2.3
Spurenbeseitigung
Mit dieser Auswahl
2.4
„Pfeile entfernen“ werden alle Pfeile wieder gelöscht
Formelauswertung
Wie Excel eine Formel abarbeitet kann schön dargestellt werden mit der Auswahl „Formelauswertung“. Sollte eine Formel einmal nicht das erwartete Ergebnis erbringen und
die Prüfung keinen rechten Aufschluss ergibt, setzen Sie den Zellzeiger auf D26 und
wählen
das Icon
Über die Schaltfläche „Auswerten“ wird jetzt schrittweise zunächst der Zellinhalt angezeigt, die Bedingung geprüft, der Rückgabewert ausgegeben usw. bis hin zum Ergebnis. Mit der Schaltfläche „Einzelschritt“ kann eine hinter einem Zellbezug steckende
Formel auch noch abgearbeitet werden, bevor es mit der eigentlichen Formel weitergeht (Procedureschritt). So kann man nahezu jedem Problem auf die Schliche kommen.
Seite 12
3
Bedingte Formatierung
Tabelle: Verschiedenes, Gewichtstabelle
Um etwas besonders hervorzuheben wenn gewisse Bedingungen eintreten, kann aus
dem MFL-Register „Start“, Gruppe „Formatvorlagen“ die Auswahl „Bedingte Formatierung“ ... dienen.
Im Beispiel soll bei Überschreiten des Gewichtslimits die Darstellung in Rot auf hellrot
erfolgen.
Zunächst die Bedingung festlegen: Gewichtslimit in B10
Eingabezellen B12 bis
B18 markieren und
„Bedingte Formatierung“
auswählen:
Bei den Regeln zum Hervorheben von Zellen die
Auswahl Größer als…
wählen.
Im folgenden Dialog das
Limit angeben und
die Art der Darstellung bestimmen:
Ergebnis: 
Mit der Auswahl „Regeln verwalten…“ können noch weitere Bedingungen gesetzt, gelöscht und bestehende verändert werden.
Seite 13
Mit der Version Excel 2007 gibt es eine ganze Reihe von bereits vorgegebenen Formatierungen, von „Ampeln“ bis „Diagrammen“ sind den Gestaltungsmöglichkeiten kaum
Grenzen gesetzt. Es können jetzt auch Felder formatiert werden deren Bedingungen
aus Formeln in anderen Feldern resultieren.
Tipp:
Wollen Sie wissen, wo in ihrem Arbeitsblatt bedingte Formatierungen (oder auch Gültigkeitsprüfungen und einiges andere mehr) vorkommen, so können Sie mit der Auswahl
MFL „Start, Gruppe „Bearbeiten“ Icon „Suchen und Auswählen“ Gehe Zu… (oder Strg + G) zu folgendem Dialogfeld
gelangen:
Schaltfläche „Inhalte...“
Bedingte Formate auswählen und OK
Sollten in dieser Tabelle (Register, Arbeitsblatt) bedingte Formatierungen bestehen
werden diese markiert.
Seite 14
4
Zielwertsuche
Tabelle: Verschiedenes
Ziel jeder Berechnung ist ein Ergebnis. Manchmal entspricht das Ergebnis jedoch nicht
den Erwartungen. Man kann nun an verschieden Parametern herum probieren und sich
so einem Ziel nähern, oder man nutzt die Zielwertanalyse.
Für unser Beispiel im Register Verschiedenes nehmen wir an wir hätten 9000 Zulassungsvorgänge, die Gebühren einbringen in Höhe von 48,00 € pro Vorgang. Davon sind
3,00 € an das KBA abzuführen und 2% erhält das Bayerische Verkehrsministerium.
Dies wird geleistet von 12 Mitarbeitern, die 50.000,00 € / MA im Jahr verdienen. Das
Jahresergebnis ist nicht ermutigend.
Wählen Sie aus der MFL Register „Daten“, Gruppe „Datentools“ Icon „Was-wäre-wennAnalyse“, Auswahl „Zielwertsuche…“
Für das Feld Zielzelle: setzen wir den Bezug ein, der den Gewinn bzw. Verlust repräsentiert (D28). In das Feld Zielwert: setzen wir 0 ein – wir wollen ja keinen Gewinn erzielen! Nun sollten wir überlegen wo Veränderungen möglich sind, beispielsweise beim
Verdienst des Personals (C26) – OK. Ergebnis:
Excel ermittelt das für das
Ziel erforderliche Gehalt.
Abbrechen stellt den alten
Stand wieder her.
Seite 15
5
Filter
Tabelle: Listen
5.1
AutoFilter
Sehr hilfreich ist es, Listen zu filtern um nur die Informationen angezeigt zu bekommen,
auf die es gerade ankommt. Setzen Sie den Zellzeiger in die Tabelle.
Wählen Sie aus dem MFL-Register „Daten“, Gruppe „Sortieren und Filtern“ Icon „Filtern“
aus, so wird Ihnen in jede Spalte der Überschriftenzeile ein
eingefügt. Klicken Sie
diesen Knopf an, haben Sie die Möglichekeit zu sortieren (auch nach Farben), und Sie
erhalten alle Werte, die in dieser Spalte vorkommen, zur Auswahl und können durch
anklicken den Datenbestand danach filtern.
Wir wollen in unserer Telefonliste die Rufnummern herausfiltern, die im Erdgeschoß
angeschlossen sind. Dazu müssen wir die Spalte Zi filtern nach Einträgen, die mit E
beginnen:
Die Auswahlmöglichkeiten basieren alle auf einem Benutzerdefinierten
Filter, unterscheiden sich
nur durch die Vorgabe
Durchsucht die Spalte
Zimmer (Zi) nach Einträgen beginnend mit E
(E*).
Seite 16
Folgende Datensätze ausgegeben:
Nun wollen wir die Telefone des 1. Obergeschosses ermitteln. Auswahl diesmal: „Zwischen…“
Achten Sie bei der Auswahl der logischen Operatoren auf den genauen Wortlaut, insbesondere bei der und/oder – Verknüpfung. Für mehr als zwei Kriterien verwenden Sie
den Spezialfilter (siehe 5.2).
Es können freilich auch mehrere Filter hintereinander gesetzt werden.
Achtung!
Sind in der Liste Leerzeilen enthalten, so selektiert der Assistent die Liste nur bis zur
ersten Leerzeile. Wollen Sie die gesamte Tabelle incl. der Leerzeilen filtern, müssen Sie
diese vor dem AutoFilter-Aufruf komplett markieren!
5.2
Spezialfilter
Er ermöglicht die Vorgabe von mehreren Selektions-Kriterien. Dazu erstellen wir einen
Kriterien Bereich mit der Spaltenüberschrift, in der gesucht werden soll und darunter die
zu filternden Werte.
Seite 17
in der Spalte Org soll nach ARGE, 60 und 11 gefiltert werden
Wählen Sie
Spezialfilter.
aus dem MFL-Register „Daten“, Gruppe „Sortieren und Filtern“
Der Assistent markiert die von ihm analysierte Liste und trägt sie in Listenbereich ein.
Unter Kriterienbereich tragen wir M1 bis M4 ein
OK.
Wir erhalten eine Liste die nur die gefilterten Einträge enthält.
bringt unsere komplette Liste wieder zum Vorschein.
6
Gültigkeitsprüfung
Im MFL-Register „Daten“, Gruppe „Datentools“ kann man eine dem Icon „Datenüberprüfung“ nachstehen Dialog aufrufen, welche die Eingabe auf bestimmte Werte beschränkt
Auswahlmöglichkeiten von
„Zulassen:“
 Jeder Wert
 Ganze Zahl
 Dezimal
 Liste
 Datum
 Zeit
 Textlänge
 Benutzerdefinier
Seite 18
Die Standardvorgabe ist „Jeder Wert“. Hier wurden die Eingaben auf Ganze Zahlen
zwischen 1 und 4 beschränkt.
Im Register „Eingabemeldungen“ können Beschreibungen zur richtigen Auswahl stehen.
Im Register „Fehlermeldungen“ können Hinweise zur Fehlervermeidung untergebracht
oder auch eine andere als die gültige Vorgabe zugelassen werden.
Praktisch ist auch die Möglichkeit der Erzeugung von Drop-Down-Feldern über eine
Auswahl-Liste:
Mit o.a. Gültigkeitsregel sind in der Spalte App nur noch folgende Eingaben möglich:
Hinweis:
Will man die Daten für eine Listenprüfung in eine andere Tabelle schreiben, so ist das
ab Excel 2007 möglich! Dabei muss die Anweisung in „Quelle:“ um den Tabellennamen
manuell erweitert werden, z.B. =Tabelle2!$A$1:$A$6
Seite 19
7
Daten strukturieren
7.1
Namen
Einzelne Zellen oder ganze Zellbereiche können mit Namen versehen werden. Dies
erleichtert die Lesbarkeit von Formeln und Funktionen sowie die manuelle Eingabe erheblich.
Wir möchten in unserer Telefonliste die Spalte PrivGeb mit einem Name versehen.
Markieren K2 bis K104. Im MFL-Register „Formeln“ stehen in der Gruppe „Definierte
Namen“ folgende Icons zur Verfügung:
Die universellste Möglichkeit bietet der „Namens-Manager“
Hier werden alle in der gesamten Arbeitsmappe vergebenen Namen angezeigt – derzeit
sind keine vorhanden. Wählen Sie die Schaltfläche „Neu...“
Die Spaltenüberschrift „PrivGeb“ wird
als Name vorgeschlagen, wir ändern ihn
auf „Privat“
Hier können zusätzliche Infos angegeben werden
Dieser Bereich wird mit dem Namen
repräsentiert.
OK.
Seite 20
Ergebnis im Namensmanager:
Bei der Gelegenheit vergeben wir auch gleich einen Namen für die ganze Tabelle, incl
der Spaltennamen. Schaltfläche „Neu...“, erweitern des Zellbezuges im Feld „Bezieht
sich auf:“ auf $A$1:$K$104 und benennen ihn „Telefonliste“. OK, Namens-Manager
schließen.
.
Wenn Sie sich nun beispielsweise die Summe der Privatgespräche ausweisen wollen
können Sie eingeben „=SUMME(Privat)“. Wenn die Namen nicht mehr alle bekannt sind
kann nach Eingabe von „=SUMME(“ das Icon „In Formel verwenden“ eine Auswahl
aller verfügbaren Namen bereitstellen.
7.2
PivotTable und PivotChart-Bericht (Menü Daten)
Tabelle: Listen
Ein sehr mächtiges Instrument zum Gliedern und Strukturieren von Listen ist die PivotAnsicht. Sie arbeitet ähnlich wie die Teilergebnisse, aber mit Unterstützung eines
Assistenten.
Um eine Übersicht über die Privatgespräche getrennt nach Organisationseinheiten und
Geschlecht zu erhalten können wir wie folgt vorgehen:
Markieren der vollständigen Tabelle oder, da wir ja Namen vergeben haben, klicken Sie
am linken Ende der Bearbeitungsleiste auf  und
wählen Sie „Telefonliste“.
Im MFL-Register „Einfügen“, Gruppe „Tabellen“ Icon „PivotTable“ aufrufen:
Der zu analysierende Bereich
wurde ja bereits markiert.
Alternativ könnte Bezug auch
hier ausgewhählt oder der vorhin vergebene Name „Telefonliste“ angegeben werden.
In neues Tabellenblatt
OK
Seite 21
Es wird ein neues Tabellenregister eingefügt und der Assistent meldet sich mit der
Feldliste:
Hier sind alle Spaltenfelder
angelistet. Wir ziehen mit der Maus
das
 Feld Org in Zeilenbeschriftungen,
 das Feld geschl in Spaltenbeschriftungen
 und das Feld PrivGeb auf Werte.
Mit diesem/n Feld/ern in Werte kann
gerechnet werden. Standardmäßig
wird Anzahl vorgegeben, mit Klick
und Auswahl „Wertfeldeinstellungen“ können auch andere Kalkulationsmethoden ausgewählt werden –
wir wählen Summe.
Hier kann das Ausgabeformat bestimmt
werden, z.B. Währungsformat
Schaltfläche OK
Seite 22
Die neue Tabelle soll dann folgenden Inhalt aufweisen:
Befindet sich der Zellzeiger innerhalb der Pivot-Tabelle zeigt die MFL zusätzliche Register:
Hier lassen sich für das Layout viele Gestaltungsmöglichkeiten wählen als auch zusätzliche Einstellungen treffen.
7.3
Konsolidierung (Menü Daten)
Datei: DLSchx
Das Konsolidieren (Zusammenführen) von Datenbeständen kann ein ausgesprochen
aufwändiges Unterfangen werden. Excel unterstützt uns mit dieser Funktion enorm.
Im Beispiel gehen wir davon aus, dass künftig die Dienstleister im Hause verstärkt zum
Nachweis ihrer Tätigkeit im Hinblick auf die Verursacher verpflichtet werden. So wird,
z.B., die EDV künftig Informationen liefern müssen, für wen sie tätig war und wie lange.
Öffnen Sie dazu die Vorlage DL (Datei / Neu… / Vorlagen auf meinem Computer) und
speichern sie diese ab unter DLSchn (das n steht für die Schüler-Nummer ihres Arbeitsplatzes).
Seite 23
Sie finden hier die Tabellen für alle Monate zur Erfassung der Dienstleistungszeiten vor.
Der Einfachheit halber wurden die beiden Spalten „Sg“ und „Std dezimal“ mit Bereichsnamen versehen (siehe 7.1), und zwar KJan, KFeb, KMrz … jeweils für den Zellbereich
B3:C100.
Füllen Sie bitte in den ersten Monaten einige Zeilen aus.
.
Zunächst sollen die Monate des Jahres konsolidiert werden. Dies geschieht in der Tabelle 2009, Zelle A3.
Wählen Sie Daten / Konsolidieren ... und setzen Sie die Häkchen bei Beschriftung aus:
Oberster Zeile und Linker Spalte. Wenn die Daten im Nachhinein aktualisiert werden
sollen auch den Haken in „Verknüpfung mit Quelldaten“.
Im Feld Verweis wird nun aus der Mappe DLSchn die Tabelle Jan selektiert und hier die
Zellen B3 bis C100 markiert bzw der Bereichsname KJan eingetragen.
Mit „Hinzufügen“ übernehmen Sie die Daten und verfahren genauso mit der
Tabelle Feb, ebenfalls
„Hinzufügen“ – OK.
Seite 24
Alternativ können Sie auch die Bereichsnamen „KJan und „KFeb“ verwenden.
OK
Das Ergebnis könnte etwa so aussehen:
von Schüler 1 (DLSch1) wurden
für Sg50:
1,5 Std
für Sg10:
3 Std
für Sg13:
1 Std
für Sg23:
2,5 Std ...
geleistet.
Die Schaltknöpfe 1 und 2 blenden die Ebenen ein und aus (1=nur die Summen,
2=einzelen Einsätze).
Darüber hinaus ist es möglich, die konsolidierten Daten aller Mitarbeiter zu konsolidieren. Zur Vorbereitung vergeben wir den Namen „KSchn“ (wobei n wieder für die Schülernummer steht!) für den Zellbereich A3 bis etwa C30 (siehe 7.1). Bitte Datei speichern!
Verknüpfung der Dateien im Arbeitsblatt Fortg_1, Tabelle DL, Zelle A3:
Seite 25
Beim Verweis muss etwas manuelle Nacharbeit geleistet werden, da die Schaltfläche
„Durchsuchen…“ nur die Dateien anzeigt. Hier sind Bereichsnamen optimal. Auf genaue Syntax (Schreibweise) achten!
'G:\_Daten\Schüler!\[DLSch1.xls]Jan'!$B$6:$C$19
Laufwerk
Verzeichnispfad
Dateiname
(Mappe) in [ ]
Absoluter
Zellbezug
Tabelle
oder
mit !
Bereichsname z.B.
KSch1
Seite 26
8
Transponieren
Erweist sich die Anordnung von Zeilen und Spalten als unglücklich, so kann man Zeilen
in Spalten und Spalten in Zeilen tauschen. Markieren Sie dazu die Tabelle, die transponiert (umgeformt) werden soll und legt den Inhalt in die Zwischenablage (Strg + C).
Denn Zellzeiger an die gewünschte Zielposition setzten und mit der Schaltfläche
Transponieren
die umgeformte Tabelle wieder einfügen.
9
Hyperlinks
Mit Hyperlinks lassen sich Verknüpfungen auf Stellen in der Tabelle, der Mappe, in eine
andere Datei oder gar ins Internet erstellen oder eine E-Mail versenden. Wir wollen in
der Tabelle Leer ein Inhaltsverzeichnis mit direkter Sprungmöglichkeit auf die Tabellen
dieser Arbeitsmappe einrichten.
Setzen Sie den Zellzeiger auf Wenn-Dann und wählen Sie aus dem MFL-Register „Einfügen“ Gruppe „Hyperlinks“ das Icon „Hyperlink“ (oder Strg + K).
Es öffnet sich folgender Dialog:
Seite 27
Wählen Sie „Aktuelles Dokument“ und im Dialogfeld unter Zellbezug die passende Tabelle ‚Wenn-Dann‘ aus. Sie können auch bestimmen, in welche Zelle der Zellzeiger gesetzt werden soll. OK
Der Hyperlink wird jetzt durch die blaue Schriftfarbe und die Unterstreichung als solcher
gekennzeichnet. Bei Mausklick auf diesen Text wird in die Tabelle „Wenn-Dann“ gewechselt.
10
Mustervorlagen
Ein Arbeitsblatt, das mühsam erarbeitet mehreren Mitarbeitern zur Verfügung gestellt
werden soll, gilt es gegen unbeabsichtigtes Verändern oder gar Zerstören zu sichern.
Dabei ist der Dokumentenschutz ein wirksames Mittel (siehe Grundlagenkurs), es verhindert jedoch nicht, dass ausgefüllte Arbeitsblätter das ursprüngliche Modell überschreiben. Dagegen hilft eine Vorlage. Ein Berechnungsblatt, dass immer wieder benutzt werden soll, wird unter Office-Schaltfläche „Speichern unter “ „ExcelArbeitsmappe“ wie folgt abgelegt:
12-
Dateityp: Excel-Vorlage (*.xltx) oder Excel 97-2003-Vorlage (*.xlt)
Speicherort: Vorlagen werden gewöhnlich im Ordner _Textbausteine abgelegt
(Laufwerk G:\ für alle Sg-Nutzer, Lw. H:\ für alleinige Nutzung
Dateiname: Hier im Beispiel „Test.xltx“
Seite 28
2
1
Die Datei ist mit einem speziellen Icon versehen, erkenntlich am oberen, gelben Rand.
Zum Aufruf einer solchen Vorlage gehen Sie wie folgt vor:
Office-Schaltfläche – Neu – Meine Vorlagen…
oder
Neu von vorhandenem …
Excel öffnet jetzt ein neues Arbeitsblatt basierend auf der ausgewählten Vorlage, z.B.
Test. Als Dateiname wird immer der Vorlagenname+lfd. Nr vorgeschlage, hier im Beispiel also Test1. Die Mustervorlage selbst bleibt immer unberührt.
Soll jedoch eine Änderung im Berechnungsblatt, also in der Vorlage, durchgeführt werden, so ist die Datei mit Datei / Öffnen... aufzurufen und danach wieder als Vorlage abzuspeichern.
Seite 29
Übungen
Formeln und Funktionen
Tabelle Übungen
Ermitteln Sie die gefahrenen km von Dienstfahrten. Je km 0,20 € Erstattung. Wenn die
Dienstfahrt weiter als 150 km ist werden ab dem 151. km 0,25 € gezahlt.
Bei Erreichen des km-Standes von 50.000 km oder bei Beginn des nächsten Quartales
soll ein Hinweis auf „Inspektion“ ausgegeben werden (bezogen auf „heute“).
Summieren Sie in der Tabelle HH_Liste mit der Funktion Teilergebnisse die
Betragsspalten Planung und Ist .
Zählen Sie die Anzahl der Ist-Buchungen
Formelüberwachung
Lassen Sie sich von den Übungsaufgaben eine Überwachung der Vorgänger und Nachfolger anzeigen
Bedingte Formatierung
Tabelle Listen
Heben Sie die Apparate-Typen analog hervor
Filter
Filtern Sie die Tabelle Listen mit dem AutoFilter nach allen Adv.-Telefonen im 3. Stock.
Filtern Sie die Tabelle HH_Liste mit dem Spezialfilter nach Kriterien Ihrer Wahl
PivotTable
Gruppieren Sie die Tabelle HH_Liste nach den Ist-Summen, die für die einzelnen Anbieter (Empfänger) für die jeweiligen Sachgebiete aufgewendet wurden.
Hyperlinks
Vervollständigen Sie die Hyperlinks für alle Tabellen.
Richten Sie auch eine Rücksprungmöglichkeit auf „Leer“ ein

Excel für Fortgeschrittene

Transcrição

Documentos relacionados

Excel-Tipp: Stundenangaben in Dezimalzahlen umwandeln

Excel-Tipp: Hyperlinks auf Word

Dank Excel und der Zinseszinz-Formel die Kosten eines Kredits

Excel 2007 Profiwissen - Videotraining

Excel-Stundenlohn berechnen

Access 2010 Allergeninformation Onlineschulung

Bevölkerungsexplosion (Excel)

Daten- und Wissensmanagement (DWM)

GETTING STARTED ….

Das Anwendungsfenster in Excel 2010