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

實(shí)例10制作水電費(fèi)繳費(fèi)清單—定位單元格—相對(duì)引用—絕對(duì)引用—混合引用

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

祥和社區(qū)后勤部管理員小王，每月負(fù)責(zé)收取小區(qū)用戶的水電費(fèi)。為避免錯(cuò)收、漏收費(fèi)用現(xiàn)象的發(fā)生，故在收取費(fèi)用前，需要制作水電費(fèi)繳費(fèi)清單并將其發(fā)給每位業(yè)主，既可以提高工作效率，又能夠保證信息的準(zhǔn)確性。

編排效果

掌握技能

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

定位單元格。

相對(duì)引用。

絕對(duì)引用。

混合引用。

獲取外部數(shù)據(jù)

Excel支持外部數(shù)據(jù)的導(dǎo)入，包括來自網(wǎng)站、數(shù)據(jù)庫和文本格式的數(shù)據(jù)。

1.新建名為“水電費(fèi)繳費(fèi)清單”的Excel文檔。

2.單擊“數(shù)據(jù)”選項(xiàng)卡→“獲取外部數(shù)據(jù)”組→“自文本”按鈕，打開“導(dǎo)入文本文件”對(duì)話框。1.新建2.單擊

3.?選擇需導(dǎo)入文件所在位置。

4.選中文件預(yù)覽區(qū)中所示文件。

5.單擊“導(dǎo)入”按鈕，打開“文本導(dǎo)入向?qū)А睂?duì)話框。

6.如果不需要設(shè)置，可直接單擊“完成”按鈕打開“導(dǎo)入數(shù)據(jù)”對(duì)話框，再單擊“確定”按鈕完成數(shù)據(jù)導(dǎo)入。3.選擇4.選中5.單擊6.單擊

修改內(nèi)容

由外部導(dǎo)入的數(shù)據(jù)，往往無法滿足文檔設(shè)計(jì)需要，需進(jìn)一步增刪數(shù)據(jù)。

1.按照?qǐng)D中實(shí)例，輸入文本和數(shù)據(jù)。合并單元格A1:F1、A2:C2、A3:C3。

2.選中A1:F10單元格，設(shè)置文字對(duì)齊方式為“居中”。單擊A1單元格，設(shè)置字體大小為12號(hào)，加粗。2.設(shè)置1.輸入

3.按住“Ctrl”鍵，選中D2:D3、D6:F10單元格。

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

5.在下拉列表中，選擇“中文(中國)”項(xiàng)，設(shè)置所選單元格格式。4.單擊3.選中5.選擇

插入乘法運(yùn)算公式

1.單擊D6單元格。

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

3.單擊B6單元格，引用該單元格的數(shù)據(jù)。

4.在編輯欄中輸入“*”號(hào)。

5.單擊C4單元格，引用該單元格的數(shù)據(jù)。

6.輸入“+”號(hào)。

7.單擊C6單元格，引用該單元格的數(shù)據(jù)，繼續(xù)輸入“*”號(hào)。

8.單擊D3單元格，引用該單元格的數(shù)據(jù)，按“Enter”鍵完成公式輸入。

單元格的絕對(duì)引用

公式中使用單元格的數(shù)據(jù)，根據(jù)單元格的地址是否發(fā)生變化，分為相對(duì)引用、絕對(duì)引用和混合引用三種引用方式。一般情況下為相對(duì)引用方式，單元格的地址會(huì)隨著公式位置的改變而改變。若不希望公式中引用的值發(fā)生變化，可使用絕對(duì)引用方式。

修改單元格的引用方式為絕對(duì)引用時(shí)，需在引用單元格的列號(hào)和行號(hào)前加入“$”符號(hào)，以保證被引用的單元格地址不變。輸入前，需調(diào)整輸入法為英文狀態(tài)。

1.雙擊D6單元格，激活公式編輯狀態(tài)。

2.在D2單元格的列標(biāo)“D”和行號(hào)“2”的前邊分別輸入“$”符號(hào)。1.雙擊2.輸入

切換引用方式

選中公式中的單元格地址。按一次“F4”鍵，列號(hào)和行號(hào)前自動(dòng)添加“$”符號(hào)。按兩次“F4”鍵，行號(hào)前自動(dòng)添加“$”符號(hào)。按三次“F4”鍵，行號(hào)恢復(fù)，列號(hào)前自動(dòng)添加“$”符號(hào)。按四次“F4”鍵，單元格恢復(fù)相對(duì)引用狀態(tài)。

3.選中D6單元格公式中的“D3”，按一次“F4”鍵。

4.按“Enter”鍵完成公式輸入，拖動(dòng)鼠標(biāo)將D6單元格公式填充至D7:D10單元格。

插入嵌套公式

1.單擊F6單元格。

2.單擊編輯欄“插入函數(shù)”按鈕，打開“插入函數(shù)”對(duì)話框。1.單擊2.單擊

3.單擊“或選擇類別”列表框的下拉箭頭，在下拉列表中選中“邏輯”類函數(shù)。

4.在“選擇函數(shù)”列表中，選中“IF”函數(shù)。

5.單擊“確定”按鈕，打開“函數(shù)參數(shù)”對(duì)話框。3.單擊4.選中5.單擊

6.在“Logical_test”參數(shù)后，輸入“(D6-E6)>0”；在“Value_if_true”參數(shù)后，輸入“D6-E6”；在“Value_if_false”參數(shù)后，輸入“0”。

7.單擊“確定”按鈕完成輸入。6.輸入7.單擊

8.拖動(dòng)鼠標(biāo)將F6單元格公式填充至F7:F10單元格。8.填充

查找與定位

用Excel進(jìn)行數(shù)據(jù)處理時(shí)，經(jīng)常會(huì)遇到查找或定位問題。在數(shù)據(jù)量較少時(shí)，通常會(huì)用鼠標(biāo)拖動(dòng)滾動(dòng)條到達(dá)需要的位置。數(shù)據(jù)量較多時(shí)，若要定位滿足條件的多個(gè)單元格，可通過定位功能完成操作。

1.分別在G7和H8單元格中輸入數(shù)值“1”，選中G7:H8單元格。

2.將鼠標(biāo)放在H8單元格的右下角，當(dāng)鼠標(biāo)變?yōu)椤啊毙螤顣r(shí)，拖動(dòng)鼠標(biāo)至D10單元格，完成數(shù)據(jù)的填充。1.輸入2.填充

3.選中G7:H10單元格，按組合鍵“Ctrl+G”，打開“定位”對(duì)話框。單擊“定位條件”按鈕，繼續(xù)打開“定位條件”對(duì)話框。3.單擊

4.選中“空值”項(xiàng)。

5.單擊“確定”按鈕完成定位。4.選中5.單擊

批量插入多行

1.單擊“開始”選項(xiàng)卡→“單元格”組→“插入”按鈕右側(cè)的箭頭→“插入工作表行”項(xiàng)。1.單擊

2.選中G8:H14單元格。

3.按“Delete”刪除鍵，刪除選中的數(shù)據(jù)。

在定位無規(guī)律的單元格時(shí)，往往為數(shù)據(jù)添加有規(guī)律的輔助項(xiàng)，如連續(xù)數(shù)字等，完成定位后再將輔助項(xiàng)刪除。2.選中3.刪除

定位與復(fù)制

1.選中A5:F5單元格。

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

鈕，完成對(duì)表頭的復(fù)制。

3.選中A6:F14單元格。

4.單擊“開始”選項(xiàng)卡→“編輯”組→“查找和選擇”按鈕→“定位條件”項(xiàng)。3.選中4.單擊1.選中2.單擊

5.在“定位條件”對(duì)話框中，選中“空值”項(xiàng)。

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

7.按組合鍵“Ctrl+V”粘貼表頭信息至定位單

元格。7.粘貼

定位文本

1.選中B7:B14單元格。

2.單擊“開始”選項(xiàng)卡→“編輯”組→“查找和選擇”按鈕→“定位條件”項(xiàng)。1.選中2.單擊

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

4.取消“數(shù)字”復(fù)選框的勾選狀態(tài)。

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

6.右鍵單擊定位選中的任意單元格。

7.在快捷菜單中，單擊“插入”命令，打開“插入”對(duì)話框。

8.選中“整行”項(xiàng)。

9.單擊“確定”按鈕完成插入。3.選中4.取消5.單擊6.右擊7.單擊8.選中9.單擊

批量增加表格邊框

1.選中A5:F18單元格。

2.單擊“開始”選項(xiàng)卡→“編輯”組→“查找和選擇”按鈕→“定位條件”項(xiàng)。

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

4.單擊“確定”按鈕完成定位設(shè)置。1.選中2.單擊3.選擇4.單擊

5.右鍵單擊定位選中的任意單元格。

6.在打開的“格式”懸浮欄中，單擊“其他框線”按鈕

，在下拉列表中選擇“所有框線”項(xiàng)。5.右擊6.選擇

定位公式

定位條件不可多選，當(dāng)表格中的數(shù)據(jù)不單一時(shí)，需多次定位方可完成設(shè)置。

1.選中A5:F18單元格。

2.單擊“開始”選項(xiàng)卡→“編