Чому не працює формула впр (vlookup) в excel - рішення

Функція ВПР найпопулярніша і часто використовувана функція. Знання даної функції дуже допоможе вам у вашій роботі. Опис роботи функції ВПР з прикладом ви можете подивитися в спеціальній статті, а в даному уроці ми розглянемо приклади, коли функція ВПР не працює або видає помилки.

Якщо ви навчилися використовувати функцію, але у вас виникає помилка, то в цій статті ми розглянемо можливі помилки і шляхи їх вирішення.

Стовпець пошуку шуканого значення не є крайнім лівим.

Найпоширеніша помилка, коли ВВР не працює. Давайте розглянемо той же приклад, що ми розглядали при описі функції ВПР. У нас є таблиця замовлень і Прайс лист.

Зверніть увагу, що в даному прикладі таблиця Прайс лист відрізняється. Назви товарів тепер перебувають не в першому стовпці, а в третьому, після ціни товару.

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

Рішення

1. Якщо є можливість, то перенесіть стовпець, де шукається шукане значення в крайнє ліве положення (або просто лівіше стовпчика з якого необхідно повертати значення, в нашому прикладі щодо стовпчика з ціною) як зазначено на малюнку вище.

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

Виділяємо перший стовпець прайс листа, вибираємо «Головна», «Осередок», «Вставити», «Вставити стовпці на лист». Або використовуйте порожній стовпець, якщо він у вас вже є.
Встаємо в перший осередок допоміжного стовпця і просто робимо посилання на потрібний нам стовпець і простягаємо формулу вниз.

Отримали точну копію стовпчика з найменуванням товару лівіше ціни, тепер можна використовувати функцію ВПР. Таблицю тепер необхідно брати починаючи з нашого допоміжного стовпця.

Відео: Функція ВПР в Excel (VLOOKUP в Excel) (Урок 1) [Eugene Avdukhov, Excel Для Всіх]

3. Можна використовувати комбінацію функцій ІНДЕКС (INDEX) і ПОИСКПОЗ (MATCH), як більш гнучку альтернативу для ВПР.

Функція ВПР повертає помилку # Н / Д (# N / A)

# Н / Д (# N / A) означає «відсутня» ( «not available»). При протягуванні формули ВПР у вас з`являється # Н / Д. Питання навіть не в тому, що ВПР не працює, а чому з`являються дані значення.

Рішення

1. Не закріплений діапазон таблиці

Якщо при протягуванні формули ВПР у вас перше значення підтяглося а в інших відобразилося # Н / Д то швидше за все ви не закріпили діапазон таблиці за допомогою $ і при протягуванні у вас таблиця почала з`їжджати вниз. Ще раз прочитайте уважно опис функції ВПР з прикладом.

2. Шукане значення і значення в просматриваемом діапазоні не збігається

На малюнку вище є # Н / Д два рази. Один раз навпроти товару «Савок» і інший навпаки слова «Стіл».

В першому випадку зрозуміло - товару «Савок» у нас немає в прайс-листі, звідси і помилка.

У другому випадку незрозуміло, товар «Стіл» у нас є, але все одно Н / Д #. Як правило помилка полягає в написанні слів і форматах або в таблиці замовлень або на сторінці Прайс лист.

Відео: ІНДЕКС та ПОИСКПОЗ або як бути, коли не працює ВПР ()?

У нашому випадку у нас після слова "Стіл" стоїть пробіл, тому дане слово не перебуває у прайс-листі. Для початку перевірте вміст комірок на невидимі прогалини. Особливо часто вони зустрічаються в кінці рядка. Натисніть на осередок і перейдіть в кінець формули. Видаліть зайві прогалини, якщо вони є.

[Blockquote_gray "] Читайте статтю як видалити зайві прогалини в Excel[/ Blockquote_gray]

В даному прикладі можна також використовувати функцію СЖПРОБЕЛИ

= ВПР (СЖПРОБЕЛИ (A3)-$ F $ 2: $ I $ 16-3-0)

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

У нашому випадком ми шукаємо текстові значення - «Найменування товару», іноді пошук йде по числовим значенням. В даному випадку # Н / Д може з`являтися, коли шукане значення, наприклад у форматі числа, а в таблиці у вигляді тексту. Для виправлення помилки потрібно перевести формат тексту в числа або навпаки, тобто зробити однаковий формат.

Ще більш рідкісний випадок, це коли в слові може бути замість російських слів латинські і навпаки. Наприклад A і А це різні літери різних алфавітів, тому і слова будуть різними для ВПР. Щоб перевірити, просто зробіть в окремому стовпці посилання одного осередку на іншу = A4 = F6 в нашому прикладі ця формула поверне «Брехня» так як ці осередки не рівні (в одному слові є зайвий пробіл), аналогічна ситуація буде якщо замість кириличної літери «С »буде англійська літера« C ».

Функція ВПР повертає помилку # значить! (#VALUE!)

Зазвичай Excel повідомляє про помилку #VALUE! (# Значить!), Коли значення, використане в формулі, не підходить за типом даних. Якщо брати функцію ВПР, то зазвичай варто розглянути дві основні причини помилки # значить !.

1. Шукане значення більше 255 символів.

У функції ВПР є обмеження на довжину шуканого значення - воно не повинно бути більше 255 символів. Якщо ви зіткнулися з такою проблемою, то рекомендуємо використовувати для цих цілей все ту ж зв`язку ІНДЕКС + ПОИСКПОЗ (INDEX + MATCH).

Відео: Використання функції ВПР в MS Excel 2007

2. Неправильно зазначений повний шлях на іншу книгу.

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

3. Аргумент номер_стовпчика в функції ВПР менше 1

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

Інші важливі особливості роботи функції ВПР

Якщо ви хочете розбити не саму осередок, а текст в осередку по стовпцях, то тоді вам потрібно дивитися іншу статтю «Як розбити текст по стовпцях в Excel»

1. ВВР не чутлива до регістру

Функція ВПР не чутлива до регістру і для неї все символи нижнього і верхнього регістру будуть однакові. Тобто, слово «Стіл», «СТІЛ» і «стіл» для функції ВПР будуть однаковими.

Якщо вам необхідно враховувати регістр при використанні ВВР, то використовуйте іншу функцію Excel, наприме (VIEW, СУММПРОИЗВ, ІНДЕКС та ПОИСКПОЗ) в поєднанні з збігаючись, яка розрізняє регістр і повертається ИСТИНУ або БРЕХНЯ при збігу або не збігом з шуканим значенням.

2. ВВР повертає перше знайдене значення

Завжди пам`ятайте, що ВПР повертає перше знайдене значення, тому якщо в нашому прикладі в прайс-листі помилково буде два однакових товару з різною ціною, то повертатися буде тільки перший ціна.

3. Робота функції ВПР і додаванні або видаленні стовпчика

Як правило при написанні функції ВПР ми в ручну відраховуємо стовпець і вказуємо його у вигляді числа. У нашому прикладі з прайс листом. Ви візуально визначили, що разом з допоміжним стовпцем, ціна товару знаходиться в третьому стовпці і вказали цифру 3 в функцію.

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

Для вирішення цієї проблеми можна використовувати функцію ПОИСКПОЗ для пошуку номера стовпчика. Завданням пошуку значення ми можемо вказати текст «Ціна» і шукати в рядку - шапці таблиці Прайс лист. Тепер якщо додавати стовпці в таблицю Прайс лист, то функція ПОИСКПОЗ все одно знайде осередок зі словом «Ціна» і поверне на номер позиції даного осередку.