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