Як шукати значення, яке повертає кілька відповідних значень у 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. клацання OK, тепер ви бачите лише значення пошуку та відповідні значення відфільтровано.
Значення пошуку повертає кілька відповідних значень із визначеною функцією
Якщо вас цікавить визначена функція, ви також можете вирішити проблему з визначеною функцією.
1. прес Alt + F11 ключі, щоб відкрити Microsoft Visual Basic для програм вікна.
2. клацання Модулі > Insert вставити a Модулі вікно та скопіюйте наведений нижче 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").
Найкращі інструменти продуктивності офісу
Покращуйте свої навички Excel за допомогою Kutools для Excel і відчуйте ефективність, як ніколи раніше. Kutools для Excel пропонує понад 300 додаткових функцій для підвищення продуктивності та економії часу. Натисніть тут, щоб отримати функцію, яка вам найбільше потрібна...
Вкладка Office Передає інтерфейс із вкладками в Office і значно полегшує вашу роботу
- Увімкніть редагування та читання на вкладках у Word, Excel, PowerPoint, Publisher, Access, Visio та Project.
- Відкривайте та створюйте кілька документів на нових вкладках того самого вікна, а не в нових вікнах.
- Збільшує вашу продуктивність на 50% та зменшує сотні клацань миші для вас щодня!
