Intelligente Tabellen/Listen (1)

Arbeiten mit Intelligenten Tabellen bzw. Listen

Hinter diesen unscheinbaren Namen versteckt sich eine Leistungsfähigkeit des Excel, von der ich früher noch nicht einmal zu träumen wagte. Und auch heute noch freue ich mich immer wieder aufs neue, dass ich dieses Feature nutzen kann. Apropos „nutzen“, viele User nutzen die Funktionalität an sich, das heißt sie klicken auf die Menü-Schaltfläche Als Tabelle formatieren um die Daten farblich aufzupeppen. Das ist ein angenehmer Nebeneffekt, die vielen positiven Dinge, die sich dahinter noch verbergen, sind weitestgehend unbekannt. Und dem wollen wir mit diesen Beiträgen entgegen wirken.

Vorbereitung

Sie sollten auf jeden Fall die hier vorgestellten Beispiele selbst nacharbeiten, denn nur lesen bringt nicht wirklich viel. Als Datenbasis verwende ich eine hier im Blog öfter verwendete Tabelle, die Mitglieder des deutschen Bundestages, Stand Mitte 2015. Das sind erst einmal die Roh-Daten, die zu einer Kunden-Liste aufbereitet werden. Da das nicht direkt und vor allen Dingen nicht ausschließlich zu diesem Thema gehört, habe ich die Beschreibung der Aufbereitung ausgelagert und an dieser Stelle in unserem Blog beschrieben. Die fertige Kunden-Liste steht Ihnen hier zum Download zur Verfügung. Der Form halber noch dieser Hinweis: Nur die Namen und die Partei-Zugehörigkeit entspricht der Realität zu dem Zeitpunkt, alle anderen Angaben sind durch den Zufall bzw. durch die Zufalls-Funktionalität des Excel bestimmt. Die später verwendeten kalendarischen Daten sowie die Umsatzzahlen als auch die Auswahl der Käufer sind reine Fiktion und ohne die Absicht einer Wertung.

Auch wenn Sie die Arbeitsmappe selber erstellt haben, sollten Sie der besseren Nachvollziehbarkeit und Vergleichbarkeit wegen stets die hier verlinkten Mappen verwenden; Kundennummern, (später verwendete) kalendarische Daten und Beträge sind dann in jedem Fall genau so, wie hier aufgezeigt.

Laden Sie also bitte erst einmal nur die Kundenliste, soweit Sie es noch nicht getan haben. Öffnen Sie die Datei. Bewerten Sie den Aufbau der Datei. – Sie erkennen, dass in der ersten Zeile Überschriften sind und danach folgend über 600 Kunden, die nach ihrem Nach- und Vornamen sortiert sind. Und nun sind Sie fit für die Intelligente Tabelle.  🙂 

Erste Schritte (Überblick)

Eigentlich ist dieser Abschnitt ein Intermezzo. Vielleicht auch ein Appetizer, um Sie auf den Geschmack zu bringen und um aufzuzeigen, dass auch größere Datenmengen problemlos handhabbar sind. Wobei auch die hundertfache Menge an Zeilen kein Problem darstellt. – So richtig Schritt für Schritt werden Sie weiter unten in die Materie eingeführt.

Die oben genannte Datei (mit den knapp 700 Datensätzen) haben Sie geladen, die Kundenliste ist das aktive Blatt. Die aktive Zelle ist an beliebiger Stelle im Datenbereich, beispielsweise A1. Zu diesem Zeitpunkt betätigen Sie StrgL oder StrgT; beides führt zum gleichen Ziel, soweit Sie eine Version ab 2007 haben. Umgehend wird sich solch ein Dialog-Fenster öffnen:

Dialogfenster zur Erstellung einer Liste

Dialogfenster zur Erstellung einer Liste

Kontrollieren Sie unbedingt die Vorgaben, denn es ist nicht zwingend gewährleistet, dass die dort stehenden Angaben stimmen. Der Bereich wird aus dem Areal der zusammenhängenden Daten gebildet, welche sich um die aktive Zelle gruppieren. Komplett leere Zeilen bedeuten das Ende der Liste! - Die Überschriften identifiziert Excel, wenn in der ersten Zeile der Markierung ein anderer Datentyp vorhanden ist als in den darunter liegenden. In diesem Fall sind die Vorgaben korrekt und Sie können sie per OK übernehmen. Umgehend zeigen sich die Daten in einem gänzlich anderen Layout:

Aus den Daten automatisch erstellte Tabelle

Aus den Daten automatisch erstellte Tabelle

Was natürlich sofort auffällt: Die blau-weiße Tabellierung und dass offensichtlich in der Überschriftzeile noch Untermenüs oder weitere Auswahlen verborgen sind. Die Sortierung nach dem Namen hat sich nicht geändert.

Klicken Sie in A1 einmal auf die -Schaltfläche. Sie werden erkennen, dass Sie alle Filter- und Sortierungsmöglichkeiten haben, die in diesem Kontext sinnvoll sind. Und das, obwohl Sie sich nicht im Daten-Menü befinden. Das gilt für jede Spalte der Tabelle, obwohl Sie in dieser Richtung nicht direkt aktiv geworden sind. – Nutzen Sie einige Möglichkeiten der Sortierung und Filterung, um sich damit vertraut zu machen.

Selbstredend können Sie auch in einer Spalte mehrere Filter einsetzen. Oder auch in unterschiedlichen Spalten. Und dann noch sortieren. Vieles ist mit wenigen Mausklicks möglich, was einer der großen Vorteile der Listen ist. Zurück zum Ursprungszustand kommen Sie immer, wenn Sie die Spalten einzeln zurück setzen. Wenn Sie mehrere Filter verwendet haben, können Sie diese auch mit einem Schlag zurücksetzen, indem Sie im Menü Daten auf die Filter-Schaltfläche klicken.

Diese Tabelle bzw. Liste wird in folgenden Abschnitten eine wesentliche Rolle spielen. Es ist die Liste aller Kunden eines imaginären Unternehmens. Beispielsweise eines des Caterings des Deutschen Bundestages ähnlichen Geschäftszweiges. Aber das ist sekundär. Sie werden jetzt erst einmal im „Trippelschritten“ anhand einer wesentlich kleineren Datenbasis einige elementare Vorteile der Intelligenten Tabellen kennen lernen.

Intermezzo

Laden Sie zu Beginn einfach einmal diese kleine Mappe herunter. Es ist ein mehr als spartanisch einfaches Rechnungsformular, aber das ist gewollt so. Als erstes markieren Sie bitte die kompletten Spalten E:F und betätigen dann StrgShift$. Sie werden keinen Effekt bemerken, noch nicht … Danach tragen Sie einfach bitte folgende Werte in Zeile 4 ein:

  • Datum: 1.4.15
  • Anzahl: 3
  • Einheit: Paar
  • Bezeichnung: Socken
  • Einzelpreis: 4,95

Dabei belassen Sie es bitte erst einmal. Übrigens, ist Ihnen etwas aufgefallen? Der Einzelpreis ist automatisch als € (oder Ihrer Landeswährung) formatiert. Das haben Sie gerade vorher initiiert, als Sie auf die beiden kompletten Spalten die erwähnte Tastenkombination  StrgShift$ angewendet haben.

Achten Sie nun darauf, dass die Aktive Zelle im Bereich der Tabelle ist. Entweder per Klick auf die Schaltfläche im Hauptmenü Start oder per StrgL bzw. StrgT wandeln Sie den Bereich A3:F4 in eine Intelligente Tabelle, eine Liste um. So weit ist das ja nichts neues und nichts spektakuläres. Aber woran erkennen Sie, dass es sich wirklich um eine Liste handelt? Wenn Sie eben über das Menü gegangen sind hätten Sie ja ein Layout wählen können, das keine farblichen Hervorhebungen bietet und dann wäre die Identifikation nicht wirklich leicht.

Zwei Möglichkeiten will ich Ihnen hier aufzeigen. Markieren Sie bitte den reinen Datenbereich der eben erstellten Tabelle, ohne Überschriften. Also A4:F4. Das geht von Hand oder Sie aktivieren eine Zelle innerhalb der Liste und dann StrgA. Achten Sie dann einmal auf den Bereich des Tabellenblatts, wo die Adresse oder der Bereichsname steht (in der Abbildung hierunter gelb gemarkert):

Automatisch vergebener Bereichs-Name für die Tabelle/Liste

Automatisch vergebener Bereichs-Name für die Tabelle/Liste

Und da Sie den Namen nicht vergeben haben, muss es ja Excel gewesen sein  😉 . Interessant ist vielleicht folgendes: Wenn Sie die Überschriften in die Markierung mit einbeziehen, dann wird Ihnen dort auch nicht mehr der Bereichsname angezeigt. – Der zweite Weg der Kontrolle: Egal wo Sie sich in der Tabelle befinden, F5 und dann Tabelle1 als Verweis wählen. OK und ruck zuck wird der Datenbereich der Liste markiert.

„Sprechende“ Formeln

Natürlich ist mit dieser Überschrift nicht gemeint, dass Ihr Rechner mit Ihnen redet  😎 . Gemeint ist, dass Formeln, die Formeltexte durchaus sehr selbsterklärend sein können. So auch in unserer Liste. Auch wenn es Ihnen erst einmal umständlich erscheint, gehen Sie exakt so vor, wie hier beschrieben:

  • Klicken Sie in F4 
  • Schreiben Sie das = – Zeichen
  • Klicken Sie in B4 
  • * und dann ein Klick auf E4 
  • Eingabe schließt die Formel ab.

Die Berechnung ist (natürlich) korrekt. So weit keine Überraschung. Aber schauen Sie sich einmal die Formel an, die könnte eine Überraschung sein: =[Anzahl]*[Einzelpreis]. Das ist doch mal eine klare und deutliche Ansage. Der Endpreis berechnet sich aus dem Einzelpreis multipliziert mit dem Endpreis. Schick!

Automatik ist besser …

Nein, es geht nicht ums Autofahren. Da mag jeder so denken, wie sie oder er will. Es geht natürlich um Excel. Schreiben Sie in A5 einfach einmal 6-5 hinein. Ohne Gleichheitszeichen, ohne alles. Und wenn Sie die Zelle verlassen, dann wird Ihnen vielleicht das eine oder andere auffallen. Erst einmal steht in A5 das Datum 06.05. (des aktuellen Jahres) und es ist in exakt dem Format, wie in A4 bereits vorhanden. Und schauen Sie einmal nach F4. Dort wurde augenscheinlich bereits eine Berechnung vorgenommen. Und wenn Sie in die Zelle klicken werden Sie erkennen, dass tatsächlich die Formel aus der darüber liegenden Zelle 1:1 übernommen wurde. Wenn Sie nun noch 2 Dosen Niespulver á 1,82 ergänzen wird es Sie nicht wundern, dass der Gesamtpreis dort steht, ohne dass Sie die Formel nach unten kopiert haben. Also: Automatik ist oft, aber nicht immer besser. Und wir sind als Anwender nicht davon befreit, einmal mehr hin zu sehen.

Einer geht noch  😀 

Also, ich konnte es mir nicht verkneifen, im Internet nachzusehen. Es gibt doch tatsächlich heute noch Niespulver. Zwar wohl nicht mehr als Mittel aus dem medizinischen Bereich (wie vor etwa 50 Jahren) sondern nur noch als Scherzartikel. Aber das nur nebenbei. Ich möchte Ihnen eine weitere ausgesprochene Annehmlichkeit der Intelligenten Tabelle vorstellen. Und zwar soll die Gesamtsumme der Rechnung automatisch berechnet werden.

Eigentlich kein Problem, wenn ich da nicht eine kleine Gemeinheit in das Tabellenblatt eingebaut hätte. Aber dazu gleich mehr.  😈  Das Ergebnis der Berechnung soll in H3 stehen und auch einen Aufschlag von 19% MwSt. enthalten. Nochmals der Hinweis, dass Sie unbedingt die angebotene Mappe Rechnung (leer) verwenden, sonst ist der Effekt nicht gegeben. – Die typische Formel in H3 wäre ja nun: =SUMME(F:F)*1,19 und sollte auch das korrekte Ergebnis bringen.

Dass das angezeigte Ergebnis nicht richtig sein kann, das steht fest. Aber was ist hier falsch gelaufen? Die Formel an sich ist absolut OK. Also was passt da nicht? Na gut, ein „netter“ Kollege will Sie zur Verzweifelung bringen und hat in F30 eine 100 eingetragen. Und dann die Formatierung der Zelle so eingerichtet, dass alles unsichtbar ist. Setzen Sie das Zellformat dort erst einmal auf Standard und löschen Sie anschließend den Inhalt dieser Zelle. Dann stimmt auch die Berechnung.

Zurück zu unserer Intelligenten Tabelle und deren Vorzügen. Der Demonstration wegen schreiben Sie gerne noch einmal in A30 die 100 hinein; dann sind praktisch gleiche Ausgangsbedingungen für das folgende Procedere. - Löschen Sie jetzt erst einmal die etwas fehleranfällige Formel und gehen Sie stattdessen wiederum exakt so vor:

  • Fügen Sie standardmäßig das Gleichheitszeichen = ein
  • Anschließend summe( (mit der öffnenden Klammer)
  • Danach zeigen Sie mit der Maus auf den oberen Rand der zu berechnenden Spalten-Überschrift. Der Mauszeiger muss ein dicker Pfeil sein.
  • Jetzt ein Mausklick (links) und sofort wird der Datenbereich der entsprechenden Spalte markiert:

Ganze Spalte korrekt markieren

Ganze Spalte korrekt markieren

Sie sehen, dass dieses Mal der automatisch vergebene Name der Tabelle in die Formel einfließt und die Spaltenüberschrift in [eckigen Klammern] verwendet wird.

  • Schließen Sie die Formel mit der runden Klammer und ergänzen Sie noch *1,19.  Die Formel sieht nun so aus: =SUMME(Tabelle1[Gesamtpreis])*1,19
  • Prüfen Sie noch einmal das Ergebnis, es wird korrekt sein.
  • Tragen Sie nun in Zeile 6 beliebige Werte ein und beachten Sie, was sich mit der Brutto-Summe tut. Der zu berechnende Bereich wird dynamisch angepasst.

So viel erst einmal zu den wesentlichen Grundlagen der dynamischen Tabelle. Sie können nun noch ein wenig experimentieren. Beispielsweise mit der Formatierung, die alleine mit den bereits vorhandenen Vorlagen recht vielgestaltig sein kann. Zur Sicherheit noch einmal die von uns erstellte Mappe zum Vergleich.

Hinweis: Wenn Sie schon ein gewisses Maß an Erfahrung mit Intelligenten Tabellen haben werden Sie hier und da gewiss über scheinbare Ungereimtheiten „stolpern“. Ein Beispiel wäre die Rechnungs-Summe; ja, es geht auch anders und diese Funktionalitäten werden auch weitestgehend diskutiert. Aber alles zu seiner Zeit.  🙂 

In einem weiteren Beitrag werden Sie dann auch mit der ersten Datei weiter arbeiten, wo auch eine Liste der Verkäufe für Sie bereit steht. Derzeit aber (Stand: Dezember 2015) ist das noch angedacht, steht aber auf der To-Do-Liste.

Das könnte Sie auch interessieren: Intelligenten Listen einen Namen geben

▲ nach oben …

Dieser Beitrag wurde unter Ohne Makro/VBA, Tabelle und Zelle abgelegt und mit , , verschlagwortet. Setze ein Lesezeichen auf den Permalink.