会计常用Excel技巧.ppt

目录 Page1 4 数据透视表图 3 公式与函数 2 常用功能和技巧 1 工作簿和工作表 5 简述图表应用 6 初识宏与VBA 培训前言 Office是最为流行的办公软件 其家族成员主要有Excel Word PowerPoint Access及Outlook 前三者应用较为广泛 而Excel无疑是最重要的成员 Excel可以制作电子表格 进行各种数据处理及计算分析等 被广泛应用于管理 统计 财务 金融等众多领域 在很多企业和单位 Excel已经成为数据处理和信息化建设的重要工具 无论用户身处哪个行业 所在公司有没有实施信息ERP系统 只要和数据打交道 Excel几乎是不二的选择 Page2 培训前言 其实市面上有很多关于Excel的书籍写得很不错 如Excelhome网站组织编写的 Excel2007应用大全 Excel实战技巧精粹 系列等 出版图书是面向全国不同水平层次的读者 知识点涉及较多或为求全面难免讲解不够深入 学习不知从何入手 有些不常用的功能 花了不少的时间和精力学习 却极少使用 本教材根据公司内部培训资料整理而成 配以电子档实例 动态操作图及配套练习题 力求培训简单有效和快速吸收 Page3 培训前言 笔者从事财务工作13年 因此主要以财务工作中实际应用较多的操作技能为培训重点 旨在提高财务人员的实操能力 提升工作效率 也适用于统计 数据分析和文职类工作 特别说明 Excel操作通常会有三种实现方式 菜单栏选项 单击鼠标右键和快捷功能键 本着方便快捷的原则 一般只讲解常用的方式 本教材适用Excel2007版本 Page4 第1章工作簿和工作表1 1认识Excel1 2工作簿的操作1 3工作表的操作 Page5 第1章工作簿和工作表 1 1认识Excel首先 我们来了解一下Excel几个重要的对象 按照包含关系依次为工作簿 工作表和单元格 工作簿 是指Excel环境中用来储存并处理工作数据的文件 也就是我们常说的Excel文档 它是Excel工作区中一个或多个工作表的集合 受内存影响 每个工作簿最多可创建255个工作表 新建的工作簿默认有3个工作表 如果要改变默认表数量 可以通过Excel选项 常用选项卡进行修改 Page6 第1章工作簿和工作表 1 1认识Excel工作表 显示在工作簿窗口中 由行列交叉构成的表格 一个工作表可以有1 048 576行和16 384列 Excel2003版本为65 536行和256列 单元格 工作表中行与列的交叉部分 它是组成表格的最小单位 可拆分或者合并 单元格按所在的行列位置来命名 例如 B5 指的是B列与第5行交叉位置上的单元格 还有其他对象如图形 批注 控件等 在后面的章节涉及时再详细介绍 Page7 第1章工作簿和工作表 1 2工作簿的操作1 2 1基本操作创建 Ctrl N 在Windows窗口空白处点击右键 新建 Excel工作表 也可以在Excel程序已打开时按Ctrl N新建一个空白工作簿 复制 Ctrl C 剪切 Ctrl X 粘贴 Ctrl V 通过点击鼠标右键或快捷键完成 同一文件夹下复制 按住Ctrl键 同时用鼠标拖曳则效率更高 删除 Ctrl D 通过点击鼠标右键 按Ctrl D或Delete键完成 重命名 F2 选中工作簿名称 点击鼠标右键或按F2功能键进入修改状态 Page8 第1章工作簿和工作表 1 2 2直接保存手动保存 点击菜单栏图标或使用快捷键Ctrl S 工作簿打开后有修改内容或格式 不包括查找 点击关闭会提示是否保存修改内容 否则直接关闭 自动保存 为避免各种意外造成工作簿未能及时保存 Excel提供了自动保存和恢复功能 当发生断电或程序崩溃的情形 Excel重启后会在左侧弹出最后一次保存前的工作簿 但最后一次保存后编辑的内容将不会保存 无法恢复 友情提示 自动保存不能代替手工保存 经常点击保存按钮或Ctrl S 培养良好的工作习惯 Page9 第1章工作簿和工作表 1 2 3另存为另存为的作用在于原工作簿修改后不能直接保存时 可以将修改后的内容以其他格式或不同名称保存至同一文件夹中 如保存至不同文件夹 则不受名称重复限制 也不会出现 xx文件已经存在 是否替换现有文件 的提示 另存成功后 当前工作簿切换为新的工作簿 如打开A修改后另存为B 则A自动关闭 B为打开状态 A修改后如果没有手动保存 A的内容不会发生改变 工作簿打开界面 点击左上角图标 另存为选项下有多种文件格式可以选择 Page10 第1章工作簿和工作表 1 2 3另存为 启用宏的Excel工作簿 是指工作簿包含宏表函数 宏或者VBA代码时 必须以这种格式保存 否则无法正常保存使用宏表函数的定义名称 宏或者VBA代码 Excel97 2003工作簿 则主要是考虑到版本向下兼容 不能向上兼容 即低版本不能另存为高版本 使两台不同Excel版本的电脑之间可以共享资源 还有其他一些格式可以根据需要进行选择 不过实际使用的概率不大 Page11 第1章工作簿和工作表 1 2 4工作簿加密和只读工作簿打开界面 点击左上角图标 准备 加密文档 两次输入密码 保存后即可完成工作簿加密 再次打开文档 需要输入正确的密码 解除加密状态 点击左上角图标 准备 加密文档 清除密码后保存文档 加密解除 为防止其他使用人更改文件内容 还可以选择 标记为最终状态 相当于标记为只读属性 2007版还增加了一种通过添加不可见数字签名的方式 来确保工作簿的完整性 对文档进行数字签名后 文档就变成只读 以防止修改 Page12 第1章工作簿和工作表 1 3工作表的操作1 3 1基本操作选中工作表标签 以下简称表标签 点击鼠标右键可以新建 删除 重命名 隐藏或取消隐藏 移动或复制 保护工作表 选中表标签后 按下Ctrl键 按住鼠标左键向左或向右拖动 可以快速地复制工作表 不按Ctrl键 快速移动工作表 选项 可以新建工作簿及实现不同工作簿之间工作表的复制或转移 如勾选 建立副本 为复制 否则为移动 无论复制还是移动 前提是目标工作簿处于打开状态 深度隐藏 即工作表界面无法显示和取消隐藏 选中工作表标签 右键 查看代码进入VBA编辑界面 选择需要隐藏的工作表 将其Visible属性改为 然后退出即可 Page13 第1章工作簿和工作表 1 3 2保护工作表依次选择审阅 更改组 保护工作表或者选中工作表标签右键选择保护工作表 两次输入密码 保存后完成表格加密 工作表加密后 允许用户执行哪些操作 可以根据需要对加密内容进行勾选 如图1 1 图1 1 Page14 第1章工作簿和工作表 1 3 2保护工作表勾选 编辑对象 加密后仍然可以对图形 图表 控件等对象进行选中和编辑 否则只能选中一部分对象 勾选 编辑方案 加密后仍然可以对数据 数据工具 假设分析下的方案管理器 单变量求解进行编辑 特别提示 如果想在工作表加密后使用自动筛选和数据透视表 必须在加密前标记筛选状态和添加数据透视表 并同时勾选 使用自动筛选 和 使用数据透视表 即使这样 数据透视表仍然有一部分操作受到限制 Page15 第1章工作簿和工作表 1 3 2保护工作表设置允许用户编辑区域 是一个非常实用的功能 因为工作表加密后 经常是部分区域锁定 部分区域需要编辑 提示 新建工作表的单元格默认处于锁定状态 如果需要锁定大部分单元格区域 加密前先点击 允许用户编辑区域 按需要添加单元格范围 这部分单元格加密后仍然可以进行编辑 如果只是锁定小部分区域 可以在加密前全选工作表 按Ctrl A或点击左上角行列交叉的区域 然后右键 设置单元格格式 保护选项卡 去掉 锁定 前面的勾 这样全表单元格取消锁定状态 再选定需要锁定的单元格区域 勾选 锁定 即可 Page16 第1章工作簿和工作表 1 3 3打印操作点击左上角图标选择打印或Ctrl P调出打印对话框 根据需要修改打印设置 如打印范围 打印份数 打印内容等 页面布局中 可以设置固定的打印区域 页边距 页眉页脚 纸张大小等 设置顶端标题行 可以在打印多页的情况下 保证每一页都有正文标题和表头字段 插入分页符 则可以根据自己的需要安排每页打印内容 而不必每页都保持一致 Page17 第1章工作簿和工作表 1 3 3打印操作为提升美感或添加公司LOGO 还可以制作或插入图片作为文档背景 但背景图片不能直接打印 可以在设置页眉或页脚时插入所需图片 即可以实现此功能 特别提示 打印预览空白文档 会提示 MicrosoftOfficeExcel未发现可以打印的内容 但只要表格中有一个空格 就不会出现以上提示 如果你遇到明明可以打印成一页却出现两页的情况 就应该考虑是不是存在超出打印页面的空格 格式设置或标点符号之类的无效内容 Page18 第2章常用功能和技巧2 1快速录入和快捷操作2 2开始菜单2 3排序2 4筛选2 5查找和替换2 6定位填充2 7分列与选择性粘贴2 8数据有效性2 9其他功能和常见问题 Page19 第2章常用功能和技巧 为了便于记忆和实际应用 本章将按菜单栏分布顺序 选择常用的编辑或操作功能进行讲解 涉及到函数应用和数据汇总分析的 分别在第三章函数和公式 第四章数据透视表图进行讲述 此章暂不涉及 开始本章之前 先来认识一下 快速访问工具栏 即菜单栏上方长条形区域 右键点击最后的倒三角箭头 可以通过勾选增加和删除现有快捷键 选择其他命令 还可以自定义更多快捷键 如图2 1 Page20 第2章常用功能和技巧 2 1快速录入和快捷操作2 1 1快速录入自动更新日期与固定日期的输入选取任意一个单元格 输入函数或快捷键 自动更新当前日期 TODAY 自动更新当前日期和时间 NOW 固定当前日期 按Ctrl 固定当前时间 按Ctrl Shift 固定当前日期和时间 按Ctrl 然后按空格键 再按Ctrl Shift 自动更新 意味着工作表每一次重新计算 单元格内的日期和时间都会发生变化 Page21 第2章常用功能和技巧 2 1 1快速录入序列填充通过序列填充可以快速输入相同或有序的文本和数字 实现方式一般有三种 鼠标拖动填充柄输入序列 鼠标左键或右键拖放 通过菜单栏 编辑 填充 序列 通过菜单栏 数据 数据有效性 设置 选项卡 有效性条件选择 序列 和 侧重于数字 文本型数字和自定义序列的输入 主要用于固定内容的文本多次选择性输入 稍后在数据有效性章节讲解 Page22 第2章常用功能和技巧 2 1 1快速录入鼠标拖曳填充选中单元格 鼠标移到单元格边框右下角 当鼠标变成十字架 按住鼠标左键向下拖曳或右键向下拖曳后根据实际需求进行选择 还有一种更快速的方法 就是当鼠标变成十字架时 双击鼠标左键 但前提是该填充列左边或右边列有相同行数的数据且中间不能断行 拖曳方向不同 填充结果也不一样 向下或向右是数字的绝对值递增 向上或向左是绝对值递减 来看两组动态操作图 序列填充1和序列填充2 Page23 第2章常用功能和技巧 2 1 1快速录入序列填充1 A列使用鼠标左键拖曳 因为左右列无相同行数的数据 且必须选中两个单元格的数据 否则无法判断等差序列的步长值 B C D列都是双击鼠标左键实现快速填充 操作结果显示 B列按自然数顺序填充 C列数字顺序填充 文本不变 D列按自定义序列顺序填充 达到最大值以后重新返回最小值再次顺序填充 序列填充2 拖曳鼠标左键同时按住Ctrl键 鼠标变成大小两个十字架 同样的数据 拖曳以后结果变成了复制 Page24 第2章常用功能和技巧 2 1 1快速录入特殊情况下的数据输入 输入分数 如1 2 应先输入 0 及一个空格后顺序输入1 2 结果显示 1 2 输入平方米m2 先输入m2 再双击单元格进入编辑状态 抹黑数字2 鼠标右键 设置单元格格式 特殊效果选择上标 即可达到所需效果m2 输入纯数字 如身份证号码和银行卡号 超过15位会变成科学计数法形式且最后几位变成0 如果输入前加英文状态下单引号 Excel自动将其作为字符型数据处理 可以避免出现错误 插入特殊字符 选择插入菜单 特殊符号 符号 更多 包括特殊符号 标点符号 数学序号等 Page25 第2章常用功能和技巧 2 1 2快捷操作Ctrl A 选择数据区域任意单元格 按1次快速选中数据区域 但区域中不能有完全的空白行或列 再按1次 全选工作表 如图2 2 选择A1 B7任意单元格 按Ctrl A只能选中A1 B7区域 如果C1 C7任意单元格有数据 可以选中A1 D7 同理 A8 B8有一个数据且C1 C7无数据 则选中A1 B11 空白行列交叉处C8如果有数据则可以全选A1 D11 图2 2 Page26 第2章常用功能和技巧 2 1 2快捷操作双击单元格边框 选择数据区域中任意单元格 双击黑色边框下边 鼠标快速移到数据区域最后一行 同理 双击上边框移到顶部 左右边框则分别移到数据区域第一列 最后一列 不管上下左右 鼠标所在行或列都不能有空行或空列 否则只能移到第一个空行或空列之前 Shift 双击单元格边框 按住Shift键同时双击单元格边框则选中所选单元格所在行或列 其他规则一致 其操作效果等同于Ctrl Shift 方向键 还有Ctrl C复制 Ctrl V粘贴 Ctrl X剪切 Ctrl D向下复制 Ctrl R向右复制 F5调用定位框 Ctrl F查找替换 双击单元格快速进入编辑状态 F4重复上一次操作 不包括录入数据 等 请依据自身需求选择学习和使用 Page27 第2章常用功能和技巧 2 1 3操作技巧 快速修改单元格内容的前后左右次序选中单元格区域 按住Shift键 鼠标停留在右边框或下边框 鼠标变成带箭头的十字架后向目标单元格拖曳 当目标单元格右侧或下方出现 工 形状 松开鼠标和按键 即可完成顺序调换 选择多个工作表同时编辑或输入如果想一次操作多张工作表 省略后续的复制 粘贴操作 可采用以下方法 按住Shift键或Ctrl键并配以鼠标操作 在工作簿底部选择多个彼此相邻或不相邻的工作表标签 就可以对选中的工作表实行多方面的批量处理 Page28 第2章常用功能和技巧 2 1 3操作技巧一般情况下 多表同时进行的操作主要有以下几种 1 页面设置 可快速对选中工作表设置相同的页面 2 在多个工作表中一次性输入相同的数据 3 在多个工作表中进行一系列相同操作 如设置字号 字体 颜色 进行单元格的合并撤销等 4 快速输入相同的公式 不管哪种操作 前提是多张工作表应具有相同的数据位置和格式 否则操作结果会出现错行或错列的情况 Page29 第2章常用功能和技巧 2 2开始菜单开始菜单主要是一些字体及格式设置 单元格行列调整 制表的快捷键 鼠标移到旁边后都有相应的说明 不再赘述 重点说一下格式刷 合并单元格 条件格式 制作斜线表头和排序 格式刷 快速将指定单元格的格式复制到目标单元格 具体操作 选择含有所需格式的单元格区域 单击工具条上的 格式刷 按钮 此时鼠标变成了刷子形状 然后单击要格式化的单元格区域即可将格式拷贝过去 Page30 第2章常用功能和技巧 2 2 1合并居中合并单元格 两个或多个相邻的水平或垂直单元格 合并成一个跨多列或多行显示的大单元格 选定区域左上角第一个单元格的数据出现在合并单元格的中心 如第一列单元格无数据则保留第二列 第一行无数据则保留第二行第一列单元格 如图2 3 选定区域A1 B3中 保留单元格A2的内容 其余单元格被覆盖 选中合并单元格 编辑栏地址始终显示选定区域最左上角单元格 图2 3 合并前 合并后 Page31 第2章常用功能和技巧 2 2 1合并居中选中已合并单元格 再次点击 合并后居中 按钮 即取消单元格合并 为了表格更美观 经常需要用到这个功能 如图2 4 但是有合并单元格的工作表 可能会影响复制 粘贴 筛选 排序等正常编辑操作 而弹出错误提示 图2 4 Page32 第2章常用功能和技巧 2 2 1合并居中跨列居中 如果是单行需要合并居中 可使用跨列居中代替 一样的效果且不影响正常编辑 选择所需单元格 只能其中一个单元格有数据 否则可能达不到所预期的效果 鼠标右键 设置单元格格式 对齐选项卡 水平对齐 选择跨列居中 合并单元格应用技巧合并单元格使数据或表格变得美观 却对Vlookup Count Sumif等查找统计公式应用带来了一些麻烦 通过组合应用可以解决这个问题 主要思路就是复制表格后 取消合并单元格 利用定位填充功能将F列数据填充完整 再利用格式刷复制A列格式即可 具体操作步骤如动态图合并 格式刷 Page33 第2章常用功能和技巧 2 2 2条件格式条件格式 使用数据条 颜色刻度和图标集来直观地显示数据 目的在于突出显示用户所关注的单元格或单元格区域 强调异常值 条件格式基于条件更改单元格区域的外观 如图2 5 条件格式设置的条件为C列大于650 000的红色字体粉红色背景显示 更改单元格数据格式会随时更新 如C8改成600 000 Page34 第2章常用功能和技巧 2 2 2条件格式Excel2007在条件格式这一块增加了很多功能 包括数据条 色阶 图标集等 来看一下应用数据条和图标集的效果 都是根据数值大小显示相应的长度或图标 让数据使用者看起来更直观 如图2 6和2 7 图2 6图2 7 Page35 第2章常用功能和技巧 2 2 2条件格式条件格式还可以通过设置公式更改单元格的外观 选中A2 D10 条件格式 管理规则 选择规则类型 使用公式确定要设置格式的单元格 为符合此公式的值设置格式 在下面的方框中输入公式 ISEVEN ROW COLUMN 选择相应的填充颜色 单元格显示如图2 8 图2 8 Page36 第2章常用功能和技巧 2 2 3斜线表头制作斜线表头 中国人习惯在表头的行列交叉部分 用斜线分隔 分别标示表格横向和纵向数据的类别 而Excel只能在单个单元格 包括合并单元格 使用斜线 所以只能通过其他辅助方式完成 先说单斜线表头的制作方法 合并单元格 输入年度 部门 鼠标移到 度 字后面 按Alt Enter强制换行 文本左对齐 在 年度 前面输入空格 添加斜线 就可以了 操作过程如动态图单斜线表头 另一种方法 在A3 A4单元格分别输入年度和部门 分别文本右对齐 左对齐 再画一条斜线 同样能实现这个效果 再来看看两条斜线的表头 方法差不多 不过斜线只能通过绘图方式完成 操作过程如动态图双斜线表头 Page37 第2章常用功能和技巧 2 2 4文本换行自动换行和强制换行自动换行 在同一单元格通过多行显示 使单元格中所有内容都可见 操作方法 选择要设置格式的单元格 单击自动换行按钮即可 再次单击取消自动换行 自动换行 单元格文本内容根据列宽自动分割成多行显示以适应列宽 当更改列宽时 数据换行会自动调整 强制换行 可以在指定的位置插入换行符Alt Enter 实现特殊情况下的换行需求 比如制作斜线表头 提示 使用强制换行 必须删除换行符才能取消换行 Page38 第2章常用功能和技巧 2 3排序2 3 1基本原则对一列或多列数据按文本 数字 日期和时间 升序或降序 进行排序 也可以按自定义序列 如大 中 小 或格式 包括单元格颜色 字体颜色或图标集 进行排序 大多数排序操作都是按列 也可以按行进行 在按升序排序时 Excel使用如下排序次序 降序 则反之 数字 数字按从最小的负数到最大的正数进行排序 日期 日期按从最早的日期到最晚的日期进行排序 文本 文本及文本型数字按以下次序排序 0123456789 空格 ABCDEFGHIJKLMNOPQRSTUVWXYZ Page39 第2章常用功能和技巧 2 3 1基本原则字母 数字组合文本按从左到右的顺序逐字符进行排序 例如 如果一个单元格中含有文本 A100 Excel会将其放在含有 A1 单元格的后面 含有 A11 单元格的前面 汉字则按拼音首字母排序 第一个字母相同则按第二个汉字首字母 以此类推 逻辑 在逻辑值中 FALSE排在TRUE之前 错误值 所有错误值 如 NUM 和 REF 的优先级相同 空白单元格 无论升序还是降序排序 空白单元格总是放在最后 以上类型数据排序顺序 按升序 数字 日期 文本 逻辑值 错误值 Page40 第2章常用功能和技巧 2 3 2自定义序列创建自定义序列 在单元格区域中 按照需要的顺序从上到下输入要排序的值 例如 董事长 总经理 经理 科长 组长 选择包含自定义序列的单元格区域 单击 Office按钮 选择Excel选项 常用选项卡 在 创建用于排序和填充序列的列表 右边 单击 编辑自定义列表 在 自定义序列 对话框中 单击 导入 然后单击确定两次 完成自定义序列 按自定义序列排序时 排序次序应选择 自定义序列 Page41 第2章常用功能和技巧 2 3 3排序方法单列排序在数据区域中选定某一列标题所在单元格 单击快速访问工具栏中的升序或降序按钮即完成排序 多列排序单击选项卡 选择自定义排序 调出排序对话框 然后根据多个排序关键字的优先级别 按照从高到低的原则依次添加 并选择相应的排序依据和次序 勾选 数据包含标题 排序关键字显示行标题 否则显示列标如列A 列B等 特别提示 对数据进行排序之前 应该先取消隐藏已隐藏的行或列 否则排序结果会出现错误 示例如图2 9和2 10 看看相同关键字 不同优先级别排序后的效果 Page42 第2章常用功能和技巧 2 3 3排序方法按班级 升序 总分 降序 语文 降序 排序后的效果如图2 9 通过对比可以看出 首先按班级果果1班 首字母G在H之前 排在前面 果果1班中总分最高的排在前面 总分相同 语文成绩最高的排在前面 原表 排序后 Page43 图2 9 第2章常用功能和技巧 2 3 3排序方法按总分 降序 班级 升序 语文 降序 排序后的效果如图2 10 第一张表 对比一下两张表不同排序方式的差别 更有助于理解排序功能 排序后 图2 9排序后 Page44 图2 10 第2章常用功能和技巧 2 3 4排序技巧应用利用特别的排序方式隔行插入空行这个技巧比较实用 主要是利用含有数字的辅助列排序后 由Excel自动插入空行 示例如动态图隔行插入空行 隔行插入两个空行 也是一样的思路 在1 1 9 1序列后再加一组1 2 2 2 3 2 9 2即可 也可以是1 11 2 11 3 11 9 11 插入三行以及更多行 则以此类推 隔两行或两行以上插入一个空行或多行 该怎么实现 插入辅助列的数字有什么规律 由各位自己来总结吧 Page45 第2章常用功能和技巧 2 4筛选定义 根据指定条件 显示那些满足条件的行 隐藏不符合条件的行 筛选过的数据子集 不需要重新排列或移动就可以复制 查找 编辑 设置格式 制作图表和打印 筛选包括自动筛选和高级筛选 自动筛选可以按多个列 即多次指定筛选条件 进行筛选 每追加一次筛选都是基于前一次的筛选结果 使用自动筛选可以创建三种筛选类型 按条件 格式或列表值 对于每个单元格区域或列表来说 这三种筛选类型是互斥的 即不能同时应用两种以上类型 Page46 第2章常用功能和技巧 2 4筛选特别提示 为了获得最佳效果 请不要在同一列中使用混合的存储格式 如文本和数字 或数字和日期 因为每一列只有一种类型的筛选命令可用 如果一列单元格数据使用了混合的存储格式 则显示的命令对应于出现次数最多的存储格式 例如 该列包含3个数字和4个文本单元格 点击筛选按钮显示的筛选命令是 文本筛选 反之 包含4个数字和3个文本则显示 数字筛选 Excel2007的列表值筛选从界面和可操作性都有了很大改善 之前版本的列表值筛选不能多选 而且列表值没有正确的排序 导致用户筛选时出现错误判断 Page47 第2章常用功能和技巧 2 4筛选2 4 1自动筛选通常是在一个数据区域中的一列或几列中查找相同的值 筛选范围和筛选结果都在同一个数据区域 操作方法 选择数据区域中的任意一个单元格 单击 开始 选项卡 编辑组 排序和筛选 筛选 或单击 数据 选项卡 筛选 数据区域首行标记筛选按钮 选择两个以上单元格 则在选中区域从首列开始 第一行标记筛选按钮 重复操作一次取消筛选 提示 筛选按钮所在行的数据不包含在筛选数据范围内 Page48 第2章常用功能和技巧 2 4 1自动筛选文本 数字和日期筛选 筛选命令明细项各不相同 但操作方法基本一致 首先可以在左边的筛选列表中直接勾选或取消勾选数据 如勾选不能满足需求 可以通过右边的命令细项设定筛选条件 按多个条件筛选 则选择 自定义筛选 多条件筛选要注意 与 和 或 的区别 两个条件都必须满足则选择 与 否则选择 或 示例如动态图自动筛选 筛选花花4班数学成绩大于95或小于90的学生 Page49 第2章常用功能和技巧 2 4 1自动筛选快速筛选 选中目标单元格 点击鼠标右键 筛选 分别可以 按所选单元格的值筛选 按所选单元格的颜色筛选 按所选单元格的字体颜色筛选 和 按所选单元格的图标筛选 快速筛选出该列相同值的单元格区域 通配符的使用当筛选值不确定或模糊筛选时 就需要使用通配符 用问号 和星号 分别表示一个或多个字符 具体如图2 11 Page50 第2章常用功能和技巧 2 4 2高级筛选若要通过复杂的条件来筛选单元格区域 须使用 数据 选项卡上 高级 筛选命令 在要筛选的单元格区域上方插入至少三个空白行 作为筛选条件区域 条件区域必须具有列标签 并确保与数据区域之间至少留有一个空白行 高级筛选的条件比较复杂 但有一个基本原则 同一行是 与 并列关系 筛选结果必须同时满足 同一列是 或 选择关系 筛选结果满足其中任意一个条件即可 通过示例来理解筛选条件的应用更直观 如图2 12 Page51 第2章常用功能和技巧 2 4 2高级筛选高级筛选条件示例图 Page52 图2 12 第2章常用功能和技巧 2 4 2高级筛选利用高级筛选删除重复值或筛选唯一值这是一个很有用的功能 工作中也经常用得到 现实中有人会利用自动筛选分批删除每一项数据的重复行 有人将数据排序后逐一删除重复行 数据少还好 几千上万行的怎么办 筛选唯一值和删除重复值是两个紧密相关的任务 这是因为二者所显示的结果相同 一个唯一值的列表 但差别也很明显 筛选唯一值是临时隐藏重复的值 删除重复值则会永久删除重复值且无法恢复 操作方法还是用示例来说明 筛选唯一值 分两种方式 如动态图高级筛选 删除重复值 弹出对话框后 可以勾选判断重复值的数据列 如动态图删除重复值 Page53 第2章常用功能和技巧 2 5查找和替换按Ctrl F 调出 查找和替换 对话框 可以搜索指定字符串 如果需要 也可以用其他字符串替换该字符串 查找内容可以是文本 数字和错误值 也可以选择特定单元格格式 如背景颜色 字体颜色等 单元格格式可以设定 也可以从目标单元格区域提取 点击 查找和替换 对话框右下角的 选项 有6个查找参数可以选择和勾选 范围 选择 工作表 可将搜索范围限制为活动工作表 选择 工作簿 可搜索活动工作簿中的所有工作表 搜索 选择 按列 按列向下搜索 选择 按行 按行向右搜索 Page54 第2章常用功能和技巧 2 5查找和替换查找范围 指定是要搜索目标单元格的值还是其所隐含的公式或批注内容 例如 某单元格数据显示为 11 但实际上包含公式 10 1 查找范围选择 值 搜索 11 时 指向该单元格 查找范围为 公式 则显示 找不到正在搜索的数据 在 替换 选项卡上 查找范围中 公式 是唯一的选项 区分大小写 区分大小写字符 单元格匹配 搜索与 查找内容 框中指定的内容完全匹配的字符 查找全部 查找文档中符合搜索条件的所有内容 并在下方的显示框全部列示 Page55 第2章常用功能和技巧 2 5查找和替换 替换 选项卡中的选项大都与 查找 选项卡相同 只不过还具有一些可用于替换搜索数据的附加选项 替换为 输入要用来替换 查找内容 的替换字符 若要从文档中删除 查找内容 框中的字符 请将 替换为 框保留为空即可 全部替换 替换文档中符合搜索条件的所有内容 如果希望逐项查看并有选择地替换各项 请单击 替换 而不是单击 全部替换 通配符的使用与自动筛选的使用规则基本一致 参考2 4 1自动筛选通配符的使用 Page56 第2章常用功能和技巧 2 5查找和替换特别提示 在数据区域选中任意一个单元格 查找和替换的范围默认为整张工作表 选择两个 含 以上单元格 则查找替换范围限制在选中单元格区域 如果不注意这个问题 就可能出现这样的困扰 表格中明明看到了搜索值 Excel却提示 找不到正在搜索的数据 其实这是Excel一个基本的原则 选择任意一个单元格 Excel根据相应的操作自动选择全部数据区域 选择两个或两个以上单元格 默认选择选中区域 查找和替换的操作示例参考下一节 定位填充 Page57 第2章常用功能和技巧 2 6定位填充定位 根据指定的位置 命名区域或定位条件 快速选中目标单元格区域 工作表界面 按F5弹出定位对话框 点击 定位条件 对话框会显示多项定位条件 如批注 常量 公式 空值等等 根据字面意思即可理解 此处重点讲解一下空值和可见单元格的定位条件应用 空值 快速选中当前工作表数据区域中的空白单元格 可见单元格 快速选中当前工作表非隐藏状态的单元格区域 例如 当目标单元格区域存在隐藏行或列 复制后粘贴 隐藏的行或列也一并粘贴 如果使用定位条件 可见单元格选中后复制 则隐藏行不会被复制粘贴 Page58 第2章常用功能和技巧 2 6定位填充定位技巧应用 定位填充操作方法 选中目标单元格区域 按F5 定位条件 空白值 选中空白单元格以后 按等号 第一个空白单元格等于上一单元格的值 同时按Ctrl Enter 然后全选表格复制 粘贴值 示例说明 会计一般会用到带核算项目 部门 的科目余额表 用于统计分析期间费用或制造费用 本示例是一张从金蝶财务软件引出的制造费用科目余额表 带明细核算项目 需要会计按部门统计各明细科目的金额 本示例主要是组合使用查找替换 定位填充及自动筛选功能 操作如动态图定位填充 Page59 第2章常用功能和技巧 2 7分列与选择性粘贴2 7 1分列根据数据内容 可以基于分隔符 如空格或逗号 或数据中的特定分栏符位置将单元格内容拆分到不同的列中 分隔符包括空格 逗号 分号和其他任何能作为区分标识的符号或字符 也可以是固定宽度的分栏符 分列操作向导总共三个步骤 第三个步骤可以分别对拆分后的列设置数据格式 如常规 文本和日期 选中 不导入此列 跳过 则该列被忽略 不在拆分后的数据结果中显示 默认状态下 分列操作结果覆盖原有数据 如果需要保留被拆分列 可以在 目标区域 的右边框重新指定单元格地址 操作方法如动态图分列 Page60 第2章常用功能和技巧 2 7 1分列分列技巧应用 转换数据格式文本型数字转换数字方法比较多 分列 选择性粘贴以及鼠标右键直接 转换为数字 文本型日期与日期互转 数字转换成文本型数字基本上只能使用分列 无论哪种转换 操作方法基本一致 分列向导第1步选择 固定宽度 第2步无须建立分列线 直接点击 下一步 第3步选择需要转换的格式 然后点击完成 Page61 第2章常用功能和技巧 2 7 2选择性粘贴大多数人 习惯使用Ctrl C和Ctrl V完成复制 粘贴 使用菜单栏粘贴选项完成粘贴值 Ctrl V同时复制源数据的公式 格式 批注 边框等 粘贴值将源数据统一粘贴为数值 常量 且不带格式 边框及批注 使用 选择性粘贴 对话框 可复制工作表中的复杂选项 可以根据需求进行相应选择 同时还可以将复制的源数据与目标区域的数据进行运算 开始菜单 粘贴 选择性粘贴 弹出 选择性粘贴 对话框 简单介绍一下个别选项的作用 仅从字面就可以理解的不再赘述 Page62 第2章常用功能和技巧 2 7 2选择性粘贴有效性验证 将所复制单元格的数据有效性验证规则粘贴到粘贴区域 边框除外 粘贴所复制单元格的所有单元格内容和格式 边框除外 加 指定要将所复制数据与目标单元格或单元格区域中的数据相加 减 乘同加运算 除 指定要用所复制的数据 除数 除目标单元格或单元格区域 被除数 中的数据 跳过空单元 选中此复选框 则当源数据中有空单元格时 可避免替换目标区域中相对应位置的值 Page63 第2章常用功能和技巧 2 7 2选择性粘贴转置 选中此复选框 所复制数据的行列可实现位置转换 粘贴链接 粘贴后的数据链接到源数据 相当于复制了源数据的位置 因此当源数据发生改变 粘贴后的数据相应发生改变 用示例来演示一下如何完成复制后的加运算 减 除 乘运算同理 和行列转置 如动态图选择性粘贴 Page64 第2章常用功能和技巧 2 8数据有效性2 8 1基本功能作用 根据预先设定的规则 验证用户输入数据的有效性 当数据不符合预设规则时弹出警告提示 指导用户及时清除无效数据 更正输入内容 数据有效性的应用 可以归为两大类 限制输入非法值 制作可供选择输入的下拉列表 操作方法 选中需应用数据有效性的单元格区域 选择数据菜单 数据有效性 在 数据有效性 对话框设置选项卡输入有效性条件 限制数据输入规则包括数字大小 常量序列 日期时间区间 文本长度以及公式计算后的值 Page65 第2章常用功能和技巧 2 8 1基本功能设置提醒框选中I列 选择 数据有效性 菜单 单击 输入信息 选项卡 勾选 选定单元格时显示输入信息 输入提示信息的标题和文本 如图2 13 Page66 第2章常用功能和技巧 2 8 1基本功能设置出错警示框选中I列 选择 数据有效性 菜单 单击 出错警告 选项卡 勾选 输入无效数据时显示出错警告 选择警告样式 输入警告信息的标题和文本 如图2 14 Page67 第2章常用功能和技巧 2 8 2技巧应用限制同一列中不能输入重复的内容假设A列限制输入重复的姓名 选中A列 在对话框选择自定义 公式框输入 COUNTIF A A A2 1 勾选 输入无效数据时显示出错警告 设置成功后 在A列输入重复的姓名 Excel会弹出警告信息 并根据选择的警告样式直接取消输入内容 询问是否继续或仅提示输入非法值 制作可供选择输入的下拉列表以制作一份省市级联菜单为例 输入省份后根据省份名称自动显示城市列表 制作方法如动态图2 13级联菜单 Page68 第2章常用功能和技巧 2 9其他功能和常见问题2 9 1超链接为了快速访问另一个文件或同一个文件中的其他位置以及网页上的相关信息 可以在工作表单元格中插入超链接 超链接可以简单理解为目标位置的访问路径 创建超链接有4种类型 指向新文件 现有文件和网页 工作簿特定位置和电子邮件地址 每种类型操作方法基本一致 特别提示 如果单击指向电子邮件地址的超链接 电子邮件程序将自动启动 并会创建一封以该邮件地址为 收件人 的新邮件 前提是已经安装了电子邮件程序 否则会出现错误提示 Page69 第2章常用功能和技巧 2 9 1超链接如果在单元格中输入电子邮件地址或网址 Excel会自动创建一个指向该地址的超链接 例如 输入正确 规范的网址或 回车后单元格内容自动创建一个超链接 再次点击单元格会弹出相应的网页 编辑 取消和删除超链接 不能用鼠标左键选中 必须通过鼠标右键菜单操作 利用超链接创建文件目录是比较典型的应用 以创建同一工作簿特定位置的超链接为例 操作过程参考动态图2 14超链接 Page70 第2章常用功能和技巧 2 9 2常见问题在实际使用过程中 经常会有一些我们认为按常理无法解释的现象 比如只有几个简单表格的工作簿 大小却有几十M之多 计算结果理论上应该是整数却莫名多出很多位小数等 这些异常现象有一些是Excel本身的小Bug或无法避免的问题 但大部分还是因为用户对Excel了解不够深入而得出的错误结论 本节主要就是针对这类常见问题进行解析或提供相应的处理方法 Page71 第2章常用功能和技巧 2 9 2常见问题明明是数字为什么不能求和 如图2 15 A B两列同样的数字 选中A列状态栏会自动显示总数或平均值 但选中B列却只有计数结果 为什么会有这样的差异 因为A B两列的数据格式不一样 A列是数字 B列是文本型数字 其实质还是文本 从第一个文本型数字单元格开始选中该列 会出现图二中的菱形符号 单击该符号选择转换为数字即可以全部转换为数值型数字 也可以使用选择性粘贴的运算 分列等方式完成数字转换 图2 15 Page72 第2章常用功能和技巧 2 9 2常见问题为莫名肥胖的Excel文件减肥 1很多人都可能曾经遇到这个问题 文件不明原因的增大 打开 计算都很慢 有时甚至造成文件损坏而无法打开的情况 导致这个问题的原因大概有以下几种 从网页上复制内容直接粘贴到工作表中 而没有使用选择性粘帖 工作表存在大量的直线 方框或其他图形对象 由于很小 肉眼几乎无法看到 按功能键F5 定位条件 对象 确定 再按Delete键清除 工作表在很大的范围内设置了单元格格式或者条件格式以及数据有效性 我们在应用一些格式设置时 往往为了方 Page73 第2章常用功能和技巧 2 9 2常见问题为莫名肥胖的Excel文件减肥 2便 直接在整行 整列或者在多行和多列中应用 太多空白单元格格式 包括字体 颜色等 势必要造成Excel文件体积变大 遇到这种情况 可以手动清除空白单元格格式 但更好的方法是养成一个良好的操作习惯 大量重复使用复杂 冗长的公式 尤其是复杂的引用公式或引用范围较大的公式 数据量较大时 尽量采用添加辅助行或列以简化公式或定义名称的处理方式 根据实际引用范围选择单元格区域或采用动态引用 避免无效的引用范围 外部链接 特别是死链接 的影响 工作簿中如果包含了一些外部引用 文件在打开时 默认情况下Excel总是尝试去 Page74 第2章常用功能和技巧 2 9 2常见问题为莫名肥胖的Excel文件减肥 3链接源文件 以刷新数据 在保存时会纪录链接的变化情况 当源文件的位置或者内容发生变化时 就可能产生死链接 如无必要 请将链接公式粘贴值 文件异常退出 或者其他不可预见的原因 可能造成工作簿内工作表结构方面的损坏 如果你的文件不存在前面提到的几个问题 可以尝试如下方法 新建一个工作簿 把现有文件中的工作表逐一复制到新的工作簿中 复制后粘贴值 此方法不足之处 目标工作表的行列格式可能要重新设置和调整 Page75 第3章公式与函数3 1公式和函数概述3 2公式的基础知识3 3函数分类和参数要求3 4常用函数 Page76 第3章公式与函数 3 1公式和函数概述公式和函数是Excel的特色之一 也最能体现其出色的数据计算和分析能力 灵活使用函数和公式可以大大提升数据处理分析的能力和效率 函数 Function 和公式 Formula 是彼此相关但又完全不同的两个概念 公式是以 号开头 进行数据运算处理并返回结果的等式 函数是预定义的公式 按特定算法执行计算而产生一个或一组结果 从广义的角度来讲 函数也是一种公式 公式的组成要素包括等号 运算符 常量 单元格引用 函数 定义名称等 公式可以用在单元格中 直接返回运算结果来为单元格赋值 Page77 第3章公式与函数 3 1公式和函数概述也可以在条件格式 数据有效性中使用 通过公式运算结果所产生的逻辑值 来决定用户定义的规则是否生效 公式通常只能从其他单元格中获取数据来进行运算 而不能直接或间接地通过自身所在单元格进行计算 除非是有目的的迭代运算 否则会造成循环引用错误 函数的结构 以等号 开始 后面紧跟函数名称 左括号和该函数的参数 多个参数以逗号分隔 最后是右括号 以Round为例 将A2单元格中的数字四舍五入并保留两位小数 公式为 Round A2 2 参数 函数中用来执行操作或计算的值 参数的类型与函数有关 常用的参数类型包括数字 文本 单元格引用和名称 Page78 第3章公式与函数 3 1公式和函数概述创建带函数的公式 可以点击编辑栏按钮 弹出 插入函数 对话框 然后根据需要选择和查找相应函数 通过Excel选项勾选 公式记忆式键入 当你输入 等号 和函数开头的一个或几个字母 Excel会在单元格的下方显示一个动态下拉列表 该列表根据输入进度逐步显示与已输入字母相匹配的有效函数 参数和名称 嵌套函数 在处理一些复杂的计算要求时 需要将一个函数作为另一函数的参数使用 也可以理解为函数的组合应用 修改公式 进入公式编辑状态有三种方式 快捷键F2 双击公式位置 单击编辑栏 Page79 第3章公式与函数 3 2公式的基础知识3 2 1运算符运算符是公式的基本元素之一 用于指定要对公式中元素执行的计算类型 分4种类型 算术 比较 文本连接和引用运算符 如图3 1 Page80 第3章公式与函数 3 2 1运算符运算顺序 与常规的数学计算式运算相似 所有的运算符都有运算的优先级 如图3 2 Page81 第3章公式与函数 3 2 1运算符如果要人为改变公式的运算顺序 可以使用括号提高运算优先级 数学计算式中使用小括号 中括号 和大括号 以改变运算的优先级别 在Excel中均使用小括号代替 括号的优先级将高于图3 2中的所有运算符 如果在公式中使用多组括号进行嵌套 其计算顺序是由最内层的括号逐级向外进行运算 例如公式 INT A1 1 3 先执行A1 1运算 再将得到的和除以3 最后由INT函数取整 特别提示 数学计算式的乘 除 乘幂在Excel中的表示方式有所不同 例如数学计算式 3 2 2 10 4 3 3 Excel中的公式表示为 3 2 2 10 4 3 3 2 Excel的数据一般可以分为文本 数值 日期 逻辑值 错误值等几种类型 Page82 第3章公式与函数 3 2 2数据类型文本 在公式中用一对半角双引号 所包含的内容表示文本 例如 Excel 是由5个字符组成的文本 日期和时间 是数值的特殊表现形式 每一天用数值1表示 1小时为1 24 1分钟的值为1 24 60 1秒钟的值为1 24 60 60 逻辑值 只有TRUE和FALSE两个 返回某表达式是真或假 错误值 Excel公式由于某些计算原因无法返回正确结果 显示为错误值 包括 DIV 0 N A NAME NULL NUM REF 和 VALUE 共八种 特别提示 数字与数值是两个不同的概念 数字可以包含文本型数字和数值型数字两种 数值是由负数 零或正数组成的数据 Page83 第3章公式与函数 3 2 2数据类型错误值解析 当列宽不够显示数字或运算结果超过日期时间的允许范围时 1900 1 1至9999 12 31 会出现此错误 VALUE 参数或操作数类型错误时 会出现此错误 例如公式 SUM Excel 对文本字符串进行求和运算 就会产生这样的错误 DIV 0 当数字被零 0 除时 会出现此错误 NAME 当Excel未识别公式中的文本时 出现此错误 N A 当数值对函数或公式不可用时 或数组公式中使用的参数的行数或列数与包含数组公式的区域的行数或列数不一致时会出现此错误 REF 当单元格引用无效时 出现此错误 NUM 公式或函数中使用无效数字值时 出现此错误 Page84 第3章公式与函数 3 2 2数据类型 NULL 使用交叉运算符 空格 来进行单元格引用 但引用的两个区域并不存在实际的交叠区域 出现此错误 错误自动检查 当单元格中的公式显示为错误值时 单元格左上角会显示绿色三角箭头的错