錦子老師

TOBY：「早安，錦子老師，如何在固定欄位下，利用多條件找到相對的數值，目前卡在班級上，因為它是合併儲存格。

1、在K1儲存格輸入數值，則L1會顯示要抓取的班別。

2、在L2儲存格輸入月份數值，則M2會顯示該班級人數合計，N2會顯示該班級金額合計。

請問要如何設定公式完成。」

錦子老師：「這裡面有各種作法，請參考：

點取L2儲存格輸入公式：

=INDIRECT("R1C"&TEXT(1+(K1-1)*3,0),FALSE)

【公式解說】

首先將K1儲存格的值-1乘上3再加1，算出要抓第幾欄的資料，再透過TEXT函數將其變為文字類型(TEXT(1+(K1-1)*3,0))。

再透過INDIRECT函數將R1C字串加上傳回的欄號數，傳回代表的儲存格(R列號C欄號交叉的儲存格)內容依照R1C1模式(INDIRECT("R1C"&TEXT(1+(K1-1)*3,0),FALSE))。

公式也可更改為：=CHOOSE(K1,A1,D1,G1)

點取M2儲存格輸入公式：

=SUMPRODUCT((MONTH(INDIRECT("R3C"&TEXT(1+(K1-1)*3,0)&":"&"R153C"& TEXT(1+(K1-1)*3,0),FALSE))=$L2)*(INDIRECT("R3C"&TEXT(2+(K1-1)*3,0)&":"&"R153C"& TEXT(2+(K1-1)*3,0),FALSE)))

【公式解說】

首先將K1儲存格的值-1乘上3再加1，算出要抓第幾欄的資料，再透過TEXT函數將其變為文字類型(TEXT(1+(K1-1)*3,0))。

再透過INDIRECT函數將R3C/R153字串加上傳回的欄號數，傳回要運算的儲存格範圍(R3C?:R153C?)依照R1C1模式(INDIRECT("R3C"&TEXT(1+(K1-1)*3,0)&":"&"R153C"& TEXT(1+(K1-1)*3,0),FALSE)。

再傳回運算範圍內日期的月份等於L2儲存格內容的儲存格(MONTH(INDIRECT("R3C"& TEXT(1+(K1-1)*3,0)&":"&"R153C"& TEXT(1+(K1-1)*3,0),FALSE))=$L2)。

再乘上對應的隔壁欄人數值(INDIRECT("R3C"&TEXT(2+(K1-1)*3,0)&":"&"R153C"& TEXT(2+(K1-1)*3,0),FALSE))。

最後透過SUMPRODUC函數計算總和。

公式也可更改為：

=SUMPRODUCT((MONTH(CHOOSE($K$1,$A$3:$A$153,$D$3:$D$153,$G$3:$G$153))=$L$2)*(CHOOSE($K$1,B3:B153,E3:E153,H3:H153)))

【公式解說】

根據K1儲存格的值，傳回對應的儲存格範圍(CHOOSE($K$1,$A$3:$A$153,$D$3:$D$153, $G$3:$G$153))。

再傳回對應的儲存格範圍日期的月份等於L2儲存格內容(MONTH(CHOOSE($K$1,$A$3: $A$153,$D$3:$D$153,$G$3:$G$153))=$L$2)。

再乘上對應的隔壁欄人數值((CHOOSE($K$1,B3:B153,E3:E153,H3:H153)))。

最後透過SUMPRODUC函數計算總和。

點取N2儲存格輸入公式：

=SUMPRODUCT((MONTH(INDIRECT("R3C"&TEXT(1+(K1-1)*3,0)&":"&"R153C"& TEXT(1+(K1-1)*3,0),FALSE))=$L2)*(INDIRECT("R3C"&TEXT(3+(K1-1)*3,0)&":"&"R153C"& TEXT(3+(K1-1)*3,0),FALSE)))

公式也可更改為：

=SUMPRODUCT((MONTH((CHOOSE($K$1,$A$3:$A$153,$D$3:$D$153,$G$3:$G$153)))=$L$2)*(CHOOSE($K$1,C3:C153,F3:F153,I3:I153)))

以上就是今天主要學習的知識點，希望對大家有所幫助~~有什麼問題歡迎留言，我會儘量及時的給大家答覆~~

更多相關影片教學：請點我

部落格相關範例