Sie ist noch nicht angekommen beim Excel-User: Die Tabelle. Über kurz oder lang wird sie aber Vorrang vor der Liste bekommen, und die strukturierten Verweise lösen endlich (endlich!) die Tabellenbezüge ab. Worum es geht? Um das ab Excel 2007 verfügbare Symbol Tabelle unter Einfügen/Tabellen (neben der PivotTable). Und sollten Sie das bisher unter „kenn ich nicht, brauch ich nicht“ eingeordnet haben, wird´s aber höchste Zeit, sich damit zu beschäftigen.

Am Anfang war die Liste

Wenn ich das Thema Tabelle im PowerUser-Seminar oder bei Seminaren für Controller oder Personalfachkräfte anspreche, kennt kaum jemand die neue Technik. Zu lange hat man sich mit Listen und Bezügen beschäftigt, zu fest sitzen die alten Zöpfe mit Zellenbezügen, zum Beispiel in Formeln wie =SUMME($A1:$A345). Selbst der Begriff Liste ist eher selten geläufig. Nachdem Microsoft dem Benutzer auf jede beliebige Datenmenge im Tabellenblatt Filter und Sortierungen anwenden lässt, ist es diesem egal, ob er eine brauchbare Liste vor sich hat. Das war nicht immer so: Bis Version 4.0 musste eine Liste mit sauberer (Text)kopfzeile komplett markiert werden, bevor ein AutoFilter oder ein Sortierlauf seine Dienste anbot. Die Profis machen das immer noch, um große Datenmengen sicher im Griff zu haben, und zwar mit Shortcuts wie [Strg]+[*] (current region) oder – mit dem Zellzeiger in der linken oberen Zelle – [Strg]+[Umschalt]+[Ende] (used region). Letztere ist die sichere Variante, denn was dieser Shortcut nicht erwischt, wurde im Tabellenblatt noch nie benutzt. [STRG]+[*] entspricht dem Menübefehl Start/Bearbeiten/Suchen und Auswählen/Gehe zu/Inhalte/aktueller Bereich und markiert Listen, die an der erste Leerzeile und an der ersten Leerspalte ihre Grenzen haben.

Tipp: Wenn der used region größer ist als er sein sollte, auch wenn nichts mehr drin steht, löschen Sie alle Zeilen und Spalten ab Ende der Liste, speichern und schließen die Mappe und öffnen sie wieder.

Dass die Unkenntnis über Listen und Listenmarkierungen besonders bei großen Datenmengen in der Praxis zu massiven Fehlern führen, muss ich nicht mehr erwähnen (siehe „Wie Excel die Welt ruiniert“). Wer zum Filtern und Sortieren die ganze Kopfzeile markiert oder für den Datenbereich der PivotTable ganze Spalten benutzt, hat das Prinzip der Liste auf jeden Fall nicht verstanden.

Mit Excel 97 wagte man den ersten Versuch, die Listen zu dynamisieren, d.h. dem Benutzer einen Bereich erzeugen zu lassen, der sich mit Eingabe neuer Daten automatisch aktualisierte (was Datenbanktabellen immer schon gemacht haben). Dazu musste ein Bereich (Liste!) zur Liste erklärt werden, die Liste hatte dann einen Namen und dieser erweiterte sich selbständig, sobald neue Datensätze angefügt wurden. Der Befehl im Extras-Menü wurde Liste genannt, damit konnte niemand was anfangen, auch die automatische Ergebniszeile konnte die Excel-Gemeinde nicht davon überzeugen, sich langsam von der Liste zu lösen.

Von der Liste zur Tabelle

Ab Excel 2007 heißt die dynamische Liste Tabelle, was auch nicht die beste Übersetzung ist, denn unter einer Tabelle versteht Excel Normal-User halt nun mal das Tabellenblatt. Die Amis tun sich leicht, ein sheet ist ein Tabellenblatt, eine table ist eine dynamische Liste. Wir müssen uns daran gewöhnen und vor allem unseren Sprachgebrauch auf die beiden Begriffe einstellen:

  • Eine neue Arbeitsmappe enthält keine Tabellen, sondern Tabellenblätter (gut ist auch der Begriff Register).
  • Listen, die sich automatisch erweitern, sind Tabellen. Zum Erzeugen einer Tabelle wird die Liste markiert und mit Einfügen/Tabellen/Tabelle in eine Tabelle umgewandelt.

 

Wie die Tabelle anschließend formatiert wird, wie die Ergebniszeile zu nutzen ist und was eine Tabelle sonst noch zu bieten hat, können Sie in meinen Büchern Excel Praxisbuch für die Versionen 2010 und 2013 und Excel Formeln und Funktionen nachlesen, hier finden Sie auch praktische Beispiele zur Tabellenfunktion.

 

Strukturierte Verweise

Die wichtigste Neuerung, die unsere Tabelle mit sich bringt, ist der strukturierte Verweis. Da eine Tabelle als dynamischer Bereich kein definiertes Ende mehr hat, werden nicht mehr die Zellen im Tabellenblatt, sondern die Spalten für die Berechnungen benutzt. Hier am Beispiel der Spalte Umsatz. Die Zelle E2 enthält die Formel für Menge mal Preis:

  • Formel in der Liste: =C2*D2
  • Formel in der Tabelle: =[@[Preis brutto]]*[@Menge]

Strukturierte Verweise funktionieren auch von außen, zum Beispiel dieser, der die Anzahl Produkte über 50 EUR Ladenpreis berechnet (Zelle außerhalb, z. B. $H$1):

  • Formel in der Liste: =ZÄHLENWENN($C$2:$C$8;“>50″)
  • Formel in der Tabelle: =ZÄHLENWENN(Tabelle1[Preis brutto];“>50″)

Jetzt wird’s spannend: Tabellen und ODBC

Wer per ODBC externe Daten aus Datenbanken, Textdateien oder Excel-Listen einliest, kommt nicht umhin, sich mit Tabellen zu beschäftigen, denn genau eine solche legt Excel mit der Verknüpfung an. Und das verdoppelt den Nutzeffekt der Tabelle: Der Bereich ist durch die ODBC-Verknüpfung dynamisch, lässt sich über den Tabellennamen ansprechen, und die Tabelle bietet die Möglichkeit, zusätzliche Spalten sowie strukturierte Verweise zu nutzen.

Mit Daten/Externe Daten abrufen wird zunächst die Datenquelle bestimmt (Access-Datenbank, HTML-Webdateien, Textdaten, SQL-Server, Analysis-Cubes u.a.).

Excel-Tabellen importieren Sie mit dem Query-Assistenten, verwenden Sie den ODBC-Treiber Excel-Files und bestimmen Sie die Arbeitsmappe. Wählen Sie den Bereich oder das Tabellenblatt und importieren Sie die gewünschten Spalten.

Tipp: Der ODBC-Treiber für Excel erkennt nur Bereichsnamen. Enthält Ihre Arbeitsmappen nur namenlose Tabellenblätter, werden Sie die Fehlermeldung „Diese Datenquelle enthält keine sichtbaren Tabellen“ erhalten. Bestätigen Sie mit OK und schalten Sie unter Optionen die Systemtabellen ein, dann werden die Listen in den Tabellenblättern angeboten. Tabellennamen kennt der ODBC-Treiber übrigens nicht.

Im letzten Dialog des Query-Assistenten bestimmen Sie, ob die ODBC-Verknüpfung als Tabelle oder gleich als PivotTable angelegt wird. Entscheiden Sie sich für die Tabelle und geben Sie dieser über die Tabellentools gleich einen kürzeren Namen (der natürlich weiterhin jede Aktualisierung mitmacht).

Die Tabelle lässt sich jetzt mit zusätzlichen Spalten versehen, sowohl inmitten der ODBC-Verknüpfung als auch rechts oder links davon. In unserem Beispiel könnten Sie den Nettopreis aus dem Bruttopreis herausrechnen. Geben Sie der Tabelle vorher den Namen „tbl_Sales“, dann bleiben die strukturierten Verweise übersichtlich:

  • Spalte C markieren, mit [Strg]+[+] eine neue Spalten einfügen.
  • Spalte mit „Preis netto“ benennen
  • Formel in C2: =[@[Preis brutto]]/1,19

Die Formel für die Umsatzsumme (externer Verweis, z. B. in Zelle H1):

=SUMME(tbl_Sales[Umsatz])

Alternativ dazu können Sie natürlich einfach die Ergebniszeile der Tabelle einschalten.

Noch ein Tipp: Im Projektmagazin habe ich einen zweiteiligen Artikel über Tabellen und strukturierte Verweise mit Fokus auf Projektarbeit veröffentlicht. Nutzen Sie das kostenlose Schnupperabo zum Lesen und Downloaden der Beispiele:

www.projektmagazin.de/artikel/das-excel-werkzeug-tabelle-im-praxiseinsatz-teil-1_1082963