Salt la conținutul principal

Cum se extrag valori unice din mai multe coloane din Excel?

Presupunând că aveți mai multe coloane cu valori multiple, unele valori se repetă în aceeași coloană sau coloană diferită. Și acum doriți să găsiți valorile care sunt prezente în ambele coloane o singură dată. Există trucuri rapide pentru a extrage valori unice din mai multe coloane din Excel?


Extrageți valori unice din mai multe coloane cu formule

Această secțiune va acoperi două formule: una folosind o formulă matrice adecvată pentru toate versiunile Excel și alta folosind o formulă matrice dinamică special pentru Excel 365.

Extrageți valori unice din mai multe coloane cu formula Array pentru toate versiunile Excel

Pentru utilizatorii cu orice versiune de Excel, formulele matrice pot fi un instrument puternic pentru extragerea de valori unice pe mai multe coloane. Iată cum o poți face:

1. Presupunând valorile dvs. în interval A2: C9, vă rugăm să introduceți următoarea formulă în celula E2:

=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"),)&""
notițe: În formula de mai sus, A2: C9 indică intervalul de celule pe care doriți să extrageți valorile unice, E1: E1 este prima celulă a coloanei pe care doriți să plasați rezultatul, $ 2: $ 9 standurile de lângă rânduri conțin celulele pe care doriți să le utilizați și $ A: $ C indică coloanele care conțin celulele pe care doriți să le utilizați. Vă rugăm să le schimbați la ale dvs.

2. Apoi apăsați Shift + Ctrl + Enter tastele împreună, apoi glisați mânerul de umplere pentru a extrage valorile unice până când apar celule goale. Vedeți captura de ecran:

Explicația acestei formule:
  1. $ A $ 2: $ C $ 9: Aceasta specifică intervalul de date care trebuie verificat, adică celulele de la A2 la C9.
  2. IF(($A$2:$C$9<>"")*(COUNTIF($E$1:E1,$A$2:$C$9)=0), ROW($2:$9)*100+COLUMN($A:$C), 7^8):
    • $A$2:$C$9<>"" verifică dacă celulele din interval nu sunt goale.
    • COUNTIF($E$1:E1,$A$2:$C$9)=0 determină dacă valorile acestor celule nu au fost încă enumerate în intervalul de celule de la E1 la E1.
    • Dacă sunt îndeplinite ambele condiții (adică, valoarea nu este goală și nu este încă listată în coloana E), funcția IF calculează un număr unic pe baza rândului și coloanei sale (ROW($2:$9)*100+COLUMN($A: $C)).
    • Dacă condițiile nu sunt îndeplinite, funcția returnează un număr mare (7^8), care servește ca substituent.
  3. MIN(...): Găsește cel mai mic număr returnat de funcția IF de mai sus, corespunzător locației următoarei valori unice.
  4. TEXT(...,"R0C00"): Convertește acest număr minim într-o adresă de stil R1C1. Codul de format R0C00 indică conversia numărului în formatul de referință al celulei Excel.
  5. INDIRECT(...): Folosește funcția INDIRECT pentru a converti adresa de stil R1C1 generată în pasul anterior înapoi la o referință normală de celulă în stil A1. Funcția INDIRECT permite referirea celulelor pe baza conținutului unui șir de text.
  6. &"": Adăugarea &"" la sfârșitul formulei asigură că rezultatul final este tratat ca text, astfel încât numerele pare vor fi afișate ca text.
 
Extrageți valori unice din mai multe coloane cu formula pentru Excel 365

Excel 365 acceptă matrice dinamice, ceea ce facilitează extragerea de valori unice din mai multe coloane:

Introduceți sau copiați următoarea formulă într-o celulă goală în care doriți să puneți rezultatul, apoi faceți clic Intrați cheie pentru a obține toate valorile unice simultan. Vedeți captura de ecran:

=UNIQUE(TOCOL(A2:C9,1))


Extrageți valori unice din mai multe coloane cu Kutools AI Aide

Dezlănțuiți puterea Kutools AI Aide pentru a extrage fără probleme valori unice din mai multe coloane în Excel. Cu doar câteva clicuri, acest instrument inteligent vă cercetează datele, identificând și listând intrări unice din orice interval selectat. Uitați de necazul formulelor complexe sau codului vba; îmbrățișați eficiența Kutools AI Aide și transformați-vă fluxul de lucru Excel într-o experiență mai productivă și fără erori.

notițe: Pentru a folosi asta Kutools AI Aide of Kutools pentru Excel, Vă rugăm să descărcați și instalați Kutools pentru Excel mai întâi.

După instalarea Kutools pentru Excel, faceți clic pe Kutools AI > Ajutor AI pentru a deschide Kutools AI Aide panou:

  1. Introduceți cerința dvs. în caseta de chat și faceți clic Trimiteți sau apăsați Intrați cheie pentru a trimite întrebarea;
    „Extrageți valori unice din intervalul A2:C9, ignorând celulele goale și plasați rezultatele începând cu E2:”
  2. După analiză, faceți clic Executa butonul pentru a rula. Kutools AI Aide va procesa cererea dvs. folosind AI și va returna rezultatele în celula specificată direct în Excel.


Extrageți valori unice din mai multe coloane cu Tabelul pivot

Dacă sunteți familiarizat cu tabelul pivot, puteți extrage cu ușurință valorile unice din mai multe coloane urmând pașii următori:

1. La început, vă rugăm să introduceți o nouă coloană goală în partea stângă a datelor dvs., în acest exemplu, voi insera coloana A lângă datele originale.

2. Faceți clic pe o celulă din datele dvs. și apăsați Alt + D , apoi apăsați P tasta imediat pentru a deschide PivotTable și PivotChart Wizard, alege Intervalele multiple de consolidare în pasul 1 al vrăjitorului, vezi captura de ecran:

3. Apoi apasa Pagina Următoare → buton, verificați Creează-mi un singur câmp de pagină opțiunea din pasul 2 al vrăjitorului, vezi captura de ecran:

4. Continuați să faceți clic Pagina Următoare → buton, faceți clic pentru a selecta intervalul de date care include noua coloană stângă de celule, apoi faceți clic pe Adăuga pentru a adăuga intervalul de date la Toate gamele casetă listă, vezi captura de ecran:

5. După selectarea intervalului de date, continuați să faceți clic Pagina Următoare →, în pasul 3 al vrăjitorului, alegeți unde doriți să puneți raportul PivotTable după cum doriți.

6. În cele din urmă, faceți clic pe finalizarea pentru a finaliza vrăjitorul și a fost creat un tabel pivot în foaia de lucru curentă, apoi debifați toate câmpurile din Alegeți câmpurile pe care să le adăugați la raport secțiune, vezi captura de ecran:

7. Apoi verificați câmpul Valoare sau trageți valoarea la Rânduri etichetă, acum veți obține valorile unice din mai multe coloane după cum urmează:


Extrageți valori unice din mai multe coloane cu cod VBA

Cu următorul cod VBA, puteți extrage, de asemenea, valorile unice din mai multe coloane.

1. Țineți apăsat butonul ALT + F11 tastele și deschide fișierul Fereastra Microsoft Visual Basic pentru aplicații.

2. Clic Insera > Moduleși lipiți următorul cod în fereastra modulului.

VBA: extrageți valori unice din mai multe coloane

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. Apoi apăsați F5 pentru a rula acest cod și va apărea o casetă de solicitare pentru a vă reaminti să selectați intervalul de date pe care doriți să îl utilizați. Vedeți captura de ecran:

4. Apoi faceți clic pe OK, va apărea o altă casetă de solicitare pentru a vă permite să alegeți un loc pentru a pune rezultatul, a se vedea captura de ecran:

5. Clic OK pentru a închide acest dialog și toate valorile unice au fost extrase simultan.


Mai multe articole relative:

  • Numărați numărul de valori unice și distincte dintr-o listă
  • Presupunând că aveți o listă lungă de valori cu unele elemente duplicate, acum doriți să numărați numărul de valori unice (valorile care apar în listă o singură dată) sau valorile distincte (toate valorile diferite din listă, înseamnă unic valori + primele valori duplicate) într-o coloană așa cum este afișat ecranul din stânga. În acest articol, voi vorbi despre cum să rezolvați această sarcină în Excel.
  • Extrageți valori unice pe baza criteriilor din Excel
  • Să presupunem că aveți următorul interval de date pe care doriți să le enumerați numai numele unice ale coloanei B pe baza unui criteriu specific al coloanei A pentru a obține rezultatul așa cum este prezentat mai jos. Cum ați putea face față acestei sarcini în Excel rapid și ușor?
  • Permiteți numai valori unice în Excel
  • Dacă doriți să păstrați doar valori unice care intră într-o coloană a foii de lucru și să preveniți duplicatele, acest articol vă va introduce câteva trucuri rapide pentru a vă ocupa de această sarcină.
  • Suma valorilor unice pe baza criteriilor din Excel
  • De exemplu, am o serie de date care conține coloane Nume și comandă, acum, pentru a însuma doar valorile unice în coloana Comandă pe baza coloanei Nume, după cum se arată în următoarea captură de ecran. Cum să rezolvați această sarcină rapid și ușor în Excel?

Cele mai bune instrumente de productivitate de birou

🤖 Kutools AI Aide: Revoluționați analiza datelor pe baza: Execuție inteligentă   |  Generați codul  |  Creați formule personalizate  |  Analizați datele și generați diagrame  |  Invocați funcțiile Kutools...
Caracteristici populare: Găsiți, evidențiați sau identificați duplicatele   |  Ștergeți rândurile goale   |  Combinați coloane sau celule fără a pierde date   |   Rundă fără Formula ...
Super căutare: VLookup cu mai multe criterii    VLookup cu valori multiple  |   VLookup pe mai multe foi   |   Căutare fuzzy ....
Listă derulantă avansată: Creați rapid o listă derulantă   |  Listă drop-down dependentă   |  Listă derulantă cu selectare multiplă ....
Manager de coloane: Adăugați un număr specific de coloane  |  Mutați coloanele  |  Comutați starea vizibilității coloanelor ascunse  |  Comparați intervale și coloane ...
Caracteristici prezentate: Focus pe grilă   |  Vedere de proiectare   |   Big Formula Bar    Manager registru de lucru și foi   |  Biblioteca de resurse (Text automat)   |  Data Picker   |  Combinați foi de lucru   |  Criptare/Decriptare celule    Trimiteți e-mailuri după listă   |  Super Filtru   |   Filtru special (filtrează bold/italic/barat...) ...
Top 15 seturi de instrumente12 Text Instrumente (Adăuga text, Eliminați caractere,...)   |   50+ Diagramă Tipuri de (Gantt Chart,...)   |   40+ Practic Formule (Calculați vârsta pe baza zilei de naștere,...)   |   19 inserare Instrumente (Introduceți codul QR, Inserați imaginea din cale,...)   |   12 Convertire Instrumente (Numere la cuvinte, conversie valutara,...)   |   7 Merge & Split Instrumente (Rânduri combinate avansate, Celule divizate,...)   |   ... și altele

Îmbunătățiți-vă abilitățile Excel cu Kutools pentru Excel și experimentați eficiența ca niciodată. Kutools pentru Excel oferă peste 300 de funcții avansate pentru a crește productivitatea și a economisi timp.  Faceți clic aici pentru a obține funcția de care aveți cea mai mare nevoie...

Descriere


Fila Office aduce interfața cu file în Office și vă face munca mult mai ușoară

  • Activați editarea și citirea cu file în Word, Excel, PowerPoint, Publisher, Access, Visio și Project.
  • Deschideți și creați mai multe documente în filele noi ale aceleiași ferestre, mai degrabă decât în ​​ferestrele noi.
  • Vă crește productivitatea cu 50% și reduce sute de clicuri de mouse pentru dvs. în fiecare zi!
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