Совет: Попробуйте использовать новые функции XLOOKUP и XMATCH , улучшенные версии функций, описанных в этой статье. Эти новые функции работают в любом направлении и по умолчанию возвращают точные совпадения, что делает их проще и удобнее в использовании, чем их предшественники.
Предположим, что у вас есть список номеров расположения офисов, и вам нужно знать, какие сотрудники находятся в каждом офисе. Электронная таблица огромна, поэтому вы можете подумать, что это сложная задача. Это на самом деле довольно легко сделать с функцией поиска.
Функции ВПР и HLOOKUP , а также INDEX и MATCH являются одними из наиболее полезных функций в Excel.
Примечание: Функция мастера подстановки больше не доступна в Excel.
Ниже приведен пример использования функции ВПР.
=ВПР(B2;C2:E7,3,ИСТИНА)
В этом примере B2 является первым аргументом — элементом данных, который должен работать функции. Для ВПР этот первый аргумент является значением, которое требуется найти. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, например smith или 21000. Второй аргумент — это диапазон ячеек C2-:E7, в котором выполняется поиск нужного значения. Третий аргумент — это столбец в диапазоне ячеек, содержащий нужное значение.
Четвертый аргумент необязателен. Введите TRUE или FALSE. Если ввести ИСТИНА или оставить аргумент пустым, функция возвращает приблизительное совпадение значения, указанного в качестве первого аргумента. Если ввести значение FALSE, функция будет соответствовать значению, предоставленному первым аргументом. Другими словами, если оставить четвертый аргумент пустым или ввести TRUE, вы получаете большую гибкость.
В этом примере показано, как работает функция. При вводе значения в ячейку B2 (первый аргумент) ВПР выполняет поиск ячеек в диапазоне C2:E7 (2-й аргумент) и возвращает ближайшее приблизительное совпадение из третьего столбца в диапазоне, столбца E (3-й аргумент).
Четвертый аргумент пуст, поэтому функция возвращает приблизительное совпадение. Иначе потребуется ввести одно из значений в столбец C или D, чтобы получить какой-либо результат.
Если вы знакомы с VLOOKUP, функция HLOOKUP также проста в использовании. Вы вводите те же аргументы, но выполняется поиск по строкам, а не по столбцам.
Использование INDEX и MATCH вместо ВПР
При использовании ВПР существуют определенные ограничения: функция ВПР может искать значение только слева направо. Это означает, что столбец, содержащий искать значение, всегда должен располагаться слева от столбца, содержащего возвращаемое значение. Теперь, если электронная таблица не создана таким образом, не используйте VLOOKUP. Вместо этого используйте сочетание функций INDEX и MATCH.
В данном примере представлен небольшой список, в котором искомое значение (Воронеж) не находится в крайнем левом столбце. Поэтому мы не можем использовать функцию ВПР. Для поиска значения "Воронеж" в диапазоне B1:B11 будет использоваться функция ПОИСКПОЗ. Оно найдено в строке 4. Затем функция ИНДЕКС использует это значение в качестве аргумента поиска и находит численность населения Воронежа в четвертом столбце (столбец D). Использованная формула показана в ячейке A14.
Дополнительные примеры использования INDEX и MATCH вместо ВПР см. в статье , https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ Билл Джелен (Bill Jelen), Microsoft MVP.
Попробуйте попрактиковаться
Если вы хотите поэкспериментировать с функциями подстановки, прежде чем опробовать их с собственными данными, вот некоторые примеры данных.
Пример ВПР на работе
Скопируйте следующие данные в пустую электронную таблицу.
Совет: Перед вставкой данных в 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 |
Пример HLOOKUP
Скопируйте всю таблицу и вставьте ее в ячейку 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 |
Примеры INDEX и MATCH
В этом последнем примере функции INDEX и MATCH используются вместе для возврата самого раннего номера счета и соответствующей даты для каждого из пяти городов. Так как дата возвращается в виде числа, мы используем функцию TEXT, чтобы отформатировать ее как дату. Функция ИНДЕКС использует результат, возвращенный функцией ПОИСКПОЗ, как аргумент. Сочетание функций ИНДЕКС и ПОИСКПОЗ используется в каждой формуле дважды — сперва для возврата номера счета, а затем для возврата даты.
Скопируйте всю таблицу и вставьте ее в ячейку 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 |
