Dynamische Bereiche in Excel: Wann OFFSET und wann eine Tabelle?
Halten Sie Summen und Dropdownlisten beim Hinzufügen von Zeilen aktuell. Lernen Sie OFFSET und COUNTA, den Effekt fehlender IDs und die Einrichtung einer Excel-Tabelle kennen.
Sie ergänzen einen Artikel in Excel, aber Summe und Dropdownliste bleiben unverändert. Zeigt der Bezug weiter auf A2:A10, liegt die neue elfte Zeile außerhalb. Mit einem dynamischen Bereich bestimmt Excel das Ende der Liste selbst, sodass Formeln bei Änderungen nicht angepasst werden müssen.
Höhe und Breite mit OFFSET bestimmen
Bei einer zusammenhängenden Liste können Sie die Überschriften und Artikel-IDs zählen, um OFFSET die Höhe und Breite vorzugeben. Legen Sie das Beispiel auf Sheet1 an und geben Sie die Summenformel auf einem anderen Blatt ein. Übernehmen Sie die japanischen Überschriften genau, damit die strukturierten Verweise weiter unten funktionieren.
| Zelle | Spalte A | Spalte B | Spalte C |
|---|---|---|---|
| Zeile 1 | 商品ID (Artikel-ID) |
商品名 (Artikelname) |
数量 (Menge) |
| Zeile 2 | A01 | Stift | 4 |
| Zeile 3 | A02 | Notizbuch | 6 |
=SUM(OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A$1:$A$1000),COUNTA(Sheet1!$A$1:$Z$1)))
Beginnen Sie ohne Versatz bei A1. Die Zahl der Werte in Spalte A bestimmt die Höhe, die Zahl der Überschriften in Zeile 1 die Breite. Das Beispiel ergibt A1:C3 mit drei Zeilen und drei Spalten und der Summe 10. Ergänzen Sie in Zeile 4 A03, Lineal und 2. Der Bereich wächst auf A1:C4 und die Summe auf 12. Die IDs sind Text, und die gezählten Bereiche enthalten keine fremden Daten. Microsoft-Referenz zu OFFSET
Löschen Sie nur die ID in A3. In Spalte A zählen nun drei Zellen: A1 mit der Überschrift und die IDs in A2 und A4. OFFSET gibt daher die ersten drei Zeilen zurück, A1:C3. Die Menge 6 aus Zeile 3 bleibt enthalten, die Menge 2 aus Zeile 4 fällt heraus. Die Summe ist wieder 10.
Eine leere Überschrift kann die letzten Spalten aus dem Bereich verdrängen. COUNTA zählt Zellen mit Formeln, die eine leere Zeichenfolge ("") liefern. Halten Sie Überschriften und IDs in einer bestehenden Mappe lückenlos und prüfen Sie die Obergrenze und leere Zellen. Eine fehlende Menge ändert die Höhe nicht, wenn die ID vorhanden ist. Microsoft-Referenz zu COUNTA
Wenn die Überschriften in Zeile 2 beginnen
Steht in Zeile 1 der Berichtstitel, beginnt die Liste bei A2. Zählen Sie für Höhe und Breite ab Zeile 2, damit der Titel außen vor bleibt. Schieben Sie das Beispiel eine Zeile nach unten, ergibt sich für Überschriften und zwei Artikel A2:C4. Die Summe bleibt 10.
=SUM(OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A$2:$A$1000),COUNTA(Sheet1!$A$2:$Z$2)))
Eine Tabelle verwenden, die beim Hinzufügen von Zeilen wächst
Markieren Sie bei einer neuen Liste den Bereich mit Überschriften und wählen Sie Einfügen → Tabelle. Nennen Sie die Tabelle Items. Die folgende Formel summiert die Mengen aus einer Zelle außerhalb der Tabelle. Neue Tabellenzeilen erweitern den Summenbereich. Microsoft-Anleitung zu strukturierten Verweisen
=SUM(Items[数量])
Nur Zeilen mit Artikel-ID summieren
Wird A1:C4 zur Tabelle Items, liefert die obere Formel trotz leerem A3 die Summe 12 (4 + 6 + 2). Um nur Zeilen mit einer ID einzubeziehen, verwenden Sie SUMIF mit der Bedingung, dass die ID-Spalte nicht leer ist. Im Beispiel ergibt das 6 (4 + 2 in Zeile 2 und 4). Microsoft-Referenz zu SUMIF
=SUMIF(Items[商品ID],"<>",Items[数量])
Dropdownliste mit der Tabelle koppeln
Für eine Liste mit Artikelnamen auf einem anderen Blatt öffnen Sie in Desktop-Excel Formeln → Namens-Manager. Legen Sie ItemChoices im Bereich Arbeitsmappe mit =Items[商品名] unter Bezieht sich auf an. Wählen Sie für die Eingabezelle in der Datenüberprüfung Liste und als Quelle =ItemChoices. Die Überschrift bleibt außen vor, und neue Zeilen am Tabellenende liefern weitere Einträge. Füllen Sie den Namen bei jeder neuen Zeile aus, um leere Einträge zu vermeiden. Microsoft-Anleitung zum Erstellen von Dropdownlisten