Функция суммпроизв в excel

Количество ячеек, не равных нескольким критериям

Для подсчета ячеек, не равных многим критериям (т.е. не равны х, у, z, и т.д.), вы можете использовать формулу, основанную на СЧЁТЕСЛИ, СУММПРОИЗВ и СЧЁТЗ.

Если у вас есть всего лишь пару значений, которые вы не хотите рассчитывать, вы можете использовать функцию СЧЁТЕСЛИМН следующим образом:

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

Эта формула использует именованный диапазон «Критерии» (E5: E7) для хранения значений, которые мы не хотим рассчитывать.

Мы начинаем путем подсчета всех значений в диапазоне с функцией СЧЁТЗ:

Далее, мы генерируем подсчет всех значений, которые мы не хотим считать с помощью СЧЁТЕСЛИ, так:

Поскольку диапазон содержит несколько значений, СЧЁТЕСЛИ будет возвращать несколько результатов. В примере, мы получаем обратно массив значений, как этот:

и мы используем СУММПРОИЗВ, чтобы суммировать все элементы в массиве, получаем 5. Это число затем вычитается из первоначальной общей суммы с получением конечного результата.

Использование СУММПРОИЗВ вместо СУММ избавляет от необходимости использовать формулу массива.

А6:А15

Как работает СУММЕСЛИМН?

При помощи СУММЕСЛИМН можно найти сумму величин, для которых есть много условий. Она появилась впервые в MS Excel 2007, поэтому вы можете использовать ее во всех современных версиях программы.

По сравнению с СУММЕСЛИ, синтаксис СУММЕСЛИМН немного сложнее:

Первые 3 аргумента являются обязательными, а вот дополнительные диапазоны и связанные с ними условия являются необязательными.

диапазон_суммирования — требуется одна или более ячеек для суммирования. Это может быть отдельная клеточка, область или именованный диапазон. Суммируются только ячейки с чисами; пустые и текстовые значения игнорируются.

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

условие1- обязательное первое условие, которое должно быть выполнено. Вы можете предоставить его в виде числа, логического выражения, ссылки, текста или другой функции Excel. Например, вы можете использовать такие критерии, как 10, «> = 10», A1, «яблоко» или СЕГОДНЯ().

Все, что следует далее — это дополнительные диапазоны и связанные с ними критерии. Они не являются обязательными, но если у вас только одно ограничение, то зачем вам эта функция? Просто используйте СУММЕСЛИ. Тем не менее, вы можете использовать до 127 пар диапазон/условие.

Важно! Функция СУММЕСЛИМН работает с логикой «И». Это означает, что число в диапазоне суммирования учитывается, только если оно удовлетворяет всем указанным критериям (все требования соблюдаются для этой ячейки)

Пример суммирования с использованием функции СУММЕСЛИ

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

В таблице указаны позиции, их количества, а также принадлежность к той или иной группе товаров (первый столбец). Рассмотрим пока упрощенное использование СУММЕСЛИ, когда нам нужно посчитать сумму только по тем позициям, значения по которым соответствуют некоторому условию. Например, мы хотим узнать, сколько было продано топовых позиций, т.е. тех, значение которых превышает 70 ед. Искать такие товары глазами, а потом суммировать вручную не очень удобно, поэтому функция СУММЕСЛИ здесь очень уместна.

Первым делом выделяем ячейку, где будет подсчитана сумма. Далее вызываем Мастера функций. Это значок fx в строке формул. Далее ищем в списке функцию СУММЕСЛИ и нажимаем на нее. Открывается диалоговое окно, где для решения данной задачи нужно заполнить всего два (первые) поля из трех предложенных.

Поэтому я и назвал такой пример упрощенным. Почему 2 (два) из 3 (трех)? Потому что наш критерий находится в самом диапазоне суммирования.

В поле «Диапазон» указывается та область таблицы Excel, где находятся все исходные значения, из которых нужно что-то отобрать и затем сложить. Задается обычно с помощью мышки.

В поле «Критерий» указывается то условие, по которому формула будет проводить отбор. В нашем случае указываем «>70». Если не поставить кавычки, то они потом сами дорисуются.

Последнее поле «Дапазон_суммирования» не заполняем, так как он уже указан в первом поле.

Таким образом, функция СУММЕСЛИ берет критерий и начинает отбирать все значения из указанного диапазона, удовлетворяющие заданному критерию. После этого все отобранные значения складываются. Так работает алгоритм функции.

Заполнив в Мастере функций необходимые поля, нажимаем на клавиатуре кнопку «Enter», либо в окошке Мастера «Ок». На месте вводимой функции должно появиться рассчитанное значение. В моем примере получилось 224шт. То есть суммарное значение проданных товаров в количестве более 70 штук составило 224шт. (это видно в нижнем левом углу окна Мастера еще до нажатия «ок»). Вот и все. Это был упрощенный пример, когда критерий и диапазон суммирования находятся в одном месте.

Теперь давайте рассмотрим, пример, когда критерий не совпадает с диапазоном суммирования. Такая ситуация встречается гораздо чаще. Рассмотрим те же условные данные. Пусть нам нужно узнать сумму не больше или меньше какого-то значения, а сумму конкретной группы товаров, допустим, группы Г.

Для этого снова выделяем ячейку с будущим подсчетом суммы и вызываем Мастер функций. В первом окошке указываем диапазон, где содержится критерий, в нашем случае это столбец с названиями групп товаров. Далее сам критерий прописываем либо вручную, оставив в соответствующем поле запись «группа Г», либо просто указываем мышкой ячейку с нужным критерием. Последнее окошко – это диапазон, где находятся суммируемые данные.

Результатом будет сумма проданных товаров из группы Г – 153шт.

Итак, мы посмотрели, как рассчитать одну сумму по одному конкретному критерию. Однако чаще возникает задача, когда требуется рассчитать несколько сумм для нескольких критериев. Нет ничего проще! Например, нужно узнать суммы проданных товаров по каждой группе. То бишь интересует 4 (четыре) значения по 4-м (четырем) группам (А, Б, В и Г). Для этого обычно делается список групп в виде отдельной таблички. Понятное дело, что названия групп должны в точности совпадать с названиями групп в исходной таблице. Сразу добавим итоговую строчку, где сумма пока равна нулю.

Затем прописывается формула для первой группы и протягивается на все остальные

Здесь только нужно обратить внимание на относительность ссылок. Диапазон с критериями и диапазон суммирования должны быть абсолютным ссылками, чтобы при протягивании формулы они не «поехали вниз», а сам критерий, во-первых нужно указать мышкой (а не прописать вручную), во-вторых, должен быть относительной ссылкой, так как каждая сумма имеет свой критерий суммирования

Заполненные поля Мастера функций при подобном расчете будут выглядеть примерно так.

Как видно, для первой группы А сумма проданных товаров составила 161шт (нижний левый угол рисунка). Теперь нажимаем энтер и протягиваем формулу вниз.

Все суммы рассчитались, а их общий итог равен 535, что совпадает с итогом в исходных данных. Значит, все значения просуммировались, ничего не пропустили.

СУММЕСЛИ

Аргументы

Рассмотрим аргументы для СУММЕСЛИ.

=СУММЕСЛИ(диапазон; критерий; )

Диапазон – здесь необходимо указать, тот столбец, данные в котором будут сравниваться с заданным Критерием. В примере, это D2:D26.

Критерий – здесь нужно выбрать одно значение из ранее указанного Диапазона. Сначала будем указывать мальчиков, затем девочек.

Диапазон суммирования – это столбец, значения в котором нужно просуммировать, если они относятся к выбранному Критерию. Например, если для выбранного Диапазона D2:D26 указан Критерий «мальчик», значит в столбце «Средний бал»: С2:С26, будут суммироваться все значения, которые относятся к мальчикам.

Пример

Выделяем ячейку G4 и ставим «=». Пишем в ней СУММЕСЛИ и открываем скобку «(». В качестве Диапазона, из которого будут выбираться значения, выделяем D2:D26. В качестве Критерия, нужно выбрать одно из значений в ранее указанном Диапазоне. У нас это «мальчик», поэтому выделяем ячейку D3. Диапазон суммирования – это столбец, в котором указан «Средний бал». Выделяем С2:С26. Между аргументами ставьте «;», в конце, закройте скобку «)». Нажмите «Enter».

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

Теперь давайте растянем формулу по столбцу. Конечно, можно также прописать ее и для девочек, но если у Вас много разных критериев – это займет немало времени. Закрепим диапазоны для столбцов С и D, чтобы при копировании формулы они не ползли вниз.

Для этого выделяем G4 и в «Строке формул» после D2, D26, C2, C26 нажимаем клавишу «F4». В адресах на ячейки, появятся знаки «$». Таким образом, мы делаем ссылки абсолютными. Чтобы растянуть формулу по столбцу, потяните за правый нижний угол ячейки, курсор изменит при этом вид на черный плюсик.

Если Вам интересно, как сделать ссылки в Эксель: абсолютные, относительные, на другой лист или книгу, прочтите статью по этой теме.

Дальше смотрим на «Строку формул». В качестве аргументов для Диапазона и Диапазона суммирования берутся правильные значения: D2:D26, C2:C26. В качестве Критерия укажите адрес нужной ячейки. Поскольку, нужно посчитать средний балл для всех девочек, в столбце D выбираем адрес любой ячейки, в которой указано «девочка»: D4.

Функция «СУММЕСЛИМН»

Данная функция работает, как и «СУММЕСЛИ», но дает больше возможностей в задании параметров математической операции. Для применения этой функции, вам нужно выполнить следующие шаги:

Щелкаем левой кнопкой мышки по пустой ячейке, где будем вводить функцию

Нажмите на «fx» или «Вставить функцию».

Нажимаем по значку функции «fx»

Появится окно, где из перечня выберите функцию «СУММЕСЛИМН».

Выбираем функцию «СУММЕСЛИМН»

Кликните на «ОК» или два раза нажмите на «СУММЕСЛИМН».

Щелкаем по функции дважды или нажимаем «ОК»

Проявится меню «Аргументы функции».

В поле «Диапазон суммирования», вводим ячейки из столбца «Сумма», протянув левую кнопку мышки от первой до последней ячейки

В поле «Диапазон условия1» вводим ячейки из столбца «Товар», выделив их левой кнопкой мышки

В поле «Условие1» вводим «яблоки» или другое значение, результат которого мы хотим получить

Щелкаем по кнопке «ОК»

Вводим данные в соответствующие поля, задаем необходимые условия в кавычках, щелкаем по кнопке «ОК»

Результат, высчитанный функцией «СУММЕСЛИМН»

Эту функцию также можно ввести вручную, однако это будет довольно сложно, поскольку присутствует много условий. Чтобы программа работала корректно, и вы не потеряли время, пытаясь найти ошибку в длинной записи, рекомендуем все же воспользоваться действием через «fx».

С наглядной инструкцией вы также можете ознакомиться в видео.

Как правильно пишется, ударение в слове «поставленная задача»

Значение слова «поставить»

ПОСТА́ВИТЬ, -влю, -вишь; сов., перех.

Все значения слова «поставить»

Зада́ча — проблемная ситуация с явно заданной целью, которую необходимо достичь; в более узком смысле задачей также называют саму эту цель, данную в рамках проблемной ситуации, то есть то, что требуется сделать. В первом значении задачей можно назвать, например, ситуацию, когда нужно достать предмет, находящийся очень высоко; второе значение слышно в указании: «Ваша задача — достать этот предмет». Несколько более жёсткое понимание «задачи» предполагает явными и определёнными не только цель, но и условия задачи, которая в этом случае определяется как осознанная проблемная ситуация с выделенными условиями (данным) и требованием (целью). Ещё более узкое определение называет задачей ситуацию с известным начальным состоянием системы и конечным состоянием системы, причём алгоритм достижения конечного состояния от начального известен (в отличие от проблемы, в случае которой алгоритм достижения конечного состояния системы не известен).

Все значения слова «задача»

Предложения со словосочетанием «поставленная задача»:

• Наш войсковой подвоз, и ранее не справлявшийся с поставленными задачами, вовсе «захлебнулся».

• Им была поставлена задача — обнаружить бойцов штурмовой группы ещё на подходе, до проникновения в самолёт.

• Параллельно им была поставлена задача по патрулированию воздушного пространства в районе аэродрома.

(все предложения)

Автосумма в Excel

Одна из самых простых и часто используемых функций в программе – это автосумма. Чтобы ее применить, сделайте следующее:

1. Выберите пустую ячейку, в которой пропишется итоговый результат.
2. Нажмите на иконку знака суммы, которая располагается на панели инструментов.
3. Программа автоматически выберет диапазон ячеек, а вверху отобразится функция СУММ . Нажмите на клавишу Enter, чтобы увидеть результат.

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

Автосумма

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

сумм произв если

​ двумя столбцами (полями):​​ в этой статье.​ (или массивы) в​=СУММПРОИЗВ((B2:B8=C12)*(C2:C8=C11)*(D2:D8=C13);E2:E8)​Диалоговое окно заполнили​ складывает. Этой функцией​ (=; =).​ENTER​ функция СУММПРОИЗВ() возвращает​Пытаюсь расшевелить Вас​Примерчик прилагается.​Cactus​

​ через знак умножения??​​ значения — выдает​

​ месяца (предполагается, что​​ в одной строке​2. другой вариант =СУММПРОИЗВ((A2:A13=D2)*(B2:B13);—(B2:B13>=E2))​ текстовым «Фрукты» и​

​Скопируйте данные примера из​​ качестве аргументов (части​​Третий способ​

​ так.​​ можно сделать​Критерии можно указывать в​, что снимает некий​ значение ошибки #ЗНАЧ!.​ на «почитать-подумать», а​vikttur​

​: Доброй ночи форумчане​​V​ в качестве результата​ в таблице данные​ соответствует Условию И​Разберем подробнее использование​ числовым «Количество на​ приведенной ниже таблицы​

​ формулы, которые делают​​.​​Нажимаем «ОК». В ячейке​

Сумма с разных листов

Excel позволяет одновременно работать с несколькими таблицами, которые расположены на разных листах. Для этого действуйте согласно простой инструкции:

1. Выберите пустую ячейку на первом листе, затем перейдите в мастер функций так, как описывалось выше.
2. В окне «Аргументы функции » поставьте курсор в строкуЧисло2 , затем перейдите на другой лист программы.
3. Выделите нужные для подсчета ячейки и кликните по кнопке подтверждения.

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

Как использовать СУММЕСЛИ – Примеры.

Определив синтаксис, давайте теперь попробуем использовать функцию СУММЕСЛИ на практике.

СУММЕСЛИ больше, меньше или равно

Давайте взглянем на некоторые формулы СУММЕСЛИ, которые вы можете использовать для сложения значений, больших, меньших или равных заданному значению.

Поясним примеры на скриншоте выше:

• Пример 1. Добавляет значения больше 6 в диапазон ячеек A5: A15.
• Пример 2. Складывает значения в диапазоне ячеек B5: B15, если в соответствующем столбце A меньше 8.
• Пример 3. Складывает значения в ячейках A5: A15, которые равны значению в ячейке C4. Два варианта написания
• Пример 4. Складывает значения в ячейках B5: B15, если соответствующая ячейка в столбце A отличается от значения в ячейке C4.
• Пример 5. Добавляет значения больше или равные 8 в диапазоне A5: A15.
• Пример 6. Складывает значения в ячейках B5: B15, если соответствующее значение в столбце A меньше или равно 8.

СУММЕСЛИ с текстовыми критериями

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

Поясним примеры на скриншоте выше:

• Пример 1. Складывает значения ячеек B2: B6, если соответствующая ячейка в столбце A содержит в точности слово вишня и никаких других слов или символов. Ячейки, содержащие вишню и яблоки, не будут включены.
• Пример 2. Складывает значения ячеек B2: B6, если соответствующая ячейка в столбце A содержит слово «шня» отдельно или в сочетании с любыми другими словами или символами. Ячейки, содержащие «вишню» и «вишню», будут добавлены, потому что они содержат «шня».
• Пример 3. Складывает значения ячеек B2: B6, если соответствующая ячейка в столбце A содержит значение, отличное от Cherry».
• Пример 4. Складывает значения ячеек B2: B6, если соответствующая ячейка в столбце A не содержит слова «шня» ни отдельно, ни в сочетании с любым другим словом. В этом случае будет отображаться только сумма яблок.

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

Если вы хотите пойти еще дальше и получить универсальную формулу СУММЕСЛИ, вы можете заменить числовое или текстовое значение в критериях ссылкой на ячейку, например:

= СУММЕСЛИ (A2: A8; «<>» & F1; C2: C8)

В этом случае вам не нужно изменять формулу условной суммы на основе других критериев — просто введите новое значение в ссылочную ячейку.

Формулы СУММЕСЛИ с подстановочными знаками

Если вы намереваетесь условно оценивать ячейки на основе текстовых критериев и хотите получить совпадение с промежуточной суммой, вам нужно будет использовать подстановочные знаки в формуле СУММЕСЛИ. Доступны следующие подстановочные знаки:

• Звездочка (*): обозначает любое количество символов
• Знак вопроса (?): Обозначает символ в определенной позиции.
• Амперсанд (&): обозначает добавление текста. В следующем примере показано, как это работает.

Поясним примеры на скриншоте выше:

Пример 1. Складывает значения ячеек B2: B6, если соответствующая ячейка в столбце A содержит частичное совпадение, то есть в начале и в конце любого количества символов.
Пример 2. Складывает значения ячеек B2: B6, если соответствующая ячейка в столбце A содержит частичное совпадение, то есть последняя буква может быть любой. В этом примере я намеренно ошибся и написал вишну.
Пример 3

Добавляет значения ячеек B2: B6, если соответствующая ячейка в столбце A содержит частичное совпадение, критерии включают ссылку на ячейку, заключенную в звездочки, обратите внимание на использование амперсанда (&) до и после ссылки на ячейку для объединения строки.

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

= СУММЕСЛИ (A2: A8; «????»; C2: C8)

Данные итогов в таблице Excel

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

Щелкните любое место таблицы.

Выберите Работа с таблицами > Конструктор и установите флажок Строка итогов.

Строка итогов будет вставлена в нижней части таблицы.

Примечание: Если применить в строке итогов формулы, а затем отключить ее, формулы будут сохранены. В приведенном выше примере мы применили функцию СУММ для строки итогов. При первом использовании строки итогов ячейки будут пустыми.

Выделите нужный столбец, а затем выберите вариант из раскрывающегося списка. В этом случае мы применили функцию СУММ к каждому столбцу:

Вы увидите, что Excel формулу: =ПРОМЕЖУТОЧНЫЕ.109;). Это функция SUBTOTAL для функции СУММ, а также формула структурированной ссылки, которая является исключительно Excel таблицами. Подробнее об использовании структурированных ссылок в Excel таблицах.

К итоговому значению можно применить и другие функции, щелкнув Другие функции или создав их самостоятельно.

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

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

Щелкните любое место таблицы.

Перейдите в таблицу > строку итогов.

Строка итогов будет вставлена в нижней части таблицы.

Примечание: Если применить в строке итогов формулы, а затем отключить ее, формулы будут сохранены. В приведенном выше примере мы применили функцию СУММ для строки итогов. При первом использовании строки итогов ячейки будут пустыми.

Выделите нужный столбец, а затем выберите вариант из раскрывающегося списка. В этом случае мы применили функцию СУММ к каждому столбцу:

Вы увидите, что Excel формулу: =ПРОМЕЖУТОЧНЫЕ.109;). Это функция SUBTOTAL для функции СУММ, а также формула структурированной ссылки, которая является исключительно Excel таблицами. Подробнее об использовании структурированных ссылок в Excel таблицах.

К итоговому значению можно применить и другие функции, щелкнув Другие функции или создав их самостоятельно.

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

Вы можете быстро подытогить итоги в Excel, включив параметр Строка итогов.

Щелкните любое место таблицы.

На вкладке Конструктор таблиц > стилей > строке итогов.

Строка Итог будет вставлена в нижнюю часть таблицы.

Настройка агрегатной функции для ячейки «Строка итогов»

Примечание: Это одна из нескольких бета-функций, и в настоящее время она доступна только для части инсайдеров Office. Мы будем оптимизировать такие функции в течение следующих нескольких месяцев. Когда они будут готовы, мы сделаем их доступными для всех участников программы предварительной оценки Office и подписчиков Microsoft 365.

Строка итогов позволяет выбрать агрегатную функцию для каждого столбца.

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

Выберите агрегатную функцию для столбца

Обратите внимание, что вы можете щелкнуть Другие функции, чтобы увидеть дополнительные параметры

Вы всегда можете задать вопрос специалисту Excel Tech Community, попросить помощи в сообществе Answers community, а также предложить новую функцию или улучшение на веб-сайте Excel User Voice.