Conas gach teaglaim féideartha in Excel a liostáil nó a ghiniúint?

Ligean le rá, tá an dá cholún sonraí seo a leanas agam, agus anois, ba mhaith liom liosta de na teaglamaí uile is féidir a ghiniúint bunaithe ar an dá liosta luachanna mar a thaispeántar ar chlé. B’fhéidir, is féidir leat na teaglamaí go léir a liostáil ceann ar cheann mura bhfuil mórán luachanna ann, ach, má tá roinnt colúin le luachanna iolracha ag teastáil chun na teaglamaí féideartha a liostáil, seo roinnt cleasanna gasta a d’fhéadfadh cabhrú leat déileáil leis an bhfadhb seo in Excel .

Déan liosta nó giniúint gach teaglaim féideartha ó dhá liosta le foirmle

Déan liosta nó giniúint gach teaglaim féideartha ó thrí liosta nó níos mó le cód VBA

Déan liosta nó giniúint gach teaglaim féideartha ó iliomad liostaí le gné chumhachtach

Déan liosta nó giniúint gach teaglaim féideartha ó dhá liosta le foirmle

Is féidir leis an bhfoirmle fhada seo a leanas cabhrú leat gach teaglaim féideartha de luachanna dhá liosta a liostáil go tapa, déan mar a leanas:

1. Iontráil nó cóipeáil an fhoirmle thíos i gcill bhán, sa chás seo, cuirfidh mé isteach í i gcill D2, agus ansin brúigh Iontráil eochair chun an toradh a fháil, féach an scáileán:

=IF(ROW()-ROW($D$2)+1>COUNTA($A$2:$A$5)*COUNTA($B$2:$B$4),"",INDEX($A$2:$A$5,INT((ROW()-ROW($D$2))/COUNTA($B$2:$B$4)+1))&"-"&INDEX($B$2:$B$4,MOD(ROW()-ROW($D$2),COUNTA($B$2:$B$4))+1))

nótaí: San fhoirmle thuas, $ A $ 2: $ A $ 5 is é raon luachanna na chéad cholúin, agus $ B $ 2: $ B $ 4 is é raon luachanna an dara liosta ar mhaith leat na teaglamaí uile is féidir a liostáil, an $ D $ 2 an cill a chuir tú an fhoirmle, is féidir leat na tagairtí cille do do riachtanas a athrú.

2. Ansin roghnaigh cill D2 agus tarraing an láimhseáil líonta síos go dtí na cealla go dtí go bhfaighidh tú na cealla bána, agus tá na teaglamaí féideartha uile liostaithe bunaithe ar luachanna an dá liosta. Féach an pictiúr:

Déan liosta nó giniúint gach teaglaim féideartha ó thrí liosta nó níos mó le cód VBA

B’fhéidir go bhfuil sé deacair duit an fhoirmle thuas a chur i bhfeidhm, má tá sonraí iolracha colúin ann, beidh sé trioblóideach í a mhodhnú. Anseo, tabharfaidh mé cód VBA isteach chun déileáil leis go tapa.

1. Coinnigh síos an ALT + F11 eochracha a oscailt Microsoft Visual Basic d’Fheidhmchláir fhuinneog.

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

Cód VBA: Cruthaigh gach teaglaim de 3 cholún nó níos mó

Sub ListAllCombinations()
'Updateby Extendoffice
Dim xDRg1, xDRg2, xDRg3 As Range
Dim xRg  As Range
Dim xStr As String
Dim xFN1, xFN2, xFN3 As Integer
Dim xSV1, xSV2, xSV3 As String
Set xDRg1 = Range("A2:A5")  'First column data
Set xDRg2 = Range("B2:B4")  'Second column data
Set xDRg3 = Range("C2:C4")  'Third column data
xStr = "-"   'Separator
Set xRg = Range("E2")  'Output cell
For xFN1 = 1 To xDRg1.Count
    xSV1 = xDRg1.Item(xFN1).Text
    For xFN2 = 1 To xDRg2.Count
        xSV2 = xDRg2.Item(xFN2).Text
      For xFN3 = 1 To xDRg3.Count
        xSV3 = xDRg3.Item(xFN3).Text
        xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3
        Set xRg = xRg.Offset(1, 0)
       Next
    Next
Next
End Sub

nótaí: Sa chód thuas, A2: A5, B2: B4, C2: C4 an bhfuil an raon sonraí a theastaíonn uait a úsáid, E2 an cill aschuir is mian leat na torthaí a aimsiú. Más mian leat gach teaglaim de níos mó colún a fháil, athraigh agus cuir paraiméadair eile leis an gcód mar do riachtanas.

3. Ansin, brúigh F5 eochair chun an cód seo a rith, agus ginfear gach teaglaim de na 3 cholún ag an am céanna, féach an scáileán:

Déan liosta nó giniúint gach teaglaim féideartha ó iliomad liostaí le gné chumhachtach

Má tá liostaí iolracha ann is gá luachanna a liostáil na teaglamaí féideartha, b’fhéidir go mbeadh sé deacair duit an cód a mhodhnú. Anseo, is féidir liom uirlis chumhachtach a mholadh - Kutools le haghaidh Excel, tá gné áisiúil ann Liostaigh Gach Teaglaim ar féidir leo na teaglamaí uile is féidir a liostáil go tapa bunaithe ar liostaí sonraí tugtha.

Leideanna:Chun é seo a chur i bhfeidhm Liostaigh Gach Teaglaim gné, 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 Kutools > Ionsáigh > Liostaigh Gach Teaglaim, féach ar an scáileán:

2. Sa an Liostaigh Gach Teaglaim bosca dialóige, déan na hoibríochtaí mar atá thíos an taispeána a thaispeántar:

3. Ansin tá na luachanna agus na deighilteoirí sonraithe uile liostaithe sa bhosca dialóige, féach an scáileán:

4Agus ansin cliceáil Ok cnaipe, agus popfaidh bosca pras amach le cur i gcuimhne duit cill a roghnú chun an toradh a aschur, féach an scáileán:

5. Cliceáil OK, gineadh gach ceann de na teaglamaí féideartha bunaithe ar na liostaí a tugadh isteach sa bhileog oibre mar a thaispeántar an scáileán a leanas:

Cliceáil chun Íoslódáil Kutools le haghaidh Excel Anois!

Earraí níos coibhneasta:

Cruthaigh Gach Comhcheangal de 3 Nó Il-Cholún
Ag ceapadh, tá 3 cholún sonraí agam, anois, ba mhaith liom gach teaglaim de na sonraí sna 3 cholún seo a ghiniúint nó a liostáil mar atá thíos an pictiúr a thaispeántar. An bhfuil aon mhodhanna maithe agat chun an tasc seo a réiteach in Excel?

Faigh Gach Teaglaim atá Comhionann le Suim tugtha
Mar shampla, tá an liosta uimhreacha seo a leanas agam, agus anois, ba mhaith liom a fháil amach cén teaglaim d’uimhreacha ar an liosta suim suas le 480, sa phictiúr a leanas a thaispeántar, is féidir leat a fheiceáil go bhfuil cúig ghrúpa de chomhcheangail fhéideartha ann a bhfuil cothrom leo go 480, mar shampla 300 + 60 + 120, 300 + 60 + 40 + 80, srl. An t-alt seo, labhróidh mé faoi roinnt modhanna chun a fháil amach cé na cealla arb ionann iad agus luach sonrach in Excel.

Gin nó Liostáil Gach Permutations Féideartha
Mar shampla, tá trí charachtar XYZ agam, anois, ba mhaith liom gach tréscaoilteacht féideartha a liostáil bunaithe ar na trí charachtar seo chun sé thoradh dhifriúla a fháil mar seo: XYZ, XZY, YXZ, YZX, ZXY agus ZYX. In Excel, conas a d’fhéadfá gach tréscaoilteacht a ghiniúint nó a liostáil go tapa bunaithe ar líon difriúil carachtar?

Cruthaigh Liosta de na Teaglaim 4 Digití Féideartha go léir
I roinnt cásanna, b’fhéidir go mbeidh orainn liosta a ghiniúint de na teaglamaí 4 dhigit uile is féidir de uimhir 0 go 9, rud a chiallaíonn liosta de 0000, 0001, 0002… 9999 a ghiniúint. Chun tasc an liosta in Excel a réiteach go tapa, tugaim roinnt cleasanna isteach duit.

Na hUirlisí Táirgiúlachta Oifige is Fearr

Réitíonn Kutools for Excel an chuid is mó de do chuid Fadhbanna, agus Méadaíonn sé do Tháirgiúlacht 80%

Athúsáid: Cuir isteach go tapa foirmlí casta, cairteacha agus aon rud a d'úsáid tú roimhe seo; Cealla a Chriptiú le pasfhocal; Cruthaigh Liosta Ríomhphoist agus seol ríomhphoist ...
Barra Foirmle Super (cuir línte iolracha téacs agus foirmle in eagar go héasca); Leagan Amach Léitheoireachta (líon mór cealla a léamh agus a chur in eagar go héasca); Greamaigh go dtí an Raon Scagtha...
Cumaisc Cealla / Sraitheanna / Colúin gan Sonraí a chailleadh; Ábhar Cealla Scoilt; Comhcheangail Sraitheanna / Colúin Dúblacha... Cill Dúblach a Chosc; Déan comparáid idir Ranganna...
Roghnaigh Dúblach nó Uathúil Sraitheanna; Roghnaigh Blank Rows (tá na cealla uile folamh); Aimsigh Super agus Fuzzy Aimsigh i go leor Leabhar Oibre; Roghnaigh go randamach ...
Cóip Díreach Cealla Il gan tagairt fhoirmle a athrú; Tagairtí Cruthaigh Auto chuig Bileoga Il; Cuir Urchair isteach, Boscaí Seiceála agus go leor eile ...
Sliocht Téacs, Cuir Téacs leis, Bain de réir Poist, Bain Spás; Subtotals Paging a chruthú agus a phriontáil; Tiontaigh Idir Ábhar Cealla agus Tráchtanna...
Scagaire Super (scéimeanna scagaire a shábháil agus a chur i bhfeidhm ar bhileoga eile); Ard-Sórtáil de réir míosa / seachtaine / lae, minicíocht agus níos mó; Scagaire Speisialta le cló trom, iodálach ...
Comhcheangail Leabhair Oibre agus Bileoga Oibre; Cumaisc Táblaí bunaithe ar eochaircholúin; Roinn Sonraí i Ilbhileoga; Baisc Tiontaigh xls, xlsx agus PDF...
Níos mó ná 300 gné chumhachtach. Tacaíochtaí Office / Excel 2007-2019 agus 365. Tacaíonn sé le gach teanga. Imscaradh éasca i d’fhiontar nó d’eagraíocht. Gnéithe iomlána triail saor in aisce 30 lá. Ráthaíocht ar ais airgid 60 lá.

Leigh Nios mo... Íoslódáil saor in aisce ... Ceannach ...

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á!

Leigh Nios mo... Íoslódáil saor in aisce ... Ceannach ...

32 Comments
Login

Sort by Newest
- Best
- Popular
- Newest
- Oldest

You are guest

or post as a guest, but your post won't be published automatically.

Loading comment... The comment will be refreshed after 00:00.

To post as a guest, your comment is unpublished.
robertsu · 2 hours ago
How to remove the repetition if I have color 1, color 2, color 3 in different orders(i.e. color2, color 3, color 1) but they are the same for my need? tks!
- Reply
To post as a guest, your comment is unpublished.
Juan Tomás · 1 years ago
Excelentes soluciones, muchas gracias, lo logré con Kutools
- Reply
To post as a guest, your comment is unpublished.
RAFFAELE · 1 years ago
NON FUNZIONA. HO SEGUITO GLI STESSI PASSAGGI MA MI DICE CHE C'E' UN ERRORE
- Reply
To post as a guest, your comment is unpublished.
Guy · 1 years ago
Is there a way to output this as a txt file? When you have millions of results in the D column it's not exactly practical to drag the fill handle.
- Reply
To post as a guest, your comment is unpublished.
JJ · 2 years ago
Almost very handy. Would be good if it actually used whatever is in the cell ($A$1)and not convert the cell to text. Then I could just paste what I need in A1 and run it again without having to change anything.
- Reply
To post as a guest, your comment is unpublished.
pocholoclub@gmail.com · 2 years ago
I used the extions and works fantastic,, but now i have a problem, i need to calculate the sum,, but the result is a text,, i enter a separator that is the simbol +, but finally i have a text and is impossible to convert in a operation.
- Reply
To post as a guest, your comment is unpublished.
sergio · 2 years ago
Hello, and if these combinations intead text are number,, how can i made the calculation for the results???? i try to add the "+" but excel dont know that is the simbol.. how you solve this?
- Reply
To post as a guest, your comment is unpublished.
ricardo · 2 years ago
Hola me arroja una referencia circula la formula
=SI(FILA()-FILA($D$1)+1>(CONTARA($A$1:$A$4)*CONTARA($B$1:$B$3));"";INDICE($A$1:$A$4;RESIDUO((FILA()-FILA($D$1));COUNTA($B$1:$B$3)+1))
&INDICE($B$1:$B$3;RESIDUO(FILA()-FILA($D$1);CONTARA($B$1:$B$3))+1))
- Reply
To post as a guest, your comment is unpublished.
anirbank · 3 years ago
Why my formula did not work to create 25 combinations in Column E (5 elements from Col A * 5 elements from Col B) ? Whereas the formula in this article worked in Column D when I dragged it for 25 cells? Screenshot - https://prnt.sc/ihwr18
- Reply
To post as a guest, your comment is unpublished.
Mihai · 3 years ago
Hi,
I want to know what to do if I want to generate more than 1,4 million combination and I exceede the Excel rows limit?
Is there a way of puting Kudos to continue its iteration on the next column?
- Reply
To post as a guest, your comment is unpublished.
Marco · 3 years ago
Has anyone figured out a formula yet to do this for 5 sets of data? I've been stumped on this so far.
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 3 years ago
  
  Hello, Marco,
  The Kutools can help you to solve your problem quickly,please check the following screenshot:
  Please try it, hope it can help you, thank you!
  
  未标题-1.png
  
  Reply
To post as a guest, your comment is unpublished.
acomuna · 3 years ago
Is KuTools able to sort cells in a row in a "simple combination" fashion?
I mean, if I have these data:
_________________________
John Jack Paul Macy

Mark Larry

Jerry Paul Mary

Sam Jeff Peter Lucas
_________________________

And I want to output them like this:
___________
John Jack

John Paul

John Macy

Jack Paul

Jack Macy

Paul Macy

Mark Larry

Jerry Paul

Jerry Marry

Paul Mary

Sam Jeff

Sam Peter

Sam Lucas

Jeff Peter

Jeff Lucas

Peter Lucas
____________

How can I do this? Can KuTools do sth like this?

Thanks!
- Reply
- To post as a guest, your comment is unpublished.
  skyyang · 3 years ago
  
  Hi, Arthur,
  Sorry, the Kutools can not help you to solve this job as you said.
  Thank your for your comment.
  
  Reply
To post as a guest, your comment is unpublished.
Eoin · 3 years ago
Any idea how to add a space inbetween ?
- Reply
- To post as a guest, your comment is unpublished.
  Mary · 3 years ago
  
  You can add a space by adding " "& before you index column B, right after the existing &, like so.....
  
  =IF(ROW()-ROW($D$1)+1>COUNTA($A$1:$A$4)*COUNTA($B$1:$B$3),"",INDEX($A$1:$A$4,INT((ROW()-ROW($D$1))/COUNTA($B$1:$B$3)+1))&" "&INDEX($B$1:$B$3,MOD(ROW()-ROW($D$1),COUNTA($B$1:$B$3))+1))
  
  ....or any other delimiter you like!
  
  Reply
- To post as a guest, your comment is unpublished.
  skyyang · 3 years ago
  
  Hello, Eoin,
  the formula in this article can not help you to add a space between the cell values, but, you can apply the Kutools for Excel, with it, you can type any delimiter as you want to separate the combined result, see screenshot:
  
  doc-1.png
  
  Reply
To post as a guest, your comment is unpublished.
JiffyLimbo · 3 years ago
For more columns:
The first part of the formulate needs to be modified to multiply all the possibilities, for example this would be for 6 columns:

COUNTA($A$2:$A$9)*COUNTA($B$2:$B$9)*COUNTA($C$2:$C$9)*COUNTA($D$2:$D$9)*COUNTA($E$2:$E$9)*COUNTA($F$2:$F$9)
so you are adding
*COUNTA(YourRangeHere)
for each column

The second part of the formula needs to be modified for each column as well like so:

INDEX($A$2:$A$9,MOD(ROW()-ROW($I$2),COUNTA($A$2:$A$9))+1)&INDEX($B$2:$B$9,MOD(ROW()-ROW($I$2),COUNTA($B$2:$B$9))+1)&INDEX($C$2:$C$9,MOD(ROW()-ROW($I$2),COUNTA($C$2:$C$9))+1)&INDEX($D$2:$D$9,MOD(ROW()-ROW($I$2),COUNTA($D$2:$D$9))+1)&INDEX($E$2:$E$9,MOD(ROW()-ROW($I$2),COUNTA($E$2:$E$9))+1)&INDEX($F$2:$F$9,MOD(ROW()-ROW($I$2),COUNTA($F$2:$F$9))+1)

so you are adding

&INDEX(YourRangeHere,MOD(ROW()-ROW($I$2),COUNTA(YourRangeHere))+1)

So put them all together and you get this example for 6 columns:

=IF(ROW()-ROW($I$2)+1>COUNTA($A$2:$A$9)*COUNTA($B$2:$B$9)*COUNTA($C$2:$C$9)*COUNTA($D$2:$D$9)*COUNTA($E$2:$E$9)*COUNTA($F$2:$F$9),"",INDEX($A$2:$A$9,MOD(ROW()-ROW($I$2),COUNTA($A$2:$A$9))+1)&INDEX($B$2:$B$9,MOD(ROW()-ROW($I$2),COUNTA($B$2:$B$9))+1)&INDEX($C$2:$C$9,MOD(ROW()-ROW($I$2),COUNTA($C$2:$C$9))+1)&INDEX($D$2:$D$9,MOD(ROW()-ROW($I$2),COUNTA($D$2:$D$9))+1)&INDEX($E$2:$E$9,MOD(ROW()-ROW($I$2),COUNTA($E$2:$E$9))+1)&INDEX($F$2:$F$9,MOD(ROW()-ROW($I$2),COUNTA($F$2:$F$9))+1))

Where you would paste this formula into $I$2 and it would be looking at $A$2:$A$9 $B$2:$B$9 $C$2:$C$9 ... up to $F$2:$F$9
- Reply
- To post as a guest, your comment is unpublished.
  JiffyLimbo · 3 years ago
  
  this doesn't work :( more complicated than this
  
  Reply
To post as a guest, your comment is unpublished.
JiffyLimbo · 3 years ago
For more columns:
The first part of the formulate needs to be modified to multiply all the possibilities, for example this would be for 6 columns
COUNTA($A$2:$A$9)*COUNTA($B$2:$B$9)*COUNTA($C$2:$C$9)*COUNTA($D$2:$D$9)*COUNTA($E$2:$E$9)*COUNTA($F$2:$F$9)
so you are adding
*COUNTA(YourRangeHere)
for each column
The second part of the formula needs to be modified for each column as well like so:
INDEX($A$2:$A$9,MOD(ROW()-ROW($I$2),COUNTA($A$2:$A$9))+1)&INDEX($B$2:$B$9,MOD(ROW()-ROW($I$2),COUNTA($B$2:$B$9))+1)&INDEX($C$2:$C$9,MOD(ROW()-ROW($I$2),COUNTA($C$2:$C$9))+1)&INDEX($D$2:$D$9,MOD(ROW()-ROW($I$2),COUNTA($D$2:$D$9))+1)&INDEX($E$2:$E$9,MOD(ROW()-ROW($I$2),COUNTA($E$2:$E$9))+1)&INDEX($F$2:$F$9,MOD(ROW()-ROW($I$2),COUNTA($F$2:$F$9))+1)
so you are adding
&INDEX(YourRangeHere,MOD(ROW()-ROW($I$2),COUNTA(YourRangeHere))+1)

So put them all together and you get this example for 6 columns:

=IF(ROW()-ROW($I$2)+1>COUNTA($A$2:$A$9)*COUNTA($B$2:$B$9)*COUNTA($C$2:$C$9)*COUNTA($D$2:$D$9)*COUNTA($E$2:$E$9)*COUNTA($F$2:$F$9),"",INDEX($A$2:$A$9,MOD(ROW()-ROW($I$2),COUNTA($A$2:$A$9))+1)&INDEX($B$2:$B$9,MOD(ROW()-ROW($I$2),COUNTA($B$2:$B$9))+1)&INDEX($C$2:$C$9,MOD(ROW()-ROW($I$2),COUNTA($C$2:$C$9))+1)&INDEX($D$2:$D$9,MOD(ROW()-ROW($I$2),COUNTA($D$2:$D$9))+1)&INDEX($E$2:$E$9,MOD(ROW()-ROW($I$2),COUNTA($E$2:$E$9))+1)&INDEX($F$2:$F$9,MOD(ROW()-ROW($I$2),COUNTA($F$2:$F$9))+1))

Where you would paste this formula into $I$2 and it would be looking at $A$2:$A$9 $B$2:$B$9 $C$2:$C$9 ... up to $F$2:$F$9
- Reply
- To post as a guest, your comment is unpublished.
  JiffyLimbo · 3 years ago
  
  this doesnt work :( more complicated than this
  
  Reply
To post as a guest, your comment is unpublished.
Javier · 4 years ago
anyone know how to modify this to reflect 6 columns of data?
- Reply
To post as a guest, your comment is unpublished.
Javier · 4 years ago
anyone know how to modify this to reflect 6 columns of data, rather than just two?
- Reply
- To post as a guest, your comment is unpublished.
  shey · 3 years ago
  
  looking for the same thing. anyone know?
  
  Reply
To post as a guest, your comment is unpublished.
NB · 4 years ago
How could you use KuTools, or even a formula, if you wanted all the permutations of the Month header. Jan, Jan and Feb, Jan and Mar, Jan and Mar, Jan and Feb and Mar, etc etc
- Reply
To post as a guest, your comment is unpublished.
jitu · 4 years ago
VERY HELPFUL.I am now able to generate combinations very easily.
- Reply
To post as a guest, your comment is unpublished.
Veerendra Kumar · 4 years ago
Can I get the formula for all the combinations of a 5*5 matrix (5 rows and 5 columns ) I tried but not able to get please help me.......
- Reply
To post as a guest, your comment is unpublished.
Free · 4 years ago
Is there a way to make this formula work where it produces the various combinations still separated in 2 separate columns, but adjacent to one another?
- Reply
- To post as a guest, your comment is unpublished.
  Bill · 4 years ago
  
  Have you had any luck with this yet? I'm trying to do the same thing and have all of the possible combinations next to each other in a 2 separate columns.
  
  Reply
- To post as a guest, your comment is unpublished.
  Bill · 4 years ago
  
  Have you had any luck figuring out how to do this? I'm trying to do the exact same thing and have all of my probabilities in 2 separate columns in excel.
  
  Reply
To post as a guest, your comment is unpublished.
GATES · 4 years ago
example : my list of values is 1,2,3,4,5,6......80 , i want kutool to display all combinations of a set of 2 numbers, example : 1-2 , 1-3 , 1-4 , 1-5 , ..................79-80 . is this possible with KUTOOL ?
- Reply
To post as a guest, your comment is unpublished.
Kyle · 5 years ago
This is a brilliant formula! I have no clue how it works, but I just change the collum A and B to match how long my lists are, and have my output in D1.
- Reply

Logáil isteach do chuntas

Athshocraigh Pasfhocal