Mit ARRAYFORMULA Formeln auf Spalten anwenden

Wenn Sie normalerweise in jeder Zeile eine Berechnung benötigen, schreiben Sie eine Formel und ziehen sie nach unten. Das funktioniert, hinterlässt aber Hunderte kopierter Formeln, die Sie bei neuen Daten jedes Mal erweitern müssen.

Google Sheets bietet einen besseren Weg: ARRAYFORMULA. Eine Formel in einer einzigen Zelle berechnet die gesamte Spalte auf einmal und wächst automatisch mit.

ARRAYFORMULA weist Sheets an, eine Formel auf ganze Bereiche statt auf einzelne Zellen anzuwenden. Das Ergebnis wird nach unten ausgegeben und füllt so viele Zeilen aus, wie der Eingabebereich enthält.

Statt also =A2*B2 hundertmal zu wiederholen, schreiben Sie eine Formel, die auf A2:A und B2:B verweist, und die Ergebnisse werden automatisch ausgefüllt.

Angenommen, Spalte A enthält die Menge und Spalte B den Preis. Um die Summe pro Zeile zu berechnen, setzen Sie diese einzelne Formel in C2 ein.

Sie multipliziert jeden Wert in A mit dem passenden Wert in B bis zum Ende der Spalten. C3 und die folgenden Zellen müssen Sie nie wieder bearbeiten.

Beachten Sie A2:A anstelle von A2:A100. Die offene Schreibweise bedeutet alle Zeilen ab Zeile 2, sodass neu hinzugefügte Daten automatisch eingeschlossen werden.

Das ist die eigentliche Stärke von ARRAYFORMULA: Sie macht Ihr Tabellenblatt zukunftssicher. Fügen Sie eine neue Bestellung hinzu, erscheint die Summe ohne zusätzlichen Aufwand.

Offene Bereiche schließen Tausende leerer Zeilen unterhalb Ihrer Daten ein, und die Formel versucht, auch für diese Berechnungen durchzuführen. Dadurch werden häufig 0 oder unerwünschte Werte angezeigt.

Schließen Sie die Berechnung in eine IF-Funktion ein, die prüft, ob die Zeile leer ist. Wenn A leer ist, geben Sie eine leere Zeichenfolge zurück; andernfalls führen Sie die Berechnung aus.

Ein nützlicher Trick: Fügen Sie die Spaltenüberschrift in die Array-Formel ein, sodass die gesamte Spalte von einer einzigen Zelle gesteuert wird. Setzen Sie diese Formel in C1 ein.

Der erste Teil gibt den Text der Überschrift aus; der Rest berechnet jede Datenzeile. Das ; stapelt die Ergebnisse vertikal zu einer einzigen ausgefüllten Spalte.

ARRAYFORMULA ist nicht nur für Berechnungen geeignet. Sie funktioniert auch mit Textfunktionen. Um eine vollständige Namensspalte aus Vor- und Nachnamen zu erstellen, verketten Sie die beiden Bereiche.

Dadurch wird in jeder Zeile gleichzeitig der Vorname in A mit dem Nachnamen in B verbunden, mit einem Leerzeichen dazwischen.

Einige Funktionen verarbeiten Arrays selbstständig und benötigen keine ARRAYFORMULA. SUMIF, FILTER, UNIQUE und QUERY funktionieren bereits mit Bereichen.

Sie verwenden ARRAYFORMULA vor allem dann, wenn eine normalerweise auf eine einzelne Zelle angewendete Operation wie *, & oder LEFT für jede Zeile ausgeführt werden soll.

Funktionen wie LEFT, UPPER und TRIM arbeiten normalerweise mit einer einzelnen Zelle. Innerhalb von ARRAYFORMULA wirken sie auf einen ganzen Bereich.

Damit werden alle E-Mail-Adressen in Spalte A auf einmal in Großbuchstaben umgewandelt. Ohne diese Umschließung würden Sie nur die erste Zelle umwandeln.

Sie müssen den Funktionsnamen nicht immer eingeben. Geben Sie in Google Sheets Ihre Formel mit Bereichsverweisen ein und drücken Sie dann Strg plus Umschalt plus Eingabetaste (auf dem Mac Befehl plus Umschalt plus Eingabetaste).

Sheets schließt die Formel automatisch für Sie in ARRAYFORMULA ein. So lässt sich eine heruntergezogene Formel schnell in eine einzige Formel umwandeln, die Ergebnisse automatisch ausgibt.

Bei elementweisen Operationen sollten die Bereiche die gleiche Höhe haben. Die Kombination von A2:A und B2:B50 kann zu Fehlern oder falsch ausgerichteten Ergebnissen führen.

Verwenden Sie für beide Seiten offene Bereiche (A2:A und B2:B) oder begrenzen Sie beide auf dieselbe Größe. Einheitlichkeit sorgt dafür, dass die Ausgabe zeilenweise ausgerichtet bleibt.

Testen Sie Ihr Wissen über ARRAYFORMULA.

Sie haben gelernt, ganze Spalten mit einer einzigen Formel zu berechnen:

• ARRAYFORMULA wendet eine Operation auf ganze Bereiche an
• Offene Bereiche wie A2:A schließen neue Zeilen automatisch ein
• Mit IF(A2:A = "", "", ...) überspringen Sie leere Zellen
• Die Funktion funktioniert mit Berechnungen, Textverknüpfungen und Funktionen wie UPPER
• Ctrl/Cmd plus Shift plus Enter fügt die Umhüllung für Sie ein

Eine Zelle, eine dynamische Spalte.