序言

上周为公司的月度保费报告做了一些升级调整，主要是涉及产品保费量展示部分由以前的静态展示固定产品保费改为按保费量动态展示产品排行。其中涉及的Excel公式技巧包括但不限于OFFSET应用，动态排名展示，合并单元格文字等。

原工具中有三张表格，其中有互相参考验证数据正确性的Tracking cells，这里面我只讲稍微复杂一些的最后一张，且数据经过随机数扭曲，所以本文中展示的表格中的Tracking cells不会正常显示。

正文

该工具的最终目的是使用系统中生成的原始数据利用excel的公式功能自动整理计算数据并以要求形式展示。工具的目的是为了减少人力成本，因此一个原则是尽最大可能减少工具使用人在工具定板后使用时投入的成本，在此原则之上再尽力减少制作工具本身需要的时间和精力成本。本篇中我会分别介绍这个工具的版面设计以及从已有数据结构到需求的展示样式的逐步实现方法以及其中设计的一些可以广泛应用的小技巧。

工具版面设计

我设计Excel工具时一个习惯的guideline是将数据分为至少三个部分：

不改变原数据的结构，以所得即所用的原则组成一个数据输入区，用这样的数据做成的工具可以减少该工具在后续使用中对使用人“调整原数据”的需求，从而降低使用成本。
将数据以标准形式整理并计算的数据处理区，这个部分通常会包含标准化的数据分类，这样做的目的是将原数据在该区域内透明地以标准化方式整理计算，以方便后续数据展示，并使各部分公式易于阅读和后续维护修改。
最后是数据的输出形式，即数据展示区，这个部分通常是按数据使用人的需求调整，也即在工具制作期间制作人通常不能控制的部分，我们最后的目的就是将处理好的数据按使用人需求展示。

本工具的版面既是根据上述习惯排版：

工具版面分为“数据输入区”，“数据处理区”和“数据展示区”三个部分

工具设计思路

在开始设计工具之前需要理清最重要的两点，第一是到手的原数据结构，第二就是最终需要展示的信息。在这个案例中，我们提取出来的原数据为月度数据，包含信息有4列，分别是险种，新/续业务，本/上年信息以及保费本身。其中每个险种分别会有本/上年的新/续业务保费，这样2x2=4种的组合。因此可以知道标准格式中只需要包含所有可能险种数x4行既可以展示所有可能的输入组合。

此时开始分析需要展示的信息，所需展示的信息可以分为3部分：首先是将险种按当前年度总保费的大小排名并展示前8个险种，将此之外的保费信息加总归入Other类。其次将各月保费按需求的时间维度分组加总展示(如月度，季度，半年度，当前合计等)。最后只需要动态地将未展示出来的险种名合并展示作为备注即可。

为满足该需求，我的设计是将保费性质信息作为标准化维度放在数据处理区的最左边并以月份为列名排列所有数据输入区的数据，排列后在处理区将各险种按当年保费(新续业务加总)排出大小并标识，最后在展示区以OFFSET为引用处理区不同月度数据的方法，以降低制作工具本身所需的时间，提高公式的可读性和修改弹性。

实现方法

此章中我不会事无巨细介绍所有出现的技巧，如果想要详细了解建议在最后链接中下载该工具样本自己研究，我的公式应该都是trackable的，这里我只会分别介绍几个可以广泛应用到其他需求中的方法和公式。

如何跳行/列引用数据

这是制作该工具时我使用的第一个小技巧，答案非常简单，既是OFFSET。在工具中可以看到，原数据中每月数据有4列信息，而数据处理区中我们只希望每1列展示一个月信息，这样导致的问题是如果不使用OFFSET而将处理区中的SUMIFS公式直接向右复制，则会使每个引用信息仅向右移动1列，从而导致引用区域错位。因此我们需要左边处理区每向右移动1列，公式中的引用区同步向右移动4列，才能将所有引用信息移动至原数据中的下月中相应区域。

以处理区中最左上角的公式为例：

=SUMIFS(OFFSET($VV$177,,EUU$177,,ED30,OFFSET($TT$177,,EC30,OFFSET($SS$177,,EB30)/1000000

=SUMIFS(OFFSET($V $30:$ V$177,E $28,,),OFFSET($ U $30:$ U$177,E $28,,),$ D30,OFFSET($T $30:$ T$177,E $28,,),$ C30,OFFSET($S $30:$ S$177,E $28,,),$ B30)/1000000

该公式除去OFFSET便是非常简单的SUMIF公式如下，即将原数据中的信息按标准模板加总排列：

=SUMIFS($V $30:$ V $177,$ U $30:$ U $177,$ D30,$T $30:$ T $177,$ C30,$S $30:$ S $177,$ B30)/1000000

那么OFFSET是怎么用的呢？OFFSET函数本身的作用就是以公式参数的方法来移动改变引用区域，他的参数如下：

=OFFSET(Reference, rows, cols, [height],[width])

其中Reference为引用的区域，后面的四个参数分别代表在Reference的基础上要移动的行数，列数，高度，宽度，正数代表向右/向下，负数反之。

以我们的公式为例，OFFSET($V$30:$V$177,,E$28,,)即代表将$V$30:$V$177移动E$28标注的列数(此处为0，即不移动)，其他参数未设置则用,隔开。到这里我只需要将每个月的列上以等差数列向右排列引用所需移动的列数，即可以一个公式为每个月需要引用的原数据找到正确的引用位置。同样的方法在数据展示区也大量的使用，因此可以用一个公式以改变最少参数的方法来填入表格中。

OFFSET这样的公式非常明显，是为了减轻工具制作和维护的成本而存在的，因为用到不同的公式越少，后期维护时就越简单，毕竟这里你也可以选择人手将不同月份的引用区域拖动到他们合适的位置，不过制作和维护的成本将大大增加。

仅对当前第一次出现的元素计算

我们想要将产品按当前合计保费量排名，也即将当年的新/续保费加总并排名，但我们却不能直接使用SUMIF加总，因为按照我的版面设计，如果直接全部加总排行时每个产品会有两个相同的保费，从而影响排行名次。因此我们要想办法使每个产品名后仅出现一个加总保费。好在我们的数据处理区是标准化设计，上半部分全部是当年数据且每个险种按NB-RN的顺序规律排列。因此我们可以是用公式从上至下仅仅对该元素第一次出现时进行计算。

=IF(COUNTIF($BB30,$B30)=1,SUM(OFFSET(E30:P30,,,2,)),0)

=IF(COUNTIF($B $30:$ B30,$B30)=1,SUM(OFFSET(E30:P30,2,)),0)

这个公式分为两个部分，其中IF部分为重点，即判断语句COUNTIF($B$30:$B30,$B30)=1。需要注意其中$B$30:$B30中:后的B30仅仅固定了列数，因此该公式在下拉时该引用区域也会相应拉长。这就使得这个COUNTIF公式可以实现计算从上到下到目前为止B列该元素出现过的次数。而此时将判断语句写为该COUNTIF为1则表示从上到下第一次出现这个元素时，则计算后面的SUM(OFFSET(E30:P30,,,2,))，否则返回0。

这个SUM函数中的OFFSET 的height参数被设置为2，因此加总包括该引用处在内的向下共2个单元格。以此实现每个出现的产品进行一次NB和RN的数值加总。

将出现过的数字按大小排名

上一步中我们已经得到了每一个产品的当年总保费，那么使用RANK.EQ函数既可以对保费量进行排名从而使每个险种得到一个相应的排名。本来此时在展示区左边我只需要手动输入1,2,3便可以自动展示排名为1,2,3的产品名，但此处有一个特殊需求，那就是由于下架，要将PAM这个产品的除出排名，直接并入Other分类。但由于PAM还是有续保保费，因此它每个月的保费量也并不是固定的，虽然我可以使用IF函数将PAM的排名直接设定为999，但我们依然需要一个可以动态将已有数字从小到大排列的方法，否则会出现的问题是假设PAM实际排名是6，但它被公式设定为排名999，这会导致手动输入的6后面将没有可以引用的数据，我们需要自动将上方排列设置为4,5,7来跳过排名为6的PAM。

我想到的办法是SMALL公式：

=SMALL($AA$103,ROW(A2)/2)

=SMALL($A $30:$ A$103,ROW(A2)/2)

该公式可用参数为SMALL(array,k)，其作用既是返回该数据中第K个最小值，同理的还有LARGE函数。

在这里的使用方法既是展示在数据处理区中出现的排行数中排行第ROW(A2)/2的最小值，使用ROW(A2)就可以使该K值随着公式的下拉而增加，ROW(A2)返回的是A2单元格的行数，也即2。该数值/2则可以使每个数值出现两次，以此匹配每个险种名显示两次以匹配NB和RN这两行数据，因为A2和A3两行的计算结果分别是1和1.5，都会被公式向下取整认定为1，同理如果希望数值重复N次，则/N即可实现，注意不要让商小于1。

IF公式的数组应用

最后的一个展示要求是将Other分类中，也即保费排名未在前八位但本年有保费收入的产品名备注出来:

=CONCAT("*The Other Products contains ",IF(($QQAAAABB$103,3)&", ",""))

=CONCAT(“*The Other Products contains “,IF(($Q $30:$ Q $103<>0)*($ A $30:$ A $103>MAX($ A $6:$ A $21)),LEFT($ B $30:$ B$103,3)&”, “,””))

这个公式中的主要知识点便是IF公式的数组应用

IF(($Q $30:$ Q $103<>0)*($ A $30:$ A $103>MAX($ A $6:$ A $21)),LEFT($ B $30:$ B$103,3)&“, “,””)

该公式的重点，也是难点，便是条件语句部分的($Q$30:$Q$103<>0)*($A$30:$A$103>MAX($A$6:$A$21)),此处的乘法操作是为了筛选出该范围中同时满足两个条件的行数，因为Excel中所有1等价于TRUE，0等价于FALSE，因此此时两个判断语句的结果每行相乘，只有两个判断均为TRUE时，产生的结果才是TRUE。（0乘0或0乘1都是0这个很好理解吧）这时IF函数返回的数组既是所有同时满足这两个条件的行(两个1相乘才返回1)，即当年总保费不为0且排名大于展示区所有排名的产品代码。最后用CONCAT函数便可以将数组返回的数据组合在一个单元格中。