Ten artykuł jest częścią większej serii poświęconej użytecznym funkcjom Excela. Linki do pozostałych artykułów znajdują się poniżej.
Większość osób uważa, że formułę w Excelu można wykorzystać do uzyskania jednego wyniku w określonej komórce arkusza kalkulacyjnego.
Ale dostępne w Excelu dynamiczne formuły tablicowe pozwalają wykorzystać jedną formułę w wielu komórkach — w każdej z innym obliczeniem — dzięki czemu szybko stworzysz duże arkusze i zminimalizujesz ryzyko błędów. Możesz używać tych magicznych formuł, gdzie tylko chcesz, by powtórzyć obliczenia na zbiorze danych.
Oto dwa sposoby na to, jak zaoszczędzić czas i wysiłek, korzystając z dynamicznych formuł tablicowych.
Na początek: stwórz tabliczkę dodawania
Twój cel: Przygotować tabliczkę dodawania i klucz odpowiedzi, takie jak przedstawione poniżej, by pomóc dziecku w nauce matematyki (a sobie — w sprawdzaniu odpowiedzi).
Po staremu: Po wpisaniu wartości, które będą dodawane, w pierwszej kolumnie i w pierwszym wierszu, tworzysz formułę taką jak =C4+B5 w pierwszej komórce tabeli. Następnie kopiujesz ją i wklejasz w całym wierszu, a później kopiujesz cały wiersz i wklejasz go do pozostałych. Sporo kopiowania i wklejania!
Dynamicznie: Stwórz jedną formułę, która będzie obejmowała cały zakres komórek i policzy wszystkie wartości jednocześnie.
Jak to zrobić: Wykorzystaj poniższą tabelę jako przykład. W pierwszej komórce z odpowiedzią wpisz =C4:J4+B5:B18, gdzie C4:J4 to zakres danych w pierwszym wierszu, a B5:B18 — w pierwszej kolumnie. Wciśnij Enter, aby wypełnić całą tabelę.
Wyższy poziom: sumowanie danych według osób i kategorii
Twój cel: Sprawdzenie, jakie ogólne wyniki sprzedażowe mają najlepsi sprzedawcy tygodnia, i aktualizowanie tych wyników w miarę dodawania nowych najlepszych sprzedawców tygodnia.
Po staremu: Użycie funkcji =SUMA.JEŻELI(), która sumuje wyniki poszczególnych sprzedawców Ale ponieważ co tydzień wybierani są najlepsi sprzedawcy, a tabela się rozrasta, trzeba by za każdym razem aktualizować arkusz ręcznie.
Dynamicznie: Wykorzystaj dynamiczną formułę tablicową, by automatycznie uzupełniać tabelę, gdy dodawani będą nowi najlepsi sprzedawcy tygodnia.
Jak to zrobić: Tabela poniżej pokazuje dotychczasowych zwycięzców tygodnia. Aby stworzyć listę sprzedawców i wykluczyć powtarzające się wpisy, użyj nowej funkcji UNIKATOWE(). W pustym miejscu arkusza wpisz formułę =UNIKATOWE(B3:B12). B3:B12 to lista dotychczasowych najlepszych sprzedawców.
Aby podliczyć ogólne wyniki sprzedaży dla każdej osoby, wykorzystaj specjalną wersję funkcji =SUMA.JEŻELI. W kolumnie obok imienia Murat wpisz formułę =SUMA.JEŻELI(B3:B12,E3#,C3:C12). B3:B12 to obecna lista najlepszych sprzedawców, a C3:C12 to lista tygodniowych wyników sprzedażowych.
Znak # to nasz magiczny element — informuje Excel, że należy sprawdzić cały zakres dynamicznej tablicy — czyli, innymi słowy, uwzględnić nowe wiersze. Wciśnij Enter, a Excel podsumuje wyniki sprzedażowe poszczególnych osób.
Teraz, kiedy dodasz Alyssę jako najlepszą sprzedawczynię w 11. tygodniu, podsumowanie zostanie uzupełnione automatycznie.
Excel oferuje wiele sposobów wykorzystania dynamicznych formuł tablicowych — na przykład funkcje FILTRUJ(), SORTUJ(), SORTUJ.WEDŁUG(), SEKWENCJA() oraz LOSOWA.TABLICA(). Dynamiczne formuły tablicowe wymagają użycia tych funkcji tylko raz, aby wypełnić nieskończenie wiele komórek, dzięki czemu oszczędzisz mnóstwo czasu.