Tipps und Tricks rund um Excel
| A | B | C | D | E | F | G | H | I/J | K |
| L | M | N | O | P/Q | R | S | T | U-W | X-Z |
Formatierte Tabelle
- Grundlagen
- Rechnen in formatierten Tabellen
- In Listen zählen
- Namen in formatierten Tabellen
- Dynamische Diagramme
- Bezüge mit Namen
In einer Arbeitsmappe gibt es pro Monat ein Tabellenblatt mit einer Tabelle. Die Monatstabellen sind unterschiedlich groß, die Anzahl der Städte schwankt. Darum steht das Ergebnis mal in Zeile 11, mal in 12, mal in 13.
| Januar | Februar | März |
|
|
|
In der Zusammenstellung möchte ich bequem auf alle Tabellen zugreifen, auch auf künftige Monate. Dazu möchte ich eine einzige Formel schreiben und kopieren.
In der ersten Zeile stehen die Monatsnamen, darunter sollen die Ergebnisse aufgeführt werden.
|
|
Es gibt zwei Lösungen:
- Mit Namen
- Mit formatierten Tabellen
Mit Namen
Die Ergebniszellen werden benannt mit "Januar", "Februar" etc.
Klicken Sie in die Ergebniszelle vom Januar und wählen Sie "Formeln / Namen definieren / Namen definieren". Benennen Sie die Zelle als "Januar" und legen Sie als Bereich "Arbeitsmappe" fest. Mit OK bestätigen. |
|
Dass die Namensvergabe geklappt hat, sehen Sie in der Bearbeitsleiste im Namenfeld. Wenn Sie eine Ergebniszelle anklicken, erscheint dort der von Ihnen vergebene Name. Zeigen (MP4, ohne Ton, 30 sec)
|
|
Fahren Sie so fort mit allen anderen Tabellenblättern. Zum Schluss muss jede Ergebniszelle den Monatsnamen als Namen tragen.
Auf die Namen kann einfach Bezug genommen werden mit =JANUAR oder =FEBRUAR. Der Inhalt der so benannten Zelle wird ausgegeben. Aber ich möchte nicht jeden für jeden Monat eine einzelne Formel schreiben. Darum weiter:
Im Blatt "Zusammenstellung" schreiben Sie die Monatsnamen als Beschriftung der Spalten - achten Sie darauf, dass die Schreibweise hier identisch ist mit der Schreibweise der Namen in den Monatsblättern!
Die erste Formel für Januar lautet: =INDIREKT(A1) |
|
INDIREKT übernimmt aus Zelle A1 die Bezeichnung "Januar" und wandelt sie in einen Bezug um. Der Inhalt der Formel lautet also =JANUAR. Da der Name "Januar" für die gesamte Arbeitsmappe definiert wurde, ist er eindeutig.
Füllen Sie die Formel nach rechts über alle Monate hinweg aus. Der Zellbezug ist relativ, wird also zu B1 und C1 - die jeweils den folgenden Monatsnamen liefern.
Mit einer formatierten Tabelle
Grundlage ist die formatierte Tabelle. Jede Tabelle ist als formatierte Tabelle angelegt, das Ergebnis wird mit "Tabellentools / Entwurf / Ergebniszeile" erstellt. Jede Tabelle erhält einen Namen (ACHTUNG! Ich meine den Tabellenblatt-Namen, sondern den Namen der Tabelle!).
Am Beispiel der Februar-Tabelle zeige ich, was ich meine. Die aktive Zelle steht in der formatierten Tabelle für den Februar. Über "Tabellentools / Entwurf" tragen Sie ganz links außen unter "Tabellennamen" den Namen ein: Februar. Das wird für die anderen Tabellen auch gemacht. Wichtig ist, dass die Beschriftung in der Zusammenstellung identisch ist mit dem Tabellennamen!
|
|
Jetzt kann ich in der Zusammenstellung eine Formel schreiben. Ich beginne unter dem Januar in Zelle A2. Die Formel lautet
=INDIREKT(A1&"[[#Ergebnisse];[Umsatz]]")
Die Funktion fülle nach rechts für die Monate Februar und März aus.
Zur Erklärung:
- INDIREKT baut einen Zellbezug aus Textbestandteilen zusammen
- A1 beinhaltet den Monatsnamen, hier den Januar
- [[#Ergebnisse];[Umsatz]] ist der Bezug auf die Ergebniszeile und die Summe für den Umsatz in der formatierten Tabelle
- Das & verbindet den Namen "Januar" mit [[#Ergebnisse];[Umsatz]]
- Alles wird in Anführungszeichen gepackt, weil es Text ist
- Gelesen wird die Formel als:
Nimm den Monatsnamen aus der Zelle darüber, hänge [[#Ergebnisse];[Umsatz]] an und gib den Inhalt dieser Zelle aus.
09.02.2013