Excel-TippDynamische Bereiche mit ERWEITERN() genau definieren

Die Funktion ERWEITERN() richtet sich vor allem an diejenigen, die regelmäßig mit dynamischen Bereichen arbeiten. Ob für Dashboards, Berichte oder formatierte Auswertungen – mit ERWEITERN() können Sie die Darstellung der Daten präzise steuern.

Was macht ERWEITERN()?

Die Funktion ERWEITERN() ermöglicht es Ihnen, einen Zellbereich auf eine definierte Größe zu bringen – sowohl in Zeilen als auch in Spalten. Falls der Ursprungsbereich kleiner ist als die gewünschte Zielgröße, werden die fehlenden Zellen automatisch mit einem von Ihnen festgelegten Wert aufgefüllt; zum Beispiel einem Bindestrich "-" oder #NV.

Die Syntax der Funktion ist:

ERWEITERN(Matrix; Zeilen; [Spalten]; [Auffüllwert])

• Matrix: Der Bereich (Zellen oder Tabellenbereich Ihres Excel-Arbeitsblatts), den Sie erweitern möchten.
• Zeilen: Die gewünschte Anzahl an Zeilen im Ergebnisbereich.
• Spalten: (optional) Die gewünschte Anzahl an Spalten.
• Auffüllwert: (optional) Der Wert, mit dem leere Felder aufgefüllt werden sollen.

Wenn Sie das Argument Spalten oder den Auffüllwert weglassen, dann verwendet Excel automatisch die bestehende Anzahl an Spalten und #NV als Standard für den Auffüllwert.

Warum ist ERWEITERN() nützlich?

Gerade beim Einsatz von dynamischen Array-Funktionen wie FILTER(), SORTIEREN() oder EINDEUTIG() ändert sich die Größe der zurückgegebenen Bereiche je nach Datenlage. Das führt im Berichtswesen oder auf Dashboards oft zu Problemen: Denn die Ergebnisse „springen“, verschieben andere Inhalte oder passen nicht mehr in fest definierte Formate.

Mit ERWEITERN() stellen Sie sicher, dass:

• der Bericht immer gleich viele Zeilen umfasst – zum Beispiel 10 Zeilen, auch wenn nur 3 Einträge gefunden wurden;
• leere Zellen gezielt aufgefüllt werden – zum Beispiel mit einem Platzhalter wie "-" oder "nicht vorhanden";
• Designs, Rahmen und Diagramme konsistent bleiben, unabhängig von der Datenmenge.

Praxisbeispiel: Dynamischer Vertriebsreport mit ERWEITERN()

Stellen Sie sich vor, Sie möchten eine Liste mit den Verkäuferinnen und Verkäufern anzeigen, die mehr als 5.000 EUR Umsatz erzielt haben. Allerdings sollen immer genau 10 Zeilen angezeigt werden, damit das Dashboard schön einheitlich bleibt – egal, wie viele Verkäufer die Bedingung erfüllen.

In der folgenden Abbildung sehen Sie Daten für das Praxisbeispiel. Diese Daten stehen im Bereich A2:B21 auf einem Tabellenblatt.

Sie wollen jetzt die Umsätze filtern, deren Wert größer als 5.000 ist. Hierzu können Sie einfach die Funktion FILTER() wie folgt einsetzen:

=FILTER(A2:B21;B2:B21>5000)

Als Ergebnis bekommen Sie 4 Listeneinträge als Ergebnis dargestellt. Dies ist zwar grundsätzlich richtig, kann aber zu Problemen führen, wenn Sie die Daten in ein Dashboard oder Diagramm übernehmen und hier genau 10 Datensätze zur Übernahme definiert haben.

Dieses Problem lösen Sie, indem Sie das Filterergebnis immer auf 10 Einträge erhöhen. Ergänzen Sie die Filterformel daher wie folgt:

=ERWEITERN(FILTER(A2:B21;B2:B21>5000);10;2;"-")

Was passiert hier genau?

• FILTER(…) filtert die Verkäuferinnen und Verkäufer heraus, deren Umsatz größer als 5.000 EUR ist.
• ERWEITERN(…; 10; 2; "-") sorgt dafür, dass immer ein Bereich mit 10 Zeilen und 2 Spalten angezeigt wird.
• Falls weniger als 10 Treffer vorliegen, werden die übrigen Zellen mit "-" befüllt.

Weitere Tipps und Varianten für ERWEITERN()

Spalten nicht angeben

Wenn Sie nur die Anzahl der Zeilen festlegen und die ursprüngliche Spaltenanzahl beibehalten wollen, reicht:

=ERWEITERN(FILTER(…); 10)

Alternative Auffüllwerte

Sie können auch "n.v.", "keine Daten" oder auch 0 als Auffüllwert angeben – je nach Kontext Ihres Berichtes.

In Diagrammen nutzen

Wenn Sie mit ERWEITERN() eine konstante Anzahl an Zeilen liefern, können Sie diesen Bereich problemlos in Diagrammen verwenden – ohne dass sich die Datenquelle für das Diagramm, der gefilterte und erweiterte Bereich, ständig verändert.