Как найти значение и получить несколько соответствующих результатов в Excel?
В этом руководстве рассказывается, как выполнять поиск значения и возвращать несколько соответствующих значений в Excel, как показано на приведённых ниже снимках экрана:
Поиск значения с возвратом нескольких соответствующих значений с помощью формул массива
Поиск значения с возвратом нескольких соответствующих значений с помощью фильтрации
Поиск значения с возвратом нескольких соответствующих значений с помощью определённой функции
Поиск значения с возвратом нескольких соответствующих значений с помощью формул массива
Приведённая ниже длинная формула массива поможет найти значение и вернуть все соответствующие результаты.
1. Введите искомое значение в пустую ячейку. См. снимок экрана:
2. В соседнюю ячейку введите следующую формулу =IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$D$4,ROW($A$1:$A$7)),ROW(1:1)),2)),«»,)
INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$D$4,ROW($A$1:$A$7)),ROW(1:1)),2))и нажмите клавиши Shift + Ctrl + Enterодновременно, затем перетащите маркер автозаполнения, чтобы заполнить ячейки вниз, пока не появится первая пустая ячейка. См. снимок экрана:
Примечание: в приведённой выше формуле диапазон $A$1:$B$7 указывает на исходные данные, диапазон $A$1:$A$7 обозначает столбец, в котором выполняется поиск нужного значения, ячейка $D$4 — это ячейка, куда вы вводите искомое значение на шаге 1, а цифра 2 означает, что соответствующие значения ищутся во втором столбце.
Поиск значения с возвратом нескольких соответствующих значений с помощью фильтрации
В Excel для решения этой задачи можно также воспользоваться функцией фильтрации.
1. Выделите диапазон столбца, в котором нужно найти значение, и нажмите Данные > Фильтр. См. снимок экрана:
2. Затем щёлкните стрелку в первой ячейке вашего диапазона, выберите «Выбрать диапазон» и установите флажок напротив нужного значения в раскрывающемся списке. См. снимок экрана:
3. Нажмите кнопку ОК, после чего отобразятся только искомое значение и соответствующие ему значения.
Поиск значения с возвратом нескольких соответствующих значений с помощью определённой функции
Если вас интересуют конкретные функции, вы также можете воспользоваться ими для решения этой задачи.
1. Нажмите клавиши Alt + F11, чтобы открыть окно Microsoft Visual Basic для приложений.
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-дневная полнофункциональная пробная версия— без регистрации и кредитной карты
- Лучшее соотношение цены и качества— экономия по сравнению с покупкой отдельных надстроек