Как найти искомое значение в столбце

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

Описание

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

Создание образца листа

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

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

	A	B	C	D	E
1	Имя	Правитель	Возраст		Поиск значения
2	Анри	501	Плот		Иванов
3	Стэн	201	19
4	Иванов	101	максималь
5	Ларри	301	составляет

Определения терминов

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

Термин	Определение	Пример
Массив таблиц	Вся таблица подстановки	A2: C5
Превышающ	Значение, которое будет найдено в первом столбце аргумента «инфо_таблица».	E2
Просматриваемый_массив -или- Лукуп_вектор	Диапазон ячеек, которые содержат возможные значения подстановки.	A2: A5
Номер_столбца	Номер столбца в аргументе инфо_таблица, для которого должно быть возвращено совпадающее значение.	3 (третий столбец в инфо_таблица)
Ресулт_аррай -или- Ресулт_вектор	Диапазон, содержащий только одну строку или один столбец. Он должен быть такого же размера, что и просматриваемый_массив или Лукуп_вектор.	C2: C5
Интервальный_просмотр	Логическое значение (истина или ложь). Если указано значение истина или опущено, возвращается приближенное соответствие. Если задано значение FALSE, оно будет искать точное совпадение.	ЛОЖЬ
Топ_целл	Это ссылка, на основе которой вы хотите основать смещение. Топ_целл должен ссылаться на ячейку или диапазон смежных ячеек. В противном случае функция СМЕЩ возвращает #VALUE! значение ошибки #ИМЯ?.
Оффсет_кол	Число столбцов, находящегося слева или справа от которых должна указываться верхняя левая ячейка результата. Например, значение “5” в качестве аргумента Оффсет_кол указывает на то, что верхняя левая ячейка ссылки состоит из пяти столбцов справа от ссылки. Оффсет_кол может быть положительным (то есть справа от начальной ссылки) или отрицательным (то есть слева от начальной ссылки).

Функции

LOOKUP ()

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

Ниже приведен пример синтаксиса формулы подСТАНОВКи.

   = Просмотр (искомое_значение; Лукуп_вектор; Ресулт_вектор)

Следующая формула находит возраст Марии на листе “образец”.

   = ПРОСМОТР (E2; A2: A5; C2: C5)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в векторе подстановки (столбец A). Формула затем соответствует значению в той же строке в векторе результатов (столбец C). Так как “Мария” находится в строке 4, функция Просмотр возвращает значение из строки 4 в столбце C (22).

Примечание. Для функции Просмотр необходимо, чтобы таблица была отсортирована.

Чтобы получить дополнительные сведения о функции Просмотр , щелкните следующий номер статьи базы знаний Майкрософт:
 

Использование функции Просмотр в Excel

ВПР ()

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

Ниже приведен пример синтаксиса формулы ВПР :

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

Следующая формула находит возраст Марии на листе “образец”.

   = ВПР (E2; A2: C5; 3; ЛОЖЬ)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в левом столбце (столбец A). Формула затем совпадет со значением в той же строке в Колумн_индекс. В этом примере используется “3” в качестве Колумн_индекс (столбец C). Так как “Мария” находится в строке 4, функция ВПР возвращает значение из строки 4 В столбце C (22).

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

Как найти точное совпадение с помощью функций ВПР или ГПР

INDEX () и MATCH ()

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

Ниже приведен пример синтаксиса, объединяющего индекс и Match для получения одинаковых результатов поиска и ВПР в предыдущих примерах:

    = Индекс (инфо_таблица; MATCH (искомое_значение; просматриваемый_массив; 0); номер_столбца)

Следующая формула находит возраст Марии на листе “образец”.

= ИНДЕКС (A2: C5; MATCH (E2; A2: A5; 0); 3)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Затем он будет соответствовать значению в той же строке в столбце C. Так как “Мария” находится в строке 4, формула возвращает значение из строки 4 в столбце C (22).

Обратите внимание Если ни одна из ячеек в аргументе “число” не соответствует искомому значению (“Мария”), эта формула будет возвращать #N/А.
Чтобы получить дополнительные сведения о функции индекс , щелкните следующий номер статьи базы знаний Майкрософт:

Поиск данных в таблице с помощью функции индекс

СМЕЩ () и MATCH ()

Функции СМЕЩ и ПОИСКПОЗ можно использовать вместе, чтобы получить те же результаты, что и функции в предыдущем примере.

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

   = СМЕЩЕНИЕ (топ_целл, MATCH (искомое_значение; просматриваемый_массив; 0); Оффсет_кол)

Эта формула находит возраст Марии на листе “образец”.

   = СМЕЩЕНИЕ (A1; MATCH (E2; A2: A5; 0); 2)

Формула использует значение «Мария» в ячейке E2 и находит слово «Мария» в столбце A. Формула затем соответствует значению в той же строке, но двум столбцам справа (столбец C). Так как “Мария” находится в столбце A, формула возвращает значение в строке 4 в столбце C (22).

Чтобы получить дополнительные сведения о функции СМЕЩ , щелкните следующий номер статьи базы знаний Майкрософт:
 

Использование функции СМЕЩ

Поиск нужных данных в диапазоне

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

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

Необходимо определить регион поставки по артикулу товара, набранному в ячейку C16.

Задача решается при помощи двух функций:

=ИНДЕКС(A1:G13;ПОИСКПОЗ(C16;D1:D13;0);2)

Функция ПОИСКПОЗ ищет в столбце D1:D13 значение артикула из ячейки C16. Последний аргумент функции 0 – означает поиск точного (а не приблизительного) соответствия. Функция выдает порядковый номер найденного значения в диапазоне, т.е. фактически номер строки, где найден требуемыый артикул.

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

Ссылки по теме

• Использование функции ВПР (VLOOKUP) для поиска и подстановки значений.
• Улучшенная версия функции ВПР (VLOOKUP)
• Многоразовый ВПР

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

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

​ДВССЫЛ​Запишите формулу для вставки​

​ аргумент​

​Здесь функция​

• ​John Doe1​Имя клиента​непредсказуем​начальная_позиция​Использование аргумента массива таблицы​Для поиска значения в​Читайте также: Функции ИНДЕКС​ ПОИСКПОЗ, мы создали​

• ​ на против строки​ запятой. Вторым аргументом​Аналогичным образом можно произвести​ на кнопку​

  ​«Просматриваемый массив»​​.​ цен из таблицы​match_type​ROW​,​(Customer) и​

• ​=СУММПРОИЗВ((A4:A15=D4)*(B4:B15))​значение 8, чтобы​ в функции ВПР​ большом списке можно​ и ПОИСКПОЗ в​

​ вторую для вывода​ с именем Товар​ является​ поиск и самой​«OK»​– это адрес​Во-первых, позвольте напомнить синтаксис​​Lookup table 2​​(тип_сопоставления).​(СТРОКА) действует как​John Doe2​Название продукта​соответствующие значения суммируются​ поиск не выполнялся​​К началу страницы​ ​ использовать функцию просмотра.​​ Excel и примеры​ названия строк таблицы​ 4. Но так​

​«Просматриваемый массив»​ близкой позиции к​.​ диапазона, в котором​ функции​на основе известных​Использовав​ дополнительный счётчик. Так​и т.д. Фокус​(Product). Не забывайте,​=СУММЕСЛИ(A4:A15;D4;B4:B15)​​ в той части​​В этой статье описаны​​ Функция ВПР часто​ их использования​ по зачиню ячейки.​ как нам заранее​.​«400»​Программа выполняет вычисление и​ расположено искомое значение.​ДВССЫЛ​

​ названий товаров. Для​0​ как формула скопирована​ с нумерацией сделаем​ что объединенный столбец​соответствующие значения суммируются​ текста, которая является​ синтаксис формулы и​ используется, но можно​Функция СТОЛБЕЦ в Excel​ Название соответствующих строк​ не известен этот​ПОИСКПОЗ​по убыванию. Только​ выводит порядковый номер​ Именно позицию данного​(INDIRECT):​ этого вставьте созданную​

​в третьем аргументе,​ в ячейки F4:F9,​ при помощи функции​ должен быть всегда​=БИЗВЛЕЧЬ(A3:B15;2;F15:F16)​ серийным номером (в​ использование функций​ задействовать и функции​ возвращает номер столбца​ (товаров) выводим в​ номер мы с​будет просматривать тот​ для этого нужно​

​ позиции​ элемента в этом​INDIRECT(ref_text,[a1])​ ранее формулу в​

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

​ Вы говорите функции​ мы вычитаем число​COUNTIF​ крайним левым в​возвращается ошибка #ЧИСЛО!​ данном случае —​ПОИСК​ ГПР, ИНДЕКС и​

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

​ на листе по​ F2.​ помощью функции СТОЛБЕЦ​ диапазон, в котором​ произвести фильтрацию данных​«Сахар»​ массиве и должен​ДВССЫЛ(ссылка_на_текст;[a1])​ качестве искомого значения​ПОИСКПОЗ​3​(СЧЁТЕСЛИ), учитывая, что​ диапазоне поиска, поскольку​Для функции ВПР() требуется,​ “МДС0093”). Функция​и​

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

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

• ​ в поле​ в той ячейке,​

• ​ПОИСКПОЗ​ ссылкой на ячейку​

• ​ВПР​ в точности совпадающее​

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

support.office.com

ПОИСК, ПОИСКБ (функции ПОИСК, ПОИСКБ)

​ в столбце B:​ функция​ которому производится поиск,​​начинает поиск с​​в Microsoft Excel.​​ и ее аргументов:​​ аргумент. Но с​

Описание

​ задач всегда обращайте​​ B4:G15.​​ приближенную к 350​​«Тип сопоставления»​​ которую мы задали​.​ (стиль A1 или​:​ с искомым значением.​1​=B2&COUNTIF($B$2:B2,B2)​ВПР​ был левее столбца,​ восьмого символа, находит​Функции​

​=ВПР(;;;)​

​ ее помощью можно​​ внимание на второй​​Это позволяет функции ВПР​ рублям. Поэтому в​аргументов функции установить​

​ ещё на первом​«Тип сопоставления»​ R1C1), именем диапазона​

​=VLOOKUP(VLOOKUP(A2,New_SKU,2,FALSE),Price,3,FALSE)​

​ Это равносильно значению​​в ячейке​​=B2&СЧЁТЕСЛИ($B$2:B2;B2)​просматривает при поиске​ который используется для​ знак, указанный в​ПОИСК​​Например, =ВПР(21500;C2:E7;3;ЛОЖЬ).​​ эффективно решать разнообразные​​ и третий аргумент​​ собрать целый массив​ данном случае указываем​ значение​ шаге данной инструкции.​указывает точное совпадение​ или текстовой строкой.​​=ВПР(ВПР(A2;New_SKU;2;ЛОЖЬ);Price;3;ЛОЖЬ)​​FALSE​​F4​​После этого Вы можете​ значения.​​ вывода. Обойти это​​ аргументе​​И​​Первый аргумент (часть, необходимая​ задачи.​ поисковой функции ГПР.​

​ значений. В результате​​ координаты столбца​

• ​«1»​ Номер позиции будет​ нужно искать или​

• ​ Второй аргумент определяет,​Здесь​(ЛОЖЬ) для четвёртого​(строка 4, вычитаем​ использовать обычную функцию​Итак, Вы добавляете вспомогательный​ ограничение позволяет, например,​искомый_текст​ПОИСКБ​ для работы функции)​Функция с параметром: =​ Количество охваченных строк​ в памяти хранится​

​«Сумма выручки»​.​ равен​ неточное. Этот аргумент​ какого стиля ссылка​

Синтаксис

​Price​

​ аргумента​

​ 3), чтобы получить​ВПР​

• ​ столбец в таблицу​​ вариант с использованием​, в следующей позиции,​

• ​находят одну текстовую​​ — это искомое​ СТОЛБЕЦ (С3) возвращает​ в диапазоне указанного​​ все соответствующие значения​​. Опять ставим точку​

• ​Урок:​​«4»​ может иметь три​​ содержится в первом​​– именованный диапазон​ВПР​

Замечание

• ​2​​, чтобы найти нужный​​ и копируете по​​ функций ИНДЕКС() и​​ и возвращает число​ строку в другой​ значение. Это может​​ значение 3, т.к.​​ в аргументе, должно​​ каждому столбцу по​​ с запятой. Третьим​

• ​Сортировка и фильтрация данных​​.​​ значения:​ аргументе:​$A:$C​​.​​в ячейке​​ заказ. Например:​​ всем его ячейкам​ ПОИСКПОЗ(). Эквивалентная формула​ 9. Функция​ и возвращают начальную​ быть ссылка на​ (C) является третьим​ совпадать с количеством​​ строке Товар 4​​ аргументом является​

• ​ в Excel​​Урок:​​«1»​A1​

• ​в таблице​​Вот так Вы можете​​F5​Находим​

• ​ формулу вида:​​ приведена в статье​​ПОИСК​ позицию первой текстовой​ ячейку, например B2,​​ по счету.​​ строк в таблице.​ (а именно: 360;​

• ​«Тип сопоставления»​​Эффективнее всего эту функцию​​Мастер функций в Экселе​,​, если аргумент равен​Lookup table 2​​ создать формулу для​​(строка 5, вычитаем​2-й​=B2&C2​ о функции ВПР().​всегда возвращает номер​ строки (считая от​ или значение, например​​Аргумент «ссылка» необязательный. Это​​ А также нумерация​ 958; 201; 605;​. Так как мы​ использовать с другими​Выше мы рассмотрели самый​«0»​TRUE​​, а​​ поиска по двум​ 3) и так​товар, заказанный покупателем​. Если хочется, чтобы​​Задача подразумевает, что диапазон​​ знака, считая от​ первого символа второй​ “кузьмина” или 21500.​​ может быть ячейка​​ должна начинаться со​ 462; 832). После​ будем искать число​​ операторами в составе​​ примитивный случай применения​и​(ИСТИНА) или не​​3​​ критериям в Excel,​

Примеры

​ далее.​Dan Brown​ строка была более​ поиска содержит неповторяющиеся​ начала​ текстовой строки). Например,​Второй аргумент — это​ или диапазон, для​ второй строки!​ чего функции МАКС​ равное заданному или​ комплексной формулы. Наиболее​ оператора​

​«-1»​
​ указан;​
​– это столбец​
​ что также известно,​
​SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,””),ROW()-3))​
​:​	​ читаемой, можно разделить​	​ значения. В самом​
​просматриваемого текста​	​ чтобы найти позицию​ диапазон ячеек, который,​ которого нужно получить​Скачать пример поиска значения​	​ остается только взять​
​ самое близкое меньшее,​	​ часто её применяют​ПОИСКПОЗ​. При значении​R1C1​ C, содержащий цены.​ как двумерный поиск​	​НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;””);СТРОКА()-3))​
​=VLOOKUP(“Dan Brown2”,$A$2:$C$16,3,FALSE)​	​ объединенные значения пробелом:​ деле, если критерию​, включая символы, которые​ буквы “n” в​ как вы предполагаете,​ номер столбца.​ в столбце и​ из этого массива​	​ то устанавливаем тут​
​ в связке с​	​, но даже его​«0»​, если​На рисунке ниже виден​ или поиск в​	​Функция​
​=ВПР(“Dan Brown2”;$A$2:$C$16;3;ЛОЖЬ)​	​=B2&» «&C2​ удовлетворяет сразу несколько​ пропускаются, если значение​	​ слове “printer”, можно​
​ содержит искомое значение.​	​Аргумент – ссылка на​ строке Excel​ максимальное число и​	​ цифру​

support.office.com

Поиск позиции ТЕКСТа в MS EXCEL с выводом значения из соседнего столбца

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

​Важно:​​ ячейку:​​Читайте также: Поиск значения​ возвратить в качестве​«1»​ИНДЕКС​

Задача

​Для удобства на листе​​ точное совпадение. Если​​ALSE​ нами формулой:​Функция​(НАИМЕНЬШИЙ) возвращает​3-й​

Решение

​ использовать следующую формулу:​

• ​ какой строки выводить​начальная_позиция​=ПОИСК(“н”;”принтер”)​
• ​ В функции ВПР столбец,​Функция выдала номер колонки​
• ​ в диапазоне таблицы​ значения для ячейки​. Закрываем скобки.​

​. Данный аргумент выводит​ добавляем ещё два​ указано значение​(ЛОЖЬ).​В начале разъясним, что​СУММПРОИЗВ​n-ое​товар, заказанный покупателем​=VLOOKUP(“Jeremy Hill Sweets”,$A$7:$D$18,4,FALSE)​ соответствующее ему значение​больше 1.​Эта функция возвращает​ содержащий искомое значение​ для этой ячейки.​

​ Excel по столбцам​	​ D1, как результат​Третий аргумент функции​
​ в указанную ячейку​	​ дополнительных поля:​​«1»​​В нашем случае ссылка​
​ мы подразумеваем под​	​(SUMPRODUCT) возвращает сумму​​наименьшее значение в​​Dan Brown​
​=ВПР(“Jeremy Hill Sweets”;$A$7:$D$18;4;ЛОЖЬ)​	​ из соседнего столбца?​​Скопируйте образец данных из​​4​
​ или ссылку на​	​Аргумент опущен:​​ и строкам​​ вычисления формулы.​
​ИНДЕКС​	​ содержимое диапазона заданное​«Заданное значение»​​, то в случае​​ имеет стиль​ выражением «Динамическая подстановка​​ произведений выбранных массивов:​
​ массиве данных. В​	​:​
​или​	​ Если все же​
​ следующей таблицы и​	​, так как “н”​

​ ячейку, должен быть​Функция вернула номер столбца,​По сути содержимое диапазона​Как видно конструкция формулы​«Номер столбца»​ по номеру его​и​ отсутствия точного совпадения​A1​ данных из разных​=SUMPRODUCT(($A$2:$A$9=”Lemons”)*($A$1:$I$1=”Mar”),$A$2:$I$9)​ нашем случае, какую​

​=VLOOKUP(“Dan Brown3″,$A$2:$C$16,3,FALSE)​=VLOOKUP(B1,$A$7:$D$18,4,FALSE)​ диапазон поиска содержит​ вставьте их в​ является четвертым символом​ крайним левым столбцом​ в котором находится.​ нас вообще не​ проста и лаконична.​оставляем пустым. После​ строки или столбца.​«Номер»​ПОИСКПОЗ​, поэтому можно не​ таблиц», чтобы убедиться​=СУММПРОИЗВ(($A$2:$A$9=”Lemons”)*($A$1:$I$1=”Mar”);$A$2:$I$9)​ по счёту позицию​=ВПР(“Dan Brown3”;$A$2:$C$16;3;ЛОЖЬ)​

​=ВПР(B1;$A$7:$D$18;4;ЛОЖЬ)​ повторяющиеся значения, то​ ячейку A1 нового​ в слове “принтер”.​ в диапазоне.​Аргумент – вертикальный диапазон​ интересует, нам нужен​ На ее основе​

​ этого жмем на​​ Причем нумерация, как​. В поле​выдает самый близкий​ указывать второй аргумент​ правильно ли мы​В следующей статье я​ (от наименьшего) возвращать​На самом деле, Вы​Где ячейка​ второй столбец из​ листа Excel. Чтобы​Можно также находить слова​

​Третий аргумент — это​ ячеек:​ просто счетчик строк.​ можно в похожий​ кнопку​

excel2.ru

Продвинутые примеры с функцией ВПР: поиск по нескольким критериям

​ и в отношении​«Заданное значение»​​ к нему элемент​​ и сосредоточиться на​ понимает друг друга.​ буду объяснять эти​ – определено функцией​ можете ввести ссылку​​B1​​ таблицы выше поясняет​ отобразить результаты формул,​ в других словах.​ столбец в диапазоне​Функция вернула номер столбца,​ То есть изменить​ способ находить для​«OK»​ оператора​вбиваем то наименование,​ по убыванию. Если​ первом.​Бывают ситуации, когда есть​​ функции во всех​​ROW​ на ячейку в​

• ​содержит объединенное значение​ какое значение будет​
• ​ выделите их и​ Например, функция​ поиска ячеек, содержащий​
• ​ в котором расположен​ аргументы на: СТРОКА(B2:B11)​
• ​ определенного товара и​.​
• ​ПОИСКПОЗ​ которое нужно найти.​
• ​ указано значение​Итак, давайте вернемся к​

Поиск в Excel по нескольким критериям

​ несколько листов с​​ деталях, так что​​(СТРОКА) (смотри Часть​ качестве искомого значения​ аргумента​ выведено (обычно возвращается​ нажмите клавишу F2,​=ПОИСК(“base”;”database”)​ значение, которое нужно​ диапазон.​ или СТРОКА(С2:С11) –​ другие показатели. Например,​Как видим, функция​, выполняется не относительно​ Пусть теперь это​«-1»​

Пример 1: Поиск по 2-м разным критериям

​ нашим отчетам по​ данными одного формата,​ сейчас можете просто​​ 2). Так, для​​ вместо текста, как​lookup_value​​ первое значение, удовлетворяющее​​ а затем — клавишу​​возвращает​​ найти.​Аргумент – горизонтальный диапазон​ это никак не​ минимальное или среднее​ИНДЕКС​ всего листа, а​

​ будет​​, то в случае,​​ продажам. Если Вы​ и необходимо извлечь​ скопировать эту формулу:​ ячейки​ представлено на следующем​(искомое_значение), а​ критерию).​ ВВОД. При необходимости​​5​​Хотя четвертый аргумент не​​ ячеек:​​ повлияет на качество​ значение объема продаж​

​при помощи оператора​
​ только внутри диапазона.​

​«Мясо»​ если не обнаружено​​ помните, то каждый​​ нужную информацию с​​=INDEX($A$2:$I$9,MATCH(“Lemons”,$A$2:$A$9,0),MATCH(“Mar”,$A$1:$I$1,0))​​F4​ рисунке:​

​4​Если диапазон поиска содержит​ измените ширину столбцов,​, так как слово​ является обязательным, большинство​Функция СТОЛБЕЦ вернула номер​​ формулы. Главное, что​​ используя для этого​​ПОИСКПОЗ​​ Синтаксис этой функции​. В поле​ точное совпадение, функция​ отчёт – это​ определенного листа в​=ИНДЕКС($A$2:$I$9;ПОИСКПОЗ(“Lemons”;$A$2:$A$9;0);ПОИСКПОЗ(“Mar”;$A$1:$I$1;0))​функция​​Если Вы ищите только​​– аргумент​ повторяющиеся значения и​

​ чтобы видеть все​ “base” начинается с​ пользователей вводят аргумент​ крайнего левого столбца​ в этих диапазонах​​ функции МИН или​​в заранее указанную​ следующий:​«Номер»​ выдает самый близкий​​ отдельная таблица, расположенная​​ зависимости от значения,​Если Вы не в​

​НАИМЕНЬШИЙ({массив};1)​
​2-е​

​col_index_num​

​ требуется вернуть не​
​ данные.​

​ пятого символа слова​​ ЛОЖЬ (или 0).​​ (А) в указанном​ по 10 строк,​​ СРЗНАЧ. Вам ни​​ ячейку выводит наименование​​=ИНДЕКС(массив;номер_строки;номер_столбца)​​устанавливаем курсор и​​ к нему элемент​​ на отдельном листе.​ которое введено в​ восторге от всех​

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

​возвращает​повторение, то можете​(номер_столбца), т.е. номер​ одно, а все​Данные​ “database”. Можно использовать​ Почему? Потому что​ диапазоне. Если выделить​ как и в​ что не препятствует,​«Чай»​При этом, если массив​ переходим к окну​

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

​ функции​​ в этом случае​​ формулу в строке​

​ таблице. И нумерация​
​ чтобы приведенный этот​

​. Действительно, сумма от​ одномерный, то можно​ аргументов оператора тем​ если ведется поиск​ верно, Вы должны​​ проще это объяснить​​ Excel, Вам может​(наименьший) элемент массива,​ вспомогательного столбца, создав​

​ которые необходимо извлечь.​ то читайте статью​Доход: маржа​ПОИСК​ функция будет искать​ формул и нажать​

​ начинается со второй​
​ скелет формулы применить​

​ реализации чая (300​ использовать только один​ же способом, о​ не точного значения,​ дать названия своим​ на примере.​ понравиться вот такой​ то есть​ более сложную формулу:​Если Вам необходимо обновить​ Запрос на основе​маржа​и​точное совпадение​

​ кнопку F9, то​​ строки!​​ с использованием более​​ рублей) ближе всего​ из двух аргументов:​ котором шел разговор​ а приблизительного, чтобы​ таблицам (или диапазонам),​Представьте, что имеются отчеты​ наглядный и запоминающийся​1​=IFERROR(VLOOKUP($F$2,INDIRECT(“$B$”&(MATCH($F$2,Table4[Customer Name],0)+2)&”:$C16″),2,FALSE),””)​

​ основную таблицу (Main​ Элементов управления формы.​Здесь “босс”.​ПОИСКБ​. Можно ввести аргумент​ программа выдаст все​Во многих поисковых формулах​ сложных функций для​ по убыванию к​«Номер строки»​ выше.​​ просматриваемый массив был​​ причем все названия​​ по продажам для​​ способ:​

Извлекаем 2-е, 3-е и т.д. значения, используя ВПР

​. Для ячейки​​=ЕСЛИОШИБКА(ВПР($F$2;ДВССЫЛ(“$B$”&(ПОИСКПОЗ($F$2;Table4[Customer Name];0)+2)&”:$C16″);2;ИСТИНА);””)​​ table), добавив данные​Совет​Формула​для определения положения​ ИСТИНА или вообще​ номера столбцов заданного​ очень часто приходится​ реализации максимально комфортного​ сумме 350 рублей​или​В окне аргументов функции​ упорядочен по возрастанию​ должны иметь общую​ нескольких регионов с​

​Выделите таблицу, откройте вкладку​F5​В этой формуле:​ из второй таблицы​: Если в диапазон​Описание​ символа или текстовой​ не вводить аргумент,​ диапазона.​ использовать функцию ПОИСКПОЗ​

​ анализа отчета по​ из всех имеющихся​«Номер столбца»​​ в поле​​ (тип сопоставления​ часть. Например, так:​ одинаковыми товарами и​Formulas​​возвращает​​$F$2​​ (Lookup table), которая​​ поиска постоянно вводятся​Результат​ строки в другой​​ но если точное​​Но при нажатии кнопки​ как вспомогательную в​ продажам.​

​ в обрабатываемой таблице​
​.​

​«Искомое значение»​«1»​​CA_Sales​​ в одинаковом формате.​(Формулы) и нажмите​

• ​2-й​​– ячейка, содержащая​​ находится на другом​​ новые значения, то​​=ПОИСК(“и”;A2;6)​

  ​ текстовой строке, а​
​ совпадение не будет​

• ​ Enter в ячейке​​ комбинациях с другими​​Например, как эффектно мы​​ значений.​​Особенность связки функций​

  ​указываем адрес ячейки,​
​) или убыванию (тип​

​,​ Требуется найти показатели​Create from Selection​наименьший элемент массива,​ имя покупателя (она​ листе или в​ для исключения ввода​

​Позиция первого знака “и”​​ затем вернуть текст​​ найдено, функция вернет​ с формулой отобразится​ функциями такими как:​ отобразили месяц, в​

​Если мы изменим число​
​ИНДЕКС​

​ в которой вписано​

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

​,​ региона:​Отметьте галочками​3​ – ссылка абсолютная);​ Excel, то Вы​

​ определенные ограничения (см.​ A2, начиная с​ПСТР​​приблизительное совпадение​​ левого столбца.​ и др. Но​ продажа, с помощью​«Приблизительная сумма выручки»​ПОИСКПОЗ​«Мясо»​).​​TX_Sales​​Если у Вас всего​Top row​, и так далее.​$B$​ можете собрать искомое​ статью Ввод неповторяющихся​

Извлекаем все повторения искомого значения

​ шестого знака.​​и​​, а большинство людей​Чтобы на листе появились​ какую пользу может​ второй формулы. Не​на другое, то​заключается в том,​. В полях​Аргумент​и так далее.​​ два таких отчета,​​(в строке выше)​​INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,””),ROW()-3))​​– столбец​​ значение непосредственно в​​ значений). Для визуальной​

​7​ПСТРБ​ приблизительное совпадение не​ номера всех столбцов​ приносить данная функция​ сложно заметить что​ соответственно автоматически будет​ что последняя может​

​«Просматриваемый массив»​
​«Тип сопоставления»​

​ Как видите, во​ то можно использовать​ и​ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;””);СТРОКА()-3))​​Customer Name​​ формуле, которую вставляете​ проверки наличия дубликатов​=ПОИСК(A4;A3)​или заменить его​ устраивает.​ диапазона, который является​ работая самостоятельно. Из​ во второй формуле​​ пересчитано и содержимое​​ использоваться в качестве​и​

​не является обязательным.​ всех именах присутствует​ до безобразия простую​Left column​

Часть 1:

​Функция​
​;​

​ в основную таблицу.​​ можно использовать Условное​Начальная позиция строки “маржа”​ с помощью функций​Чтобы убедиться в том,​ аргументом функции СТОЛБЕЦ,​ самого названия функции​​ мы использовали скелет​​ поля​ аргумента первой, то​​«Тип сопоставления»​​ Он может быть​ «_Sales».​ формулу с функциями​​(в столбце слева).​​INDEX​Table4​

​Как и в предыдущем​​ форматирование (см. статью​​ (искомая строка в​ЗАМЕНИТЬ​​ что использование приблизительного​

Часть 2:

​ нужно использовать формулу​
​ ПОИСКПОЗ понятно, что​

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

Часть 3:

​ нем нет надобности.​
​ДВССЫЛ​

​и​​ имена диапазонам из​​ значение определённой ячейки​​ (на этом месте​​ в таблице поиска​Для организации динамической сортировки​ строке “Доход: маржа”​ЗАМЕНИТЬБ​ серьезные последствия, предположим,​ количество ячеек, сколько​​ заключается в определении​​ структура формулы: ВПР(B1;A5:G14;СТОЛБЕЦ(B5:G14);0).​Урок:​ столбца.​​ и в предыдущем​​ В этом случае​​соединяет значение в​​ЕСЛИ​​ значений в верхней​​ в массиве​ также может быть​​ (Lookup table) вспомогательный​​ пополняемого диапазона поиска​​ (ячейка, в которой​​. Эти функции показаны​​ что ищется цена​​ элементов входит в​ позиции исходного значения,​​ Мы заменили функцию​​Функция ИНДЕКС в Excel​

Часть 4:

​Давайте взглянем, как это​
​ способе – адрес​

​ его значение по​​ столбце D и​​(IF), чтобы выбрать​ строке и левом​C2:C16​​ обычный диапазон);​​ столбец с объединенными​​ можно использовать идеи​​ выполняется поиск — A3).​​ в примере 1​​ детали с идентификатором​​ горизонтальный диапазон. Вводим​​ которое содержит диапазон​​ МАКС на ПОИСКПОЗ,​​Как видим, оператор​​ можно сделать на​​ диапазона и число​​ умолчанию равно​​ текстовую строку «_Sales»,​

Часть 5:

​ нужный отчет для​
​ столбце Вашей таблицы.​

​. Для ячейки​$C16​​ значениями. Этот столбец​​ из статьи Сортированный​8​ данной статьи.​​ 2345768, но вы​​ формулу и нажимаем​ ячеек или таблица.​ которая в первом​ПОИСКПОЗ​ практике, используя всю​«0»​«1»​

Двумерный поиск по известным строке и столбцу

​ тем самым сообщая​ поиска:​ Теперь Вы можете​F4​– конечная ячейка​ должен быть крайним​ список.​=ЗАМЕНИТЬ(A3;ПОИСК(A4;A3);6;”объем”)​Важно:​

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

​ВПР​=VLOOKUP($D$2,IF($D3=”FL”,FL_Sales,CA_Sales),2,FALSE)​ осуществлять поиск, используя​функция​ Вашей таблицы или​

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

​ левым в заданном​Во второй части нашего​​Заменяет слово “маржа” словом​​ ​​ и ввели их​​ + Shift +​ очень просто для​ полученное предыдущей формулой.​​ функцией для определения​​ У нас стоит​​ жмем на кнопку​​«Тип сопоставления»​

​в какой таблице​
​=ВПР($D$2;ЕСЛИ($D3="FL";FL_Sales;CA_Sales);2;ЛОЖЬ)​

​ эти имена, напрямую,​ИНДЕКС($C$2:$C$16;1)​​ диапазона.​​ для поиска диапазоне.​ учебника по функции​ “объем”, определяя позицию​Эти функции могут быть​ в формулу следующим​ Enter.​ диапазонов или таблиц​ Оно теперь выступает​ порядкового номера указанного​ задача вывести в​«OK»​, прежде всего, имеет​​ искать. Если в​​Где:​ без создания формул.​​возвратит​​Эта формула находит только​Итак, формула с​

​ВПР​
​ слова "маржа" в​

​ доступны не на​ образом:​Аргумент – ссылка на​

• ​ с одним столбцом​ в качестве критерия​​ элемента в массиве​​ дополнительное поле листа​
• ​.​ смысл только тогда,​ ячейке D3 находится​​$D$2​​В любой пустой ячейке​
• ​Apples​ второе совпадающее значение.​​ВПР​​(VLOOKUP) в Excel​

​ ячейке A3 и​​ всех языках.​​=ВПР​ горизонтальный массив:​​ или с одной​​ для поиска месяца.​ данных. Но польза​«Товар»​После того, как мы​​ когда обрабатываются числовые​​ значение «FL», формула​– это ячейка,​​ запишите​​, для​

​ Если же Вам​может быть такой:​ мы разберём несколько​ заменяя этот знак​Функция ПОИСКБ отсчитывает по​(2345678;A1:E7;5)​Формула вернула номера столбцов​ строкой. Поэтому сразу​

Функция СУММПРОИЗВ

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

​ значения, а не​
​ выполнит поиск в​

Функции ИНДЕКС и ПОИСКПОЗ

​ содержащая название товара.​=имя_строки имя_столбца​F5​ необходимо извлечь остальные​=VLOOKUP(B2&” “&C2,Orders!$A&$2:$D$2,4,FALSE)​ примеров, которые помогут​

​ и последующие пять​
​ два байта на​

Именованные диапазоны и оператор пересечения

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

1. ​ текстовые.​​ таблице​​ Обратите внимание, здесь​​, например, так:​​функция​
2. ​ повторения, воспользуйтесь предыдущим​​=ВПР(B2&” “&C2;Orders!$A&$2:$D$2;4;ЛОЖЬ)​​ Вам направить всю​ знаков текстовой строкой​​ каждый символ, только​​ на другую деталь,​ массива.​ на конкретном примере​ возвращает номер столбца​ применяется в комплексных​ которого равна 350​«Номер»​В случае, если​FL_Sales​ мы используем абсолютные​
3. ​=Lemons Mar​ИНДЕКС($C$2:$C$16;3)​​ решением.​​Здесь в столбцах B​

   ​ мощь​
   ​ “объем.”​
   ​ если языком по​

   ​ потому что функция​В качестве аргумента нельзя​ проиллюстрируем как применять​ 2 где находится​ формулах.​ рублям или самому​

​отобразится позиция слова​ПОИСКПОЗ​, если «CA» –​ ссылки, чтобы избежать​… или наоборот:​возвратит​

1. ​Если Вам нужен список​​ и C содержатся​​ВПР​

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

Используем несколько ВПР в одной формуле

​Автор: Максим Тютюшев​ близкому к этому​«Мясо»​при заданных настройках​ в таблице​ изменения искомого значения​=Mar Lemons​​Sweets​​ всех совпадений –​ имена клиентов и​на решение наиболее​=ПСТР(A3;ПОИСК(” “;A3)+1,4)​ с поддержкой БДЦС.​ число, меньшее или​ несколько областей.​

​ таблицы с двумя​ продаж для товара​Имеем таблицу, в которой​ значению по убыванию.​​в выбранном диапазоне.​​ не может найти​CA_Sales​ при копировании формулы​Помните, что имена строки​и так далее.​ функция​ названия продуктов соответственно,​ амбициозных задач Excel.​Возвращает первые четыре знака,​​ В противном случае​​ равное указанному (2345678).​​ столбцами и более.​ 4. После чего​ записаны объемы продаж​​ Данный аргумент указан​​ В данном случае​

​ нужный элемент, то​и так далее.​ в другие ячейки.​ и столбца нужно​IFERROR()​​ВПР​​ а ссылка​​ Примеры подразумевают, что​​ которые следуют за​

1. ​ функция ПОИСКБ работает​​ Эта ошибка может​​Формула с использованием функции​Для примера возьмем список​​ в работу включается​​ определенных товаров в​​ в поле​​ она равна​

   ​ оператор показывает в​
​Результат работы функций​

   ​$D3​​ разделить пробелом, который​​ЕСЛИОШИБКА()​​тут не помощник,​​Orders!$A&$2:$D$2​​ Вы уже имеете​​ первым пробелом в​​ так же, как​​ привести к неправильному​ выдает массив последовательных​ автомобилей из автопарка​ функция ИНДЕКС, которая​

2. ​ разных месяцах. Необходимо​«Приблизительная сумма выручки на​​«3»​​ ячейке ошибку​ВПР​– это ячейка​ в данном случае​В завершение, мы помещаем​ поскольку она возвращает​​определяет таблицу для​​ базовые знания о​

   ​ строке "Доход: маржа"​
​ функция ПОИСК, и​

   ​ выставлению счета клиенту.​​ чисел. Эту особенность​​ средней фирмы, как​​ возвращает значение по​​ в таблице найти​​ листе»​​.​​«#Н/Д»​​и​ с названием региона.​

​ работает как оператор​ формулу внутрь функции​ только одно значение​

Динамическая подстановка данных из разных таблиц при помощи ВПР и ДВССЫЛ

​ поиска на другом​ том, как работает​ (ячейка A3).​ отсчитывает по одному​Если для аргумента “приблизительное​ можно применить для​ показано ниже на​

​ номеру сроки и​ данные, а критерием​.​Данный способ хорош тем,​.​ДВССЫЛ​ Используем абсолютную ссылку​ пересечения.​IFERROR​ за раз –​ листе.​

​ эта функция. Если​марж​ байту на каждый​ соответствие” указано значение​ решения других задач.​ рисунке:​ столбца из определенного​ поиска будут заголовки​

​Отсортировываем элементы в столбце​ что если мы​При проведении поиска оператор​будет следующий:​ для столбца и​​При вводе имени, Microsoft​​(ЕСЛИОШИБКА), поскольку вряд​​ и точка. Но​​Чтобы сделать формулу более​ нет, возможно, Вам​=ПОИСК(“”””;A5)​

​ символ.​
​ ЛОЖЬ или 0,​

​Например, рассчитаем значение выражения​

• ​В обеих столбцах названия​​ в ее аргументах​ строк и столбцов.​«Сумма выручки»​ захотим узнать позицию​ не различает регистры​Если данные расположены в​ относительную ссылку для​ Excel будет показывать​
• ​ ли Вас обрадует​​ в Excel есть​ читаемой, Вы можете​ будет интересно начать​Позиция первой двойной кавычки​К языкам, поддерживающим БДЦС,​ а точного совпадения​ 1 + ½​ автомобилей и отделов​ диапазона. Так как​
• ​ Но поиск должен​​по возрастанию. Для​​ любого другого наименования,​​ символов. Если в​​ разных книгах Excel,​ строки, поскольку планируем​ подсказку со списком​ сообщение об ошибке​ функция​ задать имя для​ с первой части​ (“) в ячейке​ относятся японский, китайский​​ нет, вместо неправильного​​ + 1/3. Используем​ повторяются, но нет​

​ у нас есть​ быть выполнен отдельно​​ этого выделяем необходимый​​ то не нужно​ массиве присутствует несколько​ то необходимо добавить​ копировать формулу в​​ подходящих имен, так​​#N/A​INDEX​

​ просматриваемого диапазона, и​ этого учебника, в​​ A5.​​ (упрощенное письмо), китайский​ значения формула возвращает​ формулу: =СУММПРОИЗВ(1/СТОЛБЕЦ(A2:C2)).​ парных дубликатов. Например,​ номер столбца 2,​ по диапазону строки​ столбец и, находясь​ будет каждый раз​ точных совпадений, то​ имя книги перед​​ другие ячейки того​​ же, как при​(#Н/Д) в случае,​​(ИНДЕКС), которая с​​ тогда формула станет​​ которой объясняются синтаксис​​5​​ (традиционное письмо) и​​ в ячейку строку​Выполним более сложные манипуляции​

​ в списке 2​
​ а номер строки​

​ или столбца. То​

• ​ во вкладке​​ заново набирать или​ПОИСКПОЗ​ именованным диапазоном, например:​ же столбца.​
• ​ вводе формулы.​​ если количество ячеек,​ легкостью справится с​ выглядеть гораздо проще:​ и основное применение​​=ПСТР(A5;ПОИСК(“”””;A5)+1;ПОИСК(“”””;A5;ПОИСК(“”””;A5)+1)-ПОИСК(“”””;A5)-1)​​ корейский.​
• ​ “#Н/Д”. Это наилучшее​​ с числовым рядом:​ автомобиля марки Ford,​ в диапазоне где​ есть будет использоваться​«Главная»​ изменять формулу. Достаточно​выводит в ячейку​=VLOOKUP($D$2,INDIRECT($D3&”Workbook1!_Sales”),2,FALSE)​FL_Sal​Нажмите​ в которые скопирована​ этой задачей. Как​​=VLOOKUP(B2&” “&C2,Orders,4,FALSE)​​ВПР​

Как работают ДВССЫЛ и ВПР

​Возвращает из ячейки A5​ПОИСК(искомый_текст;просматриваемый_текст;[начальная_позиция])​​ решение. В данном​​ найдем сумму значений​

​ но оба они​
​ хранятся названия месяцев​

​ только один из​, кликаем по значку​ просто в поле​ позицию самого первого​=ВПР($D$2;ДВССЫЛ($D3&”Workbook1!_Sales”);2;ЛОЖЬ)​es​Enter​ формула, будет меньше,​ будет выглядеть такая​

• ​=ВПР(B2&” “&C2;Orders;4;ЛОЖЬ)​​. Что ж, давайте​​ только текст, заключенный​​ПОИСКБ(искомый_текст;просматриваемый_текст;[начальная_позиция])​ случае “#Н/Д” не​
• ​ от 1 до​​ из разных отделов.​​ в любые случаи​​ критериев. Поэтому здесь​​«Сортировка и фильтр»​

​«Заданное значение»​ из них.​​Если функция​​и​и проверьте результат​ чем количество повторяющихся​ формула, Вы узнаете​

​Чтобы формула работала, значения​ приступим.​ в двойные кавычки.​Аргументы функций ПОИСК и​ означает, что формула​ 1/n^3, где n​ Если мы захотим​ будет 1. Тогда​ нельзя применить функцию​, а затем в​вписать новое искомое​Давайте рассмотрим на примере​ДВССЫЛ​CA_Sales​​В целом, какой бы​​ значений в просматриваемом​​ в следующем примере.​​ в крайнем левом​​Поиск в Excel по​​босс​ ПОИСКБ описаны ниже.​ введена неправильно (за​ = 6. Формула​

​ узнать номер позиции​​ нам осталось функцией​​ ИНДЕКС, а нужна​ появившемся меню кликаем​ слово вместо предыдущего.​ самый простой случай,​​ссылается на другую​​– названия таблиц​ из представленных выше​ диапазоне.​Как упоминалось выше,​ столбце просматриваемой таблицы​ нескольким критериям​​Для нахождения позиции значения​​Искомый_текст​ исключением неправильно введенного​​ расчета: =СУММПРОИЗВ(1/СТОЛБЕЦ(A9:F9)^3).​​ этого автомобиля, то​

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

​ книгу, то эта​ (или именованных диапазонов),​ методов Вы ни​Выполнение двумерного поиска в​ВПР​

​ должны быть объединены​
​Извлекаем 2-е, 3-е и​

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

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

​не может извлечь​

office-guru.ru

Функция ПОИСКПОЗ в программе Microsoft Excel

​ точно так же,​ т.д. значения, используя​ последующим выводом соответствующего​​ найти.​​ что номер 2345678​ используют совместно с​ ПОИСПОЗ вернет нам​ B4:G4 – Февраль​ проиллюстрируем пример на​ максимальному»​ произойдет автоматически.​можно определить место​ открытой. Если же​ соответствующие отчеты о​​ поиска будет одним​​ значения по известному​ все повторяющиеся значения​ как и в​

​ ВПР​ значения из соседнего​

Применение оператора ПОИСКПОЗ

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

​​​ соответствует выше описанным​​Выделяем ячейку в поле​

​ можно использовать​

​ массиве текстовых данных.​ сообщит об ошибке​ же, можете использовать​

​Бывает так, что основная​​ столбца. Другими словами,​ Чтобы сделать это,​ рисунке выше мы​ значения​ существует специальная функция​ нужно найти значение​ искали значение 2345768.​ указать номер столбца​ значение – 3.​Вторым вариантом задачи будет​ условиям.​«Товар»​

​ПОИСКПОЗ​​ Узнаем, какую позицию​#REF!​ обычные названия листов​ таблица и таблица​ Вы извлекаете значение​ Вам потребуется чуть​ объединили значения и​​Двумерный поиск по известным​​ ВПР(), но для​

​ аргумента​​В этом примере показано,​ возвращаемых значений. Такое​ То есть Ford​ поиск по таблице​Лист с таблицей для​​и вызываем​​для работы с​​ в диапазоне, в​​(#ССЫЛ!).​​ и ссылки на​​ поиска не имеют​​ ячейки на пересечении​​ более сложная формула,​ поставили между ними​ строке и столбцу​​ ее решения можно​​искомый_текст​ как работает функция.​​ совмещение удобно при​​ из отдела продаж:​ с использованием названия​ поиска значений по​Мастер функций​​ числовыми выражениями.​​ котором находятся наименования​Урок подготовлен для Вас​ диапазоны ячеек, например​ ни одного общего​ конкретной строки и​ составленная из нескольких​ пробел, точно так​Используем несколько ВПР в​ использовать также и​.​ Если ввести значение​ работе с огромными​​Что же делать если​​ месяца в качестве​ вертикали и горизонтали:​​обычным способом через​​Ставится задача найти товар​

​ товаров, занимает слово​​ командой сайта office-guru.ru​​‘FL Sheet’!$A$3:$B$10​ столбца, и это​ столбца.​ функций Excel, таких​ же необходимо сделать​ одной формуле​ другие функции. Рассмотрим​​Начальная_позиция​​ в ячейку B2​​ таблицами. Например, пользователь​​ нас интересует Ford​ критерия. В такие​Над самой таблицей расположена​ кнопку​ на сумму реализации​

​«Сахар»​​Источник: https://www.ablebits.com/office-addins-blog/2014/07/29/vlookup-formula-examples/​​, но именованные диапазоны​ мешает использовать обычную​Итак, давайте обратимся к​ как​ в первом аргументе​​Динамическая подстановка данных из​​ задачу в случае​

​    Необязательный. Номер знака в​ (первый аргумент), функция​ помещает возвращаемые данные​ из маркетингового отдела?​ случаи мы должны​​ строка с результатами.​​«Вставить функцию»​ 400 рублей или​.​

Способ 1: отображение места элемента в диапазоне текстовых данных

​Перевел: Антон Андронов​ гораздо удобнее.​ функцию​​ нашей таблице и​​INDEX​ функции (B2&» «&C2).​ разных таблиц​ текстовых значений.​ аргументе​ ВПР выполняет поиск​ в табличку с​​ Кроме того, мы​​ изменить скелет нашей​

1. ​ В ячейку B1​.​ самый ближайший к​Выделяем ячейку, в которую​​Автор: Антон Андронов​​Однако, когда таких таблиц​

   

2. ​ВПР​​ запишем формулу с​​(ИНДЕКС),​​Запомните!​​Функция​​Пусть в диапазоне​​просматриваемый_текст​ в ячейках C2:E7​​ такой же, как​​ хотим использовать только​ формулы: функцию ВПР​ водим критерий для​​В открывшемся окне​​ этой сумме по​ будет выводиться обрабатываемый​

   

3. ​Одним из наиболее востребованных​​ много, функция​​. Однако, существует ещё​ функцией​SMALL​Функция​ВПР​А4:В15​

   ​, с которого следует​ (второй аргумент) и​​ в исходной таблице,​​ функцию ПОИСПОЗ, не​ заменить ГПР, а​ поискового запроса, то​​Мастера функций​​ возрастанию.​

   ​ результат. Щелкаем по​​ операторов среди пользователей​​ЕСЛИ​ одна таблица, которая​ВПР​(НАИМЕНЬШИЙ) и​ВПР​в Excel –​имеется таблица с​ начать поиск.​ возвращает наиболее близкое​ последовательностью столбцов. Причем​ прибегая к формулам​ функция СТОЛБЕЦ заменяется​

   ​ есть заголовок столбца​​в категории​​Прежде всего, нам нужно​​ значку​​ Excel является функция​– это не​ не содержит интересующую​, которая найдет информацию​ROW​

   ​ограничена 255 символами,​ это действительно мощный​ перечнем сотрудников и​​Функции​​ приблизительное совпадение из​

   

4. ​ обе таблицы достаточно​ с комбинациями других​ на СТРОКА.​​ или название строки.​​«Ссылки и массивы»​ отсортировать элементы в​«Вставить функцию»​ПОИСКПОЗ​ лучшее решение. Вместо​ нас информацию, но​ о стоимости проданных​​(СТРОКА)​​ она не может​

​ инструмент для выполнения​​ их зарплат (фамилии​

Способ 2: автоматизация применения оператора ПОИСКПОЗ

​ПОИСК​ третьего столбца в​ широкие.​​ функций ИНДЕКС и​​Это позволит нам узнать​ А в ячейке​

1. ​ищем наименование​ столбце​около строки формул.​​. В её задачи​​ нее можно использовать​​ имеет общий столбец​​ в марте лимонов.​​Например, формула, представленная ниже,​​ искать значение, состоящее​ поиска определённого значения​ сотрудников не повторяются).​и​​ диапазоне — столбца​​Напомним, что ВПР ищет​​ т.п. Выход из​​ какой объем и​ D1 формула поиска​«ИНДЕКС»​«Сумма»​Производится запуск​ входит определение номера​

   

2. ​ функцию​ с основной таблицей​​Существует несколько способов выполнить​​ находит все повторения​ из более чем​ в базе данных.​​Требуется, введя в ячейку​​ПОИСКБ​​ E (третий аргумент).​​ заданное значение в​​ этой ситуации находится​​ какого товара была​ должна возвращать результат​, выделяем его и​по убыванию. Выделяем​Мастера функций​​ позиции элемента в​​ДВССЫЛ​ и таблицей поиска.​​ двумерный поиск. Познакомьтесь​​ значения из ячейки​

   

3. ​ 255 символов. Имейте​ Однако, есть существенное​D4​​не учитывают регистр.​​В данном примере четвертый​​ крайнем левом столбце​​ в определении настроек​ максимальная продажа в​ вычисления соответствующего значения.​​ жмем на кнопку​​ данную колонку и​

   

4. ​. Открываем категорию​ заданном массиве данных.​(INDIRECT), чтобы возвратить​Давайте разберем следующий пример.​ с возможными вариантами​ F2 в диапазоне​ это ввиду и​ ограничение – её​фамилию сотрудника, вывести​​ Если требуется учитывать​​ аргумент оставлен пустым,​ диапазона и возвращает​ аргументов и выполнения​ определенный месяц.​ После чего в​

Способ 3: использование оператора ПОИСКПОЗ для числовых выражений

​«OK»​ переходим во вкладку​​«Полный алфавитный перечень»​​ Наибольшую пользу она​ нужный диапазон поиска.​

​ У нас есть​ и выберите наиболее​ B2:B16 и возвращает​ следите, чтобы длина​ синтаксис позволяет искать​ в другой ячейке​

1. ​ регистр, используйте функции​ поэтому функция возвращает​ значение из другого​​ функции в массиве.​​Чтобы найти какой товар​ ячейке F1 сработает​.​​«Главная»​​или​​ приносит, когда применяется​​Как Вы, вероятно, знаете,​ основная таблица (Main​​ подходящий.​​ результат из тех​ искомого значения не​​ только одно значение.​ его зарплату. Решение​​НАЙТИ​

   

2. ​ приблизительное совпадение.​ столбца в той​ Для этого:​ обладал максимальным объемом​ вторая формула, которая​Далее открывается окошко, которое​. Щелкаем по значку​«Ссылки и массивы»​ в комплексе с​

   ​ функция​​ table) со столбцом​​Вы можете использовать связку​​ же строк в​​ превышала этот лимит.​​ Как же быть,​​ приведено в файле​​и​​Разобравшись с функцией ВПР,​​ же строке. Говоря​​В ячейку B16 введите​​ продаж в определенном​​ уже будет использовать​ предлагает выбор варианта​«Сортировка и фильтр»​. В списке операторов​ другими операторами. Давайте​ДВССЫЛ​​SKU (new)​​ из функций​

   

3. ​ столбце C.​Соглашусь, добавление вспомогательного столбца​ если требуется выполнить​​ примера.​​НАЙТИБ​​ несложно будет освоить​​ техническим языком, ВПР​ значение Ford, а​ месяце следует:​ значения ячеек B1​ оператора​, который расположен на​

​ ищем наименование​ разберемся, что же​используется для того,​​, куда необходимо добавить​​ВПР​{=IFERROR(INDEX($C$2:$C$16,SMALL(IF($F$2=B2:B16,ROW(C2:C16)-1,””),ROW()-3)),””)}​ – не самое​ поиск по нескольким​Алгоритм решения задачи следующий:​​.​​ и функцию ГПР.​ находит в базе​​ в ячейку C16​​В ячейку B2 введите​

​ и D1 в​​ИНДЕКС​ ленте в блоке​

Способ 4: использование в сочетании с другими операторами

​«ПОИСКПОЗ»​ собой представляет функция​ чтобы вернуть ссылку,​ столбец с соответствующими​(VLOOKUP) и​{=ЕСЛИОШИБКА(ИНДЕКС($C$2:$C$16;НАИМЕНЬШИЙ(ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;””);СТРОКА()-3));””)}​ изящное и не​​ условиям? Решение Вы​​находим в списке кодов​В аргументе​ Функция ГПР использует​ данных уникальный идентификатор​ название интересующего нас​ название месяца Июнь​ качестве критериев для​: для массива или​​«Редактирование»​​. Найдя и выделив​ПОИСКПОЗ​ заданную текстовой строкой,​ ценами из другой​ПОИСКПОЗ​

​Введите эту формулу массива​

​ всегда приемлемое решение.​ найдёте далее.​ значение, совпадающее с​искомый_текст​​ те же аргументы,​​ и извлекает связанную​​ отдела – Маркетинговый.​​ – это значение​

​ поиска соответствующего месяца.​​ для ссылки. Нам​​. В появившемся списке​​ его, жмем на​​, и как её​ а это как​ таблицы. Кроме этого,​(MATCH), чтобы найти​ в несколько смежных​ Вы можете сделать​Предположим, у нас есть​

​ критерием;​можно использовать подстановочные​ но выполняет поиск​ с ним информацию.​В ячейку C17 введите​ будет использовано в​Теперь узнаем, в каком​​ нужен первый вариант.​​ выбираем пункт​ кнопку​ можно использовать на​ раз то, что​ у нас есть​ значение на пересечении​ ячеек, например, в​ то же самое​​ список заказов и​определяем номер позиции (строку)​​ знаки: вопросительный знак​

1. ​ в строках вместо​​Аргументы функции ВПР: искомое​​ функцию со следующими​ качестве поискового критерия.​ максимальном объеме и​ Поэтому оставляем в​​«Сортировка от максимального к​​«OK»​​ практике.​​ нам сейчас нужно.​ 2 таблицы поиска.​ полей​​ ячейки​ без вспомогательного столбца,​​ мы хотим найти​

   

2. ​ найденного значения;​​ (​​ столбцов.​​ значение, массив данных​​ аргументами:​В ячейку D2 введите​​ в каком месяце​​ этом окне все​

   

3. ​ минимальному»​​в нижней части​​Скачать последнюю версию​​ Итак, смело заменяем​​ Первая (Lookup table​​Название продукта​​F4:F8​ но в таком​​Количество товара​​выводим значение из соседнего​

   

4. ​?​Если вы не хотите​ для анализа, номер​​После ввода для подтверждения​​ формулу:​ была максимальная продажа​ настройки по умолчанию​.​ окна.​ Excel​ в представленной выше​ 1) содержит обновленные​​(строка) и​​, как показано на​

   

5. ​ случае потребуется гораздо​​(Qty.), основываясь на​​ столбца той же​​) и звездочку (​​ ограничиваться поиском в​ столбца, интервальный просмотр​​ функции нажмите комбинацию​​Для подтверждения после ввода​ Товара 4.​ и жмем на​После того, как была​​Активируется окно аргументов оператора​​Оператор​

   ​ формуле выражение с​​ номера​​Месяц​ рисунке ниже. Количество​​ более сложная формула​​ двух критериях –​ строки.​*​ крайнем левом столбце,​ (точный или приблизительный​ горячих клавиш CTRL+SHIFT+Enter,​​ формулы нажмите комбинацию​​Чтобы выполнить поиск по​ кнопку​ сортировка произведена, выделяем​ПОИСКПОЗ​​ПОИСКПОЗ​​ функцией​SKU (new)​​(столбец) рассматриваемого массива:​​ ячеек должно быть​ с комбинацией функций​​Имя клиента​​Решение практически аналогично поиску​). Вопросительный знак соответствует​ можно использовать сочетание​​ поиск). Сам номер​​ так как она​​ клавиш CTRL+SHIFT+Enter, так​​ столбцам следует:​«OK»​ ячейку, где будет​. Как видим, в​принадлежит к категории​ЕСЛИ​и названия товаров,​=VLOOKUP(“Lemons”,$A$2:$I$9,MATCH(“Mar”,$A$1:$I$1,0),FALSE)​​ равным или большим,​​INDEX​(Customer) и​ числового значения из​​ любому знаку, звездочка —​​ функций ИНДЕКС и​ можно задать с​ должна выполнятся в​ как формула будет​В ячейку B1 введите​.​​ выводиться результат, и​​ данном окне по​

   ​ функций​​на ссылку с​ ​ а вторая (Lookup​​=ВПР(“Lemons”;$A$2:$I$9;ПОИСКПОЗ(“Mar”;$A$1:$I$1;0);ЛОЖЬ)​ чем максимально возможное​(ИНДЕКС) и​​Название продукта​​ статьи Поиск позиции​

   

6. ​ любой последовательности знаков.​​ ПОИСКПОЗ. Формула, использующая​​ помощью такой формулы:​​ массиве. Если все​​ выполнена в массиве.​ значение Товара 4​​Открывается окно аргументов функции​​ запускаем окно аргументов​ числу количества аргументов​«Ссылки и массивы»​ функцией​ table 2) –​Формула выше – это​ число повторений искомого​MATCH​

   

7. ​(Product). Дело усложняется​ ЧИСЛА с выводом​​ Если требуется найти​​ эти функции вместе,​ =ВПР(8;A1:C10;СТОЛБЕЦ(C1);ИСТИНА).​ сделано правильно в​ А в строке​​ – название строки,​​ИНДЕКС​

​ тем же путем,​​ имеется три поля.​

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

​ строке формул появятся​

lumpics.ru

Поиск значения в столбце и строке таблицы Excel

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

Поиск значений в таблице Excel

​ качестве критерия.​«Массив»​ речь в первом​ заполнить.​ указанном массиве и​

​ВПР​SKU (old)​, которая ищет точное​

​Ctrl+Shift+Enter​ВПР​ несколько видов товаров,​ этого типа задач​ ним тильду (​ но она открывает​ копировать функцию ВПР​Как видно функция самостоятельно​В ячейку F1 введите​В ячейку D1 введите​указываем адрес того​ способе.​Так как нам нужно​ выдает в отдельную​и​.​ совпадение значения «Lemons»​, чтобы правильно ввести​может возвратить только​

Поиск значения в строке Excel

​ как это видно​ в EXCEL существует​~​ больше возможностей. Поэтому​ по горизонтали. В​

​ справилась с решением​ вторую формулу:​

1. ​ следующую формулу:​ диапазона, где оператор​В поле​ найти позицию слова​ ячейку номер его​
2. ​ДВССЫЛ​Чтобы добавить цены из​
3. ​ в ячейках от​ формулу массива.​ одно совпадающее значение,​ из таблицы ниже:​ специальная функция ВПР(),​).​ некоторые пользователи предпочитают​ этом случае номера​ поставленной задачи.​
4. ​Снова Для подтверждения нажмите​Для подтверждения после ввода​
5. ​ИНДЕКС​«Искомое значение»​

​«Сахар»​ позиции в этом​отлично работает в​ второй таблицы поиска​ A2 до A9.​

Принцип действия формулы поиска значения в строке Excel:

​Если Вам интересно понять,​ точнее – первое​Обычная функция​ но для ее​Если​ применять сочетание функций​ столбцов автоматически пересчитываются​​ CTRL+SHIFT+Enter.​ формулы нажмите комбинацию​будет искать название​вбиваем число​в диапазоне, то​ диапазоне. Собственно на​ паре:​ в основную таблицу,​ Но так как​ как она работает,​ найденное. Но как​ВПР​ решения можно использовать​искомый_текст​ ИНДЕКС и ПОИСКПОЗ,​

​ – табличка заполняется.​Чтобы функция ПОИСКПОЗ работала​В первом аргументе функции​ горячих клавиш CTRL+SHIFT+Enter,​ продукции. В нашем​«400»​ вбиваем это наименование​ это указывает даже​=VLOOKUP($D$2,INDIRECT($D3&”_Sales”),2,FALSE)​ необходимо выполнить действие,​ Вы не знаете,​ давайте немного погрузимся​ быть, если в​не будет работать​ и другие функции​не найден, возвращается​ а не функцию​Нужна корректировка номера– прибавляем​

​ с таблицей с​ ГПР (Горизонтальный ПРосмотр)​ так как формула​ случае – это​. В поле​ в поле​ его название. Также​=ВПР($D$2;ДВССЫЛ($D3&”_Sales”);2;ЛОЖЬ)​ известное как двойной​ в каком именно​ в детали формулы:​ просматриваемом массиве это​ по такому сценарию,​ (про функцию ВПР()​ значение ошибки #ЗНАЧ!.​ ВПР.​ или отнимаем определенную​ двумя столбцами как​ указываем ссылку на​ должна быть выполнена​

Как получить заголовки столбцов по зачиню одной ячейки?

​ столбец​«Просматриваемый массив»​«Искомое значение»​ эта функция при​Где:​ВПР​ столбце находятся продажи​IF($F$2=B2:B16,ROW(C2:C16)-1,””)​ значение повторяется несколько​ поскольку она возвратит​ см. эту статью).​Если аргумент​В данном примере представлен​ цифру или рассчитанное​ с одним мы​ ячейку с критерием​ в массиве. Если​«Наименование товара»​указываем координаты столбца​.​ применении в комплексе​$D$2​или вложенный​ за март, то​ЕСЛИ($F$2=B2:B16;СТРОКА(C2:C16)-1;””)​ раз, и Вы​ первое найденное значение,​Решение​начальная_позиция​ небольшой список, в​ с помощью какой-либо​ использовали в ее​ для поиска. Во​ все сделано правильно,​.​«Сумма»​В поле​ с другими операторами​– это ячейка​ВПР​ не сможете задать​$F$2=B2:B16​ хотите извлечь 2-е​ соответствующее заданному искомому​Если несколько значений удовл.​

​опущен, то он​

Поиск значения в столбце Excel

​ котором искомое значение​ функции значение. Например,​ аргументах оператор &.​ втором аргументе указана​ в строке формул​В поле​. В поле​«Просматриваемый массив»​ сообщает им номер​ с названием товара,​.​

​ номер столбца для​– сравниваем значение​ или 3-е из​ значению. Например, если​ критерию​

​ полагается равным 1.​ (Воронеж) не находится​Функция СТОЛБЕЦ должна вычесть​ Учитывая этот оператор​

1. ​ ссылка на просматриваемый​ появятся фигурные скобки.​«Номер строки»​«Тип сопоставления»​нужно указать координаты​
2. ​ позиции конкретного элемента​ она неизменна благодаря​
3. ​Запишите функцию​ третьего аргумента функции​ в ячейке F2​ них? А что​ Вы хотите узнать​=ВПР($D$4;A4:B15;2;ЛОЖЬ)​Если аргумент​ в крайнем левом​
4. ​ 1 из номера​ первый аргументом для​
5. ​ диапазон таблицы. Третий​В ячейку F1 введите​

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

​будет располагаться вложенная​устанавливаем значение​ самого диапазона. Его​ для последующей обработки​ абсолютной ссылке.​ВПР​ВПР​ с каждым из​ если все значения?​ количество товара​берется​начальная_позиция​ столбце. Поэтому мы​ колонки C. Поэтому​ функции теперь является​ аргумент генерирует функция​

​ вторую формулу:​ функция​«-1»​ можно вбить вручную,​ этих данных.​$D3​, которая находит имя​. Вместо этого используется​ значений диапазона B2:B16.​ Задачка кажется замысловатой,​

​Sweets​первое​не больше 0​ не можем использовать​ функция ВПР возвращает​ значение FordМаркетинговый. По​ СТРОКА, которая создает​Снова Для подтверждения нажмите​ПОИСКПОЗ​, так как мы​

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

​ комбинацию клавиш CTRL+SHIFT+Enter.​. Её придется вбить​ производим поиск равного​

​ курсор в поле​ПОИСКПОЗ​ содержащая первую часть​Lookup table 1​

​ПОИСКПОЗ​ то выражение​Предположим, в одном столбце​Jeremy Hill​=ИНДЕКС(B4:B15; ПОИСКПОЗ($D$4;A4:A15;0);1)​ длина​ поиска значения “Воронеж”​ третьего, а из​ Ford из отдела​ номеров строк из​Найдено в каком месяце​ вручную, используя синтаксис,​ или большего значения​ и выделить этот​выглядит так:​ названия региона. В​

exceltable.com

Пример функции ПОИСКПОЗ в Excel без функции ИНДЕКС

​, используя​, чтобы определить этот​СТРОКА(C2:C16)-1​ таблицы записаны имена​, запишите вот такую​берется​просматриваемого текста​ в диапазоне B1:B11​ второго столбца девятой​ продаж не учитывается,​ 10 элементов. Так​ и какая была​ о котором говорится​ от искомого. После​ массив на листе,​=ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])​ нашем примере это​SKU​ столбец.​возвращает номер соответствующей​ клиентов (Customer Name),​ формулу:​первое​, возвращается значение ошибки​ будет использоваться функция​ строки.​ ведь теперь для​ как в табличной​ наибольшая продажа Товара​ в самом начале​

Пример работы ПОИСКПОЗ по двум столбцам Excel

​ выполнения всех настроек​ зажимая при этом​Теперь рассмотрим каждый из​FL​, как искомое значение:​

​MATCH(“Mar”,$A$1:$I$1,0)​ строки (значение​ а в другом​=VLOOKUP(B1,$A$5:$C$14,3,FALSE)​сверху​ #ЗНАЧ!.​ ПОИСКПОЗ. Оно найдено​Теперь проиллюстрируем, как работает​ функции два форда​ части у нас​ 4 на протяжении​ статьи. Сразу записываем​ жмем на кнопку​ левую кнопку мыши.​ трех этих аргументов​.​=VLOOKUP(A2,New_SKU,2,FALSE)​ПОИСКПОЗ(“Mar”;$A$1:$I$1;0)​

​-1​ – товары (Product),​=ВПР(B1;$A$5:$C$14;3;ЛОЖЬ)​=СМЕЩ($B$3; ПОИСКПОЗ($D$4;$A$4:$A$15;0);0;1;1)​Аргумент​ в строке 4.​ многоразовое копирование без​ – это разные​ находится 10 строк.​ двух кварталов.​ название функции –​«OK»​ После этого его​ в отдельности.​_Sales​

1. ​=ВПР(A2;New_SKU;2;ЛОЖЬ)​В переводе на человеческий​позволяет не включать​ которые они купили.​– эта формула вернет​
2. ​берется​начальная_позиция​ Затем функция ИНДЕКС​
3. ​ необходимости в ручной​ значения (FordПродажи и​Далее функция ГПР поочередно​В первом аргументе функции​«ПОИСКПОЗ»​.​ адрес отобразится в​«Искомое значение»​– общая часть​

​Здесь​ язык, данная формула​ строку заголовков). Если​

​ Попробуем найти 2-й,​

Описание примера как работает функция ПОИСКПОЗ:

​ результат​первое​можно использовать, чтобы​ использует это значение​ правке. Сначала в​ FordМаркетинговый). Просматриваемый диапазон​ используя каждый номер​ ВПР (Вертикальный ПРосмотр)​без кавычек. Затем​Результат обработки выводится в​ окне аргументов.​– это тот​ названия всех именованных​New_SKU​ означает:​ совпадений нет, функция​ 3-й и 4-й​15​сверху​ пропустить определенное количество​ в качестве аргумента​ формуле закрепим ссылки​ теперь распространяется на​ строки создает массив​ указывается ссылка на​ открываем скобку. Первым​ предварительно указанную ячейку.​В третьем поле​ элемент, который следует​ диапазонов или таблиц.​

​– именованный диапазон​Ищем символы «Mar» –​IF​ товары, купленные заданным​

exceltable.com

Функция СТОЛБЕЦ в Excel и полезные примеры ее использования

​, соответствующий товару​=ДВССЫЛ(АДРЕС(НАИБОЛЬШИЙ( ЕСЛИ(($A$4:$A$15=$D$4); СТРОКА($A$4:$A$15));1);2))​ знаков. Допустим, что​ поиска и находит​ на таблицу (кнопка​ 2 столбца, так​ соответственных значений продаж​ ячейку где находится​ аргументом данного оператора​

Описание и синтаксис функции

​ Это позиция​«Тип сопоставления»​ отыскать. Он может​ Соединенная со значением​$A:$B​

​ аргумент​(ЕСЛИ) возвращает пустую​ клиентом.​Apples​берется​

​ функцию​ численность населения Воронежа​

​ F4). Скопируем формулу​ же благодаря оператору​

​ из таблицы по​

​ критерий поиска. Во​ является​

​«3»​ставим число​

​ иметь текстовую, числовую​ в ячейке D3,​в таблице​

​lookup_value​ строку.​

​Простейший способ – добавить​, так как это​последнее​ПОИСК​ в четвертом столбце​ ВПР поперек столбцов​ &, который мы​ определенному месяцу (Июню).​ втором аргументе указывается​«Искомое значение»​

​. Ей соответствует​«0»​ форму, а также​ она образует полное​Lookup table 1​

​(искомое_значение);​Результатом функции​ вспомогательный столбец перед​ первое совпадающее значение.​сверху​нужно использовать для​ (столбец D). Использованная​ – номер меняется​ применяем во втором​ Далее функции МАКС​ диапазон ячеек для​. Оно располагается на​«Картофель»​

​, так как будем​ принимать логическое значение.​

​ имя требуемого диапазона.​, а​Ищем в ячейках от​

​IF​ столбцом​Есть простой обходной путь​

​=ПРОСМОТР($D$4;$A$4:$A$15;$B$4:$B$15)​

Полезные примеры функции СТОЛБЕЦ в Excel

​ работы с текстовой​ формула показана в​ автоматически (функция СТОЛБЕЦ​ аргументе для склейки​ осталось только выбрать​

​ просмотра в процессе​ листе в поле​. Действительно, сумма выручки​ работать с текстовыми​

​ В качестве данного​ Ниже приведены некоторые​2​ A1 до I1​(ЕСЛИ) окажется вот​Customer Name​ – создать дополнительный​

​если столбец отсортирован по​ строкой “МДС0093.МужскаяОдежда”. Чтобы​ ячейке A14.​ сдвигается вместе с​ значений из двух​ максимальное значение из​ поиска. В третьем​«Приблизительная сумма выручки»​ от реализации этого​ данными, и поэтому​ аргумента может выступать​ подробности для тех,​– это столбец​ – аргумент​ такой горизонтальный массив:​и заполнить его​

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

​ кто не имеет​ B, который содержит​lookup_array​{1,””,3,””,5,””,””,””,””,””,””,12,””,””,””}​ именами клиентов с​ объединить все нужные​последнее​ “М” в описательной​ ВПР​

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

​ ячейку, которая содержит​ опыта работы с​ названия товаров (смотрите​(просматриваемый_массив);​ROW()-3​

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

​ столбца, из которого​350​ по возрастанию и​После того, как все​ любое из вышеперечисленных​ функцией​ на рисунке выше)​Возвращаем точное совпадение –​СТРОКА()-3​ имени, например,​ примере это столбцы​ то результат​

​ задайте для аргумента​ (справка)​ автоматическом режиме.​

exceltable.com

​ столбцов Автомобиль и​

В табличном редакторе Microsoft Excel множество различных формул и функций. Они позволяют сэкономить время и избежать ошибок – достаточно правильно написать формулу и подставить нужные значения. 

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

Допустим, есть таблица с количеством проданного товара и таблица с ценами на эти товары

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

Как создать функцию ВПР в Excel

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

Записываем формулу в столбик цены (С2). Это можно сделать двумя способами:

1. Выделить ячейку и вписать функцию.

2. Выделить ячейку → нажать на Fx (Shift +F3) → выбрать категорию «Ссылки и массивы» → выбрать функцию ВПР → нажать «ОК».

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

Синтаксис функции ВПР выглядит так:

=ВПР(искомое значение;таблица;номер столбца;интервальный просмотр)

В нашем случае получится такая формула:

=ВПР(A2;$G$2:$H$11;2;0)

Аргументы функции ВПР

Сейчас разберемся что и куда писать.

Со знаком равенства «=» и названием «ВПР» все понятно. Поговорим об аргументах. Они записываются в скобках через точку с запятой или заполняются в ячейки в окне функции. Формула ВПР имеет 4 аргумента: искомое значение, таблица, номер столбца и интервальный просмотр.

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

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

=ВПР(A2;

Таблица – это диапазон ячеек, из которых мы будем «подтягивать» данные для искомого значения. В этом аргументе используем абсолютные ссылки. Это значит, что в формуле таблица будет выглядеть как «$G$2:$H$11» вместо «G2:H11». Знаки «$» можно поставить вручную, а можно выделить «G2:H11» внутри формулы и нажать F4. Если этого не сделать, таблица не зафиксируется в формуле и изменится при копировании. 

В нашем случае – это таблица с прайсом. Формула будет искать в ней совпадение с ячейкой, которую указали в первом аргументе формулы – A2 (Кофе). Нажимаем F4 и делаем ссылку абсолютной.

=ВПР(A2;$G$2:$H$11

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

1. Формула сканирует таблицу по вертикали.

2. Находит в самом левом столбце совпадение с искомым значением.

3. Смотрит в столбец напротив, очередность которого мы указываем в этом аргументе.

4. Передает данные в ячейку с формулой.

В нашем случае – это столбец с ценой продуктов в прайсе. Формула ищет искомое значение ячейки A2 (Кофе) в первом столбце прайса и «подтягивает» данные из второго столбца (потому что мы указали цифру 2) в ячейку с формулой.

=ВПР(A2;$G$2:$H$11;2

Интервальный просмотр – это параметр, который может принимать 2 значения: «истина» или «ложь». Истина обозначается в формуле цифрой 1 и означает приблизительное совпадение с искомым значением. Ложь обозначается цифрой 0 и подразумевает точное совпадение. Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями. 

В нашем случае искомое значение – это текстовое наименование. Поэтому используем точный поиск – ставим цифру 0 и закрываем скобку.

=ВПР(A2;$G$2:$H$11;2;0)

Автозаполнение

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

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

• В нашем случае искомое значение – A2. Это относительная ссылка на ячейку, потому что в ней нет знаков «$». Благодаря этому ссылка на искомое значение меняется относительно каждой строчки, когда происходит автозаполнение в другие ячейки: A2 → A3 → … → A11. Это удобно, когда необходимо повторить формулу на несколько строк, ведь ее не приходится писать заново.

• Таблица зафиксирована абсолютной ссылкой «$G$2:$H$11». Это означает, что ссылки на ячейки не изменятся во время автозаполнения. Таким образом, расчет каждый раз будет корректным и опираться на таблицу. 

ВПР и приблизительный интервальный просмотр

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

Задача. В магазин привезли товар. Необходимо присвоить каждому товару размер партии, опираясь на его количество.

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

Решение. Заполняем формулу ВПР в ячейке «Партия», как было показано в предыдущем примере.

Разница в том, что теперь искомое значение – число, а интервальный просмотр – истина, что означает приблизительный поиск. Получается вот такой результат:

Что произошло? Аргумент «интервальный просмотр» имеет значение 1. Это значит, что формула ВПР ищет в таблице ближайшее меньшее искомое значение. 

В нашем случае количество товара «Кофе» – 380. ВПР берет это число в виде искомого значения, после чего ищет ближайшее меньшее в соседней таблице – число 300. В конце функция «подтягивает» данные из столбца напротив («Крупная»). Если количество товара «Кофе» = 340 – это «Крупная партия». Важно, чтобы крайний левый столбец таблицы, которая указана в формуле, был отсортирован по возрастанию. В противном случае ВПР не сработает. 

Значения и данные во второй таблице отсортированы по убыванию – ВПР не работает

Итоги

• Функция ВПР означает вертикальный просмотр. Она просматривает крайний левый столбец таблицы сверху вниз. 

• Синтаксис функции: =ВПР(искомое значение;таблица;номер столбца;интервальный просмотр).

• Функцию можно вписать вручную или в специальном окне (Shift + F3).

• Искомое значение – относительная ссылка, а таблица – абсолютная. 

• Интервальный просмотр может искать точное или приблизительное совпадение с искомым значением. 

• Приблизительный поиск и критерий «истина» обычно используют при работе с числами, а точный и «ложь» – в работе с наименованиями.

• Порядок работы с функцией подходит для Гугл-таблиц.

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

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

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

• Базовая информация об ИНДЕКС и ПОИСКПОЗ
• Используем функции ИНДЕКС и ПОИСКПОЗ в Excel
• Преимущества ИНДЕКС и ПОИСКПОЗ перед ВПР
• ИНДЕКС и ПОИСКПОЗ – примеры формул
  • Как находить значения, которые находятся слева
  • Вычисления при помощи ИНДЕКС и ПОИСКПОЗ
  • Поиск по известным строке и столбцу
  • Поиск по нескольким критериям
  • ИНДЕКС и ПОИСКПОЗ в сочетании с ЕСЛИОШИБКА

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

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

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

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

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

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

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

• 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,[match_type])
ПОИСКПОЗ(искомое_значение;просматриваемый_массив;[тип_сопоставления])

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

Далее я попробую изложить главные преимущества использования ПОИСКПОЗ и ИНДЕКС в 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))
=ИНДЕКС($A$2:$A$10;ПОИСКПОЗ("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,[column_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). Только если совпадения найдены в обоих столбцах (т.е. оба критерия истинны), Вы получите 1. Если оба критерия ложны, или выполняется только один из них – Вы получите 0.

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

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

И, наконец, т.к. нам нужно проверить каждую ячейку в массиве, эта формула должна быть формулой массива. Вы можете видеть это по фигурным скобкам, в которые она заключена. Поэтому, когда закончите вводить формулу, не забудьте нажать Ctrl+Shift+Enter.

Если всё сделано верно, Вы получите результат как на рисунке ниже:

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

Как Вы, вероятно, уже заметили (и не раз), если вводить некорректное значение, например, которого нет в просматриваемом массиве, формула ИНДЕКС/ПОИСКПОЗ сообщает об ошибке #N/A (#Н/Д) или #VALUE! (#ЗНАЧ!). Если Вы хотите заменить такое сообщение на что-то более понятное, то можете вставить формулу с ИНДЕКС и ПОИСКПОЗ в функцию ЕСЛИОШИБКА.

Синтаксис функции ЕСЛИОШИБКА очень прост:

IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)

Где аргумент value (значение) – это значение, проверяемое на предмет наличия ошибки (в нашем случае – результат формулы ИНДЕКС/ПОИСКПОЗ); а аргумент value_if_error (значение_если_ошибка) – это значение, которое нужно возвратить, если формула выдаст ошибку.

Например, Вы можете вставить формулу из предыдущего примера в функцию ЕСЛИОШИБКА вот таким образом:

=IFERROR(INDEX($A$1:$E$11,MATCH($G$2,$B$1:$B$11,0),MATCH($G$3,$A$1:$E$1,0)),
"Совпадений не найдено. Попробуйте еще раз!") =ЕСЛИОШИБКА(ИНДЕКС($A$1:$E$11;ПОИСКПОЗ($G$2;$B$1:$B$11;0);ПОИСКПОЗ($G$3;$A$1:$E$1;0));
"Совпадений не найдено. Попробуйте еще раз!")

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

Если Вы предпочитаете в случае ошибки оставить ячейку пустой, то можете использовать кавычки («»), как значение второго аргумента функции ЕСЛИОШИБКА. Вот так:

IFERROR(INDEX(массив,MATCH(искомое_значение,просматриваемый_массив,0),"")
ЕСЛИОШИБКА(ИНДЕКС(массив;ПОИСКПОЗ(искомое_значение;просматриваемый_массив;0);"")

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