ВПР с условиями ЕСЛИ выполнены

У меня есть проблема с заполнением рабочего листа правильным номером дела.

Sheet 1: (Report)
SSN         | Service Date
123456      | 10/01/2014

Sheet 2: (Data)
SSN         | Case Number  | Start Date | End Date
123456      | 0000000      | 01/01/2010 | 12/31/2012
123456      | 1111111      | 01/01/2013 | 05/31/2014
123456      | 2222222      | 06/01/2014 | 11/10/2015

Как я могу выполнить ВПР на основе того, чтобы дата обслуживания находилась в пределах «диапазона» начальной и конечной дат другого листа?

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

Я искал в Интернете и нашел "МАТЧ". Я могу сопоставить первый результат случая, совпадающего с SSN, но как перейти к следующему случаю, если он не совпадает?

=IF(E2>=INDEX('CASE NUMBERS'!A:F,MATCH(C2,'CASE NUMBERS'!A:A,0),4)&E2<=INDEX('CASE NUMBERS'!A:F,MATCH(C2,'CASE NUMBERS'!A:A,0),5),"YES","NO")

Я использую Excel 2013 в Windows 7 на работе.


excel vlookup excel-formula sumifs worksheet-function
person George    schedule 10.11.2015    source источник
comment
всегда ли данные на листе 2 сортируются по SSN и Start Date? Или это могло перепутать так, что не все SSN сгруппированы вместе? (Я думаю, вы всегда можете убедиться, что это отсортировано, отсортировав его самостоятельно)   -  person Scott Holtzman    schedule 10.11.2015
comment
@ScottHoltzman Они не отсортированы, но я обычно сам сортирую в Excel   -  person George    schedule 10.11.2015

Ответы (3)


arrow_upward
2
arrow_downward

Если SSN находится в A1 обоих листов, а ваши Case Number являются числовыми (кроме 0000000), вы можете попробовать:

=SUMIFS(Sheet2!B:B,Sheet2!A:A,A2,Sheet2!C:C,"<="&B2,Sheet2!D:D,">="&B2)

СУММЕСЛИМН объясняется здесь (и в других местах!).

person pnuts    schedule 10.11.2015

arrow_upward
3
arrow_downward

Вам потребуется 3 условия. a) Дата начала меньше даты обслуживания b) Дата окончания больше даты обслуживания и c) совпадают ли номера SSN?

Используйте новую функцию AGGREGATE¹ для принудительного перехода любых несовпадений в состояние ошибки при использовании параметра игнорирования ошибок (например, 6) для исключения ошибок.

=INDEX(Sheet2!$B$2:$B$9999, AGGREGATE(15, 6, ROW($1:$9998)/((Sheet2!C$2:C$9999<=B2)*(Sheet2!D$2:D$9999>=B2)*(Sheet2!A$2:A$9999=A2)), 1))

ВПР по нескольким критериям

Во всех смыслах и целях формула рабочего листа рассматривает ЛОЖЬ как ноль (например, 0) и ИСТИНА как единицу (например, 1). Любое число, умноженное на ноль, равно нулю, а любое число, умноженное на единицу, является тем же числом. Функция AGGREGATE извлекает позицию строки первого совпадения в Sheet2! B2 "B9999. Эта позиция строки будет числом где-то внутри ROW (1: 9998). Любая из строк, которые не соответствуют всем трем условиям, будет иметь как минимум один ноль, умноженный на знаменатель. Это делает знаменатель равным нулю. Все, что делится на ноль, вызывает #DIV/0! ошибку, и AGGREGATE отбрасывает их из результирующего набора. Параметр 15 AGGREGATE - это SMALL и последний 1 - это порядковый номер k для МАЛЕНЬКОГО (самого маленького). Таким образом, из всех строк, соответствующих всем трем условиям, АГРЕГАТ возвращает самую низкую строку в функция ИНДЕКС, которая извлекает значение из Sheet2! B2" B9999.

Сузьте диапазоны максимум до 5 строк и используйте Оценить формулу, чтобы пройти по формуле и лучше понять ее.

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

¹ Функция AGGREGATE был представлен в Excel 2010. Он недоступен в более ранних версиях.

person Community    schedule 10.11.2015
comment
Вы можете объяснить решение? У меня возникли проблемы с добавлением этого кода в свой отчет. Нужен ли знак "$"? У меня # ЧИСЛО! ошибка во всех строках. - person George; 10.11.2015
comment
Только если залить. $ блокирует строки, поэтому вы не смещаете диапазоны ячеек по мере заполнения. Объясню выше. - person ; 10.11.2015

arrow_upward
1
arrow_downward

Эта формула массива всегда будет печатать последнее совпадение:

=INDEX(Sheet2!B:B,MAX((Sheet2!A:A=A2)*(Sheet2!C:C<=B2)*(Sheet2!D:D>=B2)*ROW(A:A)))

Это формула массива, и ее необходимо подтвердить с помощью Ctrl + Shift + Enter.

  • Работает, если есть несколько решений, соответствующих критериям
  • Он также работает со всеми типами данных, которые вы хотите показать (значения / даты / строки).

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

person Dirk Reichel    schedule 10.11.2015
comment
Эти ссылки на полный диапазон столбцов повлияют на задержку вычислений. Даже с одной формулой я заметил почти целую секунду, чтобы получить результат. - person ; 10.11.2015
comment
Я не заметил ничего похожего на задержку с менее чем 30 копиями этой формулы ... однако я включил ее из-за мощности моего компьютера (сервера) - person Dirk Reichel; 10.11.2015