Иногда формулы в Excel кажутся такими устрашающими и непостижимыми, что мы не разобравшись сразу стараемся их избегать. Одна из таких, это ИНДЕКС ПОИСКПОЗ. Страхи наши перед этой функцией связаны лишь с непониманием как же она работает. В этой статье мы разберем и пошагово объясним ход ее вычисления.
И да, часто наблюдал ситуацию когда работодатель оценивал навыки владения Excel:
- А, Вы можете пользоваться функцией ИНДЕКС ПОИСКПОЗ?
- Да.
- Вы, приняты!
Итак, по порядку:
Функция ИНДЕКС
ИНДЕКС находит и получает значение из массива данных, которое расположено на пересечении строки и столбца. Выглядит она так ИНДЕКС(массив; номер строки; номер столбца)
Простой пример для наглядности.
Имеем данные, расположенные в диапазоне А1:С4. Из этого диапазона нам необходимо узнать какое значение расположено в ячейке на пересечении 3 строки и 2 столбца. Для этого мы в ячейке Е2 записываем формулу, где сначала указываем наш диапазон, затем через точку с запятой номер строки и еще через точку с запятой номер столбца
=ИНДЕКС(A1:C4;3;2)
Функция ПОИСКПОЗ
ПОИСКПОЗ - это функция Excel, используемая для определения положения искомого значения в строке, столбце.
= ПОИСКПОЗ (искомое значение, массив, тип соответствия)
Пример для наглядности.
В таблице расположены данные о сотрудниках организации, они могут быть с большим набором данных, но для нас сейчас главное понять принцип работы и мы с Вами рассмотрим пример попроще. Из имеющегося диапазона нам необходимо найти в какой строке расположена информация о сотруднике с табельным номером 10. Для этого записываем формулу
=ПОИСКПОЗ(F2;A2:A21;0)
, где в ячейке F2 мы указываем табельный номер, по которому нам необходимо определить номер строки диапазона. Затем через точку с запятой обозначаем диапазон, в котором расположены табельные номера A2:A21. Далее через точку с запятой указываем тип соответствия "точное совпадение", которое записывается в виде цифры 0.
В результате вычисления формула нам выводит результат, который говорит нам, что табельный номер 10 расположен в 3 строке нашего диапазона.
Объединяем две функции
По отдельности навряд ли Вы будете использовать эти две формулы, но если их объединить, то мы получим мощный инструмент работы с данными. Приступим.
Зная как работает каждая формула в отдельности мы сделаем следующее.
В функцию ИНДЕКС, которая выводит результат на пересечении строки и столбца массива, вместо аргумента, который отвечает за номер строки мы внедрим формулу ПОИСКПОЗ и найдем номер этой строки по нужному нам условию. В результате чего формула выведет в ячейку необходимый нам результат.
Пример для наглядности.
Все из той же таблицы, по тому же табельному номеру 10 нам необходимо узнать Ф.И.О сотрудника. Для этого по вышеуказанному принципу мы записываем формулу, которая у нас будет выглядеть следующим образом:
=ИНДЕКС(A2:D21; ПОИСКПОЗ(F2;A2:A21;0); 2)
, где в формуле ИНДЕКС первым у нас идет диапазон в котором расположены наши данные - A2:D21.
Затем мы подменяем номер строки функцией ПОИСКПОЗ для того, чтобы определить ее по нашему условию (табельный номер 10). Далее в самой формуле ПОИСКПОЗ мы ссылаемся на искомый нам табельный номер 10, который указываем в ячейке F2, и через точку с запятой обозначаем диапазон, в котором расположены табельные номера A2:A21. Затем через точку с запятой указываем тип соответствия "точное совпадение" в виде цифры 0.
Завершаем написание формулы ПОИСКПОЗ закрывающей кавычкой и уже через точку с запятой указываем номер столбца, в котором расположена информация Ф.И.О., в нашем случае это столбец 2.
В результате вычисления формула ПОИСКПОЗ найдет табельный номер 10 и подставит номер строки, в которой он расположен, и формула будет соответствовать
=ИНДЕКС(A2:D21;3;2)
В результате мы получаем Ф.И.О., которая соответствует табельному номеру 10.
Также формулу ПОИСКПОЗ мы можем применить и для поиска номера столбца, заменив ее в качестве аргумента формулы ИНДЕКС, отвечающей за номер столбца
=ИНДЕКС(A1:D21;ПОИСКПОЗ(F2;A1:A21;0);ПОИСКПОЗ(G1;A1:D1;0))
Экспериментируйте и у Вас все получится. Удачи
