Глава 10. Удивительные функции СУММПРОИЗВ и СУММЕСЛИМН

Это глава из книги: Майкл Гирвин. Ctrl+Shift+Enter. Освоение формул массива в Excel.

Предыдущая глава                                Оглавление                                 Следующая глава

Функция СУММПРОИЗВ имеет так много удивительных применений в Microsoft Excel, что ей не зазорно посвятить целую главу. Прелесть этой функции в том, что она без применения Ctrl+Shift+Enter способна:

• перемножить два и более массива и сложить результаты умножения;
• сложить элементы одного массива.

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

Вспомним примеры использования СУММПРОИЗВ, рассмотренные в предыдущих главах:

Рис. 2.14 – сумма разностей двух столбцов =СУММПРОИЗВ(D3:D6-C3:C6)
Рис. 3.4 – сумма произведений столбца на константу =СУММПРОИЗВ(B2:B5*(1-B7))
Рис. 6.6 – общее число символов в колонке =СУММПРОИЗВ(ДЛСТР(A2:A6))
Рис. 6.11 – сумма затрат, выбранных из таблицы просмотра функцией СУММЕСЛИ (для неотсортированных исходных данных) =СУММПРОИЗВ(СУММЕСЛИ(Е4:Е6,B3:В7,F4:F6))
Рис. 6.12 – (подобно рис. 6.11), но для отсортированных данных =СУММПРОИЗВ(ПРОСМОТР(B3:B7,Е4:F6))
Рис. 7.18 – сумма n наибольших значений в диапазоне В2:В8, где n размещено в ячейке D3 =СУММПРОИЗВ(НАИБОЛЬШИЙ(B2:B8;СТРОКА(ДВССЫЛ("1:"&D3))))
Рис. 7.33 – сумма затрат из некоторых столбцов (заданных массивом констант), выбранных по определенному товару функцией ВПР =СУММПРОИЗВ(ВПР(A7;A2:H4;{2;4;5;7;8}))

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

Рис. 10.1. Поэлементное умножение двух диапазонов одинаковых размеров с последующим суммированием результатов умножения

Скачать заметку в формате Word или pdf, примеры и ExternalData в формате Excel2013

Основные свойства функции СУММПРОИЗВ:

1. СУММПРОИЗВ обрабатывает два массива или более; функция сначала попарно перемножает элементы массивов, а затем суммирует результаты умножения.
2. Число аргументов функции от 1 до 255; аргументы называются массив1, массив2 и так далее; массивы должны иметь одинаковую размерность, например, 1*3 и 1*3, 4*1 и 4*1, 2*4 и 2*4.
3. Функция интерпретирует нечисловые значения как нулевые.
4. Аргументы функции поддерживают операции с массивами, а ввод функции не требует нажатия Ctrl+Shift+Enter.
5. Если массивы имеют разную размерность и их нужно умножить, вы можете использовать оператор умножения внутри одного аргумента функции (ниже будет показано, как это сделать). Если массивы умножаются внутри одного аргумента, и хотя бы один из элементов массива не число, функция вернет ошибку #ЗНАЧ! Способность функции СУММПРОИЗВ интерпретировать нечисловые значения как нулевые распространяется только на ситуации, когда каждый массив помещен в отдельный аргумент. Обойти эту проблему можно с помощью функции МУМНОЖ, о чем будет рассказано в главе 18.
6. Функция может обработать единственный массив, помещенный в аргумент массив1; в этом случае просто выполнится суммирование элементов массива.
7. Если у вас есть массив логических величин и вы хотели бы использовать его в расчетах, вы должны предварительно преобразовать значения ИСТИНА и ЛОЖЬ в единицы и нули. Любые математические операции справятся с этой задачей, но я рекомендую использовать двойное отрицание, как самый быстрый метод вычисления.
8. Если у вас Excel 2007 или более поздняя версия и вы подсчитываете ячейки или суммируете их содержимое с использованием нескольких критериев, используйте функции СУММЕСЛИМН и СЧЁТЕСЛИМН, потому что они работают быстрее.
9. Функция СУММПРОИЗВ может использоваться в качестве замены СУММЕСЛИ, СЧЁТЕСЛИ, СУММЕСЛИМН и СЧЁТЕСЛИМН (подробнее см. ниже в этой главе).

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

Наиболее очевидное применение функции СУММПРОИЗВ – умножение двух или более массивов одинаковых размеров, с последующим их суммированием. Например, на рис. 10.1 показан ряд денежных знаков (банкнот и монет) и ряд их количеств. Цель – рассчитать размер банковского вклада. Действуя обычным образом, вы должны были бы создать формулу, которая умножает стоимость каждого денежного знака на их количество, а затем суммирует результаты; что-то типа: $100 * 22 + $50 * 0 + $20 * 50 + $10 * 22 + $5 * 25 + $1 * 38 + $0,50 * 0 + $0,25 * 40 + $0,10 * 50 + $0,05 * 0 + $0,01 * 50 = $3598,50. К счастью, функция СУММПРОИЗВ позволяет упростить эту формулу =СУММПРОИЗВ(B1:L1;B2:L2).

На рис. 10.2 показаны преимущества функции СУММПРОИЗВ над формулой массива на основе функции СУММ. СУММПРОИЗВ не требует нажатия Ctrl+Shift+Enter, и она справляется с ячейками с текстом, так как интерпретирует их как нулевые. Функция массива СУММА возвращает ошибку #ЗНАЧ!, потому что не может умножить текст на число. Рис. 10.3–10.5 показывают еще три примера умножения массивов одинаковых размеров с последующим суммированием.

Рис. 10.2. СУММПРОИЗВ не требует нажатия Ctrl+Shift+Enter, и справляется с ячейками с текстом, так как интерпретирует их как нулевые

Рис. 10.3. Расчет взвешенной оценки

Рис. 10.4. Расчет объема закупок на основе средневзвешенных данных

Рис. 10.5. Функция СУММПРОИЗВ может обрабатывать до 255 диапазонов; в этом примере умножаются 4 столбца

Примечание: если формула ссылается только на две ячейки, например, =СУММПРОИЗВ(A10;B10), и хотя бы одна их них пуста, функция вернет ошибку; если обе ячейки содержат числа (пусть и нули), ошибки не будет.

Умножение трех диапазонов различного размера с последующим суммированием результатов. На рис. 10.6 приведен пример из финансовой области. Вы оцениваете ожидаемую доходность портфеля из двух акций. Расчет учитывает вероятность экономического состояния рынка (B4:B6), вес каждой акции (C1, D1), и доходности отдельных акций (C4:D6). Если вы займетесь вычислениями вручную, это будет непросто: 0,15 * 0,6 * 0 + 0,7 * 0,6 * 0,06 + 0,15 * 0,6 * 0,1 + 0,15 * 0,4 * -0,15 + 0,7 * 0,4 * 0,05 + 0,15 * 0,4 * 0,2 = 0,0512.

Рис. 10.6. Умножение поможет справиться с массивами разных размеров

Вы можете обойти требование о том, что массивы в аргументах функции СУММПРОИЗВ должны иметь одинаковые размеры, путем умножения диапазонов в одном аргументе, как показано на рис. 10.6. Процесс вычисления показан на рис. 10.7. Вы помните, что аргумент массив1 функции СУММПРОИЗВ может обрабатывать массивы, поэтому нажатия Ctrl+Shift+Enter для ввода формулы не требуется. Обратите внимание, что перемножаемые массивы имеют не совсем произвольную размерность. Первый массив (В4:В6) – это как бы заголовок строк, второй массив (С1:D1) – заголовок столбцов, третий массив (С4:D6) – тело таблицы. Если бы вы попробовали формулу типа =СУММПРОИЗВ(B4:B6*C4:C5*D4:D6), вы получили бы ошибку #Н/Д! Формула попыталась бы сделать следующие вычисления: В4*С4*D4 + B5*C5*D5 + B6*Н/Д*D6, а поскольку третьего элемента во втором диапазоне нет, ничего бы не получилось …

Рис. 10.7. Процесс расчета в случае, если массивы имеют разный размер

Функцию СУММПРОИЗВ часто используют для суммирования результатов, возвращаемых каким-либо оператором массива, например, функцией ОКРУГЛ (рис. 10.8). Преимущество СУММПРОИЗВ над СУММ состоит в том, что первая не требует нажатия Ctrl+Shift+Enter. На рис. 10.8 показано, как можно получить сумму счета. Бухгалтерам знакома эта проблема: прежде чем суммировать подитоги по строкам, нужно сделать округление с точностью до копейки. Если такого округления не сделать, то сумма счета может не совпасть с суммой по отдельным строкам.

Рис. 10.8. Сумма счета равна сумме всех строк; в свою очередь сумма по строке получается округлением до копеек произведения цены на количество

Я пишу эту книгу в 2013 году. Начиная с 2007 г. в Excel появились функции СУММЕСЛИМН и СЧЁТЕСЛИМН, которые могут суммировать содержимое ячеек и считать количество ячеек с использованием нескольких критериев. Кроме того, уже давно в Excel есть функции БДСУММ и БСЧЁТ, которые также могут осуществлять вычисления с несколькими условиями. Эти четыре функции зачастую более эффективны, чем СУММПРОИЗВ. Так почему же вы должны изучать применение СУММПРОИЗВ для целей выборки с несколькими критериями? Вот некоторые из этих причин:

1. Многие до сих пор используют версию Excel 2003 (или более раннюю), в которой нет функций СУММЕСЛИМН и СЧЁТЕСЛИМН, а использование БДСУММ и БСЧЁТ затруднено, так как они требуют соответствующей подготовки исходных данных и не позволяют напрямую скопировать формулы вдоль столбца (строки).
2. В Excel 2007 или более поздней версии вы можете столкнуться с электронной таблицей, подготовленной ранее, и в которой была использована СУММПРОИЗВ.
3. СУММПРОИЗВ может выполнить некоторые расчеты (с несколькими условиями), перед которыми СУММЕСЛИМН и СЧЁТЕСЛИМН спасуют.

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

Рассмотрим исходные данные, включающие имена сотрудников, номера проектов и время, потраченное на участие в проекте (рис. 10.9–10.12). Цель – определить суммарное время, потраченное сотрудником Kip на Проект 2 (это И критерий, так как оба условия должны быть выполнены, чтобы включить время в суммирование). Как показано на рис. 10.9 первый набор формул использует СУММЕСЛИМН и СЧЁТЕСЛИМН. Поскольку большинство пользователей знакомились с Excel до 2007 года, они вполне могли пропустить появление новых функций в версии 2007, и продолжают по старинке применять СУММПРОИЗВ, БДСУММ и БСЧЁТ. В то же время, для подобных целей СУММЕСЛИМН и СЧЁТЕСЛИМН более эффективны.

Рис. 10.9. В Excel 2007 (и более поздней версии) СУММЕСЛИМН и СЧЁТЕСЛИМН – лучший выбор

СУММЕСЛИМН и СЧЁТЕСЛИМН обладают следующими преимуществами:

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

На рис. 10.10 показаны примеры БДСУММ и БСЧЁТ. Если вы используете Excel 2003 или более раннюю версию, и имена полей присутствуют, как в наборе данных, так и в области критериев, и вам не нужно копировать формулы из одной ячейки в другую, используйте функции базы данных. Во-первых, они работают быстрее, чем СУММПРОИЗВ; во-вторых, их проще создать, особенно если у вас много условий.

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

Рис. 10.10. В Excel 2003 (и более ранней версии) используйте БДСУММ и БСЧЁТ

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

Рис. 10.11. Возможно также использовать и СУММПРОИЗВ

На рис. 10.12 показано еще одно преимущество СУММПРОИЗВ над функциями базы данных (в Excel 2003) – возможность копирования формул. В этом примере вы суммируете выборки на основе двух критериев и помещаете результат в таблицу, основанную на именах сотрудников и номерах проектов. Вы создали формулу в ячейку F3 и скопировали ее в диапазон F3:G5.

Рис. 10.12. СУММПРОИЗВ позволяет копировать формулы

Чтобы понять, зачем нужно двойное вычитание (отрицание) в СУММПРОИЗВ при вычислении с несколькими критериями выборки, необходимо вспомнить, что:

• СУММПРОИЗВ не может распознать логические значения ИСТИНА и ЛОЖЬ; все нечисловые данные СУММПРОИЗВ интерпретирует как нули.
• Любые математические операции преобразует значение ИСТИНА в 1, а ЛОЖЬ – в 0.

Давайте посмотрим на рис. 10.13. Цель формулы в ячейке D2 – подсчет числа вхождений записи Kip в столбце А. Мы поместили оператор сравнения массива и числа (A2:A5=C2) в аргумент массив1 функции СУММПРОИЗВ. Если вы выделите этот оператор и нажмете F9, то увидите результирующий массив, включающий значения ИСТИНА и ЛОЖЬ (рис. 10.14). Вы еще не забыли, что для отмены расчета нужно нажать Ctrl+Z?

Рис. 10.13. Оператор массива (A2:A5=C2) спрашивает: «равен ли какой-либо элемент Kip»?

Рис. 10.14. Оператор сравнения возвращает массив значений ИСТИНА и ЛОЖЬ; СУММПРОИЗВ интерпретирует все эти значения, как нулевые

Если ввести формулу, вы получите ноль, потому что СУММПРОИЗВ воспринимает логические значения в виде нулей (рис. 10.15). Тем не менее, если вы сможете преобразовать значение ИСТИНА в 1, а ЛОЖЬ – в 0, СУММПРОИЗВ осуществит суммирование, и вернет число единиц.

Рис. 10.15. СУММПРОИЗВ интерпретирует нечисловые данные как нули, поэтому сумма нулей дает ноль

Преобразовать логическое значение ИСТИНА в 1, а ЛОЖЬ – в 0 может любая математическая операция (рис. 10.16).

Рис. 10.16. Любые математические операции преобразуют логическое значение ИСТИНА в 1, а ЛОЖЬ – в 0

На рис. 10.17 показано, как использовать математические операции (или функцию Ч) в формуле массива СУММПРОИЗВ, чтобы подсчитать, сколько Kip, в столбце А. Чтобы увидеть процесс вычисления, например, по формуле [1], встаньте на ячейке D2, выделите целиком аргумент функции СУММПРОИЗВ и нажмите F9 (рис. 10.18). Наиболее часто используют двойное отрицание. Это связано, во-первых с тем, что оно работает чуть быстрее, а во-вторых, с тем, что простое отрицание занимает высокую позицию в списке очередности выполнения операторов в Excel (подробнее см. главу 1). Приоритет отрицания даже выше, чем возведение в степень.

Рис. 10.17. Шесть способов преобразования логических значений ИСТИНА и ЛОЖЬ в 1 и 0

Рис. 10.18. Как СУММПРОИЗВ интерпретирует логические значения ИСТИНА и ЛОЖЬ с помощью двойного отрицания

Теперь, когда вы убедились в том, что двойное отрицание является эффективным средством для преобразования значений ИСТИНА в 1 и ЛОЖЬ – в 0, давайте вернемся к формуле СУММПРОИЗВ с использованием двух критериев выборки (см. рис. 10.11), и подробно рассмотрим ее работу (рис. 10.19).

Рис. 10.19. Пошаговая работа формулы =СУММПРОИЗВ(--(A2:A7=E2);--(B2:B7=F2);C2:C7)

И критерии (логическое умножение) и СУММПРОИЗВ. Условные вычисления с помощью функции АГРЕГАТ (см. главу 4) и все примеры этой главы использовали И критерии: все условия должны быть соблюдены для того, чтобы включить пункт в подсчет количества или суммирование. Например, если у вас есть формула =СУММПРОИЗВ(--(A2:A7=E2);--(B2:B7=F2);C2:C7), будут суммироваться только те элементы диапазоне С2:С7, для которых и --(A2:A7=E2) и --(B2:B7=F2) вернут единицы. Когда СУММПРОИЗВ умножает два массива заполненные единицами и нулями на третий массив, заполненный числами, это называется логическим умножением.

Различия в синтаксисе операторов сравнения, используемых в функциях СУММЕСЛИМН, БДСУММ, СУММПРОИЗВ (рис. 10.23–10.26).

Рис. 10.23. СУММЕСЛИМН суммирует время звонков Sioux в период между 7 и 13 октября; условия вписаны в ячейки А2 и А3

Рис. 10.24. Аналогично, но условия прописаны в самой формуле (в ячейке А5); А2 и А3 содержат только данные; эта методика полезна, если данные из А2 и А3 используются в других местах

Рис. 10.25. БДСУММ требует, чтобы операторы сравнения были помещены в ячейку, а также, чтобы заголовки в таблице и в области критериев совпадали; БДСУММ менее гибкая, чем СУММЕСЛИМН

Рис. 10.26. СУММПРОИЗВ требует, чтобы операторы сравнения были размещены в аргументе массив1, массив2 и т.д.; именно с этим связано большее время работы этой функции по сравнению, например, с СУММЕСЛИМН

Итак:

• СУММЕСЛИ, СУММЕСЛИМН, СЧЁТЕСЛИ, СЧЁТЕСЛИМН: оператор сравнения может находиться, как в ячейке с критерием, так и внутри формулы
• БДСУММ, БСЧЁТ: оператор сравнения должен находиться в ячейке с критерием
• СУММПРОИЗВ: оператор сравнения следует поместить внутрь аргумента массив1…

Далее будет рассмотрено несколько примеров, когда вычисления можно выполнить только с использованием СУММЕСЛИМН, СЧЁТЕСЛИМН и т.п., а также обратные ситуации, когда единственное решение – использование СУММПРОИЗВ.

Ссылка на книгу: СУММЕСЛИМН, СЧЁТЕСЛИМН и т.п. не справляются; используем СУММПРОИЗВ. Функции СУММЕСЛИМН, СЧЁТЕСЛИМН и т.п. содержат аргументы диапазон_суммирования и диапазон_условия1. Эти аргументы не могут обрабатывать массивы ни при каких обстоятельствах. Когда вы используете в формуле ссылку на книгу, а затем закроете книгу с внешними данными, ссылка на книгу преобразуется в массив и вызывает ошибку #ЗНАЧЕН! Как показано на рис. 10.27, когда книга с внешними данными открыта, нет никаких проблем: функция СУММЕСЛИМН правильно вычисляет сумму – 41. Однако, как показано на рис. 10.28, когда книга с внешними данными закрыта, СУММЕСЛИМН дает ошибку. Аналогичная ошибка возникает, если вы используете ссылки на книгу в функциях, подобных СУММЕСЛИМН, например, в СЧЁТЕСЛИМН. В то же время СУММПРОИЗВ и при открытой книге с внешними данными (рис. 10.27), и при закрытой книге (рис. 10.28), работает без сбоев.

Рис. 10.27. Когда книга с внешними данными открыта, ошибки нет

Рис. 10.28. Когда книга с внешними данными закрыта, СУММЕСЛИМН возвращает ошибку, а СУММПРОИЗВ по-прежнему «в седле»

Примечание: создание ссылки на книгу в формулах аналогично добавлению обычный ссылки на ячейку. Разница лишь в том, что находясь в режиме редактирования формулы, вы можете кликнуть кнопку внешней книги внизу экрана на панели задач (или нажать Ctrl+Tab), чтобы перейти к внешней книге, а затем выбрать в ней нужный лист и диапазон ячеек. Кроме того, пока внешняя книга открыта, ссылка в формуле показывает только имя внешней книги и название листа. Когда внешняя книга закрыта, ссылка показывает полное имя файла и путь к нему (см. рис. 10.28; видно, что я сохранил файл на Dropbox).

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

Рассмотрим пример: сколько пятниц 13-е находится между двумя датами?

Примечание: издатель этой книги, Билл MrExcel Джелен, специально попросил меня включить в книгу этот пример, потому что это его любимая формула массива!

Как показано на рис. 10.29, цель формулы – найти и вернуть в ячейку единственное значение: количество «пятниц 13-е», находящееся между двумя датами. На первый взгляд это кажется практически невыполнимой задачей! Особенно, учитывая, что у вас нет полного список дат.

Рис. 10.29. Цель – подсчитать количество «пятниц 13-е», находящееся между двумя датами

К счастью, существует возможность создать прямо внутри формулы массив последовательных дат, основываясь на дате начала и дате окончания. В главе 7 (посвященной массивам констант) вы уже видели формулу, которая создает массив последовательных чисел (см. раздел динамический массив переменной длины из последовательных чисел). На рис. 10.30 показано, как можно использовать аналогичную формулу, чтобы создать массив последовательные дат в ячейке В4.

Рис. 10.30. Динамический массив переменной длины из последовательных чисел

Если выделить целиком формулу =СТРОКА(…) и нажать F9, можно будет увидеть результирующий массив, возвращаемый этой формулой. На рис. 10.31 показана небольшую часть этого массива; всего в нем 701 дата.

Рис. 10.31. Формула ДВССЫЛ() возвращает массив, заполненный датами (в присущем Excel формате)

Далее вам надо представить даты в «правильном» формате. Используйте для этого функцию ТЕКСТ, указав, например, формат "ДДД Д". В этом формате одновременно присутствует и день недели (ДДД) и день месяца (Д), например, Пт 13, Сб 14… Поместите функцию СТРОКА(ДВССЫЛ()) в аргумент значение функции ТЕКСТ (рис. 10.32).

Рис. 10.32. Функция ТЕКСТ позволяет представить даты в требуемом формате

Чтобы проверить, что возвращает функция ТЕКСТ, выделите ее целиком и нажмите F9. На рис. 10.34 показана часть массива (по-прежнему, из 701 элемента), одна из дат "Пт 13" выделена.

Рис. 10.34. Массив, возвращаемый функцией ТЕКСТ, показывает текстовые строки, содержащие день недели номер дня в месяце

Далее вы должны преобразовать массив текстовых строк в массив логических значений, путем сравнения массива и текстовой строки "Пт 13" (рис. 10.35).

Рис. 10.35. Оператор сравнения задает вопрос: «есть ли день равный "Пт 13"»?

На рис. 10.36 показана небольшая часть массива, возвращаемого оператором сравнения. Массив содержит 701 логическое значение. ИСТИНА соответствует дню "Пт 13"!

Рис. 10.36. ИСТИНА соответствует дню "Пт 13"

На рис. 10.37 показана распространенная ошибка, которую совершают многие пользователи Excel (включая меня) – попытка поместить формулу ТЕКСТ(…) в аргумент диапазон функции СЧЁТЕСЛИ. Что вполне логично, тем более что большинство аргументов функций Excel может обрабатывать операции с массивами (единственное рассмотренное до сих пор исключение относилось к аргументу искомое_значение функции ВПР). Ведь всё что вам нужно, это подсчитать количество значений ИСТИНА в массиве, возвращаемом формулой ТЕКСТ(). К сожалению, в этом месте подстерегает второе исключение: аргумент диапазон функции СЧЁТЕСЛИ также не поддерживает операции с массивами.

Рис. 10.37. Оператор массива не работает в аргументе диапазон функции СЧЁТЕСЛИ

Но как только вы попробуете ввести в ячейку формулу, изображенную на рис. 10.37, вы получите сообщение об ошибке. На рис. 10.39 приведено сообщение об ошибке характерное для Excel 2013; в других версиях, вы можете получить несколько иное сообщение.

Рис. 10.39. Размещая оператор массива в аргументе диапазон функции СЧЁТЕСЛИ, вы получите следующее не очень информативное сообщение об ошибке

В функциях СУММЕСЛИ, СЧЁТЕСЛИ, СРЗНАЧЕСЛИ, СУММЕСЛИМН, СЧЁТЕСЛИМН и СРЗНАЧЕСЛИМН аргументы диапазон и диапазон_условия не поддерживают операции с массивами. На помощь приходит функция СУММПРОИЗВ. Как показано на рис. 10.40 и 10.41, вы можете поместить оператор сравнения массивов в скобки, добавить перед ним двойное отрицание, и получить результат.

Рис. 10.40. СУММПРОИЗВ работает с операторами массивов

Рис. 10.41. Найдено три значения «пятница 13-е» между начальной и конечной датами

Функция ЕСЛИ внутри СУММПРОИЗВ. В главе 4 рассматривалось козырное правило функции ЕСЛИ: независимо от того, где в большой формуле размещена функция ЕСЛИ, формула массива всегда будет требовать нажатия Ctrl+Shift+Enter. Это означает, что если вы взяли функцию, которая может обрабатывать массив без нажатия Ctrl+Shift+Enter (например, АГРЕГАТ или СУММПРОИЗВ) и пометили внутрь функцию ЕСЛИ, то теперь формула будет требовать нажатия Ctrl+Shift+Enter.

Как показано на рис. 10.42, если вы используете ЕСЛИ внутри функции СУММПРОИЗВ, формула требует Ctrl+Shift+Enter. Формула в ячейке А5 была введена с помощью Enter что привело к ошибке #ЗНАЧ! Формула в ячейке A6 была введена с Ctrl+Shift+Enter и поэтому рассчитывает правильно. Проблема с использованием функции ЕСЛИ внутри СУММПРОИЗВ заключается в том, что пользователи электронной таблицы могут ошибочно думать, что аргумент массив функции СУММПРОИЗВ массив аргументов может обрабатывать оператор массива внутри функции ЕСЛИ. Чтобы избежать возможной двусмысленности, лучше использовать формулы, использованные в ячейках А10 и А11.

Рис. 10.42. Использование функции ЕСЛИ внутри СУММПРОИЗВ может привести к неоднозначности

Основные аспекты использования функции СУММПРОИЗВ:

• Умножает массивы одинаковой размерности, а затем суммирует результаты умножения.
• Суммирует результаты возвращаемые оператором массива без нажатия Ctrl+Shift+Enter.
• Может выполнять операции с массивами различной размерности; в этом случае оператор массива следует разместить в одном аргументе.
• Обрабатывает ссылки на внешнюю книгу, в то время как СУММЕСЛИМН и т.п. не могут этого.
• Работает с операторами массивов, в то время как аргументы диапазон и диапазон_условия функции СУММЕСЛИМН и т.п. этого не делают.
• Если у вас Excel 2007 (или более поздняя версия), для расчетов с несколькими критериями следует предпочесть СУММЕСЛИМН, СЧЁТЕСЛИМН и т.п.
• Если вы делаете расчеты с несколькими критериями и вам не нужно копировать формулу, наиболее эффективными могут быть функции базы данных.
• При использования функции ЕСЛИ внутри СУММПРОИЗВ, требуется нажатие Ctrl+Shift+Enter; чтобы не допускать двусмысленности, рекомендуется использовать альтернативную формулу.