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
1\u3001\u7ec4\u5408\uff1a\u8ba1\u7b97\u4ece\u7ed9\u5b9a\u6570\u76ee\u7684\u5bf9\u8c61\u96c6\u5408\u4e2d\u63d0\u53d6\u82e5\u5e72\u5bf9\u8c61\u7684\u7ec4\u5408\u6570\u3002\u5229\u7528\u51fd\u6570 COMBIN \u53ef\u4ee5\u786e\u5b9a\u4e00\u7ec4\u5bf9\u8c61\u6240\u6709\u53ef\u80fd\u7684\u7ec4\u5408\u6570\u3002
\u8bed\u6cd5\uff1aCOMBIN(number,number_chosen)
Number \u9879\u76ee\u7684\u6570\u91cf\u3002
Number_chosen \u6bcf\u4e00\u7ec4\u5408\u4e2d\u9879\u76ee\u7684\u6570\u91cf\u3002
\u6bd4\u5982\uff1a\u4ece9\u4e2a\u6570\u5b57\u7403\u4e2d\uff0c\u968f\u673a\u62bd3\u4e2a\u7403\uff0c\u5171\u6709\u591a\u5c11\u79cd\u7ec4\u5408\uff0c\u5728excel\u4efb\u4e00\u5355\u5143\u683c\u8f93\u5165\u201c=COMBIN(9,3)\u201d\uff0c\u56de\u8f66\u3002\u7ed3\u679c\u5c31\u4f1a\u5f97\u5230\u300284\u79cd
2\u3001\u6392\u5217\uff1a\u8fd4\u56de\u4ece\u7ed9\u5b9a\u6570\u76ee\u7684\u5bf9\u8c61\u96c6\u5408\u4e2d\u9009\u53d6\u7684\u82e5\u5e72\u5bf9\u8c61\u7684\u6392\u5217\u6570\u3002\u6392\u5217\u4e3a\u6709\u5185\u90e8\u987a\u5e8f\u7684\u5bf9\u8c61\u6216\u4e8b\u4ef6\u7684\u4efb\u610f\u96c6\u5408\u6216\u5b50\u96c6\u3002\u6392\u5217\u4e0e\u7ec4\u5408\u4e0d\u540c\uff0c\u7ec4\u5408\u7684\u5185\u90e8\u987a\u5e8f\u65e0\u610f\u4e49\u3002\u6b64\u51fd\u6570\u53ef\u7528\u4e8e\u5f69\u7968\u62bd\u5956\u7684\u6982\u7387\u8ba1\u7b97\u3002
\u8bed\u6cd5\uff1aPERMUT(number,number_chosen)
Number \u8868\u793a\u5bf9\u8c61\u4e2a\u6570\u7684\u6574\u6570\u3002
Number_chosen \u8868\u793a\u6bcf\u4e2a\u6392\u5217\u4e2d\u5bf9\u8c61\u4e2a\u6570\u7684\u6574\u6570\u3002
\u6bd4\u5982\uff1a\u4ece9\u4e2a\u540c\u5b66\uff0c\u968f\u673a\u62bd3\u4e2a\u540c\u5b66\uff0c\u6392\u5217\u4e3a\u4e00\u5217\uff0c\u5171\u6709\u591a\u5c11\u4e2a\u6392\u5217\u65b9\u5f0f\uff0c\u5728excel\u4efb\u4e00\u5355\u5143\u683c\u8f93\u5165\u201c=PERMUT(9,3)\u201d\uff0c\u56de\u8f66\u3002\u7ed3\u679c\u5c31\u4f1a\u5f97\u5230\u3002504\u79cd
1、打开"排列组合.XLS"。
2、打开"Microsoft Query"。
3、选择数据源:EXCEL FILES*,不要勾选"使用查询向导" 确定。
4、打开存放在电脑E:盘上的"排列组合.xls"文件,作为外部数据源。
5、添加"职员$"工作表到Query,并关闭。
6、点击Query工具栏中"SQL"按钮。
7、填写如下SQL语句:
select *
from [职员$] A,[职员$] B,[职员$] C
where A.职员<B.职员 and B.职员<C.职员
order by A.职员,B.职员,C.职员
点击确定。
8、稍微等待一下,系统会把满足条件所用排列组合显示出来。
9、最后选择Query"文件"菜单下"将数据返回到EXCEL"选项。
10、弹出"导入数据"对话框,鼠标单击D1单元格,确认数据导入在D1单元格,确定,当然也可以把结果放在新工作表中。
11、因为全排列的数据量很大,有969种排列组合,把EXCEL表进行了窗口冻结操作。
12、后续对结果进行处理,打印出抽签条即可。
何必用宏呢?用随机函数,你按一下F9就会自动生成新一列数
D,E
,F中分别输入
=OFFSET(A1,INT(RAND()*6)-1,0)
=OFFSET(B1,INT(RAND()*8)-1,0)
=OFFSET(C1,INT(RAND()*6)-1,0)
刚测试了下,不知道怎么用RAND的时候总是偶尔会出点问题,修改了下
=OFFSET(A1,INT(RANDBETWEEN(1,5))-1,0)
=OFFSET(B1,INT(RANDBETWEEN(1,7))-1,0)
=OFFSET(C1,INT(RANDBETWEEN(1,5))-1,0)
要用VBA才行，公式做不到
新建模块
Sub arrange()
Dim a, b, c, d, e As Integer
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) = a & b & c
d = d + 1
End If
Next c
Next b
Next a
End Sub
Sub assemble()
Dim a, b, c, d, e As Integer
d = 1
For a = 0 To 9
For b = 0 To 9
If b > a Then
For c = 0 To 9
If c > b Then
Cells(d, 2) = a & b & c
d = d + 1
End If
Next c
End If
Next b
Next a
End Sub
用VBA：
Sub zuhe()
r = 1
For a = 0 To 7
For b = a + 1 To 8
For c = b + 1 To 9
Cells(r, 1) = "'" & a & b & c
r = r + 1
Next
Next
Next
End Sub
Sub pailie()
r = 1
For a = 0 To 9
For b = 0 To 9
For c = 0 To 9
If a <> b And a <> c And b <> c Then
Cells(r, 2) = "'" & a & b & c
r = r + 1
End If
Next
Next
Next
End Sub
绛旓細`number_chosen` 鏄閫夊彇鐨勫厓绱犳暟閲忋5. **RANDBETWEEN(bottom, top)**: 杩斿洖浣嶄簬鎸囧畾鑼冨洿鍐呯殑闅忔満鏁存暟銆備綘鍙互缁撳悎杩欎釜鍑芥暟鍜屽叾浠Excel鍔熻兘锛堝鏉′欢鏍煎紡鍖栵級鏉ユā鎷熼殢鏈鎺掑垪鎴缁勫悎銆傚湪浣跨敤杩欎簺鍑芥暟鏃讹紝纭繚浣犵殑Excel鐗堟湰鏀寔杩欎簺鍑芥暟锛屽苟涓旀敞鎰忓嚱鏁扮殑鍙傛暟鍜岃娉曪紝浠ョ‘淇濇纭殑璁＄畻缁撴灉銆
绛旓細鍥炵瓟杩囩被浼肩殑闂锛岄涓诲彲浠ュ弬鑰冧竴涓嬨傝繖绉嶉渶姹傚缓璁娇鐢╬ower pivot鏉ュ仛锛岄鍏堣嚜琛屾悳绱ower pivot鍔犲埌鑿滃崟閲岀殑鏂规硶銆備娇鐢╟trl+t鎶婁綘鐨勫悇鍒楀悇鑷彉鎴愯秴绾ц〃锛屾敞鎰忔渶濂芥妸琛ㄦ牸鍒嗗紑锛屼笉鐒跺鏄撴妸涓ゅ垪鏁版嵁鏀惧湪涓璧枫傜劧鍚庡湪榧犳爣瀹氫綅鍦ㄨ〃鏍间笂锛屽湪power pivot閲屾壘鍒 娣诲姞鍒版暟鎹ā鍨嬨傞兘娣诲姞鍒版暟鎹ā鍨嬩互鍚庯紝鍦╬ower ...
绛旓細excel瀵瑰嚑涓瓧姣杩涜鎺掑垪缁勫悎鍦ˋ1:A26杈撳叆a~z锛岀劧鍚庤緭鍏ヤ互涓嬪叕寮忥紝骞跺悜涓嬪～鍏呭叕寮忥細=OFFSET(A$1,INT((ROW(A1)-1)/(26*26*26)),)&OFFSET(A$1,MOD(INT((ROW(A1)-1)/(26*26)),26),)&OFFSET(A$1,MOD(INT((ROW(A1)-1)/26),26),)&OFFSET(A$1,MOD(ROW(A1)-1,26),)璇﹁闄勫浘...
绛旓細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...
绛旓細鐢excel璁＄畻鎺掑垪缁勫悎鐨勪釜鏁扮殑鏂规硶姝ラ濡備笅锛1銆侀鍏堟墦寮璁＄畻鏈猴紝鍦ㄨ绠楁満鍐呮墦寮excel锛岀劧鍚庡湪excel鐣岄潰鍐呴殢鎰忔壘涓涓┖鐧藉崟鍏冩牸骞朵娇鐢ㄩ紶鏍囩偣鍑伙紝鍐嶅湪鐣岄潰鍐呮壘鍒扳渇x鈥濋夐」骞朵娇鐢ㄩ紶鏍囩偣鍑汇2銆佺劧鍚庡湪寮瑰嚭鐨勭晫闈㈠唴鎵惧埌鈥淐OMBIN鈥濋夐」骞朵娇鐢ㄩ紶鏍囩偣鍑汇3銆佹渶鍚庡氨鍙互鍙戠幇鎴愬姛鐨勭敤excel璁＄畻鎺掑垪缁勫悎鐨勪釜鏁颁簡銆
绛旓細excel瀵瑰嚑涓瓧姣杩涜鎺掑垪缁勫悎鍦ˋ1:A26杈撳叆a~z锛岀劧鍚庤緭鍏ヤ互涓嬪叕寮忥紝骞跺悜涓嬪～鍏呭叕寮忥細=OFFSET(A$1,INT((ROW(A1)-1)/(26*26*26)),)&OFFSET(A$1,MOD(INT((ROW(A1)-1)/(26*26)),26),)&OFFSET(A$1,MOD(INT((ROW(A1)-1)/26),26),)&OFFSET(A$1,MOD(ROW(A1)-1,26),)璇﹁闄勫浘...
绛旓細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,...
绛旓細姣斿锛氫粠9涓暟瀛楃悆涓紝闅忔満鎶3涓悆锛屽叡鏈夊灏戠缁勫悎锛屽湪excel浠讳竴鍗曞厓鏍艰緭鍏モ=COMBIN(9,3)鈥濓紝鍥炶溅銆傜粨鏋滃氨浼氬緱鍒般84绉 2銆鎺掑垪锛氳繑鍥炰粠缁欏畾鏁扮洰鐨勫璞￠泦鍚堜腑閫夊彇鐨勮嫢骞插璞＄殑鎺掑垪鏁般傛帓鍒椾负鏈夊唴閮ㄩ『搴忕殑瀵硅薄鎴栦簨浠剁殑浠绘剰闆嗗悎鎴栧瓙闆嗐傛帓鍒椾笌缁勫悎涓嶅悓锛岀粍鍚堢殑鍐呴儴椤哄簭鏃犳剰涔夈傛鍑芥暟鍙敤浜庡僵绁ㄦ娊濂栫殑...
绛旓細涓嶈A鍒楀拰B鍒楁湁澶氬皯涓暟鎹紝鍦–1鍗曞厓鏍艰緭鍏ュ涓嬪叕寮忥細=INDEX(A:A,QUOTIENT(ROW()-1,COUNTA(B:B))+1,1)&INDEX(B:B,MOD(ROW()-1,COUNTA(B:B))+1,1)鍥炶溅纭鍚庯紝鍚戜笅鎷夊叕寮忥紝鎷夊埌缁撴灉鏄庢樉瓒呭嚭涓烘銆傛渶鍚庢妸C鍒楄秴鍑虹殑鍗曞厓鏍煎垹闄ゅ嵆鍙傚鏋淎/B涓ゅ垪缁х画澧炲姞鏁版嵁锛屽彧闇瑕佸皢C鍒楀叕寮忕户缁悜涓嬫媺...
绛旓細鏈夊叧鎺掑垪缁勫悎鍙備笅闈袱涓 Excel鎬庢牱浠庢暟鎹泦閫塺涓暟鎹帓鍒楃粍鍚堝垪琛紙涓锛塃xcel鎬庢牱澶勭悊鎺掑垪缁勫悎鍒楄〃"
