[开_123][已结]面额统计终极版，挑战最短纪录吧!

wangg913

经wangjguo44兄验证，公式会因浮点误差引发错误计算。
现更正如下：
D5=IF(C5="","",INT((D$2/1%+1%-SUM(LARGE({1;2;5}*10^{0,1,2,3,4},ROW($2:2))*IF(D$4:D4<"",D$4:D4)))/LARGE({1;2;5}*10^{0,1,2,3,4},ROW(A3))))
数组公式。

[ 本帖最后由 wangg913 于 2010-8-9 23:23 编辑 ]

foodorwater

大头的公式真是精粹。。。

sunya_0529

原帖由 wangg913 于 2010-8-9 18:02 发表
经wangjguo44兄验证，公式会因浮点误差引发错误计算。
现更正如下：
D5=IF(C5="","",INT((D$2/1%+1%-SUM(LARGE({1;2;5}*10^{0,1,2,3,4},ROW($2:2))*IF(D$4:D4

浮点误差还真没注意到，烦请大头兄抽空给写个总结吧，估计没有更精简的思路了，呵呵

wangg913

首先要声明一下：我认为这是一个特别好的竞赛题目，发到正式竞赛区会得到更多更精妙的思路，虽稍有遗憾但仍应该为楼主的创造性思维喝彩。       
这个题目，初次看题时，觉得必须用上面的单元格进行辅助。以D9“5元张数”为例，根据题目要求第四条，即“4、钞票张数应该遵循大额优先原则，
可用一张面额100元的，不允许用两张50元的；”，如果不计算出100元、50元、20元、10元的张数，并从总金额中扣除，是无法计算5元的张数的；                                                       
所以总体思路是：     5元张数=取整((金额-SUM({100元；50元；20元；10元}*D$5:D8))/5元)       
公式这样实现：         D9=INT((D$2-SUM(C5:C8*D5:D8))/C9)                               
可是遇到最大的麻烦是，C5:C17都是中文，还有可能是空单元格，仅仅用SUM函数是无法取得票面金额序列的；
因此题目的关键是怎样将C5：C17转化为“票面金额”数值，即将 100元；50元；20元；10元；5元；2元；1元；5角；2角；1角；2分；1分
转化为 {100;  50;  20;  10;  5;  2;  1;  0.50;  0.20;  0.10;  0.02;  0.01}                               
并且公式是从D5开始下拉，D5单元格必定要用到C4和D4进行辅助，因此还要将“面值选择”，“钞票张数”转化为0
这样公式大体的样式变为，D5=INT( D$2 - SUM(  转化C$4:C4为票面数值  *  转化D$4:D4为钞票张数 ) )/ (转化C5为票面金额 100)                                                               
下拉至D17时公式变为，D17=INT( D$2 - SUM(  转化C$4:C16为票面数值  *  转化D$4:D16为钞票张数 ) )/ (转化C16为票面金额 0.01)       
D列好办，用TEXT进行转换，可轻易将“钞票张数“和空单元格强制转为0，数值不变，TEXT(D$4:D16,"0;;0;!0")
        也可用IF判断。 IF(D$4:D16<"",D$4:D16) , 这个公式源于所有数值均小于""，即使 9E+307也小于"",所有文本都大于""
        TEXT(D$4:D16,"0;;0;!0")        公式长度 23        TRUE=9E+307<""
        IF(D$4:D16<"",D$4:D16)        公式长度 22        TRUE="0">""               
        使用LEN函数会发现，IF语句更短，所以使用了后者，虽然看起来前者更短。
对于C列文本的数值转换，可发现一个规律，就是将文本的最后一位”圆、角、分"替换掉之后，成为
        {"面值选择";100 ; 50 ; 20 ; 10 ; 5 ; 2 ; 1 ; 5 ; 2 ; 1 ; 5 ; 2 ; 1}
        这时候可以取巧，将  “元”替换为"00",  “角”替换为0，  “分”替换为""
        这样替换后，就变为：”面值选择“， 10000，5000 , 2000 , 1000 , 500 , 100 , 50 , 20 , 10 , 5 , 2, 1         
              再用TEXT函数就能得到我所要求的数值了。                                                                               
经过简化，公式为：
         =IF(C5="","",INT((D$2/1%-SUM(TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C$4:C4,"元",0&0),"角",0),"分",),"0;;;!0")*TEXT(D$4:D4,"0;;0;!0")))/LEFT(C5,LEN(C5)-1)%/10^FIND(RIGHT(C5)," 分角元")))
公式长度178
===================================================================================
公式长度178，似乎长了点，但又不能再简化了。可是根据经验，通常情况下实现一个功能，公式长度一般很少长于150的，所以我想一定还有别的思路。       
经过观察，你会发现，人民币的面额有个规律，1分2分5分、1角2角5角、1元2元5元、10元20元50元，100元之后就没有更大的面额了，               

就此我们可以构造一个数组，{1;2;5}*{1,10,100,1000,10000}                               
        1        10        100        1000        10000               
        2        20        200        2000        20000                               
        5        50        500        5000        50000                               
C列的规律是从上而下票面额逐渐变小，可用LARGE函数来取值，LARGE({1;2;5}*10^{0,1,2,3,4},ROW(3:15))       
                100元                100
                50元                50
                20元                20
                10元                10
                5元                5
                2元                2
                1元                1
                5角                0.5
                2角                0.2
                1角                0.1
                5分                0.05
                2分                0.02
                1分                0.01
公式最终是：
         =IF(C5="","",INT((D$2/1%+1%-SUM(LARGE({1;2;5}*10^{0,1,2,3,4},ROW($2:2))*IF(D$4:D4<"",D$4:D4)))/LARGE({1;2;5}*10^{0,1,2,3,4},ROW(A3))))
         公式中添加“+1%”的原因是：经wangjguo44兄验证，可能出现浮点误差而导致错误计算，即“分”的数量与实际不符，少算或者多算一“分”。

[ 本帖最后由 wangg913 于 2010-8-16 22:01 编辑 ]

sunya_0529

原帖由 wangg913 于 2010-8-16 18:29 发表
首先要声明一下：我认为这是一个特别好的竞赛题目，发到正式竞赛区会得到更多更精妙的思路，虽稍有遗憾但仍应该为楼主的创造性思维喝彩。        
这个题目，初次看题时，觉得必须用上面的单元格进行辅助。以D9“5元张数” ...

非常感谢wangg913（大头兄）的总结和分享，也感谢sclzah、gdfcx等几位E友的热情参与。

下面是我原先的思路，虽然和大头兄的精妙思路相比，不值一提，可为了总结一下学习心得，贴出来比较一下：

1. 
   
2. =IF(C5="","",INT(ROUND(SUM(TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C$4:C4,"元",""),"角","0%"),"分","%"),"#.##;;;!0")*TEXT(D$4:D4,"0;;!0;!0"))-D$2,2)/-SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C5,"元",""),"角","0%"),"分","%")))
   
3. 209字符
   
4. 
   
5. =SUMPRODUCT(--TEXT(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(C5:C17,"元",""),"角","0%"),"分","%"),"#.00;;;!0"),D5:D17)
   
6. 106字符
   

复制代码

对比大头兄的思路，几点学习心得如下：

1、浮点运算处理的思路

　　众所周知，在Excel中，小数位的四则运算容易产生浮点错误，比如123.1-123，将结果保留个17位小数，就可以看出运算出来的值不是0.1，而一个无限接近于0.1的数。通常处理这种浮点错误的思路是使用精度控制函数，如ROUND()、TRUNC()等参与运算各个过程。还有一个思路就是将数值都先转换成整数，完成四则运算后再等比例处理回去。

　　对比我的这个公式和大头兄的第一个思路就可以看出，整体放大后运算的方法，使公式省了不少字符，有效地缩短了公式长度。

2、数组维度转换

　　内存数组比较抽象，需要一定的空间想像能力才能更好地理解并运用他们。通常处理内存数组的公式都是在同等维度的情况下操作，即参与运算的数组行列长度相等，不过实际应用中，我们可能需要在数组的维度之间根据需要进行换算，比如大头兄的第二个思路里，巧妙地利用了人民币面值的数字规律，利用“{1;2;5}*{1,10,100,1000,10000}”这样一个三行单维数组与五列单维数组的乘运算，构建了一个三行五列的多维数组，刚好包括了人民币面值乘以100后的所有数字，再利用Large()函数来降维，形成了一个一列多行的一维数组，刚好和C5:C17区域里的面额值匹配，构思之巧妙，令人叹服。

　　降维的函数还有Frequency()等支持多单元格数组公式的函数，要在实际中游刃有余地运用他们，还需要勤加修炼，呵呵。

3、解决实际问题的思路

　　题目中提到的这个问题相信年长一点的出纳都遇到过，应该说是一个比较贴合实际应用的问题。从大头兄的两个思路来看，第一种是常规思路，第二种是总结规律后的理论研究型思路，效率高且很有针对性。

　　试想，如果币值里有诸如7角、60元等（当然这种情况现实生活中还没有，起码目前没有 ），能用得上的公式就只有第一个思路了。

　　相信目前在ExcelHome里的大部分家人，学习Excel都是从事实际应用的，所以解决问题的思路所遵循的原则应该实用、高效、简单、通用。实用第一，不管白猫黑猫，能抓耗子就是好猫，不管基础操作，还是函数、透视表甚或是VBA，能解决问题的方法都可以；在解决同一个问题有多个实用思路可以选择的情况下，我们要考虑的就是效率的问题了，高射炮打蚊子虽然也行，但效率不言而喻； 解决效率的选择后，就要考虑解决方案的复杂性，简单就是硬道理；最后再考虑的就是通用性，是不是同样的方法可以举一反三，触类旁通。

　　以上是自己的一些学习心得，谨与广大E友分享。虽然本题参与的人很少，但相信什么问题用心研究，都会有所得。

　　* 如果版主留意此帖的话，希望能给wangg913（大头兄）加技术分鼓励。

[ 本帖最后由 sunya_0529 于 2010-8-19 10:01 编辑 ]

wshcw

做了一早上，公式臭长，不发上来了。

泓()

学习 大头 阳光多了<头>

wshcw

原帖由 wangg913 于 2010-8-16 18:29 发表
首先要声明一下：我认为这是一个特别好的竞赛题目，发到正式竞赛区会得到更多更精妙的思路，虽稍有遗憾但仍应该为楼主的创造性思维喝彩。        
这个题目，初次看题时，觉得必须用上面的单元格进行辅助。以D9“5元张数” ...

征求大头的意见,我觉得C列为空时,结果不必空(为0也行),于是公式为:
D5公式:
=(C5>"")*INT((1%%+D$2-SUM(IF(D$4:D4<"",D$4:D4)*LARGE({5;2;1}*10^{0,1,2,3,4},ROW($2:2)))%)/LARGE({5;2;1}*10^{0,1,2,3,4},ROW(A3))%)
单元格格式:G/通用格式;;
这样一来,D19公式也可简化成:
=SUM(LARGE({5;2;1}*10^{0,1,2,3,4},ROW(3:15))*D5:D17%)

[ 本帖最后由 wshcw 于 2010-8-19 00:17 编辑 ]

wangg913

改变单元格格式，也是个办法。对了，其实为空对此一举。
我说了不算，问问楼主。

sunya_0529

大理版主，从效果上来说，视觉呈现是一样的，结果也正确。

不过在出题时，已经说明使用“纯公式”，单元格格式不算，呵呵:)

D19的公式精简得好，谢谢版主提供条件表达式的精简思路

[ 本帖最后由 sunya_0529 于 2010-8-19 10:08 编辑 ]