操作 实验五Excel 5.实验目的 1 ● 熟悉和掌握工作表的基本编辑操作。 ● 掌握在工作表中单元格的格式设置。 ● 掌握工作表中输入数据及数据格式的设置方法。 ● 熟悉并掌握公式及函数的使用。 ● 掌握数据的图表化操作。 5.相关知识 2 1. 工作簿及工作表的基本操作 (1)工作簿的基本操作。 (2)工作表的基本操作。 2. 单元格的操作 (1)单元格的编辑。 (2)单元格的格式化操作。 3. 数据的操作 (1)数据的编辑。 (2)数据的格式化操作。 4. 数据的应用操作 (1)利用函数对数据进行计算,如SUM 、AVERAGE 、LOOKUP 、IF 等函数的使用。 (2)数据的排序、筛选、汇总操作。 (3)数据的条件显示操作。 5. 数据的图表化表示 (1)图表的创建。 (2)数据透视表的生成。 (3)数据透视图的建立。 6. 页面设置与打印 (1)进行页面的常规设置,如页边距、纸张方向、纸张大小等。 (2)进行打印区域和打印标题的设置。 5.实验内容 3 1.Excel的窗口组成 Exe1所示。 cl的窗口组成如图5. 图5.l的窗口组成 1Exce 进入Ex创建一个空白工作簿文件,文件名默认为“工作簿1.,即可输入 数据。 cel, xlsx” 2. 工作簿及工作表的基本操作 (1)工作簿的基本操作。 工作簿是Excel的核心,是Excel计算和储存数据的文件。每一个工作簿最多可包 含255 张工作表。 工作表是工作簿的一部分,是Excel用来存储和处理数据的最主要文档,它包含排成 行和列的单元格。工作表的名称显示在工作簿窗口底部的工作表标签上。 用户创建工作簿后,存储工作簿时,需要用户为工作簿命名,并决定存储位置。对已 经存盘的工作簿文件,可以单击“文件”标签中的“打开”命令打开工作簿文件,该操作实际 上是将该文件调入内存并显示在屏幕上。编辑完工作簿后可单击“文件”标签中的“关闭” 命令将其关闭。 44大学计算机基础(第 3 版)上机实验指导 (2)工作表的基本操作。 ①创建工作表:单击工作表下方标签处的添加按钮,即添加一个新的工作表。 ②选定工作表:按住Shift键并选定连续工作表;按住Ctrl键可选定不连续的工 作表。 ③移动工作表:使用鼠标拖曳可移动工作表。 ④重命名工作表:右键单击工作表名称,在弹出的快捷菜单中选择“重命名”命令可 对工作表重命名,或双击工作表名称改名。 ⑤删除工作表:右键单击工作表名称,在弹出的快捷菜单中选择“删除”命令可删除 选中的工作表。 ⑥复制工作表:右键单击工作表名称,在弹出的快捷菜单中选择“移动或复制”命 令,弹出“移动或复制工作表”对话框,勾选“建立副本”复选框,选择复制的位置,如图5. 所示,单击“确定”按钮即可完成工作表的复制。 2 ⑦冻结拆分窗格。 在工作表中选定作为拆分分割点的单元格,单击“视图”选项卡“窗口”组中的“拆分” 按钮,工作表就会被拆分成4个窗格。拖动窗格间的分隔线可调节窗格大小。同样地,如 果拆分窗格后再次单击“拆分”按钮,将取消对工作表的拆分。 选定视图选项卡,单击窗口组中的“ 按钮,3所示的下拉列表 冻结窗格” 在弹出如图5. 中选择冻结窗格的方式,即可完成冻结窗格的设置。 图5.移动或复制工作表” 图5. 2“ 对话框3 冻结窗格 3. 单元格的操作 (1)单元格的编辑。 ①选取单元格的方法:可以通过鼠标拖曳或鼠标与键盘结合的方法来选取单元格, 也可以拖曳行(列)号来选取多行(列)。 ②插入单元格:选取要插入单元格右侧的单元格,在选取的单元格上右击,在弹出 命令,4所示的“ 的快捷菜单中单击“插入” 弹出如图5.插入”对话框,选择某种插入方式, 单击“确定”按钮完成插入单元格操作。如果选取了多行(列), 在选取的行(列)上右击,然 后在弹出的快捷菜单中单击“插入”命令,则在选取的行(列)前面插入多行(列), 插入的行 实验五Excel 操作 (列)数等于选取的行(列)数。 ③删除单元格:删除单元格的操作方法与插入单元格的操作方法类似,右键单击选 中的单元格后,弹出如图5.删除” 选择某种删除方式, 确定” 即可删除单元格。 5所示的“ 对话框, 单击“ 按钮 对话框5“ 图5.4 “插入” 图5.删除”对话框 (2)单元格的格式化操作 。 设置列宽和行高的方法如下 。 ①粗略调整行高和列宽:通过鼠标拖动行或列的边框线来调整行高和列宽。 ②精确调整行高和列宽:选定要调整行高或列宽的行或列,单击“开始”选项卡的 “单元格”组中的“格式”按钮,在弹出的下拉菜单中选取相应的调整模式进行设置即可。 合并单元格的方法如下。 在“开始”选项卡的“对齐方式”组中还可对单元格式进行合并操作,如果需要将单元 格中的数据换行,可设置“自动换行”或在输入数据时按Alt+Enter组合键进行单元格内 数据换行操作。 设置单元格的边框和底纹方法如下。 在“设置单元格格式”对话框中的“边框”选项卡中可进行边框线条的详细设置,如 图5.在“ 选项卡中可进行底纹的详细设置,如图5.7所示。 6所示; 填充” 图5.边框”选项卡 6“ 64 大学计算机基础(第 3 版)上机实验指导 实验五 Excel 操作 图5.7 “填充”选项卡 4.数据的操作 (1)数据的编辑。 选定工作表后,用户就可以在工作表中输入数据,即根据需要,将系统允许的各类数 据输入到指定的单元格中。单元格中可以存储文本、数值、日期、时间等数据。 ● Excel对数据类型进行自动识别。 如果没有对单元格中的数据类型进行设置,Excel会以默认的方式进行类型的识别。 如果输入的是字符,Excel会认为是文本,并把类型设置为“常规”,对齐方式为左对齐;如 果输入的是数字,Excel会认为是数值,并把类型设置为“常规”,对齐方式为右对齐,如果 数字的位数超过11位,则Excel会以科学记数法的方式来表示输入的数字;如果输入的 是日期型的格式,如“2014/09/01”,Excel会将数据类型设置为日期型。 ● 输入像数字的文本。 有些数据看起来像数字,但它们其实是文本,如学号、商品编号、身份证号等,因为它 们不可进行算术运算或进行算术运算没有意义。这种情况下必须将单元格数据类型设置 为文本再进行数据的输入。 方法一:先将单元格的数据类型设置为“文本”,再进行数据的输入。 方法二:在数据前输入一个半角的撇号“'”表示其后面的数据是文本。 ● 输入系列数据的方法如下。 ① 利用鼠标填充序列号:选定要生成序列数据的第一个单元格,并输入起始序号。 然后按下Ctrl键,拖动填充柄,这时在鼠标旁出现一个小“+”号以及随鼠标移动而变化 的数字标识,当数字标识与需要的最大序列号相等时,松开Ctrl键和鼠标即可。 ② 利用鼠标填充序列数据:首先按照序列的规律在第一个和第二个单元格中输入 47 序列的第一个和第二个数据,如输入1、3。然后选定这两个单元格,并将鼠标指向填充 柄。按下鼠标左键并拖动填充柄,当到达目标单元格时,松开鼠标左键,即可完成序列数 据填充,如1、3、5、7、9、11 、……。 ③创建自定义填充序列。 单击“文件”标签,在左侧窗格的列表中单击选项命令,弹出“Excel选项”对话框。在 左侧列表中单击“高级”命令,并拖动右侧的垂直滚动条直至“常规”选项栏,单击其中的 “编辑自定义列表”按钮。 打开“自定义序列”对话框,在“输入序列”文本框中输入自定义序列项,以Enter键或 英文逗号进行分隔。单击“添加”按钮,自定义的序列将出现在“自定义序列”框中,如图5. 8 所示。 图5.8 “自定义序列”对话框 单击“确定”按钮关闭对话框,完成自定义序列添加。如果要在工作表中填充自定义 序列,用户只要在单元格中输入序列的第一项,然后拖动填充柄或鼠标左键双击填充柄, 就可以自动完成自定义序列的填充。 (2)数据的格式化操作。 ①设置字体格式:与Word中的设置方法相同。 ②设置数据的对齐方式:在Excel中,单元格中数据除了水平对齐外,还有垂直对 齐,可通过“开始”选项卡的“对齐”组中的功能按钮进行设置。 【例5.编辑如表5.工作表文件可于指定的相关地址进行下载)。 1】1所示的例表( 84 大学计算机基础(第 3 版)上机实验指导 表5.例表 1 ①打开“ 1.lx文件, 合并单元格, 成绩 例5.xs” 在整个数据表上插入一行, 填入表头“ 表”,设置其字体为“宋体”,字号为“20 号”,表头内容不设边框。 ②设置表中数据格式。设置表中“学号”列数据为文本格式。选中“学号”列的数据, 通过“开始”选项卡的“数字”组中的“数字格式”,设置该数据格式为“文本”。 ③选中入学日期列数据,右键单击,在弹出的快捷菜单中选择“设置单元格格式”→ “数字”→“自定义”,设置日期格式为“yyyy-mm-dd”。 ④选中“姓名”列数据,单击“开始”选项卡的“对齐方式”组中右下角的对话框启动 器,打开“设置单元格格式”对话框,在“对齐”项选择“分散对齐”,将姓名列数据设置为分 散对齐,其他单元格数据水平居中对齐。 ⑤单击“开始”选项卡的“字体”组中右下角对话框启动器,打开“设置单元格格式”对 话框,在“填充”页的“图案颜色”列表给列标题添加颜色为“白色,背景1,深色50%”。 ⑥选择“框线”命令,或者直接打开“设置单元格格式”对话框,在“边框”选项卡中参 照表5.1为表格内添加单、双边框线,并将外边框设置加粗效果。 ⑦保存工作簿。 5. 数据的应用操作 (1)利用函数对数据进行计算。 ● 单元格地址的引用方式。 ①相对引用。 默认情况下,Excel使用“A1”形式描述单元格地址,即用字母表示列标,用数字表示 行号。第一种单元格引用形式为“R1C1”,在这种形式中行号和列标都用数字表示。若要 使用第二种单元格引用形式,可以在“文件”标签栏中单击“选项”命令,在弹出的“Excel 选项”对话框左侧列表中,单击“公式”选项,在右侧窗格中的“使用公式”栏中勾选“R1C1 引用样式”,单击“确定”按钮关闭对话框即可。 ②绝对引用。 绝对引用是指在把公式复制到新位置时,其中的单元格地址保持不变。设置绝对地 址需在行号和列标前面分别加上“$”。 ●Excel的运算符。 Excel具有强大的数据运算能力,用户可以用公式进行简单的计算,如加、减、乘、除 等,也可以完成较复杂的财务、统计及科学计算,还可以用公式进行比较或操作文本(字符 串)。Excel的常用运算符如表5. 2所示。 实验五Excel 操作 大学计算机基础(第3 版)上机实验指导 表5.2 Excel的常用运算符 运算符种类运算符符号 算术运算符+、-、*、/、%、^ 比较运算符=、>、>=、<、<=、<> 连接运算符& 引用运算符:、, ● 使用公式与函数。 ① 输入公式:输入公式必须以等号开头,且数据类型格式不能设置为文本。 ② 使用函数:函数是Excel预定义的内置公式,可以进行数学、文本、逻辑的运算或 者查找工作表的信息,与直接利用公式计算比较,使用函数进行计算的速度更快,同时还 可以减小输入时的出错率与代码量。Excel的常用函数如表5.3所示。 表5.3 Excel的常用函数 格 式功 能 SUM(c1,c2,…) 计算各参数数值的和 AVERAGE(c1,c2,…) 求各参数数值的平均值 COUNT(c1,c2,…) 计算参数组中的数值型数据的个数 MAX(c1,c2,…) 计算各参数数值中的最大值 MIN(c1,c2,…) 计算各参数数值中的最小值 LOOKUP(lookup.value,lookup_vector,[resultvector]) 从单行、单列或从数组中查找一个值 VLOOKUP(lookup_value,table_array,col_index_ num, [range_lookup]) 搜索表区域首列满足条件的元素 INT(c) 对参数取整 ABS(c) 取给定参数的绝对值 MOD(c1,c2) 求c1/c2的余数 SQRT(c) 取给定参数的平方根值 RAND() 产生0到1之间的一个随机数 ③ 复制公式:将含有公式或函数的单元格复制到工作表中另一位置,单元格中的相 对地址会随之发生改变,但若使用绝对地址表示则不会发生改变。也可以使用鼠标拖曳 的方法来快速复制公式。 (2)单元格的条件格式设置。 ① 选中要进行条件格式设置的单元格。 ② 单击“开始”选项卡的“样式”组中的“条件格式”按钮,在弹出的下拉菜单中选取某 种条件格式。 50 (3)数据的排序、筛选和汇总操作。 ● 数据的排序。 ①选择工作表中需要排序的单元格区域。 ②打开“数据”选项卡,在“排序和筛选”组中单击“排序”按钮。 ③打开“排序”对话框,对排序选项进行设置,设置完成后,单击“确定”按钮即可完成 对数据表的排序。 ● 数据的筛选。 ①选定要筛选的数据表中的任意一个或多个单元格(包括表的列标题)。 ②单击“数据”选项卡的“排序和筛选”组中的“筛选”按钮,此时在每个列标题的右侧 出现一个倒三角按钮,单击该按钮可以对筛选项目进行设置。 ● 数据的汇总。 ①对要分类汇总的表格按照分类关键字段进行排序。 ②选中表中任一单元格,单击“数据”选项卡“分级显示”组中的“分类汇总”按钮,弹 出“分类汇总”对话框,在此对话框中对分类字段与汇总项进行设置,然后完成分类汇总。 【2】1的文档“ 1.lx” 例5.练习公式和函数的使用。使用例5.例5.xs进行操作。 ①计算总分。将光标置于单元格H3,单击“公式”选项卡的“Σ”命令,选择“E3:G3”, 按回车键。将鼠标指向填充柄,双击填充柄,将会填充其他学生的总分。 ②计算平均分。将光标置于单元格I3,输入公式“=H3/3”,按回车键。打开“设置 单元格格式”对话框,选择“数值”,保留小数位2位。选中I2,鼠标指向填充柄,双击填充 柄,将填充其他学生的平均分。 ③划分奖学金等级。在J3中输入公式“=IF(I3>=90,"一等",IF(I3>=80,"二 等"))使用如上同样操作,复制J3公式,完成其他同学的奖学金等级划分。,(") (") ,(”) ④选择“平均分”列所在数据,单击“开始”选项卡的“样式”组中完成条件显示,选择 “突出显示单元格规则” 大于”在对话框填写“80填充“ ,9所示。 的“, ,(”) 浅红色填充”如图5. 图5. 9 操作示例图 ⑤将“学号”和“姓名”列数据复制到Shet2中,从A1位置粘贴,右侧增加一列,标题 为“学期末成绩”,标题格式同“学号”和“姓名”列。 ⑥要跨表引用单元格,将光标置于Shet2的C2单元格,输入“=Shet1!H3*0.按,(”) 5 回车键,完成计算,双击填充句柄,完成其他同学的学期末成绩的计算,并设置数据格式为 保留小数位0位,最后调整该列单元格边框样式,其数值字号为“11号”,“居中”,如 实验五Excel 操作 大学计算机基础(第3 版)上机实验指导 图5.10所示。 图5.10 Sheet2示例图 ⑦ 保存工作簿。 注意: ① 所有的公式或函数中所用到的符号,都是英文符号,如果输入的是中文符号系统 将无法识别。 ②IF函数的格式:=IF(条件判断表达式,条件判真时值,条件判假时值)。该格式 中“条件判断表达式”指的是任何可以判断为真(True)或假(False)的表达式。“条件判真 时值”指的是当“条件判断表达式”的结果为真时所返回的值,如果忽略,则返回True。 “条件判假时值”指的是当“条件判断表达式”结果为假时所返回的值,如果忽略,则返回 False。 IF函数允许嵌套,最多可嵌套7层。可以在“条件判真时值”或“条件判假时值”处输 入嵌套的IF函数。 【例5.3】 Excel工作簿中有两个表,分别是“商品表”和“销售表”,如图5.11所示。 根据“商品表”中的数据,使用LOOKUP或VLOOKUP函数,在“销售表”中自动填写商 品名称与单价。 图5.11 商品表与销售表 具体操作步骤如下。 ① 在“销售表”的B2单元格中输入以下公式: = LOOKUP(A2,商品表!A$2:B$6,商品表!B$2:B$6) ② 在“销售表”的C2单元格中输入以下公式: = LOOKUP(A2,商品表!A$2:B$6,商品表!C$2:C$6) 52