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

Рассказываем, как считать долю и изменение, прибавлять и вычитать проценты и как избежать ошибки, из-за которой получается 1500% вместо 15%.

Коротко
  • Доля считается как часть, делённая на целое: =B2/C2, а затем формат процента.
  • Формат включается сочетанием Ctrl+Shift+5 или кнопкой %.
  • Прибавить 15% к цене: =A2*1,15. Вычесть: =A2*0,85.
  • Рост между двумя значениями: =(B2-A2)/A2.
  • Для расчёта от итога закрепите ячейку: =B2/$B$10.

Как Excel понимает проценты

Для Excel процент — это обычное число, только показанное иначе. 15% в ячейке равно 0,15. Формат всего лишь умножает отображаемое значение на 100 и дописывает знак. Отсюда следуют два практических вывода:

  • В формуле умножать на 100 не нужно. Если написать =B2/C2*100 и добавить формат процента, получится значение в 100 раз больше нужного.
  • Если уже есть столбец с числом вроде 15 и включить для него формат процента, вы можете увидеть 1500%. Сначала разделите такие числа на 100 или вводите значения в уже настроенную ячейку.

Число знаков после запятой задаётся кнопками Увеличить разрядность и Уменьшить разрядность на вкладке Главная. Округление здесь только на экране: сама ячейка хранит точное значение.

Основные формулы

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

Что считаем Формула Пример
Какую долю B составляет от C =B2/C2 30 от 200 = 15%
Сколько составляет 15% от числа =A2*15% 15% от 2 000 = 300
Прибавить 15% =A2*(1+15%) 2 000 → 2 300
Вычесть 15% =A2*(1-15%) 2 000 → 1 700
Изменение от старого к новому =(B2-A2)/A2 от 200 до 250 = +25%
Исходное число по итогу с наценкой =B2/(1+15%) 2 300 → 2 000

Таблица формул Excel для расчёта процентов: доля, прибавить и вычесть процент, изменение между значениями

Как посчитать долю каждой строки от итога

Это типичная задача: есть продажи по менеджерам и общий итог в ячейке B10. Нужно понять, какую часть даёт каждый.

  1. В ячейке C2 напишите =B2/$B$10.
  2. Нажмите Enter и включите формат процента сочетанием Ctrl+Shift+5.
  3. Протяните формулу вниз за маркер заполнения в углу ячейки.

Знаки доллара закрепляют ссылку. Без них при протягивании вместо $B$10 появится B11, B12 и так далее, а итоговая ячейка будет пустой, что даст ошибку #ДЕЛ/0!. Поставить знаки можно клавишей F4: щёлкните на адресе в формуле и нажмите её один раз.

Если доли складываются в чуть больше или меньше 100%, это из-за округления на экране. Увеличьте разрядность, и сумма станет 100%.

Как посчитать рост и снижение

Изменение считается от старого значения: =(Новое-Старое)/Старое. Если в A2 расходы прошлого квартала, а в B2 текущего, формула будет =(B2-A2)/A2. Положительное значение показывает рост, отрицательное — снижение.

Если прошлое значение равно нулю или пусто, получится ошибка #ДЕЛ/0!. Чтобы её не было, используйте =ЕСЛИОШИБКА((B2-A2)/A2;""). Также обращайте внимание на разницу между процентами и процентными пунктами: рост ставки с 10% до 12% — это плюс 2 процентных пункта и плюс 20% к ставке.

Как изменить целый столбец цен на процент

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

  1. В пустой ячейке введите множитель: для наценки 15% это 1,15, для скидки 10% это 0,9.
  2. Скопируйте эту ячейку сочетанием Ctrl+C.
  3. Выделите столбец с ценами.
  4. Щёлкните правой кнопкой, выберите Специальная вставка.
  5. Отметьте Умножить и нажмите ОК.
  6. Удалите ячейку с множителем.

Перед этим сохраните копию данных: отмена работает, но лишь пока вы не закроете файл. Формулы в столбце цен при такой вставке тоже будут изменены, поэтому проверьте результат на нескольких строках.

Лучше не прописывать процент прямо в формуле. Если вынести его в отдельную ячейку, например $E$1, и использовать формулу =A2*(1+$E$1), ставку можно менять в одном месте, и пересчитается вся таблица.

Если результат неправильный

  • Видите 0,15 вместо 15%. Включите формат процента.
  • Видите 1500%. В исходной ячейке уже было число 15. Разделите его на 100.
  • Видите 0%. Значение меньше половины процента, а разрядность равна нулю. Добавьте знаки после запятой.
  • Ошибка #ЗНАЧ! Одно из чисел записано как текст. Проверьте запятую и пробелы в числе: 2 000,50 должно быть числом, а не текстом.
  • Ошибка #ДЕЛ/0! Делитель равен нулю или пуст. Проверьте ссылки и закрепление.

Самая частая формула здесь — доля =B2/$B$10 с форматом процента. Освоив её и изменение =(B2-A2)/A2, вы закроете большую часть задач.

Частые вопросы

Как посчитать НДС в Excel?

Сумма без налога умножается на ставку, а для итога на 1+ставка. Ставку лучше вынести в отдельную ячейку. Если ставка изменится, менять придётся только её. Перед применением уточните действующую ставку для своей ситуации.

Что делать, если проценты отличаются от калькулятора?

Проверьте округление и порядок действий. Калькулятор часто считает последовательно, а в Excel важны скобки: =B2-A2/A2 и =(B2-A2)/A2 дадут разный результат.

Можно ли рисовать проценты в ячейках графически?

Да, через условное форматирование: Главная → Условное форматирование → Гистограммы. В каждой ячейке появится полоска, пропорциональная значению.

© kak2.ru