Как да направите vlookup и да свържете множество съответстващи стойности в Excel?
Както всички знаем, VLOOKUP функцията в Excel може да ни помогне да търсим стойност и да върнем съответните данни в друга колона, но по принцип може да получи само първата относителна стойност, ако има множество съвпадащи данни. В тази статия ще говоря за това как да правя vlookup и да свържа множество съответстващи стойности само в една клетка или вертикален списък.
Vlookup и връщане на множество съответстващи стойности вертикално с формула
Vlookup и свързване на множество съвпадащи стойности в клетка с дефинирана от потребителя функция
Vlookup и свързване на множество съвпадащи стойности в клетка с Kutools за Excel
Vlookup и връщане на множество съответстващи стойности вертикално с формула
Да предположим, че имам следния диапазон от данни, за да получа всички съответни стойности на базата на конкретна стойност вертикално, както е показано на следната екранна снимка, можете да приложите формула за масив.
1. Въведете тази формула: =IF(COUNTIF($A$1:$A$16,$D$2)>=ROWS($1:1),INDEX($B$1:$B$16,SMALL(IF($A$1:$A$16=$D$2,ROW($1:$16)),ROW(1:1))),"") в празна клетка, където искате да поставите резултата, например E2, и след това натиснете Ctrl + Shift + Enter ключове заедно, за да получите базата на относителната стойност по конкретен критерий, вижте екранната снимка:
Забележка: В горната формула:
A1: A16 е диапазонът от колони, който съдържа конкретната стойност, която искате да търсите;
D2 показва конкретната стойност, която искате да vlookup;
B1: B16 е диапазонът от колони, от който искате да върнете съответните данни;
$ 1: $ 16 показва препратката към редовете в диапазона.
2. След това изберете клетка E2 и плъзнете манипулатора за запълване надолу към клетките, докато получите празни клетки и всички съответстващи стойности са изброени в колоната, както е показано на следната екранна снимка:
Vlookup и свързване на множество съвпадащи стойности в клетка с дефинирана от потребителя функция
Вместо да получавате относителните стойности вертикално, понякога искате да съвпадащите стойности в една клетка и да ги свържете с конкретен разделител. В този случай следната дефинирана от потребителя функция може да ви направи услуга.
1. Задръжте надолу ALT + F11 за да отворите Microsoft Visual Basic за приложения прозорец.
2. Щракнете Поставете > Модулии поставете следния код в Модули Window.
VBA код: Vlookup и свързване на множество съвпадащи стойности в клетка
Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice
Dim x As Range
Dim result As String
result = ""
For Each x In lookuprange
If x = lookupval Then
result = result & " " & x.Offset(0, indexcol - 1)
End If
Next x
CusVlookup = result
End Function
3. След това запазете и затворете този код, върнете се в работния лист и въведете тази формула: =cusvlookup(D2,A1:B16,2) в празна клетка, където искате да поставите резултата, и натиснете Въведете ключ, всички съответстващи стойности, базирани на конкретни данни, са върнати в една клетка с разделител за интервал, вижте екранната снимка:
Забележка: В горната формула: D2 показва стойностите на клетките, които искате да търсите, A1: B16 е диапазонът от данни, който искате да извлечете данните, числото 2 е номерът на колоната, от който трябва да бъде върната съвпадащата стойност, можете да промените тези препратки към вашите нужди.
Vlookup и свързване на множество съвпадащи стойности в клетка с Kutools за Excel
Ако имате Kutools за Excel, Със своята Разширено комбиниране на редове функция, можете бързо да завършите тази работа с лекота. Тази функция може да ви помогне да комбинирате всички съвпадащи стойности с конкретни разделители въз основа на същите данни в друга колона.
| Kutools за Excel : с повече от 300 удобни добавки за Excel, безплатни за изпробване без ограничение за 30 дни. |
След инсталиране Kutools за Excel, моля, направете следното:
1. Изберете диапазона от данни, за който искате да получите съответните стойности въз основа на конкретните данни.
2, След това кликнете Kutools > Обединяване и разделяне > Разширено комбиниране на редове, вижте екранна снимка:
3. В Разширено комбиниране на редове в диалоговия прозорец щракнете върху името на колоната, въз основа на която искате да комбинирате, след което щракнете Първичен ключ бутон, вижте екранната снимка:
4. След това щракнете върху друго име на колона, за което искате да върне съответстващите стойности, и щракнете Комбинирам за да изберете един разделител за разделяне на комбинираните стойности, вижте екранната снимка:
5, И после щракнете върху Ok всички съответни стойности, базирани на същите стойности, са комбинирани заедно с конкретен разделител, вижте екранните снимки:
 |
 |
 |
Изтеглете и изпробвайте безплатно Kutools за Excel сега!
Демонстрация: Vlookup и свързване на множество съвпадащи стойности в клетка с Kutools за Excel
Най-добрите инструменти за продуктивност в офиса
Усъвършенствайте уменията си за Excel с Kutools за Excel и изпитайте ефективност, както никога досега. Kutools за Excel предлага над 300 разширени функции за повишаване на производителността и спестяване на време. Щракнете тук, за да получите функцията, от която се нуждаете най-много...
Раздел Office Внася интерфейс с раздели в Office и прави работата ви много по-лесна
- Разрешете редактиране и четене с раздели в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Отваряйте и създавайте множество документи в нови раздели на един и същ прозорец, а не в нови прозорци.
- Увеличава вашата производителност с 50% и намалява стотици кликвания на мишката за вас всеки ден!
