多工作表動態累計求和【Excel分享】

06-09

多工作表動態累計求和

大家好，今天和大家分享「多工作表動態累計求和",對於跨工作表求和對於初級學員，新手來說是一個棘手的問題，跨工作簿就更不說了，中級用戶也搞不定，因為跨工作簿要用到vba和透視里的SQL這些知識，也許你會說，這些我都沒有聽過，說明你的學習Excel路還很長，如果你天天要和Excel打交道2個小時以上的朋友，建議好好學學Excel

一、這問朋友的提問

A列公式中的求和怎麼根據B1單元格月份的變動來求相應的累計值？

如：B2=2月，A列求的累計值是1月：2月這工作表之間的合計值；B2=5月時，A列求的累計值是1月：5月這工作表之間的合計值，具體效果看下面的動畫

二、方法1：用計算器相加

這種方法，來讀今天這篇文章的肯定沒有了，但是現實生活中真有這樣的人，他不知Excel這個電子表格可以計算，他一這打開Excel，一邊用手指敲著計算器。

三、方法2：用公式一個一個引用各個工作表

這一類朋友可以占的比例就多了，輸入一個等號，然後一個一個工作表去引用

1、公式截圖

2、公式

="1月"!A1 "2月"!A1 "3月"!A1 "4月"!A:A "5月"!A1

3、公式解釋

如果單純是這樣求和，這樣做也是可以的，但是如果再什麼條件用這種方法就沒有辦法實現了

這種方法，當然也實現不了動態，也就是不能根據B1單元格值而改變求和的工作表數目，只能手動重新修改一個公式，再填充公式，它的缺點顯而易見。

四、方法3：N降維實現

1、公式截圖

2、公式

=SUM(N(INDIRECT(ROW(INDIRECT("1:"&SUBSTITUTE($B$1,"月","")))&"月!A"&ROW(A1))))

3、公式解釋

這是數組公式，把公式輸入之好，游標定位到編輯欄里，然後三鍵一齊下Ctrl Shift 回車

INDIRECT("1:"&SUBSTITUTE($B$1,"月",""))把月替換掉得到月份的數字這樣和到文本「1:5",我們在它外面嵌套一個indirect函數就變成了1到5行的單元格區域，記得是1到5行的單元格區域，得到是單元格區域，重要的事我說三遍了。我們在它外面再嵌套一個row函數，得到1到5行的的行號：1;2;3;4;5

ROW(INDIRECT("1:"&SUBSTITUTE($B$1,"月","")))&"月!A"&ROW(A1)這個返回的是{"1月!A1";"2月!A1";"3月!A1";"4月!A1";"5月!A1"}，得到每個工作表的A1單元格文本，記得是文本字元串，只有再給它嵌套一個indirect函數，才會變成每個工作表的A1單元格，這個是動態的，下拉A1會變成A2，得到公式INDIRECT(ROW(INDIRECT("1:"&SUBSTITUTE($B$1,"月","")))&"月!A"&ROW(A1))

為什麼要在indirect函數前面加N函數呢？，因為indirect函數這裡構建了多維，所以要通過N函數來降維

最後用sum函數求和得到我們想要結果

五、方法4：Sumif降維實現

1、公式截圖

2、公式

=SUM(SUMIF((INDIRECT(ROW(INDIRECT("1:"&SUBSTITUTE($B$1,"月","")))&"月!A"&ROW(A1))),"<>0"))

3、公式解釋

大部分和第3種解法差不多，也是數組公式

sumif這裡也是起到一個降維作用，相當於N函數

sumif函數第1參數：條件所在的區域是(INDIRECT(ROW(INDIRECT("1:"&SUBSTITUTE($B$1,"月","")))&"月!A"&ROW(A1)))得到單元格區域

sumif函數第2參數：條件是不"<>0",因為我們這裡是求和，這樣設置和條件，不管它是負數，還是正數都可以

sumif函數第3參數和第1參數如果是一樣的，第3參數可以不寫。

六、小結

前面2種方法不可取，後面2種方法才體現Excel函數博大精深，這話一點也不假，當然，如果你確實不會函數，那也只能用前面2種方法，當然兩者之間辦事效率就不用我說了