Поиск позиции функция в excel

Функция ПОИСКПОЗ в Google Таблицах

Я хотел бы начать с Google Sheets MATCH, потому что это очень просто. Он сканирует ваши данные для определенного значения и возвращает его позицию:

=ПОИСКПОЗ(search_key, диапазон, )

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

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

=ПОИСКПОЗ(“Черника”, A1:A10, 0)

Базовая информация об ИНДЕКС и ПОИСКПОЗ

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

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

ИНДЕКС – синтаксис и применение функции

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

Каждый аргумент имеет очень простое объяснение:

array
(массив) – это диапазон ячеек, из которого необходимо извлечь значение.
row_num
(номер_строки) – это номер строки в массиве, из которой нужно извлечь значение. Если не указан, то обязательно требуется аргумент column_num
(номер_столбца).
column_num
(номер_столбца) – это номер столбца в массиве, из которого нужно извлечь значение. Если не указан, то обязательно требуется аргумент row_num
(номер_строки)

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

Вот простейший пример функции INDEX
(ИНДЕКС):

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

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

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

ПОИСКПОЗ – синтаксис и применение функции

Функция MATCH
(ПОИСКПОЗ) в Excel ищет указанное значение в диапазоне ячеек и возвращает относительную позицию этого значения в диапазоне.

Например, если в диапазоне B1:B3
содержатся значения New-York, Paris, London, тогда следующая формула возвратит цифру 3
, поскольку “London” – это третий элемент в списке.

MATCH(«London»,B1:B3,0)
=ПОИСКПОЗ(«London»;B1:B3;0)

Функция MATCH
(ПОИСКПОЗ) имеет вот такой синтаксис:

MATCH(lookup_value,lookup_array,)
ПОИСКПОЗ(искомое_значение;просматриваемый_массив;)

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

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

Позвольте напомнить, что относительное положение искомого значения (т.е. номер строки и/или столбца) – это как раз то, что мы должны указать для аргументов row_num
(номер_строки) и/или column_num
(номер_столбца) функции INDEX
(ИНДЕКС). Как Вы помните, функция ИНДЕКС
может возвратить значение, находящееся на пересечении заданных строки и столбца, но она не может определить, какие именно строка и столбец нас интересуют.

Совместное использование ПОИСКПОЗ и ИНДЕКС в Excel

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

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

Пускай ячейка C15 содержит указанный нами месяц, например, Май. А ячейка C16 — тип товара, например, Овощи. Введем в ячейку C17 следующую формулу и нажмем Enter:

=ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))

Как видите, мы получили верный результат. Если поменять месяц и тип товара, формула снова вернет правильный результат:

В данной формуле функция ИНДЕКС принимает все 3 аргумента:

Первый аргумент – это диапазон B2:E13, в котором мы осуществляем поиск.
Вторым аргументом функции ИНДЕКС является номер строки. Номер мы получаем с помощью функции ПОИСКПОЗ(C15;A2:A13;0). Для наглядности вычислим, что же возвращает нам данная формула:
Третьим аргументом функции ИНДЕКС является номер столбца. Этот номер мы получаем с помощью функции ПОИСКПОЗ(C16;B1:E1;0). Для наглядности вычислим и это значение:

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

=ИНДЕКС(B2:E13;D15;D16)

Как видите, все достаточно просто!

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

Поиск позиции элемента в списке с ПОИСКПОЗ (MATCH)

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

Синтаксис этой функции следующий:

=ПОИСКПОЗ( Что_ищем ; Где_ищем ; Режим_поиска )

Что_ищем — это значение, которое надо найти
Где_ищем — это одномерный диапазон или массив (строка или столбец), где производится поиск
Режим_поиска — как мы ищем: точно (0), с округлением в большую строну (-1) или в меньшую сторону (1)

Давайте рассмотрим несколько полезных вариантов ее применения на практике.

Точный поиск

Классический сценарий — поиск точного текстового совпадения для нахождения позиции нужного нам текста или числа в списке:

Поиск первой или последней текстовой ячейки

Если в качестве искомого значения задать звездочку, то функция будет искать первую ячейку с текстом и выдавать её позицию. Для поиска последней текстовой ячейки можно изменить третий аргумент Режим_поиска с нуля на минус 1:

Числа и пустые ячейки в этом случае игнорируются.

Поиск ближайшего числа или даты

Если последний аргумент задать равным 1 или -1, то можно реализовать поиск ближайшего наименьшего или наибольшего числа. Таблица при этом обязательно должна быть отсортирована по возрастанию или убыванию соответственно. В общем и целом, это чем-то похоже на интервальный просмотр у функции ВПР (VLOOKUP) , но там возможен только поиск ближайшего наименьшего, а здесь — есть выбор.

Например, нам нужно выбрать генератор из прайс-листа для расчетной мощности в 47 кВт. Если последний аргумент задать равным 1 и отсортировать таблицу по возрастанию, то мы найдем ближайшую наименьшую по мощности модель (Зверь):

Если же третий аргумент равен -1 и таблица отсортирована по убыванию, то мы найдем ближайшую более мощную модель (Бомба):

Связка функций ПОИСКПОЗ и ИНДЕКС

Очень часто функция ПОИСКПОЗ используется в связке с другой крайне полезной функцией — ИНДЕКС (INDEX) , которая умеет извлекать данные из диапазона по номеру строки-столбца, реализуя, фактически, «левый ВПР».

Так, в предыдущем примере получить не номер, а название модели генератора можно очень легко:

Ну, и поскольку Excel внутри хранит и обрабатывает даты как числа, то подобный подход на 100% работает и с датами. Например, мы можем легко определить на каком этапе сейчас находится наш проект:

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

Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?

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

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

4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:

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

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

Например, если у вас есть таблица A1:C10 и вы хотите извлечь данные из столбца B, вы должны установить значение 2 для аргумента col_index_num (номер_столбца) функции ВПР, например так:

=ВПР(«искомое значение»,A1:C10,2)
=ВПР(«искомое значение»;A1:C10;2)

Если вы затем вставите новый столбец между столбцами A и B, значение аргумента должно быть изменено с 2 на 3; в противном случае формула вернет результат вновь вставленного столбца.

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

3. Нет ограничений на размер искомого значения. При использовании функции ВПР помните об ограничении длины искомого значения в 255 символов, иначе вы рискуете получить ошибку #ЗНАЧ. (#СТОИМОСТЬ!). Таким образом, если таблица содержит длинные строки, единственным рабочим решением является использование ИНДЕКС/ПОИСКПОЗ.

Предположим, вы используете следующую формулу с функцией ВПР, которая ищет в ячейках с B5 по D10 значение, указанное в ячейке A2:

=ВПР(A2,B5:D10,3,ЛОЖЬ)
=ВПР(A2,B5:D10,3,ЛОЖЬ)

Формула не будет работать, если значение в ячейке A2 длиннее 255 символов. Вместо этого вы должны использовать аналогичную формулу ИНДЕКС/ПОИСКПОЗ:

=ИНДЕКС(D5:D10,СООТВЕТСТВИЕ(ИСТИНА,ИНДЕКС(B5:B10=A2,0),0))
=ИНДЕКС(D5:D10,СООТВЕТСТВИЕ(ИСТИНА,ИНДЕКС(B5:B10=A2,0),0))

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

Влияние ВПР на производительность Excel особенно заметно, если ваша рабочая книга содержит сотни сложных формул массива, таких как ВПР+СУММ. Дело в том, что для проверки каждого значения в массиве требуется отдельный вызов функции ВПР. Таким образом, чем больше значений содержит массив и чем больше формул массива содержит ваша таблица, тем медленнее будет работать Excel.

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

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

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

Например, на рисунке ниже формула вернет число 5, поскольку имя “Дарья” находится в пятой строке диапазона A1:A9.

В следующем примере формула вернет 3, поскольку число 300 находится в третьем столбце диапазона B1:I1.

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

– функция ПОИСКПОЗ ищет первое значение в точности равное заданному. Сортировка не требуется.
1 или вовсе опущено – функция ПОИСКПОЗ ищет самое большое значение, которое меньше или равно заданному. Требуется сортировка в порядке возрастания.
-1 – функция ПОИСКПОЗ ищет самое маленькое значение, которое больше или равно заданному. Требуется сортировка в порядке убывания.

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

ИНДЕКС

Самый простой вариант объяснить — использовать пример. Если вы понимаете что такое ширина и долгота на карте — вам будет очень просто представить. Функция индекс просто ищет значение по заданным точкам данных. Грубо говоря, вы сообщаете ей координаты места, которое нужно найти и она находит его.

Как с адресом квартиры, например. Как бы много не было адресов, если вы сообщите полный — вас всегда можно будет найти.

Итак, чтобы найти вас нужен полный адрес, а чтобы найти что-то в Excel нужен адрес строки и столбика.

Так и работает эта функция.

Вот пример, строка 9, столбик 5 выдаст такой результат:

Функция довольно простая и её редко где можно применить, но…

Мы конечно же можем комбинировать её с другими функциями! В таком случае она будет очень даже кстати

Вот синтаскис:

Диапазон — диапазон ячеек, среди которого будет выполнен поиск;
Строка/столбик — аргумента может быть сразу 2, а может быть только 1, например, строка.
Также можно указывать дополнительные диапазоны.

Чаще всего дополнительные диапазоны не используются, но всякое бывает.

Совместное внедрение ПОИСКПОЗ и ИНДЕКС в Excel

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

На рисунке ниже представлена таблица, которая содержит месячные объемы продаж всякого из 4 видов продукта. Наша задачка, указав требуемый месяц и тип продукта, получить размер продаж.

Пускай ячейка C15 содержит обозначенный нами месяц, к примеру, Май. А ячейка C16 – тип продукта, к примеру, Овощи. Введем в ячейку C17 последующую формулу и нажмем Enter:

=ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))

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

В данной формуле функция ИНДЕКС воспринимает все 3 аргумента:

1-ый аргумент – это спектр B2:E13, в котором мы осуществляем поиск.
Вторым аргументом функции ИНДЕКС является номер строчки. Номер мы получаем при помощи функции ПОИСКПОЗ(C15;A2:A13;0). Для наглядности вычислим, что все-таки возвращает нам данная формула:
Третьим аргументом функции ИНДЕКС является номер столбца. Этот номер мы получаем при помощи функции ПОИСКПОЗ(C16;B1:E1;0). Для наглядности вычислим и это значение:

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

=ИНДЕКС(B2:E13;D15;D16)

Видите ли, все довольно просто!

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

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

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

1. Использование функции ПОИСКПОЗ для текстовых значений

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

АБ1Очень маленький342Маленький123Средний144Большой565Очень большой7Далее вы можете ввести значение, которое хотите найти, в пустую ячейку. Например, вы можете использовать функцию ПОИСКПОЗ, чтобы найти слово «маленький» в ваших данных. Для этого вы можете ввести слово «Маленький» в пустую ячейку, такую как ячейка C1:

АБС1Очень маленький34Маленький2Маленький123Средний144Большой565Очень большой7После того, как вы знаете, какое условие поиска вы хотите сопоставить, вы можете ввести функцию ПОИСКПОЗ или XПОИСКПОЗ в пустую ячейку и нажать «ВВОД» на клавиатуре. Например, вы можете поместить функцию в любую ячейку от C2 до C5 и нажать «ENTER», чтобы позволить Excel найти значение:

АБС1Очень маленький34Маленький2Маленький12=ПОИСКПОЗ(C1, A1:A5, 0)3Medium14=XMATCH(C1, A1:A5, 0)4Большой56=ПОИСКПОЗ(Маленький, A1:A5, 0)5Extra large7=XMATCH(Small, A1:A5, 0) После нажатия «ENTER» Excel возвращает значение «2», что означает, что искомое значение находится во второй ячейке массива, который вы искали. Здесь ячейка A2. Наконец, после того как Excel найдет совпадение в вашем наборе данных, вы можете нажать «CTRL+S», чтобы сохранить книгу Excel для дальнейшего использования.

2. Использование функции ПОИСКПОЗ для неточных значений

Вы можете использовать функцию ПОИСКПОЗ, чтобы найти значения в данных, которые близки к нужному значению. Для этих поисков вы можете использовать другое значение, чтобы найти следующее ближайшее значение в вашем наборе данных: 1 или -1. Во-первых, вы можете ввести свои данные в электронную таблицу Excel. Например, вы можете ввести данные {1, 3, 4, 5} в электронную таблицу как:

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

=ПОИСКПОЗ(2, A1:A4, 1)

=XПОИСКПОЗ(2, A1:A4, -1)

Обратите внимание, что значение последнего из двух функций отличается, потому что они используют разные правила. После того, как вы введете функцию, вы можете нажать «ВВОД», чтобы Excel нашел совпадение

Здесь Excel выводит число «1», означающее, что следующее значение, близкое к «2», но меньшее, чем оно находится в первой позиции выбранного вами массива, ячейке A1, которая имеет значение 1. После того, как Excel найдет match, вы можете сохранить книгу, перейдя в верхний левый угол в разделе «Файл» и нажав «Сохранить», чтобы сохранить свою работу.

Обратите внимание, что ни один из продуктов или компаний, упомянутых в этой статье, не связан с компанией Indeed

Функции ИНДЕКС и ПОИСКПОЗ в Excel на простых примерах

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

Более подробно о функциях ВПР и ПРОСМОТР.

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

В следующем примере формула вернет 3, поскольку число 300 находится в третьем столбце диапазона B1:I1.

– функция ПОИСКПОЗ ищет первое значение в точности равное заданному. Сортировка не требуется.
1 или вовсе опущено – функция ПОИСКПОЗ ищет самое большое значение, которое меньше или равно заданному. Требуется сортировка в порядке возрастания.
-1 – функция ПОИСКПОЗ ищет самое маленькое значение, которое больше или равно заданному. Требуется сортировка в порядке убывания.

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

Функция ИНДЕКС возвращает содержимое ячейки, которая находится на пересечении заданных строки и столбца. Например, на рисунке ниже формула возвращает значение из диапазона A1:C4, которое находится на пересечении 3 строки и 2 столбца.

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

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

Например, следующая формула возвращает пятое значение из диапазона A1:A12 (вертикальный вектор):

Данная формула возвращает третье значение из диапазона A1:L1(горизонтальный вектор):

Совместное использование ПОИСКПОЗ и ИНДЕКС в Excel

Пускай ячейка C15 содержит указанный нами месяц, например, Май. А ячейка C16 – тип товара, например, Овощи. Введем в ячейку C17 следующую формулу и нажмем Enter:

=ИНДЕКС(B2:E13; ПОИСКПОЗ(C15;A2:A13;0); ПОИСКПОЗ(C16;B1:E1;0))

В данной формуле функция ИНДЕКС принимает все 3 аргумента:

Первый аргумент – это диапазон B2:E13, в котором мы осуществляем поиск.
Вторым аргументом функции ИНДЕКС является номер строки. Номер мы получаем с помощью функции ПОИСКПОЗ(C15;A2:A13;0). Для наглядности вычислим, что же возвращает нам данная формула:
Третьим аргументом функции ИНДЕКС является номер столбца. Этот номер мы получаем с помощью функции ПОИСКПОЗ(C16;B1:E1;0). Для наглядности вычислим и это значение:

=ИНДЕКС(B2:E13;D15;D16)

Как видите, все достаточно просто!

ИНДЕКС

Эта функция возвращает значение конкретной ячейки в указанном диапазоне:

=ИНДЕКС(диапазон; номер_строки; )

Если в диапазон ячеек входит только один столбец, достаточно будет указать только номер строки. Когда в выбранном диапазоне получается два столбца и больше, можно будет выбрать его номер, чтобы точно обозначить ячейку.

Попробуем в нашем примере с бюджетом найти того, кто сделал самую большую покупку. Мы уже знаем номер строки, в которой записана самая большая сумма, и на этой же строке записан тот, кто эту сумму потратил. Используем это:

=ИНДЕКС(B1:B10;E10)

Когда Excel встретит такую формулу, он возьмёт наш диапазон из второго столбца, затем возьмёт номер строки с самой большой покупкой (она у нас в ячейке Е10, нашли в прошлом примере), и определит, что самую крупную покупку сделал Миша.

Функция ЕСЛИ в Excel (общие сведения)

Любая программа пусть это даже небольшая, обязательно содержит последовательность действий, которая называется алгоритмом. Выглядеть он может так:

Проверить всю колонку А на предмет наличия четных чисел.
Если четное число обнаружено, сложить такие-то значения.
Если четное число не обнаружено, то выдать надпись «не обнаружено».
Проверить получившееся число, является ли оно четным.
Если да, то сложить его со всеми четными числами, отобранными в пункте 1.

И пусть это только гипотетическая ситуация, которая вряд ли будет нужной в реальной жизни, но выполнение любой задачи обязательно подразумевает наличие похожего алгоритма. Перед тем, как использовать функцию ЕСЛИ, нужно иметь в голове четкое представление о том, какого результата нужно достичь.

Синтаксис функции ЕСЛИ с одним условием

Любая функция в Ecxel выполняется с помощью формулы. Шаблон, по которому нужно передавать функции данные, называется синтаксисом. В случае с оператором ЕСЛИ, формула будет в таком формате.

=ЕСЛИ (логическое_выражение;значение_если_истина;значение_если_ложь)

Давайте рассмотрим синтаксис более подробно:

Логическое выражение. Это непосредственно условие, соответствие или несоответствие которому проверяет Excel. Проверяться могут как числовые, так и текстовая информация.
Значение_если_истина. Результат, который отобразится в ячейке в случае, если проверяемые данные соответствуют заданному критерию.
Значение_если_ложь. Результат, который выводится в ячейку, если проверяемые данные не соответствуют условию.

Вот пример для наглядности.

Здесь функция осуществляет сравнение ячейки А1 с числом 20. Это первый пункт синтаксиса. Если содержимое оказывается больше этого значения, в ячейку, где формула была прописана, выводится значение «больше 20». Если же этому условию ситуация не соответствует – «меньше или равно 20».

Вот еще одна ситуация. Для получения права сдавать экзаменационную сессию учащимися должна быть пройдена зачетная. Студентам получилось одержать зачет по всем предметам, и теперь остался последний, который оказался решающим. Наша задача – определить, какие из студентов допущены к экзаменам, а какие – нет.

Поскольку нам нужно проверить текст, а не число, в первом аргументе указывается B2=”зач.”.

Синтаксис функции ЕСЛИ с несколькими условиями

Нередко недостаточно одного критерия, на предмет соответствия которому нужно проверять значение. Если надо учитывать больше одного варианта, можно вкладывать функции ЕСЛИ одна в другую. Получится несколько вложенных функций.

Чтобы было более наглядно, вот синтаксис.

=ЕСЛИ(логическое_выражение;значение_если_истина;ЕСЛИ(логическое_выражение;значение_если_истина;значение_если_ложь))

В этом случае функция проверит сразу два критерия. Если первое условие оказывается истинным возвращается значение, полученное в результате совершения операции в первом аргументе. Если нет – осуществляется проверка на соответствие второму критерию.

Вот пример.

А с помощью такой формулы (показана на скриншоте ниже) можно сделать анализ успеваемости каждого студента.

Как видим, здесь было добавлено еще одно условие, но принцип не изменился. Так можно сразу несколько критериев проверять.

ПОИСКПОЗ в Excel в примерах

По одному критерию

Для поиска значений в столбце возьмем, к примеру ПОИСКПОЗ («Дарья»; A1:A9; 1). При подстановке фразы расположенной в B1, и поиске в промежутке A1:A9, в место, где используется формула, будет возвращено его местоположение. Искомое слово находится в пятой ячейке, поэтому возвращено «5».

В этом случае формула =ПОИСКПОЗ(А3;В1:I1). Поскольку число 300 находится в третьем столбце, будет возвращена «3».

По двум столбцам

Для поиска значений в диапазоне ячеек по определенному условию воспользуемся таблицей автомобилей в отделах.

Названия авто и отделов дублируются в массиве, но не попарно. Отыщем первый попавшийся Форд:

Для того, чтобы конкретизировать запрос и найти авто из другого отдела нужно в ячейке B16 ввести основной критерий, а в C16 ввести критерий, которые сужает область, а в формуле, в «искомое значение» вписать номера ячеек c входными данными вот так: «B16&C16». Такие же манипуляции провести с массивом, в котором происходит поиск. То есть, дописать к основному «B2:B13» промежутку знак «&» и ввести второй промежуток «C2:C13».

Удерживая «CTRL+SHIFT+ENTER» произвести операцию.

Текстовые значения

Для такого типа операций обязательно нужно заключать слово в кавычки (например, “Слон”), иначе будет возвращена ошибка.

Числа

С числами все просто. Ввести число в “Искомое значение”, затем указать промежуток и запустить выполнение. В зависимости от типа сопоставления программа выдаст результат.

Подстановочные знаки

Среди прочего, есть возможности распознавания знаков. Происходит это вот так:

«?» – является заменой для любого символа.
«*» – замена любой последовательности символов.

Работает подстановка только в случае, если тип сопоставления равен «0».

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

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

Поиск с учетом регистра

Так как формула не различает изменения в регистре, следует внести некоторые изменения:

ПОИСКПОЗ (ИСТИНА; СОВПАД (ИскомоеЗначение;Просматриваемый_Массив); 0)

Суть формулы в том, что «СОВПАД» позволяет найти точные совпадения в таблице. Если найденный элемент в таблице соответствует критериям – его расположение возвращается в качестве результата. Для обработки требуется нажать комбинацию «Ctrl+Shift+Enter».

Нужные данные расположены в E1, промежуток, в котором его нужно найти — A2:A8.

Совмещение с ИНДЕКС

Ячейка, в которой используется функция «ИНДЕКС» принимает значение, которое она получает в результате использования «ПОИСКПОЗ».

Формула выглядит вот так:

=ИНДЕКС (столбец для возвращения, ПОИСКПОЗ (значение, столбец, 0)).

Например, имеется таблица населения нескольких стран:

Найдем население Нигерии с использованием формулы:

=ИНДЕКС (промежуток1; ПОИСКПОЗ («ИскомоеЗначение»; ПромежутокЗначений2; 0)), где:

«промежуток1» — столбец с количеством населения (C2:C11);
«промежуток2» — столбец с названием страны (B2:B11).

Далее, ПОИСКПОЗ проводит поиск «Искомое значение» (Нигерия) среди тех, что расположены в ячейках B2:B11. Будет возвращено «8», ведь в списке Нигерия находится на восьмом месте.

Число 7 передается во второй параметр функции «ИНДЕКС» и указывает на ячейку под соответствующим номером из промежутка (C8). Результат:

Функция ПОИСКПОЗ в Google Таблицах

Базовая информация об ИНДЕКС и ПОИСКПОЗ

ИНДЕКС – синтаксис и применение функции

ПОИСКПОЗ – синтаксис и применение функции

Совместное использование ПОИСКПОЗ и ИНДЕКС в Excel

Поиск позиции элемента в списке с ПОИСКПОЗ (MATCH)

Точный поиск

Поиск первой или последней текстовой ячейки

Поиск ближайшего числа или даты

Связка функций ПОИСКПОЗ и ИНДЕКС

Почему ИНДЕКС/ПОИСКПОЗ лучше, чем ВПР?

4 главных преимущества использования ПОИСКПОЗ/ИНДЕКС в Excel:

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

ИНДЕКС

Совместное внедрение ПОИСКПОЗ и ИНДЕКС в Excel

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

1. Использование функции ПОИСКПОЗ для текстовых значений

2. Использование функции ПОИСКПОЗ для неточных значений

Функции ИНДЕКС и ПОИСКПОЗ в Excel на простых примерах

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

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

Совместное использование ПОИСКПОЗ и ИНДЕКС в Excel

ИНДЕКС

Функция ЕСЛИ в Excel (общие сведения)

Синтаксис функции ЕСЛИ с одним условием

Синтаксис функции ЕСЛИ с несколькими условиями

ПОИСКПОЗ в Excel в примерах

По одному критерию

По двум столбцам

Текстовые значения

Числа

Подстановочные знаки

Поиск с учетом регистра

Совмещение с ИНДЕКС

Похожие записи:

Похожие записи: