Funkcje Excela, co muszę umieć z Excela? – top 13

Co muszę umieć z Excela? – 13 funkcji Excela

Każdy, pracujący w Excelu musi umieć obsługiwać podstawowe funkcje Excela. Bez znajomości ich obsługi ciężko jest przebrnąć rozmowę o pracę, a jeżeli to się jakimś cudem uda, praca etatowa z arkuszami kalkulacyjnymi będzie katorgą. W tym wpisie dowiesz się, co musisz wiedzieć, aby było Ci łatwiej zawodowo używać Excela.

Skróty klawiszowe w Excelu

W pierwszej chwili możesz pomyśleć, że to dla zaawansowanych użytkowników Excela. Przecież na początku trzeba wiedzieć, co i gdzie kliknąć, aby dopiero potem uczyć się robić to szybciej klawiaturą – nic bardziej mylnego. Skróty pracy na obszarach, takie jak:

• wciśnij CTRL + STRZAŁKI — ustaw się w komórce przed następną pustą komórką w dół / górę / prawo / lewo
• użyj CTRL + SHIFT STRZAŁKI — zaznacz w dół / górę / lewo / prawo do komórki przed pustą komórką
• wciśnij CTRL + SPACE — zaznacz kolumnę
• SHIFT + SPACE — zaznacz wiersz

to skróty, które każdy, pracujący w Excelu musi znać. Wyobrażasz sobie zaznaczanie nawet 1 000 wierszy, używając myszy?

Warto nauczyć się też skrótów szybkiego formatowania danych:

• użyj CTRL + SHIFT  + 1 – format liczbowy
• wciśnij CTRL + SHIFT + 2 – format godziny
• użyj CTRL + SHIFT + 3 – format daty
• wciśnij CTRL + SHIFT  + 4 – format waluty
• użyj CTRL + SHIFT + 5 – format procentowe

Wystarczy zaznaczyć dane skrótami z pierwszej grupy i nałożyć formatowanie odpowiednią kombinacją z grupy drugiej.
Warto też przyjrzeć się skrótom filtrowania czy blokowania komórek (F4), a więcej przydatnych skrótów znajdziesz w kursie nauki Excela od podstaw (kliknij tu)

Formatowanie warunkowe w Excelu

Narzędzie Excela, które służy do oznaczania wartości, aby szybciej można było skupić się na istotnych pozycjach w arkuszu kalkulacyjnym.

W opcjach formatowania warunkowego znajdziemy następujące grupy oznaczeń danych:

• reguły wyróżniania komórek, które służą do podświetlania wartości większych i mniejszych od zadanych, dat z przedziału, tekstu czy duplikatów,
• reguły pierwszych/ostatnich służą do oznaczania najwyższych i najniższych wyników czy wartości pod i nad średnią,
• paski danych reprezentują wynik poprzez długość paska w komórce,
• skale kolorów pozwala regulować odcień tła komórki względem jej zawartości,
• zestawy ikon umożliwiają sygnalizowanie zawartości komórki poprzez małe grafiki np. kulki w kolorach, gwiazdy.

Formatowanie warunkowe aktywuje się na bieżąco przy każdym przeliczeniu, a więc świetnie sprawdza się, gdy wklejasz nowe dane czy dokonujesz edycji już istniejących.

Na szczególną uwagę zasługuje wątek oznaczania duplikujących się wartości, ale o tym w następnym akapicie.

Praca z duplikatami w Excelu

Duplikaty utrudniają pracę z funkcjami zliczania czy wyszukiwania, a więc każda tabela powinna mieć kolumnę klucza pierwotnego. Charakteryzuje się on dwoma cechami:

• nie zawiera duplikatów
• nie zawiera pustych komórek

Kolumna ta, to często np. ID zamówienia, ID produktu, NR PESEL.
Dzięki unikatowości dla wierszy, kolumna taka świetnie sprawdza się jako pomost do doszukiwania danych z innych tabel.
Jeśli chcesz sprawdzić czy w zestawieniu występują duplikaty, zaznacz kolumnę, użyj opcji podświetlania duplikatów. Excel nie rozstrzyga która z pozycji jest oryginalna, podświetla wszystkie takie same pozycje w zaznaczonym wcześniej obszarze.

Mając tak oznaczone duplikaty, może je łatwo wyfiltrować przy użyciu opcji filtra.
Usuwanie duplikatów może zostać przez Ciebie wykonane przy użyciu opcji „Usuń duplikaty” z karty „Dane”.

Usunięciu ulegnie drugi i każdy kolejny napotkany duplikat. Możesz zdecydować, czy usunąć tylko komórkę kolumny z duplikatami czy też cały wiersz tabeli – nadal bazując na podstawie jednej komórki.

Odwołania do komórek w Excelu

To fundament pracy z formułami w Excelu, więc bez znajomości sposobów odwoływania się i blokowania adresów komórek ciężko będzie Ci skonstruować funkcje Excela, które po przeciągnięciu do większego obszaru, będą działać prawidłowo.

W Excelu występują 4 główne typy odwołania do komórek, wliczając w to blokowanie całkowite i częściowe.

Oto ich przykłady:

•  A1 to komórka odblokowana co powoduje, że przy kopiowaniu formuły z takim odwołaniem, adres będzie się zmieniał razem z miejscem wklejenia formuły.
• $A$1 to zablokowanie całkowite, a więc formuła zawsze będzie odwoływała się właśnie do tego adresu. Aby wywołać takie blokowanie, użyj raz F4 po wpisaniu adres (w przypadku laptopów FN+F4).
• A$1 to zablokowanie wiersza, bo symbol dolara przy wierszu oznacza, że podczas kopiowania komórki, kolumna będzie się zmieniać, ale zawsze będziemy odwoływać się do zadanego wiersza. Aby wywołać takie blokowanie, użyj dwa razy F4 po wpisaniu adres (w przypadku laptopów FN+F4).
• $A1 to zablokowanie kolumny, bo symbol dolara przy kolumnie oznacza, że podczas kopiowania komórki, wiersz będzie się zmieniać, ale zawsze będziemy odwoływać się do zadanej kolumny. Aby wywołać takie blokowanie, użyj trzy razy F4 po wpisaniu adres (w przypadku laptopów FN+F4). Aby z powrotem odblokować komórkę, wciśnij odpowiednią liczbą razy F4 (lub FN+F4).

Funkcje Excela i odwoływanie między plikami i arkuszami

To temat na pozór zbliżony do blokowania zakresów – chodzi o sytuacje, w której musisz pobrać dane z innego arkusza lub pliku.

Funkcje Excela a odwołanie między arkuszami

Funkcje Excela mają możliwość odwoływania się do innego arkusza, wystarczy wpisać  =, przełączyć kartę i wskazać obszar, który Cię interesuje. Założmy, że będzie to obszar od A1 do D10 arkusza „Dane”, a więc Twoim oczom ukaże się adres:
=Dane!A1:D10
Dane! symbolizuje nazwę arkusza – uwaga! – gdyby nazwa arkusza zwierała znaki typu spacja, adres wyglądałby tak: 'Dane 1′!, a więc pojawiłby się apostrofy.
Zwróć też uwagę, że adres A1:D10 jest odblokowany.

Funkcje Excela a odwołanie między plikami

Tutaj temat na wysokości zaznaczania obszarów działa bardzo podobnie. Wybierasz =, przełączasz się do innego pliku, z którego chcesz dane i zaznaczasz obszar. Pamiętaj o tym, aby wcześniej otworzyć wszystkie pliki, z których chcesz zaciągnąć w ten sposób dane.
Założmy, że odwołujesz się do danych pliku Raport_Sprzedaży, arkusza Dane, obszaru A1:D10. Adres będzie wyglądał tak:
=[Raport_Sprzedaży.xlsx]Dane!$A$1:$D$10
to, co w nawiasach kwadratowych, to nazwa pliku wraz z rozszerzeniem. Pozostałą część na pewno już rozpoznajesz. Adres wygląda tak dopóki plik, z którego pobierasz dane jest otwarty.
Gdy go zamkniesz, ukaże się:

=’C:\Users\M\Downloads\[Raport_Sprzedaży.xlsx]dane’!$A$1:$D$10

To, co [Raport_Sprzedaży.xlsx] to pełna ścieżka miejsca, w którym spoczywa plik.
Zwróć uwagę, że gdy zaznaczasz dane między plikami, zakres $A$1:$D$10 jest domyślnie zablokowany. Nie miało to miejsca przy odwołaniach między arkuszami tego samego pliku.

Funkcje Excela w pracy z czasem

Bez względu na stanowisko czas, to coś, z czym prędzej czy póżniej przyjdzie Ci się mierzyć. Data dostawy, termin płatności faktury, data końca zwolnienia lekarskiego, dzień płatności wynagrodzenia – wszystko to wiąże się z operacją na funkcjach czasu. Warto zacząć od zrozumienia żelaznej zasady pracy z datami w Excelu:
Excel traktuje daty jako liczba dni, liczone od 1 stycznia 1900 roku. Każdy dzień to 1, każda godzina to 1/24, a minuta 1/1440. Korzystając z tej zależności możesz dodawać i odejmować czas. Warto znać minimum te kilka funkcji:

• DZIEŃ,
• MIESIĄC,
• ROK,
• NUM.TYG,
• DATA,
• DNI.ROBOCZE,
• DZIEŃ.ROBOCZY.

Dzięki nim będzie Ci znacznie łatwiej mierzyć się i z testami z rozmów o pracę, i ze swoimi obowiązkami w pracy zawodowej.

Tekstowe funkcje Excela

Jeśli potrzebujesz wyciągnąć z komórki pewien fragment danych, tekstowe funkcje Excela to coś, z czym musisz się zaprzyjaźnić. Opisałem je szczegółowo w tym wpisie (kliknij tu), ale i tu nie zostawię Cię z niczym. Bardzo ważna jest znajomość funkcji:

• LEWY,
• PRAWY,
• FRAMGENT.TEKSTU,
• ZNAJDŹ,
• DŁ,

oraz zagnieżdzanie ich jedna w drugiej, co pozwala wyciągać dane z komórek w zupełnie nowy sposób. Więcej we wpisie poświęconym tematowi funkcji tekstowych Excela o tutaj.

Logiczne funkcje Excela

Funkcje te służą do tworzenia pierwszych, podstawowych algorytmów decyzyjnych w Excelu. Za pomocą funkcji taki jak JEŻELI, ORAZ czy LUB możesz sprawdzać jeden lub więcej warunków i gdy są spełnione (lub nie) zwracać konkretny wynik. Prostym przykładem będzie ocena, czy handlowcowi należy się premia. Ma zostać naliczona, gdy jego sprzedaż w miesiącu przekroczyła zadany dla niego próg, ale co, jeśli w firmie pracuje 100 handlowców. Sprawdzenie tego linijka po linijce zajmie dużo czasu, więc z pomocą przychodi prosta funkcja JEŻELI, w której sprawdzimyu czy sprzedaż jest większa od progu. Jeśli tak, funkcja ma zwrócić wartość premii, a jeśli nie, funkcja powinna zwrócić 0.
To oczywiście bardzo podstawowy przykład. Zagnieżdżając w JEŻELI funkcje ORAZ czy LUB można budować znacznie bardziej złożone algorytmy sprawdzające, dlatego warto umieć stosować te funkcje.

Funkcja Excela do filtrowania i sortowania

Gdybyś wybierał się do pracy jako murarz i nie wiedział, jak zrobić beton, to właśnie byłbyś kimś, kto chce pracować w Excelu, a nie zna opcji filtrowania i sortowania. Filtr można podzielić na 4 rodzaje:

• liczb,
• dat,
• tekstu,
• kolorów.

Pierwszy z nich umożliwia nam np. filtrowanie najwyższe / najniższe wartości czy zakresy pomiędzy dwoma liczbami.
Filtr dat pozwala wykonać filtrowanie np. na YTD czyli Year to Date – daty od początku roku do dziś, a to często spotykany sposób raportowania wyników.
Filtr tekstu umożliwa między innymi wyciągnięcie z tabeli danych, zawierających konkretne słowo. W jednej z firm, w której szkoliłem, w ten sposób wydzielano produkty wybranej kategorii.
Filtr kolorów świetnie współgra z formatowaniem warunkowym, ale przydaje się też, gdy chcesz oznaczyć kolorem kilka wierszy, a potem je wyfiltrować.

W filtrze możemy znaleźć również opcję sortowania. Pozwala ona na zamianę kolejności danych w tabeli względem naszych preferencji.
To tylko wierzchołek możliwości, jakie daje opcja filtra w Excelu. Przyglądnij się koniecznie tej opcji!

Praca z pustymi wierszami w Excelu

To właśnie obsługa pustych wierszy często oddziela tych, którzy pracowali w Excelu od tych, którzy dopiero mają w planie. Oto kilka powodów, dla których warto pozbywać się pustych wierszy z tabeli danych:

• po założeniu filtra dane pod pustym wierszem nie są nim objęte,
• założenie tabeli przestawnej powoduje, że domyślnie proponowany przez Excela zakres nie obejmuje danych pod pustym wierszem,
• szybkie przeciąganie formuł przy użyciu tego małego, zielonego kwadracika w prawym dolnym rogu komórki powoduje, że formuły zostają przeciągnięte tylko do pustego wiersza.

To oczywiście nie wszystkie problemy, jakie generują puste wiersze. Usunąc je możesz na kilka sposobów. Między innymi:

• zaznaczając najpierw obszar, potem zakładając filtr, filtrując na puste wiersze i usuwając je
• korzystając z sortowania
• korzystając z menu *Idź do specjalnie*

Warto pamiętać, aby sprawdzić dane, na których przyjdzie nam pracować,  szczególnie na rozmowie o prace.

Tabele przestawne w Excelu

Świetne narzędzie do szybkiego agregowania danych, a szczegółowe informacje jak się nią posługiwać znajdziesz o tutaj (kliknij tu)
W dużym skrócie musisz mieć źródło danych, na którym zbudujesz tabelę przestawną.
Sama konstrukcja analizy to już tylko przeciąganie odpowiednich nagłówków kolumn do odpowiednich pól.   
Dane w wierszach oraz kolumnach agregują się według kryteriów i zostają im przypisane liczby. Pole filtra służy do ograniczenia naszej analizy do np. konkretnego kraju czy kategorii. Wszystkio zależy od układu danych.

Funkcje warunkowego zliczania w Excelu

Koniecznie przyglądnij się funkcją takim, jak:

• SUMA.WARUNKÓW
• LICZ.WARUNKI
• ŚREDNIA.WARUNKÓW

Te funkcje Excela na pierwszy rzut oka przypominają Tabele Przestawne i faktycznie mają z nimi wspólną cechę: agregują dane. Jeśli chcesz policzyć wyniki dla konkretnych kategorii albo zliczyć wystąpienia zamówień w przedziale czasu, konieczie przyglądnij się tym funkcją. Świetnie sprawdzą się jeśli dość sztywny układ Tabeli Przestawnej jest dla Ciebie zbyt ograniczający. Formuły możesz kształować według własnych potrzeb i tworzyć z nich raport w takim kształcie, na jakim Ci zależy.

Graficzna prezentacja danych w Excelu

W pierwszej chwili do głowy powinny przyjść Ci wykresy i pytanie w stylu: jak je sforatować, jak dodać etykiety czy linię trednu? To tylko kilka, które trzeba sobie zadać, a najważniejsze z nich to: jaki wykres dopasować do danych? Najpopularniejszymi w Excelu są wykresy liniowe, kolumnowe i kołowe, więc warto znać chociaż te 3 i umieć je właściwie dobrać do danych.
Na przykład wykres kołowy reprezentuje udział części w całości. Jeśli nie mamy pełnych danych, nie używajmy tego typu wykresu. Źle sprawdzi się też, gdy części jest więcej niż 3, bo w takiej sytuacji zancząco spada jego czytelność.

Podsumowanie

Jak widzisz Excel jest dość złożonym narzędziem. Wszystko, co wypisałem powyżej to absolutne minimum do pracy zawodowej z tym programem. Właśnie dlatego te opcje szczegółowo omówiłem w kursie Excel w CV (kliknij tutaj). Po jego ukończeniu, oprócz certyfikatu, będziesz też gotowy do zderzenia się z testami na rozmowie o pracę i podstawowymi zdaniami na etacie.

Jeśli nasuną Ci się pytania, pisz śmiało!

Zielone pozdrowienia,
Michał Kowalczyk