TOP 5 formuły w Excelu do efektywnej pracy dla Finansistów i nie tylko!
19.05.2024
Znajomość 5 najczęściej używanych formuły w Excelu może Ci się przydać.
Bo wiecie, można “łopatalogicznie” dojść do pewnych rozwiązań np. przeliczając pewne rzeczy na kalkulatorze lub licząc na kartce…
Tylko po co?
Ułatwiając być może komuś życie, poniżej znajdziesz listę TOPOWYCH FORMUŁ, wraz ze szczegółowym opisem “kiedy i gdzie” je stosować.
Gotowy?
SUMA
WYSZUKAJ.PIONOWO / X.WYSZUKAJ - formuły w Excelu
“Jestem ESKPLORATOREM tabel!“
Tak mógł zareagować KAŻDY, kto pierwszy raz wypełnił tabelę danymi z innych tabel, za pomocą formuły WYSZUKAJ.PIONOWO, czy dzisiaj bardziej aktualną X.WYSZUKAJ.
Formuła (jedna bądź druga) bez znajomość, której nie powinno się wpisywać Excela w swoim CV.
O co w niej chodzi?
Mamy mnóstwo tabel i nie wszystkie dane są w tej samej tabeli, a czasami chcemy aby były. Jak więc łączyć dane?
- Ręcznie – przeszukując rekord za rekordem w jednej i przeklejać pojedynczo każdy rekord, a jakby ich było tysiące, to najlepiej zamknąć kompa i płakać po kątach
- Znaleźć kolumnę, która jest łącznikiem między jedną i drugą tabelą (IDENTYFIKATOR) i skorzystać z formuły X.WYSZUKAJ lub WYSZUKAJ.PIONOWO.
No jak dla mnie wybór jest prosty…
Jeżeli chciałbyś potrenować formułę wyszukaj.pionowo, to zapraszam Cię do mojego artykułu o tej formule. Tutaj link do artykułu: “Najbardziej użyteczna formuła w Excelu? Jak użyć wyszukaj.pionowo”
Czym jest identyfikator i dlaczego jest on tak ważny?
Dla użytkowników, którzy dopiero co rozpoczynają swoją przygodę z Excelem, pewną niejasnością może być słowo IDENTYFIKATOR.
Czym on jest i dlaczego on jest taki istotny?
Identyfikator jest unikatowym rekordem, który występuję w jednej i drugiej tabeli. Jest swoistym łącznikiem bez którego nasze tabele się nie połączą.
To o co musimy dbać, to aby w tabeli z której chcemy pobrać jakieś dane, był on UNIKATOWY.
Jeśli w tabeli słownikowej (tej po prawej na powyższym obrazku) dwa razy występował np. rekord 701. Raz była to np. “Sprzedaż brutto”, a drugi raz np. “Sprzedaż brutto TOTAL”, to który rekord by nam się wyświetlił?
Odpowiedzią na to pytanie jest rekord znajdujący się wyżej (pierwszy od góry).
Problemy z formuła Wyszukaj.Pionowo
Jednym z największych mankamentów formuły WYSZUKAJ.PIONOWO jest wymaganie dotyczące zachowanie kolejności danych w tabeli, z której chcemy zaciągnąć jakieś dane.
W tabeli tej identyfikator MUSI BYĆ zawsze po lewej stronie od danych, które chcemy wyświetlić.
Patrz porównanie, powyższego obrazka i słownika po lewej i po prawej.
Zasady te na szczęście zostały schowane do szuflady dla formuły X.Wyszukaj
To nie jest treść, której szukałeś? Zapraszam Cię do dalszej eksploracji
JEŻELI lub WARUNKI - proste formuły
JEŻELI lub prostsza wersja dostępna od wersji WARUNKI
Dla jednej i drugiej formuły głównym celem jest wykonywanie operacji warunkowych, co oznacza, że możemy ustalić różne działania w zależności od spełnienia określonego warunku.
- Bardzo przydatne, gdy chcemy określić np. kategorie wyniku liczbowego (ZŁY lub DOBRY)
- Gdy chcemy określić przedziały liczbowe np. 65% to ocena 3.
- Gdy chcemy zaproponować działanie w zależności od wyniku np. Gdy Twój budżet bieżący STAN KONTA wynosi poniżej 500 zł, to idź do rodziców po pożyczkę. 😉
Jedna i druga formuła działa podobnie, całość moglibyśmy sprowadzić do prostego
=JEŻELI/WARUNKI (test_warunków ; gdy spełniony to zrób lub wyświetl “TAK” ; gdy niespełniony to wykonuj dalszą czynność lub stwórz kolejny warunek)
Jednak, co niektórzy zadają sobie pytanie, dlaczego mówimy o dwóch formułach, a nie o jednej?!
Przez długi czas mieliśmy dostęp do jednej formuły JEŻELI, którą zna większość użytkowników Excela. Jednak formuła, ta ma pewne ograniczenia. W formule JEŻELI możliwe jest stworzenie tylko 7 warunków. Jednak ilość otwartych nawiasów i wymagane zagnieżdżenia formuły (Jeżeli, jeżeli, jeżeli…) i dopilnowanie aby na końcu wstawić odpowiednią ilość nawiasów, zapewne nie jednego użytkownika Excela w przeszłości doprowadziła do bólu głowy… i tak kiedyś ktoś genialny wpadł na stworzenie prostszej formuły WARUNKI, na którą warto się przestawić.
Z resztą co ja tutaj Wam będę pisał zapoznajcie się ze zdjęciem, rzućcie okiem na plusy i minusy jednej i drugiej formuły.
Wcześniej o formule JEŻELI napisałem obszerny artykuł z przykładami i filmem instruktażowym. Do obejrzenia tutaj (link).
SUMA.WARUNKÓW - formułą księgowych i analityków
Nie ma bardziej popularnej formuły, której “nawklepywałem” się częściej niż SUMA.WARUNKÓW.
SUMA.WARUNKÓW (ang. SUMIFS) to funkcja, która sumuje wartości w zakresie, który spełnia kilka kryteriów. W przeciwieństwie do prostszej funkcji SUMA.JEŻELI (ang. SUMIF), która pozwala na zastosowanie jednego kryterium, SUMA.WARUNKÓW umożliwia użycie wielu warunków jednocześnie. Dzięki temu możemy tworzyć bardziej zaawansowane i precyzyjne analizy danych.
Wiem, że brzmi skomplikowanie, ogólnie chodzi o to, że chcemy po kilku pojedynczych kryteriach zsumować wszystkie odpowiadające wartości.
Wyobraźmy sobie, gdzie mamy kolumny: rok, miesiące, nazwę konta czy wersję chcemy zsumować wszystkie odpowiadające rekordy dla jednego przecięcia składającego się z kilku składowych.
Właśnie w tym celu stosujemy sumę warunków.
Formuła ta jest jedną z trudniejszych dla poczatkujących użytkowników. Jednak odpowiednie poznanie pozwala zaoszczędzić sporo czasu i tworzyć skomplikowane analizy i porównania.
LICZ.WARUNKI
LICZ.WARUNKI (ang. COUNTIFS) to funkcja, która liczy liczbę komórek spełniających wszystkie podane kryteria w różnych zakresach. W przeciwieństwie do LICZ.JEŻELI (ang. COUNTIF), która działa z jednym kryterium, LICZ.WARUNKI pozwala na użycie wielu kryteriów jednocześnie.
W porównaniu do powyższej suma.warunków w formule licz.warunki pomijamy kolumnę wartości. Nie sumujemy żadnej wartości, tylko zliczamy ilość wystąpień danych rekordów.
Tak np. jeżeli byśmy pracowali w dziale kadr i chcieli zliczyć ilość pracowników w danych departamentach i ich długość stażu moglibyśmy posłużyć się formułą licz.warunki, jak to zostało zrobione poniżej.
TOP 5 formuły w excelu, a efektywność pracy - podsumowanie
Znajomość powyższych formuł pozwali Ci zaoszczędzić sporo czasu w przyszłości.
Jeśli artykuł Ci się spodobał zapraszam do dalszej eksploracji strony, choćby do zajrzenia do zakładki “Bloki Tematyczne”, gdzie może znajdziesz coś dla siebie, a to wszystko nakarmi mój algorytm i pomoże innym trafić na moją stronę. 😉
Pozdrawiam,
Mariusz
Czyli coś dla każdego szanującego się zarządcy kapitałów naszych firm.
Poczynając od danych finansowych, HR, produkcyjne…
No istne “must have” KAŻDEGO UŻYTKOWNIKA EXCELA
Gdy chcemy coś zsumować, nie musimy wyciągać kalkulatora z szuflady i liczyć 1234,32 + 213341,32 + 23432,23 (…).
Mamy szereg możliwości wywołania tej prostej formuły, która zrobi to za nas za jednym zamachem.
Są to:
Tam też mamy po rozwinięciu dostępny szereg innych pomocnych formuł takich jak: Średnia, Maksimum, Min, Zliczenie.
Jedyne wymagania musimy ustawić się w komórce bezpośrednio pod naszymi danymi.
Opcja 1 i 2 już daje szereg możliwości przyspieszania, dlatego warto z nią się zaprzyjaźnić