10 funkcji w Excelu do analizy danych, które skutecznie ułatwią pracę

Patrząc na świat aplikacji niejednokrotnie można dostać oczopląsu od nadmiaru opcji do wyboru. Nie inaczej sprawa wygląda w świecie analizy danych lub raportów. Dodatkowo, część rozwiązań ma zastosowanie do bardziej zaawansowanych operacji, które dla wielu mniejszych firm lub ogranizacji nie są czymś koniecznym. Jeśli jesteś w takiej grupie, albo potrzebujesz od czegoś zacząć, to warto najpierw sięgnąć do podstaw. W tym artykule przyglądnę się, jakie funkcje w Excelu wybrać do analizy danych na dobry początek. Narzędzie to od lat jest rozwijane i w wielu przypadkach po prostu robi swoje 😀 Zobaczmy zatem co można szybko ulepszyć. Zapraszam do lekutry!

Dlaczego Excel jest lepszy?

Dlaczego Excel a nie inne super i „coolowe” aplikacje? Po pierwsze arkusze kalkulacyjne są powszechne. Praktycznie każdy, kto skończył szkołę, uczelnie lub ma styczność z pracą biurową zetknął się z Excelem. Mogło to być ewentualnie Google Sheets czy Open Office. Co więcej dla wielu osób to program używany na co dzień. Jeśli natomiast przygotujecie cos fajnego w mniej znanym narzędziu, to zapewne dojdzie bariera poznawcza. Będzie ona szczególnie widoczna, gdy postanowicie się z kimś podzielić waszą pracą. Jeszcze bardziej sprawa może się skomplikować, jeśli trzeba będzie coś poprawić, odesłać itd. Nawet jeśli to narzędzie będzie za darmo to trzeba je pobrać, zainstalować, coś doczytać lub tp. A to już zjada nasz czas i jak i energię.

Dochodzi też do tego wiedza i umiejętności. Im bowiem bardziej popularne narzędzie, tym większa jest szansa, że znajdziecie osoby które wam pomogą z jakim problemem. Można tu zaliczyć także fora internetowe lub filmiki instruktażowe na You Tube. Poza tym, jeśli poznacie pewna „logikę” przygotowania i obróbki danych w Excelu, to łatwiej będzie wam poznać inne narzędzia. Pewne mechanizmy lub tematy okażą się bowiem uniwersalne, niezależnie od tego z jakim programem pracujemy. Oczywiście rozwiązania, czy pewne możliwości już będą się różnić, ale problemy na które natrafimy bedą podobne. Zatem poznając funkcje do analizy danych w Excelu robicie pewien bardziej uniwersalny krok, który pomoże w przyszłości.

Przy okazji jeśli jesteeś na etapie obymślania kluczowych metryk (KPI) dla swojej działalności, to zapraszam również do innych moich postów. Najlepiej zacząć od wprowadzenia czyli – Z KPI przez życie (LINK).

Pakiet podstawowy – pierwsza pomoc

Zanim przejdę do sedna jest jeszcze kilka uwag. Po pierwsze, poniższe tematy to zbiór funkcji w Excelu, od których dobrze jest zacząć lub mieć opanowanych, jeśli zabieramy się za analizę danych. W tym przypadku będzie to głównie przygotowanie danych. W przyszłości przejdziemy do innych tematów pozwalających na analizę sensu stricte, jak tabele przestawne lub wykresy. A jeśli chcielibyście zobaczyć pęłną listę formuł w Excelu z krótkimi opisami możecie jąznaleźć przykładowo na oficjalnej stronie Microsoftu (link do strony).

Kolejna rzecz to kompleksowość. Pewne rzeczy zapewne można by opisać szerzej, ale artykuł ten ma przede wszystkim stanowić punkt startowy. Niektóre niżej opisane funkcje, jak choćby VLookUp potrzebują nieco czasu i oswojenia i można by pisać o nich więcej. Jako pomoc załączam również plik, gdzie pokazane są wszystkie poniższe przykłady. Tam też możecie dodatkowo poćwiczyć 🙂

Jak każda lista także i ta będzie do pewnego stopnia subiektywna, aczkolwiek jest poprzedzona przeglądem w internetach i własnym doświadczeniem 🙂 Poza tym jak zobaczycie określenie funkcja używam tu dość szeroko, a więc nie będą to tylko tzw. formuły. Co ważne również, celowo na razie pomijam temat rozszerzeń do Excela, a więc power pivot czy power query. Bierzemy zatem na tapetę funkcje do analizy danych w „czystym” Excelu.

Pozostaje jeszcze uwaga językowa. Nazwy wymieniam po angielsku i po polsku, ale zrzuty ekranowe będą już po angielsku, gdyż takiej wersji używam na co dzień. Co do składni będzie ona tak czy inaczej taka sama. Także jakiejkolwiek wersji językowej używacie, powinno być ok.

A tu jeszcze Bonusowy Excelowy z poniższymi przykładami 🙂

Przygotowanie danych – podzielmy się na grupy

Jak wspomniałem wcześniej w zestawieniu skupię się na przygotowaniu danych. A bardziej szczegółowo, poniże fukncje podzielę na grupy. Pierwsza to operacje na istniejących danych, pozwalające lepiej je przygotować do dalszej obróbki lub analizy. Poza tym będzie coś o czasie. Druga grupa to trzy funkcje warunkowe (Jeśli czyli IF), które pomogą nam wyliczyć dodatkowe kolumny lub policzyć sumy według wybranych kryteriów. I ostatnia część, a właściwie pojedyncza funkcja (Wyszukaj.Pionowo czyli VlookUp) to sytuacja, kiedy potrzebujemy połączyć dwie lub więcej tabel w oparciu o wspólny mianownik. Pozwoli to na szybkie wzbogacenie naszej „bazy” bez manualnych poprawek. No to zaczynajmy!

1) ZLACZ.TEKSTY czyli CONCATENATE

Polskie tłumaczenie mówi już dużo 🙂 A pewnie jeszcze więcej pokaże poniższy zrzut ekranu. Jest to dość prosta, ale bardzo przydatna formula. Dosłownie łączymy tekst z dwóch lub więcej różnych komórek. Co więcej, możemy też dodać jakiś stały element przy okazji. Przykładowo poniżej została dodana spacja pomiędzy imieniem i nazwiskiem. Jest do widoczne w składni formuły w postaci cudzysłowu. Dzięki temu mamy imię i nazwisko pisane oddzielnie.

2) TEKST jako KOLUMNY czyli TEXT to COLUMN

W odróżnieniu od większości przytoczonych tu przykładów jest to wbudowana funkcja, która uruchamiamy z meny na górze (Dane – Tekst jako kolumny). Czasem zamiast „wyliczać” coś potrzebujemy podzielić tekst. Poniżej opisuje funkcje LEWY i PRAWY, dzięki którym możemy odcinać cześć tekstu. Jednak mają one swoje ograniczenia. Jednym z nich jest sytuacja, kiedy potrzebujemy rozdzielić tekst, ale nie chcemy nic stracić. Dodatkowo chcemy, żeby każda cząstka byłą umieszczona w oddzielnej kolumnie. W przypadku Tekst jako kolumny jest to możliwe. Najlepiej spójrzcie na przykład poniżej. Kluczowy jest tzw. ogranicznik (delimitator), który pozwala nam powiedzieć, po czym mamy oddzielić poszczególne części. Tu jest to tzw. myślnik lub półpauza czyli „-„. Dodatkowo, nie musimy się przejmować długością poszczególnych członów (tu imion), gdyż Excel idzie od ogranicznika do ogranicznika i rozdziela wszystko do osobnych kolumn (poniżej „niebieskie” komórki). Ten rodzaj funkcji możemy użyć także w kilku innych przypadkach, ale najszybciej zyskacie jak opanujecie ten podstawowy wariant.

Jeśli szukacie tej fukncji, to musicie pójść do zakładki Dane i tam już bezpośrednio klikacie na odpowiednią ikonkę, jak poniżej (w wersji ang. to Text to Column). Wcześniej jednak musicie zaznaczyć komórki, na których chcecie przeprowadzić taką operację.

Następnie zostanie otworzone okno robocze. Wybieracie opcję jak poniżej (Rozdzielany/ Delimited) i klikacie przycisk „Następny” (Next).

Następnie wybieramy typ ogranicznika. Może być z listy, lub jak w tym przypadku zaznaczamy „Inne” (Other) i wpisujemy z ręki „-„. W ten sposób okienko poniżej pokazuje nam podgląd, jak będzie wyglądać nasz podział. Zatwierdzamy poprzez „Następny” (Next) i możemy jeszcze ewentualnie wybrać czy podzielone komórki zapisujemy w inny miejscu, czy (domyślnie) nadpisujemy istniejące. W naszym przypadku wbyrałem, aby podział nastąpił obok, czyli widzimy stan początkowy a obok wynik działania funkcji.

3) LEWY, PRAWY, FRAGMENT.TEKSTU czyli odpowiednio LEFT,RIGHT i MID

Koeljny punkt to powiedzmy grupa funkcji, które jednak zachowują się bardzo podobnie. Służą one od odcinania pożądanej część tekstu, tak żeby zostało to co nas interesuje. Dwie pierwsze działają identycznie, tyle że raz od lewej, raz od prawej. Jak to śpiewali…Prawy do Lewego, wypij kolego 😉 Może to o tych funkcjach? Hm? 😉  No dobra, ale serio o co w tym chodzi? Nieraz jest tak, że potrzebujemy wykorzystać tylko część tekstu i trzeba niejako „odciąć” coś, będzie nam potrzebne. Za pomocą funkcji ustalamy, ile znaków (licząc od lewej lub prawej) chcemy zostawić. Przykład zamieszczam poniżej. W obu przypadkach „wyciągamy” tylko kod, a więc 4 znaki.

W tej samej „rodzinie” mamy również FRAGMENT.TEKSTU czyli MID. Działanie jest analogiczne jak LEFT i RIGHT, ale tekst ucinamy zaczynając od środka. W tym przypadku mówimy Excelowi, w którym miejscu ma zacząć a następnie ile znaków chcemy zwrócić. Stąd w funkcji są 3 argumenty do wprowadzenia a przy LEWY i PRAWY tylko dwa. Widać to w składni formuł.

4) Formatowanie daty

Data, obliczanie czasu to ogólnie szeroki temat. Jest dużo możliwości wyświetlania daty i tego jak liczymy i jak ją wyświetlamy. Na pewno nieraz ważne będzie ustawienie formatowania dat. Można to zrobić poprzez zwykłe formatowanie komórek (skrót Ctrl +1). Tu jednak bardzo ważna uwaga, szczególnie jeśli chcecie wykorzystywać daną kolumnę do obliczeń czasu. To co dla nas może się wydawać datą, dla Excela niekoniecznie nią będzie. Formatowanie a także obliczenia czasu możemy stosować tylko, jeśli Excel rozpoznaje coś jako datę lub czas. Inaczej traktuje to jako zwykły tekst.

Przykładowo poniżej tylko komórka zaznaczona na zielono nosi w sobie datę.  Co ma na to wpływ? Z uwagi na to, iż w moim Windowsie mam tzw. ustawienia regionalne angielskie, to format daty, jaką Excel oczekuje to MM/DD/RRRR (zobaczcie na kolejny zrzut ekranu). A wiec pomimo tego, że dwa pierwsze wiersze wyglądają na daty, to tylko druga komórka wpisuje się w oczekiwany format. A co z pierwszą? Zaczyna się ona od „15”, podczas gdy Excel spodziewa się tu znaleźć miesiąc, a więc liczby 1-12. z Tego powodu jest traktowana jako tekst. Pewnym ułatwieniem, jeśli robimy szybki test, będzie to, że data jest wyrównywana automatycznie do prawej. Wówczas możemy „na oko” zobaczyć, jak Excel to widzi.

To jakie mamy ustawienia daty warunkują nasze ustawienia w systemie.  Poniżej zrzut z Windowsa (ustawienia regionalne), gdzie widzimy jakiej daty spodziewa się Excel. Jeśli chcecie to zmienić, to należy wybrać inny kraj z listy i zatwierdzić. W ten sposób zmienimy również format daty, którą później będzie oczekiwał Excel.

Teraz jeśli zaznaczę wszystkie 3 komórki i zmienię formatowanie dat, przykładowo jak poniżej:

To w praktyce tylko zielona komórka ulegnie jakiejkolwiek zmianie, pozostałe dwie są traktowane jako tekst, a więc w żaden sposób Excel ich nie zmienia czyli nie formatuje.

5) DNI.ROBOCZE czyli NETWORKDAYS

Czas to ogólnie duży temat, nie tylko w życiu 😀 Można by sporo pisać, ale na ten moment polecam jeszcze poniższą funkcję, a na pewno prędzej czy później przyda się. Liczy ona różnicę pomiędzy dwoma datami, ale bierze pod uwagę tylko dni robocze tj poniedziałek-piątek. Co więcej można tak ją ustawić, że do weekendów jako wyłączenia dodamy również święta, które oddzielnie wpiszemy. To trzeci argument, który jest jednak opcjonalny i tutaj nie użwaym go. Poniżej widać różnicę w liczeniu dni w kolumnie D i E – zwykła różnica i ta wyliczona za pomcą DNI.ROBOCZE.

6) USUŃ.ZBĘDNE.ODSTĘPY czyli TRIM

Nieraz zdarza się, że dane z jakiegoś systemu lub źródła zawierają niepotrzebne spacje. Niekiedy są one tym bardziej widoczne, jeśli łączymy je (ZLACZ.TEKSTY) z innymi komórkami. Wówczas zamiast usuwać takie niepotrzebne spacje ręcznie, możemy zastosować powyższą funkcję. Pamiętajmy również, że dla Excela spacja to znak jak każdy inny.  Może to mieć czasem znaczenie, jeśli budujemy formuły, gdzie wstawiamy np. szukane słowo. A więc pozornie dla nas coś może wyglądać tak samo, ale dla Excela już nie będzie. Spacja to zatem pełnoprawny znak, chociaż dla nas niewidoczny 😉

7) JEŻELI czyli IF

Typowa warunkowa funkcja. Jeśli uczycie się jakiegokolwiek obcego języka przychodzi moment, że pojawiają się konstrukcje warunkowe, takie jak „Jeśli wygrałbym w totka to pojechałbym na Hawaje”. To znacznie zwiększa nasze możliwości komunikacyjne 🙂 Podobnie jest w tym przypadku. Funkcja JEŻELI pozwala nam ustawić warunek i 2 możliwe scenariusze, kiedy warunek jest spełniony lub nie. Poniższy przykład pokazuje „Dużo”, jeśli liczba dni w kolumnie D jest większa od 2 i „Mało” jeśli jest ona mniejsza lub równa 2. Na wyższym etapie wtajemniczenia funkcje można zagnieżdżać, a więc mamy funkcje w funkcji i tych warunków i różnych wariantów jest znacznie więcej. Ale jak ze wszystkim, zacznijcie od postaw 🙂

8) SUMA.JEZELI czyli SUMIF

Oprócz standardowej Sumy dla danej kolumny/wiersza przydatne będzie również sumowanie warunkowe. Przykładowo chcemy policzyć sumę sprzedaży tylko dla działu Zabawki, ale nie chcemy „naruszać” naszej tabelki tj. nic w niej usuwać. Pozwoli nam to dodać tylko pozycję (sumę) spełniającą dane kryterium.

Ma ona 2 odmiany. Prostsza (zawiera 2 argumenty) to wtedy, kiedy sumujemy wartości spełniające dane kryterium – np. sumuj jeżeli wartość jest większa niż X. Na poniższym zrzucie z ekranu to sekcja niebieska. Druga jest nieco trudniejsza (3 argumenty), ale też znacznie ciekawsza. W tym przypadku funkcja sprawdza daną kolumnę zawierająca np. tekst i jeśli warunek jest spełniony wówczas sumuje coś z innej. Ten przykład odzwierciedlają komórki zaznaczone na zielono.

9) LICZ.JEZELI czyli COUNTIF

Analogicznie jak powyżej działa funkcja licząca wystąpienie określonego argumentu w danym obszarze. W porównaniu do Sumuj. Jeżeli mamy jednak do wyboru tylko opcję prostą, a więc formuła z dwoma argumentami. W tym przypadku nie chcemy zatem zliczyć np. wszystkie wiersze w danej kolumnie, ale tylko te które spełniają dane kryterium. Tak jak w poniższym przykładzie – pokaż ile rekordów dotyczy działu „Południe”.

10) WYSZUKAJ.PIONOWO czyli VLOOKUP

Bardzo często zdarza się, że pracując z danymi będziemy potrzebowali połączyć 2 lub więcej tabel i tu Wyszukaj.Pionowo czyli VLookUP jest nieoceniony. To coś, co zmienia życie 😀 Chociaż jak wszystko ma także swoje ograniczenia. Żeby zrozumieć w skrócie, o co w tym wszystko chodzi popatrzcie na poniższą prostą tabelkę. Tabela A zawiera pewne dane (imię i miasto), ale potrzebujemy także daty urodzenia, co znajduje się w tabeli B. Za pomocą funkcji VlookUp można bardzo szybko „dociągnąć” brakujące informacje. Ale tylko wówczas, gdy mamy wspólną część, tj kolumnę, która pozwoli połączyć oba zbiory. W tym przypadku to imię. Oczywiście poniższe tabelki maja tylko po kilka wierszy, ale jeśli mamy do czynienia z setkami czy tysiącami wierszy to VLOOKUP ratuje nam życie 🙂 Poza tym jak widać też na tym przykładzie imiona nie muszą wystąpić w tej samej kolejności.

Składnie na powyższym przykładzie można opisać w taki oto sposób. Weź imię z kolumny B, następnie szukaj go w kolumnie G i jeśli znajdziesz to zwróć informację z kolumny H z tego samego rzędu (druga kolumna licząc od G – stąd w formule trzeci argument to 2). Weź pod uwagę tylko dokładne dopasowania (w składni odpowida temu 0).

Powyższa funkcja ma też odmianę Wyszukaj.Poziomo czyli HlookUP. Działa analogicznie ale patrzymy nie na kolumny, ale na wiersze. Z uwagi jednak na charakter typowej tabeli (kolumny z nagłówkami) to raczej VlookUp będzie znacznie bardziej przydatny. Dobrze jednak wiedzieć, żę w razie czegoś Excel ma również taką formułę w pogotowiu. Poza tym zapraszam też do oddzielnego artykułu poświęconemu funkcjom Wyszukaj (Pionowo/Poziomo i X).

Coż…to tyle na ten moment. Bądźcie czujnie bo będzie wiecej! 🙂 Pozdrawiam i życzę owocnych ćwiczeń!