实验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.数据筛选中建立条件区域时,两个条件构成与的关系和两个条件构成或的关系,其建立的条件区域有何区别?