Das Excel-Werkzeug "Tabelle" im Praxiseinsatz

Teil 2:
Tabellen konsolidieren mit ODBC und SQL
"Tabellen" und "strukturierte Verweise" sind hilfreiche Werkzeuge für die Datenanalyse in Excel. Welche Vorteile diese beiden Werkzeuge bieten, zeigt der erste Teil dieses Beitrags an praktischen Beispielen. Doch auch beim Einlesen externer Daten per ODBC sind Tabellen und strukturierte Verweise nützlich. In diesem zweiten und abschließenden Teil beschreibt Ignatz Schels, wie Sie Daten aus externen Quellen importieren, als Tabellen bearbeiten und mit strukturierten Verweisen analysieren können.

Das Excel-Werkzeug "Tabelle" im Praxiseinsatz

Teil 2:
Tabellen konsolidieren mit ODBC und SQL
"Tabellen" und "strukturierte Verweise" sind hilfreiche Werkzeuge für die Datenanalyse in Excel. Welche Vorteile diese beiden Werkzeuge bieten, zeigt der erste Teil dieses Beitrags an praktischen Beispielen. Doch auch beim Einlesen externer Daten per ODBC sind Tabellen und strukturierte Verweise nützlich. In diesem zweiten und abschließenden Teil beschreibt Ignatz Schels, wie Sie Daten aus externen Quellen importieren, als Tabellen bearbeiten und mit strukturierten Verweisen analysieren können.

Ab Version 2007 stellt Excel für die Datenanalyse die Werkzeuge "Tabellen" und "strukturierte Verweise" zur Verfügung. Welche Vorteile diese gegenüber herkömmlichen Excel-Listen und Zellverweisen bieten und wie man strukturierte Verweise verwendet, um Tabellendaten zu analysieren, ist im ersten Teil des Beitrags beschrieben. Doch auch beim Einlesen externer Daten per ODBC (open database connectivity) bringen die Werkzeuge Vorteile. Der zweite und abschließende Teil zeigt, wie Daten per ODBC aus externen Quellen importiert, als Tabellen bearbeitet und mit strukturierten Verweisen analysiert werden.

ODBC ist eine standardisierte Datenbankschnittstelle von Microsoft auf Basis der Datenbanksprache SQL. Für den Datenimport in Excel-Tabellenblätter stehen entsprechende Treiber zur Verfügung, die mit der Installation des Office-Pakets eingerichtet werden. Welche ODBC-Treiber genau auf Ihrem Rechner installiert sind, können Sie im "ODBC-Datenquellen-Administrator" erkennen, den Sie in der Systemsteuerung in der Gruppe "Verwaltung" finden.

Standardmäßig bietet Excel Importmöglichkeiten für Daten aus unterschiedlichen Quellen, u.a. aus Access, dem Web und aus Textdateien. Sie finden die entsprechenden Befehle im Register Daten im Bereich Externe Daten abrufen (Bild 1). Hinter jeder der Optionen verbirgt sich ein Assistent, der den Anwender durch den Prozess der ODBC-Integration führt. Für einfache Abfragen z.B. aus externen Excel-Tabellenblättern reicht der ODBC-Abfrageassistent "Microsoft Query" aus, den Sie im Bereich Externe Daten abrufen über die Auswahl Aus anderen Quellen aufrufen.

Bild 1: Für einfache Abfragen externer Excel-Dateien reicht der ODBC-Abfrageassistent "Microsoft Query" aus.

ODBC und Tabellen

Eine ODBC-Verbindung auf externe Daten lieferte in Excel bis Version 2003 eine Liste, die mit der externen Datenquelle verknüpft war. Ab Excel 2007 ist das Ergebnis eine Tabelle – was die Datenanalyse erleichtert. (Eine ausführliche Beschreibung zu Tabellen und strukturierten Verweisen finden Sie im ersten Teil dieses Beitrags.)

Gegenüber herkömmlichen Listen haben Tabellen folgende Vorteile:

  • Tabellen haben einen Namen, der sich automatisch an die Dimension der Tabelle anpasst. Werden neue Daten angefügt, erweitert sich der Bereich, auf den sich der Tabellenname bezieht, automatisch.
  • Die Ergebniszeile summiert oder zählt automatisch einzelne Spaltenwerte.
  • Mit internen strukturierten Verweisen können Daten spaltenweise berechnet werden. Die Formeln verwenden keine Bezüge (z.B. =$A$1), sondern Spaltennamen (z.B. =[@Budget]) und sind daher intuitiver zu bedienen.
  • Externe strukturierte Verweise verwenden Elemente der Tabelle für Analysen, zum Beispiel einzelne Spalten ([Spaltenname]), den Datenbereich ([#Daten]), die Kopfzeile ([#Kopfzeilen]) oder die Ergebniszeile ([#Ergebnisse]).
  • Die Vorteile von Tabellen als Ergebnis einer ODBC-Verknüpfung sind:
  • Die Tabelle bleibt nicht auf die Spalten aus der Quellanwendung beschränkt, sondern es können jederzeit weitere Spalten ergänzt werden. Befinden sich in diesen Spalten Formeln, schreibt Excel diese automatisch fort, falls sich durch die Aktualisierung der ODBC-Verbindung der Tabellenbereich vergrößert.
  • ODBC-Verknüpfungen sind technisch gesehen SQL-Befehle. Wer diese (einfache) Datenbanksprache beherrscht, kann externe Tabellen gezielt filtern, sortieren oder sogar kombinieren.

Externe Daten per ODBC zusammenführen

Beispiel: Mehrprojektverwaltung

Das Prinzip des Datenimports über ODBC lässt sich am einfachsten an einem Beispiel erläutern (Die zugrunde liegenden Listen finden Sie in der Beispieldatei im Anhang). Im Beispiel sollen Projektlisten aus unterschiedlichen Tabellenblättern einer Excel-Arbeitsmappe zu einer gemeinsamen Liste bzw. Tabelle zusammengeführt werden. Die Listen sind einheitlich aufgebaut und enthalten neben den Spalten Projekt, Beginn und Ende auch Spalten für die Plankosten und den jeweiligen Fertigstellungsgrad (Bild 2). Da im Beispiel die einzelnen Unternehmensbereiche sowohl die Projektlisten als auch die Fertigstellungsgrade der Projekte ständig updaten, muss die Gesamtübersicht dynamisch auf jede Änderung reagieren und stets den aktuellen Stand darstellen. Außerdem soll in der Gesamtliste aus den Produkten von Plankosten und Fertigstellungsgrad der Earned Value (Fertigstellungswert) für alle Einzelprojekte berechnet werden.

Die Projektlisten sind nicht in Tabellen konvertiert und haben auch keine Bereichsnamen. Beides ist zwar möglich, jedoch nicht Voraussetzung für den Datentransfer per ODBC sowie die Analyse mit strukturierten Verweisen.

Achten Sie darauf, dass die Listen weder überflüssige Zeilen und Spalten enthalten noch Teilergebnisse oder Zwischensummen. Über die Filterfunktionen der ODBC-Verbindung lassen sich zwar z.B. Leerzeilen leicht herausfiltern, am sichersten sind jedoch reine Datenlisten.

Bild 2: Drei Projektlisten in Excel-Tabellenblättern.

Gesamtübersicht anlegen und die erste Liste per ODBC einlesen

  • Schließen Sie die Arbeitsmappe mit den drei Projekten, sie darf während des Aufbaus der ODBC-Verbindung nicht aktiv sein.
  • Legen Sie für die Analyse der Projekte eine neue Arbeitsmappe an. In der ersten leeren Tabelle starten Sie den ODBC-Transfer mit dem Befehl Daten / Externe Daten abrufen / Aus anderen Quellen. Wählen Sie Von Microsoft Query.
  • In der Liste der ODBC-Datenquellen entscheiden Sie sich für den ODBC-Treiber für Excel. Ab Version 2007 von Excel heißt dieser Excel Files, zuvor hatte er die Bezeichnung Excel Dateien. Die Option Query-Assistenten zur Erstellung / Bearbeitung von Abfragen verwenden muss aktiviert sein, damit der Assistent Sie durch die Prozedur führt.
  • Die Verbindung wird…

Bewertungen und Kommentare

(nur angemeldete Benutzer)

Diese Funktion steht nur eingeloggten Nutzern zur Verfügung. Jetzt einloggen
Gesamt
Bewertungen 1

Fortsetzungen des Fachartikels

Teil 1:
Mit strukturierten Verweisen die Datenanalyse vereinfachen
Projektdaten, wie z.B. Termine, Arbeitspakete oder Kosten werden oft als Listen in Excel gepflegt, um sie bequem auswerten zu können. Ändert sich allerdings der Umfang einer solchen Liste, müssen zur Datenanalyse oft die Zellverweise von Formeln, …
Alle anzeigen