《跟著名師學(xué)電腦Excel應(yīng)用入門(mén)實(shí)例》課件-第13章

實(shí)例13制作貸款購(gòu)車計(jì)算器—插入PMT函數(shù)—插入與設(shè)置形狀—保護(hù)工作表—保存模板

學(xué)習(xí)情境

貸款購(gòu)物在當(dāng)今已成為一種時(shí)尚，對(duì)于無(wú)大量存款，但每月還小額款項(xiàng)基本沒(méi)問(wèn)題的個(gè)人或家庭而言，房貸、車貸等分期購(gòu)物方式較好地解決了資金流動(dòng)的難題。

小張也想根據(jù)自己目前的經(jīng)濟(jì)狀況買車，希望自己設(shè)計(jì)一個(gè)貸款購(gòu)車計(jì)算器，從而衡量哪種貸款方式較為合適。

編排效果

掌握技能

通過(guò)本實(shí)例，將學(xué)會(huì)以下技能：

插入PMT函數(shù)。

插入與設(shè)置形狀。

保護(hù)工作表。

保存模板。

設(shè)置文檔結(jié)構(gòu)

1.啟動(dòng)Excel程序，打開(kāi)自動(dòng)命名為“工作簿1”的Excel文檔，按照?qǐng)D中所示內(nèi)容輸入相應(yīng)文字。1.輸入

2.選中列B至列F。

3.?右鍵單擊選中列的列標(biāo)處。

4.在彈出的快捷菜單中，單擊“列寬”命令，打開(kāi)“列寬”對(duì)話框。

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

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

7.右鍵單擊選中區(qū)域，在彈出的快捷菜單中，單擊“設(shè)置單元格格式”命令，打開(kāi)“設(shè)置單元格格式”對(duì)話框。

8.單擊“數(shù)字”選項(xiàng)卡→“貨幣”分類，再單擊“小數(shù)位數(shù)”右側(cè)的向下箭頭，調(diào)整小數(shù)位數(shù)至“0”。6.選中7.單擊8.單擊

添加下拉列表

為了方便使用，可將單元格設(shè)置成具有下拉列表的形式，不需輸入，僅通過(guò)選擇相應(yīng)的項(xiàng)即可將內(nèi)容顯示至表單中。

1.對(duì)B2:F2單元格執(zhí)行“合并后居中”操作。

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

3.單擊C7單元格。

4.單擊“數(shù)據(jù)”選項(xiàng)卡→“數(shù)據(jù)工具”組→“數(shù)據(jù)有效性”按鈕。

5.在打開(kāi)的“數(shù)據(jù)有效性”對(duì)話框中，單擊“設(shè)置”選項(xiàng)卡→“允許”欄下方的列表框箭頭，選中“序列”項(xiàng)。

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

7.單擊C8單元格。

8.單擊“數(shù)據(jù)”選項(xiàng)卡→“數(shù)據(jù)工具”組→“數(shù)據(jù)有效性”按鈕。

9.在“數(shù)據(jù)有效性”對(duì)話框中，選中“序列”項(xiàng)，在“來(lái)源”處輸入“1,2,3,4,5”。1.合并2.輸入3.單擊4.單擊5.選中6.輸入7.單擊9.輸入8.單擊

引用動(dòng)態(tài)數(shù)據(jù)

在單元格中輸入的數(shù)據(jù)通常是不可變的，為了提高數(shù)據(jù)的可重用性，一般使用引用單元格地址的方式進(jìn)行計(jì)算，當(dāng)被引用單元格的數(shù)據(jù)發(fā)生變化時(shí)，其他數(shù)據(jù)也能夠動(dòng)態(tài)地更新，從而做到輸入較少數(shù)據(jù)即可進(jìn)行大量運(yùn)算的效果。

1.單擊C10單元格，在編輯欄中，輸入“=”號(hào)，激活公式編輯狀態(tài)。

2.單擊C6單元格，引用其地址至公式中，繼續(xù)輸入“*”號(hào)，再單擊C7單元格，引用其地址至公式中。單擊“輸入”按鈕完成計(jì)算。2.引用1.單擊

3.單擊C11單元格，在編輯欄中，輸入“=”號(hào)，激活公式編輯狀態(tài)。

4.單擊C6單元格，引用其地址至公式中，繼續(xù)輸入“-”號(hào)，再單擊C10單元格，引用其地址至公式中。單擊“輸入”按鈕完成計(jì)算。

5.單擊C12單元格，在編輯欄中，輸入“=”號(hào)，激活公式編輯狀態(tài)。

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

插入PMT函數(shù)

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

1.單擊C13單元格。

2.單擊“公式”選項(xiàng)卡→“函數(shù)庫(kù)”組→“財(cái)務(wù)”按鈕→“PMT”函數(shù)，打開(kāi)“函數(shù)參數(shù)”對(duì)話框。1.單擊2.單擊

3.設(shè)置“Rate”參數(shù)的值為“F4/12”。其中，F(xiàn)4單元格為銀行公布的年利率，此項(xiàng)需根據(jù)每年公布的實(shí)際情況而定。

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

5.單擊C11單元格，將其地址引用至“Pv”參數(shù)處，再在最前面輸入負(fù)號(hào)。

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

減少小數(shù)位

1.單擊C14單元格，在編輯欄中，輸入“=”號(hào)，激活公式編輯狀態(tài)。

2.單擊C13單元格，引用其地址至公式中，繼續(xù)輸入“*”號(hào)，再單擊C12單元格，引用其地址至公式中。單擊“輸入”按鈕完成計(jì)算。2.引用1.單擊

3.單擊“開(kāi)始”選項(xiàng)卡→“數(shù)字”組→“減少小數(shù)位數(shù)”按鈕。3.單擊

4.單擊C15單元格，在編輯欄中，輸入“=”號(hào)，激活公式編輯狀態(tài)。

5.引用C14單元格至公式中，繼續(xù)輸入“-”號(hào)，再引用C11單元格。單擊“輸入”按鈕完成計(jì)算。

6.單擊C16單元格，在編輯欄中，輸入“=”號(hào)，激活公式編輯狀態(tài)。

7.引用C10單元格至公式中，繼續(xù)輸入“+”號(hào)，再引用C14單元格。單擊“輸入”按鈕完成計(jì)算。

添加總結(jié)語(yǔ)

至此，貸款購(gòu)車所需數(shù)據(jù)已進(jìn)行了計(jì)算，而為了更加清晰地展示計(jì)算結(jié)果，可將結(jié)果引用至醒目的位置，比如添加總結(jié)語(yǔ)。

1.單擊B18單元格，輸入圖中所示內(nèi)容。1.輸入

2.單擊D18單元格。

3.輸入“=”號(hào)后，引用C10單元格地址的內(nèi)容至活動(dòng)單元格。2.單擊3.引用

4.單擊F18單元格。

5.輸入“=”號(hào)后，引用C13單元格地址的內(nèi)容至活動(dòng)單元格。4.單擊5.引用

6.單擊C19單元格。

7.輸入“=”號(hào)后，引用C16單元格地址的內(nèi)容至活動(dòng)單元格。6.單擊7.引用

8.單擊E19單元格。

9.輸入“=”號(hào)后，引用C16單元格，繼續(xù)輸入“-”號(hào)，再引用C6單元格地址的內(nèi)容至活動(dòng)單元格。按“Enter”鍵完成計(jì)算。8.單擊9.引用

定位單元格

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

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

3.在“定位條件”對(duì)話框中，選中“公式”項(xiàng)。

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

5.設(shè)置選中單元格的字號(hào)為“14”號(hào)。

6.單擊“開(kāi)始”選項(xiàng)卡→“字體”組→“字體顏色”按鈕，按照默認(rèn)設(shè)置將字體顏色設(shè)置為“紅色”，對(duì)齊方式設(shè)置為“居中”。3.選中4.單擊6.單擊5.選中

插入形狀

1.選中B2單元格，設(shè)置表格標(biāo)題字體為“華文彩云”，字號(hào)為“24”號(hào)。

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

3.將鼠標(biāo)置于任意列列標(biāo)的右側(cè)，拖動(dòng)鼠標(biāo)適當(dāng)調(diào)整列寬。1.設(shè)置3.拖動(dòng)2.設(shè)置

4.單擊“插入”選項(xiàng)卡→“插圖”組→“形狀”按鈕

→“矩形”項(xiàng)，此時(shí)鼠標(biāo)呈“”形狀。4.單擊

5.拖動(dòng)鼠標(biāo)自B2單元格的左上角至F2單元格的右下角，釋放鼠標(biāo)，完成形狀的插入。5.拖動(dòng)

美化形狀

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

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

3.單擊“形狀輪廓”按鈕，在下拉列表中，單擊“自動(dòng)”項(xiàng)。

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

“陰影”→“右下斜偏移”項(xiàng)。

美化邊框

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

2.單擊“開(kāi)始”選項(xiàng)卡→“字體”組→“邊框”按鈕→“其他邊框”項(xiàng)，打開(kāi)“設(shè)置單元格格式”對(duì)話框。1.選中2.單擊

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

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

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

6.單擊“線條樣式”選擇內(nèi)部框線樣式。

7.單擊“內(nèi)部”按鈕，為選中區(qū)域增加內(nèi)部框線。

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

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

設(shè)置填充顏色

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

2.單擊“開(kāi)始”選項(xiàng)卡→“字體”組→“填充”按鈕右側(cè)的箭頭，在下拉列表中，單擊“其他顏色”項(xiàng)。2.單擊1.選中

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

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

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

6.單擊“開(kāi)始”選項(xiàng)卡→“字體”組→“填充”按鈕右側(cè)的箭頭，在下拉列表中，單擊“淺藍(lán)”項(xiàng)。5.選中6.單擊

復(fù)制形狀

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

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

3.將粘貼后的矩形拖動(dòng)至B18:F19單元格，并將鼠標(biāo)置于矩形邊緣，當(dāng)鼠標(biāo)呈“”形狀時(shí)，拖動(dòng)矩形改變其至合適的大小。2.粘貼3.拖動(dòng)1.復(fù)制

4.選中表格標(biāo)題四周的矩形，按住“Ctrl”鍵，當(dāng)鼠標(biāo)呈“”形狀時(shí)，向下拖動(dòng)至B6:F7單元格。4.拖動(dòng)

5.將鼠標(biāo)置于矩形邊緣，當(dāng)鼠標(biāo)呈“”形狀時(shí)，拖動(dòng)矩形改變其至合適的大小，使矩形位于E6:F16區(qū)域。

6.選中位于E6:F16區(qū)域的矩形，單擊“繪圖工具”功能區(qū)→“格式”選項(xiàng)卡→“形狀樣式”組→“彩色輪廓-藍(lán)色，強(qiáng)調(diào)顏色1”項(xiàng)。

7.單擊“形狀填充”按鈕→“無(wú)填充顏色”項(xiàng)。5.調(diào)整7.單擊6.單擊

設(shè)置段落格式

在插入的自選圖形中，可以增加文字內(nèi)容，也可以為文字設(shè)置不同的格式。若文字?jǐn)?shù)量與形狀大小不匹配，可采用設(shè)置段落大小的方式，擴(kuò)大或縮小行間距。

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

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

3.輸入圖中所示文字。

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

5.在打開(kāi)的“段落”對(duì)話框中，單擊“行距”右側(cè)的列表框，在下拉列表中選中“1.5倍行距”項(xiàng)，設(shè)置行距。

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

插入剪貼畫(huà)

1.單擊“插入”選項(xiàng)卡→“插圖”組→“剪貼畫(huà)”按鈕，在文檔右側(cè)出現(xiàn)“剪貼畫(huà)”任務(wù)窗格。

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

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

4.在剪貼畫(huà)預(yù)覽區(qū)域，單擊所需圖形完成插入。

4.在剪貼畫(huà)預(yù)覽區(qū)域，單擊所需圖形完成插入。

5.調(diào)整插入的剪貼畫(huà)的大小，并拖動(dòng)其至合適的位置。

至此，本文檔已基本設(shè)計(jì)完成，為了觀察設(shè)計(jì)效果，可取消網(wǎng)格線。方法為：?jiǎn)螕簟耙晥D”選項(xiàng)卡→“顯示”組，默認(rèn)狀態(tài)下，Excel為我們勾選“網(wǎng)格線”項(xiàng)，單擊復(fù)選框，即可取消對(duì)其的勾選。

保護(hù)工作表

工作表設(shè)計(jì)完成后，若不希望他人進(jìn)行修改，可為工作表設(shè)置保護(hù)措施，如加密。

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

2.單擊“開(kāi)始”選項(xiàng)卡→“單元格”組→“格式”按鈕→“設(shè)置單元格格式”項(xiàng)。

3.在打開(kāi)的“設(shè)置單元格格式”對(duì)話框中，單

擊“保護(hù)”選項(xiàng)卡→“鎖定”項(xiàng)，取消對(duì)其的勾選。

4.單擊“審閱”選項(xiàng)卡→“更改”組→“保護(hù)工作表”按鈕，顯示“保護(hù)工作表”對(duì)話框。

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

6.選中“選定鎖定單元格”項(xiàng)，同時(shí)自動(dòng)選中“選定未鎖定的單元格”項(xiàng)。單擊“確定”按鈕打開(kāi)“確認(rèn)密碼”對(duì)話框。

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

保存為模板

Excel提供了多種模板供用戶使用，而當(dāng)我們?cè)O(shè)計(jì)好一個(gè)Excel文檔后，為了方便使用，同