Datenmodell in Excel
1:n-Beziehungen in der Tabellenkalkulation
E s muss nicht immer gleich eine Datenbank sein. Sobald Sie eine Beziehung zwischen mehreren Tabellen benötigen, bietet Ihnen Excel mit seinem Beziehungsmodell diese Möglichkeit. Sie können jetzt Abhängigkeiten zwischen den Zellen uneterschiedlicher Tabellen herstellen. Bei dem Import von Tabellen aus einer Datenbank versucht Excel sogar, automatisch eine Beziehung zu erstellen. Mit dem neuen Beziehungsmodell in Excel können Sie problemlos 1 : n-Beziehungen erstellen und die Werte anschließend mit Pivot-Tabellen auswerten. Wenn es beim Import nicht automatisch funktioniert, dann müssen Sie die Beziehung manuell herstellen. Sie können auch bestehende Excel-Tabellen zueinander in Beziehung setzen. Diese Funktionalität bietet Ihnen noch weit mehr Möglichkeiten als die mittlerweile veralteten Funktionen von S-Verweis und W-Verweis.
Das Umwandeln von Bereichen in Tabellen
Die grundsätzliche Voraussetzung zum Erstellen von Beziehungen in Excel ist das Arbeiten mit Tabellen. Dazu müssen Sie die für die Beziehung relevanten Bereiche als Tabelle formatieren. Der umzuwandelnde Bereich darf keine leeren Zeilen und keine leeren Spalten enthalten. Nur dann handelt es sich um einen zusammengehörenden Bereich, den Excel erkennen kann. Jede einzelne Spalte des Bereichs muss eine Überschrift enthalten. Wenn diese Voraussetzungen erfüllt sind, können Sie den Bereich problemlos in das Tabellenformat umwandeln. Klicken Sie zur Umwandlung zunächst in eine beliebige Zelle des umzuwandelnden Datenbereichs. Aktivieren Sie dann im Register Start das Symbol Als Tabelle formatieren. Suchen Sie sich im daraufhin eingeblendeten Fenster ein beliebiges Format aus und klicken Sie darauf. Jetzt läuft ein Markierungsrahmen um den ausgewählten Tabellenbereich. Das Dialogfenster mit der Frage, wo die Daten Ihrer Tabelle sind, wird eingeblendet. Der markierte Bereich wird in das Eingabefenster eingetragen. Falls der Bereich nicht stimmt, können Sie den Bereich auch manuell in dieses Fenster eintippen. Setzen Sie, falls nicht vorhanden, einen Haken in das Kontrollkästchen Tabelle hat Überschriften. Mit einem Klick auf die Schalt äche OK wird der Excel-Bereich in eine Tabelle umgewandelt. Sie erkennen das daran, dass ein neues Registerblatt, die Entwurfsansicht der Tabellentools, einge- blendet wird. Den gleichen Vorgang müssen Sie in dem zweiten Tabellenblatt auch vornehmen.
Namen für die Tabellen vergeben
In der Beispielmappe Datenbankmodell be nden sich zwei Tabellenblätter: Kunden und Ansprechpartner. Diese sollen zueinander in Verbindung gesetzt werden. Bei der Umwandlung der Bereiche in Tabellen werden die Tabellen im Hintergrund automatisch mit einem Tabellennamen ( Tabelle 1 und Tabelle 2) versehen. Bei mehreren Tabellen, die in Beziehung gesetzt werden sollen, verliert man schnell den Überblick. Es ist vorteilhaft, wenn Sie mit Tabellennamen arbeiten, die etwas über den Inhalt der Tabellen aussagen. Um die Tabellennamen in aussagefähige Namen zu ändern, klicken Sie im Register Formeln auf das Symbol Namensmanager. Im jetzt eingeblendeten Dialogfenster ändern Sie mit Hilfe der Schalt äche Bearbeiten die Namen der bestehenden Tabellen. Sie können zum Beispiel die Haupttabelle in Mastertabelle und die abhängige Tabelle in Slavetabelle umbenennen. Durch das Betätigen der Schalt äche Schließen werden Ihre Tabellen umbenannt.
Der Aufbau einer 1 : n-Beziehung zwischen Tabellen über das Datenmodell
Um eine 1 : n-(eins-zu-unendlich-)Verbindung zu erstellen, muss in einer Tabelle ein
sogenannter Primärschlüssel vorhanden sein. Leider unterstützt Excel dieses Datenformat nicht. Primärschlüssel bedeutet, dass jeder Wert in der Spalte für den Primärschlüssel nur einmal vorkommen darf. Am besten nehmen Sie dafür eine Nummer. In unserem Beispiel ist das die Kundennummer. Es ist unbedingt erforderlich, dass in der zu verknüpfenden Zelle der Slavetabelle dasselbe Datenformat wie in der Mastertabelle verwendet wird. Zur Überprüfung, ob jeder Wert nur einmal vorhanden ist, verwenden Sie die bedingte Formatierung. Markieren Sie dazu die Werte in der Spalte mit dem Primärschlüssel und aktivieren Sie dann im Register Start das Symbol Bedingte Formatierung. Im daraufhin eingeblendeten Menü ziehen Sie den Cursor auf den Eintrag Regeln zum Hervorheben von Zellen und wählen dann den Eintrag Doppelte Werte aus. Mit einem Klick auf die Schalt äche OK werden alle doppelten Werte hervorgehoben, und Sie können diese korrigieren. In der verknüpften Slave-Tabelle wird dieser Wert Fremdschlüssel genannt und darf beliebig häu g vorkommen.
Das Datenmodell erstellen
Nachdem die beiden Basis-Tabellen erstellt sind, müssen diese über Beziehungen zueinander in Verbindung gebracht werden. Zur Erstellung dieser Beziehung wechseln Sie zunächst in das Tabellenblatt mit den Kundendaten und klicken dort in eine Zelle innerhalb der Tabelle. Aktivieren Sie anschließend das Register Daten und klicken Sie in der Gruppe Datentools auf das Symbol Beziehungen. Jetzt wird das Dialogfenster Beziehungen bearbeiten eingeblendet. Durch das Betätigen der Schalt äche Neu wird das Dialogfenster Beziehung erstellen eingeblendet. Sie wählen zunächst im Bereich Tabelle die Slavetabelle (das ist die Tabelle mit den Ansprechpartnern) aus und geben als Fremdspalte das Feld Kundennummer ein. Dann wählen Sie im Bereich Verwandte Tabelle die Mastertabelle (Kundentabelle) und die Spalte Kundennummer als Primärschlüssel aus. Mit einem Klick auf die Schalt äche OK wird die Beziehung erstellt und das Dialogfenster geschlossen. Das Dialogfenster mit den Beziehungen wird wieder angezeigt. Kontrollieren Sie hier noch einmal, ob die gewünschten Beziehungen übereinstimmen und betätigen Sie dann die Schalt äche Schließen.
Die Auswertung der Daten mithilfe einer Pivot-Tabelle
Noch erkennen Sie die erstellte Beziehung nicht. Daher sollen die Tabellen jetzt ausgewertet werden. Für die Auswertung bietet sich eine Pivot-Tabelle an. Aktivieren Sie zunächst das Tabellenblatt mit Ihren Kunden. Klicken Sie dann auf eine beliebige Zelle in der Kundentabelle. Aktivieren Sie dann das Register Einfügen und klicken Sie in der Gruppe Tabellen auf das Symbol Pivot Table. Im jetzt eingeblendeten Dialogfenster aktivieren Sie das Optionskästchen Das Datenmodell dieser Arbeitsmappe verwenden. Die Pivot-Tabelle wird als neues Arbeitsblatt erstellt. Im Bereich Pivot Table-Felder klappen Sie die Tabellennamen mit einem Klick auf das jeweilige Dreieck vor den Tabellennamen auf, sodass Sie alle zugehörigen Feldbezeichnungen sehen. Aktivieren Sie in der Mastertabelle das Feld Kundennummer. Da es sich hierbei um ein numerisches Feld handelt, wird es als Wertefeld erkannt. Ziehen Sie daher dieses Feld im unteren Fensterbereich der Pivot Tabellenfelder (Felder zwischen den Bereichen Ziehen und Ablegen) aus dem Bereich Wert in den Bereich Zeilen. Aktivieren Sie jetzt in der Tabelle Mastertabelle das Feld Kundenname. Damit die Daten im Tabellenformat angezeigt werden, aktivieren Sie in den PivotTable-Tools das Register Entwurf. Klicken Sie auf die Berichtslayouts und wählen Sie die Darstellung In Tabellenformat darstellen aus. Jetzt werden die ausgewählten Daten übersichtlich dargestellt.
Darstellung der Beziehungen
Damit die Beziehung im Datenmodell dargestellt wird, aktivieren Sie das Feld Kundennummer aus den Feldern der Slavetabelle. Jetzt werden nur noch die Kunden angezeigt, die auch mindestens einen Ansprechpartner haben. Ändern Sie die Feldeinstellungen für die Kundennummer aus der Slavetabelle auf die Berechnungsart Anzahl. Klicken Sie dazu mit der rechten Maustaste auf das Wertfeld Summe von Kunden und wählen Sie im Kontextmenü den Befehl Wert-
feldeinstellungen aus. Das gleichnamige Dialogfenster wird eingeblendet. Auf dem Registerblatt Werte zusammenfassen nach wählen Sie den Eintrag Anzahl aus. Schon haben Sie eine Zusammenfassung der Firmen mit den zugeordneten Ansprechpartnern erstellt. Damit Sie auch die Namen der Ansprechpartner zu den Kunden angezeigt bekommen, wählen Sie aus den Pivot-Table-Feldern das Feld Nachname aus. Falls Sie auch die Abteilung der jeweiligen Ansprechpartner sehen möchten, setzen Sie einen Haken vor das PivoTable-Feld Abteilung.
Gruppierungen in den Pivottabellen ein- und ausblenden
Zur Darstellung von Ansprechpartnern zu bestimmten Kunden haben Sie die Möglichkeit, Kundengruppen zu reduzieren oder zu erweitern. Damit alle Kundennummern auf einmal reduziert werden, klicken Sie auf eine beliebige Kundennummer. Aktivieren Sie in den PivotTable-Tools das Register Analysieren. Klicken Sie dann in der Gruppe Aktives Feld auf das Symbol Feld reduzieren. Alle Kunden werden reduziert. Klicken Sie jetzt auf das Plussymbol vor der Kundennummer, die Sie analysieren möchten. Daraufhin werden alle Ansprechpartner zu dem ausgewählten Kunden eingeblendet. Damit Sie wieder alle Kunden mit vorhandenem Ansprechpartner sehen können, klicken Sie auf den Befehl Feld erweitern.
Daten ltern mit der Datenschnitt-Funktion
Eine komfortable Art der Daten lterung bietet der Datenschnitt. Aktivieren Sie zum Erstellen von Datenschnitten in der Gruppe Filtern das Symbol Datenschnitt einfügen. Das gleichnamige Dialogfenster wird eingeblendet. Wählen Sie in diesem Dialogfenster aus der Mastertabelle die Felder
Kundenname und Ort aus. Aus der Slavetabelle wählen Sie die Felder Abteilung und Nachname aus. Durch das Betätigen der Schalt äche OK werden die ausgewählten Felder als Datenschnitte in die Tabelle eingefügt. Das Geniale an der Filterung mit den Datenschnitten ist die Anzeige der Auswahl. Alle im obersten Datenschnitt (Kundenname) ausgewählten Felder zeigen die Treffer in den nachgeordneten Datenschnitten an. Die Treffer werden fett formatiert dargestellt. Möchten Sie jetzt zum Beispiel nur Ansprechpartner aus einer bestimmten Abteilung analysieren, so klicken Sie im Datenschnitt Abteilung auf den gewünschten Abteilungsnamen. Zum Entfernen von Filtereinstellungen im Datenschnitt dient das Symbol mit dem Trichter. Sobald Sie einen Datenschnitt aktivieren, werden die Optionen der DatenschnittTools eingeblendet. Mit den Formatvorlagen versehen Sie die Datenschnitte mit unterschiedlichen Farben, sodass eine Unterscheidung der einzelnen Filter gewährleistet ist.