Общая формула
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Резюме
С большими наборами данных точное соответствие ВПР может быть очень медленным, но вы можете сделать ВПР быстрее, используя два ВПР, как описано ниже.
Заметки:
- Если у вас меньший набор данных, этот подход излишний. Используйте его только с большими наборами данных, когда скорость действительно имеет значение.
- Вы должны отсортировать данные по значению поиска, чтобы этот трюк сработал.
- В этом примере используются именованные диапазоны. Если вы не хотите использовать именованные диапазоны, используйте вместо них абсолютные ссылки.
ВПР с точным соответствием выполняется медленно
Когда вы используете VLOOKUP в «режиме точного соответствия» для большого набора данных, это может действительно замедлить время вычислений на листе. Например, если имеется 50 000 или 100 000 записей, расчет может занять минуты.
Точное совпадение задается указанием FALSE или нуля в качестве четвертого аргумента:
=VLOOKUP(val,data,col,FALSE)
Причина, по которой VLOOKUP в этом режиме работает медленно, заключается в том, что он должен проверять каждую запись в наборе данных, пока не будет найдено совпадение. Иногда это называют линейным поиском.
ВПР с приблизительным соответствием выполняется очень быстро
В режиме приблизительного соответствия ВПР выполняется очень быстро. Чтобы использовать ВПР с приблизительным соответствием, необходимо отсортировать данные по первому столбцу (столбец подстановки), а затем указать ИСТИНА для 4-го аргумента:
=VLOOKUP(val,data,col,TRUE)
(По умолчанию для ВПР установлено значение true, что по умолчанию пугает, но это уже другая история).
Для очень больших наборов данных переход на ВПР с приблизительным соответствием может означать резкое увеличение скорости.
Итак, понятно, правда? Просто отсортируйте данные, используйте приблизительное совпадение, и все готово.
Не так быстро (хех).
Проблема с функцией VLOOKUP в режиме «приблизительного совпадения» заключается в следующем: VLOOKUP не будет отображать ошибку, если искомое значение не существует. Хуже того, результат может выглядеть совершенно нормальным, даже если он совершенно неправильный (см. Примеры). Не то, что вы хотите объяснять своему боссу.
Решение состоит в том, чтобы использовать ВПР дважды, оба раза в режиме приблизительного соответствия:
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Объяснение
Первый экземпляр VLOOKUP просто ищет значение поиска ( id в этом примере):
=IF(VLOOKUP(id,data,1,TRUE)=id
и возвращает ИСТИНА только тогда, когда значение поиска найдено. В этом случае
формула снова запускает ВПР в режиме приблизительного совпадения, чтобы получить значение из этой таблицы:
VLOOKUP(id,data,col,TRUE)
Опасности отсутствия подстановочного значения нет, поскольку первая часть формулы уже проверена, чтобы убедиться, что она есть.
Если искомое значение не найдено, выполняется часть «значение, если ЛОЖЬ» функции ЕСЛИ, и вы можете вернуть любое значение, которое захотите. В этом примере мы используем NA (), мы возвращаем ошибку # N / A, но вы также можете вернуть сообщение типа «Отсутствует» или «Не найдено».
Помните: вы должны отсортировать данные по поисковому значению, чтобы этот трюк сработал.
