Поиск значения в зависимости от того, какой набор дат находится между другими датами

Question

и извинения, если это дубликат - если бы вы могли указать мне в направлении каких-либо существующих ответов, это было бы здорово.

У меня есть набор диапазонов дат в Excel, каждый из которых имеет какую-то метку. например

LabelA  01/01/10  31/01/10
LabelB  01/02/10  28/02/10
LabelC  01/03/10  31/03/10

Если у меня есть другая дата, я хотел бы найти ярлык, относящийся к диапазону дат, в который попадает эта дата. например, 15/02/10 я хотел бы вернуть LabelB. Я знаю, что диапазоны дат не будут перекрываться, хотя между датой окончания одного и датой начала следующего может быть разрыв.

Answer 1

Можете ли вы добавить (может быть скрытый) дубликат столбца А справа (или переместить столбец А) и даты в порядке?

Если это так, функция VLOOKUP может быть ответом. Он ищет значение в первом столбце и просматривает несколько столбцов. Вот пример.

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

Answer 2

Вы можете сделать это, создав формулу массива, которая сравнивает каждый диапазон дат с требуемой датой. Если дата является диапазоном, она возвращает дату окончания.

Затем выполните сопоставление в эту дату окончания, чтобы найти соответствующую строку.

Все это может быть сделано в одной ячейке по мере необходимости.

Если данные в A1:C3, а требуемая дата в B4, то эта формула:

=INDEX(A1:A3,MATCH(SUM(IF(B1:B3<B4,1,0)*IF(C1:C3>=B4,C1:C3,0)),C1:C3,0))

Должен делать то, что вы хотите. Это должно быть введено как формула массива.

Если дата не указана ни в одном из диапазонов, она вернет # N/A

Answer 3

Вы можете сделать несколько сумм условий, используя sumproduct . Вы бы использовали (допустим, что Date1 - это B:B, Date2 - это C:C, E1 - это дата, которую вы хотите найти)

sumproduct(--(B1:B50<=E1),--(C1:C50>=E1),Row(B1:B50))

Это даст вам номер строки, так как эта сумма множественных условий истинна, только когда E1 равно> = B и <= C, и суммирует номер строки всех строк, где это правда (как вы сказали, самое большее 1), Чтобы затем найти ярлык, вы можете:

index(A1:A50,sumproduct(--(B1:B50<=E1),--(C1:C50>=E1),Row(B1:B50)))

Также, если вы думаете, что ваш номер может быть в одном из пробелов, вы можете сделать:

if(sumproduct(--(B1:B50<=E1),--(C1:C50>=E1),Row(B1:B50))=0,"Date Not Found",index(A1:A50,sumproduct(--(B1:B50<=E1),--(C1:C50>=E1),Row(B1:B50))))

Answer 4

Для меня это звучит как раз то, для чего было создано условное форматирование . Вы можете установить до 3 различных условий для ячейки или диапазона ячеек, и когда одно из условий соответствует ячейке, форматирование применяется к этим условиям.

Вот так много электронных таблиц типа "светофор".