☰

Excel: найдите самые ранние/поздние значения для каждой даты в списке дат и времени

tag-icon • tag-icon microsoft-excel microsoft-excel-2016
Excel: найдите самые ранние/поздние значения для каждой даты в списке дат и времени

У меня есть список последовательного времени прибытия и отправления, например:

| IN/OUT | DATE/TIME             |
|--------|-----------------------|
| IN     | 1/2/2019  8:25:59 AM  |
| OUT    | 1/2/2019  11:51:59 AM |
| IN     | 1/2/2019  12:48:59 PM |
| OUT    | 1/2/2019  4:45:59 PM  |
| IN     | 1/3/2019  9:08:59 AM  |
| OUT    | 1/3/2019  12:38:59 PM |
| IN     | 1/3/2019  3:23:59 PM  |
| OUT    | 1/3/2019  4:59:59 PM  |
| IN     | 1/4/2019  8:49:59 AM  |
| OUT    | 1/4/2019  5:11:59 PM  |

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

Например, из приведенной выше таблицы я хотел бы извлечь следующее:

| IN/OUT | DATE/TIME             |
|--------|-----------------------|
| IN     | 1/2/2019  8:25:59 AM  |
| OUT    | 1/2/2019  4:45:59 PM  |
| IN     | 1/3/2019  9:08:59 AM  |
| OUT    | 1/3/2019  4:59:59 PM  |
| IN     | 1/4/2019  8:49:59 AM  |
| OUT    | 1/4/2019  5:11:59 PM  |

Я знаю, что могу использовать VLOOKUP, чтобы получить все значения 'IN' или 'OUT', но проблема в том, что большинство дней в списке имеют больше одного. Мне нужны только первый и последний, но я не уверен, как это сделать.

Есть ли способ сделать это с помощью VLOOKUP, с вспомогательной таблицей или без нее? Или, может быть, другой метод сработает лучше?

решение1

1. Не забудьте поискать, прежде чем спрашивать

Использование MAX() в Excel для списка дат с использованием нескольких критериев

Видите раздел «Связанные»?

СВЯЗАННЫЙ ЗАПРОС

2. MIN/ MAXРецепт CSE

ОБРАЗЕЦ ИЗОБРАЖЕНИЯ

MINдля «В»:

=MIN(IF($A$2:$A$99=F2,IF($B$2:$B$99=G2,$C$2:$C$99)))

Это то, что они называют формулой массива или «CSE» — введите ее в строку формул, затем нажмите , Ctrl Shift Enterчтобы ввести ее как формулу массива, затем скопируйте вниз.

Это интуитивно понятно, не так ли? Получите наименьшее значение, Cгде Fсовпадает Aи Gсовпадает B.

Это дает только минимальные значения IN, хотя... замена MAXна in MINв каждой второй строке дает максимальные значения OUT... Но у кого есть на это время? Давайте разобьем их вместе с помощью простого IF:

3. ОКОНЧАТЕЛЬНАЯ ФОРМУЛА

ОКОНЧАТЕЛЬНАЯ ФОРМУЛА

=IF(F2="IN",MIN(IF($A$2:$A$99=F2,IF($B$2:$B$99=G2,$C$2:$C$99))),MAX(IF($A$2:$A$99=F2,IF($B$2:$B$99=G2,$C$2:$C$99))))

Снова введите эту формулу в F2, нажмите Ctrl Shift Enterи скопируйте вниз.

Примечание:

  1. При необходимости отрегулируйте ссылки на ячейки.
  2. Вы не особо указали форму входных данных; я разделил каждый аргумент с помощью функции Excel «Текст по столбцам», чтобы иметь возможность работать с датами и временем независимо.
  3. НЕТ отлова ошибок, и решение предполагает, что у вас уже есть желаемые тестовые значения, установленные, как показано. Это должно быть легко — скопировать ваши входные столбцы IN/OUT и DATE и использовать «Удалить дубликаты», чтобы сгенерировать ваш уникальный список для соответствия.
  4. В том, что у меня в столбце, нет специального форматирования C. Это простой текст, как показано.Вероятно, вам придется использовать Format Painter.(из этой колонки)в вашем конечном выходном столбце!
  5. Не обращайте внимания на мою колонку D, которую я использовал для простой проверки ошибок при составлении этого материала и забыл удалить перед тем, как сделать скриншоты.

Связанный контент