Познакомимся с особенностями расчета разницы между двумя числами в процентах в Excel в зависимости от знаков рассматриваемых величин (обе величины положительные, обе отрицательные или разных знаков).
Приветствую всех, уважаемые читатели блога TutorExcel.Ru.
Кажется, что может быть проще, чем посчитать дельту в процентах между двумя числами — это несложная математическая операция.
Сначала вычислить разницу между двумя величинами в абсолютном выражении (разность первого и второго чисел), а затем полученный результат разделить на вторую величину.
Но все несколько сложнее, чем кажется на первый взгляд, и для некоторых случаев приведенная формула может дать неправильный результат. Поэтому давайте перейдем к рассмотрению особенностей вычислений.
Формула разницы между числами в процентах в Excel
Для удобства записи обозначим первую величину как A, а вторую как B.
Тогда процентный прирост первого числа ко второму, как уже писалось выше, будет считаться по следующей формуле:
Теперь перейдем к проверке корректности формулы расчета и рассмотрим различные примеры в зависимости от знаков величин A и B.
Вариант 1: A и B одного знака (+ и +, или − и −)
Начнем со стандартного варианта, когда мы сравниваем друг с другом сопоставимые величины.
Предположим, что компания в этом году продала товаров на сумму 120 тысяч рублей, а в прошлом году — на сумму 100 тысяч рублей.
Вычислим темпы роста доходов компании год к году, т.е. посмотрим насколько компания в этом году заработала в процентах больше, чем в прошлом.
В данном случае, A это 120 тысяч рублей, а B, соответственно, 100 тысяч рублей, применим формулу и найдем соотношение между ними:
В итоге получаем, что 120 тысяч рублей больше, чем 100 тысяч рублей на 20%.
Действительно, все достаточно просто, но теперь рассмотрим те же самые величины, но с обратным знаком: -120 и -100.
И для лучшей наглядности расчета рассмотрим не доходы компании, а ее убытки, и представим, что убытки компании в этом году составили -120 тысяч рублей, когда в прошлом году составляли -100 тысяч рублей.
Логично предположить, что мы должны получить ответ -20%, так как на самом деле -120 меньше, чем -100 (с точки зрения математики, чем левее число на числовой оси, тем оно меньше) и, следовательно, в результате получить отрицательный процентный прирост.
Другими словами, убытки компании увеличились и результаты компании ухудшились, а значит прирост в процентах отрицательный.
Проверяем формулу и повторяем расчет:
Однако мы опять получаем результат 20%, который, как мы уже выяснили, является неправильным.
Поэтому отсюда делаем вывод, что наша формула для отрицательных чисел, к сожалению, не работает.
Скорректируем расчет, вместо величины B в знаменателе формулы поставим ее модуль.
Тогда формула расчета разницы в процентах примет окончательный вид:
Пересчитаем пример и посмотрим какой ответ в итоге получится:
Отлично, все сошлось. При этом обратите внимание, что для положительных величин формула на самом деле осталась прежней и никак не поменялась, так как модуль положительные числа не изменяет.
Идем дальше и рассмотрим более сложный вариант.
Вариант 2: A и B разных знаков (+ и −, или − и +)
Теперь разберем нестандартный вариант, и от сравнения чисел одинаковых знаков (до этого мы сравнивали положительное с положительным и отрицательное с отрицательным) перейдем к сравнению чисел разных знаков.
Однако при попытке произвести такое сравнение, мы приходим к логическому несоответствию.
Например, давайте подумаем во сколько число 20 больше числа -5?
Предположим, что мы каким-то образом посчитали отношение и нашли численный ответ (предположу, что на предыдущий вопрос в качестве ответа хочется сказать, что в 5 раз больше):
Однако точно такие же расчеты можно привести для чисел 20 и 4 (получается, что 20 одновременно в 5 раз больше, и чем 4 и чем -5).
Такого быть не может, в связи с чем получается, что посчитать разницу между двумя величинами с разными знаками в процентах корректно нельзя, так как сравниваются не сопоставимые друг с другом величины.
Поэтому в подобных случаях принято писать N/M — Not Meaningful (также встречаются обозначения N.M., NM), что можно перевести как не имеет смысла или не имеет значения.
В официальных пресс-релизах или презентациях крупных компаний это может обозначаться следующим образом (к примеру, результаты компании Thomson Reuters за 1 квартал 2017 г.):
В результате, после всех рассуждений, мы можем вернуться в Excel и составить итоговую формулу расчета разницы в процентах между двумя числами.
Сначала с помощью функции ЕСЛИ проверим условие, что величины одного знака — это равносильно условию, что их произведение больше нуля. В том случае если условие выполняется, то считаем по формуле, если же не выполняется, то в качестве ответа возвращаем ошибку (обозначение NM):
Спасибо за внимание!
Если у вас остались вопросы по методике расчета — спрашивайте в комментариях.
Удачи вам и до скорых встреч на страницах блога TutorExcel.Ru!
почему делим на меньшее из этих двух чисел?
Мы необязательно делим на меньшее, мы делим на число к которому считаем прирост.
Добрый день! А у меня все банально просто, но все-равно не могу разобраться:
Цена продажи товара наша = 439,72, у конкурентов 320,00 нужно узнать на сколько процентов у конкурентов дешевле (ну или у нас дороже).
Если ввести формулу =(439,72/320,00)-1, и поставить процентное отображение в ячейке = 37%. (обратно пересчитываем 320+27% — получается около 439,72)
А если сделать наоборот =(320,00/439,72)-1 — получается -27% и, соответственно, если обратно к стоимости 320 добавить 27%, то 439,72 не получается — как правильно считать в данных случаях — всегда делить большее число на меньшее? а если в списке есть позиции, которые у нас дешевле, а у конкурентов дороже — там нужно менять формулу или можно просто ее тянуть в низ на все позиции?
Смотрите, тут все зависит исходя от какой базы вы считаете (от себя или от конкурентов).
В вашем примере нужно не к 320 добавить 27%, а вычесть 27% из 440, тогда получите как раз 320.
Поэтому определитесь к чему именно вы считаете прирост, формула должна быть одна и та же для всех данных.
Здравствуйте.
Рытвюсь посчитать разницу по вашей инструкции и не получается =)
Допустим в этом году компания заработала 17 рублей, а в прошлом 2 рубля.
Считаю прирост: (17-2)/100 = 15/100 = 0.15 = 15%
Но разница между 17 и 2 явно не 15%.
Подскажите, пожалуйста, что я делаю не так.
Андрей, нужно делить не на 100, а на число к которому считаем прирост.
Формула расчета (A — B)\|B|, т.е. для вашего примера A = 17, B = 2, считаем и получаем (17 — 2)/2 = 15/2 = 7,5 = 750%.
Здравствуйте. А как все-таки посчитать изменения в процентах чисел с разными знаками в Excel?
С точки зрения математики такой процент считать не совсем корректно, так как нет однозначного соответствия при расчете, я в статье описываю почему так делать нельзя.
Если же не считать варианта нет, то попробуйте применить стандартную формулу.