Как использовать функцию ПРОЦЕНТИЛЬ в Excel —
Функция ПРОЦЕНТИЛЬ Excel вычисляет «k-й процентиль» для набора данных. Процентиль — это значение, ниже которого попадает определенный процент значений в наборе данных. Вы можете использовать ПРОЦЕНТИЛЬ для определения 90-го процентиля, 80-го процентиля и т. Д.
Возвращаемое значение
Синтаксис
Аргументы
- array — Значения данных.
- k — Число, представляющее k-й процентиль.
Версия
Примечания по использованию
Функция ПРОЦЕНТИЛЬ Excel вычисляет «k-й процентиль» для набора данных. Процентиль — это значение, ниже которого попадает определенный процент значений в наборе данных. Процентиль, рассчитанный с .4 как k, означает, что 40% процентов значений меньше или равны вычисленному результату, процентиль, вычисленный с k = .9, означает, что 90% процентов значений меньше или равны вычисленному результату.
Чтобы использовать ПРОЦЕНТИЛЬ, укажите диапазон значений и число от 0 до 1 для аргумента «k», который представляет процент. Например:
Вы также можете указать k в процентах, используя символ%:
ПРОЦЕНТИЛЬ возвращает значение, большее или равное указанному процентилю.
Как использовать функцию ПРОЦЕНТИЛЬ в Excel
Функция ПРОЦЕНТИЛЬ берет данные из диапазона и возвращает k-й процентиль. Эта функция позволяет вам выбрать диапазон и какой процентиль вы хотите вернуть. Примерами возвращаемых процентилей могут быть 90-й, 50-й или даже 10-й процентиль диапазона чисел.
Просто для обновления того, что такое статистический процентиль, если вы тестировали в 25-м процентиле своего класса, это будет означать, что 25% вашего класса протестировали ниже, чем вы.
Как работает процентильная функция
При вводе функция ПРОЦЕНТИЛЬ должна вводиться как формула. Чтобы добавить эту функцию, вы должны выполнить следующие шаги:
- Щелкните ячейку, в которой должен отображаться результат.
- Введите "= ПРОЦЕНТИЛЬ (". Конечно, без кавычек!
- После выбора массива чисел ставится запятая. Это диапазон чисел, для которого вы находите процентиль.
- Возвращаемый процентиль с закрытой круглой скобкой. Это число следует вводить в виде десятичной дроби.
- Наконец, данные необходимо ввести в ячейку.
Более подробно синтаксис функции ПРОЦЕНТИЛЬ можно увидеть здесь:
= ПРОЦЕНТИЛЬ (массив; k)
В этой функции есть два аргумента: массив и k. Массив определяет набор данных, а k определяет, какое значение процентиля выбирается от нуля до единицы. Оба эти аргумента необходимы для работы этой функции.
Массив можно ввести как массив или именованный диапазон.
Вставка функции PERCENTILE
Функцию ПРОЦЕНТИЛЬ можно добавить в ячейку с помощью метода вставки, который предоставляет Excel. Если у вас есть проблемы с запоминанием того, как использовать синтаксис для этой функции, вставка его — лучший вариант.
Чтобы использовать этот метод, сначала необходимо выбрать ячейку. Затем необходимо выбрать вкладку с формулами и нажать кнопку «Дополнительные функции» на ленте Excel. Совместимость следует выбирать вместе с выбором ПРОЦЕНТИЛЬ из списка.
Затем появляется окно аргументов функций, в которое нужно добавить массив данных и k-й процент.
Массив можно ввести с клавиатуры или выбрать из электронной таблицы, щелкнув стрелку вверх справа от поля массива. K-й процентиль можно ввести в десятичной форме или выбрать ссылку на ячейку, щелкнув стрелку справа от этого поля.
Примеры синтаксиса функций PERCENTILE
Ниже приведены несколько примеров использования функции ПРОЦЕНТИЛЬ в различных ситуациях.
= ПРОЦЕНТИЛЬ (диапазон1; 0,4) — 40-й процентиль
- Здесь функция использует именованный диапазон в качестве входных данных для массива, а выбранный k равен 0,4. В результате отобразится 40-й процентиль названного диапазона range1.
= ПРОЦЕНТИЛЬ (диапазон1; D1)
- Здесь функция использует именованный диапазон в качестве входных данных для массива, а выбранный k — D1. В результате процентиль диапазона 1 будет отображаться в зависимости от значения k, которое отображается в ячейке D1.
= ПРОЦЕНТИЛЬ (диапазон1,80%)
- 5 функция использует именованный диапазон в качестве входных данных для массива, и выбранный k равен 80%. В результате перцентиль именованного диапазона range1 будет отображать 80-й процентиль.
= ПРОЦЕНТИЛЬ (C5: C14.; E5)
- В этом последнем примере синтаксиса ссылочный диапазон C5: C14 служит массивом, а k находится в ссылке на ячейку E5. Процентиль ссылочного диапазона C5: C14 будет отображаться в зависимости от k, которое отображается в ячейке E5.
Как видно из приведенных выше примеров, есть несколько различных способов отображения аргументов в функции.
Практический пример функции PERCENTILE
В этом разделе я покажу вам полный пример вставки функции PERCENTILE, когда функция ищется из списка функций. Будет найден 20-й процентиль набора данных. См. Простой набор данных на иллюстрации ниже.
Чтобы использовать функцию вставки, необходимо щелкнуть ячейку и щелкнуть вкладку формул.
Затем можно щелкнуть функцию вставки. Введите процентиль в появившейся строке поиска и выберите процентиль из списка.
Затем необходимо выбрать диапазон, процентиль которого вы хотите найти, после щелчка в поле массива. За этим следует добавление числа от .1 до 1 после щелчка по полю с меткой k.
Добавление функциональных аргументов
После нажатия кнопки ОК в результате появляется 20-й процентиль набора данных.
Процентильный результат
Это означает, что 2 — это место, где находится 20% данных. Итак, 2 находится в 20-м процентиле набора данных.
Подобные функции
Есть две другие функции, которые используются для определения процентилей. Совсем недавно Microsoft создала новые функции процентилей, специфичные для включения и исключения процентилей в результатах.
Каждая из этих функций кратко описана ниже.
PERCENTILE.EXC — Это показывает процентиль, исключая процентиль, выбранный в результатах.
PERCENTILE.INC — Эта функция даст тот же результат, что и функция процентиля, потому что они обе включают выбранный процентиль в результаты.
Рекомендации
Microsoft. (нет данных). ПРОЦЕНТИЛЬ функция. Получено 5 января 2020 г. с сайта https://support.office.com/en-us/article/percentile-function-91b43a53-543c-4708-93de-d626debdddca.
Статьи по Теме
Чтобы узнать больше об использовании функций в Excel, я рекомендую следующую книгу. Я использую Библию Excel в течение многих лет, чтобы лучше понять все аспекты этого продукта Microsoft.
Как рассчитать процент с помощью формул в Excel.
Если вам необходимо работать с процентами, вы будете рады узнать, что в Excel есть инструменты, облегчающие вашу задачу.
Вы можете использовать Excel для подсчета ваших ежемесячных показателей прибыли и убытков в процентах. Будь это рост издержек или процентное соотношение объема продаж из месяца в месяц, вы всегда будете в курсе ключевых показателей вашего бизнеса. Excel поможет вам в этом.
Вы также узнаете, как работать с процентами на продвинутом уровне, вычисляя среднее арифметическое, а также величину процентиля, что может быть полезным во многих случаях.
Шаг за шагом это руководство научит вас рассчитывать проценты в Excel. Давайте посмотрим на некоторые процентные формулы Excel, функции и советы на примере листа с бизнес расходами и листа со школьными оценками.
Вы получите навыки, которые позволят вам умело работать с процентами в Excel.
Деморолик
Вы можете посмотреть полное руководство в деморолике выше или прочитать пошаговое описание ниже. Для начала скачайте исходные бесплатные файлы: Excel percentages worksheets Мы будем использовать их в процессе выполнения упражнений.
1. Ввод данных в Excel
Вставьте следующие данные (или откройте загруженный файл «persentages.xlsx«, что находится в исходных файлах этого руководства). В нем есть лист Expenses (Расходы). Позже мы будем использовать лист Grades (Оценки).
Данные листа с процентами
Рассчитайте увеличение процента
Предположим, что вы ожидаете увеличение расходов на 8% в следующем году и вы хотите видеть эти значения.
Перед тем как начать писать формулы, полезно знать, что в Excel вы можете делать расчеты используя и знак процента (20%), и десятичные дроби (0.2 или просто .2). Символ процента для Excel это всего лишь форматирование.
Мы хотим увидеть конечную сумму, не только величину роста.
Шаг 1
В ячейке A18 напишите с 8%-ным ростом. Поскольку в этой ячейке и текст, и число, Excel будет считать, что вся ячейка содержит текст.
Шаг 2
Нажмите Tab, потом в ячейке B18 напишите следующую формулу: =B17 * 1.08
Или вы можете использовать следующую формулу: =B17 * 108%
Полученная сумма будет 71,675, как показано ниже:
Расчет увеличения процента в Excel
3. Расчет уменьшения процента
Может быть, вы думаете, что расходы наоборот уменьшатся на 8%. Чтобы увидеть результат, формула будет похожей. Начните с показа общей уменьшенной суммы, не только величины уменьшения.
Шаг 1
В ячейке A19, напишите с 8%-ным уменьшением.
Шаг 2
Нажмите Tab, потом в ячейке B19 напишите следующую формулу: =B17 * .92
Или вы можете написать формулу по-другому: =B17 * 92%
Результат будет 61,057.
Расчет уменьшения процента в Excel
Шаг 3
Если нужна возможность менять размер процента роста или уменьшения цены, то вы можете написать формулы следующим образом:
Формула для роста на 8% в ячейке B18 будет =B17 + B17 * 0.08
Шаг 4
Формула для уменьшения на 8% в ячейке B19 будет =B17 – B17 * 0.08
В этих формулах вы сможете просто менять .08 на любое другое число, чтобы получить результат с другим размером процента.
4. Расчет величины процента
Теперь перейдем к формуле расчета величины процента. Что если вы хотите знать сколько составляют эти 8%? Чтобы сделать это, умножьте общую сумму в B17 на 8 процентов.
Шаг 1
В ячейке A20 напишите 8% от итоговой суммы.
Шаг 2
Нажмите клавишу Tab, и в ячейке B20 напишите следующую формулу: =B17 * 0.08
Или вы можете записать эту формулу следующим образом: =B17 * 8%
Результатом будет 5,309.
Расчет величины процента в Excel
5. Внесение изменений без редактирования формул
Если вы хотите изменять процент без редактирования формул, то необходимо поместить его в отдельную ячейку. Начнем с названий строк.
Шаг 1
В ячейке A22 напишите Изменения. Нажмите клавишу Enter.
Шаг 2
В ячейке A23 напишите Наибольшая сумма. Нажмите клавишу Enter.
Шаг 3
В ячейке A24 напишите Наименьшая сумма. Нажмите клавишу Enter.
На листе это должно выглядеть следующим образом:
Лист Excel с дополнительными строками
Теперь добавим проценты и формулы справа от заголовков.
Шаг 4
В ячейке B22 напишите 8%. Нажмите клавишу Enter.
Шаг 5
В ячейке B23 напишите следующую формулу, которая рассчитает итоговую сумму с добавлением 8%: =B17 + B17 * B22
Шаг 6
В ячейке B24 напишите следующую формулу, которая рассчитает итоговую сумму за минусом 8%: =B17 * (100% — B22)
Если в ячейке B22 вы напишите 8%, то Excel автоматически отформатирует эту ячейку в виде процентов. Если же вы напишите .08 или 0.08, Excel сохранит эту ячейку как есть. Вы всегда можете поменять формат ячейки на формат в виде процентов, нажав на ленте кнопку Percent Style:
Кнопка Percent Style и ее действие
Совет: вы также можете отформатировать числа в виде процентов при помощи сочетания клавиш Ctrl + Shift + % для Windows или Command + Shift + % для Mac.
6. Расчет изменения размера процента
Вы также можете захотеть рассчитать, как изменяется размер процента от месяца к месяцу. Это даст вам представление о том, растут или падают ли ваши расходы. Давайте сделаем это ниже в колонке С.
Общее правило для подсчета изменения размера процента следующее:
=(новое значение — старое значение) / новое значение
Если январь — это первый месяц, то у него нет изменения размера процента. Первое изменение по сравнению с январем будет в феврале и рядом с данными февраля мы напишем следующее:
Шаг 1
Чтобы рассчитать первое изменение размера процента, внесем в ячейку C5 следующую формулу для его расчета: =(B5-B4)/B5
Шаг 2
Excel отобразит результат как дробь, поэтому нажмите кнопку Percent Style на ленте (или используйте указанное выше сочетание клавиш), чтобы отобразить число в виде процента.
Кнопка Percent Style для отображения в виде процентов
Теперь, когда у нас есть процент разности Февраля с Январем, мы можем автоматически заполнить колонку с формулами, чтобы узнать изменение размера процента для оставшихся месяцев.
Шаг 3
Наведите мышку надо точкой в нижнем правом углу ячейки, в которой стоит значение -7%.
Точка для автозаполнения в Excel
Шаг 4
Когда указатель мыши изменится на перекрестие, выполните двойной щелчок мышью.
Если вы не знакомы с функцией автозаполнения, прочитайте про технику 3 в моей статье о техниках работы с Excel:
Шаг 5
Выберите ячейку B3 (c заголовком «Сумма»).
Шаг 6
Наведите указатель мышки на точку для автозаполнения и перетащите ее на одну ячейку вправо, в ячейку С3. Так вы продублируете заголовок вместе с его форматированием.
Шаг 7
В ячейке С3 замените заголовок на % Изменения.
Все изменения размеров процента подсчитаны
7. Расчет процента от общей суммы
Последней техникой на этом лиcте будет расчет процента от общей суммы за каждый месяц. Представьте этот процент как круговую диаграмму, в которой каждый месяц составляет один сектор, а сумма всех секторов составляет 100%.
Неважно, рассчитываете вы его с помощью Excel или на бумаге, будет использоваться следующая простая операция деления:
Значение одного элемента/общая сумма
. которую надо представить в виде процента.
В нашем случае мы разделим каждый месяц на общую сумму, которая находится внизу колонки B.
Шаг 1
Выберите ячейку C3 и выполните автозаполнение одной ячейки, до D3.
Шаг 2
Поменяйте текст в ячейке D3 на % от общей суммы.
Шаг 3
В ячейке D4 напишите эту формулу, только пока не нажимайте Enter (Ввод): =B4/B17
Шаг 4
Перед нажатием клавиши ввода, нам надо убедиться, что при автозаполнении не будет ошибок. Поскольку мы будем автоматически заполнять колонки вниз, делитель (сейчас это B17), не должен изменяться. Ведь если он изменится, то все расчеты с февраля по декабрь окажутся неверными.
Убедитесь, что текстовый курсор все еще в ячейке перед делителем «B17».
Шаг 5
Нажмите клавишу F4 (на Mac — Fn + F4).
Так вы вставите знак доллара ($) перед значением колонки и строки, и делитель будет иметь следующий вид: $B$17. $B означает что колонка В не изменится на колонку С и т.д., а $17 означает что номер строки 17 не изменится на строку 18 и т.д.
Шаг 6
Убедитесь, что формула в итоге выглядит следующим образом: =B4/$B$17 .
Формула для процента от общей суммы
Шаг 7
Теперь можно нажать клавишу Enter (Ввод) и выполнить автозаполнение.
Нажмите Ctrl + Enter (на Mac — Command + ↩) чтобы ввести формулу не двигая курсор мыши в ячейку снизу.
Шаг 8
Нажмите кнопку Percent Style на ленте или используйте сочетание клавиш Ctrl + Shift + % (Command + Shift + %).
Шаг 9
Наведите указатель курсора мыши над точкой для автозаполнения в ячейке D4.
Шаг 10
Когда курсор изменится на перекрестие, выполните двойной щелчок мышью, чтобы автоматически заполнить ячейки внизу формулами.
Теперь для каждого месяца будет показываться величина его процента от общей суммы.
Заполненный процент от общей суммы
8. Процентное ранжирование
Ранжирование значений в виде процентного значения является статистическим методом. Вы, наверное, знакомы с ним со школы, когда вычисляется средний бал учеников, и это позволяет ранжировать их в виде процента. Чем выше оценки, тем выше процент.
Список чисел (оценок, в нашем случае) находится в группе ячеек, которые в Excel называются массивом. В массивах нет ничего особенного, поэтому не будем давать им определение. Просто Excel так называет диапазон ячеек, который вы используете в формуле.
В Excel существует две функции для вычисления процентного ранжирования. Одна из них включает начальные и конечные значения массива, а другая нет.
Посмотрите на вторую закладку листа: Grades (Оценки).
Процентное ранжирование в Excel — лист с оценками
Здесь список из 35 средних баллов, отсортированных по возрастанию. Первое, что мы хотим узнать, это процентное ранжирование для каждого среднего балла. Для этого мы будем использовать функцию =PERCENTRANK.INC. «INC» в этой функции значит «включая», поскольку она будет включать первый и последний баллы в списке. Если вы хотите исключить первый и последний баллы массива, то используйте функцию =PERCENTRANK.EXC.
В функции два обязательных аргумента и выглядит она следующим образом: =PERCENTRANK.INC(array, entry)
- array (массив) — это группа ячеек, которые составляют список (в нашем случае B3:B37)
- entry (значение) — это любое значение или ячейка в списке
Шаг 1
Выберите первую ячейку в списке C3.
Шаг 2
Напишите следующую формулу, но не нажимайте клавишу Enter (Ввод): =PERCENTRANK.INC(B3:B37
Шаг 3
Этот диапазон должен быть неизменным, чтобы мы могли применить автозаполнение всех ячеек сверху вниз.
Поэтому нажмите клавишу F4 (на Mac — Fn + F4), чтобы добавить знак доллара к значениям.
Формула должна выглядеть следующим образом: =PERCENTRANK.INC($B$3:$B$37
Шаг 4
В каждой ячейке в колонке С, мы увидим процентное ранжирование соответствующего значения в колонке B.
Нажмите на ячейку B3 и закройте скобки.
Формула в итоге такая: =PERCENTRANK.INC($B$3:$B$37,B3)
Функция процентного ранжирования
Теперь мы можем внести и отформатировать значения.
Шаг 5
Если необходимо, снова выберите ячейку С3.
Шаг 6
Выполните двойной щелчок мышью на точке автозаполнения ячейки С3. Это автоматически заполнит ячейки колонки ниже.
Шаг 7
На ленте, нажмите кнопку Percent Style, чтобы отформатировать колонку в виде процентов. В результате вы должны увидеть следующее:
Заполненная и отформатированная функция процентного ранжирования
Таким образом, если у вас хорошие оценки и ваш средний балл 3.98, то вы находитесь в 94 м процентиле.
9. Вычисление процентиля.
Используя функции PERCENTILE, вы можете рассчитать процентиль. С помощью этой функции вы можете ввести размер процента и получить значение из массива, которое соответствует этому проценту. Если точного значения в диапазоне нет, то Excel покажет то значение, которое «должно» быть в списке.
В каких случаях это необходимо? Например, если вы подаете заявку на университетскую программу, которая принимает только тех студентов, которые относятся к диапазону от 60 го процентиля. И вам надо узнать, какой средний бал соответствует 60%. Посмотрев на список, мы увидим, что 3.22 — это 59%, а 3.25 — это 62%. Используя функцию =PERCENTILE.INC мы узнаем точный ответ.
Формула строится так: =PERCENTILE.INC(array, percent rank)
- array (массив) — это диапазон ячеек, которые составляют список (как в предыдущем примере, B3:B37)
- rank (ранг) — это процент (или десятичная дробь от 0 до 1 включительно)
Также как и для функции процентного ранжированая, вы можете использовать функцию =PERCENTILE.EXC, которая исключает первое и последнее значения массива, но в нашем случае это не нужно.
Шаг 1
Выберите под списком ячейку В39.
Шаг 2
Вставьте в нее следующую формулу: =PERCENTILE.INC(B3:B37,60%)
Так как нам не надо применять автозаполнение, нет нужды делать массив неизменным.
Шаг 3
На ленте нажмите один раз клавишу Decrease Decimal (Уменьшить разрядность), чтобы округлить число до двух десятичных разрядов после запятой.
В результате в данном массиве 60 м перцентилем будет средний бал 3.23. Теперь вы знаете какие оценки нужны для того, чтобы быть принятым на программу.
Результат функции вычисления процентиля
Заключение
Проценты вовсе не сложные, и Excel рассчитывает их, используя те же правила математики, которые вы бы использовали, считая на бумаге. Excel также использует общепринятый порядок операций в тех случаях, когда в вашей формуле есть сложение, вычитание и умножение:
- Скобки
- Показатель степени
- Умножение
- Деление
- Сложение
- Вычитание
Я запоминаю этот порядок с помощью мнемонической фразы: Сказал Степан: Умора, Давайте Словим Вора.
Процентиль в excel что это
Функция Excel ПРОЦЕНТИЛЬ возвращает k-й процентиль значений в заданном диапазоне. k от 0 до 1, включая 0 и 1.
Примечание: ПРОЦЕНТИЛЬ был заменен на PERCENTILE.INC. Хотя ПРОЦЕНТИЛЬ по-прежнему доступен в текущих версиях Excel, с этого момента следует рассмотреть возможность использования новой функции, поскольку ПРОЦЕНТИЛЬ может быть недоступен в будущих версиях Excel.
Синтаксис
=PERCENTILE( array , k )
аргументы
- массив (обязательно): Массив или диапазон данных, по которым рассчитывается k-й процентиль.
- к (обязательно): Значение процентиля или десятичное значение в диапазоне от 0 до 1, включая 0 и 1. Например, k=0.9 означает 90-й процентиль значений в массиве, что означает, что 90% значений меньше или равны расчетный результат.
Возвращаемое значение
Функция ПРОЦЕНТИЛЬ возвращает числовое значение.
Примечания к функциям
- • ПРОЦЕНТИЛЬ возвращает #СТОИМОСТЬ! значение ошибки, если k не числовой.
- • ПРОЦЕНТИЛЬ возвращает #NUM! значение ошибки, если:
- к < 0 or k> 1;
- массив пусто.
Пример
Чтобы получить 90-й, 70-й, 50-й процентили массива, как показано ниже, скопируйте или введите приведенную ниже формулу в верхнюю ячейку F5 и нажмите Enter чтобы получить первый результат:
=ПРОЦЕНТИЛЬ( 3 млрд долларов: 11 млрд долларов , E5 )
√ Примечание: Знаки доллара ($) выше обозначают абсолютные ссылки, что означает, что аргумент массива в формуле не изменится при перемещении или копировании формулы в другие ячейки. Однако знаки доллара не добавляются к k поскольку вы хотите, чтобы он был динамичным. После ввода формулы перетащите маркер заполнения вниз, чтобы применить формулу к ячейкам ниже.
Связанные функции
Функция Excel ПРОЦЕНТИЛЬ.ВКЛ возвращает k-й процентиль значений в заданном диапазоне. k от 0 до 1, включая 0 и 1.
Функция Excel ПРОЦЕНТИЛЬ.ИСКЛ возвращает k-й процентиль значений в заданном диапазоне. k составляет от 1/(n+1) до n/(n+1), в том числе, где n — количество значений в диапазоне.
Функция Excel МАЛЕНЬКИЙ возвращает k-е наименьшее значение (1-е наименьшее значение, 2-е наименьшее значение и т. д.) в массиве или диапазоне данных.
Функция Excel НАИБОЛЬШИЙ возвращает k-е наибольшее число в массиве или диапазоне данных.
Функция RANK возвращает ранг числа по сравнению с другими числами в том же списке. Одинаковый ранг будет присвоен повторяющимся значениям в списке.
Лучшие инструменты для работы в офисе
Kutools for Excel — Помогает вам выделиться из толпы
Хотите быстро и качественно выполнять свою повседневную работу? Kutools for Excel предлагает 300 мощных расширенных функций (объединение книг, суммирование по цвету, разделение содержимого ячеек, преобразование даты и т. д.) и экономит для вас 80 % времени.