錦子老師

楊樂兒:「錦子老師您好,我有一個收入/支出表格在收支記錄工作表。

我想要在搜尋資料工作表中,撈出收入(A2儲存格)項目,2020(A5儲存格)年8月的記錄,不知公式要怎麼設定?麻煩解惑 感恩,謝謝!」

錦子老師:「這個問題可以使用SUMPRODUCT、INDEX、LARGE與COUNTIFS函數組合來解決,作法如下:」

步驟1:點取C2儲存格輸入公式。

=IFERROR(INDEX(收支記錄!A1:A6,SUMPRODUCT(LARGE((YEAR(收支記錄!A2:A6)=搜尋資料!A5)*(MONTH(收支記錄!A2:A6)=搜尋資料!A7)*ROW(收支記錄!A2:A6)*(收支記錄!B2:B6=搜尋資料!A2),COUNTIFS(收支記錄!A2:A6,">=44044",收支記錄!A2:A6,"<=44074",收支記錄!B2:B6,搜尋資料!A2)+2-ROW())),1),"")

再按CTRL+SHIFT+ENTER鍵完成陣列公式輸入,並將公式複製C2:H6儲存格。

【公式解說】

=IFERROR(INDEX(收支記錄!A1:A6,SUMPRODUCT(LARGE((YEAR(收支記錄!A2:A6)=搜尋資料!A5)*(MONTH(收支記錄!A2:A6)=搜尋資料!A7)*ROW(收支記錄!A2:A6)*(收支記錄!B2:B6=搜尋資料!A2),COUNTIFS(收支記錄!A2:A6,">=44044",收支記錄!A2:A6,"<=44074",收支記錄!B2:B6,搜尋資料!A2)+2-ROW())),1),"")

公式1:(YEAR(收支記錄!A2:A6)=搜尋資料!A5)*(MONTH(收支記錄!A2:A6)=搜尋資料!A7)*ROW(收支記錄!A2:A6)*(收支記錄!B2:B6=搜尋資料!A2)

將收支記錄工作表A2:A6儲存格的年份等於搜尋資料工作表A5儲存格內容,及收支記錄工作表A2:A6儲存格的月份等於搜尋資料工作表A7儲存格內容,及收支記錄工作表B2:B6儲存格的月份等於搜尋資料工作表A2儲存格內容傳回其列號ROW(收支記錄!A2:A6)。

公式2:COUNTIFS(收支記錄!A2:A6,">=44044",收支記錄!A2:A6,"<=44074",收支記錄!B2:B6,搜尋資料!A2)

統計收支記錄工作表A2:A6儲存格日期值介於44044與44074(這個值是2020/8/12020/8/31,可以視條件自行調整該二個值)之間,且收支記錄工作表B2:B6等於A2儲存格內容(收入)的筆數。

公式3:LARGE(公式1,公式2+2-ROW())

傳回公式1中第N(公式2+2-ROW())大的值,這是由於抓取方式是由小到大,故加2減列號將該值愈變愈小。

公式4:SUMPRODUCT(公式3)

傳回公式3的合計。

公式5:INDEX(收支記錄!A1:A6,公式4,1)

傳回A1:A6儲存格範圍中第N(公式4)列,第1欄資料。

公式6:IFERROR(公式5,"")

如果公式5錯誤,則不填入資料,否則填入公式5。

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

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

更多相關文章:請點我