本文共计约3000字，建议阅读时间3分钟目录
本篇开始讲Excel函数与公式的下篇——高级函数。 基础函数请移步Excel数据分析——函数与公式（上） 进阶函数请移步Excel数据分析——函数与公式（中）。 三、高级函数 1、数组函数 “数组”这个概念在编程语言中并不陌生，但在Excel中可能很多人以为自己没接触过，但其实随手可见。 如下动图所示，引用的区域B3:C5其实就是一个数组，在编辑栏按F9，得到B3:C5对应的3行2列的常量数组{1,2;3,4;5,6}。从这里我们可以知道，数组是一对花括号{}把所有元素包含起来，用逗号代表列与列的间隔，分号代表行与行的间隔。  ▴ Excel数组、溢出概念 单单元格数组公式和多单元格数组公式 如果数组只存在于计算过程的内存中，最终输出结果只有一个单元格，那么这就是单单元格数值公式，典型的如SUM函数。 如果返回结果是数组，输出结果是存在多个单元格中，这就是多单元格公式了，典型的如TRANSPOSE函数。由于多单元格数组公式是一次计算产出多单元格结果，比起多单元格的多次重复计算，计算效率是高很多的。 在Excel 2019版本之前的数值公式，需要编辑完成后按下CSE（Ctrl+Shift+Enter）才是正确的（这时公式前后会多出来一对花括号），但现在可以跟普通函数一样，只按Enter即可。因为Excel特别的溢出设计。有了这个就不需要先计算好有结果是几乘几的数组，再选好同等大小区域来应用数组公式了。 没有花括号的单单元格数组公式，看起来跟普通公式毫无二致。比如上文提到的求最高三项平均值，公式是=AVERAGEA(LARGE(E3:E12,ROW(1:3)))，就是先利用ROW(1:3)产生一个{1,2,3}的常量数组，作为LARGE函数的第2个参数，取出区域中的TOP1、TOP2和TOP3，再对这三个值求平均。再比如查找与应用函数中的第一个例子，单列查找结果多列，公式=XLOOKUP($G5,B$2:B$26,C2:D26)，输出的是1行*2列，也用到了溢出，不用考虑旁边各自的公式要怎么写了。 多单元格数组公式具有同一性，因为数组本来就是按相对位置计算的，所以绝对和相对引用的区分就没那么重要了。只需要在左上角编辑一个公式，Excel就会自动溢出到周边单元格，整个数组公式区域用一个发光的边框框起来。事实上只有数组的左上角单元格可编辑，其他单元格的编辑栏置灰不可编辑。 那么本小节的数组公式，就着重讲多单元格数组公式的应用了。
数组公式应用示例 方框为数组公式区域，浅绿色单元格为数组公式可编辑单元格  ▴ 数组公式应用示例 2、VBA自定义函数 若你需要经常做某种计算，而用Excel自带公式实现，步骤很多容易出错，建议你把计算逻辑固化为自定义函数。 我曾经编辑过一个函数，实现功能是：将一个指标值映射到Ymin-Ymax之间，也就是指数化。具体功能是，指标值大于Xmax时，Y取Ymax，指标值小于Xmin时，Y取Ymin，指标值在Xmin-Xmax之间时，Y在Ymin-Ymax之间线性分布。 '自定义函数VBA代码brFunction zs_score(low As Double, high As Double, weight As Double, value As Double, up As Boolean)br flag = 1br If up = False Thenbr flag = -1br End Ifbrbrbr If value * flag >= high * flag Thenbr zs_score = weightbr ElseIf value * flag <= low * flag Thenbr zs_score = 0br Elsebr zs_score = weight * (value - low) / (high - low)br ' MsgBox 'hi'br End IfbrEnd Function自定义函数唯一不方便的就是改格式。我的解法是：把包含函数的Excel发布为xlam加载项，每次启动Excel时自动加载函数，这样函数就在任何地方都可用了。不过，如果Excel发给了别人，别人的Excel没装加载项的话是无法识别自定义函数的。要么把加载项一起发给对方，要么把公式结果转成值（如果公式本身不重要结果值更重要的话）。 3、其他场景下的函数应用 条件格式+函数公式实现甘特图效果 从“Excel文件”|“新建”，在输入框内搜索“甘特”，可以找到如下图所示的甘特图模板。这个甘特图就是通过条件格式+函数公式实现的。  ▴ Excel甘特图（条件格式+函数实现） 从“开始”|“样式”|“条件格式”|“管理规则”，可以看到条件格式的具体规则。  ▴ 查看条件格式的具体规则 点击“编辑规则”，可以看到，条件格式规则的规则类型是“使用公式”  ▴ 编辑条件格式规则  ▴ 使用公式编辑条件格式规则 公式具体如下： 已完成（灰色底纹）条件规则公式为： =AND(task_start<=I$5,ROUNDDOWN((task_end-task_start+1) *task_progress,0)+task_start-1>=I$5) 计划完成（紫色底纹）条件规则公式为： =AND(task_end>=I$5,task_start<J$5) 这里定义了三个名称，分别是task_start、task_end和task_progress，这三个名称在应用到每个条件格式单元格时公式是相对引用（行变列不变），以应用在I9单元格时这三个名称所对应的引用区域为例，如下所示。 task_start=项目日程安排!$E9 task_end=项目日程安排!$F9 task_progress=项目日程安排!$D9 此外，公式还可以在数据有效性、图表等多个场景下应用，就不一一介绍了。 4、关于函数使用的建议 我有一个理念：一共工具只要有逻辑，就一定能够为我所用，只不过是不同场合不同需求罢了。 这可能跟技多不压身是一个道理吧。但我这里更想强调的是，我们学一个工具或者技能，本质上是在学其中的逻辑。 回到Excel函数上来说，我们要学习的不是几个函数在几个特定场景下的示例，而是学习各个函数的特性，当没有现成可用的函数的时候，如何利用函数特性来构造自己需要的函数。理解函数本质特性才能灵活应用，这也是理解为什么有一些特定的组合出现。 举个例子。 问题： 以下是上万条线路的妥投情况，需要知道每个线路是在第几天妥投率超过60%。  ▴ 函数构造应用示例 解法： 简单做法是从第一天开始筛选60%的，大于60%就标个对应天数，然后后边的每一列都基于前N天为否的继续筛选。无非就是繁琐而已，当天数很多时效率必然低下。 如果应用函数来解决，需要把问题拆解为两步：
我们如果充分理解函数原理，公式就还可以继续简化。 公式=11-COUNTIF(C6:L6,'>60%')，即总天数10减去大于60%的所有天数+1，即开始大于60%是在第几天。 其他一些使用函数的建议
|
|
|
