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

Совет: Попробуйте использовать новые функции кслукуп и ксматч с улучшенными версиями функций, описанными в этой статье. Эти новые функции работают в любом направлении и возвращают точное совпадение по умолчанию, упрощая и удобнее в использовании, чем их предшественники.

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

Функции ВПР и ГПР вместе с функцией индекс и ПОИСКПОЗявляются наиболее полезными функциями в Excel.

Примечание: Функция "Мастер подстановок" больше не доступна в Excel.

Ниже приведен пример использования функции ВПР.

=ВПР(B2;C2:E7,3,ИСТИНА)

В этом примере ячейка B2 является первым аргументом— элементом данных, для работы которого требуется функция. Для функции ВПР первым аргументом является значение, которое нужно найти. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, таким как "Иванов" или 21 000. Второй аргумент — это диапазон ячеек (C2-: E7, в котором нужно найти искомое значение. Третьим аргументом является столбец в диапазоне ячеек, который содержит искомое значение.

Четвертый аргумент необязателен. Введите значение истина или ложь. Если ввести ИСТИНА или оставить аргумент пустым, функция возвращает приблизительное совпадение значения, указанного в качестве первого аргумента. Если ввести значение ложь, функция будет соответствовать значению, представленному первым аргументом. Другими словами, если оставить четвертый аргумент пустым — или ввести значение истина, вы получаете более гибкие возможности.

В этом примере показано, как работает функция. Когда вы вводите значение в ячейке B2 (первый аргумент), функция ВПР ищет ячейки в диапазоне C2: E7 (второй аргумент) и возвращает ближайшее приближенное соответствие из третьего столбца в диапазоне, столбец E (Третий аргумент).

Типичный пример использования функции ВПР

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

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

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

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

В данном примере представлен небольшой список, в котором искомое значение (Воронеж) не находится в крайнем левом столбце. Поэтому мы не можем использовать функцию ВПР. Для поиска значения "Воронеж" в диапазоне B1:B11 будет использоваться функция ПОИСКПОЗ. Оно найдено в строке 4. Затем функция ИНДЕКС использует это значение в качестве аргумента поиска и находит численность населения Воронежа в четвертом столбце (столбец D). Использованная формула показана в ячейке A14.

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

Дополнительные примеры использования функции индекс и MATCH вместо функции ВПР можно найти в статье https://www.MrExcel.com/Excel-Tips/Excel-VLOOKUP-index-Match/ by Bill Джилена, Microsoft MVP.

Попробуйте попрактиковаться

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

Пример функции ВПР на работе

Скопируйте указанные ниже данные в пустой лист.

Совет: Перед вставкой данных в Excel задайте ширину столбцов для столбцов A — 250 пикселей и нажмите кнопку Перенос текста (вкладкаГлавная , группа Выравнивание ).

Плотность

Вязкость

Температура

0,457

3,55

500

0,525

3,25

400

0,606

2,93

300

0,675

2,75

250

0,746

2,57

200

0,835

2,38

150

0,946

2,17

100

1,09

1,95

50

1,29

1,71

0

Формула

Описание

Результат

=ВПР(1,A2:C10,2)

Используя приблизительное соответствие, функция ищет в столбце A значение 1, находит наибольшее значение, которое меньше или равняется 1 и составляет 0,946, а затем возвращает значение из столбца B в той же строке.

2,17

=ВПР(1,A2:C10,3,ИСТИНА)

Используя приблизительное соответствие, функция ищет в столбце A значение 1, находит наибольшее значение, которое меньше или равняется 1 и составляет 0,946, а затем возвращает значение из столбца C в той же строке.

100

=ВПР(0,7,A2:C10,3,ЛОЖЬ)

Используя точное соответствие, функция ищет в столбце A значение 0,7. Поскольку точного соответствия нет, возвращается сообщение об ошибке.

#Н/Д

=ВПР(0,1,A2:C10,2,ИСТИНА)

Используя приблизительное соответствие, функция ищет в столбце A значение 0,1. Поскольку 0,1 меньше наименьшего значения в столбце A, возвращается сообщение об ошибке.

#Н/Д

=ВПР(2,A2:C10,2,ИСТИНА)

Используя приблизительное соответствие, функция ищет в столбце A значение 2, находит наибольшее значение, которое меньше или равняется 2 и составляет 1,29, а затем возвращает значение из столбца B в той же строке.

1,71

Пример функции ГПР

Скопируйте всю таблицу и вставьте ее в ячейку A1 пустого листа Excel.

Совет: Перед вставкой данных в Excel задайте ширину столбцов для столбцов A — 250 пикселей и нажмите кнопку Перенос текста (вкладкаГлавная , группа Выравнивание ).

Оси

Подшипники

Болты

4

4

9

5

7

10

6

8

11

Формула

Описание

Результат

=ГПР("Оси";A1:C4;2;ИСТИНА)

Поиск слова "Оси" в строке 1 и возврат значения из строки 2, находящейся в том же столбце (столбец A).

4

=ГПР("Подшипники";A1:C4;3;ЛОЖЬ)

Поиск слова "Подшипники" в строке 1 и возврат значения из строки 3, находящейся в том же столбце (столбец B).

7

=ГПР("П";A1:C4;3;ИСТИНА)

Поиск буквы "П" в строке 1 и возврат значения из строки 3, находящейся в том же столбце. Так как "П" найти не удалось, возвращается ближайшее из меньших значений: "Оси" (в столбце A).

5

=ГПР("Болты";A1:C4;4)

Поиск слова "Болты" в строке 1 и возврат значения из строки 4, находящейся в том же столбце (столбец C).

11

=ГПР(3;{1;2;3:"a";"b";"c";"d";"e";"f"};2;ИСТИНА)

Поиск числа 3 в трех строках константы массива и возврат значения из строки 2 того же (в данном случае — третьего) столбца. Константа массива содержит три строки значений, разделенных точкой с запятой (;). Так как "c" было найдено в строке 2 того же столбца, что и 3, возвращается "c".

c

Примеры ИНДЕКСов и СОВПАДЕНИй

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

Скопируйте всю таблицу и вставьте ее в ячейку A1 пустого листа Excel.

Совет: Перед вставкой данных в Excel задайте ширину столбцов для столбцов от A до 250 пикселей и нажмите кнопку Перенос текста (вкладкаГлавная , группа Выравнивание ).

Счет

Город

Дата выставления счета

Счет с самой ранней датой по городу, с датой

3115

Коломна

07.04.12

="Казань = "&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ("Казань",$B$2:$B$33,0),1)& ", Дата выставления счета: " & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ("Казань",$B$2:$B$33,0),3),"m/d/yy")

3137

Коломна

09.04.12

="Орел = "&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ("Орел",$B$2:$B$33,0),1)& ", Дата выставления счета: " & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ("Орел",$B$2:$B$33,0),3),"m/d/yy")

3154

Коломна

11.04.12

="Челябинск = "&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ("Челябинск",$B$2:$B$33,0),1)& ", Дата выставления счета: " & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ("Челябинск",$B$2:$B$33,0),3),"m/d/yy")

3191

Коломна

21.04.12

="Нижний Новгород = "&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ("Нижний Новгород",$B$2:$B$33,0),1)& ", Дата выставления счета: " & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ("Нижний Новгород",$B$2:$B$33,0),3),"m/d/yy")

3293

Коломна

25.04.12

="Москва = "&ИНДЕКС($A$2:$C$33,ПОИСКПОЗ("Москва",$B$2:$B$33,0),1)& ", Дата выставления счета: " & ТЕКСТ(ИНДЕКС($A$2:$C$33,ПОИСКПОЗ("Москва",$B$2:$B$33,0),3),"m/d/yy")

3331

Коломна

27.04.12

3350

Коломна

28.04.12

3390

Коломна

01.05.12

3441

Коломна

02.05.12

3517

Казань

08.05.12

3124

Остин

09.04.12

3155

Остин

11.04.12

3177

Остин

19.04.12

3357

Остин

28.04.12

3492

Орел

06.05.12

3316

Dallas (Даллас)

25.04.12

3346

Dallas (Даллас)

28.04.12

3372

Челябинск

01.05.12

3414

Челябинск

01.05.12

3451

Dallas (Даллас)

02.05.12

3467

Dallas (Даллас)

02.05.12

3474

Dallas (Даллас)

04.05.12

3490

Dallas (Даллас)

05.05.12

3503

Челябинск

08.05.12

3151

Новый Орлеан

09.04.12

3438

Новый Орлеан

02.05.12

3471

Нижний Новгород

04.05.12

3160

Омск

18.04.12

3328

Омск

26.04.12

3368

Омск

29.04.12

3420

Омск

01.05.12

3501

Москва

06.05.12

См. также

Краткий справочник:

функции автоподстановки и справки (ссылка)

используйте аргумент table_array в функции ВПР

Нужна дополнительная помощь?

Совершенствование навыков работы с Office
Перейти к обучению
Первоочередный доступ к новым возможностям
Присоединиться к программе предварительной оценки Office

Были ли сведения полезными?

Спасибо за ваш отзыв!

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

×