Виправлення помилки #N/A у функції VLOOKUP

У цій статті наведено найпоширеніші причини, які можна знайти в хибному результаті, і дає можливість використовувати індексацію та відповідну функцію.

Порада.: Також можна посилатися на короткий довідник: поради з виправлення неполадок , які містять поширені причини #NA проблем у ЗРУЧНОМУ файлі PDF. Ви можете надати спільний доступ до PDF-файлу іншим користувачам або друкувати для власного посилання.

Проблема: значення підстановки міститься не в першому стовпці аргументу масив_таблиці

Одне обмеження функції VLOOKUP – це лише те, що в масиві таблиці можна шукати лише значення з лівого стовпця. Якщо значення підстановки немає в першому стовпці масиву, з'явиться повідомлення #N/A.

У таблиці нижче ми хочемо отримати кількість проданих одиниць капусти.

Помилка #NA у функції VLOOKUP: значення підстановки міститься не в першому стовпці масиву таблиці.

#N/A призводить до помилки, тому що значення підстановки "Кале" відображається у другому стовпці (продюсувати) table_array аргументу a2: C10. У цьому випадку програма Excel шукає її у стовпці A, а не у стовпці B.

Вирішення: ви можете спробувати вирішити цю проблему, змінивши компонент VLOOKUP, щоб посилатися на потрібний стовпець. Якщо це не можливо, спробуйте перемістити стовпці. Це також може бути вельми непрактично, якщо у вас є великі або складні електронні таблиці, де значення комірок – це результати інших обчислень, або, можливо, є інші логічні причини, через які не можна переходити між стовпцями. Рішення – використовувати комбінацію ІНДЕКСНИХ і ВІДПОВІДНИХ функцій, які можуть шукати значення в стовпці незалежно від положення розташування в таблиці підстановки. Переглянути наступний розділ.

Використання ІНДЕКСУ або ЗІСТАВЛЕННЯ замість цього

Індексування та зіставлення – це непогані варіанти для багатьох ВИПАДКІВ, у яких функція VLOOKUP не відповідає вашим потребам. Основні переваги ІНДЕКСУВАННЯ або ЗІСТАВЛЕННЯ – це те, що ви можете шукати значення в стовпці в будь-якому розташуванні в таблиці підстановки. Функція INDEX повертає значення з вказаної таблиці або діапазону – відповідно до його розташування. Функція MATCH повертає відносне розташування значення в таблиці або діапазоні. Використовуйте індекс і ЗІСТАВТЕ разом у формулі, щоб шукати значення в таблиці або масиві, вказавши відносне розташування значення в таблиці або масиві.

Нижче наведено кілька переваг використання ІНДЕКСУ або ЗІСТАВЛЕННЯ замість функції VLOOKUP.

  • З ІНДЕКСАМИ та ВІДПОВІДНИМ значенням повернуте значення не має бути в тому самому стовпці, що й стовпець підстановки. Цей параметр відрізняється від функції VLOOKUP, у якому повернуте значення має бути в зазначеному діапазоні. Чому це важливо? Для функції VLOOKUP потрібно знати номер стовпця, який містить повернуте значення. Хоча це може здатися складним, воно може бути громіздким, якщо у вас є велика таблиця, і потрібно підрахувати кількість стовпців. Крім того, якщо додати або видалити стовпець у таблиці, потрібно виконати перерахунок та оновити аргумент col_index_num . З функціями INDEX і MATCH перерахунок непотрібний, тому що стовпець підстановки відрізняється від стовпця, який містить повернуте значення.

  • За допомогою ІНДЕКСУВАННЯ та ЗІСТАВЛЕННЯ можна вказати рядок або стовпець у масиві або вказати обидва. Це означає, що ви можете шукати значення як по вертикалі, так і по горизонталі.

  • За допомогою функцій INDEX і MATCH можна шукати значення в будь-якому стовпці. На відміну від VLOOKUP, у якому можна лише шукати значення в першому стовпці таблиці – індекс і відповідність працюватиме, якщо значення підстановки в першому стовпці, останньому або будь-якому місці між ними.

  • ІНДЕКСУВАННЯ та ЗІСТАВЛЕННЯ пропонують гнучкість для динамічного посилання на стовпець, який містить повернуте значення.Це означає, що ви можете додати стовпці до таблиці без розриву ІНДЕКСУ та відповідності. З іншого боку, ФУНКЦІЯ VLOOKUP переривається, якщо потрібно додати стовпець до таблиці, тому що він робить статичну посилання на таблицю.

  • ІНДЕКС і відповідність пропонує більшу гнучкість у відповідності.ІНДЕКСУВАННЯ та відповідність можна знайти точну відповідність або значення, яке більше або менше за значення підстановки. ФУНКЦІЯ VLOOKUP буде шукати лише найближчий відповідник значення (за замовчуванням) або точне значення. ФУНКЦІЯ VLOOKUP також припускає, що перший стовпець таблиці відсортовано в алфавітному порядку, і припустимо, що ваша таблиця не налаштовано таким чином, ФУНКЦІЯ VLOOKUP поверне перший найближчий збіг в таблиці, яка може бути не для даних, які ви шукаєте.

Синтаксис

Щоб створити синтаксис для ІНДЕКСУ або ЗІСТАВЛЕННЯ, потрібно використати аргумент "масив/посилання" з функції INDEX і вкласти синтаксис ВІДПОВІДНОГО синтаксису всередині неї. У цій формі можна виконати такі дії:

=INDEX(масив або посилання;MATCH(значення_підстановки;масив_який_переглядається;[тип_зіставлення])

За допомогою функції INDEX або MATCH можна замінити VLOOKUP на прикладі вище. Синтаксис матиме такий вигляд:

=INDEX(C2:C10;MATCH(B13;B2:B10;0))

Простою українською мовою це означає:

= INDEX (повертає значення з C2: C10, яке буде відповідати (Кале, що знаходиться десь в області B2: B10 масив, у якому повернуте значення – перше значення, яке відповідає Кале)).

Функції INDEX і MATCH можна використовувати замість функції VLOOKUP.

Формула шукає перше значення в діапазоні C2:C10, яке відповідає значенню Капуста (у B7) і повертає значення в клітинці C7 (100), тобто перше значення, яке збігається з "Капуста".

Проблема: не вдається знайти точний збіг

Якщо аргумент range_lookup має значення FALSE, а ФУНКЦІЯ VLOOKUP не може знайти точну відповідність даних – повертає #N/a помилку.

Рішення: Якщо ви впевнені, що у вашій електронній таблиці є відповідні дані, а ФУНКЦІЯ VLOOKUP не вловлю його, Приділіть час переконатися, що клітинки, на які посилається, не мають прихованих пробілів або недрукованих символів. Крім того, переконайтеся, що клітинки наслідуватимуть потрібний тип даних. Наприклад, клітинки з числами мають бути відформатовані як число, а не текст.

Крім того, можна скористатися функцією Clean або TRIM , щоб очистити дані в клітинках.

Проблема: значення підстановки менше найменшого значення в масиві

Якщо аргумент range_lookup має значення TRUE (істина), і значенням підстановки менше найменшого значення в масиві – з'явиться повідомлення #N/a. У разі значення ІСТИНА здійснюється пошук приблизної відповідності в масиві та повертається найближче значення, менше за значення підстановки.

У наведеному нижче прикладі значення підстановки – 100, але в діапазоні B2:C10 немає значень, менших за 100, тому відображається повідомлення про помилку.

Помилка N/A у функції VLOOKUP, яка виникає, якщо значення підстановки менше найменшого значення в масиві

Вирішення.

  • За потреби виправте значення підстановки.

  • Якщо ви не можете змінити значення підстановки та потребуєте більшої гнучкості за допомогою відповідних значень, спробуйте використати індекс або ЗІСТАВЛЕННЯ замість функції VLOOKUP: перегляньте розділ вище в цій статті. За допомогою ІНДЕКСУВАННЯ або ЗІСТАВЛЕННЯ можна шукати значення, більші за менші, менше або дорівнює значенню підстановки. Щоб отримати докладні відомості про використання ІНДЕКСУВАННЯ або ЗІСТАВЛЕННЯ замість функції VLOOKUP, зверніться до попереднього розділу в цій статті.

Проблема: стовпець підстановки не відсортовано за алфавітом

Якщо аргумент range_lookup має значення TRUE, а один із стовпців підстановки не відсортовано за зростанням (а-я), то з'явиться повідомлення #N/a.

Вирішення.

  • Змініть функцію VLOOKUP таким чином, щоб вона шукала точний збіг. Для цього установіть для аргументу точність_пошуку значення ХИБНІСТЬ. Не потрібно сортувати значення FALSE.

  • Використовуйте функцію INDEX або MATCH, щоб шукати значення в невідсортованій таблиці.

Проблема: значення є великим числом із рухомою комою

Якщо у клітинках є значення "час" або "великі десяткові числа", програма Excel повертає #N/A через точність рухомої точки. Числа з плаваючою комою – числа, які слідують за Десятковою комою. (Excel зберігає значення часу як числа з плаваючою комою). Програма Excel не може зберігати числа з дуже великими плаваючою точкою, щоб функція працювала належним чином, числа з плаваючою комою повинні бути округленими до 5 десяткових розрядів.

Вирішення. Скоротіть числа, округливши їх до п’яти десяткових розрядів за допомогою функції ROUND.

Відповіді на запитання щодо конкретних функцій

Опублікуйте запитання на форумі спільноти Microsoft Excel

Допомога в удосконаленні програми Excel

Маєте пропозиції щодо того, як покращити наступну версію програми Excel? Тоді перегляньте розділи на веб-сайті Excel User Voice.

Додаткові відомості

Примітка.:  Цю сторінку перекладено за допомогою засобу автоматичного перекладу, тому вона може містити смислові, синтаксичні або граматичні помилки. Ми вважаємо, що цей вміст стане вам у пригоді. Повідомте нас, чи була ця інформація корисною. Для довідки цю статтю можна переглянути англійською мовою.

Удосконалення навичок роботи з Office
Ознайомтеся з навчальними матеріалами
Отримуйте нові функції раніше за інших
Приєднайтеся до оцінювачів Office

Ця інформація корисна?

Дякуємо за ваш відгук!

Дякуємо, що знайшли час і надіслали нам відгук! Можливо, у нас не буде часу відповісти на кожен коментар, але докладемо максимум зусиль, щоб переглянути їх усі. Вас цікавить, як ми використовуємо ваші відгуки?

×