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

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

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

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

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

Первый аргумент (часть данных, которая обеспечивает работу функции) — это искомое значение. Это может быть ссылка на ячейку или конкретное значение, например «кузнецова» или 21 000. Второй аргумент — это диапазон ячеек, который, на ваш взгляд, содержит искомое значение. В нашем примере это диапазон C2–C7. Третий аргумент — это столбец в диапазоне ячеек, содержащий значение, которое нужно найти.

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

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

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

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

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

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

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

ВПР в действии

Скопируйте всю таблицу и вставьте ее в ячейку A1 пустого листа 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 в той же строке.

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

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

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

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

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

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

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

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

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

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

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

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

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

ГПР в действии

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

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

Оси

Подшипники

Болты

4

4

9

5

7

10

6

8

11

Формула

Описание

Результат

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ИНДЕКС и ПОИСКПОЗ в действии

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

Скопируйте всю таблицу и вставьте ее в ячейку 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

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

К началу страницы

Примечания : 

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

  • Используйте английский вариант этой статьи, который находится здесь, в качестве справочного материала. Вместе с другими участниками сообщества Майкрософт вы можете улучшить эту статью с помощью Community Translation Framework (CTF). Просто наведите указатель мыши на предложение в статье и выберите команду "УЛУЧШИТЬ ПЕРЕВОД" в мини-приложении CTF. Для получения дополнительных сведений о CTF щелкните здесь. Используя CTF, вы соглашаетесь с нашими условиями предоставления услуг.

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

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

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

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

×