Найти ячейку в excel по содержимому: Проверьте, содержит ли ячейка текст (без регистрации в досье)
Содержание
Проверьте, содержит ли ячейка текст (без регистрации в досье)
Excel для Microsoft 365 Excel 2021 Excel 2019 Excel 2016 Excel 2013 Excel 2010 Excel 2007 Еще…Меньше
Предположим, вам нужно убедиться, что столбец содержит текст, а не числа. Или, возможно, вам нужно найти все заказы, соответствующие конкретному продавцу. Если вы не беспокоите текст в верхнем или нижнем регистре, можно проверить, содержит ли ячейка текст несколькими способами.
Для поиска текста также можно использовать фильтр. Дополнительные сведения см. в теме Фильтрация данных.
Поиск ячеек, содержащих текст
Чтобы найти ячейки, содержащие определенный текст, выполните указанные здесь действия.
-
Вы диапазон ячеек, в которые нужно в поиске.
Чтобы найти результат поиска на всем из них, щелкните любую ячейку.
-
На вкладке Главная в группе Редактирование нажмите кнопку Найти и & выберите, а затем нажмите кнопку Найти.
-
В поле Найти введите текст (или числа), который нужно найти. Вы также можете выбрать недавний поиск в поле Найти.
Примечание: В условиях поиска можно использовать поддеревные знаки.
-
Чтобы указать формат поиска, нажмите кнопку Формат и сделайте выбор во всплыващем окне Найти формат.
-
Нажмите кнопку Найти все или Найти далее.
В списке Найти все перечислены все вхождения элемента, которые необходимо найти, и можно сделать ячейку активной, выбрав определенное вхождение. Вы можете отсортировать результаты поиска Найти все, щелкнув заглавную кнопку.
org/ListItem»>
Нажмите кнопку Параметры, чтобы дополнительно определить поиск. Например, можно найти все ячейки, содержащие данные одного типа, например формулы.
В поле Внутри можно выбрать лист или книгу для поиска на листе или во всей книге.
Примечание: Чтобы остановить поиск, нажмите клавишу ESC.
Проверьте, есть ли в ячейке текст
Для этого используйте функцию ЕТЕКСТ.
Проверка того, совпадает ли ячейка с определенным текстом
Используйте функцию ЕСЛИ для возврата результатов для заского условия.
Проверка того, совпадает ли часть ячейки с определенным текстом
Для этого используйте функции ЕСЛИ,ПОИСКи Е ЧИСЛОЭЛЕБР.
Примечание: Функция ПОИСК не имеет чувствительность к делу.
Поиск значений с помощью функций ВПР, ИНДЕКС и ПОИСКПОЗ
Совет: Попробуйте использовать новые функции ПРОСМОТР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Поиск значений с помощью ВПР, ИНДЕКС или ПОИСКПОЗ
Совет: Попробуйте использовать новые функции 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,ИСТИНА)ГПР Пример
Скопируйте все ячейки этой таблицы и вставьте их в ячейку 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 | Даллас | 5/5/12 | |
3503 | Даллас | 08. | |
3151 | Новый Орлеан | 09.04.12 | |
3438 | Новый Орлеан | 02.05.12 | |
3471 | Новый Орлеан | 04.05.12 | |
3160 | Тампа | 18. | |
3328 | Тампа | 26.04.12 | |
3368 | Тампа | 29.04.12 | |
3420 | Тампа | 01.05.12 | |
3501 | Тампа | 06. |
04.12
04.12
04.12
05.12
04.12
04.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, VLOOKUP возвращает значение из строки 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).
