實例13制作貸款購車計算器—插入PMT函數—插入與設置形狀—保護工作表—保存模板

學習情境

貸款購物在當今已成為一種時尚，對于無大量存款，但每月還小額款項基本沒問題的個人或家庭而言，房貸、車貸等分期購物方式較好地解決了資金流動的難題。

小張也想根據自己目前的經濟狀況買車，希望自己設計一個貸款購車計算器，從而衡量哪種貸款方式較為合適。

編排效果

掌握技能

通過本實例，將學會以下技能：

插入PMT函數。

插入與設置形狀。

保護工作表。

保存模板。

設置文檔結構

1.啟動Excel程序，打開自動命名為“工作簿1”的Excel文檔，按照圖中所示內容輸入相應文字。1.輸入

2.選中列B至列F。

3.?右鍵單擊選中列的列標處。

4.在彈出的快捷菜單中，單擊“列寬”命令，打開“列寬”對話框。

5.輸入“列寬”的值“15”，單擊“確定”按鈕返回工作表。2.選中4.單擊3.右擊5.輸入

6.?按下“Ctrl”鍵，選中以下單元格：C6、C10:C11、C13:C16。

7.右鍵單擊選中區域，在彈出的快捷菜單中，單擊“設置單元格格式”命令，打開“設置單元格格式”對話框。

8.單擊“數字”選項卡→“貨幣”分類，再單擊“小數位數”右側的向下箭頭，調整小數位數至“0”。6.選中7.單擊8.單擊

添加下拉列表

為了方便使用，可將單元格設置成具有下拉列表的形式，不需輸入，僅通過選擇相應的項即可將內容顯示至表單中。

1.對B2:F2單元格執行“合并后居中”操作。

2.在F4單元格中，輸入“5.33%”；在C6單元格中，輸入“150000”。

3.單擊C7單元格。

4.單擊“數據”選項卡→“數據工具”組→“數據有效性”按鈕。

5.在打開的“數據有效性”對話框中，單擊“設置”選項卡→“允許”欄下方的列表框箭頭，選中“序列”項。

6.在“來源”欄下方的編輯框中輸入“30%,40%,50%,60%”。

7.單擊C8單元格。

8.單擊“數據”選項卡→“數據工具”組→“數據有效性”按鈕。

9.在“數據有效性”對話框中，選中“序列”項，在“來源”處輸入“1,2,3,4,5”。1.合并2.輸入3.單擊4.單擊5.選中6.輸入7.單擊9.輸入8.單擊

引用動態數據

在單元格中輸入的數據通常是不可變的，為了提高數據的可重用性，一般使用引用單元格地址的方式進行計算，當被引用單元格的數據發生變化時，其他數據也能夠動態地更新，從而做到輸入較少數據即可進行大量運算的效果。

1.單擊C10單元格，在編輯欄中，輸入“=”號，激活公式編輯狀態。

2.單擊C6單元格，引用其地址至公式中，繼續輸入“*”號，再單擊C7單元格，引用其地址至公式中。單擊“輸入”按鈕完成計算。2.引用1.單擊

3.單擊C11單元格，在編輯欄中，輸入“=”號，激活公式編輯狀態。

4.單擊C6單元格，引用其地址至公式中，繼續輸入“-”號，再單擊C10單元格，引用其地址至公式中。單擊“輸入”按鈕完成計算。

5.單擊C12單元格，在編輯欄中，輸入“=”號，激活公式編輯狀態。

6.單擊C8單元格，引用其地址至公式中，繼續輸入“*”號，再輸入“12”。單擊“輸入”按鈕完成計算。4.引用3.單擊6.引用5.單擊

插入PMT函數

PMT函數屬于財務類函數的一種，常用于計算在固定利率下，貸款的等額分期償還額。PMT函數共包含Rate、Nper、Pv、Fv、Type5個參數，其中前3個參數非常重要。Rate代表各期利率，利率/12代表一個月的還款額；Nper代表貸款期限，若Rate中計算的是一個月的還款額，那么此處需輸入還款幾個月；Pv為貸款總額，因貸款是出賬，為了閱讀方便，可在其值前加負號。

1.單擊C13單元格。

2.單擊“公式”選項卡→“函數庫”組→“財務”按鈕→“PMT”函數，打開“函數參數”對話框。1.單擊2.單擊

3.設置“Rate”參數的值為“F4/12”。其中，F4單元格為銀行公布的年利率，此項需根據每年公布的實際情況而定。

4.設置“Nper”參數的值為“C12”。此處不需要輸入，單擊C12單元格即可完成引用。

5.單擊C11單元格，將其地址引用至“Pv”參數處，再在最前面輸入負號。

6.單擊“確定”按鈕完成函數設置。每月還款額會根據年利率、貸款時間和貸款金額的變化而變化。6.單擊3.輸入5.輸入4.引用結果

減少小數位

1.單擊C14單元格，在編輯欄中，輸入“=”號，激活公式編輯狀態。

2.單擊C13單元格，引用其地址至公式中，繼續輸入“*”號，再單擊C12單元格，引用其地址至公式中。單擊“輸入”按鈕完成計算。2.引用1.單擊

3.單擊“開始”選項卡→“數字”組→“減少小數位數”按鈕。3.單擊

4.單擊C15單元格，在編輯欄中，輸入“=”號，激活公式編輯狀態。

5.引用C14單元格至公式中，繼續輸入“-”號，再引用C11單元格。單擊“輸入”按鈕完成計算。

6.單擊C16單元格，在編輯欄中，輸入“=”號，激活公式編輯狀態。

7.引用C10單元格至公式中，繼續輸入“+”號，再引用C14單元格。單擊“輸入”按鈕完成計算。

添加總結語

至此，貸款購車所需數據已進行了計算，而為了更加清晰地展示計算結果，可將結果引用至醒目的位置，比如添加總結語。

1.單擊B18單元格，輸入圖中所示內容。1.輸入

2.單擊D18單元格。

3.輸入“=”號后，引用C10單元格地址的內容至活動單元格。2.單擊3.引用

4.單擊F18單元格。

5.輸入“=”號后，引用C13單元格地址的內容至活動單元格。4.單擊5.引用

6.單擊C19單元格。

7.輸入“=”號后，引用C16單元格地址的內容至活動單元格。6.單擊7.引用

8.單擊E19單元格。

9.輸入“=”號后，引用C16單元格，繼續輸入“-”號，再引用C6單元格地址的內容至活動單元格。按“Enter”鍵完成計算。8.單擊9.引用

定位單元格

1.選中B18:F19單元格，按組合鍵“Ctrl+G”定位單元格。

2.單擊“定位”對話框中的“定位條件”按鈕。1.選中2.單擊

3.在“定位條件”對話框中，選中“公式”項。

4.單擊“確定”按鈕返回，并選中滿足定位條件的單元格。

5.設置選中單元格的字號為“14”號。

6.單擊“開始”選項卡→“字體”組→“字體顏色”按鈕，按照默認設置將字體顏色設置為“紅色”，對齊方式設置為“居中”。3.選中4.單擊6.單擊5.選中

插入形狀

1.選中B2單元格，設置表格標題字體為“華文彩云”，字號為“24”號。

2.選中A3:F19單元格，設置正文字體為“微軟雅黑”。

3.將鼠標置于任意列列標的右側，拖動鼠標適當調整列寬。1.設置3.拖動2.設置

4.單擊“插入”選項卡→“插圖”組→“形狀”按鈕

→“矩形”項，此時鼠標呈“”形狀。4.單擊

5.拖動鼠標自B2單元格的左上角至F2單元格的右下角，釋放鼠標，完成形狀的插入。5.拖動

美化形狀

1.選中上面插入的形狀，激活“繪圖工具”。

2.單擊“格式”選項卡→“形狀樣式”組→“形狀填充”按鈕，在下拉列表中，單擊“無填充顏色”項。

3.單擊“形狀輪廓”按鈕，在下拉列表中，單擊“自動”項。

4.單擊“形狀效果”按鈕，在下拉列表中，單擊

“陰影”→“右下斜偏移”項。

美化邊框

1.選中B6:D8和E4:F4單元格。

2.單擊“開始”選項卡→“字體”組→“邊框”按鈕→“其他邊框”項，打開“設置單元格格式”對話框。1.選中2.單擊

3.在“邊框”選項卡中，單擊“顏色”欄下拉列表中的“綠色”。

4.單擊“線條樣式”選擇外邊框樣式。

5.單擊“外邊框”按鈕，為選中區域增加外邊框。

6.單擊“線條樣式”選擇內部框線樣式。

7.單擊“內部”按鈕，為選中區域增加內部框線。

8.單擊“確定”按鈕完成設置。3.單擊5.單擊4.單擊6.單擊7.單擊8.單擊

9.選中B10:B16單元格，重復步驟2～8，其中框線顏色改為“紅色”。9.設置

設置填充顏色

1.按住“Ctrl”鍵，同時選中E4:F4、B6:D8和B10:D16單元格。

2.單擊“開始”選項卡→“字體”組→“填充”按鈕右側的箭頭，在下拉列表中，單擊“其他顏色”項。2.單擊1.選中

3.在“顏色”對話框中，單擊需要的顏色。

4.單擊“確定”按鈕完成選中色彩的填充。3.單擊4.單擊

5.選中C6:C8和F4單元格。

6.單擊“開始”選項卡→“字體”組→“填充”按鈕右側的箭頭，在下拉列表中，單擊“淺藍”項。5.選中6.單擊

復制形狀

1.選中矩形，按組合鍵“Ctrl+C”完成復制。

2.按組合鍵“Ctrl+V”粘貼矩形。

3.將粘貼后的矩形拖動至B18:F19單元格，并將鼠標置于矩形邊緣，當鼠標呈“”形狀時，拖動矩形改變其至合適的大小。2.粘貼3.拖動1.復制

4.選中表格標題四周的矩形，按住“Ctrl”鍵，當鼠標呈“”形狀時，向下拖動至B6:F7單元格。4.拖動

5.將鼠標置于矩形邊緣，當鼠標呈“”形狀時，拖動矩形改變其至合適的大小，使矩形位于E6:F16區域。

6.選中位于E6:F16區域的矩形，單擊“繪圖工具”功能區→“格式”選項卡→“形狀樣式”組→“彩色輪廓-藍色，強調顏色1”項。

7.單擊“形狀填充”按鈕→“無填充顏色”項。5.調整7.單擊6.單擊

設置段落格式

在插入的自選圖形中，可以增加文字內容，也可以為文字設置不同的格式。若文字數量與形狀大小不匹配，可采用設置段落大小的方式，擴大或縮小行間距。

1.右鍵單擊矩形的邊緣。

2.在彈出的快捷菜單中，單擊“編輯文字”命令，進入為形狀添加文字的狀態。2.單擊1.右擊

3.輸入圖中所示文字。

4.選中輸入的文字，右鍵單擊選中區域，在彈出的快捷菜單中，單擊“段落”命令。

5.在打開的“段落”對話框中，單擊“行距”右側的列表框，在下拉列表中選中“1.5倍行距”項，設置行距。

6.單擊“確定”按鈕完成設置。5.選中6.單擊

插入剪貼畫

1.單擊“插入”選項卡→“插圖”組→“剪貼畫”按鈕，在文檔右側出現“剪貼畫”任務窗格。

2.在“搜索文字”框中，輸入需要的圖片名稱，如“車”。

3.單擊“剪貼畫”任務窗格→“搜索”按鈕，在窗格下方出現與名稱相符的圖片。

4.在剪貼畫預覽區域，單擊所需圖形完成插入。

4.在剪貼畫預覽區域，單擊所需圖形完成插入。

5.調整插入的剪貼畫的大小，并拖動其至合適的位置。

至此，本文檔已基本設計完成，為了觀察設計效果，可取消網格線。方法為：單擊“視圖”選項卡→“顯示”組，默認狀態下，Excel為我們勾選“網格線”項，單擊復選框，即可取消對其的勾選。

保護工作表

工作表設計完成后，若不希望他人進行修改，可為工作表設置保護措施，如加密。

1.選中C6:C8和F4單元格。

2.單擊“開始”選項卡→“單元格”組→“格式”按鈕→“設置單元格格式”項。

3.在打開的“設置單元格格式”對話框中，單

擊“保護”選項卡→“鎖定”項，取消對其的勾選。

4.單擊“審閱”選項卡→“更改”組→“保護工作表”按鈕，顯示“保護工作表”對話框。

5.輸入密碼，如“123”。

6.選中“選定鎖定單元格”項，同時自動選中“選定未鎖定的單元格”項。單擊“確定”按鈕打開“確認密碼”對話框。

7.再次輸入密碼后，單擊“確定”按鈕完成設置。

保存為模板

Excel提供了多種模板供用戶使用，而當我們設計好一個Excel文檔后，為了方便使用，同