Загальна формула
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Резюме
Завдяки великим наборам даних точний збіг VLOOKUP може бути дуже повільним, але ви можете зробити VLOOKUP швидким освітленням за допомогою двох VLOOKUPS, як пояснено нижче.
Примітки:
- Якщо у вас менший набір даних, цей підхід є надмірним. Використовуйте його лише з великими наборами даних, коли швидкість дійсно важлива.
- Щоб цей фокус спрацював, потрібно відсортувати дані за значенням пошуку.
- У цьому прикладі використовуються іменовані діапазони. Якщо ви не хочете використовувати іменовані діапазони, використовуйте натомість абсолютні посилання.
Точний збіг VLOOKUP повільний
Коли ви використовуєте VLOOKUP у "режимі точного збігу" для великого набору даних, це може реально сповільнити час обчислення на аркуші. Скажімо, 50 000 записів або 100 000 записів, обчислення може зайняти хвилини.
Точна відповідність встановлюється шляхом подання FALSE або нуля як четвертий аргумент:
=VLOOKUP(val,data,col,FALSE)
Причиною того, що VLOOKUP у цьому режимі є повільним, є те, що він повинен перевіряти кожен окремий запис у наборі даних, поки не буде знайдено збіг. Це іноді називають лінійним пошуком.
Приблизна відповідність VLOOKUP дуже швидка
У режимі приблизних збігів VLOOKUP надзвичайно швидкий. Щоб використовувати VLOOKUP з наближеною відповідністю, потрібно відсортувати дані за першим стовпцем (стовпець пошуку), а потім вказати TRUE для 4-го аргументу:
=VLOOKUP(val,data,col,TRUE)
(VLOOKUP за замовчуванням має значення true, що є страшним за замовчуванням, але це вже інша історія).
З дуже великими наборами даних перехід на приблизний VLOOKUP може означати різке збільшення швидкості.
Отож, безглуздо, так? Просто сортуйте дані, використовуйте приблизний збіг, і все готово.
Не так швидко (хе).
Проблема з VLOOKUP в режимі "приблизного збігу" полягає в наступному: VLOOKUP не відображатиме помилку, якщо значення пошуку не існує. Гірше того, результат може виглядати цілком нормально, хоча це абсолютно неправильно (див. Приклади). Не те, що ви хочете пояснити своєму начальнику.
Рішення полягає у використанні VLOOKUP двічі, обидва рази в режимі приблизного збігу:
=IF(VLOOKUP(id,data,1,TRUE)=id, VLOOKUP(id,data,col,TRUE), NA())
Пояснення
Перший екземпляр VLOOKUP просто шукає значення пошуку ( ідентифікатор у цьому прикладі):
=IF(VLOOKUP(id,data,1,TRUE)=id
і повертає TRUE лише тоді, коли знайдене значення пошуку. У цьому випадку
формула знову запускає VLOOKUP в режимі приблизного збігу, щоб отримати значення з цієї таблиці:
VLOOKUP(id,data,col,TRUE)
Немає загрози відсутнього значення пошуку, оскільки перша частина формули вже перевірена, щоб переконатися, що вона є.
Якщо значення пошуку не знайдено, функція IF виконує частину "value if FALSE", і ви можете повернути будь-яке значення, яке вам подобається. У цьому прикладі ми використовуємо NA (), ми повертаємо помилку # N / A, але ви також можете повернути повідомлення типу "Відсутнє" або "Не знайдено".
Пам'ятайте: ви повинні сортувати дані за значенням пошуку, щоб цей фокус спрацював.
