Spis treści
Co to są tablice dynamiczne
We wrześniu 2018 r. firma Microsoft wydała aktualizację, która dodaje zupełnie nowe narzędzie do programu Microsoft Excel: tablice dynamiczne i 7 nowych funkcji do pracy z nimi. Te rzeczy, bez przesady, radykalnie zmieniają wszystkie zwykłe techniki pracy z formułami i funkcjami i dotyczą dosłownie każdego użytkownika.
Rozważ prosty przykład, aby wyjaśnić istotę.
Załóżmy, że mamy prostą tabelę z danymi dotyczącymi miast-miesięcy. Co się stanie, jeśli wybierzemy dowolną pustą komórkę po prawej stronie arkusza i wprowadzimy do niej formułę, która łączy nie z jedną komórką, ale bezpośrednio z zakresem?
We wszystkich poprzednich wersjach Excela po kliknięciu Wchodzę otrzymalibyśmy zawartość tylko jednej pierwszej komórki B2. Jak inaczej?
Cóż, lub byłoby możliwe owinięcie tego zakresu w jakąś funkcję agregującą, taką jak =SUMA(B2:C4) i otrzymanie za to sumy końcowej.
Gdybyśmy potrzebowali bardziej złożonych operacji niż pierwotna suma, takich jak wyodrębnianie unikalnych wartości lub Top 3, musielibyśmy wprowadzić naszą formułę jako formułę tablicową za pomocą skrótu klawiaturowego Ctrl+Shift+Wchodzę.
Teraz wszystko jest inne.
Teraz po wpisaniu takiej formuły możemy po prostu kliknąć Wchodzę – i uzyskaj w rezultacie natychmiast wszystkie wartości uXNUMXbuXNUMXbto, o których mówiliśmy:
To nie magia, ale nowe tablice dynamiczne, które ma teraz Microsoft Excel. Witamy w nowym świecie 🙂
Funkcje pracy z tablicami dynamicznymi
Z technicznego punktu widzenia cała nasza tablica dynamiczna jest przechowywana w pierwszej komórce G4, wypełniając wymaganą liczbę komórek w prawo iw dół swoimi danymi. Jeśli wybierzesz dowolną inną komórkę w tablicy, łącze na pasku formuły będzie nieaktywne, co oznacza, że znajdujemy się w jednej z komórek „potomnych”:
Próba usunięcia jednej lub więcej komórek „podrzędnych” do niczego nie doprowadzi – Excel natychmiast je przeliczy i wypełni.
Jednocześnie możemy bezpiecznie odwoływać się do tych „dziecięcych” komórek w innych formułach:
Jeśli skopiujesz pierwszą komórkę tablicy (na przykład z G4 do F8), to cała tablica (jej referencje) przesunie się w tym samym kierunku, co w zwykłych formułach:
Jeśli potrzebujemy przesunąć tablicę, to wystarczy przesunąć (myszą lub kombinacją Ctrl+X, Ctrl+V), znowu tylko pierwsza główna komórka G4 – po niej zostanie przeniesiona w nowe miejsce i cała nasza tablica zostanie ponownie rozszerzona.
Jeśli potrzebujesz odwołać się w innym miejscu arkusza do utworzonej tablicy dynamicznej, możesz użyć znaku specjalnego # („funt”) po adresie jej wiodącej komórki:
Na przykład teraz możesz łatwo utworzyć listę rozwijaną w komórce, która odwołuje się do utworzonej tablicy dynamicznej:
Błędy tablicy dynamicznej
Ale co się stanie, jeśli nie ma wystarczającej ilości miejsca na rozszerzenie tablicy lub jeśli na jej ścieżce znajdują się już komórki zajęte przez inne dane? Poznaj zupełnie nowy typ błędów w programie Excel — #PRZENOSIĆ! (#ROZLANIE!):
Jak zawsze, jeśli klikniemy na ikonkę z żółtym rombem i wykrzyknikiem, otrzymamy dokładniejsze wyjaśnienie źródła problemu i szybko znajdziemy zakłócające komórki:
Podobne błędy wystąpią, jeśli tablica wyjdzie poza arkusz lub trafi w scaloną komórkę. Jeśli usuniesz przeszkodę, wszystko zostanie natychmiast poprawione w locie.
Tablice dynamiczne i tablice inteligentne
Jeśli tablica dynamiczna wskazuje na „inteligentną” tabelę utworzoną za pomocą skrótu klawiaturowego Ctrl+T lub Strona główna – Formatuj jako tabelę (Strona główna — Formatuj jako tabelę), wtedy odziedziczy również swoją główną cechę – autodopasowanie.
Podczas dodawania nowych danych na dole lub po prawej stronie inteligentna tabela i zakres dynamiczny również zostaną automatycznie rozciągnięte:
Jest jednak jedno ograniczenie: nie możemy używać odniesienia do zakresu dynamicznego w forach wewnątrz inteligentnej tabeli:
Tablice dynamiczne i inne funkcje programu Excel
Dobra, mówisz. Wszystko to jest ciekawe i zabawne. Nie ma potrzeby, jak poprzednio, ręcznego rozciągania formuły z odwołaniem do pierwszej komórki oryginalnego zakresu w dół i w prawo i tak dalej. I to wszystko?
Nie całkiem.
Tablice dynamiczne to nie tylko kolejne narzędzie w programie Excel. Teraz są osadzone w samym sercu (lub mózgu) Microsoft Excel – jego silniku obliczeniowym. Oznacza to, że inne znane nam formuły i funkcje programu Excel obsługują teraz również pracę z tablicami dynamicznymi. Rzućmy okiem na kilka przykładów, aby dać wyobrażenie o głębi zmian, które zaszły.
Transponować
Aby transponować zakres (zamień wiersze i kolumny) Microsoft Excel zawsze miał wbudowaną funkcję TRANSP (TRANSPONOWAĆ). Aby jednak z niego skorzystać, należy najpierw poprawnie wybrać zakres dla wyników (np. jeżeli na wejściu był zakres 5×3, to trzeba było wybrać 3×5), a następnie wejść do funkcji i nacisnąć klawisz połączenie Ctrl+Shift+Wchodzę, ponieważ może działać tylko w trybie formuł tablicowych.
Teraz możesz po prostu wybrać jedną komórkę, wprowadzić do niej tę samą formułę i kliknąć normalną Wchodzę – dynamic array zrobi wszystko sama:
Tabliczka mnożenia
To jest przykład, który podałem, gdy poproszono mnie o wizualizację zalet formuł tablicowych w programie Excel. Teraz, aby obliczyć całą tablicę pitagorejską, wystarczy stanąć w pierwszej komórce B2, wpisać tam formułę mnożącą dwie tablice (pionowy i poziomy zbiór liczb 1..10) i po prostu kliknąć na Wchodzę:
Klejenie i konwersja kopert
Tablice można nie tylko mnożyć, ale także sklejać ze standardowym operatorem & (ampersand). Załóżmy, że musimy wyodrębnić imię i nazwisko z dwóch kolumn i poprawić wielkość liter w oryginalnych danych. Robimy to za pomocą jednej krótkiej formuły, która tworzy całą tablicę, a następnie stosujemy do niej funkcję PROPNACH (WŁAŚCIWY)uporządkować rejestr:
Wniosek Top 3
Załóżmy, że mamy kilka liczb, z których chcemy uzyskać trzy pierwsze wyniki, układając je w kolejności malejącej. Teraz odbywa się to za pomocą jednej formuły i znowu bez żadnej Ctrl+Shift+Wchodzę jak przedtem:
Jeśli chcesz, aby wyniki były umieszczane nie w kolumnie, ale w rzędzie, wystarczy w tej formule zamienić dwukropki (separator linii) na średnik (separator elementów w obrębie jednej linii). W angielskiej wersji programu Excel te separatory to odpowiednio średniki i przecinki.
WYSZUKAJ.PIONOWO wyodrębnianie wielu kolumn jednocześnie
Funkcje VPR (WYSZUKAJ.PIONOWO) teraz możesz pobierać wartości nie z jednej, ale z kilku kolumn na raz – wystarczy podać ich liczby (w dowolnej kolejności) jako tablicę w trzecim argumencie funkcji:
Funkcja OFFSET zwracająca tablicę dynamiczną
Jedną z najciekawszych i najbardziej użytecznych (po VLOOKUP) funkcji do analizy danych jest funkcja SPRZEDAŻ (ZRÓWNOWAŻYĆ), której poświęciłem kiedyś cały rozdział w mojej książce i artykuł tutaj. Trudność w zrozumieniu i opanowaniu tej funkcji zawsze polegała na tym, że w wyniku zwracała ona tablicę (zakres) danych, ale nie mogliśmy tego zobaczyć, ponieważ Excel nadal nie wiedział, jak pracować z tablicami po wyjęciu z pudełka.
Teraz ten problem należy już do przeszłości. Zobacz, jak teraz, używając pojedynczej formuły i tablicy dynamicznej zwracanej przez OFFSET, możesz wyodrębnić wszystkie wiersze dla danego produktu z dowolnej posortowanej tabeli:
Przyjrzyjmy się jej argumentom:
- A1 – komórka startowa (punkt odniesienia)
- СКПОЗ(F2;A2:A30;0) – obliczenie przesunięcia od komórki początkowej w dół – do pierwszej znalezionej kapusty.
- 0 – przesunięcie „okna” w prawo względem komórki startowej
- СЧЁТЕСЛИ(A2:A30;F2) – obliczenie wysokości zwracanego „okna” – liczba wierszy, w których znajduje się kapusta.
- 4 — wielkość „okna” w poziomie, czyli wyjściowa 4 kolumny
Nowe funkcje dla tablic dynamicznych
Oprócz obsługi mechanizmu dynamicznej tablicy w starych funkcjach, do programu Microsoft Excel dodano kilka zupełnie nowych funkcji, zaostrzonych specjalnie do pracy z tablicami dynamicznymi. W szczególności są to:
- GRADE (SORTOWAĆ) – sortuje zakres wejściowy i tworzy dynamiczną tablicę na wyjściu
- Sortuj (SORTUJ WEDŁUG) – potrafi sortować jeden zakres po wartościach z drugiego
- FILTER (FILTR) – pobiera wiersze z zakresu źródłowego, które spełniają określone warunki
- UNIK (UNIKALNY) – wydobywa unikalne wartości z zakresu lub usuwa duplikaty
- SLMASYWNE (TABLICA KOLEJOWA) – generuje tablicę liczb losowych o określonym rozmiarze
- PO URODZENIU (SEKWENCJA) — tworzy tablicę z ciągu liczb o podanym kroku
Więcej o nich – trochę później. Są warte osobnego artykułu (a nie jednego) do przemyślanego przestudiowania 🙂
wnioski
Jeśli przeczytałeś wszystko, co napisano powyżej, to myślę, że już zdajesz sobie sprawę ze skali zmian, które zaszły. Tak wiele rzeczy w programie Excel można teraz zrobić łatwiej, łatwiej i bardziej logicznie. Muszę przyznać, że jestem trochę zszokowany tym, jak wiele artykułów będzie teraz musiało zostać poprawionych tutaj, na tej stronie iw moich książkach, ale jestem gotów to zrobić z lekkim sercem.
Podsumowując wyniki, плюсы tablice dynamiczne, możesz napisać:
- Możesz zapomnieć o kombinacji Ctrl+Shift+Wchodzę. Program Excel nie widzi teraz różnicy między „formułami zwykłymi” a „formułami tablicowymi” i traktuje je w ten sam sposób.
- O funkcji SUMPRODUCT (SUMA ILOCZYN), który był wcześniej używany do wprowadzania formuł tablicowych bez Ctrl+Shift+Wchodzę Ty też możesz zapomnieć – teraz to już dość proste SUMA и Wchodzę.
- Inteligentne tabele i znane funkcje (SUM, IF, VLOOKUP, SUMIFS itp.) teraz również w pełni lub częściowo obsługują tablice dynamiczne.
- Istnieje kompatybilność wsteczna: jeśli otworzysz skoroszyt z tablicami dynamicznymi w starej wersji programu Excel, zamienią się one w formuły tablicowe (w nawiasach klamrowych) i będą nadal działać w „starym stylu”.
Znalazłem numer minusy:
- Nie można usuwać pojedynczych wierszy, kolumn ani komórek z tablicy dynamicznej, tzn. jest ona pojedyncza.
- Nie możesz sortować tablicy dynamicznej w zwykły sposób Dane – Sortowanie (Dane — Sortuj). Jest teraz do tego specjalna funkcja. GRADE (SORTOWAĆ).
- Zakres dynamiczny nie może zostać przekształcony w tabelę inteligentną (ale można utworzyć zakres dynamiczny w oparciu o tabelę inteligentną).
Oczywiście to nie koniec i jestem pewien, że Microsoft będzie dalej ulepszał ten mechanizm w przyszłości.
Gdzie mogę pobrać?
I na koniec główne pytanie 🙂
Firma Microsoft po raz pierwszy ogłosiła i pokazała podgląd tablic dynamicznych w programie Excel we wrześniu 2018 r. na konferencji Zapalać. W ciągu następnych kilku miesięcy odbyły się gruntowne testy i uruchamianie nowych funkcji, po raz pierwszy koty pracowników samego Microsoftu, a następnie testerów-wolontariuszy z kręgu Office Insiderów. W tym roku aktualizacja, która dodaje dynamiczne macierze, zaczęła być stopniowo udostępniana zwykłym subskrybentom Office 365. Na przykład otrzymałem go dopiero w sierpniu wraz z moją subskrypcją Office 365 Pro Plus (ukierunkowana na miesiąc).
Jeśli Twój Excel nie ma jeszcze tablic dynamicznych, ale naprawdę chcesz z nimi pracować, dostępne są następujące opcje:
- Jeśli masz subskrypcję Office 365, możesz po prostu poczekać, aż ta aktualizacja do Ciebie dotrze. Jak szybko to się dzieje, zależy od tego, jak często aktualizacje są dostarczane do Twojego pakietu Office (raz w roku, raz na sześć miesięcy, raz w miesiącu). Jeśli masz komputer firmowy, możesz poprosić administratora o skonfigurowanie aktualizacji do częstszego pobierania.
- Możesz dołączyć do grona ochotników testujących niejawnych testerów pakietu Office — wtedy jako pierwszy otrzymasz wszystkie nowe funkcje i funkcje (ale oczywiście istnieje szansa na zwiększone błędy w programie Excel).
- Jeśli nie masz subskrypcji, ale pudełkową samodzielną wersję programu Excel, będziesz musiał poczekać do wydania następnej wersji pakietu Office i programu Excel przynajmniej w 2022 roku. Użytkownicy takich wersji otrzymują tylko aktualizacje zabezpieczeń i poprawki błędów, a wszystkie nowe „gadżety” trafiają teraz tylko do subskrybentów Office 365. Smutne, ale prawdziwe 🙂
W każdym razie, gdy w Excelu pojawią się tablice dynamiczne – po tym artykule będziesz na to gotowy 🙂
- Co to są formuły tablicowe i jak ich używać w programie Excel
- Sumowanie okien (zakresów) za pomocą funkcji OFFSET
- 3 sposoby transpozycji tabeli w programie Excel