一篇把Excel玩出花来的折腾笔记，涉及数据可视化、AI工具、统计学、Excel公式。不用担心，我会以数据小白的角度来写，最基础的概念我都会解释。
不少宝爸宝妈使用育儿App来记录宝贝的生长，追踪身高体重变化。实际上，育儿App那么多功能，我也就用这一项，就为这个就要在手机上装个大几百Mb的App，这让我动了卸载的念头。我也不是真缺这几百Mb，只是忽然意识到，这也是个练手的好机会。不就是个数据分析工具嘛，我万能的Excel会搞不定？
系统规划
动手前，想清楚这事情该怎么做。首先来看看育儿App的生长曲线是怎么回事。
这是宝宝树的儿童生长曲线。中间的50%线是中位数，如果我宝贝的身高（体重）刚好落在这条线上，说明这个月龄比她高（重）的宝贝和比她矮（轻）的宝贝人数大概一样多。往上的75%线和97%线，表示这个位置身高（体重）超过75%和97%的同龄宝贝，往下的25%和3%同理。看宝贝的数据点落在什么位置，大概就知道她生长状况相对整体如何。
我要的也是一个类似的分析工具，它应该具有以下能力：
- 能记录宝贝每次测得的身高体重
- 能查询各月龄的身高体重正常范围
- 能清晰表达我宝贝各月龄身高体重偏离正常范围的程度
至于这东西是不是个图表、有没有曲线，不重要。重要的是第3点，它的计算能力，能衡量偏离程度，并用一种直观的方式表示出来。这一点我认真构思了一下，觉得比较适合的表现形式是有两个方向的条形图，类似这种：
这类图表叫做diverging bar chart，不知道中文叫什么。它可以把两组数据在同一个维度上两两对比。
如果只用来表达一组数据，它反映的就是该数据围绕某个基准值的方向及距离，最常见是表达正负。
这很适合用来表示我宝贝的生长数据，以参考值的中等水平作为基准值，表现女儿的身高（体重）是偏低了还是偏高。至于偏离基准值多远，图表用柱子长短来表达，柱子长短的差异有时不是那么明显，我觉得应该进一步简化，只使用符号。低于基准值用减号，高于基准值用加号，偏离越多符号就越多，这样当我看到三、四个加号（+++）减号（—-）时，就知道宝贝的生长趋势该引起重视了。
准备数据
有了具体目标，该开始干活了。先实现前2项能力：
- 能记录宝贝每次测得的身高体重
- 能查询各月龄的身高体重正常范围
宝贝生长数据
宝贝的身高体重数据存在宝宝树App里，形式如图（月龄的左边还有一行日期，不想暴露女儿生日，没截进来）：
宝宝树没有数据导出功能。虽然我可以一条条手动输入到Excel，但难道不该用聪明点的办法吗？
我先把宝宝树里的记录一屏一屏截下来，用了一个叫Screen Master的Android应用拼成长图。
然后使用白描OCR工具（https://web.baimiaoapp.com/）从长图中识别出文字，得到如下右侧结果：
这样格式错乱混在一起，乍看没法用。但在AI时代，这都不是事儿。
搞定！复制到Excel即可。补充一下，表里的日龄、月龄、年龄是用记录日期减去女儿生日得到的，自动计算无需手填。
正常范围标准
各月龄的身高体重参考值，在卫健委的网站可以找到。2022年发布的标准，还蛮新的，编号是WS/T 423—2022，跟宝宝树同一个数据源： http://www.nhc.gov.cn/fzs/s7848/202211/8b94606198e8457dafb3f8355135f1a3/files/e38068f0a62d4a1eb1bd451414444ec1.pdf
里面找到了格式如下的数据，正是我要的：
稍微解释下这个表格的意思。中位数前面讲过，这里最关键的是看懂这个“SD”，Standard Deviation，标准差。这是个非常基础的统计学术语，在解释标准差之前，我们需要先了解正态分布。要知道，卫健委统计的儿童身高体重，样本量一定是非常大的，也就是说测量了很多很多儿童的身高体重。身高体重这种随机产生的数据，只要样本量够大，每个儿童的数值就会围绕平均数（这里它用的是中位数，与平均数应该很接近）呈正态分布。这是正态分布的样子：
横向是身高（体重）的值，由小到大，纵向是该身高（体重）对应的儿童人数。中央的垂直虚线代表中位数，绝大多数儿童的数据落在中位数附近，说明还是中等水平的儿童最多。越往两边去，人数越少，说明身高（体重）值特别低或者特别高的人很少，情况越极端，人数越少。
现在说回标准差。我们不谈公式，不做计算，不必关心它怎么来的，我们关心的是标准差和正态分布的关系。
标准差体现在正态分布图上，就是每两根垂直虚线间的距离，这些虚线是等距的。怎么理解标准差？它是正态分布的一把标尺，通过标准差，我们可以准确知道某个范围内的数据占总数的比例。比如我们可以说，有68%的儿童，身高（体重）在中位数上下一个标准差范围内。有95%的儿童，身高（体重）在上下两个标准差范围内。
要注意它名字里有“标准”二字，这两个字可不是随便说说的。标准差是正态分布的一个独特性质，不同的数据集算出的标准差数值可能不一样，但比例却是一致的。只要是正态分布，它1个、2个、3个标准差范围对应的占比就一定是68%、95%、99.7%，这就是神奇的地方。生活中各种各样的随机数据，都会呈现正态分布。所以只要我们知道了平均值（或中位数）和标准差，就可以知道手上任何一个数据在整体中所处的位置。
现在，回来处理数据，把卫健委表格复制到Excel，年龄全部折算成月龄：
表格里列出了每个月龄儿童身高（体重）中位数是多少，低于和高于中位数1、2、3个标准差位置的数值分别是多少。这就是我要的判断依据，知道女儿的身高（体重）在同龄宝贝里处于什么位置，相对于中等水平偏离得严不严重。
绘制曲线
接下来，要啃硬骨头了，来实现第3个能力，“表达我宝贝在相应月龄身高体重偏离正常范围多少”，这是实打实的Excel技巧。
现在我的Excel里有两张表格，一张记录着我宝贝各月龄的数据表，一张列出各月龄的正常值范围的参照表。我要做的是在宝贝数据表里新增几列偏离列，在里面查询参照表，得出偏离程度，以加减号的形式表现出来。减号写在左列，靠右对齐；加号写在右列，靠左对齐。这就实现了简化版diverging bar chart。
匹配对照月龄
这个事情想想是不难，不就是拿vlookup去查嘛，月龄对上，然后一堆If嵌套对比数值大小，输出符号，肯定能搞定。
一动手发现没那么简单，因为卫健委表格的月龄有断档：
它从2周岁开始，每3个月才出一行数据。这很合理，宝贝过了2周岁后，生长确实没有婴儿时那么快了，没必要那么频繁去追踪。但这影响到我的查询方法，如果我在宝贝25月龄的时候记录身高体重，直接用vlookup去查，什么也匹配不到，后续的计算便无从谈起。
此时有个土办法，规整数据，手动补全参照表。把缺失的月龄加上，用更小月龄的参照值来填充。比如把25、26月龄的参照标准都填成24月龄的。
但这是练手项目啊，拒绝土办法。我要在宝贝数据表里实现智能匹配！
于是再增加一个隐藏列，用来计算每行的月龄对应参照表里多大月龄。
这一列的公式如下：
=IF(ISBLANK(A2),"",INDEX('生长对照表'!A$3:A$46,COUNTIFS('生长对照表'!A$3:A$46,"<="&C2),0))
翻译成人话是：先查日期列是不是空的，它空我也空。如果不是，就去参照表里数一数比宝贝月龄小或者相等的有多少行，这就实现了向下匹配。
在2周岁以前，月龄与对照月龄一定是相同的。我手动测试了一下，25月龄时如果有记录，它会匹配24月龄作为参照。
计算偏离程度
有了对照月龄列，不担心参照表匹配不上，现在可以放心在偏离列里做计算了。
以身高偏低列的公式为例：
=IF(ISBLANK(F2),"",IF(F2>VLOOKUP(E2,'生长对照表'!A$3:O$46,12),"",IF(F2=VLOOKUP(E2,'生长对照表'!A$3:O$46,12),"=",REPT("-",5-RANK(F2,{F2,VLOOKUP(E2,'生长对照表'!A$3:O$46,11),VLOOKUP(E2,'生长对照表'!A$3:O$46,10),VLOOKUP(E2,'生长对照表'!A$3:O$46,9)},1)))))
啊……这个公式就有点丧心病狂了，我要先拆解一下再翻译。从外向里看，分为3层：
第1层
=IF(ISBLANK(F2),"",IF(F2>VLOOKUP(E2,'生长对照表'!A$3:O$46,12),"",IF(F2=VLOOKUP(E2,'生长对照表'!A$3:O$46,12),"=",第2层)))
这部分先看身高列是不是空的，它空我也空。如果不是，就开始把它与参照表对比，查出对应的身高中位数是多少。如果高于中位数，这列留空（这列专填减号）；如果等于中位数，写个等号“=”；如果低于中位数，就进入第二层，输出一定数量的减号。
第2层
REPT("-",第3层)
原本是打算用一层又一层的 If 条件判断来决定输出几个减号，后来想想这方法也有点傻。这就是简单的方法，Rept函数可以把一个字符串重复输出一定次数。现在问题甩给第3层，计算要输出减号的数量。
第3层
5-RANK(F2,{F2,VLOOKUP(E2,'生长对照表'!A$3:O$46,11),VLOOKUP(E2,'生长对照表'!A$3:O$46,10),VLOOKUP(E2,'生长对照表'!A$3:O$46,9)},1)
此处用到一个Excel隐藏技巧：数组。Excel公式里引用一个范围，这就构成一个数组，我们大多数时候就是这么用的。但你知道吗？可以像编程软件那样，在Excel里手动创建数组，关键就是这个大括号 {} 。比如 {1,2,3,4} 在Excel公式里就等效于这个：
但数组的用法更灵活，可以手动把八竿子打不到一块的数据凑在一起。单看 {} 里的内容：
{F2,VLOOKUP(E2,'生长对照表'!A$3:O$46,11),VLOOKUP(E2,'生长对照表'!A$3:O$46,10),VLOOKUP(E2,'生长对照表'!A$3:O$46,9)}
我这个数组，把宝贝的身高（F2）和-1、-2、-3个标准差的身高值放在一个数组里。
RANK(F2,数组,1)
然后用Rank函数做个排序，得出宝贝身高在这4个数值里从小到大排第几。最后再用5减去这个数字，就得到减号的数量。至于为什么是用5减，这是个数学问题，不展开，但分情况想想就很容易理解了。
用类似原理，改出另外3个偏离列的公式，效果立竿见影。几个符号表示宝贝的数值在几个标准差范围内。根据正态分布的特征，95%的儿童生长数据都在2个标准差范围内，所以看到2个符号时，我没什么需要担心的，目前为止宝贝一切正常。
数据可视化
既然要做数据可视化，就要让值得留意的数据更显眼，一目了然。加减号的效果稍微糙了点。
其实用不着多复杂的图形设计、高级渐变色之类的。要突出异常值，只需要用区别足够明显的符号代替加减号，再简单写个条件格式，用背景色区分就能达到目的，我自己用足够了。
3个符号它代表宝贝的数值低于或高于95%的同龄儿童，需要引起重视了，用黄色。4个符号表示低于或高于99.7%的同龄儿童，用红色。我手动改了几个极端值出来，实际效果如下：
后记
搞定，收工！现在可以把育儿App卸了，愉快按下叉叉按钮。
这类生长记录小工具，我相信有很多现成的，但自己创造的乐趣是它们无法替代的。像其中的数组、Rept函数、Rank函数，都是现学现用，收获很大。这里面最有意思的部分其实是前期的规划构思，真正动起手来，整个过程1小时就搞定了。
它印证了多种知识、工具、技巧相互组合的威力。见招拆招，总能有效解决问题。
最后说明一下，我们用的真的不是同一个Excel，我更喜欢用Google Sheets。如果想要在Excel里重复我的实验，未必能成功。可能少数细节要变通一下，但两者的公式和用法是高度一致的。
【2024.1.18 更新】 有些朋友想要表格文件，我亲自尝试了下，转成Excel后部分公式无法正常工作了。因为Excel并不支持把数组常量作为rank的引用范围，而且数组常量里也无法引用其他单元格。所以Office的Excel做这个会相对麻烦，估计得一堆if嵌套了，还是建议大家有条件就用Google Sheets。
我把这个表格做了两个可供取用的版本（男宝/女宝），你需要魔法上网，且有Google账号，点击【File - make a copy】就有一份在你自己的Google Sheets里了。
男宝版：
https://docs.google.com/spreadsheets/d/11D3oxCzVtGosAuySidwDeHgoMauo8_b4EhAcirQHNUg/edit?usp=sharing
女宝版：
https://docs.google.com/spreadsheets/d/1zBMY5MfzmSSqHRrG0yu9Nz-81xkNjJAvximXY9fgVlU/edit?usp=sharing