ПОИСКПОЗ и ИНДЕКС в Excel: пример и объяснения

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

Как работает оператор ПОИСКПОЗ

ПОИСКПОЗ и ИНДЕКС в Excel: пример и объяснения

ПРИМЕЧАНИЕ! Если вам что-то будет в этой главе не понятно, не переживайте, мы еще раз все повторим на примерах. Ваша задача внимательно прочесть эту главу и попытаться уловить саму суть. Саму структуру мы уже разберем далее.

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

Теперь давайте рассмотрим синтаксис:

=ПОИСКПОЗ(Значение;Массив; [Точность поиска])

  • Значение – это тот элемент, которые мы хотим найти. Может принимать любые значения от цифирного и символьного до логического или адреса ячейки.
  • Массив – это диапазон ячеек, среди которых и будет искать функция ПОИСКПОЗ.
  • Точность поиска – этот аргумент указывает на то, насколько точным должно быть значение, которые мы ищем. Может принимать три значения. «1» – если данный элемент не найден по точному значению, то выводит информацию о максимально приближенном по убиванию. «-1» – то же самое что и «1», но выводит максимально приближенное по возрастанию. «0» – ищет только точные совпадения. Данные аргумент не является обязательным и его можно опустить из функции.

Если функция не находит совпадений, то выводит:

#Н/Д

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

Пример 1: Поиск адреса элемента

В первом примере мы рассмотрим, как именно вообще работает функция, как её заполнять и что она возвращает. Представим себе, что нам нужно среди множества товаров найти адрес массива со значением «Молоко».

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

ПОИСКПОЗ и ИНДЕКС в Excel: пример и объяснения

  1. Ставим «Категорию» – «Ссылки и массивы».
  2. Ищем нашу функцию, выделяем её и жмем «ОК».

ПОИСКПОЗ и ИНДЕКС в Excel: пример и объяснения

  1. Теперь уже заполняем значения. В первой строке указываем адрес элемента, который мы хотим найти. Вы можете указать как адрес, кликнув мышкой, так и вписать вручную – например:

“Молоко”

  1. Далее ниже указываем диапазон адресов – их можно выбрать мышкой, или вписать вручную. Например:

A2:A6

  1. «Тип_сопоставления» – это как раз та самая точность. Так как мы работаем с текстом, то лучше указать полную точность:

0

ПОИСКПОЗ и ИНДЕКС в Excel: пример и объяснения

  1. Жмем «ОК».

ПОИСКПОЗ и ИНДЕКС в Excel: пример и объяснения

Обратите внимание, что функция выводит адрес относительно массива, а не адрес ячейки. То есть «Молоко» находится в 4 строке, но в массиве товаров адрес – 3. Об этом нужно всегда помнить.

Пример 2: Поиск товара и использование адресов

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

  1. Давайте создадим еще две строки – «Поиск товара» и «Адрес». Первую строку мы будем использовать в качестве исходного значения, которое в любой момент будет меняться. Для примера введем туда название любого элемента из массива.
  2. Теперь ставим курсор в поле «Адрес» и вставляем нашу функцию.

ПОИСКПОЗ и ИНДЕКС в Excel: пример и объяснения

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

ПОИСКПОЗ и ИНДЕКС в Excel: пример и объяснения

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

ПОИСКПОЗ и ИНДЕКС в Excel: пример и объяснения

Пример 3: Работа с числовыми значениями

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

  1. Так функция работает последовательно и выводит адрес элемента, который подходит лучше всего – нам нужно отсортировать колонку. Выделите колонку с цифрами, нажав по букве сверху.
  2. Перейдите на вкладку «Главная».

ПОИСКПОЗ и ИНДЕКС в Excel: пример и объяснения

  1. Теперь справа в разделе «Редактирование» находим значок «Сортировка и фильтр» и выбираем «Сортировка по убыванию».

ПОИСКПОЗ и ИНДЕКС в Excel: пример и объяснения

  1. Оставляем настройку по умолчанию и жмем по кнопке сортировки. Вы увидите, что сортировка произошла также со строками.

ПОИСКПОЗ и ИНДЕКС в Excel: пример и объяснения

  1. Выбираем любую ячейку и вставляем в неё функцию.

ПОИСКПОЗ и ИНДЕКС в Excel: пример и объяснения

  1. Теперь вставляем в первую строчку наше приближенное значение, которое мы хотим найти. Указываем диапазон массива. И в конце ставим «-1», чтобы также попробовать найти не точное совпадение, а приближенное.

ПОИСКПОЗ и ИНДЕКС в Excel: пример и объяснения

  1. И мы нашли товар с прибылью, приближенную к 30 000 – им оказалось «Молоко».

ПОИСКПОЗ и ИНДЕКС в Excel: пример и объяснения

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

Пример 4: Использование с другими функциями

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

=ИНДЕКС(массив;номер_строки;номер_столбца)

Если вам пока ничего не понятно, не стоит переживать – сейчас мы все разберем на примере. В нашем примере – наша задача вывести не просто адрес массива, а наименование товара, которое имеет приближенную сумму к числу 32 000.

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

ПОИСКПОЗ и ИНДЕКС в Excel: пример и объяснения

  1. Выбираем «Сортировку по возрастанию».

ПОИСКПОЗ и ИНДЕКС в Excel: пример и объяснения

  1. В качестве суммы мы будем использовать значение – 32 000. Теперь нам нужно найти товар – вставляем туда функцию.

ПОИСКПОЗ и ИНДЕКС в Excel: пример и объяснения

  1. Используем:

ИНДЕКС

ПОИСКПОЗ и ИНДЕКС в Excel: пример и объяснения

  1. Нам нужна обычная формула, поэтому оставьте настройки по умолчанию.

ПОИСКПОЗ и ИНДЕКС в Excel: пример и объяснения

  1. В качестве массива указываем диапазон товаров.
  2. В «Номер строки» нам нужно вписать уже формулу ПОИСКПОЗ. В качестве значения, по которому мы будем искать, указываем соседнюю строку 32 000. Далее указываем диапазон всех сумм. В конце ставим «1», так как мы до этого делали сортировку по возрастанию (вспоминаем прошлый пример). «Номер столбца» не указываем.

ПОИСКПОЗ и ИНДЕКС в Excel: пример и объяснения

Далее он выведет правильную информацию. Если вы посмотрите на картинку ниже, то вы можете немного запутаться – почему «Хлеб», который имеет сумму «23013» максимально приближен к 32 000, а не «Молоко» с суммой в «33670».

ПОИСКПОЗ и ИНДЕКС в Excel: пример и объяснения

Все дело в сортировке. Тут вы должны уяснить и понять, каким образом работает функция ПОИСКПОЗ. При выставлении в формуле аргумента «1» – он возвращает самое близкое к этому по убыванию, а это как раз 23013.

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

Я понимаю, что понять это сразу достаточно сложно, поэтому я вам советую потренироваться и попробовать оба варианта с различными аргументами. Таким образом вы сможете понять логику функции и сможете её применять в ваших примерах и задачах. Лучше всего использовать вместе формулы индекса и поиска позиции в Эксель – да так сложнее, но в итоге вы и поймете, как их обычно используют на практике.

На этом все, дорогие читатели портала WiFiGiD.RU. Я понимаю, что урок получился достаточно сложный. Если что-то было непонятно, или вам нужно что-то объяснить – пишите свои вопросы в комментариях, и я вам помогу.

Автор статьи
Бородач 1344 статей
Сенсей по решению проблем с WiFiем. Обладатель оленьего свитера, колчана витой пары и харизматичной бороды. Любитель душевных посиделок за танками.
WiFiGid
Комментарии: 4
  1. Евгения

    Спасибо вам огромное за такой подробный урок с примерами. Долго не могла понять эти формулы, но вроде в голове началась образовываться связь :idea:

  2. Павел

    Вродре разобрался, но вообще реально, сложновато понять логику некоторых функций.

  3. Аноним

    Вот к чему эти сложности. Могли бы конечно функции попроще как-то сделать или просто пологичнее – согласен с прошлым оратором.

  4. Deuce H_ K_

    А я сразу обратил внимание вот на что. Чуть ли не единственные русские функции в зарубежном Excel, видимо еще из тех времен, когда были “партнеры”. :sad:

Добавить комментарий
После отправки комментарий может не отображаться - это нормально. Сразу же после модерации он будет опубликован. Если Вы хотите быстро узнать о получении ответа, рекомендуем оставить свой e-mail (это необязательно). E-mail используется исключительно для Вашего оповещения, мы не занимаемся спамом.

;-) :| :x :twisted: :smile: :shock: :sad: :roll: :razz: :oops: :o :mrgreen: :lol: :idea: :grin: :evil: :cry: :cool: :arrow: :???: :?: :!:

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

Adblock
detector