Vor Ver­wen­dung der Daten wer­den meis­tens For­ma­tie­run­gen & Berech­nun­gen durch­ge­führt. Bei neu­en Daten müs­sen die aus­ge­üb­ten Schrit­te immer wie­der manu­ell umge­setzt wer­den, zumal das Risi­ko für Feh­ler immer grö­ßer wird.

Um dies zu ver­mei­den, bie­tet Excel soge­nann­te Intel­li­gen­te Tabel­len an. Die Vor­tei­le und Funk­tio­nen die­ser Tabel­len erklä­re ich dir in die­sem Bei­trag. Enjoy! 🙂

Aufbau & Design 🖌

Tabelle erstellen

Bevor wir die Daten in eine intel­li­gen­te Tabel­le umwan­deln, soll­te sicher­ge­stellt wer­den, dass alle Spal­ten eine ein­deu­ti­ge Bezeich­nung haben und die Daten­sät­ze voll­stän­dig sind, also kei­ne Lücken aufweisen.

Im Fal­le einer feh­len­den Bezeich­nung oder einer neu­en Spal­te ver­gibt die intel­li­gen­te Tabel­le auto­ma­tisch die Bezeich­nung Spal­teX, wobei X für eine fort­lau­fen­de Num­mer steht.

Nun kli­cken wir in eine belie­bi­ge Zel­le der Daten und drü­cken den Hot­key STRG + T oder STRG + L. Über die Benut­zer­ober­flä­che fin­dest du die intel­li­gen­te Tabel­le unter der Regis­ter­kar­te Ein­fü­gen –> Tabel­le.

Wie du unschwer erken­nen wirst, ist die auto­ma­ti­sche Tabel­le nun erstellt. Die Über­schrif­ten sind fett & mit Far­be hin­ter­legt, eben­so sind die Daten­sät­ze farb­lich von­ein­an­der abge­ho­ben.
Zusätz­lich hat die Tabel­le einen Fil­ter ver­passt bekom­men, die­ser kann aber auch per STRG + SHIFT + L (oder die Benut­zer­ober­flä­che) deak­ti­viert werden.

Möch­test du neue Daten anfü­gen, so kannst du die­se ein­fach drun­ter oder dane­ben ein­fü­gen / erfas­sen. Die Tabel­le erkennt die­se auto­ma­tisch. Die Aus­nah­me bil­det die Ergeb­nis­zei­le (sie­he hier). Ist die­se akti­viert, musst du in der letz­ten Daten­zei­le in der letz­ten Spal­te per TAB-Taste (links von Q) eine neue Zei­le ein­fü­gen, dort kannst du die Daten dann ein­fü­gen.

Tabellenname – Definition deiner Daten!

Auf der lin­ken Sei­te fin­dest du ein Feld, in wel­chem du der Tabel­le einen Namen geben kannst. Dies ist sehr zu emp­feh­len, um die Über­sicht­lich­keit dei­ner Daten zu gewährleisten.

Stan­dard­mä­ßig wer­den die Tabel­len als TabelleX, wobei X für eine fort­lau­fen­de Num­mer steht, benannt. Erstellst du nun meh­re­re Tabel­len (Tabelle1, Tabelle2, …) wirst du ohne sinn­vol­le Benen­nung in For­meln schnell den Über­blick ver­lie­ren.

Tools

PivotTable

Klickst du auf Mit Pivot­Ta­ble zusam­men­fas­sen, so erstellst du aus den Daten ein Pivot­Ta­ble, mit wel­chem du die Daten wei­ter ana­ly­sie­ren kannst.

Der gro­ße Vor­teil der intel­li­gen­ten Tabel­le gegen­über unstruk­tu­rier­ten Daten, ist die auto­ma­ti­sche Erwei­te­rung der Daten­quel­le bei Pivot­Ta­bles. Was mei­ne ich damit?

Erstellst du ein Pivot­Ta­ble auf Basis unstruk­tu­rier­ter Daten und fügst neue Daten hin­zu, so muss die Daten­quel­le des Pivot­Ta­ble manu­ell erwei­tert wer­den (bspw. $A$5:$B$5 -> $A$5:$B$10). Fügst du hin­ge­gen der intel­li­gen­ten Tabel­le neue Daten hin­zu, so erkennt die Pivot­Ta­ble auto­ma­tisch die neu­en Daten (z. B. stand­or­te).

Duplikate entfernen

Die­se Funk­ti­on erkennt die Tabel­le samt Über­schrif­ten und lässt dich so ein­fach Dupli­ka­te aus den Daten entfernen.

In Bereich konvertieren

Du möch­test die Daten wie­der in eine unstruk­tu­rier­te Form brin­gen? Drü­cke In Bereich kon­ver­tie­ren um die intel­li­gen­te Tabel­le aufzulösen.

Datenschnitt

Mit Daten­schnitt ein­fü­gen kannst du für jede belie­bi­ge Spal­te eine visu­el­le Fil­ter­flä­che ein­fü­gen.

Der Daten­schnitt kann an eine belie­bi­ge Stel­le bewegt und for­ma­tiert wer­den. Das macht ihn im Gegen­satz zum her­kömm­li­chen Fil­ter ange­neh­mer für die Nutzer.

Tabellenformatoptionen

Kopfzeile

Mit der Opti­on Kopf­zei­le kannst du die Über­schrif­ten voll­stän­dig deak­ti­vie­ren. Der Sinn dahin­ter erschließt sich mir ehr­lich gesagt nicht, aber viel­leicht kann es wer gebrauchen. 😃

Schaltfläche “Filter”

Schalt­flä­che „Fil­ter“ akti­viert / deak­ti­viert dir die Fil­ter­funk­ti­on der Tabelle.

Ergebniszeile

Die Ergeb­nis­zei­le ist eine genia­le Sache. Damit kannst du dir per Drop­Down für jede Spal­te eine der Standard-Funktionen, wie z. B. Sum­me oder Mit­tel­wert, aus­ge­ben lassen.

So wie die Spal­ten haben auch die Ergeb­nis­se einen fes­ten Namen als Zell­be­zug (z. B. standorte[[#Ergebnisse];[Umsatz]]), auf die du dich bezie­hen kannst.

Die Ergeb­nis­zei­le arbei­tet mit der TEILERGEBNIS-Funktion. Fil­terst du eine Spal­te, so bezieht sich das Ergeb­nis nur auf die gefil­ter­ten Daten. Da das Teil­ergeb­nis mit den Funk­tio­nen 100, 101 … arbei­tet, wer­den auch aus­ge­blen­de­te Zei­len igno­riert.

Erste & letzte Spalte

Akti­vierst du die­se Optio­nen, wer­den die ers­te oder letz­te Spal­te fett her­vor­ge­ho­ben oder je nach Tabel­len­for­mat­vor­la­ge far­big hin­ter­legt.

Gebänderte Zeilen & Spalten

Stan­dard­mä­ßig sind die gebän­der­ten Zei­len in der Tabel­le akti­viert. Das bedeu­tet, dass die Zei­len abwech­seln­de Far­ben (Weiß und von Tabel­len­for­mat­vor­la­ge abhän­gi­ge Far­be) haben.

Gebän­der­te Spal­ten ist das glei­che Prin­zip, nur für die Spal­ten statt Zei­len. Bei­des in Kom­bi­na­ti­on geht natür­lich auch. 😉

Tabellenformatvorlagen

Auf der rech­ten Sei­te der Regis­ter­kar­te sehen wir die Tabel­len­for­mat­vor­la­gen. Hier kannst du aus den Kate­go­rien Hell, Mit­tel & Dun­kel ein Design für die Tabel­le aus­wäh­len oder dir eine eige­ne Vor­la­ge erstellen.

Soll­test du spä­ter dei­ne Mei­nung ändern und bspw. eine ande­re Far­be wol­len, so hat dies kei­ne Aus­wir­kun­gen auf die Daten. Be colorful!

Berechnungen & Formeln -
Hier beginnt die Magie! 🧙🏻‍♂️

Festgelegte Spaltennamen

Wir haben wei­ter oben bereits ken­nen­ge­lernt, dass intel­li­gen­te Tabel­len einen fest­ge­leg­ten Namen (bspw. Tabelle1) haben. Dies gilt eben­so für die Spal­ten; Zei­len haben jedoch einen Ver­weis per @-Zeichen!

Neh­men wir an, du möch­test auf eine kom­plet­te Spal­te der Tabel­le ver­wei­sen. Gehe in eine belie­bi­ge Zel­le außer­halb der Tabel­le und gebe das Gleich­heits­zei­chen (=) zum Schrei­ben einer For­mel ein. Danach kannst du entweder

• in eine Zel­le der gewünsch­ten Spal­te kli­cken und STRG + Leer­tas­te drücken
• an das obe­re Ende der Über­schrift gehen (es erscheint ein schwar­zer Pfeil) und klicken

Die For­mel zeigt dir nun einen Bezug zum Namen der Spal­te, bspw. standorte[Bundesland].

Doch was ist der Vor­teil hier­von? Durch den fes­ten Bezug kannst du in dei­nem Arbeits­blatt Ände­run­gen vor­neh­men, ohne dass die Bezü­ge davon betrof­fen sind.

Beson­ders bei Ver­knüp­fun­gen auf exter­ne Datei­en ist das ein rie­si­ger Plus­punkt. Hier der Vergleich:

1. Du ver­knüpfst eine exter­ne Datei auf die Arbeits­da­tei ohne Bezug auf die Tabel­le, bspw. Blatt1!$A:$A. Ver­schiebst du die Daten nun in eine ande­re Spal­te und hast die exter­ne Datei nicht geöff­net, so erhältst du beim spä­te­ren Öff­nen der exter­nen Datei einen Feh­ler oder kei­nen Wert. Die exter­ne Datei bezieht sich wei­ter­hin auf die Spal­te A.
2. Dies­mal ver­knüpfst du die exter­ne Datei auf die Arbeits­da­tei mit Bezug auf die Tabel­le, bspw. standorte[Umsatz]. In die­sem Fall kannst du die Tabel­le belie­big ver­schie­ben, da sich die exter­ne Datei auf die Tabel­len­spal­te bezieht. Ob die Tabel­le nun in Spal­te A:C oder in E:G ist, das ist egal. Die Tabel­le ist unab­hän­gig von den Zellkoordinaten.

Ein wei­te­rer Vor­teil ist die bes­se­re Les­bar­keit dei­ner For­meln. Auch hier ein klei­nes Bei­spiel 😊
Wel­che Vari­an­te lässt sich bes­ser lesen?

1. Ohne Tabel­len­be­zug
   =SUMMEWENNS(
   $G:$G;
   $C:$C;“Nordrhein-Westfalen”;
   $F:$F;“Klein”)
2. Mit Tabel­len­be­zug
   =SUMMEWENNS(
   standorte[Umsatz];
   standorte[Bundesland];“Nordrhein-Westfalen”;
   standorte[Größe];“Klein”)

Auch wenn die For­mel in einem ande­ren Blatt oder einer ande­ren Datei wäre, wüss­test du mit dem Tabel­len­be­zug viel schnel­ler, wie sich der Wert ergibt! Und unser Bei­spiel ist noch eine eher kur­ze Formel. 😉

Das @-Zeichen im Namen

Mit dem @-Zeichen unter­schei­det die Tabel­le zwi­schen dem Bezug auf eine gan­ze Spal­te (standorte[Umsatz]) oder der ein­zel­nen Zei­le der Spal­te (stand­or­te[@Umsatz]).

Der Bezug auf die gan­ze Spal­te (standorte[Umsatz]) kann von über­all aus erfol­gen, beim Bezug auf die ein­zel­ne Zei­le ist das jedoch anders!

Dies funk­tio­niert nur, wenn du dich inner­halb der glei­chen Zei­le auf eine Tabel­len­zel­le beziehst. Bei­spiel:
Die ers­te Zei­le in der Tabel­le ist die Zei­le 3, du möch­test auf die Steu­er in Zei­le 3 verweisen.

• Ver­weist du außer­halb der Tabel­le in Zei­le 3 auf Zei­le 3 (Steuer-Spalte), so erhältst du den Bezug standorte[@Steuer]. (@ wegen dem Zeilenbezug!)
• Ver­weist du außer­halb der Tabel­le in Zei­le 4 (oder Zei­le 2, 5, 6, …) auf Zei­le 3 (Steuer-Spalte), so erhältst du den Bezug H3 – also auf die Koor­di­na­te der Zel­le.

Wür­dest du die For­mel in dar­un­ter­lie­gen­den Zel­len wei­ter­füh­ren, so wäre im ers­ten Fall der Bezug immer standorte[@Steuer], im zwei­ten Fall wäre der Bezug fort­lau­fend; also H3, H4, H5 …

IntelliSense – die smarte Hilfe beim Schreiben von Formeln ✍🏻

Micro­soft bie­tet in vie­len Ihrer Pro­gram­me das Tool Intel­li­Sen­se. Viel­leicht sagt dir der Begriff nichts, aber du hast es bestimmt schon gese­hen. Gibst du eine For­mel wie bspw. =SUMME ein, so hast du Intel­li­Sen­se schon kennengelernt! 😊

Intel­li­Sen­se sind die Vor­schlä­ge zur Wei­ter­füh­rung dei­ner For­mel, die du bei der Ein­ga­be erhältst. Gibst du bspw. =SUM ein, so spuckt dir Intel­li­Sen­se gleich wei­ter­füh­ren­de Vor­schlä­ge wie SUMME, SUMMEWENN, … aus. Per Dop­pel­klick oder TAB-Taste kannst du einen Ein­trag auswählen.

Kom­bi­nie­ren wir Intel­li­Sen­se mit intel­li­gen­ten Tabel­len, so wird die Ein­ga­be von For­meln kin­der­leicht. Neh­men wir als Bei­spiel die SUMMEWENNS-Formel, wel­che wir oben geschrie­ben haben. Hier noch­mal die For­mel: =SUMMEWENNS(standorte[Umsatz];standorte[Bundesland];“Nordrhein-Westfalen”;standorte[Größe];“Klein”)

Fan­ge mit der Ein­ga­be von =SUMMEWENNS( an und gebe nach der Klam­mer manu­ell stand­or­te ein. Aha! Dir wird sofort die intel­li­gen­te Tabel­le als Aus­wahl vor­ge­schla­gen. Mit der TAB-Taste kannst du das Ergeb­nis sofort vervollständigen.

Automatische Erweiterung von Formeln

Wie schaut es nun mit For­meln aus, die wir in der Tabel­le schrei­ben? Auch hier bie­tet die intel­li­gen­te Tabel­le groß­ar­ti­ge Vorteile.

For­meln inner­halb der Tabel­le wer­den auto­ma­tisch auf alle Zei­len der Spal­te erwei­tert! Änderst du die For­mel, so wird die Ände­rung für die gesam­te Spal­te über­nom­men. Eben­so erhal­ten neue Zei­len auto­ma­tisch die Formel.

Neh­men wir an, wir möch­ten für unse­re Stand­or­te die Steu­er aus­rech­nen, die für den Umsatz fäl­lig wird. Wir erstel­len eine neue Spal­te (Name Steu­er) und geben in eine belie­bi­ge Zel­le der Spal­te die For­mel ein:
= [@Umsatz] * 19%

Mit Enter bestä­ti­gen und Zack! Die For­mel wur­de für alle Ein­trä­ge ange­wen­det! Kein ner­vi­ges, manu­el­les Zie­hen der For­meln. Und das Bes­te: Du kannst die For­mel in jeder Zei­le edi­tie­ren, da die Ände­rung für alle Zei­len ange­wen­det wird!

Automatische Erweiterung von Formatierungen

Bei For­ma­tie­run­gen (z. B. For­ma­tie­rung [Umsatz]-Spal­te als Wäh­rung -> 0,00 €) ist es etwas anders. Eine Spal­te muss ein­ma­lig for­ma­tiert wer­den, erst dann wird das For­mat von neu­en Zei­len eben­falls übernommen.

Bezie­hen wir uns dar­auf­hin auf die­se Spal­te, so nimmt die ande­re Spal­te das For­mat eben­so an. Bei­spiel wäre das Hin­zu­fü­gen der [Steu­er]-Spal­te. Sobald wir die Steu­er berech­nen, nimmt die Spal­te eben­falls das For­mat Wäh­rung an.

Was gibt es bei intelligenten Tabellen zu beachten? 🤔

Hilfe! Ich brauche eine manuelle Änderung!

Sicher­lich wird es vor­kom­men, dass du für eine ein­zel­ne Zel­le in einer berech­ne­ten Spal­te einen fes­ten Wert oder eine ande­re For­mel ein­ge­ben möchtest.

Das ist defi­ni­tiv nicht zu emp­feh­len. Durch einen manu­el­len Ein­trag wird die auto­ma­ti­sche Ände­rung aller For­meln der Spal­te deak­ti­viert.

Ein Work­around ist die Ver­wen­dung der WENN-Funktion, indem du dei­ne manu­el­le Ände­rung an eine Bedin­gung knüpfst.

Bei­spiel:
Der Stand­ort L001 & L014 haben einen Steu­er­ab­zug von 5.000 €. Statt bei die­sen Stand­or­ten in der [Steu­er]-Spal­te manu­ell –5.000 € zu rech­nen, nut­zen wir die WENN-Funktion.

=WENN(
ODER([@StandortID] = “L001”;[@StandortID] = “L014”);
[@Umsatz] * 19% – 5000;
[@Umsatz] * 19%)

Nun möch­test du den­noch für eine Zel­le der Spal­te eine manu­el­le Ände­rung durch­füh­ren. Bei Ein­ga­be merkst du jedoch, dass die Ände­rung auto­ma­tisch für alle Zei­len über­nom­men wird!?

Der Trick ist, dass du nach Ein­ga­be der For­mel und Bestä­ti­gung mit Enter auf Rück­gän­gig (STRG + Z) drückst. Dadurch setzt Excel die auto­ma­ti­sche Erwei­te­rung zurück, übrig bleibt die Ände­rung nur in der Zei­le, in der du die Ein­ga­be getä­tigt hast.

Die automatische Erweiterung meiner Formel funktioniert nicht 🙁

Es kann vor­kom­men, dass die auto­ma­ti­sche Erwei­te­rung bei dir deak­ti­viert ist. Kam mir auch schon vor. Man kennt’s, Excel und sei­ne Macken. 😜

Du fin­dest die Ein­stel­lung unter Datei -> Optio­nen -> Doku­ment­prü­fung -> AutoKorrektur-Optionen -> Auto­For­mat wäh­rend der Eingabe

Wich­tig sind die Optionen

• Neue Zei­len und Spal­ten in die Tabel­le ein­schlie­ßen
  (das ist die auto­ma­ti­sche Erwei­te­rung dei­ner Tabel­le bei neu­en Daten) und
• For­meln in Tabel­len fül­len, um berech­ne­te Spal­ten zu erstel­len
  (das ist die auto­ma­ti­sche Formelerweiterung).

Kann ich Tabellen formatieren? 🤔

Ja! 😁 Neben den Tabel­len­for­mat­vor­la­gen kannst du wie bei unstruk­tu­rier­ten Daten dei­nen künst­le­ri­schen Fähig­kei­ten frei­en Lauf las­sen. Tex­te kön­nen fett gestal­tet, Zel­len far­big hin­ter­legt werden.

Manueller Umbruch in Überschriften

Dei­ne Spal­ten­über­schrift ist ellen­lang und du möch­test die­se gern in zwei Zei­len anzei­gen. Vie­le neh­men dafür den Trick ALT + Enter her, wel­cher einen Text­um­bruch ein­fügt.

Grund­sätz­lich ist dies kein Welt­un­ter­gang, aber auch nicht zu emp­feh­len. Grund hier­für ist, dass die­ser Umbruch auch in For­meln ange­zeigt wird. Die Funk­ti­on ist dadurch nicht ein­ge­schränkt, jedoch erschwert es die Les­bar­keit der Formel.

Eine bes­se­re Lösung ist die Nut­zung des visu­el­len Text­um­bruchs des Excel Benut­zer­ober­flä­che. Die­sen fin­dest du in der Regis­ter­kar­te Start in der Kate­go­rie Aus­rich­tung.

Fixierte Überschrift beim Scrollen

Abschlie­ßend noch ein klei­ner Tipp. Bist du mit dei­nem Cur­sor aktiv in der intel­li­gen­ten Tabel­le und scrollst so weit nach unten, dass die Über­schrif­ten nicht mehr sicht­bar sind, dann wan­dern die­se in die Spal­ten­be­zeich­nung.

Heißt: Statt D, E, F, … zei­gen dir die Spal­ten die Tabel­len­über­schrift (z. B. Stand­ort­ID oder Bun­des­land) an. Nice! 😎

Fazit 💡

Intel­li­gen­te Tabel­len bie­ten vie­le nütz­li­che Funk­tio­nen, um dir das Excel-Leben leich­ter zu gestal­ten. Beson­ders Fea­tures wie die auto­ma­ti­sche For­mel­er­wei­te­rung oder fest­ge­leg­te Spal­ten­na­men erleich­tern vieles.

Sofern mög­lich soll­te man immer eine intel­li­gen­te Tabel­le nut­zen, um sei­ne Roh­da­ten in eine schö­ne und leicht ver­ar­beit­ba­re Form zu bringen.