EXCEL技能提升ppt课件.pptx

EXCEL技能提升 遇到这个问题应该怎么办 要把每个人的订单汇总起来 应该怎么办 遇到这个问题应该怎么办 又要按计算器加班到天亮了 加班青年 遇到这个问题应该怎么办 简单 选中汇总表 然后 一个一个表加过去呗 张三 D2 李四 D2 王五 D2 赵六 D2 陈七 D2 文艺青年 遇到这个问题应该怎么办 走你青年 选中汇总表中的D2单元格 输入 SUM 张三 陈七 D2 下拉 右拉填充公式 搞定 Excel软件提升了数据处理的效率 Excel软件提升了数据处理的效率 操作效率 数图呈现 数据分析 表格规范 掌握EXCEL常用函数及数据处理技能 提高工作效率 培训目标 目录 1 函数基础 函数和公式函数的参数函数输入方法常用函数 1 1函数和公式 什么是函数 Excel函数即使预先定义 执行计算 分析等处理数据任务的特殊公式 如 sum number1 number2 其中 sum 称为函数名称 一个函数只有唯一一个名称 它决定了函数的功能和用途 函数名称后面一定是括号 里面的参数必须用逗号分割 Excel函数可以分为内置函数和扩展函数两大类 内置函数只要启动了Excel 用户就可以使用它们 扩展函数必须通过启用 宏 命令加载 然后才能像内置函数那样使用 1 1函数和公式 什么是公式 函数与公式既有区别又互相联系 如果说函数时Excel预先定义好的特殊公式 公式就是由用户自行设计对工作表进行计算和处理的公式 例 sum E1 H1 A1 26 算术运算符 加减乘除等比较运算符 大于 小于 等于 不等于等文本运算符 比如连接符号 连接多个文本引用运算符 如SUM A1 A30 中的冒号等 开始 函数 对单元格A1的引用 算术运算符 常量 1 2函数的参数 函数右边括号中的部分称为参数 假如一个函数可以使用多个参数 那么参数与参数之间使用半角逗号进行分隔 如 sum number1 number2 1 常量 直接输入到单元格或公式中的数字或文本 SUM E1 H1 262 逻辑值 TRUE 1 或FALSE 0 VLOOKUP A1 A 1 F 7 2 0 3 数组 例 SUM A1 A5 B1 B5 4 错误值 N A 乱码 5 单元格引用 例如E1 H1 6 嵌套函数 一个函数是另一个函数参数 SUM SUMIF DBOM F F A12 DBOM H H 7 名称和标志 单元格或区域直接命名 如将 A F命名为1 1 3函数输入方法 1 插入函数 对话框 2 编辑栏 单元格直接输入 1 4常用函数 文本函数 mid left right查找和定位函数 vlookup match计数与求和函数 count sum max average逻辑函数 if countif sumif sumifs 1 4 1文本函数 MID MID text start num num chars 从文本字符串中指定的起始位置起返回指定长度的字符 1 4 1文本函数 LEFT LEFT text num chars 从一个文本字符串的第一个字符开始返回指定个数的字符 1 4 1文本函数 RIGHT RIGHT text num chars 从一个文本字符串的最后一个字符开始返回指定个数的字符 1 4 2查找函数 vlookup Vlookup lookup value table array clo index num range lookup 搜索表区域首列满足条件的元素 确定待检索单元格在区域中的行序号 再进一步返回选定单元格的值 默认情况下 表是以升序排序的 例 根据考试成绩查找成绩评价 VLOOKUP B2 F 2 G 5 2 TRUE 1 4 3定位函数 match Match lookup value lookup array match type 返回符合特定值特定顺序的项在数组中的相对位置 MATCH 22 H2 H6 0 1 4 4计数函数 count Count value1 value1 计算区域中包含数字的单元格的个数 例 计算D2 E6区域中包含数字的单元格的个数 count D2 E6 1 4 5求和函数 sum SUM number1 number2 计算单元格区域中所有数值的和 例 计算D2 E6区域中所有数值的和 SUM D2 E6 1 4 6求最大值函数 max Max number1 number2 返回一组数值中的最大值 忽略逻辑值及文本 例 找出D2 E6区域中最大的数值 MAX D2 E6 1 4 7求最小值函数 min Min number1 number2 返回一组数值中的最小值 忽略逻辑值及文本 例 找出D2 E6区域中最小的数值 MAX D2 E6 1 4 8求平均值函数 average Average number1 number2 返回其参数的算术平均值 参数可以是数值或包含数值的名称 数组或引用 例 计算D2 E6区域数值的算术平均数 Average D2 E6 1 4 9逻辑函数 if IF logical test value if true value if false 判断是否满足某个条件 如果满足返回一个值 如果不满足则返回另一个值 例 判断同一行中D列数字是否大于E列数字 IF D2 E2 OK NG 1 4 10逻辑函数 countif countif range criteria 计算某个区域中满足给定条件的单元格数目 例 计算D2 E6区域中出现数字 5 的数目 COUNTIF D2 E6 5 1 4 11逻辑函数 sumif sumif range criteria sum range 对满足条件的单元格求和 例 计算销售额不小于50万的区域总净利润 SUMIF B2 B15 50 1 4 11逻辑函数 sumifs sumifs sum range criteria range1 criteria1 riteria range2 criteria2 对一组给定条件指定的单元格求和 例 计算A B产品销售额都不小于50万的区域总净利润 SUMIFS D2 D15 B2 B15 50 C2 C15 50 目录 排序筛选分类汇总分列数据有效性条件格式数据透视表 2 数据处理 2 1排序 排序是为了快速的查找自己想要的数据 并按照自己想要的顺序排列 使用排序 常用的几种排序 升序 降序 多条件排序 笔画排序 按行排序 2 1排序 升序 降序 选中 数据 单击 排序和筛选 选择 升序 降序 或 自定义排序 单击 自定义排序 弹出如图对话框 2 1排序 多条件排序 选中 数据 单击 自定义排序 单击 添加条件 增加次要关键字 2 1排序 笔画排序 按行排序 选中 数据 单击 自定义排序 单击 选项 选择 笔划排序 按行排序 2 2筛选 筛选是为了快速的挑出自己想要的数据 并按照自己想要的顺序排列 使用筛选 常用的几种筛选 简单筛选 数字筛选 多条件筛选 自定义筛选 2 2筛选 简单筛选 选中 数据 单击 排序和筛选 选择 筛选 点击筛选下拉标志 筛选想要的数据 2 2筛选 数字筛选 点击筛选下拉标志 选择 数字筛选 2 2筛选 多条件筛选 添加辅助区域 并根据需求输入公式 形成辅助区域 2 2筛选 多条件筛选 选中任一单元格 在工具栏中找到 数据 中 排序和筛选 一栏 选择 高级 出现如图所示对话框 在 列表区域 选中全部需要筛选的数据 2 2筛选 多条件筛选 在 条件区域 中选中制作完成的辅助条件区域 完成筛选 2 2筛选 自定义筛选 点击筛选下拉标志 选择 自定义筛选 在自定义自动筛选方式界面设置筛选要求 设置后点击确定即可筛选出符合要求的数据 2 3分类汇总 在使用excel时常常会输入一些具有类似特性的数据 当数据的量特别大的时候 就无法在同一个屏幕中看到所有的数据类型 这时就可以将其按照各自的特性进行分组 对大量数据进行快速分类汇总 然后在需要的时候点开某一个组的数据进行编辑 常用的分类汇总 创建组与取消分组 自动分类汇总 2 3分类汇总 创建组与取消分组 通过分组的方式 把所有输入的数据按照其特性 市场部 行政部 研发部 进行分类 首先选中 市场部 栏目下的所有数据的行 注意是选择整行 然后从 数据 选项卡中找到 创建组 按钮 单击 创建组 在所选数据行的左侧会出现一个黑色的竖线 竖线底端会出现一个减号 单击黑色竖线或者减号即可将这个分组的分数折叠起来 2 3分类汇总 创建组与取消分组 依次对 行政部 和 研发部 的数据创建分组 创建好之后 可以将所有分组折叠起来 这样显示的是三个标题 市场部 行政部 和 研发部 当需要查看某一个分组中的数据时 只要单击相应的加号即可 通过创建分组 使数据表中的数据非常简洁直观 取消分组 将相应的数据选中 然后在 数据 选项卡中单击 取消分组 即可 2 3分类汇总 自动分类汇总 选择 数据 选项卡中 分类汇总 就会出现一个对话框 按需求来选择想要汇总的内容 选择好了直接按确定系统会自动把数据给汇总好 2 4分列 编辑大量有规律的单列数据 使数据规范化 按照自己的要求进行多列表示和显示 2 4分列 选中数据 选择 数据 选项卡下的 分列 按照文本分列向导操作 选择数据分列格式 完成分列 2 4分列 指定长度分列 选中目标区域 选择 数据 选项卡下的 分列 调出文本分列向导 点击选中 固定宽度 w 后 点击下一步 2 4分列 指定长度分列 鼠标左键点击插入两条分隔线 身份证号码的7 14位位置 点击下一步 1 忽略不在返回值中显示的字符段 在数据预览中选中要忽略的列 点不导入此列 跳过 I 2 对中间出生日期设定为日期格式3 目标区域选定 B 6 分列返回值在B2单元格位置列示 4 点击完成 返回结果 2 5数据有效性 制作表格时 避免输入错误数据 规范输入的数据 并提醒填表人如何填写 2 5数据有效性 选择需要设置的区域或整行或整列 单击 数据 选项卡下的 数据有效性 按钮 在弹出的 数据有效性 对话框中的 设置 选项卡下 选择 允许 下面的 序列 根据需要设置是否 忽略空值 在下面的 来源 文本框中输入待选的列表项 各数值间以半角逗号分隔 不要加引号 然后点击 确定 2 5数据有效性 返回工作表 刚才选择的区域中已经有下拉箭头了 点击这个箭头可弹出下拉列表 2 6条件格式 将一定要求的数据按照自己要求的格式显示 便于识别和提示 选中区域 单击 开始 选项卡标签 点击 样式 功能区的 条件格式 选择或新建单元格数据规则 2 7数据透视表 选中数据区域 点击 插入 选项卡中 数据透视表 如选中了数据区域 在 创建数据透视表 中最上面的选择区域框可以看到选中的单元格区域 如果没有选中 可以在此选取 数据透视表可以选载在新工作表创建和在当前工作表创建 如数据透视表较大 内容很多 建议在新工作表中生成透视表 完成设置后 点击 确定 2 7数据透视表 excel中出现一个新工作表 左边是空白的透视表区域 右边是数据透视表字段列表 可以拖动和设置 把 姓名 字段拖到行标签位置 剩余经费 拖动到 数值 位置 左边的数据透视表则按照不同的姓名将剩余经费进行了汇总 行标签处可以插入多个 但是前后顺序要根据自己的需求来选择 透视表数据显示区域 2 7数据透视表 数据透视表数据可以生成数据透视图 选中透视表上任意一个单元格 选择 选项 选项卡中 数据透视图 弹出 插入图表 选择一个合适的图表类型 点击 确定 按钮 就可以自动生成数据透视图 目录 3 1创建图表 1 方法一 一键生成法选择制作图表的数据 按下 2 方法二 利用图表向导 推荐 单击 插入 选项卡 在 图表 组可以看到各类图表 Step1 修改图表选项