Twój szef myśli, że zna Excela. Prawdopodobnie potrafi zsumować kolumnę, narysować wykres i z pewnym wysiłkiem użyć funkcji WYSZUKAJ.PIONOWO, od czasu do czasu zwracając błędny wynik. Dla niego to szczyt możliwości arkusza kalkulacyjnego. A teraz spójrz prawdzie w oczy: to, co dla niego jest sufitem, dla Ciebie może stać się zaledwie podłogą. Istnieje zupełnie inny poziom pracy z danymi – poziom, na którym kończy się żmudne klikanie, a zaczyna elegancka automatyzacja.
Na tym poziomie, zamiast spędzać godziny na ręcznym czyszczeniu i łączeniu danych, piszesz jedną formułę. Zamiast ręcznie filtrować tabelę w poszukiwaniu odpowiedzi na kolejne pytanie szefa, tworzysz dynamiczny raport, który sam się aktualizuje. To nie jest odległa przyszłość ani umiejętność zarezerwowana dla programistów. To zbiór konkretnych narzędzi, które już teraz masz pod ręką, ukrytych w czeluściach Excela.
Badania przeprowadzone przez Acuity Training, firmę specjalizującą się w szkoleniach IT, jasno pokazują, że pracownicy z zaawansowaną znajomością Excela wykonują swoje zadania nawet 12 razy szybciej niż osoby posiadające jedynie podstawowe umiejętności. Dwanaście razy! Czas więc przestać klikać i zacząć działać jak prawdziwy analityczny ninja. Oto arsenał, który w tym pomoże.
1. Formuła TEKST.POŁĄCZ – poskłada wszystko w całość
Często masz dane w kilku kolumnach – imię, nazwisko, miasto, kod pocztowy – i musisz złożyć z nich jeden, spójny ciąg znaków, na przykład do etykiety adresowej lub unikalnego identyfikatora. Zamiast bawić się w ręczne sklejanie komórek za pomocą operatora „&”, co przy większej liczbie elementów jest drogą przez mękę, użyj formuły TEKST.POŁĄCZ (w angielskiej wersji TEXTJOIN).
Jej konstrukcja jest genialna w swojej prostocie. Najpierw podajesz znak, który ma oddzielać łączone elementy (np. spacja, przecinek, myślnik). Następnie decydujesz, czy formuła ma ignorować puste komórki (zazwyczaj chcesz, żeby to robiła), a na końcu wskazujesz cały zakres komórek do połączenia. Na przykład =TEKST.POŁĄCZ(” „; PRAWDA; A2:D2) połączy zawartość komórek od A2 do D2, oddzielając je spacją i pomijając ewentualne puste pola. Otrzymujesz czystą oszczędność czasu i nerwów.
2. Formuła USUŃ.ZBĘDNE.ODSTĘPY – wielkie porządki w danych
Dane kopiowane z innych systemów, stron internetowych czy plików tekstowych często zawierają ukryte, złośliwe spacje. Podwójne odstępy między słowami, spacje na początku lub na końcu tekstu – to wszystko sprawia, że Twoje formuły wyszukujące (jak WYSZUKAJ.PIONOWO) nie działają, a dane wyglądają nieprofesjonalnie. Zamiast ręcznie kasować każdy nadmiarowy odstęp, co jest zadaniem godnym Syzyfa, użyj formuły USUŃ.ZBĘDNE.ODSTĘPY (TRIM).
Formuła jest dziecinnie prosta – przyjmuje tylko jeden argument, czyli komórkę, którą ma oczyścić. Wpisujesz =USUŃ.ZBĘDNE.ODSTĘPY(A1) i gotowe. Formuła sama usunie wszystkie spacje początkowe i końcowe oraz zostawi tylko pojedyncze odstępy między wyrazami. Można ją nazwać cichą higienistką Twojego arkusza. Sprząta bałagan, którego często nawet nie widać na pierwszy rzut oka, ale który potrafi napsuć krwi.
3. WYSZUKAJ.PIONOWO – klasyk, którego trzeba znać i jego ograniczenia
Każdy, kto spędził z Excelem więcej niż tydzień, słyszał o WYSZUKAJ.PIONOWO (VLOOKUP). Ta formuła to absolutny klasyk do wyszukiwania danych. Pozwala odnaleźć wartość w jednej tabeli (np. cenę produktu po jego kodzie) i przenieść ją do innej. Jest użyteczna, posiada jednak kilka kłopotliwych cech, które czynią z niej narzędzie nieco archaiczne i momentami kapryśne.
Jej największą wadą jest to, że potrafi szukać tylko w pierwszej kolumnie zaznaczonego zakresu i zwracać wartości z kolumn po prawej stronie. Nie potrafi spojrzeć w lewo. Dodatkowo, domyślnie szuka przybliżonych wartości, co często prowadzi do katastrofalnych błędów. Dlatego zawsze pamiętaj, aby jako ostatni argument formuły wpisać FAŁSZ lub 0, co wymusi na niej dokładne wyszukiwanie. Szef prawdopodobnie zna omawianą formułę, ale czy wie o jej wadach i nowoczesnej alternatywie? Zapewne nie.
4. X.WYSZUKAJ – następca tronu, który deklasuje poprzednika
Jeśli WYSZUKAJ.PIONOWO było starym, wiernym, ale nieco rozklekotanym wołem roboczym, to X.WYSZUKAJ (XLOOKUP) jest lśniącym, nowoczesnym supersamochodem. Dostępna w nowszych wersjach Excela (Microsoft 356), jest odpowiedzią na wszystkie bolączki swojego poprzednika. Szuka w dowolnym kierunku – w lewo, w prawo, w górę, w dół. Jest prostsza w budowie, bo nie musisz liczyć kolumn. Po prostu wskazujesz, czego szukasz, gdzie tego szukasz i skąd ma zostać zwrócony wynik.
Dodatkowo, X.WYSZUKAJ domyślnie szuka dokładnego dopasowania, więc ryzyko błędu jest mniejsze. Ma też wbudowaną obsługę błędu #N/D!. Możesz od razu w formule określić, co ma się wyświetlić, gdy niczego nie znajdzie (np. tekst „Brak danych”), bez konieczności opakowywania jej w dodatkową formułę JEŻELI.BŁĄD. Opanowanie wspomnianej formuły stawia Cię o kilka długości przed każdym, kto wciąż męczy się ze starym, dobrym VLOOKUP-em.
5. Formuły SUMA.WARUNKÓW i LICZ.WARUNKI – inteligentne liczenie
Proste sumowanie (SUMA) czy liczenie (LICZ) to podstawy. Prawdziwa zabawa zaczyna się, gdy musisz policzyć lub zsumować coś, co spełnia określone kryteria. Szef pyta: „Ile sprzedaliśmy krzeseł obrotowych w oddziale warszawskim w zeszłym miesiącu?”. Ręczne filtrowanie i sumowanie to prosta droga do pomyłki i straty czasu. Tutaj wkraczają SUMA.WARUNKÓW (SUMIFS) i LICZ.WARUNKI (COUNTIFS).
Pozwalają one na podanie wielu par kryteriów. Możesz zsumować sprzedaż (zakres sumowania), ale tylko dla wierszy, gdzie w kolumnie „Region” jest „Warszawa” (pierwszy warunek) ORAZ w kolumnie „Produkt” jest „Krzesło obrotowe” (drugi warunek) ORAZ w kolumnie „Data” jest odpowiedni miesiąc (trzeci warunek). Możesz dodać tyle warunków, ile potrzebujesz. Dzięki nim stworzysz dynamiczne podsumowania, które robią piorunujące wrażenie, a w istocie są bardzo proste w konstrukcji.
6. Formuła JEŻELI z zagnieżdżonym ORAZ lub LUB – logiczny kombajn
Formuła JEŻELI (IF) to fundament podejmowania decyzji w Excelu. Sprawdza, czy warunek jest prawdziwy i w zależności od tego zwraca jedną lub drugą wartość. Jej prawdziwą moc odkrywasz, gdy połączysz ją z funkcjami logicznymi ORAZ (AND) i LUB (OR). Umożliwia to budowanie złożonych, wieloetapowych warunków.
Chcesz przyznać premię pracownikowi, który osiągnął sprzedaż powyżej 50 000 zł ORAZ ma ocenę satysfakcji klienta powyżej 90%? Żaden problem. Użyjesz konstrukcji: =JEŻELI(ORAZ(Sprzedaż>50000; Ocena>90%); „Premia”; „Brak premii”). Chcesz dać zniżkę klientowi, który jest stałym klientem LUB zrobił zakupy za ponad 1000 zł? Użyj funkcji LUB. Opanowanie podanych kombinacji pozwala zautomatyzować masę decyzji, które normalnie wymagałyby ręcznej weryfikacji każdego wiersza.
7. INDEKS i PODAJ.POZYCJĘ – duet dla koneserów
Zanim pojawił się X.WYSZUKAJ, zaawansowani użytkownicy Excela mieli swój sposób na ominięcie ograniczeń WYSZUKAJ.PIONOWO. Używali kombinacji dwóch formuł: INDEKS (INDEX) i PODAJ.POZYCJĘ (MATCH). Choć może wydawać się to skomplikowane, w rzeczywistości jest bardzo logiczne i daje ogromną elastyczność. Warto znać opisywaną metodę, zwłaszcza gdy pracujesz na starszych wersjach Excela.
Działa to dwuetapowo. Najpierw PODAJ.POZYCJĘ znajduje numer wiersza (lub kolumny), w którym znajduje się szukana wartość. Następnie INDEKS pobiera tę informację i zwraca wartość z komórki znajdującej się na przecięciu podanego wiersza i kolumny w określonym zakresie. Ponieważ obie funkcje działają niezależnie, możesz szukać wartości w jednej kolumnie i zwracać odpowiadający jej wynik z dowolnej innej kolumny, nawet z tej po lewej stronie. To technika starych mistrzów, która wciąż budzi szacunek.
8. Formuła UNIKATOWE – koniec z ręcznym usuwaniem duplikatów
Masz listę tysiąca transakcji i chcesz szybko uzyskać listę wszystkich unikalnych klientów, którzy dokonali zakupu? Kiedyś wymagało to użycia narzędzia „Usuń duplikaty”, które na stałe modyfikowało dane, lub skomplikowanych formuł tablicowych. Teraz wystarczy jedna, prosta formuła: UNIKATOWE (UNIQUE), dostępna w nowszych wersjach programu.
Wpisujesz w komórkę =UNIKATOWE(A2:A1000), gdzie A2:A1000 to Twoja lista klientów, naciskasz Enter i… gotowe. Excel automatycznie wyświetli listę unikalnych wartości, „rozlewając” wyniki na komórki poniżej. Funkcja działa dynamicznie, co oznacza, że jeśli na oryginalnej liście pojawi się nowy klient, automatycznie zostanie on dodany do wyniku. Czysta efektywność, która sprawi, że szef będzie przecierał oczy ze zdumienia.
9. Formuła FILTRUJ – twój własny, dynamiczny raport
To kolejne użyteczne narzędzie z rodziny formuł dynamicznych. Formuła FILTRUJ (FILTER) pozwala na stworzenie dynamicznie aktualizowanej listy danych, która spełnia podane przez Ciebie kryteria. Możesz wyciągnąć z ogromnej tabeli wszystkie wiersze dotyczące sprzedaży z konkretnego miasta lub od konkretnego handlowca, a wyniki pojawią się w nowym miejscu.
Jej użycie jest proste: =FILTRUJ(zakres_danych; warunek). Na przykład =FILTRUJ(A2:D100; C2:C100=”Warszawa”) zwróci wszystkie dane z zakresu A2:D100, ale tylko dla wierszy, w których w kolumnie C znajduje się „Warszawa”. Możesz łączyć wiele warunków, tworząc w pełni interaktywne raporty bez jednego kliknięcia w przycisk filtra na wstążce. Wyniki aktualizują się w czasie rzeczywistym, gdy zmieniasz dane źródłowe. To narzędzie zmienia zasady gry w raportowaniu.
10. Formuła PRZESUNIĘCIE – tajna broń do tworzenia dynamicznych zakresów
Na koniec coś dla prawdziwych adeptów sztuki ninja – formuła PRZESUNIĘCIE (OFFSET). Jest ona nieco trudniejsza do opanowania na początku, ale jej możliwości są ogromne. Zamiast zwracać konkretną wartość, zwraca odwołanie do zakresu komórek, który jest dynamicznie określany na podstawie podanych argumentów.
Możesz jej użyć do tworzenia zakresów, które automatycznie się powiększają, gdy dodajesz nowe dane. Jest to niezwykle przydatne przy tworzeniu dynamicznych wykresów, które same aktualizują swój zakres danych, lub przy budowaniu list rozwijanych, które czerpią wartości z dynamicznie rosnącej tabeli. Opanowanie formuły PRZESUNIĘCIE to jak zdobycie czarnego pasa w Excelu. Twój szef prawdopodobnie nigdy o niej nie słyszał, a Ty będziesz mógł tworzyć rozwiązania, które wydają się działać „same z siebie”.
Czas ruszyć do walki – i co dalej?
Każda z poznanych formuł to narzędzie, które w odpowiednich rękach potrafi ciąć czas pracy i eliminować błędy z precyzją lasera. To podstawa, która już teraz stawia Cię daleko przed korporacyjną średnią. Jednak arsenał analitycznego wojownika jest znacznie większy.
Pomyśl o tym, co właśnie poznałeś, jako o początku drogi. Drogi do całkowitego zapanowania nad danymi. Wkrótce zajmiemy się tworzeniem interaktywnych dashboardów, które sprawią, że Twoje raporty będą wyglądać jak centrum dowodzenia NASA. Pokażemy, jak za pomocą Power Query możesz zautomatyzować 80% procesów importowania i czyszczenia danych, raz na zawsze zapominając o metodzie „kopiuj-wklej”.
Śledź nasz blog, aby nie przegapić kolejnych wpisów z tej serii.
