Ребята, всем привет! 👋 Продолжаем изучать Excel.
При работе в Excel могут возникать ситуации, в которых вам нужно искать значение. В этом уроке я покажу, как применять комбинацию функций ИНДЕКС и ПОИСКПОЗ для выполнения горизонтального и вертикального поиска, двустороннего поиска, поиска с учетом регистра и поиска, отвечающего нескольким критериям.
✍️ Если вы только начинаете осваивать Excel мы уверены, каждый может найти для себя что-то новое!
✨ А прежде, чем мы начнем 📣 напомню, теперь у нас на канале есть удобный рубрикатор 👉 Быстрый поиск решения. Путеводитель по Excel, а все видео 📽 предыдущих уроков доступны и на YouTube.
📝Немного теории...
⏩ Функция ИНДЕКС()
Функция ИНДЕКС возвращает значение элемента в таблице или массиве на основе указанных вами номеров строк и столбцов
Синтаксис функции:
= ИНДЕКС(массив,номер_строки,[номер_столбца])
Аргументы функции
- массив - это диапазон ячеек, именованный диапазон или таблица.
- Номер_строки — это номер строки в массиве, из которого нужно вернуть значение. Если этот аргумент опущен, требуется следующий – номер_столбца.
- Номер_столбца — это номер столбца, из которого нужно вернуть значение. Если он опущен, требуется номер_строки.
О функции ИНДЕКС в Excel вы должны знать, что:
- Функция ИНДЕКС может работать с вертикальными и горизонтальными диапазонами.
- Функция ИНДЕКС не чувствительна к регистру.
- Номер строки идет перед номером столбца (если вам нужны оба числа) в формуле ИНДЕКС.
⏩ Функция ПОИСКПОЗ()
Функция ПОИСКПОЗ в Excel используют для поиска точной позиции искомого значения в списке или массиве данных.
Данная функция возвращает число, соответствующее позиции искомого значения.
Синтаксис функции:
=ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])
Аргументы функции
- искомое_значение – значение, с которым вы хотите сопоставить данные из массива или списка данных;
- просматриваемый_массив – диапазон ячеек в котором вы осуществляете поиск искомых данных;
- [тип_сопоставления] – (не обязательно) – этот аргумент определяет каким образом, будет осуществлен поиск. Допустимые значения для аргумента: “-1”, “0”, “1”
О функции ПОИСКПОЗ() в Excel вы должны знать, что:
- Функция ПОИСКПОЗ возвращает позицию значения поиска в массиве поиска, а не само значение.
- Функция ПОИСКПОЗ возвращает первое совпадение в случае дублирования.
- Как и функция ИНДЕКС, функция ПОИСКПОЗ может работать с вертикальными и горизонтальными диапазонами.
- ПОИСКПОЗ также не чувствителен к регистру.
- Если поисковое значение формулы ПОИСКПОЗ имеет текстовую форму, заключите его в кавычки
☑️ Пример объединения ИНДЕКС и ПОИСКПОЗ
Чтобы понять, как мы можем комбинировать функции ИНДЕКС и ПОИСКПОЗ рассмотрим пример.
Например, чтобы знать цену товара, мы должны использовать формулу:
=ИНДЕКС(A2:D7;ПОИСКПОЗ(G1;B2:B7;0);ПОИСКПОЗ(F2;A1:D1;0))
☑️ ИНДЕКС и ПОИСКПОЗ, чтобы применить поиск слева
Теперь предположим, что вам нужно знать категорию товара
Фактически, возможность поиска слева является одним из аспектов, в котором комбинация ИНДЕКС и ПОИСКПОЗ превосходит ВПР.
В данном случае все, что вам нужно сделать, это изменить значение в ячейке F2 на «Категория» и использовать ту же формулу, что и показанная выше, функции ИНДЕКС и ПОИСКПОЗ сразу же сообщат вам ответ:
☑️ ИНДЕКС и ПОИСКПОЗ для применения двустороннего поиска
Теперь вы можете составить формулу комбинации ИНДЕКС и ПОИСКПОЗ со значениями динамического поиска для применения двустороннего поиска:
- Определяем категорию:
=ИНДЕКС(A2:D7;ПОИСКПОЗ(G1;B2:B7;0);ПОИСКПОЗ(F2;A1:D1;0))
- Определяем производителя:
=ИНДЕКС(A2:D7;ПОИСКПОЗ(G1;B2:B7;0);ПОИСКПОЗ(F3;A1:D1;0))
- Определяем цену:
=ИНДЕКС(A2:D7;ПОИСКПОЗ(G1;B2:B7;0);ПОИСКПОЗ(F4;A1:D1;0))
📣 Примечание: Если информацию о товаре задать в виде выпадающего списка:
то после применения вышеуказанных формул вы можете легко получить информацию о любых товарах:
☑️ ИНДЕКС и ПОИСКПОЗ для применения поиска с учетом регистра
Из приведенных выше примеров мы знаем, что функции ИНДЕКС и ПОИСКПОЗ не чувствительны к регистру. Однако в тех случаях, когда вам нужна формула для различения символов верхнего и нижнего регистра, вы можете добавить функцию СОВПАД() к вашим формулам следующим образом:
{=ИНДЕКС(A2:D7;ПОИСКПОЗ(ИСТИНА;СОВПАД(G1;A2:A7);0);ПОИСКПОЗ(F2;A1:D1;0))}
⚠ ВАЖНО: это формула массива, требующая ввода с помощью Shift + Ctrl + Enter.
☑️ ИНДЕКС и ПОИСКПОЗ для применения поиска по нескольким критериям
При работе с большой базой данных с несколькими столбцами и заголовками строк всегда сложно найти что-то, что удовлетворяет нескольким условиям.
В этом случае для поиска по нескольким критериям воспользуйтесь приведенной ниже формулой:
{=ИНДЕКС(D2:D7;ПОИСКПОЗ(1;(G1=A2:A7)*(G2=B2:B7)*(G3=C2:C7);0))}
ВАЖНО! не забудьте ввести Shift + Ctrl + Enter.
А на этом сегодня все. 👏 Теперь вы знаете как применять комбинацию функций ИНДЕКС и ПОИСКПОЗ для выполнения горизонтального и вертикального поиска, двустороннего поиска, поиска с учетом регистра и поиска, отвечающего нескольким критериям. Я надеюсь, что вы нашли этот урок полезным.
Продолжение следует.., а поэтому подписывайтесь на канал, чтобы не пропустить новые уроки и полезные фишки Excel. Следите за нашими новостями и вы узнаете больше о VBA и Excel в частности.
Понравился урок!? не забываем оставлять комментарий 📝 Нам очень важна Ваша обратная связь.
💝 А если у Вас пока нет вопросов, но вы дочитали данную статью до конца оставьте в комментариях просто смайлик 😉 (автору будет приятно)
И конечно же, за лайк 👍 и репост 🔁 данного поста благодарочка 💖 и респект 🤝 каждому!
