3098Excel如何傳回每年年未及季未的匯率－錦子老師

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儲存格(自行估計要算幾年份來決定範圍)。