У меня есть 2 листа в Excel.
Одно поле содержит поля даты.
например A-1 Дата1
На 2-м листе у меня есть диапазоны дат с идентификаторами.
A-1 Начальная дата A2-Конечная дата A3-Идентификатор диапазона дат
Мне нужно создать новое поле на первом листе, которое будет содержать идентификатор диапазона Date1.
Например, на 2-м листе у меня диапазон A1-1 мая 2017 г. A2-30 мая 2017 г. A3-7
На первом листе А1 — 3 мая 2017 г. Поэтому я хочу, чтобы на моем первом листе был новый столбец со значением 7.
Как это сделать в Excel?
решение1
Как уже сказал @Ron Rosenfeld, если диапазон в Sheet2 включает все даты в Sheet1 и нет перекрытия, простой VLOOKUP должен работать. Однако, если ваш диапазон дат в Sheet2 не обязательно в порядке возрастания и с пробелами между ними и возможным перекрытием. Попробуйте это решение. Ограничение заключается в том, что оно должно возвращать первый идентификатор диапазона для диапазона дат, где данные из Sheet1 находятся в пределах диапазона.
В этом примере Лист1 имеет даты в столбце A1:A10. Лист2 имеет начальную дату в A1:A7, конечную дату в B1:B7 и идентификатор диапазона в C1:C7.
В ячейке B1 Листа 1 поместите следующую Формулу массива.
=IF(ISERROR(INDEX(Sheet2!$C$1:$C$7,MATCH(1,IF(A1>=Sheet2!$A$1:$A$7,IF(A1<=Sheet2!$B$1:$B$7,1,0)),0))),"Not Found",INDEX(Sheet2!$C$1:$C$7,MATCH(1,IF(A1>=Sheet2!$A$1:$A$7,IF(A1<=Sheet2!$B$1:$B$7,1,0)),0)))
После этого нажмите клавиши CTRL+SHIFT+ENTER, чтобы сделать ее формулой массива. Теперь вы увидите формулу, заключенную в фигурные скобки в адресной строке. Теперь перетащите ее вниз до нужных строк. Теперь столбец B должен извлечь соответствующие идентификаторы диапазонов из Листа 2, где дата в столбце A попадает в диапазон.
Формула основана на проверке вложенных IF для возврата массива значений TRUE, FALSE на основе диапазона соответствия и использования внешнего INDEX-MATCH для извлечения первой позиции соответствия 1 и возврата значения в этой позиции из столбца Range ID.
