• DV-Seminare und Workshops

    DV-Seminare und Workshops
  • Datenbanken und VBA-Makroprogrammierung

    Datenbanken und VBA-Makroprogrammierung
  • Gute Computerbücher

    Gute Computerbücher

Wie Excel Tabellen neu definiert

XLPUW2

27. Februar 2014

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.

tabellen1 300x155

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 und Excel Formeln und Funktionen nachlesen, hier finden Sie auch praktische Beispiele zur Tabellenfunktion.

ExcelPraxis FF Hanser

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 aus externen Daten 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 (bis Excel 2016) oder Daten/Abrufen und Transformieren wird zunächst die Datenquelle bestimmt (Access-Datenbank, HTML-Webdateien, Textdaten, SQL-Server, Analysis-Cubes u.a.).

Excel-Tabellen importieren Sie in älteren Versionen 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. 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.

Ab Excel 2016 verwenden Sie Power Query zum Importieren von Excel-Daten. Wählen Sie Daten/Abrufen und Transformieren. Geben Sie die Excel-Mappe als Quelle an.

Die Tabelle lässt sich 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

Seminare

Ignatz Schels und Ignaz A. Schels jun. gehören zu den besten EDV-Trainern für Excel-Spezialseminare, Access-Datenbankentwicklung und Programmierung im Office-Umfeld (VBA, M). Ihre Partner sind renommierte Management-Seminaranbieter in Deutschland, in der Schweiz und in Österreich.

Seminare von Schels DV-Training sind alles andere als Standard. Als Autoren von Büchern und Artikeln sind sie stets am Puls der Zeit und mit neuen Technologien vertraut. 

Dazu kommen langjährige Erfahrung und viele Einsätze bei zahlreichen Firmen, Instituten und Behörden. Ihre Spezialseminare sind bestens geeignet für fortgeschrittene Anwender und unverzichtbar für Unternehmensleiter, um die Mannschaft auf Kurs zu bringen und interne Prozesse zu optimieren. Erfahrung und Kompetenz, Liebe zum Job und zum Mitmenschen und eine große Portion Humor - das macht ein unvergessliches Seminar von Schels DV-Training. 

Zur Seminarübersicht

Datenbankentwicklung, VBA-Programmierung und BI-Lösungen

Von der PivotTable bis zur Multiuser-Datenbank mit Schnittstelle zu SAP, Cubes und Big Data - Entwickeln Sie im Workshop Datenbanken und VBA-Lösungen mit Excel, Access und anderen Office-Komponenten (Outlook, Word, PowerPoint ..). Optimieren Sie Ihr Berichtswesen mit professionellen Business-Intelligence-Lösungen und interaktiven Dashboards.

Schnell, kostengünstig mit Festpreisgarantie und Rundum-Service - so bleibt das Budget im Rahmen.

Lassen Sie Ihre Mitarbeiter "on the job" schulen - die Ausbildung von "key usern" während der Programmierung verringert Folgekosten. Fordern Sie ein Angebot an und vereinbaren Sie einen Analyse- und Konzepttermin.

Hier einige Projektbeispiele.

 

 

Bücher, Fachpublikationen, Trainings-Videos

Die beste Fachliteratur über Microsoft Office-Applikationen: Fachbücher von Ignatz Schels und Ignaz A. Schels jun. sind aus gutem Grund Bestseller: Von Praktikern für die Praxis geschrieben, sofort anwendbare Beispiele, Tools und Makros und natürlich immer mit den besten Tipps und Tricks.

Die beste Wahl für Ihre persönliche Weiterbildung oder als Seminarunterlagen für Inhouse-Seminare.

Hier finden Sie eine Übersicht über die aktuellen Bücher.

Suche