《跟着名师学电脑Excel应用入门实例》课件-第10章

实例10制作水电费缴费清单—定位单元格—相对引用—绝对引用—混合引用

学习情境

祥和社区后勤部管理员小王，每月负责收取小区用户的水电费。为避免错收、漏收费用现象的发生，故在收取费用前，需要制作水电费缴费清单并将其发给每位业主，既可以提高工作效率，又能够保证信息的准确性。

编排效果

掌握技能

通过本实例，将学会以下技能：

定位单元格。

相对引用。

绝对引用。

混合引用。

获取外部数据

Excel支持外部数据的导入，包括来自网站、数据库和文本格式的数据。

1.新建名为“水电费缴费清单”的Excel文档。

2.单击“数据”选项卡→“获取外部数据”组→“自文本”按钮，打开“导入文本文件”对话框。1.新建2.单击

3. 选择需导入文件所在位置。

4.选中文件预览区中所示文件。

5.单击“导入”按钮，打开“文本导入向导”对话框。

6.如果不需要设置，可直接单击“完成”按钮打开“导入数据”对话框，再单击“确定”按钮完成数据导入。3.选择4.选中5.单击6.单击

修改内容

由外部导入的数据，往往无法满足文档设计需要，需进一步增删数据。

1.按照图中实例，输入文本和数据。合并单元格A1:F1、A2:C2、A3:C3。

2.选中A1:F10单元格，设置文字对齐方式为“居中”。单击A1单元格，设置字体大小为12号，加粗。2.设置1.输入

3.按住“Ctrl”键，选中D2:D3、D6:F10单元格。

4.单击“开始”选项卡→“数字”组→“会计数字格式”按钮右侧的箭头。

5.在下拉列表中，选择“中文(中国)”项，设置所选单元格格式。4.单击3.选中5.选择

插入乘法运算公式

1.单击D6单元格。

2.输入“=”号，进入公式编辑状态。

3.单击B6单元格，引用该单元格的数据。

4.在编辑栏中输入“*”号。

5.单击C4单元格，引用该单元格的数据。

6.输入“+”号。

7.单击C6单元格，引用该单元格的数据，继续输入“*”号。

8.单击D3单元格，引用该单元格的数据，按“Enter”键完成公式输入。

单元格的绝对引用

公式中使用单元格的数据，根据单元格的地址是否发生变化，分为相对引用、绝对引用和混合引用三种引用方式。一般情况下为相对引用方式，单元格的地址会随着公式位置的改变而改变。若不希望公式中引用的值发生变化，可使用绝对引用方式。

修改单元格的引用方式为绝对引用时，需在引用单元格的列号和行号前加入“$”符号，以保证被引用的单元格地址不变。输入前，需调整输入法为英文状态。

1.双击D6单元格，激活公式编辑状态。

2.在D2单元格的列标“D”和行号“2”的前边分别输入“$”符号。1.双击2.输入

切换引用方式

选中公式中的单元格地址。按一次“F4”键，列号和行号前自动添加“$”符号。按两次“F4”键，行号前自动添加“$”符号。按三次“F4”键，行号恢复，列号前自动添加“$”符号。按四次“F4”键，单元格恢复相对引用状态。

3.选中D6单元格公式中的“D3”，按一次“F4”键。

4.按“Enter”键完成公式输入，拖动鼠标将D6单元格公式填充至D7:D10单元格。

插入嵌套公式

1.单击F6单元格。

2.单击编辑栏“插入函数”按钮，打开“插入函数”对话框。1.单击2.单击

3.单击“或选择类别”列表框的下拉箭头，在下拉列表中选中“逻辑”类函数。

4.在“选择函数”列表中，选中“IF”函数。

5.单击“确定”按钮，打开“函数参数”对话框。3.单击4.选中5.单击

6.在“Logical_test”参数后，输入“(D6-E6)>0”；在“Value_if_true”参数后，输入“D6-E6”；在“Value_if_false”参数后，输入“0”。

7.单击“确定”按钮完成输入。6.输入7.单击

8.拖动鼠标将F6单元格公式填充至F7:F10单元格。8.填充

查找与定位

用Excel进行数据处理时，经常会遇到查找或定位问题。在数据量较少时，通常会用鼠标拖动滚动条到达需要的位置。数据量较多时，若要定位满足条件的多个单元格，可通过定位功能完成操作。

1.分别在G7和H8单元格中输入数值“1”，选中G7:H8单元格。

2.将鼠标放在H8单元格的右下角，当鼠标变为“”形状时，拖动鼠标至D10单元格，完成数据的填充。1.输入2.填充

3.选中G7:H10单元格，按组合键“Ctrl+G”，打开“定位”对话框。单击“定位条件”按钮，继续打开“定位条件”对话框。3.单击

4.选中“空值”项。

5.单击“确定”按钮完成定位。4.选中5.单击

批量插入多行

1.单击“开始”选项卡→“单元格”组→“插入”按钮右侧的箭头→“插入工作表行”项。1.单击

2.选中G8:H14单元格。

3.按“Delete”删除键，删除选中的数据。

在定位无规律的单元格时，往往为数据添加有规律的辅助项，如连续数字等，完成定位后再将辅助项删除。2.选中3.删除

定位与复制

1.选中A5:F5单元格。

2.单击“开始”选项卡→“剪贴板”组→“复制”按

钮，完成对表头的复制。

3.选中A6:F14单元格。

4.单击“开始”选项卡→“编辑”组→“查找和选择”按钮→“定位条件”项。3.选中4.单击1.选中2.单击

5.在“定位条件”对话框中，选中“空值”项。

6.单击“确定”按钮完成定位设置。5.选中6.单击

7.按组合键“Ctrl+V”粘贴表头信息至定位单

元格。7.粘贴

定位文本

1.选中B7:B14单元格。

2.单击“开始”选项卡→“编辑”组→“查找和选择”按钮→“定位条件”项。1.选中2.单击

3.在“定位条件”对话框中，选中“常量”项。

4.取消“数字”复选框的勾选状态。

5.单击“确定”按钮完成定位设置。

6.右键单击定位选中的任意单元格。

7.在快捷菜单中，单击“插入”命令，打开“插入”对话框。

8.选中“整行”项。

9.单击“确定”按钮完成插入。3.选中4.取消5.单击6.右击7.单击8.选中9.单击

批量增加表格边框

1.选中A5:F18单元格。

2.单击“开始”选项卡→“编辑”组→“查找和选择”按钮→“定位条件”项。

3.在“定位条件”对话框中，选择“常量”项。

4.单击“确定”按钮完成定位设置。1.选中2.单击3.选择4.单击

5.右键单击定位选中的任意单元格。

6.在打开的“格式”悬浮栏中，单击“其他框线”按钮

，在下拉列表中选择“所有框线”项。5.右击6.选择

定位公式

定位条件不可多选，当表格中的数据不单一时，需多次定位方可完成设置。

1.选中A5:F18单元格。

2.单击“开始”选项卡→“编