W świecie Excela często pojawia się potrzeba odpowiedzi na proste pytanie: „Ile to procent?”. Niby łatwe, a jednak na tyle powszechne, że Microsoft wprowadził do Excela 365 specjalną funkcję – ODSETEK – która tę odpowiedź daje bez kombinowania. Jeśli do tej pory wpisywałeś =A1/B1 i ręcznie formatowałeś wynik jako procent, od teraz możesz to robić bardziej elegancko i czytelnie.
Funkcja ODSETEK to wygodna formuła obliczająca udział jednej liczby lub zbioru w innej liczbie lub zbiorze – wyrażony w formacie dziesiętnym, który po sformatowaniu jako procent daje jasną informację o proporcjach.
Składnia funkcji ODSETEK:
=ODSETEK(podzbiór; całość)
Argumenty funkcji nie zostały przetłumaczone w Excel na język polski.
| Argument | Opis |
| Podzbiór (data_subset) | Zakres lub liczba, która stanowi część większego zbioru |
| Całość (data_all) | Zakres lub liczba reprezentująca pełną wartość, do której odnosimy się |
Działanie funkcji:
Excel sumuje oba zakresy (jeśli są zakresami), a następnie dzieli sumę podzbiór przez sumę całość. Wynik zwracany jest jako ułamek dziesiętny, np. 0,84 = 84%.
Jak szybko policzyć, jaki procent całości stanowi dana wartość?
Dane:
| Typ | Liczba |
| Liczba poprawnych odpowiedzi: | 42 |
| Liczba wszystkich pytań: | 50 |
Formuła:
=ODSETEK(B4;B5)
Wynik: 0,84 → po sformatowaniu jako procent → 84%
Jak obliczyć wzrost lub spadek procentowy między dwiema liczbami?
Dane:
| Typ | Liczba |
| Stara wartość | 1000 |
| Nowa wartość | 1250 |
Formuła:
=ODSETEK(B5;B4)
Wynik: 1,25 → 125% (czyli wzrost o 25%)
Aby uzyskać samą zmianę:
= ODSETEK(B5;B4)-1
→ wynik: 0,25 → 25%
Jak obliczyć udział procentowy wybranej kategorii?
Dane:
| Produkt | Sprzedaż |
| Produkt 1 | 25 000,00 |
| Produkt 2 | 100 000,00 |
Formuła:
=ODSETEK(B4;B$4:B$5)
Wynik: 0,20 → 20% oraz 0,80 → 80%
To oznacza, że Produkt 1 odpowiada za 20% całkowitej sprzedaży.
Jak policzyć odsetek dla filtrowanego zbioru danych?
Dane:
| Kraj | Przychód |
| USA | 40000 |
| Niemcy | 30000 |
| USA | 15000 |
| Francja | 15000 |
| Wielka Brytania | 33000 |
| USA | 5000 |
Formuła:
=ODSETEK(FILTRUJ(B4:B9;A4:A9=”USA”);B4:B9)
Wynik: 0,43 → 43%
Udział przychodów z USA w całkowitych przychodach.
Jak połączyć ODSETEK z warunkiem JEŻELI?
Formuła:
=JEŻELI(B4=0; „Brak danych”; ODSETEK(B4; B7:B9))
Jeśli wartość w komórce B4 lub pozostałych w kolumnie B wynosi 0, funkcja zwróci informację. W przeciwnym razie obliczy odsetek przychodu w TYLKO pozostałych krajów nie uwzględniając tych w których nie ma danych.
Jak zabezpieczyć się przed błędem dzielenia przez zero?
W tym przykładzie zastosujemy bardziej zaawansowane narzędzia wyszukiwania w postaci funkcji X.WYSZUKAJ , listę rozwijaną, formatowanie warunkowe oraz zapytanie Power Query w formie tabeli Excelowej. Jeśli Cię zainteresują powyższe rozwiązania to zachęcamy do zapoznania się z nimi na naszym blogu! 😊
Dzięki temu, będziemy mogli zmieniać wybór do obliczenia w czytelny sposób, który będzie odszukany w tabeli Excel. Gdy dane źródłowe się zmienią tabela po odświeżeniu się załaduje nowa z nowymi danymi.
Tabela Excel, z której pobieramy dane (zapytanie Power Query) nazywa się w naszym przykładzie „Dane_tabeli_zewnętrzne”
=JEŻELI.BŁĄD(ODSETEK(X.WYSZUKAJ(A4;Dane_tabeli_zewnętrzne[Kraj];Dane_tabeli_zewnętrzne[Przychód);B7:B10);”wczytaj dane”)
Jeśli argument data_all będzie zerem (np. tabela się jeszcze nie zaczytała z Power Query) to funkcja zwróci informację „wczytaj dane”, a nie błąd #DZIEL/0!
Wartość, którą mamy podzielić przez całość znajdujemy za pomocą funkcji X.WYSZUKAJ. Jeśli danych jeszcze nie ma to zostaniemy poinformowani o tym zamiast błędu #DZIEL/0! abyśmy wczytali dane źródłowe (ewentualnie odświeżyli źródła). Formatowanie warunkowe sprawi, że treść będzie szara i pochylona kursywą.
Funkcja ODSETEK to jak pilot zdalnego sterowania do obliczania procentów – wszystko zdalnie, bez potrzeby ręcznego wpisywania formuł typu =A1/B1. Przyda się zarówno w analizie kosztów, sprzedaży, wyników testów, jak i w prostych zestawieniach.
Ale pamiętaj – tylko użytkownicy Microsoft 365 mogą z niej korzystać. W starszych wersjach Excela (2019, 2021 itd.) funkcja nie jest dostępna. W takich przypadkach trzeba wrócić do klasyki – dzielenie i formatowanie.
Warto łączyć ODSETEK z funkcjami:
Lub z listami rozwijanymi z dynamicznie zmieniającymi się wyborami użytkowników.
