Как в эксель посчитать критериальную функцию

СЧЁТЕСЛИ (функция СЧЁТЕСЛИ)

С помощью статистической функции СЧЁТЕСЛИ можно подсчитать количество ячеек, отвечающих определенному условию (например, число клиентов в списке из определенного города).

Самая простая функция СЧЁТЕСЛИ означает следующее:

• =СЧЁТЕСЛИ(где нужно искать;что нужно найти)

• =СЧЁТЕСЛИ(A2:A5;»Лондон»)
• =СЧЁТЕСЛИ(A2:A5;A4)

СЧЁТЕСЛИ(диапазон;критерий)

Имя аргумента

диапазон (обязательный)

Группа ячеек, для которых нужно выполнить подсчет. Диапазон может содержать числа, массивы, именованный диапазон или ссылки на числа. Пустые и текстовые значения игнорируются.

критерий (обязательный)

Число, выражение, ссылка на ячейку или текстовая строка, которая определяет, какие ячейки нужно подсчитать.

Например, критерий может быть выражен как 32, «>32», В4, «яблоки» или «32».

В функции СЧЁТЕСЛИ используется только один критерий. Чтобы провести подсчет по нескольким условиям, воспользуйтесь функцией СЧЁТЕСЛИМН.

Примеры

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

Количество ячеек, содержащих текст «яблоки» в ячейках А2–А5. Результат — 2.

Количество ячеек, содержащих текст «персики» (значение ячейки A4) в ячейках А2–А5. Результат — 1.

Количество ячеек, содержащих текст «яблоки» (значение ячейки A2) и «апельсины» (значение ячейки A3) в ячейках А2–А5. Результат — 3. В этой формуле для указания нескольких критериев, по одному критерию на выражение, функция СЧЁТЕСЛИ используется дважды. Также можно использовать функцию СЧЁТЕСЛИМН.

Количество ячеек со значением больше 55 в ячейках В2–В5. Результат — 2.

Количество ячеек со значением, большим или равным 32 и меньшим или равным 85, в ячейках В2–В5. Результат — 1.

Количество ячеек, содержащих любой текст, в ячейках А2–А5. Подстановочный знак «*» обозначает любое количество любых символов. Результат — 4.

Количество ячеек, строка в которых содержит ровно 7 знаков и заканчивается буквами «ки», в диапазоне A2–A5. Подставочный знак «?» обозначает отдельный символ. Результат — 2.

Распространенные неполадки

Возможная причина

Для длинных строк возвращается неправильное значение.

Функция СЧЁТЕСЛИ возвращает неправильные результаты, если она используется для сопоставления строк длиннее 255 символов.

Для работы с такими строками используйте функцию СЦЕПИТЬ или оператор сцепления &. Пример: =СЧЁТЕСЛИ(A2:A5;»длинная строка»&»еще одна длинная строка»).

Функция должна вернуть значение, но ничего не возвращает.

Аргумент критерий должен быть заключен в кавычки.

Формула СЧЁТЕСЛИ получает #VALUE! ошибка при ссылке на другой лист.

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

Рекомендации

Помните о том, что функция СЧЁТЕСЛИ не учитывает регистр символов в текстовых строках.

Критерий не чувствителен к регистру. Например, строкам «яблоки» и «ЯБЛОКИ» будут соответствовать одни и те же ячейки.

Использование подстановочных знаков

В критериях можно использовать подстановочные знаки — вопросительный знак (?) и звездочку (*). Вопросительный знак соответствует любому отдельно взятому символу. Звездочка — любой последовательности символов. Если требуется найти именно вопросительный знак или звездочку, следует ввести значок тильды (~) перед искомым символом.

Например, =СЧЁТЕСЛИ(A2:A5;»яблок?») возвращает все вхождения слова «яблок» с любой буквой в конце.

Убедитесь, что данные не содержат ошибочных символов.

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

Для удобства используйте именованные диапазоны.

ФУНКЦИЯ СЧЁТЕСЛИ поддерживает именованные диапазоны в формуле (например, =COUNTIF(fruit;»>=32″)-COUNTIF(fruit;»>85″). Именованный диапазон может располагаться на текущем листе, другом листе этой же книги или листе другой книги. Чтобы одна книга могла ссылаться на другую, они обе должны быть открыты.

Примечание: С помощью функции СЧЁТЕСЛИ нельзя подсчитать количество ячеек с определенным фоном или цветом шрифта. Однако Excel поддерживает пользовательские функции, в которых используются операции VBA (Visual Basic для приложений) над ячейками, выполняемые в зависимости от фона или цвета шрифта. Вот пример подсчета количества ячеек определенного цвета с использованием VBA.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в 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 для 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

1. На листе коснитесь первой пустой ячейки после диапазона ячеек с числами или выделите необходимый диапазон ячеек касанием и перемещением пальца.
2. Коснитесь элемента Автосумма.
3. Нажмите Сумма.
4. Коснитесь флажка. Готово!

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

Когда вы нажимаете кнопку Автосумма, Excel автоматически вводит формулу для суммирования чисел (в которой используется функция СУММ).

Приведем пример. Чтобы добавить числа за январь в бюджете «Развлечения», выберите ячейку B7, которая непосредственно под столбцом чисел. Затем нажмите кнопку Автоумма. В ячейке B7 появится формула, Excel выделит сумму ячеек.

Чтобы отобразить результат (95,94) в ячейке В7, нажмите клавишу ВВОД. Формула также отображается в строке формул вверху окна Excel.

• Чтобы сложить числа в столбце, выберите ячейку под последним числом в столбце. Чтобы сложить числа в строке, выберите первую ячейку справа.
• Автосема находится в двух местах: главная > и Формула >Автосема.
• Создав формулу один раз, ее можно копировать в другие ячейки, а не вводить снова и снова. Например, при копировании формулы из ячейки B7 в ячейку C7 формула в ячейке C7 автоматически настроится под новое расположение и подсчитает числа в ячейках C3:C6.
• Кроме того, вы можете использовать функцию «Автосумма» сразу для нескольких ячеек. Например, можно выделить ячейки B7 и C7, нажать кнопку Автосумма и суммировать два столбца одновременно.
• Также вы можете суммировать числа путем создания простых формул.

Дополнительные сведения

Вы всегда можете задать вопрос эксперту в Excel Tech Community или получить поддержку в сообществах.

Выборочные вычисления по одному или нескольким критериям

Имеем таблицу по продажам, например, следующего вида: Задача: просуммировать все заказы, которые менеджер Григорьев реализовал для магазина «Копейка».

Способ 1. Функция СУММЕСЛИ, когда одно условие

Если бы в нашей задаче было только одно условие (все заказы Петрова или все заказы в «Копейку», например), то задача решалась бы достаточно легко при помощи встроенной функции Excel СУММЕСЛИ (SUMIF) из категории Математические (Math&Trig) . Выделяем пустую ячейку для результата, жмем кнопку fx в строке формул, находим функцию СУММЕСЛИ в списке: Жмем ОК и вводим ее аргументы:

• Диапазон — это те ячейки, которые мы проверяем на выполнение Критерия. В нашем случае — это диапазон с фамилиями менеджеров продаж.
• Критерий — это то, что мы ищем в предыдущем указанном диапазоне. Разрешается использовать символы * (звездочка) и ? (вопросительный знак) как маски или символы подстановки. Звездочка подменяет собой любое количество любых символов, вопросительный знак — один любой символ. Так, например, чтобы найти все продажи у менеджеров с фамилией из пяти букв, можно использовать критерий . . А чтобы найти все продажи менеджеров, у которых фамилия начинается на букву «П», а заканчивается на «В» — критерий П*В. Строчные и прописные буквы не различаются.
• Диапазон_суммирования — это те ячейки, значения которых мы хотим сложить, т.е. нашем случае — стоимости заказов.

Способ 2. Функция СУММЕСЛИМН, когда условий много

Если условий больше одного (например, нужно найти сумму всех заказов Григорьева для «Копейки»), то функция СУММЕСЛИ (SUMIF) не поможет, т.к. не умеет проверять больше одного критерия. Поэтому начиная с версии Excel 2007 в набор функций была добавлена функция СУММЕСЛИМН (SUMIFS) — в ней количество условий проверки увеличено аж до 127! Функция находится в той же категории Математические и работает похожим образом, но имеет больше аргументов:

При помощи полосы прокрутки в правой части окна можно задать и третью пару (Диапазон_условия3—Условие3), и четвертую, и т.д. — при необходимости.

Если же у вас пока еще старая версия Excel 2003, но задачу с несколькими условиями решить нужно, то придется извращаться — см. следующие способы.

Способ 3. Столбец-индикатор

Добавим к нашей таблице еще один столбец, который будет служить своеобразным индикатором: если заказ был в «Копейку» и от Григорьева, то в ячейке этого столбца будет значение 1, иначе — 0. Формула, которую надо ввести в этот столбец очень простая:

=(A2=»Копейка»)*(B2=»Григорьев»)

Логические равенства в скобках дают значения ИСТИНА или ЛОЖЬ, что для Excel равносильно 1 и 0. Таким образом, поскольку мы перемножаем эти выражения, единица в конечном счете получится только если оба условия выполняются. Теперь стоимости продаж осталось умножить на значения получившегося столбца и просуммировать отобранное в зеленой ячейке:

Способ 4. Волшебная формула массива

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

=СУММ((A2:A26=»Копейка»)*(B2:B26=»Григорьев»)*D2:D26)

После ввода этой формулы необходимо нажать не Enter , как обычно, а Ctrl + Shift + Enter — тогда Excel воспримет ее как формулу массива и сам добавит фигурные скобки. Вводить скобки с клавиатуры не надо. Легко сообразить, что этот способ (как и предыдущий) легко масштабируется на три, четыре и т.д. условий без каких-либо ограничений.

Способ 4. Функция баз данных БДСУММ

В категории Базы данных (Database) можно найти функцию БДСУММ (DSUM) , которая тоже способна решить нашу задачу. Нюанс состоит в том, что для работы этой функции необходимо создать на листе специальный диапазон критериев — ячейки, содержащие условия отбора — и указать затем этот диапазон функции как аргумент:

=БДСУММ(A1:D26;D1;F1:G2)

Критериальное оценивание как ключ к заниям

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

В результате анализа данных, представленных на диаграмме…

В ходе оформления отчета использовались следующие возможности…

Привитие ценностей

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

Межпредметные связи

Предварительные знания

Запланированные этапы урока

Запланированная деятельность на уроке

Начало 1 урока

Приветствие.

Ознакомление с целями обучения и критериями оценивания

1. мин

1. Актуализация знаний методом «вопрос» и «ответ». Рубрика «А вы знаете?»

Учащимся раздаются карточки с вопросами и с ответами других вопросов в карточках. Первый учащийся прочитает вопрос, а другие смотрят на свои карточки и постараются найти правильный ответ. После ответивши учащийся читает свой вопрос и все повторяется.

Учитель проводит формативное оценивания в виде золотых звездочек если учащиеся находят правильные ответы на заданные вопросы.

«Основы табличного редактора Excel »

1. мин

1. Практическое задание «Создать таблицу»

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

Учащиеся создают таблицу по предложенной задаче и оформляют таблицу по критериям.

Учитель проводит формативное оценивания в виде золотых звездочек если учащиеся оформление таблицы по заданным критериям

1. мин

1. Практическое задание «Продажи». Парная работа.

Учащимся предоставляется задача «Продажи» где, нужно используя строку формула рассчитать Объём и Выручку.

Учитель проводит формативное оценивания в виде золотых звездочек если учащиеся правильно составили формулы для расчета

1. Теоретическое знание

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

Учитель предоставляет информацию об использовании выше указанных функции в табличном процессоре для создания экономического проекта.

1. Закрепление полученных знании методом «Собери фигуру», не большая групповая работа.

Учитель делит класс на три группы с помощью нескольких геометрических фигур.

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

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

Файл «Собери фигуру»

1. Практическое задание «экономически проект»

Учащимся предлогается задание создать микропроект используя экономические функции:

По завершению уитель проводит обсуждение полученных результатов работ.

Учитель проводит формативное оценивания в виде золотых звездочек если учащиеся правильно написали формулы с использованием встроенных функции

1. Подведение итогов урока.

Обратная связь. Получение результатов анализа обратной связи учащихся.

Листы для обратной связи

1. Проведения оценивания учащихся для каждого критерия успеха по достижению цели обучения и цели урока.

Дифференциация – каким образом Вы планируете оказать больше поддержки? Какие задачи Вы планируете поставить перед более способными учащимися?

Оценивание – как Вы планируете проверить уровень усвоения материала учащимися?

Здоровье и соблюдение техники безопасности

Дифференциация может быть выражена в подборе заданий, в ожидаемом результате от конкретного ученика, в оказании индивидуальной поддержки учащемуся, в подборе учебного материала и ресурсов с учетом индивидуальных способностей учащихся (Теория множественного интеллекта по Гарднеру).

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

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

Здоровье сберегающие технологии.

Используемые физминутки и активные виды деятельности.

Пункты, применяемые из Правил техники безопасности на данном уроке.

Рефлексия по уроку

Были ли цели урока/цели обучения реалистичными?

Все ли учащиеся достигли ЦО?

Если нет, то почему?

Правильно ли проведена дифференциация на уроке?

Выдержаны ли были временные этапы урока?

Какие отступления были от плана урока и почему?

Используйте данный раздел для размышлений об уроке. Ответьте на самые важные вопросы о Вашем уроке из левой колонки.

Общая оценка

Какие два аспекта урока прошли хорошо (подумайте как о преподавании, так и об обучении)?

Что могло бы способствовать улучшению урока (подумайте как о преподавании, так и об обучении)?

Что я выявил(а) за время урока о классе или достижениях/трудностях отдельных учеников, на что необходимо обратить внимание на последующих уроках?

Просмотрено: 0%
Просмотрено: 0%

Рабочие листы к Вашему уроку:

• 

• 

• 

• 

• 

• 

• 

• 

• 

• 

• 

• 

• 

• 

• 

Рабочие листы
к вашим урокам

Выбранный для просмотра документ На завтра прочитать (6).docx

Синтаксис функции СУММЕСЛИ

СУММЕСЛИ( диапазон , критерий , диапазон_суммирования )

В нашем примере нам необходимо заполнить вторую таблицу справа – столбец суммы заказов по городам. Как вы видите из синтаксиса функции СУММЕСЛИ нам потребуется три аргумента.

диапазон – это диапазон сравнения, то есть тот массив, в котором будут сравниваться с критериями. В нашем пример это A5:A504 , чтобы при протягивании формулы наш диапазон не сдвигался, сделаем его абсолютным, поставив знак $. Получаем – $A$5:$A$504

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

диапазон_суммирования – это тот диапазон, который необходимо просуммировать, при совпадении критериев. В нашем примере это диапазон: B5:B504 , который мы также для удобства превратим в абсолютный $B$5:$B$504

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

Для решения задачи с данным примером в ячейку F5 впишем следующую формулу:

= СУММЕСЛИ ( $A$5:$A$504 ; E5 ; $B$5:$B$504 )

Логика работы функции СУММЕСЛИ следующая: в диапазоне $A$5:$A$504 ищется критерий E5 (Санкт-Петербург) , если Санкт-Петербург находится, то суммируется кол-во заказов из этой строчки то есть из диапазона $B$5:$B$504

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

Функция «СЧЁТЕСЛИ» в Excel.

Рассмотрим, как посчитать количество ячеек в Excel . Для этого достаточно написать формулу, которая будет считать ячейки с данными по определенным условиями . Здесь хорошо подходит функция в Excel «СЧЁТЕСЛИ» . Это значит, что формула будет считать ячейки только в том случае, если будут условия, которые мы пропишем в формуле. Например, посчитать результат голосования или ответов в анкете, опросе, провести другой анализ данных, т.д.
Эта функция нужна и для составления больших формул с многими условиями. Чтобы понять эту функцию, рассмотрим несколько примеров.
Первый пример.
У нас такая таблица.

Посчитаем количество ячеек с числами больше 300 в столбце B. В ячейке В7 пишем формулу.

На закладке «Формулы» в разделе «Библиотека функций» нажимаем кнопку «Другие функции» и, в разделе «Статистические», выбираем функцию «СЧЁТЕСЛИ» . Заполняем диалоговое окно так.
Указали диапазон столбца В. «Критерий» — поставили «>300» — это значит, посчитать все ячейки в столбце В, где цифры больше 300.
Получилось такая формула.
Формула посчитала так — в двух ячейках стоят цифры больше 300 (330, 350).

Часто работаете со средневзвешенными величинами? Часто рассчитываете ожидаемые значения (expected values)? Значит часто считаете сумму произведений. Если вы еще не пользуетесь функцией SUMPRODUCT (СУММПРОИЗВ), то она вам понравится!

Стыдно признаться, но еще совсем недавно, если мне надо было посчитать сумму произведений, я построчно считал произведения, а потом их суммировал. А потом я нашел функцию SUMPRODUCT (СУММПРОИЗВ). Она суммирует произведения двух диапазонов.

Синтаксис : SUMPRODUCT(array1;array2;[array3]. )

array1, 2, 3. — диапазоны данных, значения из которых будут перемножаться поячеечно. Они обязательно должны быть одного размера.

Пример: расчет средневзвешенной

В данном примере рассчитывается средняя сумма расходов на рекламу на категорию клиентов, взвешенная на обороты по категориям.

Как рассчитать средневзвешенную? Математически для этого построчно умножаются обороты на расходы на рекламу, суммируем полученные произведения и делим полученный результат на сумму оборотов.

Как работает формула SUMPRODUCT (СУММПРОИЗВ)

Рассмотрим на примере вверху.

Функция SUMPRODUCT(C2:C3;D2:D3) делает следующее:

(1) взять первое значение из диапазона C2:C3 (то есть 1,000,000.00), умножить его на первое значение из диапазона D2:D3 (то есть 100,000.00), произведение запомнить (100,000,000,000.00)

(2) взять второе значение из диапазона C2:C3 (то есть 2,500,000.00), умножить его на второе значение из диапазона D2:D3 (то есть 25,000.00), произведение сложить с сумой всех предыдущих произведений (в данном случае с 100,000,000,000.00), получается 162,500,000,000.00.

(3) если бы диапазоны были длиннее, то второй шаг бы повторялся до конца диапазонов.

ВОЛШЕБСТВО СУММПРОИЗВ ИЛИ САМАЯ ПОЛЕЗНАЯ ФОРМУЛА EXCEL ДЛЯ ЭКОНОМИСТОВ

Функция СУММПРОИЗВ — без преувеличения одна из самых полезных в списке функций Excel. Если бы ее не было, таблицы Excel были бы совсем другим программным продуктом.

Когда я только-только начинал изучать Excel, эта функция показалась мне совершенно бесполезной, потому что получить такой же результат (а именно сумму произведений элементов массивов) я мог и другими способами, тем более, что сначала мне редко приходилось производить какие-то сложные вычисления. Поэтому я надолго забыл про эту ненужную, как мне казалось, функцию.

К счастью, со временем мое отношение к ней поменялось, и сейчас я объясню, почему.

Обратите внимание на следующий рисунок:

Простой пример использования СУММПРОИЗВ

Столбец D (Объем) — это произведение первых трех столбцов. В ячейке D2 содержится формула =A2*B2*C2, она же скопирована вниз, до ячейки D10. Единственное предназначение столбца D в данном примере — это расчет промежуточных значений, которые потом нужно сложить — это сделано в ячейке D11. Иногда введение столбца с промежуточными значениями оправдано, но иногда нужно всего лишь получить окончательный результат, и мы можем получить его с помощью функции СУММПРОИЗВ. Обратите внимание на формулу в строке формул — она возвращает точно такой же результат.

Исходя из этого примера стоит отметить два момента:

Во-первых, функция СУММПРОИЗВ перемножает между собой значения, находящиеся на одной и той же позиции в разных массивах, а после выполнения всех операций умножения складывает их результаты. В примере массивы были представлены в виде столбцов, но они с равным успехом могли бы быть строками или прямоугольными диапазонами любых размеров. Они также могли бы быть константами массивов (т.е., например, ), или именованными диапазонами, или результатом логических операций, а также смесью из любых функций Excel. Единственное требование к диапазонам заключается в том, что они должны быть прямоугольными, непрерывными и одинаковыми по размеру (по каждому измерению).
Во-вторых, в этом простом примере я использовал данные из трех столбцов (массивов). В функции СУММПРОИЗВ можно использовать от одного до 30 массивов, поскольку 30 — это максимальное количество аргументов функции Excel.

Итак, СУММПРОИЗВ предназначена для суммирования произведений одинаково расположенных элементов разных массивов. В чем же ее исключительная полезность? В том, что ее можно использовать для анализа и обработки данных не совсем очевидным образом.

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

Исходные данные для примера СУММПРОИЗВ

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

Допустим, нам нужно вычислить общую выручку в рублях от продаж самолетов по всему миру. Для этого нужно перемножить значения столбцов Цена, Количество и Курс в тех строках, в которых Продукт = Самолет. Вот 9 возможных способов сделать это с помощью функции СУММПРОИЗВ:

=СУММПРОИЗВ((Количество*Цена*Курс); —(Продукт= «Самолет» ))

=СУММПРОИЗВ((Количество*Цена*Курс); 1 *(Продукт= «Самолет» ))

=СУММПРОИЗВ((Количество*Цена*Курс); (Продукт= «Самолет» )* 1 )

=СУММПРОИЗВ((Количество*Цена*Курс); (Продукт= «Самолет» )+ 0 )

=СУММПРОИЗВ((Количество*Цена*Курс); (Продукт= «Самолет» )^ 1 )

=СУММПРОИЗВ((Количество*Цена*Курс); ЗНАК(Продукт= «Самолет» ))

=СУММПРОИЗВ((Количество*Цена*Курс); Ч(Продукт= «Самолет» ))

=СУММПРОИЗВ((Количество*Цена*Курс); (Продукт= «Самолет» )*ИСТИНА)

Эта формула требует некоторых пояснений.

Прежде всего, я присвоил имена столбцам таблицы, чтобы формулы легче читались и выглядели аккуратней. Вместо имен можно было бы использовать ссылки вида Таблица1[Количество] или даже ссылки на конкретные ячейки, например, C2:C40 вместо «Продукт», на результат это никак не повлияет. Порядок аргументов также не имеет значения.

У первых восьми вариантов есть две общие черты: они используют два аргумента, разделенных точкой с запятой, и каждый из них так или иначе трансформирует результат логической проверки Продукт=»Самолет» в числовой эквивалент, то есть в 1 или 0. Если не провести такое преобразование, функция СУММПРОИЗВ вернет 0. Двойной минус — это самый быстрый из предложенных способов. Подробнее о таком преобразовании — в следующей статье.

Последний вариант предпочтительней остальных. Все вычисления происходят в рамках одного аргумента, поэтому можно использовать столько массивов, сколько хочется, преодолев ограничение Excel на 30 аргументов функции. Более того, преобразовывать логические значения в числовые не нужно, потому что Excel делает это автоматически во время операции умножения. Но есть и один минус — этот вариант незначительно медленнее остальных. Это расплата за большую гибкость в построении логических выражений. Точнее говоря, за возможность комбинировать условия с помощью оператора ИЛИ.

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

=СУММПРОИЗВ((Количество*Цена*Курс)*(Продукт= «Самолет» )*((Валюта= «EUR» )+(Валюта= «USD» )))

При создании сложных логических проверок нужно помнить, что операция сложения эквивалентна логическому оператору ИЛИ, а операция умножения — оператору И. То есть приведенную выше формулу следует понимать как указание перемножить значения столбцов Количество, Цена и Курс для строк в которых Продукт=»Самолет» И Валюта=»EUR» ИЛИ «USD».

Такой синтаксис функции СУММПРОИЗВ позволяет усложнять ее до бесконечности, используя любые, сколь угодно сложные логические проверки. А в качестве бонуса этот синтаксис помогает отлавливать ошибки, например, в том случае, если вместо числовых значений в исходных массивах присутствует текст. Синтаксис с несколькими аргументами превращает текст в 0 и в итоге выдает неверный результат вычислений, а синтаксис с одним аргументом возвращает ошибку. Мне кажется, что явное указание на ошибку в данных гораздо приятнее, чем неправильные вычисления.

Продолжим пример. Давайте рассчитаем среднюю стоимость одной яхты в Африке и Австралии в рублях. Общую сумму продаж яхт найдем с помощью формулы

=СУММПРОИЗВ((Количество*Цена*Курс)*(Продукт= «Яхта» )*((Регион= «Африка» )+(Регион= «Австралия» )))

Количество проданных яхт определим формулой:

=СУММПРОИЗВ((Количество)*(Продукт= «Яхта» )*((Регион= «Африка» )+(Регион= «Австралия» )))

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