Note: The other languages of the website are Google-translated. Back to English

Kako našteti ali ustvariti vse možne kombinacije v Excelu?

Recimo, da imam naslednja dva stolpca podatkov, zdaj pa želim ustvariti seznam vseh možnih kombinacij na podlagi obeh seznamov vrednosti, kot je prikazano na levi sliki zaslona. Mogoče lahko naštejete vse kombinacije eno za drugo, če je vrednosti malo, če pa je za naštevanje možnih kombinacij več stolpcev z več vrednostmi, je tukaj nekaj hitrih trikov, ki vam lahko pomagajo pri reševanju te težave v Excelu. .

Navedite ali ustvarite vse možne kombinacije iz dveh seznamov s formulo

Navedite ali ustvarite vse možne kombinacije s treh ali več seznamov s kodo VBA

Seznam ali generiranje vseh možnih kombinacij iz več seznamov z zmogljivo funkcijo


Navedite ali ustvarite vse možne kombinacije iz dveh seznamov s formulo

Naslednja dolga formula vam lahko pomaga hitro našteti vse možne kombinacije vrednosti dveh seznamov. Naredite naslednje:

1. Vnesite ali kopirajte spodnjo formulo v prazno celico, v tem primeru jo vnesem v celico D2 in nato pritisnem Vnesite tipko, da dobite rezultat, glejte sliko zaslona:

=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))

Opombe: V zgornji formuli, 2 A $ 5: XNUMX A $ je obseg vrednosti prvih stolpcev in $ 2 $: $ 4 $ je obseg vrednosti drugega seznama, za katere želite navesti vse njihove možne kombinacije, $ D $ 2 je celica, v katero ste postavili formulo, lahko sklice na celice spremenite glede na vaše potrebe.

2. Nato izberite celico D2 in povlecite ročico za polnjenje navzdol do celic, dokler ne dobite praznih celic, na podlagi vrednosti dveh seznamov pa so bile navedene vse možne kombinacije. Oglejte si posnetek zaslona:


Navedite ali ustvarite vse možne kombinacije s treh ali več seznamov s kodo VBA

Mogoče je zgornjo formulo nekoliko težko uporabiti, če je podatkov o stolpcih več, bo to težavno za spreminjanje. Tukaj bom predstavil kodo VBA za hitro obravnavo.

1. Držite tipko ALT + F11 tipke za odpiranje Microsoft Visual Basic za aplikacije okno.

2. Kliknite Vstavi > Moduliin prilepite naslednjo kodo v Moduli Okno.

Koda VBA: ustvari vse kombinacije 3 ali več stolpcev

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
Opombe: V zgornji kodi, A2: A5, B2: B4, C2: C4 so obseg podatkov, ki ga želite uporabiti, E2 je izhodna celica, v kateri želite poiskati rezultate. Če želite dobiti vse kombinacije več stolpcev, spremenite in dodajte druge parametre v kodo, kot želite.

3. Nato pritisnite F5 za zagon te kode in vse kombinacije treh stolpcev bodo ustvarjene hkrati, glejte posnetek zaslona:


Seznam ali generiranje vseh možnih kombinacij iz več seznamov z zmogljivo funkcijo

Če je na seznamih več vrednosti, je treba navesti možne kombinacije, morda boste težko spremenili kodo. Tu lahko priporočim močno orodje - Kutools za Excel, vsebuje priročno funkcijo Seznam vseh kombinacij ki lahko na podlagi danih seznamov podatkov hitro našteje vse možne kombinacije.

nasveti:Če želite uporabiti to Seznam vseh kombinacij funkcijo, najprej morate prenesti Kutools za Excelin nato funkcijo hitro in enostavno uporabite.

Po namestitvi Kutools za Excel, naredite tako:

1. klik Kutools > Vstavi > Seznam vseh kombinacij, glej posnetek zaslona:

2. v Seznam vseh kombinacij v pogovornem oknu izvedite postopke, kot je prikazano spodaj:

3. Nato so bile vse navedene vrednosti in ločila navedena v pogovornem oknu, glejte posnetek zaslona:

4. In nato kliknite Ok in izpiše se pozivno polje, ki vas opomni, da izberete celico za izpis rezultata, glejte posnetek zaslona:

5. Kliknite OK, so vse možne kombinacije na podlagi danih seznamov ustvarjene v delovnem listu, kot je prikazano na sliki spodaj:

Kliknite za prenos Kutools za Excel zdaj!


Več relativnih člankov:

  • Ustvari vse kombinacije 3 ali več stolpcev
  • Recimo, da imam 3 stolpce podatkov, zdaj želim ustvariti ali navesti vse kombinacije podatkov v teh 3 stolpcih, kot je prikazano spodaj. Ali imate kakšne dobre metode za reševanje te naloge v Excelu?
  • Poiščite vse kombinacije, ki so enake dani vsoti
  • Na primer, imam naslednji seznam številk, zdaj pa želim vedeti, katera kombinacija številk na seznamu znaša do 480, na naslednjem prikazanem posnetku zaslona lahko vidite, da obstaja pet skupin možnih kombinacij, ki se seštevajo enako do 480, na primer 300 + 60 + 120, 300 + 60 + 40 + 80 itd. V tem članku bom govoril o nekaterih metodah iskanja, katere celice seštejejo do določene vrednosti v Excelu.
  • Ustvari ali naštej vse možne permutacije
  • Na primer, imam tri znake XYZ, zdaj želim našteti vse možne permutacije, ki temeljijo na teh treh znakih, da dobim šest različnih rezultatov: XYZ, XZY, YXZ, YZX, ZXY in ZYX. Kako lahko v Excelu hitro ustvarite ali navedete vse permutacije glede na različno število znakov?
  • Ustvari seznam vseh mogočih 4-mestnih kombinacij
  • V nekaterih primerih bomo morda morali ustvariti seznam vseh možnih 4-mestnih kombinacij števil od 0 do 9, kar pomeni, da bomo ustvarili seznam 0000, 0001, 0002… 9999. Za hitro reševanje naloge s seznamom v Excelu vam predstavljam nekaj trikov.

 


Najboljša orodja za pisarniško produktivnost

Kutools za Excel rešuje večino vaših težav in poveča vašo produktivnost za 80%

  • Ponovna uporaba: Hitro vstavite zapletene formule, grafikoni in vse, kar ste že uporabljali; Šifriraj celice z geslom; Ustvari poštni seznam in pošiljanje e-pošte ...
  • Vrstica Super Formula (enostavno urejanje več vrstic besedila in formule); Bralna postavitev (enostavno branje in urejanje velikega števila celic); Prilepite v filtrirani obseg...
  • Združi celice / vrstice / stolpce brez izgube podatkov; Vsebina razdeljenih celic; Združi podvojene vrstice / stolpce... prepreči podvojene celice; Primerjaj obsege...
  • Izberite Duplicate ali Unique Vrstice; Izberite prazne vrstice (vse celice so prazne); Super Find in Fuzzy Find v mnogih delovnih zvezkih; Naključna izbira ...
  • Natančna kopija Več celic brez spreminjanja sklica formule; Samodejno ustvarjanje referenc na več listov; Vstavi oznake, Potrditvena polja in še več ...
  • Izvleček besedila, Dodaj besedilo, Odstrani po položaju, Odstrani presledek; Ustvari in natisni vmesne seštevke strani Pretvarjanje med vsebino celic in komentarji...
  • Super filter (shranite in uporabite sheme filtrov za druge liste); Napredno razvrščanje glede na mesec / teden / dan, pogostost in drugo; Poseben filter s krepko, ležeče ...
  • Združite delovne zvezke in delovne liste; Spoji tabele na podlagi ključnih stolpcev; Razdelite podatke na več listov; Paketna pretvorba xls, xlsx in PDF...
  • Več kot 300 zmogljivih funkcij. Podpira Office / Excel 2007-2021 in 365. Podpira vse jezike. Enostavna uvedba v vašem podjetju ali organizaciji. 30-dnevna brezplačna preizkusna različica vseh funkcij. 60-dnevna garancija vračila denarja.
zavihek kte 201905

Kartica Office prinaša vmesnik z zavihki v Office in poenostavi vaše delo

  • Omogočite urejanje in branje z zavihki v Wordu, Excelu, PowerPointu, Publisher, Access, Visio in Project.
  • Odprite in ustvarite več dokumentov v novih zavihkih istega okna in ne v novih oknih.
  • Poveča vašo produktivnost za 50%in vsak dan zmanjša na stotine klikov miške za vas!
dno pisarniške mize

 

Komentarji (42)
Ocenjeno 5 iz 5 · 1 ocene
Ta komentar je moderator na spletnem mestu minimiziral
To je briljantna formula! Nimam pojma, kako deluje, ampak samo spremenim stolpca A in B, da se ujemata s tem, kako dolgi so moji seznami, in imam svoj izhod v D1.
Ta komentar je moderator na spletnem mestu minimiziral
primer: moj seznam vrednosti je 1,2,3,4,5,6......80, želim, da kutool prikaže vse kombinacije nabora dveh številk, primer: 2-1, 2-1, 3-1, 4-1, ..................5-79. ali je to možno s KUTOOL-om?
Ta komentar je moderator na spletnem mestu minimiziral
Ali obstaja način, da ta formula deluje tako, da ustvari različne kombinacije, ki so še vedno ločene v 2 ločena stolpca, vendar mejijo drug na drugega?
Ta komentar je moderator na spletnem mestu minimiziral
Ste imeli kaj sreče, ko ste ugotovili, kako to storiti? Poskušam narediti popolnoma isto stvar in imeti vse svoje verjetnosti v 2 ločenih stolpcih v excelu.
Ta komentar je moderator na spletnem mestu minimiziral
Ste imeli s tem že kaj sreče? Poskušam narediti isto stvar in imeti vse možne kombinacije druga poleg druge v 2 ločenih stolpcih.
Ta komentar je moderator na spletnem mestu minimiziral
Ali lahko dobim formulo za vse kombinacije matrike 5*5 (5 vrstic in 5 stolpcev) Poskusil sem, vendar nisem mogel dobiti, prosim, pomagajte mi ......
Ta komentar je moderator na spletnem mestu minimiziral
V ZELO KORABNO. Zdaj lahko zelo enostavno ustvarjam kombinacije.
Ta komentar je moderator na spletnem mestu minimiziral
Kako bi lahko uporabili KuTools ali celo formulo, če bi želeli vse permutacije v glavi meseca. Jan, Jan in Feb, Jan in Mar, Jan in Mar, Jan in Feb in Mar itd
Ta komentar je moderator na spletnem mestu minimiziral
kdo ve, kako to spremeniti tako, da bo odražalo 6 stolpcev podatkov in ne le dva?
Ta komentar je moderator na spletnem mestu minimiziral
iščem isto stvar. kdo ve?
Ta komentar je moderator na spletnem mestu minimiziral
kdo ve, kako to spremeniti, da odraža 6 stolpcev podatkov?
Ta komentar je moderator na spletnem mestu minimiziral
Za več stolpcev:
Prvi del formule je treba spremeniti, da se pomnožijo vse možnosti, na primer to bi bilo za 6 stolpcev
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)
torej dodajaš
*COUNTA (vaš obseg tukaj)
za vsak stolpec
Drugi del formule je treba spremeniti tudi za vsak stolpec takole:
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)
torej dodajaš
&INDEX(vaš obseg tukaj,MOD(VRSTIC()-ROW($I$2),COUNTA(vaš obseg tukaj))+1)

Torej jih združite in dobite ta primer za 6 stolpcev:

=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))

Kjer bi to formulo prilepili v $I$2 in bi gledala $A$2:$A$9 $B$2:$B$9 $C$2:$C$9 ... do $F$2:$F$9
Ta komentar je moderator na spletnem mestu minimiziral
to ne deluje :( bolj zapleteno od tega
Ta komentar je moderator na spletnem mestu minimiziral
Za več stolpcev:
Prvi del formule je treba spremeniti, da se pomnožijo vse možnosti, na primer to bi bilo za 6 stolpcev:

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)
torej dodajaš
*COUNTA (vaš obseg tukaj)
za vsak stolpec

Drugi del formule je treba spremeniti tudi za vsak stolpec takole:

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)

torej dodajaš

&INDEX(vaš obseg tukaj,MOD(VRSTIC()-ROW($I$2),COUNTA(vaš obseg tukaj))+1)

Torej jih združite in dobite ta primer za 6 stolpcev:

=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))

Kjer bi to formulo prilepili v $I$2 in bi gledala $A$2:$A$9 $B$2:$B$9 $C$2:$C$9 ... do $F$2:$F$9
Ta komentar je moderator na spletnem mestu minimiziral
to ne deluje :( bolj zapleteno od tega
Ta komentar je moderator na spletnem mestu minimiziral
Imate kakšno idejo, kako dodati presledek vmes?
Ta komentar je moderator na spletnem mestu minimiziral
Pozdravljeni, Eoin,
formula v tem članku vam ne more pomagati pri dodajanju presledka med vrednostmi celice, vendar lahko uporabite Kutools za Excel, z njo lahko vnesete poljubno ločilo, saj želite ločiti kombinirani rezultat, glejte posnetek zaslona:
Ta komentar je moderator na spletnem mestu minimiziral
Presledek lahko dodate tako, da dodate " "& pred indeksiranjem stolpca B, takoj za obstoječim &, kot je tako.....

=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))


....ali kateri koli drug ločilo, ki vam je všeč!
Ta komentar je moderator na spletnem mestu minimiziral
Ali lahko KuTools razvrsti celice v vrsti na način "preproste kombinacije"?
Mislim, če imam te podatke:
_________________________
John Jack Paul Macy

Mark Larry

Jerry Paul Mary

Sam Jeff Peter Lucas
_________________________



In želim jih prikazati takole:
___________
John Jack

Janeza Pavla

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
____________


Kako lahko to storim? Ali lahko KuTools naredi kaj takega?

Hvala!
Ta komentar je moderator na spletnem mestu minimiziral
Živjo, Arthur,
Žal vam Kutools ne more pomagati rešiti tega dela, kot ste rekli.
Hvala za vaš komentar.
Ta komentar je moderator na spletnem mestu minimiziral
Ali je že kdo ugotovil formulo za to za 5 nizov podatkov? Zaenkrat sem bil ob tem obupan.
Ta komentar je moderator na spletnem mestu minimiziral
Pozdravljeni, Marco,
Kutools vam lahko pomaga hitro rešiti težavo, preverite naslednji posnetek zaslona:
Prosim, poskusite, upam, da vam bo pomagalo, hvala!
Ta komentar je moderator na spletnem mestu minimiziral
Hi,
Zanima me, kaj naj storim, če želim ustvariti več kot 1,4 milijona kombinacij in presežem omejitev vrstic v Excelu?
Ali obstaja način za uvrstitev pohvale za nadaljevanje njegove ponovitve v naslednjem stolpcu?
Ta komentar je moderator na spletnem mestu minimiziral
Zakaj moja formula ni delovala pri ustvarjanju 25 kombinacij v stolpcu E (5 elementov iz stolpca A * 5 elementov iz stolpca B)? Medtem ko je formula v tem članku delovala v stolpcu D, ko sem jo potegnil za 25 celic? Posnetek zaslona - https://prnt.sc/ihwr18
Ta komentar je moderator na spletnem mestu minimiziral
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))
Ta komentar je moderator na spletnem mestu minimiziral
Pozdravljeni, in če so te kombinacije namesto besedila številka, kako lahko naredim izračun za rezultate???? poskušam dodati "+", vendar excel ne vem, da je to simbol.. kako to rešiš?
Ta komentar je moderator na spletnem mestu minimiziral
Uporabil sem razširitve in deluje fantastično, zdaj pa imam težavo, moram izračunati vsoto, vendar je rezultat besedilo,, vnesem ločilo, ki je simbol +, končno pa imam besedilo in je nemogoče pretvoriti v operaciji.
Ta komentar je moderator na spletnem mestu minimiziral
Skoraj zelo priročno. Dobro bi bilo, če bi dejansko uporabil vse, kar je v celici ($A$1), in ne bi pretvoril celice v besedilo. Potem bi lahko samo prilepil, kar potrebujem, v A1 in ga znova zagnal, ne da bi bilo treba karkoli spremeniti.
Ta komentar je moderator na spletnem mestu minimiziral
Ali obstaja način, da to izpišem kot datoteko txt? Ko imate v stolpcu D na milijone rezultatov, ni ravno praktično, da vlečete ročico za polnjenje.
Tu še ni objavljenih komentarjev
Obremenitev Več
Pustite vaše komentarje
Objava kot gost
×
Ocenite to objavo:
0   Znaki
Predlagane lokacije

Sledi nam

Copyright © 2009 - www.extendoffice.com. | Vse pravice pridržane. Poganja ga ExtendOffice. | Kazalo
Microsoft in logotip Office sta blagovni znamki ali registrirani blagovni znamki družbe Microsoft Corporation v ZDA in / ali drugih državah.
Zaščiteno s Sectigo SSL