SUMIFS может искать с учетом многих критериев сразу (до 127!), и находит поля, отвечающие сразу всем критериям. Формула SUMIFS впервые возникает только в Excel 2007, предыдущие версии ее не имеют. Для подсчета не суммы значений, а числа записей, можно использовать парную к SUMIFS формулу COUNTIFS. Она также присутствует только в Excel 2007 и 2010. Правила оформления вызовов имен и операторов там те же, что и для формулы SUMIF. 

Запрос 3. Каковы ежемесячные выплаты зарплаты только инженерам? 

Алгоритм 

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

Критерии

– Основной массив – Salary (C7)
– Критерий 1 – Start_period (D7)
– Критерий 2 – Type (E7) 
– Значение критерия 2 – Инженер

Формула: 

=SUMIFS(INDIRECT($C7),INDIRECT($D7),"<="&G$2,INDIRECT($E7),$F7)

=SUMIFS(

Условная сумма по нескольким критериям. 
 

INDIRECT($C7),

Сложить отвечающие критерию значения из массива, имя которого вписано в клетку С7 (Salary).
Колонка закреплена жестко (знак $) для последующего копирования. 

INDIRECT($D7),

Найти массив критериев выбора, в котором записаны периоды начала работы сотрудников. Формула найдет его по имени, которое вписано в ячейку D7 (Start_period).
Колонка закреплена жестко (знак $) для последующего копирования.
 

"<="&G$2,

Если сотрудник уже работает в компании, поле start_period его записи меньше или равно значению текущего периода (ячейка G2).
Ряд закреплен жестко (знак $) для последующего копирования. 
 

INDIRECT($E7),

Найти массив критериев выбора, в котором записаны типы сотрудников. Формула найдет его по имени, которое вписано в ячейку E7 (Type).
Колонка закреплена жестко (знак $) для последующего копирования.
 

$F7

Из массива Type будут выбраны только записи, у которых в поле Type стоит значение «Инженер» (ячейка F7).
Колонка закреплена жестко (знак $) для последующего копирования.

)

Формула завершена

Запрос 4. Сколько инженеров работают в компании в каждом из периодов? 

Алгоритм 

– Найти для данного периода общее число всех тех сотрудников, для которых значение Type равно «инженер». 

Критерии

– Основной массив – не нужен
– Критерий 1 – Start_period (D8)
– Критерий 2 – Type (E8) 
– Значение критерия 2 – Инженер

Формула: 

=COUNTIFS(INDIRECT($D8),"<="&G$2,INDIRECT($E8),$F8)

=COUNTIFS(

Условное сложение по нескольким критериям. 
 

INDIRECT($D8),

Найти массив критериев выбора, в котором записаны периоды начала работы сотрудников. Формула найдет его по имени, которое вписано в ячейку D8 (Start_period).
Колонка закреплена жестко (знак $) для последующего копирования. 

"<="&G$2,

Если сотрудник уже работает в компании, поле start_period его записи меньше или равно значению текущего периода (ячейка G2). 

Ряд закреплен жестко (знак $) для последующего копирования.
 

INDIRECT($E8),

Найти массив критериев выбора, в котором записаны типы сотрудников. Формула найдет его по имени, которое вписано в ячейку E8 (Type).
Колонка закреплена жестко (знак $) для последующего копирования. 
 

$F8

Из массива Type будут выбраны только записи, у которых в поле Type стоит значение «Инженер» (ячейка F8).
Колонка закреплена жестко (знак $) для последующего копирования.

)

Формула завершена

Запрос 4 (первый способ). Сколько инженеров поступило на работу в компанию в каждом из периодов? 

Алгоритм 

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

Критерии

– Основной массив – не нужен
– Критерий 1 – Start_period (D9)
– Критерий 2 – Type (E9) 
– Значение критерия 2 – Инженер

Формула: 

=COUNTIFS(INDIRECT($D9),G$2,INDIRECT($E9),$F9)

В результате этих упражнений должна получиться матрица с такими значениями:

 

SUMIFS и COUNTIFS не дают возможности использовать сложно-структурированные критерии. Во-первых, их единственной внутренней логической операцией является AND, и запросы критериев, включающие OR и NOT, они не могут обработать. Во-вторых, они могут манипулировать в качестве критериев только единичными численными значениями, и попытка обработать внутри них на соответствие критериям массив даст неустранимую ошибку. Следующие запросы находится за пределами их возможностей: 

– Сколько сотрудников, кроме инженеров и руководителей, работает во всех подразделениях компании?
– Сколько руководителей, не являющихся сотрудниками отдела разработки, работает в компании?
– Какова в данном периоде сумма выплат по бонусам, не связанным с первичным приемом на работу? 

Дополнительный довод в пользу отказа от SUMIFS и COUNTIFS в настоящее время еще в том, что использование Excel 2007 и 2010 создает проблемы с обратной совместимостью. Распространенность Office 2003 достаточно велика для того, чтобы риск того, что модель, созданная в версии 2007 или 2010, не заработала у кого-то из партнеров аналитика. 

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

– Использованием функции суммирования SUM как функции массива
– Использованием функции суммы произведений SUMPRODUCT

Мы рассмотрим второй способ, который требует меньше программных ресурсов и, одновременно, проще в написании.

Расходы на персонал: SUMPRODUCTS как альтернатива

SUMPRODUCT, как и SUM, принимает в качестве аргументов только массивы, которые перемножает между собой: все первые члены, все вторые, все третьи и так далее, и возвращает их сумму. Мы используем ее следующим образом. 
– Каждый из ее аргументов будет содержать результат проверки заданного массива на соответствующий критерий, с результатом примерного вида {0;1..1:0}, где 0 соответствует логическому «нет», а 1 логическому «да». 
– Последний массив будет содержать массив тех данных, сумму которые мы хотим получить на выходе (его последнее положение также облегчит инструментальную проверку формулы на логические ошибки). 
– Перемножение этих массивов обнулит значения всех записей, кроме тех, которые удовлетворили всем критериям. 
– Критерии можно настроить максимально гибко на основе почти любых логических конструкций.
Вначале рассмотрим, как посредством SUMPRODUCT воспроизвести уже сделанные запросы. 
Запрос 2 (второй способ). Какой бонус за переход выплачивается ежемесячно?
Алгоритм 
- Найти для данного периода общую сумму бонусов всех тех сотрудников, кто в данном периоде поступил на работу в компанию. 
Критерии
- Основной массив – Bonus (C10)
- Критерий 1 – Start_period (D10)
Формула: 

=SUMPRODUCT(INDIRECT($D10)=G$2,INDIRECT($C10))

Однако, если мы введем эту формулу, окажется, что она возвращает нулевые значения. Проблема в том, что выражение INDIRECT($D11)=G$2 выдает только значения TRUE или FALSE. Хотя им соответствуют логические 1 и 0, внутри Excel это не численные значения, и формула SUMPRODUCT не получит массива из нулей и единиц – как мы рассчитывали. Способов превратить TRUE и FALSE в 1 и 0 несколько, и простейший – это форсировать численный режим путем единичного оператора «минус». Двойной минус перед аргументом критерия восстановит корректность формулы. 
Корректно оформленная формула SUMPRODUCT будет выглядеть так: 

=SUMPRODUCT(--(INDIRECT($D11)=G$2),INDIRECT($C11))

=SUMPRODUCT(

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

--

Два единичных оператора «минус»:
— первый изменит знаки всех элементов массива, обратив их в числа,
— второй вернет все в исходное состояние, но уже в численном виде. 

INDIRECT($D11)

Найти массив критериев выбора, в котором записаны периоды начала работы сотрудников. Формула найдет его по имени, которое вписано в ячейку D11 (Start_period).Колонка закреплена жестко (знак $) для последующего копирования.
 

=G$2),

Сравнить полученные из массива периоды начала работы с текущим периодом (G2). В данном случае мы не можем опустить знак равенства, поскольку аргумент формулы – все выражение в целом.
Ряд закреплен жестко (знак $) для последующего копирования.
 

INDIRECT($C11)

Найти массив с суммами бонусов сотрудников. Формула найдет его по имени, которое вписано в ячейку С11 (Bonus).
Колонка закреплена жестко (знак $) для последующего копирования.

)

Формула завершена
Теперь воспроизведем запросы, которые позволят нам заменить использование функций SUMIFS и COUNTIFS.
Запрос 4 (второй способ). Сколько инженеров поступило на работу в компанию в каждом из периодов? 
Алгоритм 
- Найти для данного периода общее число всех тех сотрудников, кто в данном периоде поступил на работу в компанию и для которых значение Type равно «инженер». 
Критерии
- Основной массив – не нужен
- Критерий 1 – Start_period (D12)
- Критерий 2 – Type (E12) 
- Значение критерия 2 – Инженер
Формула: 

=SUMPRODUCT(--(INDIRECT($D12)<=G$2),--(INDIRECT($E12)=$F12))

=SUMPRODUCT(

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

--

Два единичных оператора «минус»:
— первый изменит знаки всех элементов массива, обратив их в числа,
— второй вернет все в исходное состояние, но уже в численном виде.

(INDIRECT($D12)

Найти массив критериев выбора, в котором записаны периоды начала работы сотрудников. Формула найдет его по имени, которое вписано в ячейку D12 (Start_period).Колонка закреплена жестко (знак $) для последующего копирования.
 

<=G$2),

Если сотрудник уже работает в компании, поле start_period его записи меньше или равно значению текущего периода (ячейка G2).
Ряд закреплен жестко (знак $) для последующего копирования.
 

--

Два единичных оператора «минус»:
— первый изменит знаки всех элементов массива, обратив их в числа,
— второй вернет все в исходное состояние, но уже в численном виде.
 

INDIRECT($E12)

Найти массив с типами сотрудников. Формула найдет его по имени, которое вписано в ячейку E12 (Type).
Колонка закреплена жестко (знак $) для последующего копирования.
 

=$F12)

Из массива Type будут выбраны только записи, у которых в поле Type стоит значение «Инженер» (ячейка F12).
Колонка закреплена жестко (знак $) для последующего копирования.

)

Формула завершена
А теперь рассмотрим ситуации, в которых SUMPRODUCTSявляется единственным способом решения задачи. 

Расходы на персонал: SUMPRODUCTS без альтернативы

Запрос 5. Каковы выплаты текущих бонусов в каждом из периодов? 
Алгоритм 
- Найти для данного периода общую сумму бонусов, выплачиваемых сотрудникам, которые уже работают в компании время, не менее того, что установлено для получения первого бонуса. 
Критерии
- Основной массив – Bonus (C13)
- Критерий 1 – Start_period (D13)
- Критерий 2 – Bonus_period (E13) 
- Значение критерия 2 – разность текущего периода и периода приема сотрудника на работу кратна числу периодов, необходимых для получения бонуса.
Формула: 
В данном случае нам придется сделать критерий результатом дополнительного вычисления. Компания должна выплатить сотруднику бонус, если в данном периоде подошел срок очередного бонуса. Например, сотрудник, принятый на работу в 3 периоде с периодичностью бонуса раз в 2 периода, получит бонус в 2+3=5 периоде, 5+2=7 периоде, 7+2=9 периоде и так далее. А в 1 периоде, хотя 1+2=3, он бонуса не получит, потому что еще не работает в компании. 
Кратность можно выразить как остаток от деления числа на частное, равный нулю. Для вычисления остатка от деления в Excel есть функция MOD. Ее аргументы – делимое и делитель. Воспользуемся ею. В качестве делителя используем бонусный период, а в качестве делимого – разность периода и периода первоначального приема на работу. 

=SUMPRODUCT(--(MOD(G$2-INDIRECT($D13),INDIRECT($E13))=0),--(G$2-INDIRECT($D13)>0),INDIRECT($C13))

=SUMPRODUCT(

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

--

Два единичных оператора «минус»:
— первый изменит знаки всех элементов массива, обратив их в числа,
— второй вернет все в исходное состояние, но уже в численном виде

(MOD

Функция остатка. Первый аргумент – делимое, второй аргумент – делитель.
 

(G$2-

Делимое – разность текущего периода и периода приема на работу.Получить значение текущего периода (G2).Ряд закреплен жестко (знак $) для последующего копирования.
 

INDIRECT($D13),

Найти массив критериев выбора, в котором записаны периоды начала работы сотрудников. Формула найдет его по имени, которое вписано в ячейку D13 (Start_period).Колонка закреплена жестко (знак $) для последующего копирования.
 

INDIRECT($E13)

Делитель – периодичность бонуса.Найти массив с суммами бонусов сотрудников. Формула найдет его по имени, которое вписано в ячейку E13 (Bonus_period).Колонка закреплена жестко (знак $) для последующего копирования.
 

)=0),

Проверка на кратность. Если остаток от деления равен нулю, условие выполнено.
 

--

Два единичных оператора «минус»:
— первый изменит знаки всех элементов массива, обратив их в числа,
— второй вернет все в исходное состояние, но уже в численном виде
 

(G$2-INDIRECT($D13)

Разность текущего периода и периода приема на работу..
 

>0),

Если текущий период больше (но не равен!) периода приема на работу, значит, данный сотрудник уже нанят на работу и работает в счет первого или более периодического бонуса.
 

INDIRECT($C13)

Найти массив с суммами бонусов сотрудников. Формула найдет его по имени, которое вписано в ячейку С13 (Bonus).Колонка закреплена жестко (знак $) для последующего копирования.

)

Формула завершена

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

А в целом эти упражнения оставят после себя матрицу следующего вида:
В заключение следует сказать, что созданный нами лист запросов – один из основных субэлементов финансовой модели, чаще всего называющийся schedule (роспись). Мы создали роспись денежных потоков, связанных с наймом. Роспись является расчетом денежных потоков от отдельных бизнес-процессов, видов деятельности или других промежуточных событий. Рассчитанные в ней денежные потоки в дальнейшем сводятся в операционный, инвестиционный или финансовый денежный поток, и далее – в общий денежный поток компании. Росписей в модели может быть столько, сколько это необходимо аналитику. 
База данных однотипных компонентов фирмы лежит в основе не только кадровой деятельности. Практически любой вид деятельности компании генерирует те или иные повторяемые финансовые события, которые могут быть систематизированы и рассчитаны описанными способами.
Домашнее задание:
  1. Используя этот массив данных и уже изученные формулы, сформируйте выражение для запроса «Каковы будут общие бонусные платежи компании за каждый данный период?», не прибегая при этом более чем к одной формуле SUMPRODUCT. 
  2. Используя полученные знания, составьте базу данных и матрицу запросов для решения следующей проблемы: 
Фирма закупает оборудование по следующему плану:
- Мобильные телефоны обновляются в среднем раз в период
- Ноутбуки обновляются в среднем раз в два периода
- Сервера обновляются раз в три периода
Технику, обновляемую раз в два и более периода, относят к инвестиционному денежному потоку, а обновляемую чаще – к операционному. 
Рассчитайте для произвольного количества и цен устройств операционный и денежный поток.