Совет
Попробуйте использовать новые функции ПРОСМОТРX и ПОИСКПОЗX , улучшенные версии функций, описанных в этой статье. Эти новые функции работают в любом направлении и по умолчанию возвращают точные совпадения, что делает их проще и удобнее в использовании, чем их предшественников.
Предположим, что у вас есть список номеров офисов, и вам нужно знать, какие сотрудники находятся в каждом офисе. Электронная таблица огромна, поэтому может показаться, что это сложная задача. На самом деле это довольно легко сделать с помощью функции поиска.
Функции ВПР и ГПР , а также ИНДЕКС и ПОИСКПОЗ — одни из самых полезных функций в Excel.
Примечание
Функция "Мастер подстановок" больше не доступна в Excel.
Вот пример использования функции ВПР.
=ВПР(B2;C2:E7,3,ИСТИНА)
В этом примере B2 является первым аргументом — элементом данных, который необходим функции для работы. Для функции ВПР этот первый аргумент является значением, которое необходимо найти. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, таким как "иванов" или 21 000. Второй аргумент — это диапазон ячеек C2-:E7, в котором необходимо искать искомое значение. Третий аргумент — это столбец в диапазоне ячеек, содержащий искомое значение.
Четвертый аргумент необязателен. Введите значение ИСТИНА или ЛОЖЬ. Если ввести ИСТИНА или оставить аргумент пустым, функция возвращает приблизительное совпадение значения, указанного в качестве первого аргумента. Если ввести значение ЛОЖЬ, функция будет соответствовать значению, указанному в первом аргументе. Другими словами, если оставить четвертый аргумент пустым или ввести значение ИСТИНА, это обеспечит большую гибкость.
В этом примере показано, как работает функция. При вводе значения в ячейку B2 (первый аргумент) функция ВПР выполняет поиск в ячейках в диапазоне C2:E7 (второй аргумент) и возвращает ближайшее приблизительное совпадение из третьего столбца диапазона, столбца E (третий аргумент).
Четвертый аргумент пуст, поэтому функция возвращает приблизительное совпадение. Иначе потребуется ввести одно из значений в столбец C или D, чтобы получить какой-либо результат.
Когда вы знакомы с функцией ВПР, функция ГПР также проста в использовании. Вы вводите те же аргументы, но поиск производится в строках, а не в столбцах.
Использование функций ИНДЕКС и ПОИСКПОЗ вместо ВПР
Использование функции ВПР накладывает определенные ограничения: функция ВПР может находить значение только слева направо. Это означает, что столбец, содержащий искомое значение, всегда должен находиться слева от столбца, содержащего возвращаемое значение. Если ваша электронная таблица построена не таким образом, не используйте функцию ВПР. Вместо этого используйте сочетание функций ИНДЕКС и ПОИСКПОЗ.
В данном примере представлен небольшой список, в котором искомое значение (Воронеж) не находится в крайнем левом столбце. Поэтому мы не можем использовать функцию ВПР. Для поиска значения "Воронеж" в диапазоне B1:B11 будет использоваться функция ПОИСКПОЗ. Оно найдено в строке 4. Затем функция ИНДЕКС использует это значение в качестве аргумента поиска и находит численность населения Воронежа в четвертом столбце (столбец D). Использованная формула показана в ячейке A14.
Попробуйте попрактиковаться
Если вы хотите поэкспериментировать с функциями просмотра, прежде чем опробовать их на собственных данных, вот некоторые примеры данных.
Пример функции ВПР в работе
Скопируйте следующие данные на пустую электронную таблицу.
Совет
Перед вставкой данных в Excel задайте ширину столбцов с A по C на 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 по C на 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 задайте ширину столбцов с А по 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 |
См. также
Краткий справочник: обзор функции ВПР
Функции ссылки и поиска (справка)