Как найти значение и вернуть несколько соответствующих значений в Excel?
Это руководство рассказывает о том, как найти значение и вернуть несколько соответствующих значений в Excel, как показано на скриншотах ниже:
Поиск значения с возвратом нескольких соответствующих значений с помощью формулы массива
Поиск значения с возвратом нескольких соответствующих значений с помощью Фильтра
Поиск значения с возвратом нескольких соответствующих значений с помощью Определенной Функции
Поиск значения с возвратом нескольких соответствующих значений с помощью формулы массива
Вот длинная формула массива, которая поможет вам найти значение и вернуть несколько соответствующих значений.
1. Введите значение, которое вы хотите найти, в пустую ячейку. См. скриншот:
2. В соседней ячейке введите эту формулу =ЕСЛИ(ЕОШИБКА(ИНДЕКС($A$1:$B$7,НАИМЕНЬШИЙ(ЕСЛИ($A$1:$A$7=$D$4,СТРОКА($A$1:$A$7)),СТРОКА(1:1)),2)),"",
ИНДЕКС($A$1:$B$7,НАИМЕНЬШИЙ(ЕСЛИ($A$1:$A$7=$D$4,СТРОКА($A$1:$A$7)),СТРОКА(1:1)),2)) в нее, и нажмите Shift + Ctrl + Enter клавиши вместе, затем перетащите Автозаполнение для заполнения ячеек до появления первой пустой ячейки. См. скриншот:
Примечание: В приведенной выше формуле $A$1:$B$7 указывает диапазон данных, $A$1:$A$7 обозначает диапазон столбца, в котором вы ищете определенное значение, $D$4 указывает ячейку, в которую вы вводите значение для поиска на шаге 1, 2 означает поиск соответствующих значений во втором столбце.
Поиск значения с возвратом нескольких соответствующих значений с помощью Фильтра
В Excel вы также можете использовать функцию Фильтр для решения этой проблемы.
1. Выберите диапазон столбцов, в которых вы хотите найти значение, и нажмите Данные > Фильтр. См. скриншот:
2. Затем нажмите кнопку со стрелкой в первой ячейке выбранного диапазона и отметьте значение, которое вы хотите найти только в раскрывающемся списке. См. скриншот:
3. Нажмите OK, теперь вы видите, что только искомое значение и его соответствующие значения отфильтрованы.
Поиск значения с возвратом нескольких соответствующих значений с помощью Определенной Функции
Если вас интересует Определенная Функция, вы также можете решить проблему с ее помощью.
1. Нажмите Alt + F11, чтобы открыть окно Microsoft Visual Basic For Applications.
2. Нажмите Модуль > Вставить, чтобы вставить окно Модуля, и скопируйте следующий код VBA в это окно.
VBA: Поиск значения с возвратом нескольких соответствующих значений.
Function MyVlookup(pWorkRng As Range, pRng As Range, pColumnIndex As Integer, Optional pType As String = "v")
'Updateby20140827
Dim xRow As Single
Dim xCol As Single
Dim arr() As Variant
ReDim arr(0)
For i = 1 To pRng.Rows.Count
If pWorkRng = pRng.Cells(i, 1) Then
arr(UBound(arr)) = pRng.Cells(i, pColumnIndex)
ReDim Preserve arr(UBound(arr) + 1)
End If
Next
If pType = "h" Then
xCol = Range(Application.Caller.Address).Columns.Count
For i = UBound(arr) To xCol
arr(UBound(arr)) = ""
ReDim Preserve arr(UBound(arr) + 1)
Next
ReDim Preserve arr(UBound(arr) - 1)
MyVlookup = arr
Else
xRow = Range(Application.Caller.Address).Rows.Count
For i = UBound(arr) To xRow
arr(UBound(arr)) = ""
ReDim Preserve arr(UBound(arr) + 1)
Next
ReDim Preserve arr(UBound(arr) - 1)
MyVlookup = Application.WorksheetFunction.Transpose(arr)
End If
End Function
3. Закройте окно и введите эту формулу в ячейку =MyVlookup(A10,$A$2:$B$7,2) (A10 указывает значение для поиска, $A$2:$B$7 указывает диапазон данных, 2 указывает номер индекса столбца). Нажмите Shift + Ctrl + Enter. Затем перетащите маркер заполнения вниз по ячейкам, поместите курсор в Строку формул и снова нажмите Shift + Ctrl + Enter.
Совет: Если вы хотите вернуть значения в горизонтальных ячейках, вы можете ввести эту формулу =MyVlookup(A10, $A$2:$B$7, 2, "h").
Лучшие инструменты для повышения продуктивности в Office
Повысьте свои навыки работы в Excel с помощью Kutools для Excel и ощутите эффективность на новом уровне. Kutools для Excel предлагает более300 расширенных функций для повышения производительности и экономии времени. Нажмите здесь, чтобы выбрать функцию, которая вам нужнее всего...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите режим вкладок для редактирования и чтения в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна вместо новых отдельных окон.
- Увеличьте свою продуктивность на50% и уменьшите количество щелчков мышью на сотни ежедневно!
Все надстройки Kutools. Один установщик
Пакет Kutools for Office включает надстройки для Excel, Word, Outlook и PowerPoint, а также Office Tab Pro — идеально для команд, работающих в разных приложениях Office.
- Комплексный набор — надстройки для Excel, Word, Outlook и PowerPoint плюс Office Tab Pro
- Один установщик, одна лицензия — настройка занимает считанные минуты (MSI-совместимо)
- Совместная работа — максимальная эффективность между приложениями Office
- 30-дневная полнофункциональная пробная версия — без регистрации и кредитной карты
- Лучшее соотношение цены и качества — экономия по сравнению с покупкой отдельных надстроек