Excel Datenbanken - Kreuzschwestern.de

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