Пошук значень за допомогою функцій ВПР, ІНДЕКС та ПОШУКПОЗ

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

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

Функції ВПР і HLOOKUP , і навіть INDEX і MATCH одна із найкорисніших функцій в Excel.

Примітка: Функція майстра підстановки більше не доступна Excel.

Нижче наведено приклад використання функції ВПР.

=ВПР(B2;C2:E7,3,ІСТИНА)

У цьому прикладі B2 є першим аргументом - Елементом даних, який повинен працювати функції. Для ВВР цей перший аргумент є значенням, яке потрібно знайти. Цей аргумент може бути посиланням на комірку або фіксованим значенням, наприклад, smith або 21000. Другий аргумент — це діапазон осередків C2-:E7, в якому виконується пошук потрібного значення. Третій аргумент - це стовпець у діапазоні осередків, що містить потрібне значення.

Четвертий аргумент є обов'язковим. Введіть TRUE або FALSE. Якщо ввести ІСТИНА або залишити аргумент порожнім, функція повертає приблизний збіг значення, вказаного як перший аргумент. Якщо ввести значення FALSE, функція відповідатиме значенню, наданому першим аргументом.Іншими словами, якщо залишити четвертий аргумент порожнім або ввести TRUE, ви отримуєте більшу гнучкість.

У цьому прикладі показано, як функція працює. При введенні значення в комірку B2 (перший аргумент) ВПР виконує пошук осередків у діапазоні C2:E7 (2-й аргумент) і повертає найближчий приблизний збіг третього стовпця в діапазоні, стовпця E (3-й аргумент).

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

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

Використання INDEX та MATCH замість ВПР

При використанні ВВР існують певні обмеження: функція ВВР може шукати значення тільки зліва направо. Це означає, що стовпець, що містить шукати значення, завжди повинен розташовуватися ліворуч від стовпця, що містить значення, що повертається. Тепер, якщо електронну таблицю не створено таким чином, не використовуйте VLOOKUP. Натомість використовуйте поєднання функцій INDEX і MATCH.

У цьому прикладі представлений невеликий список, у якому шукане значення (Воронеж) не знаходиться у крайньому лівому стовпці. Тому ми можемо використовувати функцію ВПР. Для пошуку значення "Воронеж" у діапазоні B1:B11 буде використовуватися функція ПОШУКПОЗ. Воно знайдено у рядку 4. Потім функція ІНДЕКС використовує це значення як аргумент пошуку та знаходить чисельність населення Воронежа у четвертому стовпці (стовпець D). Використана формула показана в комірці A14.

Додаткові приклади використання INDEX та MATCH замість ВПР див.у статті, https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ Білл Джелен (Bill Jelen), Microsoft MVP.

Спробуйте попрактикуватися

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

Приклад ВПР на роботі

Скопіюйте наведені нижче дані в порожню електронну таблицю.

Порада: Перед вставкою даних в Excel задайте ширину стовпців від A до C 250 пікселям і натисніть кнопку Обтікати текст (Вкладка Головна , група Вирівнювання ).

ПОШУК, ПОШУКБ (функції ПОШУК, ПОШУКБ)

У цій статті описано синтаксис формули та використання функцій ПОШУК і ПОШУКБ у Microsoft Excel.

Опис

Функції ПОШУК І ПОШУКБ знаходять один текстовий рядок в інший і повертають початкову позицію першого текстового рядка (вважаючи перший символ другого текстового рядка). Наприклад, щоб знайти позицію літери "n" у слові "printer", можна використати таку функцію:

Ця функція повертає 4, оскільки "н" є четвертим символом у слові "принтер".

Також можна знайти слова в інших словах. Наприклад, функція

повертає 5тому що слово "base" починається з п'ятого символу слова "database". Можна використовувати функції ПОШУК і ПОШУКБ для визначення положення символу або текстового рядка в іншому текстовому рядку, а потім повернути текст за допомогою функцій ПСТР і ПСТРЛ або замінити його за допомогою функцій ЗАМЕНІТИ і ЗАМІНИТИ. Ці функції показані у прикладі 1 цієї статті.

• Ці функції можуть бути доступні не всіма мовами.
• Функція ПОШУКБ відраховує по два байти на кожний символ, тільки якщо мовою за промовчанням є мова з підтримкою БДЦС.Інакше функція ПОШУК працює так само, як функція ПОШУК, і відраховує по одному байти на кожен символ.

До мов, що підтримують БДЦС, належать японська, китайська (спрощений лист), китайська (традиційний лист) та корейська.

Синтаксис

Аргументи функцій ПОШУК та ПОШУКБ описані нижче.

• Шуканий_текст Обов'язковий текст.
• Текст, що переглядається Обов'язковий. Текст, у якому потрібно знайти значення аргументу. шуканий_текст.
• Початкова_позиція Необов'язковий. перегляданий_текст, з якого слід розпочати пошук.

Зауваження

• Функції ПОШУК і ПОШУКБ не враховують регістр. Якщо потрібно враховувати регістр, використовуйте функції. ЗНАЙТИ і ЗНАЙТИБ.
• В аргументі шуканий_текст можна використовувати знаки підстановки: знак питання (?) та зірочку (*).Питальний знак відповідає будь-якому знаку, зірочка — будь-якої послідовності знаків.~).
• Якщо значення find_text не знайдено, #VALUE!
• Якщо аргумент початкова_позиція опущений, він належить рівним 1.
• Якщо start_num не більше 0 (нуль) або більше довжини аргументу within_text , #VALUE! Повертається значення помилки.
• Аргумент початкова_позиція можна використовувати, щоб пропустити певну кількість знаків. ПОШУК потрібно використовувати для роботи з текстовим рядком "МДС0093.Чоловічий Одяг". Щоб знайти перше входження "М" в описовій частині текстового рядка, задайте для аргументу початкова_позиція значення 8, щоб пошук не виконувався у тій частині тексту, яка є серійним номером (у даному випадку – "МДС0093"). Функція ПОШУК починає пошук з восьмого символу, знаходить знак, вказаний у аргументі шуканий_текст, у наступній позиції, і повертає число 9. Функція ПОШУК завжди повертає номер знака, рахуючи від початку тексту, що переглядається, включаючи символи, які пропускаються, якщо значення аргументу початкова_позиція більше 1.

Приклади

Скопіюйте зразок даних з наступної таблиці та вставте їх у комірку A1 нового аркуша Excel. Щоб відобразити результати формул, виділіть їх та натисніть клавішу F2, а потім – клавішу Enter. За потреби змініть ширину стовпців, щоб побачити всі дані.