EXCEL实训指导书.doc

实验一、EXCEL基本操作一、实验目的：1 熟悉EXCEL基本操作环境2 掌握Excel工作薄、工作表、单元格等基本概念。3 掌握新建、保存、打开与关闭工作薄的方法。4 掌握输入各种类型数据的方法。5 掌握填充柄的使用与自动填充的方法。二、实验内容及要求：1、启动Excel2、打开工作薄“实训一”，并回答下列问题：1)、它由几个工作表构成，其名称分别是什么？2)、“产品销售明细”表中的C9单元的内容是什么？3、打开/关闭Excel界面中的“编辑栏”和“状态栏”4、关闭以上工作薄5、创建一个新的工作薄，并在工作表sheet1中输入下列工件表内容，保存工作薄并命名为“人事档案表.xls”。（保存位置自定）三、实验步骤1、通过单击任务栏的开始菜单中的程序项实现；2、双出打开实训素材中的”实训一xls”工作薄文件；很简单：看看左下角工作表标签就明白了；“产品销售明细”表中的C9单元的内容是“PIV1.4G”。3、操作提示：通过“视图”菜单中的相应项目实现。4、选择菜单【文件】【关闭】思考：菜单命令【文件/关闭】与【文件/退出】功能有何不同？5、选择菜单【文件】【新建】，打开“新建工作薄”任务窗格选择“新建工作薄”任务窗格中的“空白工作薄”输入工作表数据内容操作提示：u “序号”栏为非数值数字，输入时要在数字前加单引号；u 为了提高输入效率，输入时请使用填充柄；选择菜单【文件】【保存】，打开“另存为” 对话框指定保存位置并输入工作薄名”人事档案表”，单击“保存”按钮实训二、编辑工作表与工作表操作一、实验目的：1. 掌握单元格的复制、移动、插入行、列或单元格与删除、清除单元格的方法2. 掌握工作表的选择、重命名、插入、复制、移动与删除的方法二、实验内容及要求：一）、工作表操作：打开工作薄“实训二”，并完成下列操作1、重命名工作表将“实训二”工作簿中的“工资表”、更名为“1月工资表”。2、插入工作表在“实训二”工作簿中的“1月工资表”的右侧插入两个工作表。3、移动工作表将“实训二”工作簿中的新插入的两个工作表移至“人事档案表”表的左侧。4、删除工作表删除“实训二”工作簿中的新插入的两个工作表。5、复制工作表利用“实训二”工作簿中的“1月工资表”工作表复制出两个工作表，要求排列在“1月工资表”的右侧，并命名为“2月工资表”和“3月工资表”。二）、编辑工作表1、利用剪贴板进行复制或移动利用剪贴板将人事档案表中“姓名”数据区域中的员工姓名复制到1月工资表、2月工资表和3月工资表中的相应区域。2、插入行、列1）、在人事档案表中的“出生日期”列的右侧插入1空白列，并在列标题输入“工龄”2）、在1月工资表中的倒数第二行插入一空白行，并输入行标题-“合计”3、清除与删除单元格1）、在数据输入练习工作表中清除等差序列1的格式。2）、在数据输入练习工作表中，删除多余的空行和空列。三、实验步骤一）、工作表操作：双击打开工作薄“实训二”1、重命名工作表操作步骤：鼠标指向工作表标签中的工作表“工资表”，右击弹出快捷菜单选择【重命名】输入工作表新名“1月工资表”2、插入工作表操作步骤：选择插入工作表位置：单击工作表标签中的工作表“1月工资表”右侧工作表，即 “数据输入练习” 工作表选择菜单【插入】【工作表】命令，插入一个工作表l 使用“快捷菜单”法也可以实现，试试看！重复步骤至再插入一个工作表3、移动工作表操作步骤：在工作表标签中单击选择要移动的一个工作表选择菜单【编辑】【移动或复制工作表】命令，打开对话框选择将移动到的位置，即“人事档案表”工作表，并按确定按钮l 使用“快捷菜单”法也可以实现，试试看！重复步骤至再移动一个工作表思考：你能一次性移动2个工作表吗？（提示：如果要同时移动2个文件，你会怎么办？）4、删除工作表删除“实训二”工作簿中的新插入的两个工作表。操作步骤：在工作表标签中同时选择要删除的2个工作表操作提示：先单击选择1个工作表，再按住CTRL键同时选择第2个工作表选择菜单【编辑】【删除工作表】命令l 使用“快捷菜单”法也可以实现，试试看！5、复制工作表操作步骤：在工作表标签中单击选择“1月工资表”工作表选择菜单【编辑】【移动或复制工作表】命令，打开对话框选择将复制到的位置，即“数据输入练习”工作表单击选定“建立副本”选项（打勾），并按确定按钮将复制过来的工作表“1月工资表(2)”重命名为“2月工资表”l 使用“快捷菜单”法也可以实现，试试看！重复步骤至再复制一个工作表二）、编辑工作表双击打开工作薄“实训二”1、利用剪贴板进行复制或移动操作步骤：选定人事档案表中的“姓名”数据区域B2：B21选择菜单【编辑】【复制】将单元光标定位到1月工资表中的B4单元，即第1个职员的姓名单元。选择菜单【编辑】【粘贴】l 使用“快捷菜单”法也可以实现，试试看！重复重复步骤至再复制到另外两个工作表思考：想想看你还有更好更快的方法吗？2、插入行、列1）、在人事档案表中的“出生日期”列的右侧插入列操作步骤：将单元光标定位到人事档案表中的“出生日期”列的右列选择菜单【插入】【列】在新插入列的标题单元输入“工龄”2）、在1月工资表中的倒数第二行插入一空白行，并输入行标题-“合计”操作步骤：将单元光标定位到1月工资表中的倒数第二行，即“领导签名”的上一行位置选择菜单【插入】【行】在新插入行输入标题文字“合计”3、清除与删除单元格操作步骤：1）、在数据输入练习工作表中清除等差序列1的格式。单击行标签选定数据输入练习工作表中的等差序列1一行选择菜单【编辑】【清除】【格式】2）、在数据输入练习工作表中，删除多余的空行和空列。操作提示：选选择待删除的行或列，再使用菜单命令【编辑】【删除】实训三、格式化工作表说明：.一、实验目的：1. 掌握设置字符格式的方法2. 掌握改变数字外观的方法3. 掌握改变列宽与行高的方法4. 掌握设置单元格的边框与底纹的方法5. 掌握使用条件格式的方法6. 掌握自动套用格式的方法二、实验内容及要求：打开工作薄“实训三”,完成下列操作：一）、在“人事档案表”中完成下列操作1、设置字符格式1）、将标题“人事档案表”设置为，字体黑体；字号14；颜色红色，居中对齐2）、将列标题设置为，字体仿宋体；字号12；颜色蓝色，居中对齐2、为表格增加边框：给“人事档案表” 数据区域A2：I22添加边框。外边框为双线蓝色，内边框为单线浅蓝色。其余部分不显示网格线。3、为表格增加底纹给“人事档案表”列标题添加底纹，颜色为浅黄；给“人事档案表”内容区域添加底纹，颜色为浅灰 25%。样张如下图示：二）、在“工资表”中完成下列操作1、改变数字外观1）、将“工资表”数值区域（即区域：C4：O24）格式设置为：数值型、保留两位小数、使用千位分隔样式；2）、将“职务工资”栏目格式设置为：使用货币样式2、改变列宽与行高1）、将“工资表”数据区域各行的行高设置为202）、将“工资表”数据区域各列的列宽设置为最适合的列宽3、使用条件格式将职务工资项目在1800以上的数据设置为红色、斜体；4、自动套用格式给“数据输入练习”工作表套用系统样式。三、实验步骤打开工作薄“实训三”一）、在“人事档案表”中完成下列操作1、设置字符格式1）、将标题“人事档案表”设置为，字体黑体；字号14；颜色红色，居中对齐操作步骤：选择“人事档案表”工作表标题行；右击弹出快捷菜单，选择“设置单元格格式”（或选择菜单【格式】【单元格】），打开单元格格式对话框选择“字体”选项卡，设置字体为黑体，字号为14，颜色为红色选择“对齐”选项卡，设置齐方式为“居中”单击“确定”按钮2）、将列标题设置为，字体仿宋体；字号12；颜色蓝色，居中对齐l 也可以通过“格式”工具栏按钮进行相关设置。操作步骤：选择“人事档案表”列标题区域（即：区域A2：I2）；右击弹出快捷菜单，选择“设置单元格格式”（或选择菜单【格式】【单元格】），打开单元格格式对话框选择“字体”选项卡，设置字体为仿宋体，字号为12，颜色为蓝色选择“对齐”选项卡，设置齐方式为“居中”单击“确定”按钮2、为表格增加边框：给“人事档案表”数据区域A2：I22添加边框。外边框为双线蓝色，内边框为单线浅蓝色。其余部分不显示网格线。操作步骤：选择“人事档案表” 数据区域A2：I22；右击弹出快捷菜单，选择“设置单元格格式”（或选择菜单【格式】【单元格】），打开单元格格式对话框选择“边框”选项卡，设置“线条样式”为双线，颜色为蓝色单击“外边框”按钮选择“边框”选项卡，设置“线条样式”为单线，颜色为浅蓝色单击“内边框”按钮单击“确定”按钮3、为表格增加底纹给“人事档案表”列标题添加底纹，颜色为浅黄；给“人事档案表”内容区域添加底纹，颜色为浅灰 25%。操作步骤：选择“人事档案表” 列标题区域A2：I2；右击弹出快捷菜单，选择“设置单元格格式”（或选择菜单【格式】【单元格】），打开单元格格式对话框选择“图案”选项卡，设置图案颜色为浅黄，并单击“确定”按钮选择“人事档案表” 列标题区域A3：I22右击弹出快捷菜单，选择“设置单元格格式”（或选择菜单【格式】【单元格】），打开单元格格式对话框选择“图案”选项卡，设置图案颜色为浅灰25%，并单击“确定”按钮二）、在“工资表”中完成下列操作1、改变数字外观1）、将“工资表”数值区域（即区域：C4：O24）格式设置为：数值型、保留两位小数、使用千位分隔样式；操作步骤：选择“工资表” 数值区域：C4：O24；右击弹出快捷菜单，选择“设置单元格格式”（或选择菜单【格式】【单元格】），打开单元格格式对话框选择“数字”选项卡，单击选择“分类”栏中的“数值”类型，设定小数点倍数为2，选定“使用千位分隔符”项（打勾）单击“确定”按钮2）、将“职务工资”栏目格式设置为：使用货币样式操作步骤：选择“工资表” 中的“职务工资”栏目区域：C4：C24；右击弹出快捷菜单，选择“设置单元格格式”（或选择菜单【格式】【单元格】），打开单元格格式对话框选择“数字”选项卡，单击选择“分类”栏中的“货币”类型单击“确定”按钮2、改变列宽与行高1）、将“工资表”数据区域各行的行高设置为20操作步骤：选择“工资表” 中数据区域区域：第4至24行；右击弹出快捷菜单，选择“行高”（或选择菜单【格式】【行】【行高】），打开行高对话框设定行高值为20单击“确定”按钮2）、将“工资表”数据区域各列的列宽设置为最适合的列宽操作步骤：选择“工资表” 中的第A至O行；选择菜单【格式】【列】【最合适的列宽】3、使用条件格式将职务工资项目在1800以上的数据设置为红色、斜体；操作步骤：选择“工资表” 中的职务工资项目区域：C4：C23；选择菜单【格式】【条件格式】），打开条件格式对话框设置条件为“大于1800”，如下图示单击“格式”按钮，打开单元格格式对话框设置颜色为红色，字形为“倾斜”单击“确定”按钮4、自动套用格式给“数据输入练习”工作表套用系统样式。操作步骤：选择“数据输入练习” 中的数据区域：A1：K14；选择菜单【格式】【自动套用格式】，打开自动套用格式对话框选择一种系统格式，例如：古典2格式单击“确定”按钮实训四、公式与函数运算一、实验目的：1. 掌握公式输入方法；2. 了解单元格引用的基本概念；3. 掌握在公式中使用函数的方法；4. 掌握常用函数（SUM、AVERAGE、MAX、MIN、IF、COUNT等）的正确使用方法；二、实验内容及要求：打开工作薄“实训四”,在“成绩表”中完成下列操作：1、 设置“成绩表”项目计算公式按照下列说明分别设置操作考核、期末成绩、总评成绩等项目的计算公式；1) 操作考核=EXCEL模块（50%）+PowerPoint模块（30%）+FrontPage模块（20%）、2) 期末成绩=操作考核（70%）+理论考核（30%）3) 总评成绩=期末成绩（60%）+平时成绩（40%）2、绝对地址引用使用“成绩权重表”中的相应权重值，改写“成绩表”中的“期末成绩”和“总评成绩”项目公式3、常用函数应用使用求平均函数（average）设置“平均成绩”行（68行）的各项计算公式；思考：你能计算出各项成绩项目的最高、最低成绩吗？4、编写“等级成绩”栏计算公式，计算方法如下： 优秀 85=S=100等级成绩= 合格 60=S85 不合格 S=85,优秀,IF(J2=60,合格,不合格)”后回车，完成第一位同学的“等级成绩”项目公式设置（直接输入公式既麻烦又容易出错，建议通过下列方法使用鼠标选择输入）。l 首先使用鼠标单击左上角函数栏右侧按钮，从下拉菜单中选择if函数,再设置if函数条件值后单击“确定”按钮，函数条件设置如下图示：因为当S（总评成绩）=85,优秀,？)” 中的”?”，按Del键将其删除。l 与前面方法类似，使用鼠标单击左上角函数栏右侧按钮，从下拉菜单中选择if函数,再设置if函数条件值后单击“确定”按钮完成公式设置，函数条件设置如下图示：使用填充柄完成其他同学“等级成绩”项目设置先将单元光标定位到“成绩表”的K2单元，并将鼠标指向该单元的右下角，当出现黑十字光标后直接拖动鼠标至K67单元（最后一名同学的“等级成绩”栏）思考：如果将“等级成绩”栏计算公式改为如下，你能正确设置其计算公式吗？ 优秀 90=S=100等级成绩= 良好 75=S90 及格 60=S75 不及格 S60其中：S指总评成绩实训五、数据管理与分析一、实验目的：1. 掌握利用记录单查找记录的方法 2. 掌握数据排序的方法3. 掌握数据筛选的方法4. 掌握分类汇总的方法5. 掌握数据透视表的简单运用二、实验内容及要求：打开工作薄“实训五”,完成下列操作：1、使用“数据/记录单”命令，查找“人事档案表”中姓黄的员工记录信息；2、在人事档案表中按部门和应发工资两列数据排序。部门相同的情况下，按应发工资降序排列；3、对人事档案表工作表进行筛选，显示部门为“信息技术系”或“中文系”员工中应发工资在2500-3000元的记录；4、对人事档案表工作表按部门进行分类汇总，计算应发工资分类合计值；5、利用“人事档案表”表建立数据透视表，分析各部门人员职称人数分布情况，结果如下图示：三、实验步骤打开工作薄“实训五”，选择“人事档案表”工作表1、使用“数据/记录单”命令，查找“人事档案表”中姓黄的员工记录信息；操作步骤：选择记录单区域A2：J22（或将单元光标定位在记录单区域内任何单元位置）；选择菜单【数据】【记录单】，打开记录单对话框；单击“条件”按钮；在姓名栏输入“黄”后回车；单击“下一条”、“上一条”按钮翻页浏览满足条件的记录信息2、在人事档案表中按部门和应发工资两列数据排序。部门相同的情况下，按应发工资降序排列；操作步骤：选择数据区域A2：J22（或将单元光标定位在数据区域内任何单元位置）；选择菜单【数据】【排序】，打开排序对话框；设置排序条件，如下图示；单击“确定”按钮；3、对人事档案表工作表进行筛选，显示部门为“信息技术系”或“中文系”员工中应发工资在2500-3000元的记录；分析：如果将显示部门、应发工资看成两个条件，条件间为同时满足关系，故可以通过自动筛选功能实现。操作步骤：选择数据区域A2：J22（或将单元光标定位在数据区域内任何单元位置）；选择菜单【数据】【筛选】【自动筛选】；单击“部门”右侧按钮，从下拉菜单中选择“自定义”项，打开对话框；设置自动筛选“部门”项条件，如下图示：单击“确定”按钮；重复步骤-，设置设置自动筛选“应发工资”项条件，如下图示：单击“确定”按钮思考：如何取消自动筛选结果？ 此题也可以通过高级筛选方法来实现，你能完成吗？4、对人事档案表工作表按部门进行分类汇总，计算应发工资分类合计值；操作步骤：首先，参照题1步骤，按分类字段-部门进行排序（分类汇总的先决条件！）；选择数据区域A2：J22（或将单元光标定位在数据区域内任何单元位置）；选择菜单【数据】【分类汇总】，打开分类汇总对话框；设置 “分类汇总”条件，如下图示：单击“确定”按钮；思考：如何清除“分类”汇总结果？5、利用“人事档案表”表建立数据透视表，分析各部门人员职称人数分布情况操作步骤：选择数据区域A2：J22（或将单元光标定位在数据区域内任何单元位置）；选择菜单【数据】【数据透视表和数据透视图】，打开数据透视表向导；单击“下一步”，设置数据源区域，如下图示：单击“下一步”，再单击“布局”按钮，打开“布局”对话框，如下图示：使用鼠标拖动相应项目，设计数据透视表布局，如下图示：单击“确定”按钮；指定数据透视表显示位置；单击“完成”按钮；思考：你能设计出下列格式的数据透视表吗？（与上题相似，想必你能行）实训六、图表一、实验目的：1. 掌握创建图表的方法2. 掌握图表的格式化的方法二、实验内容及要求：打开工作薄“实训六” ，并完成下列操作1、根据“职称结构分布表” 中的职称和中文系汇总数据创建分离形三维饼图，如下图示：2、对以上建立的图表中各图表项格式进行修改。样图如下：三、实验步骤打开工作薄“实训六”1、根据“职称结构分布表” 中的职称和中文系汇总数据创建分离形三维饼图操作步骤：选择数据源区域A1：A5和G1：G5；（按住Ctrl键分别选择）选择菜单【插入】【图表】，打开图表向导；选择图表类型为“饼图”，子图表类型为“分离形三维饼图”：单击“下一步”，再