ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

   
EH云课堂-专业的职场技能充电站 Excel转在线管理系统,怎么做看这里 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
Excel不给力? 何不试试FoxTable! Excel 2016函数公式学习大典 高效办公必会的Office实战技巧 免费下载Excel行业应用视频
300集Office 2010微视频教程 Tableau-数据可视化工具 精品推荐-800套精选PPT模板,点击获取 ExcelHome出品 - VBA代码宝免费下载
你的Excel 2010实战技巧学习锦囊 欲罢不能, 过目难忘的 Office 新界面 Excel VBA经典代码实践指南
查看: 5724|回复: 9

[Excel 函数与公式] [第31期]求不重复组合之和及个数[已结]

[复制链接]

TA的精华主题

TA的得分主题

发表于 2007-12-31 14:36 | 显示全部楼层 |阅读模式

不重复组合之和及个数:

(附件增加了数据示例)

答案请发送至我的邮箱:w829924@hotmail.com,并跟贴占位,请勿在跟贴中直接发答案
[此贴子已经被作者于2008-1-22 19:29:01编辑过]
单选投票, 共有 16 人参与投票

距结束还有: 5812 天17 小时36 分钟

您所在的用户组没有投票权限

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?免费注册

x

TA的精华主题

TA的得分主题

发表于 2007-12-31 18:53 | 显示全部楼层


答案已经发到版主的邮箱,没有考虑A2:G2有重复的情况,不知道是否满足要求,公式长度304

请您批阅,祝新春快乐!

QUOTE:

H2公式(长度198):

=TEXT(MID(SMALL(100+TEXT(FREQUENCY(MMULT((MOD(ROW($1:$127)/2^COLUMN($A:$G)*2,2)>=1)*$A$2:$G$2,ROW($1:$7)^0),ROW($1:$700))+ROW($1:$701)/10^3+1%%,"[<1]1!0!0"),MIN(ROW(A1),701)),8-3*COLUMN(A1),3),"#;")

QUOTE:

主要思路正确,1%%固定小数尾数个数的方法很巧妙,但mmult(x,y)还可以优化. 结果正确并满足答题要求,评2分.

[此贴子已经被willin2000于2008-1-22 18:27:26编辑过]

TA的精华主题

TA的得分主题

发表于 2008-1-15 19:23 | 显示全部楼层

太难了,还是参与一下,已发送邮件至w829924@hotmail.com

QUOTE:

H2公式(长度198):

=IF(SUM($I$1:$I1)<127,--MID((1.0001-1/SMALL(1/(1-FREQUENCY(MMULT($A$2:$G$2*MOD(INT(ROW($1:$127)/2^{6,5,4,3,2,1,0}),2),ROW($1:$7)^0),ROW($1:$700))-ROW($1:$701)/1000),ROW()-1))/10^3,(1=I$1)*3+3,3),"")

QUOTE:

FREQUENCY(MMULT(.... 方法正确,用1/(1-Freq来反序,方法比较独特. 所以要用SUM($I$1:$I1)<127来排错,稍显复杂.1=I$1有点取巧,不过也是一种方法.

结果正确并满足答题要求,评2分.

[此贴子已经被willin2000于2008-1-22 18:27:56编辑过]

TA的精华主题

TA的得分主题

发表于 2008-1-15 23:33 | 显示全部楼层
占位跟贴,就题论题用了176字符,[em01]期待答案的公布!
H2公式(长度176):
QUOTE:
=--MID(SMALL(10^6+TEXT(FREQUENCY(MMULT($A$2:$G$2,MOD(INT(COLUMN($A:$DW)*2/2^ROW($1:$7)),2)),ROW($1:$700))*10^3+ROW($1:$701),"[>999];1!e6"),MIN(ROW()-1,701)),2+(COLUMN()=8)*3,3)
用菜单->选项->视图->窗口选项->取消"零值"来屏蔽0.
QUOTE:
完全符合出题出发点,用TEXT的判断功能来生成最大数,其中1!e6用的非常精彩, 结果正确并满足答题要求,评2分, 本期最佳答案+1分, 总评3分.
[此贴子已经被willin2000于2008-1-22 18:28:18编辑过]

TA的精华主题

TA的得分主题

发表于 2008-1-18 16:52 | 显示全部楼层

占位跟贴

唉!做了个开始209 现在203个字符,希望总结前能改到200以下,[em06][em06][em06]

QUOTE:

H2公式(长度199):

=IF(SUM($I$1:$I1)<127,--MID((1.0001-1/SMALL(1/(-FREQUENCY(MMULT($A$2:$G$2*MOD(INT(ROW($1:$127)/2^{6,5,4,3,2,1,0}),2),ROW($1:$7)^0),ROW($1:$700))+1-ROW($1:$701)/1000),ROW(A1)))/10^3,(1=I$1)*3+3,3),"")

QUOTE:
方法和3楼一样,结果正确并满足答题要求,评2分.
[此贴子已经被willin2000于2008-1-22 18:32:41编辑过]

TA的精华主题

TA的得分主题

发表于 2008-1-20 11:49 | 显示全部楼层

题目确实比较难,不光有字符限制的问题,还有嵌套限制的问题。

想了不少方案,但只找到一种符合要求的答案,期待其他人的精彩答案。

QUOTE:

一共有6种方法,其中满足200个字符的公式(长度200):

=TEXT(MOD(10^(6*($H$1=H$1))*(SMALL(--TEXT(FREQUENCY(MMULT((MOD(ROW($1:$127)/2^{0,1,2,3,4,5,6},2)>=1)*$A$2:$G$2,{1;1;1;1;1;1;1}),ROW($1:$65535))+ROW($1:$65536)/10^6,"[>=1];1000"),ROW(A1))),1000),"#;;")


QUOTE:

TEXT(FREQUENCY(MMULT(  方向正确,不改方法,上面的公式其实可以再短点(颜色部分,长度191):

=TEXT(MOD(10^(6*($H$1=H$1))*(SMALL(--TEXT(FREQUENCY(MMULT((MOD(ROW($1:$127)/2^{0,1,2,3,4,5,6},2)>=1)*$A$2:$G$2,ROW($1:$7)^0),ROW($1:$65535))+ROW(A:A)/10^6,"[>=1];1000"),ROW(A1))),1000),"#;;")

用($1:$65536)来排除使得公式速度不太理想.

由于其他5个公式也都是没有用MID来取数,所以较长, 如果mmult进一步优化,5个公式都可以修改到200个字符以下. 其中TEXT(...TEXT(...."[>10]0!:0.00;1")..."s.00","h.mm") 和TEXT(...;""100000""")的用法值得借鉴.

公式1结果正确并满足答题要求,评2分.

[此贴子已经被willin2000于2008-1-22 18:57:12编辑过]

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?免费注册

x

TA的精华主题

TA的得分主题

发表于 2008-1-20 16:54 | 显示全部楼层

跟帖占位,,,,,,,,很想知道答案,,,,,,还好最后一天赶上了,,,哈哈哈哈哈哈哈哈哈哈

QUOTE:

H2公式(长度199):

=IF(SUM($I$1:$I1)<127,--MID((1.0001-1/SMALL(1/(-FREQUENCY(MMULT($A$2:$G$2*MOD(INT(ROW($1:$127)/2^{6,5,4,3,2,1,0}),2),ROW($1:$7)^0),ROW($1:$700))+1-ROW($1:$701)/1000),ROW()-1))/10^3,(1=I$1)*3+3,3),"")

QUOTE:

同3楼,结果正确并满足答题要求,评2分

[此贴子已经被willin2000于2008-1-22 18:52:11编辑过]

TA的精华主题

TA的得分主题

 楼主| 发表于 2008-1-22 18:53 | 显示全部楼层

能实现本题要求的公式:
公式1使用整数位数固定法,长度172个字符:
=--MID(SMALL(10^6+TEXT(FREQUENCY(MMULT($A$2:$G$2,INT(MOD(COLUMN($A:$DW)*2/2^ROW($1:$7),2))),ROW($1:$700))*10^3+ROW($1:$701),"[<999]1!e6"),MIN(ROW(A1),701)),29-3*COLUMN(),3)

公式2使用小数尾数固定法,长度173个字符:
=--MID(SMALL(101+TEXT(FREQUENCY(MMULT($A$2:$G$2,INT(MOD(COLUMN($A:$DW)*2/2^ROW($1:$7),2))),ROW($1:$700))-1+ROW($1:$701)/10^3,"[>];99")+1%%,MIN(ROW(A1),701)),29-3*COLUMN(),3)

公式3是178个字符(使用1!e6的方式后就变成公式1):
=--MID(10^6+SMALL(TEXT(FREQUENCY(MMULT($A$2:$G$2,INT(MOD(COLUMN($A:$DW)*2/2^ROW($1:$7),2))),ROW($1:$700))*10^3+ROW($1:$701)-1,"[<999]999999")+1,MIN(701,ROW(A1))),29-3*COLUMN(),3)

本题只是规定不能使用条件格式或自定义格式这2种方式来屏蔽0,所以以上公式可以在工具选项中的视图/窗口选项中取消"零值"的勾选来不显示0,以上三个公式都是采用这种设置.

也可以用公式来屏蔽0值,公式4长度为183个字符:
=TEXT(MID(SMALL(10^6+TEXT(FREQUENCY(MMULT($A$2:$G$2,--(MOD(COLUMN($A:$DW)*2/2^ROW($1:$7),2)>=1)),ROW($1:$700))*10^3+ROW($1:$701),"[<999]1!e6"),MIN(ROW()-1,701)),29-3*COLUMN(),3),"#;")
--由于嵌套限制,所以用--代替INT(嵌套限制针对函数,不针对运算符)

最短的公式是(162个字符,但速度太慢,所以只作为一种思路介绍):
=--MID(SMALL(10^9+TEXT(FREQUENCY(MMULT($A$2:$G$2,MOD(INT(COLUMN($A:$DW)*2/2^ROW($1:$7)),2)),ROW($1:$65535))*10^6+ROW(A:A),"[<10e5]1!e9"),ROW(A1)),56-6*COLUMN(),3)

下面解释一下以上公式和本题用到的主要关键点.

关键点1:
为取得所有的组合所以需要产生一个数组B(但需要主要的是下面这个数组可以有多种方向,知识点3就是利用了这一点)
0 0 0 0 0 0 1
0 0 0 0 0 1 0
0 0 0 0 0 1 1
......
1 1 1 1 1 1 1
然后逐个和A2:G2(数组A)相乘相加(使用函数mmult)就能得到各个组合之和.

关键点2:
mmult函数对2个相乘数组Amn Bxy的要求是n=x,为了不使用TRANSPOSE减少公式长度,所以直接用mmult(数组A,数组B),由于数组A=1*7,所以要求数组B是7*127,这样结果就是1*127的数组组合之和.

关键点3:
产生一个大小方向是怎样的数组, COLUMN($A:$DW)/2^ROW($1:$7)是得到7*127数组的最短公式,COLUMN($A:$DW)是127列,除ROW(1:7)后得到7行127列.其中一个技巧是用2^ROW($1:$7)/2代替2^(ROW($1:$7)-1)能减少字符,所以最后公式是COLUMN($A:$DW)*2/2^ROW($1:$7)
大小方向是指下面几种数组:

所以使用COLUMN(A:DW)还是ROW(1:127), 用ROW(1:7)还是COLUMN(A:G)是本题关键之一,这影响到MMULT的相乘方式.

关键点4:(28,29期的考点)
由于一共有7个单元格,且每格单元格最大为99,所以最大组合之和=7*99=693,所以可以用Frequency来求得1:693出现的次数.

关键5:
将用freq产生的次数和对应和进行组合.组合串S=次数 与 和,组合方式有两种一种是S1=次数*10^3+和,另一种是S2=次数+和/10^n

所有答案都考虑到了4和5.

关键点6:
使用TEXT函数的判断功能将重复的和区分出来,在S1中用"<".S2用">",这里比较关键的是TEXT和S组合和方式的协同方式.
更好地使用一些技巧,比如e指数(S1中)和%(S2中),或使用 -1 ..."99"..+1, 能进一步减少字符.

关键点7:(也是29期的考点)
使用MIN来解决SMALL个数不够时的出错问题.

关键点8:(29期也用到了)
根据列的不同,使用MID来取得结果,主要解决MID的第二参数问题.


本期题的公式中虽然只出现了一次A2:G2,但围绕着A2:G2却需要进行一连串的变换,考虑公式时要注意各个函数之间的协同,这也是出本题的目的之一.

下面以公式4为例简单解释一下公式原理:
公式4=TEXT(MID(SMALL(10^6+TEXT(FREQUENCY(MMULT($A$2:$G$2,--(MOD(COLUMN($A:$DW)*2/2^ROW($1:$7),2)>=1)),ROW($1:$700))*10^3+ROW($1:$701),"[<999]1!e6"),MIN(ROW()-1,701)),29-3*COLUMN(),3),"#;")

无中生有
a=--(MOD(COLUMN($A:$DW)*2/2^ROW($1:$7),2)>=1)
--生成用来取值的01数组.

各取所需
b=MMULT($A$2:$G$2,a)
--和01相乘相加取得所有组合之和.

大海捞针
c=FREQUENCY(b,ROW($1:$700))
--求出各个和的次数,在b中没有的次数为0,内存结果数组行数为701行.

相依为命
d=c*10^3+ROW($1:$701)
--将次数和"和"组合.

唇亡齿寒
e=text(d,"[<999]1!e6")
--次数(c)为0的改成10^6,其他按原来的.

取长补短
f=10^6+e
--固定字符位数,为MID取数做准备.

捉襟见肘
g=MIN(ROW()-1,701)
--由于c的函数是701,所以当大于701时small会出错,用min来限制.

以小欺大
h=small(f,g)
--从小到大逐个取出.

偷梁换柱
i=29-3*COLUMN()
--确定MID取数的起始位置.

随遇而安
j=MID(h,i)
--取得对应的值


一夫当关 >>以下两个是锦上添花.
k="#;"
--屏蔽0值的格式

入乡随俗
Text(j,k)
--取得最终结果


本次答题人数不多,所以多啰嗦了几句.
[此贴子已经被作者于2008-1-23 23:50:19编辑过]

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?免费注册

x

评分

参与人数 2鲜花 +5 收起 理由
sayhi95 + 3 感谢帮助
horselyq + 2 太强大了

查看全部评分

TA的精华主题

TA的得分主题

发表于 2008-1-22 20:49 | 显示全部楼层
QUOTE:
以下是引用chrisfang在2008-1-20 11:49:26的发言:

题目确实比较难,不光有字符限制的问题,还有嵌套限制的问题。

想了不少方案,但只找到一种符合要求的答案,期待其他人的精彩答案。

QUOTE:

由于其他5个公式也都是没有用MID来取数,所以较长, 如果mmult进一步优化,5个公式都可以修改到200个字符以下. 其中TEXT(...TEXT(...."[>10]0!:0.00;1")..."s.00","h.mm") 和TEXT(...;""100000""")的用法值得借鉴.

没用Mid取数主要还是因为没有理解题目可以采用其他方式来屏蔽0值的潜规则,不然的话用了mid还要再嵌套Text来屏蔽零值的话就会超过嵌套限制了。

如果可以使用选项来屏蔽零值,一下子就海阔天空了,腾挪的余地就大了不少。

题目本身不错,让我发现了不少新的解题思路,但实在是综合了太多的考查点,有些曲高和寡了。

[此贴子已经被作者于2008-1-22 20:59:06编辑过]

TA的精华主题

TA的得分主题

发表于 2019-1-4 15:58 | 显示全部楼层
willin2000 发表于 2008-1-22 18:53
能实现本题要求的公式:公式1使用整数位数固定法,长度172个字符:=--MID(SMALL(10^6+TEXT(FREQUENCY(MMULT($A ...

我好好研读学习下,不常用的函数真的太多了,楼主能推荐下怎么学习更好吗
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

关闭

最新热点上一条 /1 下一条

关注官方微信,每天学会一个新技能

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

GMT+8, 2019-6-17 21:00 , Processed in 1.482692 second(s), 19 queries , Gzip On, MemCache On.

Powered by Discuz! X3.4

© 2001-2017 Wooffice Inc.

   

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

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

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