Роб ведет статистику по каждой выполняемой им работе каждый день. Например, он отслеживает точное количество часов и минут, потраченных на каждое задание, вводя время начала (столбец B) и время окончания (столбец C) для каждого задания в формате: 16.05.11 11:25. В отдельном столбце (столбец G) у него есть дата для каждого дня месяца. Справа от каждой даты (в столбце H) он хочет показать, сколько заданий он выполнил в каждую из дат calendar. Роб интересуется, какую формулу он использовал бы, чтобы вычислить этот счет.

Эта задача не так проста, как может показаться сначала. Причина в том, как хранятся время начала и окончания.

Столбец B содержит дату и время начала в одном столбце в формате «16.05.11 11:25», а столбец C содержит дату и время окончания в том же формате. Вы можете подумать, что можете использовать функцию СЧЁТЕСЛИ в столбце G следующим образом:

=COUNTIF(C$2:C$1000,G2)

Однако это не сработает. Причина проста — столбец G (в данном случае ячейка G2) содержит дату без времени. Например, он может содержать дату 16.05.11. По сравнению с ячейкой C2, которая может содержать 11:25 утра 16.05.11, они не совпадают. Помните, что Excel выполняет сравнение на основе серийного номера даты и времени, хранящегося в каждой ячейке. Учитывая только что упомянутые примеры значений, ячейка G2 будет содержать серийный номер 39217, а ячейка C2 будет содержать серийный номер 39217.47569. Поскольку эти два значения не совпадают, СЧЁТЕСЛИ не считает их равными.

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

=SUMPRODUCT((INT(C$2:C$1000)=G2)*1)

Целое число каждого из значений в диапазоне C2: C1000 сравнивается с датой в G2, давая массив значений True и False. Умножение каждого из них на 1 превращает значения True и False в значения 1 и 0 соответственно. Затем формула суммирует эти продукты, давая желаемое количество.

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

=SUMPRODUCT(--(INT(C$2:C$1000)=G2))

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

=COUNT(IF(INT(C$2:C$1000)=INT(G2),1,FALSE))

=SUM(IF(ROUND(C$2:C$1000,0)=G2,1,0))

Помните, что формулы массива необходимо вводить в ячейку с помощью Ctrl + Shift + Enter.

ExcelTips — ваш источник экономичного обучения Microsoft Excel.

Этот совет (10421) применим к Microsoft Excel 97, 2000, 2002 и 2003.

Вы можете найти версию этого совета для ленточного интерфейса Excel (Excel 2007 и новее) здесь:

link: / excelribbon-Counting_Jobs_Completed_On_a_Date [Подсчет заданий, завершенных в дату].