richtigen Excel Datenbanken 1 VORBEMERKUNGEN: 1.1 WAS IST EINE DATENBANK? Die Daten werden ähnlich einem Karteikasten gespeichert und verwaltet. Datenbank Karteikasten Enthält alle Kunden Datensatz Karteikarte Enthält einen Kunden alle Informationen eines Kunden werden eingetragen und gepflegt Datenfeld Karteikartenfeld Enthält z.B. den Nachnamen eines Kunden Jedes Datenfeld ist einem Feldnamen zugeordnet (wird definiert durch die Spaltenüberschrift) 1.2 - 1.3 WANN UND WARUM EXCEL DATENBANKPROGRAMME? - DATENBANKEN STATT PROFESSIONELLER wenn kleine Datenmengen verwaltet werden (ca. 1000 Datensätze) wenn einfache Listen mit Berechnungen benötigt werden um Daten aus anderen Programmen mit Excel-Funktionen zu analysieren weil die Komplexität der relationalen oder hierarchischen Datenbanken zu weit ginge WAS IST FÜR EXCEL EINE DATENBANK? Jede zusammenhängende Liste wird als Datenbank erkannt und Excel erwartet in der ersten Zeile eine Überschriftenzeile. Wichtig: Es darf innerhalb des Datenbestandes (Überschrift und Datensätze) keine Leerzeile geben! 1.4 DER DATENBANK-AUFBAU Feldnamen Datensatz Datenfeld Seite 1 1.5 1.5.1 GRUNDSÄTZLICHE MÖGLICHKEITEN ZUR BEARBEITUNG EINER EXCEL-LISTE: Wie sehe ich immer die Überschriftenzeile? Falls eine Excel-Liste mehr als knapp 50 Datensätze enthält bei einer Schriftgröße von 11/12 Pt, wird die Überschriftenzeile beim Scrollen ausgeblendet. Durch Positionieren des Cursors in der zweiten Zeile auf dem Feld A2 und anschließendem Aufruf des Menüpunktes Fenster mit der Option Fixieren, wird die erste Zeile fixiert. Die Fixierung wird also jwls. vor der aktuellen Cursorposition eingerichtet; dies gilt für die Zeile und die Spalte. 1.5.2 Wie kann ich die Reihenfolge der Spalten einer Datenbank vertauschen? Es wird die betreffende Spalte markiert durch anklicken des Spaltenkopfes, anschließend wird bei gedrückter Shift-Taste der Mauszeiger unterhalb des Spaltenkopfes (am Datenbereich) auf den Zellrahmen positioniert und per drag&drop an die gewünschte Stelle verschoben. Seite 2 2 MÖGLICHKEITEN DER BEARBEITUNG EINER EXCELDATENBANK: 2.1 SORTIEREN EINER EXCEL-DATENBANK Theorie: Der Cursor muß in die zu sortierende Spalte gesetzt werden, anschließend eine der beiden Schaltflächen anklicken, um auf- oder absteigend zu sortieren. Falls mehr als eine Spalte sortiert werden soll, muß dies nacheinander in umgekehrter Reihenfolge geschehen. Auf keinen Fall dürfen eine oder mehrere Spalten markiert werden, da sonst der Datenbestand zerstört wird. Die Datensätze werden nicht zusammengehalten, sondern nur der markierte Bereich wird sortiert und die Zeilenzugehörigkeit ignoriert. Die bessere Alternative ist der Weg über das Menü Daten mit der Option Sortieren. Die folgende Dialogbox erscheint In dieser Dialogbox können bis zu drei Sortierschlüssel definiert werden. Jeder Sortierschlüssel kann auf- oder absteigend eingestellt werden. Man kann Excel mitteilen, dass die Liste keine (Überschrift) enthält, falls wirklich keine Feldnamen generiert wurden. Der erste Sortierschlüssel gibt die Hauptsortierung an. Der zweite Sortierschlüssel sortiert innerhalb identischer Einträge des ersten Schlüssels. Der dritte Sortierschlüssel sortiert innerhalb identischer Einträge des zweiten Schlüssels. Übungen: Öffnen Sie bitte die Datei "Namen.xls" aus dem Verzeichnis K:\it\Datenbanken. Sortieren Sie aufsteigend nach dem Geburtsdatum. Sortieren Sie absteigend nach dem Ort und der Postleitzahl. Sortieren Sie im Sinne des Telefonbuchs. Seite 3 2.2 ARBEITEN MIT DER DATENMASKE Theorie: Um sich nicht selbst in der Tabelle bewegen zu müssen mittels Maus, gibt es eine recht komfortable Möglichkeit, Datenbanken zu verwalten. (Datensätze hinzufügen, editieren, löschen und suchen.) Wenn der Cursor im Datenbankbereich positioniert ist, läßt sich die Datenmaske über Menü Daten - Maske aufrufen. Die Maske besteht aus zwei Teilen. Die linke zeigt den Datenbestand an, die rechte Seite bietet Befehlsschaltflächen, um die gewünschte oder benötigte vornehmen zu können. Die Spaltenüberschriften werden automatisch zu Beschriftungen für die Eingabemöglichkeiten, um den Datenbestand zu pflegen. (im linken Teil) Per Bildlaufleiste werden die Datensätze durchgeblättert. Der aktuelle Datensatz wird oben im rechten Teil der Dialogbox angezeigt mit dem Zusatz, wieviel Datensätze es insgesamt gibt. Über die Schaltfläche NEU wird an das Ende der Datenbank eine neue Zeile angefügt. Beachte: Beim Anlegen eines neuen Datensatzes erfolgt der Wechsel zur nächsten Eingabemöglichkeit über die Tabulatortaste/ oder per Mausklick. Die ENTER-Taste legt den neuen Datensatz sofort an. Löschen von Datensätzen über die Schaltfläche Anzeige der Datensatznummer Anfügen eines Datensatzes Löschen der gesamten Zeile Löschen führt zum löschen der gesamten Zeile, d.h. Excel achtet darauf, dass die Datenbankregeln (zusammenhängender Datenblock) eingehalten werden. Anzeige und Eingabe von Daten. Die Schaltfläche ist nur verfügbar, wenn an Bildlaufleiste für die Datensätze einem Datensatz Änderungen vorgenommen wurden, die noch nicht gespeichert wurden. Korrekturen werden durch den Wechsel auf einen anderen Datensatz gespeichert. Außer dieser ersten Ebene (der Maskenebene) zum eingeben und löschen, kann eine zweite Ebene aktiviert werden. Die zweite Ebene ist die Suchebene und erlaubt es, mit Suchbedingungen den Datenbestand relativ schnell zu selektieren. Seite 4 Über die Schaltfläche wird der Suchprozeß eingeleitet. In die einzelnen Eingabeboxen kann Text eingetragen werden, der die Suchbedingung darstellt. Das Suchergebnis wird durch die Schaltflächen Vorherigen suchen abgerufen - in Abhängigkeit von der aktuellen Cursorposition wird dabei nach oben bzw. nach unten gesucht. Excel setzt bei der Suche automatisch den Platzhalter * am Ende des Textes ein, so dass die Eingabe von Teilzeichenketten möglich ist. Falls eine beliebige Zeichenkette am Anfang stehen darf, jedoch ein bestimmter Text enthalten sein soll, muß der Eintrag lauten z. B. für die Namenssuche . Soll nur ein Zeichen ersetzt werden, gilt das ? als Platzhalter. Muß der Eintrag in der Datenbank mit dem Eintrag in der Suchmaske identisch sein, dann wird ein = vorangestellt. Die Benutzung von anderen Operatoren, wie < , > <> ist ebenso möglich, wenn nach Werten/ Einträgen gesucht werden muß, ab dem angegebenen Wert; z.B. für die Suche nach Namen ab dem Buchstaben "M" (inkl. "M")müßte der Eintrag lauten ">M" , aber für die Suche nach Namen, die mit dem Buchstaben "M" beginnen, müßte der Eintrag lauten "=m*" . (Die Groß-/Kleinschreibung ist nicht relevant!) Die Suchbedingungen werden zurückgesetzt, indem sie gelöscht werden mittels der Schaltfläche . Über die Schaltfläche Schließen wird die Maske geschlossen, egal ob sie aus der Suchoder Anzeigeebene heraus aufgerufen wird. Übungen: Bitte öffnen Sie die Datenmaske. (Datei "Namen.xls") Suchen Sie bitte nach dem Tippfehler "Stuutgart" (statt Stuttgart) und korrigieren Sie ihn. Lassen Sie sich alle Mitarbeiter mit dem Namen "Meier" anzeigen. Suchen Sie nach Alfred Tetzlaff und löschen Sie diesen Datensatz. Suchen Sie nach den Mitarbeitern, die vor 1950 geboren wurden. Suchen Sie alle "Schmitts" und "Schmidts" in einem Suchvorgang. Seite 5 2.3 DATENSELEKTION MIT DEM AUTOFILTER Theorie: Der Autofilter bietet eine sehr schnelle Möglichkeit selbst größere Datenbestände spaltenweise zu selektieren. Die Bedingungen der einzelnen Spalten können kombiniert werden, so dass mehrere Filterbedingungen ergänzend gesetzt werden können. Wie wird der Autofilter aktiviert? Der Cursor steht im Datenbankbereich, Menü Daten - Filter - Autofilter. Dies hat zur Folge, dass neben den Feldnamen das Symbol für ein Listenfeld erscheint, dadurch kann pro Spalte aus den vorhandenen Einträgen eine Auswahl getroffen werden. Anschließend ist der Datenbestand bereits gefiltert. Optisch zu erkennen ist Such-Ebene dies an der blauen Einfärbung der Zeilen und des Listensymbols neben der/den gefilterten Spalten. Falls mit anderen Vergleichsoperatoren als "=" gearbeitet werden soll, muß der Autofilter für die ausgewählte Spalte "benutzerdefiniert" angegeben werden. In der folgenden Dialogbox ist eine Auswahl des Operators möglich, es kann aus den Listeneinträgen gewählt oder ein Wert eingetragen werden, und es kann eine zweite Filterbedingung erzeugt werden; Operator, Listeneintrag oder Wert. Es sind die Operatoren entspricht (=), ist größer als (>), ist kleiner als (<), ist größer oder gleich (>=); ist kleiner oder gleich (<=), entspricht nicht (<>) möglich. Bei Zeichenketten (Wörtern keine Zahlen!) ist der Einsatz von Platzhaltern möglich. Falls zwei Bedingungen gesetzt wurden, sind diese durch logische Operatoren miteinander verbunden. Logisches UND beide Bedingungen müssen erfüllt sein. Sowohl das eine muß erfüllt sein als auch das andere. Logisches ODER eine von beiden Bedingungen muß erfüllt sein. Entweder das eine oder das andere muß erfüllt sein. Falls die Berliner und Düsseldorfer Mitarbeiter/ Kunden selektiert werden sollen, müssen die eingetragenen Bedingungen durch ein logisches ODER verbunden werden. Denn in der Spalte "Ort" ist entweder Berlin oder Düsseldorf eingetragen. Seite 6 Falls ein Postleitzahlenbereich/ Umsatzbereich o.ä. selektiert wird, müssen die beiden Einträge durch ein logisches UND verbunden werden. Denn der angegebene erste Wert stellt eine Minimalgrenze dar, der angegebene zweite Wert stellt eine Maximalgrenze dar. Sowohl das Minimum muß überschritten werden als auch das Maximum unterschritten bleiben. Hinweis: Falls eine Datumsabfrage erfolgt, muss die Eingabe im Datumsformat erfolgen wird für die gesuchte Zeitspanne, Übungen: Filtern Sie in der Tabelle Kunden2.xls die Kunden, deren Umsatz 25000,- DM übersteigt und die noch offene Posten haben. Selektieren Sie die Kunden, deren Bestellung im Jahr 1991 erfolgte. Weisen Sie alle Kölner und Essener Kunden aus, deren Bestellanzahl geringer als 10 ist. Seite 7 2.4 DATENSELEKTION MIT DEM SPEZIALFILTER Theorie: Die Anzahl der Bedingungen pro Spalte ist nicht begrenzt. Die Datenselektion wird dadurch flexibler, aber auch unkomfortabler. Der Spezialfilter setzt drei Bereiche voraus, so dass zunächst einmal einige Vorbereitungen zu treffen sind. Erstens muß es den auszuwertenden Datenbereich geben und er sollte optisch hervorgehoben werden. Zweitens wird ein Suchkriterienbereich benötigt, wo die Bedingungen eingetragen werden. Dieser sollte in einer anderen Farbe formatiert sein. Wichtig: Der Suchkriterienbereich muß die identischen Feldnamen enthalten! (Kopieren der Überschriften) Der Suchkriterienbereich besteht aus den Spaltenüberschriften (Feldnamen) und einer oder mehreren Zeilen darunter. Falls Bedingungen in einer Zeile eingetragen werden, sind diese durch ein logisches Und miteinander verbunden. Es müssen alle Bedingungen erfüllt sein (sowohl - als auch). Falls Bedingungen in mehreren Zeilen eingetragen werden, sind diese durch ein logisches Oder miteinander verbunden. Nur eine der Bedingungen muß erfüllt sein (entweder - oder). Der Eintrag läßt sich natürlich kombinieren, so dass einerseits logische Und Bedingungen gesetzt werden, andererseits jedoch auch kann zusätzlich das logische Oder eingesetzt werden. logisches Und logisches Und Logisches Oder Was würde hier gefiltert? Alle Kölner Kunden, deren Umsatz kleiner als 20000 ist, sowie alle Essener Kunden, deren Umsatz kleiner 15000 ist, sowie alle Düsseldorfer Kunden unabhängig vom Umsatz. Falls eine logische Und Bedingung innerhalb einer Zeile auf ein Feld definiert werden soll, muß dieser Feldname doppelt im Suchkriterienbereich stehen. Dadurch ist wiederum eine Bereichsabfrage z.B. bezogen auf einen PLZ-Bereich möglich. Seite 8 Bedingungen auf nicht vorhandene Felder in der Datenbank können durchgeführt werden, um spezielle Auswertungen zu fahren. Der mittlere Bestellwert ließe sich z.B. ermitteln, indem ein neues ("kalkulatorisches Feld") im Suchkriterienbereich erzeugt wird. Dabei könnte die Bedingung lauten, der mittlere Bestellwert soll 2500 übersteigen. Die bekannten Vergleichsoperatoren (=,<,>,>=,<=,<>) können eingesetzt werden, ebenso finden die Platzhalter ?,* bei Zeichenketten Verwendung. Excel setzt automatisch den Platzhalter "*" an den Eintrag im Suchkriterienbereich. Um dies zu verhindern muß man mit dem Vergleichsoperator "=" arbeiten. Bei der Verwendung des "=" und den Platzhaltern ist folgende Syntax erforderlich: ="=Schmi?t". Nur auf diese Weise läßt sich das Gleichheitszeichen mit den Platzhaltern kombinieren; durch die Anführungszeichen weiß Excel, dass eine Zeichenkette abgeprüft werden soll. Drittens kann ein Ausgabebereich definiert werden, wenn man die Liste nicht an der alten Stelle filtern möchte. Dies ist jedoch optional. Im Ausgabebereich müssen nicht die Feldnamen angegeben werden, denn Excel übernimmt alle Feldnamen und Inhalte, die der/den Bedingungen entsprechen automatisch. Ebensowenig muß der Zielbereich markiert werden, denn Excel erweitert den Bereich automatisch. Da dies ohne Rückfrage geschieht, sollte der Zielbereich leer sein. Gefüllte Zeilen werden ohne Sicherheitsabfrage Alle Feldnamen und Inhalte werden angezeigt. überschrieben. Falls nur eine bestimmte Auswahl von Feldern (Spaltenüberschriften) in der gefilterten Liste erscheinen soll, müssen die Feldnamen im Zielbereich vorhanden sein und angegeben werden beim Ausführen des Spezialfilters. Nur die Inhalte der markierten Felder werden angezeigt. (Können in beliebiger Reihenfolge stehen). Viertens können die einzelnen Bereiche mit einem Namen benannt werden, so dass bei Aufruf des Spezialfilters mit Namen operiert werden kann. Vorgehensweise Markieren des Bereichs, Menü Einfügen - Namen - Festlegen die daraufhin erscheinende Dialogbox sieht vor, dass für den markierten Bereich ein Name festgelegt werden kann. Darüber hinaus werden bestehende Namen angezeigt. Wird ein bestehender Name angeklickt, wird unten der zugehörige Zellbereich angezeigt. Die Zelladressen sind absolut adressiert, damit beim Kopieren nach unten keine Fehler auftreten. Falls Namen festgelegt werden, können diese in den Formeln benutzt werden, was die Arbeit erleichtern kann. Seite 9 Fünftens Der Aufruf des Spezialfilters erfolgt über Menü Daten - Filter - Spezialfilter Die Dialogbox mit den benötigten Angaben läßt sich auf zwei Arten füllen. Einerseits können die Zelladressen angegeben werden durch markieren der Bereiche Andererseits können die vergebenen Namen benutzt werden, so dass die Markierung entfällt. Standardvorschlag von Excel ist die Selektion an gleicher Stelle, so dass kein separater Ausgabebereich genutzt wird. Durch Einschalten der Option "an eine andere Stelle kopieren" wird der selektierte Datenbestand in anderen Zellen angezeigt. Falls dies auf einem anderen Tabellenblatt erfolgen soll, muß von diesem aus der Spezialfilter gestartet werden. Übungen: Selektieren Sie die Hamburger und Berliner Kunden, deren Umsatz größer ist als 40000. Filtern Sie die Kunden des PLZ-Bereichs "2" raus. Filtern Sie die Kunden heraus, deren durchschnittlicher Bestellwert über 5000 liegt. Seite 10