Post on 06-Feb-2018
Hinweise für PC-Einsteiger
zusammengestellt von Berthold Güssmer
Der EXCEL-Bildschirm www.guessi.de
Inhaltsverzeichnis
1 EINLEITUNG ....................................................................................................... 3
2 MICROSOFT EXCEL 97 /2000............................................................................ 4
2.1 Der EXCEL-Bildschirm.............................................................................................................. 4
2.2 Maustechniken........................................................................................................................... 5
2.3 Formeleingabe ........................................................................................................................... 6
2.4 Zeichenformatierung................................................................................................................. 7
2.5 Tabellenkalkulationswerkzeugbau .......................................................................................... 8 2.5.1 Bau der Kalkulationsvorlage für einen Mitarbeiter............................................................... 8 2.5.2 Bau der Kalkulationsvorlage für das erste Team .............................................................. 10 2.5.3 Arbeitsmappe für Team 2 .................................................................................................. 15 2.5.4 Verknüpfen der Arbeitsmappen zu einem Werkzeug........................................................ 17
2.6 Grafische Darstellung von Berechnungen ........................................................................... 19 2.6.1 Datenaufbereitung ............................................................................................................. 19 2.6.2 Aufbau der Grafik .............................................................................................................. 20
2.7 Navigation ................................................................................................................................ 24 2.7.1 Schaltflächen ..................................................................................................................... 24 2.7.2 Makroaufzeichnung ........................................................................................................... 27 2.7.3 Makro zuweisen................................................................................................................. 28
2.8 Datenbankfunktionen.............................................................................................................. 30 2.8.1 Datenbestände sortieren ................................................................................................... 30 2.8.2 Dateneingabemaske.......................................................................................................... 31 2.8.3 Datenfilter .......................................................................................................................... 32 2.8.4 Teilergebnisse ................................................................................................................... 36 2.8.5 Pivot-Tabellen.................................................................................................................... 38 2.8.6 Die Arbeit mit Microsoft Query........................................................................................... 41
Seite - 2 - T_excel.doc
1 Einleitung Die Personalcomputer sind aus dem heutigen Leben, ob privat oder geschäftlich, nicht mehr wegzudenken. Der effektive Umgang mit diesen Maschinen wird überall erwartet und gefordert, aber er ist leider noch kein Schulstoff. Die Nutzer der Rechentechnik wollen oft nur ihre Texte mit dem Rechner schreiben oder bestimmte Berechnungen in Tabellen anfertigen. Sie haben sich selbst mit dem allernötigsten Wissen versorgt, und sich damit abgefunden, dass der Rechner dieses oder jenes einfach nicht macht, einiges auf seine eigene Weise löst oder sogar abstürzt Ein gewisses Mindestmaß an Grundkenntnissen ist unabdingbar, um so etwas wie Herrschaft über einen Rechner zu erlangen. Im folgenden soll es darum gehen, ein paar wichtige Fragen in diesem Zusammenhang zu beantworten.
- Was macht eigentlich das Betriebssystem? - Wie mache ich aus meinem Rechner eine Druckerei oder ein Zeichenbrett? - Wie setze ich die Tabellenkalkulation ein?
Autor: Berthold Güssmer Seite- 3 -
Der EXCEL-Bildschirm www.guessi.de
2 MICROSOFT EXCEL 97 /2000
2.1 Der EXCEL-Bildschirm
Formeleingabe
Statusleiste
Format-Symbolleiste Standard-Symbolleiste
Dateiname
Mausklick � rechts für weitere Symbolleisten
Dialogzeile
Adressfeld
Arbeitsblattreiter
beinhaltet: - Text - Zahl - Formel - Objekt
aktive Zeile
Zeilenköpfe
aktuelle Zelle
aktive Spalte
Spaltenköpfe
Vervielfältigungskästchen
Menüleiste
In der Titelleiste des Fensters befindet sich der Dateiname der geöffneten Tabelle. Mit Hilfe der Menüleiste sind alle Menübefehle über das in Windows übliche Aufblättern von Befehlszeilen ausführbar. Einige Befehle können auch über die Symbolleisten ausgeführt werden, was im allgemeinen schneller geht. In diesem Fall sind aber oft einige Befehlsoptionen nicht erreichbar, so dass man von Fall zu Fall entscheiden muss, welche Aktion günstiger ist.
Seite - 4 - T_excel.doc
Bau der Kalkulationsvorlage für einen Mitarbeiter ECL
2.2 Maustechniken
Markierter Bereich
Ganze Tabelle markieren
Spalten(bereich) markieren
Zeilen(bereich) markieren
Gewählte Spaltenbreite ziehen Doppelclick optimiert
bei sichtbarem Pfeil Spalte verschieben/kopieren
Gewählte Zeilenhöhe ziehen Doppelklick optimiert
bei sichtbarem Pfeil Zeile verschieben/kopieren
gilt auch für Bereiche Das Betriebssystem Windows ist grafisch orientiert, d. h. es nutzt ein Zeigegerät � hauptsächlich die Maus- für große Teile der Bedienung. Das gilt auch für alle Windows-Anwendungen. Typisch ist dabei die Funktion der linken Maustaste, mit der Objekte �gepickt� oder �geklickt� werden, um sie auszuwählen - zu �markieren�. Ebenfalls typisch der �Doppelklick� � 2 Klicks mit der linken Maustaste schnell hintereinander, um die Auswahl zu bestätigen oder Aktionen auszuführen. Eine übliche Technik ist auch das �drag & drop� � man hält die linke Maustaste auf einem markierten Objekt gedrückt und bewegt es mit dem Mauszeiger an einen Zielort, wo es durch Loslassen der linken Taste �fallengelassen� wird. Bei gedrückter Strg-Taste wird das Objekt sicher kopiert, bei gedrückter Shift-Taste sicher verschoben. In Windows 95/98/Me oder NT/2000 ist die rechte Maustaste ebenfalls konsequent eingearbeitet. Hiermit hat man die Möglichkeit ein �Kontextmenü� aufzurufen, um weitere Aktionen, die je nach Arbeitssituation sinnvoll sind auszuführen � also immer mal die rechte Maustaste einsetzen.
Autor: Berthold Güssmer Seite- 5 -
Formeleingabe www.guessi.de
2.3 Formeleingabe
Formeleingabe in aktuelle Zelle
Formeleingabe: (auch mit = möglich) Eingabe von Zahlen: 1, 2, 3 ... Operatoren: +, -, *, / Zelladressen: (günstig mit Mausaktion) relativ z.B. A1; B7 absolut z.B. $A$1 ; B$7; $C6 (umschalten mit F4) Funktionsaufruf: per Tastaur möglich
Formeleingabedialog kann verschoben werden (siehe Beispiel Summenfunktion)
Funktionsaufruf
Beispiel für Formeleingabedialog - Summenfunktion
Summenbereich mit Mausaktion ziehen oder tippen bis 30 Bereiche möglich
Eingabe von Tastatur:
=Summe(Anfangszelladresse:Endzelladresse) z.B. =Summe(A1:Z15)
Seite - 6 - T_excel.doc
Bau der Kalkulationsvorlage für einen Mitarbeiter ECL
Beispiel für Formeleingabedialog �Wenn-dann-sonst..�.
Eingabe von Tastatur: WENN(Prüfung;Dann-Wert;Sonst-Wert)
z.B. =Wenn(C3<0;D8*100/B7;�leerer Text�)
2.4 Zeichenformatierung Dialog Menübefehl Format - Zellen
Festlegen von Umrahmungen oder Untersteichungen
Positivfall: Text, Zahl, Formel eingeben
Negativfall: Text, Zahl, Formel eingeben
Festlegen freigegebener Tabellenbereiche (Aktivierung im Menü Extras)
Festlegen der Hintergrundfarben, -verläufe und -muster
Festlegen der Zeicheneigenschaften Größe, Schriftart, Schriftschnitt)
Festlegen der Anordnung und Ausrichtung der Zeichen in der Tabellenzelle
Prüfung/Bedingung formulieren
Festlegen der Text- und Zahlenformate sowie der Dezimalstellenanzeige
Einige Funktionen sind auch über die Formatsymbolleiste aufrufbar.
Autor: Berthold Güssmer Seite- 7 -
Tabellenkalkulationswerkzeugbau www.guessi.de
2.5 Tabellenkalkulationswerkzeugbau
Beispiel der Mitarbeiterumsatzkalkulation in einem Vertriebsbüro mit 2 Teams
Eingabefeld Jahresplansumme Format Buchhaltung
2.5.1 Bau der Kalkulationsvorlage für einen Mitarbeiter
Eingabe des Mitarbeiternamens
Speichern der Datei als EXCEL-Vorlage (Dateityp xlt im Vorlagenordner von EXCEL mit Datei - Spechern unter..- Mustervorlage )
Eingabefelderbereich monatl. IST-Werte
AbsolutJahrespAdresse- festste
Summenfelder =Summe(Zellbereich)z.B. =SUMME(C8:C19)
)
Vorläufiger Blatt- name für Vorlage
Seite - 8 -
Berechnen des monatl. Planumsatzes Formel: =$C$4*C8/100
er Zellbezug auf lansumme : C4 hend mit (F4)
relativer Zellbezug auf monatl. Prozentanteil Adresse : C8 - vervielfältigbar
Durch Kombination der absoluten und rZellbezüge, können die Formeln verviel
PB we
Berechnen der monatl. Planerfüllung Formel: =WENN($C$4<>0;E8/D8*100;""
elativen fältigt werden
rüfbedingung: erechnung erfolgt nur, enn Jahresplansumme
ingetragen wurde.
Negativfall: Ausgabe eines leeren Textes
Positivfalll: Berechnen des Prozentanteils
T_excel.doc
Bau der Kalkulationsvorlage für einen Mitarbeiter ECL
Prüfbedingung: Berechnung erfolgt nur, wenn Jahresplansumme eingetragen wurde.
Berechnen der kumulativen. Planerfüllung Formel für Monat Februar: =WENN($C$4<>0;SUMME($E$8:E9)/SUMME($D$8:D9)*100;"")
Negativfall: Ausgabe eines leeren Textes
Positivfall: Berechen der Prozentanteile aller bisherigen Monate
Durch Kombination der absoluten und relativen Zellbezüge, kann die Formel vervielfältigt werden
Negativffall:Berechen der Provisionsprozent-anteile mit 11 % des IST-Anteils
Positivfall: Berechen der Provisionszentanteile mit 8 % des IST-Anteils
Prüfbedingung: IIst die kum IST-Summe kleiner als ein Drittel der Jahressplansumme ?
Berechnen der Provisionsanteile-8 % bzw. 11 % bei Erreichen von einem Drittel des Plans =WENN(SUMME($E$8:E8)<$C$4*0,33;E8*0,08;E8*0,11)
Wenn die kumulative Planerfüllung nur berechnet werden soll, falls auch Istwerte erreicht wurden, kann die Formel durch die Und()-Funktion ergänzt werden: =WENN(UND($C$4<>0;E)<>0);SUMME($E$8:E9)/SUMME($D$8:D9)*100;"")
Autor: Berthold Güssmer Seite- 9 -
Tabellenkalkulationswerkzeugbau www.guessi.de
2.5.2 Bau der Kalkulationsvorlage für das erste Team Aufruf einer neuen Arbeitsmappe
Löschen der Arbeitsblätter Tabelle2 und Tabelle3 per Mausklick-Rechts Einfügen von 3 leeren Mitarbeitervorlagen (xlt-Datei) per Mausklick-Rechts als Arbeitsblätter Muster, Muster (2), Muster (3)
Seite - 10 - T_excel.doc
Bau der Kalkulationsvorlage für das erste Team ECL
Umbenennen der Arbeitsblätter Muster, Muster (2) und Muster (3)ebenfalls per Mausklick-Rechts auf die Blattreiter in �Team 1�, �Anfang� und �Ende�
(Tabelle1 bleibt vorerst unberücksichtigt)
Aufbau der Summenblätter für �Team1� aus den Kalkulationsblättern �Anfang� und �Ende� mittels 3D-Formel
Machen Sie das Blatt �Team1� aktiv! Stellen Sie den Cursor in das Feld C4 und geben Sie über = eine Formel ein: Zuerst wählen Sie die Funktion Summe() Wechseln Sie auf das Blatt �Anfang� und wählen Sie als erstes Argument ebenfalls die Zelle C4, anschließend drücken Sie die Umschalt-Taste und wählen das Blatt �Ende�. Damit werden alle Zellwerte der Zellen C4, die sich zwischen den Blättern �Anfang� und �Ende� befinden addiert.
Autor: Berthold Güssmer Seite- 11 -
Tabellenkalkulationswerkzeugbau www.guessi.de
Nach Eingabe afertiggestellt. Zur Arbeit mit dMitarbeiterkalku�Ende� per MauAnschließend wMit der Eingabealle Summen so
Seite - 12 -
Die 3D-Formel in Feld C4 lautet=Summe(Anfang:Ende!C4)
Verfahren Sie analog mit allen weiteren Eingabefeldern.
ller Formeln für die Berechnungsfelder ist das Werkzeug für ein Team
em Werkzeug innerhalb des Teams werden Mustervorlagen des lationsblattes für jeden Kollegen zwischen den Arbeitsblättern �Anfang� und sklick-Rechts auf den Arbeitsblattreiter �Ende� angelegt. erden die Daten des Mitarbeiters in das jeweilige Arbeitsblatt eingegeben. der Daten im Mitarbeiterblatt werden auf dem 3D-Formelblatt des Teams fort berechnet.
T_excel.doc
Bau der Kalkulationsvorlage für das erste Team ECL
Einfügen des Arbeitsblattes für den Kollegen Meier und Eingabe der Plandaten für das Jahr sowie der IST-Werte für das erste Quartal.
Einfügen des Arbeitsblattes für den Kollegen Schulze und Eingabe der Plandaten für das Jahr sowie der IST-Werte für das erste Quartal.
Autor: Berthold Güssmer Seite- 13 -
Tabellenkalkulationswerkzeugbau www.guessi.de
Im Summenblatt für das Team1 werden sofort alle Daten der Kollegen Meier und Schulze auf Grund der 3D-Formeln addiert. Weitere Mitarbeiter können zwischen den Blättern �Anfang� und �Ende� eingefügt werden. Auch diese werden sofort in die Summenbildung mit einbezogen. Es ist aber auch möglich, Mitarbeiterblätter mit der Maus hinter das Blatt �Ende� oder vor das Blatt �Anfang� zu schieben, um es von der Summenbildung auszuschließen.
Für das zweite Team wird nun analog zum ersten Team eine Arbeitsmappe mit Kalkulationsblättern für die dortigen Mitarbeiter angefertigt.
Seite - 14 - T_excel.doc
Arbeitsmappe für Team 2 ECL
2.5.3 Arbeitsmappe für Team 2 Team2 - Kollege Müller
Team 2 - Kollege Schmidt
Autor: Berthold Güssmer Seite- 15 -
Tabellenkalkulationswerkzeugbau www.guessi.de
Team 2 - Summenblatt
Mit der Menüfunktion Fenster anordnen, Option unterteilt, können beide Arbeitsmappen gleichzeitig sichtbar gemacht werden.
Seite - 16 - T_excel.doc
Verknüpfen der Arbeitsmappen zu einem Werkzeug ECL
2.5.4 Verknüpfen der Arbeitsmappen zu einem Werkzeug Es ist nun noch erforderlich in einem übergreifenden Arbeitsblatt die Daten beider Teams zusammenfassen zu lassen. Hierzu werden Formeln benutzt, die auf unterschiedliche Arbeitsmappen zugreifen. Dazu müssen die erforderlichen Mappen geöffnet sein. Erzeugen Sie mit dem Befehl �Datei-Neu� eine weitere Arbeitsmappe, die ebenfalls auf der Vorlage des Mitarbeiterkalkulationsblattes beruht. Die geöffneten Mappen werden nicht geschlossen. Bei der Formeleingabe können Sie direkt in die Zellen der geöffneten Mappen klicken, um mit dem Operator �+� die Zellwerte zu addieren. Die Dateinamen der Mappen werden dabei in eckige Klammern gesetzt. Beim Vervielfältigen der Formeln lösen Sie vorher die absoluten Zellbezüge auf. Speichern Sie die Datei als Arbeitsmappe.
Au
Neue Arbeitsmappe auf Basis der Vorlage Mitarbeiterkalkulationsblatt
Eingabe der arbeitsmappenübergreifenden Formel per Mausklick in die jeweiligen Zellen Beim Vervielfältigen ist zu beachten, dass absolute Zellbezüge vorher in relative umgewandelt werden .
tor: Berthold Güssmer Seite- 17 -
Tabellenkalkulationswerkzeugbau www.guessi.de
Nach dem wiederholten Ausführen des Befehls �Fenster- anordnen- unterteilt�, richten Sie die Ansicht auf das Werkzeug wie folgt ein:
Nun muss das Werkzeug mit dem Befehl �Datei � Arbeitsbereich speichern� komplett gesichert werden. Hierbei handelt es sich um eine Datei vom Typ xlw, die beim Aufruf selbstständig dafür sorgt, dass alle erforderlichen Bausteine geladen werden. Vergeben Sie einen �sprechenden� Dateinamen. Damit ist das Werkzeug einsatzbereit. Die Datenpflege erfolgt nun nur noch in den Mitarbeiterkalkulationsblättern bei aktivierten Schutz (Menü Extras � Schutz) Die Summenblätter für die Teams und das komplette Büro werden jetzt automatisch aktualisiert, wenn neue Daten in die Mitarbeiterkalkulationsblätter eingegeben werden..
Seite - 18 - T_excel.doc
Datenaufbereitung ECL
2.6 Grafische Darstellung von Berechnungen
2.6.1 Datenaufbereitung Grafische Darstellungen eignen sich oft besser zur Veranschaulichung von Zahlenwerten, als es die Zahlen selbst vermögen. Daher erstellen wir Balkendiagramme zur Darstellung der kumulativen Planerfüllung aller Mitarbeiter. Hierzu wird eine neue Tabelle in der übergreifenden Arbeitsmappe angelegt, die per Formeleingabe Verknüpfungen zu den Mitarbeitereingabefeldern erhält.
='[Team_VL11.xls]Kollege Meier'!$C$3 Verknüpfung zu den Namensfeldern der Mitarbeiter: ='[Team_VL11.xls]Kollege Meier'!$C$3
Absoluter Zellbezug
Mausclick rechts auf den Reiter Um Umbe
Verknüpfung zu den Mitarbeiterdaten im Feld kumulatives IST ='[Team_VL12.xls]Kollege Schmidt'!G8
relativer Zellbezug
Durch die Verknüpfung zu den Mitarbeitereingaktuellen Stand und sie können zur Erstellung
Autor: Berthold Güssmer
Einfügen Tabellebenennen �Daten�Einfügen Tabelle
nennen in �Daten�
abefeldern sind die Daten ständig auf dem von Grafiken genutzt werden.
Seite- 19 -
Grafische Darstellung von Berechnungen www.guessi.de
2.6.2 Aufbau der Grafik
Markieren des Datenbereiches, der dargestellt werden soll Für das erste Quartal ist das:
A7:E10
Aufruf des Diagramm-Assistenten
Wahl des Diagrammtyps
Seite - 20 - T_excel.doc
Aufbau der Grafik ECL
Datenbereich kann per Maus gesteuert werden
Wahl der Datenanordnung
Formatierung des Diagramms
Wahl der Beschriftung des Diagramms
Autor: Berthold Güssmer Seite- 21 -
Grafische Darstellung von Berechnungen www.guessi.de
Einfügung des Diagramms als Blatt oder als Objekt
Eingefügtes Diagrammobjekt
Seite - 22 - T_excel.doc
Aufbau der Grafik ECL
Anordnen des Diagrammobjektes in Größe und Darstellung
Fertig!
Autor: Berthold Güssmer Seite- 23 -
Navigation www.guessi.de
2.7 Navigation
2.7.1 Schaltflächen
Einfügen einer neuen Tabelle und Umbenennen des Blattes in �Navigation� per Mausclick-rechts
Markieren des Blattes und Wahl der Hintergrundfarbe Grau 25%
Seite - 24 - T_excel.doc
Schaltflächen ECL
Aufruf der Symbolleiste Zeichnen und Auswahl der Funktion Autoformen-Standardformen-Rahmen
Zeichnen der künftigen Schaltfläche über das Kontextmenü lässt sich Text hinzufügen
(Mausclick-rechts)
Autor: Berthold Güssmer Seite- 25 -
Navigation www.guessi.de
Die Schaltfläche kann mit einem Hyperlink � eine Sprungadresse � versehen werden. Dazu eignen sich Dateien, Arbeitsblätter oder benannte Bereiche. Im Beispiel ist in die Arbeitsmappe des Firmenleiters ein neues Blatt zur Steuerung aufgenommen worden. Darauf sind Autoformobjekte angeordnet worden, die per Hyperlink an bestimmte Stellen springen. Die Schaltfläche �Otto� soll beispielsweise zur Dateneingabe auf das Blatt des Mitarbeiters Otto verzweigen. Dieses liegt in der Mappe �Team1� auf dem Blatt �Otto�. Die Verknüpfung wird mit der Schaltfläche �Hyperlink einfügen� in der Standardsymbolleiste oder durch markieren des Objektes mit der rechten Taste erzeugt.
Die Schaltfläche Datei verlinkt zur Arbeitsmappe �Team1�und die Taste Textmarke verbindet zum Arbeitsblatt �Otto�
Nach erfolgreicher
Auswahl der Sprungadresse
S
Verlinkung verwandelt sich der Cursor in den Browsercursor, den �Zeigefinger�, wenn man ihn über das Schaltflächenobjekt bewegt. Mit etwas Geschick kann man sich ein Arbeitsblatt aufbauen, das an alle erforderlichen Stellen führt.
Von dort kann man Objekte mit der Rücksprungadresse versehen, damit man wieder auf die Steuerungsseite gelangt.
eite - 26 - T_excel.doc
Makroaufzeichnung ECL
2.7.2 Makroaufzeichnung
Starten Sie im Menü Extras-Makro-Aufzeichnen
den Makrorecorder
Das Makro erhält den Namen QUARTAL1
in dieser Arbeitsmappe
Durch Bestätigung mit �ok� wird die Aufzeichnung gestartet.
Alle Aktionen werden bis zum Beenden der Aufzeichnung
mitgeschnitten.
Während der Makroaufzeichnung bewegen Sie sich zu der Einfügestelle des Diagramms. In diesem Fall ist das die Zelle A22 im Arbeitsblatt Daten. Wenn die Einfügestelle erreicht ist, beenden Sie die Makroaufzeichnung über das Menü Extras-Makro-Aufzeichnung beenden.
Autor: Berthold Güssmer Seite- 27 -
Navigation www.guessi.de
2.7.3 Makro zuweisen
Nun muß das Makro der Schaltfläche zugewiesen
werden. Dazu öffnen Sie mit rechter Maustaste das
Kontextmenü der Schaltfläche und wählen �Makro zuweisen�
Wählen Sie im folgenden Dialog das entsprechende
Makro und bestätigen Sie mit �ok�
Seite - 28 - T_excel.doc
Makro zuweisen ECL
Wenn Sie anschließend mit der Maus auf die Schaltfläche gehen, erhalten Sie den
Browsercursor - auch �Zeigefinger� genannt.
Beim Clicken sollte sofort das Diagramm sichtbar sein
Nach diesem Prinzip lassen sich beliebig viele Schaltflächen für alle denkbaren Aktionen erstellen.
Autor: Berthold Güssmer Seite- 29 -
Datenbankfunktionen www.guessi.de
2.8 Datenbankfunktionen
2.8.1 Datenbestände sortieren In EXCEL sind leistungsfähige Funktionen zur Arbeit mit Listen enthalten. Dabei werden Datenbestände als tabellenorientierte Sammlungen von strukturierten Daten aufgefasst, die inhaltlich in Spaltenanordnung gegliedert werden und beliebig viele Zeilen als �Datensätze� aufnehmen. Einfache Beispiele sind Telefonbuchtabellen, Adresslisten, CD-Sammlungen, Inventarverzeichnisse o.ä.. In unserem Fall handelt es sich um die Erfassung von Nettokosten diverser Rechnungen von Liefer- und Leistungsfirmen, die einem Konto, bzw. einem Gruppenmerkmal zugeordnet werden. Später sollen die Daten nach den Merkmalen ausgewertet werden.
Nettokosten
Gruppenmerkmal
Leistungsfirma
Kontenzuordnung
Datum der Rechnung
Zelleinträge sortieren
Zelleinträge filtern
Eingabemaske
Aufruf der Datenbankfunktionen
Seite - 30 -
Nach Zwischenebenen gliedern
Pivotabellenabfrage /~bericht
T_excel.doc
Dateneingabemaske ECL
Datensortierdialog
Festlegen des Sortierfeldes
Festlegen der Sortierreihenfolge
Alle Listen sollten möglichst mit Spaltenüberschriften geführt werde, um eine bessere Übersicht zu erreichen.
Es sind drei Sortierfelder möglich, die hierarchisch verschachtelt werden.
Mit �ok� wird die Sortierung ausgeführt
2.8.2 Dateneingabemaske Über die Funktion Maske können alle Daten in einer Eingabeschablone eingegeben oder angezeigt werden.
Löschen einer Zeile
Eintragen einer neuen Zeile
Spaltenstruktu
Suchfunktionen
Autor: Berthold Güssmer Seite- 31 -
Datenbankfunktionen www.guessi.de
2.8.3 Datenfilter Mit den Datenfilterfunktionen �Autofilter� oder �Spezialfilter� kann die Anzeige auf bestimmte Daten eingeschränkt werden. Alle anzeigen hebt die Filterung wieder auf.
Seite - 32 - T_excel.doc
Datenfilter ECL
Die Funktion �Autofilter� öffnet eine Liste der Einträge in jeder Datenspalte, die durch die Auswahl per Mausklick dafür sorgt, dass alle anderen Einträge ausgeblendet werden.
Hier können 2 Kriterien kombiniert werden
Dieser Autofilter führt zu folgendem Ergebnis
Autor: Berthold Güssmer Seite- 33 -
Datenbankfunktionen www.guessi.de
Mit der Spezialfilterfunktion können fast alle nichtbenötigten Daten weggeblendet werden. Es ist lediglich erforderlich, die zu filternden Spaltenüberschriften in einen freien Tabellenbereich � den Kriterienbereich - zu kopieren und die Werte, die angezeigt werden sollen, einfach darunter zu setzen. Einträge, die untereinander stehen, werden �ODER�-verknüpft; Einträge, die nebeneinander stehen, werden �UND�-verknüpft Der Listenbereich der Daten und der Kriterienbereich sind im Spezialfilterdialog einzugeben.
Auszug/Kopie der gefilterten Daten in einen anderen Tabellenbereich
Einträge, die untereinander stehen, werden �ODER�-veWerte fortlaufend eingetragen werden; um sie in die Au
Seite - 34 -
Listenbereich der Daten
Kriterienbereich mit den zu filternden Daten
rknüpft, d.h. es genügt, dass die swahl aufzunehmen.
ODER-Verknüpfung Alle Einträge Toner, Kopier und Pap werden herausgesucht
T_excel.doc
Datenfilter ECL
Einträge, die nebeneinander stehen, werden �UND�-verknüpft, d.h. es müssen beide nebeneinander stehenden Einträge, in den gefilterten Datensätzen vorhanden sein.
Autor: Berthold Güssmer
UND-Verknüpfung Die Einträge Toner und Bürob.Center müssen in dem Datensatz vorhanden sein
Seite- 35 -
Datenbankfunktionen www.guessi.de
2.8.4 Teilergebnisse Wenn man die Zwischensummen von Gruppendaten ermitteln lassen möchte, kann man die Funktion �Teilergebnisse� einsetzen. Damit wird der Datenbestand für jede Gruppe aufsummiert. Es empfiehlt sich, die Daten vorher sortieren zu lassen.
Auswahl der Statistikfunktion, mit der die Gruppierung erfolgen soll
Angabe der auszuwertenden Spalten
Einstellen der Spalte, nach der gruppiert werden soll
Detaildatensätze für Gruppen Toner und Sonst ausgeblendet
Detaildatensätze für Gruppen Plotter und PC eingeblendet
Zwischensumme für Gruppen Toner und Sonst
Zwischensumme für Gruppen Plotter und PC
Seite - 36 - T_excel.doc
Teilergebnisse ECL
Anzeigen der Teilergebnisgliederungsebene -hier Ebene 2 aktiviert
Pivotabellenabfrage /~bericht
Anzeigen der Teilergebnisgliederungsebene -hier Ebene 1 aktiviert
Autor: Berthold Güssmer Seite- 37 -
Datenbankfunktionen www.guessi.de
Anzeigen der Teilergebnisgliederungsebene -hier Ebene 3 aktiviert, d.h. es sind alle Detaildatensätze sichtbar
2.8.5 Pivot-Tabellen Das mächtigste Werkzeug zur Datenanalyse in EXCEL ist die Pivot-Tabelle. Mit dieser Technik können Datenbestände sowohl nach Zeilen als auch nach Spalten gruppiert werden. Wir möchten die Daten sowohl nach Firmen als auch nach Gruppen analysieren lassen. Aufruf des Pivotabellendialogs im Menü Daten-Pivottabelle
Einstellen der Datenquelle
Pivottabellenschalter
Fortsetzen der Analyse
Seite - 38 - T_excel.doc
Pivot-Tabellen ECL
Eingabe des Datenbereichs
Fortsetzen der Analyse
Bestimmen des Arbeitsblattes, auf dem die Analyse erstellt wird
Bearbeiten der Analyse
Analysedialog
Spaltenbereich
Datenbereich
Durch Ziehen der Felder auf die Analysebereiche werden die erforderlichen Gruppierungen vorgenommen und Auswertungen vorgenommen
Seitenbereich
Zeilenbereich
In unserem Beispiel ziehen wir die Felder Gruppe in den Spaltenbereich, Firma in den Zeilenbereich und Netto per Summenfunktion auf den Datenbereich
Spaltenbereich
Zeilenbereich Datenbereich
Autor: Berthold Güssmer Seite- 39 -
Datenbankfunktionen www.guessi.de
Nach Fertigstellen des Dialogs liefert EXCEL sofort eine in den Zeilen nach Firmen und in den Spalten nach Gruppen analysierte Tabelle, die alle gewünschten Ergebnisse enthält.
Über Listen lassen sich Auswertungen auf bestimmte Eintragungen beschränken
Über Listen lassen sich Auswertungen auf bestimmte Eintragungen beschränken
Mit der Pivottabellensymbolleiste lassen sich weitere Möglichkeiten einstellen
Der Pivottabellen-Assistent ermöglicht das weitere Verbessern der Analyse über die Änderung des Analysediaologs
Seite - 40 - T_excel.doc
Die Arbeit mit Microsoft Query ECL
Die Einschränkung auf die Gruppen Kopier, Pap, und Toner führt sofort zur Ermittlung der Kopierkostenanteile
Die Daten sind sofort auch grafisch auswertbar. (siehe Punkt. 1.6. )
2.8.6 Die Arbeit mit Microsoft Query EXCEL stellt ein interessantes Werkzeug für die Arbeit mit Datenbeständen bereit. Mit dem gesondert zu installierenden Programmteil �MS-Query� unter der Option Datenzugriff hat man die Möglichkeit Datenbankabfragen zu tätigen. Beispiel Modernisierungskostenumlage Für ein Rekonstruktionsvorhaben im Wohnungsbau sind Sanierungsarbeiten in einem bestimmten Umfang angefallen. Teile der Kosten fallen unter die Rubrik �Modernisierung�. Damit sind die Kosten anteilsmäßig auf die Mieter verteilbar. Es muss berechnet werden, welchen Anteil an den Kosten jeder einzelne Mieter zu tragen hat. Es gibt eine Liste der Mieter und eine Liste der Gewerkekosten. Nun muss für jeden Mieter ein Datensatz je Gewerk angelegt werden. Dazu werden die beiden Tabellen zu einem Gesamtdatenbestand �gejoint�, d.h. sie werden sozusagen �gekreuzt�.
Autor: Berthold Güssmer Seite- 41 -
Datenbankfunktionen www.guessi.de
In der ersten Tabelle ist eine Liste der Mieter enthalten. Sie enthält Informationen zur Einzelwohnung, deren Hauszuordnung, Lage, Mietername und zur Wohnfläche. Haus Lage Mieter WFL_ges Wohnfläche Bögelstr. 20 EG links Leer-WE 2525,18 69,43 Bögelstr. 20 EG rechts Schulze 2525,18 60,61 Bögelstr. 20 1.OG links Dietrich 2525,18 69,18 Bögelstr. 20 1.OG rechts Lampe 2525,18 72,95 Bögelstr. 20 2.OG links Köhler 2525,18 71 Bögelstr. 20 2.OG rechts Franz 2525,18 75,12 Bögelstr. 20 3.OG links Lindemann 2525,18 71,61 Bögelstr. 20 3.OG rechts Leer-WE 2525,18 75,6 Bögelstr. 20 DG links Leer-WE 2525,18 72,33 Bögelstr. 20 DG rechts Moosdorf 2525,18 52,01 Bögelstr. 22 EG links Ebert 2525,18 50,3 Bögelstr. 22 EG rechts Maier 2525,18 50,3 Bögelstr. 22 1.OG links Gutsch 2525,18 57,33 Bögelstr. 22 1.OG rechts Tobel 2525,18 57,3 Bögelstr. 22 2.OG links Jacobs/Schulze 2525,18 59,41 Bögelstr. 22 2.OG rechts Müller-Doost 2525,18 59,38 Bögelstr. 22 3.OG links Heubach 2525,18 59,41 Bögelstr. 22 3.OG rechts Leer-WE 2525,18 59,38 Bögelstr. 22 DG links Normann 2525,18 61,93 Bögelstr. 22 DG rechts Neumann 2525,18 48,65 Bögelstr. 24 EG links Dröste 2525,18 50,3 Bögelstr. 24 EG rechts Finger 2525,18 50,3 Bögelstr. 24 1.OG links Buch 2525,18 57,33 Bögelstr. 24 1.OG rechts Beutner 2525,18 57,3 Bögelstr. 24 2.OG links Schuhmann 2525,18 59,41 Bögelstr. 24 2.OG rechts Leer-WE 2525,18 59,37 Bögelstr. 24 3.OG links Schmidt 2525,18 59,41 Bögelstr. 24 3.OG rechts Gabler 2525,18 59,4 Bögelstr. 24 DG links Pörschmann 2525,18 33,04 Bögelstr. 24 DG rechts Novis 2525,18 72,32 Bögelstr. 28 EG links Baumbach 2525,18 74,89 Bögelstr. 28 EG rechts Zahn 2525,18 67,56 Bögelstr. 28 1.OG links Franke 2525,18 75,71 Bögelstr. 28 1.OG rechts Zimmer 2525,18 68,15 Bögelstr. 28 2.OG links Beyer 2525,18 79,52 Bögelstr. 28 2.OG rechts Kloist 2525,18 69,91 Bögelstr. 28 3.OG links Hempel 2525,18 79,52 Bögelstr. 28 3.OG rechts Leer-WE 2525,18 69,91 Bögelstr. 28 DG links Löbert/Woog 2525,18 66,57 Bögelstr. 28 DG rechts Leer-WE 2525,18 62,03
Seite - 42 - T_excel.doc
Die Arbeit mit Microsoft Query ECL
Eine zweite Tabelle enthält eine gewerkeorientierte Liste der Kosten, die bei der Modernisierung angefallen sind und eine Information darüber, wie viel Prozent davon auf den Mieter übertragen werden dürfen. G_nr Gewerk Kosten Mod
1 Grundleitungen 19.039,84 DM 0 2 Maurer-, Beton-,Putzarbeiten 23.256,94 DM 0 3 Schwammsan./Zimmerer 83.457,27 DM 0 4 Abrissarbeiten 14.317,76 DM 0 5 Dachdecker 66.286,00 DM 0 6 Werkstein-,Fliesenarb. 12.575,36 DM 0 7 Tischlerarbeiten 9.696,68 DM 10 8 Parkett,Treppensan. 16.691,43 DM 0 9 Schlosserarbeiten 9.721,96 DM 10
10 Malerarbeiten WE 49.190,50 DM 20 11 Dachbodendämmung 24.721,48 DM 80 12 Kellerdeckendämmung 54.291,48 DM 80 13 HT-Sanitär,Warmwasser 36.387,83 DM 50 14 HT-Heizung 55.200,43 DM 100 15 HT-Elektroinst. 22.676,08 DM 70 16 Maurer-, Beton-,Putzarbeiten 84.593,64 DM 30 17 Abrissarbeiten 16.211,68 DM 0 18 Fliesenarbeiten 178.635,60 DM 100 19 Tischlerarbeiten 85.690,00 DM 10 20 Malerarbeiten WE 179.717,05 DM 30 21 Bodenbelag in WE 95.376,05 DM 40 22 Trockenbau 258.123,89 DM 50 23 HT-Sanitär/Warmwasser 164.814,40 DM 40 24 HT-Heizungsinst. 131.006,32 DM 100 25 HT-Elektroinst. 144.642,26 DM 70 26 Briefkastenanlage 10.427,20 DM 30
Beide Tabellen liegen auf Arbeitsblättern einer EXCEL-Tabelle. Die Datenbereiche wurden markiert und benannt.
Benannter Datenbereich
Autor: Berthold Güssmer Seite- 43 -
Datenbankfunktionen www.guessi.de
Benannter Datenbereich
Die Datei wird als normale EXCEL-Datei gespeichert und geschlossen. In einer neuen Arbeitsmappe wird der Dialog �Daten-externe Daten-neue Abfrage erstellen� geöffnet.
MS-Query fragt nach der Datenquelle.
Als Datenquelle wird die EXCEL Arbeitsmappe gewählt
Seite - 44 - T_excel.doc
Die Arbeit mit Microsoft Query ECL
Auswahl der Datendatei
Im nächsten ersten Schritt werden die Daten der Wohnungen gelesen. Dazu wird der Datenbereich �Wohnungen� markiert und per Pfeiltaste ausgewählt.
Die folgenden Dialoge können durchgeschaltet werden.
Auswahl des Datenbereichs
A
utor: Berthold Güssmer Seite- 45 -Datenbankfunktionen www.guessi.de
Nun müssen die Daten weiter bearbeitet werden.
Im Abfragefenster des Abfragegenerators werden die Daten der Tabelle angezeigt.
Seite - 46 -
Die Abfrage muss weiter bearbeitet werden.
�Wohnungen�
T_excel.doc
Die Arbeit mit Microsoft Query ECL
Nun müssen die Gewerke integriert werden. Über die Schaltfläche oder den Dialog Tabelle hinzufügen kann dies geschehen.
Die Daten des Bereiches �Gewerke� müssen hinzugefügt werden Anschließend wird das Fenster geschlossen.
Hinzufügen der Daten des Bereiches Gewerke
Die Daten werden einfach in den Tabellenbereich gezogen
Die Daten der Gewerkeliste können nun einfach in den Tabellenbereich gezogen werden. Damit wird für jede Wohnung ein Datensatz für jedes Gewerk angelegt.
Autor: Berthold Güssmer Seite- 47 -
Datenbankfunktionen www.guessi.de
Das Ergebnis ist einfach verblüffend:
Für jede Wohnung wurde ein Datensatz je Gewerk angelegt. Der Vorteil ist, dass man nun jede Wohnung einzeln bearbeiten kann.
Es sind sehr viele Datensätze entstanden, doch das ist auch erforderlich, wenn man die Möglichkeit benötigt, jede Wohnung individuell behandeln zu können. Der gesamte Datenbestand kann mit den Datenbankfunktionen von EXCEL bearbeitet werden. Man kann Berechnungen durchführen, Teilergebnisse einsetzen und vor allem Pivot-Tabellenberichte generieren lassen. Beispiel Verknüpfen von Katalogdaten MS-Query ermöglicht der Tabellenkalkulation, relationale Beziehungen zwischen Tabellen aufzubauen. Diese sonst nur aus Datenbanken bekannte Technik ist seht vorteilhaft einsetzbar. Es gibt diverse Kataloge von Anbietern und Herstellern, in denen Produktpaletten beschrieben und mit Preisen versehen sind. Bekannt ist z.B. der SIRADOS-Katalog, auf den wir im Beispiel zurückgreifen, aber auch jeder andere Katalog ist analog einsetzbar. Für ein Angebot liegt eine Zusammenstellung der Einzelpositionen nach Bestellnummern des Kataloges vor. Dies kann auch eine als Stückliste aus einer CAD-Konstruktion extrahierte Attributzuordnung sein.
Seite - 48 - T_excel.doc
Die Arbeit mit Microsoft Query ECL
Im Beispiel handelt es sich um die
A
Stückliste der Sanitärartikel aus einem AUTOCAD-Projekt. Die Bauteile wurden in AUTOCAD 2000 mit den Katalognummern aus dem SIRADOS-Katalog, der als EXCEL-Tabelle vorliegt, versehen. Um eine Ausschreibung oder eine Kostenermittlung in EXCEL anzufertigen, müssen die Stücklistenpositionen mit den Textbeschreibungen und Preisen aus dem Katalog versehen werden. Diese Aufgabe löst das Programm MS-Query auf elegante Weise
Die Spalten werden markiert und der gesamte Datenbereich wird im Namensfeld mit der Bezeichnung �Daten� versehen.
utor: Berthold Güssmer
In ein neues Arbeitsblatt werden die Katalogdaten perZwischenablage eingefügt. Die Spalten werden markiert und der gesamte Datenbereich wird im Namensfeld mit der Bezeichnung �Katalog� versehen. .Auch die Arbeitsblattbe-zeichnung �Katalog� wird eingegeben
Seite- 49 -
Datenbankfunktionen www.guessi.de
Über eine Pivot-Tabelle werden die Katalogbauteile in einem dritten Arbeitsblatt ausgezählt. Dazu werden die Katalognummern in den Zeilenbereich gezogen und im Datenbereich wird die Anzahl der Katalognummern ausgezählt. Die entstehende Pivot-Tabelle wird anschließend markiert und kopiert.
Ein neues Arbeitsblatt wird mit �Anzahl� benannt.Über den Dialog �Bearbeiten-Inhalte einfügen- Werte� werden nur die Werte ohne Formeln eingefügt. Die überflüssigen Zeilen werden gelöscht und die Spalten mit �Anzahl� benannt. Die Datei wird anschließend im EXCEL-Format gespeichert und geschlossen Nun möchten wir Listen erzeugen, in denen die Positionseinträge über die Katalognummern mit Langtexten aus dem Katalog verbunden sind.
Dazu erzeugen wir in einer neuen Arbeitsmappe mit dem Zusatzprogramm Microsoft Query eine Abfrage. Als Datenquelle wird die EXCEL-Datei angegeben. Für die Bauteilliste benötigen wir zuerst den Datenbereich mit den Bauteilen. Der Ablauf erfolgt genau wie im vorherigen Beispiel. Die Abfrage wird ebenfalls weiterbearbeitet. Als Tabelle wird nun der Katalogdatenbestand hinzugefügt. Da wir nicht alle Texte benötigen, sondern nur die, die als Bauteil auch vorhanden sind, wird eine Verbindung von der Kalognummer der Bauteilliste zur Ordnungszahl des Kataloges einfach per Ziehen gelegt.
Seite - 50 - T_excel.doc
Die Arbeit mit Microsoft Query ECL
Anschließend kann der Langtext des Katalogs in den Tabellenbereich gezogen werden. Das Ergebnis wird an EXCEL zurückgegeben. Die Abfrage kann unter dem Dateityp *.dqy gespeichert und wiederverwendet werden.
In der Bauteilliste wurden die Positionsnummern mit den zugehörigen Langtexten aus dem Katalog versehen.
Autor: Berthold Güssmer Seite- 51 -
Datenbankfunktionen www.guessi.de
Für die Ausschreibung und die Kostenermittlung dient die per Pivot-Tabelle aufbereitete Liste mit der Anzahl der Bauteile als Ausgangspunkt für eine Abfrage. Im Abfragegenerator werden die Katalogdaten hinzugefügt und über die Beziehung Ordnungszahl-KNR verbunden.
DSDD
F
S
ie Felder Langtext, Ergebnis und EP werden in die Tabelle gezogen und in einer neuen paltenüberschrift wird Ergebnis*EP eingetragen.
Die Felder Langtext,Ergebnis und EP werden in die Tabelle gezogen
Der Gesamtpreis kann direkt per Formel berechnet werden
amit wird der Gesamtpreis berechnet. Anschließend wird die Abfrage gespeichert und die aten werden an EXCEL zurückgegeben.
ür die Ausschreibung brauchen wir eine weitere Abfrage, aber lediglich mit den Langtexten.
eite - 52 - T_excel.doc