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

Kako izvleči edinstvene vrednosti iz več stolpcev v Excelu?

Recimo, da imate več stolpcev z več vrednostmi, se nekatere vrednosti ponovijo v istem stolpcu ali drugem stolpcu. Zdaj želite najti vrednosti, ki so v obeh stolpcih prisotne samo enkrat. Ali obstajajo kakšni hitri triki za pridobivanje edinstvenih vrednosti iz več stolpcev v Excelu?


Izvlecite edinstvene vrednosti iz več stolpcev s formulo matrike

Tu je formula matrike, ki vam lahko pomaga izvleči edinstvene vrednosti iz več stolpcev.

1. Ob predpostavki, da so vaše vrednosti v območju A2: C9, v celico E2 vnesite naslednjo formulo:

=INDIRECT(TEXT(MIN(IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&""

Opombe: V zgornji formuli, A2: C9 označuje obseg celic, v katere želite izvleči edinstvene vrednosti, E1: E1 je prva celica stolpca, v katero želite umestiti rezultat, $ 2: $ 9 stojala za vrstice vsebujejo celice, ki jih želite uporabiti, in $ A: $ C označuje, da stolpci vsebujejo celice, ki jih želite uporabiti. Prosimo, spremenite jih v svoje.

2. Nato pritisnite Shift + Ctrl + Enter tipke skupaj in nato povlecite ročico za polnjenje, da izvlečete edinstvene vrednosti, dokler se ne pojavijo prazne celice. Oglejte si posnetek zaslona:


Izvlecite edinstvene vrednosti iz več stolpcev z vrtilno tabelo

Če poznate vrtilno tabelo, lahko enostavne vrednosti enostavno izvlečete iz več stolpcev z naslednjimi koraki:

1. Najprej vstavite en prazen stolpec na levi strani vaših podatkov, v tem primeru bom vstavil stolpec A poleg prvotnih podatkov.

2. Kliknite eno celico v svojih podatkih in pritisnite Alt + D tipke, nato pritisnite P tipko takoj odprite Vrtilna tabela in čarovnik vrtilnih grafikonov, izberite Več konsolidacijskih obsegov v čarovniku korak1 si oglejte posnetek zaslona:

3. Nato kliknite Naslednji gumb, preverite Ustvari mi enostransko polje možnost v čarovniku step2, glej posnetek zaslona:

4. Pojdi na klik Naslednji kliknite, da izberete obseg podatkov, ki vključuje levi novi stolpec celic, nato kliknite Dodaj , da dodate obseg podatkov v Vsi razponi seznam, glej posnetek zaslona:

5. Po izbiri obsega podatkov nadaljujte s klikom Naslednji, v 3. koraku čarovnika izberite, kam želite postaviti poročilo vrtilne tabele, kot želite.

6. Končno kliknite Konec za dokončanje čarovnika in v trenutnem delovnem listu je bila ustvarjena vrtilna tabela, nato počistite vsa polja v Izberite polja, ki jih želite dodati v poročilo razdelek, glej posnetek zaslona:

7. Nato preverite polje vrednost ali povlecite vrednost v Vrstice zdaj boste iz več stolpcev dobili enolične vrednosti, kot sledi:


Izvlecite edinstvene vrednosti iz več stolpcev s kodo VBA

Z naslednjo kodo VBA lahko iz več stolpcev izvlečete tudi edinstvene vrednosti.

1. Držite tipko ALT + F11 in odpre tipko Okno Microsoft Visual Basic for Applications.

2. Kliknite Vstavi > Moduliin prilepite naslednjo kodo v okno modula.

VBA: iz več stolpcev izvlecite edinstvene vrednosti

Sub Uniquedata()
'Updateby Extendoffice
Dim rng As Range
Dim InputRng As Range, OutRng As Range
Set dt = CreateObject("Scripting.Dictionary")
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
For Each rng In InputRng
    If rng.Value <> "" Then
        dt(rng.Value) = ""
    End If
Next
OutRng.Range("A1").Resize(dt.Count) = Application.WorksheetFunction.Transpose(dt.Keys)
End Sub

3. Nato pritisnite F5 Če želite zagnati to kodo, se prikaže pozivno okno, ki vas opomni, da izberete obseg podatkov, ki ga želite uporabiti. Oglejte si posnetek zaslona:

4. In nato kliknite OK, se bo pojavilo drugo pozivno polje, ki vam bo omogočilo, da izberete kraj, kjer boste postavili rezultat, glejte sliko zaslona:

5. klik OK Če želite zapreti to pogovorno okno, so bile naenkrat izvlečene vse edinstvene vrednosti.


Iz enega samega stolpca izvlecite edinstvene vrednosti z neverjetno funkcijo

Včasih morate iz enega stolpca izvleči edinstvene vrednosti, zgornje metode vam ne bodo pomagale, tukaj vam lahko priporočim koristno orodje -Kutools za Excel, Z njegovim Izvleči celice z edinstvenimi vrednostmi (vključi prvi dvojnik) pripomoček, lahko hitro izvlečete edinstvene vrednosti.

Opomba:Če želite uporabiti to Izvleči celice z edinstvenimi vrednostmi (vključi prvi dvojnik), najprej bi morali prenesti Kutools za Excelin nato funkcijo hitro in enostavno uporabite.

Po namestitvi Kutools za Excel, naredite tako:

1. Kliknite celico, v katero želite izpisati rezultat. (Opombe: Ne kliknite celice v prvi vrstici.)

2. Nato kliknite Kutools > Pomočnik za formulo > Pomočnik za formulo, glej posnetek zaslona:

3. v Pomočnik za formule pogovorno okno, naredite naslednje:

  • Izberite Besedilo možnost iz Formula tip spustni seznam;
  • Potem izberite Izvleči celice z edinstvenimi vrednostmi (vključi prvi dvojnik) Iz Izberite fromula polje s seznamom;
  • Na desni Vnos argumentov v razdelku izberite seznam celic, za katere želite izvleči edinstvene vrednosti.

4. Nato kliknite Ok in povlecite ročico za polnjenje do celic, v katere želite navesti vse edinstvene vrednosti, dokler se ne prikažejo prazne celice, glejte posnetek zaslona:

Brezplačno prenesite Kutools za Excel zdaj!


Več relativnih člankov:

  • Štejte na seznamu število edinstvenih in ločenih vrednot
  • Recimo, da imate dolg seznam vrednosti z nekaj podvojenimi elementi, zdaj želite prešteti število unikatnih vrednosti (vrednosti, ki so na seznamu samo enkrat) ali ločenih vrednosti (vse različne vrednosti na seznamu pomenijo unikatne vrednosti + 1. podvojene vrednosti) v stolpcu, kot je prikazano na levi sliki zaslona. V tem članku bom govoril o tem, kako ravnati s tem delom v Excelu.
  • Izvlecite edinstvene vrednosti na podlagi meril v Excelu
  • Recimo, da imate naslednji obseg podatkov, v katerem želite navesti samo enolična imena stolpca B na podlagi določenega merila stolpca A, da dobite rezultat, kot je prikazano na spodnji sliki zaslona. Kako bi se lahko hitro in enostavno spopadli s to nalogo v Excelu?
  • Dovoli samo edinstvene vrednosti v Excelu
  • Če želite obdržati samo enolične vrednosti, ki se vnašajo v stolpec delovnega lista, in preprečiti podvojitve, bo ta članek predstavil nekaj hitrih trikov za reševanje te naloge.
  • Vsota edinstvenih vrednosti na podlagi meril v Excelu
  • Na primer, zdaj imam nabor podatkov, ki vsebujejo stolpca Ime in Naročilo, da v stolpcu Naročilo seštejem samo enolične vrednosti na podlagi stolpca Ime, kot je prikazano na sliki spodaj. Kako hitro in enostavno rešiti to nalogo v Excelu?

Najboljša orodja za pisarniško produktivnost

Kutools za Excel rešuje 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 (31)
Ocenjeno 5 iz 5 · 1 ocene
Ta komentar je moderator na spletnem mestu minimiziral
Is this formula complete? =INDIRECT(TEXT(MIN(IF(($A$2:$C$9"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&"",
Ta komentar je moderator na spletnem mestu minimiziral
To še ni popravljeno :sad:
Ta komentar je moderator na spletnem mestu minimiziral
kakšna izguba časa..... formula NE deluje
Ta komentar je moderator na spletnem mestu minimiziral
Hvala vam!!! Več ur sem poskušal to narediti in ugotoviti, kaj se je zgodilo s Pivot Wizard (drug članek).
Ta komentar je moderator na spletnem mestu minimiziral
Uporabljam vašo kodo VBA, vendar ne želim, da se okno prikaže. Namesto tega želim natančno določiti, kateri obseg celic naj uporabim vsakič in točno v katero polje vstaviti izhod. Vhodni obseg in izhod bi bila na dveh različnih listih. kako naj posodobim VBA, da to storim? Hvala vam!!
Ta komentar je moderator na spletnem mestu minimiziral
Zdravo! Ali kdo ve, zakaj se zdi, da ta formula vodi do napake po vrstici 87? Na primer, deluje odlično in potem mi na določeni točki samo vrne napake za vsako vrstico.. kar je najslabše! Ker sem tako blizu točno tistemu, kar potrebujem tukaj ...
Ta komentar je moderator na spletnem mestu minimiziral
=INDIRECT(TEXT(MIN(IF(($A$2:$C$9"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0),ROW($2:$9)*100+COLUMN($A:$C),7^8)),"R0C00"),)&"" It doesn't work
Ta komentar je moderator na spletnem mestu minimiziral
zdravo, želim izvleči edinstvene celice iz prvega stolpca, ko ga primerjam z drugim stolpcem (imam tri neenake stolpce), kako lahko to storim?
Ta komentar je moderator na spletnem mestu minimiziral
zdravo, imam tri neenake stolpce in želim izvleči edinstvene celice prvega stolpca. kako lahko to naredim?? Hvala vnaprej
Ta komentar je moderator na spletnem mestu minimiziral
ljubim

Izvlecite edinstvene vrednosti iz več stolpcev z vrtilno tabelo
Ta komentar je moderator na spletnem mestu minimiziral
Ali lahko prosim predložite pravilno formulo ... funkcija VBA deluje v redu.
Samo za svoj projekt raje uporabljam pravilno formulo.


Hvala
Ta komentar je moderator na spletnem mestu minimiziral
ali kdo ve, za izhod, kako ga narediti v več vrsticah, ne pa v eno vrstico? (trenutno je rezultat ene vrstice dosežen z worksheetfunction.transpose, toda kar želim doseči (kot rezultat) je, da so pri izbiri za 3 stolpce vrnjeni rezultat tudi 3 stolpci, namesto enega
Ta komentar je moderator na spletnem mestu minimiziral
Ta formula matrike je PRAVILNA. Podatki v stolpcih od A do C, prva formula rezultata v celici D2... Ta se razlikuje od druge formule matrike, da je kasnejša formula kopiranja navzdol in Ctrl+Shift+Enter vso formulo. Vendar je treba to formulo matrike narediti s Ctrl+Shift+Enter v prvi celici in kopirati navzdol.
Ta komentar je moderator na spletnem mestu minimiziral
Muchas gracias por la macro!!! me fue muy util
Ta komentar je moderator na spletnem mestu minimiziral
prilagodil sem se svojemu listu, vendar vračam samo prvo vrednost v definiranem nizu... kaj mi manjka?
Ta komentar je moderator na spletnem mestu minimiziral
Pozdravljeni, Cody,
Zgornja formula dobro deluje na mojem delovnem listu, ali lahko tukaj podate posnetek zaslona vaše težave s podatki?
Hvala!
Ta komentar je moderator na spletnem mestu minimiziral
Glede različice formule, ali lahko bolj podrobno razložite, kaj ta del počne? *100+COLUMN($A:$C),7^8)),"R0C00") Natančneje, kaj so * 100, 7 ^ 8in "R0C000" delaš? Vse ostalo razumem, vendar ne morem ugotoviti, čemu služijo.
Ta komentar je moderator na spletnem mestu minimiziral
Malo pozno za odgovor, ampak...
ROW($2:$9)*100 - to je množenje številke vrstice *100, torej če je v vrstici 5, je zdaj številka 500
STOLPEC($A:$C) - to se doda številki vrstice*100, tako da, če je vrstica 5 stolpec 2, je številka 502.
7^8)), - to (mislim) naj bi imela največjo vrednost za stavek min od prej.
"R0C00") - to oblikuje besedilo glede na številko. V primeru smo imeli 502, kar daje R5C02 (vrstica 5, stolpec 02).

Če imate veliko stolpcev, vendar ne veliko vrstic, ga lahko spremenite v ROW($2:$9)*1000+STOLPEC($A:$C),7^8)),"R0C000")
Ta komentar je moderator na spletnem mestu minimiziral
Hvala za kodo. Uporabljam kodo VBA te strani. Ali obstaja način za dodajanje kode za razvrščanje, potem ko so edinstvene vrednosti ekstrahirane, da jo samodejno razvrsti?
Ta komentar je moderator na spletnem mestu minimiziral
ali lahko namesto makra ustvarimo funkcijo uniqdata?
Ta komentar je moderator na spletnem mestu minimiziral
Živjo, İlhan, Če vam je všeč uporabniško definirana funkcija za ustvarjanje formule za rešitev te težave, vam lahko pomaga spodnja koda: Ko vstavite kodo, izberite seznam celic, kamor želite vstaviti rezultate. Nato vnesite to formulo:=Edinstveni (A1:C4)  v vrstici s formulo.Pritisnite Ctrl + Shift + Enter skupaj. 


Function Uniques(ByVal inputRange As Range)
Dim inputArray As Variant
Dim myColl As New Collection
Dim xVal As Variant
Dim outArray() As Variant
On Error Resume Next
With inputRange
inputArray = Application.Intersect(.Cells, .Parent.UsedRange).Value
End With
On Error GoTo 0
On Error Resume Next
For Each xVal In inputArray
myColl.*** Item:=xVal, Key:=(CStr(xVal) & TypeName(xVal))
Next xVal
myColl.Remove "String"
On Error GoTo 0
ReDim outArray(1 To Application.Max(myColl.Count, Application.Caller.Cells.Count))
For xVal = 1 To UBound(outArray)
outArray(xVal) = vbNullString
Next xVal
For xVal = 1 To Application.Min(myColl.Count, Application.Caller.Cells.Count)
outArray(xVal) = myColl(xVal)
Next xVal
If Application.Caller.Columns.Count = 1 Then
Uniques = Application.Transpose(outArray)
Else
Uniques = outArray
End If
End Function
Ta komentar je moderator na spletnem mestu minimiziral
Czy to żart?
Ta komentar je moderator na spletnem mestu minimiziral
Formula matrike na vrhu deluje odlično, če se uporablja s podatki na istem listu, vendar ko jo poskušam uporabiti za sklicevanje na iste točne podatke z drugega lista, formula ne vrne ničesar. Ne morem ugotoviti, zakaj. Ali obstaja omejitev pri funkcijah matrike, ki vam preprečuje sklicevanje na obsege na drugem listu?

Hvala za kakršen koli vpogled, ki ga lahko zagotovite.
Ta komentar je moderator na spletnem mestu minimiziral
Pozdravljena Erin,

Z veseljem pomagam. Funkcija INDIRECT v tej formuli je bolj zapletena za uporabo pri sklicevanju na podatke v drugih delovnih listih. Te funkcije ni priporočljivo uporabljati pri sklicevanju na obsege na različnih delovnih listih.

Na primer: Zdaj so podatki v List1, želim se sklicovati na vsebino celice C2 List1 na List2. Najprej v kateri koli dve celici v List2, kot sta D1 in D2, vnesite Sheet1 oziroma C2. Na tej točki vnesite formulo v prazno celico Sheet2:
=INDIRECT("'"&D1&"'!"&D2), potem je mogoče vrniti vsebino celice C2 v Sheet1.

Kot lahko vidite, so stvari veliko bolj zapletene. Upam, da bo moja razlaga pomagala. Imej lep dan.

S spoštovanjem,
Mandy
Ta komentar je moderator na spletnem mestu minimiziral
Pozdravljeni gospod! VBA je delal čudeže, najlepša hvala za to! Zanima me, če spremenim izvirne podatke, ali je mogoče samodejno osvežiti stolpec z edinstvenimi vrednostmi?
Ocenjeno 5 iz 5
Ta komentar je moderator na spletnem mestu minimiziral
Pozdravljeni Ioannis,

Z veseljem pomagam. Ko spremenite izvirne podatke, VBA ne more samodejno osvežiti rezultata. In najlažji način, ki si ga lahko zamislim, je, da pritisnete Ctrl + Alt + F9, da osvežite vse rezultate na delovnih listih v vseh odprtih delovnih zvezkih. Imej lep dan.

S spoštovanjem,
Mandy
Ta komentar je moderator na spletnem mestu minimiziral
Hvala za ta čudovit članek.

Za ljudi, ki uporabljajo array formular v neangleškem Excelu posebej je treba paziti na niz besedilnega formata: v vašem primeru: "R0C00".
Za nemščino bi to pomenilo "Z0S00". Vendar je "S" poseben znak, ki se nanaša na sekunde za oblikovanje časa. Ta znak je treba ubežati, zato je pravilni niz za nemški Excel »Z0\S00«.

Upam, da bo to komu pomagalo v prihodnosti :-)
Tu še ni objavljenih komentarjev
Obremenitev Več
Pustite vaše komentarje
Objava kot gost
×
Ocenite to objavo:
0   Znaki
Predlagane lokacije

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