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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Пример функции ВПР в действии

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

Совет:    Прежде чем вставлять данные в 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 – D ширину в 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

Челябинск

25.04.12

3346

Челябинск

28.04.12

3372

Челябинск

01.05.12

3414

Челябинск

01.05.12

3451

Челябинск

02.05.12

3467

Челябинск

02.05.12

3474

Челябинск

04.05.12

3490

Челябинск

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

Дополнительные сведения о функциях поиска

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

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

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

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

×