SUMIF之跨表统计以及INDEX()的致命伤

总结多表格数据汇总问题－－－－－结合以前的帖子和自己的发挥在浏览SUMIF函数相关帖子的时候发现了一个帖子，也是讲多表汇总的，多表名称是不规范的情况。但里面的公式不够简洁。如果表格的名称是规范的我们可以用INDIRECT()嵌套row(1:n)之类的函数进行数组运算，那应该是比较简单的。先就简单的情况演示一下角色莫小贝小六小米湘玉流星小雨汇总-朴素18918018999136231汇总－发展18918018999136231朴素的方式是我思维的基础，从朴素的公式我分析出需要用SUM()---之间都是用＋运算需要把单引号中的1，2，3用ROW(1:3)来数组化其中列号(B,C,D,E,F,G,H)的改变和公式承载单元格是同步的，考虑用column()把他们联系起来运用INDIRECT()把这些地址用字符串运算符把每个需要的元素粘合在一起。其中，列号用COLUMN()绑定是这样的：运用了CHAR(),CODE().65=CODE(A)A=CHAR(65)于是：BCDE=CHAR(65+COLUMN()-3)于是：最终的公式{=SUM(N(INDIRECT('&ROW(1:3)&'!&CHAR(65+COLUMN()-1)&2)))}=SUM(N(INDIRECT({'1'!B2;'2'!B2;'3'!B2})))使用朴素的方式可以为我们提供格式,这样我们就没有必要去记格式了，照什么葫芦什么瓢。朴素的东西是不完善的，但她为我们提供了一条可参考的重要的线索。工作表命名混乱的情况上面的例子之所以简单，是得益于工作表命名规范性－－－1，2，3（泛指工作表名称中绝大多数文字相同，只是个别用数字序列或字母等来区分的情况）那么对于工作表命名混乱的情况该如何呢。角色莫小贝小六小米湘玉流星小雨汇总-朴素409425495399331577汇总－发展？？？？？？对于朴素的公式=排山倒海!H2+惊涛骇浪!H2+郑重的起誓!H2+诶呀我的妈呀!H2+葵花点穴!H2+'1'!H2+'2'!H2+'3'!H2='1'!H2+'2'!H2+'3'!H2我们发觉实质是相同的，就是表格名称没有规律性。这里我们就用到了提取工作表名称的名称定义：sht_name=mid(get.workbook(1),find(],get.workbook(1))+1,1000)&t(now())用F9庖丁解牛：sht_name＝{汇总,排山倒海,惊涛骇浪,郑重的起誓,诶呀我的妈呀,葵花点穴,1,2,3}发现我们定义的名称SHT_NAME为我们提供了一个包含工作表名称的数组，我们只要用公式从这个数组中提取工作表名称分配给汇总项的求和因子就能把公式从朴素升级到发展。由于汇总表的存在，直接用SHT_NAME做数组参数不行。就是说公式相对莫小贝#N/A=SUM(N(INDIRECT(sht_name&!b2)))于是我们想从这个数组从截取一段，我们称之为sht_name1,那么怎么生成sht_name1呢？刚开始：我想当然的认为：sht_name1{=index(sht_name,row(2:9))}但是用庖丁解牛发现结果并不是数组，而是一个数；不解，也许就是INDEX（）的致命伤吧。于是发了求助帖，主要就是钻到IDNEX()的致命伤里面了。吃饭回来后，看了朋友的回复，反觉是个简单的问题，使用了LOOKUP函数。在此还是感谢那位朋友，呵呵。sht_name#N/A=LOOKUP(ROW(2:9),ROW(1:9),sht_name)用F9解读后：{排山倒海;惊涛骇浪;郑重的起誓;诶呀我的妈呀;葵花点穴;1;2;3}这就是我们想要的。和sht_name相对比就是少了汇总所以，公式可以这样：角色莫小贝小六小米湘玉流星汇总－发展#N/A#N/A#N/A#N/A#N/A=SUM(N(INDIRECT(LOOKUP(ROW(2:9),ROW(1:9),sht_name)&!&CHAR(65+COLUMN()-2)&2)))我觉得这个问题是个普遍的问题，因为很多数据源都是很不规范的，所以这是个很有杀伤力的武器。另外大家要注意的就是这个问题的衍生发现：＝INDEX(SHT_NAME,ROW(2:9))不能用来生成一个新的数组。也许中间由于个人的知识有限导致了不正确的结论，希望大家给予指正。好了！没想到想探探SUMIF的底细居然探出个这么个东西拉，我想到擂台上也上他们显示一把。呵呵。总结多表格数据汇总问题－－－－－结合以前的帖子和自己的发挥在浏览SUMIF函数相关帖子的时候发现了一个帖子，也是讲多表汇总的，多表名称是不规范的情况。但里面的公式不够简洁。如果表格的名称是规范的我们可以用INDIRECT()嵌套row(1:n)之类的函数进行数组运算，那应该是比较简单的。夏东海151='1'!H2+'2'!H2+'3'!H2朴素151=SUM(N(INDIRECT('&ROW(1:3)&'!&CHAR(65+COLUMN()-1)&2)))朴素的方式是我思维的基础，从朴素的公式我分析出需要用SUM()---之间都是用＋运算需要把单引号中的1，2，3用ROW(1:3)来数组化其中列号(B,C,D,E,F,G,H)的改变和公式承载单元格是同步的，考虑用column()把他们联系起来运用INDIRECT()把这些地址用字符串运算符把每个需要的元素粘合在一起。=CHAR(65)FG于是：最终的公式{=SUM(N(INDIRECT('&ROW(1:3)&'!&CHAR(65+COLUMN()-1)&2)))}=SUM(N(INDIRECT({'1'!B2;'2'!B2;'3'!B2})))使用朴素的方式可以为我们提供格式,这样我们就没有必要去记格式了，照什么葫芦什么瓢。上面的例子之所以简单，是得益于工作表命名规范性－－－1，2，3（泛指工作表名称中绝大多数文字相同，只是个别用数字序列或字母等来区分的情况）夏东海382=排山倒海!H2+惊涛骇浪!H2+郑重的起誓!H2+诶呀我的妈呀!H2+葵花点穴!H2+'1'!H2+'2'!H2+'3'!H2？对于朴素的公式=排山倒海!H2+惊涛骇浪!H2+郑重的起誓!H2+诶呀我的妈呀!H2+葵花点穴!H2+'1'!H2+'2'!H2+'3'!H2这里我们就用到了提取工作表名称的名称定义：sht_name=mid(get.workbook(1),find(],get.workbook(1))+1,1000)&t(now()){汇总,排山倒海,惊涛骇浪,郑重的起誓,诶呀我的妈呀,葵花点穴,1,2,3}发现我们定义的名称SHT_NAME为我们提供了一个包含工作表名称的数组，我们只要用公式从这个数组中提取工作表名称分配给=SUM(N(INDIRECT(sht_name&!b2)))是不行的，主要是多加了汇总表的B2.于是我们想从这个数组从截取一段，我们称之为sht_name1,那么怎么生成sht_name1呢？#N/A=INDEX(sht_name,ROW(2:9))但是用庖丁解牛发现结果并不是数组，而是一个数；不解，也许就是INDEX（）的致命伤吧。于是发了求助帖，主要就是钻到IDNEX()的致命伤里面了。吃饭回来后，看了朋友的回复，反觉是个简单的问题，使用了LOOKUP函数。=LOOKUP(ROW(2:9),ROW(1:9),sht_name)用F9解读后：{排山倒海;惊涛骇浪;郑重的起誓;诶呀我的妈呀;葵花点穴;1;2;3}这就是我们想要的。和sht_name相对比就是少了汇总所以，公式可以这样：小雨夏东海#N/A#N/A=SUM(N(INDIRECT(LOOKUP(ROW(2:9),ROW(1:9),sht_name)&!&CHAR(65+COLUMN()-2)&2)))我觉得这个问题是个普遍的问题，因为很多数据源都是很不规范的，所以这是个很有杀伤力的武器。另外大家要注意的就是这个问题的衍生发现：＝INDEX(SHT_NAME,ROW(2:9))不能用来生成一个新的数组。也许中间由于个人的知识有限导致了不正确的结论，希望大家给予指正。没想到想探探SUMIF的底细居然探出个这么个东西拉，我想到擂台上也上他们显示一把。呵呵。发展=排山倒海!H2+惊涛骇浪!H2+郑重的起誓!H2+诶呀我的妈呀!H2+葵花点穴!H2+'1'!H2+'2'!H2+'3'!H2角色莫小贝小六小米湘玉流星小雨夏东海欢迎度76708833287531角色莫小贝小六小米湘玉流星小雨夏东海欢迎度65221936289154角色莫小贝小六小米湘玉流星小雨夏东海欢迎度136997938986角色莫小贝小六小米湘玉流星小雨夏东海欢迎度5331244761853#N/A角色莫小贝小六小米湘玉流星小雨夏东海欢迎度13819094556487角色莫小贝小六小米湘玉流星小雨夏东海欢迎度75708833287531角色莫小贝小六小米湘玉流星小雨夏东海欢迎度89253652856921角色莫小贝小六小米湘玉流星小雨夏东海欢迎度25856514238799