Dynamiczne formuły tablicowe i zachowanie rozlanej tablicy

Formuły programu Excel zwracające zestaw wartości, znane również jako tablica, zwracają te wartości do sąsiednich komórek. To zachowanie jest zwane rozlaniem.

Formuły, które mogą zwracać tablice o zmiennym rozmiarze, są nazywane dynamicznymi formułami tablicowymi . Formuły, które obecnie zwracają tablice, które są pomyślnie rozlane, mogą być określane jako rozlane formuły tablicowe.

Poniżej przedstawiono kilka uwag ułatwiających zrozumienie tego typu formuł i korzystanie z nich.

Co oznacza rozlanie?

Uwaga: Starsze formuły tablicowe, nazywane starszymi formułami tablicowymi, zawsze zwracają wynik o stałym rozmiarze — zawsze są rozlane do tej samej liczby komórek. Zachowanie rozlania opisane w tym temacie nie ma zastosowania do starszych formuł tablicowych.

Rozlanie oznacza, że formuła spowodowała powstanie wielu wartości, a te wartości zostały umieszczone w sąsiednich komórkach. Na przykład =SORT(D2:D11,1,-1), która sortuje tablicę w kolejności malejącej, zwróci odpowiednią tablicę o wysokości 10 wierszy. Ale wystarczy wprowadzić formułę w lewej górnej komórce lub F2 w tym przypadku i zostanie ona automatycznie rozlana do komórki F11.

Sortuj wartości w komórkach D2:D11 za pomocą funkcji =SORTUJ(D2:D11,1,-1)

Kluczowe kwestie

Po naciśnięciu klawisza Enter w celu potwierdzenia formuły, program Excel dynamicznie dopasuje rozmiar zakresu danych wyjściowych i umieści wyniki w każdej komórce w tym zakresie.
Jeśli piszesz dynamiczną formułę tablicową, aby działać na liście danych, może być przydatne umieszczenie jej w tabeli programu Excel, a następnie użycie odwołania strukturalnego w celu odwoływania się do danych. Dzieje się tak, ponieważ odwołania strukturalne są automatycznie dostosowywane w miarę dodawania lub usuwania wierszy z tabeli.
Rozlane formuły tablicowe nie są obsługiwane w samych tabelach programu Excel, dlatego należy umieścić je w siatce poza tabelą. Tabele najlepiej nadają się do przechowywania wierszy i kolumn niezależnych danych.
Po wprowadzeniu rozlanej formuły tablicowej, po zaznaczeniu dowolnej komórki w obszarze rozlania program Excel umieści wyróżnione obramowanie wokół zakresu. Obramowanie zniknie po zaznaczeniu komórki poza obszarem.
Tylko pierwsza komórka w obszarze rozlania jest edytowalna. Jeśli zaznaczysz inną komórkę w obszarze rozlania, formuła będzie widoczna na pasku formuły, ale tekst jest „wyszarzony” i nie można go zmienić. Jeśli chcesz zaktualizować formułę, zaznacz lewą górną komórkę w zakresie tablicy, zmień ją zgodnie z potrzebami, a następnie program Excel automatycznie zaktualizuje resztę obszaru rozlanego po naciśnięciu klawisza Enter.
Nakładanie się formuły — formuły tablicowe nie mogą być wpisane, jeśli coś blokuje zakres danych wyjściowych. W takim przypadku program Excel zwróci błąd #ROZLANIE! oznaczający blokadę. W przypadku usunięcia blokady formuła będzie rozlewana zgodnie z oczekiwaniami. W poniższym przykładzie zakres wyjściowy formuły nakłada się na inny zakres z danymi i jest wyświetlany z obramowaniem kropkowanym nakładającym się na komórki z wartościami, wskazując, że nie można zastosować rozlania. Usuń dane blokujące lub skopiuj je w inne miejsce, a formuła rozleje się zgodnie z oczekiwaniami.
Starsze formuły tablicowe wprowadzone kombinacją klawiszy CTRL+SHIFT+ENTER (CSE) są nadal obsługiwane ze względu na zgodność wsteczną, ale nie powinny być już używane. Jeśli chcesz, możesz przekonwertować starsze formuły tablicowe na dynamiczne formuły tablicowe, lokalizując pierwszą komórkę w zakresie tablicy, kopiując tekst formuły, usuwając cały zakres starszej tablicy, a następnie ponownie wprowadzając formułę w lewej górnej komórce. Przed uaktualnieniem starszych formuł tablicowych do dynamicznych formuł tablicowych należy pamiętać o pewnych różnicach obliczeniowych między nimi.
Program Excel ma ograniczoną obsługę tablic dynamicznych między skoroszytami, a ten scenariusz jest obsługiwany tylko wtedy, gdy oba skoroszyty są otwarte. Jeśli zamkniesz skoroszyt źródłowy, wszystkie połączone dynamiczne formuły tablicowe zwrócą błąd #ADR! , gdy zostaną odświeżone.