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

Kako v Excel iskati in vrniti več vrednosti brez dvojnikov? 

Včasih boste morda želeli vlookup in vrniti več ujemajočih se vrednosti v eno celico hkrati. Toda, če je v vrnjene celice vnesenih nekaj ponavljajočih se vrednosti, kako lahko prezrete dvojnike in ohranite edinstvene vrednosti le, če vrnete vse ujemajoče se vrednosti, kot je prikazano na sliki zaslona, ​​prikazani v Excelu?

doc vrne več enoličnih vrednosti 1

Vlookup in vrnitev več ujemajočih se vrednosti brez dvojnikov z uporabniško določeno funkcijo


Vlookup in vrnitev več ujemajočih se vrednosti brez dvojnikov z uporabniško določeno funkcijo

Naslednja koda VBA vam lahko pomaga vrniti več ujemajočih se vrednosti brez dvojnikov. Naredite to:

1. Držite tipko Alt + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

2. Kliknite Vstavi > Moduliin prilepite naslednjo kodo v Moduli Okno.

Koda VBA: Vlookup in vrnitev več unikatnih ujemajočih se vrednosti:

Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
    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. Po vstavitvi kode kliknite Orodja > Reference v odprtem Microsoft Visual Basic za aplikacije okno in nato v izpuščenem Reference - VBAProject pogovorno okno, preverite Izvajanje Microsoftovih skriptov možnost v Razpoložljive reference seznam, glej posnetek zaslona:

doc vrne več enoličnih vrednosti 2

4. Nato kliknite OK da zaprete pogovorno okno, shranite in zaprite okno s kodo, se vrnite na delovni list in vnesite to formulo: =MultipleLookupNoRept(E2,A2:C17,3) v prazno celico, kjer želite izpisati rezultat, pritisnite Vnesite tipko, da dobite pravi rezultat, kot ga potrebujete. Oglejte si posnetek zaslona:

doc vrne več enoličnih vrednosti 3

Opombe: V zgornji formuli, E2 je merilo, ki ga želite pregledati, A2: C17 je obseg podatkov, ki ga želite uporabiti, število 3 je številka stolpca, ki vsebuje vrnjene vrednosti.


Najboljša orodja za pisarniško produktivnost

Kutools for Excel Reši večino vaših težav in poveča vašo produktivnost za 80 %

  • Ponovna uporaba: Hitro vstavite zapletene formule, grafikoni in vse, kar ste že uporabljali; Šifriraj celice z geslom; Ustvari poštni seznam in pošiljanje e-pošte ...
  • Vrstica Super Formula (enostavno urejanje več vrstic besedila in formule); Bralna postavitev (enostavno branje in urejanje velikega števila celic); Prilepite v filtrirani obseg...
  • Združi celice / vrstice / stolpce brez izgube podatkov; Vsebina razdeljenih celic; Združi podvojene vrstice / stolpce... prepreči podvojene celice; Primerjaj obsege...
  • Izberite Duplicate ali Unique Vrstice; Izberite prazne vrstice (vse celice so prazne); Super Find in Fuzzy Find v mnogih delovnih zvezkih; Naključna izbira ...
  • Natančna kopija Več celic brez spreminjanja sklica formule; Samodejno ustvarjanje referenc na več listov; Vstavi oznake, Potrditvena polja in še več ...
  • Izvleček besedila, Dodaj besedilo, Odstrani po položaju, Odstrani presledek; Ustvari in natisni vmesne seštevke strani Pretvarjanje med vsebino celic in komentarji...
  • Super filter (shranite in uporabite sheme filtrov za druge liste); Napredno razvrščanje glede na mesec / teden / dan, pogostost in drugo; Poseben filter s krepko, ležeče ...
  • Združite delovne zvezke in delovne liste; Spoji tabele na podlagi ključnih stolpcev; Razdelite podatke na več listov; Paketna pretvorba xls, xlsx in PDF...
  • Več kot 300 zmogljivih funkcij. Podpira Office / Excel 2007-2021 in 365. Podpira vse jezike. Enostavna uvedba v vašem podjetju ali organizaciji. 30-dnevna brezplačna preizkusna različica vseh funkcij. 60-dnevna garancija vračila denarja.
zavihek kte 201905

Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo

  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
dno pisarniške mize
Komentarji (13)
Ocene še ni. Bodite prvi in ​​ocenite!
Ta komentar je moderator na spletnem mestu minimiziral
kaj če bi želel iz tega ustvariti seznam v tabeli namesto vseh rezultatov v eni celici?
Ta komentar je moderator na spletnem mestu minimiziral
Pozdravljeni, Tom,
Če želite izvleči edinstvene vrednosti na seznamu celic namesto ene celice, vam lahko pomaga naslednja formula:

=LOOKUP(2, 1/((COUNTIF($E$1:E1, $B$2:$B$12)=0)*($D$2=$A$2:$A$12)), $B$2:$B$12)

Prosim poskusite.
Ta komentar je moderator na spletnem mestu minimiziral
Živjo Skyyang,

Najlepša hvala za to formulo.
To mi deluje. Vendar pa obdelava velikega nabora podatkov traja dolgo.
Ali lahko spremenimo to formulo, da bo delovala toliko hitreje?
Hvala še enkrat
Rasike
Ta komentar je moderator na spletnem mestu minimiziral
Živjo, skyyang, kaj pa, če želiš rezultat kot stolpec?
Ta komentar je moderator na spletnem mestu minimiziral
Ali obstaja način za dodajanje presledka med več vrednostmi, pridobljenimi v rezultatih, brez uvedbe vejice na koncu seznama? Na primer, vaš zgornji rezultat bi bil prikazan kot: "Emily, James, Daisy, Gary" namesto tega: "Emily, James, Daisy, Gary"

Poskušal sem urediti ta del kode VBA: xStr = xStr & xDic.Keys(I) & "," tako, da je: xStr = xStr & xDic.Keys(I) & ", "

To je dodalo presledek med vrednostmi, vendar je dodalo tudi vejico za zadnjo vrednostjo. "Emily, James, Daisy, Gary,"

Ali obstaja način, da deluje s presledkom, vendar brez dodatne vejice za zadnjo vrednostjo?
Ta komentar je moderator na spletnem mestu minimiziral
Živjo, Demetre,
Uporabite presledek za ločevanje vrednosti, le spremeniti morate kodo vba:
iz xStr = xStr & xDic.Keys(i) & "," naj bo to: xStr = xStr & xDic.Keys(i) & " "

Prosim poskusite.
Ta komentar je moderator na spletnem mestu minimiziral
xStr = xStr & xDic.Keys(I) & "," naj bo to: xStr = xStr & xDic.Keys(I) & ", "

Ali obstaja način za zamenjavo "," z ALT+ENTER v celici, tako da bodo rezultati v isti celici, vendar v različnih vrsticah? Ali moram za to uvesti dodaten modul VBA in jih združiti?

Poleg tega je ta koda precej počasna, ko se premika po ogromnih tabelah. Pozna kdo kakšno hitrejšo rešitev?
Ta komentar je moderator na spletnem mestu minimiziral
Živjo Imre,
Če želite ločiti vrednosti rezultatov s tipkama Alt + Enter, uporabite naslednjo uporabniško določeno funkcijo:

Funkcija MultipleLookupNoRept(Lookupvalue kot niz, LookupRange kot obseg, ColumnNumber kot celo število)
Dim xDic kot nov slovar
Zatemni xvrstice tako dolgo
Dim xStr kot niz
Dim i as Long
On Error Resume Next
xRows = LookupRange.Rows.Count
Za i = 1 To xRows
Če je LookupRange.Columns(1).Cells(i).Value = Lookupvalue, potem
xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
Konec Če
Naslednji
xStr = ""
MultipleLookupNoRept = xStr
Če je xDic.Count > 0 Potem
Za i = 0 Za xDic.Count - 1
xStr = xStr & xDic.Keys(i) & Chr(10) + Chr(13)
Naslednji
MultipleLookupNoRept = Left(xStr, Len(xStr) - 1)
Konec Če
Debug.Print xStr
End Function

In nato storite z zgornjimi koraki v tem članku, končno, po vnosu formule kliknite Prelomi besedilo pod zavihkom Domov.
Ta komentar je moderator na spletnem mestu minimiziral
Hi

Iz tega sem želel ustvariti seznam v tabeli namesto vseh rezultatov v eni celici. Zato sem uporabil podobno formulo spodaj (kar ste predlagali)

=LOOKUP(2, 1/((COUNTIF($E$1:E1, $B$2:$B$12)=0)*($D$2=$A$2:$A$12)), $B$2:$B$12)

Vendar pa ta obdelava iz velikega nabora podatkov traja dolgo.
Ali obstaja kakšna alternativna metoda za hitrejšo obdelavo?
Hvala še enkrat
Rasike
Ta komentar je moderator na spletnem mestu minimiziral
hi,
medtem ko se je čas večkratnih ogledov vrednosti lota moj delovni list obesil. Ali obstajajo kakšni drugi načini za več pogledov brez ponavljanja????

in prav tako sem uporabil na novem namizju, prav tako se je samo obesilo ...

moja vrednost podatkov je okoli 10,000 vrstic
Ta komentar je moderator na spletnem mestu minimiziral
Pozdravljeni, naredil sem, kot ste rekli, in odlično je, vendar še vedno ni rešilo ene moje težave. Kaj se zgodi, ko imate edinstveno vrednost v vsakem mesecu? =MultipleLookupNoRept(E2,A2:C17,3) , poskušam E2&1 za januar, vendar ne deluje
Ta komentar je moderator na spletnem mestu minimiziral
Živjo, Jame,
Ali lahko tukaj podate svojo težavo kot posnetek zaslona, ​​da bom lahko razumel vaše zahteve?
Ta komentar je moderator na spletnem mestu minimiziral
To je odlično! Kako naj to prilagodim, da v slovar ne dodam ničelnih vrednosti? Poskušal sem dodati krepko spodaj, vendar se zadnji niz še vedno vrača s primerki ,"",.


xRows = LookupRange.Rows.Count
Za i = 1 To xRows
Če je LookupRange.Columns(1).Cells(i).Value = Lookupvalue in ni prazen(LookupRange.Columns(1).Cells(i).Value), potem
xDic.Add LookupRange.Columns(ColumnNumber).Cells(i).Value, ""
Konec Če
Naslednji

Hvala,
Tu še ni objavljenih komentarjev

Sledi nam

Copyright © 2009 - www.extendoffice.com. | Vse pravice pridržane. Poganja ga ExtendOffice. | Kazalo
Microsoft in logotip Office sta blagovni znamki ali registrirani blagovni znamki družbe Microsoft Corporation v ZDA in / ali drugih državah.
Zaščiteno s Sectigo SSL