如何用excel列出如下所有排列组合的情形？ EXCEL 利用排列组合公式列出所有排列组合情况
excel\u600e\u4e48\u5217\u51fa\u6240\u6709\u7684\u6392\u5217\u7ec4\u5408\uff1f\u5982\u679c\u5168\u90fd\u662f4\u90093\uff0c\u4e14\u6bcf\u96944\u4e2a\u6709\u4e00\u4e2a\u7a7a\u5355\u5143\u683c\u7684\u8bdd\uff0c\u53ef\u7528\u4e0b\u5217\u65b9\u6cd5\uff1a
1\u3001\u9009\u4e2dA\u5217\uff0c\u70b9\u51fb\u3010\u6570\u636e\u3011\u9009\u9879\u5361\u4e0b\u7684\u3010\u5206\u5217\u3011\uff0c\u4e00\u76f4\u4e0b\u4e00\u6b65\u5230\u6700\u540e\uff0c\u6570\u636e\u683c\u5f0f\u9009\u62e9\u6587\u672c\uff0c\u7136\u540e\u70b9\u51fb\u5b8c\u6210\u3002
2\u3001\u5728B1\u8f93\u5165\uff1a=IF(A1="","",REPLACE(PHONETIC(INDIRECT("a"&(MIN(IF(A1:A5="",ROW(A1:A5),9999))-4)&":"&"a"&(MIN(IF(A1:A5="",ROW(A1:A5),9999))-1))),MIN(IF(A1:A5="",ROW(A1:A5),9999))-ROW(A1),1,""))\u7136\u540e\u540c\u65f6\u6309ctrl shift enter\u7ed3\u675f\u3002
3\u3001\u9009\u4e2dB1\uff0c\u9f20\u6807\u79fb\u5230B1\u53f3\u4e0b\u89d2\uff0c\u6309\u4f4f\u5e76\u5f80\u4e0b\u6258\u62c9\u5373\u53ef\u3002
\u6ce8\uff1a\u5982\u679c\u662f5\u90094\u7b49\u7c7b\u578b\uff0c\u53ef\u5c06\u516c\u5f0f\u4e2d\u7684A1:A5\u6539\u6210A1:A6\uff0c-4\u6539\u6210-5\u5373\u53ef\u3002\u540c\u7406\uff0c\u8fd8\u53ef\u6539\u62106\u90095,7\u90096\u7b49\u3002\u4ee5\u6b64\u7c7b\u63a8\u3002
\u56fe\u4e2d\u7684\u516c\u5f0f\u53ea\u9002\u7528\u4e8e\u6570\u636e\u7684\u6c42\u548c\uff0c\u697c\u4e3bC=5858ANP\u53ea\u662f\u9879\u76ee\u7684\u5408\u5e76\u3002\u697c\u4e3b\u9700\u8981\u7684\u6570\u6392\u5217\u7ec4\u5408\uff0c\u9700\u8981\u8f93\u5165\u6392\u5217\u7ec4\u5408\u516c\u5f0fC1=A1&B1\u5c31\u53ef\u4ee5\u4e86\u3002
1、首先在电脑中打开excel软件，输入要组合的数据。
2、在新的一列中输入公式：=INDEX($E$1:$E$3,INT((ROW(E1)-1)/4)+1)，如图所示，其中4表示甲乙丙丁四个的数量。
3、然后回车，往下拖拉至ABC数量乘以甲乙丙丁的数量，这里为3X4=12。
4、同样，在新的一列输入公式：=INDEX($F$1:$F$4,MOD(ROW(E1)-1,4)+1)，同样的操作。
5、最后将两者的数据合在一起，可以直接将两个单元格用&（与命令）加载一起，也可以直接将两个公式用&合在一起，就完成了。
这个排列组合经常要用到，托您的问题，我学习了一下，做了一个排列组合的代码。只当抛砖引玉，希望有高手批评指正。（这个程序是将10个数字，按每条4个数字进行组合）
Sub 组合()
Dim arr_S(1 To 10)
Dim arr_O
Dim I, J, K1, K2, K3, K4 As Integer
I = Sheet1.Range("A65536").End(xlUp).Row
'arr_S = Sheets("sheet1").Range("a2:a" & I) 这个运行时出错
For J = 1 To I - 1 '给原始序列数组赋值
arr_S(J) = Cells(J + 1, 1)
Next J
I = I - 1 '原始数据的个数
J = I * (I - 1) * (I - 2) * (I - 3) / (1 * 2 * 3 * 4) '按4列组合，计算目标数组的个数
ReDim arr_O(1 To J, 1 To 4)
J = 1
For K1 = 1 To I - 3
For K2 = K1 + 1 To I - 2
For K3 = K2 + 1 To I - 1
For K4 = K3 + 1 To I
arr_O(J, 1) = arr_S(K1)
arr_O(J, 2) = arr_S(K2)
arr_O(J, 3) = arr_S(K3)
arr_O(J, 4) = arr_S(K4)
J = J + 1
Next K4
Next K3
Next K2
Next K1
For I = 1 To J - 1
Cells(I + 1, 3) = arr_O(I, 1)
Cells(I + 1, 4) = arr_O(I, 2)
Cells(I + 1, 5) = arr_O(I, 3)
Cells(I + 1, 6) = arr_O(I, 4)
Next I
End Sub
执行结果如图所示：
'你这个用excel函数比较麻烦，但用VBA代码就比较简单。
'如果你的四个环节名分别放在A1：D1，第一环节的四种情况分别在A2：A4，……，则在excel VBA里运行下面的代码就可以在A6:P21区域中得出你要的256中组合
Sub 四环节组合()
Dim i%, j%, k%, l%, m%, n%
For i = 2 To 5 Step 1
For j = 2 To 5 Step 1
m = m + 1
n = 0
For k = 2 To 5 Step 1
For l = 2 To 5 Step 1
n = n + 1
Cells(m + 5, n) = Cells(i, 1) & Cells(j, 2) & Cells(k, 3) & Cells(l, 4)
Next
Next
Next
Next
End Sub
使用 Excel常用在线工具 的批量组合功能，操作如下
如果组合为一列则选择 组合成一列。 excelfb
Sub musub()
Dim caseArray(3) As String
caseArray(0) = "A"
caseArray(1) = "B"
caseArray(2) = "C"
caseArray(3) = "D"
Dim rowNum As Integer
For i = 1 To 4
For j = 1 To 4
For k = 1 To 4
For l = 1 To 4
rowNum = rowNum + 1
Cells(rowNum, 1) = "情形" & CStr(rowNum)
Cells(rowNum, 2) = caseArray(i - 1)
Cells(rowNum, 3) = caseArray(j - 1)
Cells(rowNum, 4) = caseArray(k - 1)
Cells(rowNum, 5) = caseArray(l - 1)
Next l
Next k
Next j
Next i
End Sub
绛旓細鐜板湪鍙槸鍒楀嚭浜70绉嶏紝杩樻湁澶氬皯绉嶆垜涔熶笉鐭ラ亾搴旇鎬庝箞绠楋紝搴旇鏄5鐨9娆℃柟鍔1绉嶆儏鍐靛惂銆傚洜涓鸿繕鏈夊緱1鍒嗙殑涓2娆3鍒嗙殑涓1娆★紝1鍒嗙2娆4鍒嗕腑1娆°傘傘傚鏋滄槸5鐨9娆℃柟锛岄偅璁＄畻缁撴灉鏄1953125绉嶇粍鍚堬紝鍐嶅姞涓婁竴涓0锛屼篃灏辨槸璇鎵鏈鐨勭粍鍚堜竴鍏辨槸1953126绉嶃備笉鐭ラ亾鎴戣鐨勫涓嶅銆傘傘傚簲璇ヨ繖鏍疯绠楁墠鏄纭...
绛旓細Sub 鎺掑垪()Dim k For k = 1 To 5 For r = 1 To 5 x = Range("f65536").End(xlUp).Row Cells(x + 1, "F") = Cells(1, k) & Cells(1, r)Next Next 鎺掑垪2 End Sub Sub 鎺掑垪2()Dim k For k = 1 To 5 For r = 1 To 5 x = Range("f65536").End(xlUp).Row C...
绛旓細=index($a$1:$a$11,mod(row()-1,11)+1)&index($b$1:$b$9,quotient(row()-1,11)+1)杩欎釜鍏紡涓嬫媺鍒99琛岋紝鍙互鎶鎵鏈缁勫悎閮鍒楀嚭鏉
绛旓細excel鍒╃敤rank鎺掑悕娆″叕寮忕殑鏂规硶濡備笅锛氬伐鍏凤細鎴村皵K550銆乄in11銆乪xcel2022鐗堟湰銆1銆侀鍏堝湪琛ㄦ牸涓鍒楀嚭濮撳悕鍜屽悇绉戠殑鎴愮哗锛屽姞涓涓诲垎鍒椾娇鐢╯um鍑芥暟杩涜姹傚拰鎿嶄綔銆2銆佺劧鍚庣偣鍑诲埌H2鍗曞厓鏍煎苟鐐瑰嚮鈥渇x鈥濇彃鍏ュ嚱鏁帮紝閫夋嫨鈥渞ank鈥濆嚱鏁般3銆佸湪鍑芥暟鍙傛暟璁剧疆涓牴鎹笅鍥剧殑鍙傛暟杩涜璁剧疆锛岄渶瑕佹坊鍔犫$鈥濈粷瀵瑰紩鐢ㄧ鍙枫4銆佺偣鍑...
绛旓細1銆侀鍏堟墦寮excel琛ㄦ牸锛濡備笅鍥撅紝宸﹁竟鏄煇骞寸骇瀛︾敓淇℃伅琛紝鐜板湪鎯宠鎸夌彮绾ф彁鍙栫浉搴旂殑瀛︾敓濮撳悕銆2銆佸厛鍦ㄧ彮绾у墠鎻掑叆涓鍒楄緟鍔╁垪銆3銆佸湪鏂版彃鍏ョ殑杈呭姪鍒楄緭鍏ヤ腑鍏紡=B2&COUNTIF($B$1:B2,B2)锛屽苟涓嬫媺鍒板簳銆4銆佸湪E2鍗曞厓鏍艰緭鍏ュ叕寮=IFERROR(VLOOKUP(E$1&ROW(A1),$A:$C,3,0),"")锛屽苟涓嬫媺鐩磋嚦鍑虹幇绌...
绛旓細3銆乺ank鍑芥暟鎺掑悕鍙互鍦Excel浣跨敤銆俽ank鍑芥暟涓昏鐢ㄤ簬鎺掑簭锛屽彲浠ヨ繑鍥炰竴鍒楁暟瀛楃殑鏁板瓧鎺掍綅锛屽叾鏁板瓧鐨勬帓浣嶆槸鐩稿浜庡垪琛ㄤ腑鍏朵粬鍊肩殑澶у皬锛涜娉曟牸寮忎负鈥淩ANK(number锛宺ef锛孾order])鈥濄傜畝鍗曞仛涓紨绀猴紝閭ｄ箞杩欓噷灏辨槸=RANK(E15锛孍15锛欵22)銆4銆佹墦寮excel琛ㄦ牸锛岃緭鍏ヤ竴浜涙暟瀛椾綔涓轰緥瀛愩傛帴鐫杈撳叆鍏紡鈥=RANK(A2锛$A$...
绛旓細d = 1 For a = 0 To 9 For b = 0 To 9 For c = 0 To 9 If a <> b And b <> c And a <> c Then Cells(d, 1).NumberFormatLocal = "@"Cells(d, 1) = Trim(Str(a)) & b & c d = d + 1 End If Next c Next b Next a End Sub 1銆佹墦寮EXCEL 2銆丄LT+F11...
绛旓細浣跨敤 RANK.EQ 鍑芥暟鎺掑簭锛屽湪 C2 鍗曞厓鏍间互涓嬪叕寮忥紝鍐嶅鍒跺埌浣欎笅鍗曞厓鏍笺=RANK.EQ(B2,$B$2:$B$9,0)
绛旓細鎬庝箞鏄1鍒11浣嶆暟锛岃繕鑳8浣嶄负1缁勶紵鏄8涓负涓缁勶紵鍐欏叕寮 =RANDBETWEEN(1,11)&"锛"&RANDBETWEEN(1,11)&"锛"&RANDBETWEEN(1,11)&"锛"&RANDBETWEEN(1,11)&"锛"&RANDBETWEEN(1,11)&"锛"&RANDBETWEEN(1,11)&"锛"&RANDBETWEEN(1,11)&"锛"&RANDBETWEEN(1,11)鐒跺悗涓嬫媺澶嶅埗 ...
绛旓細鍛靛懙锛岀涓涓柟妗嗛噷杈撳叆1锛岀劧鍚庢妸榧犳爣鏀惧湪鏂规鐨勫彸涓嬭锛岀瓑浠栧彉鎴愰粦鑹插疄蹇冪殑鍗佸瓧鏃讹紝鍚戜笅鎷栧姩榧犳爣锛岃繖涓椂鍊欙紝浣犳嫋鍔ㄥ埌绗120涓柟妗嗙殑鏃跺欙紝灏变細鍙戠幇锛屼粠1鍒120宸茬粡鍐欎笂浜嗐傜浣犲ソ杩
