Excel

Как создать формулу в excel с процентами: Как в Экселе посчитать проценты – решение проблемы

Содержание

Как посчитать процент от числа и долю в Эксель

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

Считаем долю от общего числа

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

Доля (%) = Число 1/Число 2*100%, где:

  • Число 1 – собственно говоря, наше исходное числовое значение
  • Число 2 – итоговое число, долю в котором мы хотим выяснить

К примеру, давайте попробуем вычислить, какова доля числа 15 в числе 37.

Результат нам нужен в процентах. В данном значение “Числа 1” равно 15, а “Числа 2” – 37.

  1. Выбираем ячейку, где нам нужно произвести расчеты. Пишем знак “равно” (“=”) и далее формулу расчета с нашими числами: =15/37*100%.
  2. После того, как мы набрали формулу, нажимаем клавишу Enter на клавиатуре, и результат сразу же отобразится в выбранной ячейке.

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

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

  1. Кликаем правой кнопкой мыши по ячейке с результатом (неважно, до того, как мы написали в ней формулу и получили результат или после), в появившемся перечне команд щелкаем по пункту “Формат ячеек…”.
  2. В окне форматирования мы окажемся во вкладке “Число”. Здесь в числовых форматах кликаем по строке “Процентный” и в правой части окна указываем желаемое количество знаков после запятой.
    Наиболее распространенный вариант – “2”, его мы и ставим в нашем примере. После этого жмем кнопку OK.
  3. Готово, теперь мы получим в ячейке именно процентное значение, что и требовалось изначально.

Кстати, когда формат отображения в ячейке настроен в виде процентов, вовсе не обязательно в формуле писать “*100%“. Достаточно будет выполнить простое деление чисел: =15/37.

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

Итак, приступим к выполнению поставленной задачи:

  1. Выбираем первую ячейку столбца (не считая шапку таблицы). Как обычно, написание любой формулы начинается со знака “=“. Далее пишем формулу расчета процента, аналогично рассмотренному примеру выше, только заменив конкретные числовые значения адресами ячеек, которые можно прописать вручную, либо добавляем их в формулу кликами мыши. В нашем случае, в ячейку E2 нужно написать следующее выражение: =D2/D16. Примечание: не забываем заранее настроить формат ячеек результирующего столбца, выбрав отображение в виде процентов.
  2. Нажимаем Enter, чтобы получить результат в заданной ячейке.
  3. Теперь нам нужно произвести аналогичные расчеты для остальных строк столбца. К счастью, возможности Эксель позволяют избежать ручного ввода формулы для каждой ячейки, и этот процесс можно автоматизировать путем копирования (растягивания) формулы в другие ячейки. Однако тут есть небольшой нюанс. В программе по умолчанию при копировании формул происходит корректировка адресов ячеек согласно смещению. Когда речь идет о продажах каждого отдельного наименования, так и должно быть, но координаты ячейки с итоговой выручкой должны оставаться неизменными.
    Чтобы ее зафиксировать (сделать абсолютной), нужно перед обозначениями строки и столбца добавить символ “$“. Либо, чтобы не печатать этот знак вручную, выделив адрес ячейки в формуле, можно просто нажать клавишу F4. По завершении нажимаем Enter.
  4. Теперь осталось растянуть формулу на другие ячейки. Чтобы это сделать, наводим курсор на правый нижний угол ячейки с результатом, указатель должен поменять форму на крестик, после чего, растягиваем формулу вниз, зажав левую кнопку мыши.
  5. Вот и все. Как мы и хотели, ячейки последнего столбца заполнились долями продаж каждого конкретного наименования продукции в совокупной выручке.

Разумеется, в расчетах вовсе не обязательно заранее считать итоговую выручку и выводить результат в отдельную ячейку. Все можно сразу посчитать с помощью одной формулы, которая для ячейки E2 выглядеть так: =D2/СУММ(D2:D15).

В данном случае, мы сразу посчитали общую выручку в формуле расчета доли, используя функцию СУММ. О том, как ее применять, читайте в нашей статье – “Как в Экселе посчитать сумму ячеек“.

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

$” перед обозначениями строк и столбцов в адресах ячеек диапазона суммы: =D2/СУММ($D$2:$D$15).

Находим процент от числа

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

Математическая формула для расчета выглядит следующим образом:

Число 2 = Процент (%) * Число 1, где:

  • Число 1 – исходное число, процент по которому нужно вычислить
  • Процент – соответсвенно, величина самого процента
  • Число 2 – финальное числовое значение, которое требуется получить.

Например, давайте узнаем, какое число составляет 15% от 90.

  1. Выбираем ячейку, в которой будем выводить результат и пишем формулу выше, подставляя в нее наши значения: =15%*90. Примечание: Так как результат должен быть в абсолютном выражении (т.е. в виде числа), формат ячейки – “общий” или “числовой” (но не “процентный”).
  2. Нажимаем клавишу Enter, чтобы получить результат в выбранной ячейке.

Подобные знания помогают решать множество математических, экономических задач, физических и других задач. Допустим, у нас есть таблица с продажами обуви (в парах) за 1 квартал, и мы планируем в следующем продать на 10% больше. Нужно определить, какому количеству пар для каждого наименования соответствуют эти 10%.

Чтобы выполнить задачу, выполняем следующие шаги:

  1. Для удобства создаем новый столбец, в ячейки которого будем выводить результаты расчетов. Выбираем первую ячейку столбца (на считая шапки) и пишем в ней формулу выше, заменив конкретное значение сходного числа на адрес ячейки: =10%*B2.
  2. После этого жмем клавишу Enter, и результат сразу же отобразится в ячейке с формулой.
  3. Если мы хотим избавиться от цифр после запятой, так как в нашем случае количество пар обуви может исчисляться только целыми числами, переходим в формат ячейки (как это сделать, мы разобрали выше), где выбираем числовой формат с отсутствием десятичных знаков.
  4. Теперь можно растянуть формулу на оставшиеся ячейки столбца.

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

  1. Допустим, наша таблица содержит такой столбец “E” (Значение %).
  2. Пишем в первой ячейке результирующего столбца все ту же формулу, только теперь и конкретное значение процента меняем на адрес ячейки с содержащейся в ней процентной величиной: =E2*B2.
  3. Щелкнув Enter получаем результат в заданной ячейке. Осталось только растянут его на нижние строки.

Заключение

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

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

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

Вычислительные действия в ячейке

Итак, если вам просто нужно узнать, чему будет равно число, после прибавки к нему определенного процента, то следует в любую ячейку листа, или в строку формул, вбить выражение по следующему шаблону: «=(число)+(число)*(величина_процента)%».

Допустим, нам нужно вычислить, какое число получится, если прибавить к 140 двадцать процентов. Записываем следующую формулу в любую ячейку, или в строку формул: «=140+140*20%».

Далее жмем на кнопку ENTER на клавиатуре, и смотрим результат.

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

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

Прежде всего, выбираем ячейку, куда будет выводиться результат. Ставим в ней знак «=». Далее, кликаем по ячейке, содержащей данные, к которым следует прибавить процент. Ставим знак «+». Опять кликаем по ячейке, содержащей число, ставим знак «*». Далее, набираем на клавиатуре величину процента, на который следует увеличить число. Не забываем после ввода данной величины ставить знак «%».

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

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

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

Мы выяснили, что прибавить процент к числу в программе Microsoft Excel не так уж сложно. Тем не менее, многие пользователи не знают, как это сделать, и допускают ошибки. Например, самой распространенной ошибкой является написание формулы по алгоритму «=(число)+(величина_процента)%», вместо «=(число)+(число)*(величина_процента)%». Данное руководство должно помочь не допускать подобных ошибок.

Мы рады, что смогли помочь Вам в решении проблемы.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Помогла ли вам эта статья?
ДА НЕТ

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

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

Вычитание процента в Excel

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

В виде формулы в Экселе это будет выглядеть следующим образом: «=(число)-(число)*(величина_процента)%».

Продемонстрируем вычитание процента на конкретном примере. Допустим, из числа 48 нам нужно вычесть 12%. Кликаем по любой ячейке листа, или делаем запись в строке формул: «=48-48*12%».

Чтобы произвести вычисление, и увидеть результат, кликаем по кнопке ENTER на клавиатуре.

Вычитание процентов из таблицы

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

В случае, если мы хотим вычесть определенный процент из всех ячеек конкретного столбца, то, прежде всего, становимся на самую верхнюю пустую ячейку таблицы. Ставим в ней знак «=». Далее, кликаем по той ячейке, процент из которой нужно вычесть. После этого, ставим знак «-», и опять кликаем по той самой ячейке, по которой кликали до этого. Ставим знак «*», и с клавиатуры набираем величину процентов, которую следует вычесть. В конце ставим знак «%».

Кликаем по кнопке ENTER, после чего, производятся вычисления, и результат выводится в ту ячейку, в которую мы записали формулу.

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

Итак, мы рассмотрели два основных случая вычитания процентов из числа в программе Microsoft Excel: как простого вычисления, и как операцию в таблице. Как видим, процедура вычитания процентов не слишком сложна, а применение её в таблицах помогает существенно упростить работу в них.

Мы рады, что смогли помочь Вам в решении проблемы.
Опишите, что у вас не получилось. Наши специалисты постараются ответить максимально быстро.
Помогла ли вам эта статья?
ДА НЕТ

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

Процент – это сотая доля от числа. Например, 1% от тысячи: 1000/100 = 10. Проценты используют для расчета различных показателей: соотношения, отклонения, маржа, ставка по кредиту или депозиту и др. В этой статье я расскажу, как в Экселе удобно работать с такими долями и как от суммы высчитать процент.

Чтобы рассчитать процент числа x от y, нужно разделить x на y  и умножить это на 100. К примеру, чтобы узнать, сколько процентов от 12 составляет тройка, считаем так: 3/12*100=25%.

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

Есть 3 простых способа записи процентов:

  1. Запишите десятичное число, и для этой ячейки выполните команду Главная – Число – Процентный формат
  2. После введения десятичной дроби выделите эту ячейку и нажмите Ctrl+Shift+;
  3. Введите в ячейку число и поставьте знак процента

В первых двух случаях программа умножит дробь на 100 и добавит значок «%». В третьем – только применит процентный формат числа.

Вы можете изменять количество отображаемых знаков после запятой кнопками «Увеличить разрядность» и «Уменьшить разрядность». Это не изменит значение числа, а только его отображение на листе.

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

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

Как узнать процент от суммы в Экселе

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

Как вычесть процент от числа, или добавить

Чтобы увеличить число на заданное количество процентов, нужно умножить его на сумму (1+процент):

А чтобы уменьшить – умножаем на разницу (1-процент).

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

Чтобы узнать, на сколько процентов y больше или меньше, чем x, нужно вычислить такое выражение: (x-y)/x.

На практике расчет таков:

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

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

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

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

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

Как в эксель посчитать процент от числа в столбце, формулы расчета

Нам  нужно рассчитать  сумму скидки зная процент и начальную цену.  Процент скидки  указан в отдельной графе.  Для расчета нужно цену товара умножить на 100 и разделить на величину нашей скидки. Как это сделать ексель а не на калькуляторе?   Ввести формулы в ячейку через знак «равно», ставя на нужные ячейки курсор мыши.

В первой строке таблицы у нас ячейка в графе «Процент скидки»   отформатирована в «процентном» формате. Это значит, что все числа введенные в нее  будут поделены на сто, а отображаются они в таблице со значком «%»  Ставим курсор в графу «Сумма скидки», вводим формулу =E24*F24 То есть «Цену»  умножаем на «Процент скидки»:

При вводе формул зависимые ячейки выделяются  разным цветом, что удобно. После ввода формулы нажимаем Enter. Во второй строке графы «Процент скидки » величина процента указана в формате обычного числа. В этом варианте  в графе «сумма скидки» мы уже введем в ячейку классическую формулу из математики за пятый класс (через знак «равно»):

 То есть «Цену» делим на сто и умножаем на «Процент». Результат получился одинаковым в обеих строках.  Итак, в экселе (в зависимости от формата  созданных таблиц) мы можем вводить одни и те же формулы по разному.  Решим обратную задачу. Пусть нам известна только  сумма скидки и цена товара.

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

Нужно «Сумму скидки» разделить на «Цену» и  умножить на сто если формат ячейки числовой…

… или «Сумму скидки» поделить на «Цену» если формат ячейки в процентах:

Считаем процент одного числа от другого в эксель

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

В нашем примере таким вычисляемым показателем  будет является графа «Цена». Теперь вводим формулу в нее.

Нужно «Сумму скидки» поделить на «Процент скидки» если формат ячейки в графе «Цена» процентный…

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

 

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

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

Можно сделать наглядную таблицу продаж, в которой будет отражен процент продаж каждого товара в общей доле. Делают такие таблицы по горизонтали, можно и  по вертикали. Мне по душе вертикальные — это когда ИТОГИ расположены внизу таблицы. Нам требуется вычислить товары-лидеры продаж.

У нас есть список товаров, сумма продаж каждого за месяц и итоговая сумма  всех продаж за месяц. Нужно разделить «сумму продаж за месяц»   на «ИТОГО» и умножить на сто. Вводим формулу в графу лидеры продаж в первой строке:

 

 У меня графа «Лидеры продаж» в процентном формате и ее итоговая графа то же. Учитываю этот момент. Обратите внимание! В формуле перед итоговой ячейкой ставим знак $ и перед номером строки ставим $ — это будет абсолютный  адрес (клавиша Shift +4).

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

Протягиваем формулу мышью по графе вниз до «итогов» и получаем готовую таблицу если все сделали правильно:

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

Автор публикации

не в сети 3 недели

admin

0 Комментарии: 61Публикации: 386Регистрация: 04-09-2015

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

Пакет для создания документов Microsoft Office — популярный комплекс программ. С ними знаком каждый человек, который набирал информацию на компьютере, составлял списки, рассчитывал различного рода показатели, создавал презентации для демонстрации их аудитории. Среди программ пакета особого внимания заслуживает «Эксель» — помощник в проведении различных расчетов. Для определения % от числа, прибавления процентных значений стоит знать некоторые нюансы, которые упростят расчеты.

Формат ячеек

Перед введением формулы в ячейку следует установить формат «процентный». В этом случае нет необходимости вводить дополнительные действия. Рядом с полученным значением автоматически поставится значок «%».

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

Основная формула

В «Экселе» существуют общие правила для расчета процентов. После выбора формата ячейки вводят формулу.

Базовую формулу процентов в Excel записывают следующим образом:

  1. Ставится значок «=».
  2. Курсором нажимается то значение, процент которого нужно рассчитать.
  3. Ставится косая линия деления с клавиатуры.
  4. Затем нажимается ячейка, к которой рассчитывается процентное соотношение.

Расчет процентов в «Экселе» в столбец

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

Расчет процентных значений в строках

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

Ставится значок «равно», выбирается ячейка, проценты которой необходимо рассчитать. Затем — черта деления. Выбирается значение ячейки, к которой нужно рассчитать процентное соотношение.

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

Расчет от общей суммы

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

Для этого можно поставить знак «=» и, выделяя поочередно ячейки, нажимать знак «+» между ними. Затем нажимается клавиша Enter.

Также можно после проставления «=» набрать на клавиатуре СУММ, открыть скобку, ввести диапазон ячеек для суммирования, после чего нажать Enter.

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

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

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

Если нужно прибавить проценты Excel к столбцу значений, можно вписать следующую формулу: значок «=», ячейка, к которой нужно добавить проценты, величина процентов, знак «/», та же ячейка, к которой добавляются проценты, умноженные на сто.

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

Перед тем как в Excel прибавить процент к числу, расположенному в другом столбце, формула должна принять вид: знак «равно», ячейка, к которой нужно добавить проценты, знак «плюс», ячейка со значением процентов со знаками «$», умноженная на ячейку, к которой прибавляем проценты. При условии, что у последней формат установлен «процентный».

Если проценты находятся в одном столбце, а значения, к которым нужно их добавить — в другом, то формула, позволяющая в Excel прибавить проценты к числу, примет следующий вид: знак «равно», ячейка со значением, знак «плюс», ячейка с процентами, умноженная на ячейку с числом. Важно не перепутать форматы ячеек! Процентный формат может быть установлен только у ячеек с %.

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

Во всех формулах расчетов процентов обязателен знак «=». Если не поставить его, формула не произведет расчет.

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

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

Если нужно добавить проценты одной ячейки, рассчитать долю от одного значения, используется значок «$», который устанавливается в строку формул перед номером строки и буквой столбца.

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

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

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

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

Базовый процент

Термин «процент» образован от латинского per centum , что означает «на сотню». Как вы, наверное, помните из школьного урока математики, процент — это доля от 100, которая вычисляется путем деления числителя на знаменатель и умножения результата на 100.

Основная процентная формула выглядит следующим образом:

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

Например, если у вас было 20 яблок и вы дали 5 своим друзьям, сколько вы дали в процентном отношении? Выполнив несложный расчет = 5/20 * 100 вы получите ответ — 25%.

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

К сожалению, не существует универсальной формулы Excel для расчета процентов, которая охватывала бы все возможные сценарии. Если вы спросите кого-нибудь: «Какую формулу процентов я использую, чтобы получить желаемый результат?», Скорее всего, вы получите ответ типа «Ну, это зависит от того, какого именно результата вы хотите достичь».

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

Базовая процентная формула Excel

Основная формула для расчета процента в Excel:

Часть / Итого = Процент

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

А теперь давайте посмотрим, как можно использовать процентную формулу Excel для реальных данных. Предположим, у вас есть количество « Заказанных товаров » в столбце B и « Доставленных товаров » в столбце C. Чтобы узнать процент доставленных товаров, выполните следующие действия:

  • Введите формулу = C2 / B2 в ячейку D2 и скопируйте ее в нужное количество строк.
  • Нажмите кнопку Percent Style (вкладка Home > Number group), чтобы отобразить полученные десятичные дроби в виде процентов.
  • Не забудьте при необходимости увеличить количество десятичных знаков, как описано в разделе «Советы по процентному соотношению».
  • Готово! 🙂

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

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

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

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

Пример 1. Итог находится в конце таблицы в определенной ячейке

Очень распространенный сценарий — сумма в одной ячейке в конце таблицы. В этом случае процентная формула будет аналогична той, которую мы только что обсудили, с той лишь разницей, что ссылка на ячейку в знаменателе является абсолютной ссылкой (со знаком $).Знак доллара фиксирует ссылку на заданную ячейку, поэтому она никогда не меняется независимо от того, куда копируется формула.

Например, если у вас есть некоторые значения в столбце B и их сумма в ячейке B10, вы должны использовать следующую формулу для вычисления процентов от общей суммы: = B2 / $ B $ 10

Вы используете относительную ссылку на ячейку B2, потому что хотите, чтобы она изменялась при копировании формулы в другие ячейки столбца B. Но вы вводите $ B $ 10 в качестве абсолютной ссылки на ячейку, потому что вы хотите оставить знаменатель фиксированным на B10. при автоматическом заполнении формулы до строки 9.

Наконечник. Чтобы сделать знаменатель абсолютной ссылкой, введите вручную знак доллара ($) или щелкните ссылку на ячейку в строке формул и нажмите F4.

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

Пример 2. Части итога находятся в нескольких строках

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

В этом случае вы можете использовать функцию СУММЕСЛИ, чтобы сначала сложить все числа, относящиеся к данному продукту, а затем разделить это число на общее количество, например:

= СУММЕСЛИ (диапазон, критерий, диапазон_сумма) / всего

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

= СУММЕСЛИ (A2: A9, E1, B2: B9) / $ B $ 10

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

= СУММЕСЛИ (A2: A9, «вишня», B2: B9) / $ B $ 10

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

= (СУММЕСЛИ (A2: A9, «вишня», B2: B9) + СУММЕСЛИ (A2: A9, «яблоки», B2: B9)) / $ B $ 10

Для получения дополнительной информации о функции СУММ, пожалуйста, ознакомьтесь со следующими руководствами:

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

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

Формула Excel для процентного изменения (процентное увеличение / уменьшение)

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

Процентное изменение = (B — A) / A

При применении этой формулы к реальным данным важно правильно определить, какое значение — A, а какое — B.Например, вчера у вас было 80 яблок и как у вас 100, что означает, что теперь у вас на 20 яблок больше, чем раньше, что на 25% больше. Если у вас было 100 яблок, а теперь у вас их 80, ваше количество яблок уменьшилось на 20, то есть на 20%.

Учитывая вышеизложенное, наша формула Excel для процентного изменения принимает следующую форму:

(новое значение — старое значение) / старое значение

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

Пример 1. Расчет процентного изменения между 2 столбцами

Предположим, у вас есть цены за последний месяц в столбце B и цены за этот месяц в столбце C. Затем вы можете ввести следующую формулу в ячейку D2, чтобы рассчитать процентное изменение в таблице Excel:

= (C2-B2) / B2

Эта формула процентного изменения вычисляет процентное увеличение / уменьшение в этом месяце (столбец C) по сравнению с прошлым месяцем (столбец B).

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

Пример 2. Расчет процентного изменения между строками

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

= (C3-C2) / C2

Где C2 — это 1 ячейка st , а C3 — ячейка 2 и с данными.

Примечание. Обратите внимание, что вы должны пропустить первую строку с данными и поместить свою процентную формулу Excel в ячейку 2 и , которая в этом примере является D3.

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

Если вы хотите рассчитать процентное изменение по сравнению с определенной ячейкой, вам необходимо исправить ссылку на эту ячейку, используя абсолютную ссылку на ячейку со знаком $, e. г. $ C $ 2.

Например, вот формула процентного изменения Excel, которая вычисляет процентное увеличение / уменьшение для каждого месяца по сравнению с январем (C2):

= (C3- 2 канадских доллара) / 2 канадских доллара

Когда вы перетаскиваете формулу вниз, чтобы скопировать ее в другие ячейки, абсолютная ссылка ($ C $ 2) останется прежней, а относительная ссылка (C3) изменится на C4, C5 и так далее.

Расчет суммы и итога в процентах

Как вы только что видели, вычислять проценты в Excel легко, как и вычислять суммы и итоги, если вы знаете проценты.

Пример 1. Рассчитать сумму по итогу и в процентах

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

Поможет следующая формула:

Итого * Процент = Сумма

Предполагая, что общее значение находится в ячейке A2, а проценты в B2, приведенная выше формула превращается в простую форму = A2 * B2 и возвращает 104.50

Помните, когда вы вводите число, за которым следует знак процента (%) в Excel, число интерпретируется как сотая часть его значения. Например, 11% фактически сохраняется как 0,11, и Excel использует это базовое значение во всех формулах и расчетах.

Другими словами, формула = A2 * 11% эквивалентна = A2 * 0,11 . Естественно, вы можете использовать десятичное число, соответствующее процентному соотношению, непосредственно в формуле, если это лучше подходит для ваших рабочих листов.

Пример 2. Расчет итога по сумме и процентам

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

Поскольку 30% — это скидка, вы сначала вычитаете ее из 100%, чтобы узнать, какой процент вы действительно должны заплатить (100% — 30% = 70%). Теперь вам нужна формула для расчета исходной цены, т.е. найти число, 70% которого равно 400.

Формула выглядит следующим образом:

Сумма / Процент = Итого

Применительно к реальным данным он может принимать любую из следующих форм:

= A2 / B2 или = A2 / 0.7 или = A2 / 70%

Как увеличить / уменьшить число на процент

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

Чтобы увеличить сумму на процент, используйте эту формулу:

= Сумма * (1 +%)

Например, формула = A1 * (1 + 20%) увеличивает значение в ячейке A1 на 20%.

до уменьшить сумма на процент:

= Сумма * (1 -%)

Например, формула = A1 * (1-20%) уменьшает значение в ячейке A1 на 20%.

В нашем примере, если A2 — ваши текущие расходы, а B2 — процент, на который вы хотите увеличить или уменьшить эту сумму, вот формулы, которые вы должны ввести в ячейку C2:

Увеличение на процент: = A2 * (1 + B2)

Уменьшить на процент: = A2 * (1-B2)

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

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

Вот 5 быстрых шагов для решения этой задачи:

  1. Введите все числа, которые вы хотите увеличить или уменьшить в каком-либо столбце, столбец B в этом примере.
  2. В пустой ячейке введите одну из следующих формул:

    Увеличение в процентах: = 1 + 20%

    Уменьшить на процент: = 1-20%

    Естественно, вы можете заменить 20% в приведенных выше формулах на желаемый процент.

  3. Выделите ячейку с формулой (в нашем случае C2) и скопируйте ее, нажав Ctrl + C.
  4. Выберите диапазон ячеек, который нужно изменить, щелкните выделение правой кнопкой мыши и выберите Специальная вставка…
  5. В диалоговом окне Специальная вставка выберите Значения в разделе Вставить , Умножить в разделе Операция и нажмите ОК .

И вот результат — все числа в столбце B увеличены на 20%:

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

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

Вас также может заинтересовать

Как рассчитать процент отклонения в 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 по умолчанию приоритет операции деления выше, чем операции вычитания в этой формуле, нет необходимости применять круглые скобки.

Формулы

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

Рисунок 2.1 «Заполненная рабочая книга по личному кассовому бюджету» показывает заполненную рабочую книгу, которая будет продемонстрирована в этой главе. Обратите внимание, что эта книга содержит четыре листа.Первый рабочий лист, Сводка бюджета , содержит формулы, которые используют или ссылаются на данные из трех других рабочих листов. В результате рабочий лист «Сводка бюджета » служит обзором данных, которые были введены и рассчитаны на трех других рабочих листах книги.

Создание базовой формулы

Видеоурок: Основные формулы

Формулы Используются для вычисления различных математических результатов в Excel и могут использоваться для создания практически любых пользовательских вычислений, необходимых для вашей цели.используются для вычисления различных математических результатов в Excel и могут использоваться для создания практически любых пользовательских вычислений, необходимых для вашей цели. Кроме того, при построении формулы в Excel вы используете местоположения ячеек, которые при добавлении в формулу становятся ссылками на ячейки Адреса ячеек, используемые в формулах. Excel ссылается на значения, введенные в ячейки, используемые в формулах, для получения выходных данных. Когда значения изменяются в ячейке, на которую ссылается формула, Excel автоматически создает новые выходные данные.Когда ссылка на ячейку используется сама по себе в месте расположения ячейки (адресу ячейки предшествует знак равенства [ = ]), Excel отображает значение, введенное в эту ячейку. Это означает, что Excel использует или ссылается на введенное число в ячейку при вычислении математического вывода. В результате, когда числа в ссылках на ячейки изменяются, Excel автоматически создает новый результат. Это то, что дает Excel возможность создавать различные сценарии «что, если», которые будут объяснены позже в этой главе.

Чтобы продемонстрировать построение базовой формулы, мы начнем работать с рабочим листом «Сведения о бюджете» в рабочей книге «Персональный бюджет», который показан на рисунке 2.2 «Рабочий лист с подробным бюджетом». Чтобы заполнить этот рабочий лист, мы добавим несколько формул и функций. Таблица 2.1 «Определения категорий расходов» содержит определения для каждой из категорий расходов, перечисленных в диапазоне A3: A11. Когда вы разрабатываете личный бюджет, эти категории определяются на основе того, как вы тратите свои деньги.Вполне вероятно, что каждый человек может иметь разные категории или определять одни и те же категории по-разному. Поэтому важно просмотреть определения в таблице 2.1 «Определения категорий расходов», чтобы понять, как мы определяем эти категории, прежде чем продолжить.

Рисунок 2.2 Рабочий лист деталей бюджета

Таблица 2.1 Определения категорий расходов

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

Первая формула, которую мы добавим в рабочий лист «Сведения о бюджете» , рассчитает значения ежемесячных расходов.Формула будет построена таким образом, чтобы значения в столбце «Годовые расходы» делились на 12. Это покажет, сколько денег будет потрачено в месяц для каждой из категорий, перечисленных в столбце A. Ниже поясняется, как работает эта формула. создано:

  1. Щелкните вкладку рабочего листа Сведения о бюджете , чтобы открыть рабочий лист.
  2. Щелкните ячейку C3.
  3. Введите знак равенства ( = ). Когда первый символ, введенный в ячейку, является знаком равенства, он сигнализирует Excel о необходимости выполнения вычисления или создания логического вывода.
  4. Тип D3 . Это добавляет D3 к формуле, которая теперь является ссылкой на ячейку. Excel будет использовать любое значение, введенное в ячейку D3, для вывода.
  5. Введите символ косой черты (/). Это символ деления в Excel. Как показано в таблице 2.2 «Математические операторы Excel», математические операторы в Excel немного отличаются от тех, которые используются в обычном калькуляторе.
  6. Введите число 12 .При этом значение в ячейке D3 делится на 12. В этой формуле — число или постоянное числовое значение, используемое при построении формул. Степень / Показатель

    Почему?

    Использовать ссылки на ячейки

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

    Рисунок 2.3 «Добавление формулы на лист» показывает, как формула отображается в ячейке C3 до нажатия клавиши ENTER.Рисунок 2.4 «Вывод формулы для ежемесячных расходов» показывает вывод формулы после нажатия клавиши ENTER. Ежемесячные расходы на коммунальные услуги составляют 250 долларов США, поскольку формула берет годовые расходы в ячейке D3 и делит его на 12. Если значение в ячейке D3 изменяется, формула автоматически выводит новый результат. Мы рассчитываем ежемесячные расходы для каждой категории, потому что людям часто платят и выставляют счета за эти товары на ежемесячной основе. Эта формула позволяет сравнить ваш ежемесячный доход с вашими ежемесячными счетами, чтобы определить, достаточно ли у вас дохода для оплаты этих расходов.

    Рисунок 2.3 Добавление формулы в рабочий лист

    Рисунок 2.4 Выходная формула для ежемесячных расходов

    Почему?

    Использовать универсальные константы

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

    Относительные ссылки (копирование и вставка формул)

    Дополнительный файл: перейдите к цели Excel 2.00. (Используйте файл Excel Objective 2.01, если начинаете здесь.)

    Видеоурок: Относительные ссылки

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

    Как создать собственную формулу в Excel

    Как и во всех моих статьях, мы начнем со сценария. У меня ниже таблица с деталями заказа. От общей суммы заказа мы получаем скользящую скидку. Если мы заказываем от 0 до 49,99 $, мы не получаем скидки, от 50 до 199.99 $ мы получаем скидку 10%, а от 200 $ до 399,99 $ — скидку 20%. При заказе любого товара на сумму более 400 $ заказ считается недействительным. Это добавит небольшой поворот, чтобы сделать UDF более интересным.

    Чтобы создать UDF, нам нужно добавить код VBA в модуль. Для этого нам нужно находиться в среде VBA, и для этого мы используем кнопку Visual Basic на вкладке «Разработчик» на ленте, как показано ниже, или сочетание клавиш ALT + F11.

    Как только мы это сделаем, должна появиться среда разработки VBA.Именно здесь вы будете проводить большую часть своего времени, если следите за моими сообщениями в блоге или решите пройти наш курс электронного обучения Excel VBA.

    Прежде чем мы создадим определяемую пользователем функцию, нам нужно добавить модуль в наш проект VBA. У нас уже был бы хотя бы один макрос, если бы мы записали какие-либо макросы в эту книгу, но, поскольку у меня нет, нам нужно будет добавить один. Для этого щелкните правой кнопкой мыши проект VBA в окне «Проект» и выберите «Вставить + модуль» (показано ниже).

    В этот новый модуль мы добавляем следующий код VBA:

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

    Теперь мы можем вернуться к нашему листу и использовать эту UDF так же, как мы использовали бы формулу, как вы можете видеть ниже.

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

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

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

    И получившийся лист:

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

    Расчет процентов с помощью чисел — Apple Community

    Привет Адриан,

    Вы написали: « Мне нужно сделать это с двумя столбцами на самом деле A и B, оба столбца должны быть добавлены на эти 5%, я думаю, что это только возможно. «

    Если я правильно читаю описание вашей таблицы, столбец A показывает количество единиц, столбец B — цену каждой единицы, а столбец C — цену этого количества единиц.

    Если вы увеличите цену за единицу на 5%, это также увеличит общую цену n единиц на 5% при условии, что вы умножите количество на новую цену за единицу, а не на старую цену за единицу.

    Вот шаг за шагом:

    Исходная таблица:

    Формула в C2 и заполненный столбец C: = A * B

    Добавьте новый столбец (D), в котором будет храниться формула для увеличенной цены.

    D2 и заполнено: = B * (1 + 5%)

    Не снимая выделенных ячеек, скопируйте (команда-C)

    Теперь выберите ячейку B2 и перейдите в Edit> Paste Values ​​(Numbers ‘ 09) или Правка> Вставить результаты формулы (числа 3)

    Результаты должны быть такими, как показано ниже:

    (Обратите внимание, что столбец C был пересчитан, чтобы показать результаты увеличения цен за единицу на 5%.Столбец D (обозначенный «temp») также был пересчитан, чтобы показать, какими будут цены за единицу после второго повышения на 5%. последний шаг — удалить этот столбец, оставив нас с исходной таблицей, но с удельными ценами, увеличенными на 5%:

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

    Изменения цен будут внесены в прейскурант индивидуально или в общем порядке, как здесь. Обратите внимание, что новые цены повлияют на новые счета-фактуры И любые существующие счета-фактуры, все еще связанные с прайс-листом (поэтому каждый «печатается», чтобы отделить его от документа расчета). Вот простой пример использования прайс-листа в качестве справочной таблицы. Номер позиции и количество вводятся в счете-фактуре.Остальное добавляется по формулам.

    Формулы:

    Прейскурант не содержит формул.

    Счет-фактура:

    C2 и заполнен справа от столбца D и ниже до строки 6:

    = ЕСЛИ ОШИБКА (ВПР ($ B, прайс-лист :: $ A: $ C, COLUMN () — 1), «»)

    В строке 6 пустая ячейка в столбце A вызовет ошибку «не удалось найти». ЕСЛИОШИБКА перехватывает это и помещает нулевую строку в C6 (и в D6).

    E2 и заполнено до E6: = IF (LEN (D) <1, "", A * D)

    LEN (D) возвращает длину (в количестве символов) содержимого ячейки в той же ячейке строка столбца D.Если ячейка пуста или содержит пустую строку (как в строке 6), длина равна нулю, и IF поместит пустую строку в столбец E.

    SUM в строке седьмой интерпретирует текст (включая пустую строку) как ноль, поэтому это не влияет на суммы в кумах A и E.

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

    A7: = SUM (A)

    E7: = SUM (E)

    С уважением,

    Barry

    Справка, примеры и информация о формулах Excel

    Обновлено: 30.11.2020 компанией Computer Hope

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

    Основы

    • Все формулы электронных таблиц начинаются со знака равенства (=).
    • После символа равенства вводится ячейка или функция формулы. Функция сообщает электронной таблице тип формулы.
    • Если выполняется математическая функция, математическая формула заключена в круглые скобки.
    • Использование двоеточия (:) позволяет получить диапазон ячеек для формулы. Например, A1: A10 — это ячейки с A1 по A10.
    • По умолчанию формулы создаются с использованием относительной ссылки на ячейку, и если вы добавите знак доллара ($) перед столбцом или строкой, он станет абсолютной ссылкой на ячейку.

    Ввод формулы электронной таблицы

    Ниже представлен анимированный визуальный пример того, как формула Excel может быть вставлена ​​в электронную таблицу. В нашей первой формуле, введенной в ячейку «D1», мы вручную вводим формулу = сумма, чтобы сложить 1 + 2 (в ячейках A1 и B2), чтобы получить сумму «3».«В следующем примере мы используем мышь для выделения ячеек с A2 по D2, а затем щелкаем кнопку формулы в Excel, чтобы автоматически создать формулу. Далее мы покажем, как вы можете вручную ввести формулу, а затем с помощью мыши получить значения ячеек (вы также можете выделить несколько ячеек, чтобы создать диапазон). Наконец, мы вручную вводим формулу раз (*), используя функцию суммы, чтобы найти значение 5 * 100.

    Примеры формул

    Запись

    Перечисленные ниже функции могут не совпадать на всех языках Microsoft Excel.Все эти примеры выполнены в английской версии Microsoft Excel.

    Наконечник

    Примеры ниже перечислены в алфавитном порядке. Если вы хотите начать с наиболее распространенной формулы, мы рекомендуем начать с формулы = СУММ.

    =

     = 

    = (равно) создает ячейку, равную другой. Например, если вы поместите = A1 в B1, все, что было в A1, автоматически будет помещено в B1. Вы также можете создать формулу, которая сделает одну ячейку равной более чем одному значению.Например, если в ячейке A1 было имя, а в ячейке B1 — фамилия, вы можете поместить в ячейку A2 = A1 & «» & B1 , которая объединяет A1 с B1 с пробелом между каждым значением. Вы также можете использовать формулу конкатенации для объединения значений ячеек.

    СРЕДНЕЕ

     = СРЕДНИЙ (X: X) 

    Отображение среднего количества между ячейками. Например, если вы хотите получить среднее значение для ячеек от A1 до A30, введите: = AVERAGE (A1: A30) .

    СЧЕТ

     = СЧЁТ (X: X) 

    Подсчитайте количество ячеек в диапазоне, который содержит только число.Например, вы можете узнать, сколько ячеек между A1 и A15 содержат числовое значение, используя = COUNT (A1: A15) . Если бы ячейки A1 и A5 содержали только числа, значение ячейки, содержащей эту функцию, было бы равно «2».

    COUNTA

     = СЧЁТ (X: X) 

    Подсчитайте количество ячеек в диапазоне, которые содержат любой текст (текст и числа, а не только числа) и не являются пустыми. Например, вы можете подсчитать количество ячеек, содержащих текст, в ячейках с A1 по A20, используя = COUNTA (A1: A20) .Если бы семь ячеек были пустыми, было бы возвращено число «13».

    СЧЕТЕСЛИ

      = СЧЁТЕСЛИ (X: X; «*»)  

    Подсчитайте ячейки с определенным значением. Например, если у вас = СЧЁТЕСЛИ (A1: A10, «ТЕСТ») в ячейке A11, то любая ячейка от A1 до A10, содержащая слово «тест», будет считаться за единицу. Итак, если у вас есть пять ячеек в этом диапазоне, которые содержат слово test, A11 скажет «5».

    IF

      = ЕСЛИ (*)  

    Синтаксис оператора IF: = IF (CELL = «VALUE», «PRINT OR DO THIS», «ELSE PRINT OR DO THIS») .Например, формула = ЕСЛИ (A1 = «», «ПУСТО», «НЕ ПУСТО») заставляет любую ячейку, кроме A1, говорить «ПУСТО», если в ячейке A1 ничего не было. Если A1 не пуст, в других ячейках будет указано «НЕ ПУСТО». Оператор IF имеет более сложное применение, но, как правило, его можно свести к приведенной выше структуре.

    Использование IF также может быть полезно в тех случаях, когда вам может потребоваться вычислить значения в ячейке, но только если эти ячейки содержат значения. Например, вы можете разделять значения между двумя ячейками. Однако, если в ячейках ничего нет, вы получите # DIV / 0! ошибка.Используя оператор IF, вы можете вычислить ячейку, только если она содержит значение. Например, если вы хотите выполнить функцию деления только в том случае, если A1 содержит значение, вы можете ввести: = IF (A1 = «», «», SUM (B1 / A1)) , который делит ячейку B1 на A1, только если A1 содержит текст. В противном случае ячейка остается пустой.

    КОСВЕННО

     = КОСВЕННО («A» и «2») 

    Возвращает ссылку, указанную в текстовой строке. В приведенном выше примере формула вернет значение ячейки, содержащейся в A2.

     = КОСВЕННО («А» & СЛУЧАЙНОМУ (1,10)) 

    Возвращает значение случайной ячейки между A1 и A2 с использованием косвенных функций и функций randbetween (объяснено ниже).

     = МЕДИАНА (A1: A7) 

    МИН. И МАКС.

    Найдите медиану значений ячеек с A1 по A7. Например, четыре — это медиана для 1, 2, 3, 4, 5, 6, 7.

     = МИН / МАКС (X: X) 

    Min и Max представляют минимальную или максимальную сумму в ячейках. Например, если вы хотите получить минимальное значение между ячейками A1 и A30, вы должны положить = MIN (A1: A30) или если вы хотите получить максимальное значение примерно = MAX (A1: A30) .

    ПРОДУКТ

     = ПРОДУКТ (X: X) 

    Умножает несколько ячеек вместе. Например, = ПРОДУКТ (A1: A30) умножит все ячейки вместе, поэтому A1 * A2 * A3 и т. Д.

    RAND

     = СЛУЧАЙ () 

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

    СЛУЧАЙНОМУ

     = СЛУЧМЕЖДУ (1,100) 

    Создает случайное число между двумя значениями.В приведенном выше примере формула создаст случайное целое число от 1 до 100.

    ТУР

     = ОКРУГЛ (X, Y) 

    Округлите число до определенного количества десятичных знаков. X — это ячейка Excel, содержащая число, которое нужно округлить. Y — число десятичных знаков для округления. Ниже приведены примеры.

     = ОКРУГЛ (A2; 2) 

    Округляет число в ячейке A2 до одного десятичного знака. Если число равно 4,7369, в приведенном выше примере это число будет округлено до 4.74. Если число 4,7614, оно будет округлено до 4,76.

     = ОКРУГЛ (A2; 0) 

    Округляет число в ячейке A2 до нуля десятичных знаков или до ближайшего целого числа. Если число равно 4,736, в приведенном выше примере это число будет округлено до 5. Если число равно 4,367, оно будет округлено до 4.

    СУМ

     = СУММ (X: X) 

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

     = СУММ (A1 + A2) 

    Складываем ячейки A1 и A2.

     = СУММ (A1: A5) 

    Добавьте ячейки с A1 по A5.

     = СУММ (A1, A2, A5) 

    Складывает ячейки A1, A2 и A5.

     = СУММ (A2-A1) 

    Вычтите ячейку A1 из A2.

     = СУММ (A1 * A2) 

    Умножить ячейки A1 и A2.

     = СУММ (A1 / A2) 

    Разделите ячейку A1 на A2.

    СУММЕС

     = СУММЕСЛИ (X: X; «*» X: X) 

    Выполнять функцию СУММ, только если в первых выбранных ячейках есть указанное значение. Примером этого может быть = СУММЕСЛИ (A1: A6, «ТЕСТ», B1: B6) , который добавляет только значения B1: B6, если слово «тест» было помещено где-то между A1: A6.Поэтому, если вы поместите TEST (без учета регистра) в A1, но у вас есть числа от B1 до B6, он добавит только значение в B1, потому что TEST находится в A1.

    • См. Наше определение СУММЕСЛИ для дополнительной информации.

    СЕГОДНЯ

     = СЕГОДНЯ () 

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

    TREND

     = ТЕНДЕНЦИЯ (X: X) 

    Найти общее значение ячейки. Например, если в ячейках с A1 по A6 было 2,4,6,8,10,12 и вы ввели формулу = ТЕНДЕНЦИЯ (A1: A6) в другую ячейку, вы получите значение 2, потому что каждое число идет рост на 2.

    ОТДЕЛКА

     = ОБРЕЗАТЬ () 

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

    .

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

Ваш адрес email не будет опубликован.