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

實(shí)例7制作家庭收支預(yù)算表—自動求和—輸入公式—引用單元格—定義單元格名稱—復(fù)制公式的計算結(jié)果—在局部表格中套用表格格式

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

家庭收支預(yù)算表是對家庭未來一定時期內(nèi)收入和支出的計劃。編制家庭收支預(yù)算表是家庭經(jīng)濟(jì)管理的一個重要組成部分。

小張準(zhǔn)備從今年年初開始，制作家庭收支預(yù)算表，包括工資收入、獎金收入、生活開支、交通與通信開支、孩子花銷等項(xiàng)目，以便合理規(guī)劃收入，統(tǒng)籌安排開支。

編排效果

掌握技能

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

自動求和。

輸入公式。

引用單元格。

定義單元格名稱。

復(fù)制公式的計算結(jié)果。

在局部表格中套用表格格式。

設(shè)置表格框架

1.新建Excel工作簿，將其重命名為“家庭收支計劃”。

2.按照實(shí)例要求輸入文本。

3.單擊A1單元格，設(shè)置字體為“華文中宋”，字號為“16”。

4.單擊“居中”按鈕，選中A2:I10單元格，設(shè)置字體為“微軟雅黑”，字號為“11”。1.新建2.輸入4.單擊3.設(shè)置

5.選中B4:I10單元格。

6.在“開始”選項(xiàng)卡→“數(shù)字”組中，單擊“會計數(shù)字格式”按鈕右側(cè)的箭頭。

7.在下拉列表中，單擊“其他會計格式”命令。5.選中6.單擊7.單擊

8.在“設(shè)置單元格格式”對話框中，單擊“小數(shù)位數(shù)”右側(cè)的箭頭，調(diào)整小數(shù)位數(shù)為“0”。

9.單擊“確定”按鈕完成設(shè)置。8.單擊9.確定

自動求和

Excel提供了自動求和功能，能夠方便、快捷地對數(shù)值進(jìn)行運(yùn)算，包括求和、平均值、計數(shù)、最大值、最小值等。

1.為表格輸入數(shù)據(jù)，其中列C輸入“0”，按“Enter”鍵后將顯示“-”。1.輸入

2.單擊D4單元格。

3.在“公式”選項(xiàng)卡→“函數(shù)庫”組中，單擊“自動求和”按鈕。此時，D4單元格內(nèi)自動出現(xiàn)公式=SUM(B4:C4)”，公式左側(cè)虛線為求和范圍，表示對B4至C4單元格的數(shù)據(jù)進(jìn)行求和運(yùn)算。

4.按“Enter”鍵完成計算。計算結(jié)果如左圖所示。2.單擊4.按“Enter”鍵3.單擊計算結(jié)果

修改求和范圍

1.單擊H4單元格。

2.單擊“自動求和”按鈕的下拉箭頭，在下拉列表中單擊“求和”項(xiàng)。

3.按“Enter”鍵確定輸入。

4.單擊編輯欄，進(jìn)入公式編輯狀態(tài)，同時表格中出現(xiàn)數(shù)值計算范圍的虛線框。

5.將鼠標(biāo)懸浮于虛線框左下方，當(dāng)鼠標(biāo)為斜向雙向箭頭形狀時，拖動藍(lán)色邊框至E4單元格。

6.單擊編輯欄中的“輸入”按鈕完成計算，并退出編輯狀態(tài)。

公式編輯狀態(tài)

單擊包含公式的編輯框后，進(jìn)入公式編輯狀態(tài)，如果在計算時數(shù)值或公式引用不當(dāng)，可單擊編輯欄的“取消”按鈕撤銷當(dāng)前操作。4.單擊5.拖動6.單擊單擊

輸入公式

在Excel中，常用輸入公式的方法計算數(shù)值，該公式由等于號“=”、操作數(shù)和運(yùn)算符組成，其中操作數(shù)即被引用的單元格中的數(shù)據(jù)，在公式中表現(xiàn)為單元格的名稱。

本例中，每月結(jié)余的金額為每月收入總和減去每月開支總和，需對兩個單元格的數(shù)值進(jìn)行減法運(yùn)算。

1.單擊I4單元格。

2.輸入“=”號，進(jìn)入公式編輯狀態(tài)。

3.單擊D4單元格，輸入“-”號。

4.繼續(xù)單擊H4單元格。

5.單擊“輸入”按鈕完成計算。2.輸入1.單擊5.單擊3.單擊4.單擊

Excel運(yùn)算符

Excel包含四種類型運(yùn)算符，分別為算術(shù)運(yùn)算符、比較運(yùn)算符、文本運(yùn)算符和引用運(yùn)算符。

按每個運(yùn)算符的特定順序從左到右計算公式。若公式中包含多個運(yùn)算符，則遵守運(yùn)算符的優(yōu)先級順序來計算。

Excel運(yùn)算符的優(yōu)先級順序如左圖所示，此順序由高到低排列，優(yōu)先級高的運(yùn)算符先參與運(yùn)算。

復(fù)制公式

復(fù)制公式至其他單元格可保持函數(shù)原型不變，以及單元格的相對引用。復(fù)制方式種類較多，包括直接復(fù)制法、句柄填充法和選擇性粘貼法等。以下為直接復(fù)制法。

1.單擊D4單元格。

2.在“開始”選項(xiàng)卡→“剪貼板”組中，單擊“復(fù)制”按鈕。2.單擊1.單擊

3.單擊D5單元格。

4.按下“Shift”鍵的同時，單擊D9單元格。3.單擊4.按“Shift”鍵單擊

5.單擊“粘貼”按鈕下方的箭頭。

6.在下拉列表中，單擊“公式”項(xiàng)，完成復(fù)制。5.單擊6.單擊．

單元格的相對引用

下面使用句柄填充法復(fù)制H4單元格的公式至H5:H9單元格。

1.單擊H4單元格。

2.將鼠標(biāo)置于H4單元格右下方的填充柄處，當(dāng)鼠標(biāo)呈“”形狀時，拖動鼠標(biāo)至H9單元格，完成公式復(fù)制。

3.雙擊H4單元格，觀察到公式的求和范圍變更為E4:G4單元格。

4.雙擊H5單元格，觀察到公式的求和范圍變更為E5:G5單元格。與H4單元格相比，求值范圍發(fā)生了相對變化。

定義單元格名稱

當(dāng)表格數(shù)據(jù)量比較大時，再使用類似“A1”的單元格命名方式，極容易造成混淆。此時，可為單元格定義容易區(qū)分的名稱，來實(shí)現(xiàn)快速定位。

1.單擊D4單元格。

2.在“公式”選項(xiàng)卡→“定義的名稱”組中，單擊“定義名稱”按鈕。1.單擊2.單擊

3.在彈出的“新建名稱”對話框的“名稱”欄輸入“一月收入總和”。

4.單擊“確定”按鈕完成名稱定義并返回工作表。3.輸入4.單擊

5.單擊H4單元格。

6.在“名稱框”中直接輸入新名稱“一月開支總和”。5.單擊6.輸入

快速定位單元格

單元格名稱定義完成后，可使用定位功能，快速選中單元格。步驟為：按組合鍵“Ctrl+G”，彈出“定位”對話框，在“定位”欄單擊需要選中的單元格名稱，再單擊“確定”按鈕即可定位到該單元格。單擊

管理單元格名稱

單元格的名稱創(chuàng)建完成后，如果需要對名稱進(jìn)行編輯和刪除操作，可以使用名稱管理器進(jìn)行管理，同時也可以使用該管理器進(jìn)行名稱的新建。

1.在“公式”選項(xiàng)卡→“定義的名稱”組中，單擊“名稱管理器”按鈕。1.單擊

2.在“名稱管理器”對話框中，單擊“新建”按鈕。

3.在“新建名稱”對話框中，輸入名稱“一月結(jié)余”，也可以添加備注“一月份結(jié)余的金額”。

4.單擊“引用位置”右側(cè)的按鈕，選取需要定義名稱的單元格。此時“新建名稱”對話框縮小，并更名為“新建名稱-引用位置”對話框。2.單擊3.輸入4.單擊

5.單擊I4單元格，此時“新建名稱-引用位置”對話框的引用位置變更為“=Sheet1!$I$4”。

6.再次單擊“引用位置”右側(cè)的按鈕，返回“新建名稱”對話框，單擊“確定”按鈕后，完成對I4單元格的命名。5.單擊6.單擊

在公式中使用單元格名稱

將定義好的單元格名稱應(yīng)用至公式中，能夠增強(qiáng)公式的可讀性。

1.按組合鍵“Ctrl+G”，快速定位至“一月結(jié)余”單元格。

2.在“公式”選項(xiàng)卡→“定義的名稱”組中，單擊“用于公式”按鈕。

3.在下拉列表中，單擊“一月收入總和”項(xiàng)，將自動在“一月結(jié)余”單元格中添加公式“=一月收入總和”。

4.單擊編輯框，進(jìn)入公式編輯狀態(tài)，輸入減號“-”。4.輸入

5.單擊“用于公式”按鈕，在下拉列表中，單擊“一月開支總和”項(xiàng)，則在公式中引用“一月開支總和”單元格。5.單擊

6.單擊編輯欄的“輸入”按鈕確定修改，則公式變更為“一月結(jié)余?=?一月收入總和?-?一月開支總和”。6.單擊

使用該方法創(chuàng)建的公式具有唯一性，若將該公式復(fù)制至其他單元格，其求值范圍不會更改，屬于絕對引用。效果

復(fù)制計算結(jié)果

1.將表格內(nèi)容填充完整后，選中A1:I10單元格。

2.按組合鍵“Ctrl+C”復(fù)制選中的內(nèi)容。

3.單擊“Sheet2”工作表標(biāo)簽。

4.在“開始”選項(xiàng)卡→“剪貼板”組中，單擊“粘貼”按鈕。

5.在下拉菜單中，單擊“選擇性粘貼”項(xiàng)。

6.在“選擇性粘貼”對話框中，選中“值和數(shù)字格式”項(xiàng)。

7.單擊“確定”按鈕。4.單擊5.單擊6.選中7.單擊3.單擊

當(dāng)某單元格數(shù)值為“####”時，說明列寬過窄，無法完整顯示單元格的內(nèi)容，可通過調(diào)整列寬來顯示正確的內(nèi)容。方法為：將鼠標(biāo)置于需要調(diào)整列寬的列號右側(cè)，當(dāng)鼠標(biāo)呈圖示形狀時，向右拖動鼠標(biāo)，即可增大列寬。拖動

局部表格套用格式

根據(jù)選中區(qū)域的不同，可為不同區(qū)域套用表格樣式，以豐富表格色彩，突出視覺感。

1.單擊“Sheet1”工作表標(biāo)簽，選中A3:A10單元格。

2.單擊“開始”選項(xiàng)卡→“樣式”組→“套用表格格式”按鈕。

3.在下拉列表中，單擊“表樣式中等深淺9”。2.單擊1.單擊3.單擊

4.單擊“開始”選項(xiàng)卡→“編輯”組→“排序和篩選”按鈕，在下拉菜單中，單擊“篩選”項(xiàng)，取消篩選狀態(tài)。4.單擊

5.選中單元格B3:D10，重復(fù)步驟1～4，設(shè)置表格樣式為“表樣式中等深淺11”。

6.選中單元格E3:H10，重復(fù)步驟1～4，設(shè)置表格樣式為“表樣式中等深淺10”。

7.選中單元格I3:I10，重復(fù)步驟1～4，設(shè)置表格樣式為“表樣式中等深淺14”。5.設(shè)置6.設(shè)置7.設(shè)置

填充單元格顏色

1.單擊A3單元格。

2.單擊“開始”選項(xiàng)卡→“剪貼板”組→“格式刷”按鈕。

3.當(dāng)鼠標(biāo)呈“”形狀時，單擊A2單元格，應(yīng)用格式。1.單擊2.單擊3