从逻辑思路到实战应用:轻松做Excel数据分析

目录 第 1 部分 数据采集与汇总 第 1 章 快速汇总大量的一维表单 ................................................................... 3 1.1 “现有连接”命令 +SQL 语句 ............................................................................ 3 1.1.1 1.1.3 1.1.4 1.1.5 1.1.6 1.2.1 1.2.3 SQL 语句的基本知识 ......................................................................................... 3 汇总全部列、部分行数据 ................................................................................... 8 汇总部分列、全部行数据 ................................................................................... 9 汇总部分列、部分行数据 ................................................................................... 9 汇总不同工作簿里的工作表 ............................................................................. 10 汇总当前工作簿里的多个工作表 ...................................................................... 10 汇总不同工作簿里的多个工作表 ...................................................................... 12 1.1.2 汇总全部列数据 ................................................................................................. 5 1.2 VBA 方法 ........................................................................................................ 10 1.2.2 汇总不同工作簿里的一个工作表 .......................................................................11 1.2.4 VBA 基本知识概述 ........................................................................................... 14 1.3 Power Query 方法 .......................................................................................... 24 1.3.1 汇总当前工作簿的多个工作表 .......................................................................... 24 1.3.2 汇总多个工作簿，每个工作簿仅有一个工作表................................................. 28 1.3.3 汇总多个工作簿，每个工作簿有多个工作表 .................................................... 34 1.3.4 汇总多个工作簿里满足条件的数据................................................................... 38 第 2 章 快速汇总大量二维表格 ..................................................................... 39 2.1 灵活汇总结构完全相同的工作表 ..................................................................... 39 2.1.1 使用 SUM 函数快速汇总 .................................................................................. 39 2.1.2 使用合并计算工具快速汇总 ............................................................................. 40 2.1.3 使用 INDIRECT 函数 ....................................................................................... 42 2.2 大量二维表格的快速汇总 ................................................................................ 43 2.2.1 2.2.3 利用多重合并计算数据区域透视表................................................................... 44 利用 Power Query ........................................................................................... 48 I 2.2.2 利用 INDIRECT 函数 ....................................................................................... 47 从逻辑思路到实战应用，轻松做 Excel 数据分析 第 3 章 关联工作表的快速汇总 ..................................................................... 52 3.1 Microsoft Query 工具 ...................................................................................... 52 3.1.1 Microsoft Query 汇总的基本步骤 ..................................................................... 52 3.1.2 Microsoft Query 汇总的注意事项 ..................................................................... 55 3.2 Power Query 工具 .......................................................................................... 57 3.2.1 建立基本查询 ................................................................................................... 57 3.2.2 合并已有查询 ................................................................................................... 58 3.2.3 添加自定义列 ................................................................................................... 61 3.2.4 加载为数据模型，便于以后随时调用 ............................................................... 62 第 4 章 个数不定工作表的自动滚动汇总 ...................................................... 64 4.1 单独使用 INDIRECT 函数 ............................................................................... 64 4.2 INDIRECT 函数与查找函数联合使用 .............................................................. 65 4.3 INDIRECT 函数与分类汇总函数联合使用 ....................................................... 66 第 5 章 获取其他类型文件的数据 ................................................................. 68 5.1 从文本文件中获取数据 ................................................................................... 68 5.2 从数据库中获取数据 ....................................................................................... 70 5.3 从网页上获取数据........................................................................................... 72 第 2 部分 数据查询与提取 第 6 章 从一个工作表获取部分数据 ............................................................. 77. 6.1 利用透视表快速制作明细表 ............................................................................ 77 6.1.1 每次制作一个明细表 ........................................................................................ 77 6.1.2 一次批量制作多个明细表 ................................................................................. 79 6.2 利用函数制作自动化明细表 ............................................................................ 81 6.2.1 制作满足一个条件的明细表 ............................................................................. 81 6.2.2 制作满足多个条件的明细表 ............................................................................. 83 6.2.3 综合练习 .......................................................................................................... 84 6.3 利用 VBA 一键完成明细表 .............................................................................. 84 6.3.1 通过录制宏将普通的筛选 / 复制 / 粘贴工作自动化 ........................................... 85 6.3.2 使用 ADO 技术快速筛选查询数据 .................................................................... 86 6.3.3 在当前工作簿中批量制作明细表 ...................................................................... 87 6.3.4 批量制作明细表工作簿..................................................................................... 88 6.4 制作与源数据链接的动态明细表 ..................................................................... 89 第 7 章 从大量工作表中提取部分数据 .......................................................... 90 7.1 从一个工作簿的多个工作表中提取数据 .......................................................... 90 II 目 录 7.1.1 多个工作表的合并查询..................................................................................... 90 7.1.2 其他查询操作 ................................................................................................... 95 7.2 从多个工作簿的多个工作表中提取数据 .......................................................... 96 7.2.1 多个工作簿的合并查询..................................................................................... 97 7.2.2 其他查询操作 ................................................................................................. 102 第 8 章 从其他类型文件中提取部分数据 .................................................... 103 8.1 从文本文件中提取数据 ................................................................................. 103 8.1.1 8.1.2 利用 Microsoft Query ..................................................................................... 103 利用 Power Query ......................................................................................... 106 8.2 从其他数据库中提取数据 .............................................................................. 108 第 3 部分 数据分析与挖掘 第 9 章 数据分析是对数据的思考 ............................................................... 111 9.1 案例剖析：毛利预算分析 ...............................................................................111 9.1.1 9.1.2 9.1.3 9.1.4 9.1.5 9.1.6 9.2.1 9.2.2 9.2.3 9.2.4 9.2.5 9.2.6 9.2.7 失败的分析报告 ..............................................................................................111 醒目标识需要特别关注的异常数据..................................................................112 用仪表盘直观表达出整体预算完成情况 ..........................................................112 分析各个产品毛利预算偏差影响 .....................................................................112 分析各个产品销量、单价、成本对毛利预算偏差影响 .....................................113 进一步分析问题产品 .......................................................................................113 价值树的原理 ..................................................................................................113 营业利润价值树分析 .......................................................................................114 毛利价值树分析——依照计算逻辑流程 ..........................................................114 毛利价值树分析——依照业务单元(详细).....................................................115 毛利价值树分析——依照业务单元(简略).....................................................116 净利润率价值树分析 .......................................................................................116 净资产收益率价值树分析 ................................................................................117 9.2 利用价值树分析企业经营 ...............................................................................113 第 10 章 管理者驾驶舱，一目了然的经营分析与决策报告 ......................... 118 10.1 经营成果分析驾驶舱仪表盘示例 ..................................................................118 10.1.1 10.1.2 10.2.1 10.2.2 10.2.3 预算执行分析仪表盘 .....................................................................................119 同比分析仪表盘 ............................................................................................119 使用数据透视表建立简单的销售分析仪表盘 ................................................ 120 使用函数建立自动化销售分析仪表盘 ........................................................... 121 联合使用函数和透视表建立销售分析仪表盘 ................................................ 121 10.2 销售分析驾驶舱仪表盘示例 ........................................................................ 120 III 从逻辑思路到实战应用，轻松做 Excel 数据分析 10.2.4 利用 VBA 自动对客户进行排名 .................................................................... 123 10.3 人工成本分析驾驶舱仪表盘示例 ................................................................. 123 10.4 利用 Power 工具建立经营分析仪表盘......................................................... 124 第 11 章 智能表格，日常数据的基本分析 .................................................. 125 11.1 智能表格的创建与维护................................................................................ 125 11.1.1 11.1.3 创建智能表格 ............................................................................................... 125 手工调整表格大小 ........................................................................................ 126 11.1.2 智能表格的格式化 ........................................................................................ 126 11.1.4 为表格添加列、插入行 ................................................................................. 127 11.1.5 删除表格的列或行 ........................................................................................ 128 11.2 利用智能表格快速分析数据 ........................................................................ 128 11.2.1 11.2.3 为表格添加汇总行 ........................................................................................ 128 使用快速分析工具来分析表格 ...................................................................... 130 11.2.2 使用切片器筛选数据 .................................................................................... 129 第 12 章 固定格式报表：函数的综合运用 .................................................. 131 12.1 示例数据 ..................................................................................................... 131 12.2 从原始数据直接制作底稿 ............................................................................ 132 12.2.1 12.2.2 12.3.1 12.3.2 从原始数据表单中直接汇总计算 .................................................................. 132 快速得到结构相同的汇总表 ......................................................................... 132 分析各个产品指定项目的当月数和累计数 .................................................... 133 分析指定产品、指定项目的各月预算执行情况............................................. 134 12.3 从底稿中直接进行计算分析 ........................................................................ 133 第 13 章 多维汇总分析，数据透视表工具的灵活运用 ................................ 136 13.1 制作数据透视表的常用方法 ........................................................................ 136 13.1.1 13.1.2 13.1.3 13.1.4 13.1.5 以一个表格数据创建数据透视表 .................................................................. 136 以多个二维表格数据创建数据透视表 ........................................................... 138 以多个一维表单数据创建数据透视表 ........................................................... 138 在不打开工作簿的情况下创建数据透视表 .................................................... 139 以表格的部分数据创建数据透视表............................................................... 140 13.2 布局数据透视表 .......................................................................................... 140 13.2.1 “数据透视表字段”窗格 ............................................................................... 140 13.2.2 13.2.3 13.3.1 13.3.2 13.3.3 数据透视表布局的几种方法 ......................................................................... 141 数据透视表工具 ........................................................................................... 141 设计透视表的样式 ........................................................................................ 141 设计透视表的布局 ........................................................................................ 142 重复 / 不重复项目标签 ................................................................................. 143 13.3 美化数据透视表 .......................................................................................... 141 IV 目 录 13.3.4 13.3.5 13.3.7 13.3.9 13.3.10 13.3.12 显示 / 隐藏报表的行总计和列总计 ............................................................... 143 合并项目标签单元格 .................................................................................... 144 设置值字段的汇总依据................................................................................. 145 显示 / 隐藏字段的分类汇总 .......................................................................... 145 对分类字段项目自动排序 ........................................................................... 146 显示没有数据的分类字段项目 .................................................................... 146 13.3.6 修改值字段名称 ........................................................................................... 144 13.3.8 设置值字段的数字格式................................................................................. 145 13.3.11 对分类字段项目手动排序 ........................................................................... 146 13.3.13 刷新数据透视表 ......................................................................................... 147 13.4 利用透视表分析数据 ................................................................................... 147 13.4.1 13.4.2 13.4.3 13.4.4 13.4.5 13.4.6 13.4.7 13.4.8 13.4.9 13.5.1 13.5.2 13.5.3 通过设置字段汇总方式分析数据 .................................................................. 148 通过设置字段显示方式分析数据 .................................................................. 148 通过组合字段分析数据................................................................................. 149 自定义计算字段 ........................................................................................... 151 自定义计算项 ............................................................................................... 152 计算字段和计算项的区别 ............................................................................. 153 透视表的基本数据筛选................................................................................. 154 筛选值*大(*小)的前 N 个项目 .............................................................. 154 使用切片器快速筛选报表 ............................................................................. 156 费用预实分析 ............................................................................................... 157 动态进销存管理 ........................................................................................... 158 客户销售同比分析与流入流出分析............................................................... 160 13.5 透视表综合应用案例 ................................................................................... 157 第 14 章 Power Pivot 报表，更加强大的数据分析工具 ............................ 162. 14.1 加载 Power Pivot ........................................................................................ 162 14.2 建立单个工作表的 Power Pivot .................................................................. 163 14.3 建立多个关联工作表的 Power Pivot ........................................................... 167 14.4 以大量工作表数据创建 Power Pivot ........................................................... 171 14.5 以文本文件数据创建 Power Pivot ............................................................... 172 14.6 以数据库数据创建 Power Pivot .................................................................. 174 第 4 部分 分析结果可视化 第 15 章 一图抵万言，数据分析可视化图表 ............................................... 179 15.1 图表到底是什么 .......................................................................................... 179 15.1.1 15.1.2 对图表的困惑 ............................................................................................... 179 案例剖析 ...................................................................................................... 180 V 从逻辑思路到实战应用，轻松做 Excel 数据分析 15.1.3 15.2.1 15.2.2 15.2.3 15.2.4 15.2.5 15.3.1 15.3.2 15.3.3 15.3.4 15.3.5 15.3.6 15.3.7 15.3.8 数据充满思想，图表充满思考 ...................................................................... 183 阅读表格是基本功 ........................................................................................ 183 案例剖析举例之一 ........................................................................................ 184 案例剖析举例之二 ........................................................................................ 184 案例剖析举例之三 ........................................................................................ 185 案例剖析举例之四 ........................................................................................ 186 利用固定数据区域绘图................................................................................. 186 利用定义名称绘图 ........................................................................................ 187 为图表添加新数据系列................................................................................. 189 修改数据系列 ............................................................................................... 190 删除数据系列 ............................................................................................... 190 设置数据系列的坐标轴................................................................................. 190 设置数据系列的图表类型 ............................................................................. 191 图表的保存位置 ........................................................................................... 192 15.2 从阅读表格入手 .......................................................................................... 183 15.3 绘制图表的基本方法 ................................................................................... 186 15.3.9 复制图表 ...................................................................................................... 192 15.3.10 删除图表 .................................................................................................... 192 15.4 格式化图表 ................................................................................................. 193 15.4.1 15.4.2 15.4.3 15.4.4 15.4.5 15.4.6 15.4.7 15.4.8 图表结构及主要元素 .................................................................................... 193 为图表添加元素 ........................................................................................... 195 选择图表元素的方法 .................................................................................... 195 设置图表元素格式的工具 ............................................................................. 196 格式化图表区 ............................................................................................... 196 格式化绘图区 ............................................................................................... 197 格式化坐标轴 ............................................................................................... 197 格式化图表标题、坐标轴标题、图例 ........................................................... 197 15.4.9 格式化网格线 ............................................................................................... 197 15.4.10 格式化数据系列 ......................................................................................... 198 15.4.11 格式化数据标签.......................................................................................... 198 15.4.12 突出标识图表的重要信息 ........................................................................... 199 15.4.13 让图表元素显示为单元格数据 .................................................................... 199 15.4.14 让数据点显示为形状 .................................................................................. 199 15.4.15 15.5.1 15.5.2 15.5.3 15.5.4 VI 简单是美 .................................................................................................... 199 柱形图 .......................................................................................................... 199 条形图 .......................................................................................................... 200 折线图 .......................................................................................................... 202 XY 散点图 .................................................................................................... 203 15.5 常见分析图表及其基本应用案例 ................................................................. 199 目 录 15.5.5 15.5.6 15.5.7 15.5.8 15.5.9 饼图 ............................................................................................................. 204 圆环图 .......................................................................................................... 206 面积图 .......................................................................................................... 208 雷达图 .......................................................................................................... 209 气泡图 .......................................................................................................... 210 15.5.10 树状图 ........................................................................................................ 212 15.5.11 旭日图 ........................................................................................................ 213 15.5.12 直方图 ........................................................................................................ 213 15.5.13 箱形图 ........................................................................................................ 214 15.5.14 漏斗图 ........................................................................................................ 214 15.5.15 瀑布图 ........................................................................................................ 215 15.5.16 组合图 ........................................................................................................ 216 第 16 章 动态图表，让数据分析更灵活 ...................................................... 219 16.1 动态图表技术：概述 ................................................................................... 219 16.1.1 16.1.2 16.1.3 16.1.4 16.1.5 16.1.6 16.2.1 16.2.2 16.2.3 16.2.4 16.2.5 16.2.6 动态图的基本原理 ........................................................................................ 219 动态图表制作方法：辅助区域法 .................................................................. 220 动态图表制作方法：动态名称法 .................................................................. 222 动态图表制作方法：数据透视图法............................................................... 222 制作动态图表的六大步骤 ............................................................................. 223 制作动态图表需要注意的几个问题............................................................... 223 使用组合框制作动态图表 ............................................................................. 223 使用列表框制作动态图表 ............................................................................. 224 使用选项按钮制作动态图表 ......................................................................... 225 使用复选框制作动态图表 ............................................................................. 226 使用滚动条制作动态图表 ............................................................................. 228 使用数值调节钮制作动态图表 ...................................................................... 230 16.2 常见表单控件控制的动态图表制作 ............................................................. 223 第 17 章 数据透视图与数据透视表联合使用，建立个性化的小 BI ............. 232. 17.1 创建数据透视图 .......................................................................................... 232 17.1.1 17.1.2 17.1.3 17.1.4 17.2.1 17.2.2 17.3.1 创建数据透视图的基本方法 ......................................................................... 232 关于数据透视图的分类轴 ............................................................................. 233 数据透视图的美化 ........................................................................................ 234 利用切片器控制透视表和透视图 .................................................................. 234 建立多重合并计算数据区域透视表............................................................... 235 联合使用切片器和数据透视图进行分析 ....................................................... 236 创建普通的数据透视表................................................................................. 237 VII 17.2 二维表格的透视分析 ................................................................................... 235 17.3 一维表格的透视分析 ................................................................................... 237 从逻辑思路到实战应用，轻松做 Excel 数据分析 17.3.2 17.3.3 17.3.4 分析指定产品的客户销售额 ......................................................................... 238 分析指定客户的产品销售情况 ...................................................................... 238 分析客户销售排名 ........................................................................................ 238 第 18 章 Power View 报表，更加灵活的可视化 ....................................... 240. 18.1 创建报表 ..................................................................................................... 240 18.2 报表的可视化 .............................................................................................. 241 第 5 部分 实战案例精讲 第 19 章 精选案例讲解：手工台账数据管理与分析模板——日常考勤 ............ 管理 ............................................................................................ 247 19.1 设计考勤表母版 .......................................................................................... 247 19.2 各月考勤记录表 .......................................................................................... 248 19.3 统计分析 ..................................................................................................... 248 第 20 章 精选案例讲解：基于系统导出数据的分析模板——两年销售 ............... 同比分析 ..................................................................................... 251. 20.1 制作分析底稿 .............................................................................................. 251 20.2 两年销售额、销售成本和毛利整体分析 ...................................................... 252 20.3 两年销售额、销售成本和毛利的月度同比分析 ........................................... 253 20.4 分析企业销售额同比增长原因：产品影响................................................... 253 20.5 分析指定产品销售额同比增长原因：量价分析 ........................................... 254 20.6 分析企业毛利同比增长原因：产品影响 ...................................................... 257 20.7 分析指定产品毛利同比增长原因：销量 - 单价 - 成本分析........................... 257 20.8 客户排名分析 .............................................................................................. 259 20.9 分析客户流动：新增客户 ............................................................................ 259 20.10 分析客户流动：流失客户 .......................................................................... 260 20.11 分析客户流动：存量客户 .......................................................................... 260 VIII