Функція ВПР в форматі Excel

При тривалій роботі з електронними таблицями, кількість записів стає значним і виконати пошук будь-якого значення стає досить складним процесом. Розробники компанії Майкрософт подбали і про це, внаслідок чого з’явилася функція ВПР в форматі Excel, яка дозволить за пару митей відшукати потрібну комірку. За рахунок цього робота в значній мірі спрощується і економиться багато часу.

В англомовній версії програми можна зустріти VOOKUP – це одна і та ж функція. Розшифровується ця абревіатура, як вертикальний перегляд (Vertical LookUp). При завданні цієї функції в формулі, Excel проводить пошук по стовпцях і шукає збіг комбінації символів (цифр) шляхом звичайного перебору, починаючи з першого рядка по всіх колонках. Існує також функція ГПР – горизонтальний перегляд, однак вона менш затребувана, адже, як правило, колонок в таблиці завжди набагато менше, а відповідно і пошук з ВПР буде здійснений швидше.

Формат написання формули із застосуванням ВВР

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

  • відкрийте документ, в якому необхідно провести пошук;
  • в рядку пошуку формули наберіть ВПР (VOOKUP для аннглійской версії Excel);
  • відкрийте повний перелік формул і клацніть на кнопці «Знайти»;

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

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

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

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

Четверте поле не обов’язково до заповнення. Воно містить логічне значення і за замовчуванням встановлено «ІСТИНА» (або цифра 1).

Щоб розібратися, що і де вводити, розглянемо конкретний приклад.

Наприклад, нам потрібно в таблиці (див. Скріншот) за кодом товару знайти і відобразити найменування.

Примітка: На зображенні формула (в колонці Е) прописана лише для того, щоб наочно побачити, яким чином працює програма.

У прикладі 1 і 3 зазначено значення, яке відсутнє в заданому діапазоні. Так, як четвертий параметр не був заданий (не описує цифра 0 або слово ЛОЖЬ), програма аналізує вміст комірок і виводить в результаті то значення, яке найбільш близьке за значенням (в даному випадку замість коду товару 2 і 14 на екран були виведені значення рядків з кодом 1 і 13). Якщо ж користувачеві необхідний результат лише точного збігу, в четвертому параметрі необхідно вказувати значення «0». Це можна наочно побачити за наступним скриншоту (значення 2 не було знайдено, тому в осередку відображено # Н / Д).

Також, як шуканої величини можна використовувати безпосередньо осередок. Іншими словами: при введенні адреси осередку здійснюється пошук значення, яке прописано в ній. Більш наочно це видно на скріншоті.

Чи довго може здійснюватися пошук?

Безліч користувачів не прописують четвертий параметр (не вводять значення 0). Вважається, що в цьому випадку процес пошуку здійснюється швидше. Якщо ж масив значень величезний, пошук може затягтися на значний час, при цьому, при введенні значення БРЕХНЯ, операція триватиме дійсно мало не в два рази довше, ніж без заповнення четвертого поля у вікні «Аргументи функції». Деякі користувачі відписувати на тематичних форумах, що при вказівці нуля, програма могла повністю подвисать, без примусового завершення роботи Excel просто висів і самостійно розвантажуватися з системи не хотів.

Якщо у Вашій таблиці є сотні, а то й тисячі формул, найкраще виконати сортування першого стовпчика. Це допоможе скоротити час відображення значень з формулами, так званого перерахунку, ВВР в 4-5 разів. Це обумовлено тим, що програмою, як і людині, простіше мати справу з уже відсортованими даними. В цьому випадку починає працювати, так званий, бінарний пошук. До речі кажучи, при введенні значення 0 в якості 4-го параметра, бінарний пошук не працює. Це дійсно проблема, через що і час пошуку значно більше. Розробники намагаються якимось чином оптимізувати цю задачу, але на даний момент цього нема.

Ссылка на основную публикацию