Rewolucja w Excelu: 10 Funkcji Tablicowych, Które Zmienią Twój Sposób Pracy
- Szczegóły
Nadal wpisujesz formułę w jedną komórkę, a potem przeciągasz ją przez 500 wierszy? Mam dla Ciebie dobrą wiadomość: to już przeszłość. Excel 365 wprowadził funkcje tablicowe - formuły, które same „rozlewają się” na tyle komórek, ile potrzeba. Zero przeciągania. Zero kopiowania. Wpisujesz raz, wynik pojawia się wszędzie.
Po 12 latach prowadzenia szkoleń widzę, jak ludzie reagują na te funkcje. Najpierw niedowierzanie. Potem: „Dlaczego nikt mi tego wcześniej nie pokazał?!” A prawda jest taka, że większość kursów Excela wciąż uczy metod z 2010 roku.
W tym artykule pokażę Ci 10 funkcji tablicowych Excel 365, które dosłownie zmieniają sposób pracy z danymi. Niektóre z nich zastąpią Ci formuły, nad którymi wcześniej spędzałeś godziny.
Czym są funkcje tablicowe w Excel 365?
Funkcje tablicowe (ang. dynamic array functions) to formuły wprowadzone w Excel 365, które automatycznie zwracają wyniki do wielu komórek naraz. W odróżnieniu od klasycznych formuł, nie musisz ich kopiować ani przeciągać - Excel sam określa rozmiar wyniku i „rozlewa” go (ang. spill) na sąsiednie komórki.
To fundamentalna zmiana w filozofii pracy z Excelem.
Przeczytaj także: Wymiana filtra w Aqua Vac Excell S20
Lista funkcji tablicowych Excel 365
- TABLICA.NA.TEKST - koniec z ręcznym łączeniem danych
- PODZIEL.TEKST - parser tekstu bez VBA
- LOSOWA.TABLICA - generowanie danych testowych
- STOS.PION / STOS.POZIOM - łączenie zakresów bez kopiowania
- TEKST.PO / TEKST.PRZED - wyciąganie fragmentów tekstu
- WYBIERZ.KOLUMNY / WYBIERZ.WIERSZE - reorganizacja danych
- DO.KOLUMNY / DO.WIERSZA - transformacja układu danych
- ZAWIŃ.WIERSZE / ZAWIŃ.KOLUMNY - reshape danych
- UNIKATOWE - deduplikacja jednym kliknięciem
- SEKWENCJA - automatyczne numerowanie i serie
Szczegółowe omówienie wybranych funkcji tablicowych
TABLICA.NA.TEKST
Co robi: Zamienia tablicę (zakres komórek) w jeden ciąg tekstowy z wybranym separatorem.
Skąd wziął się problem? Przez lata łączenie wartości z wielu komórek w jeden tekst wymagało albo nudnego „klikaj i wpisuj separatory” (=A1&”, „&A2&”, „&A3…), albo skomplikowanych formuł ze ZŁĄCZ.TEKSTY i TABLE.IF. W przypadku dynamicznych zakresów - koszmaru z VBA.
Dlaczego TABLICA.NA.TEKST jest tak użyteczna? Jedna funkcja. Dowolny zakres. Dowolny separator. Funkcja przyjmuje cztery argumenty - tablicę, separator (opcjonalnie tekst dla pustych komórek) i kolejność skanowania.
PODZIEL.TEKST
Co robi: Dzieli tekst na części według separatora i zwraca tablicę wyników - każdy fragment w osobnej komórce.
Skąd wziął się problem? Klasyczny scenariusz: dostajesz plik CSV, gdzie w jednej kolumnie masz „Imię Nazwisko”, a potrzebujesz osobnych kolumn. Albo kody produktów typu „CAT-123-XL”, które trzeba rozbić. Do tej pory: Tekst jako kolumny (ręczna operacja), skomplikowane formuły z LEWY/PRAWY/ZNAJDŹ, albo Power Query.
Przeczytaj także: Aqua Vac Excell 20S: Zamienne filtry powietrza
Dlaczego PODZIEL.TEKST zmienia grę? Jedna formuła. Dynamiczny wynik. Automatyczna aktualizacja przy zmianie źródła.
Przykład z życia: Firma logistyczna, szkolenie z automatyzacji. Analityk codziennie ręcznie rozdzielał adresy na ulicę, numer, kod pocztowy, miasto. Przy 200 przesyłkach dziennie - godzina czystej straty.
Wynik rozlewa się automatycznie: „ul. Główna 15” | „00-001” | „Warszawa”
Jak to zrobić poprawnie?
Składnia:
Przeczytaj także: Angażująca Metoda Flipped Classroom
=PODZIEL.TEKST(tekst; separator; [nr_kolumny]; [ignoruj_puste]; [dopasuj_tryb]; [pad_z])
Kluczowe parametry:
- tekst - komórka z tekstem do podziału
- separator - znak lub ciąg znaków do podziału
- nr_kolumny - opcjonalnie: zwróć tylko n-ty element (zamiast wszystkich)
Zaawansowane użycie - podział po wielu separatorach:
=PODZIEL.TEKST(A2;{"-";"_";" "})
Ta formuła dzieli tekst zarówno po myślniku, podkreślniku, jak i spacji.
LOSOWA.TABLICA
Co robi: Generuje tablicę losowych liczb o podanych wymiarach.
Skąd wziął się problem? Testujesz formułę? Budujesz prototyp dashboardu? Przygotowujesz prezentację z „anonimowymi” danymi? Do tej pory: wpisywanie =LOS() w jedną komórkę i kopiowanie na 1000 wierszy. Albo szukanie danych testowych w internecie.
Dlaczego LOSOWA.TABLICA zmienia grę? Jedna funkcja generuje całą tablicę danych o dowolnych wymiarach. 100 wierszy × 5 kolumn? Proszę bardzo. I za każdym razem inne liczby (lub te same, jeśli użyjesz parametru).
Przykład z życia: Na szkoleniu z Power BI kursant chciał przetestować wydajność swojego modelu na większych danych. Oryginalne dane - poufne. Rozwiązanie: wygenerowanie 50 000 wierszy danych testowych w 3 sekundy.
=LOSOWA.TABLICA(50000;5;1000;100000)
50 000 wierszy, 5 kolumn, wartości od 1 000 do 100 000.
STOS.PION / STOS.POZIOM
Co robią: Łączą wiele zakresów w jedną tablicę - pionowo (STOS.PION) lub poziomo (STOS.POZIOM).
Skąd wziął się problem? Dane w kilku tabelach na różnych arkuszach? Raporty z 12 miesięcy, każdy w osobnym pliku? Do tej pory jedyne wyjścia to: ręczne kopiuj-wklej, Power Query, albo skomplikowane formuły INDEKS+PODAJ.POZYCJĘ z JEŻELI.BŁĄD.
Dlaczego STOS zmienia grę? Jedna formuła łączy dowolną liczbę zakresów. Dane aktualizują się automatycznie. Zero kopiowania. I - co najważniejsze - możesz mieszać zakresy o różnych wymiarach.
Przykład z życia: Szkolenie dla sieci detalicznej. Każdy sklep przysyłał raport sprzedaży w identycznym formacie. 25 sklepów × 12 miesięcy = 300 plików rocznie do „sklejenia”. Klasycznie - dzień pracy.
=STOS.PION(Styczen[Sprzedaż];Luty[Sprzedaż];Marzec[Sprzedaż])
Wynik: jedna tabela ze wszystkimi danymi. Zmiana w źródle?
| Efekt | Przed | Po |
|---|---|---|
| Kopiuj-wklej z 12 arkuszy | 1 formuła | |
| Dane przestają być aktualne | Automatyczna synchronizacja | |
| Power Query dla prostego łączenia | Rozwiązanie w arkuszu |
💡Pro tip: STOS.PION świetnie współpracuje z UNIKATOWE - najpierw połącz dane z wielu źródeł, potem wyciągnij unikalne wartości.
TEKST.PO / TEKST.PRZED
LEWY(A1;ZNAJDŹ(” „;A1)-1) - żeby wyciągnąć pierwsze słowo. Działa, ale zajmuje 5 minut na skonstruowanie i nikt później nie wie, co robi.
Dlaczego TEKST.PO/PRZED zmienia grę? Mówisz Excelowi wprost: „daj mi tekst PO tym znaku” albo „daj mi tekst PRZED tą frazą”. Bez liczenia pozycji. Bez DŁ. Bez ZNAJDŹ. Przydatne dla plików typu „raport.backup.xlsx”.
Wyciągnięcie folderu nadrzędnego ze ścieżki:
| Efekt | Przed | Po |
|---|---|---|
| PRAWY+DŁ+ZNAJDŹ (3 funkcje) | 1 czytelna funkcja | |
| Błąd przy braku separatora | Opcjonalny parametr „jeśli_brak” | |
| Trudne do zrozumienia | Samodokumentujące się |
💡Pro tip: Połącz TEKST.PO z TEKST.PRZED, żeby wyciągnąć „środek” tekstu.
WYBIERZ.KOLUMNY / WYBIERZ.WIERSZE
Co robią: Zwracają tylko wybrane kolumny lub wiersze z zakresu - w dowolnej kolejności.
Skąd wziął się problem? Masz tabelę z 20 kolumnami, a potrzebujesz tylko 5? I to w innej kolejności? Do tej pory: INDEKS z tablicą numerów kolumn, PRZESUNIĘCIE, albo po prostu kopiowanie i wklejanie. Każda metoda karkołomna na swój sposób.
Dlaczego WYBIERZ.KOLUMNY/WIERSZE zmienia grę? Wskazujesz kolumny po numerach (albo zakresach numerów). Wynik jest dynamiczny. Zmienia się kolejność w źródle? Twoja formuła nadal działa. Chcesz tylko kolumny 1, 5 i 3? Podajesz: {1;5;3}. Koniec.
Przykład z życia: Firma handlowa. Eksport z systemu ERP - 35 kolumn. Do analizy potrzebne tylko: data, klient, produkt, wartość, region. Klasycznie: ukrywanie kolumn, kopiowanie, wklejanie specjalne. Przy każdym eksporcie od nowa.
=WYBIERZ.KOLUMNY(A1:AI1000;1;5;8;12;20)
Pięć potrzebnych kolumn. Dynamicznie. Przy następnym eksporcie - ta sama formuła działa bez modyfikacji.
Jak to zrobić poprawnie?
Składnia WYBIERZ.KOLUMNY:
=WYBIERZ.KOLUMNY(tablica; nr_kolumny1; [nr_kolumny2]; ...)
Składnia WYBIERZ.WIERSZE:
=WYBIERZ.WIERSZE(tablica; nr_wiersza1; [nr_wiersza2]; ...)
Zaawansowane użycie - zakres kolumn:
=WYBIERZ.KOLUMNY(A1:Z100;1:3;10:12)
Zwraca kolumny 1-3 oraz 10-12. Nie musisz wymieniać każdej.
tags: #excel #formula #odwrocona #składnia
