Как считать скидку в excel

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

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

Кликните по кнопке ниже для загрузки Excel файла с примерами расчетов процентов в Excel:

  1. Что такое процент?
  2. Как посчитать проценты от суммы в Excel
  3. Видео урок:
  4. Как посчитать процент от суммы значений таблицы Excel
  5. Видеоурок:
  6. Как вычислить процент нескольких значений от суммы таблицы
  7. Видеоурок:
  8. Как работает эта формула?
  9. Как посчитать разницу в процентах в Excel
  10. Как найти процент между двумя числами из двух колонок в Excel
  11. Видеоурок:
  12. Как найти процент между двумя числами из двух строк в Excel
  13. Как прибавить/вычесть процент к числу в Excel

Что такое процент?

Проце́нт (лат. per cent — на сотню) — одна сотая часть. Отмечается знаком «%». Обозначает долю чего-либо по отношению к целому. Например, 25 % от 500 руб. означает 25 частей по 5 руб. каждая, то есть 125 руб.

Базовая формула расчета процента от суммы выглядит так:

(Часть/Целое) * 100 = Процент (%)

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

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

Видео урок:

Базово, рассчитать процент от суммы в Эксель можно по формуле:

(Часть/Целое) * 100 = Процент (%)

Но если использовать формат ячейки “Процентный”, то для вычисления процента от числа достаточно поделить одно число на другое. Например, у нас есть яблоки, которые мы купили по 100 руб. на оптовом складе, а розничную цену выставим 150 руб. Для того чтобы высчитать процент того, сколько составляет закупочная цена от розничной, нам потребуется:

  • Составить таблицу с ценами и добавить колонку для вычисления величины процента:

расчет-цены-закупки-от-цены-продажи

  • В ячейку D2 внести формулу, вычисляющую процент цены закупки от цены продажи:
  • Применить формат ячейки D2 “Процентный”:

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

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

Видеоурок:

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

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

Для этого нам нужно:

  • Добавить колонку к таблице с расчетом процента;
  • В ячейку C2 вставить формулу:

Расчет процента от суммы таблицы в Excel

Значки $ фиксируют колонку “B” и ячейку “9” для того, чтобы при протягивании формулы на все строки таблицы, Excel автоматически подставлял объем продаж каждого продавца и высчитывал % от общего объема продаж. Если не поставить значки “$”, то при протягивании формулы, система будет смещать ячейку B9 на столько ячеек вниз, на сколько вы протяните формулу.

  • Протянуть формулу на все ячейки таблицы, соответствующие строкам с фамилиями продавцов:

Протянуть процент от суммы всей таблицы

  • На примере выше мы протянули формулу и получили значения в виде дробных чисел. Для того чтобы перевести полученные данные в проценты выделите данные левой клавишей мыши и смените формат ячеек на “Процентный”:

Формат ячейки процентный

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

Видеоурок:

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

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

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

  • Справа от таблицы укажем товар (Помидоры), по которым хотим рассчитать долю в продажах:

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

  • Сменим формат ячейки E2 на “Процентный”;
  • В ячейку E2 вставим формулу с функцией СУММЕСЛИ, которая поможет вычислить из списка товаров Помидоры и суммировать их объем продаж, а затем поделить его на общий объем продаж товаров:
Как вычислить процент нескольких значений от суммы таблицы
Как работает эта формула?

Для расчетов мы используем формулу СУММЕСЛИ. Эта функция возвращает сумму чисел, указанных в качестве аргументов и отвечающих заданным в формуле критериям.

Синтаксис функции СУММЕСЛИ:

=СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])

  • диапазон – диапазон ячеек, по которым оцениваются критерии. Аргументом могут быть числа, текст, массивы или ссылки, содержащие числа;
  • условие – критерии, которые проверяются по указанному диапазону ячеек и определяют, какие ячейки суммировать;
  • диапазон_суммирования – суммируемые ячейки. Если этот аргумент не указан, то функция использует аргумент диапазонв качестве диапазон_суммирования.

Таким образом, в формуле =СУММЕСЛИ($A$2:$A$8;$E$1;$B$2:$B$8)/B9 мы указали “ $A$2:$A$8 ” как диапазон товаров, среди которых функция будет искать нужный нам критерий (Помидоры). Ячейка “ $E$1 ” указана в качестве критерия и указывает что мы ищем “Помидоры”. Диапазон ячеек “ $B$2:$B$8 ” обозначает какие ячейки нужно суммировать, в случае если искомый критерий был найден.

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

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

Например, для расчета разницы в процентах между двумя числами А и В используется формула:

(B-A)/A = Разница между двумя числами в процентах

На практике, при использовании этой формулы важно определить какое из нужных вам чисел является “А”, а какое “В”. Например, представим что вчера у вас было в наличии 8 яблок, а сегодня стало 10 яблок. Таким образом количество яблок относительно вчерашнего у вас изменилось на 25% в большую сторону. В том случае, если у вас вчера было 10 яблок, а сегодня стало 8 яблок, то количество яблок, относительно вчерашнего дня сократилось на 20%.

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

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

Видеоурок:

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

Для расчетов нам потребуется формула:

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

  • Протяните формулу на все строки со значениями;
  • Измените формат ячейки для колонки “Изменение, %” на “Процентный”.

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

В том случае, если у нас есть список в котором в каждой строке указан период с данными продаж и нам нужно вычислить изменения от периода к периоду, то нам потребуется формула:

Где B2 это первая строчка, B3 вторая строчка с данными. После ввода формулы не забудьте отформатировать ячейки с вычислениями как “Процентные”. Выполнив все выше описанные действия, вы получите результат:

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

Если у вас есть необходимость вычислить изменения относительно какой-то конкретной ячейки, то важно зафиксировать ее значками “$”. Например, если перед нами стоит задача вычислить изменения объема продаж относительно Января, то формула будет такой:

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

На примере выше значения продаж каждого месяца сравниваются с данными продаж Января.

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

При расчетах может понадобиться прибавить к какому-либо числу процент. Например, представим, что мы планируем расходы на отпуск. Для того чтобы рассчитать необходимую сумму денег на неделю отпуска, мы закладываем, что в отпуске будем тратить 30% больше чем в обычную рабочую неделю. Для такого расчета нам потребуется формула:

Например, мы хотим прибавить к числу “100” двадцать процентов, тогда формула будет следующая:

Если задача стоит вычесть 20% от числа “100”, то формула выглядит так:

Вернемся к нашей задаче. Запланируем, что на неделю отпуска мы будем тратить на 30% больше чем в регулярную неделю, а неделю после отпуска будем экономить и тратить на 30% меньше чем в неделю до отпуска. Тогда расчет нашего бюджета будет выглядеть так:

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

Еще больше полезных приемов в работе с функциями и списками данных в Excel вы узнаете в практическом курсе “От новичка до мастера Excel“. Успей зарегистрироваться по ссылке!

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

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

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

Использование таблицы данных в Эксель

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

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

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

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

Как создать таблицу данных в Эксель

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

  1. Модель прибыли компании и условия партнерской программы.
  2. Схема данных, которая чем-то будет напоминать таблицу Пифагора. Нам нужно, чтобы в строке отображались количественные значения для каждого бонуса, который предлагается в рамках этой программы. Например, в качестве них может выступать набор чисел от 100 до 500, которые делятся на 50. А бонус сам в процентах должен измеряться, например, в рамках 3-10 процентах, и главное, чтобы они были кратными 5.

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

Теперь в ячейку, расположенную на пересечении, вводим формулу, =B15/B8 . На забываем, что формат ячейки должен быть процентным.

Следующим шагом мы осуществляем выделение диапазона D2:M17. Далее нам нужно выбрать вкладку «Данные», чтобы создать таблицу (именно там находятся все необходимые настройки). На этой вкладке нам нужно воспользоваться инструментом «Анализ: что если» и там найти пункт «Таблица данных». Сам инструмент находится в группе «Работа с данными».

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

1

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

В нашем случае правила такие:

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

Сами диапазоны, которые мы используем в этом примере, вы можете увидеть на этом скриншоте.

2

Важно учесть, что нами осуществлялся расчет наиболее оптимальных предложений при показателях второй границы для количественной границы 1. Если же нам нужно рассчитать скидки для второй количественной границы, то нужно тогда указывать ссылки на другие ячейки. В нашем случае это $B$4 и $B$5.

После того, как мы подтвердим наши действия, таблица будет автоматически заполнена нужными данными. В нашем случае получилось автоматически отобразить 135 возможных вариантов. И ничего не понадобилось перебирать вручную. Удобно, не так ли?

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

Анализ таблицы данных с помощью визуализации (что если)

Здесь у нас появляется вопрос: а что, нам нужно потом и анализировать данные вручную? Ведь компьютер вряд ли сможет настолько хорошо понимать наши задачи. В определенной степени, да. Но можно несколько упростить этот процесс с помощью визуализации.

Как это сделать? Для данной цели существует инструмент, который называется «Условное форматирование». Последовательность действий следующая:

  1. Находим курсором требуемый диапазон и выделяем его. В нашем случае это набор ячеек E3:M17.
  2. Далее нам нужно выбрать правило «Между» в разделе «Условное форматирование».

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

3

  • Задаем те проценты, которые нас интересуют. В нашем случае это значения, находящиеся в пределах от 7 до 8 процентов. В правой части окна можно выбрать стиль форматирования, который будет использоваться для выделения совпадений.
  • Теперь коридор доходов показывается автоматически. Мы знаем, что выход из него чреват потерей прибыли. Пользователь дальше может с легкостью понимать, какой размер скидки оптимальный в определенной ситуации, чтобы было выгодно как покупателю, так и продавцу. А с помощью условного форматирования это вообще легко стало.

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

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

    4

    Аналогичным методом осуществляется расчет бонусов и уровня для второй границы. Только не стоит забывать указывать правильные ссылки для каждого конкретного случая.

    Точно таким же образом строится таблица для прибыли нетто. Мы можем задать подходящее в этом случае условное форматирование.

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

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

    Как правильно учитывать товар в Excel

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

    Кроме этого знание некоторых математических функций также является обязательным.

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

    Также могут понадобиться и дополнительные поля. Это такие колонки, как форма оплаты, дата истечения контракта и так далее.

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

    Для чего производится учет магазина в Эксель

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

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

    1. Возможность указания таких основополагающих параметров товара, как цена, количество, документы.
    2. Функция отображения того, сколько осталось каждого товара.
    3. Автоматизация заполнения полей.
    4. Создание оборотной ведомости.
    5. Управление наценками и скидками.

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

    1. Отбор документов и печать сделок, основываясь на получившейся выборке.
    2. Получение валовой чистой прибыли за квартал, месяц, день или год. В принципе, пользователь может выбирать тот период, который ему захочется. В этом плане функционал Экселя очень гибкий.
    3. Определение количества товара, которое осталось, после чего формирование прайса, который учитывает текущую ситуацию на складке.
    4. CRM-система.

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

    Как составить таблицу учета товара в Эксель

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

    Чтобы не допустить особо нелепых просчетов и ряда ошибок, лучше воспользоваться помощью опытных специалистов на первых порах. Но если их нет, не беда. Приведем простую инструкцию, как составить таблицу учета товара в Эксель.

    Порядок создания справочников

    Создание справочников – это самая главная задача, которую нужно выполнять в первую очередь. Какие могут быть справочники? Да самые разные: покупателей, поставщиков, самих товаров, скидок и любой другой информации, которая повторяется очень часто. Пользователь вправе выбирать, размещать эти данные в рамках одного листа или разделить каждый блок информации на другом листе. Рекомендуется все же второй вариант, поскольку позволяет лучше структурировать данные.

    Что такое справочник? Это список, содержащий определенную информацию, расположенную построчно.

    Последовательность действий при создании справочника следующая:

    1. Закрепление верхней строки.
    2. Определение типа справочника, который будет использоваться.
    3. Оформление справочника.

    Выпадающие списки

    Можно сделать так, чтобы пользователь выбирал нужную категорию данных в списке, после чего электронная таблица выдаст всю содержащуюся по этому критерию информацию. Для этого необходимо создать список, который можно добавить через команду «Данные – Проверка данных».

    Далее остается только настроить перечень, и он будет работать.

    Оборотные ведомости

    Чтобы сделать оборотную ведомость, нужно воспользоваться функцией СУММЕСЛИМН . Это обязательно нужно сделать в случае, если ваша задача похожа на приведенный выше пример.

    Как отслеживать критические остатки

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

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

    В любом случае, навыки создания и управления электронными таблицами являются невероятно важными для каждого предпринимателя. Не просто так говорят, что есть только две программы, которые приносят больше всего денег: PowerPoint и Excel. И это действительно так. Успехов вам.

    Ссылка на основную публикацию