Суммирование значений с учетом нескольких условий
Предположим, что вам нужно свести значения с более чем одним условием, например суммой продаж продуктов в определенном регионе. Это хороший случай для использования функции СУММЕСС в формуле.
Взгляните на этот пример, в котором есть два условия: мы хотим получить сумму продаж «Мясо» (из столбца C) в регионе «Южный» (из столбца A).
Вот формула, с помощью которая можно сопровождать эту формулу:
=СУММЕСС(D2:D11,A2:A11,»Южный»,C2:C11,»Мясо»)
Результат — значение 14 719.
Рассмотрим каждую часть формулы более подробно.
=СУММЕСЛИМН — это арифметическая формула. Она вычисляет числа, которые в этом случае находятся в столбце D. Прежде всего нужно указать расположение чисел.
=СУММЕСЛИМН(D2:D11,
Другими словами, вы хотите, чтобы формула суммировала числа в этом столбце, если они соответствуют определенным условиям. Это диапазон ячеок является первым аргументом в этой формуле — первым элементом данных, который требуется функции в качестве входных данных.
Затем вам нужно найти данные, отвечающие двум условиям, поэтому введите первое условие, указав для функции расположение данных (A2:A11) и условие («Южный»). Обратите внимание на запятую между аргументами:
=СУММЕСЛИМН(D2:D11;A2:A11;»Южный»;
Кавычка вокруг текста «Южный» указывает на то, что это текстовые данные.
Наконец, вы вводите аргументы для второго условия — диапазон ячеек (C2:C11), которые содержат слово «Мясо», а также само слово (заключенное в кавычки), чтобы приложение Excel смогло их сопоставить. В конце формулы введите закрываю скобки) и нажмите ввод. Результат — 14 719.
=СУММЕСЛИМН(D2:D11;A2:A11,»Южный»;C2:C11,»Мясо»)
Если вы ввели в Excel функцию СУММЕСС, если вы не помните аргументов, справка готова. После того как вы введете =СУММЕСС(, под формулой появится автозавершенная формула со списком аргументов в правильном порядке.
На изображении автозавершена формулы и списке аргументов в нашем примере sum_range — D2:D11, столбец чисел, которые нужно свести; criteria_range1 — A2. A11 — столбец данных, в котором находится «Южный» (критерий1).
По мере того, как вы вводите формулу, в автозавершении формулы появятся остальные аргументы (здесь они не показаны); диапазон_условия2 — это диапазон C2:C11, представляющий собой столбец с данными, в котором находится условие2 — “Мясо”.
Если вы нажмете кнопку СУММЕСС в автозавершении формул, откроется статья с дополнительной справкой.
Попробуйте попрактиковаться
Если вы хотите поэкспериментировать с функцией СУММЕСС, вот примеры данных и формула, в которую она используется.
Вы можете работать с образцами данных и формулами прямо в этой Excel в Интернете книге. Изменяйте значения и формулы или добавляйте свои собственные, чтобы увидеть, как мгновенно изменятся результаты.
Скопируйте все ячейки из приведенной ниже таблицы и вставьте их в ячейку A1 нового листа Excel. Вы можете отрегулировать ширину столбцов, чтобы формулы лучше отображались.
Что следует ввести
Использование функции СУММ для суммирования чисел в диапазоне
Для суммы чисел в диапазоне (группе ячеек) можно использовать простую формулу, но функцию СУММ проще использовать при работе с несколькими числами. Например, =СУММ(A2:A6) реже имеет ошибки при вводе, чем =A2+A3+A4+A5+A6.
Вот формула, использующая два диапазонаячеев: =СУММ(A2:A4;C2:C3) суммирует числа в диапазонах A2:A4 и C2:C3. Чтобы получить итоговую сумму в 39787, нажмите ввод.
Чтобы создать формулу:
- Введите в ячейку =СУММ и открываю скобки (.
- Чтобы ввести первый диапазон формул, который называется аргументом (частью данных, которую нужно выполнить), введите A2:A4 (или выберите ячейку A2 и перетащите ее через ячейку A6).
- Введите запятую (,), чтобы отделить первый аргумент от следующего.
- Введите второй аргумент C2:C3 (или перетащите его, чтобы выбрать ячейки).
- Введите закрываюю скобки )и нажмите ввод.
Каждый аргумент может быть диапазоном, числом или ссылками на отдельные ячейки, разделенными запятой.
- =СУММ(A2:A4;2429;10482)
- =СУММ(4823;A3:A4;C2:C3)
- =СУММ(4823;12335;9718;C2:C3)
- =СУММ(A2;A3;A4;2429;10482)
Совет: Если вам нужно свести столбцы или строки чисел рядом друг с другом, используйте авто сумму чисел.
Попробуйте попрактиковаться
Если вы хотите поиграть с образцом данных, воспользуйтесь некоторыми данными.
Чтобы узнать, как работает функция СУММ, скопируйте таблицу ниже в таблицу и вкопируйте ее в ячейку A1.
Использование функции «Автосумма» для суммирования чисел
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel для iPad Excel для iPhone Excel для планшетов с Android Excel 2010 Excel 2007 Excel для телефонов с Android Еще. Меньше
Если вам нужно свести числа в столбце или строке, позвольте Excel математические вычисления за вас. Выберем ячейку рядом с числами, которые вы хотите свести, нажмите кнопку Авто сумма на вкладке Главная и нажмите ввод , и все готово.
Когда вы нажимаете кнопку Автосумма, Excel автоматически вводит формулу для суммирования чисел (в которой используется функция СУММ).
Приведем пример. Чтобы добавить числа за январь в бюджете «Развлечения», выберите ячейку B7, которая непосредственно под столбцом чисел. Затем нажмите кнопку Автоумма. В ячейке B7 появится формула, Excel выделит сумму ячеек.
Чтобы отобразить результат (95,94) в ячейке В7, нажмите клавишу ВВОД. Формула также отображается в строке формул вверху окна Excel.
- Чтобы сложить числа в столбце, выберите ячейку под последним числом в столбце. Чтобы сложить числа в строке, выберите первую ячейку справа.
- Автосема находится в двух местах: главная > и Формула >Автосема.
- Создав формулу один раз, ее можно копировать в другие ячейки, а не вводить снова и снова. Например, при копировании формулы из ячейки B7 в ячейку C7 формула в ячейке C7 автоматически настроится под новое расположение и подсчитает числа в ячейках C3:C6.
- Кроме того, вы можете использовать функцию «Автосумма» сразу для нескольких ячеек. Например, можно выделить ячейки B7 и C7, нажать кнопку Автосумма и суммировать два столбца одновременно.
- Также вы можете суммировать числа путем создания простых формул.
Если вам нужно свести числа в столбце или строке, позвольте Excel математические вычисления за вас. Выберем ячейку рядом с числами, которые вы хотите свести, нажмите кнопку Авто сумма на вкладке Главная и нажмите ввод , и все готово.
Когда вы нажимаете кнопку Автосумма, Excel автоматически вводит формулу для суммирования чисел (в которой используется функция СУММ).
Приведем пример. Чтобы добавить числа за январь в бюджете «Развлечения», выберите ячейку B7, которая непосредственно под столбцом чисел. Затем нажмите кнопку Автоумма. В ячейке B7 появится формула, Excel выделит сумму ячеек.
Чтобы отобразить результат (95,94) в ячейке В7, нажмите клавишу ВВОД. Формула также отображается в строке формул вверху окна Excel.
- Чтобы сложить числа в столбце, выберите ячейку под последним числом в столбце. Чтобы сложить числа в строке, выберите первую ячейку справа.
- Автосема находится в двух местах: главная > и Формула >Автосема.
- Создав формулу один раз, ее можно копировать в другие ячейки, а не вводить снова и снова. Например, при копировании формулы из ячейки B7 в ячейку C7 формула в ячейке C7 автоматически настроится под новое расположение и подсчитает числа в ячейках C3:C6.
- Кроме того, вы можете использовать функцию «Автосумма» сразу для нескольких ячеек. Например, можно выделить ячейки B7 и C7, нажать кнопку Автосумма и суммировать два столбца одновременно.
- Также вы можете суммировать числа путем создания простых формул.
На планшете или телефоне с Android
- На листе коснитесь первой пустой ячейки после диапазона ячеек с числами или выделите необходимый диапазон ячеек касанием и перемещением пальца.
Коснитесь элемента Автосумма.
Нажмите Сумма.
Коснитесь флажка.
Готово!
Если вам нужно свести числа в столбце или строке, позвольте Excel математические вычисления за вас. Выберем ячейку рядом с числами, которые вы хотите свести, нажмите кнопку Авто сумма на вкладке Главная и нажмите ввод , и все готово.
Когда вы нажимаете кнопку Автосумма, Excel автоматически вводит формулу для суммирования чисел (в которой используется функция СУММ).
Приведем пример. Чтобы добавить числа за январь в бюджете «Развлечения», выберите ячейку B7, которая непосредственно под столбцом чисел. Затем нажмите кнопку Автоумма. В ячейке B7 появится формула, Excel выделит сумму ячеек.
Чтобы отобразить результат (95,94) в ячейке В7, нажмите клавишу ВВОД. Формула также отображается в строке формул вверху окна Excel.
- Чтобы сложить числа в столбце, выберите ячейку под последним числом в столбце. Чтобы сложить числа в строке, выберите первую ячейку справа.
- Автосема находится в двух местах: главная > и Формула >Автосема.
- Создав формулу один раз, ее можно копировать в другие ячейки, а не вводить снова и снова. Например, при копировании формулы из ячейки B7 в ячейку C7 формула в ячейке C7 автоматически настроится под новое расположение и подсчитает числа в ячейках C3:C6.
- Кроме того, вы можете использовать функцию «Автосумма» сразу для нескольких ячеек. Например, можно выделить ячейки B7 и C7, нажать кнопку Автосумма и суммировать два столбца одновременно.
- Также вы можете суммировать числа путем создания простых формул.
Дополнительные сведения
Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.
Функция СУММЕСЛИ
Excel для Microsoft 365 Excel для Microsoft 365 для Mac Excel для Интернета Excel 2021 Excel 2021 для Mac Excel 2019 Excel 2019 для Mac Excel 2016 Excel 2016 для Mac Excel 2013 Excel 2010 Excel 2007 Excel для Mac 2011 Excel Starter 2010 Еще. Меньше
Функция СУММЕСЛИ используется, если необходимо просуммировать значения диапазон, соответствующие указанному критерию. Предположим, например, что в столбце с числами необходимо просуммировать только значения, превышающие 5. Для этого можно использовать следующую формулу: =СУММЕСЛИ(B2:B25;»> 5″)
Это видео — часть учебного курса Сложение чисел в Excel.
- При необходимости условия можно применить к одному диапазону, а просуммировать соответствующие значения из другого диапазона. Например, формула =СУММЕСЛИ(B2:B5; «Иван»; C2:C5) суммирует только те значения из диапазона C2:C5, для которых соответствующие значения из диапазона B2:B5 равны «Иван».
- Если необходимо выполнить суммирование ячеек в соответствии с несколькими условиями, используйте функцию СУММЕСЛИМН.
Важно: Функция СУММЕСЛИ возвращает неверные результаты при использовании для сопоставления строк длиной более 255 символов или строкового #VALUE!.
Синтаксис
СУММЕСЛИ(диапазон; условие; [диапазон_суммирования])
Аргументы функции СУММЕСЛИ описаны ниже.
- Диапазон — обязательный аргумент. Диапазон ячеек, оцениваемых на соответствие условиям. Ячейки в каждом диапазоне должны содержать числа, имена, массивы или ссылки на числа. Пустые и текстовые значения игнорируются. Выбранный диапазон может содержать даты в стандартном формате Excel (см. примеры ниже).
- Условие .Обязательный аргумент. Условие в форме числа, выражения, ссылки на ячейку, текста или функции, определяющее, какие ячейки необходимо суммировать. Можно включить подстановочные знаки : вопросительный знак (?) для сопоставления с любым одним символом, звездочка (*) для соответствия любой последовательности символов. Если требуется найти непосредственно вопросительный знак (или звездочку), необходимо поставить перед ним знак «тильда» (~). Например, критерии можно выразить как 32, «>32», B5, «3?», «apple*», «*~?» или TODAY().
Важно: Все текстовые условия и условия с логическими и математическими знаками необходимо заключать в двойные кавычки («). Если условием является число, использовать кавычки не требуется.
| диапазон | Диапазон_суммирования. | Фактические суммированные ячейки |
|---|---|---|
| A1:A5 | B1:B5 | B1:B5 |
| A1:A5 | B1:K5 | B1:B5 |
Примеры
Пример 1
Скопируйте образец данных из следующей таблицы и вставьте их в ячейку A1 нового листа Excel. Чтобы отобразить результаты формул, выделите их и нажмите клавишу F2, а затем — клавишу Enter. При необходимости измените ширину столбцов, чтобы видеть все данные.
Стоимость имущества