Stosowanie formuł do kolumn za pomocą ARRAYFORMULA

Zwykle, jeśli chcesz wykonać obliczenie w każdym wierszu, wpisujesz jedną formułę i przeciągasz ją w dół. Działa to, ale pozostawia setki skopiowanych formuł, które trzeba rozszerzać za każdym razem, gdy pojawią się nowe dane.

Google Sheets oferuje lepsze rozwiązanie: ARRAYFORMULA. Jedna formuła w jednej komórce oblicza całą kolumnę jednocześnie i automatycznie się rozszerza.

ARRAYFORMULA informuje Sheets, że formuła ma działać na całych zakresach, a nie na pojedynczych komórkach. Wynik rozlewa się w dół i wypełnia tyle wierszy, ile ma zakres wejściowy.

Zamiast powtarzać 100 razy formułę =A2*B2, wpisujesz jedną formułę odwołującą się do zakresów A2:A i B2:B, a wyniki pojawiają się automatycznie.

Załóżmy, że kolumna A zawiera ilość, a kolumna B cenę. Aby obliczyć sumę dla każdego wiersza, umieść tę pojedynczą formułę w C2.

Formuła mnoży każdą wartość z A przez odpowiadającą jej wartość z B aż do końca zakresu, więc nie trzeba już zajmować się komórkami od C3 w dół.

Zwróć uwagę na A2:A zamiast A2:A100. Forma otwarta oznacza wszystkie wiersze od wiersza 2 w dół, więc nowo dodane dane są uwzględniane automatycznie.

Na tym polega prawdziwa siła ARRAYFORMULA: arkusz jest przygotowany na przyszłe dane. Dodaj nowe zamówienie, a suma pojawi się bez dodatkowej pracy.

Zakresy otwarte obejmują tysiące pustych wierszy poniżej danych, więc formuła próbuje je również obliczać, często wyświetlając 0 lub przypadkowe wartości.

Umieść obliczenie w funkcji IF, która sprawdza, czy wiersz jest pusty. Jeśli A jest puste, zwróć pusty ciąg znaków; w przeciwnym razie wykonaj obliczenie.

Przydatny trik polega na umieszczeniu nagłówka kolumny wewnątrz formuły tablicowej, dzięki czemu cała kolumna jest obsługiwana przez jedną komórkę. Umieść tę formułę w C1.

Pierwsza część zwraca tekst nagłówka, a pozostała część oblicza każdy wiersz danych. Znak ; układa je pionowo w jedną rozlewaną kolumnę.

ARRAYFORMULA służy nie tylko do obliczeń. Działa również z funkcjami tekstowymi. Aby utworzyć kolumnę pełnych imion i nazwisk, połącz oba zakresy.

W ten sposób każde imię z A zostaje połączone z każdym nazwiskiem z B, a między nimi pojawia się spacja — dla wszystkich wierszy jednocześnie.

Niektóre funkcje samodzielnie obsługują tablice i nie wymagają ARRAYFORMULA. SUMIF, FILTER, UNIQUE i QUERY już działają na zakresach.

Po ARRAYFORMULA sięga się głównie wtedy, gdy operacja zwykle działająca na jednej komórce, taka jak *, & lub LEFT, ma zostać wykonana w każdym wierszu.

Funkcje takie jak LEFT, UPPER i TRIM zwykle działają na jednej komórce. Wewnątrz ARRAYFORMULA działają na całym zakresie.

Ta formuła zamienia wszystkie adresy e-mail w kolumnie A na wielkie litery za jednym razem. Bez tego opakowania zostałaby zmieniona tylko pierwsza komórka.

Nie zawsze trzeba wpisywać nazwę funkcji. W Google Sheets wpisz formułę z odwołaniami do zakresów, a następnie naciśnij Ctrl + Shift + Enter (na Macu: Cmd + Shift + Enter).

Sheets automatycznie opakuje formułę w ARRAYFORMULA. To szybki sposób na przekształcenie przeciąganej formuły w jedną formułę rozlewającą wynik.

W przypadku operacji wykonywanych element po elemencie zakresy powinny mieć taką samą wysokość. Połączenie A2:A z B2:B50 może powodować błędy lub nieprawidłowe wyrównanie wyników.

Oba zakresy powinny być otwarte (A2:A i B2:B) albo oba mieć stały, taki sam rozmiar. Spójność zapewnia prawidłowe wyrównanie wyników w kolejnych wierszach.

Proszę sprawdzić swoją wiedzę na temat ARRAYFORMULA.

Można teraz obliczać całe kolumny za pomocą jednej formuły:

• ARRAYFORMULA stosuje operację do całych zakresów
• Zakresy otwarte, takie jak A2:A, automatycznie uwzględniają nowe wiersze
• Aby pomijać puste komórki, należy użyć IF(A2:A = "", "", ...)
• Działa z obliczeniami, łączeniem tekstu i funkcjami takimi jak UPPER
• Ctrl/Cmd + Shift + Enter automatycznie dodaje tę otoczkę

Jedna komórka, żywa kolumna.