Note: The other languages of the website are Google-translated. Back to English

Sut i wylio a chyd-fynd â sawl gwerth cyfatebol yn Excel?

Fel y gwyddom i gyd, mae'r Vlookup gall swyddogaeth yn Excel ein helpu i edrych ar werth a dychwelyd y data cyfatebol mewn colofn arall, ond yn gyffredinol, dim ond os oes data paru lluosog y gall gael y gwerth cymharol cyntaf. Yn yr erthygl hon, byddaf yn siarad am sut i wylio a chyd-fynd â gwerthoedd cyfatebol lluosog mewn un gell yn unig neu restr fertigol.

Vlookup a dychwelyd gwerthoedd paru lluosog yn fertigol gyda'r fformiwla

Vlookup a concatenate gwerthoedd paru lluosog mewn cell gyda Swyddogaeth Diffiniedig Defnyddiwr

Vlookup a concatenate gwerthoedd paru lluosog mewn cell gyda Kutools ar gyfer Excel


Gan dybio, mae gen i'r ystod ganlynol o ddata, i gael yr holl werthoedd cyfatebol yn seiliedig ar werth penodol yn fertigol fel y dangosir y screenshot canlynol, gallwch gymhwyso fformiwla arae.

doc vlookup concatenate 1

1. Rhowch y fformiwla hon: =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))),"") mewn cell wag lle rydych chi am roi'r canlyniad, er enghraifft, E2, ac yna pwyso Ctrl + Shift + Enter allweddi gyda'i gilydd i gael y sylfaen gwerth cymharol ar faen prawf penodol, gweler y screenshot:

doc vlookup concatenate 2

Nodyn: Yn y fformiwla uchod:

A1: A16 yw'r ystod golofn sy'n cynnwys y gwerth penodol rydych chi am edrych amdano;

D2 yn nodi'r gwerth penodol yr ydych am ei wylio;

B1: B16 yw'r ystod golofn rydych chi am ddychwelyd y data cyfatebol ohoni;

$ 1: $ 16 yn nodi cyfeirnod y rhesi o fewn yr ystod.

2. Yna dewiswch gell E2, a llusgwch y handlen llenwi i lawr i'r celloedd nes i chi gael celloedd gwag, a rhestrir yr holl werthoedd paru i'r golofn fel y dangosir ar-lein:

doc vlookup concatenate 3


Yn lle cael y gwerthoedd cymharol yn fertigol, weithiau, rydych chi am gael y gwerthoedd paru i mewn i un cell a'u cyd-daro â gwahanydd penodol. Yn yr achos hwn, gall y Swyddogaeth Diffiniedig Defnyddiwr ganlynol wneud ffafr i chi.

1. Dal i lawr y ALT + F11 allweddi i agor y Microsoft Visual Basic ar gyfer Ceisiadau ffenestr.

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

Cod VBA: Vlookup a concatenate gwerthoedd paru lluosog mewn cell

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. Yna arbedwch a chau'r cod hwn, ewch yn ôl i'r daflen waith, a nodi'r fformiwla hon: = cusvlookup (D2, A1: B16,2) i mewn i gell wag lle rydych chi am roi'r canlyniad, a gwasgwch Rhowch yn allweddol, mae'r holl werthoedd cyfatebol sy'n seiliedig ar ddata penodol wedi'u dychwelyd i un cell gyda gwahanydd gofod, gweler y screenshot:

doc vlookup concatenate 4

Nodyn: Yn y fformiwla uchod: D2 yn nodi'r gwerthoedd celloedd yr ydych am edrych arnynt, A1: B16 yw'r ystod ddata rydych chi am nôl y data, y rhif 2 yw'r rhif colofn y mae'r gwerth paru i'w ddychwelyd ohono, gallwch newid cyfeiriadau traethodau ymchwil at eich angen.


Os oes gennych Kutools ar gyfer Excel, Gyda'i Rhesi Cyfuno Uwch nodwedd, gallwch chi orffen y swydd hon yn gyflym yn rhwydd. Gall y nodwedd hon eich helpu i gyfuno'r holl werthoedd paru â delimiters penodol yn seiliedig ar yr un data mewn colofn arall.

Kutools ar gyfer Excel : gyda mwy na 300 o ychwanegiadau Excel defnyddiol, am ddim i geisio heb unrhyw gyfyngiad mewn 30 diwrnod.

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

1. Dewiswch yr ystod ddata rydych chi am gael y gwerthoedd cyfatebol yn seiliedig ar y data penodol.

2. Yna cliciwch Kutools > Uno a Hollti > Rhesi Cyfuno Uwch, gweler y screenshot:

3. Yn y Rhesi Cyfuno Uwch blwch deialog, cliciwch enw'r golofn rydych chi am ei chyfuno yn seiliedig, yna cliciwch Allwedd Cynradd botwm, gweler y screenshot:

doc vlookup concatenate 6

4. Yna cliciwch enw colofn arall rydych chi am ddychwelyd y gwerthoedd cyfatebol, a chlicio Cyfunwch i ddewis un gwahanydd i wahanu'r gwerthoedd cyfun, gweler y screenshot:

doc vlookup concatenate 7

5. Ac yna cliciwch Ok botwm, mae'r holl werthoedd cyfatebol sy'n seiliedig ar yr un gwerthoedd wedi'u cyfuno ynghyd â gwahanydd penodol, gweler sgrinluniau:

doc vlookup concatenate 8 2 doc vlookup concatenate 9

 Dadlwythwch a threial am ddim Kutools ar gyfer Excel Now!


Kutools ar gyfer Excel: gyda mwy na 300 o ychwanegiadau Excel defnyddiol, am ddim i geisio heb unrhyw gyfyngiad mewn 30 diwrnod. Dadlwythwch a threial am ddim Nawr!

Yr Offer Cynhyrchedd Swyddfa Gorau

Mae Kutools for Excel yn Datrys y rhan fwyaf o'ch Problemau, ac yn Cynyddu Eich Cynhyrchedd 80%

  • Ailddefnyddio: Mewnosod yn gyflym fformwlâu cymhleth, siartiau ac unrhyw beth rydych chi wedi'i ddefnyddio o'r blaen; Amgryptio Celloedd gyda chyfrinair; Creu Rhestr Bostio ac anfon e-byst ...
  • Bar Fformiwla Gwych (golygu llinellau lluosog o destun a fformiwla yn hawdd); Cynllun Darllen (darllen a golygu nifer fawr o gelloedd yn hawdd); Gludo i'r Ystod Hidlo...
  • Uno Celloedd / Rhesi / Colofnau heb golli Data; Cynnwys Celloedd Hollt; Cyfuno Rhesi / Colofnau Dyblyg... Atal Celloedd Dyblyg; Cymharwch y Meysydd...
  • Dewiswch Dyblyg neu Unigryw Rhesi; Dewiswch Blank Rows (mae pob cell yn wag); Darganfyddiad Gwych a Darganfyddiad Niwlog mewn Llawer o Lyfrau Gwaith; Dewis ar Hap ...
  • Copi Union Celloedd Lluosog heb newid cyfeirnod fformiwla; Auto Creu Cyfeiriadau i Daflenni Lluosog; Mewnosod Bwledi, Blychau Gwirio a mwy ...
  • Testun Detholiad, Ychwanegu Testun, Tynnu yn ôl Swydd, Tynnwch y Gofod; Creu ac Argraffu Subtotals Paging; Trosi rhwng Cynnwys a Sylwadau Celloedd...
  • Hidlo Super (arbed a chymhwyso cynlluniau hidlo i ddalenni eraill); Trefnu Uwch yn ôl mis / wythnos / dydd, amlder a mwy; Hidlo Arbennig gan feiddgar, italig ...
  • Cyfuno Llyfrau Gwaith a Thaflenni Gwaith; Uno Tablau yn seiliedig ar golofnau allweddol; Rhannwch Ddata yn Daflenni Lluosog; Trosi Swp xls, xlsx a PDF...
  • Mwy na 300 o nodweddion pwerus. Yn cefnogi Swyddfa / Excel 2007-2019 a 365. Yn cefnogi pob iaith. Defnydd hawdd yn eich menter neu sefydliad. Nodweddion llawn treial am ddim 30 diwrnod. Gwarant arian yn ôl 60 diwrnod.
tab kte 201905

Mae Tab Office yn Dod â rhyngwyneb Tabbed i'r Swyddfa, a Gwneud Eich Gwaith yn Haws o lawer

  • Galluogi golygu a darllen tabbed yn Word, Excel, PowerPoint, Cyhoeddwr, Mynediad, Visio a Phrosiect.
  • Agor a chreu dogfennau lluosog mewn tabiau newydd o'r un ffenestr, yn hytrach nag mewn ffenestri newydd.
  • Yn cynyddu eich cynhyrchiant 50%, ac yn lleihau cannoedd o gliciau llygoden i chi bob dydd!
gwaelod officetab
sylwadau (16)
Dim sgôr eto. Byddwch y cyntaf i sgorio!
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Sut i gael y canlyniad. Helpwch os gwelwch yn dda. data data1 canlyniad a 1 a1 b 2 a2 c b1 b2 c1 c2
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Wrth ddefnyddio'r cusvlookup a oes ffordd i ychwanegu'r enw olaf yn ogystal â choma yn y canol a allai ymddangos yng Ngholofn C
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Wedi caru'r swyddogaeth ar gyfer Excel 2013 ond wedi ei ddiwygio ychydig i newid y cymeriad gwahanu i ";" yn lle " " ac yna tynnu'r rhagddodiad " " ;" o'r gwerthoedd cyfunol Byddai canlyniadau sy'n cyfateb i werthoedd yn fy enghraifft i wedi ;canlyniad01 neu ;canlyniad01;canlyniad02 . Wedi ychwanegu'r ychwanegol Os Chwith(xResult, 1) = ";" i gael gwared ar unrhyw ychwanegol ";" ar ddechrau'r llinyn os mai dyma'r nod 1af. Rwy'n siŵr bod ffordd daclusach o'i wneud ond fe weithiodd i mi. :) Swyddogaeth CusVlookup(pGwerth Fel Llinynnol, pWorkRng Fel Ystod, pIndex Cyhyd) Dim rng Fel Ystod Dim xResult Fel Llinyn xResult = " Ar gyfer Pob rng Mewn pWorkRng Os rng = pGwerth Yna xResult = xResult &";" & rng.Offset(0, pIndex - 1) Os Chwith(xResult, 1) = ";" Yna xResult = MID(xResult,2,255) Gorffen Os Diwedd Os Nesaf CusVlookup = Swyddogaeth Diwedd Canlyniad xResult
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Gwnewch amod ar gyfer canlyniad os yw'n wag.

Swyddogaeth CusVlookup(lookupval, searchrange As Range, indexcol As Long)
'diweddaru erbyn Extendoffice 20151118
Dim x Fel Ystod
Canlyniad dim Fel Llinynnol
canlyniad=""
Ar gyfer Pob x Yn ystod chwilio
Os x = lookupval Yna
If Not result = " " Yna
canlyniad = canlyniad & " " & x.Offset(0, mynegaicol - 1)
arall
canlyniad = x.Offset(0, mynegaicol - 1)
Gorffennwch Os
Nesaf x
CusVlookup = canlyniad
Swyddogaeth End
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Mae hyn yn anhygoel ond rydw i'n edrych am rywbeth arall, mae gen i fwrdd gyda RollNo StudentName sub1, sub2, sub3 ... Cyfanswm y Canlyniad, Pan fyddaf yn mynd i mewn i Rollnumber dylai roi canlyniad fel "SName Sub1 64, sub2 78, ... Cyfanswm 389, Pas Canlyniad", a yw'n bosibl
Lleihawyd y sylw hwn gan y safonwr ar y wefan
A oes ffordd i ddileu'r gwerthoedd dyblyg yn y cydgadwyn?
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Helo, Jacob,
Efallai y bydd yr erthygl ganlynol yn eich helpu i ddatrys eich problem.
https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html

Ceisiwch, gobeithio y gall eich helpu!
Lleihawyd y sylw hwn gan y safonwr ar y wefan
A oes modd rhestru'r gwerthoedd dyblyg unwaith yn unig, gan ddefnyddio'r cod vba a'r fformiwla uchod? Nid wyf yn siŵr ble i roi’r datganiad countif>1 yn y bar fformiwla, nac yn y vba ei hun. Helpwch os gwelwch yn dda
Lleihawyd y sylw hwn gan y safonwr ar y wefan
gallwch ychwanegu dau amod ychwanegol i hepgor celloedd gwag ac i hepgor dyblygiadau:Ar gyfer i = 1 I CriteriaRange.Count
If CriteriaRange.Cells(i).Value = Cyflwr Yna
If ConcatenateRange.Cells(i).Value <> "" Yna 'SKIP BANKS
Os yw InStr(xResult, ConcatenateRange.Cells(i).Value) = 0 Yna ' SGIPIO OS YW'N DYNOL
xResult = xCanlyniad a Gwahanydd & ConcatenateRange.Cells(i).Value
Gorffennwch Os
Gorffennwch Os
Gorffennwch Os
Nesaf i
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Mae'n rhaid i mi ddweud, rwyf wedi bod yn ceisio cael fformiwla ar gyfer cyfuno gwerthoedd lluosog a'u dychwelyd i un gell am 2 ddiwrnod bellach. Mae'r "Sut i" hwn wedi fy achub !! Diolch yn fawr iawn! Fyddwn i byth wedi ei gael heb eich Modiwl!
Ond mae gen i 2 gwestiwn. Mae'r deliminator gen i fel coma yn lle gofod ac oherwydd hynny mae'n dechrau gyda choma. A oes ffordd i atal y coma cychwyn ond cadw'r gweddill?
Fy ail gwestiwn yw; Pan fyddaf yn defnyddio'r handlen llenwi mae'n newid y gwerthoedd amrediad yn ogystal â gwerth y gell rydw i eisiau edrych i fyny. Rwyf am iddo barhau i newid y rhif cell yr wyf am edrych i fyny ond cadw'r un gwerthoedd amrediad. Sut alla i wneud i hyn ddigwydd?

Diolch yn fawr iawn am eich help!!
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Gweithiodd y cusVlookup yn wych i mi. Ffordd arall o gael gwahanydd gwahanol yw lapio dwy swyddogaeth amnewid. Mae'r cyntaf (o'r tu mewn i'r tu allan) yn disodli'r gofod cyntaf heb unrhyw le, mae'r ail yn disodli pob gofod arall gyda " / " yn fy un i. Gellid defnyddio "," os ydych eisiau atalnodau.
=SUBSTITUTE(SUBSTITUTE(cusVlookup(D2,Tabl1,2)," ","",1)," "," /")

Hefyd, os nad eich gwerth chwilio yw'r golofn gyntaf, gallwch ddefnyddio 0 neu rifau negyddol i fynd i'r golofn ar y chwith.
=SUBSTITUTE(SUBSTITUTE(cusVlookup(D2,Table1,-1))," ","",1)," "," /")
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Helo, jeff,
Diolch am eich rhannu, mae'n rhaid eich bod chi'n ddyn twymgalon.
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Mae hyn yn gweithio'n wych i mi - a oes yna beth bynnag i'w newid ei fod yn gwirio a yw'r gell yn cynnwys yn hytrach na chyfatebiaeth gyflawn? Yn y bôn mae gen i restr o dasgau lle:
Colofn A: Dibyniaethau (e.e. 10003 10004 10008)
Colofn B: Cyfeirnod Tasg (ee 10001)
Colofn C: Tasgau Dibynnol (y golofn ar gyfer canlyniad y fformiwla) - lle byddai'n edrych ar gyfeirnod y dasg i weld pa resi sy'n ei gynnwys yng Ngholofn A, ac yna'n rhestru Cyfeirnod Tasg y tasgau hynny.

Ee:

Rhes | Colofn A | Colofn B | Colofn C
1 | | 10001 | 10002 10003
2 | 10001 | 10002 | 10003
3 | 10001 10002 | 10003 |
Lleihawyd y sylw hwn gan y safonwr ar y wefan
byddech am ddefnyddio'r ffwythiant Instr() a fydd yn gwirio am rywbeth mewn llinyn o destun mewn cell. Gallwch hefyd ddefnyddio Chwith() a De() os ydych yn chwilio am y manylion cychwyn neu orffen.
Lleihawyd y sylw hwn gan y safonwr ar y wefan
A oes unrhyw ffordd i gael yr "enw" unigryw ar gyfer "dosbarth1"
Lleihawyd y sylw hwn gan y safonwr ar y wefan
Helo, sym-john,
Efallai y gall yr erthygl isod ddatrys eich problem, edrychwch arno:
https://www.extendoffice.com/documents/excel/3381-excel-extract-unique-values-with-criteria.html
Nid oes unrhyw sylwadau wedi'u postio yma eto
Gadewch eich sylwadau
Postio fel Gwestai
×
Graddiwch y swydd hon:
0   Cymeriadau
Lleoliadau a Awgrymir