Как да намерите процентила, ако с критерии в Excel

Anonim


В тази статия ще научим как как да намерим стойността на процентила, ако с дадени критерии в Excel

Сценарий:

С прости думи, когато работим с дълъг разпръснат набор от данни, понякога трябва да намерим процентила на числата с някои критерии над него. Например намиране на процентила на заплатите в определен отдел или с множество критерии за дата, имена, отдел или дори може да номерира данни като заплати под стойност или количество над стойност. За това трябва ръчно да извлечете необходимите числа и след това да изчислите процентила на масив с критерии. Използване на IF функция с формули на масиви.

Как да решим проблема?

Трябва да мислите как е възможно това, да извършвате логически операции над масиви от таблици, използвайки IF функция. IF функцията в excel е много полезна, тя ще ви преведе през някои трудни задачи в Excel или други езици за кодиране. Функцията IF тества условията в масива, съответстващи на необходимите стойности, и връща резултата като масив, съответстващ на True условия като 1 и False като 0

За този проблем ще използваме следните функции:

  1. PERCENTILE функция
  2. IF функция

Ние ще изискваме тези по -горе функции и някакъв основен смисъл на работа с данни. логически условия към масиви могат да бъдат приложени с помощта на логически оператори. Тези логически оператори работят както върху текст, така и върху числа. По -долу тук е общата формула. { } къдравите скоби са вълшебният инструмент за изпълнение на формули за масиви с функция IF.

Обща формула:

{ = PERCENTILE (IF ((диапазон оп критичен), процентилен_масив), к) }

Забележка: За къдрави скоби ( { } ) Използвайте Ctrl + Shift + Enter при работа с масиви или диапазони в Excel. Това ще генерира къдрави скоби във формулата по подразбиране. НЕ се опитвайте да кодирате твърдо фигурни скоби.

range: масив, където важи условието

op: оператор, операцията е приложена

crit: критерии, приложени към обхвата

percentile_array: масив, където е необходим процентил

k: k -ти процентил (например 33% -> k = 0,33 стойност като число)

Пример:

Всичко това може да е объркващо за разбиране. Така че, нека тестваме тази формула, като я изпълним в примера, показан по -долу. Тук имаме данни за получени продукти от различни региони заедно със съответната дата, количество и цена. Тук имаме данните и трябва да намерим 25 -ия процентил или стойност, съответстваща на 25%, като се вземат предвид поръчките, които са получени само от регион „Изток“. Сега сте готови да получите желания резултат, като използвате формулата по -долу.

Използвайте формулата:

{ = ПЕРЦЕНЦИЛНО (АКО (B2: B11 = "Изток", D2: D11), 0,25) }

Обяснение:

  1. Регион B2: B11 = "Изток": проверява стойностите в региона на масива, за да съвпадат с "Изток".
  2. Логическият оператор връща True за съответстващата стойност и False за несравнимата стойност.
  3. Сега функцията IF връща само стойностите на цените, съответстващи на 1s и False такива, каквито са. По -долу е диапазонът, върнат от функцията IF и получен от функцията PERCENTILE.

{FALSE; 303,63; FALSE; 153,34; FALSE; 95,58; FALSE; FALSE; 177; FALSE}

  1. PERCENTILE функцията връща 25% (k = 0.25) процентилна цена за върнатия масив.

Тук масивите са дадени с помощта на препратката към клетката. Сега цената, съответстваща на 25% от масива, е дадена по -долу.

Както можете да видите, цената е 138.9, от трите поръчки от "Изток" с ценови стойности 303.63, 153.34 и 95.58. Сега вземете стойността на цената с различен процентил, само като промените k -тата стойност на 0,5 за 50%, 0,75 за 75% и 1 за 100% стойност на процентила.

Както можете да видите, имаме всички процентни стойности, съответстващи на дадените критерии. Сега използвайте формулата със стойност Date като критерии.

процентил, ако с критерии за дата в Excel:

Датата като критерии е сложно нещо в Excel. Тъй като Excel съхранява стойностите на датата като число. Така че, ако стойността на датата не е разпозната от excel, грешката при връщане на формулата. Тук трябва да намерим стойността от 25% процентил, която има поръчки, получени след 15 януари 2019 г.

Използвайте формулата:

{ = ПЕРЦЕНЦИЛНО (АКО (A2: A11> H3, D2: D11), 0,25)}

>: по -голямо от оператора, приложен към масива с дати.

Тук масивите са дадени с помощта на препратката към клетката. Сега цената, съответстваща на 25% от масива, е дадена по -долу.

Както можете да видите, 90,27 е стойността на 25 -ия процентил, която има дата след 15 януари 2019 г. Сега вземете процентила за различната k -та стойност.

Ето всички стойности на процентилите като резултати

Ето всички забележки от наблюденията относно използването на формулата.

Бележки:

  1. Процентният_масив във формулата работи само с числа.
  2. НЕ твърдо кодирайте фигурни скоби с формула.
  3. Ако формулата връща грешка #VALUE, проверете дали фигурните скоби трябва да присъстват във формулата, както е показано в примерите в статията.
  4. Операции като равно на ( = ), по -малко от равно на ( <= ), по-голяма от ( > ) или не е равно на () може да се извърши в рамките на приложена формула, само с числа.

Надявам се, че тази статия за Как да намерим процентила, ако критериите в Excel са обяснителни. Намерете още статии за формули за сумиране тук. Ако нашите блогове са ви харесали, споделете го с вашите първенци във Facebook. Можете също така да ни следвате в Twitter и Facebook. Ще се радваме да чуем от вас, уведомете ни как можем да подобрим, допълним или обновим работата си и да я подобрим. Пишете ни на имейл сайта

Как да използвате функцията SUMPRODUCT в Excel: Връща SUM след умножаване на стойности в множество масиви в Excel.

SUM, ако датата е между : Връща сумата на стойностите между датите или периода в Excel.

Сума, ако датата е по -голяма от датата: Връща сумата на стойностите след дадената дата или период в Excel.

2 начина за сумиране по месеци в Excel: Връща сумата на стойностите в рамките на даден конкретен месец в Excel.

Как да сумирате няколко колони с условие: Връща SUM от стойности в множество колони, имащи условие в excel.

Популярни статии:

50 Преки пътища в Excel за повишаване на вашата производителност : По -бързо изпълнете задачата си. Тези 50 преки пътища ще ви накарат да работите още по -бързо в Excel.

Как да използвате tФункцията VLOOKUP в Excel : Това е една от най -използваните и популярни функции на excel, която се използва за търсене на стойност от различни диапазони и листове.

Как да използвате функцията COUNTIF в Excel : Пребройте стойностите с условия, използвайки тази невероятна функция. Не е необходимо да филтрирате данните си, за да броите конкретни стойности. Функцията Countif е от съществено значение за подготовката на вашето табло.

Как да използвате функцията SUMIF в Excel : Това е друга основна функция на таблото. Това ви помага да обобщите стойностите при конкретни условия.