Pobierz przykładowy skoroszyt
Ten samouczek zawiera wprowadzenie do formuł tablic dynamicznych w programie Excel i Arkuszach Google.
Wstęp
We wrześniu 2022 r. firma Microsoft wprowadziła do Excela formuły tablic dynamicznych. Ich celem jest ułatwienie pisania złożonych formuł i zmniejszenie ryzyka błędu.
Dynamiczne formuły tablicowe mają ostatecznie zastąpić formuły tablicowe, czyli zaawansowane formuły, które wymagają użycia Ctrl + Shift + Enter (CSE).
Oto szybkie porównanie między formułą Array a dynamiczną formułą tablicową używaną do wyodrębnienia listy unikalnych działów z naszej listy w zakresie A2:A7.
Formuła starszej tablicy (CSE):
W komórce wprowadzany jest następujący wzór D2 i wprowadza się, naciskając Ctrl + Shift + Enter i kopiując go w dół z D2 do D5.
| 1 | {=JEŻELI.BŁĄD(INDEKS($A$2:$A$7; DOPASUJ(0; LICZ.JEŻELI($D$1:D1, $A$2:$A$7); 0))), "")} |
Formuła tablicy dynamicznej:
Poniższa formuła jest wprowadzana tylko w komórce D2 i wszedł przez naciśnięcie Enter. Na pierwszy rzut oka możesz stwierdzić, jak łatwe i proste jest napisanie formuły tablicy dynamicznej.
| 1 | =UNIKAT(A2:A7) |
Dostępność
Od sierpnia 2022 r. Formuły tablic dynamicznych są dostępne tylko dla użytkowników usługi Office 365.
Zakres rozlania i rozlania
Formuły tablic dynamicznych działają poprzez zwracanie wielu wyników do zakresu komórek na podstawie pojedynczej formuły wprowadzonej w jednej komórce.
To zachowanie jest określane jako „Rozlanie” a zakres komórek, w których umieszczane są wyniki, nazywa się „Zakres rozlania”. Po zaznaczeniu dowolnej komórki w zakresie rozlania program Excel podświetli ją cienką niebieską ramką.
W poniższym przykładzie dynamiczna formuła tablicowa SORTOWAĆ jest w celi D2 a wyniki zostały rozlane w zasięgu D2:D7
| 1 | =SORTUJ(A2:A7) |
Wyniki formuły są dynamiczne, co oznacza, że jeśli nastąpi zmiana w zakresie źródłowym, zmienią się również wyniki i zmieni się rozmiar zakresu rozlania.
#ROZLANIE!
Należy pamiętać, że jeśli zakres rozlania nie jest całkowicie pusty, zwracany jest błąd #ROZLANIE.
Po wybraniu błędu #ROZLANIA żądany zakres rozlania formuły zostanie wyróżniony przerywaną niebieską obwódką. Przeniesienie lub usunięcie danych w niepustej komórce usuwa ten błąd, umożliwiając rozlanie formuły.
Notacja odniesienia rozlania
Aby odwołać się do zakresu rozlania formuły, umieszczamy # symbol po odwołaniu do pierwszej komórki rozlanej komórki.
Możesz również odwołać się do rozlania, zaznaczając wszystkie komórki w zakresie rozlania, a odniesienie do rozlania zostanie utworzone automatycznie.
W poniższym przykładzie chcielibyśmy policzyć liczbę pracowników w naszej firmie za pomocą wzoru LICZBA po ułożeniu ich alfabetycznie przy użyciu dynamicznej formuły tablicowej SORTOWAĆ.
Wchodzimy na SORTOWAĆ formułę w D2 aby uporządkować pracowników z naszej listy:
| 1 | =SORTUJ(A2:A7) |
Następnie wchodzimy w LICZBA formuła w G2 aby policzyć liczbę pracowników:
| 1 | =ILE.LICZB(D2#) |
Zwróć uwagę na użycie # w D2#, aby odnieść się do wyników rozlanych przez SORT w zakresie D2:D7.
Nowe formuły
Poniżej znajduje się pełna lista nowych formuł tablic dynamicznych:
- JEDYNY W SWOIM RODZAJU - Zwraca listę unikalnych wartości z zakresu
- SORTOWAĆ - Sortuje wartości w zakresie
- SORTUJ WEDŁUG - Sortuje wartości na podstawie odpowiedniego zakresu
- FILTR - Filtruje zakres na podstawie podanych kryteriów
- RANDARRAY - Zwraca tablicę liczb losowych z zakresu od 0 do 1
- SEKWENCJA - Generuje listę numerów sekwencyjnych, takich jak 1, 2, 3, 4, 5
Dynamiczna formuła tablicowasw Arkuszach Google
Wszystkie powyższe przykłady działają dokładnie tak samo w Arkuszach Google, jak w Excelu.
