9 praktycznych przykładów użycia nowych formuł tablicowych – Formuły tablicowe Excel 365 (4/4)

Poznaliśmy już najważniejsze założenia pracy z formułami tablicowymi oraz kluczowe funkcje. Czas na małe podsumowanie, w którym pokażę kilka praktycznych przykładów formuł tablicowych oraz podsumuję, na co uważać.

Cykl artykułów o nowych formułach tablicowych

Oto kolejny artykuł cyklu o formułach tablicowych w Excelu 365. Dotychczas ukazały się:

• Co się zmieniło w działaniu formuł tablicowych w Excelu 365? – Formuły tablicowe Excel 365 (1/4)
• FILTRUJ, SORTUJ, SORTUJ.WEDŁUG, UNIKATOWE – Formuły tablicowe Excel 365 (2/4)
• LOSOWA.TABLICA i SEKWENCJA – Formuły tablicowe Excel 365 (3/4)
• 9 praktycznych przykładów użycia nowych formuł tablicowych w Excelu 365 – Formuły tablicowe Excel 365 (4/4)

Przykład 1: Posortowana lista unikatowych klientów wstawiona jako Poprawność danych

Zbuduj listę unikatowych klientów, która stanie się źródłem dla reguły sprawdzenia poprawności danych. Zwróć uwagę na adresowanie reguły poprawności danych.

=SORTUJ(UNIKATOWE(E2:E9427))

Przykład 2: Faktury nieopłacone na kwotę wyższą niż X (np. 10 000)

Nowe formuły świetnie sprawdzą się w sytuacji wyszukiwania informacji, które wcześniej realizowaliśmy szukaniem (Ctrl+F), filtrowaniem i sortowaniem. Oto przykład formuły, tym razem wstawionej na tabeli, która wyszukuje faktury nieopłacone powyżej kwoty zdefiniowanej przez użytkownika.

=FILTRUJ(Faktury;(Faktury[Kwota netto]>I2)*(Faktury[Status]=”nieopłacona”))

Przykład 3: Faktury nieopłacone na kwotę wyższą niż X dla klienta Y i posortowane wg kwoty

Przykład nr 2 możemy wzbogacić o sortowanie i warunek dla konkretnego klienta.

=SORTUJ(FILTRUJ(’3′!$A$2:$F$9427;(’3′!$C$2:$C$9427>I2)*(’3′!$F$2:$F$9427=”nieopłacona”)*($E$2:$E$9427=K2);3))

Przykład 4: TOP N faktur dla klienta X i zwróć tylko Nr faktury, Kwotę i Status

Dzięki dodatkowemu zastosowaniu formuły INDEKS z formułą tablicową SEKWENCJA oraz ręcznie zdefiniowaną tablicą poziomą (ta w nawiasach klamrowych), możemy decydować, ile wierszy i które kolumny, pobierzemy z wyniku.

=INDEKS(SORTUJ(FILTRUJ(Tabela_faktur;Tabela_faktur[Klient]=I2);3;-1);SEKWENCJA(K2);{1\3\6})

Przykład 5: WYSZUKAJ.PIONOWO na kluczu składającym się z 2 kolumn

Każda formuła może stać się formułą tablicową. I choć X.WYSZUKAJ formułą tablicową domyślnie nie jest, możemy ją zmusić do tablicowego utworzenia klucza łączącego 2 tabele (Nr faktury & Nr linijki), aby umożliwić wyszukanie po 2 kolumnach jednocześnie.

=X.WYSZUKAJ(H2&I2;A2:A6&B2:B6;C2:C6)

Przykład 6: Lista unikatowych klientów z nieopłaconą fakturą

Kto nie zapłacił faktury? Wyświetl klienta tylko raz.

=UNIKATOWE(INDEKS(FILTRUJ(Faktury7;Faktury7[Status]=”nieopłacona”);SEKWENCJA(ILE.WIERSZY(FILTRUJ(Faktury7;Faktury7[Status]=”nieopłacona”)));5))

Przykład 7: Transpozycja

Transpozycja może odbywać się teraz w dużo prostszy sposób.

Przykład 8: Faktury zawierające tekst w nazwie klienta

Zamiast Ctrl+F i szukania fragmentów tekstu, możemy zwrócić wyniki wyszukiwania jako tabelę.

=FILTRUJ(Faktury8;CZY.LICZBA(SZUKAJ.TEKST(I2;Faktury8[Klient]));”brak wyników”)

Przykład 9: Lista unikatowych wierszy

Jeśli korzystasz z usuwania duplikatów (na karcie Dane) po kilku kolumnach jednocześnie, możesz rozważyć formułę UNIKATOWE na kilku kolumnach, która szuka duplikatów całowierszowych.

=UNIKATOWE(A2:B7)

Na co uważać w pracy z formułami tablicowymi?

Nowe formuły tablicowe są świetne, ale tak jak w przypadku każdej nowej funkcji istnieje kilka rzeczy, o których należy pamiętać.

Wyniki nie mogą być sortowane w zwykły sposób

Rozlania zwracanego przez dynamiczną formułę tablicową nie można sortować za pomocą funkcji sortowania programu Excel. Każda taka próba zakończy się błędem „Nie można zmienić części tablicy”. Aby uporządkować wyniki od najmniejszego do największego lub odwrotnie, zawiń bieżącą formułę funkcją SORTUJ.

Nie można usunąć żadnej wartości z zakresu rozlania

Żadnej z wartości w zakresie rozlania nie można usunąć z tego samego powodu: nie można zmienić części tablicy. Tradycyjne formuły tablicowe również działają w ten sposób.

Nie można skonwertować komórek na tabelę

Jeśli spróbujesz przekonwertować zakres rozlania na tabelę programu Excel (Ctrl + T), program Excel to zrobi. Ale zamiast wyników zobaczysz tylko błąd. Co ciekawe, na danych można wstawić tabelę przestawną, ale źródło jest statyczne, więc brakuje nam tej tabeli

Nie wczytasz prosto rozlania z dodatkiem Power Query do programu Excel

Nie można załadować wyników dynamicznych formuł tablicowych do dodatku Power Query. Jeśli spróbujesz scalić dwa lub więcej zakresów rozlania za pomocą dodatku Power Query, to nie zadziała. Najlepiej wczytywać takie dane poprzez pliki, choć można próbować tworzyć dynamiczne zakresy nazwane.

Brak działania nowych formuł tablicowych w starszych wersjach Excela

Formuły tablicowe działają tylko w Excel 365 i nie będą działać w poprzednich wersjach. Zatem dobrze przemyśl, kto będzie korzystał z Twojego pliku. Stare formuły tablicowe (Ctrl + Shift + Enter) działają we wszystkich wersjach Excela. Po otwarciu skoroszytu, zawierającego dynamiczną formułę tablicową, w starym programie Excel jest ona automatycznie konwertowana na konwencjonalną formułę tablicową ujętą w {nawiasy klamrowe}. Po ponownym otwarciu arkusza w programie Excel 365 nawiasy klamrowe zostaną usunięte. Większość dynamicznych formuł tablicowych będzie wyświetlać swoje wyniki w starszej wersji programu Excel, dopóki nie wprowadzisz w nich zmian. Edycja formuły natychmiast psuje formułę i powoduje wyświetlenie błędu #NAZWA.

Pobierz plik Excel z przykładami formuł tablicowych

Tu możesz pobrać plik Excel i poćwiczyć formuły tablicowe samodzielnie.

A Ty jakie masz doświadczenia w pracy z nowymi formułami tablicowymi?

Podziel się w komentarzu.