錦子老師

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

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

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

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

=IFERROR(INDEX(收支記錄!A$1:A$6,SUMPRODUCT(LARGE((YEAR(收支記錄!$A$2:$A$6)=搜尋資料!$A$5)*(MONTH(收支記錄!$A$2:$A$6)=搜尋資料!$A$7)*ROW(收支記錄!$A$2:$A$6)*(收支記錄!$B$2:$B$6=搜尋資料!$A$2),COUNTIFS(收支記錄!$A$2:$A$6,">=44044",收支記錄!$A$2:$A$6,"<=44074",收支記錄!$B$2:$B$6,搜尋資料!$A$2)+2-ROW())),1),"")

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

【公式解說】

=IFERROR(INDEX(收支記錄!A$1:A$6,SUMPRODUCT(LARGE((YEAR(收支記錄!$A$2:$A$6)=搜尋資料!$A$5)*(MONTH(收支記錄!$A$2:$A$6)=搜尋資料!$A$7)*ROW(收支記錄!$A$2:$A$6)*(收支記錄!$B$2:$B$6=搜尋資料!$A$2),COUNTIFS(收支記錄!$A$2:$A$6,">=44044",收支記錄!$A$2:$A$6,"<=44074",收支記錄!$B$2:$B$6,搜尋資料!$A$2)+2-ROW())),1),"")

公式1：(YEAR(收支記錄!$A$2:$A$6)=搜尋資料!$A$5)*(MONTH(收支記錄!$A$2:$A$6)=搜尋資料!$A$7)*ROW(收支記錄!$A$2:$A$6)*(收支記錄!$B$2:$B$6=搜尋資料!$A$2)

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

公式2：COUNTIFS(收支記錄!$A$2:$A$6,">=44044",收支記錄!$A$2:$A$6,"<=44074",收支記錄!$B$2:$B$6,搜尋資料!$A$2)

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

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

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

公式4：SUMPRODUCT(公式3)

傳回公式3的合計。

公式5：INDEX(收支記錄!A$1:A$6,公式4,1)

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

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

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

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

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

更多相關文章：請點我