Excel能力训练

1、实验目的：学习和掌握新建工作簿、工作表、编辑工作表、管理工作表的方法，为熟练使用Excel奠定基础。实验内容：新建工作簿“职工档案.XLS”，在Sheet1工作表中输入图7-1所示的职工档案管理表数据，对此工作簿及工作表进行操作。图7-1 职工档案管理实验步骤：1启动Excel2003(1)单击【开始】【程序】【Microsoft Office】【Microsoft Excel 2003】命令，启动Excel2003。(2)单击【文件】【保存】菜单项或单击常用工具栏上的“保存”按钮，打开“另存为”对话框，在该对话框中选择文档保存的位置为E：Excel训练，在文件名框中输入“职工档案”，点击【保

2、存】按钮。2输入数据（1）单击工作表标签Sheet1，在Sheet1中按图7-1所示输入内容。在A2和A3单元格分别输入“1001”和“1002”，选中A2和A3单元格，将鼠标移动到单元格右下角的“填充柄”上，鼠标变成实心十字形状，拖动鼠标自动填充编号。（2）在D2单元格输入日期格式“1978/7/15”，确认后自动变为“1978-7-15”样式，采用同样操作方法在D3：D10单元格区域输入日期。（3）拖动鼠标选中F2：F10区域，单击【格式】【单元格】命令，打开“单元格格式”对话框，如图7-2所示，选择“数字”选项卡中的“文本”项，单击【确定】按钮，在该单元格区域输入身份证号。图7-2“单元

3、格格式”对话框（4）单击工作表标签Sheet2，将光标定位在A1单元格，先输入一个“0”和一个空格，再输入分数5/6，即“0 5/6”，在A1单元格输入分数。（5）将光标定位在工作表Sheet2的C1单元格，输入“星期一”，将鼠标移动到单元格右下角的“填充柄”上，拖动鼠标自动填充星期序列至C14单元格。（6）在Sheet2工作表中单击【工具】【选项】菜单项，打开“选项”对话框，选定对话框上的“自定义序列”选项卡，如图7-3所示。在“输入序列”框中输入“第一章”、“第二章”、“第三章”，每输入一个数据按一次【Enter】键，单击【添加】按钮，将其作为新的序列加入到自定义序列清单。在D1单元格输入

4、“第一章”，鼠标拖动“填充柄”，将“第二章”、“第三章”自动填充至D2、D3单元格。（7）在Sheet2工作表E1单元格输入数据“2”，选定E1：E6单元格区域，单击【编辑】【填充】命令，在“填充”级联子菜单选择“序列”项，打开“序列”对话框，如图7-4所示。选择序列产生在“列”，步长值“2”，终止值“100”，类型“等比序列”，确定后在E1：E6单元格区域填充等比序列。图7-3 自定义序列 图7-4 填充等比序列3单元格的操作以下操作在工作表Sheet1中完成：（1）鼠标单击第一行行号，选择【插入】【行】命令，在第一行的上方插入一行。在A1单元格输入“职工基本情况”；鼠标单击D列的列号，选择

5、【插入】【列】命令，插入空白列，在D2单元格输入“学历”。如图7-5所示。（2）鼠标单击G列列号，选择【编辑】【删除】命令，删除G列，如图7-5所示。图7-5职工基本情况4工作表的操作（1）在Sheet2之前插入一个新工作表。鼠标单击工作表标签Sheet2，选择【插入】【工作表】命令，此时在工作表Sheet2前自动插入工作表Sheet4。（2）将工作表Sheet1更名为“基本情况”。双击工作表标签Sheet1，输入“基本情况”，回车确认；或右键单击工作表标签Sheet1，在如图7-6所示的快捷菜单中选择【重命名】项。用同样方法将Sheet4更名为“工资表”。（3）右键单击“基本情况”工作表标签

6、，在如图7-7所示的快捷菜单中选择 【工作表标签】项，选择工作表标签的颜色为蓝色。（4）拖动鼠标选择 “基本情况”工作表中A2：B11区域，将该区域数据复制到“工资表”工作表的A1：B10区域，输入如图7-8所示的数据。图7-6 重命名 图7-7 工作表标签颜色（5）右键单击“工资表”工作表标签，选择【移动或复制工作表】命令，弹出如图7-9所示的“移动或复制工作表”对话框，选择将“工资表”工作表复制到Sheet3之前。图7-8 工资表图7-9“移动或复制工作表”对话框5窗口的拆分与冻结（1）鼠标单击“基本情况”工作表表签，选定D7单元格，该单元格所在位置将成为拆分的分割点，选择【窗口】【拆分】

7、命令，“基本情况”工作表被拆分为4个独立的窗口。（2）选择【窗口】【撤消拆分】命令或双击分割条，恢复窗口原来的形状。（3）选定B3单元格作为冻结点，选择【窗口】【冻结窗格】菜单项，该点上边和左边的所有单元格都将被冻结，鼠标拖动垂直和水平滚动条观察冻结后窗口显示情况。（4）选择【窗口】【取消冻结窗格】命令恢复工作表原样。实验二 工作表的格式化实验目的：掌握设置工作表行高、列宽、表格边框与底纹、数据对齐方式、条件格式、自动套用格式等格式化方法。实验内容：新建工作簿“学生成绩.XLS”，在Sheet1工作表中按图7-10输入数据，按以下步骤完成格式化操作。实验步骤：1调整行高和列宽（1）拖动鼠标选择

8、D1：F9区域，单击【格式】【列】【最适合的列宽】命令，根据单元格中的数据设置D、E、F列宽为“最适合的列宽”。（2）选择第一行的行标，单击【格式】【行】【行高】命令，在图7-11所示的“行高”对话框中设置行高20磅。图7-10 学生成绩表图7-11“行高”对话框2.设置单元格对齐方式（1）在第一行前插入一行，选中A1单元格，输入“网络041班成绩”，拖动鼠标选择A1：F1区域，单击格式工具栏“合并及居中按钮”，设置标题居中对齐。（2）拖动鼠标选择A3：F10区域，单击【格式】【单元格】命令，打开“单元格格式”对话框，选择“对齐”选项卡，如图7-12所示，在“水平对齐”和“垂直对齐”下拉列表框

9、中都选择“居中”对齐方式。观察A3：F10区域的数据对齐方式的改变。（3）拖动鼠标选择A2：F2区域，单击【格式】【单元格】命令，在图7-12所示的对话框中的“方向”选项区的数值框内输入要旋转的角度值为15度，结果如图7-13所示。图7-12对齐选项卡图7-13 单元格对齐方式3设置单元格字体、字号及底纹选择A1单元格标题，设置为楷体、16号；单击【格式】【单元格】命令，在“单元格格式”对话框中选择“图案”选项卡，选择单元格底纹颜色为黄色。4设置表格边框方法一：选择A2：F10区域，单击格式工具栏中“边框”命令右侧的下拉按钮，在如图7-14的项目菜单中选择所有框线。方法二：选择A2：F10区域

10、，打开“单元格格式”对话框的“边框”选项卡，如图7-15所示。选择“外边框”和“内部边框”，单击【确定】按钮，在A2：F10区域加上边框。效果如图7-17所示。图7-14 添加表格边框图7-15“边框”选项卡5设置条件格式（1）选择D3：F10区域，单击【格式】【条件】命令，打开如图7-16所示的“条件格式”对话框。（2）条件1设置为 “单元格数值”大于80，单击对话框中的【格式】按钮，打开选择“字体”、“边框”、“图案”等格式的对话框，选择加蓝色单元格底纹的条件格式。（3）单击【添加】按钮，条件2设置为“单元格数值”小于60，格式设置为“字形加粗倾斜”，结果如图7-17所示。 图7-16“条

11、件格式”对话框图7-17格式设置效果6自动套用格式选择A2：F10区域，单击【格式】【自动套用格式】命令，在“自动套用格式”对话框中选择“古典2”，单击【确定】按钮，观察表格的变化。7清除格式设置选择A1：F10区域，单击【编辑】【清除】【格式】命令，将前面所有格式设置清除。实验三 使用公式和函数实验目的：学会使用单元格引用的三种类型，熟练掌握公式的使用方法，Excel常用函数的使用方法。实验内容：新建工作簿“”，在Sheet1工作表中按图7-18输入数据。图7-18 成绩表实验步骤：1使用公式和SUM函数计算总分（1）在F2单元格输入“=”，鼠标分别单击C2、D2、E2单元格并从键盘输入“+

12、”号，此时F2单元格中的公式为“=C2+D2+E2”，按回车键确认后F2单元格数据即为计算结果。鼠标拖动F2单元格右下角的填充柄将公式复制到F3：F10区域，总分计算完成。（2）将上述用公式计算的总分清除，在F2单元格中输入公式：=SUM(C2:E2)，确认后计算出第一名同学的总分，鼠标拖动F2单元格右下角的填充柄将公式复制到F3：F10区域，总分计算完成。计算结果如图7-19所示图7-19 用函数计算总分图7-20 计算平均分2使用AVERAGE函数计算平均分在G2单元格中输入公式：=AVERAGE(C2:E2)，确认后，计算出第一名同学的平均分。将该公式复制到G3:G10单元格区域，计算其

13、他同学平均分。计算结果如图7-20所示。3使用RANK函数计算名次根据总分排出名次，在H2单元格中输入公式：=RANK(F2，$F$2:$F$10)，确认后即可得出第一位同学的总分成绩在全班成绩中的排名结果。将该公式复制到H3:H 10单元格区域，可计算其他同学在全班的排名。计算结果如图7-21所示。图7-21 计算名次4使用IF函数计算总评根据平均分计算学生总评成绩：平均分在85分以上为“优秀”，85至75分为“良好”，75分至60分为“及格”，60分以下为“不及格”。在I2单元格中输入公式：=IF(G2:G10>=85,"优秀",IF(G2:G10>=75,

14、"良好",IF(G2:G10>=60,"及格","不及格")，确认后，计算出第一名学生总评成绩，将公式复制到I3：I10单元格区域计算其他同学总评成绩。计算结果如图7-22所示。图7-22计算总评5使用SUMIF函数计算男同学“高等数学”总分在A11单元格输入“男生数学总分：”，在C11单元格中输入公式：=SUMIF(B2:B10，"男"，C2:C10)，确认后计算出男同学高等数学总分为“307”。6使用COUNTIF函数统计出“电子技术”成绩在80分以上的人数在A12单元格输入“电子技术>80分人数:

15、”,在D12单元格中输入公式：=COUNTIF(D2:D10,">=80")，确认后统计出电子技术成绩大于等于80分的单元格数目为“4”。7其他常用函数以下操作在工作簿“成绩.XLS”的Sheet2工作表中完成。（1）在单元格区域A1：A10中产生10个1099的随机数。在A1单元格输入公式：=10+INT(RAND()*90)，确定后将公式复制到A2：A10区域。（2）在C1单元格中输入公式：=ABS(9.5)，计算的绝对值。（3）在C2单元格中输入公式：=ROUND(1.234567,3)，计算1.234567四舍五入保留3位小数后的值。（4）在C3单元格中输入公

16、式：=MAX（-1,1,0,-3,2）,计算最大值。（5）在C4单元格中输入公式：=MIN（-1,1,0,-3,2）,计算最小值。（6）在单元格D1：D9中依次输入以下9个数：-1、1、2、3、TURE、FALSE、“002”、-3、Excel2003，在单元格D10中输入公式：=COUNT(D1:D9)，计算出单元格区域中数字个数。实验四数据清单管理实验目的：熟练掌握数据清单的排序、筛选、分类汇总方法，学会使用数据透视表分析数据的方法。实验内容：1新建工作簿“工资.XLS”，在工作表Sheet1中按图7-23建立数据表，单元格区域A1:G10是一个职工工资数据表。实验步骤1-5项在此工作簿中

17、完成。2新建工作簿“销售日记.XLS”，在工作表Sheet1中按图7-24建立数据表。实验步骤6在此工作簿中完成。图7-23工资表 图7-24 销售日记实验步骤：1添加记录（1）选择【数据】【记录单】命令，打开“记录单”对话框，如图7-25所示。该对话框默认显示数据清单中的第一条记录。（2）单击【新建】按钮，在字段编辑处输入如图7-25所示的新记录，按Enter键确认后新记录自动添加到数据清单的末尾。在工作簿“”中插入两个新工作表Sheet4、Sheet5，将工作表Sheet1中的数据复制到工作表Sheet2Sheet5中，将各工作表分别改名为工资表、排序、自动筛选、高级筛选、分类汇总。2数据

18、表的排序（1）在工作表Sheet2中选定排序的数据区域A1:G11或将光标放置在数据清单中的任意一个单元格内。（2）选择【数据】【排序】命令，在如图7-26所示的“排序”对话框中选择“基本工资”作为主要关键字（降序），“年龄”作为次要关键字（升序），确定后完成工资表排序。图7-25“记录单”对话框图7-26“排序”对话框3自动筛选（1）选择工作表“自动筛选”中的任意一个单元格，单击【数据】【筛选】【自动筛选】命令，此时字段名的右侧会显示筛选箭头，表示数据清单进入自动筛选状态。（2）打开“职称”字段筛选箭头的下拉列表框，选择“自定义”，这时会出现“自定义自动筛选方式”对话框，设置筛选条件为职称是

19、“高级会计师”和“高级工程师”的记录，如图7-27所示。确定后显示的筛选结果如图7-28所示。注：选择【数据】【筛选】【全部显示】命令，恢复显示全部记录。图7-27“自定义自动筛选方式”对话框图7-28自动筛选结果4高级筛选（1）选择工作表“高级筛选”，在此工作中的B13：C14区域设置筛选条件为基本工资1000元以下的会计师。（2）选择A1：G11区域，单击【数据】【筛选】【高级筛选】项，弹出如图7-29所示的“高级筛选”对话框。（3）“高级筛选”对话框中的列表区域显示筛选区域，单击条件区域参数框右侧“对话框折叠”按钮，拖动鼠标选择条件区域，单击“对话框折叠按钮”恢复输入参数对话框。图7-2

20、9“高级筛选”对话框（4）选择“将筛选结果复制到其他位置”单选框，“复制到”选项框被激活，输入筛选结果显示位置为A16单元格。确定后显示如图7-30所示的筛选结果。图7-30 高级筛选结果5分类汇总（1）选择工作表“分类汇总”，按“性别”升序排列记录。（2）单击【数据】【分类汇总】命令，弹出图7-31所示“分类汇总”对话框图7-31“分类汇总”对话框（3）在“分类汇总”对话框中进行以下选择：在“分类字段”下拉式列表框中择“性别”；在“汇总方式”列表框中选择“求和”；在“选定汇总项”列表框中选择“基本工资”。（4）确定后显示按“性别”字段分类汇总“基本工资”的总和。注：选择“分类汇总”对话框中的

21、【全部删除】按钮可以从数据清单中删除所有分类汇总。6数据透视表（1）将光标置于工作簿“销售日记.XLS”的工作表Sheet1中的任意单元格，打开【数据】【数据透视表和数据透视图】菜单项，弹出“数据透视表和数据透视图向导-3步骤之1”对话框。（2）在上述对话框中指定待分析数据来源及需要创建的报表类型，然后单击【下一步】按钮，打开 “数据透视表和数据透视图向导-3步骤之2”对话框。默认情况下，系统自动将选取整个数据清单作为数据源，如果数据源区域需要修改，则可直接输入数据源区域。（3）确定数据源后，单击【下一步】按钮，弹出“数据透视表和数据透视图向导-3步骤之3”对话框，选择数据透视表显示的位置为“

22、新建工作表”。单击【布局】按钮，出现“数据透视表和数据透视图向导-布局”对话框，如图7-32所示。（4）在“数据透视表和数据透视图向导-布局”对话框中，将“产品名称”拖到“行(R)”图上，将“日期”拖到“页(P)”图上，将“销售地区”拖到“列(C)”图上，将“数量”和“金额”拖到“数据(D)”图上，如图7-32所示。图7-32设置数据透视表的布局（5）单击【确定】按钮，返回到“数据透视表和数据透视图向导-3步骤之3”对话框，单击【完成】按钮，得到如图7-33所示的数据透视表。图7-33数据透视表示例实验五 图表操作实验目的：熟练掌握Excel图表的基本操作，利用图表分析数据。实验内容：新建工作

23、簿文件“LS”，在工作表Sheet1中输入如图7-34所示的数据。图7-34成绩统计表实验步骤：1创建图表（1）选定要创建图表的数据区域A2：D6，单击常用工具栏中的【】按钮或选择【插入】【图表】命令，打开如图7-35所示，“图表向导-4步骤之1-图表类型”对话框，选择图表类型为“柱形图”，子图表类型为“堆积柱形图”。（2）单击【下一步】按钮，打开“图表向导-4步骤之2-图表数据源”对话框，如图7-36所示。在“数据区域”框中输入正确的单元格区域，选择系列产生在“列”。（3）单击【下一步】按钮，打开“图表向导-4步骤之3-图表选项”对话框，如图7-37所示。在此对话框“标题”选项卡输入图表标题为“学生成绩”，分类（X）轴标题为“班级”，分类（Y）轴标题为“分数”。图7-35“图表向导-4步骤之1-图表类型”对话框图7-36“图表向导-4步骤之2-图表数据源”对话框（4）单击【下一步】按钮，在“图表向导-4步骤之4-图表位置”对话框中选择图表位置为“作为其中的对象插入”。（5）单击【完成】按钮，插入如图7-38所示的图表。7-37“图表选项”对话框 图7-38 插入图表2编辑图表（1）选定图表，单击【图表】【图表类型】命令，打开“图表类型”对话框，将图表改为“簇状柱形图”。（2）单击选定图表中“英语”数据系列，按Delete键，将“英语”数据系列从图表中删除，观