Sut i ddychwelyd gwerthoedd chwilio lluosog wedi'u gwahanu gan atalnodau mewn un gell?

Wrth weithio gyda data yn Excel, efallai y byddwch yn dod ar draws sefyllfa lle mae angen i chi ddychwelyd gwerthoedd paru lluosog ar gyfer am-edrych a'u harddangos mewn un gell, wedi'u gwahanu gan atalnodau, dash, ac ati. Er bod swyddogaethau chwilio adeiledig Excel fel "VLOOKUP" a "INDEX-MATCH" yn wych ar gyfer dod o hyd i gydweddiad sengl, mae cyfuno canlyniadau lluosog yn un gell yn gofyn am ddull mwy datblygedig. Bydd yr erthygl hon yn cyflwyno rhai ffyrdd defnyddiol o ddatrys y dasg hon yn Excel.

Dychwelyd gwerthoedd chwilio lluosog mewn cell wedi'i gwahanu gan atalnodau

• Defnyddio TEXTJOIN a FILTER (Excel 365 neu Excel 2021 ac yn ddiweddarach)
• Defnyddio Kutools ar gyfer Excel (Pob fersiwn Excel)
• Defnyddio Swyddogaeth a Ddiffiniwyd gan Ddefnyddiwr

Dychwelyd gwerthoedd chwilio lluosog mewn cell wedi'i gwahanu gan atalnodau gyda swyddogaethau TEXTJOIN a FILTER

Os oes gennych fynediad i Excel 365 neu Excel 2021 a fersiynau diweddarach, defnyddio'r fformiwla arae deinamig yw'r dull symlaf.

Cymhwyswch y fformiwla isod i mewn i gell wag i allbynnu'r canlyniad, ac yna pwyswch yr allwedd "Enter" i gael y canlyniad.

=TEXTJOIN(", ", TRUE, FILTER($B$2:$B$15, $A$2:$A$15=D2))

Yna, llusgwch yr handlen llenwi i lawr i lenwi'r fformiwla i gelloedd eraill, gweler y llun:

Dychwelwch werthoedd chwilio lluosog mewn cell wedi'i wahanu gan atalnodau gyda Kutools ar gyfer Excel

Mae "Kutools for Excel" yn ychwanegiad sydd wedi'i gynllunio i symleiddio tasgau cymhleth yn Excel. Mae ei nodwedd "Edrych Un-i-Llawer" ddatblygedig yn caniatáu ichi dynnu'r holl werthoedd cyfatebol yn ddiymdrech i mewn i un gell gyda amffinydd penodedig - dim angen fformiwlâu cymhleth na chodio VBA.

Ar ôl gosod Kutools ar gyfer Excel, gwnewch fel hyn:

Cliciwch "Kutools" > "Super Lookup"> "Edrych un-i-lawer (yn dychwelyd canlyniadau lluosog)" i agor y blwch deialog. Yn y blwch deialog, nodwch y gweithrediadau fel a ganlyn:

1. Dewiswch yr "Ystod allbwn a gwerthoedd Edrych" yn y blychau testun ar wahân;
2. Dewiswch yr ystod tabl yr ydych am ei ddefnyddio;
3. Nodwch y golofn allweddol a'r golofn dychwelyd o'r gwymplen "Colofn Allweddol" a "Colofn Dychwelyd" ar wahân;
4. Yn olaf, cliciwch ar y botwm "OK".
   

Canlyniad:

Mae'r holl gelloedd cyfatebol gyda'r un gwerth wedi'u cyfuno'n un gell sydd wedi'u gwahanu â'r coma, gweler y sgrinlun:

Awgrymiadau: Os ydych chi am ddefnyddio amffinydd gwahanol i wahanu'r data, gallwch glicio "Dewisiadau" a dewis eich amffinydd dymunol. Yn ogystal, gallwch chi gyflawni gweithrediadau eraill ar y canlyniadau, megis crynhoi, cyfartaleddu, a mwy.

Dychwelyd gwerthoedd chwilio lluosog mewn cell wedi'i gwahanu gan atalnodau gyda Swyddogaeth Ddiffiniedig Defnyddiwr

Os nad oes gennych Excel 365 neu Excel 2021, bydd yr adran hon yn eich arwain gam wrth gam ar sut i greu a defnyddio UDF i ddychwelyd gwerthoedd chwilio lluosog mewn un gell, wedi'u gwahanu gan atalnodau.

1. Daliwch y bysellau "ALT" + "F11" i lawr i agor y ffenestr "Microsoft Visual Basic for Applications".

2. Cliciwch "Mewnosod" > "Modiwl", a gludwch y cod canlynol yn Ffenestr y Modiwl.

Cod VBA: Dychwelwch werthoedd edrych lluosog i mewn i un gell sydd wedi'i gwahanu gan goma

Function JoinLookupValues(lookupValue As String, lookupRange As Range, returnRange As Range, delimiter As String) As String
    'Updateby Extendoffice
    Dim result As String
    Dim i As Long
    result = ""
    For i = 1 To lookupRange.Rows.Count
        If lookupRange.Cells(i, 1).Value = lookupValue Then
            result = result & returnRange.Cells(i, 1).Value & delimiter
        End If
    Next i
    If Len(result) > 0 Then
        JoinLookupValues = Left(result, Len(result) - Len(delimiter))
    Else
        JoinLookupValues = ""
    End If
End Function

3. Yna arbedwch y cod hwn a chau ffenestr y Modiwl, ewch yn ôl i'ch taflen waith, a rhowch y fformiwla isod i mewn i gell wag yr ydych am ddychwelyd y canlyniad. Yna, llusgwch yr handlen llenwi i lawr i lenwi'r fformiwla hon i gelloedd eraill, gweler y llun:

=JoinLookupValues(D2, $A$2:$A$15, $B$2:$B$15, ", ")

I gloi, gall dychwelyd gwerthoedd chwilio lluosog mewn un gell wedi'u gwahanu gan atalnodau gael ei gyflawni'n effeithlon gan ddefnyddio gwahanol ddulliau, yn dibynnu ar eich fersiwn Excel a'ch anghenion. Trwy ddewis y dull sy'n cyd-fynd orau â'ch gofynion, gallwch symleiddio'ch dadansoddiad data a gwella cynhyrchiant. Os oes gennych ddiddordeb mewn archwilio mwy o awgrymiadau a thriciau Excel, mae ein gwefan yn cynnig miloedd o sesiynau tiwtorial.

Erthyglau cysylltiedig: