EXCEL速成（應用篇）：控件+窗體+條件語句等運用

今天為大家分享一個案例：遠期匯率的EXCEL計算模型設計

當然如果想學習更多的資源，看完后推薦您學習：

1.遠期匯率計算原理

遠期匯率、即期匯率與利率之間存在著內(nèi)在聯(lián)系，通過利率平價理論可以從即期匯率與兩種貨幣利率差異中，計算出一種貨幣對另一種貨幣升貼水的具體數(shù)字，最終推算出遠期匯率。假設Ef表示遠期匯率，Es表示即期匯率，N表示期限，Ia表示A國利率水平（直接標價法國對應的利率水平），Ib表示B國利率水平（間接標價法國對應的利率水平），計算公式如下：

[(Ef-Es)/Es]*12/N=Ia-Ib

針對上述公式分析，最終計算的目標值為遠期匯率Ef，而Es、Ia、Ib為已知數(shù)據(jù)，不妨將N值（月數(shù)）區(qū)間設定為1-36個月，以便于設計遠期匯率的EXCEL計算模型。

2．遠期匯率的EXCEL計算模型設計

2.1 選項與智能文本的設計

2.1.1 設計動態(tài)日期與靜態(tài)元素

（1）在C2單元格鍵入動態(tài)顯示當前日期的函數(shù)公式“=Today( )”，并將C2：C3合并單元區(qū)域；

（2）在A1、A10:D10、A19:單元格中鍵入本模型的靜態(tài)文字。合并A1:E1、A19:B19單元區(qū)域。

（3）選定A3:E24單元區(qū)域，按下Ctrl鍵，右擊菜單“設置單元格格式”組中“圖案”，選擇“橙色”，同時將B13、B16、D16、D19、D20、D23處單元區(qū)域設置為“白色”。

2.1.2 設計分組框窗體控件

（1）單擊Excel選擇“視圖/工具欄/窗體”菜單命令

（2）單擊“窗體工具欄”中的“分組框”按鈕，此時鼠標變“+”字狀，在A3單元格中拖出一個控件；將其標簽修改為“幣種1（單位幣種）”；接著右擊該控件選擇“設置控件格式”命令，單擊“三維陰影”選項。

（3）右擊該分組框選擇“復制”命令、在A6單元格右擊選擇“粘貼”命令；再將該控件標簽修改為“幣種2（標價數(shù)值）”；用同樣方法A12、A15、A18、A22分組框，并且修改控件標簽。

（4）點擊分組框拖動調(diào)整其大小、位置。（如圖1所示）

2.1.3 設計單選按鈕窗體控件

（1）首先，在幣種1（單位幣種）“窗體”中點擊“選項按鈕”，此時鼠標變“+”字狀，在A4單元格中拖出一個單選按鈕；按下鍵盤上的Ctrl鍵并單擊該控件進入編輯狀態(tài)，將其標簽修改為“美元”；通過控件上的小圓圈調(diào)整其大小、位置；右擊該控件選擇“設置控件格式”，在“控制”卡的“單元格鏈接”中鍵入“$A$11”選定“三維陰影”選項。

其次，復制并粘貼為兩個單選按鈕，修改控件的標簽為“英鎊”、“歐元”。

最后，將三個單選按鈕放置于“幣種1（單位幣種）”分組框內(nèi)。若選擇“美元”選項，A11單元格將顯示“1”；選擇“英鎊”、“歐元”選項則分別顯示“2”或“3”，這是單選按鈕的設計代碼。

（2）運用上述方法，首先，在幣種2（標價數(shù)值）“窗體”中點擊“選項按鈕”，此時鼠標變“+”字狀，在A7單元格中拖出一個單選按鈕；按下鍵盤上的Ctrl鍵并單擊該控件進入編輯狀態(tài)，將其標簽修改為“人民幣”；通過控件上的小圓圈調(diào)整其大小、位置；右擊該控件選擇“設置控件格式”，在“控制”卡的“單元格鏈接”中鍵入“$B$11”選定“三維陰影”選項。

其次，復制并粘貼為兩個單選按鈕，修改控件的標簽為“日元”、“港幣”。

最后，將三個單選按鈕放置于“幣種2（標價數(shù)值）”分組框內(nèi)。若選擇“人民幣”選項，B11單元格將顯示“1”；選擇“日元”、“港幣”選項則分別顯示“2”或“3”。

（3）設計單元格連接，在C11單元格中鍵入“=A11”, D11單元格中鍵入“=B11”。

2.1.4選項代碼值轉換為智能文本

在A13單元格運用條件函數(shù)IF公式，將設計代碼轉換為智能文本：“=IF(A11=1,'美元/',IF(A11=2,'英鎊/','歐元/'))”，如圖2部的編輯框所示。公式的含義是：設計代碼B11為1時，A13則顯示“美元”，若為2則顯示“英鎊”，否則顯示為“歐元”。

運用上述方法，分別在B13、A16、C16、A23、A24中輸入以下代碼：

B13=IF(B11=1,'人民幣',IF(B11=2,'日元','港元'))

A16 =IF(C11=1,'美國',IF(C11=2,'英國','歐盟'))

C16 =IF(D11=1,'中國',IF(D11=2,'日本','香港'))

A23= IF(A11=1,'美元/',IF(A11=2,'英鎊/','歐元/'))

A24= IF(B11=1,'人民幣',IF(B11=2,'日元','港元'))

2.2設計滾動條控件

（1）單擊“窗體工具欄”中的“滾動條”按鈕，在C19單元格中拖動一個控件，并調(diào)整控件的大小、位置。

（2）右擊C19單元格的滾動條選擇“設置控件格式”命令，在“控制”卡中鍵入最小值“1”、最大值“36”、步長“1”、頁步長“1”、單元格鏈接“$C$20”（如圖2所示）。

2.3 設計遠期匯率計算法則

首先，不妨在單元格設定已知數(shù)據(jù)，即C13=6.8855，B16=6.18%，D16=3.56%，同時幣種1選擇“美元”，幣種2選擇“人民幣”。

接著，依據(jù)遠期匯率計算公式：[(Ef-Es)/Es]*12/N=Ia-Ib

計算遠期匯率Ef（所在單元格C23）。根據(jù)已知條件將字母替換成單元格，即：

C23=(D16-B16)*C13*(C20/12)+C13

最后,根據(jù)公式換算，在C23單元格鍵入“=(D16-B16)*C13*(C20/12)+C13”。同時，將輔助數(shù)據(jù)9—11行進行隱藏，模型設計完畢。

由于EXCEL具有自動計算功能，當初始數(shù)據(jù)發(fā)生變化，就能自動計算其他數(shù)據(jù)。該計算方法可以推廣到金融教學中將非常方便和快捷。今天為大家分享到這里，相信對你有用。