Excel, strukturierte Tabelle - Excel Hands on - Blog - HSC-Consulting, Controlling, Excel & Coaching

Direkt zum Seiteninhalt

Hauptmenü:

Excel, strukturierte Tabelle

Herausgegeben von in Excel Standard ·
Tags: ExcelstrukturierteTabellen
Auswertungen mit strukturierten Tabellen
Der Umgang mit strukturierten Tabellen ist im Hinblick auf die Formelsyntax etwas anders als in herkömmlichen Excel-Tabellenblättern. Zunächst muss eine strukturierte Tabelle erst einmal eingerichtet werden. Für manche Aufgaben ist sie eine Option für andere hingegen ein Muss. Ein Muss, beipielsweise bei PivotTabellen oder dem Aufbau von Datenmodellen in PowerPivot.
Excel-Liste als Tabelle formatieren - Namen in einem Tabellenblatt
 
Tabellen, die über START/als Tabelle formatieren (oder mit der Tastenkombination (Strg)+(T) ) eingerichtet wurden, sind nicht einfach nur bunt. Sie bieten viele Möglichkeiten, wie Automatisches Filtern, Ergebnis Zeilen, Summen etc.
 
Definieren einer strukturierten Tabelle
 
     
  • Positionieren Sie den Cursor in den Daten [1] und drücken die      Tasten (Strg)+(T) [2].
  •  
  • Geben Sie der »intelligenten Tabelle« über den Befehl      TABELLENTOOLS/ENTWURF den Namen tbl_Mengen [3].
  •  
  • Sie erhalten eine formatierte Tabelle [4], (vgl. Abbildung      1.1).
 
Abbildung 1.1: Erstellen einer »strukturierten Tabelle« (Ausschnitt der Zieltabelle)
 


 
Berechnungen in einer intelligenten Tabelle vornehmen
 
Jede Berechnung kann jetzt mit sprechenden Namen tbl_Mengen erfolgen. Zuerst kommt der Name der Tabelle [1] (er wird schon vorgeschlagen, wenn Sie die ersten Buchstaben tippen), dann eine eckige Klammer:
 
 
=SUMME(tbl_Mengen[   [2]
 
 
Abbildung 1.2: Auswahl der Funktion und der Namen mittels der sensitiven Dateneingabe

 
Excel bietet jetzt alle Spaltennamen an. Im Beispiel ist es Monat, Deutschland usw.
 
Mit =Summe(tbl_mengen[Deutschland]) [2], [3] summiert alle Einträge in der Spalte “Deutschland”[4].
 
 
Mit dem Summensymbol [4] lassen sich einmalig auch andere Anordnungen der Summe auswählen.
 
 
Weitere Berechnungen zur Datenauswertung
 
Strukturierte Tabellen bieten zahlreiche Berechnungswege an. In der folgenden Abbildung sehen Sie einige Beispiele.

 
Abbildung 1.3: Beispielhafte Berechnungen mit den Funktionen der »strukturierten Tabelle«
 

Zusammenfassung der Formeln
 
[1]  Summenbildung mit Feldnamen
 
[2]  #Daten berücksichtigt alle Werte aus dem Datenbereich, ohne Kopfzeile und Ergebniszeile
 
[3]  #Kopfzeile berücksichtigt alle Einträge der Kopfzeile. Deshalb wird in dem Beispiel mit -1 das Feld Monat wieder herausgerechnet
 
[4]  #Alle berücksichtigt alle Werte einschließlich der Feldnamen und Ergebniszeile
 
     =Anzahl2(tbl_Mengen([[#Alle];[Monat]]) liefert als Ergebnis 13 hingegen
 
[5]  =ANZAHL2(tbl_Mengen[Monat]) liefert als Ergebnis 12
 
[6]  =ANZAHL2(tbl_Mengen[[#Daten];[Deutschland]]) liefert ebenfalls das Ergebnis 12. (Beachte: doppelte [[ )
 
     Oder einfacher =Summe(tbl_mengen[Deutschland]) summiert alle Einträge in der Spalte “Deutschland”.

 
In Kombination mit “Tabellenname[[#Zusatz];[Spaltenname]]” gibt folgende Zusätze:
 
     
  • #Alle - Alle Einträge der Spalte inklusive Spaltenüberschrift      und Ergebniszelle
  •  
  • #Daten - Nur die Daten - ohne Spaltenüberschrift und ohne      Ergebniszelle
  •  
  • #Kopfzeilen - Nur den Eintrag der Kopfzeile - das wäre das Feld Betrag bei =tbl_Mengen[[#Kopfzeile];[Deutschland]]
  •  
  • #Ergebnisse - Die Ergebniszelle dieser Spalte (sofern eine      Ergebniszeile aktiviert wurde)
  •  
  • @ (diese Zeile) - Den Inhalt der Zelle aus der gleichen Zeile - wenn ich      in H5 schreibe  =tbl_Mengen[@Italien] erhalte ich als      Ergebnis den Wert 480, der in der gleichen Zeile wie H5      in der      Spalte Italien steht.
 
 
Beispiel: Mit =tbl_Mengen[@Italien] erhalte ich den Wert 480 aus der Spalte Italien (vgl. Abbildung 1.4)
 
 
Abbildung 1.4: Mit der Funktion MAX() den höchsten Wert in der Tabelle ermitteln



 
Teilbereiche der Tabelle summieren
 
Es ist auf einfachem Weg möglich auch Teile einer Zeile zu summieren, beispielsweise von Deutschland bis Schweiz.
 
Dann geben Sie auf der betreffenden Monatszeile die folgende Formel ein:
 
=SUMME(tbl_Mengen[@[Deutschland]:[Schweiz]])
 
Als Ergebnis der Zeile Aug erhalten Sie den Wert 1010.

 
Abbildung 1.5: Eingabe der Funktion Summe in der Zeile September


 
Das @Zeichen vor Deutschland:Schweiz führt dazu, dass exakt die Zeile ausgewertet wird, in der die Formel eingetragen wurde.
 
Die Formel in Zeile 12: =SUMME(tbl_Mengen[@[Deutschland]:[Schweiz]])
 
 
Weitere Berechnungen und deren Formeln
 
Summe der Menge für Italien    =SUMME(tbl_Mengen[Italien])
 
Summe der Gesamtmenge        =SUMME(tbl_Mengen[#Daten])
 
Anzahl der Länder                     =ANZAHL2(tbl_Mengen[#Kopfzeilen])-1
 
oder                                        =ANZAHL2(tbl_Mengen[[#Kopfzeilen];[Deutschland]:[Italien]])
 
Anzahl der Zeilen in der Monatsspalte    =ANZAHL2(tbl_Mengen[[#Alle];[Monat]])
 
Anzahl der Monatszeilen                       =ANZAHL2(tbl_Mengen[Monat])
 
Anzahl der Dateneinträge    
 
im Feld Deutschland:                            =ANZAHL2(tbl_Mengen[[#Daten];[Deutschland]])
 
 
Feldnamen an anderer Stelle anzeigen
 
Die Feldbezeichnungen der Tabelle sollen sich an anderer Position im Tabellenblatt wiederholen und bei Änderungen in der Ursprungstabelle angepasst werden.
 
 
Markieren Sie im Zielbereich so viele Zellen wie die Quelltabelle (Zeile 4) (vgl. Abbildung 1.6) aufweist und geben in der Zielzelle die Formel =tbl_Mengen[#Kopfzeilen] ein. Bestätigen Sie die Eingabe mit der Tastenkombination SHIFT+STRG+RETURN
 
Die Anzeige der Formel in der Bearbeitungszeile mit geschweiften Klammern bedeutet, dass die eine Matrixformel handelt, die den zusammenhängenden Bereich als Array auswertet.
 
 
Mehrere Funktionen kombinieren
 
Sie möchten den Namen des Monats ausgeben, in dem der Maximalwert der Tabelle auftritt.
 
(Es wird immer davon ausgegangen, dass es nur ein Maximum in der gesamten Matrix gibt!).
 
 
Abbildung 1.6: Rückgabe des Monats mit dem Maximumwert
 
Die Formel:
 
 
=BEREICH.VERSCHIEBEN(B4;
 
VERGLEICH(MAX(tbl_Mengen[#Daten]);
 
tbl_Mengen[Deutschland];
 
0);
 
0)
 
 
Sie möchten den Namen des Landes ausgeben, in dem der Maximalwert der Tabelle auftritt.
 
(Es wird immer davon ausgegangen, dass es nur ein Maximum in der gesamten Matrix gibt!)
 
 
Abbildung 1.7: Rückgabe des Spaltennamens mit dem Maximumwert

 
 
Die Formel:
 
=INDEX(tbl_Mengen[#Kopfzeilen];
 
1;
 
SUMMENPRODUKT((tbl_Mengen[[Deutschland]:[Italien]]=
 
MAX(tbl_Mengen[#Daten]))*
 
SPALTE(tbl_Mengen[[#Kopfzeilen];[Monat]])))




       



Zurück zum Seiteninhalt | Zurück zum Hauptmenü