EXCEL实操训练-

1、2021-8-7 EXCEL 实操训练 2021-8-7 目 录 一、认识EXCEL与内容保护 二、数据的输入 三、数据管理 四、绘制图表 五、排版与打印 六 、数 据 透 视 表 七 、百 分 位 值 八 、函 数 2021-8-7 一、认识EXCEL与内容保护 p 工 作 表 p 单 元 格 p 冻 结 窗 口 p 保 护 文 件 p 保 护 工 作 薄 p 保 护 工 作 表 p 保 护 单 元 格 2021-8-7 工作表工作表标签 工作表（Worksheet）：由单元格构成的表格，是工作簿的重要组成部分，又称电子（报）表格。 工作簿是工作表的集合。 一个工作簿可包含多个工作表。 系统

2、默认一个工作簿包含三个 工作表名称为：Sheet1 ， Sheet2，Sheet3。 一个工作 簿最多有255张互相独立的工 作表Sheet。 2021-8-7 为便于对单元格的引用，系统赋予每个单元格一个地址：列标+行号。 如：F6表示的是第F列第6行交叉点处的单元格； A1：D4表示的是A1到D4单元格之间的巨型区域。 行号 列号 B2 2021-8-7 当一个工作表数据超过一屏幕时，滚动会使表头移出当前屏幕，而不利于观看。有 时，我们希望数据上下移动时，表头不动，左右移动时，表题列不动。 冻结工作表 “视图”|“窗口”|“拆分”（【窗口】冻结窗格） 若要取消拆分：执行【窗口】/【取消拆分

3、】命令 取消冻结，可选择【窗口】【撤消窗口冻结】。 2021-8-7 1、保护文件 点击文件“另存为”“工具”“常规选项”设置密码保 护或者设置“只读文件” 2021-8-7 2、保护工作簿 工作簿的保护有两个方面：第一是保护工作簿，防止他人非法访问；第二是禁止他人对工 作簿或对工作簿窗口的非法操作。 2021-8-7 3、对工作表的保护 如果不允许对工作簿中工作表进行移动、删除、插入、隐藏、取消隐藏、重命名或禁止对工 作簿窗口的移动、缩放、隐藏、取消隐藏或关闭，操作步骤如下： 【工具】/保护/保护工作表 如果选勾复选框，工作表将不能移动、删除、插 入等 键入密码，若要取消这种保护，可以选择【

4、工具】 保护撤消工作簿保护。 2021-8-7 4、保护单元格 首先使工作表处于非保护状态（撤消工作表保护） 选定需要锁定或取消锁定的单元格区域； 【格式】单元格/“保护”标签 注意：清除“锁定”复选框并单击“确定”按钮。重新对工作表实施保护后，本操作中取消 锁定的单元格就是可以进行修改的单元格，其余单元格为保护单元格。 2021-8-7 二、数据的输入 p 输入文本 p 输入数值 p 输入日期和时间 p 快速填充数据 2021-8-7 每个单元格最多包含32000个字符。 文本包含任何字母、中文字符、数字和键盘符号组合。 当数字作为文本输入时， 1、输入文本 存储数据： 取消输入: 默认单元

5、格内左对齐 数字前输入单引号表示输入的数字 作为文本处理。如 1234 Enter键或Tab键;上、下、左、右的光标键; 或在其它单元格单击鼠标，都可以存储当前单元格中输入的数据。 若在未存储之前，取消输入的数据，按Esc键 2021-8-7 2、输入数字 对齐方式：默认右对齐 u正数：直接输入 u负数：带括号或使用“-”号，如：-15或（15）均表示负数。 u小数：“工具”-“选项”-“编辑” u输纯分数：应在分数前加“0”和一个空格，要避免把输入的数字当作日期，如：2013-4-1。 正数，设置小数位数 负数，设置尾数0的个数 2021-8-7 （二）当输入一个超过列宽的较长数字时，Exc

6、el会自动采用科如果单元格中 给出【#】标记，说明列宽已不足以显示数据（扩大列宽后数据会重新完 整地显示），但系统“记忆”了该单元格的全部内容，当选中该单元格后，在 编辑栏中会显示完整内容。 2021-8-7 3、输入日期和时间 1）输入日期：使用分隔符 /或 - 1996/10/1，1996-10-1， 01-OCT-96,1/OCT/96 3/4，3-4：则显示“3月4日” 2）输入时间：hh:mm:ss 19：15，7:15空格PM，19时15分，下午7时15分 （12小时制：AM或PM，PM或P表示下午，AM或A表示上午。如“8:20 PM”） 日期和时间视为数字处理，右对齐。输入时，

7、必须严格按照规则，当输入的不是合法的日 期时系统自动认为是文本，被当做字符串来处理，就左对齐了，如：2011.3.4。 2021-8-7 3）日期和时间的组合输入 既要输入日期又要输入时间，则用空格分隔开 如：1996-10-01 19：15 ， 96年10月1日19时15分。 输入当天日期：ctrl+; 组合 输入当前时间：ctrl+: 组合 2021-8-7 4、填充 在Excel中，利用数据的填充可以将大量数据迅速地输入到工作表中去.对于有 一定规律的数据和公式，Excel提供快速序列填充，主要包括等比序列、等差序 列、日期序列、自定义序列等，对于不同的序列，填充方法稍有不同。 2021

8、-8-7 （1）填充柄自动填充 适用于填充相同文本序列、等比序列、等差序列、日期序列甚至是 自定义序列，但必须告诉Excel在开始时如何去做。步骤如下： 在第一个单元格区域中输入数据； 选择填好数据的单元格或区域，将光标移至区域的右下角填充柄处； 鼠标左键拖动填充柄至最后一个单元格松开鼠标即可; 2021-8-7 按住Ctrl键,选定所有需要输入相同数据的单元格 在最后选定的单元格中输入数据，按Ctrl+Enter键 (2)在不连续单元格中输入相同内容 2021-8-7 数字选项卡 数值：设置小数位数 货币：设置货币样式 日期：日期的表示形式 百分比：0.2 -20% 科学计数法： 0.145

9、 -1.45E-01 5、单元格的修饰： (1)“格式”/“单元格” 2021-8-7 设置对齐方式 默认情况下，Excel根据单元格中数据的类型，自动调整数据的 对齐方式。用户也可利用“单元格格式”的“对齐”选项卡进行设 置。 文本对齐方式：水平、垂直 文本控制：合并单元格 文本方向：旋转度 2021-8-7 设置字体 字体选项卡: 字体、字形、字号、 下划线、颜色 2021-8-7 框线的设置： 使用“格式”工具栏上的“边框”各按钮， 或执行“格式”/“单元格”命令，可以为表 格设置线条和边框线。 单击“格式”工具栏上“边框”按钮右边的小箭 头，屏幕出现“边框”各小图标。 2021-8-7

10、 单元格底纹： 2021-8-7 (2)格式/条件格式 满足条件的单元格显示特定的格式 2021-8-7 例：将小于60分的用红色倾斜显示，大于90的绿色倾斜加粗 2021-8-7 使用格式刷： 首先选中要复制格式的单元格； 然后单击工具栏上的“格式刷”按钮，最后在要复制到的单元格上单击。 (3)格式的复制 选择性粘贴 复制后，单击鼠标右键选择“选择性粘贴” 2021-8-7 (4)单元格的插入与修改、移动与复制、清除与删除： 插入： 选中要插入的单元格或区域； 选择【插入】单元格，弹出“插入”对话框 在提供的四种选择中选择所需的一种，再单击“确定”按钮。 若在原数据上略加修改且数据较长时，可

11、通过双击单元格来进行修改，此时状态栏 显示为“编辑”。也可单击要修改的单元格，重新录入新数据，状态栏显示为“就 绪”。 修改单元格内容： 2021-8-7 清除： 将单元格或区域的内容清除，使之成为空白区域。 删除： 将单元格或区域的整个内容（包括内容和单元格本身）去掉，然后它的右邻 （或下邻）单元格产生移动来顶替当前空间。 删除与清除： 2021-8-7 数据的清除包括四个方面：数据的格式、批注、内容及全部。我们可以只清除其中 一个方面，如只清除数据的内容，这时格式仍然保留。 【例】 清除单元格B3，B4，B5的全部内容。操作步骤如下： 选定区域B3:B5; 选择【编辑】清除全部 说明：在“

12、清除”子菜单中： “全部”：清除所有信息； “格式”：只清除格式，内容和批注不变 “内容”：只清除内容，格式和批注不变； “批注”：只清除批注（在单元格的右上角有红色小三角的表示有批注）。 若只需要清除数据的内容，保留数据的格式、批注等，还可以用以下简单的方法。 方法一： 选定B3:B5，按键。 方法二： 选定B3:B5，指针放在单元格区域内并用快捷菜单中的“清除内容”项 2021-8-7 清除单元格： 单元格还在,清除内容、格式、批注 删除单元格： 单元格不存在,将选定的单元格从工作表中删除 右键快捷菜单“删除” 或“编辑”菜单-删除 Backspace键或Del键 2021-8-7 （5）

13、插入批注 选择【插入】菜单的“批注”命令， 就会出现一个文本框，直接输入内容 即可。 此时，在当前单元格的右上角有一 个红色三角形，当鼠标经过它时，批 注会自动显示。 批注就是单元格的说明、备注。 2021-8-7 1 、数据清单 2 、数据排序 3 、数据筛选 4 、分类汇总 三、数据管理 2021-8-7 Excel工作表中单元格构成的矩形区域，又称数据列表。 记录：数据清单中的每一行（第一行称为表头）,每一行对应数据库中的一个 记录。 字段：每一列称为字段。列标是数据库中的字段名称 数据清单中不允许有空行或空列； 不能有数据完全相同的两行记录。 1、数据清单 2021-8-7 （1）简单

14、排序 例如：如何对“学生成绩统计表.xls”中的学生按“计算机基础”成绩降序排列？ （2）复杂数据排序 例如：对“学生成绩统计表.xls”中的学生按下列方式排列： 首先按“计算机基础”成绩降序排，如果“计算机基础”成绩相等，则按“高等 数学”成绩降序排，如果“高等数学”成绩也相等，则按“大学英语”成绩降序 排。 2、数据排序 2021-8-7 2021-8-7 当用户只对数据列表中一部分数据感兴趣时，可以使用数据筛选功能。数 据筛选将不感兴趣的记录暂时隐藏起来，只显示感兴趣的数据。 数据筛选分为两种： 自动筛选 高级筛选 3、数据筛选 步骤如下： 在数据清单以外的位置建立条件区域 “数据”“筛

15、选”“自动/ 高级筛 选”命令 2021-8-7 （1） 自动筛选 全部：显示所有行。 前10个：显示由数据项或百分比指定的上限或下限范围内的行。 自定义：使用当前列中的两个条件值，或者其他比较运算符，而 不是“与”运算符（默认运算符） 空白：只显示此列中含有空白单元格的数据行。 非空白：只显示此列中含有数值的数据行。 例1：将“学生成绩统计表.xls”中的计算机基础不及格的学生筛选出来？ 2021-8-7 （2） 高级筛选 能实现更复杂的筛选功能 例1：将“学生成绩统计表.xls”中的计算机基础不及格或者英语 不及格的学生筛选出来？ 例2：将“学生成绩统计表.xls”中的计算机基础不及格或者

16、英语 不及格的男生筛选出来？ 2021-8-7 1、建立条件区域，同一 行上的条件关系为逻辑与， 不同行之间的关系为逻辑 或 2、打开高级筛选 对话框 3、选择数据区域 和条件区域 4、单击“确定”按钮 2021-8-7 定义：是对数据清单按某个字段进行分类，将字段值相同的连续记录作为一类， 进行求和、平均、计数等汇总运算。 例如：求“学生成绩统计表.xls”中男生和女生的英语平均成绩 注意：在分类汇总之前，首先一定要对分类字段进行排序，否则分类汇总就没 有意义了。 4、 分类汇总 2021-8-7 先要对数据清单按分类的关键字进行排序，再执行“数据” 的“分类汇总”命令。 例4-19 在成绩

17、表中，按“班号”字段分类汇总“成绩”的 平均分(即统计出各个班的平均分) 2021-8-7 5、输入有效数据 选定若干个单元格 选“数据|有效性”命令 2021-8-7 用图表来描述电子表格中的数据是Excel的主要功能之一。Excel 能够将电子表格中的数据转换成各种类型的统计图表，更直观地揭 示数据之间的关系，反映数据的变化规律和发展趋势，使我们能一 目了然地进行数据分析。 常用的图表类型有柱形图、条形图、饼图、折线图、XY散点图 等 四、绘制图表 2021-8-7 （一）创建图表 1使用快捷键创建 选定区域按下F11键， 自动创建 2使用向导（如图1.2.3.4） 选择数据插入菜单图 表

18、类型确定数据系列设 置图表选项确定图表位置 2021-8-7 2021-8-7 如果选择“作为新工作表插入”单选按钮，会在该工作簿中创建一个图表工作表； 如果选择“作为其中的对象”单选按钮，将创建一个嵌入图表。 2021-8-7 （二）认识图表 2021-8-7 （三）编辑图表 选定图表项 选定绘图区 选定数据序列 2021-8-7 1页面 一、页面设置 五、排版与打印 2021-8-7 2页边距 2021-8-7 3页眉和页脚 2021-8-7 4工作表 2021-8-7 二、打印文档 打印的方法与Word基本相同,下面对打印对话框不同点加以说明： 选定区域：只打印工作表中选定的单元格区域。

19、非相邻的选定区域 打印在独立页上。此项优于在“页面设置”对话框中设定的打印区域。 2021-8-7 选定工作表：打印所有当前选定工作表的所有区域，按选定的 页数打印。如果没有定义打印页数，则打印整个工作表。 整个工作簿：打印当前工作簿中所有的工作表。 也可直接单击常用打印，将根据先前在“页面设置”对 话框中指定的选项，直接打印当前活动工作表。 2021-8-7 打印预览与打印 选择“文件”菜单中的“打印预览”命令，或者单击“常用”工具栏中的“打 印预览”按钮进入打印预览方式。 1打印预览 “下一页/上一页”按钮：当打印的工作表多于一页时，单击“下一页”按钮，可 以显示下一页；单击“上一页”按钮

20、，可以显示上一页。 “缩放”按钮：使工作表在放大视图和全页视图之间切换。 “打印”按钮：可以打开“打印”对话框。 “设置”按钮：可以打开“页面设置”对话框。 “页边距”按钮：可以调整页边距、页眉和页脚。 “分页预览/普通视图”按钮：可以在分页预览视图和普通视图之间切换。 “关闭”按钮：可以返回到工作表的编辑窗口中。 2021-8-7 2021-8-7 58 六、数据透视表 1. 数据透视表的建立 1）活动单元格在数据区域 数据数据透视表和图表报告 2）下一步下一步，注意 核实“选定区域”是否正确 3）显示“3步骤之3”，如右图 3步骤之3 版式 2021-8-7 59 4）单击“版式”按钮 5

21、）如图“版式”设置对话框 将主分类字段拖入到行，次分 类字段拖到列，汇总字段拖到 数据区域。 注意：双击数据区域中的汇总 字段可在此设置汇总方式 6）确定完成 版式设置对话框 2021-8-7 60 2. 数据透视表的修改 如图： 右击汇总字段名，可“隐藏” 和进行“字段设置“ 数据透视表工具栏 隐藏字段设置 2021-8-7 61 下图“字段设置”对话框，可设 置汇总方式 拖动“数据透视表”工具栏中的 字段名到透视表数据区，可添加 汇总字段 2021-8-7 七、百分位值 50分位位置=1+0.5*(7-1) 75分位位置=1+0.75*(7-1) 50分位值=(C4-C3)*0+C4 75

22、分位值=(C6-C5)*0.5+C5 3.7804233.3992063.2682543.4194442.527381 3.7833333.5967593.3968253.4417992.535714 3.7939813.6230163.5726853.5856482.771296 4.0342183.8255133.6820353.6732063.136285 4.1344714.1235193.8240744.0841533.24037 4.2574074.2153.8553174.1037043.375926 4.294634.2481484.2344444.3514813.406243

23、 50分位位置 4 75分位位置 5.5 50分位值4.03 3.83 3.68 3.67 3.14 75分位值4.20 4.17 3.84 4.09 3.31 2021-8-7 八、EXCEL函数应用 2021-8-7 Excel的主要功能不在于它能显示、存储数据，更重要的是对数据的计算能力， 它允许使用公式和函数对数据进行计算。 （1）公式 是对工作表中的数据进行分析与计算的等式。它以“=”开始，由常量、单元 格地址和函数组成。选定单元格输入“=”输入包括要计算的单元格相应的运算， 回车键或者在编辑栏中“”按钮即可。 （2）函数 是预先定义好的公式，由函数名、括号及括号内的参数组成。多个参

24、数之间用 “，”分隔。函数的分类：财务函数、日期与时间函数、数学与三角函数、统计 函数、查找与引用函数、数据库函数、文本函数、逻辑函数和信息函数等九类 。 2021-8-7 2、单元格的引用 相对引用：指当把一个含有单元格地址的公式复制到一个新的位置或者用一个公式填入一个区 域时，公式中的单元格地址会随着改变。 举例：将E16的求和结果复制到E17，E18，E19等. 选定E16，在其中输入=C16-D16，回车确定输入 使用编辑栏中复制命令 选定E17到E19处，用编辑栏中粘贴命令，结果自动得出。 2021-8-7 绝对引用：指在把公式复制或填入到新位置时，使其中的单元格地址保持不变。 需在

25、行号和列标前加上“”。 例：在F2中输入公式“$C$2+$D$2+$E$2” 设置绝对地址 需在行号和列 号前加符号“$” 2021-8-7 例：在F2中输入公式“C2+$D$2+$E2” 混合引用：是指在一个单元格地址中，既有绝对地址引用又有相对地址引用。 2021-8-7 表表5 公式返回的错误值及产生的原因（参考）公式返回的错误值及产生的原因（参考） 常见错误点：常见错误点： 2021-8-7 EXCEL函数的结构 Excel 函数即是预先定义，执行计算、分析等处理数据任务的特殊公式。 Excel 函数结构: 也有一些函数是没有 参数的,如ROW() 左右括号成对 出现 单 一 结 构

26、嵌 套 结 构 参数与参数之间使 用半角逗号进行分 隔 2021-8-7 函数参数常用符号或表示方法 n 函数公式中的文本必须用半角引号，如：东南汽车;而非直接输入 东南汽 车或“东南汽车” n 连接符: =（大于等于）;82000,(A3-2000)*40%-10375,IF(A362000,(A3-2000)*35%-6375,IF(A342000,(A3- 2000)*30%-3375,IF(A322000,(A3-2000)*25%-1375,IF(A37000,(A3-2000)*20%- 375,IF(A34000,(A3-2000)*15%-125,IF(A32500,(A3-2

27、000)*10%-25,IF(A32000,(A3- 2000)*5%,0) 2021-8-7 Sumif:条件求和函数 用途：根据指定条件对若干单元格、区域或引用求和。 语法：SUMIF(条件区域，条件，需求和的区域) 参数：条件是由数字、逻辑表达式等组成的判定条件。 2021-8-7 Sumif案例 请统计人资组同仁的工资总额: Sumif(A:A, 人资组,C:C) 2021-8-7 Countif：条件计数函数。 用途：计算区域中满足给定条件的单元格的个数。 语法：COUNTIF(统计区域，条件) 参数：“统计区域”为需要计算其中满足条件的单元格数目的单元格区域。“条件” 为确定哪些单

28、元格将被计算在内的条件，其形式可以为数字、表达式或文本。 2021-8-7 Countif案例 以上述样表为据,完成以下案例: n 例1：依“师员”字段，统计师员级的人数 countif(C:C,师)；countif(C:C,员) n 例2:统计全公司年资大於等於3年的人数及大于等于3年小于5年的人数 countif(I:I, =3)；countif(I:I, =3)- countif(I:I, =5) n 例3:检测到职编号是否有重复值 countif(A:A, A2) 2021-8-7 Counta 用途：返回参数组中非空值的数目。利用函数COUNTA 可以 计算数组或单元格区域中数据项的

29、个数。 语法：COUNTA(单元格区域1,单元格区域2) 说明：参数的个数为130 个。 2021-8-7 Counta的思考案例 n 例1：如果A1=6.28、A2=3.74，其余单元格为空，则公式 “=COUNTA(A1:A7)”的计算结果等于?。 n 例2：统计C列的非空白单元格的表示方法为：？; 若整张 EXCEL表的每个单元格都有数据,前述函数公式的结果为? 2021-8-7 Offset n 用途：以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以是一个单元格 或单元格区域（并不返回值）。 n 语法：OFFSET(作为偏移量参照系的引用位置，上下偏移的行数，左右偏移的

30、列数， height，width)。 n 参数： 作为偏移量参照系的引用位置：它必须是单元格或相连单元格区域的引用； 上(下)偏移的行数：正数代表在起始引用下边;负数代表在起始引用的上边； 0代表没有上 （下）偏移。 左(右)偏移的列数:正数代表在起始引用右边;负数代表在起始引用的左边；0代表没有左（右） 偏移。 Height ：是要返回的引用区域的总行数（必须为正数） Width ：是要返回的引用区域的总列数（必须为正数）。 2021-8-7 Offset的思考题 n 问题一： OFFSET(B1,2,1,4,1)定位到哪几个单元格？ n 问题二： 以A1单元格为参照，要定位到红线区域如何写

31、函 数： =OFFSET(A1,6,1,2,2) 2021-8-7 Row 用途：返回给定引用的行号。 语法：ROW(单元格引用)。“单元格引用”为需要得到其行号的单元格或单元格区域。 实例:利用row建立序号: 2021-8-7 MOD：取余 用途：返回两数相除的余数，其结果的正负号与除数相同。 语法：MOD(被除数，除数) 参数：除数不能为零。 Mod函数的案例 实例1：公式“=MOD(14，4)”返回?；“=MOD(-5，-2)”返回？ 2021-8-7 Vlookup 返回表格或数组当前行中指定列处的数值。 语法：VLOOKUP(索引值，数据区域，列序号，查找方式) 参数： 索引值为需

32、要在数据表第一列中查找的数值，它可以是数值、引用或文字串。 数据区域为需要在其中查找数据的数据表，可以使用对区域或区域名称的引用。 列序号 指数据区域 中待返回的匹配值的列序号。它等于1 时，返回数据区域 第一列中的 数值；它等于 2时，返回数据区域第二列中的数值，以此类推。 查找方式为一逻辑值,指明函数VLOOKUP 返回时是精确匹配还是近似匹配。如果为1或 省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于索引值 的最 大数值；如果为0，函数VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值 #N/A。 2021-8-7 Vlookup案例 案例一:利用vlooku

33、p 抓取另一张表中的资料。 案例二(请参考链接之案例,课后自学)：用vlookup计算所得税,解决IF只能嵌套七层的 局限。 2021-8-7 match 用途：返回在指定方式下与指定数值匹配的数组中元素的相应位置(不是具体的单元格)。 语法：MATCH(A，B，C)。 参数： A:为需要在数据表中查找的数值或单元格引用。 B:是可能包含所要查找的数值的连续单元格区域。 C:它说明Excel 如何在B 中查找A。C 的常用值为0，表示函数MATCH 查找等于A 的第 一个数值。 注意：MATCH 函数返回B 中目标值的位置，而不是数值本身。 2021-8-7 Match函数的案例 =MATCH(11，A1:A9，0)返回的值是? =match(420,A3:E3,0)返回的值是? 2021-8-7 Left/Right：截取字符串函数 用途：根据指定的字符数返回文本