Excel中高知识培训.ppt

Excel知识中高级培训 Newton Liu2005 12 Excel基础知识 表格数据输入 对多个单元格输入相同数据 按住Ctrl键的同时 单击需要输入数据的单元格 在光标闪动的单元格中输入数据 同时按下Ctrl和Enter键 数据就被同时输入了 如下图 Excel基础知识 各种类型数据的输入 数值数据在输入数字时 可以包括一个逗号 如 1 450 500 05 数值项目中的单个句点作为小数点处理 在数字前输入的正号被忽略 在负数前加上一个减号或用圆括号括起来 注 在公式中 不能用圆括号表示负数 不能用逗号来分隔千位 也不能在数字前使用美元符号 Excel基础知识 各种类型数据的输入 文本数据在Excel中输入的文字通常是指文本 任何输入的字符集 只要不被系统解释成数字 公式 日期 时间 逻辑值 将其视为文字 如果想在单元格中输入一组数据 且前面为0开头 只要在输入项前添加 即可 Excel基础知识 各种类型数据的输入 日期数据当在单元格中输入可识别的日期和时间数据时 单元格的格式会自动从 通用 转换为相应的 日期 或者 时间 格式 而不需要去设定该单元格为日期或者时间格式 工作表的格式化 自动套用格式化选定要格式化的区域A1 G14 注意并非整张表 否则在套用时会导致内存不足 工作表的格式化 执行 格式 自动套用格式 命令 弹出下左图所示对话框 选择自己喜欢的格式 单击 确定 工作表的样式如下右图 工作表的格式化 单元格的格式化如果对上表还是不满意 还可以对单元格进一步格式化 例如进一步格式化数据区域 C3 G14 选定数据区域C3 G14 执行 格式 单元格 命令 弹出以下对话框 在框中选择 数字 标签 并在 分类 列表框中选择 货币 然后在 小数位数 文本框中输入 0 再在 货币符号 下拉框中选择 单击确定 如下图 工作表的格式化 另外 若在 单元格格式 对话框中选择 对齐 标签 弹出下左图选项卡 可以根据需要对单元格内的字体进行位及方向的设置 选择 字体 选项卡 可以设置正文的字体 选择 边框 选项卡 弹出下右图选项卡 可设置工作表的边界及颜色 选择 图案 可设置单元格的底纹及颜色 选择 保护 可以对单元格进行锁定及隐藏 下图是设置了 对齐 边框 图案 后的效果 工作表的数据统计 数据表的建立数据表由字段结构和记录数据组成 而字段结构就是表格的列标题 记录数据就是表格中的各行数据 建立数据表的字段结构在工作表第一行输入各字段名 输入数据表的记录数据可从字段行的下一行开始逐行输入 也可用 记录单 命令输入记录数据 执行 数据 记录单 命令 弹出确认对话框 单击 确定 工作表的数据统计 数据的编辑查找记录修改记录注意 在记录单方式下定位时 记录单中的具有公式的字段不能修改 当修改与之有关的字段时 它的值也随之发生变化 插入记录追加记录删除记录注意 删除的记录不能恢复 工作表的数据统计 数据的排序用排序按扭排序 单击要排序的字段 在常用工具栏有两个工具按钮 升序 和 降序 用菜单命令排序 执行 数据 排序 命令 打开 排序 对话框 对某区域排序 选定区域排序即可 其他数据顺序不变 恢复排序 在未经排序的数据第一列增加一个序号 1 2 3 想恢复之前的数据 只要按序号升序排序即可 工作表的数据统计 数据的筛选自动筛选自动筛选数据 执行 数据 筛选 自动筛选 命令 则数据表的每个字段名旁边出现了下拉按钮 单击与筛选条件有关的字段的下拉按钮 在出现的下拉列表中选择2001 结果如下右图 自动筛选案例介绍 用自定义条件筛选 在上左图中的下拉列表中单击 自定义 则出现 自定义自动筛选方式 对话框 单击 编号 下拉按钮 选择条件大于2001小于1001 中间的单选按钮用来规定这两个条件的关系 与 表示两个条件必须同时成立 而 或 表示两个条件之一成立即可 取消筛选 有两种方法可以取消筛选 执行 数据 筛选 全部显示 命令 执行 数据 筛选 自动筛选 命令 单击筛选字段的下拉按钮 在出现的下拉列表中单击 全部 选项 高级筛选 数据的分类汇总 分类汇总是分析数据表的常用方法 自动分类汇总 多字段分类汇总 演示见Excel表格 分类汇总 Excel文本转数值小技巧 如果我们在Excel设置成 文本型 的单元格中输入数字 那这些文本型的数字就不能作各种排序 求和等函数运算 即使简单地把单元格格式改成 数值 也无济于事 这时我们需要把将它们转换成数值格式 方法一在任意一个空白单元格中输入数值1 选中该单元格 执行复制操作 然后选中需要转换的单元格 区域 执行 编辑 选择性粘贴 命令 打开 选择性粘贴 对话框 选中其中的 乘 选项后 确定返回即可 以上方法可用于所有版本的Excel中 方法二如果你使用的是Excel2002或Excel2003 则可选中需要转换单元格 区域 单元格旁边会出现一个智能标记 按一下这个智能标记 在随后弹出的下拉列表中选中 转换为数字 选项 即可快速完成转换 Excel表格数据行列巧互换 一张Excel报表 行是项目栏 列是单位栏 现在想使整张表格反转 使行是单位栏 列为项目栏 且其中的数据也随之变动 也就是想让Excel表格数据的行列互换 该怎么做呢 可先选中需要交换的数据单元格区域 执行 复制 操作 然后选中能粘贴下数据的空白区域的左上角第一个单元格 执行 编辑 选择性粘贴 命令在打开的 选择性粘贴 对话框中选中 转置 选项后 确定 返回即可 行列快速转换 Excel的存储值和显示值 Excel2000 2002使用15位有效数字精度进行存储和计算 当公式执行计算时 Excel通常会使用所引用的单元格中的存储值而不是显示值 例如 如果两个单元格中存储值都为 10 005 并且格式都设置为货币格式 那么每一单元格中都会显示为 10 01 如果将两个单元格相加 由于Excel引用的是存储值10 005 而不是显示值10 01 因此所得的结果将是 20 01而不是 20 02 这时候 为了能够得到 20 02这个正确结果 需要进行如下设置 在 工具 菜单上 单击 选项 再单击 重新计算 选项 在 工作簿选项 标题下 选定 以显示值为准 复选框 单击 确定 按钮 注意 如果使用工作表中显示的 带格式的 数值更改工作簿的计算精度 那么当前工作簿中所有工作表上的所有常量数值都将被永久性地更改 如果在以后计算中又重新选择完全精度 则初始的基本数值将无法恢复 公式的介绍 公式是工作表中的数值进行计算的等式 公式要以 开始 如 5 2 3公式也可以包括下列所有内容或其中之一 函数 引用 运算符和常量 下面给出一个典型的公式结构 PI C4 2这个公式中 函数 PI 返回值pi为3 142 运算符 表示将数字乘幂 表示相乘 引用 A2返回A2单元格中的值 常量 直接输入公式中的数字或文本的值 如2 引用对象的语法 算术运算符 比较 文本 引用运算符 单元格引用的分类及语法 相对引用公式中的相对单元格引用 例如A1 是基于包含公式和单元格引用的单元格的相对位置 如果公式所在单元格的位置改变 引用也随之改变 如果多行或多列地复制公式 引用会自动调整 默认情况下 新公式使用相对引用 例如 如果将单元格B2中的相对引用复制到单元格B3 将自动从 A1调整到 A2 绝对引用单元格中的绝对单元格引用 例如 A 1 总是在指定位置引用单元格 如果公式所在单元格的位置改变 绝对引用保持不变 如果多行或多列地复制公式 绝对引用将不作调整 默认情况下 新公式使用相对引用 需要将它们转换为绝对引用 例如 如果将单元格B2中的绝对引用复制到单元格B3 则在两个单元格中一样 都是 A 1 单元格引用的分类及语法 混合引用混合引用具有绝对列和相对行 或是绝对行和相对列 绝对引用列采用 A1 B1等形式 绝对引用行采用A 1 B 1等形式 如果公式所在单元格的位置改变 则相对引用改变 而绝对引用不变 如果多行或多列地复制公式 相对引用自动调整 而绝对引用不作调整 例如 如果将一个混合引用从A2复制到B3 它将从 A 1调整到 B 1 在Excel输入公式时 只要正确使用F4键 就能简单地对单元格的相对引用和绝对引用进行切换 如 对于某单元格所输入的公式为 SUM B4 B8 选中整个公式 按下F4键 该公式内容变为 SUM B 4 B 8 表示对横 纵行单元格均进行绝对引用 第二次按下F4键 公式内容又变为 SUM B 4 B 8 表示对横行进行绝对引用 纵行相对引用 第三次按下F4键 公式则变为 SUM B4 B8 表示对横行进行相对引用 对纵行进行绝对引用 第四次按下F4键时 公式变回到初始状态 SUM B4 B8 即对横行纵行的单元格均进行相对引用 需要说明的一点是 F4键的切换功能只对所选中的公式段有作用 在相对引用 绝对引用和混合引用间切换 选中包含公式的单元格 在编辑栏中 选择要更改的引用 按F4在组合中切换 更改为 列反映 如果将包含引用的公式向下和向右复制两个单元格时 引用类型更新的方式 正复制的公式引用 说明 更改为 A 1 绝对列和绝对行 A 1A 1 相对列和绝对行 C 1 A1 绝对列和相对行 A3A1 相对列和相对行 C3 相对引用与绝对引用快速切换 函数的介绍 函数的结构结构 函数的结构以等号 开始 后面紧跟函数名称和左括号 然后以逗号分隔输入参数 最后是右括号 函数名称 如果要查看可用函数的列表 可单击一个单元格并按Shift F3 参数 参数可以是数字 文本 逻辑值 例如TRUE或FALSE 数组 错误值 例如 N A 或单元格引用 指定的参数都必须为有效参数值 参数也可以是常量 公式或其他函数 参数工具提示 在键入函数时 会出现一个带有语法和参数的工具提示 例如 键入 ROUND 时 工具提示就会出现 工具提示只在使用内置函数时出现 函数的介绍 常量是不用计算的值 例如 日期2008 10 9 数字210以及文本 季度收入 都是常量 表达式或由表达式得出的结果不是常量 如果在公式中使用常量而不是对单元格的引用 例如 30 70 110 则只有在自己更改公式时其结果才会更改 输入公式如果创建含有函数的公式 插入函数 对话框将有助于输入工作表函数 在公式中输入函数时 插入函数 对话框将显示函数的名称 各个参数 函数功能和参数说明 函数的当前结果和整个公式的当前结果 常用函数见Excel案例 嵌套函数的用法 有时需要将某函数作为另一函数的参数使用 例如 下面的公式使用了嵌套的AVERAGE函数并将结果与值50进行比较 IF AVERAGE E2 E5 50 SUM G2 G5 0 有效的返回值当嵌套函数作为参数使用时 它返回的数值类型必须与参数使用的数值类型相同 例 如果参数返回一个TRUE或FALSE值 那么嵌套函数也必须返回一个TRUE或FALSE值 否则 Excel将显示 VALUE 错误值 嵌套级别限制公式可包含多达七级的嵌套函数 当函数B在函数A中用作参数时 函数B则为第二级函数 例如 AVERAGE函数和SUM函数都是第二级函数 因为它们都是IF函数的参数 在AVERAGE函数中嵌套的函数则为第三级函数 以此类推 公式审核 搜索函数帮助 函数向导输入 直接输入函数 Excel字符的替换 数据合并 数据的分列 公式变为数值 锁定隐藏公式 保护工作簿 条件格式 可按条件设置不同颜色 条件格式 数据的动态变色 在 条件格式 中 作为条件的不仅是具体的数据 而且可以用公式的返回结果 我们以成绩表为例 让前10名的成绩数值以 蓝色 显示出来 让低于平均分的成绩数值以 红色 显示出来 1 选中成绩所在的单元格区域 执行 格式 条件格式 命令 打开 条件格式 对话框 2 按第1个方框右侧的下拉按钮 在随后弹出的下拉列表中 选择 公式 选项 然后在后面的方框中输入公式 C2 LARGE C 2 C 61 10 注意 由于是对整个区域一次性设置的 所以我们对C2单元格和C列作了 相对引用 而成绩都位于第2行至第61行 所以我们对这个区域作了 绝对引用 即在相应的位置前面添加了一个 符号 上述公式的含义是 当单元格中的数值大于或等于第10名成绩时 用 LARGE C 2 C 61 10 公式确定 使用该格式 3 按下 格式 按钮 打开 单元格格式 对话框 在 字体 标签中 按 颜色 右侧的下拉按钮 在随后弹出的调色板中 选择 蓝色 选项 确定返回 4 单击一下 添加 按钮 添加一个 条件格式 再仿照上述操作 设置好低于平均分的 条件格式 注意 平均分用 C2 AVERAGE C 2 C 61 公式计算 5 设置完成后 确定返回 条件格式 数据的动态变色 设置数据格式 清除格式 数据有效性 防止重复输入数据 数据有效性 控制输入内容 数据有效性 设置下拉列表 添加多个视图 视图管理 共享工作薄 解决共享工作簿中的修订冲突 当两个用户试图保存影响同一单元格的修订时 Excel为其中一个用户显示 解决冲突 对话框 在 解决冲突 对话框中 可看到有关每一次修订以及其他用户所造成的修订冲突的信息 若要保留自己的修订或其他人的修订并转到下一个修订冲突上 请单击 接受本用户 或 接受其他用户 若要保留自己的所有剩余修订或所有其他用户的修订 请单击 全部接受本用户 或 全部接受其他用户 若要使自己的修订覆盖所有其他用户的修订 而且不再看到 解决冲突 对话框 请关闭此功能