Note: The other languages of the website are Google-translated. Back to English
Logáil isteach  \/ 
x
or
x
Cláraigh  \/ 
x

or

Conas gach teaglaim de 3 cholún nó níos mó a ghiniúint in Excel?

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?

Cruthaigh gach teaglaim bunaithe ar 3 cholún sonraí trí fhoirmle a úsáid

Cruthaigh gach teaglaim bunaithe ar 3 cholún sonraí nó níos mó trí chód VBA a úsáid

Cruthaigh gach teaglaim bunaithe ar 3 cholún sonraí nó níos mó trí ghné uamhnach a úsáid


Cruthaigh gach teaglaim bunaithe ar 3 cholún sonraí trí fhoirmle a úsáid

Is féidir leis an bhfoirmle fhada seo a leanas cabhrú le gach teaglaim de 3 cholún a liostáil, déan mar seo:

1. Le do thoil, cliceáil cill áit ar féidir an toradh a aschur, agus ansin déan an fhoirmle thíos a chóipeáil agus a ghreamú inti:

=IFERROR(INDEX($A$2:$A$4,INT((ROW(1:1)-1)/((COUNTA($B$2:$B$6)*(COUNTA($C$2:$C$5)))))+1)&"-"&INDEX($B$2:$B$6,MOD(INT((ROW(1:1)-1)/COUNTA($C$2:$C$5)),COUNTA($B$2:$B$6))+1)&"-"&INDEX($C$2:$C$5,MOD((ROW(1:1)-1),COUNTA($C$2:$C$5))+1),"")

nótaí: San fhoirmle seo: A2: A4, B2: B6, C2: C5 is iad na raonta sonraí a theastaíonn uait a úsáid.

2. Ansin, tarraing an láimhseáil líonta síos go dtí na cealla go dtí go dtaispeántar cealla bána, is é sin le rá gur liostaíodh gach teaglaim de na 3 cholún, féach an scáileán:


Cruthaigh gach teaglaim bunaithe ar 3 cholún sonraí nó níos mó trí chód VBA a úsáid

Tá sé deacair an fhoirmle fhada thuas a úsáid, má tá ilcholúin ann is gá sonraí a úsáid, 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 i bhFuinneog an Mhodúil.

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:A4")  'First column data
Set xDRg2 = Range("B2:B6")  'Second column data
Set xDRg3 = Range("C2:C5")  '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: A4, B2: B6, C2: C5 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 nó níos mó ag an am céanna, féach an scáileán:


Cruthaigh gach teaglaim bunaithe ar 3 cholún sonraí nó níos mó trí ghné uamhnach a úsáid

Má tá tú Kutools le haghaidh Excel, lena chumhachtach Liostaigh Gach Teaglaim gné, is féidir leat gach teaglaim de ilcholúin a liostáil go tapa agus go héasca.

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, sonraigh sonraí na gcolún agus na deighilteoirí chun na teaglamaí a liostáil mar a leanas an pictiúr a thaispeántar:

3. Tar éis na sonraí agus an deighilteoir a shocrú, ansin, cliceáil Ok cnaipe, sa chéad bhosca pras eile, roghnaigh cill chun an toradh a aimsiú, féach an scáileán:

4. Agus ansin, cliceáil OK cnaipe, gineadh gach teaglaim láithreach mar atá thíos an pictiúr a thaispeántar:

Cliceáil chun Íoslódáil Kutools le haghaidh Excel agus triail saor in aisce Anois!



  • 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 agus Sonraí a Choinneáil; Ábhar Cealla Scoilt; Comhcheangail Sraitheanna Dúblacha agus Suim / Meán... 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 ...
  • Foirmlí is Fearr agus Cuir isteach go tapa, Ranganna, Cairteacha agus Pictiúir; Cealla a Chriptiú le pasfhocal; Cruthaigh Liosta Ríomhphoist agus seol ríomhphoist ...
  • 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...
  • Grúpáil Tábla Pivot de réir uimhir na seachtaine, lá na seachtaine agus níos mó ... Taispeáin Cealla Díghlasáilte, Faoi Ghlas de réir dathanna éagsúla; Aibhsigh Cealla a bhfuil Foirmle / Ainm orthu...
cluaisín kte 201905
  • 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á!
bun officetab
Say something here...
symbols left.
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.
    sarah · 1 months ago
    Hello So this is a code for 9 columns :') 

    Sub ListAllCombinations()
    'Updateby Extendoffice
    Dim xDRg1, xDRg2, xDRg3, xDRg4, xDRg5, xDRg6, xDRg7, xDRg8, xDRg9 As Range
    Dim xRg As Range
    Dim xStr As String
    Dim xFN1, xFN2, xFN3, xFN4, xFN5, xFN6, xFN7, xFN8, xFN9 As Integer
    Dim xSV1, xSV2, xSV3, xSV4, xSV5, xSV6, xSV7, xSV8, xSV9 As String
    Set xDRg1 = Range("A2:A3") 'First column data
    Set xDRg2 = Range("B2:B3") 'Second column data
    Set xDRg3 = Range("C2:C10") 'Third column data
    Set xDRg4 = Range("D2:D2") 'Third column data
    Set xDRg5 = Range("E2:E3") 'Third column data
    Set xDRg6 = Range("F2:F3") 'Third column data
    Set xDRg7 = Range("G2:G4") 'Third column data
    Set xDRg8 = Range("H2:H3") 'Third column data
    Set xDRg9 = Range("I2:I3") 'Third column data
    xStr = "-" 'Separator
    Set xRg = Range("K2") '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
    For xFN4 = 1 To xDRg4.Count
    xSV4 = xDRg4.Item(xFN4).Text
    For xFN5 = 1 To xDRg5.Count
    xSV5 = xDRg5.Item(xFN5).Text
    For xFN6 = 1 To xDRg6.Count
    xSV6 = xDRg6.Item(xFN6).Text
    For xFN7 = 1 To xDRg7.Count
    xSV7 = xDRg7.Item(xFN7).Text
    For xFN8 = 1 To xDRg8.Count
    xSV8 = xDRg8.Item(xFN8).Text
    For xFN9 = 1 To xDRg9.Count
    xSV9 = xDRg9.Item(xFN9).Text
    xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3 & xStr & xSV4 & xStr & xSV5 & xStr & xSV6 & xStr & xSV7 & xStr & xSV8 & xStr & xSV9
    Set xRg = xRg.Offset(1, 0)
    Next
    Next
    Next
    Next
    Next
    Next
    Next
    Next
    Next
    End Sub
  • To post as a guest, your comment is unpublished.
    Jayanta · 4 months ago
    Hi
    In VBA code I used four column and range of the column are E2:E75, B2:B267, C2:C195 & D2:D267. Out put range is J2. In this case out put result was exceed row limit. Please help to solve the error
  • To post as a guest, your comment is unpublished.
    Betty · 10 months ago

    Thank you so much for this code. I have modified the code for the amount of column I need (25).

    Thanks,

  • To post as a guest, your comment is unpublished.
    H · 11 months ago
    Thank you so much. Exactly what I need :-)))
  • To post as a guest, your comment is unpublished.
    Karthik · 11 months ago
    =IFERROR(INDEX($A$2:$A$5,INT((ROW(1:1)-1)/((COUNTA($B$2:$B$3)*(COUNTA($C$2:$C$3)* (COUNTA($D$2:$D$4)* (COUNTA($E$2:$E$6)* (COUNTA($H$2:$H$6)* (COUNTA($G$2:$G$6)* (COUNTA($H$2:$H$6))))))))))+1)&"-"&INDEX($B$2:$B$3,MOD(INT((ROW(1:1)-1)/COUNTA($C$2:$C$3)),COUNTA($B$2:$B$3))+1)&"-" &INDEX($C$2:$C$3,MOD(INT((ROW(1:1)-1)/COUNTA($D$2:$D$4)),COUNTA($C$2:$C$3))+1)&"-" &INDEX($D$2:$D$4,MOD(INT((ROW(1:1)-1)/COUNTA($E$2:$E$6)),COUNTA($D$2:$D$4))+1)&"-" &INDEX($E$2:$E$6,MOD(INT((ROW(1:1)-1)/COUNTA($F$2:$F$6)),COUNTA($E$2:$E$6))+1)&"-" &INDEX($F$2:$F$6,MOD(INT((ROW(1:1)-1)/COUNTA($G$2:$G$6)),COUNTA($F$2:$F$6))+1)&"-" &INDEX($G$2:$G$6,MOD(INT((ROW(1:1)-1)/COUNTA($H$2:$H$6)),COUNTA($G$2:$G$6))+1)&"-"&INDEX($H$2:$H$6,MOD((ROW(1:1)-1),COUNTA($H$2:$H$6))+1),"")
    • To post as a guest, your comment is unpublished.
      jen · 11 months ago
      i need this formula for 4 columns
  • To post as a guest, your comment is unpublished.
    Nikhil · 1 years ago
    HOw to do below formula for 5 columns? Trying to figure out but its giving error

    =IFERROR(INDEX($A$2:$A$4,INT((ROW(1:1)-1)/((COUNTA($B$2:$B$6)*(COUNTA($C$2:$C$5)))))+1)&"-"&INDEX($B$2:$B$6,MOD(INT((ROW(1:1)-1)/COUNTA($C$2:$C$5)),COUNTA($B$2:$B$6))+1)&"-"&INDEX($C$2:$C$5,MOD((ROW(1:1)-1),COUNTA($C$2:$C$5))+1),"")
    • To post as a guest, your comment is unpublished.
      skyyang · 1 years ago
      Hello, Nikhil,
      For getting all combinations from 5 columns, may be the below VBA code can help you, please change the cell references to your data.

      Sub ListAllCombinations() 'Updateby Extendoffice Dim xDRg1, xDRg2, xDRg3, xDRg4, xDRg5 As Range Dim xRg As Range Dim xStr As String Dim xFN1, xFN2, xFN3, xFN4, xFN5 As Integer Dim xSV1, xSV2, xSV3, xSV4, xSV5 As String Set xDRg1 = Range("A2:A7") 'First column data Set xDRg2 = Range("B2:B7") 'Second column data Set xDRg3 = Range("C2:C7") 'Third column data Set xDRg4 = Range("D2:D7") 'Fourth column data Set xDRg5 = Range("E2:E7") 'Fifth column data xStr = "-" 'Separator Set xRg = Range("H2") '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 For xFN4 = 1 To xDRg4.Count xSV4 = xDRg4.Item(xFN4).Text For xFN5 = 1 To xDRg5.Count xSV5 = xDRg5.Item(xFN5).Text xRg.Value = xSV1 & xStr & xSV2 & xStr & xSV3 & xStr & xSV4 & xStr & xSV5 Set xRg = xRg.Offset(1, 0) Next Next Next Next Next End Sub
      Please try, hope it can help you!
      • To post as a guest, your comment is unpublished.
        Tikikus · 4 months ago
        Hello skyyang, I changed the code for 7 rolls, but seeing Excel has only 1,048,576 rows the VBA code can't give all the combinations.
        Do you have an idea how I can continue on other columns?
         I think this code  -  Set xRg = xRg.Offset(1, 0)
        needed to change 
  • To post as a guest, your comment is unpublished.
    anon · 1 years ago
    I CANNOT THANK YOU ENOUGH! SAVED ME SO MUCH TIME!
  • To post as a guest, your comment is unpublished.
    Tik · 1 years ago
    Thanks a lot for sharing this post. Thanks a lot to @Balaji for his/her formula
  • To post as a guest, your comment is unpublished.
    balaji · 2 years ago
    To generate any random combination we can use
    =INDEX($A$2:$A$4,RANDBETWEEN(1,3))&"-"&INDEX($B$2:$B$6,RANDBETWEEN(1,5))&"-"&INDEX($C$2:$C$5,RANDBETWEEN(1,4))