Remkomplekty.ru

IT Новости из мира ПК
5 просмотров
Рейтинг статьи
1 звезда2 звезды3 звезды4 звезды5 звезд
Загрузка...

Персентиль в excel

Процентили в EXCEL

Рассмотрим понятие процентиля, функцию ПРОЦЕНТИЛЬ.ВКЛ() , процентиль-ранг и построим кривую процентилей.

Сначала разберемся на примерах, что такое процентиль , затем рассмотрим соответствующие функции MS EXCEL.

Задача. Проектируют койку на круизном лайнере. Необходимо, чтобы 95% пассажиров помещались на койке в полный рост. Как вычислить длину койки?

Для решения задачи потребуется найти рост, ниже которого 95% населения. Для этого нужно сделать репрезентативную выборку , скажем, из 2000 человек, отсортировать значения выборки по возрастанию , потом определить значение с позицией равной 1901 (2000*95%+1). Пусть найденный рост оказался равен 190 см. Ответ : Длина койки должна быть 190 см (+ запас для комфортного размещения на койке).

Значение 190 см называется 95%-й процентилью данной выборки , т.е. 95% опрошенных людей имеет рост 100) обычно наблюдается хорошее соответствие. Повторы значений также могут привести к несоответствию значения процентиля и соответствующего % значений (см. ниже).

Примечание : Процентили часто называют перцентилями (с этим соглашается и MS WORD) или центилями . В версии MS EXCEL 2007 и более ранних использовалась функция ПЕРСЕНТИЛЬ() , которая оставлена для совместимости. Но, начиная с версии EXCEL 2010, появились функции ПРОЦЕНТИЛЬ.ВКЛ() и ПРОЦЕНТИЛЬ.ИСКЛ() – английское название PERCENTILE.EXC(), а Условное форматирование предлагает настроить правило с использованием именно процентилей . В свою очередь, надстройка Пакет Анализа имеет инструмент Ранг и Персентиль . Google также отдает предпочтение процентилям , выдавая гораздо больше результатов на запрос «процентиль», чем на запрос «перцентиль» (на начало 2016 года).

Таким образом, для процентилей используется 3 названия: процентиль (MS EXCEL, Google) , персентиль (MS EXCEL) , перцентиль (MS WORD) .

Ниже детально рассмотрим как работает функция ПРОЦЕНТИЛЬ.ВКЛ() и создадим ее аналог с помощью альтернативной формулы. Также рассмотрим функцию ПРОЦЕНТРАНГ.ВКЛ() и кривую процентилей .

СОВЕТ : Нижеследующие разделы следует читать пользователям, владеющими базовыми понятиями математической статистики (случайная величина, функция распределения) .

Функция ПРОЦЕНТИЛЬ.ВКЛ()

Начиная с версии MS EXCEL 2010 для расчета процентилей используется функция ПРОЦЕНТИЛЬ.ВКЛ() – английское название PERCENTILE.INC(). В более ранних версиях MS EXCEL использовался ее аналог — функция ПЕРСЕНТИЛЬ() .

Напомним определение процентиля , данное выше: K-й Процентиль представляет такое собой значение Х в наборе данных, которое разделяет набор на две части: одна часть содержит K% данных, меньших Х , а другая часть содержит все остальные значения набора (т.е. 1- K % данных б о льших Х).

Разберем детально как работает функция ПРОЦЕНТИЛЬ.ВКЛ() .

Пусть имеется массив значений ( выборка ). В массиве 49 значений, массив расположен в диапазоне B15:B63 , имеются повторы значений , массив для удобства отсортирован по возрастанию (см. файл примера , лист ПРОЦЕНТИЛЬ.ВКЛ ).

Рассчитаем 0,08-ю процентиль ( 8%-процентиль ) с помощью формулы =ПРОЦЕНТИЛЬ.ВКЛ(B15:B63; 0,08) . Получим, что 0,08-я процентиль равна 4,84.

Проанализируем, что мы получили.

  • Во-первых, значения 4,84 нет в массиве (есть 4 и 5), т.е. функция ПРОЦЕНТИЛЬ.ВКЛ() интерполирует значения.
  • Во-вторых, процент значений меньших 4,84 равен не точно 8%, а 8,16%=4/49*100% (т.к. всего 4 значения в массиве меньше 4,84). Это произошло, т.к. в выборке относительно мало значений.
  • Другой причиной расхождения могут стать повторы. Например, заменив, первые 4 значения в массиве (т.е. 1; 2; 3; 4) числом 5, мы получим вместо 8,16% — 0%. Это произошло потому, что теперь 0,08-я процентиль равна 5, а в выборке нет ни одного значения меньше 5.

Как видно из рисунка ниже первое значение ( минимальное , равное 1) является 0-й процентилью .

Соответственно, 1-й процентилью (100% процентилью ), является максимальное значение равное 120 (см. файл примера лист ПРОЦЕНТИЛЬ.ВКЛ).

Как видно из рисунка, следующее за минимальным значением (т.е. число 2) является 0,0208-й процентилью . Значение 0,0208 или 2,08% — это (k-1)*1/(n-1), где n – это количество значений в массиве (в нашем массиве n=49), а k – это позиция числа в массиве (в данном случае k=2, где 2 – это позиция, а не само число).

Поясним эту формулу. Для вычисления процентили принимается, что весь диапазон значений массива (от мин до макс) разбит n значениями на равные интервалы (их всего n-1). Соответственно, 1/(n-1), это «ширина» интервала (весь диапазон равен 1 или 100%). Обратите внимание, что «ширина» интервала не зависит от данных, а только от их количества. В нашем случае «ширина» интервала равна 0,0208 или 2,08%.

Приведем алгоритм расчета 12,50%-процентили функцией ПРОЦЕНТИЛЬ.ВКЛ() (см. ячейку С21 ):

  • ПРОЦЕНТИЛЬ.ВКЛ() определяет «ширину» интервала (в долях или процентах): =1/(49-1)=0,0208;
  • подсчитывает Количество интервалов , которые были укладываются в 12,50%, т.е. =12,50%/2,08%=6 (значение процентиля кратно ширине интервала, т.е. делится нацело);
  • 6-й интервал располагается между числами массива 10 и 11. Верхняя граница последнего 6-го интервала равна 11;
  • Следовательно, 12,50%-я процентиль равна 11 (см. ячейку B21 ).

По аналогии с непрерывной функцией распределения (см. статью про квантили ), получается, что 12,50% значений должно быть меньше полученного числа 11 (в соответствии с определением процентиля ). Фактически получается, что таких значений 6 (1; 2; 3; 4; 5; 10) и процент значений меньших 11 равен 12,24%=6/49 (причины расхождения: повторы и небольшое количество значений).

Если значение процентиля не кратно ширине интервала (ширина интервала равна 1/(n-1)), то имеет место интерполирование. Например, вспомним результат вычисления 0,08-й процентили равный 4,84. Значение процентили (т.е. не результат, а %) равно 0,08 (8%), что соответствует 3-м целым интервалам (8%/2,08%= 3 ,84) и некой доли (0,84) от ширины следующего интервала. Границами этого «неполного» интервала являются значения 4 ( 0,0625-я процентиль ) и 5 ( 0,0833-я процентиль ). Т.к. разница между 5 и 4 равна 1, то умножая «пройденную» долю интервала (0,84) на длину интервала в абсолютных значениях (=5-4=1), получаем 0,84. В итоге получаем 4,84: 4 – левая граница интервала + часть следующего (5-4)*0,84.

Если бы в массиве вместо 5 было значение 6, то значение 0,08-й процентили было бы равно 5,68 (4 – левая граница интервала + (6-4)*0,84=1,68).

Альтернативный расчет процентили с помощью формул приведен в файле примера .

Примечание : Некоторые значения процентилей имеют специальные названия:

  • 25-я процентиль называется 1-й квартилью;
  • 50-я процентиль называется Медианой (2-я квартиль);
  • 75-я процентиль называется 3-й квартилью.
Читать еще:  Перестал открываться файл excel

Функция ПРОЦЕНТРАНГ.ВКЛ() и Кривая процентилей

Функция ПРОЦЕНТРАНГ.ВКЛ() используется для оценки относительного положения значения в массиве. Для заданного значения функция вычисляет сколько значений в массиве меньше или равно ему. Точнее — какой процент значений массива меньше или равен ему. Результат функции называется процентиль-ранг (percentile rank) . Понятно, что для максимального значения процентиль-ранг равен 0,00%, а для наименьшего — 100% (все значения массива меньше или равны ему).

Функция ПРОЦЕНТРАНГ.ВКЛ() , английская версия – PERCENTRANK(), является, в каком-то смысле, обратной функции ПРОЦЕНТИЛЬ.ВКЛ() : т.е. задавая в качестве аргумента значение из массива, функция ПРОЦЕНТРАНГ.ВКЛ() вернет значение процентили кратной 1/(n-1).

Как видно из рисунка выше, для повторяющихся значений функция ПРОЦЕНТРАНГ.ВКЛ() вернет, естественно, одинаковые значения. Также поступает функция РАНГ.РВ() или РАНГ() (см. статью Функция РАНГ() в MS EXCEL ).

Действительно, функции РАНГ.РВ() и ПРОЦЕНТРАНГ.ВКЛ() очень похожи. Первая возвращает позицию числа в массиве в зависимости от его значения. Вторая, в принципе, делает тоже самое, но результат выводится в % от общего количества значений в массиве.

Как видно из картинки выше, чтобы получить процентиль-ранг необходимо значение ранга уменьшить на 1 и разделить на n-1. Значение ранга , естественно, должно быть отсортировано по возрастанию .

По выборке можно оценить функцию распределения Генеральной совокупности , из которой взята данная выборка. Для этой цели построим Кривую процентилей (percentile curve или percentile rank plot). Кривая процентилей представляет собой график зависимости процентиль-ранга от значений выборки .

Возьмем выборку состоящую из 100 значений (см. файл примера лист Кривая процентилей ). Значения содержатся в диапазоне А5:А104 .

Сначала построим таблицу частот для каждого из значений выборки .

Примечание : В отличие от Гистограммы , где кумулятивная таблица частот строится для интервалов значений, таблицу частот для Кривой процентилей строят для каждого из значений выборки .

Из таблицы видно (столбец Частота нарастающим итогом ), что примерно 1 процент значений меньше или равен значения 3,27, примерно 2 процента на уровне или ниже 3,28, 5 процентов на уровне или ниже 3,29, и так далее. График Кривой процентилей для этих данных приведен на картинке ниже.

СОВЕТ : Про построение графиков см. статью Основные типы диаграмм .

Следует отметить, что использование данных из таблицы приведет к точечному виду кривой (так как процентиль-ранг будет изменяться скачком для каждого значения выборки ). Поэтому, сглаженная кривая, построенная на основе этих данных будет лучше представлять оцениваемую функцию распределения (пунктирная кривая).

Построив пунктирную кривую, становится ясно, зачем нам пришлось вводить понятие процентиль-ранга: процентиль-ранг – является приблизительной вероятностью выбрать случайную величину меньше или равную соответствующему значению (сравните с определением функции распределения). Это, в частности следует из расчета процентиль-ранга по формуле =СЧЁТЕСЛИ($A$5:$A$104;» 100 значений).

Примечание : Формула =(РАНГ.РВ(A5;$A$5:$A$104;1)-1)/ (СЧЁТ($A$5:$A$104)-1) эквивалентна формуле =ПРОЦЕНТРАНГ.ВКЛ($A$5:$A$104;A5;5)

Процентили

— это характеристики набора данных, которые выражают ранги элементов массива в виде чисел от 1 до 100, и являются показателем того, какой процент значений находится ниже определенного уровня.

Например, значение 30-й процентили указывает, что 30% значений располагается ниже этого уровня.

На конкретном примере поясним понятие процентиля:

Пример 1 . Группа студентов из 20 человек получила на экзамене по статистике следующие балы: три студента — 5 баллов, 8 студентов — 4 балла, 6 студентов — 3 бала и 3 студента — 2 балла. Вычислить процентиль успеваемости каждого студента.

Решение.

Формула процентиля

Процентиль = n(x≤X)/N*100

n(x≤X) — число студентов, получивших бал не менее X ,

X — количество балов конкретного студента, процентиль которого находим ,

N — число всех студентов .

Для удобства вычислений ранжируем выборку балов от максимального значения до минимального ( в порядке убывания): 5,5,5,4,4,4,4,4,4,4,4,3,3,3,3,3,3,2,2,2

Допустим нам необходимо определить процентиль студента Иванова получившего на экзамене 5 баллов:

Находим n(x≤X)=n(x≤5)=20 — т.е. 20 студентов получили бал не выше 5, тода

Процентиль (Иванова) = 20/20*100=100

Допустим необходимо определить процентиль студента Петрова получившего на экзамене 4 балла:

Находим n(x≤X)=n(x≤4)=17 — т.е. 17 студентов получили бал не выше 4, тода

Процентиль (Петрова) = 17/20*100=85

Допустим необходимо определить процентиль студента Сидорова получившего на экзамене 3 балла:

Находим n(x≤X)=n(x≤3)=9 — т.е. 9 студентов получили бал не выше 3, тода

Процентиль (Иванова) = 9/20*100=45

После расчета процентиля можно составить таблицу стандартизации. Для наших баллов она будет выглядеть следующим образом:

Алгоритм расчета процентилей

1. Для каждого человека посчитать, какое количество человек набрало столько же или меньше баллов.

2. Посчитать сколько процентов составляет это количество от всей выборки.

Процентиль – это процент людей из выборки, набравших столько же или меньше баллов, чем конкретный человек.

Процентиль является достаточно распространенной шкалой стандартизации, среди психологов, социологов, биологов, медиков и т.д., т.к. очень удобен и понятен. Его диапазон от 1 до 100.

Процентили указывают на относительное положение индивида в выборке стандартизации. Их также можно рассматривать, как ранговые градации, общее число которых равно 100, с той лишь разницей, что при ранжировании принято начинать отсчет сверху, т.е. с лучшего члена группы, получающего ранг 1. В случае же процентилей отсчет ведется снизу, поэтому, чем ниже процентиль, тем хуже позиция индивида.

Процентиль может использоваться для стандартизации как нормально распределенных случайных величин СВ, так и данных с ненормальным распределением.

Расчет процентилей в Excel

Для расчета процентилей нам понадобится функция СЧЕТЕСЛИ.

Для расчета, для каждого значения нужно ввести формулу:

=(СЧЁТЕСЛИ(диапазон;условие)*100)/N , где N – количество человек.

Инструмент анализа «Ранг и персентиль»

Этот инструмент в отличие от соответствующих функций не просто определяет ранги и персентили, а сортирует их в порядке уменьшения значения. Он может производить эту операцию не для одной характеристики (например, одного размера), а нескольких, сгруппированных по столбцам или по строкам (рис. 2.10).

Рис. 2.10. Инструмент анализа данных «Ранг и персентиль»

Инструмент выводит значения рангов и персентилей на тот же рабочий лист (необходимо указать ячейку, откуда начнётся вывод), новый рабочий лист или новую рабочую книгу. (Необходимо установить переключатель в положение «по столбцам» или «по строкам» в зависимости от расположения данных во входном диапазоне.)

Читать еще:  Команда правка в excel

2.4 Представление эмпирических и теоретических распреде­лений в программе MS EXCEL

Функция ЧАСТОТА(рис. 2.11) используется для подсчета количества значений случайной величины (частот), попадающих в заданные интервалы («карманы»).

Рис. 2.11. Аргументы функцииЧАСТОТА

Последовательность действий должна быть следующей.

1. В таблице строится массив границ интервалов, который в аргументах функцииЧАСТОТА, см. рис. 2.11, обычно представлен как «массив интервалов» (а в ряде версий MS EXCEL — как «Двоичный массив»). Для правильного выбора массива границ интервалов нужно первоначально оценить оптимальное количество интервалов m, например по формуле (1.1), и длину интервала: L = (xmax — xmin)/m, округлив L в бόльшую сторону.

2. Выделяется массив ячеек, куда в результате будут введены значения частот (на единицу больше числа заданных границ интервалов). Это объясняется тем, что первый и последний интервалы (в порядке увеличения значений их границ) строго ограничены лишь с одной стороны, а со второй — соответственно -∞ и +∞.

3. Открывается функцияЧАСТОТА.

4. В открытое окно аргументов функциивводятся массив данных и массив границ интервалов. При этом значения частот уже появляются в окне.

5. Для выведения значений частот в таблицу (в выделенный по п. 2 массив ячеек) необходимо одновременно нажать комбинацию клавиш CTRL+SHIFT+ENTER.

Таким образом, с помощью функцииЧАСТОТА можно сформировать интервальный статистический ряд, который будет состоять из двух строк. В первой строке определены границы интервалов, причём первый и последний интервалы ограничены с одной стороны. Во второй строке должны быть расположены значения частот, попавших в заданные интервалы.

Функция МОДА (рис. 2.12) родственна функции ЧАСТОТА, но определяет не эмпирическую плотность, а наиболее часто повторяющееся значение в каждом из массивов или аргументов, число которых («Число1, число2, . »), может быть не более 30. Поэтому если множество данных не содержит одинаковых значений, то функция МОДА показывает ошибку.

Рис. 2.12. Аргументы функцииМОДА

Инструмент анализа «Гистограмма»близок по решаемым задачам к функции «ЧАСТОТА», но имеет значительно более широкие возможности. В нём поле «Входной интервал» (рис. 2.13) соответствует полю «Массив данных» в функции «ЧАСТОТА», а поле «Интервал карманов» соответствует полю «Массив интервалов» в функции «ЧАСТОТА». Но в отличие от функции «ЧАСТОТА» поле «Интервал карманов» не обязательно для заполнения. При заполнении эти значения должны быть введены в возрастающем порядке. В Microsoft EXCEL вычисляется число (частота) «попаданий» данных между текущим началом отрезка и соседним бóльшим по порядку, если такой есть. При этом включаются значения на нижней границе отрезка и не включаются значения на верхней границе. Если «Интервал карманов» не был введен, то набор отрезков, равномерно распределенных между минимальным и максимальным значениями данных, будет создан автоматически. Может потребоваться, чтобы интервалы располагались не в возрастающем порядке, как обычно, а в порядке убывания частоты. Получаемый график в этом случае будет напоминать по форме известную диаграмму Парето [17]. Для этого следует поставить «галочку» в окне «Парето (отсортированная диаграмма)» (см. рис. 2.13).

Рис. 2.13. Инструмент анализа данных «Гистограмма»

Иногда необходимо знать не только распределение частот, но и накопленный процент частот (интегральную функцию дискретного распределения) из расчёта, что сумма всех частот = 100 %. Для вывода этого дополнительного графика интегральных процентов следует установить флажок не только в окне «Вывод графика», но и в окне «Интегральный процент» (см. рис. 2.13). В результате в дополнение к синим столбцам плотности распределения «Частота» появится график лилового цвета «Интегральный %». (Следует знать, что график «Интегральный %» появляется только при выводе основного графика «Частота»).

Гистограммы различного вида уже не в виде частот, а либо просто как раскладки значений по категориям и рядам данных, либо как накопленный абсолютный или относительный вклад каждой категории в общую сумму, могут быть получены также в рамках графических опций программыMS EXCEL. Кроме того, эти опции позволяют преобразовать не только форму получаемой гистограммы (ширину зазора между столбцами частот, формат осей, подписи данных, и т.д.), но и сам тип диаграммы, представляющей «Частоту» и «Интегральный %».

Выбор направления расчёта (интегральной функции или плотности распреде­ления) в статистических функциях

В окне многих статистических функций распределения случайной величины, определяемых программой MS EXCEL, см. § 4.4, присутствует аргумент «Интегральная» (рис. 2.14 ).

Рис. 2.14 Аргумент «Интегральная»

Здесь «Интегральная» — логическое значение, определяющее «направление» расчёта; если этот аргумент имеет значение ЛОЖЬ (нуль), то определяется функция плотности распределения f; если этот аргумент имеет значение ИСТИНА (любое другое число), то определяется интегральная функция F.

Перцентили

Заметим, что перцентиль представляет собой какой-то элемент массива, имеющий определенный ранг и выраженный в тех же единицах, что и сам массив в целом. Так, 60-й перцентиль эффективности сбора металлолома в конторе «Ржавая подкова» составляет, скажем, 85062 руб. (измерен не в процентах, а в рублях, как элемент набора данных). Если этот 60-й перцен- тиль, равный 85062 руб., характеризует деятельность определенного агента по заготовкам (например, г-на Пупкина), то это означает, что примерно 60 % других тружеников имеют результат ниже, чем у г-на Пупкина, а 40 % — более высокие показатели.

Перцентили используются для двух целей:

чтобы показать значение элемента в массиве при заданном перцен- тильном ранге (например, «10-й перцентиль равен 46293 руб.»);

чтобы показать перцентильный ранг значения данного элемента в рассматриваемом массиве статистических данных (например, «эффективность заготовок металлолома агента г-на Козлевича составляет 65994 руб., что соответствует 55-му перцентилю»).

Продолжим рассмотрение нашей задачи. В диалоговом окне Ранг и персентиль заполним поле Входной интервал (рис.7).

Рис. 7. Диалоговое окно Ранг и перцентиль

В нем укажем данные 2-й графы табл. 3 (вместе с заголовком), относящиеся к фирме «Колокольный звон» (это диапазон ячеек $B$1:$B$13). Отметим флажком позицию Метки в первой строке (поскольку нам нужно со-хранить заголовок этой графы), а затем в окне Выходной интервал укажем ячейку $I$1, в которой будет размещена таблица с рассчитанными показателями рангов и перцентилей. После этого — кнопка ОК.

Читать еще:  Создание olap куба в excel

Затем аналогичным образом поступим с данными 3-й графы (сведения от фирмы «Мельхиор»). При заполнении диалогового окна Ранг и персентиль отметим диапазон ячеек $С$1:$С$13, а для опции Выходной интервал покажем ячейку, которая должна быть по соседству с первой половинкой нашей общей таблицы. Это ячейка $М$1.

В окончательном виде наша таблица примет следующий вид (рис.8).

Как видно, Excel аккуратно проранжировал результаты по каждому эпизоду, расположив студентов по местам в соответствии с их материальны- ми успехами, а также указал их перцентильный ранг (в %). Для дальнейших рассуждений данные по перцентилям мы использовать не станем, а вот ранги окажутся совершенно необходимыми. 1 J К L М N 0 Р Точка Фирма «Колокольный звон» Ранг Процент Точка Фирма «Мельхиор» Ранг Процент 11 3,5 1 100,00% 12 4,5 1 100,00% 12 3,4 2 90,90% 4 4,1 2 90,90% 6 3,3 3 81,80% 1 3,3 3 81,80% 4 3,2 4 72,70% 9 3,2 4 72,70% 2 3,1 5 63,60% 11 3,1 5 63,60% 1 2,8 6 54,50% 2 3 6 54,50% 9 2,5 7 45,40% 3 2,8 7 45,40% 5 2,4 8 36,30% 6 2,7 8 36,30% 10 2,3 9 27,20% 10 2,6 9 27,20% 7 2,2 10 18,10% 7 2,5 10 18,10% 3 2 11 9,00% 8 2,3 11 9,00% 8 1,8 12 ,00% 5 2,1 12 ,00% Рис. 8. Расчетная таблица с показателями рангов и перцентилей

На основании ранговых оценок организуем сводную таблицу, аналогичную уже знакомой нам табл.3 (рис.9). Для удобства перейдем на другой рабочий лист (Лист 2). Для выполнения последующих расчетов используем итоговый результат, отражающий сумму разностей квадратов рангов, равную 105. Оформим вспомогательную таблицу (рис.9), в которой укажем значение ScF = 105, размер выборки n = 12, а также предусмотрим в ней ячейку, где поместим рассчитанное значение коэффициента ранговой корреляции р (ячейка Е22).

Поместим курсор в ячейку Е22, а затем в поле формулы запишем уравнение, по которому будем рассчитать коэффициент р. Выглядит оно так:

= 1 — 6*(Е20)/(Е21*(Е21А2 — 1)) В ячейке появится искомый результат 0,632867. С округлением принимаем его равным 0,633 — коэффициент оказался именно таким, каким мы его вычислили «вручную».

Полученный результат показывает, что в данной ситуации надлежит высказать совершенно те же соображения по поводу исследуемого процесса, какие были сделаны для случая расчета коэффициента р традиционным способом. При доверительной вероятности 0,95 студенты вполне могут горделиво полагать, что их материальные достижения всецело определяются личным усердием и не зависят от каких-то иных привходящих факторов. Однако требование более строгой оценки (с вероятностью 99 %) делает такое мнение менее очевидным и для значимого статистического вывода возникает необходимость расширить выборку (привлечь для анализа большее число студентов) либо (при невозможности это сделать) отнестись к результату вполне философски.

Вычислить PERCENTILE в MS Excel, когда некоторые значения являются значениями ошибок

Вычисление 90% процентиля целых чисел в диапазоне скажем A1:A10 может быть выполнено по формуле =PERCENTILE(A1:A10, 0.9) . Обычно это работает хорошо, но если одна или несколько ячеек в диапазоне A1:A10 являются ошибкой (помеченной как #N/A ), то вычисленное значение процентиля также равно #N/A .

Возможно ли, используя только формулы Excel, вычислить процентиль для всех значений (кроме ячеек, содержащих значения ошибок) в диапазоне A1:A10 ?

2 Ответа

В Excel 2010 или более поздних версиях вы можете использовать функцию AGGREGATE для этого, AGGREGATE имеет возможность игнорировать значения ошибок, поэтому вы можете использовать эту формулу

См. справку для функции AGGREGATE для получения более подробной информации, но 16 обозначает тип функции — в этом случае PERCENTILE.INC (эквивалент PERCENTILE в более ранних версиях) и 6 означает «ignore errors»

с помощью Ctrl + Shift+Enter.

Похожие вопросы:

Я использую Apache POI 3.9 и мне нужно использовать формулу Excel PERCENTILE и оценить формулу изнутри Java. Проблема в том, что PERCENTILE не поддерживается. Я получаю ошибку.

Я хочу применить одну или функцию в excel с некоторыми ячейками, но Excel имеет значение #N/A(Error) в содержащих ячейках, где я хочу применить формулу. Можно ли применить или формулу с ячейками.

Я хочу получить значения max и min со значениями других столбцов в ms excel 2007. У меня есть две колонки | Column A | Column B | ———————— |A | 18 | ———————— |B | 78 |.

Я пытаюсь импортировать некоторые данные для работы с файлом Excel, но у меня возникают проблемы с числовыми значениями ячеек. У меня есть несколько столбцов, которые будут иметь значения, где.

Это мой список ниже. Я пытаюсь вычислить 95% звонков, которые вернулись за сколько миллисекунд. Milliseconds Number of Calls 45 14 46 33 47 40 48 41 49 83 50 114 51 124 52 82 53 89 54 99 55 82 56 72.

Мне нужно создать сценарий автоматизации. Мне будет предоставлен файл MS Excel и файл MS-Access. Скрипт будет считывать данные из файлов excel и access, сравнивать их, и строки с разными значениями.

У меня есть CSV файл: fr id 1 10000152 1 10000212 1 10000847 1 10001018 2 10001052 2 10001246 14 10001908 . Это таблица частот, где id -целочисленная переменная, а fr -число вхождений.

Я создал веб-сайт asp.net,который хранит данные на листе ms-excel с веб-страницы,проблема в том, что все остальные значения столбцов, кроме первого столбца(регистрационный номер), являются.

Я хочу вычислить уравнение с переменной в нем. Я хочу перечислить значения переменных в строке, затем excel должен перечислить все результаты в соответствии с этими значениями; y(x) = 5x + 4 Мне.

У меня есть таблица в MS Access, имеющая 4 строки с четырьмя столбцами A, B, C, D Я хочу экспортировать эти записи в лист Excel следующим образом, Ожидаемый Результат : 8 строк в Excel Как должен.

Ссылка на основную публикацию
Adblock
detector