Как да върна стойност за търсене на множество съответни стойности в 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. кликване Модули > Поставете за вмъкване на 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% и намалява стотици кликвания на мишката за вас всеки ден!
