W tym artykule dowiemy się, jak znaleźć wartość percentyla, jeśli przy podanych kryteriach w programie Excel
Scenariusz:
W prostych słowach, pracując z długimi rozproszonymi zbiorami danych, czasami musimy znaleźć percentyl liczb z pewnymi kryteriami nad nim. Na przykład, znalezienie percentyla wynagrodzeń w określonym dziale lub posiadanie wielu kryteriów dla daty, nazwisk, działu lub nawet liczb danych, takich jak pensje poniżej wartości lub ilość powyżej wartości. W tym celu musisz ręcznie wyodrębnić wymagane liczby, a następnie obliczyć percentyl tablicy z kryteriami. Użycie funkcji JEŻELI z formułami tablicowymi.
Jak rozwiązać problem?
Musisz pomyśleć, jak to jest możliwe, aby wykonywać operacje logiczne na tablicach tabel przy użyciu funkcji IF. Funkcja IF w Excelu jest bardzo przydatna, przeprowadzi Cię przez trudne zadania w Excelu lub innych językach kodowania. Funkcja IF testuje warunki na tablicy odpowiadającej wymaganym wartościom i zwraca wynik jako tablicę odpowiadającą warunkom True jako 1 i False jako 0
W przypadku tego problemu użyjemy następujących funkcji:
- PERCENTYL, funkcja
- JEŚLI funkcja
Będziemy wymagać tych powyższych funkcji i podstawowego poczucia obsługi danych. warunki logiczne na tablicach można stosować za pomocą operatorów logicznych. Te operatory logiczne działają zarówno na tekście, jak i na liczbach. Poniżej znajduje się ogólna formuła. { } nawiasy klamrowe to magiczne narzędzie do wykonywania formuł tablicowych z funkcją JEŻELI.
Wzór ogólny:
| { = PERCENTYL ( JEŻELI ( (zakres op crit), tablica_centylów ), k ) } |
Uwaga: w przypadku nawiasów klamrowych ( { } ) Posługiwać się Ctrl + Shift + Enter podczas pracy z tablicami lub zakresami w programie Excel. Spowoduje to domyślnie generowanie nawiasów klamrowych we wzorze. NIE próbuj na stałe kodować znaków nawiasów klamrowych.
zakres : tablica, gdzie obowiązuje warunek
op : operator, operacja zastosowana
crit : kryteria stosowane na zakresie
percentyl_tablica : tablica, gdzie potrzebny jest percentyl
k : k-ty percentyl (na przykład 33% -> k = 0,33 wartość jako liczba)
Przykład :
Wszystko to może być trudne do zrozumienia. Przetestujmy więc tę formułę, uruchamiając ją na poniższym przykładzie. Tutaj mamy dane o otrzymanych produktach z różnych regionów wraz z odpowiednią datą, ilością i ceną. Tutaj mamy dane i musimy znaleźć 25 percentyl lub wartość odpowiadającą 25%, biorąc pod uwagę zamówienia, które są otrzymywane tylko z regionu "Wschód". Teraz jesteś gotowy, aby uzyskać pożądany wynik, korzystając z poniższej formuły.
Użyj wzoru:
| { = PERCENTYL ( JEŻELI ( B2:B11 = "Wschód" , D2:D11 ) , 0.25 ) } |
Wyjaśnienie :
- Region B2:B11 ="East" : sprawdza wartości w regionie tablicy, aby dopasować je do "East".
- Operator logiczny zwraca True dla dopasowanej wartości i False dla niedopasowanej wartości.
- Teraz funkcja JEŻELI zwraca tylko wartości cen odpowiadające 1s i False bez zmian. Poniżej znajduje się zakres zwrócony przez funkcję JEŻELI i otrzymany przez funkcję PERCENTYL.
{ FAŁSZYWE; 303.63; FAŁSZYWE ; 153,34; FAŁSZYWE ; 95,58; FAŁSZYWE ; FAŁSZYWE ; 177; FAŁSZYWE }
- Funkcja PERCENTYLE zwraca cenę centylową 25% (k=0,25) dla zwróconej tablicy.
Tutaj tablice są podane przy użyciu odwołania do komórki. Teraz cena odpowiadająca 25% tablicy podana jest poniżej.
Jak widać, cena wynosi 138,9 , z trzech zamówień ze „Wschodu” o wartościach cenowych 303,63, 153,34 i 95,58. Teraz uzyskaj wartość ceny mającą inny percentyl, zmieniając tylko k-tą wartość na 0,5 dla 50%, 0,75 dla 75% i 1 dla 100% wartości percentyla.
Jak widać, mamy wszystkie wartości percentyla odpowiadające danym kryteriom. Teraz użyj formuły z wartością daty jako kryterium.
percentyl, jeśli z kryteriami daty w Excelu:
Data jako kryteria to trudna rzecz w Excelu. Ponieważ Excel przechowuje wartości dat jako liczby. Jeśli więc wartość daty nie jest rozpoznawana przez program Excel, formuła zwraca błąd. Tutaj musimy znaleźć 25% wartości percentyla dla zamówień otrzymanych po 15 stycznia 2019 roku.
Użyj wzoru:
| { = PERCENTYL ( JEŻELI ( A2:A11 > H3 , D2:D11 ) , 0,25 ) } |
> : większa niż operator zastosowany w tablicy dat.
Tutaj tablice są podane przy użyciu odwołania do komórki. Teraz cena odpowiadająca 25% tablicy podana jest poniżej.
Jak widać, 90,27 to wartość 25. percentyla mająca datę po 15 stycznia 2019 r. Teraz uzyskaj percentyl dla innej wartości k-tej.
Oto wszystkie wartości percentyla jako wyniki
Oto wszystkie uwagi obserwacyjne dotyczące stosowania wzoru.
Uwagi:
- Tablica_centylów w formule działa tylko z liczbami.
- NIE NALEŻY twardo kodować nawiasów klamrowych za pomocą wzoru.
- Jeśli formuła zwraca błąd #ARG, sprawdź, czy w formule muszą znajdować się nawiasy klamrowe, jak pokazano w przykładach w artykule.
- Operacje takie jak równa się ( = ), mniejszy niż równy ( <= ), Lepszy niż ( > ) lub nie równa się ( ) można wykonać w ramach zastosowanej formuły, wyłącznie z liczbami.
Mam nadzieję, że ten artykuł o tym, jak znaleźć percentyl, jeśli z kryteriami w programie Excel jest objaśniający. Więcej artykułów na temat formuł sumowania znajdziesz tutaj. Jeśli podobały Ci się nasze blogi, podziel się nimi ze znajomymi na Facebooku. A także możesz śledzić nas na Twitterze i Facebooku. Chcielibyśmy usłyszeć od Ciebie, daj nam znać, jak możemy ulepszyć, uzupełnić lub unowocześnić naszą pracę i uczynić ją lepszą dla Ciebie. Napisz do nas na stronie e-mail
Jak korzystać z funkcji SUMA PRODUKT w programie Excel: Zwraca SUMA po pomnożeniu wartości w wielu tablicach w programie Excel.
SUMA, jeśli data jest między : Zwraca SUMA wartości między podanymi datami lub okresem w programie Excel.
Suma, jeśli data jest większa niż podana: Zwraca SUMA wartości po podanej dacie lub okresie w programie Excel.
2 sposoby sumowania według miesiąca w Excelu: Zwraca SUMA wartości w danym miesiącu w programie Excel.
Jak sumować wiele kolumn z warunkiem: Zwraca SUMA wartości w wielu kolumnach mających warunek w programie Excel.
Popularne artykuły :
50 skrótów Excela, aby zwiększyć produktywność : Przyspiesz swoje zadanie. Te 50 skrótów przyspieszy pracę w programie Excel.
Jak używać tFunkcja WYSZUKAJ.PIONOWO w programie Excel : Jest to jedna z najczęściej używanych i popularnych funkcji programu Excel, która służy do wyszukiwania wartości z różnych zakresów i arkuszy.
Jak korzystać z funkcji LICZ.JEŻELI w programie Excel : Policz wartości z warunkami za pomocą tej niesamowitej funkcji. Nie musisz filtrować danych, aby zliczyć określone wartości. Funkcja Countif jest niezbędna do przygotowania deski rozdzielczej.
Jak korzystać z funkcji SUMIF w programie Excel? : To kolejna ważna funkcja deski rozdzielczej. Pomaga to zsumować wartości w określonych warunkach.