Как посчитать процент отклонения в 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 по умолчанию приоритет операции деления выше операции вычитания в данной формуле нет необходимости применять скобки.
План-Фактный анализ — глоссарий КСК ГРУПП
Экономисты применяют в работе такой вид анализа, как план-фактный. Иначе его называют план-факторным. С помощью этого инструментария можно вычислить, насколько близки к реализации плановые значения, отклоняются ли сотрудники фактически от заданной цели, если да, то из-за чего.
Инструмент применяется в случаях, когда нужно контролировать выполнение производственных и финансовых показателей организации ее руководящим составом. На любом предприятии применяется программный метод ведения хозяйства, деятельность не ведется на ощупь, на авось. Структурному подразделению, филиалу организации направляются плановые значения. Они должны вести свою деятельность таким образом, чтобы выполнить план или перевыполнить его.
Так, в прогнозе могут отражаться следующие характеристики:
- количество (масса) выпускаемых товаров;
- объем оказываемых услуг, выполняемых работ.
Насколько отдел или филиал смогли достигнуть заданных значений, можно определить, проанализировав отчетные документы.
Есть годовая отчетность о доходах и расходах предприятия. В ней указывается, сколько товара на самом деле реализовано за указанный период. Информация о том, сколько товара и какого наименования произведено за то же время, есть в статотчетности о производстве изделий и в бухгалтерской документации, в частности в форме № 16. В ней отражается путь товара от выпуска до продажи. В качестве источника информации могут выступать и сведения из книги приобретений.
Главным способом определения результатов работы считается сравнительный метод. Для этого нужно взять плановые данные и фактические итоги. Нижеприведенные формулы позволят вычислить два основных показателя:
- насколько отклонилось структурное подразделение/филиал от плана: Δ = Ф-П, где Ф — показатель фактического отклонения, а П — план;
- процентный показатель достижения плановых показателей: % = Ф/П*100.
Не забудьте учитывать инфляцию цен. В этом случае данные по стоимости фактически произведенного товара нужно корректировать. Тогда вышеозначенные формулы немного усложнятся и будут выглядеть следующим образом:
- Δi = Ф/I-П, где I — это индекс цен. Формула позволит вычислить, насколько фактический результат отклонился от плана, с учетом индексации стоимости продукции.
- %i = (Ф/I)/П × 100 — формула позволяет рассчитать процентный показатель с учетом корректировки стоимости на данные инфляции.
В условиях инфляции стоимостной показатель фактического объема производства или реализации, как правило, корректируется с учетом индекса цен.
Когда будет выявлено, насколько соответствуют друг другу план и факт, нужно определить, почему так произошло. Это может быть следствием следующих обстоятельств:
- на складских площадях оставалась партия товара;
- низкая эффективность труда работников;
- выполнение пунктов договоров;
- ассортимент продукции и пр.
Такой метод аналитической работы, как план-фактный, необходим для главных статей расходов и доходов. Если нужно изучить, почему возникли нестыковки, то отдельно нужно проанализировать статьи операционные и функциональные. Есть возможность организовать анализ для всего предприятия и для его структурных подразделений. Как бы то ни было, руководство организации самостоятельно определяет, что и когда анализировать.
При использовании план-фактной методики нужно не забывать оформлять показатели результата работы и плана в одном виде, в противном случае не совсем корректно будет сравнивать информацию.
При выявлении каких-либо нестыковок можно оценить результат работы каждого звена в цепи (снабженческий отдел, производственный, логистический, кадровый и т. д.), чтобы сделать объективные выводы и принять меры к корректировке ситуации.
При анализе внимание уделяется данным, которые заметно отличаются от плановых чисел.
Бывают внешние факторы, на которые не в силах повлиять структурные подразделения:
- упал спрос;
- закрываются организации, принадлежащие партнерам.
В таких случаях нужно в срочном порядке скорректировать бюджетные показатели, чтобы они не привели к краху предприятия. Не исключено, что придется отказаться от приобретения дополнительных агрегатов, перевести сотрудников на иной режим работы.
Когда будут определены отклонения, можно приступить к следующему шагу, чтобы оценить эффективность работы компании в целом или ее СП.
Индикатором для выявления обоснованности тех или иных расходов являются завершающие данные бюджета, такие как размер чистого потока денег. Например, нестыковки бюджетных статей можно анализировать с точки зрения того, как будут развиваться события. Если рассчитать, как повлияет тот или иной показатель, выбивающийся из плана, на объем финансовых средств, есть возможность сделать вывод о необходимости изменения плана на очередной квартал, полугодие или год.
Возврат к списку
Как рассчитать процентное изменение?
В математике процент — это число или отношение, выраженное в виде доли от 100. Слово «процент» происходит от латинского слова per centum, что означает «на сто». Процент изображается с помощью символа «%». Оно не имеет единицы измерения и является безразмерным числом. Процент может быть представлен либо в дробной форме, либо в десятичной форме.
Формула процентов
Рассчитывается путем деления значения на общее значение и умножения полученного результата на 100.
Процент (%) = (Значение/общее значение) × 100 % 100} меньше, чем P
Процентное изменение
Процентное изменение рассчитывается при изменении значения величины за определенный период времени Процентное изменение рассчитывается как процентное увеличение и процентное уменьшение Для расчета процентного изменения , мы должны разделить изменение стоимости на исходное значение, а затем умножить полученное значение на 100.
Процентное изменение = [(изменение значения)/исходное значение] × 100
Где Изменение значения = Новое значение – исходное значение
Процентное увеличение
значение величины, когда она увеличивается в течение определенного периода времени. Он рассчитывается путем вычитания исходного значения из нового значения, деления его на исходное значение, а затем умножения полученного результата на 100. Формула процентного увеличения дается как 9.0003
Увеличение в процентах = [(Новое значение — Исходное значение)/Исходное значение] × 100
% от первоначальной суммы.
Процентное уменьшение
Процентное уменьшение означает процентное изменение значения величины при ее уменьшении за определенный период времени. Он рассчитывается путем вычитания нового значения из исходного значения, деления его на исходное значение, а затем умножения полученного результата на 100.
Процентное уменьшение = [(Исходное значение – Новое значение)/Исходное значение] × 100
Примечание: – х)% от исходной суммы.
Примеры задач
Задача 1: Мальчик получил на экзамене 820 баллов из 900 баллов. Подсчитайте процент отметок, полученных мальчиком.
Решение:
Дано, Количество полученных баллов = 820
Максимальное количество баллов = 900
Процент баллов = (820/900) × 100
⇒ Процент = (0,9112) × 100 баллов
3 90 мальчиком составляет 91,12%
Проблема 2: Вес Гиты на 25% больше, чем у Фатимы. Если вес Фатимы 56 кг, то на сколько процентов вес Фатимы меньше веса Гиты?
Решение:
Учитывая данные, вес Фатимы = 60 кг
Вес Гиты на 25% больше, чем у Фатимы.
Следовательно, вес Гиты = 56 + 56 × (¼) = (56 + 16) кг = 72 кг.
Требуемый процент = [(72 – 56)/72] × 100 = 22,22%
Следовательно, вес Фатимы на 22,22% меньше веса Гиты.
Задача 3. Рассчитайте процентное увеличение, если цена растительного масла со 150 фунтов стерлингов увеличится до 180 фунтов стерлингов.
Решение:
Приведенные данные, Исходная цена масла = 150 ₨
Новая цена масла = 180 ₨
Изменение значения цены = 180 ₨ – 150 ₨ = 30 ₨
( Изменение в процентах = [Изменение в процентах = [
Таким образом, процентное увеличение цены масла для жарки составляет 20% цена мобильного телефона увеличивается каждый год на 20%. Если текущая цена ₨ 15000, то какова была цена (в ₨) 3 года назад?
Решение:
Пусть цена мобильного телефона 3 года назад будет P
Цена мобильного телефона 2 года назад = P + 20% от P = P + (20/100)P = 1,20P
Цена мобильного телефона год назад = 1,20 P + 20% от (1,2 P) = 1,20 P + 0,24 P = 1,44 P
Текущая цена мобильного телефона = 1,44 P + 20% от (1,44 P) ) = 1,44 P + 0,288 P = 1,728 P
⇒ 1,728 P = 15000
⇒ P = 8680,55
₨ Следовательно, цена мобильного телефона 3 года назад составляла 8680,55 ₨
Задача 5.
₨ Изменение значения цены = 60 ₨ – 50 ₨ = 10
₨ Изменение в процентах = [(снижение цены)/первоначальная стоимость] × 100
⇒ Изменение в процентах = (10/60 ) × 100 = 16,67%
Отсюда процентное изменение цены дюжины яиц составляет 16,67%
Проблема 6: Сумма сбережений Рави составляла ₨ 5000 в январе месяце. Его сбережения уменьшились до ₨ 3800 в феврале месяце. Вычислите процент уменьшения его сбережений.
Решение:
РАБОТАЕТ ДАННЫЕ, Сумма сбережений Рави в январе = 5000
Сумма сбережений Рави в феврале = 3800
Уменьшение его сбережений = 5000 — 3800 = 1200
. экономия)/исходное значение] × 100
Теперь процентное уменьшение его сбережений = (1200/5000) 100 = 24%
Задача 7: Число увеличивается на 25%, а затем уменьшается на 25%.
Решение:
Пусть число будет 100.
Число увеличилось на 25%
Мы знаем, что если значение увеличить на x%, то новое значение равно (100 + x)% от первоначальной суммы.
⇒ Теперь увеличенное число = (100 + 25)% от 100 = (125/100) × 100 = 125
Число уменьшилось на 25%
Мы знаем, что если значение уменьшить на x%, то новое значение составит (100 – x)% от первоначального значения.
⇒ Теперь уменьшенное число = (100 – 25)% от 125 = (75/100) × 125 = 93,75
⇒ Чистое уменьшение = 100 – 93,75 = 6,25
Следовательно, число уменьшилось на 6,25%
расчет процентного увеличения/уменьшения
В этом учебном пособии показано, как создать формулу Excel для процентного увеличения или уменьшения и использовать ее как с положительными, так и с отрицательными числами.
В Microsoft Excel имеется 6 различных функций для расчета дисперсии.
Что такое процентное изменение?
Процентное изменение , также известное как процентная дисперсия или разница , представляет собой пропорциональное изменение между двумя значениями, исходным значением и новым значением.
Формула процентного изменения
вычисляет, насколько что-то изменилось между двумя периодами в процентном отношении. Например, можно рассчитать разницу между продажами в этом и прошлом году, между прогнозируемой и наблюдаемой температурой, между запланированной стоимостью и реальной.Например, в январе вы заработали 1000 долларов, а в феврале — 1200 долларов, поэтому разница составляет увеличение вашего дохода на 200 долларов.
Формула процентного изменения Excel
Существуют две основные формулы для нахождения процентной разницы между двумя числами.
Классическая формула процентной дисперсии
Вот общепринятая формула для расчета процентного изменения:
( новое_значение — старое_значение ) / старое_значение
В математике вы обычно выполняете 3 шага, чтобы вычислить процентное отклонение между любыми двумя числовыми значениями:
- Вычтите новое значение из старого.
- Разделите разницу на старое число.
- Умножьте результат на 100.
В Excel пропустите последний шаг, применив формат Процент .
Формула процентного изменения Excel
А вот более простая формула процентного изменения в Excel, которая возвращает тот же результат.
новое_значение / старое_значение — 1
Как рассчитать процентное изменение в Excel
Чтобы найти процентную разницу между двумя числами в Excel, вы можете использовать любую из приведенных выше формул.
=(C3-B3)/B3или
=C3/B3-1Приведенные выше формулы сравнивают числа в строке 3. Чтобы вычислить процент изменения во всем столбце, вам нужно сделать следующее:
- Введите формулу процентной разницы в любую пустую ячейку в строке 3, например, в D3 или E3.
- Выделив ячейку формулы, нажмите кнопку Процентный стиль на ленте или нажмите сочетание клавиш Ctrl + Shift + %. Это преобразует возвращаемое десятичное число в проценты.
- Перетащите формулу вниз на необходимое количество строк.
После копирования формулы вы получите столбец процентного изменения ваших данных.
Как работает формула процентного изменения Excel
При выполнении расчетов вручную вы берете старое (исходное) значение и новое значение, находите разницу между ними и делите ее на исходное значение.
Чтобы получить результат в процентах, умножьте его на 100. Например, если исходное значение равно 120, а новое значение равно 150, процентную разницу можно рассчитать следующим образом:
=(150-120)/120
=30/120
=0,25
0,25*100 = 25%Применение числового формата Процент в Excel автоматически отображает десятичное число в процентах, поэтому часть *100 опускается.
Формула Excel для процентного увеличения/уменьшения
Поскольку процентное увеличение или уменьшение является лишь частным случаем процентной дисперсии, оно рассчитывается по той же формуле:
( новое_значение — начальное_значение ) / начальное_значение
или
новое_значение / начальное_значение — 1
Например, чтобы рассчитать -процентное увеличение между двумя значениями (B2 и C2), используйте следующую формулу:
=(C2-B2)/B2или
=C2/B2-1Формула для расчета -процентного уменьшения точно такая же.
Excel процентное изменение абсолютного значения
По умолчанию формула процентного отклонения в Excel возвращает положительное значение для процентного увеличения и отрицательное значение для процентного уменьшения. Чтобы получить процентное изменение в виде абсолютного значения без учета его знака, оберните формулу в функцию ABS следующим образом:
ABS(( новое_значение — старое_значение ) / старое_значение)
В нашем случае формула принимает такой вид:
= АБС ((C3-B3)/B3)Этот тоже подойдет:
= АБС (C3/B3-1)Рассчитать процент скидки
В этом примере показано еще одно практическое применение формулы процентного изменения Excel — определение процента скидки. Итак, дамы, когда будете ходить по магазинам, помните:
скидка % = (цена со скидкой - обычная цена) / обычная цена
скидка % = цена со скидкой / обычная цена - 1Процент скидки отображается как отрицательное значение, поскольку новая цена со скидкой меньше начальной цены.
= АБС ((C2-B2)/B2)Рассчитать значение после процентного изменения
Чтобы получить значение после процентного увеличения или уменьшения, используйте общую формулу:
начальное_значение *(1+ процентное_изменение )
Предположим, у вас есть исходные значения в столбце B и разница в процентах в столбце C. Чтобы вычислить новое значение после процентного изменения, формула в D2 скопирована:
=В2*(1+С2)Сначала вы найдете общий процент, который нужно умножить на исходное значение. Для этого просто прибавьте процент к 1 (1+C2). А затем вы умножаете общий процент на исходные числа, чтобы получить желаемый результат.
Как видите, это решение хорошо работает как для процентного увеличения, так и для уменьшения:
Чтобы увеличить или уменьшить весь столбец на определенный процент , вы можете указать процентное значение непосредственно в формуле.
=В2*(1+5%)Здесь вы просто умножаете исходное значение на 105%, что дает значение на 5% больше.
Для удобства вы можете ввести процентное значение в предопределенную ячейку (F2) и обратиться к этой ячейке. Хитрость заключается в блокировке ссылки на ячейку знаком $, поэтому формула копируется правильно:
=B2*(1+$F$2)Преимущество этого подхода в том, что для увеличения столбца на другой процент достаточно изменить значение только в одной ячейке. Поскольку все формулы связаны с этой ячейкой, они будут автоматически пересчитаны.
Расчет процентной дисперсии с отрицательными значениями
Если некоторые из ваших значений представлены отрицательными числами, традиционная формула процентной разницы будет работать неправильно. Общепринятым решением является сделать знаменатель положительным числом с помощью функции ABS.
Вот общая формула Excel для процентного изменения с отрицательными числами:
( новое_значение — старое_значение ) / ABS( старое_значение )
Со старым значением в B2 и новым значением в C2 реальная формула выглядит следующим образом:
=(C2-B2)/АБС(B2)Примечание. Хотя эта корректировка ABS технически верна, формула может давать неверные результаты, если исходное значение отрицательное, а новое значение положительное, и наоборот.
Ошибка деления процентного изменения Excel на ноль (#DIV/0)
Если ваш набор данных содержит нулевые значения, вы, вероятно, столкнетесь с ошибкой деления на ноль (#ДЕЛ/0!) при расчете процентного изменения в Excel, потому что вы не можете разделить число на ноль в математике. Функция ЕСЛИОШИБКА может помочь решить эту проблему. В зависимости от ваших ожиданий от конечного результата используйте одно из следующих решений.
Решение 1: если старое значение равно нулю, вернуть 0
Если старое значение равно нулю, процентное изменение будет равно 0% независимо от того, равно ли новое значение нулю или нет.
=ЕСЛИОШИБКА((C2-B2)/B2, 0)или
=ЕСЛИОШИБКА(C2/B2-1, 0)Решение 2: если старое значение равно нулю, вернуть 100%
Это решение реализует другой подход, предполагающий, что новое значение выросло на 100%, начиная с нуля:
=ЕСЛИОШИБКА((C2-B2)/B2, 1)
=ЕСЛИОШИБКА(C2/B2-1, 1)В этом случае разница в процентах будет равна 100 %, если старое значение равно нулю (строка 5) или оба значения равны нулю (строка 9).
Глядя на выделенные ниже записи, становится очевидным, что ни одна из формул не идеальна:
Чтобы получить лучшие результаты, вы можете объединить две формулы в одну, используя вложенный оператор IF:
=ЕСЛИ(C2<>0, ЕСЛИОШИБКА((C2-B2)/B2, 1), ЕСЛИОШИБКА((C2-B2)/B2, 0))Эта улучшенная формула вернет:
- Процент изменяется на 0%, если и старое, и новое значения равны нулю.
- Процент изменяется на 100%, если старое значение равно нулю, а новое значение не равно нулю.
Формула процентного уменьшения задается следующим образом:
Рассчитайте процентное изменение, если цена дюжины яиц уменьшилась с 60 до 50 фунтов стерлингов. Новая цена = 50
Найдите чистый процент увеличения или уменьшения. 
Однако ни один из них не подходит для вычисления процентной разницы между двумя ячейками. Встроенные функции предназначены для нахождения дисперсии в классическом смысле, то есть того, насколько далеко набор значений разбросан от их среднего значения. Процентная дисперсия — это нечто другое. В этой статье вы найдете правильную формулу для расчета процентного изменения в Excel.
Но сколько это в процентах? Чтобы узнать это, вы используете формулу процентного изменения.
Допустим, у вас есть предполагаемые продажи в столбце B и фактические продажи в столбце C. Предполагая, что предполагаемое число является «базовым» значением, а фактическое — «новым» значением, формулы принимают следующий вид:
Чтобы вывести результат как положительное число , вложите формулы внутрь функции ABS, как в предыдущем примере:
Скажем, чтобы увеличить все значения в столбце B на 5%, введите приведенную ниже формулу в C2, а затем перетащите ее вниз по оставшимся строкам:
