Функции СУММ и ЕСЛИ являются, пожалуй, одними из самых востребованных среди начинающих пользователей Эксель. Но по мере того, как усложняются задачи, возникает потребность в более гибких инструментах и в этой статье речь пойдет о своеобразной комбинации этих функций, а именно о функции СУММЕСЛИ и ее более могущественном варианте - СУММЕСЛИМН.
Давайте на примерах разберем работу с этими функциями.
СУММЕСЛИ в сокращенном варианте
Довольно часто возникает необходимость просуммировать не все ячейки какого-то столбца, а лишь ячейки, удовлетворяющие определенному условию.
Рассмотрим упрощенную таблицу с данными и первой задачей будет определить сумму заказов, которые превышали 50 тыс. рублей.
Для этих целей идеально подходит функция СУММЕСЛИ.
У функции СУММЕСЛИ три аргумента.
Первый аргумент обязательный и он задает диапазон ячеек, по которому будет производиться поиск на соответствие заданному условию. В рассматриваемом примере это столбец «Стоимость заказа».
Второй аргумент функции также является обязательным, и он определяет условие суммирования. В рассматриваемом примере мы должны просуммировать значения, которые больше или равны 50000.
Третий аргумент функции необязательный и для решения нашей задачи он не нужен, поэтому пока его задействовать не будем.
Обращаю внимание на то, что знак "больше или равно" является текстом, а значит все условие должно быть помещено в двойные кавычки.
Задача решена.
СУММЕСЛИ в полном варианте
Теперь решим вторую задачу - необходимо определить общую сумму заказов от конкретного покупателя (Магнит).
Снова задействуем функцию СУММЕСЛИ. Напомню, что первым аргументом является диапазон, значения которого проверяются на соответствие условию. Так как нам нужно найти все заказы Магнита, то выбираем весь диапазон столбца Покупатель.
В этом диапазоне мы должны найти ячейки, в которых находится текстовое значение «Магнит». Не забываем заключить текст в двойные кавычки.
И теперь задействуем третий аргумент, ведь нам нужно просуммировать значения столбца «Стоимость заказа», поэтому выбираем соответствующий диапазон.
То есть функция пройдет весь диапазон первого столбца в поисках слова «Магнит». Как только слово будет найдено, функция переместиться в столбец «Стоимость заказа» этой же строки и запомнит значение соответствующей ячейки. Затем функция перейдет к следующей строке со словом «Магнит» и прибавит значение из той же строки столбца «Стоимость заказа» к уже сохраненному ранее. И так до конца диапазона. В результате мы получим сумму только тех значений, которые удовлетворяют условию (заданному во втором аргументе функции).
При создании формулы необязательно условие прописывать явно, а можно сослаться на определенную ячейку, содержащую подходящее значение. Например, в рассматриваемом примере можем сослаться на любую ячейку основной таблицы, которая содержит наименование «Магнит».
Функция СУММЕСЛИМН
Ну а если условий несколько, то стоит задействовать функцию СУММЕСЛИМН. Логика ее работы точно такая же, просто условий и диапазонов, в которых они должны проверяться, может быть несколько, а если точно, то функция способна обработать до 127 условий.
Например, в этой же таблице найдем сумму заказов Магнита превышающих 50 000 рублей.
Сначала указываем диапазон, в котором будем суммировать значения - это столбец «Стоимость заказа». Затем задаем первый диапазон, который будем проверять на выполнение первого условия - это диапазон с наименованием покупателя (столбец "Покупатель"). Условием будет являться название -«Магнит». Вторым условием будет превышение суммы заказа 50 000 рублей, то есть снова выбираем диапазон значений столбца «Сумма заказа» и вводим условие, не забывая включить его в двойные кавычки.
Итак, это были азы использования функций СУММЕСЛИ и СУММЕСЛИМН, а теперь давайте перейдем к рассмотрению более сложных задач.
Пример 1. Символы подстановки
Есть некоторая рабочая таблица с товарами и необходимо получить отчет о продажах электроинструмента определенного бренда.
Обратите внимание, что в этой таблице есть столбец с торговой маркой. Его задействуем для проверки полученных значений, но при решении задачи на него опираться не будем .
Удобнее было бы преобразовать диапазон с данными в умную таблицу, но не будем этого делать, чтобы в создаваемых формулах фигурировали обычные ссылки на диапазоны.
Итак, давайте определим, на какую сумму были проданы товары торговой марки «Зубр».
Если воспользоваться столбцом «Торговая марка», то формула была бы простой - указываем диапазон столбца «Торговая марка», выбираем ячейку с интересующим нас названием и затем выбираем значения столбца с суммами продаж.
Но если такого столбца нет, то задача становится чуть сложнее.
Бренд указывается в наименовании товара и нам просто нужно его правильным образом «вытащить». Помогут в этом специальные символы подставочки. Чаще всего используется символ * (звездочка). Он подменяет собой любое количество любых других символов. В ряде ситуаций можно задействовать и символ вопросительного знака (?), который подменяет только один любой символ.
У нас марка товара находится в тексте, поэтому можем просто «обернуть» бренд звездочками. И тут есть нюанс. Если мы указываем название бренда текстом, то достаточно в тех же кавычках поставить звездочки.
Заканчиваем формулу и получаем тот же результат, что и раньше.
Также условие мы можем задать ссылкой на ячейку, в которой находится интересующее нас значение. В этом случае нужно «объединить» звездочки со ссылкой в одну текстовую строку. Делается это либо функцией СЦЕПИТЬ, либо проще - с помощью символа амперсанд.
Получим точно такой же результат, что и раньше.
И тут хочу обратить ваше внимание на особенность функций СУММЕСЛИ и СУММЕСЛИМН - они нечувствительны к регистру символов. Обратите внимание на то, что в списке товаров бренд «Зубр» написан по-разному - с заглавной буквы, строчными и прописными, но на расчет это никак не повлияло, и функция вернула сумму всех искомых товаров.
Пример 2. Аналог сводной таблицы
Следующая задача, которую мы рассмотрим, будет заключаться в создании аналога сводной таблицы. При решении конкретно этой задачи я бы предпочел создать именно сводную с помощью стандартных инструментов Excel, но в некоторых ситуациях это может быть неудобным, поэтому прибегнем к формулам.
Итак, есть перечень заказов и необходимо составить таблицу, в которой подсчитать общую сумму заказов разных покупателей по городам. Условия будет два - покупатель и город, поэтому применяем функцию СУММЕСЛИМН.
В первой пустой ячейке (I3) будущей сводной вводим формулу.
Первым аргументом указываем столбец для суммирования - «Стоимость заказа» (E2:E23). Затем создаем первое условие - в столбце «Покупатель» (B2:B23) ищем «Магнит» (H3). Второе условие - в столбце «Город» (A2:A23) ищем «Москву» (I2).
Формула готова, но протянуть ее на всю таблицу не получится, так как ссылки в ней относительные (видео об относительных и абсолютных ссылках). Нам нужно будет протягивать формулу вниз и вправо, при этом важно, чтобы не изменялись диапазоны суммирования и поиска в формуле, поэтому их фиксируем полностью, а вот ссылка в первом столбце должна изменяться только по вертикали (фиксируем столбец). Ссылка в заголовке с городами должна изменяться только по горизонтали (фиксируем строку).
Теперь можем растянуть формулу на диапазон и сверится с ранее построенной сводной. Результат тот же.
Но еще раз хочу подчеркнуть, что при создании таких вот сводных на основе больших таблиц можно столкнуться с проблемой долгого пересчета. И тогда правильнее будет обратиться к стандартному инструментарию Эксель и создать классическую сводную таблицу.
Пример 3. Нестандартные таблицы
Еще одна задача, в которой функция СУММЕСЛИ будет максимально полезной — это обработка нестандартных таблиц.
Кстати, периодически вижу комментарии о том, что во всех видео рассматриваются исключительно финансовые задачи и никто не обращает внимание на задачи технического характера, где речь идет не о деньгах. Тут хочу заметить, что Эксель работает с цифрами и ему абсолютно все равно обозначают ли эти цифры рубли, тонны или джоули. Просто так повелось, что в мире финансов Excel получил максимально широкое распространение, но все принципы работы сохраняются при обработки любых данных.
Вот не финансовый пример - нужно посчитать общий объем щебня, израсходованного на всех объектах.
Задействуем функцию СУММЕСЛИ. Ищем в столбце «Материал» нужный нам и подсчитываем соответствующее значение из столбца «Расход».
Ну и давайте рассмотрим еще две задачи, в которых задействуем даты.
Пример 4. Суммирование данных за месяц
Первая задача будет состоять в том, чтобы посчитать общий объем заказов определенного покупателя за определенный месяц. Обычно в исходных данных фигурируют конкретные даты, но нам нужны именно данные за месяц.
Классические сводные таблицы с этой задачей легко справляются, а мы ее решим с помощью функции СУММЕСЛИМН. Но для этого потребуется создать вспомогательный столбец, ведь в условии нам нужно сравнивать указанный месяц с данными в диапазоне, а там находятся обычные даты.
Во вспомогательном столбце с помощью функции ТЕКСТ и соответствующего формата «ММММ» получим название месяца.
Ну а теперь самым обычным образом создадим формулу с функцией СУММЕСЛИМН и двумя условиями.
Пример 5. Суммирование между датами
Ну и последняя задача, которую мы рассмотрим — это получение данных за некоторый временной промежуток. Например, из ранее рассмотренной таблицы нужно получить объемы продаж с 15 января по 15 февраля. Условия два, поэтому снова задействуем функцию СУММЕСЛИМН.
Сначала указываем столбец для суммирования ("Стоимость заказа"), затем диапазон с датами ("Дата оплаты"), где проверяем первое условие - дата должна быть больше или равна 15 января (значение из ячейки H11). Так как мы ссылаемся на ячейку со значением, не забываем с помощью амперсанда сцепить знак «больше или равно» со ссылкой на ячейку. При этом сам знак является текстовым значением, поэтому должен быть в двойных кавычках. По аналогии задаем второе условие.
Потенциальные проблемы
И хочу обратить ваше внимание на потенциальные проблемы, с которыми вы можете столкнуться при использовании функций СУММЕСЛИ и СУММЕСЛИМН.
При выборе диапазонов нужно быть очень внимательным, так как очень легко ошибиться и получить неверный результат. Диапазоны суммирования и условий должны быть равны.
Продемонстрирую на примере.
Повторим формулу, которую мы рассматривали в самом начале, но при выборе диапазона для суммирования случайно щелкнем по табулятору столбца.
В результате в формулу будут подставлены все значения этого столбца E. Даже если ниже таблицы нет данных, то формула все равно отработает некорректно, ведь заголовок столбца теперь является частью данных и участвует в вычислениях. Получается, что результирующий диапазон смещен на одну ячейку вверх.
Итог - ошибка вычисления, которую очень сложно найти, ведь Эксель никак вас о ней не предупредит, а просто выведет неверный результат.
По этой причине не стоит забывать о сводных таблицах. В ряде ситуаций их применение намного более оправдано, нежели создание сложных условий с помощью функций СУММЕСЛИ и СУММЕСЛИМН.
_______________________________
Ссылки на мои ресурсы по Excel
★ YouTube-канал по Excel и Word
★ Телеграм