使用了大量数组公式导致Excel卡死，有没有解决方法？

题主的链接过期了，由于可能涉及题主公司的隐私数据

因此饼干将

需要用到的事实表及维度表（已去敏）
饼干做的Pivot数据模型和度量

上传到网盘供需要的人练习

链接:

密码: f3fn

本答案适合仍在为制作报表犯愁的你

Warning！Warning！Warning！

长文多图预警！！！

题主的问题是：

原始数据是10列×79562行的一维表要交叉分组求中位数，有没有好的方法？

作为一个专业的<删除>做表的</删除>数据分析师，我可以很明确的告诉题主：

有！不需要学编程！也 不需要装软件！

认真看完这篇<删除>安利</删除>答案就能上手！

熟练工 20分钟 就能做完模板！

题主这个量级的数据跑一次根本用不了 10s ！

不黑不吹！

毕竟我平时跑报表 行数 一般是这样的…

在正式安利之前，我们先整理一下目前提到的方式

题主想到的：数据透视表、数组公式、Access、VBA
知友推荐的：Oracle、SSRS、SPSS、SAS、Python

为什么不推荐这些方式？

我们看一下题主提供的原始报表：

这是一张具有 中国特色 的混合式报表

从上到下分为 4 块

• 月度放款时效中位数
• 时长环比下降率（月环比的衍生）
• 指定日期的放款时效中位数
• 指定月份下每日放款时效中位数

同时最右侧一列是读取另一份数据源的放款时效，并且也分了上述 4 块

现有的方式中

• 数据透视表适合处理大量数据，但 不支持中位数
• 数据库（Oracle）、统计软件（SPSS、SAS、Python）能算中位数，但是和 Excel 的 交互差 …尤其是中国特色混合式报表…

<删除>顺便吐槽统计软件有 Python 没 R 不服</删除>

• SSRS 没用过，但用过 MSTR 的我表示 BI 软件的 部署 绝对是个大工程

而题主最后的选择

• 数组公式，本身 不适合用于大量数据 ，尤其被引用的单元格是公式计算的结果
• VBA，可以通过 UDF 解决，但会 涉及到多维数组

或者换句话说，既然题主问的是有没有 好 的方法

那么

以饼干对这些方式的了解

并没有可以在半小时甚至半天内达到以下效果的

• 显示明细的月份切片
• 重点关注的日期切片
• 20 分钟的制作时间
• 10 秒钟的刷新时间

关键是

Excel 自带！上手容易 ！！

不得不说，微软确实厉害

早早看到个性化专业化数据分析的需求

Excel → Power Query → Power Pivot → Power View → Power BI

从查询到分析到可视化一应俱全，绝对是新手上路的不二选择！

咳咳，那么就跟着我一起看看专业<删除>做表的</删除>数据分析师的日常

1、准备工作

Power Pivot 内建于 Excel 2016，在开发工具中的 COM 加载项中进行启用

Excel 2016 的安装可以… 找 IT

PS.饼干订阅的是 Microsoft Office 365 ProPlus

2、数据ETL

由于题主提供了网盘，并且数据是 SQL 的结果

因此省了采集和清洗的过程

不过对于使用 Excel 作为数据源，饼干还是有些建议 不得不说

首先来看下原始数据的截图：

可以看到，题主的数据较为规整

但 10 行中有 4 行是辅助列

包括了 2 行使用 Vlookup 进行 跨表整列 的查询

这会

极大地影响数组公式的运算

虽然公式对于 Power Pivot 的影响相对较小

但是仍然不建议使用诸如 Vlookup、Sumif 等涉及整列的聚合函数

同时 饼干建议

对规整的数据使用 表格 的形式储存并修改表名

这会让之后的引用更加直观！

下图是根据字段属性拆分后的结果：

• 事实表
  

包括新增放款各环节时效明细和各公司放款时效汇总
题主的辅助列被我 删除 并归在了 维度表 中

• 维度表
  

包括节点对应、运营状态对应及我添加的时间维

整理好以后，习惯性地检查了下重：

有点懵逼，不过因为不确定具体的业务逻辑加上不知道删哪个就没改

3、上载数据模型

整理好数据之后，我们就需要把这些数据添加到 Power Pivot 的数据模型中

由于已经是用 表格 的形式了，可以直接找到添加的按钮：

点击添加到数据模型<删除>使用了水溅跃</删除>

什么都没有发生…

• Power Pivot 有着自己独立的操作界面的
  

顺便这里说一下，数据源不一定是表，也可以是各种格式文件或者数据库。如果需要预处理的话强烈推荐前面提到的 Power Query，储存过程式的处理加上与 Excel 和 Power Pivot 的完美衔接简直酸爽，这里因为<删除>懒</删除>用不到就不展开了

接下来点左边的 管理

或者数据标签下的 管理数据模型

进入Power Pivot 的独立 数据模型管理界面

还是熟悉的布局
还是熟悉的标签

可以在这里修改格式以及 使用 DAX 函数添加自定义列

Power Pivot 的 DAX 函数 涵盖了 Excel 原有的大部分函数及一类 非常非常非常 神奇的函数，这里先卖个关子~

4、数据关联

相信熟悉数据库的小伙伴们对创建关系一定不会陌生

Power Pivot 也可以对上载到 数据模型 的表格建立关系

因为之后会涉及到筛选方向，因此要求

被关联的两列 不能是多对多的关系

由于题主的数据结构相对比较简单

把名称一样的字段连起来之后

可以切到关系图视图欣赏一下自动生成的关系图：

<删除>欧洲细作吃我一矛！</删除>

5、数据透视

回到 Excel 的界面后，像往常一样插入一个数据透视表

不过这次是用 Power Pivot 的 数据模型 作为透视表的数据源

6、创建度量值

重点来了！

重到你都可以跳过第 5 步

• ① 选择 Power Pivot 标签下的新建度量值
  

• ② 根据题主的要求编辑中位数的度量值公式
  

Median(哪张表[哪个字段])

由于题主的困扰的关键点在于

数据透视表中没有中位数这种统计方式

……

现在有了

……

全剧终…

……

什么？你说你还想看玉树临风风流倜傥英俊潇洒才高八斗貌似潘安号称一朵梨花压海棠人送绰号 Excel 小王子的饼干怎么应对中国特色混合式报表？

……

没问题！两种需求一次满足！！

7、还是数据透视

使用 Power Pivot 数据模型创建的数据透视表有一个特点：

你可以在字段列表里看见并选择所有的数据
小图标标题

• 深色代表是已经加载到数据模型的表
  
• 浅色代表是尚未加载到数据模型的表
  

点开可以看到刚才新建的度量

注意

把度量放在 值 这个位置

就像这样

然后我们回到字段列表

直接选另一张表中的字段

接下来，就是见证奇迹的时刻！

中位数时效根据日期拆分了！

本来是想这么说的…

但是

日期自动聚合成月了

然后两个月的中位数时效还一样这点让我很尴尬啊…

心好累…

重新来

把月份去掉并加上节点状态和运营状态

接下来，就是见证奇迹的时刻！

中位数时效根据日期、节点和运营状态拆分了！

这样题主所关心的最主要的问题已经解决了

那么如何更进一步制作一张复杂的中国特色混合式报表呢？

8、拆解报表

首先，我们得了解为

什么要用 Power Pivot 制作报表？

原因很简单

再复杂的中国特色混合式报表 也可以拆解为有限个数据透视表的组合

而能用数据透视表拼出来就意味着

自！动！更！新！

多么美妙的四个字！！

咳咳…让我们再来回顾一下题主的报表结构

之前有分析过从上到下分别是

• 月度数据
  
• 月环比衍生
  
• 目标日数据
  
• 月每日数据
  

我们要做的就是

用 4 张数据透视表分别作出这 4 个数据

其中难度比较大的可能是月环比衍生的 时长环比下降率 这个度量

能坚持看到这没睡着的勇士们啊

还记得之前说过的 非常非常非常 神奇的函数么？

DAX 函数 中有专门的 时间智能函数

来对应着各种环比和 MTD 数据，比如这里的

重算([中位数时效],用日期表[日期列]的上个月的数据)

简直无情！

做完习惯和题主的数据核对一下

完美！完全

……

不一样！？！？！？

……

Excuse Me？

……

吓得我赶紧再建个度量核对一下…

发现原来

题主的中位数时效是截取过 2 位小数的…

这件事告诉我们一个深刻的道理

截取位数可能会对结果造成一定的差异

尤其另一个人做的时候没有截取…

之前查到重复值没有把各公司放款时效汇总放进来

这里因为最后一列的要用到所以手动忽略问题

加进 Power Pivot 数据模型 并同样建立关系和度量值：

9、美化报表

时间过去了20分钟

几块数据都做完数据透视表大概是这样的

实际做报表的时候

也可以留白的更大一些以免各种

意想不到的意外

最后把留白都隐藏再加上切片器就完成了这张报表

Fin

字幕时间打个硬广

知乎专栏

微信订阅号：CookieData

给你的工作效率加个油~