Excel 高效办公——数据处理与分析(案例版)

目 录

第 1章 数据分析引言
1．1 数据分析的目的 2
1．2 数据分析的流程 2
1．3 数据分析的方法 2
1．3．1 描述分析 3
1．3．2 对比分析 3
1．3．3 趋势分析 4
1．3．4 相关分析 4
1．3．5 预测分析 4
1．3．6 转化分析 5
1．3．7 分布分析 5
1．3．8 结构分析 5
1．3．9 达成分析 5
1．4 数据分析结果可视化 6

第 2 章 人力资源数据分析
2．1 在职员工结构分析 8
2．1．1 收集并备份数据 8
2．1．2 清洗数据 9
1． 日期格式不规范、不统一 9
2． 同一字段出现多种说法 10
3． 手机号、身份证号位数不准确 12
4． 性别、出生日期、年龄等
与身份证信息不一致 12
2．1．3 不同部门的在职员工分布情况 16
1． 统计各部门的在职员工人数 16
2． 可视化不同部门的在职人数 18
2．1．4 在职员工的学历分布情况分析 23
2．1．5 在职员工的性别比例分布分析 26
1． 统计不同性别的在职员工人数 26
2． 使用圆环图展现在职员工的性别比例 28
2．1．6 不同年龄段的在职员工人数分布情况 28
2．1．7 不同工龄段的在职员工人数分布情况 34
2．1．8 构建在职员工结构数据看板 36
1． 构建基础版面 36
2． 指标数据分类 38
3． 将指标数据按序排列 38
2．2 离职情况分析 40
2．2．1 离职率分析 40
1． 年度离职率 40
2． 各月离职率 47
3． 不同部门的离职率 52
4． 不同年龄段的离职率 53
5． 不同工龄段的离职率 55
6． 联动分析离职率 57
2．2．2 离职原因分析 60
2．2．3 构建离职情况分析看板 63
2．3 招聘情况分析 65
2．3．1 招聘概况分析 65
1． 不同部门的招聘人数 65
2． 不同学历的招聘人数 66
3． 不同年龄段的招聘人数 67
4． 不同性别的招聘人数 68
2．3．2 招聘过程分析 70
2．3．3 招聘结果分析 72
2．3．4 招聘渠道分析 74
2．3．5 构建招聘情况分析看板 74
2．4 薪酬结构分析 76
2．4．1 分析不同部门的实发工资 76
2．4．2 分析实发工资和绩效工资的分布情况 78
2．4．3 分析不同岗位的工资情况 80
2．4．4 分析不同分项工资的占比 80
2．4．5 构建薪酬结构分析看板 81

第3章 生产数据分析
3．1 需求预测与生产计划分析 83
3．1．1 使用移动平均法进行月度需求预测分析 83
1． 安装分析工具库 84
2． 使用移动平均法预测未来一个月的销量 85
3．1．2 使用指数平滑法进行月度需求预测分析 88
1． 判断数据变化趋势 89
2． 用一次指数平滑法预测未来一个月的销量 90
3．2 生产过程的监督与分析 93
3．2．1 生产合格率分析 93
1． 计算生产合格率 93
2． 分析生产合格率 94
3．2．2 生产合格率异常分析 96
3．2．3 设备生产能力优化决策分析 99
1． 盈亏平衡点 99
2． IF函数 99
3． OR函数 99
3．2．4 合理配料实现利润*大化 102
1． 安装规划求解插件 103
2． 用规划求解法求*佳配料方式 104
3．3 量本利预测分析 113
3．3．1 盈亏平衡点分析 114
3．3．2 目标利润分析 116

第4章 销售数据分析
4．1 月度销售数据分析 119
4．1．1 不同品类的销售额分析 119
4．1．2 不同品类的销量分析 122
4．1．3 不同品类的销售额的环比分析 123
4．1．4 不同品类的销售额的同比分析 125
4．1．5 各业务员的销售额占比分析 128
4．2 年度销售数据分析 131
4．2．1 不同品类的年度销售额分析 131
4．2．2 全年销售额的变动趋势分析 132
4．2．3 近3年销售额变动趋势对比分析 134
4．2．4 热销产品分析 137
1． 对不同规格的产品的销售数量进行汇总 137
2． 将不同规格的产品按销售数量进行排序 138
3． 将不同规格的产品按销售数量进行排名 138
4． 可视化销售数量排名前8的产品 139
4．2．5 单价对销量的影响分析 144
1． 汇总统计不同价格区间的产品销量占比 144
2． 可视化不同价格区间的产品销量占比 145
4．2．6 销售额完成率分析 147
1． 创建数据源 148
2． 创建图表 149
3． 转换动态图表 152
4．3 销售数据预测分析 154
4．3．1 使用趋势线进行销售预测分析 154
4．3．2 使用函数进行销售预测分析 158
1． 简单平均法 158
2． 移动平均法 159
3． 指数平滑法 159
4． 直线回归分析法 160
4．3．3 使用数据分析工具进行销售预测分析 160
1． 移动平均预测 161
2． 指数平滑预测 162
4．3．4 使用预测工作表进行销售预测分析 164

第5章 财务数据分析
5．1 财务数据结构分析 166
5．1．1 利润表分析 166
1． 收支结构分析 166
2． 收支明细分析 167
5．1．2 资产负债表分析 169
1． 资产结构分析 169
2． 流动资产构成要素分析 169
3． 固定资产构成要素分析 174
4． 负债和所有者权益结构分析 174
5． 流动负债构成要素分析 174
6． 所有者权益构成要素分析 175
5．1．3 现金流量表分析 175
1． 流入流出比例分析 175
2． 流入结构分析与流出结构分析 175
5．2 财务比率分析 177
5．2．1 变现能力比率分析 177
5．2．2 资产管理比率分析 179
5．2．3 负债比率分析 183
5．2．4 盈利能力比率分析 185
5．3 财务比较分析 187
5．4 财务趋势分析 190
5．5 杜邦分析 193

第6章 投资决策分析
6．1 资金管理的决策分析 198
6．1．1 银行贷款决策分析 198
1． 用相同利率计算等额本息还款 198
2． 用不同利率计算等额本息还款 201
3． 可视化不同利率和贷款期限的总还款额变化 202
6．1．2 债券筹资决策分析 204
1． 计算债券的发行价格 204
2． 分析市场利率对债券发行价格的影响 206
3． 分析票面利率对债券发行价格的影响 207
6．2 项目投资的效益分析 208
6．2．1 使用净现值法进行投资决策分析 208
6．2．2 使用内含报酬率法进行投资决策分析 210
6．3 项目投资的风险分析 212
6．3．1 通过相关系数判断风险大小 212
6．3．2 计算要求报酬率判断投资可行性 214
6．4 投资组合决策分析 215
1． 计算项目1的现值 216
2． 计算各项目的净现值 217
3． 计算企业的投入资金 217
4． 计算净现值合计 218
5． 规划求解确定选择变量 219
6．5 固定资产折旧分析 220
6．5．1 用年限平均法计算固定资产折旧额 221
6．5．2 用双倍余额递减法计算固定资产折旧额 222
1． 用双倍余额递减法计算前8年的固定资产折旧额 223
2． 用年限平均法计算*后两年的固定资产折旧额 224
6．5．3 用年数总计法计算固定资产折旧额 224
6．6 固定资产项目投资决策分析 226
6．6．1 计算每年的销售收入 227
6．6．2 计算每年的销售数量 228
6．6．3 计算每年的利息支出 229
6．6．4 计算每年的经营成本和利润总额 230
6．6．5 计算每年的所得税 231
6．6．6 计算每年的现金流入量、现金流出量和净现金流量 231
6．6．7 计算净现值 232

第7章 电商数据分析
7．1 流量分析 235
7．1．1 店铺流量数据指标 235
7．1．2 店铺流量来源 236
1． 自主访问流量 236
2． 付费流量 237
3． 淘内流量 239
4． 淘外流量 239
7．1．3 店铺流量结构分析 239
1． 删除多余数据 240
2． 调整数据格式 240
3． 分析不同流量来源的访客数 241
4． 分析排名前10的流量来源明细 243
7．1．4 页面流量分析 245
7．1．5 商品详情页流量分析 248
7．2 转化率分析 251
7．2．1 流量转化分析 251
7．2．2 提高转化率的有效方法 252
7．3 销售业绩分析 253
7．3．1 交易金额分析 253
7．3．2 客单价分析 255
7．4 客户数据分析 256
7．4．1 客户分布情况分析 256
7．4．2 会员增长与流失情况分析 258
1． 不同地区会员的增长与流失情况 258
2． 近一年店铺会员的增长与流失情况 260
7．5 利润数据分析 261
7．5．1 影响网店盈利的成本因素 261
1． 商品成本 262
2． 推广成本 262
3． 固定成本 263
7．5．2 利润预测分析 264
1． 线性预测 264
2． 模拟运算 266
3． 双变量模拟预测 268
7．6 客服数据分析 268
7．6．1 响应时间分析 269
7．6．2 咨询转化率分析 270
7．6．3 落实客单价分析 271
7．6．4 订单支付率分析 272
7．6．5 月退货率分析 273
7．6．6 客服KPI考核综合分析 274
1． 计算KPI考核综合得分 274
2． 动态可视化客服人员的KPI指标 274

第8章 在线教育数据分析
8．1 在线教育销售状况及趋势分析 281
8．1．1 销售额与毛利润分析 281
8．1．2 毛利率分析 282
8．2 渠道分析 283
8．2．1 不同渠道的销售额与毛利率分析 284
8．2．2 免费渠道的毛利率分析 286
1． 免费渠道内不同分渠道的毛利率对比分析 286
2． 免费渠道内不同分渠道的毛利率环比分析 287
8．3 客户分析 289
8．3．1 客户行为分析 289
1． 不同环节的转化率分析 289
2． 总转化率分析 293
8．3．2 客户价值分析 296

第9 章 短视频运营数据分析
9．1 自身视频的数据分析 302
9．2 同行视频数据分析 304
1． 合并多个同行视频数据工作表 304
2． 分析不同账号的粉丝数量 306
3． 分析不同账号的粉丝增量 307
9．3 热门视频数据分析 308

第 10章 利用Power BI进行数据分析
10．1 了解Power BI 310
1． Power BI Desktop的工作界面 310
2． Power BI与Excel相比的优势 311
10．2 将Excel中的数据导入Power BI Desktop 312
10．3 分析店铺热销产品 314
1． 根据产品名称统计销售数量 314
2． 按数量进行降序排列 317
3． 创建图表 317
4． 根据表格和图表内容编写分析报告 320
10．4 分析店铺产品结构 321
1． 统计各产品的销售金额 321
2． 对销售金额进行分组 324
3． 创建表格和图表 325
10．5 分析门店客流量 329
1． 分析每天的客流量 329
2． 分析星期一至星期日的客流量 332
3． 分析工作日和休息日的客流量 337