Поискпоз в excel

Примеры использования функции ОБЛАСТИ для диапазонов Excel

​ формул, начинающихся на​ находящиеся в строках​ листе Вы бы​Совет​ найти скрытый текст​ быть.​ Защита Excel» тут.​Примечания:​ расстояние между точкой​

Примеры работы функции ОБЛАСТИ в Excel для работы с диапазонами ячеек

​ следующий вид:​ целях демонстрации использования​Несмежные диапазоны складываются из​ ячеек в Excel?​

​Несколько ячеек выделенных блоком​ таблицы с вводными.​

​ Для этого необходимо​ нажать Enter, появится​

​ эту букву, еще​2 310​ не написали формулу​: Узнать на какой диапазон​

​ в Excel».​В строке «Область»​В Excel можно​

​Если функция СМЕЩ ссылается​ отсчета (указанной аргументом​Используем элемент управления «Счетчик»​ функции СМЕЩ, решение​ нескольких других диапазонов.​ Выделите мышкой блок​ (=B5:D8).​

​БМВ​ использовать еще по​ диалоговое окно с​

​ и имя диапазона!​

Как посчитать количество ссылок на столбцы таблицы Excel

​, в том столбце,​=СУММ(Продажи) – суммирование​ ячеек ссылается Имя можно​В диапазон ячеек​ указываем область, на​

​ выделить как смежные​

​ на ячейку или​ ссылка) и возвращаемой​ для выбора номера​ задачи может быть​Чтобы их выделять просто​ B3:D8. Нажмите клавишу​Одна ячейка (=A2:A2).​: так вот накурилось​ одной открывающей и​ сообщением о том,​Суть запроса на выборку​ в котором размещена​ будет производиться по​ через Диспетчер имен​ можно вставить формулу​

​ которую будет распространяться​

Определение принадлежности ячейки к диапазону таблицы

​ ячейки (расположенные рядом​ диапазон ячеек, которые​ ячейкой либо диапазоном​

​ месяца. Для этого​ реализовано более простым​ удерживайте нажатие клавиши​ F8 чтобы включить​

1. ​Целая строка (=18:18) или​=XIRR(IF(ROW(B1:INDEX(B:B;COUNTA(B5:B24)+1))=1;D2;INDEX(B5:B24;N(INDEX(ROW(B1:INDEX(B:B;COUNTA(B5:B24)+1))-1;))));IF(ROW(A1:INDEX(A:A;COUNTA(A5:A24)+1))=1;C2;INDEX(A5:A24;N(INDEX(ROW(A1:INDEX(A:A;COUNTA(A5:A24)+1))-1;)))))​ закрывающей скобки (в​ что было введено​ – выбрать из​
2. ​ формула суммирования. Формулу​ одному и тому​ расположенный в меню​
3. ​ массива. Что это​ это имя. Например,​ друг с другом),​
4. ​ находятся вне пределов​ ячеек. В качестве​ добавим пункт ленты​

​ и надежным способом.​ CTRL, а дальше​ специальный режим. В​

​ несколько строк (=18:22).​Rewty​ этом случае Excel​ слишком много аргументов.​

​ исходной таблицы строки,​ суммирования можно разместить​ же диапазону​ Формулы/ Определенные имена/​ за формулы, где​

Особенности использования функции ОБЛАСТИ в Excel

​ на всю книгу​ так и не​ рабочего листа по​ аргумента может быть​ меню «Разработчик» нажатием​

​ как при обычном​

1. ​B1:B10​ Диспетчер имен.​ применяются, смотрите в​ (на все её​ смежные ячейки (расположены​ условиям, заданным параметрами​ передано:​ правой кнопкой мыши​Пример 2. В таблице​ выделении. Также в​
2. ​ сообщение: «Расширить выделенный​ несколько столбцов (=F:K).​ не могу понять​ символ «;» как​ и закрывающую скобки.​ (подобно применению стандартного​ ниже десятой (иначе​.​Ниже рассмотрим как присваивать​ статье «Формулы массива​ листы), или только​ не рядом).​ смещ_по_строкам и смещ_по_столбцам,​Положительное целое число. В​ по любому существующему​ Excel внесены данные​ данной ситуации особенно​ фрагмент». И теперь​
3. ​Несколько несмежных диапазонов (=N5:P8;E18:H25;I5:L22).​ как)) можете описать​ разделитель аргументов в​Результат вычислений:​ Фильтра). Произведем отбор значений​ возникнет циклическая ссылка).​Иногда выгодно использовать не​ имя диапазонам. Оказывается,​
4. ​ Excel».​ на этот лист,​Чтобы быстро найти​ результатом выполнения данной​ этом случае смещение​ ее элементу:​ о количестве продаж​ полезным будет режим​ выделите мышкой блок​Целый лист (=1:1048576).​ принцип?​ функции. Например, результатом​Пример 3. Определить, принадлежит​
5. ​ из исходной таблицы​Теперь введем формулу =СУММ(Сезонные_Продажи)​ абсолютную, а относительную​ что диапазону ячеек​

Формула ИНДЕКС и ПОИСКПОЗ лучшая замена функции ВПР

Существуют весьма весомые аргументы преимущества использования формулы ИНДЕКС и ПОИСКПОЗ в Excel лучше, чем функция ВПР. Если вы уже знакомы с функцией ВПР, то наверняка знаете, что для её корректной работы искомые данные всегда должны располагаться по правую сторону от критериев:

Получается, что для работы сначала нужно упорядочить столбцы в соответствии с требованиями, а только потом совершать действия. Но иногда сама структура отчета или сводки, с которой нам нужно иметь дело, не позволяет совершать перестановки. Тогда нам очень кстати пригодится ИНДЕКС в сочетании с функцией ПОИСКПОЗ. Синтаксис ИНДЕКСА: (массив; номер_строки; номер_столбца). На первой позиции у нас будет диапазон значений (туристы, 2017), вместо следующих двух пишем ПОИСКПОЗ (искомый критерий;диапазон критериев; 0 (для точного результата)):

Рассмотрим более наглядный и реальный пример, где можно заменить ВПР. У нас есть два отчета: один — о количестве продаж определенного товара, а второй – о цене на упаковку одного товара. И как раз вторая таблица имеет такое расположение столбцов, которое не позволяет использовать нам ВПР – первыми занимают место значения, а вторыми – критерии:

Для того чтобы выполнить заполнение первой таблицы, делаем точно также, как в предыдущем примере – в ячейке D88 пишем ИНДЕКС, первым делом указываем столбец, где находятся искомые значения (цена за упаковку). Затем нужно указать, где нам искать соответствующие критерию значения — подстраиваем ПОИСКПОЗ под наше решение: выбираем искомый критерий (Хепилор), затем массив искомых критериев со второй таблицы:

Теперь просто копируем формулу до конца столбца, но не забываем закрепить ссылки, иначе массив будет спускаться дальше и мы получим некорректные значения. Наша таблица готова:

Если у нас нет данных во второй таблице о цене и препаратах, у нас будет в ячейке ошибка #Н/Д. Теперь к нашим ранее использованным функциям добавим ЕСЛИОШИБКА, которая нам поможет изменить внешний вид отчета, сделать его более понятным для читателя. Опять же, поскольку у нас вторая таблица не соответствует требованиям ВПР, на помощь приходит ИНДЕКС+ПОИСКПОЗ. Усложняем уже имеющуюся формулу, добавляя ЕСЛИОШИБКА, указывая, что при отсутствии данных, мы хотим видеть прочерк:

Таким образом формула из комбинации функций ИНДЕКС и ПОИСПОЗ работают лучше популярной функции ВПР и не имеют никаких ограничений для выборки данных из таблицы даже по нескольким условиям.

ЕСЛИ

Это одна из самых важных функций в Excel. Как и в настоящем языке программирования, она проверяет какое-то условие, и если оно выполняется — пишет в ячейку что-то одно, а если нет — пишет что-то другое.

В общем виде она выглядит так:

=ЕСЛИ(условие; “значение_если_да”;”значение_если_нет”)

В условии может быть что угодно: сравнение ячеек, другие формулы, сравнения и математические команды — всё, что вам нужно проверить.

На практике можно сделать, например, так: пусть Excel проверяет возраст, и пишет документ, который в этом возрасте удостоверяет личность. До 14 лет это свидетельство о рождении, а после — паспорт. Для этого используем такую команду в ячейке:

Программа проверит, что стоит в предыдущей ячейке,  и если там число меньше 14, то напишет «Свидетельство о рождении», а если уже есть 14 лет, то «Паспорт»:

Анализ данных с помощью сводных таблиц

Средство Сводная таблица представляет собой еще один инструмент организации и подведения итогов данных. Этот инструмент комбинирует возможности работы со списками.Мастера

• Установить курсор в любую ячейку с данными исходной таблицы « Отчет ООО «Техносервис» о продаже аудио- и видеотехники магазинам-закупщикам в 1 квартале».
• Выбрать пункт меню Данные|Сводная таблица.
• В группе Создать таблицу на основе данных, находящихся установить переключатель в Списке или базе данных Microsoft Excel.
• В группе Вид создаваемого отчета установить переключатель Сводная таблица и нажать кнопку Далее.
• Проверить, что в появившемся окне в опции Диапазон указан весь диапазон ячеек исходной таблицы, и нажать Далее.
• В опции Поместить таблицу в установить переключатель в новый лист и нажать кнопку Макет.

• При помощи мыши перетащить поле «Наименование магазина» (4-е сверху) в область Страница, поле «Вид продукции» — в область Строка, поле «Вид оплаты» — в область Столбец, поле «Сумма» — в область Данные и нажать ОК.
• Нажать Готово.

Точное или приближенное совпадение в функции ВПР

И, наконец, давайте рассмотрим поподробнее последний аргумент, который указывается для функции ВПР
– range_lookup
(интервальный_просмотр). Как уже упоминалось в начале урока, этот аргумент очень важен. Вы можете получить абсолютно разные результаты в одной и той же формуле при его значении TRUE
(ПРАВДА) или FALSE
(ЛОЖЬ).

Для начала давайте выясним, что в Microsoft Excel понимается под точным и приближенным совпадением.

• Если аргумент range_lookup
  FALSE
  (ЛОЖЬ), формула ищет точное совпадение, т.е. точно такое же значение, что задано в аргументе lookup_value
  (искомое_значение). Если в первом столбце диапазона table_array
  (таблица) встречается два или более значений, совпадающих с аргументом lookup_value
  (искомое_значение), то выбрано будет первое из них. Если совпадения не найдены, функция сообщит об ошибке #N/A
  (#Н/Д).Например, следующая формула сообщит об ошибке #N/A
  (#Н/Д), если в диапазоне A2:A15 нет значения 4
  :

  VLOOKUP(4,A2:B15,2,FALSE)
  =ВПР(4;A2:B15;2;ЛОЖЬ)

• Если аргумент range_lookup
  (интервальный_просмотр) равен TRUE
  (ИСТИНА), формула ищет приблизительное совпадение. Точнее, сначала функция ВПР
  ищет точное совпадение, а если такое не найдено, выбирает приблизительное. Приблизительное совпадение – это наибольшее значение, не превышающее заданного в аргументе lookup_value
  (искомое_значение).

Если аргумент range_lookup
(интервальный_просмотр) равен TRUE
(ИСТИНА) или не указан, то значения в первом столбце диапазона должны быть отсортированы по возрастанию, то есть от меньшего к большему. Иначе функция ВПР
может вернуть ошибочный результат.

Чтобы лучше понять важность выбора TRUE
(ИСТИНА) или FALSE
(ЛОЖЬ), давайте разберём ещё несколько формул с функцией ВПР
и посмотрим на результаты

Пример 1: Поиск точного совпадения при помощи ВПР

Как Вы помните, для поиска точного совпадения, четвёртый аргумент функции ВПР
должен иметь значение FALSE
(ЛОЖЬ).

Давайте вновь обратимся к таблице из самого первого примера и выясним, какое животное может передвигаться со скоростью 50
миль в час. Я верю, что вот такая формула не вызовет у Вас затруднений:

VLOOKUP(50,$A$2:$B$15,2,FALSE)
=ВПР(50;$A$2:$B$15;2;ЛОЖЬ)

Обратите внимание, что наш диапазон поиска (столбец A) содержит два значения 50
– в ячейках A5
и A6. Формула возвращает значение из ячейки B5

Почему? Потому что при поиске точного совпадения функция ВПР
использует первое найденное значение, совпадающее с искомым.

Пример 2: Используем ВПР для поиска приблизительного совпадения

Когда Вы используете функцию ВПР
для поиска приблизительного совпадения, т.е. когда аргумент range_lookup
(интервальный_просмотр) равен TRUE
(ИСТИНА) или пропущен, первое, что Вы должны сделать, – выполнить сортировку диапазона по первому столбцу в порядке возрастания.

Это очень важно, поскольку функция ВПР
возвращает следующее наибольшее значение после заданного, а затем поиск останавливается. Если Вы пренебрежете правильной сортировкой, дело закончится тем, что Вы получите очень странные результаты или сообщение об ошибке #N/A
(#Н/Д)

Вот теперь можно использовать одну из следующих формул:

VLOOKUP(69,$A$2:$B$15,2,TRUE) или =VLOOKUP(69,$A$2:$B$15,2)
=ВПР(69;$A$2:$B$15;2;ИСТИНА) или =ВПР(69;$A$2:$B$15;2)

Как видите, я хочу выяснить, у какого из животных скорость ближе всего к 69
милям в час. И вот какой результат мне вернула функция ВПР
:

Как видите, формула возвратила результат Антилопа
(Antelope), скорость которой 61
миля в час, хотя в списке есть также Гепард
(Cheetah), который бежит со скоростью 70
миль в час, а 70 ближе к 69, чем 61, не так ли? Почему так происходит? Потому что функция ВПР
при поиске приблизительного совпадения возвращает наибольшее значение, не превышающее искомое.

Надеюсь, эти примеры пролили немного света на работу с функцией ВПР
в Excel, и Вы больше не смотрите на неё, как на чужака. Теперь не помешает кратко повторить ключевые моменты изученного нами материала, чтобы лучше закрепить его в памяти.

Примеры использования функций ИНДЕКС и ПОИСКПОЗ по двум критериям в Excel

Пример 1. В турнирной таблице хранятся данные о сыгранных футбольных матчах для нескольких команд. Определить:

1. Сколько очков заработала команда (поиск по названию) на данный момент.
2. Суммарное значение очков, заработанных всеми командами.
3. Сколько игр было сыграно какой-либо командой.

Вид исходной таблицы данных:

Для удобства в ячейках A11, A13 и A15 созданы выпадающие списки, элементы которых выбраны из диапазонов ячеек B1:E1 (для A11) и A2:A9 (для A13 и A15), содержащих названия команд. Для создания первого выпадающего списка необходимой перейти курсором на ячейку A11. Выбрать вкладку «ДАННЫЕ» ленты меню, найти секцию с инструментами «Работа с данными» и выбрать инструмент «Проверка данных»:

В открывшемся диалоговом окне необходимо выбрать «Тип данных:» — «Список» и указать в поле «Источник» диапазон ячеек:

Такой же выпадающий список следует создать и для ячейки A15.

Для подсчета общего количества очков в ячейке B11 используем формулу:

Для получения корректного результата выражение должно быть выполнено как формула массива. Функция СУММ получает массив ячеек в виде столбца таблицы, номер которого был определен функцией ПОИСКПОЗ по критерию поиска «Очки» (наименование столбца). Поскольку в качестве аргумента номер_строки функции ИНДЕКС было передано значение 0, будет возвращен весь столбец.

Количество сыгранных игр для каждой команды может быть рассчитано как сумма выигранных, сыгранных вничью и проигранных игр. Используем следующую формулу:

Данная формула аналогична предыдущей и также должна использоваться в качестве формулы массива. Благодаря списку, привязанному к ячейке A13, можно легко определить число сыгранных игр для любой команды:

Для определения количества очков используем формулу ИНДЕКС, в которой оба аргумента, указывающие номер строки и столбца, будут принимать значения, возвращаемые функцией ПОИСКПОЗ:

В результате мы получили значение по 2-м критериям:

ИНДЕКС ПОИСКПОЗ с несколькими условиями в нескольких строках и столбцах

В этом примере показано, как выполнять поиск, проверяя два или более критерия в строках и столбцах. На самом деле это более сложный случай так называемого «матричного поиска» или «двустороннего поиска» с более чем одной строкой заголовка.

Вот общая формула ИНДЕКС ПОИСКПОЗ с несколькими критериями в строках и столбцах:

где:

Массив таблицы — область для поиска, т. е. все значения таблицы, кроме заголовков столбцов и строк.

Значение_поиска — то, что вы ищете по вертикали в столбце.

Столбец_поиска — диапазон столбцов для поиска, обычно это заголовки строк.

Значение_поиска1, значение_поиска2, … — то, что вы ищете по горизонтали в строках.

Строка_поиска1, строка_поиска2, … — диапазоны строк для поиска, обычно это заголовки столбцов.

Важное замечание! Чтобы формула работала корректно, ее нужно вводить как формулу массива  при помощи комбинации. Это разновидность классической формулы двустороннего поиска в массиве, которая ищет значение на пересечении определенной строки и столбца. Разница в том, что вы объединяете несколько значений и диапазонов поиска для оценки нескольких заголовков столбцов. Чтобы лучше понять логику ее работы, рассмотрим небольшой пример

Это разновидность классической формулы двустороннего поиска в массиве, которая ищет значение на пересечении определенной строки и столбца. Разница в том, что вы объединяете несколько значений и диапазонов поиска для оценки нескольких заголовков столбцов. Чтобы лучше понять логику ее работы, рассмотрим небольшой пример.

В приведенной ниже таблице мы будем искать значение на основе заголовков строк (элементы) и заголовков двух столбцов (регионы и поставщики). То есть, ищем по одному условию по строкам и по двум условиям – по столбцам. Чтобы упростить построение формулы, давайте сначала определим все критерии и диапазоны:

• Массив_таблицы — B3:E4
• Значение_поиска  — H1
• Столбец_поиска  (заголовки строк: товары) — A3:A5
• Значение_поиска1  (целевой регион) — H2
• Значение_поиска 2 (целевой продавец) — H3
• Строка_поиска1 (заголовки столбцов 1: регионы) — B1:E1
• Строка_поиска2 (заголовки столбцов 2: продавцы) — B2:E2

А теперь подставьте аргументы в общую формулу, описанную выше, и вы получите такой результат:

Не забудьте завершить формулу, нажав комбинацию клавиш , после чего поиск по матрице с несколькими условиями будет выполнен успешно:

Пошагово рассмотрим, как работает эта формула.

Поскольку мы ищем и по вертикали, и по горизонтали, то нужно указать номера строк и столбцов для функции ИНДЕКС (массив, номер_строки, номер_столбца).

Шаг 1.

Номер_строки предоставляется функцией ПОИСКПОЗ(H1;A3:A5;0), которая сравнивает целевой элемент (бананы) в H1 с заголовками строк в A3:A5. 

ПОИСКПОЗ(«Бананы»;{«Апельсины»:»Бананы»:»Лимоны»};0)

Это дает результат 2, потому что «Бананы» — это второй элемент в указанном списке.

Шаг 2.

Номер_столбца вычисляется путем объединения двух значений поиска и двух массивов поиска: ПОИСКПОЗ(H2&H3; B1:E1&B2:E2; 0)

Необходимым условием является то, что значения поиска должны точно соответствовать заголовкам столбцов и быть объединены в том же порядке. Чтобы проследить процесс поиска, выберите первые два аргумента в формуле ПОИСКПОЗ, затем нажмите F9, и вы увидите, что оценивает каждый аргумент:

ПОИСКПОЗ(«СеверПродавец 2»; {«СеверПродавец 1″;»СеверПродавец 2″;»ЮгПродавец 1″;»ЮгПродавец 2»}; 0)

Поскольку « СеверПродавец 2» является вторым элементом в массиве, функция возвращает 2.

Шаг 3.

После этого наша длинная двумерная формула ИНДЕКС ПОИСКПОЗ превращается в такую простую:

=ИНДЕКС(B3:E5; 2; 2)

Шаг 4.

Она возвращает значение на пересечении 2-й строки и 2-го столбца в диапазоне B3:E5, что является значением в ячейке C4.

Вот как формула ИНДЕКС ПОИСКПОЗ помогает выполнить поиск по нескольким условиям в Excel. Я благодарю вас за чтение и надеюсь вновь увидеть вас в нашем блоге.

Еще несколько статей по теме:

Ссылочная форма

Функция ИНДЕКС() дозволяет применять так именуемую ссылочную форму. Поясним на примере.

Пусть имеется спектр с числами ( А2:А10 ) Нужно отыскать сумму первых 2-х, 3-х, . 9 значений. Естественно, можно написать несколько формул =СУММ(А2:А3) , =СУММ(А2:А4) и т.д. Но, записав формулу ввиде:

получим всепригодное решение, в каком требуется изменять лишь крайний аргумент (если в формуле выше заместо 4 ввести 5, то будет подсчитана сумма первых 5-и значений).

Внедрение функции ИНДЕКС() в этом примере принципно различается от примеров рассмотренных выше, т.к. функция возвращает не само значение, а ссылку (адресок ячейки) на значение. Вышеуказанная формула =СУММ(A2:ИНДЕКС(A2:A10;4)) эквивалентна формуле =СУММ(A2:A5)

Аналогичный итог можно получить используя функцию СМЕЩ()

Сейчас наиболее непростой пример, с областями.

Пусть имеется таблица продаж нескольких продуктов по полугодиям.

Задав Продукт , год и полугодие , можно вывести соответственный размер продаж при помощи формулы =ИНДЕКС((B9:C12;D9:E12;F9:G12);B15;A19;B17)

Вся таблица вроде бы разбита на 3 подтаблицы (области), надлежащие отдельным годам: B9:C12 ; D9:E12 ; F9:G12 . Задавая номер строчки, столбца (в подтаблице) и номер области, можно вывести соответственный размер продаж. В файле примера , избранные строчка и столбец выделены цветом при помощи Условного форматирования .

Функции Excel ИНДЕКС и ПОИСКПОЗ — основы

Поскольку целью этого руководства является демонстрация альтернативного способа выполнения поиска в Excel с использованием комбинации функций ИНДЕКС и ПОИСКПОЗ, мы не будем подробно останавливаться на их синтаксисе и использовании. Тем более, что это подробно рассмотрено в других статьях, ссылки на которые вы можете найти в конце этого руководства. Мы рассмотрим лишь минимум, необходимый для понимания общей идеи, а затем подробно рассмотрим примеры формул, раскрывающие все преимущества использования ПОИСКПОЗ и ИНДЕКС вместо ВПР.

Функция ИНДЕКС

Функция ИНДЕКС (в английском варианте – INDEX) возвращает значение в массиве на основе указанных вами номеров строк и столбцов. Синтаксис функции ИНДЕКС прост:

Вот простое объяснение каждого параметра:

• массив — это диапазон ячеек, именованный диапазон или таблица.
• номер_строки — это номер строки в массиве, из которого нужно вернуть значение. Если этот аргумент опущен, требуется следующий – номер_столбца.
• номер_столбца — это номер столбца, из которого нужно вернуть значение. Если он опущен, требуется номер_строки.

Дополнительные сведения см. в статье Функция ИНДЕКС в Excel .

А вот пример формулы ИНДЕКС в самом простом виде:

=ИНДЕКС(A1:C10;2;3)

Формула выполняет поиск в ячейках с A1 по C10 и возвращает значение ячейки во 2-й строке и 3-м столбце, т. е. в ячейке C2.

Очень легко, правда? Однако при работе с реальными данными вы вряд ли когда-нибудь будете заранее знать, какие строки и столбцы вам нужны. Здесь вам пригодится ПОИСКПОЗ.

Функция ПОИСКПОЗ

Она ищет нужное значение в диапазоне ячеек и возвращает относительное положение этого значения в диапазоне.

Синтаксис функции ПОИСКПОЗ следующий:

• искомое_значение — числовое или текстовое значение, которое вы ищете.
• диапазон_поиска — диапазон ячеек, в которых будем искать.
• тип_совпадения — указывает, следует ли искать точное соответствие или наиболее близкое совпадение:
  • 1 или опущено — находит наибольшее значение, которое меньше или равно искомому значению. Требуется сортировка массива поиска в порядке возрастания.
  • 0 — находит первое значение, точно равное искомому значению. В комбинации ИНДЕКС/ПОИСКПОЗ вам почти всегда нужно точное совпадение, поэтому вы чаще всего устанавливаете третий аргумент вашей функции в 0.
  • -1 — находит наименьшее значение, которое больше или равно искомому значению. Требуется сортировка массива поиска в порядке убывания.

Например, если диапазон B1:B3 содержит значения «яблоки», «апельсины», «лимоны», приведенная ниже формула возвращает число 3, поскольку «лимоны» — это третья по счету запись в этом диапазоне:

=ПОИСКПОЗ(«лимоны»;B1:B3;0)

Дополнительные сведения см . в статье Функция ПОИСКПОЗ в Excel .

На первый взгляд полезность функции ПОИСКПОЗ может показаться сомнительной. Кого волнует положение значения в диапазоне? Что мы действительно хотим определить, так это само значение.

Однако, относительная позиция искомого значения (т. е. номера строки и столбца, в которых оно находится) — это именно то, что нам нужно указать для аргументов номер_строки и номер_столбца функции ИНДЕКС. Как вы помните, ИНДЕКС может найти значение на пересечении заданной строки и столбца, но сама не может определить, какую именно строку и столбец ей нужно выбрать.

Вот поэтому совместное использование ИНДЕКС и ПОИСКПОЗ открывает перед нами массу возможностей для поиска в Excel.


Функция ПОИСКПОЗ

Функция ПОИСКПОЗ производит поиск указанного значения в диапазоне ячеек и возвращает относительную позицию ячейки с искомым значением. То есть фактически эта функция возвращает координаты этой ячейки, а это как раз то, чего не хватает функции ИНДЕКС для полноценной замены функции ВПР.

Например, нам нужно узнать, какие координаты в диапазоне имеет «Ольга». Создадим формулу с функцией ПОИСКПОЗ и введем интересующее нас имя. Напомню, что текст вводится в кавычках.

Мы указали что искать, а теперь нужно указать где будет производиться поиск — выбираем значения столбца «Имя».

Ну и последний аргумент функции ПОИСКПОЗ очень напоминает аналогичный аргумент интервальный просмотр функции ВПР, но он может принимать три значения — меньше, больше и точное совпадение.

Если речь идет о поиске текстовых данных, то всегда выбираем точное совпадение. Другие параметры актуальны только при работе с числовыми данными. Поэтому указываем ноль и в итоге получаем цифру 3, что соответствует третьей строке в выделенном диапазоне.

Ну а теперь объединим функции ИНДЕКС и ПОИСКПОЗ в полноценную замену функции ВПР.