10 funkcji Excela, które warto znać – przydatne triki dla analityków danych

W tym artykule:

• poznasz kilka, praktycznych funkcji w Excelu, które przydają się w codziennej pracy z arkuszami kalkulacyjnymi;
• możesz samodzielnie przećwiczyć prezentowane funkcje – wystarczy pobrać plik Excel z opisywanymi przykładami.

Chciałbym podzielić się z Wami kilkoma funkcjami Excel, z których dosyć często korzystam i które według mnie znacząco ułatwiają pracę. We wpisie znajdują się także odesłania do bardziej zaawansowanych materiałów, które opisują inne Excel funkcje.

Jedne z przedstawianych wskazówek mogą okazać się Wam znane, inne może nie. Nie będę tutaj pisał o tych najbardziej popularnych funkcjach arytmetycznych i statystycznych – spisałem je wszystkie w moim ebooku Excel – Nauka na przykładach. Więcej miejsca natomiast w tym wpisie poświęcę tym formułom i trikom, które wydają mi się mniej powszechne.

Polecam mój Excel ebook – podręcznik z setkami przykładów funkcji w Excelu. Znajdziesz w nim:

• 100+ najważniejszych formuł i funkcji w Excelu z wyjaśnieniami i przykładami zastosowań,
• kurs tworzenia tabel przestawnych i opis sposobu pracy na nich,
• 30+ trików i wskazówek ułatwiających korzystanie z Excela,
• 60+ ćwiczeń wraz z rozwiązaniami, które pokazują wykorzystanie formuł w praktycznych zastosowaniach.

Dzięki temu ebookowi skutecznie zwiększysz swoją wartość na rynku pracy. Przejdź na stronę, aby zobaczyć film i książkę od środka Zobacz szczegóły >

A skąd wiem, że są mniej znane?

Otóż bardzo często jestem pytany w pracy, jak „to lub tamto” można zrobić w Excelu…? Początkowo, przy niektórych pytaniach, aż byłem zdziwiony, jak można nie wiedzieć takiej oczywistej oczywistości. Ale z czasem już nawet najprostsze pytania przestały mnie zaskakiwać. Każda nowa wersja Excela to kolejne dziesiątki lub setki opcji i możliwości – opanowanie i zapamiętanie ich wszystkich to duża sztuka.

Excel funkcje, które warto znać

Poniżej przedstawiam skrótowe i robocze zestawienie wybranych, praktycznych funkcji i trików w Excelu, które ułatwiają analizę danych, automatyzują raportowanie, umożliwiają tworzenie szybkich obliczeń i zestawień.

Po kliknięciu w przycisk poniżej można pobrać plik z przykładami ich wykorzystania. Wystarczy kliknąć i pobrać.

UWAGA! jeżeli plik się nie otwiera lub wyskakują „krzaczki” w przeglądarce, wtedy należy kliknąć prawym przyciskiem myszki na link poniżej i wybrać opcję „Zapisz element docelowy jako...”.

Kliknij, aby pobrać plik: PLIK EXCEL – Przydatne funkcje i triki – przykłady i objaśnienia 

A oto lista praktycznych funkcji Excel:

1. Łączenie i przyporządkowywanie danych w Excelu

Chyba każdy spotkał się z funkcją WYSZUKAJ.PIONOWO. Osobiście wykorzystuję ją bardzo często do łączenia danych z różnych tabel. Jej składnia jest dosyć prosta. Wystarczy podać kolejno: szukaną frazę; tabelę danych, która będzie przeszukiwana; numer kolumny, w której znajduje się poszukiwana wartość; „Fałsz” – oznaczające, że szukamy dokładne przyporządkowania.

Dodatkowo aby w Excelu nie wyświetlały się dziwne znaczki oznaczające błąd (np. gdy szukana fraza nie znajduje się w przeszukiwanej kolumnie). Funkcję wyszukaj.pionowo wpisuję zwykle w kolejną funkcję – JEŻELI.BŁĄD(….;” ”) – dzięki niej, we wszystkich polach, dla których nie znaleziono przyporządkowania, komórka pozostaje pusta.

Niestety wadą funkcji wyszukaj.pionowo jest to, że wartość, która ma zostać przyporządkowana do szukanej frazy, musi znajdować się po jej prawej stronie w tabeli. Oczywiście można zawsze zmienić kolejność kolumn, lub zduplikować ją na końcu tabeli jeszcze raz, ale na szczęście istnieje prostsze rozwiązanie, aby poradzić sobie z tym problemem.

Jeżeli jesteś posiadaczem Office 365 lub Excel 2019 to wystarczy skorzystać z funkcji X.WYSZUKAJ(). I w jednym z parametrów można tam wskazać odwołanie nawet do kolumny po lewej.

Dla posiadaczy starszych wersji Excela wystarczy stworzyć formułę, która łączy w sobie 2 funkcje: INDEKS() oraz PODAJ.POZYCJĘ(). Dla funkcji Indeks wpisujemy: adres kolumny, w której znajduje się poszukiwana wartość; a zamiast standardowego numeru wiersza wprowadzamy funkcję Podaj.Pozycję, która dla podanej frazy zwraca jej pozycję we wskazanej kolumnie.

2. Sumowanie warunkowe w Excelu oraz inne operacje, których wynik jest uzależniony od spełnienia podanych warunków

Gdy chcemy obliczyć prostą sumę z jednym warunkiem możemy posłużyć się funkcją SUMA.JEŻELI().

Natomiast, gdy ewentualnych warunków jest więcej, wtedy najlepiej posłużyć się funkcją SUMA.WARUNKÓW(). Podajemy w niej kolejno: adres sumowanej kolumny; adres kolumny dla pierwszego warunku; kryterium dla pierwszego warunku; adres kolumny dla drugiego warunku; kryterium dla drugiego warunku; itd.

Alternatywnym sposobem obliczeń warunkowych w Excelu dla funkcji Suma.Warunków jest użycie funkcji tablicowych. Są nieco trudniejsze do użycia, ale ich przewaga polega na tym, że potencjalne działania matematyczne nie ograniczają się wyłącznie do obliczania sumy. Możemy obliczyć średnią, wartość maksymalną, minimalną, dla zmiennych spełniających kilka warunków.

Każdy warunek możemy rozdzielić albo znakiem *, który oznacza tutaj funkcję logiczną „ORAZ”, albo znakiem +, który oznacza funkcję logiczną „LUB”. Należy jednak pamiętać, że takie Excel funkcje (tj. funkcje tablicowe) należy zatwierdzić kombinacją przycisków Shift+Ctrl+Enter, w innym przypadku wyskoczy informacja o błędzie lub pojawi się nieprawidłowy wynik.

3. Sumowanie komórek z kilku arkuszy

Wielokrotnie spotykamy się z tym, że plik Excel składa się z kilku lub kilkudziesięciu identycznych arkuszy, w których podane są wartości dla poszczególnych miesięcy, lat czy grup produktowych.

Jeżeli chcemy stworzyć oddzielny arkusz, który będzie zawierał sumę poszczególnych komórek z kilku wierszy, można wtedy się posłużyć przykładowo poniższą funkcją: =SUMA(Arkusz1:Arkusz5!A4) , gdzie pierwsza część to zakres arkuszy do sumowania, a po wykrzykniku podawany jest adres komórki, który będzie sumowany.

4. Fragmentatory – czyli jednoczesne filtrowanie kilku tabel przestawnych

W Excelu mamy 2 bardzo przydatne opcje: „Wstaw fragmentator” oraz „Połączenia tabeli przestawnej”. Na pozór te mało mówiące polecenia okazują się być bardzo praktycznymi, szczególnie gdy pracuje się na kilku lub kilkunastu tabelach przestawnych połączonych pod jedno źródło danych. Dzięki nim, za pomocą kilku kliknięć, można jednocześnie przefiltrować wszystkie uprzednio połączone tabele przestawne.

Wystarczy tylko wybrać opcję „Wstaw fragmentator”, wybrać zmienne, które chcemy filtrować, a następnie zaznaczyć nowo powstały fragmentator i wybrać opcję „Połączenia tabeli przestawnej”.

Tam należy zaznaczyć wszystkie te tabele, których wartości mają się zmieniać wraz ze zmianami fragmentatora. I tyle. Zamiast filtrować każdą tabelę przestawną oddzielnie, wystarczy tylko paroma kliknięciami ustawić filtry w box’ie fragmentatora.

5. Wykresy dynamiczne – co zrobić, aby wykresy automatycznie się dostosowywały do nowo wprowadzonych wartości

Podczas aktualizowania zestawienia w Excelu pojawia się konieczność ponownego sformatowania wykresów – w tym w szczególności wybrania nowego zakresu danych. Okazuje się, że bardzo przydatną funkcją byłoby takie ustawienie wykresów, aby po dodaniu (lub usunięciu) kolejnych wartości wykres sam się dostosowywał do nowego zakresu danych. Można to osiągnąć posługując się następującym trikiem:

• wybieramy zakładkę Formuły> Menedżer nazw > Nowy…, a następnie dla każdej serii danych tworzymy oddzielną nazwę, do której przypisujemy funkcję Przesunięcie(). Dzięki niej oraz dodatkowej funkcji Licz.Jeżeli jesteśmy w stanie zaznaczyć tylko zakres danych, który nas interesuje – szczegóły w załączonym przykładzie w pliku xlsx.

• Następnie w opcjach wykresu, w zakładce edytującej serię danych wstawiamy nazwę arkusza oraz nazwę serii danych, tak jak na poniższym zdjęciu.

6. Nazywanie zbiorów danych

Przy większych analizach, gdy korzystamy z licznych źródeł danych, bardzo pomocne staje się ich nazywanie. Aby to zrobić wystarczy zaznaczyć interesujący nas zakres danych (może to być przykładowo jedna kolumna lub cała tabela) a następnie wprowadzić nazwę w lewym górnym polu arkusza (zaznaczone kółkiem na poniższym zdjęciu) i nacisnąć Enter.

Dzięki temu zamiast każdorazowo odwoływać się w funkcjach do zakresu tabeli danych, możemy tylko wpisywać jej nazwę. Skraca to znacząco wprowadzanie formuł, a także późniejsze aktualizacje. Gdy potrzebne jest późniejsze poszerzenie zakresu danych, wystarczy wtedy tylko w „Menadżerze nazw” zmienić przypisanie do danej nazwy – w wyniku tego działania wszystkie tabele przestawne oraz funkcje zaktualizują się same.

7. Łączenie komórek – tworzenie własnych identyfikatorów

Pracując na tabeli danych zdarza się, że potrzebujemy dodać do niej nową kolumnę, która jest kombinacją już istniejących kolumn lub połączeniem wartości jednej kolumny z dodatkowym znakiem lub wynikiem dowolnej funkcji. Zastosowanie ma wtedy prosty symbol „&”. Oto wybrane formuły, które mogą powstać przy jego wykorzystaniu.

• = A1&” ”&B1 -> połączenie wartości komórki A1 z B1 rozdzielone spacją
• = A1&jeżeli(B1>10;”_A”;”_B”) -> uzupełnienie komórki A1 o znacznik, które jest uzależniony od wartości komórki B1

8. Wypełnianie pustych pól w tabeli (tzw. usuwanie missingów)

Puste pola w tabeli nie raz potrafią doprowadzić do szału. Zamiast zająć się analizą najpierw trzeba poświęcić sporo czasu na uporządkowanie danych. Ale jest parę trików na to, aby robić to szybciej.

Przykładowo, jeżeli chcemy uzupełnić brakujące pola o wartość, która znajduje się powyżej w komórce, wtedy wystarczy zaznaczyć zakres kolumny do uzupełnienia, następnie nacisnąć F5 > Specjalne… > Puste. Po tej operacji zauważymy, że zaznaczone są wszystkie puste komórki w kolumnie, a dodatkowo pierwsza komórka jest aktywna – tzn. można do niej wprowadzić dowolną formułę.

Zatem wprowadzamy do niej adres komórki, która znajduje się bezpośrednio powyżej jej i naciskamy Ctrl+Enter. W rezultacie tego działania, wszystkie puste komórki, które uprzednio zaznaczyliśmy, wypełniły się identyczną formułą wskazującą na komórkę znajdującą się nad nią.

9. Jak stworzyć histogram w Excelu?

Histogram to bardzo praktyczne narzędzie statystyczne. Jest to wykres, który poszczególnym wartościom zmiennej przyporządkowuje ich liczności. Niby proste, ale jak taki histogram najszybciej wykonać w Excelu?

Zacznijmy od wypisania w kolumnie poszczególnych wartości zmiennych. Jeżeli podziałka ma być co 1, wtedy po prostu wpisujemy kolejno 1,2,3,4,5,6,7…itd. Natomiast jeżeli chcemy zbadać liczebność poszczególnych przedziałów, wtedy wpisujemy tylko górną ich granicę np.: 5,10,20,50, itd. – wtedy pierwsza podziałka będzie zliczać wszystkie wystąpienia wartości poniżej 5, druga podziałka od 5 do 10, trzecia od 10 do 20.

Dalej zaznaczamy zakres danych sąsiadujący po prawej stronie ze stworzonymi przedziałami wartości. Przykładowo, jeżeli przedziały znajdują się w kolumnie A w wierszach od 1 do 5, to zaznaczamy w kolumnie B wiersze od 1 do 5. Następnie w pierwszej aktywnej komórce wpisujemy funkcję tablicową CZĘSTOŚĆ(). A jako jej zmienne wprowadzamy badaną kolumnę danych oraz uprzednio stworzoną kolumnę z przedziałami wartości. Całość zatwierdzamy Ctrl+Shift+Enter.

Automatycznie zostaną uzupełnione pola o liczności dla wszystkich zaznaczonych komórek. Dla tak przygotowanej tabeli danych narysowanie wykresu histogramu jest już czystą formalnością.

 10. Przydatne skróty klawiszowe w Excelu

• Ctrl + „~” – aby wyświetlić wszystkie użyte w arkuszu formuły. Bardzo przydatne przy wyszukiwaniu ewentualnych błędów. Ponowne wciśnięcie tej samej kombinacji klawiszy przywraca normalny widok

• F4 – jeżeli chcemy „zablokować” zakres danych w funkcji możemy zamiast wstawiać znaki „$” przed każdą pozycją kolumny i wiersza, tylko nacisnąć F4. Jedno naciśnięcie – blokowane są kolumny i wiersze, dwa naciśnięcia F4 – blokowane są tylko wiersze, trzy kliknięcia F4 i blokowane są tylko kolumny. Przy obszernych analizach danych ta funkcja skraca zdecydowanie czas i pozwala uniknąć czeskich błędów – wstawienia $ w złym miejscu.
• Ctrl+Shift+kursor do zaznaczania zakresu komórek. Zamiast przeciągać kursorem cały zakres danych wystarczy użyć kombinacji podanych klawiszy, aby zaznaczyć interesujący nas zakres danych, tj.: całą kolumnę, cały wiersz, całą tabelę.

• Ctrl+F – skrót do opcji Znajdź, bardzo praktyczny do wyszukiwania danych w arkuszu.
• Dwukrotne kliknięcie w prawy dolny ród komórki – to nie jest skrót klawiszowy, ale pewne duże ułatwienie, o którym wiele osób zapomina. Bardzo często obserwuję, jak wiele osób daną formułę przeciąga komórka po komórce. A w wielu przypadkach wystarczy tylko dwa razy kliknąć we wskazane miejsce, a formuła zostanie automatycznie skopiowana do domyślnego zakresu danych (jest to zakres istniejącej tabeli)

• Dwukrotne kliknięcie Malarza formatów – tak samo jak w przypadku powyżej, wiele osób zapomina o tym, że nie trzeba za każdym razem kopiować i wklejać formaty. Wystarczy dwukrotnie kliknąć w Malarza formatów, a dany format zostanie zapamiętany i będzie można go zastosować do dowolnej ilości komórek. Aby następnie zakończyć wklejanie formatów wystarczy ponownie dwukrotnie kliknąć w przycisk Malarz formatów lub po prostu nacisnąć ESC.

Excel funkcje – książka z setkami przykładów

To tylko nieliczne, praktyczne Excel funkcje, które sprawdzają się w codziennej pracy. Jeszcze raz gorąco polecam mój Excel podręcznik. Jest to kompleksowe opracowanie, które zawiera:

• opisy ponad 100 funkcji Excel wraz z przykładami;
• objaśnienia pracy na tabelach przestawnych;
• kilkadziesiąt trików i wskazówek, jak radzić sobie w częstych czynnościach wykonywanych w Excelu;
• Excel ćwiczenia – 60 zadań wraz z rozwiązaniami, które pokazuje praktyczne wykorzystanie formuł.

Na uwagę zasługuje fakt, że cała książka „Excel. Nauka na przykładach” jest w formacie pliku xlsx, co znacząco ułatwia naukę. Nie trzeba kopiować lub przepisywać formuł do arkusza kalkulacyjnego. Wszystko dostajesz od razu w formacie excelowym i pdf. Dzięki temu nauka Excela jest o wiele prostsza i szybsza.

Jeżeli ciekawi Cię temat arkuszy kalkulacyjnych i chcesz wiedzieć o skutecznych sposobach nauki Excela – możliwe, że znajdziesz parę podpowiedzi w innym moim wpisie – 15 pytań i odpowiedzi dotyczących nauki Excela.