Po co w ogóle mediana, kwartyle i IQR w analizie danych
Średnia a mediana – dwa różne „przeciętne” wyniki
Średnia arytmetyczna jest intuicyjna: sumuje wszystkie wartości i dzieli przez ich liczbę. Jednak przy realnych danych – zwłaszcza biznesowych – średnia bywa podatna na skrajne obserwacje. Jedno bardzo wysokie lub bardzo niskie obserwowane może całkowicie przesunąć wynik.
Mediana w Excelu działa inaczej: to środkowa wartość w uporządkowanym zbiorze. Połowa obserwacji jest od niej mniejsza lub równa, a połowa większa lub równa. Jeśli liczba obserwacji jest parzysta, mediana jest średnią z dwóch środkowych liczb. Dzięki temu pojedyncze ekstremalne wartości nie wpływają na nią tak mocno, jak na klasyczną średnią.
Efekt? Przy płacach, cenach, czasach realizacji czy wynikach sprzedaży mediana często lepiej opisuje „typowy” poziom niż średnia. Średnia powie, ile wynosi globalny przeciętny poziom, mediana podpowie, ile wynosi typowa wartość, której doświadcza przeciętny pracownik, klient czy zamówienie.
Mapa pojęć: mediana, kwartyle i IQR – co dokładnie opisują
Mediana, kwartyle i IQR (rozstęp międzykwartylowy) tworzą zestaw miar opisujących położenie i rozrzut danych.
Mediana (Q2) – środkowa wartość zbioru. Połowa obserwacji leży poniżej, połowa powyżej.
Kwartyl pierwszy (Q1) – wartość, poniżej której znajduje się ok. 25% najmniejszych obserwacji.
Kwartyl trzeci (Q3) – wartość, poniżej której znajduje się ok. 75% obserwacji (czyli powyżej Q3 leży ok. 25% największych wartości).
Zbiorczo te miary odpowiadają na dwa pytania: gdzie leży środek danych (mediana) oraz jak bardzo rozjechane są wartości wokół środka (kwartyle i IQR). I wszystko to da się policzyć w Excelu wprost z funkcji: MEDIANA, KWARTYL.INC, KWARTYL.EXC oraz prostego odejmowania dla IQR, bez żadnych dodatków czy makr.
Typowe zastosowania w biznesie i analizie operacyjnej
Mediana, kwartyle i IQR pojawiają się w każdym obszarze, w którym liczą się zarówno poziomy, jak i zmienność:
Płace i wynagrodzenia – mediana płac lepiej oddaje sytuację „przeciętnego” pracownika niż średnia, a IQR pokazuje, jak rozstrzelone są stawki w zespole.
Czasy realizacji zleceń – mediana czasu dostawy ujawnia typowe opóźnienie, a kwartyle pokazują, jak często zdarzają się bardzo długie realizacje.
Wyniki sprzedaży – mediana miesięcznej sprzedaży na handlowca nie jest tak wrażliwa na kilka spektakularnych sukcesów, za to IQR informuje, czy wyniki zespołu są stabilne, czy pełne skrajności.
W każdym z tych kontekstów Excel z wbudowanymi funkcjami statystycznymi wystarcza, by bez dodatkowych narzędzi policzyć mediana w Excelu, kwartyle w Excelu i IQR w Excelu oraz wykorzystać je w analizie rozkładu danych.
Filtrowanie anomalii przed raportowaniem
Mediana i IQR są także kluczowe, gdy pojawia się potrzeba wykrywania wartości odstających. Klasyczne podejście oparte na odchyleniu standardowym zakłada w tle zbliżony do normalnego rozkład danych. W realnych procesach biznesowych ten warunek bywa słabo spełniony.
Prosta procedura:
Policzyć Q1, Q3 oraz IQR.
Wyznaczyć „granice”: Q1 − 1,5×IQR oraz Q3 + 1,5×IQR.
Zaznaczyć obserwacje poniżej dolnej lub powyżej górnej granicy jako potencjalne odstające.
Taka metoda nie wymaga żadnych dodatków do Excela. Wystarczą funkcje kwartylowe i proste formuły. Dobrze przygotowane arkusze pozwalają automatycznie oznaczać anomalie, a następnie świadomie zdecydować, czy usunąć je z raportu, czy pozostawić i opisać jako nietypowe, ale istotne przypadki.
Przygotowanie danych w Excelu przed liczeniem mediany i kwartylów
Sprawdzenie, czy dane są liczbowe, a nie tekstowe „udające” liczby
Formuły statystyczne Excel, takie jak MEDIANA czy KWARTYL.INC, ignorują tekst. Jeśli w kolumnie z danymi część wartości jest liczbami, a część tekstami wyglądającymi jak liczby, wyniki będą zafałszowane.
Kilka prostych testów:
Zaznaczenie komórki i spojrzenie na wyrównanie – domyślnie liczby są wyrównane do prawej, tekst do lewej.
Zmiana formatu komórki na „Liczbowe” – jeśli po zmianie liczby nie da się użyć w prostym dodawaniu, to prawdopodobnie jest to tekst.
Użycie funkcji CZY.LICZBA – np. =CZY.LICZBA(A2) zwróci PRAWDA, jeśli zawartość A2 jest liczbą.
Jeżeli wartości są tekstowe, można je konwertować do liczb na kilka sposobów:
Pomnożenie przez 1: =A2*1.
Dodanie zera: =A2+0.
Funkcja WARTOŚĆ: =WARTOŚĆ(A2).
Dopiero po upewnieniu się, że kolumna zawiera wyłącznie liczby, ma sens liczenie mediany, kwartylów i IQR w Excelu bez dodatków.
Usuwanie pustych wierszy, błędów i duplikatów
Mediana i kwartyle w Excelu ignorują puste komórki, ale błędy typu #N/D!, #DZIEL/0!, #ARG! już nie – mogą sprawić, że wynik funkcji będzie również błędem. Przed przystąpieniem do obliczeń warto oczyścić zakres.
Praktyczna checklista porządkowa:
Filtr na błędy – skorzystanie z filtrowania automatycznego i zaznaczenie tylko komórek z błędami, aby je poprawić lub zastąpić np. pustą wartością (lub wartością zastępczą, jeśli analiza tego wymaga).
Usunięcie ewidentnych duplikatów – jeśli analizowany ma być zbiór bez powtórzeń, narzędzie „Usuń duplikaty” (zakładka Dane) pozwala szybko oczyścić dane.
Sprawdzenie zakresu – upewnienie się, że w analizowanym zakresie nie zostały przypadkowe wiersze z inną skalą lub jednostką.
Jeżeli błędów nie można usunąć (bo np. niosą informację), można je „zamaskować” w obliczeniach, stosując funkcje warunkowe (np. JEŻELI.BŁĄD lub FILTROWANIE w nowszych wersjach), tak aby MEDIANA czy KWARTYL.INC dostały tylko prawidłowe liczby.
Znaczenie sortowania: kiedy jest potrzebne, a kiedy zbędne
Formuły statystyczne Excel, takie jak MEDIANA i funkcje kwartylowe, same sortują dane wewnętrznie. Oznacza to, że do obliczeń nie trzeba wcześniej sortować zakresu. Mediana w Excelu zadziała poprawnie niezależnie od kolejności wierszy.
Sortowanie staje się przydatne z innych powodów:
Do wizualnej kontroli rozkładu danych – szybkie zorientowanie się, czy dane rosną, gdzie pojawiają się skoki.
Do prostego zaznaczania obserwacji ekstremalnych – np. kilka najwyższych lub najniższych wartości.
Do spójności w raportach – gdy kolejne kolumny mają być uporządkowane względem jednej miary.
Jeżeli celem jest tylko policzenie mediany, kwartylów i IQR, sortowanie nie jest konieczne. Same formuły wykryją pozycję środkową i kwartylowe bez potrzeby naruszania kolejności danych.
Praca na tabelach Excel (Ctrl+T) zamiast „gołych” zakresów
Zwykłe zakresy (np. A2:A1000) działają, ale przy rosnących zbiorach szybko robi się niezgrabnie. Przekształcenie danych w tabelę Excela (Ctrl+T) daje kilka ważnych korzyści:
Automatyczne rozszerzanie zakresów – nowe wiersze dodane pod tabelą są automatycznie w niej zawarte, a formuły oparte o nagłówki tabeli obejmują je bez korekt.
Adresowanie po nazwach – zamiast =MEDIANA(A2:A1000) można pisać =MEDIANA(Tabela1[Czas]), co jest czytelniejsze i mniej podatne na błędy.
Łatwe filtrowanie i sortowanie – filtry są wbudowane w nagłówki, co upraszcza analizy warunkowe.
Przy pracy z dużymi zbiorami danych i powtarzalnych raportach przejście na tabele mocno ułatwia utrzymanie poprawności formuł MEDIANA, KWARTYL.INC i obliczeń IQR w Excelu bez dodatków.
Mediana w Excelu krok po kroku – od podstaw do wariantów
Definicja mediany na prostych przykładach
Mediana to środkowa wartość po uporządkowaniu danych rosnąco. Można to rozbić na dwa przypadki.
Przypadek 1 – liczba obserwacji nieparzysta
Przykładowe dane: 3, 5, 7, 12, 20.
Po uporządkowaniu (tu już są uporządkowane) środkowa wartość to 7 – to jest mediana. Po lewej i prawej stronie jest po dwie wartości.
Przypadek 2 – liczba obserwacji parzysta
Przykładowe dane: 3, 5, 7, 12.
Po uporządkowaniu dwie środkowe wartości to 5 i 7. Mediana jest ich średnią: (5 + 7) / 2 = 6.
Excel robi dokładnie to samo, tylko bez ręcznego sortowania. Funkcja MEDIANA przelicza pozycję środkową (lub dwie środkowe) i zwraca odpowiednią wartość.
MEDIANA vs MEDIAN – różnice nazw i zgodność wsteczna
W polskiej wersji Excela funkcja ma nazwę MEDIANA. W angielskiej – MEDIAN. Mechanizm działania jest identyczny, zmienia się tylko nazwa. W praktyce oznacza to, że:
W polskim Excelu wpisujesz: =MEDIANA(A2:A100).
W wersji angielskiej: =MEDIAN(A2:A100).
Excel zapisuje w pliku nazwę odpowiadającą językowi instalacji. Po otwarciu polskiego pliku w angielskim Excelu formuły zostaną automatycznie przetłumaczone (MEDIANA → MEDIAN) i odwrotnie. To standardowy mechanizm lokalizacji funkcji.
Najczęstsza pułapka pojawia się przy kopiowaniu formuł z internetu – skopiowana formuła z MEDIAN nie zadziała wprost w polskiej wersji, dopóki nie zmienisz nazwy na MEDIANA.
Jak policzyć medianę dla zakresu danych w kolumnie lub wierszu
Najprostszy wariant: dane znajdują się w jednej kolumnie, np. A2:A101. Aby policzyć medianę w Excelu:
Wybierz pustą komórkę, w której ma się pojawić wynik, np. B2.
Wpisz formułę: =MEDIANA(A2:A101).
Zatwierdź Enter.
Funkcja zignoruje puste komórki oraz wartości tekstowe. Jeśli zakres zawiera błędy, formuła może zwrócić błąd – dlatego wcześniejsze czyszczenie danych jest tak ważne.
Dla wiersza działa to analogicznie: np. =MEDIANA(A2:Z2). Dla kilku nieprzylegających zakresów można wskazać je jako kolejne argumenty: =MEDIANA(A2:A10;C2:C10;E2:E10). Excel potraktuje wszystkie argumenty jako jeden zbiór i wyznaczy medianę wspólną.
Mediana dla danych filtrowanych – kiedy wynik bywa mylący
Klasyczna funkcja MEDIANA ignoruje puste komórki, ale nie odróżnia wierszy ukrytych filtrem od widocznych. Jeśli zastosujesz filtr na tabeli i potem policzysz =MEDIANA(A2:A100), Excel użyje zarówno wartości widocznych, jak i ukrytych.
To może prowadzić do sytuacji, w której:
raport prezentuje dane tylko dla jednego regionu czy działu,
a mediana dotyczy całej tabeli, nie tylko widocznej części.
Aby liczyć medianę tylko dla widocznych wierszy, trzeba użyć innego podejścia, np. funkcji AGREGUJ lub kombinacji z PODSUMY. Do tego tematu wrócimy w sekcji o analizie warunkowej, ale kluczowy fakt jest jeden: MEDIANA nie jest funkcją „świadomą filtra”.
Mediana w formule zagnieżdżonej – filtracja „ręczna”
Klasyczna MEDIANA używa wszystkich liczb z podanego zakresu. Jeśli trzeba objąć obliczeniem tylko część obserwacji (np. wartości dodatnie, wyniki powyżej zera, konkretne lata), można to wymusić formułą tablicową opartą o JEŻELI lub nowsze funkcje dynamiczne.
Najprostszy wariant z warunkiem logicznym:
=MEDIANA(JEŻELI(A2:A100>0;A2:A100))
Znaczenie:
JEŻELI(A2:A100>0;A2:A100) zwraca „tablicę” z wartościami z zakresu A2:A100 tam, gdzie spełniony jest warunek > 0, oraz FAŁSZ w pozostałych miejscach.
MEDIANA(...) ignoruje wartości logiczne (FAŁSZ) i liczy medianę tylko z liczb.
W starszych wersjach Excela (bez „dynamicznych tablic”) taką formułę trzeba zatwierdzić jako formułę tablicową: zamiast zwykłego Enter – kombinacja Ctrl+Shift+Enter. Excel doda wtedy nawiasy klamrowe { } widoczne na pasku formuły.
W wersjach z dynamicznymi tablicami (Microsoft 365 / Excel 2021) wystarczy zwykły Enter – JEŻELI zadziała tablicowo bez dodatkowych kroków.
Tego typu konstrukcję da się łatwo rozszerzyć, np. na przedział wartości:
Koniunkcja warunków jest zapisana jako iloczyn (warunek1)*(warunek2). Excel traktuje PRAWDA jako 1, FAŁSZ jako 0, więc tylko obserwacje spełniające oba warunki przechodzą do obliczenia mediany.
Przy szeregach czasowych, np. dziennych wynikach sprzedaży czy liczbie zgłoszeń do helpdesku, mediana z ruchomego okna pozwala wygładzić serie i wychwycić nagłe skoki. Działa podobnie do średniej kroczącej, ale jest odporniejsza na wartości odstające.
Przykład: dane dzienne w kolumnie A (A2:A100). Mediana z 7 dni wstecz (łącznie z bieżącym dniem) może wyglądać tak:
W komórce B8 (bo pierwszy pełny „tydzień” kończy się w wierszu 8) wpisz:
=MEDIANA(A2:A8)
Skopiuj formułę w dół do końca zakresu (B9, B10, …).
W każdym wierszu Bx mediana obejmie ostatnich 7 dni z kolumny A. Jeśli długość okna ma być zmienna, można liczyć końcowy wiersz funkcją WIERSZ() i dynamicznie budować zakres, ale w typowych raportach wystarcza proste przeciągnięcie formuły.
Podobny zabieg bywa używany w kontrolingu jakości: mediana z ostatnich kilkunastu pomiarów sygnalizuje, czy proces „ucieka” od typowej wartości, nawet jeśli pojedyncze pomiary są mocno rozrzucone.
Kwartyle w Excelu – różne funkcje, różne metody liczenia
Definicja kwartylów i powiązanie z medianą
Kwartyle dzielą uporządkowany zbiór obserwacji na cztery części o mniej więcej równej liczebności. Najczęściej korzysta się z trzech liczb:
Q1 (pierwszy kwartyl) – wartość, poniżej której znajduje się ok. 25% danych,
Q2 (drugi kwartyl) – to po prostu mediana, czyli ok. 50% danych jest poniżej, 50% powyżej,
Q3 (trzeci kwartyl) – wartość, poniżej której jest ok. 75% danych.
Sam sposób liczenia kwartylów ma kilka wariantów. Excel implementuje dwa główne schematy w osobnych funkcjach – to źródło wielu drobnych rozbieżności między raportami.
KWARTYL a KWARTYL.INC i KWARTYL.EXC – o co chodzi z wariantami
Starsze wersje Excela zawierały tylko funkcję KWARTYL. Nowsze dodały dwie kolejne: KWARTYL.INC i KWARTYL.EXC. Różnią się sposobem interpolacji i tym, czy dopuszczają skrajne percentyle.
KWARTYL – oznaczona jako przestarzała (kompatybilność wsteczna), ale nadal działa. Jej zachowanie odpowiada w praktyce KWARTYL.INC.
KWARTYL.INC – „inclusive”, korzysta z zakresu percentyli od 0 do 1 włącznie (czyli można policzyć także min i max).
KWARTYL.EXC – „exclusive”, opiera się na percentylach z przedziału (0;1), a więc wyłącza wartości skrajne w szczegółach obliczeń.
Jeśli w arkuszu nie ma potrzeby porównywania wyników z innym oprogramowaniem statystycznym, praktycznym wyborem jest konsekwentne korzystanie z KWARTYL.INC. Unika się w ten sposób niespodzianek przy małych próbach, gdzie KWARTYL.EXC potrafi zwrócić inne wyniki niż większość narzędzi.
Podstawowe przykłady użycia funkcji kwartylowych
Prosty przykład: dane liczbowe w kolumnie A, od A2 do A101. Formuły dla podstawowych kwartylów:
Pierwszy kwartyl (Q1):
=KWARTYL.INC(A2:A101;1)
Mediana (Q2) z funkcji kwartylowej:
=KWARTYL.INC(A2:A101;2)
Trzeci kwartyl (Q3):
=KWARTYL.INC(A2:A101;3)
Zauważalny fakt: KWARTYL.INC(zakres;2) nie zawsze zwraca dokładnie ten sam wynik, co MEDIANA(zakres), przy bardzo małej liczbie obserwacji różnice mogą wynikać z innej definicji pozycji kwartylu. W typowych zastosowaniach biznesowych są one jednak pomijalne.
Jeżeli sięgniemy po wariant KWARTYL.EXC, ten sam zestaw formuł będzie wyglądał tak:
Warto odnotować, że dla małych prób (n < 4) funkcja KWARTYL.EXC może zwracać błąd, ponieważ w tym podejściu nie wszystkie kwartyle są zdefiniowane.
Kwartyle jako szczególne przypadki percentyli
Kwartyle to nic innego jak percentyle dla konkretnych poziomów:
Q1 = 25. percentyl,
Q2 = 50. percentyl (mediana),
Q3 = 75. percentyl.
Excel udostępnia osobne funkcje do percentyli: PERCENTYL.INC i PERCENTYL.EXC. Składnia:
=PERCENTYL.INC(zakres;k)
=PERCENTYL.EXC(zakres;k)
Argument k to wartość z zakresu 0–1 (lub otwartego 0–1 dla EXC). Odpowiedniki kwartylowe można więc zapisać tak:
Q1: =PERCENTYL.INC(A2:A101;0,25)
Q2: =PERCENTYL.INC(A2:A101;0,5)
Q3: =PERCENTYL.INC(A2:A101;0,75)
Taka forma przydaje się, gdy obok „klasycznych” kwartylów trzeba podać np. 10. i 90. percentyl, aby uchwycić zakres „typowych” wyników pomijający 10% najniższych i 10% najwyższych obserwacji.
Kwartyla z warunkiem – gdy ważny jest tylko fragment danych
Podobnie jak medianę, kwartyle można liczyć dla podzbioru danych, wykorzystując formułę tablicową. Przykład: rozkład czasu odpowiedzi tylko dla jednego działu, gdy:
czas odpowiedzi w minutach jest w kolumnie C (C2:C1000),
nazwa działu w kolumnie B (B2:B1000).
Pierwszy kwartyl czasu odpowiedzi dla działu „Sprzedaż”:
Formuła opiera się na tej samej idei, co warunkowa mediana: JEŻELI zwraca liczby tylko tam, gdzie spełniony jest warunek na kolumnie B, a KWARTYL.INC oblicza z nich kwartyl.
W starszym Excelu tę formułę trzeba zatwierdzić jako tablicową (Ctrl+Shift+Enter). W nowszych edycjach – zwykły Enter wystarczy.
Czym jest rozstęp międzykwartylowy i co mówi o danych
Rozstęp międzykwartylowy (IQR – interquartile range) to różnica między trzecim a pierwszym kwartylem:
IQR = Q3 − Q1
Ta prosta liczba pokazuje, jak „rozsypane” są wartości w środkowych 50% rozkładu. Im większy IQR, tym większe zróżnicowanie typowych obserwacji. W przeciwieństwie do zwykłego rozstępu (max − min), IQR jest praktycznie niewrażliwy na pojedyncze ekstremalne wartości.
Stosuje się go m.in. przy:
porównywaniu rozrzutu między grupami (np. czas realizacji zamówień w dwóch magazynach),
wykrywaniu obserwacji odstających w klasycznym pudełku Tukeya,
weryfikacji, czy proces „trzyma” się ustalonych granic stabilności.
Obliczanie IQR za pomocą KWARTYL.INC
Dla danych w zakresie A2:A101 rozstęp międzykwartylowy można zapisać wprost:
=KWARTYL.INC(A2:A101;3)-KWARTYL.INC(A2:A101;1)
Jeśli zależy na przejrzystości, w sąsiednich komórkach można wypisać po kolei:
komórka B2 (Q1): =KWARTYL.INC(A2:A101;1)
komórka B3 (Q3): =KWARTYL.INC(A2:A101;3)
komórka B4 (IQR): =B3-B2
Taki układ dobrze sprawdza się w raportach, które potem są odczytywane przez inne osoby – widać nie tylko wynik, ale również jego komponenty.
IQR z warunkiem – rozrzut w wybranej podgrupie
Gdy analiza dotyczy tylko jednej kategorii (np. konkretnego produktu albo regionu), można połączyć wcześniej omówioną filtrację tablicową z obliczeniem Q1 i Q3.
Przykład: sprzedaż w kolumnie D (D2:D2000), region w kolumnie C (C2:C2000). IQR dla regionu „Północ”:
Następnie IQR jako zwykła różnica Q3 − Q1. W starszych wersjach Excela także tutaj niezbędne jest zatwierdzenie formuł z JEŻELI jako tablicowych.
IQR w identyfikacji obserwacji odstających (reguła 1,5 × IQR)
Klasyczna reguła Tukeya wskazuje potencjalne wartości odstające na podstawie IQR. Konstrukcja jest prosta:
dolna granica: Q1 − 1,5 × IQR,
górna granica: Q3 + 1,5 × IQR.
Obserwacje znajdujące się poniżej dolnej granicy lub powyżej górnej są traktowane jako kandydaci na odstające. Nie jest to „wyrok”, ale dobry punkt startu do dalszej weryfikacji.
Przykładowy układ formuł dla danych w A2:A101:
Q1 w B2: =KWARTYL.INC(A2:A101;1)
Q3 w B3: =KWARTYL.INC(A2:A101;3)
IQR w B4: =B3-B2
dolna granica w B5: =B2-1,5*B4
górna granica w B6: =B3+1,5*B4
Następnie w kolumnie obok danych (np. w B2) można oznaczyć potencjalne odstające:
=JEŻELI(LUB(A2<$B$5;A2>$B$6);"ODSTAJĄCA";"")
Takie proste oznaczenie pozwala szybko przefiltrować wartości spoza „typowego” zakresu, bez dodatkowych dodatków czy makr.
Obliczenia krok po kroku na realistycznym przykładzie danych
Układ przykładowych danych w arkuszu
Przykład: czasy realizacji zamówień internetowych w minutach. Założenia:
lista zamówień w wierszach 2–501,
kolumna A – identyfikator zamówienia,
kolumna B – magazyn (np. „Wrocław”, „Poznań”),
kolumna C – czas realizacji w minutach.
Analiza skupia się wyłącznie na kolumnie C. Pytanie kontrolne: co wiemy? Mamy surową listę czasów. Czego nie wiemy? Jak wygląda ich środek, rozrzut i czy są wartości odstające.
Mediana czasu realizacji – jeden krok do „typowego” zamówienia
Dla całej puli zamówień mediana w komórce E2:
=MEDIANA(C2:C501)
Ta jedna liczba odpowiada na praktyczne pytanie: ile minut czeka „przeciętny” klient, bez zniekształcenia przez pojedyncze rekordowo długie realizacje.
Kwartyle i IQR dla całej próby
W tym samym bloku raportowym (np. komórki E3:G6) można rozpisać podstawowe statystyki:
E3 (Q1): =KWARTYL.INC(C2:C501;1)
E4 (Q2 – mediana z kwartylu): =KWARTYL.INC(C2:C501;2)
E5 (Q3): =KWARTYL.INC(C2:C501;3)
E6 (IQR): =E5-E3
Jeśli obok wstawić zwykły rozstęp (min i max), kontrast jest natychmiastowy:
F3 (min): =MIN(C2:C501)
F4 (max): =MAKS(C2:C501)
Porównując IQR z rozstępem, można ocenić, na ile skrajne wartości „rozciągają” dane. Gdy IQR jest niewielki, a max bardzo duży, wiadomo, że problemem są jednostkowe przypadki.
Granice odstających na podstawie IQR
W kolejnym kroku granice odstających można obliczyć w komórkach:
E7 (dolna granica): =E3-1,5*E6
E8 (górna granica): =E5+1,5*E6
Następnie w kolumnie D przy każdym zamówieniu pojawia się etykieta:
=JEŻELI(LUB(C2<$E$7;C2>$E$8);"ODSTAJĄCE";"")
Po przeciągnięciu formuły w dół i włączeniu filtra można szybko wyświetlić tylko zamówienia z etykietą „ODSTAJĄCE” i sprawdzić, czy to pojedyncze incydenty (np. awaria systemu), czy powtarzający się schemat.
Porównanie dwóch magazynów – ten sam arkusz, inne pytanie
Ten sam zestaw danych pozwala sprawdzić, który magazyn charakteryzuje się mniejszym rozrzutem czasów. Załóżmy, że w komórkach H1 i I1 znajdują się nazwy: „Wrocław” oraz „Poznań”.
Analogiczny zestaw formuł dla „Poznania” umieszczony obok daje dwie pary liczb do porównania. Gdy mediany są podobne, ale IQR jednego magazynu wyraźnie wyższy, wiadomo, że proces w tym magazynie jest mniej powtarzalny.
Mediana i kwartyle dla wybranych grup – analiza warunkowa bez dodatków
Pojedynczy warunek – klasyka z funkcją JEŻELI
Najprostszy scenariusz to obliczenie mediany lub kwartylu dla jednej kategorii, tak jak przy danych magazynowych. Schemat jest zawsze ten sam:
Zdarzają się analizy, w których ważne są tylko obserwacje z określonego zakresu czasu czy wartości. Przykład: kwartyle czasu realizacji tylko dla zamówień droższych niż określony próg.
Po wyznaczeniu Q1 i Q3 dla warunkowego zestawu danych IQR liczy się identycznie jak wcześniej – jako prosta różnica. Przykładowo:
Q1 w komórce H5:
=KWARTYL.INC(JEŻELI(B2:B500="Północ";D2:D500);1)
Q3 w komórce H6:
=KWARTYL.INC(JEŻELI(B2:B500="Północ";D2:D500);3)
IQR w H7:
=H6-H5
Tak skonstruowany blok można skopiować dla kolejnych regionów lub produktów, zmieniając jedynie warunek tekstowy.
Tworzenie prostej tabeli porównawczej bez tabel przestawnych
Gdy liczba kategorii jest niewielka, wygodnym rozwiązaniem jest ręczna „mini-macierz” statystyk. Przykład: w kolumnie J lista regionów („Północ”, „Południe”, „Wschód”, „Zachód”), obok w kolumnach K, L, M – mediana, IQR oraz liczebność.
Po skopiowaniu formuł w dół powstaje prosty raport porównawczy. Widać nie tylko „środek” (mediana), ale też rozrzut i wielkość próby.
Excel po polsku i po angielsku – nazwy funkcji, separator argumentów, pułapki
Polskie i angielskie odpowiedniki funkcji
Przy pracy na różnych komputerach lub z plikami z zagranicy pojawia się problem tłumaczenia funkcji. Kluczowe odpowiedniki:
MEDIANA ↔ MEDIAN
KWARTYL ↔ QUARTILE
KWARTYL.INC ↔ QUARTILE.INC
KWARTYL.EXC ↔ QUARTILE.EXC
PERCENTYL.INC ↔ PERCENTILE.INC
PERCENTYL.EXC ↔ PERCENTILE.EXC
JEŻELI ↔ IF
MIN ↔ MIN
MAKS ↔ MAX
LICZ.JEŻELI ↔ COUNTIF
Jeśli formuła z wersji polskiej zostanie wklejona do Excela po angielsku (lub odwrotnie), program nie przetłumaczy nazw funkcji automatycznie. Trzeba je zmienić ręcznie lub użyć zewnętrznych narzędzi tłumaczących funkcje.
Separator argumentów – przecinek, średnik, a czasem odwrotnie
Drugą potencjalną barierą jest separator argumentów funkcji. W polskich ustawieniach regionalnych Excela separatorem dziesiętnym jest przecinek, dlatego argumenty oddziela się najczęściej średnikiem:
Przy kopiowaniu formuł między środowiskami często trzeba ręcznie zamienić średniki na przecinki lub odwrotnie. Błędny separator kończy się komunikatem o błędzie składni funkcji.
Kropka czy przecinek w liczbach ułamkowych
Kolejna różnica widoczna jest w argumentach typu percentyl. W polskiej wersji:
=PERCENTYL.INC(A2:A101;0,25)
Ta sama formuła po angielsku będzie brzmiała:
=PERCENTILE.INC(A2:A101,0.25)
Jeżeli w polskim Excelu pojawi się zapis 0.25, program potraktuje go zwykle jak tekst albo część innej notacji, co prowadzi do błędu. W odwrotną stronę – w angielskiej wersji 0,25 jest odczytywane nieprawidłowo.
Zamiana funkcji przestarzałych na aktualne
W arkuszach przygotowanych lata temu nadal można znaleźć funkcję KWARTYL lub PERCENTYL bez przyrostków .INC/.EXC. W nowych wersjach są one oznaczone jako przestarzałe (ang. compatibility).
Bezpieczna aktualizacja:
KWARTYL(zakres;typ) → KWARTYL.INC(zakres;typ)
PERCENTYL(zakres;k) → PERCENTYL.INC(zakres;k)
W większości zastosowań biznesowych taka podmiana nie zmienia wyników, za to eliminuje ostrzeżenia i ułatwia współpracę z nowszymi dodatkami czy szablonami.
Różnice między Excel Online, Windows i Mac
Najczęściej zadawane pytania (FAQ)
Jak policzyć medianę w Excelu krok po kroku?
Najprostszy sposób to użycie funkcji MEDIANA. Jeśli dane są w kolumnie A, w wierszach od 2 do 100, wpisz formułę:
=MEDIANA(A2:A100)
Excel sam posortuje zakres wewnętrznie i zwróci wartość środkową. Przy nieparzystej liczbie obserwacji będzie to dokładnie środkowy element, przy parzystej – średnia z dwóch środkowych wartości. Sortowanie danych przed obliczeniem nie jest konieczne, o ile zakres jest poprawnie wskazany i zawiera tylko liczby.
Jak obliczyć kwartyle w Excelu bez dodatków?
Do liczenia kwartylów służą funkcje KWARTYL.INC i KWARTYL.EXC. Dla danych w zakresie A2:A100:
=KWARTYL.INC(A2:A100;1) – pierwszy kwartyl (Q1)
=KWARTYL.INC(A2:A100;2) – mediana (Q2)
=KWARTYL.INC(A2:A100;3) – trzeci kwartyl (Q3)
Wariant INC (inclusive) uwzględnia skrajne wartości w sposobie liczenia, jest częściej stosowany w raportach biznesowych. EXC (exclusive) pomija skrajności i bazuje na innej definicji pozycji kwartylowych; stosuje się go głównie w analizach ściśle statystycznych. Zanim wybierzesz funkcję, odpowiedz na pytanie: co porównujesz i z czym chcesz być spójny (np. z raportami HR, BI, badaniami).
Jak policzyć IQR (rozstęp międzykwartylowy) w Excelu?
IQR to różnica między trzecim a pierwszym kwartylem: IQR = Q3 − Q1. W Excelu można to zrobić w jednym wzorze, np.:
=KWARTYL.INC(A2:A100;3)-KWARTYL.INC(A2:A100;1)
Otrzymany wynik pokazuje „szerokość” środkowych 50% danych. Jeśli w analizie czasu dostaw IQR jest mały, proces jest relatywnie stabilny; wysoki IQR oznacza duże rozrzuty między typowymi zamówieniami. To twardy fakt, natomiast interpretacja (czy to dobrze, czy źle) zależy już od kontekstu biznesowego.
Czym różni się mediana od średniej i kiedy lepiej użyć mediany?
Średnia arytmetyczna sumuje wszystkie wartości i dzieli przez ich liczbę. Mediana jest środkową wartością po uporządkowaniu danych. Kluczowa różnica: średnia mocno reaguje na wartości skrajne, mediana jest na nie odporna.
Przykład z praktyki: w zespole sprzedażowym jeden handlowiec robi pojedyncze, wyjątkowo duże transakcje. Średnia sprzedaż na osobę będzie „ciągnięta w górę”, podczas gdy mediana pokaże, ile zwykle sprzedaje typowy handlowiec. W obszarach takich jak płace, czasy realizacji, kwoty zamówień mediana często lepiej opisuje „typowy” poziom niż średnia.
Jak wykryć wartości odstające (outliery) w Excelu za pomocą IQR?
Najczęściej stosowany schemat wygląda tak:
Oblicz Q1 i Q3, np. =KWARTYL.INC(A2:A100;1) oraz =KWARTYL.INC(A2:A100;3).
Policz IQR: =Q3 - Q1 (jak w jednej z poprzednich odpowiedzi).
Wyznacz granice:
Dolna: Q1 - 1,5*IQR
Górna: Q3 + 1,5*IQR
Każdą obserwację poniżej dolnej lub powyżej górnej granicy można oznaczyć jako potencjalnie odstającą, np. formułą warunkową w dodatkowej kolumnie. Co dalej z takimi wartościami zrobić – usunąć, zostawić, osobno opisać – to już decyzja analityczna, a nie kwestia samego Excela.
Dlaczego Excel ignoruje część wartości przy liczeniu mediany lub kwartylów?
Funkcje MEDIANA i KWARTYL.INC/EXC biorą pod uwagę wyłącznie liczby. Tekst – nawet jeśli wygląda jak liczba – jest przez nie pomijany. Jeśli w kolumnie znajdują się „liczby jako tekst”, wyniki będą liczone tylko z części danych.
Aby sprawdzić, czy komórka zawiera liczbę, można użyć formuły =CZY.LICZBA(A2). Tekst można szybko przekształcić w liczbę przez:
pomnożenie przez 1: =A2*1,
dodanie zera: =A2+0,
funkcję =WARTOŚĆ(A2).
Dopiero po konwersji całej kolumny do prawdziwych liczb mediana, kwartyle i IQR pokażą pełny obraz danych.
Czy przed liczeniem mediany i kwartylów trzeba sortować i czyścić dane?
Sortowanie nie jest technicznie wymagane – Excel sortuje dane wewnętrznie na potrzeby obliczeń. Pomaga jednak w kontroli jakości danych i szybkim wychwyceniu skrajnych wartości. Obowiązkowy jest natomiast przegląd błędów: funkcje statystyczne ignorują puste komórki, ale napotkane błędy (#N/D!, #DZIEL/0! itd.) potrafią „zepsuć” wynik.
Przed obliczeniami warto:
przefiltrować i poprawić lub usunąć komórki z błędami,
usunąć ewidentne duplikaty, jeśli analizujemy zbiór bez powtórzeń,
upewnić się, że w jednym zakresie nie mieszają się dane w różnych jednostkach lub skalach.
Tak przygotowany zakres umożliwia liczenie mediany, kwartylów i IQR bez dodatków i bez zaskakujących, trudnych do wyjaśnienia wyników.
