自動統計指定月份指定班級的人數與金額
|
2735 |
自動統計指定月份指定班級的人數與金額 |
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)))
以上就是今天主要學習的知識點，希望對大家有所幫助~~有什麼問題歡迎留言，我會儘量及時的給大家答覆~~
更多相關影片教學：請點我