Условная ВПР

У меня есть две таблицы Excel:

У одного есть идентификатор и дата У другого есть идентификатор, две даты и значение

Я должен сопоставлять идентификаторы и возвращать значение всякий раз, когда дата в электронной таблице 1 находится между двумя датами в электронной таблице 2.

Между этими электронными таблицами существует однозначное соответствие, и значение по умолчанию равно 0, если совпадений не найдено.

В первой электронной таблице много строк. Будет ли какая-то функция ВПР, которую я могу использовать для получения правильных данных?


excel vlookup match
person Flavian Hautbois    schedule 28.08.2012    source источник

Ответы (2)


arrow_upward
2
arrow_downward

СУММПРОИЗВ и формула массива - это то, что вам нужно.

=SUMPRODUCT(--(Sheet2!$A$2:$A$2000=A2),--(Sheet2!$B$2:$B$2000<B2),--(Sheet2!$C$2:$C$2000>B2),--(Sheet2!$D$2:$D$2000))

это должно быть введено как формула массива с CTRL+SHIFT+Enter. вы увидите, что Excel окружает формулу {}, если вы ввели ее правильно.

Предположения: идентификатор для проверки в A2, дата для проверки в B2
Данные на листе 2 в следующем порядке: A идентификатор, B дата начала, C дата окончания и D значение (числовое)

формула массива возвращает список значений true/false, которые затем перемножаются. только если все критерии равны True, возвращается значение (поскольку любые ответы False равны нулю, а ноль * другое число равно нулю).

Это даст неправильные ответы, если 2 диапазона перекрываются.

person SeanC    schedule 28.08.2012
comment
Однако с этим должна быть проблема: идентификаторы не уникальны, а это означает, что в электронной таблице 2 у вас может быть несколько идентификаторов (для разных наборов дат, следовательно, взаимно однозначное соответствие). Мне кажется, что VLOOPKUP в условии остановятся при первом положительном идентификаторе... - person Flavian Hautbois; 29.08.2012
comment
Если идентификаторы не уникальны, то каким будет правильный ответ? Какое значение он должен вернуть: идентификатор первой копии или идентификатор второй копии? В зависимости от вашего ответа на этот вопрос: Один из вариантов — сделать все идентификаторы уникальными. - например, с такой формулой: = ЕСЛИ (СЧЁТЕСЛИ (A $ 1: A1, A1) > 1, A1 & - & ТЕКСТ (СЧЁТЕСЛИ (A $ 1: A1, A1), #), A1) - тогда вы бы автоматически заполнили что вниз. - person Stepan1010; 29.08.2012
comment
Или, если они действительно соответствуют по строкам, вы можете просто автоматически заполнить 1,2,3,4 и т. д. и т. д. вниз по столбцу и использовать их в качестве своих уникальных идентификаторов. - person Stepan1010; 29.08.2012
comment
Хм. Я вижу, что он хочет найти каждый идентификатор на листе 2, который соответствует идентификатору на листе 1 (это не соответствие 1 к 1), проверить даты, и как только он найдет совпадающую дату, вывести соответствующее значение, иначе вывести 0 ... Это займет немного мысли :) - person SeanC; 29.08.2012
comment
Я не уверен, что понимаю его вопрос, но если я правильно вас понимаю, Шон, вы можете попробовать работать с ним в обратном порядке, то есть поместить свою пользовательскую формулу vlookup на второй лист, а затем проверить лист один, чтобы увидеть, находится ли дата соответствующего идентификатора между даты в столбцах B и C на втором листе. - person Stepan1010; 29.08.2012

arrow_upward
2
arrow_downward

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

person Flavian Hautbois    schedule 14.11.2012