Übersichtlichere Formeln mit strukturierten Verweisen ab Excel 2007

Eine der interessanten Neuerungen in Excel 2007 sind die strukturierten Verweise, mit denen Sie sich in Formeln auf eine Tabelle oder auf einen Ausschnitt einer Tabelle beziehen können. Bei strukturierten Verweisen werden die Zellbezüge automatisch angepasst, wenn sich die Zeilen- oder Spaltenanzahl der Tabelle ändert.

An dem folgenden Beispiel soll die Anwendung der strukturierten Verweise verdeutlicht werden:

In einer neuen Arbeitsmappe sind die folgenden Tagesumsätze der letzten Woche für 3 Produktgruppen erfasst worden:

Bild1

Um die strukturierten Verweise nutzen zu können, müssen Sie zunächst den Bereich mit den eingegebenen Daten in eine Tabelle umwandeln.

  1. Markieren Sie eine Zelle in dem Bereich mit den Umsatzzahlen.

  2. Klicken Sie in der Multifunktionsleiste auf die Registerkarte Einfügen.

  3. Klicken Sie auf die Schaltfläche Tabelle in der Gruppe Tabellen.

  4. Übernehmen Sie die vorgeschlagenen Einträge in dem Dialogbild Tabelle erstellen und klicken Sie auf die Schaltfläche OK.

Excel gibt dieser Tabelle automatisch den Namen Tabelle1, nicht zu verwechseln mit dem Namen des Tabellenblatts Tabelle1.

Es empfiehlt sich, der Tabelle einen anderen Namen zu geben. Gehen Sie bitte folgendermaßen vor:

  1. Markieren Sie eine Zelle in der Tabelle mit den Umsatzzahlen. Es wird unter Tabellentools automatisch eine neue Registerkarte mit dem Namen Entwurf eingeblendet.

  2. Klicken Sie auf die Registerkarte Entwurf.

  3. In der Gruppe Eigenschaften befindet sich ein Textfeld, in dem der aktuelle Name der Tabelle erscheint.

  4. Ändern Sie den Namen beispielsweise auf Wochenumsatz ab und bestätigen Sie Ihre Änderungen mit der Enter-Taste.

Im Folgenden sollen die Umsatzzahlen der einzelnen Wochentage und der Produkte ermittelt werden.

Um die Gesamtumsätze pro Wochentag zu ermitteln, gehen Sie bitte folgendermaßen vor:

  1. Markieren Sie eine Zelle in der Tabelle mit den Umsatzzahlen.

  2. Klicken Sie auf die Registerkarte Entwurf unter dem Eintrag Tabellentools.

  3. Aktivieren Sie in der Gruppe Optionen für Tabellenformat den Eintrag Ergebniszeile. Am Ende der Tabelle wird eine neue Zeile eingeblendet. Standardmäßig werden nur für die letzte Spalte die Zahlen aufaddiert.

  4. Kopieren Sie die Formel von Freitag nach links auf die anderen Wochentage.

In der Zelle mit dem Gesamtumsatz für den Freitag finden Sie die folgende Formel:

 =TEILERGEBNIS(109;[Freitag])

Mit der Funktion Teilergebnis und der Zahl 109 werden alle Zahlen in der Spalte Freitag aufaddiert, wobei ausgeblendete Zeilen nicht berücksichtigt werden. Der Bezug [Freitag] stellt einen strukturierten Verweis dar. Bei strukturierten Verweisen muss der Bezeichner für Spalten, in diesem Fall Freitag, in eckige Klammern gesetzt werden. Die Formel müssen Sie nicht anpassen, wenn sich die Anzahl der Zeilen der Tabelle durch manuelle Eingaben oder Aktualisierungen von externen Daten ändert.

Die Gesamtumsätze je Produktgruppe können Sie berechnen, indem Sie der Tabelle eine berechnete Spalte hinzufügen:

  1. Tragen Sie in der rechten Zelle neben der letzten Spaltenüberschrift den Text Gesamt ein.

  2. Aktivieren Sie die erste Zelle unterhalb von Gesamt. Die Spalte Gesamt wird automatisch zur Tabelle Wochenumsatz hinzugefügt.

  3. Klicken Sie auf die Registerkarte Start in der Gruppe Bearbeiten auf das Summensymbol S.

  4. Bestätigen Sie die Formel mit der Enter-Taste. Es werden die Gesamtumsätze aller Produktgruppen ermittelt, ohne dass Sie die Formel kopieren müssen.

Die Formel für den Gesamtumsatz der Produktgruppe Hardware enthält wiederum einen strukturierten Verweis:

In Excel 2010 lautet die Formel:

=SUMME(Wochenumsatz[@[Montag]:[Freitag]])

In Excel 2007 lautet die Formel:

=SUMME(Wochenumsatz[[#Diese Zeile];[Montag]:[Freitag]])

Die Formel können Sie folgendermaßen lesen:

Addiere in der Tabelle Wochenumsatz alle Zahlen, die sich in derselben Zeile befinden wie die Zelle, in der das Ergebnis erscheinen soll, wobei nur die Spalten von Montag bis Freitag berücksichtigt werden.

Die Tabelle sieht jetzt folgendermaßen aus:

Bild2

Der Ausdruck [#Diese Zeile] bzw. das @-Zeichen ist einer der vordefinierten Bezeichner, die Sie für strukturierte Verweise verwenden können. Folgende vordefinierte Bezeichner können Sie in Formeln angeben:

Name

Bedeutung

[#Alle]

Gesamte Tabelle

[#Kopfzeilen]

Nur die Spaltenüberschriften der Tabelle

[#Daten]

Nur die Daten ohne Spaltenüberschriften und Ergebniszeile

[#Ergebnisse]

Nur die Ergebniszeile in der letzten Zeile der Tabelle

[#Diese Zeile]

Excel 2007: Nur die aktuelle Tabellenzeile, bezogen auf die aktuelle Zelle mit der Formel

@

Excel 2010 und Excel 2013: Nur die aktuelle Tabellenzeile, bezogen auf die aktuelle Zelle mit der Formel

Sie können strukturierte Verweise auch außerhalb der Tabelle verwenden, wobei Sie in der Formel einen vollständig qualifizierten, strukturierten Verweis verwenden müssen, d.h. Sie müssen den Namen der Tabelle angeben. Im Folgenden einige Formeln, die sich auf die Ergebniszeile in der Tabelle Wochenumsatz beziehen:

  • Gesamtumsatz in der Woche: =Wochenumsatz[[#Ergebnisse];[Gesamt]]

  • Gesamtumsatz am Dienstag: =Wochenumsatz[[#Ergebnisse];[Dienstag]]

  • Durchschnittlicher Tagesumsatz: =MITTELWERT(Wochenumsatz[[#Ergebnisse];[Montag]:[Freitag]])

  • Umsatz von Montag bis Mittwoch: =SUMME(Wochenumsatz[[#Ergebnisse];[Montag]:[Mittwoch]])

  • Umsatz vom Montag, Mittwoch und Freitag: =SUMME(Wochenumsatz[[#Ergebnisse];[Montag]];Wochenumsatz[[#Ergebnisse];[Mittwoch]];Wochenumsatz[[#Ergebnisse];[Freitag]])

Formeln, die sich auf die Ergebniszeile einer Tabelle beziehen, berücksichtigen angewandte Filter. Falls die Ergebniszeile nicht eingeblendet ist, erscheint ein Bezugsfehler.

Die folgenden Formeln zeigen Werte an, unabhängig davon, ob die Ergebniszeile eingeblendet ist. Angewandte Filter werden dabei nicht berücksichtigt.

  • Gesamtumsatz: =SUMME(Wochenumsatz[Gesamt])

  • Umsatz von Montag bis Mittwoch: =SUMME(Wochenumsatz[Montag]:Wochenumsatz[Mittwoch])

  • Umsatz von Montag, Mittwoch und Freitag:
    =SUMME(Wochenumsatz[Montag];Wochenumsatz[Mittwoch];Wochenumsatz[Freitag])

  • Umsatz von Mittwoch und Donnerstag als gemeinsame Schnittmenge der beiden Zeiträume: =SUMME(Wochenumsatz[[Montag]:[Donnerstag]] Wochenumsatz[[Mittwoch]:[Freitag]])

  • Umsatz mit Software: =SUMMEWENN(Wochenumsatz[Produktgruppe];”Software”;Wochenumsatz[Gesamt])

Hinweis

Wenn Sie sich bei den strukturierten Verweisen auf eine Tabelle in einer anderen Arbeitsmappe beziehen, wird ein Bezugsfehler angezeigt, solange die Arbeitsmappe mit der Tabelle nicht geöffnet worden ist.
Im Gegensatz zu der Schreibweise mit Zellbezügen auf externe Arbeitsmappen in der Form ‘Laufwerk:Ordner[Arbeitsmappenname]Tabellenblattname’!Zellbezug müssen Sie bei
strukturierten Verweisen auf Tabellen in externen Arbeitsmappen die Arbeitsmappe selbst öffnen. Klicken Sie dazu im Menüband in der Registerkarte Daten in der Gruppe Verbindungen
auf die Schaltfläche Verknüpfungen bearbeiten. Markieren Sie die Arbeitsmappe, die die Tabelle enthält, auf die Sie sich beziehen, und klicken Sie auf die Schaltfläche Quelle öffnen.

Michael Richter

Michael Richter

Autor

Ich unterstütze unsere Kunden in erster Linie durch Programmierung von Datenbankanwendungen und Zusatzmodulen von Microsoft Office. In Seminaren und Tipps auf unserer Blogseite gebe ich mein Wissen weiter.

4 Kommentare

  1. einstieg

    Antworten
  2. toller Beitrag, kaum woanders so ausführlich

    Antworten
  3. Excel-Wissen erweitert

    Antworten
  4. Ich habe nach langem Grübeln die Verwendung strukturierten Verweisen endlich verstanden. Damit sind mir verschiedenen Dinge in vorhandenen Excel-Tabellen klar geworden. Durch ein besseres Verständnis für die Sache hat man automatisch weniger Stress 🙂 Danke für die sehr gute Erklärung

    Antworten

Einen Kommentar abschicken

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

Pin It on Pinterest

Share This