使用了大量数组公式导致Excel卡死的解决方案

atpydr

知乎上有这么一个问题

原始数据是10列*近8万行的一维表，按要求需要生成一张交叉表，所求的值是中位数。access或者透视表都没法求中位数，所以我只能写公式。但是这个交叉表总共有近3000个单元格，用了近3000次数组公式，所以一运行电脑就几近死机…有没有更好的解决方法呢？
---------------------------------------------------

想了一下还是想用VBA代替数组公式实现功能，因为不是很想破坏现在整个报表草稿的结构…（好不容易搞出来的…）然而我之前只录过几个简单的宏，看了一下午VBA教程也没搞懂该怎么做…有没有dalao能指点一下？

咳咳

可能饼干之前几篇干货写得太干了

结果跳出率有点高 。。饼干心里苦 。。

（链接放在文末了）

这次饼干要借这个问题和大家一起聊一聊

做报表的正确思路和方法

分析问题

题主的问题是：

原始数据是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

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