зміст
Цей урок пояснює, як швидко впоратися з ситуацією, коли функція VPR (VLOOKUP) не хоче працювати в Excel 2013, 2010, 2007 і 2003, і як визначити та виправити типові помилки та подолати обмеження VPR.
У кількох попередніх статтях ми досліджували різні аспекти функції VPR в Excel. Якщо ви уважно прочитали їх, то тепер ви повинні бути експертом у цій галузі. Однак недарма вважають багато експертів Excel VPR одна з найскладніших функцій. Він має купу обмежень і функцій, які стають джерелом багатьох проблем і помилок.
У цій статті ви знайдете прості пояснення помилок #AT (#N/A), # ІМ'Я? (#ІМ'Я?) і #VALUE! (#VALUE!), які з’являються під час роботи з функцією VPR, а також прийоми і способи боротьби з ними. Ми почнемо з найпоширеніших випадків і найочевидніших причин. VPR не працює, тому краще вивчати приклади в тому порядку, в якому вони наведені в статті.
Виправлення помилки #N/A у функції VLOOKUP в Excel
У формулах с VPR Повідомлення про помилку #AT (#N/A) означає недоступний (немає даних) – з’являється, коли Excel не може знайти потрібне значення. Це може статися з кількох причин.
1. Потрібне значення вписано з помилкою
Гарна ідея спочатку перевірити цей елемент! Помилки часто трапляються, коли ви працюєте з дуже великими обсягами даних, що складаються з тисяч рядків, або коли значення, яке ви шукаєте, записується у формулу.
2. Помилка #N/A під час пошуку приблизної відповідності за допомогою VLOOKUP
Якщо ви використовуєте формулу з умовою пошуку приблизної відповідності, тобто аргументом пошук_діапазону (діапазон_пошуку) має значення TRUE або не вказано, ваша формула може повідомити про помилку # N / A у двох випадках:
- Значення, яке потрібно знайти, є меншим за найменше значення в масиві, який шукається.
- Стовпець пошуку не відсортовано за зростанням.
3. Помилка #N/A під час пошуку точної відповідності за допомогою VLOOKUP
Якщо ви шукаєте точну відповідність, тобто аргумент пошук_діапазону (діапазон_пошуку) має значення FALSE і точне значення не знайдено, формула також повідомить про помилку # N / A. Дізнайтеся більше про те, як шукати точні та приблизні збіги за допомогою функції VPR.
4. Стовпець пошуку не крайній зліва
Як ви, мабуть, знаєте, одне з найбільш істотних обмежень VPR це те, що він не може дивитися ліворуч, тому стовпець пошуку у вашій таблиці має бути крайнім лівим. На практиці ми часто про це забуваємо, що призводить до неробочої формули і помилки. # N / A.
Рішення: Якщо неможливо змінити структуру даних так, щоб пошуковий стовпець був крайнім лівим, можна скористатися комбінацією функцій ІНДЕКС (ІНДЕКС) і БІЛЬШ ЕКСПОЗИЦІЙНИЙ (MATCH) як більш гнучка альтернатива для VPR.
5. Числа відформатовані як текст
Ще одне джерело помилок # N / A у формулах с VPR це числа в текстовому форматі в головній таблиці або таблиці пошуку.
Зазвичай це відбувається, коли ви імпортуєте інформацію із зовнішніх баз даних або коли ви вводите апостроф перед числом, щоб зберегти початковий нуль.
Найбільш очевидні ознаки числа в текстовому форматі показані на малюнку нижче:
Крім того, числа можна зберігати у форматі Загальне (Загальний). У цьому випадку помітна лише одна особливість – числа вирівнюються по лівому краю комірки, тоді як за замовчуванням вони вирівнюються по правому краю.
Рішення: Якщо це одне значення, просто натисніть значок помилки та виберіть Перетворити на число (Перетворити на число) з контекстного меню.
Якщо така ситуація з багатьма числами, виберіть їх і клацніть правою кнопкою миші на вибраній області. У контекстному меню, що з’явиться, виберіть Формат ячеек (Формат клітинок) > вкладка Номер (Число) > формат Номер (Цифровий) і натисніть OK.
6. На початку або в кінці є пробіл
Це найменш очевидна причина помилки. # N / A у функції VPR, оскільки візуально важко побачити ці зайві пробіли, особливо під час роботи з великими таблицями, коли більшість даних знаходиться поза екраном.
Рішення 1: Додаткові місця в головній таблиці (де знаходиться функція VLOOKUP)
Якщо в головній таблиці з’являються зайві пробіли, ви можете переконатися, що формули працюють правильно, включивши аргумент lookup_value (lookup_value) у функцію TRIM (ОБРІЗАТИ):
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
Рішення 2: Додаткові пробіли в таблиці пошуку (у стовпці пошуку)
Якщо в пошуковій колонці зайві пробіли – прості способи # N / A у формулі с VPR не можна уникнути. Замість VPR Ви можете використовувати формулу масиву з комбінацією функцій ІНДЕКС (ІНДЕКС), БІЛЬШ ЕКСПОЗИЦІЙНИЙ (МАТЧ) и TRIM (ОБРІЗАТИ):
=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))
Оскільки це формула масиву, не забудьте натиснути Ctrl + Shift + Enter замість звичного Що натомість? Створіть віртуальну версію себе у щоб правильно ввести формулу.
Помилка #VALUE! у формулах за допомогою VLOOKUP
У більшості випадків Microsoft Excel повідомляє про помилку #VALUE! (#VALUE!), якщо значення, використане у формулі, не відповідає типу даних. Стосовно VPR, зазвичай є дві причини помилки #VALUE!.
1. Значення, яке ви шукаєте, перевищує 255 символів
Будьте обережні: функція VPR не може шукати значення, що містять більше 255 символів. Якщо значення, яке ви шукаєте, перевищує це обмеження, ви отримаєте повідомлення про помилку. #VALUE!.
Рішення: Використовуйте купу функцій ІНДЕКС+МАТЧ (ІНДЕКС + МАТЧ). Нижче наведено формулу, яка чудово впорається з цим завданням:
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. Не вказано повний шлях до книги пошуку
Якщо ви отримуєте дані з іншої книги, ви повинні вказати повний шлях до цього файлу. Точніше, ви повинні включити назву робочої книги (включно з розширенням) у квадратні дужки [ ], а потім назву аркуша та знак оклику. Уся ця конструкція повинна бути укладена в апостроф, якщо назва книги або аркуша містить пробіли.
Ось повна структура функції VPR для пошуку в іншій книзі:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
Справжня формула може виглядати так:
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
Ця формула шукатиме значення клітинки A2 в колонку B на аркуші Sheet1 в трудовій книжці Нові ціни і витягніть відповідне значення зі стовпця D.
Якщо будь-яка частина шляху до таблиці пропущена, ваша функція VPR не працюватиме та повідомить про помилку #VALUE! (навіть якщо робоча книга з таблицею пошуку наразі відкрита).
Для отримання додаткової інформації про функцію VPRпосилаючись на інший файл Excel, дивіться урок: Пошук в іншій робочій книзі за допомогою VLOOKUP.
3. Аргумент Column_num менше 1
Важко уявити ситуацію, коли хтось вводить значення менше ніж 1щоб вказати стовпець, з якого потрібно отримати значення. Хоча це можливо, якщо значення цього аргументу обчислюється іншою вкладеною функцією Excel VPR.
Отже, якщо трапиться, що аргумент col_index_num (номер_стовпця) менше ніж 1функція VPR також повідомить про помилку #VALUE!.
Якщо аргумент col_index_num (номер_стовпця) більше, ніж кількість стовпців у заданому масиві, VPR повідомить про помилку #REF! (#SSYL!).
Помилка #NAME? у VLOOKUP
Найпростіший випадок - помилка # ІМ'Я? (#NAME?) – з’явиться, якщо ви випадково напишете назву функції з помилкою.
Рішення очевидно – перевірте правопис!
VLOOKUP не працює (обмеження, застереження та рішення)
На додаток до досить складного синтаксису, VPR має більше обмежень, ніж будь-яка інша функція Excel. Через ці обмеження, здавалося б, прості формули с VPR часто призводять до несподіваних результатів. Нижче ви знайдете рішення для кількох поширених сценаріїв VPR невірно.
1. VLOOKUP не чутливий до регістру
функція VPR не розрізняє регістр і приймає малі та великі символи як однакові. Таким чином, якщо в таблиці є кілька елементів, які відрізняються лише регістром, функція VLOOKUP поверне перший знайдений елемент, незалежно від регістру.
Рішення: Використовуйте іншу функцію Excel, яка може виконувати вертикальний пошук (LOOKUP, SUMPRODUCT, INDEX і MATCH) у поєднанні з EXACTA, що розрізняє регістр. Щоб отримати додаткові відомості, ви можете дізнатися з уроку – 4 способи зробити VLOOKUP чутливим до регістру в Excel.
2. VLOOKUP повертає перше знайдене значення
Як ви вже знаєте, VPR повертає значення з заданого стовпця, що відповідає першому знайденому збігу. Однак ви можете надати йому вилучення 2-го, 3-го, 4-го або будь-якого іншого повторення потрібного значення. Якщо вам потрібно витягти всі повторювані значення, вам знадобиться комбінація функцій ІНДЕКС (ІНДЕКС), ОСТАННЯ (МАЛИЙ) і ЛІНІЯ (РЯД).
3. До таблиці додано або видалено стовпець
На жаль, формули VPR припиняють працювати кожного разу, коли до таблиці пошуку додається або видаляється новий стовпець. Це відбувається тому, що синтакс VPR вимагає вказати повний діапазон пошуку та конкретний номер стовпця для вилучення даних. Природно, що заданий діапазон і номер стовпця змінюються, коли ви видаляєте стовпець або вставляєте новий.
Рішення: І знову функції поспішають на допомогу ІНДЕКС (ІНДЕКС) і БІЛЬШ ЕКСПОЗИЦІЙНИЙ (СІРНИК). У формулі ІНДЕКС+МАТЧ Ви окремо визначаєте стовпці пошуку та пошуку, і в результаті ви можете видалити або вставити скільки завгодно стовпців, не турбуючись про необхідність оновлення всіх пов’язаних формул пошуку.
4. Під час копіювання формули посилання на клітинки спотворюються
Цей заголовок вичерпно пояснює суть проблеми, чи не так?
Рішення: Завжди використовуйте абсолютні посилання на клітинки (із символом $) на записах діапазону, наприклад $ A $ 2: $ C $ 100 or $ A: $ C. У рядку формул ви можете швидко змінити тип посилання, клацнувши F4.
VLOOKUP – робота з функціями IFERROR і ISERROR
Якщо ви не хочете лякати користувачів повідомленнями про помилки # N / A, #VALUE! or # ІМ'Я?, ви можете показати порожню клітинку або власне повідомлення. Зробити це можна шляхом розміщення VPR у функцію ПОМИЛКА (IFERROR) в Excel 2013, 2010 і 2007 або використовувати низку функцій ЯКЩО+ЄПОМИЛКА (IF+ISERROR) у попередніх версіях.
VLOOKUP: робота з функцією IFERROR
Синтаксис функції ПОМИЛКА (IFERROR) простий і говорить сам за себе:
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
Тобто для першого аргументу ви вставляєте значення, яке потрібно перевірити на наявність помилки, а для другого аргументу ви вказуєте, що повертати, якщо помилку знайдено.
Наприклад, ця формула повертає порожню клітинку, якщо значення, яке ви шукаєте, не знайдено:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
Якщо ви хочете відобразити власне повідомлення замість стандартного повідомлення про помилку функції VPR, візьміть це в лапки, наприклад:
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: робота з функцією ISERROR
Оскільки функція ПОМИЛКА з'явився в Excel 2007, при роботі в більш ранніх версіях доведеться використовувати комбінацію IF (ЯКЩО) і ЕОШИБКА (ISERROR) ось так:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
Наприклад, формула ЯКЩО+ЄПОМИЛКА+ВПР, аналогічно формулі ЯКЩО ПОМИЛКА+ВПРпоказано вище:
=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))
На сьогодні все. Сподіваюся, цей короткий посібник допоможе вам розібратися з усіма можливими помилками. VPR і зробити так, щоб ваші формули працювали правильно.