Question to say "I can!"

用SUMIF和SUMPRODUCT一起對多表進行單條件數據匯總

2014-10-21

文章來自李則見的微信,喜歡excel的好學者,可以用微信關注李則見:excelpptpx。

SUMIF和SUMPRODUCT在統計當中,可是能幫大忙的。

內容如下:

1,現在有7個工作表,每個表的數據結構是一樣的,都有5列,分別是:商品、日期、件、單價、營業額;每個表的商品的個數不同,行數也不同。比如其中一個工作表的截圖是這樣的:

2,如果你只想統計工作表“1”里的每個商品的營業額,你只需要整理出各個商品的列表,然后用SUMIF函數就可以求出來了。

=SUMIF('1'!A:A,$A2,'1'!E:E)

3,但是這樣統計出來的只是工作表“1”里的數據,工作表“2”到“7”里的數據沒有統計出來。一般來說大家可能想到先用SUMIF分別把7個表里的數據分別匯總出來,然后把這7個SUMIF相加在一起,這樣做本身是沒錯,但是如果工作表不止7個,有20個呢,或者50個呢,這時候如果把那么多SUMIF相加也不是件快樂的事情。大部分人是這么做的:

=SUMIF('1'!A:A,$A2,'1'!E:E)+SUMIF('2'!A:A,$A2,'2'!E:E)+...+SUMIF('7'!A:A,$A2,'7'!E:E)

4,上面這種多個SUMIF相加的方法其實可以用一種新的思路去替代。當然這種思路,你不喜歡,你還可以沿用上面的那種多個SUMIF相加的方法。新方法引進INDIRECT函數。如果有7個工作表的數據相加求和,那么可以把原先的SUMIF函數改造成這樣:

=SUMIF(INDIRECT(ROW($1:$7)&"!A:A"),

$A2,INDIRECT(ROW($1:$7)&"!e:e"))

其中,ROW($1:$7)代表工作表“1”到“7”的表名。而ROW($1:$7)&"!A:A",就代表工作表“1”到“7”里的A列這個區域。但是這種關于單元格區域引用的表達方式,不是標準的規范的引用方式,是不被Excel所認可的。要想這種非常規的引用形式被Excel認可,就必須用INDIRECT函數包裝下。所以SUMIF函數的第一個參數“條件區域”這里用到了INDIRECT函數:INDIRECT(ROW($1:$7)&"!A:A")。

同理,SUMIF函數的第二個參數求和區域這里也用到了INDIRECT函數進行包裝處理。

5,比如你在B2單元格輸入上述SUMIF函數公式,回車確認后的結果顯示的結果卻并不是你想要的多表求和的結果。其實結果已經匯總出來的了,你只需要在B2單元格的編輯欄,按F9鍵就可以看到下面這樣的結果值:{15516;53805;16726;20102;19780;17635;34104}。這里面有7個值,分別對應7個表的某個商品的求和。但是這7個值怎么求出他們的總和呢。這時候就可以用SUMPRODUCT函數對這里面的7個值求其總和。

在編輯欄按F9出現如下結果(只是查看一下,然后不要回車,直接按ESC返回到原來的函數公式)

所以,最終的函數公式是:

你看明白了嗎。動手操作,才是王道~~~

作者:admin | Categories:技術人生 | Tags:

發表評論

電子郵件地址不會被公開。 必填項已用*標注

*

澳洲f1赛车b盘开奖套路 欧美一级毛片免费高清 青海十一选五开奖结果一定牛 东方6十1开奖结果今天晚上 4个人用扑克牌打麻将 揭日本av女优的终身悲惨命运 世界杯篮球即时比分今天 武汉酒店按摩服务 安徽25选5 幸运龙宝贝 广东十一选五360 7日nba灰熊vs篮网视频 qq二人麻将外挂 全民老友内蒙古麻将 二四六天天好彩资料 澳洲幸运10老群 有板深雪种子下载