Что быстрее суммеслимн или суммпроизв
Эффективная работа в MS Office
Экономия 5 минут в час за счет более продуктивной работы дает за год экономию в 4 рабочие недели
Сравнение производительности формул массива с обычными формулами
Формулы массива в Excel — это замечательный инструмент, который позволяет выполнить практически любую задачу. Поиск склеенных значений, возврат второго по величине значения, специализированные функции — это лишь малая часть о того, что можно с ними делать.
Как только пользователи знакомятся с ними — они говорят «Вау, как здорово!» и пытаются их применить практически везде.
Посчитать сумму заказа — легко с формулами массива!
Посчитать сумму с учетом скидки — и тут пригодятся нам формулу массива
Посчитать накопленный итог — ну тут уж точно без массивов не обойтись..
… А спустя какое-то время удивляются почему файл пересчитывается по несколько секунд или, что еще хуже, десятков секунд.
А причина проста — если есть специальная функция, то лучше использовать ее. СУММЕСЛИ(), СЧЕТЕСЛИ(), СУММПРОИЗВ() и прочие замечательные функции были введены в Excel не просто так — они реально работают быстрее, чем их аналоги с использованием формул массива.
Приведу пример на цифрах. Возьмем 1 миллион строк и посчитаем по нему итоговое значение — так мы можем узнать разницу производительности уже на основании одной формулы.
В первом случае определим сумму по менеджеру через функцию СУММЕСЛИ(), а во втором случае — используя формулу массива СУММ(ЕСЛИ()).
Для измерения производительности будем простенький макрос на VBA
Результат можно увидеть в окне Immediate — разница в производительности от 1,5 раз. И, естественно, при увеличении количества условий, формулы массива сдают все больше — даже при 2х критериях производительность уже отличается в 3 раза.
Думаю, не нужно объяснять — если мы попробовали бы использовать на миллион строк формулу массива в каждой строчке, то она бы «повесила» Excel — формула в каждой строчке, которая вычисляется 0,3 секунды…
Мораль — каждый инструмент нужно применять с умом. Не следует каждую задачу решать с помощью формул массива — если есть аналог на функциях, то лучше использовать его
Самый быстрый ВПР
Если в ваших таблицах всего лишь несколько десятков строк, то, скорее всего, эта статья не будет для вас актуальной. На таких небольших объемах данных любой способ будет работать достаточно шустро, чтобы вы этого не замечали. Если же число строк в ваших списках измеряется тысячами, да и самих таблиц не одна-две, то время мучительного ожидания на пересчете формул в Excel может доходить до нескольких минут.
Когда я писал свою первую книжку пять лет назад, то уже делал сравнительный скоростной тест различных способов поиска и подстановки данных функциями ВПР, ИНДЕКС+ПОИСКПОЗ, СУММЕСЛИ и др. С тех пор сменилось три версии Office, появились надстройки Power Query и Power Pivot, кардинально изменившие весь процесс работы с данными. А в прошлом году ещё и обновился вычислительный движок Excel, получив поддержку динамических массивов и новые функции ПРОСМОТРХ, ФИЛЬТР и т.п.
Подопытный кролик
Тест будем проводить на следующем примере:
Способ 1. ВПР
Время вычисления = 4,3 сек.
Способ 2. ВПР с выделением столбцов целиком
В старых версиях Excel такое выделение не сильно влияло на скорость вычислений, но сейчас (неожиданно для меня, признаюсь) результат получился в разы хуже предыдущего.
Время вычисления = 14,5 сек.
Способ 3. ИНДЕКС и ПОИСКПОЗ
Формула выходит чуть сложнее, но, при этом имеет несколько ощутимых преимуществ перед классической ВПР, а именно:
По скорости, однако же, этот способ проигрывает ВПР почти в два раза:
Время вычисления = 7,8 сек.
Если же, вдобавок, полениться и выделять не ограниченные диапазоны, а столбцы целиком:
. то результат получается совсем печальный:
Время вычисления = 28,5 сек.
28 секунд, Карл! В 6 раз медленнее ВПР!
Способ 4. СУММЕСЛИ
Время вычисления = 12,8 сек.
При выделении столбцов целиком, т.е. использовании формулы вида =СУММЕСЛИ( G:G ; B2 ; H:H ) всё ещё хуже:
Время вычисления = 41,7 сек.
Это самый плохой результат в нашем тесте.
Способ 5. СУММПРОИЗВ
Этот подход сейчас встречается не часто, но всё ещё достаточно регулярно. Обычно так любят извращаться пользователи старой школы, ещё хорошо помнящие те времена, когда в Excel было всего 255 столбцов и 56 цветов 🙂
Время вычисления = 11,8 сек.
Способ 6. ПРОСМОТР
На первый взгляд всё выглядит очень удобно и логично, но всю картину портят два неочевидных момента:
При работе с неидеальными данными в реальном мире это гарантированно создаст проблемы, как вы понимаете.
Скорость же вычислений у функции ПРОСМОТР (LOOKUP) весьма приличная:
Время вычисления = 7,6 сек.
Способ 7. Новая функция ПРОСМОТРХ
Эта функция пришла с одним из недавних обновлений пока только пользователям Office 365 и пока отсутствует во всех остальных версиях (Excel 2010, 2013, 2016, 2019). По сравнению с классической ВПР у этой функции есть масса преимуществ (упрощенный синтаксис, возможность искать не только сверху-вниз, возможность сразу задать значение вместо #Н/Д и т.д.) Формула для решения нашей задачи будет выглядеть в этом случае так:
Время вычисления = 7,6 сек.
Почти в два раза медленнее, чем у ВПР, вместо которой Microsoft предлагает теперь использовать ПРОСМОТРХ. Жаль.
И, опять же, если полениться и выделить диапазоны в прайс-листе целыми столбцами:
. то скорость падает до совершенно неприличных уже значений:
Время вычисления = 28,3 сек.
А если на динамических массивах?
Прошлогоднее (осень 2019) обновление вычислительного движка Microsoft Excel добавило ему поддержку динамических массивов (Dynamic Arrays), о которых я уже писал. Это принципиально новый подход к работе с данными, который можно использовать почти с любыми классическими функциями Excel. На примере ВПР это будет выглядеть так:
Разница с классическим вариантом в том, что первым аргументом ВПР здесь выступает не одно искомое значение (а формулу потом нужно копировать вниз на остальные строки), а сразу весь массив из полумиллиона грузов B2:B500000, цены для которых мы хотим найти. Формула при этом сама распространяется вниз, занимая требуемое количество ячеек.
Время вычисления = 1 сек.
А вот олдскульные подходы на основе СУММПРОИЗВ и СУММЕСЛИ(МН) с динамическими массивами работать отказались 🙁
Что с умными таблицами?
Если предварительно превратить наши отгрузки и прайс в «умные» (по умолчанию они получат имена Таблица1 и Таблица2, соответственно), то формула с той же ВПР будет выглядеть как:
Жирным плюсом такого подхода будет возможность легко добавлять данные в наши таблицы в будущем. При дописывании новых строк в отгрузки или к прайс-листу, наши «умные» таблицы будут растягиваться автоматически.
Скорость же, как выяснилось, тоже вырастает очень значительно и примерно равна скорости работы на динамических массивах:
Время вычисления = 1 сек.
У меня есть подозрение, что дело тут не в самих «умных» таблицах, а всё в том же обновлении вычислительного движка, т.к. на старых версиях Excel такого прироста в скорости на умных таблицах я не помню.
Бонус. Запрос Power Query
Время обновления = 8,2 сек.
Итоговая таблица и выводы
Если вы честно дочитали до этого места, то какие-то выводы, наверное, уже сделали самостоятельно. Если же пропустили все детали и сразу перешли к итогам, то вот вам общая результирующая таблица по скорости всех методов:
Само-собой, у каждого из нас свои предпочтения, задачи и тараканы, но для себя я сформулировал выводы после этого тестирования так:
В этом руководстве объясняется различие между функциями СУММЕСЛИ (SUMIF) и СУММЕСЛИМН (SUMIFS) с точки зрения их синтаксиса и использования, а также приводятся примеры формул для суммирования значений с несколькими критериями в Excel 2016, 2013, 2010, 2007, 2003 и ниже.
Как известно, Microsoft Excel предоставляет множество функций для выполнения различных расчетов с данными. Мы уже рассмотрели СУММЕСЛИ, которая суммирует числа, соответствующие указанным критериям. Теперь пришло время перейти к расширенной версии этой функции СУММЕСЛИМН, которая позволяет найти сумму по нескольким условиям.
Как работает СУММЕСЛИМН?
При помощи СУММЕСЛИМН можно найти сумму величин, для которых есть много условий. Она появилась впервые в MS Excel 2007, поэтому вы можете использовать ее во всех современных версиях программы.
По сравнению с СУММЕСЛИ, синтаксис СУММЕСЛИМН немного сложнее:
СУММЕСЛИМН(диапазон_суммирования, диапазон_условия1, условие1, [диапазон_условия2, условие2],…)
Первые 3 аргумента являются обязательными, а вот дополнительные диапазоны и связанные с ними условия являются необязательными.
условие1— обязательное первое условие, которое должно быть выполнено. Вы можете предоставить его в виде числа, логического выражения, ссылки, текста или другой функции Excel. Например, вы можете использовать такие критерии, как 10, «> = 10», A1, «яблоко» или СЕГОДНЯ().
Важно! Функция СУММЕСЛИМН работает с логикой «И». Это означает, что число в диапазоне суммирования учитывается, только если оно удовлетворяет всем указанным критериям (все требования соблюдаются для этой ячейки).
1. Порядок аргументов
Аргументы применяются по-разному. В частности, диапазон_сумирования является 1-м параметром в СУММЕСЛИ, но является третьим в СУММЕСЛИМН.
На первый взгляд может показаться, что Microsoft намеренно усложняет процесс обучения для своих пользователей. Однако при ближайшем рассмотрении вы увидите причины этого. Дело в том, что этот диапазон является необязательным в СУММЕСЛИ. Если вы его опустите, то нет никаких проблем, ваша формула будет складывать в диапазоне поиска (первый параметр).
В СУММЕСЛИМН он, напротив, очень важен и обязателен, и поэтому и стоит первым. Вероятно, ребята из Microsoft подумали, что после добавления 10- й или 100- й пары диапазон/критерий кто-то может забыть указать диапазон для суммирования:)
2. Диапазон суммирования и область критериев должны быть одинакового размера
В функции СУММЕСЛИ эти аргументы не обязательно должны иметь одинаковую размерность. Достаточно указать начальную точку. В СУММЕСЛИМН они должны содержать одинаковое количество строк и столбцов.
Выражение =СУММЕСЛИМН(E2:E21;C2:C21;I2;F2:F22;I3) вернет сообщение об ошибке #ЗНАЧ!, так как второй параметр поиска (F2:F22) не совпадает по размеру с остальными (E2:E21) и (C2:C21).
Хорошо, хватит стратегии (т.е. теории), давайте перейдем к тактике (к примерам).
Суммирование с множеством условий.
Имеются данные о заказах и продаже шоколада. Подсчитаем итог совершённых продаж по молочному шоколаду. То есть, у нас два требования: должно совпадать наименование товара и в колонке «Выполнен» должно быть указано «Да».
Первым аргументом мы указываем диапазон суммирования E2:E21, а затем попарно – диапазон условия и само условие для него.
В C2:C21 будем искать слово «молочный» с любым его вхождением. То есть, до и после него могут быть еще любые другие символы.
В F2:F21 ищем «Да», то есть отметку о том, что заказ выполнен.
Если ОБА эти требования выполняются, то такой заказ нам подходит, и его стоимость мы учтём.
Как видите, у нас найдено 2 совпадения, в которых был продан молочный шоколад.
Использование операторов сравнения.
Рассчитаем по покупателю «Красный» стоимость заказов, в которых было более 100 единиц товара. Как видим, здесь нужно использовать и текстовый, и числовой критерий.
Критерии можно записать в саму формулу, и выглядеть это будет так:
Но более рационально использовать ссылки, как это и сделано на рисунке:
Примечание. Обратите внимание, что в формулах логические выражения с операторами сравнения всегда должны быть заключены в двойные кавычки («»).
Синтаксис, а также работа с числами, текстом и датами у этой функции точно такие же, как и СУММЕСЛИ. Поэтому рекомендую обратиться к нашему предыдущему материалу о условном суммировании.
А как еще можно решить нашу задачу?
Способ 2. Используем функцию СУММПРОИЗВ.
Разберем подробнее, как работает СУММПРОИЗВ():
Результатом вычисления B2:B21=$I$12 является массив
ИСТИНА означает соответствие кода покупателя условию, т.е. слову Красный. Массив этот можно увидеть, выделив в строке формул B2:B21=$I$12, а затем нажав F9.
И в результате логический массив превратится в массив чисел <0:1:0:0:0:0:0:0:0:0:0:0:1:0:0:0:1:0:0:0>.
Результатом вычисления D2:D21>I13 является массив
ИСТИНА соответствует ограничению «количество больше 100». Здесь мы также применяем двойное отрицание, чтобы преобразовать логические переменные в числа.
Результатом поэлементного умножения этих трех массивов является <0:23210:0:0:0:0:0:0:0:0:0:0:0:0:0:0:15840:0:0:0>. Суммируем эти произведения и получаем 39050.
Способ 3. Формула массива.
И еще один вариант расчета – применим формулу массива. В I14 запишем:
Не забудьте в конце нажать комбинацию клавиш CTRL+SHIFT+ENTER, чтобы обозначить это выражение как формулу массива. Фигурные скобки в начале и в конце программа добавит автоматически. Вновь получим результат 39050.
Способ 4. Автофильтр.
Еще один альтернативный вариант – применение автофильтра. Для этого преобразуйте диапазон данных A1:F21 в «умную» таблицу. Напомню, что для этого в меню «Главная» выберите «Форматировать как таблицу». После этого добавьте в нее строку итогов (вкладка «Конструктор») и установите необходимые фильтры.
Без всяких формул итог по отфильтрованным строкам будет определён.
Как СУММЕСЛИМН работает с датами?
Если вы хотите отобрать и сложить какие-то показатели в определенном временном интервале на основе текущей даты, используйте функцию СЕГОДНЯ() в ваших ограничениях, как это показано ниже.
Следующая формула суммирует числа в столбце D, если соответствующая дата в столбце А попадает в последние 7 дней, включая сегодняшний день (предполагается, что сегодня 7 февраля):
При анализе отчетов и других данных вам часто может потребоваться суммировать данные, соответствующие пустым или непустым клеткам таблицы.
А теперь давайте посмотрим, как вы можете использовать формулу СУММЕСЛИМН с «пустыми» и «непустыми» ячейками для реальных данных.
По покупателю «Красный» рассчитаем количество товара в невыполненных заказах. Для этого в столбце B ищем соответствующее название клиента, а в F – пустую ячейку. Если оба требования совпадают, складываем количество товара из столбца D.
Каждое из этих выражений дает верный результат – 144 единицы в заказе от 4 февраля.
Сумма нескольких условий.
А теперь давайте рассчитаем общую стоимость выполненных заказов по двум товарам.
Если мы просто добавим второй критерий в I3, и вместо I2 используем область I2:I3, то расчет будет неверным, поскольку в C2:C21 будем искать товар, в названии которого есть И «черный», И «молочный» одновременно. Ведь таких просто нет.
Поэтому первый вариант расчета таков:
Просто складываем выполненные заказы сначала по первому, а затем по второму наименованию.
Второй вариант: используем элемент массива критериев и функцию СУММ.
Как видите, результаты получены одинаковые. Выбирайте способ, который будет для вас проще и понятнее.
Глава 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
Основные свойства функции СУММПРОИЗВ:
Теперь давайте рассмотрим кучу примеров использования СУММПРОИЗВ.
На рис. 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. Умножение поможет справиться с массивами разных размеров
Вы можете обойти требование о том, что массивы в аргументах функции СУММПРОИЗВ должны иметь одинаковые размеры, путем умножения диапазонов в одном аргументе, как показано на рис. 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 есть функции БДСУММ и БСЧЁТ, которые также могут осуществлять вычисления с несколькими условиями. Эти четыре функции зачастую более эффективны, чем СУММПРОИЗВ. Так почему же вы должны изучать применение СУММПРОИЗВ для целей выборки с несколькими критериями? Вот некоторые из этих причин:
Прежде чем взглянуть на использование СУММПРОИЗВ в расчетах с несколькими критериями, давайте рассмотрим две важные альтернативные формулы, чтобы помочь определить верный контекст для применения СУММПРОИЗВ. Мы предполагаем, что вы хотите решить задачу с помощью формулы, а не сводной таблицы.
Рассмотрим исходные данные, включающие имена сотрудников, номера проектов и время, потраченное на участие в проекте (рис. 10.9–10.12). Цель – определить суммарное время, потраченное сотрудником Kip на Проект 2 (это И критерий, так как оба условия должны быть выполнены, чтобы включить время в суммирование). Как показано на рис. 10.9 первый набор формул использует СУММЕСЛИМН и СЧЁТЕСЛИМН. Поскольку большинство пользователей знакомились с Excel до 2007 года, они вполне могли пропустить появление новых функций в версии 2007, и продолжают по старинке применять СУММПРОИЗВ, БДСУММ и БСЧЁТ. В то же время, для подобных целей СУММЕСЛИМН и СЧЁТЕСЛИМН более эффективны.
Рис. 10.9. В Excel 2007 (и более поздней версии) СУММЕСЛИМН и СЧЁТЕСЛИМН – лучший выбор
СУММЕСЛИМН и СЧЁТЕСЛИМН обладают следующими преимуществами:
На рис. 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. СУММПРОИЗВ позволяет копировать формулы
Чтобы понять, зачем нужно двойное вычитание (отрицание) в СУММПРОИЗВ при вычислении с несколькими критериями выборки, необходимо вспомнить, что:
Давайте посмотрим на рис. 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.23–10.26).
Рис. 10.23. СУММЕСЛИМН суммирует время звонков Sioux в период между 7 и 13 октября; условия вписаны в ячейки А2 и А3
Рис. 10.24. Аналогично, но условия прописаны в самой формуле (в ячейке А5); А2 и А3 содержат только данные; эта методика полезна, если данные из А2 и А3 используются в других местах
Рис. 10.25. БДСУММ требует, чтобы операторы сравнения были помещены в ячейку, а также, чтобы заголовки в таблице и в области критериев совпадали; БДСУММ менее гибкая, чем СУММЕСЛИМН
Рис. 10.26. СУММПРОИЗВ требует, чтобы операторы сравнения были размещены в аргументе массив1, массив2 и т.д.; именно с этим связано большее время работы этой функции по сравнению, например, с СУММЕСЛИМН
Далее будет рассмотрено несколько примеров, когда вычисления можно выполнить только с использованием СУММЕСЛИМН, СЧЁТЕСЛИМН и т.п., а также обратные ситуации, когда единственное решение – использование СУММПРОИЗВ.
Ссылка на книгу: СУММЕСЛИМН, СЧЁТЕСЛИМН и т.п. не справляются; используем СУММПРОИЗВ. Функции СУММЕСЛИМН, СЧЁТЕСЛИМН и т.п. содержат аргументы диапазон_суммирования и диапазон_условия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 формате)
Рис. 10.32. Функция ТЕКСТ позволяет представить даты в требуемом формате
Чтобы проверить, что возвращает функция ТЕКСТ, выделите ее целиком и нажмите F9. На рис. 10.34 показана часть массива (по-прежнему, из 701 элемента), одна из дат » Пт 13 » выделена.
Рис. 10.34. Массив, возвращаемый функцией ТЕКСТ, показывает текстовые строки, содержащие день недели номер дня в месяце
Далее вы должны преобразовать массив текстовых строк в массив логических значений, путем сравнения массива и текстовой строки » Пт 13 » (рис. 10.35).
Рис. 10.35. Оператор сравнения задает вопрос: «есть ли день равный » Пт 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. Использование функции ЕСЛИ внутри СУММПРОИЗВ может привести к неоднозначности
Основные аспекты использования функции СУММПРОИЗВ: