Формула СУММЕСЛИМН в Excel: примеры на закупке глины
Формула СУММЕСЛИМН в Excel: примеры на шести строках закупок. Считаем оплаченную глину за неделю, разбираем пары условий и сверяем итог вручную до переноса.
Андрей
Преподаватель, научный редактор
8 мин чтения
Оплаченная глина в моей таблице стоит 8000 рублей. Но на вопрос о первой неделе ответ уже другой: 4900. Ни одно число не взялось из ошибки сложения. Различается отбор. Запрос «формула СУММЕСЛИМН в Excel примеры» я разбираю на одном списке: сначала решаю, какие покупки относятся к вопросу, затем складываю их суммы.
Коротко: СУММЕСЛИМН складывает значения тех записей, в которых одновременно выполнены заданные условия. В нашем случае это материал «Глина», неделя «Первая» и статус «Оплачено». Суммируем деньги. Строки проверяем вместе.
Формула СУММЕСЛИМН в Excel: примеры начинаются с вопроса
Я собрал шесть вымышленных закупок для кружка керамики. Здесь нет настоящей отчётности. Числа подобраны так, чтобы было видно, какое условие отсекает каждую лишнюю запись.
Перенеси четыре колонки в A1:D7: заголовки в первую строку, закупки со второй по седьмую. В D вводи числа без приписанного слова «рублей». Единица измерения у всего столбца одна.
Я записываю вопрос над будущим расчётом: «Сколько заплатили за глину в первую неделю?» Это занимает одну строку, зато не даёт незаметно заменить задачу. Нам нужны не все расходы кружка, не все заказы глины и не оплаты за обе недели.
Теперь я отмечаю подходящие записи глазами. Для этого достаточно пройти таблицу сверху вниз. Получаются строки 2, 6 и 7. В каждой совпали все три признака.
У остальных есть понятная причина исключения:
Строка 3: глина и неделя подходят, но покупка только заказана.
Строка 4: оплата есть, неделя та же, однако куплена глазурь.
Строка 5: материал и статус подходят, но неделя уже вторая.
Пока формулы нет. Это полезная пауза. Если трудно объяснить, почему строка исключена, синтаксис Excel ещё рано чинить: сначала нужно договориться о смысле вопроса.
Собираю формулу из суммы и трёх пар
В отдельную свободную ячейку, например F2, для показанной раскладки подойдёт такое выражение:
Я читаю его с первого диапазона. D2:D7 содержит суммы, которые можно складывать. Дальше идут пары: где проверяем и что ищем. A2:A7 сопоставляем с «Глина», B2:B7 с «Первая», C2:C7 с «Оплачено».
Условия не разъезжаются по разным строкам. Нельзя взять глину из одной записи, оплату из соседней и решить, что покупка подходит. Для строки 3 функция проверит её собственный статус. Там «Заказано». Сумма 1800 в результат не попадёт.
Я проверил расчёт отдельно: 2400 + 900 + 1600 = 4900. Это контрольный итог нашей таблицы. Его удобно оставить рядом с объяснением, чтобы после изменения формулы видеть, откуда появился результат.
Частая путаница возникает с СУММЕСЛИ. У похожих функций различается порядок аргументов: в СУММЕСЛИМН диапазон суммирования стоит первым. Поэтому я не пытаюсь достроить знакомую формулу наугад. Сначала нахожу столбец денег.
Затем проговариваю каждую пару целиком. Не «Глина, Первая, Оплачено», а «материал равен глине, неделя равна первой, статус равен оплате». Так быстрее заметить, что условие случайно поставили рядом с чужим столбцом.
Здесь я использую русское имя функции и точку с запятой между аргументами. Английское имя — SUMIFS. Разделитель зависит от настроек Excel и локали; встречается и запятая. Если редактор не принимает запись, сверь имя и разделители с подсказкой своей версии. По виду текста нельзя угадать все настройки чужой книги.
Формула СУММЕСЛИМН в Excel: примеры с изменённым отбором
Чтобы проверить понимание, я сначала убираю неделю. Вопрос становится другим: сколько оплаченной глины в таблице вообще?
```text
=СУММЕСЛИМН(D2:D7;A2:A7;"Глина";C2:C7;"Оплачено")
```
Теперь подходит и строка 5. Её 3100 добавляются к прежним 4900, получается 8000 рублей. Ничего не сломалось. Мы ослабили отбор и включили вторую неделю.
Затем я возвращаю исходный вопрос и добавляю ограничение: учитывать только покупки суммой не меньше 1000 рублей.
Диапазон D2:D7 теперь участвует дважды: его значения складываются, и по ним же проверяется величина покупки. Строка с 900 исключается. Остаются 2400 и 1600. Итог — 4000.
Я попробовал читать последнее условие без формулы: «каждая подходящая покупка не меньше тысячи». Это снимает ещё одну неоднозначность. Ограничение относится к отдельной записи, а не к окончательной сумме.
Обрати внимание на запись `">=1000"`. Знак сравнения входит в условие и заключён вместе с числом в кавычки. Для слов в предыдущих примерах кавычки тоже нужны. Мы сопоставляем текст, а не придумываем названия ячеек.
Три результата показывают три вопроса:
Что считаем · Подходящие суммы · Итог
Оплаченная глина, обе недели — 2400 + 3100 + 900 + 1600 — 8000
Изменение результата здесь объясняется конкретной записью. Такой проверке я доверяю больше, чем впечатлению «сумма похожа на правду».
Выношу слова из формулы в ячейки
Когда одинаковый расчёт нужен для разных материалов и недель, длинную строку неудобно править вручную. Я отвёл бы под условия отдельное место: F2 — материал, G2 — неделя, H2 — статус. Ранее поставленную туда пробную формулу нужно убрать или перенести. Результат теперь разместим в I2.
Заполни F2:H2 значениями «Глина», «Первая», «Оплачено». Формула в I2 будет такой:
```text
=СУММЕСЛИМН(D2:D7;A2:A7;F2;B2:B7;G2;C2:C7;H2)
```
Ссылки F2, G2 и H2 не заключаются в кавычки: нужны значения этих ячеек. Иначе получилось бы условие с буквальным текстом «F2», которого среди материалов нет.
Теперь можно заменить в G2 первую неделю второй. Для наших исходных записей ожидается 3100. Прежде чем вводить новую комбинацию, я всё равно называю её словами. Удобство управления условиями не отменяет смысл отбора.
Если нужны более подробные объяснения адресов и условий, пригодится разбор работы с нейросетью для формул. Здесь я оставляю диапазоны неизменными, чтобы не смешивать обучение отбору с переносом формулы по листу.
Если сумма удивила, проверяю строки и адреса
У всех диапазонов нашего примера одинаковые границы: от строки 2 до строки 7. Это не украшение. D2:D7 относится к тем же закупкам, что A2:A7, B2:B7 и C2:C7.
Одинаковая длина тоже ещё не гарантирует верность. D2:D7 и A3:A8 содержат по шесть ячеек, но сопоставляют разные строки. В маленькой таблице такой сдвиг виден. В большой книге он может спрятаться за вполне правдоподобным итогом.
Если же диапазоны различаются по размеру, СУММЕСЛИМН может вернуть ошибку #ЗНАЧ!. Не стоит объяснять любой неожиданный ответ одной причиной: ноль, ошибка и неверная сумма требуют разной проверки.
Я прохожу короткую последовательность:
Сверяю первый и последний адрес каждого диапазона.
Проверяю, что первая сумма относится к первой записи каждого столбца условий.
Нахожу одну запись, которая точно должна войти.
Нахожу соседнюю запись, которая точно должна остаться снаружи.
Сравниваю текст статуса и материала с тем, что записано в условии.
Для нашей задачи контрольная соседняя запись — неоплаченные 1800. Если они попали в итог, я смотрю на условие статуса. Если итог равен 8000, проверяю, не убрал ли неделю. Конкретный след помогает искать причину.
В новом файле граница D7 сама по себе не означает «все будущие покупки». Когда добавляешь данные ниже выбранного диапазона, пересмотри ссылки. Формула должна охватывать те записи, о которых ты спрашиваешь.
Попроси объяснить одну исключённую покупку
Я использовал здесь самостоятельный расчёт. Для своей формулы можно попросить LibraChat разобрать аргументы и объяснить отбор. Например, вставь в чат такую заготовку вместе с небольшой вымышленной таблицей:
Поисковая фраза «формула СУММЕСЛИМН в Excel примеры» ещё не описывает мою таблицу. Для разбора я добавлю адреса столбцов и вопрос к данным. Без них не узнать, относится ли D к деньгам или к количеству.
Проверь формулу СУММЕСЛИМН по указанной раскладке колонок. Объясни каждую пару диапазон/условие. Назови строки, которые должны войти в сумму, и причину исключения остальных. Если в задаче есть неоднозначность, сначала задай вопрос.
Это предлагаемый запрос. Его ответ всё равно нужно сопоставить с исходными ячейками. Текстовое объяснение не означает, что формула уже внесена в твою книгу.
Открой чат LibraChat со своей раскладкой столбцов, если хочешь разобрать непонятный аргумент. Когда планируешь прикладывать книгу целиком, сначала посмотри подходящие условия работы с файлами на странице тарифов.
А проверку начни с вопроса о 1800 рублях. Почему эта глина не вошла в наши 4900? Если можешь ответить без взгляда на формулу, смысл СУММЕСЛИМН уже держится.
