ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 6766|回复: 14

[求助]請問各位大俠,有方法統計1-49數字組合數量?

[复制链接]

TA的精华主题

TA的得分主题

发表于 2006-10-27 00:40 | 显示全部楼层 |阅读模式
4FXGPVyW.rar (47.2 KB, 下载次数: 39)
如附件,請問各位大大,排列組合1-49號碼,任選6個不重複,Combin(49,6)=13983816種排列
方式,請問是否可計算出如附件中1-49,任選6個不重複,最小的合是1 +2 +3 +4+ 5+ 6=21
最大的合是44+45+46+47+48+49=279都只有一種排列,請問如何計算另外其他合22-278
的排列數目,例如合是101,共有58446種排列,超過65536Excel無法列出排列,就不知數目,
請幫幫忙,謝謝

TA的精华主题

TA的得分主题

发表于 2006-10-27 20:44 | 显示全部楼层
递归解决

TA的精华主题

TA的得分主题

发表于 2006-10-27 21:28 | 显示全部楼层

Function counts(ByVal max As Long, ByVal m As Long, ByVal sums As Long) As Long
Dim temp As Long, i As Long, j As Long
If max >= m And m = 1 Then counts = IIf(sums <= max, 1, 0)
If max >= m And m = 2 Then
For i = 1 To max - 1
For j = i + 1 To max
If i + j = sums Then counts = counts + 1
Next
Next
End If
If max >= m And m > 2 Then counts = counts(max - 1, m, sums - m) + counts(max - 1, m - 1, sums - m)
End Function

Sub xxx()
MsgBox counts(49, 6, 101)
End Sub 

Return 58446

TA的精华主题

TA的得分主题

 楼主| 发表于 2006-10-27 22:55 | 显示全部楼层
nHCkuIW2.rar (50.66 KB, 下载次数: 13)
太感謝了,謝謝您的幫忙,謝謝,另請問有比較快的方法,可得出所有合的組合
數量,我只能更改MsgBox counts(49, 6, 101)從23慢慢改,再得出數字
謝謝您的幫忙,謝謝

TA的精华主题

TA的得分主题

发表于 2006-10-28 11:35 | 显示全部楼层

B3==INDEX({1;1;2;3;5;7;11;14;20;26;35;44;58;71;90;110;136;163;199;235;282;331;391;454;532;612;709;811;931;1057;1206;1360;1540;1729;1945;2172;2432;2702;3009;3331;3692;4070;4494;4935;5427;5942;6510;7104;7760;8442;9192;9975;10829;11720;12692;13702;14800;15944;17180;18467;19858;21301;22856;24473;26207;28009;29941;31943;34085;36308;38677;41134;43752;46461;49342;52327;55491;58767;62239;65827;69624;73551;77695;81979;86499;91164;96079;101155;106491;111999;117788;123755;130019;136479;143247;150224;157532;165056;172929;181038;189509;198230;207338;216705;226479;236534;247010;257783;269005;280534;292534;304865;317683;330850;344534;358579;373165;388138;403670;419609;436140;453091;470660;488678;507334;526461;546261;566547;587535;609040},130-ABS(150-A3))

下拖

TA的精华主题

TA的得分主题

发表于 2006-10-28 11:42 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册

1-1000中任取6数,其和为21-1021 的组合数分别为:

1,1,2,3,5,7,11,14,20,26,35,44,58,71,90,110,136,163,199,235,282,331,391,454,532,612,709,811,931,1057,1206,1360,1540,1729,1945,2172,2432,2702,3009,3331,3692,4070,4494,4935,5427,5942,6510,7104,7760,8442,9192,9975,10829,11720,12692,13702,14800,15944,17180,18467,19858,21301,22856,24473,26207,28009,29941,31943,34085,36308,38677,41134,43752,46461,49342,52327,55491,58767,62239,65827,69624,73551,77695,81979,86499,91164,96079,101155,106491,111999,117788,123755,130019,136479,143247,150224,157532,165056,172929,181038,189509,198230,207338,216705,226479,236534,247010,257783,269005,280534,292534,304865,317683,330850,344534,358579,373165,388138,403670,419609,436140,453091,470660,488678,507334,526461,546261,566547,587535,609040,631269,654039,677571,701661,726544,752019,778311,805221,832989,861394,890691,920661,951549,983139,1015691,1048966,1083239,1118274,1154336,1191191,1229120,1267865,1307723,1348439,1390299,1433051,1476997,1521860,1567959,1615020,1663351,1712680,1763332,1815010,1868056,1922176,1977700,2034337,2092435,2151676,2212426,2274370,2337862,2402590,2468926,2536531,2605795,2676382,2748670,2822326,2897747,2974571,3053214,3133318,3215286,3298763,3384171,3471126,3560070,3650622,3743211,3837459,3933815,4031871,4132096,4234086,4338296,4444325,4552649,4662836,4775383,4889861,5006753,5125634,5247008,5370418,5496389,5624468,5755166,5888033,6023602,6161390,6301952,6444809,6590501,6738553,6889527,7042914,7199299,7358177,7520118,7684620,7852276,8022550,8196058,8372268,8551780,8734066,8919750,9108268,9300268,9495190,9693666,9895140,10100268,10308458,10520390,10735476,10954380,11176518,11402579,11631941,11865318,12102093,12342963,12587315,12835871,13087980,13344390,13604454,13868903,14137094,14409784,14686291,14967398,15252428,15542146,15835879,16134419,16437053,16744600,17056351,17373107,17694164,18020350,18350920,18686729,19027037,19372681,19722925,20078634,20439030,20805006,21175789,21552253,21933630,22320822,22713018,23111149,23514409,23923710,24338250,24758970,25185025,25617385,26055210,26499450,26949270,27405650,27867710,28336460,28811025,29292395,29779700,30273960,30774260,31281650,31795220,32316000,32843085,33377536,33918401,34466772,35021703,35584265,36153517,36730561,37314409,37906195,38504936,39111745,39725644,40347778,40977121,41614850,42259945,42913561,43574683,44244499,44921945,45608242,46302331,47005411,47716429,48436617,49164872,49902459,50648281,51403581,52167267,52940616,53722485,54514185,55314579,56124955,56944182,57773582,58611972,59460709,60318616,61187027,62064770,62953214,63851135,64759937,65678402,66607910,67547249,68497835,69458402,70430402,71412575,72406349,73410470,74426402,75452837,76491275,77540414,78601730,79673927,80758518,81854151,82962376,84081848,85214092,86357769,87514441,88682713,89864185,91057468,92264137,93482809,94715097,95959561,97217852,98488537,99773241,101070537,102382089,103706412,105045209,106397001,107763465,109143129,110537709,111945674,113368779,114805500,116257566,117723459,119204948,120700455,122211789,123737379,125279007,126835109,128407507,129994576,131598179,133216698,134851969,136502380,138169808,139852580,141552614,143268244,145001360,146750303,148517005,150299744,152100494,153917540,155752828,157604650,159474994,161362089,163267965,165190858,167132770,169091944,171070425,173066391,175081930,177115228,179168344,181239471,183330711,185440192,187570060,189718450,191887479,194075289,196284041,198511811,200760804,203029104,205318886,207628241,209959389,212310354,214683401,217076561,219492069,221927964,224386526,226865726,229367889,231890994,234437336,237004901,239596030,242208640,244845118,247503389,250185809,252890311,255619297,258370630,261146759,263945555,266769436,269616280,272488552,275384060,278305316,281250136,284221000,287215732,290236860,293282136,296354136,299450620,302574132,305722440,308898136,312098916,315327420,318581352,321863320,325171036,328507157,331869321,335260234,338677543,342123921,345597023,349099571,352629146,356188520,359775282,363392171,367036784,370711910,374415071,378149106,381911546,385705196,389527595,393381599,397264671,401179718,405124211,409101023,413107634,417146968,421216428,425318989,429452063,433618591,437815993,442047262,446309740,450606472,454934809,459297761,463692688,468122652,472584934,477082649,481613087,486179328,490778670,495414246,500083275,504788943,509528478,514305030,519115836,523964100,528846978,533767728,538723515,543717561,548747040,553815228,558919218,564062340,569241696,574460580,579716103,585011614,590344141,595717088,601127493,606578723,612067825,617598221,623166875,628777265,634426364,640117613,645847994,651621004,657433541,663289158,669184763,675123871,681103399,687126919,693191263,699300060,705450151,711645127,717881839,724163935,730488180,736858279,743271007,749730031,756232135,762781044,769373455,776013151,782696839,789428263,796204140,803028272,809897288,816815049,823778194,830790545,837848750,844956690,852110925,859315395,866566670,873868650,881217915,888618425,896066670,903566670,911114925,918715415,926364650,934066670,941817895,949622425,957476690,965384750,973343045,981355696,989419051,997537292,1005706778,1013931650,1022208277,1030540861,1038925679,1047366995,1055861096,1064412205,1073016619,1081678623,1090394421,1099168360,1107996655,1116883611,1125825453,1134826549,1143883030,1152999327,1162171581,1171404181,1180693279,1190043327,1199450382,1208918959,1218445126,1228033356,1237679727,1247388776,1257156485,1266987455,1276877679,1286831715,1296845567,1306923857,1317062492,1327266159,1337530776,1347860987,1358252720,1368710684,1379230710,1389817572,1400467112,1411184060,1421964269,1432812535,1443724612,1454705362,1465750550,1476864994,1488044470,1499293862,1510608847,1521994375,1533446134,1544969030,1556558762,1568220303,1579949251,1591750646,1603620098,1615562602,1627573779,1639658691,1651812858,1664041410,1676339878,1688713347,1701157359,1713677067,1726267911,1738935112,1751674122,1764490116,1777378557,1790344689,1803383872,1816501419,1829692701,1842962985,1856307654,1869732044,1883231434,1896811229,1910466720,1924203266,1938016169,1951910858,1965882530,1979936684,1994068529,2008283517,2022576869,2036954107,2051410346,2065951179,2080571733,2095277554,2110063780,2124936028,2139889330,2154929374,2170051204,2185260460,2200552198,2215932130,2231395204,2246947204,2262583090,2278308598,2294118700,2310019204,2326004974,2342081890,2358244828,2374499620,2390841154,2407275335,2423796941,2440411950,2457115153,2473912479,2490798731,2507779911,2524850712,2542017210,2559274110,2576627439,2594071914,2611613636,2629247211,2646978814,2664803064,2682726086,2700742511,2718858539,2737068689,2755379236,2773784711,2792291339,2810893664,2829597986,2848398736,2867302289,2886303089,2905407461,2924609861,2943916690,2963322290,2982833138,3002443589,3022160069,3041976946,3061900722,3081925650,3102058309,3122292965,3142636146,3163082130,3183637522,3204296485,3225065701,3245939346,3266924050,3288014002,3309215910,3330523846,3351944596,3373472245,3395113527,3416862540,3438726096,3460698176,3482785670,3504982572,3527295720,3549719121,3572259692,3594911321,3617681004,3640562643,3663563181,3686676533,3709909721,3733256541,3756724095,3780306192,3804009881,3827828984,3851770630,3875828521,3900009866,3924308381,3948731221,3973272115,3997938299,4022723381,4047634678,4072665811,4097824043,4123103009,4148510053,4174038688,4199696339,4225476533,4251386641,4277420203,4303584672,4329873465,4356294117,4382840059,4409518771,4436323698,4463262402,4490328204,4517528749,4544857372,4572321663,4599914970,4627644966,4655504875,4683502453,4711630938,4739898030,4768296981,4796835575,4825506938,4854318938,4883264715,4912352081,4941574190,4970938938,5000439353,5030083415,5059864166,5089789530,5119852563,5150061274,5180408591,5210902608,5241536268,5272317608,5303239585,5334310321,5365522645,5396884765,5428389524,5460045073,5491844269,5523795349,5555891041,5588139668,5620533973,5653082221,5685777169,5718627169,5751624848,5784778645,5818081201,5851540897,5885150389,5918918145,5952836690,5986914579,6021144352,6055534506,6090077595,6124782204,6159640755,6194661921,6229838139,6265178023,6300674025,6336334847,6372152808,6408136699,6444278854,6480588005,6517056500,6553693160,6590490200,6627456530,6664584380,6701882600,6739343435,6776975825,6814771880,6852740630,6890874200,6929181560,6967654850,7006303130,7045118405,7084109825,7123269410,7162606250,7202112380,7241796981,7281651951,7321686562,7361892728,7402279660,7442839287,7483580911,7524496324,7565594920,7606868506,7648326415,7689960469,7731780093,7773776971,7815960620,7858322740,7900872786,7943602473,7986521349,8029620990,8072911037,8116383081,8160046701,8203893504,8247933162,8292157142,8336575209,8381178846,8425977756,8470963437,8516145686,8561515860,8607083850,8652841029,8698797225,8744943827,8791290757,8837829262,8884569359,8931502311,8978638072,9025967920,9073501904,9121231160,9169165832,9217297072,9265634960,9314170664,9362914360,9411857072,9461009072,9510361400,9559924264,9609688720,9659665072,9709844232,9760236600,9810833104,9861644080,9912660472,9963892713,10015331601,10066987666,10118851723,10170934237,10223226039,10275737691,10328459878,10381403260,10434558538,10487936307,10541527284,10595342162,10649371511,10703626122,10758096582,10812793616,10867707827,10922850039,10978210707,11033800754,11089610651,11145651255,11201913054,11258407004,11315123444,11372073429,11429247315,11486656091,11544290129,11602160518,11660257480,11718592204,11777154929,11835956777,11894988004,11954259832,12013762366,12073506929,12133483643

TA的精华主题

TA的得分主题

 楼主| 发表于 2006-10-28 12:42 | 显示全部楼层
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
哇!太感謝了,但5樓的答案所有組合數不太一樣,Combin(49,6)
答案是13983816
49選6,6個數合從21-279所有組合數相加是28269852
是不是函數的公式算錯了?請您看看,謝謝

TA的精华主题

TA的得分主题

发表于 2006-10-29 12:47 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2006-10-29 17:09 | 显示全部楼层

是我搞错了,改为:

B3=INDEX({1;1;2;3;5;7;11;14;20;26;35;44;58;71;90;110;136;163;199;235;282;331;391;454;532;612;709;811;931;1057;1206;1360;1540;1729;1945;2172;2432;2702;3009;3331;3692;4070;4494;4935;5426;5940;6506;7097;7748;8423;9163;9933;10769;11637;12579;13552;14603;15690;16856;18059;19349;20673;22087;23540;25082;26663;28340;30051;31860;33706;35648;37625;39703;41809;44016;46253;48586;50944;53402;55875;58446;61031;63706;66388;69161;71928;74781;77624;80542;83440;86412;89348;92350;95311;98324;101285;104295;107235;110215;113119;116048;118889;121751;124507;127274;129930;132581;135109;137629;140008;142370;144587;146771;148800;150794;152617;154397;156004;157554;158923;160236;161354;162410;163273;164062;164654;165176;165490;165732;165772},130-ABS(150-A3))

TA的精华主题

TA的得分主题

发表于 2006-10-29 17:11 | 显示全部楼层

3楼的函数有误,改为:

Function counts(ByVal max As Long, ByVal m As Long, ByVal sums As Long) As Long
Dim temp As Long, i As Long, j As Long

If max >= m And m = 1 Then counts = IIf(sums <= max, 1, 0)
    If max >= m And m = 2 Then
        For i = 1 To max - 1
            For j = i + 1 To max
                If i + j = sums Then counts = counts + 1
            Next
        Next
    End If
If max >= m And m > 2 Then counts = counts(max - 1, m, sums) + counts(max - 1, m - 1, sums - max)
End Function

您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-29 19:35 , Processed in 0.054539 second(s), 10 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表