EXCEL数据管理技能汇总
合集下载
相关主题
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
优质EXCEL ！ EXCEL数据管理技能汇总
微软资深讲师/OFFICE（中国）实战专家 周 刚
优化视觉 专业报表
条件格式：敏感数据报警
数据报表：数据收集型
数据报表：数据展示型
Excel 条件格式，以图标集、色条等形式展示数 据，快速发现数据背后的问题
EXCEL报表常用技能&技巧
合并单元格/解除合并单元格 隐藏行列/隐藏数据显示 冻结单元格/交叉冻结单元格
圆环图
Sales Breakdown, by State
44%
1st Half 2nd Half
51%
28% 26%
21% 30%
California Oregon Washington
Sales Breakdown, by State
100% 90% 80% 70% 60% 50% 40% 30% 20% 10% 0% 1st Half 2nd Half
Web Site Visitors
30,000 25,000 20,000 15,000 10,000 5,000 0 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 0 5,000 10,000 15,000 20,000 25,000 30,000
21.42 9.6 24.36 25.26 105.6 18.9 3.36 0.42 1.26
5520.3 211.44
数据透视表能做什么？
分机号－金额 地区－金额 。。。
字段安排
多级别的分类汇总 在列的方向分析数据 把最后一层（或多层）放到行的方向
过滤数据
页区域
过滤项 可多个
字段设置
显示前10项
2,250 2,000 1,750 1,500 1,250 1,000 750 500 250 0 Qtr-1 Qtr-2 Qtr-3 Qtr-4 Product C Product B Product A
总数 每系列贡献
Quarterly Sales, by Product
1,400 1,200 1,000 800 600 400 200 0 Qtr-1 Product C Product B Qtr-2 Product A Qtr-3 Qtr-4
给数据自动添加单位
斜表头设计
专业报表设计
表格绘制及公式填写
表格的绘制 表格线填色设置 开始日期设置 单元格格式设置
其他设置
表格外边界设置 表格线设置 滚动条设置 有效性验证 条件格式 工作表保护
数据商业智能：数据有效性
数据有效性基本应用
限制数字 日期 序列 …..
设置输入提示信息 设置出错警告 数据有限高级应用
数据透视图
求和项:金额 分机号 汇总 316 3280.7 221 2057.58 318 2028.8 228 2028.26 262 1501.1 322 1489.62 236 1344.9 339 1297 270 678.3 220 674.1 总计 16380.36
汇总 3500 3000 2500 2000 汇总 1500 1000 500 0 316 221 318 228 262 322 236 339 270 220
媒介 1 媒介 2 媒介 3 媒介 4 媒介 5 媒介 6 媒介7 媒介8
合计
约束条件：
预算 ￥ 12,500,000 媒介3与媒介4的总费用之和不得高于600万元 ￥ 媒介4与媒介6的总费用之和不得高于600万元 ￥ 33.30% 任一媒介不得花费超过三分之一的广告费用 5 每个媒介上最少应投放的广告次数
Web Site Visitors
Web Site Visitors
30,000 25,000 20,000 15,000 10,000 5,000 0 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 30,000 25,000 20,000 15,000 10,000 5,000
地产广告
316 221 318 228 262 322 236 339 270 220 218 208 272 267 207 203 306 302 206 319 210 315 334
—假设您需要为一个楼盘开展广告宣传，总预算为1250万元 —初步确选定了8个广告媒介 —每一种媒介的影响群体数与费率各不相同 影响群体数 有效读者比 广告投放次 (百万) 例 有效读者数 数 16.2 12.5 10.6 7.7 9.3 9.2 9.8 16.0 61% 67% 78% 66% 50% 42% 33% 12% 9.9 8.4 8.3 5.1 4.6 3.9 3.2 1.9 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 总读者数 （百万） 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 媒介 单价 ￥ ￥ ￥ ￥ ￥ ￥ ￥ ￥ 147,420 124,410 113,100 70,070 63,000 52,440 51,860 30,000 总费用 $0 $0 $0 $0 $0 $0 $0 $0 $0 费用百分比 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
柱状面积图
100 50 0 Jan Feb Mar Apr May Jun
200 150
线-柱图
100 50 0
可乐 百事
Jan
Feb
Mar
Apr
May
Jun
200
150 100 50 0 Jan Feb Mar Apr May Jun 可乐 百事
Washington Oregon California
ห้องสมุดไป่ตู้泡图
参加人 Ray Pat Jill George Kevin Joe Cindy Terry
原体重 285 442 240 275 290 380 188 301
星期 12 3 5 18 1 18 13 7
减重 37 20 28 43 2 15 13 32
Web Site Visitors
0 Jan Feb Mar Apr May
Jun
Jul
Aug Sep Oct Nov Dec
Web Site Visitors
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov
Web Site Visitors
Dec Nov
Jan 30,000 20,000
睿智图表——“精准+精美”的图表报告
分析图表对话框
Excel标准图表类型
图表类型 面积图 条形图 数量 6 6 图表类型 XY(散点)图 曲面图 数量 5 4
柱型图
折线图 饼图 圆环图 雷达图
7
7 6 2 3
气泡图
股价图 圆柱图 圆锥图 棱锥图
2
4 7 7 7
选择图表类型
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 7,092 7,497 7,560 5,796 13,984 18,743 15,434 14,545 17,434 16,984 15,983 27,822
数据分析与数据统计
认识分析
分析是一个探索的过程 分析是合理的,因为分析往往依赖一定数学规律 分析方法和思路需要渐渐积累
认识统计
统计是有目的的数据计算
统计规则往往可重复
EXCEL在分析与统计中的作用
生产数据
Excel
结论
数据统计分析的基础
业务数据的排序 业务数据的筛选
数据分析利器：数据透视表
分机号 316 203 316 316 316 228 208 267 206 208 316 228 228 334 208 316 218 日期 时间 通话时长 电话区号 地址 2004-7-1 9:10 0:00:49 021 上海 2004-7-1 9:11 0:01:22 021 上海 2004-7-1 9:22 0:00:37 028 成都 2004-7-1 9:23 0:00:37 028 成都 2004-7-1 9:24 0:02:34 0137 GSM 2004-7-1 9:30 0:04:02 0138 GSM 2004-7-1 9:32 0:00:43 023 重庆 2004-7-1 9:32 0:00:52 021 上海 2004-7-1 9:32 0:02:20 0532 山东青岛 2004-7-1 9:33 0:02:08 023 重庆 2004-7-1 9:34 0:01:20 0532 山东青岛 2004-7-1 9:36 0:00:55 025 南京 2004-7-1 9:38 0:03:58 025 南京 2004-7-1 9:39 0:00:44 021 上海 2004-7-1 9:42 0:00:33 023 重庆 2004-7-1 9:42 0:03:21 023 重庆 2004-7-1 9:48 0:00:29 027 武汉 金额 0.70 1.40 0.70 0.70 2.10 3.50 0.70 0.70 2.10 2.10 1.40 0.70 2.80 0.70 0.70 2.80 0.70
Weight Loss as a Function of Weeks in Program and Original Weight
25
减重
20
15
Weeks
10
5
0 150
200
250
300
350
400
450
500
Orignal Weight (Lbs)
组合图表－系统自带
200 150 百事 可乐
数据分析思想到分析技巧
什么是商业数据分析？
什么是商业数据分析？ 为什么要进行商业数据分析？
电话分析
各分机占总费用比例
218 208 272 270 220 267 339 3% 3% 3% 3% 3% 207 3% 203 5% 3% 306 236 2% 302 2% 6% 206 2% 319 2% 322 2% 6% 262 6% 228 9% 318 9% 221 9% 316 14% 8%
Feb Mar
10,000 Oct 0 Apr
Sep Aug Jul Jun
May
饼图
不能多于5或6个数据点 负数会被转化成正数 扇区可脱离中心
如果数据项太多
起始角不能修改 位置/数值/百分比
多数据系列的折线图
描绘连续数据，确定趋势 数据点
面积图
Quarterly Sales, by Product
透视
数据透视表
交互式的分类汇总工具！ 数据透视表的4个区域
页区域 行区域 列区域 数据区域
理解数据透视表
左大分类，右小分类，上过滤，中汇总
地址 (全部) 求和项:金额 星期 时间段 日 8:00 9:00 10:00 11:00 12:00 1.68 13:00 3.36 14:00 1.68 15:00 0.42 16:00 17:00 18:00 19:00 20:00 21:00 22:00 23:00 总计 7.14 一 18.9 385.5 692 487.5 528.3 299.2 449.5 684.7 551 318.6 412.4 60 50.3 31.8 二 24.1 987.7 519.7 486.1 100.6 264.4 506.8 351.9 435.5 382 207.7 74.1 35.7 116.3 三 四 21.7 384.3 952.5 488.8 347.1 229.8 421.3 629 473.8 395.9 139 134.1 64.3 34.5 14.7 1.4 4732.2 五 7 513.7 833.6 682.3 290.2 142.4 847.4 769.1 520.9 503.1 231.3 131.7 44.1 1.4 2.1 六 1.26 8.4 394.6 490.5 633.9 118.7 243.1 472.6 439.6 387.7 314.3 44.8 47.2 24.5 21.7 12.6 0.7 4969.7 4492.6 3654.9 总计 81.36 2665.8 3488.3 2800 1396.2 1182.3 2723.6 2900 2474.5 1932.8 1038.6 447.52 220.16 205.7 29.4 2.1 23588
微软资深讲师/OFFICE（中国）实战专家 周 刚
优化视觉 专业报表
条件格式：敏感数据报警
数据报表：数据收集型
数据报表：数据展示型
Excel 条件格式，以图标集、色条等形式展示数 据，快速发现数据背后的问题
EXCEL报表常用技能&技巧
合并单元格/解除合并单元格 隐藏行列/隐藏数据显示 冻结单元格/交叉冻结单元格
圆环图
Sales Breakdown, by State
44%
1st Half 2nd Half
51%
28% 26%
21% 30%
California Oregon Washington
Sales Breakdown, by State
100% 90% 80% 70% 60% 50% 40% 30% 20% 10% 0% 1st Half 2nd Half
Web Site Visitors
30,000 25,000 20,000 15,000 10,000 5,000 0 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 0 5,000 10,000 15,000 20,000 25,000 30,000
21.42 9.6 24.36 25.26 105.6 18.9 3.36 0.42 1.26
5520.3 211.44
数据透视表能做什么？
分机号－金额 地区－金额 。。。
字段安排
多级别的分类汇总 在列的方向分析数据 把最后一层（或多层）放到行的方向
过滤数据
页区域
过滤项 可多个
字段设置
显示前10项
2,250 2,000 1,750 1,500 1,250 1,000 750 500 250 0 Qtr-1 Qtr-2 Qtr-3 Qtr-4 Product C Product B Product A
总数 每系列贡献
Quarterly Sales, by Product
1,400 1,200 1,000 800 600 400 200 0 Qtr-1 Product C Product B Qtr-2 Product A Qtr-3 Qtr-4
给数据自动添加单位
斜表头设计
专业报表设计
表格绘制及公式填写
表格的绘制 表格线填色设置 开始日期设置 单元格格式设置
其他设置
表格外边界设置 表格线设置 滚动条设置 有效性验证 条件格式 工作表保护
数据商业智能：数据有效性
数据有效性基本应用
限制数字 日期 序列 …..
设置输入提示信息 设置出错警告 数据有限高级应用
数据透视图
求和项:金额 分机号 汇总 316 3280.7 221 2057.58 318 2028.8 228 2028.26 262 1501.1 322 1489.62 236 1344.9 339 1297 270 678.3 220 674.1 总计 16380.36
汇总 3500 3000 2500 2000 汇总 1500 1000 500 0 316 221 318 228 262 322 236 339 270 220
媒介 1 媒介 2 媒介 3 媒介 4 媒介 5 媒介 6 媒介7 媒介8
合计
约束条件：
预算 ￥ 12,500,000 媒介3与媒介4的总费用之和不得高于600万元 ￥ 媒介4与媒介6的总费用之和不得高于600万元 ￥ 33.30% 任一媒介不得花费超过三分之一的广告费用 5 每个媒介上最少应投放的广告次数
Web Site Visitors
Web Site Visitors
30,000 25,000 20,000 15,000 10,000 5,000 0 Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 30,000 25,000 20,000 15,000 10,000 5,000
地产广告
316 221 318 228 262 322 236 339 270 220 218 208 272 267 207 203 306 302 206 319 210 315 334
—假设您需要为一个楼盘开展广告宣传，总预算为1250万元 —初步确选定了8个广告媒介 —每一种媒介的影响群体数与费率各不相同 影响群体数 有效读者比 广告投放次 (百万) 例 有效读者数 数 16.2 12.5 10.6 7.7 9.3 9.2 9.8 16.0 61% 67% 78% 66% 50% 42% 33% 12% 9.9 8.4 8.3 5.1 4.6 3.9 3.2 1.9 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 总读者数 （百万） 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 0.0 媒介 单价 ￥ ￥ ￥ ￥ ￥ ￥ ￥ ￥ 147,420 124,410 113,100 70,070 63,000 52,440 51,860 30,000 总费用 $0 $0 $0 $0 $0 $0 $0 $0 $0 费用百分比 #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0! #DIV/0!
柱状面积图
100 50 0 Jan Feb Mar Apr May Jun
200 150
线-柱图
100 50 0
可乐 百事
Jan
Feb
Mar
Apr
May
Jun
200
150 100 50 0 Jan Feb Mar Apr May Jun 可乐 百事
Washington Oregon California
ห้องสมุดไป่ตู้泡图
参加人 Ray Pat Jill George Kevin Joe Cindy Terry
原体重 285 442 240 275 290 380 188 301
星期 12 3 5 18 1 18 13 7
减重 37 20 28 43 2 15 13 32
Web Site Visitors
0 Jan Feb Mar Apr May
Jun
Jul
Aug Sep Oct Nov Dec
Web Site Visitors
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov
Web Site Visitors
Dec Nov
Jan 30,000 20,000
睿智图表——“精准+精美”的图表报告
分析图表对话框
Excel标准图表类型
图表类型 面积图 条形图 数量 6 6 图表类型 XY(散点)图 曲面图 数量 5 4
柱型图
折线图 饼图 圆环图 雷达图
7
7 6 2 3
气泡图
股价图 圆柱图 圆锥图 棱锥图
2
4 7 7 7
选择图表类型
Jan Feb Mar Apr May Jun Jul Aug Sep Oct Nov Dec 7,092 7,497 7,560 5,796 13,984 18,743 15,434 14,545 17,434 16,984 15,983 27,822
数据分析与数据统计
认识分析
分析是一个探索的过程 分析是合理的,因为分析往往依赖一定数学规律 分析方法和思路需要渐渐积累
认识统计
统计是有目的的数据计算
统计规则往往可重复
EXCEL在分析与统计中的作用
生产数据
Excel
结论
数据统计分析的基础
业务数据的排序 业务数据的筛选
数据分析利器：数据透视表
分机号 316 203 316 316 316 228 208 267 206 208 316 228 228 334 208 316 218 日期 时间 通话时长 电话区号 地址 2004-7-1 9:10 0:00:49 021 上海 2004-7-1 9:11 0:01:22 021 上海 2004-7-1 9:22 0:00:37 028 成都 2004-7-1 9:23 0:00:37 028 成都 2004-7-1 9:24 0:02:34 0137 GSM 2004-7-1 9:30 0:04:02 0138 GSM 2004-7-1 9:32 0:00:43 023 重庆 2004-7-1 9:32 0:00:52 021 上海 2004-7-1 9:32 0:02:20 0532 山东青岛 2004-7-1 9:33 0:02:08 023 重庆 2004-7-1 9:34 0:01:20 0532 山东青岛 2004-7-1 9:36 0:00:55 025 南京 2004-7-1 9:38 0:03:58 025 南京 2004-7-1 9:39 0:00:44 021 上海 2004-7-1 9:42 0:00:33 023 重庆 2004-7-1 9:42 0:03:21 023 重庆 2004-7-1 9:48 0:00:29 027 武汉 金额 0.70 1.40 0.70 0.70 2.10 3.50 0.70 0.70 2.10 2.10 1.40 0.70 2.80 0.70 0.70 2.80 0.70
Weight Loss as a Function of Weeks in Program and Original Weight
25
减重
20
15
Weeks
10
5
0 150
200
250
300
350
400
450
500
Orignal Weight (Lbs)
组合图表－系统自带
200 150 百事 可乐
数据分析思想到分析技巧
什么是商业数据分析？
什么是商业数据分析？ 为什么要进行商业数据分析？
电话分析
各分机占总费用比例
218 208 272 270 220 267 339 3% 3% 3% 3% 3% 207 3% 203 5% 3% 306 236 2% 302 2% 6% 206 2% 319 2% 322 2% 6% 262 6% 228 9% 318 9% 221 9% 316 14% 8%
Feb Mar
10,000 Oct 0 Apr
Sep Aug Jul Jun
May
饼图
不能多于5或6个数据点 负数会被转化成正数 扇区可脱离中心
如果数据项太多
起始角不能修改 位置/数值/百分比
多数据系列的折线图
描绘连续数据，确定趋势 数据点
面积图
Quarterly Sales, by Product
透视
数据透视表
交互式的分类汇总工具！ 数据透视表的4个区域
页区域 行区域 列区域 数据区域
理解数据透视表
左大分类，右小分类，上过滤，中汇总
地址 (全部) 求和项:金额 星期 时间段 日 8:00 9:00 10:00 11:00 12:00 1.68 13:00 3.36 14:00 1.68 15:00 0.42 16:00 17:00 18:00 19:00 20:00 21:00 22:00 23:00 总计 7.14 一 18.9 385.5 692 487.5 528.3 299.2 449.5 684.7 551 318.6 412.4 60 50.3 31.8 二 24.1 987.7 519.7 486.1 100.6 264.4 506.8 351.9 435.5 382 207.7 74.1 35.7 116.3 三 四 21.7 384.3 952.5 488.8 347.1 229.8 421.3 629 473.8 395.9 139 134.1 64.3 34.5 14.7 1.4 4732.2 五 7 513.7 833.6 682.3 290.2 142.4 847.4 769.1 520.9 503.1 231.3 131.7 44.1 1.4 2.1 六 1.26 8.4 394.6 490.5 633.9 118.7 243.1 472.6 439.6 387.7 314.3 44.8 47.2 24.5 21.7 12.6 0.7 4969.7 4492.6 3654.9 总计 81.36 2665.8 3488.3 2800 1396.2 1182.3 2723.6 2900 2474.5 1932.8 1038.6 447.52 220.16 205.7 29.4 2.1 23588