zusammengestellt von Berthold Gü · PDF fileDer EXCEL-Bildschirm 2 MICROSOFT EXCEL 97...

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.


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.


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)



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.


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


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;"")



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)


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.



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.



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.


Arbeitsmappe für Team 2 ECL

2.5.3 Arbeitsmappe für Team 2 Team2 - Kollege Müller

Team 2 - Kollege Schmidt



Team 2 - Summenblatt

Mit der Menüfunktion Fenster anordnen, Option unterteilt, können beide Arbeitsmappen gleichzeitig sichtbar gemacht werden.


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 -


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..


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


Aufbau der Grafik ECL

Datenbereich kann per Maus gesteuert werden

Wahl der Datenanordnung

Formatierung des Diagramms

Wahl der Beschriftung des Diagramms


Grafische Darstellung von Berechnungen www.guessi.de

Einfügung des Diagramms als Blatt oder als Objekt

Eingefügtes Diagrammobjekt


Aufbau der Grafik ECL

Anordnen des Diagrammobjektes in Größe und Darstellung

Fertig!


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%


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)



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.



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�


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.


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



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.


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



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 -


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


Teilergebnisse ECL

Anzeigen der Teilergebnisgliederungsebene -hier Ebene 2 aktiviert

Pivotabellenabfrage /~bericht

Anzeigen der Teilergebnisgliederungsebene -hier Ebene 1 aktiviert



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


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



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


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�.



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



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



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



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 -


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


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.



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.



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 -


Ü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.



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.



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

zusammengestellt von Berthold Gü · PDF fileDer EXCEL-Bildschirm 2 MICROSOFT EXCEL 97...

Documents

Transcript of zusammengestellt von Berthold Gü · PDF fileDer EXCEL-Bildschirm 2 MICROSOFT EXCEL 97...