Gå til hovedindhold

Sådan udtrækkes unikke værdier fra flere kolonner i Excel?

Hvis du antager, at du har flere kolonner med flere værdier, gentages nogle værdier i samme kolonne eller anden kolonne. Og nu vil du kun finde de værdier, der findes i en af ​​kolonnerne en gang. Er der nogle hurtige tricks til at udtrække unikke værdier fra flere kolonner i Excel?


Udtræk unikke værdier fra flere kolonner med formler

Dette afsnit vil dække to formler: en med en matrixformel, der er egnet til alle Excel-versioner, og en anden, der bruger en dynamisk matrixformel specifikt til Excel 365.

Udtræk unikke værdier fra flere kolonner med Array-formlen for alle Excel-versioner

For brugere med enhver version af Excel kan matrixformler være et effektivt værktøj til at udtrække unikke værdier på tværs af flere kolonner. Sådan kan du gøre det:

1. Antages dine værdier inden for rækkevidde A2: C9, indtast venligst følgende formel i celle 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"),)&""
Bemærk: I ovenstående formel A2: C9 angiver det celleområde, som du vil udtrække de unikke værdier, E1: E1 er den første celle i den kolonne, du vil placere resultatet, $ 2: $ 9 står foran rækkerne indeholder de celler, du vil bruge, og $ A: $ C angiver, at kolonnerne indeholder de celler, du vil bruge. Skift dem til dine egne.

2. Tryk derefter på Skift + Ctrl + Enter nøgler sammen, og træk derefter fyldhåndtaget for at udtrække de unikke værdier, indtil tomme celler vises. Se skærmbillede:

Forklaring af denne formel:
  1. $ A $ 2: $ C $ 9: Dette specificerer det dataområde, der skal kontrolleres, som er cellerne fra A2 til 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<>"" kontrollerer om cellerne i området ikke er tomme.
    • COUNTIF($E$1:E1,$A$2:$C$9)=0 bestemmer, om værdierne af disse celler endnu ikke er blevet opført i intervallet af celler fra E1 til E1.
    • Hvis begge betingelser er opfyldt (dvs. værdien er ikke tom og endnu ikke opført i kolonne E), beregner IF-funktionen et unikt tal baseret på dens række og kolonne (ROW($2:$9)*100+COLUMN($A: $C)).
    • Hvis betingelserne ikke er opfyldt, returnerer funktionen et stort tal (7^8), som fungerer som en pladsholder.
  3. MIN(...): Finder det mindste tal, der returneres af IF-funktionen ovenfor, svarende til placeringen af ​​den næste unikke værdi.
  4. TEKST(...,"R0C00"): Konverterer dette minimumstal til en R1C1-stiladresse. Formatkoden R0C00 angiver konverteringen af ​​tallet til Excel-cellereferenceformat.
  5. INDIREKTE(...): Bruger INDIREKTE-funktionen til at konvertere R1C1-stiladressen, der blev genereret i det foregående trin, tilbage til en normal cellereference i A1-stil. Funktionen INDIREKTE tillader cellehenvisning baseret på indholdet af en tekststreng.
  6. &"": Tilføjelse af &"" i slutningen af ​​formlen sikrer, at det endelige output behandles som tekst, så lige tal vil blive vist som tekst.
 
Udtræk unikke værdier fra flere kolonner med formel til Excel 365

Excel 365 understøtter dynamiske arrays, hvilket gør det meget nemmere at udtrække unikke værdier fra flere kolonner:

Indtast eller kopier følgende formel ind i en tom celle, hvor du vil placere resultatet, og klik derefter Indtast nøglen til at få alle de unikke værdier på én gang. Se skærmbillede:

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


Udtræk unikke værdier fra flere kolonner med Kutools AI Aide

Slip kraften løs Kutools AI Aide til problemfrit at udtrække unikke værdier fra flere kolonner i Excel. Med blot et par klik filtrerer dette intelligente værktøj dine data, identificerer og viser unikke poster på tværs af ethvert udvalgt område. Glem besværet med komplekse formler eller vba-kode; Omfavn effektiviteten af Kutools AI Aide og transformer din Excel-arbejdsgang til en mere produktiv og fejlfri oplevelse.

Bemærk: For at bruge dette Kutools AI Aide of Kutools til Excel, Bedes download og installer Kutools til Excel først.

Efter installation af Kutools til Excel skal du klikke Kutools AI > AI-hjælper at åbne Kutools AI Aide rude:

  1. Indtast dit krav i chatboksen, og klik Send knappen eller tryk på Indtast nøgle til at sende spørgsmålet;
    "Udtræk unikke værdier fra området A2:C9, ignorer tomme celler, og placer resultaterne startende ved E2:"
  2. Klik efter at have analyseret Udfør knappen for at køre. Kutools AI Aide vil behandle din anmodning ved hjælp af AI og returnere resultaterne i den angivne celle direkte i Excel.


Uddrag unikke værdier fra flere kolonner med pivottabel

Hvis du er fortrolig med pivottabellen, kan du nemt udtrække de unikke værdier fra flere kolonner med følgende trin:

1. Indsæt først en ny tom kolonne til venstre for dine data. I dette eksempel indsætter jeg kolonne A ved siden af ​​de originale data.

2. Klik på en celle i dine data, og tryk på Alt + D tasterne, og tryk derefter på P nøgle med det samme for at åbne Pivottabel og PivotChart-guide, vælg Flere konsolideringsområder i guiden trin 1, se skærmbillede:

3. Klik derefter på Næste knappen, tjek Opret et enkelt sidefelt for mig mulighed i guiden trin 2, se skærmbillede:

4. Fortsæt med at klikke Næste Klik for at vælge det dataområde, der inkluderer den nye venstre kolonne af celler, og klik derefter på Tilføj for at tilføje dataområdet til Alle intervaller listefelt, se skærmbillede:

5. Når du har valgt dataområdet, skal du klikke videre Næstei guiden trin 3 skal du vælge, hvor du vil placere pivottabellen, som du vil.

6. Endelig skal du klikke på Finish for at fuldføre guiden, og der er oprettet en pivottabel i det aktuelle regneark, og fjern derefter markeringen fra alle felter fra Vælg felter, der skal tilføjes til rapporten sektion, se skærmbillede:

7. Kontroller derefter feltet Værdi eller træk værdien til Rækker etiket, nu får du de unikke værdier fra de flere kolonner som følger:


Uddrag unikke værdier fra flere kolonner med VBA-kode

Med den følgende VBA-kode kan du også udtrække de unikke værdier fra flere kolonner.

1. Hold nede ALT + F11 tasterne, og det åbner Vinduet Microsoft Visual Basic til applikationer.

2. Klik indsatte > Moduler, og indsæt følgende kode i modulvinduet.

VBA: Uddrag unikke værdier fra flere kolonner

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. Tryk derefter på F5 for at køre denne kode, og der vises et promptfelt for at minde dig om at vælge det dataområde, du vil bruge. Se skærmbillede:

4. Og klik derefter på OK, vises en anden promptboks, der giver dig mulighed for at vælge et sted at placere resultatet, se skærmbillede:

5. Klik OK for at lukke denne dialog, og alle de unikke værdier er blevet ekstraheret på én gang.


Flere relative artikler:

  • Tæl antallet af unikke og tydelige værdier fra en liste
  • Antag, du har en lang liste med værdier med nogle duplikatelementer, nu vil du tælle antallet af unikke værdier (de værdier, der kun vises på listen en gang) eller forskellige værdier (alle forskellige værdier på listen betyder det unikt værdier + 1. duplikatværdier) i en kolonne som vist venstre skærmbillede. Denne artikel vil jeg tale om, hvordan man håndterer dette job i Excel.
  • Uddrag unikke værdier baseret på kriterier i Excel
  • Antag, at du har følgende dataområde, som du kun vil liste de unikke navne på kolonne B baseret på et specifikt kriterium i kolonne A for at få resultatet som vist nedenstående skærmbillede. Hvordan kunne du håndtere denne opgave i Excel hurtigt og nemt?
  • Tillad kun unikke værdier i Excel
  • Hvis du kun vil beholde unikke værdier i en kolonne i regnearket og forhindre duplikater, introducerer denne artikel nogle hurtige tricks, som du kan håndtere denne opgave.
  • Summen af ​​unikke værdier baseret på kriterier i Excel
  • For eksempel har jeg en række data, der indeholder kolonner Navn og ordre nu for kun at opsummere unikke værdier i kolonnen Ordre baseret på kolonnen Navn som vist nedenstående skærmbillede. Sådan løses denne opgave hurtigt og nemt i Excel?

Bedste kontorproduktivitetsværktøjer

🤖 Kutools AI Aide: Revolutionér dataanalyse baseret på: Intelligent udførelse   |  Generer kode  |  Opret brugerdefinerede formler  |  Analyser data og generer diagrammer  |  Aktiver Kutools funktioner...
Populære funktioner: Find, fremhæv eller identificer dubletter   |  Slet tomme rækker   |  Kombiner kolonner eller celler uden at miste data   |   Runde uden formel ...
Super opslag: VLookup med flere kriterier    Multiple Value VLookup  |   VOpslag på tværs af flere ark   |   Fuzzy Lookup ....
Avanceret rulleliste: Opret hurtigt rulleliste   |  Afhængig rulleliste   |  Multivælg rulleliste ....
Column Manager: Tilføj et bestemt antal kolonner  |  Flyt kolonner  |  Skift synlighedsstatus for skjulte kolonner  |  Sammenlign områder og kolonner ...
Fremhævede funktioner: Grid fokus   |  Designvisning   |   Stor Formel Bar    Arbejdsbog & Ark Manager   |  Ressourcebibliotek (Autotekst)   |  Datovælger   |  Kombiner regneark   |  Krypter/Dekrypter celler    Send e-mails efter liste   |  Superfilter   |   Specielt filter (filter fed/kursiv/gennemstreget...) ...
Top 15 værktøjssæt12 tekst Værktøjer (tilføje tekst, Fjern tegn, ...)   |   50 + Chart Typer (Gantt kort, ...)   |   40+ Praktisk formler (Beregn alder baseret på fødselsdag, ...)   |   19 Indsættelse Værktøjer (Indsæt QR-kode, Indsæt billede fra sti, ...)   |   12 Konvertering Værktøjer (Tal til ord, Valutaomregning, ...)   |   7 Flet og del Værktøjer (Avancerede kombinere rækker, Opdel celler, ...)   |   ... og mere

Overlad dine Excel-færdigheder med Kutools til Excel, og oplev effektivitet som aldrig før. Kutools til Excel tilbyder over 300 avancerede funktioner for at øge produktiviteten og spare tid.  Klik her for at få den funktion, du har mest brug for...

Beskrivelse


Fanen Office bringer en grænseflade til et kontor med Office, og gør dit arbejde meget lettere

  • Aktiver redigering og læsning af faner i Word, Excel, PowerPoint, Publisher, Access, Visio og Project.
  • Åbn og opret flere dokumenter i nye faner i det samme vindue snarere end i nye vinduer.
  • Øger din produktivitet med 50 % og reducerer hundredvis af museklik for dig hver dag!
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