Как рассчитать сумму продаж в excel • процентное ранжирование

Содержание

Сегодня нам обычно приходится работать с большими объемами данных, независимо от того, посвящены ли мы исследованиям или другим секторам.

Для этого требуется уметь работать с ними и часто сравнивать и упорядочивать данные друг с другом. И в этом смысле может быть полезно использовать измерения положения, с помощью которых можно разделить общие значения того, что измеряется на несколько частей, чтобы определить, в каком положении находится одно из них. Один из самых известных и полезных — это процентиль. Но … что такое процентиль? Как рассчитать процентили? Давайте посмотрим на это в этой статье.

Статья по теме: «Психометрия: что это такое и за что отвечает?»

Квартили и интерквартильный интервал (IQR) в EXCEL

Для вычисления квартилей в MS EXCEL существует специальная функция КВАРТИЛЬ() . В этой статье дадим определение квартилей и научимся их вычислять для выборки и для непрерывного распределения. Также вычислим интерквартильный интервал.

Квартили (Quartiles) — значения, которые делят выборку (набор значений) на четыре части, содержащие приблизительно равное количество наблюдений (по 25%).

Поясним определение квартиля на примере. Пусть имеется выборка , состоящая из 50 значений в ячейках А7:А56 (см. файл примера , лист Квартиль-выборка). Для наглядности отсортируем значения по возрастанию и построим гистограмму .

Чтобы разделить выборку на 4 части достаточно 3-х квартилей .

Первый квартиль (или нижний квартиль , Q1) делит выборку , на 2 части: примерно 25% значений в выборке меньше Q1, остальные 75% — больше. Для вычисления 1-го квартиля используйте формулу =КВАРТИЛЬ.ВКЛ(A7:A56;1) . Для нашей выборки формула вернет значение 224. Значения 224 нет в выборке , формула произвела интерполяцию на основе 2-х соседних значений 223 и 227.

Примечание : Функция КВАРТИЛЬ.ВКЛ() появилась в MS EXCEL 2010. В более ранних версиях использовалась аналогичная ей функция КВАРТИЛЬ() .

Чтобы убедиться, что примерно 25% значений меньше, чем 224, используем формулу =СЧЁТЕСЛИ(A7:A56;» . В результате получим, что 26% меньше, чем 1-й квартиль .

Чем в выборке больше значений и меньше повторов , тем точнее деление выборки квартилями на четверти.

Примечание : Первый квартиль — это то же самое, что и 25-я процентиль . Подробнее см. статью про процентили .

Второй квартиль (или медиана , Q2) также делит выборку , на 2 равные части: половина чисел множества больше, чем медиана , а половина чисел меньше, чем медиана . Для вычисления 2-го квартиля используйте формулу =КВАРТИЛЬ.ВКЛ(A7:A56;2) или =МЕДИАНА(A7:A56)

Третий квартиль (или верхний квартиль , Q3) делит выборку , на 2 части: примерно 75% значений в выборке меньше Q3, остальные 25% — больше. Для вычисления 3-го квартиля используйте формулу =КВАРТИЛЬ.ВКЛ(A7:A56;3) или =ПРОЦЕНТИЛЬ.ВКЛ(A7:A56;0,75)

Примечание : Третий квартиль — это то же самое, что и 75-я процентиль .

Второй аргумент функции КВАРТИЛЬ.ВКЛ() может также принимать значения 0 и 4. В первом случае функция вернет минимальное значение , во втором – максимальное .

Описание и примеры

Процентиль (в разных источниках и версиях программ называется также перцентиль, персентиль и центиль) — статистическая мера, указывающая значение, которое заданная случайная величина не превышает с указанной вероятностью. Например, фраза «95-й процентиль равен 7» означает, что 95% всех измеренных величин не достигает значения 7 и только 5% всех измеренных величин превышает это значение.

Когда провайдер услуг по размещению оборудования клиента в дата-центре, подключению его к электропитанию и каналам связи (колокации) выставляет ежемесячный счет на оплату услуг на основании 95-го процентиля (англ. bustable billing), это означает, что провайдер отсекает 5% пиковых значений трафика за месяц. Данный метод расчета позволяет пользователям иметь короткие периоды с очень высоким трафиком, возможно в десятки раз выше, чем «обычный» трафик, без дополнительной оплаты. Если счет выставляется ежемесячно, то пользователи могут иметь очень большой трафик (в пределах выделенной пропускной способности канала) в течение 24 × 30 × 0.05 = 36 часов без дополнительной оплаты. Пример такого трафика приводится ниже на рисунке.

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

ПРОЦЕНТИЛЬ.ВКЛ (функция ПРОЦЕНТИЛЬ.ВКЛ) — Служба поддержки Майкрософт

Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel для Mac 2011 Excel Starter 2010 Еще…Меньше

Возвращает k-ю процентиль для значений диапазона, где k — число от 0 и 1 (включая эти числа).

Эта функция используется для определения порога приемлемости. Например, можно принять решение экзаменовать только тех кандидатов, которые набрали большее количество баллов, чем 90-я персентиль.

Синтаксис

ПРОЦЕНТИЛЬ.ВКЛ(массив;k)

Аргументы функции ПРОЦЕНТИЛЬ.ВКЛ описаны ниже.

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

Замечания

Если массив пуст, процентиль. Inc возвращает #NUM! значение ошибки #ЗНАЧ!.
Если k не является числом, то процентиль. Inc возвращает #VALUE! значение ошибки #ЗНАЧ!.
Если k < 0 или k > 1, процентиль.

Inc возвращает #NUM! значение ошибки #ЗНАЧ!.
Если k не кратно 1/(n — 1), функция ПРОЦЕНТИЛЬ.ВКЛ производит интерполяцию для определения значения k-ой процентили.

Пример

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

Данные
1
3
2
4
Формула	Описание	Результат
=ПРОЦЕНТИЛЬ. ВКЛ(A2:A5;0.3)	Тридцатая процентиль списка в диапазоне A2:A5.	1,9

Среднее геометрическое

При несимметричном распределении данных среднее арифметическое не будет обобщающим показателем распределения.

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

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

Если распределение данных логарифма приблизительно симметричное, то среднее геометрическое подобно медиане и меньше, чем среднее необработанных данных.

Как в Excel посчитать процент прироста прибыли

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

Если нужно посчитать процент прироста прибыли от продажи определенной продукции относительно предыдущего периода времени, то действовать нужно так:

В таблице столбики «B» и «C» несут информацию об общих продажах за тот или иной месяц. Расчет будем производить в столбике «D», поэтому ему нужно задать процентный формат.
Поставьте курсор на ячейку D и пропишите формулу , затем нажмите на Enter, чтобы программа самостоятельно все посчитала.
Растяните формулу на всю колонку с помощью специального маркера. Прибыль может быть и отрицательной, как на примере при продаже футболок.

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

В примере есть 3 строки со значениями за январь, февраль и март. Вычисление разницы начинается с февраля, т.к. в январе нет данных для сравнения. Разница прироста прибыли будет рассчитываться с помощью формулы , введенной в ячейку C10. В столбике «Изменение» нужно выставить процентный формат ячеек, чтобы понять, как менялись продажи в определенное время.

Для сравнения со значением в определенной ячейке потребуется преобразовать ссылку на абсолютную. Например, требуется узнать процент прироста прибыли сравнительно с первым месяцем в таблице – январем. Для этого координаты его значения принимают такой вид , то есть формула будет выглядеть так: . В этом случае координаты января зафиксируются и будут применять ко всем строкам при растягивании маркера.

Как посчитать разницу в процентах в Excel

При расчетах в Excel часто требуется посчитать разницу в процентах между двумя числами.

Например, для расчета разницы в процентах между двумя числами А и В используется формула:

(B-A)/A = Разница между двумя числами в процентах

На практике, при использовании этой формулы важно определить какое из нужных вам чисел является “А”, а какое “В”. Например, представим что вчера у вас было в наличии 8 яблок, а сегодня стало 10 яблок

Таким образом количество яблок относительно вчерашнего у вас изменилось на 25% в большую сторону. В том случае, если у вас вчера было 10 яблок, а сегодня стало 8 яблок, то количество яблок, относительно вчерашнего дня сократилось на 20%.

Таким образом, формула, позволяющая корректно вычислить изменения в процентах между двумя числами выглядит так:

Как найти процент между двумя числами из двух колонок в Excel

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

Для расчетов нам потребуется формула:

=(C2-B2)/B2

Протяните формулу на все строки со значениями;
Измените формат ячейки для колонки “Изменение, %” на “Процентный”.

Как найти процент между двумя числами из двух строк в Excel

В том случае, если у нас есть список в котором в каждой строке указан период с данными продаж и нам нужно вычислить изменения от периода к периоду, то нам потребуется формула:

=(B3-B2)/B2

Где B2 это первая строчка, B3 вторая строчка с данными. После ввода формулы не забудьте отформатировать ячейки с вычислениями как “Процентные”. Выполнив все выше описанные действия, вы получите результат:

Если у вас есть необходимость вычислить изменения относительно какой-то конкретной ячейки, то важно зафиксировать ее значками “$”. Например, если перед нами стоит задача вычислить изменения объема продаж относительно Января, то формула будет такой:. =(B3-B2)/$B$2

=(B3-B2)/$B$2

На примере выше значения продаж каждого месяца сравниваются с данными продаж Января.

Находим процент от числа

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

Математическая формула для расчета выглядит следующим образом:

Число 2 = Процент (%) * Число 1, где:

Число 1 – исходное число, процент по которому нужно вычислить
Процент – соответсвенно, величина самого процента
Число 2 – финальное числовое значение, которое требуется получить.

Например, давайте узнаем, какое число составляет 15% от 90.

Выбираем ячейку, в которой будем выводить результат и пишем формулу выше, подставляя в нее наши значения: =15%*90 .Примечание: Так как результат должен быть в абсолютном выражении (т.е. в виде числа), формат ячейки – “общий” или “числовой” (но не “процентный”).
Нажимаем клавишу Enter, чтобы получить результат в выбранной ячейке.

Подобные знания помогают решать множество математических, экономических задач, физических и других задач. Допустим, у нас есть таблица с продажами обуви (в парах) за 1 квартал, и мы планируем в следующем продать на 10% больше. Нужно определить, какому количеству пар для каждого наименования соответствуют эти 10%.

Чтобы выполнить задачу, выполняем следующие шаги:

Для удобства создаем новый столбец, в ячейки которого будем выводить результаты расчетов. Выбираем первую ячейку столбца (на считая шапки) и пишем в ней формулу выше, заменив конкретное значение сходного числа на адрес ячейки: =10%*B2 .
После этого жмем клавишу Enter, и результат сразу же отобразится в ячейке с формулой.
Если мы хотим избавиться от цифр после запятой, так как в нашем случае количество пар обуви может исчисляться только целыми числами, переходим в формат ячейки (как это сделать, мы разобрали выше), где выбираем числовой формат с отсутствием десятичных знаков.
Теперь можно растянуть формулу на оставшиеся ячейки столбца.

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

Допустим, наша таблица содержит такой столбец “E” (Значение %).
Пишем в первой ячейке результирующего столбца все ту же формулу, только теперь и конкретное значение процента меняем на адрес ячейки с содержащейся в ней процентной величиной: =E2*B2 .
Щелкнув Enter получаем результат в заданной ячейке. Осталось только растянут его на нижние строки.

Примеры вычисления процентилей

Рассмотрим для примера набор данных из N = 12 значений, представленный ниже в таблице 1. Для этого набора данных мы рассчитаем 40-й процентиль с помощью всех трех описанных выше методов. Значения набора данных упорядочены по величине от меньших к большим и каждому из них присвоен ранг от 1 до 12. Мы выполним расчет по трем определениям и сравним результаты наших расчетов.

Таблица 1. Набор данных для примера расчетов

Значение	Ранг
2	1
4	2
8	3
9	4
11	5
13	6
15	7
17	8
20	9
24	10
29	11
30	12

Расчет по методу 1

Определяем порядковый ранг n по определению 1 для 40-го процентиля:

Поскольку порядковый ранг n не является целым числом, мы округляем его до n = 5. По таблице 1, значение 40-го процентиля для n = 5 равно 11.

Расчет по методу 2

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

Порядковый ранг n = 5. Дробная часть {x} = 0,4 результата вычисления дробного порядкового номера x затем используется для дальнейшего расчета величины процентиля v по двум смежным значениям из исходного набора данных: значения с рассчитанным порядковым номером v_n и следующего за ним большего значения v_n+1:

Расчет по методу 3

Этот метод является еще одной альтернативой методу ближайшего ранга. В нем используется линейная интерполяция между соседними значениями в наборе данных. Определяем дробный порядковый номер x:

Порядковый ранг n = 5. Дробная часть {x} = 0,2 результата вычисления дробного порядкового номера x затем используется для дальнейшего расчета величины процентиля v по двум смежным значениям из исходного набора данных: значения с рассчитанным порядковым номером v_n и следующего за ним большего значения v_n+1:

Метод оплаты по 95-му процентилю является стандартным промышленным методом измерения ширины полосы пропускания (пропускной способности) канала, используемым интернет-провайдерами и дата-центрами.

Функции ПРОЦЕНТИЛЬ в Excel

В Excel доступно три варианта функции процентили. Если вы используете Excel 2010 или более поздние версии, у вас будет доступ ко всем этим трем функциям.

PERCENTILE — это старая функция, которая теперь сохранена в целях обратной совместимости. Вы можете использовать это, но лучше использовать новые (если они есть в вашей версии Excel). Результатом этой функции является значение от 0 до 1
ПРОЦЕНТИЛЬ.ВКЛ – это новая формула (работает точно так же, как функция ПРОЦЕНТИЛЬ). В большинстве случаев это функция, которую вам нужно использовать. Результатом этой функции является значение от 0 до 1
ПРОЦЕНТИЛЬ.ИСКЛ – работает также как функция ПРОЦЕНТИЛЬ.ВКЛ с одним отличием – результатом этой функции будет значение от 0 до 1, но исключаются также значения K от 0 до 1/(N+1) как N/(N+1) до 1 (где N – размер выборки)

Проще говоря, в большинстве случаев используйте ПРОЦЕНТИЛЬ.ВКЛ (а если вы используете Excel 2007 или более ранние версии, используйте функцию ПРОЦЕНТИЛЬ)

Ниже приведен синтаксис функции ПРОЦЕНТИЛЬ.ВКЛ в Excel:

массив – это диапазон ячеек, в котором у вас есть значения, для которых вы хотите узнать K-й процентиль
k — это значение от 0 до 1, которое дает значение k-го процентиля. Например, если вы хотите рассчитать значение 90-го процентиля, это будет 0,9 или 90 %, а для значения 50-го процентиля это будет 0,5 или 50 %.

Синтаксис остается прежним для функций ПРОЦЕНТИЛЬ и ПРОЦЕНТИЛЬ.ИСКЛ.