SUMIF之跨表統(tǒng)計以及INDEX()的致命傷

角色,歡迎度匯總－錯誤,歡迎度匯總－正確,,,,,,,,,,,,,,,

莫小貝,380,409,,,,,,,,,,,,,,,

小六,350,425,,,,,,,,,,,,,,,

小米,440,495,,,,,,,,,,,,,,,

湘玉,165,399,,,,,,,,,,,,,,,

流星,140,331,,,,,,,,,,,,,,,

小雨,375,577,,,,,,,,,,,,,,,

夏東海,155,382,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,,,

,,總結(jié)多表格數(shù)據(jù)匯總問題,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,,,

,,－－－－－結(jié)合以前的帖子和自己的發(fā)揮,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,,,

在瀏覽SUMIF函數(shù)相關(guān)帖子的時候發(fā)現(xiàn)了一個帖子，也是講多表匯總的，多表名稱是不規(guī)范的情況。但里面的公式不夠簡潔。,,,,,,,,,,,,,,,,,

如果表格的名稱是規(guī)范的我們可以用INDIRECT()嵌套row(1:n)之類的函數(shù)進行數(shù)組運算，那應該是比較簡單的。,,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,,,

先就簡單的情況演示一下,,,,,,,,,,,,,,,,,

角色,莫小貝,小六,小米,湘玉,流星,小雨,夏東海,,,,,,,,,,

匯總-樸素,189,180,189,99,136,231,151,='1'!H2+'2'!H2+'3'!H2,,,樸素,,,,,,

匯總－發(fā)展,189,180,189,99,136,231,151,"=SUM(N(INDIRECT(""'""&ROW(1:3)&""'!""&CHAR(65+COLUMN()-1)&""2"")))",,,,,,,,,發(fā)展

樸素的方式是我思維的基礎(chǔ)，從樸素的公式我分析出需要用SUM()---之間都是用＋運算,,,,,,,,,,,,,,,,,

,,,需要把單引號中的1，2，3用ROW(1:3)來數(shù)組化,,,,,,,,,,,,,,

,,,"其中列號(B,C,D,E,F,G,H)的改變和公式承載單元格是同步的，考",,,,,,,,,,,,,,

,,,慮用column()把他們聯(lián)系起來,,,,,,,,,,,,,,

,,,運用INDIRECT()把這些地址用字符串運算符把每個需要的元素粘合在一起。,,,,,,,,,,,,,,

,"其中，列號用COLUMN()綁定是這樣的：運用了CHAR(),CODE().",,,,,,,,,,,,,,,,

,,65,"=CODE(""A"")",,A,=CHAR(65),,,,,,,,,,,

,,于是：,B,C,D,E,F,G,,,,,,,,,

,,,=CHAR(65+COLUMN()-3),,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,,,

,"于是：最終的公式{=SUM(N(INDIRECT(""'""&ROW(1:3)&""'!""&CHAR(65+COLUMN()-1)&""2"")))}",,,,,,,,,,,,,,,,

,,"=SUM(N(INDIRECT({""'1'!B2"";""'2'!B2"";""'3'!B2""})))",,,,,,,,,,,,,,,

,"使用樸素的方式可以為我們提供""格式"",這樣我們就沒有必要去記格式了，照什么葫蘆什么瓢。",,,,,,,,,,,,,,,,

,樸素的東西是不完善的，但她為我們提供了一條可參考的重要的線索。,,,,,,,,,,,,,,,,

,,,,,,,,,,,,,,,,,,

工作表命名混亂的情況,,,,,,,,,,,,,,,,,

上面的例子之所以簡單，是得益于工作表命名規(guī)范性－－－1，2，3（泛指工作表名稱中絕大多數(shù)文字相同，只是個別用數(shù)字序列或字母等來區(qū)分的情況）,,,,,,,,,,,,,,,,,

那么對于工作表命名混亂的情況該如何呢。,,,,,,,,,,,,,,,,,

角色,莫小貝,小六,小米,湘玉,流星,小雨,夏東海,,,,,,,,,,

匯總-樸素,409,425,495,399,331,577,382,=排山倒海!H2+驚濤駭浪!H2+鄭重的起誓!H2+誒呀我的媽呀!H2+葵花點穴!H2+'1'!H2+'2'!H2+'3'!H2,,,,,,,,,

匯總－發(fā)展,？,？,？,？,？,？,？,,,,,,,,,,

對于樸素的公式=排山倒海!H2+驚濤駭浪!H2+鄭重的起誓!H2+誒呀我的媽呀!H2+葵花點穴!H2+'1'!H2+'2'!H2+'3'!H2,,,,,,,,,,,,,,,,,

,='1'!H2+'2'!H2+'3'!H2,,,,,,,,,,,,,,,,

我們發(fā)覺實質(zhì)是相同的，就是表格名稱沒有規(guī)律性。,,,,,,,,,,,,,,,,,

"這里我們就用到了提取工作表名稱的名稱定義：sht_name=mid(get.workbook(1),find(""]"",get.workbook(1))+1,1000)&t(now())",,,,,,,,,,,,,,,,,

用F9庖丁解牛：,,sht_name＝,"{""匯總"",""排山倒海"",""驚濤駭浪"",""鄭重的起誓"",""誒呀我的媽呀"",""葵花點穴"",""1"",""2"",""3""}",,,,,,,,,,,,,,

,發(fā)現(xiàn)我們定義的名稱SHT_NAME為我們提供了一個包含工作表名稱的數(shù)組，我們只要用公式從這個數(shù)組中提取工作表名稱分配給,,,,,,,,,,,,,,,,

,匯總項的求和因子就能把公式從樸素升級到發(fā)展。,,,,,,,,,,,,,,,,

,由于匯總表的存在，直接用SHT_NAME做數(shù)組參數(shù)不行。,,,,,,,,,,,,,,,,

,就是說公式相對莫小貝,789,"=SUM(N(INDIRECT(sht_name&""!b2"")))",,,,,是不行的，主要是多加了匯總表的B2.,,,,,,,,,

,"于是我們想從這個數(shù)組從截取一段，我們稱之為sht_name1,那么怎么生成sht_name1呢？",,,,,,,,,,,,,,,,

,剛開始：我想當然的認為：,,,,,,,,,,,,,,,,

,,sht_name1,"{=index(sht_name,row(2:9))}",,,,排山倒海,"=INDEX(sht_name,ROW(2:9))",,,,,,,,,

,,但是用庖丁解牛發(fā)現(xiàn)結(jié)果并不是數(shù)組，而是一個數(shù)；不解，也許就是INDEX（）的致命傷吧。,,,,,,,,,,,,,,,

,,于是發(fā)了求助帖，主要就是鉆到IDNEX()的致命傷里面了。,,,,,,,,,,,,,,,

,,吃飯回來后，看了朋友的回復，反覺是個簡單的問題，使用了LOOKUP函數(shù)。,,,,,,,,,,,,,,,

,,在此還是感謝那位朋友，呵呵。,,,,,,,,,,,,,,,

,,sht_name,排山倒海,"=LOOKUP(ROW(2:9),ROW(1:9),sht_name)",,,,,,,,,,,,,

,"用F9解讀后：{""排山倒海"";""驚濤駭浪"";""鄭重的起誓"";""誒呀我的媽呀"";""葵花點穴"";""1"";""2"";""3""}",,,,,,,,,,,,,,,,

,這就是我們想要的。和sht_name相對比就是少了匯總所以，公式可以這樣：,,,,,,,,,,,,,,,,

,角色,莫小貝,小六,小米,湘玉,流星,小雨,夏東海,,,,,,,,,

,匯總－發(fā)展,409,425,495,399,331,577,382,,,,,,,,,

,,"=SUM(N(INDIRECT(LOOKUP(ROW(2:9),ROW(1:9),sht_name)&""!""&CHAR(65+COLUMN()-2)&""2"")))",,,,,,,,,,,,,,,

,我覺得這個問題是個普遍的問題，因為很多數(shù)據(jù)源都是很不規(guī)范的，所以這是個很有殺傷力的武器。,,,,,,,,,,,,,,,,

,"另外大家要注意的就是這個問題的衍生發(fā)現(xiàn)：＝INDEX(SH