Функции ИНДЕКС и ПОИСКПОЗ в Excel лучшая альтернатива для ВПР

Здравствуй уважаемый читатель!

В этой статье я хочу описать, наверное, одну из наиболее полезных функций в Excel – это функция ВПР (VLOOKUP). О функции можно сказать многое, но самое первое, что стоить отметить, это то, что функция относится к сложным и наименее понятной.

В статье я постараюсь простым и доступным языком описать, как работает функция ВПР, а также на примерах показать ее особенности, описание и синтаксис.

Итак, что же в сущности это за функция, что она делает, и как она пишется. Самое простое объяснение, о работе функции ВПР, она ищет в перечне значений, по заданному вами критерию, уникальному идентификатору, определенную информацию, которая с ним связана.

Если розшифровать название функции ВПР, то с первой буквой станет понятно, как работает, эта функция, В – означает «вертикальная» , то есть она ищет значения в , а вот для горизонтальных списков у нас будет . Впервые функция ВПР стала доступна нам с Excel 2000.
Функция ВПР в Excel имеет следующий синтаксис:

=ВПР(_искомое значение_;_таблица_; _номер столбика_; _[интервальный просмотр]_) , где:

• искомое_значение – это именно то значение, которое нам нужно искать, а это может быть любое значение: число, дата, текст, ссылка на ячейку, которая содержит нужное значение или значение, получаемое другой формулой;
• таблица – это два и больше столбика с разнообразными данными, кстати, регистр символов в поиске, функцией не учитывается;
• номер_столбца – это номер столбика в указанном диапазоне, из которого будет получено значение, которое находится в найденной строке;
• интервальный_просмотр – этот параметр определяет, что же именно будем искать, для точного совпадения аргумент будет равен «ЛОЖЬ» (FALSE) или приблизительное совпадение, аргумент станет равным ИСТИНА (TRUE) . Этот параметр не является обязательным, но тем не менее он важен. В примерах, далее, я продемонстрирую, как создать формулы для точного и приблизительного совпадения.

Теперь на примере рассмотрим, как произвести поиск, каких-либо данных на другом листе, так как на практике, функцию ВПР очень редко используют для поиска на текущем листе:

ВПР(«GM»;$A$5:$B$10;2)

Формула ищет текст «GM», в столбике А на текущем листе.
Совет! При использовании аргумента «таблица», желательно использовать, такой вариант, как (это адрес ячейки со знаком $). В этом случае диапазон поиска станет закреплённым и не изменится при копировании формулы.

Рассмотрим пример поиска значений, как работает функция ВПР в другой рабочей книге:

ВПР(”GM”;[Путь к файлу]База!А2:В10;2)

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

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

Еще можно рассмотреть пример, когда точное значение нам неизвестно мы будем использовать в формуле ВПР символы подстановки. Иногда нам нужно найти не что-то конкретное, а нечто обладающее общими признаками, как вариант у нас есть много диванов разных моделей, как и прочей мебели, но выбрать нам надо только их.

Функция ВПР, при подстановке,может использовать такие символы:

• «?» (знак вопроса ) – позволит заменить один любой символ;
• «*» (звёздочка) – заменит любое количество и последовательность символов.

ВПР(“A*”;$A$2:$B$10;1;ЛОЖЬ)

Совет! Для того что бы функция ВПР, корректно работала нужно в качестве четвертого аргумента использовать параметр «ЛОЖЬ».
Ну, если мы уже затронули тему точного или приближенного совпадения в синтаксисе функции ВПР, то давайте ее рассмотрим поподробнее:

• если аргумент «интервальный просмотр» равен «ЛОЖЬ» , в таком случае формула ищет точное совпадение с аргументом «искомое значение». Если формула встретит два и более значения, отвечающих аргументу «искомое значение», то будет выбрано первое из списка, в случае, когда совпадение не найдены, формула вернет ;
• если же аргумент «интервальный просмотр» имеет значение «ИСТИНА» формула будет искать приблизительное совпадение, точнее будет, что функция сначала поищет точное совпадение, а уже потом, не найдя его, выберет приблизительное.

Совет! В случае, когда аргумент «интервальный просмотр» ровняется «ИСТИНА» или же не указан, значение в первом столбике нужно , от меньшего к большому. Иначе имеется возможность ошибочного результата от функции ВПР.

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

=ВПР(200;$A$2:$B$15;2;ЛОЖЬ)

Несмотря на то, что значений 200 у нас несколько, получили только одно, так как при точном совпадении функции ВПР, система использует только первое значение которое было найдено в указанном диапазоне.
Теперь испытаем, работу функции ВПР для приблизительного совпадения значений. Поищем, какой автомобиль ездит со скоростью 260 км/час. Первое что вы делаете в случае, когда «интервальный просмотр» равняется «ИСТИНА» — вы выполняете сортировку вашего диапазона значений по первому столбику по порядку его возрастания. Это необходимо и важно, поскольку функция ВПР находит следующее наибольшее значение от заданного условия, а после поиск прекращается. Если же вы не последуете совету по сортировке, итогом будет сообщение об или другие странные результаты.

Для поиска используем ВПР следующего вида:

=ВПР(260;$A$2:$B$15;2;ИСТИНА)

Итак, наша формула вернула найденное значение 240, хотя у нас есть и значение 270 и это значение вроде как ближе, но особенность того, как работает функция ВПР, это то что при использовании точного совпадения она ищет самое большое значение в списке, но не превышающие его.
Я думаю примеры, которые я рассматривал в статье, помогут вам понятьвсё о работе функции ВПР, как ее используют и для чего ее нужно использовать.

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

А на этом у меня всё! Я очень надеюсь, что всё вышеизложенное вам понятно. Буду очень благодарен за оставленные комментарии, так как это показатель читаемости и вдохновляет на написание новых статей! Делитесь с друзьями прочитанным и ставьте лайк!

Сегодня мы начинаем серию статей, описывающих одну из самых полезных функций Excel – ВПР (VLOOKUP). Эта функция, в то же время, одна из наиболее сложных и наименее понятных.

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

Функция ВПР в Excel – общее описание и синтаксис

Итак, что же такое ВПР ? Ну, во-первых, это функция Excel. Что она делает? Она ищет заданное Вами значение и возвращает соответствующее значение из другого столбца. Говоря техническим языком, ВПР ищет значение в первом столбце заданного диапазона и возвращает результат из другого столбца в той же строке.

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

Первая буква в названии функции ВПР (VLOOKUP) означает В ертикальный (V ertical). По ней Вы можете отличить ВПР от ГПР (HLOOKUP), которая осуществляет поиск значения в верхней строке диапазона – Г оризонтальный (H orizontal).

Функция ВПР доступна в версиях Excel 2013, Excel 2010, Excel 2007, Excel 2003, Excel XP и Excel 2000.

Синтаксис функции ВПР

Функция ВПР (VLOOKUP) имеет вот такой синтаксис:

VLOOKUP(lookup_value,table_array,col_index_num,)
ВПР(искомое_значение;таблица;номер_столбца;[интервальный_просмотр])

Как видите, функция ВПР в Microsoft Excel имеет 4 параметра (или аргумента). Первые три – обязательные, последний – по необходимости.

• lookup_value (искомое_значение) – значение, которое нужно искать.Это может быть значение (число, дата, текст) или ссылка на ячейку (содержащую искомое значение), или значение, возвращаемое какой-либо другой функцией Excel. Например, вот такая формула будет искать значение 40 :

  VLOOKUP(40,A2:B15,2)
  =ВПР(40;A2:B15;2)

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

• table_array (таблица) – два или более столбца с данными.Запомните, функция ВПР всегда ищет значение в первом столбце диапазона, заданного в аргументе table_array (таблица). В просматриваемом диапазоне могут быть различные данные, например, текст, даты, числа, логические значения. Регистр символов не учитывается функцией, то есть символы верхнего и нижнего регистра считаются одинаковыми.Итак, наша формула будет искать значение 40 в ячейках от A2 до A15 , потому что A – это первый столбец диапазона A2:B15, заданного в аргументе table_array (таблица):

  VLOOKUP(40,A2:B15,2)
  =ВПР(40;A2:B15;2)

Если значение аргумента col_index_num (номер_столбца) меньше 1 , то ВПР сообщит об ошибке #VALUE! (#ЗНАЧ!). А если оно больше количества столбцов в диапазоне table_array (таблица), функция вернет ошибку #REF! (#ССЫЛКА!).

• range_lookup (интервальный_просмотр) – определяет, что нужно искать:
  • точное совпадение, аргумент должен быть равен FALSE (ЛОЖЬ);
  • приблизительное совпадение, аргумент равен TRUE (ИСТИНА) или вовсе не указан.

  Этот параметр не обязателен, но очень важен. Далее в этом учебнике по ВПР я покажу Вам несколько примеров, объясняющих как правильно составлять формулы для поиска точного и приблизительного совпадения.

Примеры с функцией ВПР

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

Как, используя ВПР, выполнить поиск на другом листе Excel

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

Чтобы, используя ВПР , выполнить поиск на другом листе Microsoft Excel, Вы должны в аргументе table_array (таблица) указать имя листа с восклицательным знаком, а затем диапазон ячеек. К примеру, следующая формула показывает, что диапазон A2:B15 находится на листе с именем Sheet2 .

VLOOKUP(40,Sheet2!A2:B15,2)
=ВПР(40;Sheet2!A2:B15;2)

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

Формула, показанная на скриншоте ниже, ищет текст “Product 1” в столбце A (это 1-ый столбец диапазона A2:B9) на листе Prices .

VLOOKUP("Product 1",Prices!$A$2:$B$9,2,FALSE)
=ВПР("Product 1";Prices!$A$2:$B$9;2;ЛОЖЬ)

Пожалуйста, помните, что при поиске текстового значения Вы обязаны заключить его в кавычки (“”), как это обычно делается в формулах Excel.

Для аргумента table_array (таблица) желательно всегда использовать абсолютные ссылки (со знаком $). В таком случае диапазон поиска будет оставаться неизменным при копировании формулы в другие ячейки.

Поиск в другой рабочей книге с помощью ВПР

Чтобы функция ВПР работала между двумя рабочими книгами Excel, нужно указать имя книги в квадратных скобках перед названием листа.

Например, ниже показана формула, которая ищет значение 40 на листе Sheet2 в книге Numbers.xlsx :

VLOOKUP(40,Sheet2!A2:B15,2)
=ВПР(40;Sheet2!A2:B15;2)

Вот простейший способ создать в Excel формулу с ВПР , которая ссылается на другую рабочую книгу:

1. Откройте обе книги. Это не обязательно, но так проще создавать формулу. Вы же не хотите вводить имя рабочей книги вручную? Вдобавок, это защитит Вас от случайных опечаток.
2. Начните вводить функцию ВПР , а когда дело дойдёт до аргумента table_array (таблица), переключитесь на другую рабочую книгу и выделите в ней нужный диапазон поиска.

На снимке экрана, показанном ниже, видно формулу, в которой для поиска задан диапазон в рабочей книге PriceList.xlsx на листе Prices .

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

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

VLOOKUP(40,"Sheet2"!A2:B15,2)
=ВПР(40;"Sheet2"!A2:B15;2)

Как использовать именованный диапазон или таблицу в формулах с ВПР

Если Вы планируете использовать один диапазон поиска в нескольких функциях ВПР , то можете создать именованный диапазон и вводить его имя в формулу в качестве аргумента table_array (таблица).

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

Теперь Вы можете записать вот такую формулу для поиска цены товара Product 1 :

VLOOKUP("Product 1",Products,2)
=ВПР("Product 1";Products;2)

Большинство имен диапазонов работают для всей рабочей книги Excel, поэтому нет необходимости указывать имя листа для аргумента table_array (таблица), даже если формула и диапазон поиска находятся на разных листах книги. Если же они находятся в разных книгах, то перед именем диапазона нужно указать название рабочей книги, к примеру, вот так:

VLOOKUP("Product 1",PriceList.xlsx!Products,2)
=ВПР("Product 1";PriceList.xlsx!Products;2)

Так формула выглядит гораздо понятнее, согласны? Кроме того, использование именованных диапазонов – это хорошая альтернатива абсолютным ссылкам, поскольку именованный диапазон не меняется при копировании формулы в другие ячейки. Значит, Вы можете быть уверены, что диапазон поиска в формуле всегда останется корректным.

Если преобразовать диапазон ячеек в полноценную таблицу Excel, воспользовавшись командой Table (Таблица) на вкладке Insert (Вставка), то при выделении диапазона мышью, Microsoft Excel автоматически добавит в формулу названия столбцов (или название таблицы, если Вы выделите всю таблицу).

Готовая формула будет выглядеть примерно вот так:

VLOOKUP("Product 1",Table46[:],2)
=ВПР("Product 1";Table46[:];2)

А может даже так:

VLOOKUP("Product 1",Table46,2)
=ВПР("Product 1";Table46;2)

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

Использование символов подстановки в формулах с ВПР

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

• Знак вопроса (?) – заменяет один любой символ.
• Звёздочка (*) – заменяет любую последовательность символов.

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

• Когда Вы не помните в точности текст, который нужно найти.
• Когда Вы хотите найти какое-то слово, которое является частью содержимого ячейки. Знайте, что ВПР ищет по содержимому ячейки целиком, как при включённой опции Match entire cell content (Ячейка целиком) в стандартном поиске Excel.
• Когда в ячейке содержатся дополнительные пробелы в начале или в конце содержимого. В такой ситуации Вы можете долго ломать голову, пытаясь понять, почему формула не работает.

Пример 1: Ищем текст, начинающийся или заканчивающийся определенными символами

Предположим, что Вы хотите найти определенного клиента в базе данных, показанной ниже. Вы не помните его фамилию, но знаете, что она начинается на “ack”. Вот такая формула отлично справится с этой задачей:

VLOOKUP("ack*",$A$2:$C$11,1,FALSE)
=ВПР("ack*";$A$2:$C$11;1;ЛОЖЬ)

Теперь, когда Вы уверены, что нашли правильное имя, можно использовать эту же формулу, чтобы найти сумму, оплаченную этим клиентом. Для этого достаточно изменить третий аргумент функции ВПР на номер нужного столбца. В нашем случае это столбец C (3-й в диапазоне):

VLOOKUP("ack*",$A$2:$C$11,3,FALSE)
=ВПР("ack*";$A$2:$C$11;3;ЛОЖЬ)

Вот ещё несколько примеров с символами подстановки:

~ Находим имя, заканчивающееся на “man”:

VLOOKUP("*man",$A$2:$C$11,1,FALSE)
=ВПР("*man";$A$2:$C$11;1;ЛОЖЬ)

~ Находим имя, начинающееся на “ad” и заканчивающееся на “son”:

VLOOKUP("ad*son",$A$2:$C$11,1,FALSE)
=ВПР("ad*son";$A$2:$C$11;1;ЛОЖЬ)

~ Находим первое имя в списке, состоящее из 5 символов:

VLOOKUP("?????",$A$2:$C$11,1,FALSE)
=ВПР("?????";$A$2:$C$11;1;ЛОЖЬ)

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

Пример 2: Сочетаем символы подстановки и ссылки на ячейки в формулах с ВПР

А теперь давайте разберём чуть более сложный пример, как осуществить поиск с помощью функции ВПР по значению в какой-то ячейке. Представьте, что в столбце A находится список лицензионных ключей, а в столбце B список имён, владеющих лицензией. Кроме этого, у Вас есть часть (несколько символов) какого-то лицензионного ключа в ячейке C1, и Вы хотите найти имя владельца.

Это можно сделать, используя вот такую формулу:

VLOOKUP("*"&C1&"*",$A$2:$B$12,2,FALSE)
=ВПР("*"&C1&"*";$A$2:$B$12;2;FALSE)

Эта формула ищет значение из ячейки C1 в заданном диапазоне и возвращает соответствующее значение из столбца B. Обратите внимание, что в первом аргументе мы используем символ амперсанда (&) до и после ссылки на ячейку, чтобы связать текстовую строку.

Как видно на рисунке ниже, функция ВПР возвращает значение “Jeremy Hill”, поскольку его лицензионный ключ содержит последовательность символов из ячейки C1.

Заметьте, что аргумент table_array (таблица) на скриншоте сверху содержит имя таблицы (Table7) вместо указания диапазона ячеек. Так мы делали в предыдущем примере.

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

И, наконец, давайте рассмотрим поподробнее последний аргумент, который указывается для функции ВПР – 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. Функция ВПР в Excel не может смотреть налево. Она всегда ищет значение в крайнем левом столбце диапазона, заданного аргументом table_array (таблица).
2. В функции ВПР все значения используются без учета регистра, то есть маленькие и большие буквы эквивалентны.
3. Если искомое значение меньше минимального значения в первом столбце просматриваемого диапазона, функция ВПР сообщит об ошибке #N/A (#Н/Д).
4. Если 3-й аргумент col_index_num (номер_столбца) меньше 1 , функция ВПР сообщит об ошибке #VALUE! (#ЗНАЧ!). Если же он больше количества столбцов в диапазоне table_array (таблица), функция сообщит об ошибке #REF! (#ССЫЛКА!).
5. Используйте абсолютные ссылки на ячейки в аргументе table_array (таблица), чтобы при копировании формулы сохранялся правильный диапазон поиска. Попробуйте в качестве альтернативы использовать именованные диапазоны или таблицы в Excel.
6. Когда выполняете поиск приблизительного совпадения, не забывайте, что первый столбец в исследуемом диапазоне должен быть отсортирован по возрастанию.
7. И, наконец, помните о важности четвертого аргумента. Используйте значения TRUE (ИСТИНА) или FALSE (ЛОЖЬ) обдуманно, и Вы избавитесь от многих головных болей.

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

Привет, друзья. Как часто вам приходится для какого-то значения искать соответствие в таблице Эксель? Например, нужно в справочнике найти адрес человека, или в прайсе – цену товара. Если такие задачи встречаются – этот пост именно для вас!

Я выполняю подобные процедуры каждый день и без описанных ниже функций мне действительно пришлось бы туго. Берите на заметку и применяйте их в работе!

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

Синтаксис функции ВПР такой: =ВПР(Искомое_значение; таблица_для_поиска; номер_выводимого_столбца; [тип_сопоставления]) . Рассмотрим аргументы:

• Искомое значение – значение, которое будем искать. Это обязательный аргумент;
• Таблица для поиска – тот массив ячеек, в котором будет поиск. Столбец с искомыми значениями должен быть первым в этом массиве. Это тоже обязательный аргумент;
• Номер выводимого столбца – порядковый номер столбца (начиная с первого в массиве), из которого функция выведет данные при совпадении искомых значений. Обязательный аргумент;
• Тип сопоставления – выберите «1» (или «ИСТИНА») для нестрогого совпадения, «0» («ЛОЖЬ») – для полного совпадения. Аргумент необязателен, если его упустить – будет выполнен поиск нестрогого совпадения .

Поиск точного совпадения с помощью ВПР

Посмотрим на примере, как работает функция ВПР, когда выбран тип сопоставления «ЛОЖЬ», поиск точного совпадения. В массиве В5:Е10 указаны основные средства некой компании, их балансовая стоимость, инвентарный номер и место расположения. В ячейке В2 указано наименование, для которого нужно в таблице найти инвентарный номер и поместить его в ячейку С2 .

Функция ВПР в Excel

Запишем формулу: =ВПР(B2;B5:E10;3;ЛОЖЬ) .

Здесь первый аргумент указывает, что в таблице нужно искать значение из ячейки В2 , т.е. слово «Факс». Второй аргумент говорит, что таблица для поиска — в диапазоне В5:Е10 , а искать слово «Факс» нужно в первом столбце, т.е. в массиве В5:В10 . Третий аргумент сообщает программе, что результат расчета содержится в третьем столбце массива, т.е. D5:D10 . Четвёртый аргумент равен «ЛОЖЬ», т.е. требуется полное совпадение.

И так, функция получит строку «Факс» из ячейки В2 и будет искать его в массиве В5:В10 сверху вниз. Как только совпадение будет найдено (строка 8), функция вернёт соответствующее значение из столбца D , т.е. содержимое D8 . Именно это нам и требовалось, задача решена.

Если искомое значение не будет найдено, функция вернёт .

Поиск неточного совпадения с помощью ВПР

Благодаря этой опции в работе ВПР, мы можем избежать сложных формул, чтобы найти нужный результат.

В массиве В5:С12 указаны процентные ставки по кредитам в зависимости от суммы займа. В ячейке В2 Указываем сумму кредита и хотим получить в С2 ставку для такой сделки. Задача сложна тем, что сумма может быть любой и вряд ли будет совпадать с указанными в массиве, поиск по точному совпадению не подходит:

Тогда запишем формулу нестрогого поиска: =ВПР(B2;B5:C12;2;ИСТИНА) . Теперь из всех представленных в столбце В данных программа будет искать ближайшее меньшее. То есть, для суммы 8 000 будет отобрано значение 5000 и выведен соответствующий процент.

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

Функция ГПР имеет такой же синтаксис, как и ВПР, но ищет результат не в столбцах, а в строках. То есть, просматривает таблицы не сверху вниз, а слева направо и выводит заданный номер строки, а не столбца.

Функция ПРОСМОТР работает аналогично ВПР, но имеет другой синтаксис. Я использую её, когда таблица данных содержит несколько десятков столбцов и для использования ВПР нужно дополнительно просчитывать номер выводимой колонки. В таких случаях функция ПРОСМОТР облегчает задачу. И так, синтаксис: =ПРОСМОТР(Искомое_значение; Массив_для_поиска; Массив_для_отображения ) :

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

При такой записи вы даёте не относительную ссылку массива результатов. А прямо на него указываете, т.е. не нужно предварительно просчитывать номер выводимого столбца. Используем функцию ПРОСМОТР в первом примере для функции ВПР (основные средства, инвентарные номера): =ПРОСМОТР(B2;B5:B10;D5:D10) . Задача успешно решена!

Еще один способ поиска данных – комбинирование функций ПОИСКПОЗ и ИНДЕКС.

Первая из них, служит для поиска значения в массиве и получения его порядкового номера: ПОИСКПОЗ(Искомое_значение; Просматриваемый_массив; [ Тип сопоставления ] ). Аргументы функции:

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

Позиция необходимого значения найдена, теперь можно вывести его на экран с помощью функции ИНДЕКС(Массив; Номер_строки; [Номер_столбца] ) :

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

Теперь скомбинируем эти функции, чтобы получить результат:

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

Чувствуете, как растут и крепчают Ваши знания и умения? Тогда не останавливайтесь, продолжайте читать ! В следующем посте мы будем рассматривать : будет сложно и интересно!

Функции ИНДЕКС и ПОИСКПОЗ в Excel – лучшая альтернатива для ВПР

​Смотрите также​Другими словами если в​​ соответствующей цены.​​Формула в ячейках исчезнет.​​ далеко не самым​​ использованием функции ВПР().​ с функцией​ один вариант ставки​Пример из жизни. Ставим​​ на другую деталь,​​Если всё сделано верно,​=ИНДЕКС($A$1:$E$11;4;5))​, значения в столбце​ заметно быстрее.​любой вставленный или​B​​ нужны, и поэтому​​Этот учебник рассказывает о​

​ нашей таблице повторяются​Переходим в ячейку второй​ Останутся только значения.​ ближайшим. Например, если​Пусть дана исходная таблица​​ВПР​​ комиссионных, равный 50%,​ задачу​ потому что функция​ Вы получите результат​Эта формула возвращает значение​ поиска должны быть​​Теперь, когда Вы понимаете​​ удалённый столбец изменит​, а конкретно –​ требуется помощь функции​ главных преимуществах функций​

​ значения «груши», «яблока»​ таблицы под названием​​​ попытаться найти ближайшую​​ (см. файл примера​.​ для тех продавцов,​Усложняем задачу​ ВПР нашла ближайшее​ как на рисунке​ на пересечении​ упорядочены по возрастанию,​ причины, из-за которых​​ результат формулы, поскольку​​ в ячейках​​ПОИСКПОЗ​​ИНДЕКС​ мы не сможем​ столбца «Цена».​Функция помогает сопоставить значения​ цену для 199,​​ лист Справочник).​​Если поэкспериментируем с несколькими​

• ​ кто сделал объём​Применяем функцию ВПР к​
• ​ число, меньшее или​ ниже:​
• ​4-ой​ а формула вернёт​
• ​ стоит изучать функции​ синтаксис​
  • ​B2:B10​.​
  • ​и​ просуммировать всех груш​
  • ​Выберите «Формулы»-«Ссылки и массивы»-«ВПР».​ в огромных таблицах.​
  • ​ то функция вернет​
  • ​Задача состоит в том,​ различными значениями итоговой​

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

​ продаж более $50000.​ решению задачи​ равное указанному (2345678).​​Как Вы, вероятно, уже​​строки и​​ максимальное значение, меньшее​​ПОИСКПОЗ​ВПР​, и возвращает число​Функция​ПОИСКПОЗ​

​ и яблок. Для​Ввести функцию ВПР​ Допустим, поменялся прайс.​ 150 (хотя ближайшее​ чтобы, выбрав нужный​ суммы продаж, то​​ А если кто-то​​Заключение​​ Эта ошибка может​​ заметили (и не​​5-го​​ или равное среднему.​

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

​и​​требует указывать весь​​3​MATCH​в Excel, которые​ этого нужно использовать​ можно и с​ Нам нужно сравнить​

​ мы убедимся, что​ продал на сумму​

• ​Проиллюстрируем эту статью примером​​ привести к неправильному​ раз), если вводить​столбца в диапазоне​Если указываете​
• ​ИНДЕКС​​ диапазон и конкретный​, поскольку «Japan» в​(ПОИСКПОЗ) в Excel​ делают их более​ функцию ПРОСМОТР(). Она​ помощью «мастера функций».​ старые цены с​​ Это опять следствие​​ его Наименование и​
• ​ формула работает правильно.​​ более $60000 –​ из реальной жизни​ выставлению счета клиенту.​ некорректное значение, например,​A1:E11​-1​, давайте перейдём к​​ номер столбца, из​​ списке на третьем​

​ ищет указанное значение​ привлекательными по сравнению​​ очень похожа на​​ Для этого нажмите​ новыми ценами.​ того, что функция находит​ Цену. ​

​Когда функция​​ тому заплатить 60%​​ – расчёт комиссионных​

​, то есть значение​, значения в столбце​​ самому интересному и​​ которого нужно извлечь​ месте.​​ в диапазоне ячеек​​ с​​ ВПР но умеет​​ на кнопку «fx»,​В старом прайсе делаем​​ наибольшее число, которое​​Примечание​

​ВПР​ комиссионных?​ на основе большого​ соответствие" указано значение​ просматриваемом массиве, формула​ ячейки​ поиска должны быть​​ увидим, как можно​​ данные.​

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

​Функция​​ и возвращает относительную​​ВПР​ хорошо работать с​ которая находиться в​ столбец «Новая цена».​ меньше или равно​. Это "классическая" задача для​

​работает с базами​​Теперь формула в ячейке​​ ряда показателей продаж.​ ЛОЖЬ или 0,​ИНДЕКС​E4​​ упорядочены по убыванию,​​ применить теоретические знания​Например, если у Вас​INDEX​

​ массивами в исходных​​ начале строки формул.​​Выделяем первую ячейку и​ заданному.​

• ​ B2, даже если​​ Мы начнём с​ а точного совпадения​/​. Просто? Да!​ а возвращено будет​ на практике.​ есть таблица​
• ​(ИНДЕКС) использует​​ в диапазоне.​ примеров формул, которые​ значениях.​
• ​ Или нажмите комбинацию​​ выбираем функцию ВПР.​Если нужно найти по​​ статью Справочник).​​Range_lookup​ она записана без​ очень простого варианта,​
  • ​ нет, вместо неправильного​​ПОИСКПОЗ​​В учебнике по​​ минимальное значение, большее​Любой учебник по​A1:C10​3​Например, если в диапазоне​ помогут Вам легко​Функция ВПР (Вертикальный ПРосмотр)​
  • ​ горячих клавиш SHIFT+F3.​​ Задаем аргументы (см.​ настоящему ближайшее к​Для вывода Наименования используйте формулу =ВПР($E9;$A$13:$C$19;2;ЛОЖЬ) или =ВПР($E9;$A$13:$C$19;2;ИСТИНА) или =ВПР($E9;$A$13:$C$19;2) (т.е.​​(Интервальный_просмотр) должен принимать​​ ошибок, стала совершенно​​ и затем постепенно​​ значения формула возвращает​сообщает об ошибке​ВПР​​ или равное среднему.​​ВПР​​, и требуется извлечь​​для аргумента​
  • ​B1:B3​​ справиться со многими​ ищет по таблице​В появившимся диалоговом​ выше). Для нашего​ искомому значению, то ВПР() тут​ значение параметра​FALSE​ не читаемой. Думаю,​

​ будем усложнять его,​ в ячейку строку​​#N/A​​мы показывали пример​В нашем примере значения​твердит, что эта​ данные из столбца​row_num​

​содержатся значения New-York,​ сложными задачами, перед​ с данными и​ окне на поле​ примера: . Это​ не поможет. Такого​Интервальный_просмотр​(ЛОЖЬ). А значение,​​ что найдется мало​​ пока единственным рациональным​​ "#Н/Д". Это наилучшее​​(#Н/Д) или​​ формулы с функцией​​ в столбце​ функция не может​​B​​(номер_строки), который указывает​ Paris, London, тогда​ которыми функция​ на основе критериев​ категория, выберите из​ значит, что нужно​ рода задачи решены​можно задать ЛОЖЬ​

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

​ введённое в качестве​ желающих использовать формулы​ решением задачи не​ решение. В данном​#VALUE!​ВПР​​D​​ смотреть влево. Т.е.​​, то нужно задать​​ из какой строки​​ следующая формула возвратит​​ВПР​ запроса поиска, возвращает​ выпадающего списка: «Ссылки​ взять наименование материала​​ в разделе Ближайшее​​ или ИСТИНА или​Lookup_value​ с 4-мя уровнями​ станет использование функции​

​ случае "#Н/Д" не​(#ЗНАЧ!). Если Вы​​для поиска по​​упорядочены по возрастанию,​​ если просматриваемый столбец​​ значение​

​ ЧИСЛО. Там же можно​ вообще опустить). Значение​(Искомое_значение) должно существовать​ вложенности в своих​ВПР​

​ означает, что формула​ хотите заменить такое​ нескольким критериям. Однако,​ поэтому мы используем​

​ Т.е. получается простая​3​В нескольких недавних статьях​

• ​ определенного столбца. Очень​​ потом ниже укажите​​ посмотреть его в​ найти решение задачи​​ параметра ​​ в базе данных.​ проектах. Должен же​​. Первоначальный сценарий нашей​​ введена неправильно (за​​ сообщение на что-то​​ существенным ограничением такого​ тип сопоставления​ левым в диапазоне​
• ​для аргумента​​ формула:​​, поскольку «London» –​​ мы приложили все​​ часто необходимо в​​ на функцию.​​ «Новом прайсе» в​ о поиске ближайшего​номер_столбца​ Другими словами, идёт​ существовать более простой​

  
  

  ​ более понятное, то​ решения была необходимость​1​​ поиска, то нет​​col_index_num​​=INDEX($D$2:$D$10,3)​​ это третий элемент​ усилия, чтобы разъяснить​ запросе поиска использовать​Заполняем аргументы функции.​​ столбце А. Затем​​ при несортированном ключевом​нужно задать =2,​ поиск точного совпадения.​

​ способ?!​ так: если продавец​

​ номера). Это означает,​ можете вставить формулу​ добавлять вспомогательный столбец.​​. Формула​​ шансов получить от​(номер_столбца) функции​​=ИНДЕКС($D$2:$D$10;3)​​ в списке.​​ начинающим пользователям основы​​ сразу несколько условий.​​В поле «Исходное значение»​​ взять данные из​ столбце.​

​ т.к. номер столбца​В примере, что мы​И такой способ есть!​​ за год делает​​ что номер 2345678​ с​ Хорошая новость: формула​ИНДЕКС​​ВПР​​ВПР​​Формула говорит примерно следующее:​​=MATCH("London",B1:B3,0)​

​ вводим ссылку на​ второго столбца нового​Примечание​ Наименование равен 2​ рассмотрели в данной​ Нам поможет функция​​ объём продаж более​​ не был найден,​​ИНДЕКС​​ИНДЕКС​/​желаемый результат.​, вот так:​​ ищи в ячейках​​=ПОИСКПОЗ("London";B1:B3;0)​​ВПР​​ данная функция не​ ячейку под наименованием​ прайса (новую цену)​​. Для удобства, строка​​ (Ключевой столбец всегда​

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

​ статье, нет необходимости​ВПР​ $30000, то его​ потому что вы​​и​​/​​ПОИСКПО​​Функции​​=VLOOKUP("lookup value",A1:C10,2)​​ от​Функция​и показать примеры​​ может обработать более​​ товара второй таблицы​ и подставить их​ таблицы, содержащая найденное​ номер 1). ​ получать точное соответствие.​.​ комиссионные составляют 30%.​​ искали значение 2345768.​​ПОИСКПОЗ​​ПОИСКПОЗ​​З​​ПОИСКПОЗ​​=ВПР("lookup value";A1:C10;2)​D2​MATCH​ более сложных формул​

​ одного условия. Поэтому​ D3. В поле​​ в ячейку С2.​​ решение, выделена Условным форматированием.​​Для вывода Цены используйте​​ Это тот самый​Давайте немного изменим дизайн​ В противном случае,​​В этом примере показано,​​в функцию​​может искать по​​возвращает «Moscow», поскольку​​и​​Если позднее Вы вставите​

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

​до​​(ПОИСКПОЗ) имеет вот​ для продвинутых пользователей.​​ следует использовать весьма​​ «Таблица» вводим диапазон​Данные, представленные таким образом,​ Это можно сделать​ аналогичную формулу =ВПР($E9;$A$13:$C$19;3;ЛОЖЬ) (значение параметра ​ случай, когда функция​ нашей таблицы. Мы​ комиссия составляет, лишь​ как работает функция.​​ЕСЛИОШИБКА​​ значениям в двух​​ величина населения города​​ИНДЕКС​ новый столбец между​D10​ такой синтаксис:​ Теперь мы попытаемся,​ простую формулу, которая​ всех значений первой​ можно сопоставлять. Находить​ с помощью формулы =ПОИСКПОЗ($A$7;$A$11:$A$17;1)=СТРОКА()-СТРОКА($A$10).​

​номер_столбца​ВПР​​ сохраним все те​​ 20%. Оформим это​​ Если ввести значение​.​ столбцах, без необходимости​ Москва – ближайшее​в Excel гораздо​ столбцами​​и извлеки значение​​MATCH(lookup_value,lookup_array,[match_type])​ если не отговорить​ позволит расширить возможности​ таблицы A2:B7. В​​ численную и процентную​​Примечание​нужно задать =3). ​должна переключиться в​ же поля и​ в виде таблицы:​

​ в ячейку B2​Синтаксис функции​​ создания вспомогательного столбца!​​ меньшее к среднему​ более гибкие, и​​A​​ из третьей строки,​ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])​​ Вас от использования​​ функции ВПР по​​ поле «Номер столбца»​​ разницу.​​: Если в ключевом​​Ключевой столбец в нашем​

​Продавец вводит данные о​ (первый аргумент), функция​ЕСЛИОШИБКА​​Предположим, у нас есть​​ значению (12 269​​ им все-равно, где​​и​ то есть из​​lookup_value​​ВПР​​ нескольким столбцам одновременно.​​ вводим значение 2,​До сих пор мы​ столбце имеется значение​

​ случае содержит числа​​ чтобы вернуть нам​​ их по-новому, в​​ своих продажах в​​ ВПР выполняет поиск​очень прост:​ список заказов, и​ 006).​ находится столбец со​B​ ячейки​(искомое_значение) – это​, то хотя бы​Для наглядности разберем формулу​ так как во​ предлагали для анализа​ совпадающее с искомым,​ и должен гарантировано​ нужный результат.​ более компактном виде:​ ячейку B1, а​​ в ячейках C2:E7​​IFERROR(value,value_if_error)​

​ мы хотим найти​Эта формула эквивалентна двумерному​​ значением, которое нужно​​, то значение аргумента​​D4​ число или текст,​ показать альтернативные способы​ ВПР с примером​ втором столбце у​​ только одно условие​​ то функция с​ содержать искомое значение​Например:​Прервитесь на минутку и​ формула в ячейке​​ (второй аргумент) и​​ЕСЛИОШИБКА(значение;значение_если_ошибка)​​ сумму по двум​​ поиску​

​ извлечь. Для примера,​ придется изменить с​​, так как счёт​​ который Вы ищите.​ реализации вертикального поиска​​ нескольких условий. Для​​ нас находиться цена,​​ – наименование материала.​​ параметром ​ (условие задачи). Если первый​​Мы хотим определить,​​ убедитесь, что новая​

​Где аргумент​ критериям –​ВПР​​ снова вернёмся к​​2​ начинается со второй​ Аргумент может быть​ в Excel.​​ примера будем использовать​​ которую мы хотим​​ На практике же​​Интервальный_просмотр​

​ таблица​ ставку комиссионного вознаграждения,​​ приблизительное совпадение из​value​имя покупателя​и позволяет найти​ таблице со столицами​на​ строки.​ значением, в том​Зачем нам это? –​ схематический отчет по​ получить при поиске​ нередко требуется сравнить​ =ЛОЖЬ вернет первое найденное​ артикул​​ в расчёте комиссионных​​Rate Table​​ на которое продавец​​ третьего столбца в​​(значение) – это​​(Customer) и​ значение на пересечении​ государств и населением.​​3​​Вот такой результат получится​

​ числе логическим, или​​ спросите Вы. Да,​​ выручке торговых представителей​ товара. И нажимаем​ несколько диапазонов с​ значение, равное искомому,​, ​​ для продавца с​​включает те же​ может рассчитывать. В​ диапазоне — столбца​ значение, проверяемое на​продукт​​ определённой строки и​​ На этот раз​, иначе формула возвратит​ в Excel:​ ссылкой на ячейку.​ потому что​ за квартал:​

​ ОК.​ данными и выбрать​​ а с параметром​​то функция возвращает значение​​ объёмом продаж $34988.​​ данные, что и​ свою очередь, полученная​ E (третий аргумент).​ предмет наличия ошибки​

ИНДЕКС и ПОИСКПОЗ – примеры формул

​(Product). Дело усложняется​ столбца.​ запишем формулу​​ результат из только​​Важно! Количество строк и​​lookup_array​​ВПР​В данном отчете необходимо​Теперь под заголовком столбца​ значение по 2,​ =ИСТИНА - последнее​

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

​ ошибки​​ Функция​​ предыдущая таблица пороговых​ ставка используется в​В данном примере четвертый​ (в нашем случае​ тем, что один​В этом примере формула​ПОИСКПОЗ​ что вставленного столбца.​​ столбцов в массиве,​​(просматриваемый_массив) – диапазон​

​– это не​​ найти показатель выручки​​ второй таблицы «Товар»​​ 3-м и т.д.​​ (см. картинку ниже).​ #Н/Д. ​ВПР​ значений.​ ячейке B3, чтобы​ аргумент оставлен пустым,​ – результат формулы​ покупатель может купить​ИНДЕКС​/​Используя​​ который использует функция​​ ячеек, в котором​​ единственная функция поиска​​ для определенного торгового​ введите наименования того​ критериям.​Если столбец, по которому​

​Это может произойти, например,​возвращает нам значение​Основная идея состоит в​ рассчитать общую сумму​

​ сразу несколько разных​/​ИНДЕКС​ПОИСКПОЗ​

• ​INDEX​​ происходит поиск.​​ в Excel, и​ представителя в определенную​ товара по котором​

  
  

• ​ при опечатке при​ 30%, что является​​ том, чтобы использовать​​ комиссионных, которую продавец​ приблизительное совпадение.​/​ продуктов, и имена​​ПОИСКПОЗ​​, которая покажет, какое​
• ​/​(ИНДЕКС), должно соответствовать​

  
  

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

Вычисления при помощи ИНДЕКС и ПОИСКПОЗ в Excel (СРЗНАЧ, МАКС, МИН)

​будет очень похожа​ место по населению​​ИНДЕКС​​ значениям аргументов​​(тип_сопоставления) – этот​​ могут помешать Вам​ поиска наш запрос​ его цену. И​ по какой цене​ ВПР() не поможет.​ с вводом искомого​ почему же формула​

​ВПР​​ перемножение ячеек B1​​ несложно будет освоить​); а аргумент​​ на листе​​ на формулы, которые​ занимает столица России​​, Вы можете удалять​​row_num​

​ должен содержать 2​

​ нажмите Enter.​​ привезли гофрированный картон​​ В этом случае​ артикула можно использовать Выпадающий​​ выбрала строку, содержащую​​для определения нужной​ и B2).​​ и функцию ГПР.​​value_if_error​

​ (Москва).​

​ или добавлять столбцы​​(номер_строки) и​​ПОИСКПОЗ​ во многих ситуациях.​​ условия:​​Функция позволяет нам быстро​ от ОАО «Восток».​ нужно использовать функции​ список (см. ячейку ​​ именно 30%, а​​ тарифной ставки по​

​(значение_если_ошибка) – это​

О чём нужно помнить, используя функцию СРЗНАЧ вместе с ИНДЕКС и ПОИСКПОЗ

​расположены в произвольном​​ в этом уроке,​​Как видно на рисунке​​ к исследуемому диапазону,​​column_num​​, хотите ли Вы​​ С другой стороны,​– Дата сдачи выручки​​ находить данные и​​ Нужно задать два​ ПОИСКПОЗ()+ИНДЕКС() или ПРОСМОТР().​​Е9​​ не 20% или​​ таблице​​ заключена в ячейке​ те же аргументы,​ значение, которое нужно​ порядке.​ с одним лишь​ ниже, формула отлично​ не искажая результат,​(номер_столбца) функции​​ найти точное или​​ функции​ в кассу.​

• ​ получать по ним​​ условия для поиска​​Функция ВПР в Excel​).​ 40%? Что понимается​Rate Table​ B2 – это​ но выполняет поиск​
• ​ возвратить, если формула​​Вот такая формула​​ отличием. Угадайте каким?​ справляется с этой​ так как определен​MATCH​ приблизительное совпадение:​ИНДЕКС​

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

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

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

​ столбцов.​​Например, Вы можете вставить​​/​​ функции​​=INDEX($A$2:$A$10,MATCH("Russia",$B$2:$B$10,0))​ нужное значение. Действительно,​ формулы будет ошибочным.​или​ПОИСКПОЗ​ будем использовать функцию​

​ Это похоже на​Дело осложняется тем, что​​ в соответствующие ячейки​​ не должен содержать​Когда аргумент​

​Если вы не хотите​ формулу из предыдущего​ПОИСКПОЗ​

​INDEX​=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ("Russia";$B$2:$B$10;0))​ это большое преимущество,​Стоп, стоп… почему мы​не указан​​– более гибкие​​ ВПР по нескольким​​ работу с базами​​ от одного поставщика​ второй. Ее английское​ повторов (в этом​​Range_lookup​​ может продать товаров​ функцию Excel под​

​ данных. Когда к​ поступает несколько наименований.​ наименование – VLOOKUP.​ смысл артикула, однозначно​​(Интервальный_просмотр) имеет значение​​ на такую сумму,​​ названием​​ крайнем левом столбце,​

​ЕСЛИОШИБКА​{=INDEX('Lookup table'!$A$2:$C$13,MATCH(1,(A2='Lookup table'!$A$2:$A$13)*​ три аргумента:​ должно возникать проблем​ приходится с большими​ использовать функцию​ значение, меньшее или​ особенностей, которые делают​

​ следующую формулу:​ базе создается запрос,​Добавляем в таблицу крайний​Очень удобная и часто​ определяющего товар). В​TRUE​ которая не равна​

​IF​ можно использовать сочетание​​вот таким образом:​​(B2='Lookup table'!$B$2:$B$13),0),3)}​INDEX(array,row_num,[column_num])​ с пониманием, как​​ объёмами данных. Вы​​VLOOKUP​

• ​ равное искомому. Просматриваемый​​ их более привлекательными,​В ячейке С1 введите​​ а в ответ​​ левый столбец (важно!),​ используемая. Т.к. сопоставить​​ противном случае будет​​(ИСТИНА) или опущен,​ ни одному из​​(ЕСЛИ). Для тех​​ функций ИНДЕКС и​=IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)),​

  
  

  ​ работает эта формула:​​ можете добавлять и​​(ВПР)? Есть ли​ массив должен быть​ по сравнению с​​ первое значение для​​ выводятся результаты, которые​

• ​ объединив «Поставщиков» и​​ вручную диапазоны с​ выведено самое верхнее​​ функция​​ пяти имеющихся в​​ читателей, кто не​​ ПОИСКПОЗ. Формула, использующая​"Совпадений не найдено.​​(B2='Lookup table'!$B$2:$B$13);0);3)}​​И я поздравляю тех​

  
  

  ​ смысл тратить время,​​ упорядочен по возрастанию,​​ВПР​ первого критерия поискового​

​ являются ответом на​ «Материалы».​​ десятками тысяч наименований​​ значение.​

​ знаком с этой​​ эти функции вместе,​​ Попробуйте еще раз!")​Эта формула сложнее других,​ из Вас, кто​MATCH​

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

Поиск по нескольким критериям с ИНДЕКС и ПОИСКПОЗ

​=ЕСЛИОШИБКА(ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($G$2;$B$1:$B$11;0);ПОИСКПОЗ($G$3;$A$1:$E$1;0));​​ которые мы обсуждали​​ догадался!​(ПОИСКПОЗ), которая находит​​ что нужно будет​​ лабиринтах​ меньшего к большему.​Базовая информация об ИНДЕКС​ 22.03.2017.​​ искомые критерии запроса:​​Допустим, на склад предприятия​​ ключевой столбец лучше​​ и выбирает наибольшее​​ мог продать на​ она работает:​ с функцией ВПР,​"Совпадений не найдено.​

​ ранее, но вооруженные​Начнём с того, что​ положение «Russia» в​ исправлять каждую используемую​ПОИСКПОЗ​​0​​ и ПОИСКПОЗ​​В ячейку C2 введите​​Немного усложним задание, изменив​Теперь ставим курсор в​ по производству тары​ предварительно отсортировать (это также​ значение, которое не​ сумму $34988, а​IF(condition, value if true,​​ но она открывает​​ Попробуйте еще раз!")​ знанием функций​

​ запишем шаблон формулы.​​ списке:​​ функцию​​и​​– находит первое​

​ и упаковки поступили​ поможет сделать Выпадающий​ превышает искомое.​ такой суммы нет.​​ value if false)​​ больше возможностей. Поэтому​​И теперь, если кто-нибудь​​ИНДЕКС​ Для этого возьмём​=MATCH("Russia",$B$2:$B$10,0))​​ВПР​​ИНДЕКС​ значение, равное искомому.​

​ объем данных в​ задаем аргументы для​ материалы в определенном​​ список нагляднее). Кроме​​Важный момент:​ Давайте посмотрим, как​ЕСЛИ(условие; значение если ИСТИНА;​ некоторые пользователи предпочитают​ введет ошибочное значение,​и​ уже знакомую нам​

• ​=ПОИСКПОЗ("Russia";$B$2:$B$10;0))​.​​?​​ Для комбинации​​Преимущества ИНДЕКС и ПОИСКПОЗ​​ значение будет использоваться​ таблице. Расширьте объем​ функции: . Excel​ количестве.​​ того, в случае​​Чтобы эта схема​
• ​ функция​ значение если ЛОЖЬ)​​ применять сочетание функций​​ формула выдаст вот​ПОИСКПОЗ​​ формулу​​Далее, задаём диапазон для​
• ​3. Нет ограничения на​=VLOOKUP("Japan",$B$2:$D$2,3)​ИНДЕКС​​ перед ВПР​​ в качестве второго​
• ​ данных первой таблицы,​ находит нужную цену.​Стоимость материалов – в​ несортированного списка, ВПР() с​ работала, первый столбец​ВПР​Условие​​ ИНДЕКС и ПОИСКПОЗ,​​ такой результат:​Вы одолеете ее.​ИНДЕКС​ функции​ размер искомого значения.​​=ВПР("Japan";$B$2:$D$2;3)​​/​

​ИНДЕКС и ПОИСКПОЗ –​ аргумента поискового запроса.​​ добавив столбцы: «январь»,​​Рассмотрим формулу детально:​ прайс-листе. Это отдельная​​ параметром​​ таблицы должен быть​сможет справиться с​– это аргумент​

​ а не функцию​​Если Вы предпочитаете в​ Самая сложная часть​/​INDEX​​Используя​​В данном случае –​ПОИСКПОЗ​ примеры формул​В ячейке C3 мы​ «февраль», «март». Там​Что ищем.​ таблица.​Интервальный_просмотр​ отсортирован в порядке​​ такой ситуацией.​​ функции, который принимает​ ВПР.​​ случае ошибки оставить​​ – это функция​

​ПОИСКПОЗ​(ИНДЕКС), из которого​ВПР​ смысла нет! Цель​всегда нужно точное​Как находить значения, которые​ будем получать результат​ запишем суммы продаж​Где ищем.​Необходимо узнать стоимость материалов,​ИСТИНА (или опущен)​ возрастания.​​Выбираем ячейку B2 (место,​​ значение либо​

​В данном примере представлен​ ячейку пустой, то​ПОИСКПОЗ​и добавим в​

ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА в Excel

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

​ Для этого нужно​​В файле примера лист Справочник​​ командой сайта office-guru.ru​

​ котором искомое значение​​ («»), как значение​​ объяснить первой.​ функцию​ это​ значения в 255​ Вы могли понять,​​ПОИСКПОЗ​​ и ПОИСКПОЗ​​ формулу:​​ рисунке:​​ нас сделаны в​​ подставит цену из​ также рассмотрены альтернативные​Источник: http://www.howtogeek.com/howto/14455/vlookup-in-excel-part-2-using-vlookup-without-a-database/​ и находим​

​FALSE​ (Воронеж) не находится​ второго аргумента функции​​MATCH(1,(A2='Lookup table'!$A$2:$A$13),0)*(B2='Lookup table'!$B$2:$B$13)​​ПОИСКПОЗ​

​Как видите вторую таблицу​ виде раскрывающегося списка.​ второй таблицы в​ формулы (получим тот​

​Перевел: Антон Андронов​VLOOKUP​(ЛОЖЬ). В примере,​ в крайнем левом​ЕСЛИОШИБКА​ПОИСКПОЗ(1;(A2='Lookup table'!$A$2:$A$13);0)*(B2='Lookup table'!$B$2:$B$13)​​, которая будет возвращать​​.​

​0​ и столбцу​ подтверждения нажмите комбинацию​ так же нужно​ В нашем примере​ первую. И посредством​ же результат) с​Автор: Антон Андронов​(ВПР) в библиотеке​ приведённом выше, выражение​ столбце. Поэтому мы​. Вот так:​В формуле, показанной выше,​ номер столбца.​Затем соединяем обе части​

​#VALUE!​и​
​.​
​Поиск по нескольким критериям​

​ горячих клавиш CTRL+SHIFT+Enter,​

office-guru.ru

Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ

​ немного изменить, чтобы​ – «Материалы». Необходимо​ обычного умножения мы​ использованием функций ИНДЕКС(),​Функция ВПР(), английский вариант​ функций Excel:​ B1​ не можем использовать​

​IFERROR(INDEX(массив,MATCH(искомое_значение,просматриваемый_массив,0),"")​ искомое значение –​

​=INDEX(Ваша таблица,(MATCH(значение для вертикального​

​ и получаем формулу:​

• ​(#ЗНАЧ!). Итак, если​ИНДЕКС​-1​ИНДЕКС и ПОИСКПОЗ в​ так как формула​ не потерять суть​ настроить функцию так,​ найдем искомое.​

• ​ ПОИСКПОЗ() и ПРОСМОТР(). Если​ VLOOKUP(), ищет значение​Formulas​Правда ли, что B1​

  ​ функцию ВПР. Для​​ЕСЛИОШИБКА(ИНДЕКС(массив;ПОИСКПОЗ(искомое_значение;просматриваемый_массив;0);"")​ это​ поиска,столбец, в котором​=INDEX($A$2:$A$10;MATCH("Russia";$B$2:$B$10;0))​ таблица содержит длинные​работают в паре.​

• ​– находит наименьшее​ сочетании с ЕСЛИОШИБКА​ должна быть выполнена​ задачи.​ чтобы при выборе​

​Алгоритм действий:​ ключевой столбец (столбец​ в первом (в​(Формулы) >​ меньше B5?​ поиска значения "Воронеж"​Надеюсь, что хотя бы​​1​​ искать,0)),(MATCH(значение для горизонтального​=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ("Russia";$B$2:$B$10;0))​ строки, единственное действующее​ Последующие примеры покажут​ значение, большее или​Так как задача этого​​ в массиве.​​Теперь нам нужно сделать​​ наименования появлялась цена.​Приведем первую таблицу в​ с артикулами) не​

​ самом левом) столбце​Function Library​Или можно сказать по-другому:​ в диапазоне B1:B11​ одна формула, описанная​, а массив поиска​ поиска,строка в которой​Подсказка:​ решение – это​ Вам истинную мощь​ равное искомому значению.​​ учебника – показать​​Результат поиска в таблице​​ выборку данных с​Сначала сделаем раскрывающийся список:​ нужный нам вид.​ является самым левым​ таблицы и возвращает​(Библиотека Функций) >​Правда ли, что общая​ будет использоваться функция​ в этом учебнике,​

​ – это результат​ искать,0))​Правильным решением будет​ использовать​ связки​ Просматриваемый массив должен​ возможности функций​ по двум условиям:​ помощью функции ВПР​Ставим курсор в ячейку​ Добавим столбцы «Цена»​ в таблице, то​ значение из той​Lookup & Reference​ сумма продаж за​ ПОИСКПОЗ. Оно найдено​ показалась Вам полезной.​ умножения. Хорошо, что​

​=ИНДЕКС(Ваша таблица,(MATCH(значение для вертикального​ всегда использовать абсолютные​ИНДЕКС​ИНДЕКС​ быть упорядочен по​ИНДЕКС​Найдена сумма выручки конкретного​ отдельно по товару​ Е8, где и​ и «Стоимость/Сумма». Установим​ функция ВПР() не​ же строки, но​(Ссылки и массивы).​

​ год меньше порогового​ в строке 4.​ Если Вы сталкивались​ же мы должны​

Использование функции ГПР

​ поиска,столбец, в котором​ ссылки для​/​и​ убыванию, то есть​и​ торгового представителя на​ и просуммировать продажи​

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

​ будет этот список.​ денежный формат для​ применима. В этом​ другого столбца таблицы.​Появляется диалоговое окно​ значения?​ Затем функция ИНДЕКС​ с другими задачами​ перемножить и почему?​ искать,0)),(MATCH(значение для горизонтального​ИНДЕКС​ПОИСКПОЗ​ПОИСКПОЗ​ от большего к​ПОИСКПОЗ​ конкретную дату.​

​ за первый квартал.​Заходим на вкладку «Данные».​ новых ячеек.​ случае нужно использовать​Функция ВПР() является одной​Function Arguments​Если на этот вопрос​ использует это значение​ поиска, для которых​ Давайте разберем все​ поиска,строка в которой​и​.​, которая легко справляется​ меньшему.​для реализации вертикального​​ Для этого переходим​ Меню «Проверка данных».​Выделяем первую ячейку в​ альтернативные формулы. Связка​ из наиболее используемых​

Еще о функциях поиска

• ​(Аргументы функции). По​ мы отвечаем​

• ​ в качестве аргумента​ не смогли найти​

• ​ по порядку:​ искать,0))​

​ПОИСКПОЗ​

support.office.com

Использование функции ВПР в Excel: неточное соответствие

​Предположим, Вы используете вот​ с многими сложными​На первый взгляд, польза​ поиска в Excel,​​Разбор принципа действия формулы​​ в ячейку H3​Выбираем тип данных –​ столбце «Цена». В​ функций  ИНДЕКС(), ПОИСКПОЗ() образуют так называемый​ в EXCEL, поэтому​ очереди заполняем значения​ДА​ поиска и находит​ подходящее решение среди​​Берем первое значение в​​Обратите внимание, что для​, чтобы диапазоны поиска​ такую формулу с​ ситуациями, когда​ от функции​ мы не будем​ для функции ВПР​ и после вызова​​ «Список». Источник –​​ нашем примере –​

​ "правый ВПР": =ИНДЕКС(B13:B19;ПОИСКПОЗ($E$9;$A$13:$A$19;0);1)​ рассмотрим ее подробно. ​ аргументов, начиная с​(ИСТИНА), то функция​​ численность населения Воронежа​​ информации в этом​ столбце​ двумерного поиска нужно​ не сбились при​ВПР​ВПР​

​ПОИСКПОЗ​ задерживаться на их​​ с несколькими условиями:​​ функции заполняем ее​ диапазон с наименованиями​ D2. Вызываем «Мастер​В файле примера лист Справочник показано, что​В этой статье выбран​Lookup_value​ возвращает​ в четвертом столбце​ уроке, смело опишите​A​​ указать всю таблицу​​ копировании формулы в​, которая ищет в​оказывается в тупике.​вызывает сомнение. Кому​​ синтаксисе и применении.​​Первым аргументом функции =ВПР()​ аргументы следующим образом:​ материалов.​ функций» с помощью​​ формулы применимы и​​ нестандартный подход: акцент​(Искомое_значение). В данном​value if true​ (столбец D). Использованная​ свою проблему в​(Customer) на листе​ в аргументе​ другие ячейки.​

• ​ ячейках от​Решая, какую формулу использовать​
• ​ нужно знать положение​
• ​Приведём здесь необходимый минимум​ является первым условием​
• ​Исходное значение: G3.​

Пример из жизни. Ставим задачу

​Когда нажмем ОК –​ кнопки «fx» (в​ для ключевых столбцов​ сделан не на​ примере это общая​(значение если ИСТИНА).​ формула показана в​ комментариях, и мы​Main table​array​Вы можете вкладывать другие​B5​​ для вертикального поиска,​​ элемента в диапазоне?​ для понимания сути,​ для поиска значения​Таблица: A2:E7. Диапазон нашей​ сформируется выпадающий список.​ начале строки формул)​ содержащих текстовые значения,​ саму функцию, а​ сумма продаж из​ В нашем случае​ ячейке A14.​

​ все вместе постараемся​и сравниваем его​(массив) функции​ функции Excel в​до​ большинство гуру Excel​ Мы хотим знать​ а затем разберём​ по таблице отчета​ таблицы расширен.​Теперь нужно сделать так,​ или нажав комбинацию​ т.к. артикул часто​ на те задачи,​ ячейки B1. Ставим​ это будет значение​

​Краткий справочник: обзор функции​ решить её.​ со всеми именами​INDEX​ИНДЕКС​D10​ считают, что​ значение этого элемента!​​ подробно примеры формул,​​ выручки торговых представителей.​Номер столбца: {3;4;5}. Нам​ чтобы при выборе​ горячих клавиш SHIFT+F3.​ бывает текстовым значением.​

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

​ определенного материала в​ В категории «Ссылки​

​ Также задача решена​

​ с ее помощью.​Lookup_value​ ставка комиссионных при​Функции ссылки и поиска​

​ командой сайта office-guru.ru​ на листе​​А теперь давайте испытаем​​ПОИСКПОЗ​ ячейке​​/​​ положение искомого значения​ использования​ находится виртуальная таблица​ функции обращаться одновременно​ графе цена появлялась​ и массивы» находим​ для несортированного ключевого​ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр)​(Искомое_значение) и выбираем​​ общем объёме продаж​​ (справка)​​Источник: https://www.ablebits.com/office-addins-blog/2014/08/13/excel-index-match-function-vlookup/​​Lookup table​ этот шаблон на​, например, чтобы найти​A2​ПОИСКПОЗ​ (т.е. номер строки​ИНДЕКС​

​ создана в результате​ к нескольким столбцам,​ соответствующая цифра. Ставим​ функцию ВПР и​ столбца.​Искомое_значение​ ячейку B1.​ ниже порогового значения.​Использование аргумента массива таблицы​Перевел: Антон Андронов​

​(A2:A13).​ практике. Ниже Вы​

Усложняем задачу

​ минимальное, максимальное или​:​намного лучше, чем​ и/или столбца) –​и​ массивного вычисления логической​ поэтому значение данного​

​ курсор в ячейку​ жмем ОК. Данную​Примечание​- это значение,​Далее нужно указать функции​ Если мы отвечаем​ в функции ВПР​Автор: Антон Андронов​Если совпадение найдено, уравнение​​ видите список самых​​ ближайшее к среднему​=VLOOKUP(A2,B5:D10,3,FALSE)​ВПР​​ это как раз​​ПОИСКПОЗ​ функцией =ЕСЛИ(). Каждая​ аргумента будет взято​ Е9 (где должна​ функцию можно вызвать​. Для удобства, строка​ которое Вы пытаетесь​ВПР​ на вопрос​

​К началу страницы​Для поиска значения в​ возвращает​ населённых стран мира.​ значение. Вот несколько​=ВПР(A2;B5:D10;3;ЛОЖЬ)​. Однако, многие пользователи​ то, что мы​вместо​​ фамилия в диапазоне​​ в массив фигурными​ будет появляться цена).​

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

​ ячеек B6:B12 сравнивается​ скобками. А номера​Открываем «Мастер функций» и​ «Формулы» и выбрать​ решение, выделена Условным форматированием.​ с данными.​ В нашем примере​(ЛОЖЬ), тогда возвращается​ одной из самых​ использовать функцию просмотра.​(ИСТИНА), а если​ узнать население США​

​ к таблице из​ если значение в​​ к использованию​​ аргументов​

Применяем функцию ВПР к решению задачи

​.​ со значением в​ столбцов следует перечислять​ выбираем ВПР.​ из выпадающего списка​ (см. статью Выделение​Искомое_значение ​

​ это таблица​value if false​ полезных функций Excel​​ Функция ВПР часто​​ нет –​ в 2015 году.​ предыдущего примера:​ ячейке​

​ВПР​row_num​Функция​​ ячейке C2. Таким​​ через точку с​Первый аргумент – «Искомое​ «Ссылки и массивы».​​ строк таблицы в​​может быть числом или​Rate Table​(значение если ЛОЖЬ).​ под названием​ используется, но можно​0​Хорошо, давайте запишем формулу.​1.​A2​, т.к. эта функция​(номер_строки) и/или​INDEX​ образом в памяти​ запятой.​ значение» - ячейка​​Откроется окно с аргументами​​ MS EXCEL в​ текстом, но чаще​

Вставляем функцию ВПР

​. Ставим курсор в​ В нашем случае​ВПР​ задействовать и функции​​(ЛОЖЬ).​​ Когда мне нужно​MAX​​длиннее 255 символов.​​ гораздо проще. Так​​column_num​​(ИНДЕКС) в Excel​​ создается условный массив​​Интервальный просмотр: ЛОЖЬ.​

​ с выпадающим списком.​​ функции. В поле​​ зависимости от условия​ всего ищут именно​ поле​​ это значение ячейки​​и показали, как​ ГПР, ИНДЕКС и​Далее, мы делаем то​ создать сложную формулу​(МАКС). Формула находит​​ Вместо неё Вам​​ происходит, потому что​(номер_столбца) функции​

​ возвращает значение из​​ данных с элементами​​Чтобы значения в выбранных​ Таблица – диапазон​ «Искомое значение» -​​ в ячейке).​​ число. Искомое значение должно​Table_array​​ B7, т.е. ставка​​ она может быть​ ПОИСКПОЗ.​​ же самое для​​ в Excel с​

​ максимум в столбце​ нужно использовать аналогичную​ очень немногие люди​INDEX​ массива по заданным​ значений ИСТИНА и​ столбцах суммировались, тогда​ с названиями материалов​ диапазон данных первого​​Примечание​​ находиться в первом​(Таблица) и выделяем​

​ комиссионных при общем​ использована для извлечения​​Общий вид функции ВПР​​ значений столбца​

​ вложенными функциями, то​​D​ формулу​ до конца понимают​(ИНДЕКС). Как Вы​​ номерам строки и​​ ЛОЖЬ.​ всю функцию нужно​​ и ценами. Столбец,​​ столбца из таблицы​. Никогда не используйте​​ (самом левом) столбце​​ всю таблицу​ объёме продаж выше​ нужной информации из​ и ее аргументов:​​B​​ я сначала каждую​и возвращает значение​ИНДЕКС​​ все преимущества перехода​​ помните, функция​ столбца. Функция имеет​Потом благодаря формуле, в​

​ поместить внутрь функции​ соответственно, 2. Функция​​ с количеством поступивших​​ ВПР() с параметром ​​ диапазона ячеек, указанного​​Rate Table​ порогового значения.​ базы данных в​=ВПР(;;;)​​(Product).​​ вложенную записываю отдельно.​ из столбца​

​/​ с​​ИНДЕКС​​ вот такой синтаксис:​ памяти программы каждый​ СУММ(). Вся формула​​ приобрела следующий вид:​​ материалов. Это те​

​Интервальный_просмотр​ в​, кроме заголовков.​Как Вы можете видеть,​ ячейку рабочего листа.​

Заключение

​Например, =ВПР(21500;C2:E7;3;ЛОЖЬ).​​Затем перемножаем полученные результаты​​Итак, начнём с двух​C​​ПОИСКПОЗ​​ВПР​​может возвратить значение,​​INDEX(array,row_num,[column_num])​ истинный элемент заменяется​​ в целом выглядит​​ .​ значения, которые Excel​ ИСТИНА (или опущен) если​таблице​

​Далее мы должны уточнить,​ если мы берём​ Мы также упомянули,​Первый аргумент (часть, необходимая​ (1 и 0).​ функций​​той же строки:​​:​на связку​ находящееся на пересечении​ИНДЕКС(массив;номер_строки;[номер_столбца])​

​ на 3-х элементный​​ следующим образом: =СУММ(ВПР(G3;A3:E6;{3;4;5};ЛОЖЬ)).​Нажимаем ВВОД и наслаждаемся​ должен найти во​ ключевой столбец не​.​ данные из какого​​ общую сумму продаж​​ что существует два​ для работы функции)​ Только если совпадения​ПОИСКПОЗ​=INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))​=INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))​ИНДЕКС​ заданных строки и​Каждый аргумент имеет очень​ набор данных:​

​После ввода данной формулы​​ результатом.​​ второй таблице.​​ отсортирован по возрастанию,​​Таблица -​ столбца необходимо извлечь​​ $20000, то получаем​​ варианта использования функции​ — это искомое​ найдены в обоих​, которые будут возвращать​

​=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МАКС($D$2:I$10);$D$2:D$10;0))​​=ИНДЕКС(D5:D10;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B5:B10=A2;0);0))​и​ столбца, но она​ простое объяснение:​элемент – Дата.​

​ следует нажать комбинацию​Изменяем материал – меняется​
​Следующий аргумент – «Таблица».​
​ т.к. результат формулы​

​ссылка на диапазон​

office-guru.ru

Функция ВПР() в MS EXCEL

​ с помощью нашей​ в ячейке B2​ВПР​ значение. Это может​ столбцах (т.е. оба​ номера строки и​Результат: Beijing​4. Более высокая скорость​

​ПОИСКПОЗ​ не может определить,​array​элемент – Фамилия.​

​ клавиш: CTRL+SHIFT+ENTER. Внимание!​ цена:​ Это наш прайс-лист.​ непредсказуем (если функция ВПР()​ ячеек. В левом​ формулы. Нас интересует​ ставку комиссионных 20%.​

Синтаксис функции

​и только один​

​ быть ссылка на​​ критерия истинны), Вы​ столбца для функции​2.​ работы.​​, а тратить время​​ какие именно строка​(массив) – это​элемент – Выручка.​ Если не нажать​Скачать пример функции ВПР​ Ставим курсор в​ находит значение, которое​ столбце таблицы ищется ​​ ставка комиссионных, которая​​ Если же мы​

​ из них имеет​​ ячейку, например B2,​ получите​ИНДЕКС​​MIN​​Если Вы работаете​ на изучение более​ и столбец нас​ диапазон ячеек, из​А каждый ложный элемент​ комбинацию этих клавиш​ в Excel​ поле аргумента. Переходим​ больше искомого, то​​Искомое_значение​​ находится во втором​ введём значение $40000,​​ дело с запросами​​ или значение, например​1​​:​​(МИН). Формула находит​​ с небольшими таблицами,​ сложной формулы никто​​ интересуют.​

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

​ к базе данных.​​ "кузьмина" или 21500.​​. Если оба критерия​ПОИСКПОЗ для столбца​ минимум в столбце​ то разница в​ не хочет.​Теперь, когда Вам известна​ значение.​ на 3-х элементный​ ошибочно. В Excel​​ в Excel с​​ ценами. Выделяем диапазон​ которое расположено на​ расположенных правее, выводится​ для аргумента​ изменится на 30%:​ В этой статье​

​Второй аргумент — это​ ложны, или выполняется​– мы ищем​D​

Задача1. Справочник товаров

​ быстродействии Excel будет,​Далее я попробую изложить​ базовая информация об​

​row_num​ набор пустых текстовых​ иногда приходиться выполнять​ функцией ВПР. Все​ с наименованием материалов​

​ строку выше его).​​ соответствующий результат (хотя,​Col_index_num​Таким образом работает наша​

​ Вы узнаете другой​ диапазон ячеек, который,​​ только один из​​ в столбце​и возвращает значение​ скорее всего, не​ главные преимущества использования​​ этих двух функциях,​​(номер_строки) – это​ значений (""). В​ функции в массиве​ происходит автоматически. В​ и ценами. Показываем,​

​Предположим, что нужно найти​ в принципе, можно​​(Номер_столбца) вводим значение​​ таблица.​

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

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

​ Все работает быстро​ должна сопоставить.​ цена равна или​ значение из левого​И, наконец, вводим последний​ Установим ещё одно​ВПР​Важно:​​0​​ диапазоне​той же строки:​

​ же Вы работаете​ИНДЕКС​ функции​ нужно извлечь значение.​ таблица, с которой​ CTRL+SHIFT+ENTER при вводе​ и качественно. Нужно​Чтобы Excel ссылался непосредственно​ наиболее близка к​ столбца (в этом​ аргумент —​ пороговое значение: если​в Excel.​ В функции ВПР столбец,​.​B2:B11​

​=INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))​ с большими таблицами,​в Excel, а​ПОИСКПОЗ​ Если не указан,​ уже будет работать​ функций. Тогда в​ только разобраться с​ на эти данные,​

​ искомой.​​ случае это будет​Range_lookup​ продавец зарабатывает более​Если Вы этого ещё​ содержащий искомое значение​Теперь понимаете, почему мы​, значение, которое указано​=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МИН($D$2:I$10);$D$2:D$10;0))​

​ которые содержат тысячи​​ Вы решите –​и​​ то обязательно требуется​​ функция ВПР. Она​ строке формул все​ этой функцией.​ ссылку нужно зафиксировать.​Чтобы использовать функцию ВПР()​ само​(Интервальный_просмотр).​ $40000, тогда ставка​ не сделали, то​ или ссылку на​

Задача2. Поиск ближайшего числа

​ задали​ в ячейке​Результат: Lima​ строк и сотни​ остаться с​

​ИНДЕКС​ аргумент​ игнорирует все пустые​ содержимое будет взято​

1. ​ВПР в Excel очень​ Выделяем значение поля​ для решения этой​искомое_значение​
2. ​Важно:​ комиссионных возрастает до​ обязательно прочтите прошлую​
3. ​ ячейку, должен быть​​1​​H2​3.​

​ формул поиска, Excel​

​ВПР​могут работать вместе.​column_num​ наборы данных элементов.​

​ в фигурные скобки​ удобный и часто​ «Таблица» и нажимаем​ задачи нужно выполнить​)). Часто левый столбец​именно в использовании​ 40%:​ статью о функции​ крайним левым столбцом​, как искомое значение?​(USA). Функция будет​AVERAGE​ будет работать значительно​или переключиться на​ПОИСКПОЗ​(номер_столбца).​ А непустые элементы​ «{}», что свидетельствует​ используемый инструмент для​ F4. Появляется значок​​ несколько условий:​

​ называется​ этого аргумента заключается​Вроде бы всё просто​ВПР​ в диапазоне.​ Правильно, чтобы функция​ выглядеть так:​(СРЗНАЧ). Формула вычисляет​ быстрее, при использовании​ИНДЕКС​определяет относительную позицию​column_num​ сопоставляются со значением​

​ о выполнении формулы​ работы с таблицами​ $.​Ключевой столбец, по которому​ключевым​ различие между двумя​ и понятно, но​, поскольку вся информация,​Третий аргумент — это​ПОИСКПОЗ​=MATCH($H$2,$B$1:$B$11,0)​

​ среднее в диапазоне​​ПОИСКПОЗ​/​ искомого значения в​(номер_столбца) – это​ ячейки C1, использованного​

​ в массиве.​​ как с базой​В поле аргумента «Номер​ должен производиться поиск,​. Если первый столбец​ способами применения функции​​ наша формула в​​ изложенная далее, предполагает,​ столбец в диапазоне​возвращала позицию только,​=ПОИСКПОЗ($H$2;$B$1:$B$11;0)​D2:D10​

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

excel2.ru

Функция ВПР в Excel для чайников и не только

​ столбца» ставим цифру​ должен быть самым​ не содержит ​ВПР​ ячейке B2 становится​ что Вы уже​

​ поиска ячеек, содержащий​ когда оба критерия​Результатом этой формулы будет​, затем находит ближайшее​ИНДЕКС​

Как пользоваться функцией ВПР в Excel

​.​ а​ массиве, из которого​ критерия поискового запроса​ G3 наименование товара,​

​ только. Данная функция​ «2». Здесь находятся​ левым в таблице;​

​искомое_значение​. При работе с​ заметно сложнее. Если​ знакомы с принципами,​ значение, которое нужно​ выполняются.​4​ к нему и​

​вместо​

1. ​1. Поиск справа налево.​ИНДЕКС​ нужно извлечь значение.​ (Дата). Одним словом,​ в ячейке H3​ проста в освоении​
2. ​ данные, которые нужно​Ключевой столбец должен быть​,​ базами данных аргумент​ Вы внимательно посмотрите​ описанными в первой​ найти.​Обратите внимание:​, поскольку «USA» –​ возвращает значение из​ВПР​Как известно любому​использует это число​ Если не указан,​ таблица в памяти​ получаем сумму продаж​ и очень функциональна​ «подтянуть» в первую​
3. ​ обязательно отсортирован по​то функция возвращает​Range_lookup​ на формулу, то​ статье.​Хотя четвертый аргумент не​В этом случае​ это 4-ый элемент​ столбца​. В целом, такая​
4. ​ грамотному пользователю Excel,​ (или числа) и​ то обязательно требуется​ проверена функцией ВПР​ в первом квартале​ при выполнении.​ таблицу. «Интервальный просмотр»​ возрастанию;​ значение ошибки​(Интервальный_просмотр) должен всегда​
5. ​ увидите, что третий​При работе с базами​ является обязательным, большинство​ необходимо использовать третий​ списка в столбце​C​ замена увеличивает скорость​
6. ​ВПР​ возвращает результат из​ аргумент​ с одним условием​ по данному товару.​Благодаря гармоничному сочетанию простоты​ - ЛОЖЬ. Т.к.​Значение параметра ​ #Н/Д.​ иметь значение​

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

​не может смотреть​ соответствующей ячейки.​row_num​

​ поиска. При положительном​Происходит сравнение двух таблиц​ и функциональности ВПР​ нам нужны точные,​Интервальный_просмотр​Номер_столбца​FALSE​IF​

1. ​ВПР​ ЛОЖЬ (или 0).​
2. ​ функции​(включая заголовок).​
3. ​=INDEX($C$2:$C$10,MATCH(AVERAGE($D$2:D$10),$D$2:D$10,1))​13%​ влево, а это​
4. ​Ещё не совсем понятно?​(номер_строки)​

​ результате сопоставления функция​ в Excel функцией​

Быстрое сравнение двух таблиц с помощью ВПР

​ а не приблизительные​ нужно задать ИСТИНА или​- номер столбца​(ЛОЖЬ), чтобы искать​(ЕСЛИ), превратился в​передаётся уникальный идентификатор,​

1. ​ Почему? Потому что​ИНДЕКС​
2. ​ПОИСКПОЗ для строки​=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(СРЗНАЧ($D$2:D$10);$D$2:D$10;1))​.​ значит, что искомое​ Представьте функции​Если указаны оба аргумента,​ возвращает значение элемента​ ВПР и как​ используют в процессе​ значения.​ вообще опустить.​Таблицы​ точное соответствие. В​ ещё одну полноценную​ который служит для​ в этом случае​

​. Он необходим, т.к.​– мы ищем​Результат: Moscow​Влияние​

Функция ВПР в Excel с несколькими условиями

​ значение должно обязательно​ИНДЕКС​ то функция​ из третьего столбца​ только определяется совпадение​ работы с электронными​Нажимаем ОК. А затем​Для вывода Наименования товара используйте формулу =ВПР($A7;$A$11:$B$17;2;ИСТИНА) ​, из которого нужно​ нашем же варианте​ функцию​

​ определения информации, которую​

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

​ (выручка) условной таблицы.​ запрашиваемых данных, сразу​ таблицами. Но стоит​

1. ​ «размножаем» функцию по​Для вывода найденной цены (она​ выводить результат. Самый​ использования функции​
2. ​IF​ мы хотим найти​
3. ​точное совпадение​ мы задаем всю​H3​СРЗНАЧ​на производительность Excel​

​ левом столбце исследуемого​

1. ​ПОИСКПОЗ​
2. ​возвращает значение из​
3. ​ Это происходит потому,​

Функция ВПР и выпадающий список

​ подставляется их значения​ отметить, что у​ всему столбцу: цепляем​ не обязательно будет​ левый столбец (ключевой)​ВПР​(ЕСЛИ). Такая конструкция​ (например, код товара​

​. Можно ввести аргумент​

1. ​ таблицу и должны​(2015) в строке​в комбинации с​
2. ​ особенно заметно, если​ диапазона. В случае​
3. ​в таком виде:​ ячейки, находящейся на​ что в третьем​ для суммирования функцией​
4. ​ данной функции достаточно​ мышью правый нижний​

​ совпадать с заданной) используйте​ имеет номер 1​, мы должны оставить​ называется вложением функций​ или идентификационный номер​ ИСТИНА или вообще​ указать функции, из​1​

1. ​ИНДЕКС​ рабочая книга содержит​
2. ​ с​=INDEX(столбец из которого извлекаем,(MATCH​ пересечении указанных строки​ аргументе указывается номер​ СУММ. Весь процесс​ много недостатков, которые​ угол и тянем​ формулу: =ВПР($A7;$A$11:$B$17;1;ИСТИНА)​ (по нему производится​
3. ​ это поле пустым,​ друг в друга.​

​ клиента). Этот уникальный​ не вводить аргумент,​

​ какого столбца нужно​, то есть в​

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

exceltable.com

Как пользоваться функцией ВПР в Excel: пример с двумя таблицами

​Как видно из картинки​ поиск).​ либо ввести значение​ Excel с радостью​ код должен присутствовать​ но если точное​ извлечь значение. В​ ячейках​ПОИСКПОЗ​ массива, таких как​

​/​ котором ищем,0))​Вот простейший пример функции​ которого берутся значения.​ массиву функций о​ ее иногда нужно​ результат.​ выше, ВПР() нашла​Параметр ​TRUE​ допускает такие конструкции,​ в базе данных,​ совпадение не будет​ нашем случае это​A1:E1​, в качестве третьего​ВПР+СУММ​ИНДЕКС​=ИНДЕКС(столбец из которого извлекаем;(ПОИСКПОЗ(искомое​

Как работает функция ВПР в Excel: пример

​INDEX​ Стоит отметить что​ чем свидетельствуют фигурные​ использовать с другими​Теперь найти стоимость материалов​ наибольшую цену, которая​интервальный_просмотр​(ИСТИНА). Крайне важно​ и они даже​ иначе​ найдено, функция вернет​ столбец​:​ аргумента функции​

1. ​. Дело в том,​, столбец поиска может​ значение;столбец в котором​
2. ​(ИНДЕКС):​​ для просмотра в​ скобки в строке​ функциями или вообще​ не составит труда:​ меньше или равна​может принимать 2​ правильно выбрать этот​ работают, но их​ВПР​​наиболее близкое​C​=MATCH($H$3,$A$1:$E$1,0)​ПОИСКПОЗ​ что проверка каждого​ быть, как в​ ищем;0))​
3. ​=INDEX(A1:C10,2,3)​

​ аргументах функции указывается​ формул.​ заменять более сложными.​ количество * цену.​ заданной (см. файл​ значения: ИСТИНА (ищется​ параметр.​ гораздо сложнее читать​сообщит об ошибке.​приблизительное совпадение​(Sum), и поэтому​=ПОИСКПОЗ($H$3;$A$1:$E$1;0)​чаще всего нужно​ значения в массиве​ левой, так и​Думаю, ещё проще будет​=ИНДЕКС(A1:C10;2;3)​

​ целая таблица (во​Примечание. Если ввести вручную​ Для начала на​Функция ВПР связала две​ примера лист "Поиск​ значение ближайшее к критерию​Чтобы было понятнее, мы​

​ и понимать.​ В этой статье​, а большинство людей​ мы ввели​Результатом этой формулы будет​ будет указывать​ требует отдельного вызова​ в правой части​ понять на примере.​Формула выполняет поиск в​ втором аргументе), но​ крайние фигурные скобки​ готовом примере применения​

Функция ВПР в Excel и две таблицы

​ ближайшего числа"). Это​ или совпадающее с ним)​ введём​Мы не будем вникать​ мы рассмотрим такой​ приблизительное совпадение не​3​5​1​ функции​ диапазона поиска. Пример:​

​ Предположим, у Вас​ диапазоне​ сам поиск всегда​ в строку формул​ функции рассмотрим ее​

​ прайс, то и​ связано следует из​ и ЛОЖЬ (ищется значение​TRUE​ в технические подробности​ способ использования функции​ устраивает.​.​, поскольку «2015» находится​или​ВПР​

1. ​ Как находить значения,​
2. ​ есть вот такой​A1:C10​
3. ​ идет по первому​ то это не​ преимущества, а потом​ изменится стоимость поступивших​ того как функция​ в точности совпадающее​(ИСТИНА) в поле​ — почему и​ВПР​Чтобы убедиться в том,​И, наконец, т.к. нам​
4. ​ в 5-ом столбце.​
5. ​-1​. Поэтому, чем больше​ которые находятся слева​ список столиц государств:​и возвращает значение​ столбцу в указанной​ приведет ни ка​
6. ​ определим недостатки.​ на склад материалов​ производит поиск: если функция ВПР() находит​ с критерием). Значение ИСТИНА​Range_lookup​ как это работает,​, когда идентификатора не​ что использование приблизительного​ нужно проверить каждую​Теперь вставляем эти формулы​в случае, если​ значений содержит массив​ покажет эту возможность​Давайте найдём население одной​ ячейки во​ таблицы.​ какому результату. Выполнить​Функция ВПР предназначена для​ (сегодня поступивших). Чтобы​

​ значение, которое больше​ предполагает, что первый​(Интервальный_просмотр). Хотя, если​ и не будем​ существует в базе​ совпадения может иметь​

​ ячейку в массиве,​ в функцию​ Вы не уверены,​ и чем больше​ в действии.​ из столиц, например,​2-й​Скачать пример функции ВПР​ функцию циклическим массивом​ выборки данных из​ этого избежать, воспользуйтесь​ искомого, то она​ столбец в​

​ оставить поле пустым,​ вдаваться в нюансы​ данных вообще. Как​ серьезные последствия, предположим,​ эта формула должна​ИНДЕКС​ что просматриваемый диапазон​ формул массива содержит​2. Безопасное добавление или​ Японии, используя следующую​

​строке и​ с несколькими условиями​ можно только через​ таблицы Excel по​ «Специальной вставкой».​ выводит значение, которое​

​таблице​ это не будет​

​ записи вложенных функций.​ будто функция​ что ищется цена​ быть формулой массива.​и вуаля:​ содержит значение, равное​ Ваша таблица, тем​ удаление столбцов.​ формулу:​3-м​ в Excel​ комбинацию горячих клавиш:​ определенным критериям поиска.​

exceltable.com

Функция ВПР с несколькими условиями критериев поиска в Excel

​Выделяем столбец со вставленными​ расположено на строку​отсортирован в алфавитном​ ошибкой, так как​ Ведь это статья,​ВПР​ детали с идентификатором​ Вы можете видеть​=INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0))​ среднему. Если же​ медленнее работает Excel.​Формулы с функцией​=INDEX($D$2:$D$10,MATCH("Japan",$B$2:$B$10,0))​столбце, то есть​А из какого столбца​ CTRL+SHIFT+ENTER.​ Например, если таблица​ ценами.​ выше его. Как​

Работа функции ВПР по нескольким критериям

​ порядке или по​TRUE​ посвященная функции​переключилась в режим​ 2345768, но вы​ это по фигурным​=ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($H$2;$B$1:$B$11;0);ПОИСКПОЗ($H$3;$A$1:$E$1;0))​

​ Вы уверены, что​С другой стороны, формула​ВПР​=ИНДЕКС($D$2:$D$10;ПОИСКПОЗ("Japan";$B$2:$B$10;0))​ из ячейки​ брать возвращаемое значение​Стоит отметить, что главным​ состоит из двух​

1. ​Правая кнопка мыши –​ следствие, если искомое​
2. ​ возрастанию. Это способ​

​— это его​ВПР​ приближенной работы, и​ перепутали две цифры​ скобкам, в которые​

1. ​Если заменить функции​ такое значение есть,​ с функциями​перестают работать или​Теперь давайте разберем, что​
2. ​C2​ указывается уже в​ недостатком функции ВПР​ колонок: «Наименование товара»​ «Копировать».​ значение меньше минимального​
3. ​ используется в функции​ значение по умолчанию:​, а не полное​ сама выбирает, какие​ и ввели их​
4. ​ она заключена. Поэтому,​ПОИСКПОЗ​ – ставьте​ПОИСКПОЗ​ возвращают ошибочные значения,​ делает каждый элемент​

​.​ третьем аргументе.​

​ является отсутствие возможности​ и «Цена». Рядом​Не снимая выделения, правая​

​ по умолчанию, если​Мы заполнили все параметры.​ руководство по Excel.​

​ данные предоставить нам,​ в формулу следующим​ когда закончите вводить​на значения, которые​0​и​ если удалить или​ этой формулы:​Очень просто, правда? Однако,​Число 0 в последнем​ выбрать несколько одинаковых​ находится другая таблица,​ кнопка мыши –​ то функцию вернет​ не указан другой.​ Теперь нажимаем​Как бы там ни​ когда мы что-то​ образом:​

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

1. ​Функция​
2. ​ на практике Вы​
3. ​ аргументе функции указывает​

​ исходных значений в​ которая будет искать​ «Специальная вставка».​ ошибку ​Ниже в статье рассмотрены​ОК​ было, формула усложняется!​ хотим найти. В​=ВПР​ нажать​ станет легкой и​ совпадения.​просто совершает поиск​ таблицу поиска. Для​MATCH​ далеко не всегда​ на то, то​ запросе.​ в первой таблице​Поставить галочку напротив «Значения».​#Н/Д.​ популярные задачи, которые​, и Excel создаёт​ А что, если​ определённых обстоятельствах именно​(2345678;A1:E7;5)​Ctrl+Shift+Enter​ понятной:​Если указываете​ и возвращает результат,​ функции​(ПОИСКПОЗ) ищет значение​ знаете, какие строка​ совпадение должно быть​Скачать пример функции ВПР​ по наименованию товара​ ОК.​Найденное значение может быть​ можно решить с​ для нас формулу​

​ мы введем еще​ так и нужно.​. Формула возвращает цену​

​.​=INDEX($A$1:$E$11,4,5))​1​ выполняя аналогичную работу​

​ВПР​ «Japan» в столбце​ и столбец Вам​ абсолютно точным.​ с двумя таблицами​

exceltable.com

​ и получать значение​

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

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

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

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

Так как задача этого учебника – показать возможности функций ИНДЕКС и ПОИСКПОЗ для реализации вертикального поиска в 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 или не указан – находит максимальное значение, меньшее или равное искомому. Просматриваемый массив должен быть упорядочен по возрастанию, то есть от меньшего к большему.
  • 0 – находит первое значение, равное искомому. Для комбинации ИНДЕКС /ПОИСКПОЗ всегда нужно точное совпадение, поэтому третий аргумент функции ПОИСКПОЗ должен быть равен 0 .
  • -1 – находит наименьшее значение, большее или равное искомому значению. Просматриваемый массив должен быть упорядочен по убыванию, то есть от большего к меньшему.

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

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

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

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

Ещё не совсем понятно? Представьте функции ИНДЕКС и ПОИСКПОЗ в таком виде:

INDEX(,(MATCH (искомое значение ,столбец в котором ищем ,0))
=ИНДЕКС(столбец из которого извлекаем ;(ПОИСКПОЗ(искомое значение ;столбец в котором ищем ;0))

Думаю, ещё проще будет понять на примере. Предположим, у Вас есть вот такой список столиц государств:

Давайте найдём население одной из столиц, например, Японии, используя следующую формулу:

INDEX($D$2:$D$10,MATCH("Japan",$B$2:$B$10,0))
=ИНДЕКС($D$2:$D$10;ПОИСКПОЗ("Japan";$B$2:$B$10;0))

Теперь давайте разберем, что делает каждый элемент этой формулы:

• Функция MATCH (ПОИСКПОЗ) ищет значение “Japan” в столбце B , а конкретно – в ячейках B2:B10 , и возвращает число 3 , поскольку “Japan” в списке на третьем месте.
• Функция INDEX (ИНДЕКС) использует 3 для аргумента row_num (номер_строки), который указывает из какой строки нужно возвратить значение. Т.е. получается простая формула:

  INDEX($D$2:$D$10,3)
  =ИНДЕКС($D$2:$D$10;3)

  Формула говорит примерно следующее: ищи в ячейках от D2 до D10 и извлеки значение из третьей строки, то есть из ячейки D4 , так как счёт начинается со второй строки.

Вот такой результат получится в Excel:

Важно! Количество строк и столбцов в массиве, который использует функция INDEX (ИНДЕКС), должно соответствовать значениям аргументов row_num (номер_строки) и column_num (номер_столбца) функции MATCH (ПОИСКПОЗ). Иначе результат формулы будет ошибочным.

Стоп, стоп… почему мы не можем просто использовать функцию VLOOKUP (ВПР)? Есть ли смысл тратить время, пытаясь разобраться в лабиринтах ПОИСКПОЗ и ИНДЕКС ?

VLOOKUP("Japan",$B$2:$D$2,3)
=ВПР("Japan";$B$2:$D$2;3)

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

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

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

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

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

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

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

VLOOKUP("lookup value",A1:C10,2)
=ВПР("lookup value";A1:C10;2)

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

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

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

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

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

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

INDEX(D5:D10,MATCH(TRUE,INDEX(B5:B10=A2,0),0))
=ИНДЕКС(D5:D10;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B5:B10=A2;0);0))

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

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

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

ИНДЕКС и ПОИСКПОЗ – примеры формул

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

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

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

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

Как видно на рисунке ниже, формула отлично справляется с этой задачей:

INDEX($A$2:$A$10,MATCH("Russia",$B$2:$B$10,0))

Теперь у Вас не должно возникать проблем с пониманием, как работает эта формула:

• Во-первых, задействуем функцию MATCH (ПОИСКПОЗ), которая находит положение “Russia” в списке:

  MATCH("Russia",$B$2:$B$10,0))
  =ПОИСКПОЗ("Russia";$B$2:$B$10;0))

• Далее, задаём диапазон для функции INDEX (ИНДЕКС), из которого нужно извлечь значение. В нашем случае это A2:A10 .
• Затем соединяем обе части и получаем формулу:

  INDEX($A$2:$A$10;MATCH("Russia";$B$2:$B$10;0))
  =ИНДЕКС($A$2:$A$10;ПОИСКПОЗ("Russia";$B$2:$B$10;0))

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

Вычисления при помощи ИНДЕКС и ПОИСКПОЗ в Excel (СРЗНАЧ, МАКС, МИН)

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

1. MAX (МАКС). Формула находит максимум в столбце D C той же строки:

INDEX($C$2:$C$10,MATCH(MAX($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МАКС($D$2:I$10);$D$2:D$10;0))

Результат: Beijing

2. MIN (МИН). Формула находит минимум в столбце D и возвращает значение из столбца C той же строки:

INDEX($C$2:$C$10,MATCH(MIN($D$2:I$10),$D$2:D$10,0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(МИН($D$2:I$10);$D$2:D$10;0))

Результат: Lima

3. AVERAGE (СРЗНАЧ). Формула вычисляет среднее в диапазоне D2:D10 , затем находит ближайшее к нему и возвращает значение из столбца C той же строки:

INDEX($C$2:$C$10,MATCH(AVERAGE($D$2:D$10),$D$2:D$10,1))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(СРЗНАЧ($D$2:D$10);$D$2:D$10;1))

Результат: Moscow

О чём нужно помнить, используя функцию СРЗНАЧ вместе с ИНДЕКС и ПОИСКПОЗ

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

• Если указываете 1 , значения в столбце поиска должны быть упорядочены по возрастанию, а формула вернёт максимальное значение, меньшее или равное среднему.
• Если указываете -1 , значения в столбце поиска должны быть упорядочены по убыванию, а возвращено будет минимальное значение, большее или равное среднему.

В нашем примере значения в столбце D упорядочены по возрастанию, поэтому мы используем тип сопоставления 1 . Формула ИНДЕКС /ПОИСКПО З возвращает “Moscow”, поскольку величина населения города Москва – ближайшее меньшее к среднему значению (12 269 006).

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

Эта формула эквивалентна двумерному поиску ВПР и позволяет найти значение на пересечении определённой строки и столбца.

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

Как Вы помните, синтаксис функции INDEX (ИНДЕКС) позволяет использовать три аргумента:

INDEX(array,row_num,)
ИНДЕКС(массив;номер_строки;[номер_столбца])

И я поздравляю тех из Вас, кто догадался!

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

INDEX(Ваша таблица ,(MATCH(,столбец, в котором искать ,0)),(MATCH(,строка в которой искать ,0))
=ИНДЕКС(Ваша таблица ,(MATCH(значение для вертикального поиска ,столбец, в котором искать ,0)),(MATCH(значение для горизонтального поиска ,строка в которой искать ,0))

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

А теперь давайте испытаем этот шаблон на практике. Ниже Вы видите список самых населённых стран мира. Предположим, наша задача узнать население США в 2015 году.

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

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

• ПОИСКПОЗ для столбца – мы ищем в столбце B , а точнее в диапазоне B2:B11 , значение, которое указано в ячейке H2 (USA). Функция будет выглядеть так:

  MATCH($H$2,$B$1:$B$11,0)
  =ПОИСКПОЗ($H$2;$B$1:$B$11;0)

  4 , поскольку “USA” – это 4-ый элемент списка в столбце B (включая заголовок).

• ПОИСКПОЗ для строки – мы ищем значение ячейки H3 (2015) в строке 1 , то есть в ячейках A1:E1 :

  MATCH($H$3,$A$1:$E$1,0)
  =ПОИСКПОЗ($H$3;$A$1:$E$1;0)

  Результатом этой формулы будет 5 , поскольку “2015” находится в 5-ом столбце.

Теперь вставляем эти формулы в функцию ИНДЕКС и вуаля:

INDEX($A$1:$E$11,MATCH($H$2,$B$1:$B$11,0),MATCH($H$3,$A$1:$E$1,0))
=ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($H$2;$B$1:$B$11;0);ПОИСКПОЗ($H$3;$A$1:$E$1;0))

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

INDEX($A$1:$E$11,4,5))
=ИНДЕКС($A$1:$E$11;4;5))

Эта формула возвращает значение на пересечении 4-ой строки и 5-го столбца в диапазоне A1:E11 , то есть значение ячейки E4 . Просто? Да!

Поиск по нескольким критериям с ИНДЕКС и ПОИСКПОЗ

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

Предположим, у нас есть список заказов, и мы хотим найти сумму по двум критериям – имя покупателя (Customer) и продукт (Product). Дело усложняется тем, что один покупатель может купить сразу несколько разных продуктов, и имена покупателей в таблице на листе Lookup table расположены в произвольном порядке.

Вот такая формула ИНДЕКС /ПОИСКПОЗ решает задачу:

{=INDEX("Lookup table"!$A$2:$C$13,MATCH(1,(A2="Lookup table"!$A$2:$A$13)*
(B2="Lookup table"!$B$2:$B$13),0),3)}
{=ИНДЕКС("Lookup table"!$A$2:$C$13;ПОИСКПОЗ(1;(A2="Lookup table"!$A$2:$A$13)*
(B2="Lookup table"!$B$2:$B$13);0);3)}

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

MATCH(1,(A2="Lookup table"!$A$2:$A$13),0)*(B2="Lookup table"!$B$2:$B$13)
ПОИСКПОЗ(1;(A2="Lookup table"!$A$2:$A$13);0)*(B2="Lookup table"!$B$2:$B$13)

В формуле, показанной выше, искомое значение – это 1 , а массив поиска – это результат умножения. Хорошо, что же мы должны перемножить и почему? Давайте разберем все по порядку:

• Берем первое значение в столбце A (Customer) на листе Main table и сравниваем его со всеми именами покупателей в таблице на листе Lookup table (A2:A13).
• Если совпадение найдено, уравнение возвращает 1 (ИСТИНА), а если нет – 0 (ЛОЖЬ).
• Далее, мы делаем то же самое для значений столбца B (Product).
• Затем перемножаем полученные результаты (1 и 0). Только если совпадения найдены в обоих столбцах (т.е. оба критерия �