Как да направите vlookup, за да върнете множество стойности в една клетка в Excel?
Обикновено в Excel, когато използвате функцията VLOOKUP, ако има няколко стойности, които отговарят на критериите, можете да получите само първата. Но понякога искате да върнете всички съответстващи стойности, които отговарят на критериите, в една клетка, както е показано на следната екранна снимка, как бихте могли да го разрешите?
Vlookup за връщане на множество стойности в една клетка с функция TEXTJOIN (Excel 2019 и Office 365)
- Vlookup за връщане на всички съответстващи стойности в една клетка
- Vlookup за връщане на всички съответстващи стойности без дубликати в една клетка
Vlookup за връщане на множество стойности в една клетка с дефинирана от потребителя функция
- Vlookup за връщане на всички съответстващи стойности в една клетка
- Vlookup за връщане на всички съответстващи стойности без дубликати в една клетка
Vlookup за връщане на множество стойности в една клетка с полезна функция
Vlookup за връщане на множество стойности в една клетка с функция TEXTJOIN (Excel 2019 и Office 365)
Ако имате по-висока версия на Excel като Excel 2019 и Office 365, има нова функция - ПРИСЪЕДИНЕТЕ СЕ, с тази мощна функция можете бързо да направите vlooking и да върнете всички съответстващи стойности в една клетка.
Vlookup за връщане на всички съответстващи стойности в една клетка
Моля, приложете формулата по-долу в празна клетка, където искате да поставите резултата, след което натиснете Ctrl + Shift + Enter клавиши заедно, за да получите първия резултат, и след това плъзнете манипулатора за попълване надолу до клетката, която искате да използвате тази формула, и ще получите всички съответни стойности, както е показано на екранната снимка по-долу:
Vlookup за връщане на всички съответстващи стойности без дубликати в една клетка
Ако искате да върнете всички съответстващи стойности въз основа на данните за търсене без дубликати, формулата по-долу може да ви помогне.
Моля, копирайте и поставете следната формула в празна клетка, след което натиснете Ctrl + Shift + Enter ключовете заедно, за да получите първия резултат, и след това копирайте тази формула, за да попълните други клетки, и ще получите всички съответстващи стойности без дублиращите се, както е показано на екранната снимка по-долу:
Vlookup за връщане на множество стойности в една клетка с дефинирана от потребителя функция
Горната функция TEXTJOIN е достъпна само за Excel 2019 и Office 365, ако имате други по-ниски версии на Excel, трябва да използвате някои кодове за завършване на тази задача.
Vlookup за връщане на всички съответстващи стойности в една клетка
1. Задръжте натиснат ALT + F11 ключове и отваря Microsoft Visual Basic за приложения прозорец.
2. Щракнете Поставете > Модулии поставете следния код в Прозорец на модула.
VBA код: Vlookup за връщане на множество стойности в една клетка
Function ConcatenateIf(CriteriaRange As Range, Condition As Variant, ConcatenateRange As Range, Optional Separator As String = ",") As Variant
'Updateby Extendoffice
Dim xResult As String
On Error Resume Next
If CriteriaRange.Count <> ConcatenateRange.Count Then
ConcatenateIf = CVErr(xlErrRef)
Exit Function
End If
For i = 1 To CriteriaRange.Count
If CriteriaRange.Cells(i).Value = Condition Then
xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
End If
Next i
If xResult <> "" Then
xResult = VBA.Mid(xResult, VBA.Len(Separator) + 1)
End If
ConcatenateIf = xResult
Exit Function
End Function
3. След това запазете и затворете този код, върнете се в работния лист и въведете тази формула: =CONCATENATEIF($A$2:$A$11, E2, $C$2:$C$11, ", ") в конкретна празна клетка, където искате да поставите резултата, след това плъзнете манипулатора за попълване надолу, за да получите всички съответстващи стойности в една клетка, която искате, вижте екранната снимка:
Vlookup за връщане на всички съответстващи стойности без дубликати в една клетка
За да игнорирате дубликатите в върнатите съответстващи стойности, моля, направете следния код.
1. Задръжте натиснат Alt + F11 за да отворите Microsoft Visual Basic за приложения прозорец.
2. Щракнете Поставете > Модулии поставете следния код в Прозорец на модула.
VBA код: Vlookup и връщане на множество уникални съвпадащи стойности в една клетка
Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
'Updateby Extendoffice
Dim xDic As New Dictionary
Dim xRows As Long
Dim xStr As String
Dim i As Long
On Error Resume Next
xRows = LookupRange.Rows.Count
For i = 1 To xRows
If LookupRange.Columns(1).Cells(i).Value = Lookupvalue Then
xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
End If
Next
xStr = ""
MultipleLookupNoRept = xStr
If xDic.Count > 0 Then
For i = 0 To xDic.Count - 1
xStr = xStr & xDic.Keys(i) & ","
Next
MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
End If
End Function
3. След като въведете кода, щракнете Инструменти > Препратки в отвореното Microsoft Visual Basic за приложения прозорец и след това изскочи Препратки – VBAProject диалогов прозорец, проверете Microsoft Scripting Runtime опция в Налични препратки списъчно поле, вижте екранни снимки:
 |
 |
 |
4, След това кликнете OK за да затворите диалоговия прозорец, запишете и затворете прозореца с код, върнете се в работния лист и въведете тази формула: =MultipleLookupNoRept(E2,$A$2:$C$11,3) into a blank cell where you want to output the result, and then drag the fill hanlde down to get all matching values, see screenshot:
Vlookup за връщане на множество стойности в една клетка с полезна функция
Ако имате нашите Kutools за Excel, Със своята Разширено комбиниране на редове функция, можете бързо да обедините или комбинирате редовете въз основа на една и съща стойност и да направите някои изчисления, колкото е необходимо.
След инсталиране Kutools за Excel, моля, направете следното:
1. Изберете диапазона от данни, в който искате да комбинирате данни от една колона въз основа на друга колона.
2. Щракнете Kutools > Обединяване и разделяне > Разширено комбиниране на редове, вижте екранна снимка:
3. В изскочилото Разширено комбиниране на редове диалогов прозорец:
- Щракнете върху името на ключовата колона, въз основа на която да се комбинира, и след това щракнете Първичен ключ
- След това щракнете върху друга колона, чиито данни искате да комбинирате въз основа на ключовата колона, и щракнете Комбинирам за да изберете един разделител за разделяне на комбинираните данни.
4. След това кликнете OK и ще получите следните резултати:
 |
|
 |
Изтеглете и изпробвайте безплатно Kutools за Excel сега!
Още относителни статии:
- Функция VLOOKUP с някои основни и разширени примери
- В Excel функцията VLOOKUP е мощна функция за повечето потребители на Excel, която се използва за търсене на стойност в най-лявата част на диапазона от данни и връщане на съответстваща стойност в същия ред от колона, която сте посочили. Този урок говори за това как да използвате функцията VLOOKUP с някои основни и разширени примери в Excel.
- Връща множество съответстващи стойности въз основа на един или няколко критерия
- Обикновено търсенето на конкретна стойност и връщането на съответстващия елемент е лесно за повечето от нас с помощта на функцията VLOOKUP. Но опитвали ли сте някога да върнете множество съвпадащи стойности въз основа на един или повече критерии? В тази статия ще представя някои формули за решаване на тази сложна задача в Excel.
- Vlookup и връщане на множество стойности вертикално
- Обикновено можете да използвате функцията Vlookup, за да получите първата съответстваща стойност, но понякога искате да върнете всички съответстващи записи въз основа на конкретен критерий. В тази статия ще говоря за това как да направя vlookup и да върна всички съвпадащи стойности вертикално, хоризонтално или в една клетка.
- Vlookup и връщане на множество стойности от падащия списък
- В Excel, как бихте могли да направите vlooking и да върнете множество съответстващи стойности от падащ списък, което означава, че когато изберете един елемент от падащия списък, всичките му относителни стойности се показват наведнъж. В тази статия ще представя решението стъпка по стъпка.
Най-добрите инструменти за продуктивност в офиса
Усъвършенствайте уменията си за Excel с Kutools за Excel и изпитайте ефективност, както никога досега. Kutools за Excel предлага над 300 разширени функции за повишаване на производителността и спестяване на време. Щракнете тук, за да получите функцията, от която се нуждаете най-много...
Раздел Office Внася интерфейс с раздели в Office и прави работата ви много по-лесна
- Разрешете редактиране и четене с раздели в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Отваряйте и създавайте множество документи в нови раздели на един и същ прозорец, а не в нови прозорци.
- Увеличава вашата производителност с 50% и намалява стотици кликвания на мишката за вас всеки ден!
