Excel电子表格.docx

1、Excel电子表格 第五章 Excel2002电子表格 第五章 Excel 2002电子表格 5.1 Excel基本知识 电子表格软件广泛应用于统计、财务、会计、金融和审计等众多领域。任一种电子表格软件均有以下基本功能： 实现对数据的编辑、修改、管理、保存和使用。 制作各种各样的数据表格。 利用原始数据或通过分析得到的数据，生成各种图表。 进行数据统计，分析预测。 可添加图形、图像效果，增强表现力。 5.1 Excel基本知识工作簿与工作表 5.1 Excel基本知识 要使用Excel处理数据，首先应该创建或打开工作簿，在工作表的单元格中输入数据，并进行一定的格式设置，然后保存在外存中，其操作

2、方法与Word相似。 例：创建一个名为“第一季度销售统计.xls”的空白工作簿，保存在“我的文档”。后续各节中的操作如无特别说明，均在此工作簿中进行。 5.1 Excel基本知识 添加工作表 新建一个工作簿时，Excel默认有三张工作表，如果工作表个数不够使用，可以手工添加一些工作表。 操作方法：单击窗口底部的工作表标签，选定某个工作表，选择“插入/工作表”菜单命令，新添加的工作表位于选定工作表之前。 删除工作表 右击要删除的工作表标签，在快捷菜单中选择“删除”命令，确认删除操作后，无法恢复。 5.1 Excel基本知识 默认的工作表名称为Sheet1，Sheet2， ，通常应将其改成与工作表

3、内容相关的名称，便于使用。 操作方法：双击工作表标签，输入新名称后回车。 例如：把工作簿“第一季度销售统计”的表Sheet1命名为“1月份销售”；Sheet2命名为“2月份销售”；Sheet3命名为“3月份销售”。 为方便识别，还可以给工作表标签添加颜色。 右击要添加颜色的工作表标签，在快捷菜单中选择“工作表标签颜色”命令。 5.1 Excel基本知识 在工作簿内移动或复制工作表 单击待移动的工作表标签，按住鼠标左键向左或向右拖动到合适位置。 单击待复制的工作表标签，按住Ctrl键后拖动选定的工作表。在新位置出现一个完全相同的工作表。例如，复制Sheet3得到Sheet3（2），根据需要重命名

4、工作表。 5.2 工作表的基本操作 数值型数据： 数值型数据在单元格中输入后默认为右对齐 。 数据输入形式可以是： 12 +12 -12 5,621,234 $123.68 30% （123） 1.87625E3 输入分数时要先输入0和空格。 如1/4 ，正确输入是：0 1/4 否则Excel 将把分数当成日期处理（即：1 月4 日）。 5.2 工作表的基本操作 文本型数据： 文本型数据在单元格中默认为左对齐 。 输入的字符串超过单元格宽度时，若右侧单元格为空，则超宽部分一直延伸到右侧单元格；若右侧单元格有内容，则字符串超宽部分被隐藏，但在编辑栏可见其全部内容。 5.2 工作表的基本操作 日期

5、、时间型数据： 当输入的数据符合Excel 日期或时间格式时，在单元格中默认为右对齐 。 在“ 单元格格式” 对话框的“ 数字” 选项卡中选择“ 日期” 和“ 时间” 项，可看到所有Excel 可接受的形式。 例如：2005-10-01 是Excel 可接受的日期形式； 2005.10.1 不代表日期，将被作为普通文本。 5.2 工作表的基本操作 例如，在某一单元格首次输入03/10/1并回车，然后再次输入32，回车后单元格中显示的不是32而是1900-2-1（1900年2月1日是该年的第32天）。 将该单元格转换成数值格式的操作是： 单击要改变格式的单元格； 单击“ 格式/ 单元格” 菜单命

6、令，在“ 单元格格式” 对话框选择“ 数字” 选项卡； 在“ 分类” 栏中单击“ 数值” 项并“ 确定” 。 5.2 工作表的基本操作 5.2 工作表的基本操作 数字序列填充 例如，“序号”列数据的输入，有两种方法： 在起始单元格A2中填入1，再在A3中填入2，用鼠标选定A2和A3，拖动选定框右下角的填充柄到A24单元格。 在起始单元格A2中填入1，选择“编辑/填充/序列”菜单命令，在“序列”对话框中设置。 5.2 工作表的基本操作 日期序列填充 日期、月份、星期都是系统默认的序列数。只需输入第一个数，然后按住鼠标右键拖动填充柄至预定的单元格放开，在快捷菜单中选择相应的填充方式。 5.2 工作

7、表的基本操作 文本序列填充 文本中没有数字：填充操作只是复制初始单元格的内容。如在“产品类别”列输入“数码相机”时，可先在B2中输入“数码相机”，按住鼠标左键拖动填充柄至B10。 文本中全为数字：即数字作为文本处理的情况下，填充时将按等差序列（步长为1）进行。 文本中含有数字：填充时字符部分不变，数字按等差序列（步长为1）处理。 5.2 工作表的基本操作 单个单元格的选定：单击该单元格即可。 单元格区域的选定： 单击选定该区域的第一个单元格，然后拖动鼠标直至最后一个单元格； 单击该区域第一个单元格，按住Shift键，再单击该区域最后一个单元格。 不连续单元格区域的选定：先选定第一个单元格区域，

8、按住Ctrl键，再选定其他的单元格区域。 5.2 工作表的基本操作 在Excel中，选定的单元格区域地址表示为“起始单元格：终止单元格”，如A1:E10。它能够准确表达要处理的数据范围，但无法从中了解这些数据的含义，不便于数据的引用和处理。 可考虑对单元格、单元格区域进行命名，改用描述性的名称来代表某一数据区域。名称可以看作是对单元格或单元格区域给出的易于辨认和记忆的标记。 5.2 工作表的基本操作 例如，将“1月份销售”工作表中的单元格区域E1:E24命名为“原价”， F1:F24 命名为“平价售价”， G1:G24命名为“销售数量”。 操作方法 选定单元格区域（如E1:E24）； 使用“插

9、入/名称/定义”菜单命令打开“定义名称”对话框； 如果选择的单元格区域有标题，则标题显示在“在当前工作簿中的名称”框中，可以使用标题作为名称，也可以输入新的名称，然后单击“确定”。 5.2 工作表的基本操作 注意： 给单元格区域定义的名称会随工作簿一起保存，在工作簿的任何一个工作表中，编辑栏的名称框都提供这些名称，即它们可以在同一个工作簿中的任何地方使用（在后续的公式计算中介绍）。 “名称”的命名规则： 第一个字符必须是字母或下划线,名称中的字符可以是字母（不区分大小写）、数字、小数点和下划线。 名称中不能有空格，不能与用行号、列标表示的单元格地址相同。例如 no sale，C5，A1:D4

10、等都是不合法的。 5.2 工作表的基本操作 在“1月份销售”工作表的“统计日期”字段前插入两列“销售额”和“折扣率”；在顶部插入两行输入表格的题目“数码产品专卖店第一季度销售统计”。操作步骤如下： 选定“ 统计日期” 列，选择“ 插入/ 列” 命令； 选定工作表中的前两行，选择“ 插入/ 行” 命令； 表格标题一般比较醒目，放在一个合并的单元格中。选定要合并的单元格区域，如A1:J2 ，单击格式工具栏上的“ 合并及居中” 按钮，可把所选区域合并成一个单元格且输入的内容居中。（再次单击该按钮可以取消合并） 输入文本数据。 5.2 工作表的基本操作 有时需要对表格的部分单元格数据进行位置的调整，或

11、者单元格中的数据是相同的或只有少许差别，可以复制单元格，以减少输入工作。 常用操作有两种： 选中要移动或复制内容的单元格或单元格区域，单击“ 编辑” 菜单的“ 剪切” 或“ 复制” 命令；选定目的单元格，单击“ 编辑/ 粘贴” 命令。 选定单元格或单元格区域，将鼠标指针置于选定框框线 上，当鼠标指针变成十字型双向箭头时，按住鼠标左键拖动到新位置。在拖动过程中按下Ctrl 键，则是复制。 5.2 工作表的基本操作 删除单元格及其内容 选定要删除的单元格或单元格区域； 选择“编辑/删除”命令，在打开的“删除”对话框中设置，单击“确定” 。 只删除单元格中的内容，而保留删除后的空白单元格，应使用“编

12、辑/清除”命令，或直接按Delete键。 5.2 工作表的基本操作 当需要重新查看或修改工作表中的某一部分内容时，可以查找或替换指定的任何数据，包括文本、数字、日期，还可查找和替换公式和批注，操作方法与Word类似。 5.2 工作表的基本操作 Excel工作表为白色背景，略显简单呆板，同时工作时间长了眼睛易疲劳。可以给工作表更换背景： 选择“格式/工作表/背景”命令，在“工作表背景”对话框中选择一个图片文件，单击“插入”按钮。所选择的图片作为背景平铺在工作表中。 选择“格式/工作表/删除背景”菜单命令，可以取消设置，恢复为默认背景。 5.2 工作表的基本操作 Excel工作表的灰色网格线是为输

13、入、编辑方便而预设置的，在打印时，网格线无法打印出来。若希望在打印时能清楚的区分每个单元格，或为了强调工作表的某一部分，可以给单元格或单元格区域加上特殊边框和底纹。 例如，给“1月份销售”工作表中的单元格区域A1:J30加上边框和底纹。操作如下： 选定单元格区域A1:J30； 单击“格式/单元格”命令，打开“单元格格式”对话框，在“边框”选项卡设置框线，在“图案”选项卡设置底纹。 5.2 工作表的基本操作 当单元格高度或宽度不够，输入的内容只显示一半，或显示一串“”号。调整行高和列宽的方法相同，以调整列宽为例 : 用鼠标拖动调整一列的宽度 ：把光标移动到列标的边界，当变成双箭头时，按下鼠标左键

14、，拖动边界。 用鼠标拖动调整多列的宽度 ：如果要一次调整多列的宽度，先选定要调整的所有列标，然后拖动其中一个列标的边界。 用菜单设置精确列宽 ：选定所需调整的区域，单击“ 格式/ 列/ 列宽” 命令，在“ 列宽” 对话框输入精确的列宽值。 5.2 工作表的基本操作 主要是设置单元格中文本、数字的格式以及在单元格中的对齐方式。两种方法： 直接用格式工具栏相关按钮进行简单设置。 使用“格式/单元格”命令打开“单元格格式”对话框，选择“字体”、“数字”以及“对齐”选项卡进行设置。 5.2 工作表的基本操作 在Excel工作表中，可以根据一定的条件突出显示某些单元格。 例如，在“1月份销售”工作表中，

15、要突出显示“销售数量”高于40和低于15的单元格 ： 选定要设置条件格式的单元格区域G4:G26； 单击“格式/条件格式”命令，打开“条件格式”对话框进行设置。 5.2 工作表的基本操作 给某些特殊单元格添加批注解释数据含义 例如，给单元格G5添加批注，表明该数据为“超额完成销售任务” 。 添加批注：单击要添加批注的单元格，执行“插入/批注”菜单命令，在输入框中输入批注内容。单元格的右上角出现红色小三角标记。 修改批注：选中相应单元格，执行“插入/编辑批注”。 删除批注：在相应单元格右击，在快捷菜单中选择“删除批注”。 5.2 工作表的基本操作 导入图片或绘制图形 例如，在单元格插入图片文件；

16、或用“绘图”工具栏绘制简单图形；或将“画图”等程序画好的图形粘贴到Excel工作表的某一位置。 导入的图形作为独立对象，不占用单元格的空间，浮在工作表的上方，将掩盖工作表部分区域。默认情况下，导入的图形对象“大小固定, 位置随单元格而变”。 改动图片格式：选中图形，使用“格式/图片”命令的“设置图片格式”对话框 。也可用“图片”工具栏中的命令按钮实现 “裁剪”等操作。 5.3 公式和函数的应用 5.3 公式和函数的应用 公式：公式由运算符、常量、单元格引用、名称和函数等元素构成。公式必须以等号（）开头，Excel才会作为公式进行计算，否则会把输入的式子当作字符串处理，直接填入单元格中。 5.3

17、 公式和函数的应用 例1 打开“学生成绩数据表.xls”工作簿文件的sheet1工作表，计算各学生所获得的总分和平均分。 单击单元格F2，输入总分计算公式“=C2+D2+E2”并回车，F2中出现计算结果“214”，对应的公式出现在编辑栏。 单击单元格G2，输入平均分计算公式“=F2/3”并回车，G2中显示计算结果“71.333333”。 同理, 在39行计算其他学生的总分和平均分。如对单元格F3，输入“=C3+D3+E3”, 依此类推。 5.3 公式和函数的应用 5.3 公式和函数的应用 5.3 公式和函数的应用 单元格地址的相对引用 在上例中，将F2单元格中的公式复制到F3时，F2中的公式会

18、自动由“=C2+D2+E2”变为“=C3+D3+E3”而不是原样照搬。公式中的单元格地址会随着公式所在单元格位置的改变而改变，这种单元格地址的引用方式称为相对引用。 5.3 公式和函数的应用 单元格地址的绝对引用 在复制和移动公式时，如果不希望公式中引用的单元格地址发生变化（例如，公式中某一项的值固定放在某一单元格中），此时需要作绝对引用。绝对引用的表示形式是在单元格地址之前加“$”。 如：$D$1表示单元格地址固定为D1。 $D1表示列固定为D，行是相对引用。 若单元格F2中的公式为“=$C$2+$D$2+$E$2”，则复制到单元格F3后公式仍保持“=$C$2+$D$2+$E$2”不变，F3

19、的值仍为F2之值。 5.3 公式和函数的应用 例2 设上例sheet1工作表中的成绩是期中考成绩，拟将总分的15累加入期末考总成绩。现要先算出“总分的15”是多少。 如“学生成绩数据表.xls”中sheet2工作表所示，H2单元格应输入公式：“=F2*$F$11”，其中，相对引用F2，绝对引用F11。因为百分比值15固定存放在F11中，不应随公式的复制而改变其单元格地址。 当然，直接在H2单元格输入公式“=F2*15”，再进行公式复制，也能解决问题。 5.3 公式和函数的应用 5.3 公式和函数的应用 例1： 打开“学生成绩数据表.xls”的sheet1工作表，改用函数计算各学生所获得的总分和

20、平均分: 单击单元格F2，总分计算公式=C2+D2+E2 可改用 sum(C2:E2) 单击单元格G2，平均分计算公式=F2/3 可改用 average(C2:E2) 函数及其参数可直接通过键盘手工输入，也可以使用 “插入/函数”命令，在“插入函数”对话框选用sum函数，在进一步打开的“函数参数”对话框确定参数值。 5.3 公式和函数的应用 例2：在函数参数中使用已定义的名称 教材P251：计算“1月份销售”表中“销售额”的总和，把结果置于H27中；计算“原价”最大值与“平价售价”最大值的差额，将结果置于E27中。 单击单元格E27 ，编辑栏中公式为： =MAX(原价)MAX(平价售价) “

21、原价” 和“ 平价售价” 是预先定义的两个名称，分别代表单元格区域E3:E26 和F3:F26 ，该公式等同于： =MAX(E3:E26)MAX(F3:F26) 单击单元格H27 ，编辑栏中的公式：sum(H4:H26) 不能写成：sum( 销售额 ) ，因为对H4:H26 并未事先定义为“ 销售额” 这样的名称。 5.3 公式和函数的应用 例3：在“第一季度销售统计.xls”工作簿中插入一个新的工作表，命名为“1月份统计表”，计算各类别产品的销售额占总销售额的百分比 。以计算“数码相机”的销售额比例为例: 单击单元格B3，打开“插入函数”对话框，选择SUM函数。 切换到“1月份销售”工作表，

22、选中H4:H12，单击“函数参数”对话框的“确定” ，回到“1月份统计表”。 光标定位在编辑栏并输入除号，再切换到“1月份销售”，选中H27，继续在编辑栏输入“*100”并回车，回到“1月份统计表” 。 将结果精确到小数点两位。 5.4 图表的应用 5.4 图表的应用 柱形图：用来显示一段时间内数据的变化或者描述各项数据之间的差异。 条形图：用来显示特定时间内各项数据的变化情况，或者比较各项数据之间的差别，分类项垂直组织，数据值水平组织，突出数值的比较，而淡化随时间的变化。 饼图：体现的是一个整体中每一部分所占的比例； 折线图：通常用来显示等间隔时间的数据变化趋势，常见的如股票价格的折线图等。

23、 5.4 图表的应用 使用“插入/图表”菜单命令打开“图表向导”对话框 第一步：选择图表类型。本例要比较各数据间的差异，选择“ 柱形图” 中的“ 三维堆积柱形图” 。 第二步：选择图表源数据。即选定作图所需的数据和标志区。本例选中A1:B5数据区域。 第三步：设置图表选项。包括图表的标题、坐标轴、是否显示图例等选项。本例只设置“标题”选项卡。 5.4 图表的应用 嵌入式图表： 单击图表空白区域，图表边界四周出现8个小方块表示已选定图表。 拖动小方块，可使图表缩小或放大。 拖动图表区的任一部分，可使图表在工作表中移动。 按DEL键可删除图表。 独立图表: 独立图表的移动和删除实际就是移动和删除图

24、表所在的工作表。 5.4 图表的应用 例如：将“1月份统计表”中建立的柱形图改为饼图，具体操作如下： 单击图表区选中图表（如果是图表工作表则单击相应的工作表标签）； 选择“图表/图表类型”命令，或右击图表区，从快捷菜单中选择“图表类型”命令，打开“图表类型”对话框； 在“标准类型”选项卡中选择“饼图”类型的“三维饼图”子类型，并单击“确定” 。图表修饰示例1：基本操作方法：1. 修饰图表区的背景 选择“图表区”，单击图表工具栏上的“图表区格式”按钮，在“图表区格式”对话框选择“填充效果”，选择“过渡”选项卡中“预设”的“茵茵绿原”项； 2. 修饰数据系列中各数据点对应的饼图区域 单击选中饼图，

25、再单击某数据点对应的扇形块，选择图表工具栏上的“数据点格式”按钮，在“数据点格式”对话框选择“填充效果”，三个数据点图块分别设置： “过渡”选项卡中“预设”的“麦浪滚滚”、“红日西斜”，以及 “纹理”选项卡的“水滴”。图表修饰示例2: 5.5 数据管理与分析 5.5 数据管理与分析 用户在建立数据清单时应遵循： 数据清单的第一行应是各列的标题（字段名），以后可利用它查找和组织数据；第二行开始的各行均为数据清单的具体记录。； 同一列数据中，列标题的字体、格式等应区别于其他数据，标题除外的同列数据的类型和格式应一致； 不要在一个数据清单中放置空白行或列， 数据清单中不能有合并单元格； 避免在一张工

26、作表中建立多个数据清单。特殊需要时，应该 用空行和空列分隔每一个数据清单。 数据清单示例 5.5 数据管理与分析 判断工作表中已有的部分数据是不是数据清单 选中单元格区域。例如，选中“学生成绩数据表.xls”中sheet3的单元格区域C4:G12。 启用“数据/记录单”菜单命令，如果选中的数据能够满足生成数据清单的条件将正常打开“记录单”对话框。 5.5 数据管理与分析 新建一个数据清单 以上考虑的是已有的数据是否满足生成数据清单的条件，如果是，就可以进行一系列针对数据清单的数据管理操作，如排序、筛选、分类汇总等。 另外，如果要从无到有新建一个数据清单，除了直接在工作表中输入数据外，也可使用“

27、数据/记录单”命令。 方法是：在某一区域内（如A1：F1）依次输入各列标题（字段名）后，再单击其下一行的单元格（如A2）。单击“数据/记录单”命令，在弹出的提示框中单击“确定”，出现“记录单”对话框。在各字段框中输入新记录的字段值。 5.5 数据管理与分析 添加记录： 在数据清单内添加记录有两种方法，一种是直接输入数据至单元格内；一种是利用“记录单”对话框。例如，在“1月份销售”工作表第27行前添加1个记录： 在第27 行前插入空行，以便添加记录； 选定想加入记录的数据清单，即A3:J26 ； 选择“ 数据/ 记录单” 命令打开对话框，单击“ 新建” 按钮，在空白记录框中输入数据（公式计算字段

28、无需输入）。 5.5 数据管理与分析 删、改记录： 可以直接在工作表选中要删除的数据并使用“编辑/删除”命令，或选中被修改数据所在单元格，在编辑栏更改数据；也可以使用“记录单”对话框： 打开记录单对话框，通过滚动条，或者“ 上一条” 、“ 下一条” 按钮找到需要修改或删除的记录； 单击字段框进行字段值的修改（注意：记录单中通过公式计算的字段不能修改）。 对于记录单中当前正在修改的记录，可用“还原”放弃修改。 单击“删除”按钮，可以删除当前显示的记录。 5.5 数据管理与分析 如果数据清单的字段数和记录数较多，可采用条件定位法，根据目标记录应满足的条件来查找。 例如，在“1月份销售”工作表查找“

29、销售数量”大于30的记录 ： 选中区域A3:J26 ，打开“记录单”对话框，单击“条件”按钮将出现条件输入对话框，在“ 销售数量” 右侧的条件框中输入“30”并回车。 如果在多个条件框中输入查找条件，各条件是“与”的联接关系。 用“上一条”、“下一条”按钮翻查满足条件的记录。 5.5 数据管理与分析 5.5 数据管理与分析 排序方法： 单字段排序 在需要排序的数据列中单击任一单元格。 单击工具栏的“ 升序排序” 或“ 降序排序” 按纽。 多字段排序 在需要排序的数据清单中，单击任一单元格。 选择“ 数据/ 排序” 菜单命令打开“ 排序” 对话框。 在“ 主要关键字” 、“ 次要关键字” 和“

30、第三关键字” 框中，选择需要排序的列。 选中所需的其他排序选项，再单击“ 确定” 。 多字段排序： 5.5 数据管理与分析 例如，在“1月份销售”工作表按“产品类别”升序排列，“产品类别”相同的再按“品牌”升序排列，“品牌”也相同的再按“销售额”从高到低排序。 打开工作表，选择数据清单A3:J26，单击“数据/排序”命令，打开“排序”对话框。 在“主要关键字”、“次要关键字”、“第三关键字”下拉列表框中，分别选择所要排序的字段名称，同时选择排序方式。 选中“有标题行”项，即表示标题行不参加排序(否则标题行也参加排序)。 需要的话单击“ 选项” 按钮打开 “ 排序选项” 对话框，选择合适的选项。 5.5 数据管理与分析 数据筛选就是将符合条件的数据集