销售分析中_EXCEL数据统计技巧.ppt

JIT童娟 销售分析中EXCEL数据统计技巧 一 常用函数二 常用小技巧三 常用图表 一 常用函数 1 SUM 求和 AVERAGE 平均值 MAX 最大 MIN 最小 COUNT 计数 使用格式 函数 数据范围 如 SUM B2 B4 SUM B2 B3 B4 注意 数据范围里用冒号 表示连续区域 用逗号 表示不连续单元格 不要用加号例 学生成绩表 2 COUNTIF 条件计数 用来计算区域中满足给定条件的单元格的个数 使用格式 COUNTIF 数据范围 条件 注意 条件的引号一定为英文状态下 这里的条件可以是数值 文本或表达式如 COUNTIF B2 B10 60 例 学生成绩表 3 SUMIF 条件求和 根据指定条件对若干单元格求和 使用格式 SUMIF 条件区域 条件 运算范围 如 SUMIF B2 B6 男 C2 C6 SUMIF B 2 B 6 男 C2 C6 注意 复制公式时将区域锁定 使之不随公式位置的变化而变化例 纵向运用 将某张表中的数据求和导入另一张表横向运用 库存汇总表中求合计 4 IF 条件函数 根据条件判断 显示不同的结果 使用格式 IF 条件表达式 条件成立 条件不成立 如 IF C2 60 及格 不及格 例 学生成绩表 利用IF函数避免出错信息 1 当我们在C1单元格输入公式 A1 B1 当B1单元格没有输入数据时 C1单元格则会出现 DIV 0 的错误信息 如果将公式改为 IF B1 0 A1 B1 就不会显示错误信息了 2 同样 如果我们在C1单元格中输入 A1 B1 当A1 B1没有输入数值时 C1则显示出 0 如果将公式改为 IF AND A1 B1 A1 B1 这样 如果A1与B1单元格均没有输入数值时 C1也不会显示 0 了 例 库存汇总中求客户库存率 5 VLOOKUP 在表格数组的首列查找指定的值 并由此返回表格数组当前行中其他列的值 使用格式 VLOOKUP 待定位的值 数据区域 返回值所在的列 是否精确匹配 如 VLOOKUP A2 书名 A B 2 0 注意 如果为TRUE或省略 则返回精确匹配值或近似匹配值 也就是说 如果找不到精确匹配值 则返回小于 待定位值 的最大数值 如果为FALSE VLOOKUP将只寻找精确匹配值 例 代入书名 6 LEFT RIGHT 得到字符串左 右部指定个数的字符使用格式 LEFT 字符串 提取字符数 RIGHT 字符串 提取字符数 例 返回经销商省份 7 EXACT 检查两个文本值是否完全相同 如果它们完全相同 则返回TRUE 否则 返回FALSE 函数EXACT能区分大小写 但忽略格式上的差异 使用格式 EXACT 字符串1 字符串2 例 代码检查 8 RANK 排名 返回一个数值在一组数值中的排位使用格式 RANK 单元格地址 数据范围 升 降序 注意 0表示按降序排列 1表示按升序排列 降序排列时0可省略如 RANK F2 F 2 F 4 例 行政及现场管理PK榜 9 ROUND 返回按指定位数进行四舍五入的数值 使用格式 ROUND 数值 指定位数 注意 如果指定位数大于0 则四舍五入到指定的小数位 如果指定位数等于0 则四舍五入到最接近的整数 如果指定位数小于0 则在小数点左侧进行四舍五入 例 配货调整自然包 二 常用小技巧 1 选择 1 多个不连续选择 CTRL 单击 2 多个连续的选择 SHIFT 单击快速完成大范围选定的方法还可以在名称框中输入左上角地址 右下角地址 如A1 E50000 3 选定整个工作表 单击行号与列标的交界处 同Ctrl A全选 2 填充 1 等值填充 输入 选定 点击 填充柄 拖动 选定一个区域 输入填充内容 Ctrl Enter 2 等差填充 差值为1的等差序列 输入第1个值 按CTRL 拖动填充柄 其它差值的等差序列 输入前两个值并选定 拖动填充柄注意 数值与日期是相反的 直接拖动时 数值为等值填充 日期为等差填充 按CTRL 拖动时 数值为等差填充 日期为等值填充 3 移动 复制 1 单元格的操作 移动 按住左键拖动 复制 CTRL 左键拖动 2 不同工作簿中工作表的操作 移动 右击工作表标签 移动和复制工作表 设置移动到的工作薄 工作表 确定 复制 右击工作表标签 移动和复制工作表 设置移动到的工作薄 工作表 选中 建立副本 确定 4 行 列调整 1 插入多行 多列 选中要插入的行 列 数 插入 行 列 确定 2 最适合的行高 列宽 光标置于行号 列标 分界线处双击 选定多个行号 列标 可同时设置 5 状态栏的利用右击状态栏 选择求和 计数 最大值 最小值等其中一项 然后选择需要处理的数据区域 即获得与使用对应函数相同的结果 此操作便捷 高效 6 F4的妙用 1 工作表的添加选中连续的多个工作表后执行插入操作 可同时插入多个工作表 或按F4重复操作 2 公式中引用方式的切换在三种引用 相对引用 绝对引用 混合引用 间切换的方法 光标定位于单元格地址上 按F4键 如C5 C 5 C 5 C5之间的切换 7 格式刷的妙用格式刷在Excel中的作用是 将一个单元格或单元格区域的格式 如字体 字号 对齐方式 边框 底纹等 复制到另一个单元格或单元格区域中使用方法 首先选中含有格式的单元格或区域 单击或双击格式刷工具 然后刷取要应用格式的文字注意 单击只能进行一次格式复制 而双击可以复制任意次 可单击格式刷按钮本身停止或按ESC键 8 其他 1 在一个单元格中输入多行内容 换行可借助 ALT ENTER 组合键实现 2 输入当前日期 CTRL 注意 此日期是系统设置日期 3 工作组的操作 选中多个工作表标签 可以执行一些统一操作 比如复制 粘贴 填充内容 页面设置及统一打印等等 4 查看工作表中的链接 通过查看菜单栏中编辑 链接 可以检查工作表中是否有到其他表的链接 如果有 则断开链接或将有链接的数据选择性粘贴为数值 三 常用图表 图表是数值的可视化表示 1 柱形图排列在工作表的列或行中的数据可以绘制到柱形图中 柱形图用于显示一段时间内的数据变化或显示各项之间的比较情况 例 步骤 1 选中需要的数据区域 如下 2 执行插入 图表 或者点击图标向导按钮 3 在4步骤之1 图表类型中 选择 柱形图 子图表类型选 簇状柱形图 4 4步骤之2 图表原数据 默认 5 4步骤之3 图表选项中 选 标题 选项卡 设置图表名称 可在 图例 中选择图例放置位置 6 4步骤之4 图表位置 选择其中一项 点击完成 图表美化 1 上述操作完成后 出现右图 2 依次双击文字或数值 在弹出的对话框中设置字体 字号 对齐方式等 3 依次双击不同颜色的柱形图 在弹出的数据系列格式对话框中 选择 图案 选项卡下的 填充效果 设置颜色 单色 双色 4 双击背景区 网格线 改变背景颜色 网格线样式 终表如图 2 饼状图仅