原創作者： 盧子 轉自：Excel不加班

學員的問題，要計算每個周期的驗收費用。

死了不少腦細胞，終于研究出來。

=IFERROR(VLOOKUP($C3,'發電量-萬千瓦時'!$D:$CS,MATCH(TEXT($I3,"yymm")*1,'發電量-萬千瓦時'!$D$3:$CS$3,0) COLUMN(A1)*12-12,0)/DAY(EOMONTH($I3,0))*(DAY(EOMONTH($I3,0))-DAY($I3) 1) SUM(OFFSET('發電量-萬千瓦時'!$D$3,MATCH($C3,'發電量-萬千瓦時'!$D$4:$D$80,0),MATCH(TEXT($I3,"yymm")*1,'發電量-萬千瓦時'!$D$3:$CS$3,0) COLUMN(A1)*12-12,1,11)) VLOOKUP($C3,'發電量-萬千瓦時'!$D:$CS,MATCH(TEXT($I3,"yymm")*1,'發電量-萬千瓦時'!$D$3:$CS$3,0) COLUMN(A1)*12,0)/DAY(EOMONTH($I3,0))*(DAY($I3)-1),"")

公式超級複雜，今天就拿裡面一部分内容進行說明。

原表格比較複雜，這裡進行簡化，發電這個表，第一行4位數是代表年月，2位數的年 2位數的月。

現在要根據客戶、日期兩個條件，統計第1年到第4年的對應金額。

現在以盧子4、2015/7/31這個來進行說明如何計算。先看客戶在A列中第幾行，再看日期在第一行中第幾列，知道行列數後，将右邊這12個月的數據求和就是第1年。第2年就再向右12列，再求12個月，依次類推。

這已經在原來的基礎上，簡單了一半以上，希望你沒看暈。

獲取排位，可以用MATCH。

客戶第幾行：

=MATCH($A2,發電!$A$2:$A$15,0)

這已經在原來的基礎上，簡單了一半以上，希望你沒看暈。

獲取排位，可以用MATCH。

客戶第幾行：

=MATCH($A2,發電!$A$2:$A$15,0)

TEXT将日期轉變成4位數的年月格式，因為是文本格式，在前面加--轉換成數值格式。

獲取行列的交叉的第一個單元格的值。

=OFFSET(發電!$A$1,G2,H2)

獲取行列的交叉的第一個單元格的值，再向右得到12列。不能直接回車，隻能放在編輯欄，因為這是12個金額。一個單元格容納不了，會出錯。

=OFFSET(發電!$A$1,G2,H2,1,12)

再對這12個數字進行求和。

=SUM(OFFSET(發電!$A$1,G2,H2,1,12))

再将原來輔助列的公式，替換進去，這樣第1年的總金額就出來了。

=SUM(OFFSET(發電!$A$1,MATCH($A2,發電!$A$2:$A$15,0),MATCH(TEXT($B2,"yymm")*1,發電!$B$1:$BU$1,0),1,12))

第2年，在這個基礎上向右12行，第3年，在這個基礎上向右24行……

這個可以借助COLUMN*12獲取。

=COLUMN(A1)*12

第1年不需要向右，也就是0，将公式略作改動就可以從第1年開始向右拖動公式。

=COLUMN(A1)*12-12

最後，将這個也替換進去，大功告成。

=SUM(OFFSET(發電!$A$1,MATCH($A2,發電!$A$2:$A$15,0),MATCH(TEXT($B2,"yymm")*1,發電!$B$1:$BU$1,0) COLUMN(A1)*12-12,1,12))

即便是高手，寫超級複雜的公式也不是一次性寫完，而是先完成某一部分公式，再将這些零散的公式拼湊而成。

,

更多精彩资讯请关注tft每日頭條，我们将持续为您更新最新资讯!

查看全部