企业培训_excel操作技巧与应用教材

Excel操作技巧与应用 入门篇目录 一 Excel的功用二 Excel界面认识三 状态栏使用方法四 Excel常用工具介绍五 Excel运算符号的认识及数据的输入六 Excel常用函数的初步认识七 常用快捷键的认识及使用方法 第一节 Excel的功用 1 能够制作各种不同的数据报表2 能够进行各种复杂的运算3 能够进行数据库管理 排序 筛选 4 能够生成各种不同类型的图表5 完成各种逻辑功能 第二节Excel界面认识 二 Excel界面认识 二 Excel界面认识 工作薄和工作表的关系 可以把工作簿看成是一个文件 把工作表看成是一个表 工作表的构成元素 单元格 行与列 一个工作表有65536行 256列 二 Excel界面认识 状态栏 菜单栏 工具栏 编辑栏 二 Excel界面认识 文件菜单常用功能 打开 保存 打印 页面设置 二 Excel界面认识 编辑菜单常用功能 复制 粘贴 剪贴 替换 二 Excel界面认识 注意 1 在编辑菜单里清除功能和删除功能的区别 2 键盘上的Delete键在电子表格里面实现的只是清除功能 3 选择删除的时候 需要注意表格的移动方向 二 Excel界面认识 视图菜单主要是为了方便编辑把常用的工具放在常用工具栏里 在工具栏一项可以选择你所需要的常用工具 二 Excel界面认识 视图菜单 工具栏 二 Excel界面认识 插入菜单常用功能 函数 图表 工作表 二 Excel界面认识 格式菜单常用功能 单元格格式 条件格式 工作表隐藏 格式菜单提要 1 单元格格式主要是设定表格中数字或者字符串的格式的工具 最常用的一个功能 如果用得好的话 可以把表格做得更加美观 人性化 2 行 列这两功能项里面的行高 列宽 还有隐藏行或列等等为需要重点掌握的功能 3 工作表项里面的隐藏工作表在需要辅助表格的报表里是非常有用的 4 条件格式是根据具体的需要按一定条件设置一个或一系列单元格的条件格式 具体看实例 二 Excel界面认识 二 Excel界面认识 条件格式实例 下图实现的功能是 所选区域的单元格的数值介于1和10之间的格式自动变成红色加粗斜体字 二 Excel界面认识 条件格式实现此功能 二 Excel界面认识 条件格式实例之手工编辑条件公式 下图实现的功能是 所选区域 D1 D10 的单元格的数值大于60数据的格式自动变成蓝色加粗斜体字 二 Excel界面认识 实现的结果 二 Excel界面认识 工具菜单常用工具 保护 公式审核 宏 二 Excel界面认识 数据菜单常用功能 排序 筛选 分类汇总 分列 二 Excel界面认识 窗口菜单常用功能 冻结窗格 拆分 二 Excel界面认识 帮助菜单里的MicrosoftExcel帮助 H 是很好的教材 有不懂的地方可以从中找到答案和方法 适合熟手提高技能 第三节状态栏使用方法 三 状态栏使用方法 状态栏是实时显示当前鼠标选择的数据的计算结果 各种计算可以选择 如图 有求和 最大值 最小值 计数值 计数 均值 只要用鼠标选择数据 状态栏可以根据需要的计算快速实时的算出结果 例如图中1 16的求和是136 点击鼠标右键 三 状态栏使用方法 计数和计数值有点类似 很容易别人搞错 因为如果所选的都是数据的话 两者的运算结果是一样的 但是两者还是有区别的 其实计数值只是针对数据而言的 但是计数可以适用于所有字符串 例如如下两幅图片可以看得出两者的区别 三 状态栏使用方法 第四节Excel常用工具介绍 四 Excel常用工具介绍 常用编辑工具 其中熟练复制 粘贴及格式刷的快捷妙用 对工作的提高和表格的美观 起到很大的作用 具体操作将在下一篇讲述 四 Excel常用工具介绍 常用格式工具 格式工具主要是把图表按照一定的喜好和习惯 把图表设计得更加美观 便于观看和分析 将在下一篇详细论述并掩饰操作过程 第五节Excel运算符号的认识及数据的输入 五 Excel运算符号的认识 运算符号 1 算术运算符 加 减 乘 除 乘方 2 文本连接符 你好 吗 你好吗 3 比较运算符 大于 大于等于 不等于 五 Excel运算符号的认识 1 公式 公式是指含有 号的式子 2 公式的输入 将光标指定所需的单元格 直接在单元格中输入 号 输入数据或单元格的地址 加运算符 回车确认 按ESC取消输入 例如 A1 B1 B1 具体看示范 五 Excel特殊数据输入 1 以文本形式输入的数据 在数字前面加上一个单引号 例如 00056 如果没有加单引号的话 输入的数字会自动默认成56 第六节Excel常用函数的初步认识 什么是函数Excel中所提的函数其实是一些预定义的公式 它们使用一些称为参数的特定数值按特定的顺序进行计算 函数的结构 等号 如果此函数位于公式开始位置 函数名称 参数 参数用括号括起 各参数之间用逗号分隔 初步了解Excel函数公式公式的组成元素 输入到单元格中的公式均由等号开头 等号后面由五种元素组成 运算符 运算符完成对公式元素的特定计算 如 等 单元格引用 它包括单个的单元格或多外单元格组成的范围 以及命名的单元格区域 这些单元格或范围可以是同一工作表中的 也可以是同一工作薄其他工作表中的 甚至是其他工作薄工作表中的 数值或文本 如 200 人人乐锦绣购物方场 工作表函数 可以是Excel内置的函数 如IF 也可以是自定义的函数 括号 即 和 用来控制公式中各表达式被处理的优先权 注 了解公式的组成是创建公式必备的基础 Excel工作表中的公式最多可由1024个字符组成 我们日常所创建的公式 超过100个字符的非常少 这个长度足够我们使用 函数使用步骤 第四步 第三步 第二步 第一步 选择使用函数计算的单元格区域 输入需要的函数 单击excel表上的fx或插入函数 单击需要插入函数单元格 六 Excel常用函数的初步认识 函数错误值的综述 1 DIV 0 表示公式被零除 如 100 0 2 表示单元格宽度不够 3 VALUE 表示参数或操作数的类型有误 4 NAME 表示Excel不识别公式中使用的名称 5 REF 表示单元格数据公式有误 第七节常用快捷键的认识及使用方法 七 常用快捷键的认识及使用方法 七 常用快捷键的认识及使用方法 七 常用快捷键的认识及使用方法 七 常用快捷键的认识及使用方法 Ctrl Tab键 可以在两个表格文件之间转换 Ctrl C 复制 Ctrl C 两次 显示 剪贴板 Ctrl V 粘贴 Ctrl F或Shift F5 显示 查找 对话框 Ctrl H 显示 替换 对话框 Ctrl 输入日期 Ctrl Shift 输入时间 Ctrl I 撤销最后一次操作 Ctrl Y或F4 复制最后一次操作 Ctrl F3 快速打开 自定义 窗口 Ctrl F4 关闭当前工作薄 Alt F4 退出整个Excel程序 休息一下 谢谢观赏 视觉游戏 黑林图形 黑林图形 黑线看起来是不是向外弯曲的 解析 黑线完全是笔直而平行的 这种经典幻觉由19世纪德国心理学家艾沃德 黑林首先发现 视觉游戏 梯形幻觉 梯形幻觉 哪条线显得长一点 红线还是蓝线 解析 红线比蓝线显得长一点 尽管它们的长度完全相等 小于90 的角使包含它的边显得短一些 而大于90 的角使包含它的边显得长一些 这就是梯形幻觉 视觉游戏 棋盘 解析 棋盘完全是平面的 这个棋盘以瑞典艺术家奥斯卡 路透斯沃德的一个设计为基础 又布鲁诺 危斯特创造 视觉游戏 两个圆 解析 两个内部的圆大小完全一样 当一个圆被几个较大的同心圆包围时 它看起来要比那个被一些圆点包围的圆小一些 视觉游戏 身体的紫罗兰 身体的紫罗兰 你能在叶子中间招道三个隐藏的侧面人像吗 视觉游戏 比泽尔德幻觉 比泽尔德幻觉 图中所有的红色看起来都一样吗 解析 语境会影响你对颜色的感知 所有的红色都是完全一样的 这就是比泽尔德幻觉 视觉游戏 疯狂的螺帽 疯狂的螺帽 你知道直钢棒是怎样神奇地穿过这两个看似乎成直角的螺帽孔的吗 解析 两个螺帽实际是中空的 虽然它们看起来是凸面的 所以两个螺帽并不互相垂直 螺帽被下方光源照到 一般光线应来自上方 这给人们判断他们的真实三维形状提供了错误信息 美国魔术世界里 安德鲁斯创造了这个精彩的幻觉作品 提高篇目录 一 Excel熟练操作的基础二 Excel工作组的妙用三 Excel表格格式的美观四 Excel样表 辅助表的制作五 Excel常用复杂函数详解六 Excel图表分析七 Excel的加密与表格的锁定八 Excel简单的宏操作九 Excel表格打印 一 Excel熟练操作的基础 复制 Ctrl C粘贴 Ctrl V剪贴板的使用 在使用电子表格进行工作时 用剪贴板可以把工作效率提高几倍 熟练使用剪贴板 还可以实现许多功能 打开剪贴板 编辑 Office剪贴板 或者在表格中双击复制健 即双击 Ctrl C 复制进入剪贴板的数据按先后顺序是从下到上 避免搞错 具体看实例 一 Excel熟练操作的基础 剪贴板只要是实现数据和字符串的复制粘贴 会把公式过滤掉 同时它有一个很大很实用的功能是否显示不粘贴 非常方便选择性数据的复制粘贴 具体看实例 一 Excel熟练操作的基础 拖动 复制 1 数字 日期 星期的复制2 如果不希望复制的数字 日期或星期自动累加 实现复制的数字 日期或星期一样 如何实现 3 公式的拖动 公式的复制 公式 复制 拖动的锁定 行列号锁定符的使用 当我们要对表格公式快速复制的情况时 一般会选择双击此单原格的右下角来完成 双击来完成的原则是它所靠着的这一排一定要有数据 不然双击是没办法显示下去的 如果中间隔着一排空格是不是没办法双击下去了 那么就来帮大家解决这个问题 我们可以选定这两格来双击 如果有三格当然就是选定三格了 看下图 靠着数据 隔着空格 选定两格后双击看看效果 做报表对着大量数据时 想到达最后一排 是不是还在用鼠标小心翼翼的往下慢慢拖吗 还在因为不小心拖过去又往回拖呢 那么现在为你们解决这类烦恼 EXCEL表格有很多神奇之处等待着我们去发掘 来点实用的先 Ctrl Shift 方向键 将选定区域扩展到与活动单元格在同一列或同一行的最后一个非空单元格 可以直接选择到最后一行数据 上下左右 方向都可随意选择 对要选定所有数据有很好的效果 可以直接到最底就不用担心会拖过界了 但要注意的是要保证你这一列 行数据的中间没有空格存在 Ctrl Shift 空格键 在选定了一个对象的情况下 选定工作表上的所有对象 要注意的是不必要的数据先行删除 不然选定的数据多于你想要的 Shift 鼠标双击 在按住Shift键的同时在表格中把鼠标停在两个方格中间的线上 双击鼠标也可以达到此效果 往那边就双击哪边 要注意的也是要保证你的列 行数据的中间没有空格存在 把鼠标停在中间那条线上变成十字的样子的时候就可以双击 一 Excel熟练操作的基础 选择性粘贴的功用 可以实现数值 公式 格式 转置 各种运算等功能 具体请看操作示范 二 Excel工作组的妙用 大多数大型的工作表格 都是由很多小表组成的 这时为了提高工作效率 节约时间 使用工作组来工作是非常必要的 1 工作组的选择 工作表的新建 重命名 移动 复制 删除等操作的熟悉 2 工作组复制粘贴的熟悉 三 Excel表格格式的美观 设置单元格格式的使用 三 Excel表格格式的美观 1 单元格格式中的数字格式 2 单元格格式中的对齐格式 3 单元格格式中的字体格式 4 单元格格式中的边框格式 5 单元格格式中的背景格式 6 单元格格式中的保护功能 三 Excel表格格式的美观 格式刷的使用 1 双击格式刷 2 工作组格式刷的功用 3 区域格式刷新 四 Excel样表 辅助表的制作 根据工作需要的不同 我们在工作中需要制作各种Excel表格样表 一可以给自己工作带来方便 二是给别人做参考学习 三是便于规范各个分公司或部门的工作 样表主要是从实际需要出发 多考虑实用性 美观性 如果是给别人参考的 多从对方的角度出发 方便对方快速接受 如果是统一规范工作的 应从严谨规范 大体上考虑 四 Excel样表 辅助表的制作 在工作中 有时候数据量较大 整理起来比较麻烦 因而常常需要使用辅助表格 辅助表格有两种 一种是独立的辅助表 此表只是在完成一项工作的一个中间过程 就像一个工具一样 只要通过此表得到我们需要的结果即可 另外一种辅助表是和工作表在一起的 一般连接在工作表后面 是一种数据的来源 也叫数据源表 此类表在我们工作中是很常用的 具体请看实例 INT 函数的使用 将数字向下舍入到最接近的整数 语法 INT number Number需要进行向下舍入取整的实数 例如 int 6 99 所得结果是6 int 6 12 所得结果也是6 ROUND 函数的使用 将数字进行四舍五入的舍入 语法 ROUND number Number需要进行四舍五入的实数 例如 round 6 99 1 所得结果是7 0 round 6 12 1 所得结果也是6 1 五 复杂函数详解 ROUNDUP 函数的使用 将数字进行无条件向上的舍入 语法 ROUNDUP number Number需要进行四舍五入的实数 例如 roundup 6 99 1 所得结果是7 0 roundup 6 12 1 所得结果也是6 2 ROUNDDOWN 函数的使用 将数字进行无条件向下的舍入 语法 ROUNDDOWN number Number需要进行四舍五入的实数 例如 rounddown 6 99 1 所得结果是6 9 rounddown 6 12 1 所得结果也是6 1 COUNTIF 函数的详解 计算区域中满足给定条件的单元格的个数 语法COUNTIF range criteria Range为需要计算其中满足条件的单元格数目的单元格区域 Criteria为确定哪些单元格将被计算在内的条件 其形式可以为数字 表达式或文本 例如 条件可以表示为60 60 60 或 数学 说明MicrosoftExcel提供其他函数 可用来基于条件分析数据 若要使公式返回两个基于条件的值之一 例如 算出某门课程有多少学生及格 有多少学生90分以上等等 都可以使用该函数实现 SUMIF 函数的详解 根据指定条件对若干单元格求和 此公式是判断求和 非常实用语法SUMIF range criteria sum range Range为用于条件判断的单元格区域 Criteria为确定哪些单元格将被相加求和的条件 其形式可以为数字 表达式或文本 例如 条件可以表示为60 60 60 或 数学 Sum range是需要求和的实际单元格 SUMIF 函数的说明只有在区域中相应的单元格符合条件的情况下 sum range中的单元格才求和 如果忽略了sum range 则对区域中的单元格求和 例如 SUMIF E2 E24 毛利 C2 C24 E2 E24是指判断区间 毛利 是判断条件 C2 C24是求和区间 意思就是在E2 E24有 毛利 这个字段对应的C2 C24的数字相加 判断条件可以是字符串 也可以是数字 但是是字符串必须使用英文的双引号 数据就不需要 具体看示范操作 IF 函数的详解 执行真假值判断 根据逻辑计算的真假值 返回不同结果 可以使用函数IF对数值和公式进行条件检测 语法IF logical test value if true value if false IF 条件 真 假 如果条件判断为真 则执行第一个命令 反之执行第二个命令 该函数重要一个使用功能是嵌套 最多可以嵌套七层 说明函数IF可以嵌套七层 用value if false及value if true参数可以构造复杂的检测条件 在计算参数value if true和value if false后 函数IF返回相应语句执行后的返回值 IF 嵌套的实用性很广 可以用于作各种判断 例如 已知张三同学某课的成绩 求出他的成绩等级 可以用此函数实现 IF B2100 成绩有误 优秀 用IF 函数嵌套其他函数 例如嵌套sum sumif AVERAGE countif等等常用的函数 例如根据条件 求和或求平均值 进行数据运算 并反馈运算结果 可以这样实现 IF D5 合计 SUM B2 B12 IF D5 平均 AVERAGE B2 B12 条件不符 具体操作 看其他示例 常用引用函数VLOOKUPVLOOKUP用于在表格或数值组的首列查找指定的数值 并由此返回表格或数组当前行中指定列处的数值 语法形式 VLOOKUP lookup value table array col index num range lookup lookup value表示要查找的值 它必须位于自定义查找区域的最左列 可以为数值 引用或文字串 table array表示查找的区域 用于查找数据的区域 上面的查找值必须位于这个区域的最左列 可以使用对区域或名称的引用 col index num为相对列号 最左列为1 其右边一列为2 依此类推 range lookup的为一逻辑值 指明函数是精确匹配 还是近似匹配 常用文本函数LEFT MID RIGHTLEFT函数 LEFT text num chars 其中text是包含要提取字符的文本串 num chars指定要由LEFT所提取的字符数 MID函数 MID text start num num chars 其中text是包含要提取字符的文本串 start num是文本中要提取的第一个字符的位置 RIGHT函数 RIGHT text num chars 其中text是包含要提取字符的文本串 num chars指定希望RIGHT提取的字符数 例 从字符串 Thisisanapple 分别取出字符 This apple is 的具体函数写法为 LEFT Thisisanapple 4 ThisRIGHT Thisisanapple 5 appleMID Thisisanapple 6 2 is 常用日期 时间函数取出当前系统时间 日期信息用于取出当前系统时间 日期信息的函数主要NOW TODAY语法形式均为 函数名 取得日期 时间的部分字段值如果需要单独的年份 月份 日数或小时的数据时 可以使用HOUR DAY MONTH YEAR函数直接从日期 时间中取出需要的数据 比如 需要返回2001 5 3012 30PM的年份 月份 日数及小时数 可以分别采用相应函数实现 假如在E5位置 YEAR E5 2001MONTH E5 5DAY E5 30HOUR E5 12 六 Excel图表分析 图表的概念 指将工作中的数据用图形表示出来 图表便于我们阅读和分析数据 建立图表 在菜单栏里 插入 图表 即弹出如图所示的界面 Excel图表图形有 柱形图 条形图 折线图 饼图 XY散点图 面积图 圆环图 雷达图 曲面图 气泡图 股价图