Excel 基础培训课件.ppt

Excel基础培训 生产管理部徐景华2013 12 Excel概述 Excel是目前市场上功能最强大的电子表格制作软件 它和Word Powerpoint Access等组件一起 构成office办公软件的完整体系 Excel是工能强大 技术先进 实用方便且灵活的电子表格软件可以用来制作电子表格 完成复杂的数据运算 进行数据分析和预测 不仅具有强大的数据组织 计算 分析和统计功能 还可以通过图表 图形等多种形式形象地显示处理结果 更能够方便地与Office其他组件相互调用数据 实现资源共享 对我们工作实际应用提供高效的帮助 Contents 1 Excel基本界面 1 数据输入 2 格式设置 3 常用基本功能 4 加密Excel文件 5 打印设置 6 工作表的复制与移动 2 数据编辑 1 数据输入输入数字或文本a 在工作表中 单击一个单元格 b 键入所需的数字或文本 然后按Enter或Tab 默认情况下 按Enter会向下移动一个单元格 Tab会向右移动一个单元格 Excel单元格文字随时换行有时需要在一个单元格中分成几行显示文字等内容 方法一 一般是通过选中格式菜单中的 单元格 对齐 自动换行 复选项 单击 确定 即可 方法二 需要重起一行输入内容的时候 只要按住 Alt键 的同时按下回车键就可以了 这种方法又快又方便 2 数据编辑 Excel中插入空白行如果想在某一行上面插入几行空白行 可以用鼠标拖动自此行开始选择相应的行数 然后单击右键 选择插入 如果在每一行上面均插入一空白行 按住Ctrl键 依次单击要插入新行的行标按钮 单击右键 选择插入即可 Excel中消除0值有Excel中当单元格计算结果为0时 默认会显示0 这看起来显然有点碍眼 如果你想显示0时 显示为空白 可以试试下面的方法 打开 开始 Excel选项 高级 在具有零值的单元格中显示零 复选项前的 去掉 确定后 当前工作表中的值为0的单元格将全部显示成空白 2 数据编辑 2 格式设置格式设置 选中需要设置格式的单元格 然后点击右键 选中 设置单元格格式 在弹出的对话框中进行设置 在 数字 一栏选择你需要的类型 然后可以在其他菜单栏进行你需要的操作 或者直接利用窗口上面的选项进行设置 2 数据编辑 设置边框快速添加普通边框可以利用工具栏上的 边框 按钮可以快速为表格添加边框 选中需要添加边框的单元格区域 单击 格式 工具栏上的 边框 按钮 在随后弹出的下拉列表中 如图 选择一种框线样式即可 设置彩色边框选中需要添加边框的单元格区域 点击右键 设置单元格格式 命令 打开 单元格格式 对话框 切换到 边框 标签中 如图 设置好 线条 样式 和 颜色 再 预置 边框位置 全部设置完成后 确定返回即可 2 数据编辑 设置行高和列宽拖拉法 将鼠标移到行 列 标题的交界处 成双向拖拉箭头状时 按住左键向右 下 或向左 上 拖拉 即可调整行 列 宽 高 双击法 将鼠标移到行 列 标题的交界处 双击鼠标左键 即可快速将行 列 的行高 列宽 调整为 最合适的行高 列宽 设置法 选中需要设置的行高 列宽 的行 列 执行 格式行 列 行高 列宽 命令 打开 行高 列宽 对话框 输入一个合适的数值 确定返回即可 3 常用功能a 选择性粘贴 数值 公式 格式等 转置 行列互换 如图 b 格式刷 双击格式刷后可连续使用 再单击格式刷可停止使用 c 快速复制公式 单元格右下角的小黑十字上用鼠标快速点击两下 2 数据编辑 d 冻结窗口具体操作步骤 点击 视图 冻结窗口 冻结拆分窗格 快捷键 ALT W F Fa 冻行下一行 冻列右一列 行列右下格 如图所示 例 活动单元格在A2 冻结第一行活动单元格在B1 冻结第一列活动单元格在B2 冻结第一行第一列e 撤销 恢复或重复操作f 常用函数一键完成 如右图 基本的统计信息 如求和 计数 最小 大 值等 选择对应的项直接点击即可 2 数据编辑 g 隐藏列或行选择需要隐藏的 行 或 列 点击鼠标右键 点击隐藏即可 h 查找和替换不但文本或数字可以替换 连公式的部分内容都可以替换 有时在一份表格中要多次输入某个特殊符号 如 每次切换软键盘 非常麻烦 我们可以用替换的方法来加快输入的速度 先在输入需要特殊符号的地方输入一个字母 如 Z 表格整个输入完成后按 Ctrl H 组合键 或执行 编辑 替换 命令 打开 查找和替换 对话框 如图 在 查找内容 中输入上述字母 Z 在 替换为 中输入特殊符号 按下 全部替换 按钮即可 2 数据编辑 4 加密Excel文件用Excel制作完报表 如果不想让他人查看或修改 a Excel2007中点击左上角的选项按钮 在弹出菜单中选择 另存为 或者按快捷键F12也是一样的效果 b 在 另存为 界面左下角有一个 工具 按钮 2 数据编辑 点击 工具 按钮右边的小三角 在弹出菜单中选择 常规选项 设置 打开权限密码 后 他人可以用这个密码阅读这个Excel文件 设置 修改权限密码 后 他人可以用这个密码打开和修改这个Excel文件 勾选 建议只读 后 他人在试图打开Excel文件的时候 会弹出建议只读的提示窗口 2 数据编辑 5 打印设置设置打印区域选中需要打印的单元格区域 然后切换到 页面布局 选项卡 点击 页面设置 组中的 打印区域 按钮 在弹出的下列表中选择 设置打印区域 选项即可 设置打印标题在 页面设置 对话框的 工作表 选项卡中 单击 顶端标题行 文本框右侧的折叠按钮 在工作表中选中需打印的标题行即可 注 打印预览快捷键 CTRL F2 同时打印多个工作薄 表a 打印多个工作薄按Ctrl O组合键打开 打开 对话框 找到所需打印文件夹 按住Ctrl键不放依次选择需要打印的文件后 点击 工具 按钮 选择 打印 即可 2 数据编辑 b 打印多个工作表按住Ctrl键依次选中所需要打印工作表 在标题栏中会出现 工作表 字样后即可打印 6 Excel工作表的复制与移动技巧在使用Excel时 经常会需要在工作表间复制或移动数据 或者干脆复制整个工作表 复制一个工作表中所有单元格的内容和复制整个工作表是有区别的 下面分别介绍复制或移动的方法 a 复制与粘贴工作表中所有单元格 复制部分单元格也可以按下列步骤 操作步骤如下 1 按Ctrl A键选中所有单元格 也可以单击工作表左上角的行与列交点 从而选中所有单元格 2 按Ctrl C复制 3 转到另一个工作表 然后单击A1单元格 2 数据编辑 4 按Enter键或者是按Ctrl V粘贴 这样就将前一工作表中的所有单元格复制到了当前工作表中了 b 复制整个工作表复制整个工作表不只意味着要复制工作表中的单元格 还包括该工作表的页面设置参数 以及自定义的区域名称等 方法一1 移动鼠标指针到工作表标签上方 2 按住Ctrl键的同时拖动工作表到另一位置 3 松开Ctrl键和鼠标左键 这样就完成了同一工作薄整个工作表的移动并复制 方法二1 在工作表标签上单击右键 2 数据编辑 从弹出菜单中选择 移动或复制工作表 这时会出现如图所示的 移动或复制工作表 对话框 我们可以看到 可以将选定的工作表移动到同一工作表的不同位置 也可以选择移动到其它工作薄的指定位置 如果选中对话框下方的复选框 建立副本 就会在目标位置复制一个相同的工作表 2 数据编辑 1 数据输入 2 数据分列 3 用连字符 来合并文本 4 其它小技巧 3 常用小技巧 1 数据输入Excel中多个区域一次输入同一数据在多个区域输入同一数据 例如 在同一列的不同单元格中输入性别 男 可以一次性输入 按住 Ctrl 键同时 分别点选需要输入同一数据的多个单元格区域 然后直接输入数据 输入完成后 按下 Ctrl Enter 组合键确认一下即可 注意 输入完成后 不能直接按 Enter 键进行确认 如果采取此种方法输入公式 系统会智能化地修改公式中相对引用的单元格地址 快速输入大写中文数字的简便方法Excel具有将用户输入的小写数字转换为大写数字的功能 如它可自动将123 45转换为 壹佰贰拾叁点肆伍 这就极大的方便了用户对表格的处理工作 具体步骤为 a 将光标移至需要输入大写数字的单元格中 b 利用数字小键盘在单元格中输入相应的小写数字 如123 45 3 常用小技巧 C 右击该单元格 并从弹出的快捷菜单中执行 设置单元格格式 命令 d 从弹出的 单元格格式 对话框中选择 数字 选项卡 e 从 分类 列表中选择 特殊 选项 从 类别 列表框中选择 中文大写数字 选项 f 单击 确定 按钮 用户输入的123 45就会自动变为 壹佰贰拾叁点肆伍 效果非常不错 在多个工作表内输入相同内容的技巧a 打开相应工作薄b 在按下Ctrl键同时采用鼠标单击窗口需要输入相同内容的不同工作表 如Sheet1 sheet2 为这些工作表建立联系关系 c 在其中任意一个工作表中输入需要的内容 这些内容就会自动出现在关联的工作表中 d 输入完毕后 再次按下Ctrl键 并用鼠标单击关联的工作表 解除关联关系 3 常用小技巧 2 数据分列批量转换日期格式需要在Excel中输员工入出生时间时 可简单输入 yymmdd 形式 如果要求显示为 yyyy mm dd 格式 快速方法是 先选定要转化的区域点击 数据 分列 出现 文本分列向导 对话框 勾选 固定宽度 连续两次点击 下一步 按钮 在步骤三对话框的 列数据格式 中 选择 日期 并选定 YMD 形式 按下 完成 按钮即可 3 常用小技巧 妙用Excel把有规律的文本数据分列在打开的 文本向导 对话框中 选择 固定宽度 单击 下一步 单击建立分列线 按住左键可拖动分列线的位置 双击可取消分列线 3 常用小技巧 3 常用小技巧 分列完毕后 单击 下一步 设置每列的数据类型 如 第一列要设置为 文本 防止第一位是 0 的数字舍去 而第二第三列设置为 常规 即可 Excel将单元格中的内容自动判断并设置数据类型 单击 完成 按钮分列完毕 如图 3 用连字符 来合并文本如果想将多列的内容合并一列中 不需要利用函数 用连字符 就能够轻松将它搞定 在最后合并的单元格中输入公式 A1 A2 Enter即可 4 其他小技巧在绘图的时候按住shift键 可以很简单的画出正方形 直线和等边三角形等选中相应的单元格 按下 Ctrl 组合键 即可输入系统日期 按下 Ctrl shift 组合键 即可输入系统时间 注意 如果希望输入的日期或时间随系统日期或时间改变而改变 请在单元格中输入公式 TODAY 或NOW 单元格位置移动 Ctrl 方向 键 最上单元格 最单元格 最左 最右 3 常用小技巧 清除格式ERP或系统导出文件 往往带格式 导致文件大 打开慢 处理慢 严重影响工作效率操作1 全选 开始 编辑 清除格式 非常有用 请牢记 超链接同一个工作薄中工作表之间的超链接在一个工作薄中建立四个工作表 对工作表重新命名分别为 目录 A班数据 B班数据 C班数据 在 目录 工作表中输入后三个工作表名称 3 常用小技巧 点击 A班数据 点击鼠标右键 超链接 在对话框 插入超链接 选择 本文档中的位置 A班数据 确定 即可 点击对应的文本就可打开对应的工作表 如下图所示 3 常用小技巧 常用快捷键 如下表 3 常用小技巧 1 数据透视表 2 图表 3 排序 4 筛选 5 Excel中分类设置录入数据的下拉列表 6 绝对引用与相对引用 4 常用功能 1 数据透视表建立数据透视表的目的数据透视表能帮组用户分析 组织数据 利用它可以很快地从不同角度对数据进行分类汇总 首先应该明确不是所有的工作表都有建立数据透视表的必要 创建数据透视表a 将光标移入需建立数据透视表的工作表中 然后单击 插入 数据透视表 b 创建数据透视表 对话框上 选择相应的要分析的数据区域 4 常用功能 创建数据透视表 4 常用功能 创建数据透视表 4 常用功能 2 图表Excel提供了约14种标准图表类型 如面积图 柱形图 条形图 折线图 饼图 圆环图 气泡图 雷达图 股价图 曲面图 散点图 锥形图 圆柱图 菱形图等 每种图表都有几种不同的子类型 此外 Excel还提供了约20中自定义图表类型 用户可根据不同需要选用适当的图表类型 4 常用功能 选中数据区域 点击插入 柱形图 2 图表根据下图 红框 区域进行选择图表区域 编辑添加删除 轴标签 及 图例项 4 常用功能 2 图表将鼠标光标放在任意一柱形图柱上点击鼠标右键 点击 添加数据标签 即可 同上方法 设置数据标签格式 标签选项 可以给图表添加需要的标签选项 比如 名称 值 百分比等 显示数值如右图 4 常用功能 3 排序排序 行排序和列排序Excel可以对数据进行自动排序 但是 如果工作表中有拆分的单元格 则不能重新排序 工作表中某一区域重新排序 选定需要排序的 单元格 范围后 单击 数据 排序 再在 排序 对话框上选定排序规则 然后单击 确定 即可 如果选定某行再排序 仅仅是该行单元格重新排序 其他单元格不动 4 常用功能 4 筛选筛选时查找和处理区域中数据子集的快捷方法 筛选区域仅显示满足条件的行 该条件由用户针对某列指定 自动筛选的操作步骤如下 a 在存放筛选结果的工作表中任意选取一个单元格 b 单击 数据 筛选 在每一个字段名称旁产生一个下拉列表按钮 右图 c 单击要设定筛选条件的字段名字边的下拉列表框按钮 d 选取筛选条件 在该对话框上 根据需要设定筛选条件后 单击 确定 4 常用功能 5 Excel中分类设置录入数据的下拉列表如果在某些单元格中需要输入固定格式的数据 如 职称等 可以利用 数据有效性 做一个下拉列表 进行选择性输入 选中需要建立下拉列表的单元格区域 执行 数据 数据有效性 命令 打开 数据有效性 对话框 如图 在 设置 标签下 按 允许 右侧的下拉按钮 在随后弹出的快捷菜单中 选择 序列 选项 然后在下面 来源 方框中输入序列的各元素 如 高级工程师 工程师 助理工程师 技术员 其他职称 等 注 必须半角模式 确定返回 选中上述区域中某个单元格 在其右侧出现一个下拉按钮 在随后出现的下拉列表中 选择相应的元素 如 工程师 即可将该元素输入到相应的单元格中 4 常用功能 6 相对引用与绝对引用相对引用相对引用就是在输入公式时 对单元格数据地址的引用 如果复制的公式使用到相对引用 被粘贴的单元格地址将被更新 并指向与当前公式地址相对应的其他单元格 举例说明公式的相对引用 其具体操作步骤如下 a 分别在A1 A2 B1 B2 C1 C2单元格区域输入任意数据 b 在A3单元格中输入公式 A1 A2 C 将A3单元格中的公式复制到B3单元格时 B3单元格中的公式自动被更新为 B1 B2 d 同上操作 此时C3单元格中的公式被更新为 C1 C2 以此类推 这种引用方式即为相对引用 如图所示 4 常用功能 绝对引用绝对引用与相对引用不同 相对引用时单元格中的公式会被自动更新 而绝对引用在复制时不改变其公式内容 举例说明公式的绝对引用 其具体操作步骤如下 a 分别在工作表中的B1和D5单元格中输入任意数据 b 在A1单元格中输入公式 B 1 D 5 C 将A1单元格中的公式复制到工作表的任意单元格中 其公式的内容不发生改变 即为绝对引用 如图所示 4 常用功能 混合引用如果一个公式内即使用了相对引用 又使用了绝对引用 则称为混合引用 在使用混合引用时 一定要分清哪部分是相对引用 哪部分是绝对引用 绝对引用使用 符号来加以区别 例如 A1 表示列位置是绝对的 均为第A列 而行位置是相对的 A 1 则表示列位置是相对的 行位置是绝对引用 在输入公式时 可以通过按 F4 键改变引用类型 对于公式 仅输入单元格地址如 D1 为相对引用 按 F4 键 公式变为 D 1 即绝对引用 继续按 F4 键 变为混合引用 D 1 即列相对 行绝对 再接着按 F4 键 扔为混合引用 D1 但为列绝对 行相对 第4次按 F4 键时 则返回到相对引用 D1 举例说明公式的混合引用 其具体步骤如下 a 分别在A1 A5和B1 B5单元格中输入任意数据 4 常用功能 b 在A6单元格中输入公式 A1 A2 A3 A4 A5 C 将A6单元格中的数据输入到B6单元格 此时单元格中的公式被变更为 A1 A2 B3 B4 B5 可以看到 A1 A2 为绝对引用 而 B3 B4 B5 为相对引用 即为混合引用 如图所示 4 常用功能 1 与求和有关的函数的应用 2 逻辑函数 3 文本 日期 时间函数 4 查询与引用函数 5 统计函数 5 常用公式 1 与求和有关的函数的应用SUM 行或列求和 区域求和SUM函数中的参数 即被求和的单元格或单元格区域不能超过30个 换句话说 SUM函数括号中出现的分隔符 逗号 不能多于29个 否则Excel就会提示参数太多 SUMIF 在哪里找 找什么 加总哪些单元格 SUMIF函数可对满足某一条件的单元格区域求和 该条件可以是数值 文本或表达式 SUMIF C 3 C 12 销售部 F 3 F 12 SUMIF C 3 C 12 100 F 3 F 12 例1 在下面表格中 求出A部门总共有多少美元 即单元格D11的值 在D11单元格中使用公式 SUMIF C2 C10 C2 D2 D10 公式的意思是 在区域C2 C10 5 常用公式 中 找与单元格C2值相同的行 加总D2 D10这个区域里的值 例2 求B部门 且美元大于500的总和 建立F列 选出相应的行 F2使用公式 AND C2 B D2 500 函数AND的举例 依次往下拖 就可以显示整个F列相应的值 在F11使用公式 SUMIF F2 F10 F3 D2 D10 即可得到结果 5 常用公式 2 逻辑函数IF函数 条件 为真就怎样 否则就怎样 判断条件的真假 真 返回之中的指定内容 假 返回另外的指定内容 FALSE 如右图 AND函数 条件1 条件2 条件3 条件N 判断给出的条件是否都为真 为真就返回True 否则返回False OR函数 指在其参数组中 任何一个参数逻辑值为TRUE 即返回TRUE 它与AND函数的区别在于 AND函数要求所有函数逻辑值均为真 结果方为真 而OR函数仅需其中任何一个为真即可为真 5 常用公式 NOT函数 用于对数值求反 当要确保一个值不等于某一特定值时 可以使用NOT函数 简言之 就是当参数值为TRUE时 NOT函数返回的结果恰与之相反 结果为FALSE 比如 NOT 2 2 4 该参数结果为TRUE 由于是NOT函数 因此返回函数结果与之相反 为FALSE COUNTIF函数 在哪里找 找什么 可以用来计算给定区域内满足特定条件的单元格的数目 例1 在统计A列区域数值为80的单元格有几个 在A9单元格输入 COUNTIF A1 A8 80 结果显示为 2 如右图 例2 求指定单元格区域中 每个值出现的次数在 B8 单元格中输入 COUNTIF A 1 A 8 A8 回车后 在B列中拖动就可以吧整个A列对应的B列值求出来 如右图 5 常用公式 3 文本 日期 时间函数大小写转换LOWER 将一个文字串中的所有大写字母转换为小写字母 UPPER 将文本转换为大写形式 PROPER 将文字串的首字母及任何非字母字符之后的首字母转换成大写 将其余的字母转换成小写 这三种函数的基本语法形式均为函数名 text 取出字符串中的部分字符 如下图所示 LEFT函数 LEFT 从那里取 取几个 从左边开始取相应字符数的文本RIGHT函数 RIGHT 从那里取 取几个 从右边开始取相应字符数的文本MID函数 MID 从哪里取 第几个开始 取几个