你好，我是劉卓。歡迎來到我的公號，excel函數(shù)解析。之前和你分享過隔列求和的方法，今天來分享下有條件的隔列求和的方法。下面來看下數(shù)據(jù)源和結(jié)果。
下圖是各買家分期付款買產(chǎn)品的應(yīng)付款日期和金額，求截止今日應(yīng)付款的總金額。以丙為例，截止今天2020-6-1應(yīng)付款的總金額是4000+4000，共8000。因?yàn)樽詈?個(gè)日期2020-6-15大于今天的日期，所以不算。
如果沒有截止今天的時(shí)間限制，就是個(gè)隔列求和的問題。用之前的方法輕松解決，比如用下面的2個(gè)公式。
=SUMIF(B$2:G$2,"*金額*",B3:G3)
=SUMPRODUCT(MOD(COLUMN(B:G),2)*B3:G3)
但是有了截止今天的時(shí)間限制，就多了個(gè)條件，問題就稍有點(diǎn)復(fù)雜了。其實(shí)只要掌握了方法，找到了規(guī)律，解這種題還是不難的。關(guān)鍵點(diǎn)就兩個(gè)字“錯(cuò)位”。在H3單元格輸入下面的公式，向下填充。
=SUMIFS(C3:G3,C$2:G$2,"*金額*",B3:F3,"<="&TODAY())
要注意sumifs各區(qū)域的錯(cuò)位對應(yīng)關(guān)系，如下圖紅色框所示。由于條件區(qū)域1對應(yīng)的條件1是包含金額，所以先把不含金額的日期排除掉，也就是黃色的區(qū)域被排除掉。
剩下的還要看條件區(qū)域2的日期是否小于等于today()，大于today()的也被排除掉。最后對同時(shí)滿足兩個(gè)條件的求和就得到了結(jié)果。
公式還可以寫為下面的形式：
=SUMIFS(C3:G3,B$2:F$2,"*日期*",B3:F3,"<="&TODAY())
第2種，sumproduct多條件求和
在H3單元格輸入下面的公式，向下填充。
=SUMPRODUCT(MOD(COLUMN(A:E),2)*(B3:F3<=TODAY())*C3:G3)
這個(gè)公式和第1種sumifs的思路是一樣的，還是錯(cuò)位的原理，只不過換了個(gè)函數(shù)，換了種寫法。
MOD(COLUMN(A:E),2)這部分用列號除以2求余數(shù)，返回的結(jié)果為{1,0,1,0,1}。B3:F3<=TODAY()這部分判斷B3:F3的區(qū)域是否小于等于today的日期，返回的結(jié)果為{TRUE,TRUE,TRUE,TRUE,TRUE}。MOD(COLUMN(A:E),2)*(B3:F3<=TODAY())*C3:G3這3部分相乘返回的結(jié)果為{2500,0,2600,0,2700}。最后用sumproduct求和。=SUM((N(OFFSET(A3,,{1,3,5}))<=TODAY())*N(OFFSET(A3,,{2,4,6})))
OFFSET(A3,,{1,3,5})這部分以A3單元格為基點(diǎn)，分別向右偏移1,3,5列，得到了由B3，D3，F(xiàn)3組成的三維引用，用n函數(shù)降維得到了應(yīng)付款日期的序列值數(shù)組，結(jié)果為{43724,43784,43876}。N(OFFSET(A3,,{2,4,6}))這部分得到了應(yīng)付款金額的數(shù)組，結(jié)果為{2500,2600,2700}。N(OFFSET(A3,,{1,3,5}))<=TODAY()這部分判斷應(yīng)付款日期是否小于等于今天的日期，成立的返回true，不成立的返回false。結(jié)果為{TRUE,TRUE,TRUE}。(N(OFFSET(A3,,{1,3,5}))<=TODAY())*N(OFFSET(A3,,{2,4,6}))這兩部分相乘，得到的結(jié)果為{2500,2600,2700}。意思就是如果應(yīng)付款日期小于等于今天的日期，就返回對應(yīng)的應(yīng)付款金額，否則就返回0。最后用sum求和。https://pan.baidu.com/s/1JBI5g00i-x6EEQiN1DRXvQ
本站僅提供存儲服務(wù)，所有內(nèi)容均由用戶發(fā)布，如發(fā)現(xiàn)有害或侵權(quán)內(nèi)容，請
點(diǎn)擊舉報(bào)。
