Как сделать выборку в Excel из списка с условным форматированием
Если Вы работаете с большой таблицей и вам необходимо выполнить поиск уникальных значений в Excel, соответствующие определенному запросу, то нужно использовать фильтр. Но иногда нам нужно выделить все строки, которые содержат определенные значения по отношению к другим строкам. В этом случаи следует использовать условное форматирование, которое ссылается на значения ячеек с запросом. Чтобы получить максимально эффективный результат, будем использовать выпадающий список, в качестве запроса. Это очень удобно если нужно часто менять однотипные запросы для экспонирования разных строк таблицы. Ниже детально рассмотрим: как сделать выборку повторяющихся ячеек из выпадающего списка.
Для примера возьмем историю взаиморасчетов с контрагентами, как показано на рисунке:
В данной таблице нам нужно выделить цветом все транзакции по конкретному клиенту. Для переключения между клиентами будем использовать выпадающий список.
Поэтому в первую очередь следует подготовить содержание для выпадающего списка. Нам нужны все Фамилии клиентов из столбца A, без повторений.
Перед тем как выбрать уникальные значения в Excel, подготовим данные для выпадающего списка:
- Выделите первый столбец таблицы A1:A19.
- Выберите инструмент: «ДАННЫЕ»-«Сортировка и фильтр»-«Дополнительно».
- В появившемся окне «Расширенный фильтр» включите «скопировать результат в другое место», а в поле «Поместить результат в диапазон:» укажите $F$1.
- Отметьте галочкой пункт «Только уникальные записи» и нажмите ОК.
В результате мы получили список данных с уникальными значениями (фамилии без повторений).
Теперь нам необходимо немного модифицировать нашу исходную таблицу. Выделите первые 2 строки и выберите инструмент: «ГЛАВНАЯ»-«Ячейки»-«Вставить» или нажмите комбинацию горячих клавиш CTRL+SHIFT+=.
У нас добавилось 2 пустые строки. Теперь в ячейку A1 введите значение «Клиент:».
Пришло время для создания выпадающего списка, из которого мы будем выбирать фамилии клиентов в качестве запроса.
Перед тем как выбрать уникальные значения из списка сделайте следующее:
- Перейдите в ячейку B1 и выберите инструмент «ДАННЫЕ»-«Работа с данными»-«Проверка данных».
- На вкладке «Параметры» в разделе «Условие проверки» из выпадающего списка «Тип данных:» выберите значение «Список».
- В поле ввода «Источник:» введите =$F$4:$F$8 и нажмите ОК.
В результате в ячейке B1 мы создали выпадающих список фамилий клиентов.
Примечание. Если данные для выпадающего списка находятся на другом листе, то лучше для такого диапазона присвоить имя и указать его в поле «Источник:». В данном случае это не обязательно, так как у нас все данные находятся на одном рабочем листе.
Выборка ячеек из таблицы по условию в Excel:
- Выделите табличную часть исходной таблицы взаиморасчетов A4:D21 и выберите инструмент: «ГЛАВНАЯ»-«Стили»-«Условное форматирование»-«Создать правило»-«Использовать формулу для определения форматируемых ячеек».
- Чтобы выбрать уникальные значения из столбца, в поле ввода введите формулу: =$A4=$B$1 и нажмите на кнопку «Формат», чтобы выделить одинаковые ячейки цветом. Например, зеленым. И нажмите ОК на всех открытых окнах.
Готово!
Как работает выборка уникальных значений Excel? При выборе любого значения (фамилии) из выпадающего списка B1, в таблице подсвечиваются цветом все строки, которые содержат это значение (фамилию). Чтобы в этом убедится в выпадающем списке B1 выберите другую фамилию. После чего автоматически будут выделены цветом уже другие строки. Такую таблицу теперь легко читать и анализировать.
Скачать пример выборки из списка с условным форматированием.Принцип действия автоматической подсветки строк по критерию запроса очень прост. Каждое значение в столбце A сравнивается со значением в ячейке B1. Это позволяет найти уникальные значения в таблице Excel. Если данные совпадают, тогда формула возвращает значение ИСТИНА и для целой строки автоматически присваивается новый формат.
Чтобы формат присваивался для целой строки, а не только ячейке в столбце A, мы используем смешанную ссылку в формуле =$A4.
Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ
Совет: Попробуйте использовать новые функции ПРОСМОТРX и XMATCH, а также улучшенные версии функций, описанные в этой статье. Эти новые функции работают в любом направлении и возвращают точные совпадения по умолчанию, что упрощает и упрощает работу с ними по сравнению с предшественниками.
Функции ВВ., а также ИНДЕКС и ВЫБОРПОЗ — одни из самых полезных функций в Excel.
Примечание: Мастер подметок больше не доступен в Excel.
Ниже в качестве примера по выбору вы можете найти пример использования в этой области.
=ВПР(B2;C2:E7,3,ИСТИНА)
В этом примере B2 является первым аргументом —элементом данных, который требуется для работы функции. В случае СРОТ ВЛ.В.ОВ этот первый аргумент является искомой значением. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, таким как «кузьмина» или 21 000. Вторым аргументом является диапазон ячеек C2–:E7, в котором нужно найти и найти значение. Третий аргумент — это столбец в диапазоне ячеек, содержащий ищите значение.
Четвертый аргумент необязателен. Введите истина или ЛОЖЬ. Если ввести ИСТИНА или оставить аргумент пустым, функция возвращает приблизительное совпадение значения, указанного в качестве первого аргумента. Если ввести ЛОЖЬ, функция будет соответствовать значению, заведомо первому аргументу. Другими словами, если оставить четвертый аргумент пустым или ввести ИСТИНА, это обеспечивает большую гибкость.
В этом примере показано, как работает функция.
При вводе значения в ячейку B2 (первый аргумент) в результате поиска в ячейках диапазона C2:E7 (2-й аргумент) выполняется поиск в ней и возвращается ближайшее приблизительное совпадение из третьего столбца в диапазоне — столбца E (третий аргумент).
Четвертый аргумент пуст, поэтому функция возвращает приблизительное совпадение. Иначе потребуется ввести одно из значений в столбец C или D, чтобы получить какой-либо результат.
Если вы хорошо разучились работать с функцией ВГТ.В.В., то в равной степени использовать ее будет легко. Вы вводите те же аргументы, но выполняется поиск в строках, а не в столбцах.
Использование индекса и MATCH вместо ВРОТ
При использовании функции ВПРАВО существует ряд ограничений, которые действуют только при использовании функции ВПРАВО. Это означает, что столбец, содержащий и look up, всегда должен быть расположен слева от столбца, содержащего возвращаемого значения.
Теперь, если ваша таблица не построена таким образом, не используйте В ПРОСМОТР. Используйте вместо этого сочетание функций ИНДЕКС и MATCH.
В данном примере представлен небольшой список, в котором искомое значение (Воронеж) не находится в крайнем левом столбце. Поэтому мы не можем использовать функцию ВПР. Для поиска значения «Воронеж» в диапазоне B1:B11 будет использоваться функция ПОИСКПОЗ. Оно найдено в строке 4. Затем функция ИНДЕКС использует это значение в качестве аргумента поиска и находит численность населения Воронежа в четвертом столбце (столбец D). Использованная формула показана в ячейке A14.
Дополнительные примеры использования индексов и MATCH вместо В ПРОСМОТР см. в статье билла Https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ Билла Джилена (Bill Jelen), 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. |
#Н/Д |
|
=ВПР(2,A2:C10,2,ИСТИНА) |
Используя приблизительное соответствие, функция ищет в столбце A значение 2, находит наибольшее значение, которое меньше или равняется 2 и составляет 1,29, а затем возвращает значение из столбца B в той же строке. |
1,71 |
Поскольку 0,1 меньше наименьшего значения в столбце A, возвращается сообщение об ошибке.Пример ГВ.Г.В.В.
Скопируйте всю таблицу и вставьте ее в ячейку 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 |
Примеры индекса и match
В последнем примере функции ИНДЕКС и MATCH совместно возвращают номер счета с наиболее ранней датой и соответствующую дату для каждого из пяти городов.
Так как дата возвращается как число, для ее формата используется функция ТЕКСТ. Функция ИНДЕКС использует результат, возвращенный функцией ПОИСКПОЗ, как аргумент. Сочетание функций ИНДЕКС и ПОИСКПОЗ используется в каждой формуле дважды — сперва для возврата номера счета, а затем для возврата даты.
Скопируйте всю таблицу и вставьте ее в ячейку A1 пустого листа Excel.
Совет: Перед тем как вировать данные в Excel, установите для столбцов A–D ширину в 250 пикселей и нажмите кнопку «Перенос текста» (вкладка «Главная», группа «Выравнивание»).
|
Счет |
Город |
Дата выставления счета |
Счет с самой ранней датой по городу, с датой |
|
3115 |
Казань |
07. |
=»Казань = «&ИНДЕКС($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. |
=»Челябинск = «&ИНДЕКС($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. |
=»Москва = «&ИНДЕКС($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. |
|
|
3441 |
Казань |
02.05.12 |
|
|
3517 |
Казань |
08.05.12 |
|
|
3124 |
Орел |
09. |
|
|
3155 |
Орел |
11.04.12 |
|
|
3177 |
Орел |
19.04.12 |
|
|
3357 |
Орел |
28. |
|
|
3492 |
Орел |
06.05.12 |
|
|
3316 |
Челябинск |
25.04.12 |
|
|
3346 |
Челябинск |
28. |
|
|
3372 |
Челябинск |
01.05.12 |
|
|
3414 |
Челябинск |
01.05.12 |
|
|
3451 |
Челябинск |
02. |
|
|
3467 |
Челябинск |
02.05.12 |
|
|
3474 |
Челябинск |
04.05.12 |
|
|
3490 |
Челябинск |
05. |
|
|
3503 |
Челябинск |
08.05.12 |
|
|
3151 |
Нижний Новгород |
09.04.12 |
|
|
3438 |
Нижний Новгород |
02. |
|
|
3471 |
Нижний Новгород |
04.05.12 |
|
|
3160 |
Москва |
18.04.12 |
|
|
3328 |
Москва |
26. |
|
|
3368 |
Москва |
29.04.12 |
|
|
3420 |
Москва |
01.05.12 |
|
|
3501 |
Москва |
06. |
04.12
04.12
04.12
05.12
04.12
04.12
04.12
05.12
05.12
05.12
04.12
05.12Использовать встроенные функции Excel для поиска данных в таблице или диапазоне ячеек
Офис для бизнеса Больше…Меньше
Резюме
В этой пошаговой статье описывается, как найти данные в таблице (или диапазоне ячеек) с помощью различных встроенных функций в Microsoft Excel. Вы можете использовать разные формулы для получения одного и того же результата.
Создайте образец рабочего листа
В этой статье используется пример рабочего листа для иллюстрации встроенных функций Excel. Рассмотрим пример ссылки на имя из столбца A и возврата возраста этого человека из столбца C. Чтобы создать этот рабочий лист, введите следующие данные в пустой рабочий лист Excel.
Введите значение, которое хотите найти, в ячейку E2. Вы можете ввести формулу в любую пустую ячейку на том же листе.
А | Б | С | Д | Е | ||
1 | Имя | Отдел | Возраст | Найти значение | ||
2 | Генри | 501 | 28 | Мэри | ||
3 | Стэн | 201 | 19 | |||
4 | Мэри | 101 | 22 | |||
5 | Ларри | 301 | 29 |
Определения терминов
В этой статье для описания встроенных функций Excel используются следующие термины:
Срок | Определение | Пример |
Массив таблиц | Вся интерполяционная таблица | А2:С5 |
Искомое_значение | Значение, которое нужно найти в первом столбце Table_Array. | Е2 |
Lookup_Array | Диапазон ячеек, содержащий возможные значения поиска. | А2:А5 |
Col_Index_Num | Номер столбца в Table_Array, для которого должно быть возвращено соответствующее значение. | 3 (третий столбец в Table_Array) |
Массив_результатов | Диапазон, содержащий только одну строку или столбец. | С2:С5 |
Range_Lookup | Логическое значение (ИСТИНА или ЛОЖЬ). Если TRUE или опущено, возвращается приблизительное совпадение. Если FALSE, он будет искать точное совпадение. | ЛОЖЬ |
Top_cell | Это ссылка, от которой вы хотите получить смещение. Top_Cell должен ссылаться на ячейку или диапазон соседних ячеек. В противном случае СМЕЩ возвращает #ЗНАЧ! значение ошибки. | |
Offset_Col | Это количество столбцов слева или справа, на которое должна ссылаться верхняя левая ячейка результата. |
Он должен быть того же размера, что и Lookup_Array или Lookup_Vector.
Например, «5» в качестве аргумента Offset_Col указывает, что верхняя левая ячейка в ссылке находится на пять столбцов справа от ссылки. Offset_Col может быть положительным (что означает справа от начальной ссылки) или отрицательным (что означает слева от начальной ссылки).Функции
ПРОСМОТР()
Функция ПРОСМОТР находит значение в одной строке или столбце и сопоставляет его со значением в той же позиции в другой строке или столбце.
Ниже приведен пример синтаксиса формулы ПРОСМОТР:
=ПРОСМОТР(Искомое_Значение,Искомый_Вектор,Результативный_Вектор)
Следующая формула определяет возраст Марии в образце рабочего листа:
=ПРОСМОТР(E2,A2:A5,C2:C5)
Формула использует значение «Мария» в ячейке E2 и находит «Мария» в векторе поиска (столбец A).
Затем формула сопоставляется со значением в той же строке результирующего вектора (столбец C). Поскольку «Мария» находится в строке 4, функция ПРОСМОТР возвращает значение из строки 4 в столбце C (22).
ПРИМЕЧАНИЕ. Функция ПРОСМОТР требует, чтобы таблица была отсортирована.
Для получения дополнительных сведений о функции ПРОСМОТР щелкните следующий номер статьи базы знаний Майкрософт:
Как использовать функцию ПРОСМОТР в Excel
ВПР()
Функция VLOOKUP или вертикальный просмотр используется, когда данные перечислены в столбцах. Эта функция ищет значение в крайнем левом столбце и сопоставляет его с данными в указанном столбце в той же строке. Вы можете использовать VLOOKUP для поиска данных в отсортированной или несортированной таблице. В следующем примере используется таблица с несортированными данными.
Ниже приведен пример синтаксиса формулы VLOOKUP :
=ВПР(Искомое_Значение,Таблица_Массив,Количество_индекса_столбца,Диапазон_просмотра)
Следующая формула определяет возраст Марии в образце рабочего листа:
=ВПР(E2,A2:C5,3,ЛОЖЬ)
Формула использует значение «Мария» в ячейке E2 и находит «Мария» в крайнем левом столбце (столбец A). Затем формула сопоставляется со значением в той же строке в Column_Index. В этом примере используется «3» в качестве Column_Index (столбец C). Поскольку «Мэри» находится в строке 4, ВПР возвращает значение из строки 4 в столбце C (22).
Для получения дополнительных сведений о функции VLOOKUP щелкните следующий номер статьи базы знаний Майкрософт:
Как использовать ВПР или ГПР, чтобы найти точное совпадение
ИНДЕКС() и ПОИСКПОЗ()
Вы можете использовать функции ИНДЕКС и ПОИСКПОЗ вместе, чтобы получить те же результаты, что и при использовании ПРОСМОТР или ВПР .
Ниже приведен пример синтаксиса, который объединяет ИНДЕКС и ПОИСКПОЗ для получения тех же результатов, что и ПРОСМОТР и ВПР в предыдущих примерах:
=ИНДЕКС(Таблица_Массив,ПОИСКПОЗ(Искомое_Значение,Искомый_Массив,0),Количество_Индекса_Столбца)
Следующая формула определяет возраст Марии в образце рабочего листа:
=ИНДЕКС(A2:C5,ПОИСКПОЗ(E2,A2:A5,0),3)
Формула использует значение «Мария» в ячейке E2 и находит «Мария» в столбце A. Затем она соответствует значению в той же строке в столбце C. Поскольку «Мария» находится в строке 4, формула возвращает значение из строки 4 в колонке С (22).
ПРИМЕЧАНИЕ. Если ни одна из ячеек в Lookup_Array не соответствует Lookup_Value («Mary»), эта формула вернет #N/A.
Для получения дополнительных сведений о функции ИНДЕКС щелкните следующий номер статьи базы знаний Майкрософт:
Как использовать функцию ИНДЕКС для поиска данных в таблице
СМЕЩ() и ПОИСКПОЗ()
Вы можете использовать функции OFFSET и MATCH вместе, чтобы получить те же результаты, что и функции в предыдущем примере.
Ниже приведен пример синтаксиса, который объединяет OFFSET и MATCH для получения тех же результатов, что и LOOKUP и VLOOKUP :
= OFFSET(top_cell,MATCH(Lookup_Value, Lookup_Array,0),Offset_Col)
Эта формула находит возраст Мэри в образце рабочего листа:
=СМЕЩЕНИЕ(A1,ПОИСКПОЗ(E2,A2:A5,0),2)
Формула использует значение «Мария» в ячейке E2 и находит «Мария» в столбце A. Затем формула сопоставляет значение в той же строке, но на два столбца правее (столбец C). Поскольку «Мария» находится в столбце A, формула возвращает значение в строке 4 столбца C (22).
Для получения дополнительных сведений о функции OFFSET щелкните следующий номер статьи базы знаний Майкрософт:
Как использовать функцию СМЕЩЕНИЕ
Поиск значений с помощью ВПР, ИНДЕКС или ПОИСКПОЗ
Совет: Попробуйте использовать новые функции XLOOKUP и XMATCH, улучшенные версии функций, описанных в этой статье. Эти новые функции работают в любом направлении и по умолчанию возвращают точные совпадения, что делает их более простыми и удобными в использовании, чем их предшественники.
Предположим, у вас есть список номеров офисов, и вам нужно знать, какие сотрудники работают в каждом офисе. Электронная таблица огромна, поэтому вы можете подумать, что это сложная задача. На самом деле это довольно легко сделать с помощью функции поиска.
Функции ВПР и ГПР вместе с ИНДЕКС и ПОИСКПОЗ являются одними из самых полезных функций в Excel.
Примечание. Мастер поиска больше не доступен в Excel.
Вот пример использования функции ВПР.
=ВПР(B2,C2:E7,3,ИСТИНА)
В этом примере B2 — это первый аргумент — элемент данных, необходимый для работы функции. Для ВПР этот первый аргумент — это значение, которое вы хотите найти. Этот аргумент может быть ссылкой на ячейку или фиксированным значением, например «кузнец» или 21 000. Второй аргумент — это диапазон ячеек C2-:E7, в котором нужно искать значение, которое вы хотите найти. Третий аргумент — это столбец в том диапазоне ячеек, который содержит искомое значение.
Четвертый аргумент является необязательным. Введите ИСТИНА или ЛОЖЬ. Если вы введете TRUE или оставите аргумент пустым, функция вернет приблизительное соответствие значению, указанному вами в первом аргументе. Если вы введете FALSE, функция будет соответствовать значению, предоставленному первым аргументом.
Другими словами, оставляя четвертый аргумент пустым или вводя значение TRUE, вы получаете больше гибкости.
В этом примере показано, как работает функция. Когда вы вводите значение в ячейку B2 (первый аргумент), функция ВПР ищет ячейки в диапазоне C2:E7 (2-й аргумент) и возвращает наиболее близкое приближенное совпадение из третьего столбца диапазона, столбца E (3-й аргумент).
Четвертый аргумент пуст, поэтому функция возвращает приблизительное совпадение. Если бы это было не так, вам пришлось бы ввести одно из значений в столбцах C или D, чтобы вообще получить результат.
Когда вы освоитесь с функцией ВПР, функция ГПР будет столь же проста в использовании. Вы вводите те же аргументы, но поиск выполняется в строках, а не в столбцах.
Использование ИНДЕКС и ПОИСКПОЗ вместо ВПР
Существуют определенные ограничения при использовании функции ВПР — функция ВПР может искать значение только слева направо.
Это означает, что столбец, содержащий искомое значение, всегда должен располагаться слева от столбца, содержащего возвращаемое значение. Теперь, если ваша электронная таблица не построена таким образом, не используйте функцию ВПР. Вместо этого используйте комбинацию функций ИНДЕКС и ПОИСКПОЗ.
В этом примере показан небольшой список, в котором значение, которое мы хотим найти, Чикаго, отсутствует в крайнем левом столбце. Итак, мы не можем использовать функцию ВПР. Вместо этого мы будем использовать функцию ПОИСКПОЗ, чтобы найти Чикаго в диапазоне B1:B11. Оно находится в строке 4. Затем ИНДЕКС использует это значение в качестве аргумента поиска и находит население Чикаго в 4-м столбце (столбец D). Используемая формула показана в ячейке A14.
Дополнительные примеры использования ИНДЕКС и ПОИСКПОЗ вместо ВПР см. в статье https://www.mrexcel.com/excel-tips/excel-vlookup-index-match/ Билла Джелена, 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) | Используя приблизительное совпадение, ищет значение 1 в столбце A, находит наибольшее значение меньшее или равное 1 в столбце A, равное 0,946, а затем возвращает значение из столбца B в той же строке. | 2,17 |
=ВПР(1,A2:C10,3,ИСТИНА) | Используя приблизительное совпадение, ищет значение 1 в столбце A, находит наибольшее значение, меньшее или равное 1 в столбце A, что составляет 0,946, а затем возвращает значение из столбца C в той же строке. | 100 |
=ВПР(0.7,A2:C10,3,ЛОЖЬ) | Используя точное совпадение, ищет значение 0,7 в столбце A. Поскольку точного совпадения в столбце A нет, возвращается ошибка. | #Н/Д |
=ВПР(0. | Используя приблизительное совпадение, ищет значение 0,1 в столбце A. Поскольку 0,1 меньше наименьшего значения в столбце A, возвращается ошибка. | #Н/Д |
=ВПР(2,A2:C10,2,ИСТИНА) | Используя приблизительное совпадение, ищет значение 2 в столбце A, находит наибольшее значение, меньшее или равное 2 в столбце A, что составляет 1,29, а затем возвращает значение из столбца B в той же строке. | 1,71 |
1,A2:C10,2,ИСТИНА)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, ИСТИНА) | Ищет «B» в строке 1 и возвращает значение из строки 3, которая находится в том же столбце. Поскольку точное соответствие для «B» не найдено, используется наибольшее значение в строке 1, меньшее, чем «B»: «Оси» в столбце A. | 5 |
=ГПР(«Болты», A1:C4, 4) | Ищет «Болты» в строке 1 и возвращает значение из строки 4, которая находится в том же столбце (столбец C). | 11 |
=HLOOKUP(3, {1,2,3;»a»,»b»,»c»;»d»,»e»,»f»}, 2, ИСТИНА) | Ищет число 3 в константе трехстрочного массива и возвращает значение из строки 2 в том же (в данном случае третьем) столбце. В массиве констант есть три строки значений, каждая строка разделена точкой с запятой (;). Поскольку «c» находится в строке 2 и в том же столбце, что и 3, возвращается «c». | с |
Примеры ИНДЕКС и ПОИСКПОЗ
В этом последнем примере функции ИНДЕКС и ПОИСКПОЗ используются вместе для возврата самого раннего номера счета и соответствующей ему даты для каждого из пяти городов.
Поскольку дата возвращается в виде числа, мы используем функцию ТЕКСТ, чтобы отформатировать ее как дату. Функция ИНДЕКС фактически использует в качестве аргумента результат функции ПОИСКПОЗ. Комбинация функций ИНДЕКС и ПОИСКПОЗ используется дважды в каждой формуле — сначала для возврата номера счета, а затем для возврата даты.
Скопируйте все ячейки этой таблицы и вставьте их в ячейку A1 на пустом листе Excel.
Совет: Перед вставкой данных в Excel установите ширину столбцов от A до D до 250 пикселей и нажмите Перенос текста (вкладка Главная , группа Выравнивание ).
Счет-фактура | Город | Дата счета | Самый ранний счет по городу с датой |
3115 | Атланта | 07. | =»Атланта = «&ИНДЕКС($A$2:$C$33,MATCH(«Атланта»,$B$2:$B$33,0),1)& «, Дата счета: » & ТЕКСТ(ИНДЕКС($A$2 :$C$33,MATCH(«Атланта»,$B$2:$B$33,0),3),»м/д/гг») |
3137 | Атланта | 09.04.12 | =»Остин = «&ИНДЕКС($A$2:$C$33,MATCH(«Остин»,$B$2:$B$33,0),1)& «, Дата счета: » & ТЕКСТ(ИНДЕКС($A$2 :$C$33,MATCH(«Остин»,$B$2:$B$33,0),3),»м/д/гг») |
3154 | Атланта | 11. | =»Даллас = «&ИНДЕКС($A$2:$C$33,MATCH(«Даллас»,$B$2:$B$33,0),1)& «, Дата счета: » & ТЕКСТ(ИНДЕКС($A$2 :$C$33,MATCH(«Даллас»,$B$2:$B$33,0),3),»м/д/гг») |
3191 | Атланта | 21.04.12 | =»Новый Орлеан = «&ИНДЕКС($A$2:$C$33,MATCH(«Новый Орлеан»,$B$2:$B$33,0),1)& «, Дата счета: » & ТЕКСТ(ИНДЕКС($ A$2:$C$33,MATCH(«Новый Орлеан»,$B$2:$B$33,0),3),»м/д/гг») |
3293 | Атланта | 25. | =»Тампа = «&ИНДЕКС($A$2:$C$33,MATCH(«Тампа»,$B$2:$B$33,0),1)& «, Дата счета: » & ТЕКСТ(ИНДЕКС($A$2 :$C$33,MATCH(«Тампа»,$B$2:$B$33,0),3),»м/д/гг») |
3331 | Атланта | 27.04.12 | |
3350 | Атланта | 28.04.12 | |
3390 | Атланта | 01. | |
3441 | Атланта | 02.05.12 | |
3517 | Атланта | 08.05.12 | |
3124 | Остин | 09.04.12 | |
3155 | Остин | 11. | |
3177 | Остин | 19.04.12 | |
3357 | Остин | 28.04.12 | |
3492 | Остин | 06.05.12 | |
3316 | Даллас | 25. | |
3346 | Даллас | 28.04.12 | |
3372 | Даллас | 01.05.12 | |
3414 | Даллас | 01.05.12 | |
3451 | Даллас | 02. | |
3467 | Даллас | 02.05.12 | |
3474 | Даллас | 04.05.12 | |
3490 | Даллас | 05.05.12 | |
3503 | Даллас | 08. | |
3151 | Новый Орлеан | 09.04.12 | |
3438 | Новый Орлеан | 02.05.12 | |
3471 | Новый Орлеан | 04.05.12 | |
3160 | Тампа | 18. |
04.12
04.12
04.12
05.12
04.12
04.12
05.12
05.12