Preskoči na glavno vsebino

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 pisarniška orodja za produktivnost

Priljubljene funkcije: Poiščite, označite ali identificirajte dvojnike   |  Izbriši prazne vrstice   |  Združite stolpce ali celice brez izgube podatkov   |   Krog brez formule ...
Super iskanje: Več kriterijev VLookup    Multiple Value VLookup  |   VLookup na več listih   |   Nejasno iskanje ....
Napredni spustni seznam: Hitro ustvarite spustni seznam   |  Odvisni spustni seznam   |  Večkrat izberite spustni seznam ....
Upravitelj stolpcev: Dodajte določeno število stolpcev  |  Premakni stolpce  |  Preklop stanja vidnosti skritih stolpcev  |  Primerjaj obsege in stolpce ...
Predstavljene funkcije: Mrežni fokus   |  Pogled oblikovanja   |   Velika vrstica formule    Upravitelj delovnih zvezkov in listov   |  Knjižnica virov (Samodejno besedilo)   |  Izbirnik datuma   |  Združite delovne liste   |  Šifriranje/dešifriranje celic    Pošljite e-pošto po seznamu   |  Super filter   |   Poseben filter (filter krepko/ležeče/prečrtano ...) ...
15 najboljših kompletov orodij12 Besedilo Orodja (dodajanje besedila, Odstrani znake,...)   |   50 + Graf Vrste (Gantt Chart,...)   |   40+ Praktično Formule (Izračunajte starost glede na rojstni dan,...)   |   19 vstavljanje Orodja (Vstavite kodo QR, Vstavi sliko s poti,...)   |   12 Pretvorba Orodja (Številke v besede, Pretvorba valut,...)   |   7 Spoji in razdeli Orodja (Napredne kombinirane vrstice, Razdeljene celice,...)   |   ... in več

Napolnite svoje Excelove spretnosti s Kutools za Excel in izkusite učinkovitost kot še nikoli prej. Kutools za Excel ponuja več kot 300 naprednih funkcij za povečanje produktivnosti in prihranek časa.  Kliknite tukaj, če želite pridobiti funkcijo, ki jo najbolj potrebujete...

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!
Comments (31)
Rated 5 out of 5 · 1 ratings
This comment was minimized by the moderator on the site
Thank you for this solution, however what if the columns are in separate excel sheet? or is in separate columns instead of a table?
This comment was minimized by the moderator on the site
Hello, Jon,
The methods in this article ar only works well for a range of data, if your data in separate columns, you should copy and paste them into one range first, and then apply the formula or VBA code.
Thank you!
This comment was minimized by the moderator on the site
Thank you for this great article.

For people who are using the array formular in non-English Excel there must be taken special care of the text format string: in your example: "R0C00".
For German this would translate to "Z0S00". However, "S" is a special character refering to seconds for time formating. This character needs to be escaped and therefore the correct format string for German Excel is "Z0\S00".

I hope this helps someone in the future :-)
This comment was minimized by the moderator on the site
Hello, Michael H.
Thanks for your kindly explanation.
Hope this can help others in the future.😄
This comment was minimized by the moderator on the site
Thank you for this great article.

For people who are using the array formular in non-English Excel there must be taken special care of the text format string: in your example: "R0C00".
For German this would translate to "Z0S00". However, "S" is a special character refering to seconds for time formating. This character needs to be escaped and therefore the correct format string for German Excel is "Z0\S00".

I hope this helps someone in the future :-)
This comment was minimized by the moderator on the site
Hello Sir! The VBA worked wonders, thank you very much for that! I was wondering, If I change the original data, is it possible to refresh the column with the unique values automatically?
Rated 5 out of 5
This comment was minimized by the moderator on the site
Hello Ioannis,

Glad to help. After you change the original data, the VBA can not refresh the result automatically. And the easiest way I can think of is to press Ctrl + Alt + F9 to refresh all results in worksheets in all open workbooks. Have a nice day.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
The array formula at the top is working great when used with data in the same sheet, however when I try to use it to reference the same exact data from another sheet the formula returns nothing. I'm unable to figure out why. Is there a limitation with array functions that prevents you from referencing ranges in a different sheet?

Thanks for any insight you can provide.
This comment was minimized by the moderator on the site
Hello Erin,

Glad to help. The INDIRECT function in this formula is more complicated to use when referencing data in other worksheets. It is not recommended to use this feature when referencing ranges in different worksheets.

For example: Now the data is in Sheet1, I want to reference the content of cell C2 of Sheet1 in Sheet2. First, in any two cells in Sheet2, such as D1 and D2, enter Sheet1 and C2, respectively. At this point, enter the formula in the empty cell of Sheet2:
=INDIRECT("'"&D1&"'!"&D2), then the content of cell C2 in Sheet1 can be returned.

As you can see, it make things way more complex. Hope my explanation can help. Have a nice day.

Sincerely,
Mandy
This comment was minimized by the moderator on the site
Czy to żart?
This comment was minimized by the moderator on the site
can we create uniqdata function instead of macro?
This comment was minimized by the moderator on the site
Hi, İlhan,If you like a User Defined Function to create a formula for solving this problem, the below code may help you:After inserting the code, select a list of cells where you want to put the results. Then type this formula:=Uniques(A1:C4)  in the formula bar.Press Ctrl+Shift+Enter keys together. 


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
This comment was minimized by the moderator on the site
Thanks for the code. I'm using the VBA code of this page. Is there a way to add a sorting code after the unique values are extracted so it sorts it automatically?
This comment was minimized by the moderator on the site
Regarding the formula version, could you explain in more detail what this portion is doing? *100+COLUMN($A:$C),7^8)),"R0C00") Specifically, what are the *100, 7^8, and "R0C000" doing? I'm understanding everything else, but I can't figure out what these are for.
This comment was minimized by the moderator on the site
Little late for my response here but...
ROW($2:$9)*100 - this is multiplying the row number *100, so if it's in row 5, now the number is 500
COLUMN($A:$C) - this gets added to the row*100 number, so if it's row 5 col 2, then the number is 502.
7^8)), - this (I think) is to have a max value for the min statement from earlier.
"R0C00") - this formats the text based on the number. In the example, we had 502 so this gives R5C02 (row 5, col 02).

If you have a lot of columns but not many rows, then you could change it to ROW($2:$9)*1000+COLUMN($A:$C),7^8)),"R0C000")
This comment was minimized by the moderator on the site
i've adjusted to my sheet but am only returning the first value in the defined array... what am i missing?
This comment was minimized by the moderator on the site
Hello, Cody,
The above formula works well in my worksheet, could you give a screenshot of your data problem here?
Thank you!
There are no comments posted here yet
Load More
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations