Суммировать в программе Excel умеет, наверное, каждый. Но с усовершенствованной версией команды СУММ, которая называется СУММЕСЛИ, существенно расширяются возможности данной операции.
По названию команды можно понять, что она не просто считает сумму, но еще и подчиняется каким-либо логическим условиям.
СУММЕСЛИ и ее синтаксис
Функция СУММЕСЛИ позволяет суммировать ячейки, которые удовлетворяют определенному критерию (заданному условию). Аргументы команды следующие:
- Диапазон – ячейки, которые следует оценить на основании критерия (заданного условия).
- Критерий – определяет, какие ячейки из диапазона будут выбраны (записывается в кавычках).
- Диапазон суммирования – фактические ячейки, которые необходимо просуммировать, если они удовлетворяют критерию.
Получается, что у функции всего 3 аргумента. Но иногда последний может быть исключен, и тогда команда будет работать только по диапазону и критерию.
Как работает функция СУММЕСЛИ в Excel?
Рассмотрим простейший пример, который наглядно продемонстрирует, как использовать функцию СУММЕСЛИ и насколько удобной она может оказаться при решении определенных задач.
Курс Google таблицы. Урок №6. Как использовать функции СУММЕСЛИ и СУММЕСЛИМН.
Имеем таблицу, в которой указаны фамилии сотрудников, их пол и зарплата, начисленная за январь-месяц. Если нам нужно просто посчитать общее количество денег, которые требуется выдать работникам, мы используем функцию СУММ, указав диапазоном все заработные платы.
Но как быть, если нам нужно быстро посчитать заработные платы только продавцов? В дело вступает использование функции СУММЕСЛИ.
- Диапазоном в данном случае будет являться список всех должностей сотрудников, потому что нам нужно будет определить сумму заработных плат. Поэтому проставляем E2:E14.
- Критерий выбора в нашем случае – продавец. Заключаем слово в кавычки и ставим вторым аргументом.
- Диапазон суммирования – это заработные платы, потому что нам нужно узнать сумму зарплат всех продавцов. Поэтому F2:F14.
Получилось 92900. Т.е. функция автоматически проработала список должностей, выбрала из них только продавцов и просуммировала их зарплаты.
Аналогично можно подсчитать зарплаты всех менеджеров, продавцов-кассиров и охранников. Когда табличка небольшая, кажется, что все можно сосчитать и вручную, но при работе со списками, в которых по несколько сотен позиций, целесообразно использовать СУММЕСЛИ.
Функция СУММЕСЛИ в Excel с несколькими условиями
Если к стандартной записи команды СУММЕСЛИ в конце добавляются еще две буквы – МН (СУММЕСЛИМН), значит, подразумевается функция с несколькими условиями. Она применяется в случае, когда нужно задать не один критерий.
Синтаксис с использованием функции по нескольким критериям
Аргументов у СУММЕСЛИМН может быть сколько угодно, но минимум – это 5.
- Диапазон суммирования. Если в СУММЕСЛИ он был в конце, то здесь он стоит на первом месте. Он также означает ячейки, которые необходимо просуммировать.
- Диапазон условия 1 – ячейки, которые нужно оценить на основании первого критерия.
- Условие 1 – определяет ячейки, которые функция выделит из первого диапазона условия.
- Диапазон условия 2 – ячейки, которые следует оценить на основании второго критерия.
- Условие 2 – определяет ячейки, которые функция выделит из второго диапазона условия.
И так далее. В зависимости от количества критериев, число аргументов может увеличиваться в арифметической прогрессии с шагом 2. Т.е. 5, 7, 9.
Пример использования
Предположим, нам нужно подсчитать сумму заработных плат за январь всех продавцов-женщин. У нас есть два условия. Сотрудник должен быть:
Значит, будем применять команду СУММЕСЛИМН.
- диапазон суммирования – ячейки с зарплатой;
- диапазон условия 1 – ячейки с указанием должности сотрудника;
- условия 1 – продавец;
- диапазон условия 2 – ячейки с указанием пола сотрудника;
- условие 2 – женский (ж).
Итог: все продавцы-женщины в январе получили в сумме 51100 рублей.
СУММЕСЛИ в Excel с динамическим условием
Функции СУММЕСЛИ и СУММЕСЛИМН хороши тем, что они автоматически подстраиваются под изменение условий. Т.е. мы можем изменить данные в ячейках, и суммы будут изменяться вместе с ними. Например, при подсчете заработных плат оказалось, что мы забыли учесть одну сотрудницу, которая работает продавцом. Мы можем добавить еще одну строчку через правую кнопку мыши и команду ВСТАВИТЬ.
У нас появилась дополнительная строчка. Сразу обращаем внимание, что диапазон условий и суммирования автоматически расширился до 15 строки.
Копируем данные сотрудника и вставляем их в общий перечень. Суммы в итоговых ячейках изменились. Функции среагировали на появление в диапазоне еще одного продавца-женщины.
Аналогично можно не только добавлять, но и удалять какие-либо строки (например, при увольнении сотрудника), изменять значения (заменить «январь» на «февраль» и подставить новые заработные платы) и т.п.
- Создать таблицу
- Форматирование
- Функции Excel
- Формулы и диапазоны
- Фильтр и сортировка
- Диаграммы и графики
- Сводные таблицы
- Печать документов
- Базы данных и XML
- Возможности Excel
- Настройки параметры
- Уроки Excel
- Карта сайта
- Скачать примеры
Источник: exceltable.com
click fraud protection
Если вы регулярно используете Google Таблицы и когда-либо вам нужно суммировать значения на основе какого-либо условия в определенных ячейках, вам нужно знать, как использовать функцию СУММЕСЛИ в Google Таблицы .
Возможность суммирования данных с помощью этой функции не ограничивается двумя значениями. Вы можете просуммировать весь диапазон. И условие, которое вы предоставляете функции для суммирования или нет, также может зависеть от нескольких ячеек в вашей электронной таблице.
Как работает функция СУММЕСЛИ в Google Таблицах
СУММЕСЛИ — это простая функция электронной таблицы , но он достаточно гибкий, чтобы с ним можно было проводить творческие расчеты.
Вам нужно создать функцию следующим образом:
СУММЕСЛИ (диапазон; критерий; [диапазон_суммы])
Параметры этой функции следующие:
- Диапазон: Диапазон ячеек, который вы хотите использовать, чтобы оценить, нужно ли складывать значения.
- Критерий: Состояние ячеек, которое вы хотите оценить.
- Sum_range: Этот параметр является необязательным и включает ячейки, которые вы хотите суммировать. Если вы не включите этот параметр, функция просто суммирует сам диапазон.
Функция кажется простой, но тот факт, что вы можете суммировать или сравнивать диапазоны нескольких ячеек, обеспечивает гораздо большую гибкость, чем вы можете себе представить.
Пример СУММЕСЛИ с текстом
Если вы готовы начать работу с функцией СУММЕСЛИ, лучше всего использовать в таблице два столбца. Один столбец будет для вашего сравнения, а другой — для значений, которые вы хотите добавить.
В приведенном выше примере таблицы показан владелец магазина, отслеживающий покупки в течение определенного периода. Владелец магазина хочет создать дополнительные столбцы, в которых суммируются закупочные цены в столбце B для конкретных значений в столбце A.
В этом случае диапазон для сравнения будет A2: A15.
Критерием будет поисковая фраза для суммирования элемента. Итак, в этом случае, чтобы суммировать все покупки почтовых копателей, критерием будет текст «Почтовый копатель».
Суммарный диапазон — это диапазон ячеек со значениями, которые нужно суммировать. Вот это B2: B15.
Когда вы нажмете клавишу ввода, вы увидите, что определенные значения из столбца sum_range суммируются, но только с данные из ячеек, в которых столбец A соответствует указанному критерию.
Это простой способ использования функции СУММЕСЛИ; как способ извлечь значения из второго столбца на основе элементов, перечисленных в первом.
Примечание: Вам не нужно вводить критерий в формулу в двойных кавычках. Вместо этого вы можете ввести это значение в ячейку на листе и ввести эту ячейку в формулу.
Использование операторов СУММЕСЛИ с текстом
Хотя приведенный выше пример ищет идеальные совпадения, вы также можете использовать операторы, чтобы указать части текста, которые вы хотите сопоставить. Если вы измените критерии поиска, вы можете суммировать значения для ячеек, которые могут не совпадать полностью, но дать вам ответ, который вы ищете.
Используя приведенный выше пример, если вы хотите сложить покупки всех товаров Кроме электродрели, введите формулу <> оператор.
= СУММЕСЛИ (A2: A15; «<> Электродрель»; B2: B15)
Оператор <> указывает функции СУММЕСЛИ игнорировать «Электродрель», но складывать все остальные элементы в диапазоне B2: B15.
Как видно из результата ниже, функция СУММЕСЛИ работает должным образом.
Вы также можете использовать следующие операторы при использовании функции СУММЕСЛИ с текстом:
- ?: Поиск слов с любым символом там, где вы поместили?. Например? ovel »суммирует любой элемент, начинающийся с« S »и заканчивающийся« ovel »с любой буквой между ними.
- *: Поиск слов, которые начинаются или заканчиваются чем угодно. Например, «* Watch» добавит все предметы, относящиеся к любым типам часов, независимо от марки.
Примечание: Если вы действительно хотите, чтобы функция СУММЕСЛИ выполняла поиск такого символа, как «?» или «*» в тексте (и не используйте их как специальные символы), а затем ставьте перед ними символ тильды. Например, «~?» будет включать «?» символ в поисковом тексте.
Имейте в виду, что функция СУММЕСЛИ не чувствительна к регистру. Таким образом, он не делает различий между заглавными и строчными буквами, когда вы используете текст в качестве критерия поиска. Это полезно, потому что если одно и то же слово вводится с заглавными буквами или без них, функция СУММЕСЛИ по-прежнему распознает их как совпадения и правильно суммирует значения в столбце значений.
Использование операторов СУММЕСЛИ с числами
Конечно, функция СУММЕСЛИ в Google Таблицах полезна не только для поиска текста в столбцах со связанными значениями для суммирования. Вы также можете суммировать диапазоны чисел, которые соответствуют определенным условиям.
Чтобы проверить диапазон чисел для условия, вы можете использовать набор операторов сравнения.
Например, если у вас есть список чисел и вы хотите добавить их более 3000, вы можете использовать следующую команду СУММЕСЛИ.
= СУММЕСЛИ (B2: B15; «> 3000»)
Обратите внимание, что, как и в случае с текстовыми критериями, вам не нужно напрямую вводить число «3000» в формулу. Вы можете поместить это число в ячейку и использовать ссылку на эту ячейку вместо «3000» в формуле.
= СУММЕСЛИ (B2: B15; «>» «> 04.10.2019»; C2: C15)
Как это работает:
- СУММЕСЛИ проверит диапазон B2: B15 для любых дат после 4.10.2019.
- Если это правда, СУММЕСЛИ суммирует все ячейки в C2: C15 в той же строке, где это сравнение верно.
- Итоговая сумма будет отображаться в ячейке, в которую вы ввели формулу.
Если у вас есть ячейка, в которой дата не отформатирована таким образом, вы можете использовать функцию ДАТА, чтобы правильно отформатировать дату. Например, если у вас есть три ячейки (D2, D3 и D4), содержащие год, месяц и день, вы можете использовать следующую формулу.
= СУММЕСЛИ (B2: B15; «>» D3; D4); C2: C15)
Если у вас есть электронная таблица, которая содержит последние покупки в верхней части таблицы, вы можете просто использовать функцию СЕГОДНЯ, чтобы суммировать только сегодняшние покупки и игнорировать остальные.
= СУММЕСЛИ (B2: B15; СЕГОДНЯ ())
СУММЕСЛИ в Google Таблицах — это просто, но универсально
Как видите, формула СУММЕСЛИ в Google Таблицах не требует много времени, чтобы усвоить ее. Но различные способы его использования делают его таким универсальным.
Если вы используете много таблиц, где вам нужно суммировать значения на основе условий текста или чисел из других ячеек, вам следует ознакомиться с функцией СУММЕСЛИ.
Источник: ciksiti.com
Функции суммирования в Google Таблицах
Представляем цикл видео из вебинара по Таблицам Google.
В этом видео-уроке мы рассказали о функциях суммирования:
Функция СУММ
Функция СУММЕСЛИ
Функция СУММЕСЛИМН
Расписание ближайших групп:
Трюки со ссылками в Google Документах
Выделение сегментов круговых диаграмм в Google Таблицах
Установка обновлений Google Drive
Следите за нами:
Расписание курсов
- Курсы Google
- Google Документы
- Google Таблицы
- Google Презентации
- Google Формы
- Google Apps Script
- Word
- Excel
- PowerPoint
- Outlook
- Текстовые документы Pages
- Таблицы Numbers
- Презентации Keynote
Сайт использует «cookie» (данные о прошлых посещениях сайта) для персонализации сервисов и повышения удобства пользования веб-сайтом. Если вы хотите отказаться от использования «cookie», покиньте сайт или измените настройки браузера. Оставаясь на сайте, вы даете согласие на обработку cookie и персональных данных. Подробнее здесь. Согласен
Источник: sreda31.com