問題:如何能將篩選後的值加總填入指定儲存格

厄洛斯
個人積分：5分
文章編號：61566127

5分

樓主

2016-09-08 3:42

我的工作每個月都要從總表中利用樞紐分析逐項篩選加總每個單項的數量,總計420項,由於每

個月都要花上一段時間藉由樞紐分析結果登記在紙本上,再由紙本騰到電腦中,因此想看看有

沒有什麼方法可以直接讓電腦去篩選填入指定儲存格中.

問題:如何能將篩選後的值加總填入指定儲存格

(總表)

我的想法是因為總表是每天都有專人會依據當天報表輸入新資料:

1.420項每天都會不定期出現,可能3~5項,可能10幾項,就單項而言每個月最多會出現4次,也可能

某個Part No.連著數月都沒有任何資料.

2.若以總表篩選指定Part No.、YEAR、Month(或Date),例如:將同一時段如2015/JAN,Part

No.B24985,自總表篩選出來並加總,並將結果直接填入指定儲存格,由於2015/JAN並沒有這個

Part No.,因此該儲存格為空白.

問題:如何能將篩選後的值加總填入指定儲存格

(合計報表)

3.以此類推,每個Part No.每個月主動加總填入指定儲存格,若當月沒有資料,則該儲存格空白.

4.總表與合計報表是分開不同的二個工作表!

我用下圖公式試著完成上述想法,但是卻出現錯誤,(如下圖)

問題:如何能將篩選後的值加總填入指定儲存格

請問我要用什麼公式才能完成我要的結果(我用的是Excel 2010版)

公式:

=IFERROR(SUMPRODUCT(OFFSET('母體資料庫-(2015~2018)'!C2:I30118,SMALL(IF(年度總計!C1='

母體資料庫-(2015~2018)'!G2:G30118,ROW('母體資料庫-

(2015~2018)'!I2:I30118),FALSE),COLUMN(A:A))-1,,,),))))

2016-09-08 3:42 發佈

文章關鍵字問題指定儲存格

Mystique Hsiao

Mystique Hsiao
個人積分：7527分
文章編號：61580622

7527分

2樓

2016-09-09 4:24

1.您的陣列公式用法有誤，造成參數不足，所以出現錯誤（其實不需用到陣列公式）！
2.總表中的 A, B, C 欄是多餘的，建議去掉以簡化資料內容！

以下範例請參考：

C2：
=IF(SUMPRODUCT(--(YEAR(OFFSET(總表!$A$1, 1, 0, COUNTA(總表!$A:$A)))=$A2),
--(TEXT(OFFSET(總表!$A$1, 1, 0, COUNTA(總表!$A:$A)), "mmm")=$B2),
--(OFFSET(總表!$B$1, 1, 0, COUNTA(總表!$A:$A))=C$1),
OFFSET(總表!$C$1, 1, 0, COUNTA(總表!$A:$A)))=0, "",
SUMPRODUCT(--(YEAR(OFFSET(總表!$A$1, 1, 0, COUNTA(總表!$A:$A)))=$A2),
--(TEXT(OFFSET(總表!$A$1, 1, 0, COUNTA(總表!$A:$A)), "mmm")=$B2),
--(OFFSET(總表!$B$1, 1, 0, COUNTA(總表!$A:$A))=C$1),
OFFSET(總表!$C$1, 1, 0, COUNTA(總表!$A:$A))))

ps.
1.
因為要讓結果為 0 的欄位不顯示，所以公式較長！
（其實後半段公式與前半段相同）
2.
為了避免日後輸入資料變多時要再修改公式內容，
所以使用了 OFFSET 函數，否則公式會短一些！