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

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

У цій статті описано найпоширеніші причини на помилковий результат у функції VLOOKUP а також надаються варіанти для використання функцій INDEX і MATCH замість цього.

Порада.: Крім того, що стосуються на короткий довідник: VLOOKUP поради з виправлення неполадок , яка представляє поширені причини проблем #NA у зручному для файлу PDF. Можна надати спільний доступ до файлу PDF з іншими користувачами або друку для власних цілей.

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

Один обмеження VLOOKUP – це, його можна лише шукати значення лівого стовпця таблиці-масиву. Якщо значення для пошуку не в першому стовпці масиву, ви побачите помилку # n/a.

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

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

Помилка # n/a результат оскільки значення для пошуку «Кале» відображається у другому стовпці (продукти) аргументу «таблиця » A2:C10. У цьому випадку Excel шукає його у стовпці A, не стовпці b.

Рішення: можна спробувати виправити це, змінивши до функції VLOOKUP для посилання на стовпці правильний. Якщо це не можливо, спробуйте перемістити стовпців. Це також може бути дуже неможливим, якщо у вас великий або складний електронні таблиці, яких результатів обчислення інших значень клітинок, або може бути, є інші логічні причин, чому ви просто не переміщення стовпців. Рішення – це поєднання функцій INDEX і MATCH функції, які можна шукати значення у стовпці, незалежно від його місце розташування в таблиці підстановки за допомогою. У розділі далі.

Можна скористатися INDEX і MATCH замість

Функції INDEX і MATCH способами ефективного для багатьох випадках, у якому функція VLOOKUP не відповідає вашим потребам. Ключові зручно INDEX і MATCH, тому що можна шукати значення у стовпці в будь-якого місця в таблиці підстановки. Функція INDEX повертає значення із вказаної таблиці або діапазону, відповідно до його розташування. MATCH повертає відносне положення значення в діапазоні або таблиці. Використання функцій INDEX і MATCH разом у формулі для пошуку значення в таблиці або масиву, указавши відносне положення значення в таблиці масиву.

Існує кілька переваг використання INDEX і MATCH замість функції VLOOKUP.

  • З INDEX і MATCH, повернуте значення не потрібно в тому самому стовпці як стовпець підстановки. Це не відрізняється від функції VLOOKUP, у якому повернутого значення має бути вказаного діапазону. Як це важливо? Функції VLOOKUP вам потрібно знати номер стовпця, який містить повернуте значення. Хоча це може здатися складним, може бути довгим якщо великих таблиць і потрібно обчислити кількість стовпців. Крім того, якщо ви додавання або видалення стовпців у таблиці, ви повинні перерахунок і оновлення аргумент номер _ стовпця . INDEX і MATCH немає, якщо рахувати вимагається як стовпець підстановки відрізняється від стовпець, який містить повернуте значення.

  • Функції INDEX і MATCH, дають змогу рядка або стовпця в масиві, або вказати обидва. Це означає, що можна виконувати пошук значень по вертикалі та по горизонталі.

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

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

  • Функції INDEX і MATCH, який містить більшої гнучкості з збіги. Функції INDEX і MATCH можна знайти точний збіг, або значення, яке більше або менше, ніж значення для пошуку. Функція VLOOKUP шукатиме лише якнайбільше відповідає значення (за замовчуванням) або точне значення. Функція VLOOKUP також припускає за промовчанням, що першого стовпця таблиці-масиву відсортовано за алфавітом, і припустимо, що таблиці не настроєно таким чином, функція VLOOKUP повертає перший якнайбільше відповідає в таблиці, який може бути даних, яку потрібно знайти.

Синтаксис

Для створення синтаксис для INDEX і MATCH, вам потрібно використання аргументу масиву або посилання на функції INDEX і вкласти синтаксис MATCH всередині нього. Це виконати у формі.

= INDEX(array or reference, MATCH(lookup_value,lookup_array,[match_type])

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

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

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

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

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

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

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

Якщо аргумент точність _ пошуку має значення FALSE, і VLOOKUP не вдається знайти точний збіг дані – повертає значення помилки # n/a.

Рішення: Якщо ви впевнені, що відповідні дані зберігаються в електронній таблиці та функції VLOOKUP – це не зараження його, часу, щоб перевірити, на який вказує посилання клітинки немає прихованих пробілів або недрукованих символів. Також переконайтеся, що клітинки виконайте правильного типу даних. Наприклад, клітинки з числами від потрібно відформатувати як числоа не текст.

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

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

Якщо аргумент точність _ пошуку має значення TRUE, і значення підстановки _ значення менше за найменше значення в масиві, відобразиться повідомлення про помилку # n/a. TRUE виглядає на приблизного відповідають у масиві і повертає найближчим значення менше за значення підстановки.

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

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

Вирішення.

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

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

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

Якщо аргумент точність _ пошуку має значення TRUE, і будь-який стовпців підстановки не відсортовано за зростанням (AZ) –, може відобразитися повідомлення про помилку # n/a.

Вирішення.

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

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

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

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

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

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

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

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

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

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

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

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

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

Дякуємо за відгук! Схоже, вам може стати в нагоді допомога одного з наших спеціалістів служби підтримки Office, з яким ми вас можемо з’єднати.

×