Nie musisz kopiować formuł w dół, czyli ARRAYFORMULA.

Kiedy arkusz robi się duży i tabela nam rośnie, a równocześnie modyfikujemy formuły, sporo czasu zaczyna zabierać kopiowanie formuły w dół. Próbujesz zmianę formuły na pierwszej komórce, a potem KOPIUJ / WKLEJ w dół, ewentualnie przeciąganie. Jest też prosty trik.

Pokazane powyżej rozwiązania sprawdzają się przy prostych arkuszach. Jeśli jednak gnieździmy po kilka formuł, przydaje się tzw. formuła tablicowa, czyli taka która pozwoli rozszerzyć działanie formuły na cały zakres.

Pojawia się ARRAYFORMULA

Użyjmy przykładu. Ściągnąłem z Wikipedii dane na temat ludności miast w Polsce i chcę dla każdego miasta sprawdzić jaka część ludności tam mieszka. Na dwa sposoby. Jaki % ludności miast i ludności Polski w ogóle.

Plik na którym pracuję możesz zobaczyć tutaj – jeśli chcesz go zmodyfikować – zrób sobie kopię

Technicznie zadanie jest proste: Aby dowiedzieć się jaki % ludności miast stanowi Aleksandrów Kujawski, wystarczy podzielić zawartość F5 przez F2. Czyli liczbę mieszkańców Aleksandrowa przez Sumę ludności miast. Prosta formuła: =F5/F2 . Ale chcę mieć ten rezultat dla wszystkich miast. Więc muszę skopiować formułę w dół. Żeby to się udało – muszę adresować bezwzględnie komórkę F2, czyli zrobić $F$2 – inaczej przy kopiowaniu arkusz będzie sięgał po coraz niższe komórki. =F5/$F$2 to już się nadaje do ciągnięcia w dół.

Ale ja nie chcę ciągnąć w dół przez prawie 1000 komórek!

Zagnieżdżam więc moją funkcję w ARRAYFORMULA i tłumaczę jaki zakres ma teraz obejmować. Chcę, żeby działała do samego ‘dna’ arkusza.

Nowa werjsa wygląda więc tak: =ArrayFormula(F5:F/$F$2) i działa jak poniżej: (formuła jest tylko w komórce I5 – nie jest kopiowana)

Aby dodać ARRAYFORMULA do zwykłej formuły, wystarczy że w czasie edycji wciśniesz Ctrl+Shift+Enter.

Używając zakresu F5:F (kolumna F od wiersza 5 do końca) zamiast pojedynczej komórki, zastosowałem formułę dla wszystkich tych wierszy.

Jeśli tabela będzie miała stałą wielkość, wystarczy że wpiszemy np. F5:F948 i unikniemy powstania ogona z dziwnymi wartościami jak na obrazku:

Załóżmy jednak, że dane się zmienią i za pół roku lista miast się wydłuży lub skróci, a my nie chcemy mieć pustych wartości.

Jak ograniczyć działanie ARRAYFORMULA?

Powinniśmy wprowadzić zabezpieczenie, które będzie sprawdzać jak długa jest lista i ograniczyć działanie ARRAYFORMULA tylko do pełnych wierszy.

Pisząc po polsku pełne wiersze policzymy formułą ILE.NIEPUSTYCH, jej angielskim odpowiednikiem jest COUNTA. Zatem by policzyć pełne wiersze z zakresu F5:F należy zrobić =COUNTA(F5:F) . Dziś wychodzi 944. Ale może się zmienić. Dlatego powinna być badana na bieżąco. W tym celu użyjemy funkcji ARRAY_CONSTRAIN. Jej składnia wygląda następująco:

ARRAY_CONSTRAIN(zakres_danych; liczba_wierszy; liczba_kolumn)

W naszym zastosowaniu będzie wyglądać tak:

=Array_constrain(ArrayFormula(F5:F/$H$2); counta(F5:F);1)

Dodawanie formuł tablicowych i ograniczanie ich niestety zmniejsza czytelność formuł – zwłaszcza na początku obcowania z nimi, dlatego ważne jest by podzielić je na wiersze i oddzielić główną formułę o tej całej tablicowej otoczki. Natomiast jeśli pracujemy z większymi arkuszami i arkuszami, które zmieniają wymiary – znacznie przyspiesza pracę.