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

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

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

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

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

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

=COUNTIFS(C$2:C$1000,">=" & G2,C$2:C$1000,"<" & (G2+1))

Эта формула использует два критерия, эффективно проверяя, больше ли то, что находится в ячейках в столбце C, или равно значению в G2, но меньше, чем G2 + 1. Другими словами, он считает все, что происходит в течение дня, указанного в G2.

Другой подход состоит в том, чтобы убедиться, что целочисленное значение того, что находится в столбце 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(ROUNDDOWN(C$2:C$1000,0)=G2,1,0))

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

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

Этот совет (10422) применим к Microsoft Excel 2007, 2010, 2013, 2016, 2019 и Excel в Office 365. Вы можете найти версию этого совета для старого интерфейса меню Excel здесь:

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