Excel如何傳回每年年未及季未的匯率
|
3098 |
Excel如何傳回每年年未及季未的匯率 |
許如玥 ：錦子老師您好，請教一下，請問如何將
1.A、B欄中的每年年未日期及匯率抓取到D、E欄。
2. A、B欄中的每年季未日期及匯率抓取到G、H欄。
不知公式要如何設定？
錦子老師：這個問題解決方法如下：
一、每年年未日期及匯率抓取
步驟1：點取D2儲存格，輸入公式：
=IF(SUMPRODUCT(MAX((YEAR($A$2:$A$25)=2019+ROW()-2)*($A$2:$A$25)))=0,"", SUMPRODUCT(MAX((YEAR($A$2:$A$25)=2019+ROW()-2)*($A$2:$A$25))))
按CTRL+SHIFT+ENTER鍵完成陣列公式輸入，再將公式複製到D3:D4儲存格(自行估計要算幾年份來決定範圍)。
【公式說明】
=IF(SUMPRODUCT(MAX((YEAR($A$2:$A$25)=2019+ROW()-2)*($A$2:$A$25)))=0,"", SUMPRODUCT(MAX((YEAR($A$2:$A$25)=2019+ROW()-2)*($A$2:$A$25))))
公式1：YEAR($A$2:$A$25)
傳回A2:A25儲存格中日期的年份。
公式2：MAX(公式1=2019+ROW()-2)*($A$2:$A$25))
如果公式1運算結果的年份等於2019(以此年度開始統計)加上列號減2，傳回符合日期($A$2:$A$25)中最大值，表示該年份最後一天。
公式3：SUMPRODUCT(公式2)
將公式2結果的數值加總。
公式4：IF(公式3=0,"", 公式3)
如果公式3運算結果為0，則不填入資料，否則填入公式3的計算結果。
步驟2：點取E2儲存格，輸入公式：
=IFERROR(VLOOKUP(D2,$A$2:$B$25,2,0),"")
再將公式複製到E3:E4儲存格。
【公式說明】
=IFERROR(VLOOKUP(D2,$A$2:$B$25,2,0),"")
公式1：VLOOKUP(D2,$A$2:$B$25,2,0)
傳回在A2:A25儲存格中那一個儲存格與D2儲存格內容完全相同其B欄內容。
公式2：IFERROR(公式1,"")
如果公式1傳回錯誤訊息，則不填入資料，否則填入公式1結果。
二、年季未日期及匯率抓取
步驟1：點取G2儲存格，輸入公式：
=IF(SUMPRODUCT(MAX((YEAR($A$2:$A$25)=2019+QUOTIENT(ROW(I1)-1,4))*(MONTH ($A$2:$A$25)=CHOOSE(MOD(ROW(I1)-1,4)+1,3,6,9,12))*($A$2:$A$25)))=0,"",SUMPRODUCT (MAX((YEAR($A$2:$A$25)=2019+QUOTIENT(ROW(I1)-1,4))*(MONTH($A$2:$A$25)=CHOOSE (MOD(ROW(I1)-1,4)+1,3,6,9,12))*($A$2:$A$25))))
按CTRL+SHIFT+ENTER鍵完成陣列公式輸入，再將公式複製到G3:G13儲存格(自行估計要算幾年份來決定範圍)。
【公式說明】
=IF(SUMPRODUCT(MAX((YEAR($A$2:$A$25)=2019+QUOTIENT(ROW(I1)-1,4))*(MONTH ($A$2:$A$25)=CHOOSE(MOD(ROW(I1)-1,4)+1,3,6,9,12))*($A$2:$A$25)))=0,"",SUMPRODUCT (MAX((YEAR($A$2:$A$25)=2019+QUOTIENT(ROW(I1)-1,4))*(MONTH($A$2:$A$25)=CHOOSE(MOD(ROW(I1)-1,4)+1,3,6,9,12))*($A$2:$A$25))))
公式1：YEAR($A$2:$A$25)=2019+QUOTIENT(ROW(I1)-1,4)
如果A2:A25儲存格日期 的年份等於2019(以此年度開始統計)加上I1儲存格列號減1除以4的整數部份(QUOTIENT(ROW(I1)-1,4))，傳回欲抓取年份。
公式2：MONTH($A$2:$A$25)=CHOOSE(MOD(ROW(I1)-1,4)+1,3,6,9,12)
傳回A2:A25儲存格中日期的月份若等於I1儲存格列號減1除以4的餘數部份(MOD(ROW(I1)-1,4))加1對應的值(3,6,9,12)，這是為了抓季末日期。
公式3：MAX((公式1)*(公式2)*($A$2:$A$25))
傳回符合公式1與公式2條件的日期最大值。
公式4：SUMPRODUCT(公式3)
將公式3結果的數值加總。
公式5：IF(公式4=0,"", 公式4)
如果公式4運算結果為0，則不填入資料，否則填入公式4的計算結果。
步驟2：點取H2儲存格，輸入公式：
=IFERROR(VLOOKUP(G2,$A$2:$B$25,2,0),"")
再將公式複製到H3:H13儲存格。
【公式說明】
=IFERROR(VLOOKUP(G2,$A$2:$B$25,2,0),"")
公式1：VLOOKUP(G2,$A$2:$B$25,2,0)
傳回在A2:A25儲存格中那一個儲存格與G2儲存格內容完全相同其B欄內容。
公式2：IFERROR(公式1,"")
如果公式1傳回錯誤訊息，則不填入資料，否則填入公式1結果。
以上就是今天主要學習的知識點，希望對大家有所幫助~~有什麼問題歡迎留言，我會儘量及時的給大家答覆~~
更多相關影片教學：請點我
更多相關文章：請點我