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

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


Приветствую всех, уважаемые читатели блога TutorExcel.Ru.

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

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

Формула разницы между числами в процентах в Excel

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

Формула. Вариант 1.
Теперь перейдем к проверке корректности формулы расчета и рассмотрим различные примеры в зависимости от знаков величин A и B.

Вариант 1: A и B одного знака (+ и +, или − и −)

Начнем со стандартного варианта, когда мы сравниваем друг с другом сопоставимые величины.

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

В данном случае, A это 120 тысяч рублей, а B, соответственно, 100 тысяч рублей, применим формулу и найдем соотношение между ними:

Пример вычисления разницы
В итоге получаем, что 120 тысяч рублей больше, чем 100 тысяч рублей на 20%.

Действительно, все достаточно просто, но теперь рассмотрим те же самые величины, но с обратным знаком: -120 и -100.
И для лучшей наглядности расчета рассмотрим не доходы компании, а ее убытки, и представим, что убытки компании в этом году составили -120 тысяч рублей, когда в прошлом году составляли -100 тысяч рублей.

Логично предположить, что мы должны получить ответ -20%, так как на самом деле -120 меньше, чем -100 (с точки зрения математики, чем левее число на числовой оси, тем оно меньше) и, следовательно, в результате получить отрицательный процентный прирост.
Другими словами, убытки компании увеличились и результаты компании ухудшились, а значит прирост в процентах отрицательный.
Проверяем формулу и повторяем расчет:

Некорректное вычисление разницы
Однако мы опять получаем результат 20%, который, как мы уже выяснили, является неправильным.
Поэтому отсюда делаем вывод, что наша формула для отрицательных чисел, к сожалению, не работает.

Скорректируем расчет, вместо величины B в знаменателе формулы поставим ее модуль.
Тогда формула расчета разницы в процентах примет окончательный вид:

Формула. Вариант 2.
Пересчитаем пример и посмотрим какой ответ в итоге получится:

Корректное вычисление разницы
Отлично, все сошлось. При этом обратите внимание, что для положительных величин формула на самом деле осталась прежней и никак не поменялась, так как модуль положительные числа не изменяет.
Идем дальше и рассмотрим более сложный вариант.

Вариант 2: A и B разных знаков (+ и −, или − и +)

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

Однако при попытке произвести такое сравнение, мы приходим к логическому несоответствию.
Например, давайте подумаем во сколько число 20 больше числа -5?

Предположим, что мы каким-то образом посчитали отношение и нашли численный ответ (предположу, что на предыдущий вопрос в качестве ответа хочется сказать, что в 5 раз больше):

Некорректный расчет разницы
Однако точно такие же расчеты можно привести для чисел 20 и 4 (получается, что 20 одновременно в 5 раз больше, и чем 4 и чем -5).
Такого быть не может, в связи с чем получается, что посчитать разницу между двумя величинами с разными знаками в процентах корректно нельзя, так как сравниваются не сопоставимые друг с другом величины.

Поэтому в подобных случаях принято писать N/M — Not Meaningful (также встречаются обозначения N.M., NM), что можно перевести как не имеет смысла или не имеет значения.
В официальных пресс-релизах или презентациях крупных компаний это может обозначаться следующим образом (к примеру, результаты компании Thomson Reuters за 1 квартал 2017 г.):

Обозначение NM (или N.M.)
В результате, после всех рассуждений, мы можем вернуться в Excel и составить итоговую формулу расчета разницы в процентах между двумя числами.

Сначала с помощью функции ЕСЛИ проверим условие, что величины одного знака — это равносильно условию, что их произведение больше нуля. В том случае если условие выполняется, то считаем по формуле, если же не выполняется, то в качестве ответа возвращаем ошибку (обозначение NM):

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

Удачи вам и до скорых встреч на страницах блога TutorExcel.Ru!

Поделиться с друзьями:
Поиск по сайту:

Комментарии (6)

    1. tutorexcel Автор поста

      Мы необязательно делим на меньшее, мы делим на число к которому считаем прирост.

  1. Дарья

    Добрый день! А у меня все банально просто, но все-равно не могу разобраться:
    Цена продажи товара наша = 439,72, у конкурентов 320,00 нужно узнать на сколько процентов у конкурентов дешевле (ну или у нас дороже).

    Если ввести формулу =(439,72/320,00)-1, и поставить процентное отображение в ячейке = 37%. (обратно пересчитываем 320+27% — получается около 439,72)

    А если сделать наоборот =(320,00/439,72)-1 — получается -27% и, соответственно, если обратно к стоимости 320 добавить 27%, то 439,72 не получается — как правильно считать в данных случаях — всегда делить большее число на меньшее? а если в списке есть позиции, которые у нас дешевле, а у конкурентов дороже — там нужно менять формулу или можно просто ее тянуть в низ на все позиции?

    1. tutorexcel Автор поста

      Смотрите, тут все зависит исходя от какой базы вы считаете (от себя или от конкурентов).
      В вашем примере нужно не к 320 добавить 27%, а вычесть 27% из 440, тогда получите как раз 320.
      Поэтому определитесь к чему именно вы считаете прирост, формула должна быть одна и та же для всех данных.

  2. Андрей

    Здравствуйте.
    Рытвюсь посчитать разницу по вашей инструкции и не получается =)
    Допустим в этом году компания заработала 17 рублей, а в прошлом 2 рубля.
    Считаю прирост: (17-2)/100 = 15/100 = 0.15 = 15%
    Но разница между 17 и 2 явно не 15%.
    Подскажите, пожалуйста, что я делаю не так.

    1. tutorexcel Автор поста

      Андрей, нужно делить не на 100, а на число к которому считаем прирост.
      Формула расчета (A — B)\|B|, т.е. для вашего примера A = 17, B = 2, считаем и получаем (17 — 2)/2 = 15/2 = 7,5 = 750%.

Добавить комментарий

Ваш e-mail не будет опубликован.