Как подсчитать, сколько ячеек в электронной таблице Excel имеют неопределенный временной диапазон?

Question

Справочная информация: я менеджер розничного бизнеса и пытаюсь выяснить, сколько раз / дней люди, родившиеся в определенный год, приходят в наш магазин в среднем. Для этого у меня есть список всех членов этого возраста, а затем список всех случаев, когда все люди приходят. В настоящее время я проверяю все транзакции клиента вручную и отслеживаю, сколько времени они посетили магазин. Это чрезвычайно утомительно и требует много времени.

Как мне кажется, я смогу решить это быстрее, если бы знал, как: я бы хотел найти все ячейки, например, с меткой времени с 12:00:00 до 12:59:59 PM. Это даст мне общее количество посещений магазина всеми покупателями из этого списка. Как я могу пойти по этому поводу? Я действительно предпочел бы использовать инструмент "найти все", чтобы сделать это. Я думал, что смогу вставить что-то вроде 12: xx: xx PM, но это не сработало.

Надеюсь, я объяснил это достаточно четко, чтобы получить ответ или толчок в правильном направлении.

ура

Answer 1

То, на что вы смотрите, немного сложнее, чем использование формулы с помощью простого COUNTIFS() или SUMPRODUCT() . Требуется формула двумерного массива.

Я создал пример рабочего листа, который, надеюсь, поможет вам начать.

Настройте рабочий лист, как показано ниже:

Массив-введите (Ctrl+Shift+Enter) следующую формулу в I3 а затем скопируйте-вставьте / заполните вниз:

{=SUM(($C$3:$C$15>=G3)*($C$3:$C$15<=H3)*($D$3:$D$15=TRANSPOSE(OFFSET($K$3:$K$15,,MATCH(F3,$K$2:$M$2,0)-1))))}

---

Предварительно подтвержденный вариант формулы выглядит следующим образом:

{=
SUM(
   ($C$3:$C$15>=G3)
  *($C$3:$C$15<=H3)
  *($D$3:$D$15=TRANSPOSE(OFFSET($K$3:$K$15,,MATCH(F3,$K$2:$M$2,0)-1)))
)}

Объяснение:

Первые два подвыражения внутри SUM() создают одномерные массивы столбцов для фильтрации в течение требуемого интервала времени.

Третье подвыражение создает двумерный массив, состоящий из столбца для каждой из ячеек соответствующего столбца таблицы Год рождения, таким образом, фильтруя только те транзакции, которые соответствуют людям, родившимся в этом году.

OFFSET($K$3:$K$15,,MATCH(F3,$K$2:$M$2,0)-1) выбирает соответствующий столбец Год рождения в зависимости от значения в F3 , например, значение 1991 выберет $L$3:$L$15 . TRANSPOSE() требуется для преобразования массива столбцов в массив строк, чтобы при умножении получился двумерный массив. Заметки:

• Предварительно подтвержденная формула действительно работает, если введена.

Answer 2

Используйте сводную таблицу.

Поместите время в ряды и значения.

Убедитесь, что он установлен для отсчета времени:

Answer 3

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

=COUNTIFS(A2:A20,">="&C2,A2:A20,"<="&D2)

=SUMPRODUCT((A2:A20>=C2) * (A2:A20<=D2))

Если диапазон данных имеет метку даты и времени, тогда ваша формула должна быть:

=SUMPRODUCT((ROUND(MOD(A2:A20,1),10)>=C2) * (ROUND(MOD(A2:A20,1),10)<=D2))

Примечание: где C2 имеет 08:30, а D2 - 12:00.