Как вычислить в excel: Вычисление вложенной формулы по шагам

Содержание

Как посчитать сумму в Экселе

EXCEL предназначена для работы с электронными таблицами, является универсальной программой находящейся в составе пакета Microsoft Office. С помощью Эксель можно организовывать информацию, хранить и анализировать данные. Используется Microsoft Office Excel не только профессиональными специалистами. Она доступна любому желающему для выполнения различных задач. Научившись, как в Эксель посчитать сумму, пользователь сможет использовать программу для ведения домашнего бюджета, производить расчеты (простые, сложные), строить диаграммы, графики.

Как работает Excel ↓

Как считать в Excel ↓

Принцип работы Эксель

Работа Майкрософт Эксель заключается в использовании формул, которые при помощи специального процессора разрешают:

• создавать таблицы разного размера и степени сложности;

• выполнять подсчет значений, расположенных в ячейках.

Большинство пользователей не знают, как в EXCEL посчитать сумму, поэтому мы поэтапно рассмотрим, как это сделать

Подсчет суммы в Excel, этапы

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

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

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

Область, в которой значения будут суммироваться, выделяется, а в ячейке суммы будет виден диапазон ячеек, которые просуммируются. Если это нужный нам диапазон, нажимаем клавишу «Enter», если нет, то мы можем изменять этот диапазон (например В4:В9 можно изменить на В4:В6).

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

Курсор размещаем на ту ячейку, где должна находиться сумма выделенных ячеек. Нажимаем «=» (равно), кликаем на первую, выделенную желтым, ячейку, после чего в ячейке суммы будет сформирована формула. После этого кликаем «+» (плюс), за ним нажимаем на следующую выделенную желтым ячейку, опять «+», после чего жмем на последнюю, выделенную желтым, ячейку. После этого на экране появится:

Теперь жмем «Enter» и перед нами появляется подсчитанная сумма.

Как вычислить СЧЁТ, СУММ, СРЗНАЧ в Excel по цвету ячеек или цвету шрифта – инструкция

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

Инструмент «Счёт по цвету» мгновенно и без VBA считает значения в ячейках, исходя из их цвета:

Вычисление СЧЁТ, СУММ, СРЗНАЧ, МИН, МАКС для каждого цвета
Агрегация по цвету заливки и/или цвету условного форматирования
Cчёт по цвету фона ячеек или цвету шрифта
Предпросмотр и вставка таблицы результата на рабочий лист

Перед началом работы добавьте «Счёт по цвету» в Excel

«Счёт по цвету» – это один из 20+ инструментов в составе надстройки XLTools для Excel. Работает в Excel 2019, 2016, 2013, 2010, десктоп Office 365.

– пробный период дает 14 дней полного доступа ко всем инструментам.

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

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

Нажмите кнопку Счёт по цвету на панели XLTools Укажите диапазон.
Совет: нажмите на любую ячейку таблицы, и она будет выбрана автоматически.
Из выпадающего списка «Учитывать только» выберите, какой тип цветовой расцветки следует учитывать в дальнейших расчётах:
- Заданные цвета – чтобы учитывать только ячейки со сплошной заливкой фона
- Условные цвета – чтобы учитывать только ячейки с условным форматированием
- Все цвета – чтобы учитывать все ячейки, как с заданными, так и условными цветами.
Из следующего выпадающего списка выберите «Вычислять по цвету» Фона.
Посмотрите результаты в сводной таблице результатов агрегатных вычислений: Счёт, Сумм, Срзнач, Мин, Макс – с разбивкой по цвету.
Внимание: надстройка автоматически распознает и произведет расчёт по всем цветам в диапазоне. Ячейки без фона, без заливки и ячейки с белой заливкой обрабатываются вместе. Так, вы сможете сравнить результаты вычислений по цветным и бесцветным ячейкам.
Выберите поместить результаты на новый или существующий лист.
Нажмите OK Готово!

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

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

Нажмите кнопку Счёт по цвету на панели XLTools Укажите диапазон.
Совет: нажмите на любую ячейку таблицы, и она будет выбрана автоматически.
Из выпадающего списка «Учитывать только» выберите, какой тип цветовой расцветки следует учитывать в дальнейших расчётах:
- Заданные цвета – чтобы учитывать только ячейки заданным цветом шрифта
- Все цвета – чтобы учитывать все ячейки, как с заданными, так и условными цветами.
Из следующего выпадающего списка выберите «Вычислять по цвету» Шрифта.
Посмотрите результаты в сводной таблице результатов агрегатных вычислений: Счёт, Сумм, Срзнач, Мин, Макс – с разбивкой по цвету.
Выберите поместить результаты на новый или существующий лист.
Нажмите OK Готово!

Как посчитать значения ячеек на основе цвета условного форматирования

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

Нажмите кнопку Счёт по цвету на панели XLTools Укажите диапазон.
Совет: нажмите на любую ячейку таблицы, и она будет выбрана автоматически.
Из выпадающего списка «Учитывать только» выберите, какой тип цветовой расцветки следует учитывать в дальнейших расчётах:
- Условные цвета – чтобы учитывать только ячейки с условным форматированием
- Все цвета – чтобы учитывать все ячейки, как с заданными, так и условными цветами.
Из следующего выпадающего списка выберите «Вычислять по цвету» Фона или Шрифта, в зависимости от типа условного форматирования.
Посмотрите результаты в сводной таблице результатов агрегатных вычислений: Счёт, Сумм, Срзнач, Мин, Макс – с разбивкой по цвету.
Выберите поместить результаты на новый или существующий лист.
Нажмите OK Готово!

Доступные агрегатные вычисления (Count, Sum, Average, Minimum, Maximum)

Надстройка производит расчёт самых частых агрегатных функций, на основе цвета шрифта или фона ячейки:

СЧЁТ (COUNT) – подсчёт количества всех значений в диапазоне по цвету
СУММ (SUM) – сумма всех значений в диапазоне по цвету
СРЗНАЧ (AVERAGE) – среднее (арифметическое среднее) всех значений в диапазоне по цвету
МИН (MIN) – наименьшее значение в диапазоне по цвету
МАКС (MAX) – наибольшее значение в диапазоне по цвету

Какие ячейки и значения учитываются при вычислениях

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

В расчёт принимаются: числовые значения, а также формулы, функции, ссылки на ячейки, которые возвращают числовое значение.Все пустые ячейки и ячейки, которые содержат текст, даты, ошибки игнорируются.
Надстройка игнорирует скрытые строки или столбцы, т.е. в вычислениях учитываются только видимые ячейки. Если вы хотите провести вычисления по всему диапазону, пожалуйста, отобразите строки/столбцы и очистите фильтры.
Вставленная на лист, сводная таблица с результатами вычислений содержит значения (не формулы и не ссылки).
Объединённые ячейки обрабатываются как одна ячейка.

Видео: базовая математика в Excel 2013

Базовая математика в Excel.

Обучение Эксель 2013.

Базовая математика в Excel.

Базовая математика в Excel 2013

Базовая математика в Excel 2013
видео
Использование функций в Excel 2013
видео
Заказ оператора в Excel 2013
видео
Более сложные формулы в Excel 2013
видео

Основные советы по работе в Excel для Интернета

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

Хотите больше?

Используйте автосумму для суммирования чисел

Суммируйте числа, создав формулу

Пусть Excel будет вашим калькулятором.

С помощью простой формулы Excel может выполнять вычисления с числами с помощью операторов, таких как знак +, и функций, таких как СУММ .

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

Формула всегда начинается со знака =.

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

Таким образом можно добавить много чисел, а не только два.

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

A4 равен 6, A5 равен 3.

Я создам формулу, которая суммирует ячейки.

Как и во всех формулах, я снова начинаю со знака =, щелкаю ячейку, ввожу знак +, затем другую ячейку и нажимаю Enter.

И если я изменяю число в ячейке, результат формулы автоматически обновляется.

Когда вы дважды щелкаете ячейку, вы можете увидеть прямо в ячейке, является ли она формулой, например A6, или числом, например A4, или вы можете щелкнуть ячейку и посмотреть здесь в Панель формул .

Вычитание похоже на сложение, за исключением того, что вы используете знак -.

Щелкните ячейку, затем знак -, затем другую ячейку и нажмите Enter.

Умножение похоже на сложение или вычитание, за исключением того, что вы используете *.

Не используйте X для умножения; это не сработает.

Щелкните ячейку, затем *, затем другую ячейку и нажмите Enter.

Деление похоже на умножение, за исключением того, что вы используете /.

Щелкните ячейку, затем /, затем другую ячейку и нажмите Enter.

Далее, Использование функций .

Как рассчитать проценты в 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 ), чтобы отобразить полученные десятичные дроби в процентах.
Не забудьте увеличить количество знаков после запятой, если это необходимо, как описано в разделе Советы по работе с процентами.
Готово! 🙂

Та же последовательность шагов должна быть выполнена при использовании любой другой процентной формулы в 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

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

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

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

Изменение в процентах = (B — A) / A

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

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

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

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

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

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

=(C2-B2)/B2

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

Введите формулу в любую пустую ячейку строки 2, например D2. Это выведет результат в виде десятичного числа.
Выберите ячейку формулы и нажмите кнопку Percent Style на вкладке Home , чтобы преобразовать десятичное число в проценты.
Перетащите формулу вниз, чтобы скопировать ее в ячейки ниже.

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

Пример 2. Вычисление процентной разницы между двумя числами

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

=(С3-С2)/С2

Где C2 и C3 — числа, которые вы сравниваете.

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

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

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

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

=(C3-$C$2)/$C$2

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

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

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

Как вы только что видели, вычислять проценты в 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.

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

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

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

=А2/В2

или

=А2/0,7

или

=А2/70%

Совет. Чтобы получить ответ на более сложный вопрос — как рассчитать сумму процентов платежа по кредиту, зная процентную ставку — ознакомьтесь с функцией ИПМТ.

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

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

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

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

Например, чтобы увеличить значение в ячейке A1 на 20%, используйте следующую формулу:

=А1*(1+20%)

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

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

Например, чтобы уменьшить значение в ячейке A1 на 20%, используйте следующую формулу:

=А1*(1-20%)

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

Увеличение в процентах:

=А2*(1+В2)

Уменьшить на процент:

=А2*(1-В2)

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

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

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

Введите все числа, которые вы хотите увеличить или уменьшить, в какой-либо столбец, столбец B в этом примере.
В пустую ячейку введите одну из следующих формул:
Увеличение в процентах:
=1+20%
Уменьшить на процент:
=1-20%
Естественно, вы можете заменить 20% в приведенных выше формулах на желаемый процент.
Выберите ячейку с формулой (в нашем случае C2) и скопируйте ее, нажав Ctrl + C.
Выберите диапазон ячеек, которые вы хотите изменить, щелкните правой кнопкой мыши выделение и выберите Специальная вставка…
В диалоговом окне Специальная вставка выберите Значения под Вставить , Умножить под Операция и нажмите OK .

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

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