Формули підстановки excel впр, індекс і ПОИСКПОЗ, exceltip
Якщо зробити пошук по функціям підстановки, Google покаже, що ВПР набагато популярніше функції ІНДЕКС. Воно й зрозуміло, адже щоб надати функції ІНДЕКС той же функціонал, що і ВПР, необхідно скористатися ще однією формулою - ПОИСКПОЗ. Що стосується мене, було завжди непросто спробувати і освоїти дві нові функції одночасно. Але вони дають більше можливостей і гнучкості в створенні електронних таблиць. Але про все по порядку.
Функція ВПР ()
Функція ІНДЕКС ()
Щоб вирішити нашу проблему в один крок, необхідно скористатися формулами ІНДЕКС та ПОИСКПОЗ. Складність даного підходу полягає в тому, що потрібно застосувати дві функції, які, можливо, ви ніколи не застосовували до цього. Для спрощення розуміння вирішимо це завдання в два етапи.
Почнемо з функції ІНДЕКС. Жахлива назву. Коли хто-небудь говорить «індекс», у мене в голові не виникає жодної асоціації, чим же займається ця функція. А вимагає вона цілих три аргументи: = ІНДЕКС (масив; номер_рядка; [номер_стовпчика]).
Говорячи по-простому, Excel йде в масив даних і повертає значення, що знаходиться на перетині зазначеного рядка і стовпця. Начебто просто. Таким чином, формула = ІНДЕКС ($ A $ 2: $ C $ 6; 4; 2) поверне значення, що знаходиться в осередку B5.
Стосовно до нашої проблеми, щоб повернути табельний номер працівника, формула повинна виглядати наступним чином = ІНДЕКС ($ A $ 2: $ A $ 6;?; 1). Виглядає як нісенітниця, але якщо ми замінимо знак питання формулою ПОИСКПОЗ, у нас є рішення.
Функція ПОИСКПОЗ ()
Синтаксис цієї функції такий: = ПОИСКПОЗ (шукане_значення; просматріваеми_массів; [тіп_сопоставленія]).
Вона каже Excel: «Знайди шукане_значення в масиві даних і поверни номер рядка масиву, в якій це значення зустрічається». Таким чином, щоб знайти у якому рядку перебувати ім'я співробітника в осередку A10, необхідно прописати формулу = ПОИСКПОЗ (A10; $ B $ 2: $ B $ 6; 0). Якщо в осередку A10 буде ім'я «Колін Фарел», тоді ПОИСКПОЗ поверне 5-й рядок масиву B2: B6.
Ну, в принципі, все. Функція ПОИСКПОЗ вказує функції ІНДЕКС, в якому рядку шукати значення. Замініть знак питання в формулі ІНДЕКС формулою ПОІСКОПОЗ і ви отримаєте еквівалент ВВР з можливістю пошуку даних, що знаходяться лівіше ключового стовпця. Вийти так:
На початку було незвично, був величезний спокуса вставити ще одну колону і працювати, як завжди працював. Але з часом використання функції ІНДЕКС увійшло в звичку. Виявилося, що це швидше і вимагає менших маніпуляцій. Так що наступного разу, коли у вас виникне бажання поставити номеру стовпчика негативне число в ВВР, натисніть сполучення двох дивних функцій ІНДЕКС та ПОИСКПОЗ, щоб вирішити свою проблему.
Вам також можуть бути цікаві такі статті
Добрий день.
Яку надбудову мені використовувати для ВПР якщо хочу порівняти дві таблиці
за словами. В одній ПІБ повністю, в іншій ПІБ повністю і внутрішній номер.
Приклад: Іванов Олексій Петрович і Іванов Олексій Петрович PS010.
Чи можна шукати по масці по першим 10 символам? і як таке здійснити?
Добридень!
Вам необхідна функція ПСТР. Її аргументи: осередок, в якій необхідний текст; порядковий номер символу, з якого починати повернення даних; кількість символів, які необхідно вернуть.Прімерно так: = ПСТР (А1; 1; 10)
У неї є «сестри» ЛЕВСИМВ і ПРАВСИМВ, які, відповідно, повертають задану кількість символів зліва і справа
Якщо необхідно порівняти дві таблиці, мабуть, найпростіше це буде представити як
= ВПР (ПСТР (А1; 1; 10); 'діапазон'; 'номер стовпця'; 0)
Відповідно, перша таблиця з номером, друга без нього
Повертати буде значення або Н / Д
Якщо Н / Д, то задовольняє даними значенням нічого немає 🙂
ВАЖЛИВО! Ви повинні бути впевнені, що повертаючи тільки 10 знаків, ви повернете повністю значення без номера
В ідеалі тоді потрібно створити додатковий стовпець в таблиці без номера, куди за допомогою ПСТР або ЛЕВСИМВ винести тільки перші 10 знаків, і порівнювати вже конкретно з ним
Якщо перший параметр функції ВПР посилається на клітинку, яка містить число, але яке зберігається в осередку в текстовому вигляді, а перший стовпець масиву містить числа в правильному форматі, то пошук буде невдалим. Переклад числа в текст проводиться через зчеплення з нового рядка, яка змушує Excel перетворити тип даних.