实验5 电子表格 一、实验目的 1.掌握工作表和工作簿的基本操作。 2.掌握公式和基本函数的使用。 3.掌握数据清单的管理操作。 4.掌握图表的创建、修改以及修饰等操作。 二、实验环境 1.硬件环境：微机 2.软件环境：Windows XP中文版 Excel 2003中文版 三、实验内容 1.Excel的启动及其窗口 执行“开始”→“所有程序”→“Microsoft office” →“Microsoft office Excel 2003”命令，或双击桌面上的Excel快捷图标，打开Excel应用程序窗口，如图E5-1所示。 ⑴Excel工作窗口的组成 从图中可以看到，Excel的工作窗口由标题栏、工具栏、编辑栏、工作区、任务窗格、标签栏以及状态栏组成。 ⑵工作表 工作区是一张表格，称为工作表，表中每行由数字1、2、3等行名标识，每列由A、B、C等列名标识，行与列交叉的方格称为单元格。 ⑶单元格地址 单元格以由列号和行号组成的单元格地址标识，如地址C6表示第C列第6行的单元格。形式“C6”称为相对地址；在列号和行号前加入“$”符号便构成绝对地址，如“$C$6”；“$C6”或 “C$6”称为相对地址。 ⑷工作簿 窗口下面的标签栏上标有“Sheet1”、“Sheet2”、“Sheet3”，表示有三张工作表，具有下划线且白底的工作表名称为当前工作表，单击工作表名称可选择工作表。若干张工作表组成一个工作簿。  图E5-1 Excel工作窗口 2.工作表的建立 ⑴在工作表中输入数据 启动Excel后，在空白的工作表中输入如图E5-2所示的数据。操作步骤：  图E5-2 员工工资表 ①文字的输入：单击选中单元格后直接输入，如在A1单元格输入“畅想公司员工工资表”。输入区域A2：G2、区域B3：B14、区域D3：D14的文字内容。 ②数字的输入：单击选中单元格后直接输入，如在E3单元格输入“1280”。在区域E3：E14输入每位员工的基本工资。 ③等差数列的输入：在A3单元格输入“30001”，然后选中A3单元格，按住Ctrl键，移动鼠标到该单元格的右下角，当鼠标指针变为一个加号“+”时，向下拖动鼠标到A14单元格放开，则区域A1：A14中填充了公差为1的等差数列。 ④函数的输入：补贴的金额是由所属部门决定的，如果属于销售部，补贴为600，如果属于市场部，补贴为500。使用函数来决定补贴的值。选中F3单元，执行“插入/函数”命令，出现“插入函数”对话框，首先在“选择类别”下拉列表框中选择“逻辑”类别，然后在“选择函数”列表框中选择“IF”函数，单击“确定”按钮。出现如图E5-3所示的“函数参数”对话框。  图E5-3 IF函数的输入 在“Logical_test（逻辑条件）”框中输入“D3=”销售部””，在“Value_if_true（条件成立）”框中输入600，在“Value_if_false（条件不成立）”框中输入500，单击“确定”按钮。 ⑤公式的复制：将F3中的函数复制到区域F4：F14。选中F3单元格，单击常用工具栏上的“复制”按钮，再选中区域F4：F14，单击常用工具栏上的“粘贴”按钮，则F3单元格的内容复制到区域F4：F14。 ⑥日期的输入：使用“/”或“-”作为分隔符，输入格式为“年/月/日”，如“78/5/11”。 ⑦公式的输入：图中每位员工的工资总额为其基本工资与补贴之和，例如对于员工“陈欣荣”，单元格D3、E3、F3满足关系：F3=D3+E3，所以在单元格F3中输入公式：=D3+E3，符号“=”表示公式的开始。然后使用⑤中的单元格复制方法，将单元格F3的公式复制到区域F4：F14。 ⑵保存工作簿 执行“文件/保存”命令，出现“另存为”对话框，在“保存位置”选择“我的文档”，在“文件名”中输入“Salary”，在“保存类型”中选择“Microsoft Office Excel 工作簿”，单击“保存”按钮。 3.编辑工作表 ⑴工作表改名 双击标签栏上的标签“Sheet1”，当它处于被选中状态时，输入新的工作表名“工资表”。 ⑵复制工作表 单击工作表标签“工资表”，按住Ctrl键，并拖动标签“工资表”到“Sheet2”与“Sheet3”之间松开，则工作表“工资表”被复制到“工资表（2）”。 ⑶删除工作表 单击工作表标签“Sheet2”，使其成为当前工作表，然后执行“编辑/删除工作表”命令，删除工作表“Sheet2”。 ⑷插入工作表 单击工作表标签“工资表（2）”，执行“插入/工作表”命令，插入工作表“Sheet4”。 ⑸插入行 单击行名“8”选中第8行，执行“插入/行”命令插入一个空行，在B8：E8区域依次输入：郑明忠、1981-2-18、财务部、1600，重新生成A3：A15的等差数列，将F7：G7区域复制到F8：G8区域。 ⑹插入列 单击列名“C”选中第“C”列，执行“插入/列”命令插入一个空列；再次选中“C”列，执行“编辑/删除”命令删除之。 4.格式化工作表 结果如图E5-4所示。  图E5-4 格式化后的工作表 ⑴选中A1：G1区域，单击格式工具栏的“合并及居中”按钮；选中A1单元格，使用格式工具栏设置其格式为18号、蓝色、粗体、宋体字，填充颜色为浅青绿色。 ⑵将第二行设置为12号、粗体、宋体、黑色、居中。 ⑶选择区域C3：C15，执行“格式/单元格”命令，出现“单元格格式”对话框，选中“数字”卡，在“分类”列表框中选择“日期”，在“类型”列表框中“14-Mar-01”格式，单击“确定”按钮。 ⑷选择区域E3：G15，执行“格式/单元格”命令，在“单元格格式”对话框选中“数字”卡，在“分类”列表框中选择“数值”，在“小数位数”中设置为“2”，单击“确定”按钮。 ⑸单击列名A并拖动鼠标到列名G，选中A-G列，执行“格式/列”命令，选择“最适合的列宽”；选择第2-15行，执行“格式/行”命令，选择“最适合的行高”；选中第1行，执行“格式/行”命令，选择“行高”，在“行高”对话框中输入“30”，单击确定按钮。 ⑹执行“文件/页面设置”命令，出现“页面设置”对话框，选中“页面”卡，设置方向为“纵向”，纸张大小为“A4”；选中“页眉/页脚”卡，在“页眉”下拉列表框中选择“工作表”，在“页脚”下拉列表框选择“制作人……第一页”；在“工作表”卡选中“网格线”复选框。 ⑺执行“文件/打印预览”查看打印的效果。 保存工作簿。 5.数据清单操作 打开工作簿Salary，选中工作表“工资表”为当前工作表。在Excel中可以把工作表中的数据作为类似数据库的数据清单，如图所示的二维表，第二行的名称称为字段名，以下的每一行称为记录，该清单中具有7个字段，13个记录。进行以下的数据清单操作。 ⑴排序 ①要求：按“出生日期”的升序排列 操作步骤：单击选中C2单元格，即字段名“出生日期”，单击常用工具栏上的“升序排序”按钮，则记录按出生日期从小到大排序。 ②要求：按“基本工资”降序排序，如果基本工资数额相同，则按“补贴”降序排序 操作步骤：定位在清单中任一单元格，然后执行“数据/排序”命令，出现“排序”对话框，在“主要关键字”列表框中选中“基本工资”，单击右侧的“降序”按钮；在“次要关键字”列表框中选中“补贴”，单击右侧的“降序”按钮；单击选中“有标题行”；单击“确定”按钮。 ③重新按“员工编号”的升序来排序。 ⑵筛选数据 ①要求：筛选出在1980年出生的员工，结果显示在原来数据清单区域 操作步骤：定位在清单中任一单元格，执行“数据/筛选/自动筛选”命令，每个字段名右侧出现一个下拉箭头，单击“出生日期”字段名右侧的下拉按钮，选择“自定义”项，出现如图E5-5所示的对话框，输入筛选条件，单击“确定”按钮。  图E5-5 自动筛选对话框 再次单击“出生日期”字段名右侧的下拉按钮，选择“（全部）”项，恢复所有记录。 ②要求：筛选出工资总额大于1500的市场部员工，结果显示在原来数据清单区域 操作步骤：单击“部门”右侧的下拉按钮，选择“市场部”；单击“工资总额”右侧的下拉按钮，选择“自定义”，出现“自定义自动筛选方式”对话跨框，在“工资总额”下拉列表框中选择“大于或等于”，在其右侧的列表框中输入“1500”，单击“确定”，得到如图E5-6所示的筛选结果。 恢复所有的数据，并执行“数据/筛选/自动筛选”命令，取消“自动筛选”命令前的选定符号“√”。  图E5-6 自动筛选的结果 ③要求：筛选出基本工资大于1000，同时工资总额大于1500的所有员工，并将筛选结果显示在以单元格A22为首的区域中 操作步骤：首先在区域A18：B19建立条件区域在A18输入“基本工资”，在B18输入“工资总额”，在A19输入“>1000”，在B19输入“>1500”。执行“筛选/高级筛选”命令，出现“高级筛选”对话框如图E5-7所示。选中“将筛选结果复制到其他位置”，在列表区域输入“$A$2:$G$15”，在“条件区域”输入“$A$18:$B$19”，在“复制到”输入“$A$22:$G$22”，单击“确定”，得到如图E5-7所示的筛选结果。  图E5-7 高级筛选对话框及筛选结果 ④要求：筛选出基本工资大于1000，或者工资总额大于1500的所有员工，并将筛选结果显示在以单元格A30为首的区域中。 操作步骤：首先建立条件区域，将单元格B19的内容移动到B20，则区域A18：B20组成了新的条件区域。执行筛选/高级筛选”命令，在“高级筛选”对话框中，选中“将筛选结果复制到其他位置”，列表区域与③中设置相同，条件区域输入“$A$18:$B$20 ”，在“复制到”输入“$A$30:$G$30”，单击“确定”。筛选结果如图E5-8所示。  图E5-8 “基本工资大于1000或工资总额大于1500”的筛选结果 比较③和④的条件区域设置以及筛选结果。 ⑶分类汇总 要求：统计各部门的补贴总金额 操作步骤： ①将数据清单按“部门”进行排序（升序降序皆可）。 ②选择数据清单中任一单元格，执行“数据/分类汇总”命令，出现 “分类汇总”对话框，在“分类字段”中选择“部门”，在“汇总方式”中选择“求和”，在“选定汇总项”中选择“补贴”，选中“替换分类汇总”、“汇总结果显示在数据下方”复选框，单击“确定”按钮。结果如图E5-9所示。 图E5-9 按部门分类汇总的结果 在“分类汇总”对话框中单击“全部删除”按钮可恢复清单原样。 ⑷使用数据透视表 要求：求出每个部门的工资总额的最大值、最小值及平均值 操作步骤： ①选择数据清单中任一单元格，执行“数据/数据透视表和数据透视图”命令，出现“数据透视表和数据透视图向导—3步骤之1”对话框，选中“Micrisoft Excel 数据清单或数据库”及“数据透视表”两项，单击“下一步”按钮。 ②出现“数据透视表和数据透视图向导—3步骤之2”对话框，确定“选定区域”输入项是正确，单击“下一步”按钮。 ③出现“数据透视表和数据透视图向导—3步骤之3”对话框，选择“现有工作表”作为透视表显示位置，并输入单元格地址“I3”；单击“布局”按钮，出现 “数据透视表和数据透视图向导—布局”对话框，如图E5-10所示。  图E5-10 数据透视表之布局对话框 对话框右部以按钮的形式列出了数据清单中的字段名，拖动“部门”按钮到“列”区域，连续三次拖动“工资总额”按钮到“数据”区域；双击第二个工资总额按钮，在打开的对话框中汇总方式为“最大值”，同样，设置第三个按钮的汇总方式为“最小值”。单击“确定”按钮回到“数据透视表和数据透视图向导—3步骤之3”对话框，单击“完成”按钮。得到结果如图E5-11所示。  图E5-11 数据透视表 ⑸使用数据库统计函数 数据库统计函数用于对满足给定条件的数据库记录进行统计。 要求：统计市场部的员工的工资总额的平均值，结果保留在I11单元格 操作步骤： ①建立条件区域：在K11单元格输入“部门”，在K12单元格输入“市场部“，则区域K11：K12为所建立的条件区域。 ②在单元格I11输入数据库统计函数：选中单元格I11，执行“插入/函数”命令，出现“插入函数”对话框，首先在“选择类别”下拉列表框中选择“数据库”类别，然后在“选择函数”列表框中选择“DAVERAGE”函数，单击“确定”按钮。出现如图E5-12所示的“函数参数”对话框。  图E5-12 数据库统计函数 在“Database（数据库区域）”中输入“A2：G15”，在“Field（被统计的列的编号）”中输入“7”，在“Criteria（条件区域）”中输入“K11：K12”，然后单击“确定”按钮。 使用相同的步骤统计基本工资>1000的员工的人数。（提示：使用Dcount或Dcounta函数）。 6.图表操作 ⑴创建图表 要求：将工资总额位于前5名的员工的基本工资、补贴、工资总额以柱形图进行比较，结果如图E5-13所示。  图E5-13 工资总额前5名柱形图 操作步骤： ①打开工作簿“Salary”，选择工作表“工资表”为当前工作表，将数据清单按“工资总额”的降序进行排序。 ②执行“插入/图表”命令，打开“图表向导-4步骤之1-图表类型”对话框。 ③选择图表类型。在“图表向导-4步骤之1-图表类型”对话框中选择“标准类型”卡，从“图表类型”列表框中选择“柱形图”，从“子图表类型”中选择“簇状柱形图”，单击“下一步”按钮，进入“图表向导-4步骤之2- 图表数据源”对话框。 ④选择图表数据源。在“图表向导-4步骤之2- 图表数据源”对话框中，选择“数据区域”卡，选中“数据区域”输入框中全部内容，然后在工作表中拖动选择区域“A2：G7”；选择“系列”卡，选中“分类（X）轴标志（T）”输入框中全部内容，然后在工作表中拖动选择区域“B3：B7”。单击“下一步”按钮，进入“图表向导-4步骤之3- 图表选项”对话框。 ⑤设置图表选项。在“图表向导-4步骤之3- 图表选项”对话框中，选择“标题”卡，在“图表标题”中输入“工资总额前5名”，在“分类X轴”中输入“员工姓名”，在“数值（Y）轴”中输入“金额”；选择“图例”卡，选中“显示图例”复选框，在“位置”中选择“靠右”。单击“下一步”按钮，进入“图表向导-4步骤之4- 图表位置”对话框。 ⑥设置图表位置。在“图表向导-4步骤之4- 图表位置”对话框中，选择“作为其中的对象插入”，单击“完成”按钮。将得到的图表放大并拖动到适当的位置。 ⑦格式化图表。在图表中双击图表标题“工资总额前5名”，打开“图表标题格式”对话框，将其格式设置为22号、隶书、粗体字。同法，可修改坐标轴、图例的格式。 ⑧修改图表。单击选中图表，执行“图表”命令，在其中选择“图表类型”、“源数据”、“图表选项”、“位置”可重新启动图表向导对每项进行修改。 要求：创建员工“陈欣荣”的基本工资、补贴的饼图，结果如图E5-14 所示。 四、思考题 1.选中某个填有内容的单元格，执行“编辑/清除”命令与执行“编辑/删除”命令有何区别？ 2.在C1单元格中输入公式“=A1+B1”，将C1中的公式复制到D2，则D2单元格中的公式内容是什么？将C1中的公式修改为“=$A$1+B1”，再次复制到D2单元格，则D2单元格中的公式内容是什么？说明公式中的绝对地址与相对地址在复制时有何区别？ 3.数据筛选中建立条件区域时，两个条件构成与的关系和两个条件构成或的关系，其建立的条件区域有何区别？