錦子老師

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

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

2、在K2儲存格輸入年份數值。

3、在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)

點取K2儲存格輸入年份。

點取M2儲存格輸入公式：

=SUMPRODUCT((YEAR(INDIRECT("R3C"&TEXT(1+(K1-1)*3,0)&":"&"R1000C"& TEXT(1+(K1-1)*3,0),FALSE))=$K2)*(MONTH(INDIRECT("R3C"&TEXT(1+(K1-1)*3,0)&":" &"R1000C"&TEXT(1+(K1-1)*3,0),FALSE))=$L2)*(INDIRECT("R3C"&TEXT(2+(K1-1)*3,0)& ":"&"R1000C"&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)。

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

再乘上對應的隔壁欄人數值(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$1000,$D$3:$D$1000,$G$3:$G$1000))=$L$2)*(CHOOSE($K$1,B3:B100,E3:E1000,H3:H1000))*(YEAR(CHOOSE($K$1,$A$3:$A$1000,$D$3:$D$1000,$G$3:$G$1000))=$K$2))

點取N2儲存格輸入公：

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

公式也可更改為：

=SUMPRODUCT((YEAR((CHOOSE($K$1,$A$3:$A$1000,$D$3:$D$1000,$G$3:$G$1000)))=$K$2)*(MONTH((CHOOSE($K$1,$A$3:$A$1000,$D$3:$D$1000,$G$3:$G$1000)))=$L$2)*(CHOOSE($K$1,C3:C1000,F3:F1000,I3:I1000)))

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

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

部落格相關範例