Как посчитать в эксель уменьшение убытка

Как посчитать процент отклонения в Excel по двум формулам

Понятие процент отклонения подразумевает разницу между двумя числовыми значениями в процентах. Приведем конкретный пример: допустим одного дня с оптового склада было продано 120 штук планшетов, а на следующий день – 150 штук. Разница в объемах продаж – очевидна, на 30 штук больше продано планшетов в следующий день. При вычитании от 150-ти числа 120 получаем отклонение, которое равно числу +30. Возникает вопрос: чем же является процентное отклонение?

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

Процент отклонения вычисляется через вычитание старого значения от нового значения, а далее деление результата на старое значение. Результат вычисления этой формулы в Excel должен отображаться в процентном формате ячейки. В данном примере формула вычисления выглядит следующим образом (150-120)/120=25%. Формулу легко проверить 120+25%=150.

Обратите внимание! Если мы старое и новое число поменяем местами, то у нас получиться уже формула для вычисления наценки.

Ниже на рисунке представлен пример, как выше описанное вычисление представить в виде формулы Excel. Формула в ячейке D2 вычисляет процент отклонения между значениями продаж для текущего и прошлого года: =(C2-B2)/B2

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

Правильно со скобками введите формулу в ячейку D2, а далее просто скопируйте ее в остальные пустые ячейки диапазона D2:D5. Чтобы скопировать формулу самым быстрым способом, достаточно подвести курсор мышки к маркеру курсора клавиатуры (к нижнему правому углу) так, чтобы курсор мышки изменился со стрелочки на черный крестик. После чего просто сделайте двойной щелчок левой кнопкой мышки и Excel сам автоматически заполнит пустые ячейки формулой при этом сам определит диапазон D2:D5, который нужно заполнить до ячейки D5 и не более. Это очень удобный лайфхак в Excel.

Альтернативная формула для вычисления процента отклонения в Excel

В альтернативной формуле, вычисляющей относительное отклонение значений продаж с текущего года сразу делиться на значения продаж прошлого года, а только потом от результата отнимается единица: =C2/B2-1.

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

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

Так как в Excel по умолчанию приоритет операции деления выше операции вычитания в данной формуле нет необходимости применять скобки.

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

Часто бывает два типа процентов, которые нужно вычислить в Excel.

• Вычисление процента как доли от указанного значения (например, если вы съели 4 из 5 манго, какой процент манго вы съели).
• Расчет процентного изменения (г / г или м / м). Обычно это используется в отчетах о продажах, когда менеджер хочет знать, каков рост продаж год за годом или квартал за кварталом.

В этом уроке я покажу вам формула для расчета процентов в Excel а также отформатировать ячейку так, чтобы числа отображались в процентах (а не в десятичных дробях).

Расчет процента как пропорции

Примерами этого могут быть поиск покрытия продаж или статус завершения проекта.

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

Это известно как покрытие продаж. Исходя из этого, он / она может работать над моделью охвата продаж и / или каналами, чтобы привлечь больше клиентов.

Вот пример покрытия продаж для трех регионов:

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

Вот формула Exxcel для расчета процента в Excel:
= Фактически охваченные / Общее количество потенциальных клиентов
В Excel вы можете ввести = B3 / B2, чтобы рассчитать процент для региона A.

Обратите внимание, что это даст значение в формате General / Number, а не в процентном формате. Чтобы он выглядел как процент, вам нужно применить процентный формат (показанный далее в этом руководстве).

Расчет процентного изменения в Excel

Процентное изменение широко используется и отслеживается в различных сферах бизнеса. Аналитики обычно говорят о росте выручки компании или росте прибыли в процентах.

Компании часто отслеживают процентное изменение затрат ежемесячно / ежеквартально.

Вот простой пример использования процентного изменения:

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

Вот как рассчитать процентные изменения в Excel:

Изменение выручки в процентах = (выручка за 2016 г. — выручка за 2015 г.) / выручка за 2015 г.

В этом примере формула процента в ячейке D2 выглядит так:
= (C2-B2) / B2
Опять же, обратите внимание, что это даст значение в формате General / Number, а не в процентном формате. Чтобы он выглядел как процент, вам нужно применить процентный формат (показанный далее в этом руководстве).

Итак, давайте посмотрим, как применить процентное форматирование в Excel.

Применение процентного форматирования в Excel

Существует несколько способов применения процентного форматирования в Excel:

1. С помощью сочетания клавиш.
2. Используя ленту.
3. Использование диалогового окна «Ячейки числового формата».

Метод 1. Использование сочетания клавиш

Чтобы применить процентный формат к ячейке или диапазону ячеек:

• Выберите ячейку (я).
• Используйте сочетание клавиш — Ctrl + Shift +% (удерживая клавиши Ctrl и Shift, нажмите клавишу%).

Обратите внимание, что здесь применяется процентный формат без десятичных знаков. Так что, если вы используете его на 0.22587, он составит 23%.

Метод 2 — Применение процентного формата с помощью ленты

Чтобы применить процентный формат к ячейке (или диапазону ячеек):

• Выделите ячейку (или диапазон ячеек).
• Перейдите на главную -> Число -> Стиль в процентах.

Обратите внимание, что здесь применяется процентный формат без десятичных знаков. Так что, если вы используете его на 0.22587, он составит 23%.

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

Метод 3 — применение процентного формата с помощью диалогового окна «Форматирование ячеек»

Использование диалогового окна «Форматирование ячеек» дает пользователю большую гибкость при форматировании.

Вот как с его помощью применить процентный формат:

• Выделите ячейку (диапазон ячеек).
• Перейдите на главную -> Номер и щелкните средство запуска диалогового окна (значок с наклонной стрелкой в ​​правом нижнем углу группы). Откроется диалоговое окно «Формат ячейки».
  • Также можно использовать сочетание клавиш — Ctrl + 1 (удерживая клавишу управления, нажмите 1).
  • В диалоговом окне «Формат ячеек» на вкладке «Число» выберите «Процент» в списке «Категория».
  • Если вы хотите изменить количество отображаемых десятичных знаков, используйте параметр «Десятичные разряды» справа. Например, если вы хотите отобразить значение с двумя десятичными знаками, сделайте его равным 2.
  • Щелкните ОК.

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

  Все способы расчета процента в Microsoft Excel – формулы и примеры использования

  Рассчитывать проценты и выполнять действия с ними удобнее всего в программе Excel в пакете Microsoft Office, поскольку все, что требуется – это указать значения и нужную формулу.

  Процент – это сотая часть целого числа, которая на бумаге обозначается знаком % или десятичными дробями (70% = 0,70). Стандартное выражение для расчетов процента – Целое/Часть*100, но благодаря Excel вручную ничего считать не придется.

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

  Простота работы с программой заключается в том, что пользователю достаточно ввести значения целого и его части (или выбрать из введённых ранее данных), и указать принцип вычисления, а расчеты Excel произведет самостоятельно. В Excel процент вычисляется так — Часть/Целое = Процент, а умножение на 100 происходит автоматически, когда пользователь выбирает процентный формат:

  кликнуть на ячейке правой кнопкой мыши – Формат ячеек – Процентный;

  

  

  

  • или Верхнее меню – вкладка Главная – Число – выбрать из выпадающего меню нужный формат.

  Для проведения вычислений возьмем расчет выполнения рабочего плана:

  1. В таблице три столбика — с продуктом, планом выполнения и фактическим результатом.
  1. В D2 надо вписать «=C2/B2».
  2. Заранее следует столбец D перевести в Процентный формат.
  3. Скопировать выражение по всему столбику используя маркер заполнения (растянуть на весь столбец).

  Программа самостоятельно рассчитает процент выполнения плана для каждого продукта.

  Процент от числа

  В Excel можно вычислить цифру, зная только ее долю: %*Часть = Целое. Допустим, необходимо вычислить, что составляет 7% от 70. Для этого:

  • ввести «=7%*70»;
  • нажать Enter и получить необходимый результат.

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

  Процент от суммы

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

  

  • для расчета используется стандартное выражение «Часть/Целое*100», но с одной поправкой – ссылка на нужную ячейку с итоговой суммой должна быть абсолютной – при записи перед ней ставиться знак $;
  • итоговое значение записано в ячейке В10. Расчет для столбика С будет следующей – «=B2/$B$10»;
  • далее надо растянуть с помощью маркера заполнения выражение на весь столбик, поскольку ссылка была абсолютной ($B$10), то программа рассчитает все продукты самостоятельно.

  Если же данные разбросаны по всей таблице, то необходимо использовать формулу СУММЕСЛИ — она складывает значения, которые отвечают заданным параметрам, в примере – заданные продукты. Формула для примера будет выглядеть так – «=СУММЕСЛИ (диапазон критерий; диапазон сложения)/общая сумма»:

  

  • в столбце А написаны названия продуктов – это диапазон значений;
  • в столбе В – количество всех продуктов – это диапазон сложения;
  • в ячейке Е1 надо вписать критерий – название рассчитываемого фрукта, а итог записан в ячейке В10;
  • выражение выглядит так: «=СУММЕСЛИ(A2:A9;E1;B2:B9)/$B$10».

  Таким образом считается каждый параметр, т.е. продукт.

  Расчет изменения в процентах

  Сравнение двух долей также возможно с помощью Эксель. Для этого можно просто найти значения и вычесть их (из большего меньшее), а можно воспользоваться формулой прироста/уменьшения. Если надо сравнить числа А и В, то формула выглядит так «(B-A)/A = разница». Рассмотрим пример расчета в Excel:

  

  1. В колонке А вписан товар, в колонке В и С его цена за август и сентябрь соответственно.
  2. Изменение в долях будет рассчитываться в колонке D.
  3. В ячейке D2 вписать формулу «=(С2/В2)/В2».
  1. Растянуть формулу на весь столбец с помощью маркера автозаполнения.

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

  • в столбце В приведены данные за каждый месяц продаж;
  • в столбце С надо посчитать изменения. Формула будет такой – «=(B3-B2)/B2»;
  • если числа надо сравнить с конкретным показателем, то ссылку на нужную ячейку надо сделать абсолютной. Например, надо сравнить каждый месяц продаж с январем, для этого вписать в расчетную ячейку формулу «=(B3-B2)/$B$2».

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

  Расчет значения и общей суммы

  Зачастую, необходимо определить итоговую сумму зная только долю. В Excel это можно сделать двумя способами. Рассмотрим покупку ноутбука предположив, что он стоит 950 долларов. Продавец говорит, что это цена без учета налога НДС, который составляет 11%. Итоговую наценку можно узнать, сделав расчеты в Эксель:

  

  • необходимая формула «Общая сумма * % = Значение»;
  • в клетке С2 вписать «=A2*B2»;
  • итоговая наценка будет составлять 104,50 долларов.

  Второй способ расчета рассмотрим на другом примере. Допустим, при покупке ноутбука за 400 долларов продавец говорит, что цена посчитана с учетом 30% скидки. Узнать начальную цену можно так:

  

  1. Скидка в 30% — это снижение цены, поэтому надо узнать какую часть оплачивает покупатель: 100%-30% = 70%.
  2. Чтобы найти цену 70% от которой равны $400 надо использовать выражение «Часть/% = Общая сумма».
  3. В таблице в столбике А указано значение, в столбике В – процент, а в ячейке С2 — итоговая сумма.
  4. В клетку С2 вписать выражение «=А2/В2».

  Начальная стоимость составит 571,43 долларов.

  Как изменить значение на величину процента

  Часто приходиться увеличивать или уменьшать итоговое число на какую-то его долю, например, необходимо увеличить ежемесячные затраты на 20%, 30% и 35%:

  

  • в столбике А указаны ежемесячные затраты, в столбике В – доля увеличения, а в столбике С – итоговая сумма;
  • выражение для расчета такое – «=Затраты*(1+%)». С ссылками на клетки – «=A1*(1+20%)».

  Программа посчитает итог самостоятельно по всему столбику, если протянуть выражение с помощью маркера заполнения. Выражение для уменьшения суммы такое же, только со знаком минус – «=Значение*(1-%)».

  Операции с процентами

  С долями можно совершать такие же операции, как и с обычными числами: сложение, вычитание, умножение. Например, в Excel можно посчитать разницу в показателях между продажами компании с помощью команды AВS, которая возвращает абсолютную величину числа:

  

  • в столбике А – итог продаж за 2011 год, а в столбике В – за 2012 год. Необходимо найти в клетке В3 разницу между этими двумя числами;
  • изменить формат клетки В3 на Процентный;
  • вписать в клетку «=(В2/А2)/AВS(А2)», т.е. разделить продажи второго года на продажи первого и вернуть абсолютное значение последнего показателя;

  Разница между показателя составит 23 %.

  Можно также прибавить (или вычесть) процент к числу — рассмотрим действие на примере планирования отпуска:

  

  1. Допустим, что неделя отпуска будет больше в расходах на 30%, чем обычная неделя, а неделя после отпуска меньше привычных на 30%.
  2. Выражение будет выглядеть так «= Еженедельные расходы * (Еженедельные расходы + %)».
  3. В клетке В2 указаны обычные еженедельные расходы, в В3 – отпуск, а в В4 – после отпуска.
  4. В клетке В3 вписать «=В2+(В2*30%)», а в клетке В4 «=В2-(В2*30%)».

  Программа самостоятельно произведет вычисления и результаты будут 26000 рублей для отпускной недели и 14000 рублей после отпуска соответственно.

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

  1. В таблице указаны продукты и сумма покупки. Надо к сумме прибавить еще 6% надбавки.
  2. В клетке F3 надо вписать формулу «=Е3*6%».
  3. Для программы знак % означает, что она должна умножить введенное значение на 0,06 и в клетке F3 появиться результат умножение – 54.

  

  Все суммы можно быстро пересчитать, если растянуть формулу на весь столбец F.

  

  Чтобы вычесть долю, необходимо указать в ячейке А1 цифру, а в клетке А2 – процент. Расчеты произвести в клетке В1, вписав формулу «=А1-(А1*А2)».

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

  Как рассчитать процент с помощью формул в Excel

  

  Bob Flisser Last updated Jun 30, 2016

  Russian (Pусский) translation by Ola Matona (you can also view the original English article)

  Если вам необходимо работать с процентами, вы будете рады узнать, что в 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 также использует общепринятый порядок операций в тех случаях, когда в вашей формуле есть сложение, вычитание и умножение:

  1. Скобки
  2. Показатель степени
  3. Умножение
  4. Деление
  5. Сложение
  6. Вычитание

  Я запоминаю этот порядок с помощью мнемонической фразы: Сказал Степан: Умора, Давайте Словим Вора.

  Похожие публикации:

  1. Как сделать заливку в ворде
  2. Как включить ноутбук с закрытой крышкой
  3. Виртуальная клавиатура на макбуке как включить
  4. Как включить поисковую строку