Namig: drugi jeziki so prevedeni v Google. Lahko obiščete English različico te povezave.
Vpiši se
x
or
x
x
Registracija
x

or

Kako preusmeriti in združiti več ustreznih vrednosti v Excelu?

Kot vsi poznamo, Vlookup funkcija v Excelu nam lahko pomaga pri iskanju vrednosti in vrnitvi ustreznih podatkov v drugem stolpcu, vendar na splošno lahko dobi prvo relativno vrednost samo, če obstaja več primernih podatkov. V tem članku bom govoril o tem, kako v vklopu in združevanju več ustreznih vrednosti v samo eni celici ali navpičnem seznamu.

Vlookup in vrnitev več ujemajočih se vrednosti navpično s formulo

Vlookup in združite več ujemajočih se vrednosti v celici z uporabniško določeno funkcijo

Vlookup in združite več ujemajočih se vrednosti v celici z orodji Kutools for Excel


V vklopu in združite več primerjalnih vrednosti v celici:

z Kutools za ExcelJe Napredne kombinacije vrst uporabno, lahko hitro združite več podvojenih vrstic v en zapis na podlagi stolpcev ključnih besed in za druge stolpce lahko uporabijo tudi nekatere izračune, kot so vsota, povprečje, štetje itd.

  • 1. Določite stolpec s ključi, na katerega želite združiti drug stolpec na podlagi;
  • 2. Izberite ločilo za vaše kombinirane podatke.

doc združuje besedilo na podlagi meril 10

Kutools za Excel: z več kot 200 priročnimi dodatki Excel, lahko brezplačno poskusite brez omejitev v dnevih 60. Prenesite in brezplačno preizkusite zdaj!



Domneva, da imam naslednje vrste podatkov, da dobim vse ustrezne vrednosti, ki temeljijo na določeni vrednosti navpično, kot je prikazano spodaj prikazano sliko, lahko uporabite matrično formulo.

doc vlookup concatenate 1

1. Vnesite to formulo: =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))),"") v prazno celico, kjer želite dati rezultat, na primer E2, in pritisnite Ctrl + Shift + Enter skupaj, da bi dobili relativno vrednost bazo na določenem merilu, si oglejte sliko zaslona:

doc vlookup concatenate 2

Opombe: V zgornji formuli:

A1: A16 je obseg stolpcev, ki vsebuje določeno vrednost, ki jo želite iskati;

D2 označuje določeno vrednost, ki jo želite ogledati;

B1: B16 je obseg stolpcev, iz katerega želite vrniti ustrezne podatke;

$ 1: $ 16 označuje referenčne črte znotraj območja.

2. Nato izberite celico E2 in povlecite ročico za polnjenje navzdol v celice, dokler ne dobite praznih celic, in vse ustrezne vrednosti so navedene v stolpcu, kot je prikazano spodaj prikazano sliko:

doc vlookup concatenate 3


Namesto, da bi relativne vrednosti dosegli vertikalno, včasih želite, da so ujemajoče vrednosti v eno celico in jih združite s posebnim ločilom. V tem primeru vam lahko naredi naslednja uporabniška funkcija.

1. Drži dol ALT + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

2. Kliknite Vstavi > Moduli, in prilepite naslednjo kodo v Moduli Okno.

VBA koda: v celici spremljajte in združite več ujemajočih se vrednosti

Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice 20151118
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. Nato shranite in zaprite to kodo, se vrnite na delovni list in vnesite to formulo: = cusvlookup (D2, A1: B16,2) v prazno celico, kjer želite dati rezultat, in pritisnite Vnesite ključ, vse ustrezne vrednosti, ki temeljijo na določenih podatkih, so bile vrnjene v eno celico s separatorjem prostora, glejte sliko zaslona:

doc vlookup concatenate 4

Opombe: V zgornji formuli: D2 označuje vrednosti celice, ki jih želite iskati, A1: B16 je obseg podatkov, za katerega želite pridobiti podatke, številko 2 je številka stolpca, iz katere naj se vrne ustrezna vrednost, lahko spremenite te navedbe glede na vaše potrebe.


Če imate Kutools za Excel, Z njegovim Napredne kombinacije vrst funkcijo, lahko hitro opravite to delo z lahkoto. Ta funkcija vam lahko pomaga združiti vse ustrezne vrednosti s posebnimi razmejitvami, ki temeljijo na istih podatkih v drugem stolpcu.

Kutools za Excel : z več kot 300 priročnimi dodatki Excel, lahko brezplačno poskusite brez omejitev v dnevih 60.

Po namestitvi Kutools za Excel, storite naslednje:

1. Izberite obseg podatkov, na katerega želite dobiti ustrezne vrednosti na podlagi določenih podatkov.

2. Nato kliknite Kutools > vsebina > Napredne kombinacije vrst, si oglejte sliko zaslona:

doc vlookup concatenate 5

3. v Napredne kombinacije vrst pogovorno okno, kliknite ime stolpca, na katerega želite združiti, in nato kliknite Primarni ključ gumb, si oglejte posnetek zaslona:

doc vlookup concatenate 6

4. Nato kliknite drugo ime stolpca, v katerega želite vrniti ujemajoče vrednosti, in kliknite Združite če želite izbrati enega ločila, da ločite kombinirane vrednosti, si oglejte posnetek zaslona:

doc vlookup concatenate 7

5. In nato kliknite Ok gumb, vse ustrezne vrednosti, ki temeljijo na istih vrednostih, so bile združene skupaj s posebnim ločilom, glej slike s posnetkov:

doc vlookup concatenate 8  2 doc vlookup concatenate 9

Prenesite in brezplačno preizkusite Kutools za Excel zdaj!


Kutools za Excel: z več kot 200 priročnimi dodatki Excel, lahko brezplačno poskusite brez omejitev v dnevih 60. Prenesite in brezplačno preizkusite zdaj!



Priporočena orodja za produktivnost za Excel

Kutools za Excel vam pomaga, da vedno končate delo pred časom in izstopite iz množice

  • Več kot zmogljive napredne funkcije 300, zasnovane za 1500 delovne scenarije, ki povečujejo produktivnost z 70%, vam dajejo več časa za skrb za družino in uživanje v življenju.
  • Ne potrebujete več pomnilniških formul in VBA kod, od zdaj naprej pa dajate svojim možganom počitek.
  • Postanite strokovnjak za Excel v minutah 3, zapletene in ponavljajoče se operacije lahko opravite v nekaj sekundah,
  • Vsak dan zmanjšajte število operacij tipkovnice in miške, zdaj se poslovite od poklicnih bolezni.
  • 110,000 visoko učinkovite ljudi in 300 + svetovno priznanih podjetij izbiro.
  • Brezplačna preizkusna različica 60 dneva. 60-dnevno jamstvo vračila denarja. 2 let brezplačne nadgradnje in podpore.

Prinaša kartično brskanje in urejanje za Microsoft Office, veliko močnejši od zavihkov brskalnika

  • Office Tab je namenjen za Word, Excel, PowerPoint in druge Office aplikacije: Založnik, Dostop, Visio in Projekt.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Z 50% poveča vašo produktivnost in vsak dan zmanjša na stotine klikov z miško!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Russell · 2 months ago
    This is working great for me - is there anyway to change it that it checks if the cell contains rather than a complete match? Basically I have a list of tasks where:
    Column A: Dependencies (eg 10003 10004 10008)
    Column B: Task Reference (eg 10001)
    Column C: Dependent Tasks (the column for the formula result) - where it would lookup the task reference to see which rows contain it in Column A, and then list the Task Reference of those tasks.

    E.g:

    Row | Column A | Column B | Column C
    1 | | 10001 | 10002 10003
    2 | 10001 | 10002 | 10003
    3 | 10001 10002 | 10003 |
  • To post as a guest, your comment is unpublished.
    jeff · 3 months ago
    The cusVlookup worked great for me. Another way to have a different separator is to wrap in two substitute functions. The first (from inside to out) replaces the first space with no space, the second replaces all other spaces with a " / " in mine. Could use "," if you want commas.
    =SUBSTITUTE(SUBSTITUTE(cusVlookup(D2,Table1,2)," ","",1)," "," / ")

    Also, if your lookup value isn't the first column, you can use 0 or negative numbers to go to column to the left.
    =SUBSTITUTE(SUBSTITUTE(cusVlookup(D2,Table1,-1)," ","",1)," "," / ")
    • To post as a guest, your comment is unpublished.
      skyyang · 3 months ago
      Hi, jeff,
      Thanks for your sharing, you must be a warmhearted man.
  • To post as a guest, your comment is unpublished.
    Dana Rohde · 11 months ago
    I have to say, I have been trying to get a formula for combining multiple values and returning them to a single cell for 2 days now. This "How To" has saved me!! Thank you SO much! I would never have gotten it without your Module!
    I do have 2 questions though. I have the deliminator as a comma instead of a space and because of that it starts out with a comma. Is there a way to prevent the start comma but keep the rest?
    My second question is; When I use the fill handle it changes the range values as well as the cell value I want to look up. I want it to continue to change the cell number I want to look up but keep the same range values. How can I make this happen?

    Thank you so much for your help!!
  • To post as a guest, your comment is unpublished.
    Jacob Nelson · 1 years ago
    Is there a way to delete the duplicate values in the concatenate?
  • To post as a guest, your comment is unpublished.
    Sree Kanth · 1 years ago
    This is amazing but i am looking for something else, i have a table with RollNo StudentName sub1, sub2, sub3 ... Total Result, When I enter Rollnumber it should give a result like "SName Sub1 64, sub2 78,... Total 389, Result pass", is it possible