Skip to main content

Conas luachanna uathúla a bhaint as ilcholúin in Excel?

Má ghlactar leis go bhfuil roinnt colún agat a bhfuil luachanna iolracha acu, déantar roinnt luachanna arís sa cholún céanna nó sa cholún difriúil sin. Agus anois níl uait ach na luachanna atá i gceachtar colún a fháil ach uair amháin. An bhfuil aon chleasanna gasta ann chun luachanna uathúla a bhaint as ilcholúin in Excel?


Sliocht luachanna uathúla ó ilcholúin le foirmle eagar

Seo foirmle eagar is féidir cabhrú leat na luachanna uathúla a bhaint as iliomad colúin.

1. Ag glacadh le do luachanna i raon A2: C9, cuir isteach an fhoirmle seo a leanas i gcill 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"),)&""

nótaí: San fhoirmle thuas, A2: C9 léiríonn sé an raon cealla ar mhaith leat na luachanna uathúla a bhaint astu, E1: E1 an chéad chill den cholún is mian leat an toradh a chur, $ 2: $ 9 i seastáin ó na sraitheanna tá na cealla is mian leat a úsáid, agus $ A: $ C. léiríonn go bhfuil na cealla is mian leat a úsáid sna colúin. Athraigh iad go dtí do cheann féin.

2. Ansin brúigh Shift + Ctrl + Iontráil eochracha le chéile, agus ansin tarraing an láimhseáil líonta chun na luachanna uathúla a bhaint go dtí go mbeidh cealla bána le feiceáil. Féach an pictiúr:


Sliocht luachanna uathúla ó iliomad colúin le Tábla Pivot

Má tá tú eolach ar an tábla pivot, is féidir leat na luachanna uathúla a bhaint as ilcholúin leis na céimeanna seo a leanas:

1. Ar dtús, cuir isteach colún bán nua ar thaobh na láimhe clé de do chuid sonraí, sa sampla seo, cuirfidh mé colún A isteach in aice leis na sonraí bunaidh.

2. Cliceáil cill amháin i do chuid sonraí, agus brúigh Alt + D eochracha, ansin brúigh P eochair láithreach chun an Draoi PivotTable agus PivotChart, roghnaigh Raonta comhdhlúthaithe iolracha sa chéim1 draoi, féach an scáileán:

3. Ansin cliceáil Ar Aghaidh cnaipe, seiceáil Cruthaigh réimse aon leathanaigh dom rogha i draoi step2, féach an scáileán:

4. Téigh ar cliceáil Ar Aghaidh cnaipe, cliceáil chun an raon sonraí a roghnú lena n-áirítear an colún nua cealla ar chlé, ansin cliceáil Cuir cnaipe chun an raon sonraí a chur leis an Gach raon bosca liosta, féach an scáileán:

5. Tar éis duit an raon sonraí a roghnú, lean ar aghaidh le cliceáil Ar Aghaidh, i gcéim 3 an draoi, roghnaigh cá háit ar mhaith leat an tuarascáil PivotTable a chur mar is mian leat.

6. Faoi dheireadh, cliceáil Críochnaigh chun an draoi a chríochnú, agus cruthaíodh tábla pivot sa bhileog oibre reatha, ansin dícheangail na réimsí uile ón Roghnaigh réimsí le cur leis an tuairisc alt, féach an pictiúr:

7. Ansin seiceáil an réimse luach nó tarraing an Luach go dtí an Rónna lipéad, anois gheobhaidh tú na luachanna uathúla ó na colúin iolracha mar seo a leanas:


Sliocht luachanna uathúla ó iliomad colúin le cód VBA

Leis an gcód VBA seo a leanas, is féidir leat na luachanna uathúla a bhaint as iliomad colúin.

1. Coinnigh síos an ALT + F11 eochracha, agus osclaíonn sé an Fuinneog Microsoft Visual Basic for Applications.

2. Cliceáil Ionsáigh > Modúil, agus greamaigh an cód seo a leanas i bhFuinneog an Mhodúil.

VBA: Sliocht luachanna uathúla ó iliomad colúin

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. Ansin brúigh F5 chun an cód seo a rith, agus tiocfaidh bosca pras amach le cur i gcuimhne duit an raon sonraí a theastaíonn uait a úsáid a roghnú. Féach an pictiúr:

4. Agus ansin cliceáil OK, beidh bosca pras eile le feiceáil a ligfidh duit áit a roghnú chun an toradh a chur, féach ar an scáileán:

5. cliceáil OK chun an dialóg seo a dhúnadh, agus baineadh na luachanna uathúla uile ag an am céanna.


Sliocht luachanna uathúla ó cholún amháin le gné iontach

Uaireanta, ní mór duit na luachanna uathúla a bhaint as colún amháin, ní chuideoidh na modhanna thuas leat, anseo, is féidir liom uirlis úsáideach a mholadh-Kutools le haghaidh Excel, Lena Sliocht cealla le luachanna uathúla (cuir an chéad dúblach san áireamh) fóntais, is féidir leat na luachanna uathúla a bhaint go tapa.

Nóta:Chun é seo a chur i bhfeidhm Sliocht cealla le luachanna uathúla (cuir an chéad dúblach san áireamh), ar dtús, ba cheart duit an Kutools le haghaidh Excel, agus ansin an ghné a chur i bhfeidhm go tapa agus go héasca.

Tar éis a shuiteáil Kutools le haghaidh Excel, déan mar seo le do thoil:

1. Cliceáil cill inar mian leat an toradh a aschur. (nótaí: Ná cliceáil cill sa chéad ró.)

2. Ansin cliceáil Kutools > Cúntóir Foirmle > Cúntóir Foirmle, féach ar an scáileán:

3. Sa an Cúntóir Foirmlí bosca dialóige, déan na hoibríochtaí seo a leanas le do thoil:

  • Roghnaigh Téacs rogha ón Foirmle cineál liosta anuas;
  • Ansin roghnaigh Sliocht cealla le luachanna uathúla (cuir an chéad dúblach san áireamh) ó na Roghnaigh fromula bosca liosta;
  • Ar dheis Ionchur argóintí roinn, roghnaigh liosta de na cealla ar mhaith leat luachanna uathúla a bhaint astu.

4. Ansin cliceáil Ok cnaipe, agus tarraing an láimhseáil líonta chuig na cealla ar mhaith leat na luachanna uathúla go léir a liostáil go dtí go dtaispeánfar cealla bána, féach an scáileán:

Íoslódáil saor in aisce Kutools le haghaidh Excel Anois!


Earraí níos coibhneasta:

  • Líon na Luachanna Uathúla agus ar Leith ó Liosta
  • Ag ceapadh, tá liosta fada luachanna agat le roinnt míreanna dúblacha, anois, ba mhaith leat líon na luachanna uathúla a chomhaireamh (na luachanna nach bhfuil le feiceáil ar an liosta ach uair amháin) nó luachanna ar leith (gach luach difriúil ar an liosta, ciallaíonn sé uathúil luachanna + 1ú luachanna dúblacha) i gcolún mar a thaispeántar ar chlé. An t-alt seo, labhróidh mé faoi conas déileáil leis an bpost seo in Excel.
  • Sliocht Luachanna Uathúla Bunaithe ar Chritéir in Excel
  • Ag ceapadh, tá an raon sonraí seo a leanas agat nach dteastaíonn uait ach ainmneacha uathúla cholún B a liostáil bunaithe ar chritéar sonrach de cholún A chun an toradh a fháil mar a thaispeántar thíos. Conas a d’fhéadfá déileáil leis an tasc seo in Excel go tapa agus go héasca?
  • Ná lig ach Luachanna Uathúla in Excel
  • Mura dteastaíonn uait ach luachanna uathúla a iontráil ag dul isteach i gcolún de bhileog oibre agus na dúbailtí a chosc, tabharfaidh an t-alt seo roinnt cleasanna gasta isteach duit chun déileáil leis an tasc seo.
  • Suim Luachanna Uathúla Bunaithe ar Chritéir In Excel
  • Mar shampla, tá raon sonraí agam ina bhfuil colúin Ainm agus Ord, anois, chun luachanna uathúla amháin a lua i gcolún Ordaithe bunaithe ar an gcolún Ainm mar a leanas an pictiúr a thaispeántar. Conas an tasc seo a réiteach go tapa agus go héasca in Excel?

Uirlisí Táirgiúlachta Oifige is Fearr

Gnéithe Coitianta: Faigh, Aibhsigh nó Aithnigh Dúblaigh   |  Scrios Sraitheanna Bána   |  Comhcheangail Colúin nó Cealla gan Sonraí a Chailleadh   |   Babhta gan Foirmle ...
Cuardaigh Super: Ilchritéir VLookup    VLookup Illuachanna  |   VLookup Trasna Ilbhileoga   |   Amharc doiléir ....
Liosta anuas Casta: Go tapa Cruthaigh Liosta Anuas   |  Liosta anuas Cleithiúnach   |  Liosta Buail Isteach Ilroghnacha ....
Bainisteoir Colún: Cuir Líon Sonrach Colún leis  |  Colúin Bog  |  Scoránaigh Stádas Infheictheachta na gColún Ceilte  |  Déan comparáid idir Raonta & Colúin ...
Gnéithe Réadmhaoin: Fócas Eangaí   |  Amharc Dearaidh   |   Barra Mór na Foirmle    Leabhar Oibre & Bainisteoir Bileog   |  Leabharlann Acmhainní (Uaththéacs)   |  Piocálaí Dáta   |  Comhcheangail Bileoga Oibre   |  Criptigh/Díchriptigh Cealla    Seol Ríomhphost trí Liosta   |  Scagaire Super   |   Scagaire Speisialta (scagaire trom/iodálach/stailc tríd...) ...
Barr 15 Uirlisí12 Téacs uirlisí (Cuir Téacs, Bain Carachtair,...)   |   50 + Cairt cineálacha (Cairt Gantt,...)   |   40+ Praiticiúil Foirmlí (Ríomh aois bunaithe ar lá breithe,...)   |   19 Insertion uirlisí (Cuir isteach Cód QR, Ionsáigh Pictiúr ón gCosán,...)   |   12 Tiontú uirlisí (Uimhreacha le Focail, Comhshó Airgeadra,...)   |   7 Cumaisc & Scoilt uirlisí (Sraitheanna Comhcheangail Casta, Cealla Scoilt,...)   |   ... agus eile

Supercharge Do Scileanna Excel le Kutools le haghaidh Excel, agus Éifeachtúlacht Taithí Cosúil Ná Roimhe. Kutools le haghaidh Excel Tairiscintí Níos mó ná 300 Ardghnéithe chun Táirgiúlacht a Treisiú agus Sábháil Am.  Cliceáil anseo chun an ghné is mó a theastaíonn uait a fháil ...

cluaisín kte 201905


Tugann Tab Oifige comhéadan Tabbed chuig Office, agus Déan Do Obair i bhfad Níos Éasca

  • Cumasaigh eagarthóireacht agus léamh tabbed i Word, Excel, PowerPoint, Foilsitheoir, Rochtain, Visio agus Tionscadal.
  • Oscail agus cruthaigh cáipéisí iolracha i gcluaisíní nua den fhuinneog chéanna, seachas i bhfuinneoga nua.
  • Méadaíonn do tháirgiúlacht 50%, agus laghdaíonn sé na céadta cad a tharlaíonn nuair luch duit gach lá!
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