Excel 技能培训-初级篇.ppt

Excel技能培训 初级篇 MicrosoftExcel 大纲 我们要学什么 我们要学什么 1985年MicrosoftExcel问世 被公认为是功能最完整 技术最先进和使用最简便的电子表格软件 Excel可以创建和修改工作表 图表 以及分析管理数据 分析 Excel基础知识 Excel界面 Excel基本介绍 一个EXCEL文件是由 文件 工作簿 工作表 行 列 X Y 单元格 构成的 工作簿 就是处理和存储数据的文件 一个工作簿可以创建256个工作表 一个工作表包括65536行 256列 一个工作表包括65536 256个单元格EXCEL是一个容量非常大的数据表格 注 EXCEL2007在数据容量上超过了EXCEL2003EXCEL2007一个工作表包括1048576行 16384列 EXCEL2007一个工作表包括1048576 16384个单元格 Excel常用快捷键 单元格复制 Ctrl C 单元格粘贴 Ctrl V 单元格剪切 Ctrl X 撤销最后一次操作 Ctrl Z 复制上一个单元格 Ctrl 向下填充 Ctrl D 向右填充 Ctrl R 获得帮助信息 F1 编辑当前单元格 F2 重复上一步操作 F4 随机数 randbetween Excel常用技巧 Excel常用技巧 EXCEL数据输入技巧 1 数据的定义 数据是数值 文字 日期 时间 公式和符号的总称 数据的分类 数据分为字符数据和数值数据 数据输入常用方法 1 非数值数字的输入 数字前加单引号或设置单元格属性为 文本 如 身份证号码输入2 分数的输入 0 分子 分母 如 直接输1 3 EXCEL默认为1月3日 应该写成01 33 当前日期的输入 Ctrl 分号 4 当前时间的输入 Ctrl 冒号 5 批量录入相同数据 Ctrl Enter 先选择需要批量输入相同数据的区域 在输入数据 使用快捷点Ctrl Enter6 在单元格中换行 Alt Enter 注意 均为英文状态下的符号 EXCEL数据输入技巧 2 7 快速输入大写中文数字将光标移至需要输入大写数字的单元格中 在单元格中输入相应的小写数字 右击该单元格 点击 设置单元格格式 从弹出的 单元格格式 对话框中选择 数字 选项 然后从 特殊 列表框中选择 中文大写数字 选项 最后单击 确定 按钮即可 8 快速隐藏在打印工作表时 我们有时需要把某些行或者列隐藏起来 可是用菜单命令或调整行号 列标 分界线的方法比较麻烦 有一个简单方法 在英文状态下 按 Ctrl 9 或 Ctrl 0 组合键 就可以快速隐藏光标所在的行或列 9 快速选定单元格在Excel中 如果要选定已选定单元格周围的单元格 可以按上下方向键进行移动 可为了节省时间 让操作的速度更高 快速移动到活动单元格 在拖动鼠标的同时 按住 Ctrl键 选择不连续区域在拖动鼠标的同时 按住 Shift键 可以选中两点之间区域 Ctrl Shift 8 或 Ctrl 可以选中当前单元个周围连续的矩形区域 Ctrl Home 将光标移动到第一个单元格 Ctrl End 将光标移动到表格最后一个单元格 Ctrl 向右方向键 可将光标移动到当前区域最右列 其他方向箭头依此类推 Ctrl Shift 向下箭头 选择当前单元格至当前区域最后一行的内容 查找功能常用技巧 1 模糊查找按 CTRL F 进入查找 在查找里直接输查找内容为模糊查找 2 精确查找在查找里点开选项 将 单元格匹配 的勾打上为精确查找3 值查找在查找里点开选项 查找范围 选择值为 值 查找 如果单元格里是公式那必须用值查找 自动填充 1 自动填充 就是根据已输入的数据 按照系统规定的规则或用户指定的规律 自动将数据填充到选定的区域 如果单元格中是文本 数据填充柄起复制的作用 如果单元格中是日期填充柄起到填充序列的作用 自动填充 2 自动填充 3 要填充比较复杂的数据时 可以试试右键来拖动填充柄 选中单元格 编辑 填充 序列 数据分列的妙用1 用Excel输入数据时 如果在一行内输入内容过长 可以采取快速分列方法 具体操作如下 在选中单元格中输入数据或文字 然后选定所要分割的数据区域 单击菜单栏 数据 分列 在弹出的对话框中选择 固定宽度 点击 下一步 在 数据预览 中 按住分列线 移动至指定位置 点击 下一步 选择 列数据格式 点击 完成 数据分列的妙用2 批量转换日期格式以前在Excel中输入职工出生时间时 为了简单都输入成 yymmdd 形式 但上级部门一律要求输入成 yyyy mm dd 格式 那么一千多名职工出生时间肯定不能每个手工转化 最快速的方法是 先选定要转化的区域 点击 数据 分列 出现 文本分列向导 对话框 勾选 固定宽度 连续两次点击 下一步 按钮 在步骤三对话框的 列数据格式 中 选择 日期 并选定 YMD 形式 按下 完成 按钮 以前的文本即转化成了需要的日期了 选择性粘贴1 选择要行列互换的数据区域 单击 复制 按钮或按下 Ctrl C 键复制所选区域 选择行列互换后数据所在区域的第一个单元格 区域左上角 单击 编辑 菜单上的 选择性粘贴 在对话框中选择 转置 复选框 单击 确定 选择性粘贴2 对单元格进行同增同减操作在Excel中 如果要对某一单元格或某一区域中的每个单元格中的数值进行同加 同减 同乘或同除操作时 可以使用 选择性粘贴 功能轻松实现 例如 让选定区域中的每个单元格都同乘以2 首先在选定区域外的某个单元格中输入2 选择这个单元格并复制 然后选择要进行同乘操作的单元格区域 单击 编辑 菜单 选择 选择性粘贴 命令 在 运算 栏中选择 乘 单选项 单击 确定 按钮 结果显示在了每个单元格中 使用这个功能还可以对单元格进行自身操作 例如可以进行单元格的自身相加 相减 相乘以及相除 定位 在Excel中 经常要选定空白单元格 逐个选定比较麻烦 如果使用下面的方法就方便多了 打开 编辑 定位 在 定位 窗口中 按下 定位条件 按钮 选择 定位条件 中的 空值 再按 确定 空白单元格即被全部选定 如果要选定只含数据的单元格 在上面方法的 定位条件 窗口中 选择 常量 再点 确定 含有数据的单元格全部选定 快速选定 空白 和 数据 单元格 单元格的引用技巧 相对引用单元格地址会随公式所在单元格的位置变更而改变 如 A1 B1 绝对引用单元格地址将不随公式所在单元格的位置变化而改变 如 A 1 B 1 混合引用只给行号或列号加 符号 如 A 1 B2 复制后部分地址改变 显示标题技巧1 在打印时让每页都显示标题在Excel中制作一个长表格时 默认打印时 并不是每页都会显示出顶端标题行和左侧标题列 它只会在第一页上显示 那如何能让表格打印出来时 每页都带有标题栏呢 可以这样设置 打开 文件 菜单 选择 页面设置 选项 弹出 页面设置 对话框 单击 工作表 选项卡 在 打印标题 栏中的 顶端标题行 项和 左侧标题列 项中设置好要显示的标题单元格 单击 确定 按钮 这样在打印长表格时 每页都会显示出标题的内容了 显示标题技巧2 编辑时让每页都显示标题在编辑状态时向后翻页会看不到第一行的标题行 在操作数据时就会很不方便 这个问题很简单就可以解决 方法如下 选中标题所在行的下面一行单元格 打开 窗口 菜单 选择 冻结窗格 选项 上面一行就被固定了 无论怎么向下翻页 它都始终在第一行不会移动 使用Excel公式与函数 公式和函数 什么是函数Excel函数即是预先定义 执行计算 分析等处理数据任务的特殊公式 以常用的求和函数SUM为例 它的语法是SUM number1 number2 其中 SUM 称为函数名称 一个函数只有唯一的一个名称 它决定了函数的功能和用途 函数名称后紧跟左括号 接着是用逗号分隔的称为参数的内容 最后用一个右括号表示函数结束 什么是公式函数与公式既有区别又互相联系 如果说前者是Excel预先定义好的特殊公式 后者就是由用户自行设计对工作表进行计算和处理的公式 以公式 SUM E1 H1 A1 26 为例 它要以等号 开始 其内部可以包括函数 引用 运算符和常量 上式中的 SUM E1 H1 是函数 A1 则是对单元格A1的引用 使用其中存储的数据 26 则是常量 和 则是算术运算符 常用函数 SUM SUM用途 返回某一单元格区域中所有数字之和 语法 SUM number1 number2 参数 Number1 number2 为1到N个需要求和的数值 用法举例 常用函数 SUMIF SUMIF用途 对满足单个条件的单元格求和 语法 SUMIF range criteria sum range 参数 Range为用于条件判断的单元格区域Criteria是由数字 逻辑表达式等组成的判定条件Sum range为需要求和的单元格 区域或引用用法举例 常用函数 DSUM DSUM用途 求满足给定多个条件的数据库中记录的字段 列 数据的和 语法 DSUM database field criteria 参数 Database表示构成列表或数据库的单元格区域 是引用的数据 Field指定函数所使用的数据列 列表中的数据列必须在第一行具有标志项 可以用双引号括住的列标签 或是表示该列在列表中位置的数值 是数值型数据 Criteria为一组包含给定条件的单元格区域 区域包括列标签及列标签下满足某个条件的单元格 用法举例 常用函数 MAX和MIN MAX用途 返回数据集中的最大数值 语法 MAX number1 number2 参数 Number1 number2 是需要找出最大数值的1至N个数值 MIN用途 返回数据集中的最大数值 语法 MIN number1 number2 参数 Number1 number2 是需要找出最小数值的1至N个数值 用法举例 常用函数 COUNT和COUNTIF COUNT用途 返回包含数字以及包含参数列表中的数字的单元格的个数 语法 COUNT value1 value2 参数 Value1 value2 为包含或引用各种类型数据的参数 1到N个 但只有数字类型的数据才被计算 COUNTIF用途 统计某一区域中符合条件的单元格数目 语法 COUNTIF range criteria 参数 range为需要统计的符合条件的单元格数目的区域 Criteria为参与计算的单元格条件 其形式可以为数字 表达式或文本 其中数字可以直接写入 表达式和文本必须加引号 用法举例 常用函数 ROUND ROUNDUP ROUNDDOWN ROUND用途 返回某个数字按指定位数取整后的数字 语法 ROUND number num digits 参数 Number为需要进行四舍五入的数字 Num digits为指定的位数 按此位数进行四舍五入 ROUNDUP 不做四舍五入的向上取整如算通话分钟数ROUNDDOWN 不做四舍五入的向下取整用法举例 ROUND 2 1547 3 2 155ROUNDUP 2 1547 3 2 155ROUNDDOWN 2 1547 3 2 154 常用函数 LEFT和RIGHT LEFT用途 基于所指定的字符数返回文本字符串中的左边第一个或前几个字符语法 LEFT text num chars 参数 Text是包含要提取字符的文本字符串 Num chars指定要由LEFT所提取的字符数 RIGHT 为取右用法举例 LEFT ad246g 3 ad2RIGHT ad246g 3 46g 常用函数 MID MID由用户自行指定开始的位置和字符的长度 由用户自行指定开始的位置和字符的长度语法 MID text start num num chars 参数 Text是包含要提取字符的文本字符串 可以直接输入含有目标文字的单元格名称 Start num是文本中要提取的第一个字符的位置 文本中第一个字符的start num为1 以此类推 Num chars指定希望MID从文本中返回字符的个数 用法举例 假设A1身份证号码是210304198812316014 提取生日的公式是 MID A1 7 4 年 MID A1 11 2 月 MID A1 13 2 日 返回的是 1998年12月31日 常用函数 RANK RANK用途 根据指定条件对若干单元格 区域或引用求和 语法 RANK number ref order 参数 Number 为需要找到排位的数字 Ref 为数字列表数组或对数字列表的引用 Order 为一数字 指明排位的方式 如果order为0 零 或省略 MicrosoftExcel对数字的排位是基于ref为按照降序排列的列表 如果order不为零 MicrosoftExcel对数字的排位是基于ref为按照升序排列的列表 用法举例 常用函数 IF IF用途 根据指定条件对若干单元格 区域或引用求和 语法 IF logical test value if true value if false 参数 Logical test 表示计算结果为TRUE或FALSE的任意值或表达式 例如 A10 100就是一个逻辑表达式 如果单元格A10中的值等于100 表达式即为TRUE 否则为FALSE Value if true logical test为TRUE时返回的值 Value if false logical test为FALSE时返回的值 用法举例 常用函数 VLOOKUP VLOOKUP用途 在表格或数值数组的首列查找指定的数值 并由此返回表格或数组中该数值所在行中指定列处的数值 语法 VLOOKUP 查找值 区域 列序号 逻辑值 参数 查找值 为需要在数组第一列中查找的数值 它可以是数值 引用或文字符串 区域 数组所在的区域 如 B2 E10 也可以使用对区域或区域名称的引用 例如数据库或数据清单 列序号 即希望区域 数组 中待返回的匹配值的列序号 为1时 返回第一列中的数值 为2时 返回第二列中的数值 以此类推 逻辑值 为TRUE 1或FALSE 0 如果为TRUE或省略 则返回近似匹配值 也就是说 如果找不到精确匹配值 则返回小于 查找值 的最大数值 如果 逻辑值 为FALSE 函数VLOOKUP将返回精确匹配值 如果找不到 则返回错误值用法举例 Excel数据排序 筛选与分类汇总 Excel数据排序 筛选与分类汇总 排序是根据数据清单中某个字段的值排列各行记录的顺序 如果要求对一列数据对数据清单排序 在常用工具栏中单击 升序 降序 按钮即可 如果要示对多列的数据对数据清单排序 操作步骤如下 1 选中 数据 菜单中的 排序 菜单项 弹出 排序 对话框 数据排序 2 在弹出 排序 对话框中 根据数据清单中有无 标题行 在 当前数据清单 设置区中选择 有标题行 或 无标题行 选项3 在 主要关键字 次要关键字 第三关键字 中选择相关要求4 单击 确定 此时表中的数据按照要求进行排序 数据排序 数据筛选是将不满足条件的数据暂时隐藏起来 只显示符合条件的数据 Excel提供 自动筛选 和 高级筛选 前者进行简单件的筛选 后者进行复杂的条件筛选 使用自动筛选的方法 1 选中数据清单的标题行 单击 数据 菜单中的 筛选 菜单项的 自动筛选 在选中数据的每个字段名的右侧都会出现下拉箭头 数据筛选 数据筛选 2 如果要显示分数前几名考生 可单击一拉箭头 点击 前10个 系统会弹出 选前10个 对话框 用户可根据需求进行设置 最后按 确定 3 如果要进行某个不等式条件或某个区间进行筛选 右可单击一拉箭头 点击 自定义 系统会弹出 自定义 对话框 用户可根据需求进行设置 最后按 确定 数据筛选 如果筛选条件很多 可以考虑使用 高级筛选 使用 高级筛选 步骤如下 1 在与数据清单不相连的区域设置条件 将有 与 关系的条件放在同一行 有 或 关系的条件放在不同的行 注 与 表示两个条件同时成立 或 表示两个条件中有一个成立 2 单击数据清单中的任一单元格 单击 数据 菜单中的 筛选 菜单项的 高级筛选 命令 弹出 高级筛选 对话框 数据的高级筛选 3 在 高级筛选 对话框中选择数据区域和条件区域 4 可以选择两种放置筛选结果的方式 一种是 在原有区域显示筛选结果 另一种是 将当前筛选结果复制到其他位置 单击 确定 数据的高级筛选 分类汇总之前要先对数据进行排序选中数据 单击 数据 菜单中的 分类汇总 菜单项 出现 分类汇总 对话框在 分类汇总 对话框中设置 分类字段 汇总方式 汇总项 各项设置后单击 确定 按钮 完成分类汇总 分类汇总 Excel数据条件格式 条件格式的使用技巧 什么是条件格式对选定区域中符合指定条件的数据以指定格式显示如何设置条件格式格式 条件格式使用条件格式显示单元格的数据例 将表格中小于60的数字以红色显示目的 突出显示满足某个条件数据 Excel数据透视表 Excel数据透视表 利用数据透视表 可以为不必再使用复杂的计算公式和函数对数据进行分类汇总 也可以抛开诸如自动筛选或高级筛选这样的操作 学习数据透视表的制作方法 是一件值得花大力气去做的事 下面以营业厅交易明细表为例 详细介绍制作数据透视表的方法和步骤 数据透视表 制作数据透视表的方法和步骤 在数据中的任一非空白处单击 然后单击 数据 数据透视表和数据透视图 命令 打开 数据透视表和数据透视图向导 3步骤之1 对话框 单击 下一步 按钮 打开 数据透视表和数据透视图向导 3步骤之2 对话框 数据透视表的制作方法和步骤 在 数据透视表和数据透视图向导 3步骤之2 对话框中 在选定区域中选定需要进行分析的数据 确定数据源后 单击 下一步 按钮 打开 数据透视表和数据透视图向导 3步骤之3 对话框 数据透视表的制作方法和步骤 在 数据透视表和数据透视图向导 3步骤之3 对话框 单击 布局 按钮 打开 数据透视表和数据透视图向导 布局 对话框 数据透视表的制作方法和步骤 在 数据透视表和数据透视图向导 布局 对话框中 根据需要 将右边的字段拖到左边的 行 R 区域 列 C 区域和 数据 D 区域中 设置发好布局后 单击 确定 按钮 系统就返回 数据透视表和数据透视图向导 3步骤之3 对话框 单击 确定 按钮 数据透视表就初步完成了 数据透视表的制作方法和步骤 另一种布局 另一种效果 数据透视表的制作方法和步骤 此时数据透视表的格式非常难看 我们可以需要进行格式设置 单击 格式 中的 自动套用格式 对话框 或单击 数据透视表 工具样上的 设置报告格式 按钮选择一个满意的格式 单击 确定 按钮 关闭 自动套用格式 对话框 数据透视表的制作方法和步骤 此时仔细观察数据透视表发现在每个班组后边都有一个汇总行和一个空行 我们不需要这个汇总和空行 因此需要进行调整 双击 班组 打开 数据透视表字段 对话框 在 分类汇总 项目中选择 无 选项按钮 单击 布局 按钮 打开 数据透视表字段字局 对话框 取消 每项后面插入空行 复选框 最后单击 确定 按钮 数据透视表的制作方法和步骤 Q A