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 как альтернатива
– Каждый из ее аргументов будет содержать результат проверки заданного массива на соответствующий критерий, с результатом примерного вида {0;1..1:0}, где 0 соответствует логическому «нет», а 1 логическому «да».– Последний массив будет содержать массив тех данных, сумму которые мы хотим получить на выходе (его последнее положение также облегчит инструментальную проверку формулы на логические ошибки).– Перемножение этих массивов обнулит значения всех записей, кроме тех, которые удовлетворили всем критериям.– Критерии можно настроить максимально гибко на основе почти любых логических конструкций.
- Найти для данного периода общую сумму бонусов всех тех сотрудников, кто в данном периоде поступил на работу в компанию.
- Основной массив – Bonus (C10)- Критерий 1 – Start_period (D10)
=SUMPRODUCT(INDIRECT($D10)=G$2,INDIRECT($C10))
=SUMPRODUCT(--(INDIRECT($D11)=G$2),INDIRECT($C11))
=SUMPRODUCT( | Формула возвращает сумму произведений своих аргументов. Первый аргумент – логический массив проверки срока работы для определения нанятых именно в этом периоде сотрудников, второй аргумент – числовой массив сумм бонусов. |
-- | Два единичных оператора «минус»: — первый изменит знаки всех элементов массива, обратив их в числа, — второй вернет все в исходное состояние, но уже в численном виде. |
INDIRECT($D11) | Найти массив критериев выбора, в котором записаны периоды начала работы сотрудников. Формула найдет его по имени, которое вписано в ячейку D11 (Start_period).Колонка закреплена жестко (знак $) для последующего копирования. |
=G$2), | Сравнить полученные из массива периоды начала работы с текущим периодом (G2). В данном случае мы не можем опустить знак равенства, поскольку аргумент формулы – все выражение в целом. Ряд закреплен жестко (знак $) для последующего копирования. |
INDIRECT($C11) | Найти массив с суммами бонусов сотрудников. Формула найдет его по имени, которое вписано в ячейку С11 (Bonus). Колонка закреплена жестко (знак $) для последующего копирования. |
) | Формула завершена |
- Найти для данного периода общее число всех тех сотрудников, кто в данном периоде поступил на работу в компанию и для которых значение 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 без альтернативы
- Найти для данного периода общую сумму бонусов, выплачиваемых сотрудникам, которые уже работают в компании время, не менее того, что установлено для получения первого бонуса.
- Основной массив – Bonus (C13)- Критерий 1 – Start_period (D13)- Критерий 2 – Bonus_period (E13)- Значение критерия 2 – разность текущего периода и периода приема сотрудника на работу кратна числу периодов, необходимых для получения бонуса.
=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).Колонка закреплена жестко (знак $) для последующего копирования. |
) | Формула завершена |
В результате мы получили достаточно сложную, но предельно гибкую формулу. Итоговая матрица должна выглядеть следующим образом:
- Используя этот массив данных и уже изученные формулы, сформируйте выражение для запроса «Каковы будут общие бонусные платежи компании за каждый данный период?», не прибегая при этом более чем к одной формуле SUMPRODUCT.
- Используя полученные знания, составьте базу данных и матрицу запросов для решения следующей проблемы:
- Мобильные телефоны обновляются в среднем раз в период- Ноутбуки обновляются в среднем раз в два периода- Сервера обновляются раз в три периода