函数技巧辅助列能给函数应用带来什么
⇨⇦
编按：哈喽，大家好！不管是在小说，还是电视剧中，主角们都有着一招制胜的法宝，这让他们每次都能＂逢凶化吉＂。那么回到职场中，每一位EXCELER在面对各种各样的问题时，能不能也有一招能＂碾压四方＂的绝技呢？赶紧来看看吧！
＊＊＊＊＊＊＊＊＊
【前言】
在武侠小说里，经常会有一种情况——带着主角光环的人，总是凭借＂一招鲜，吃遍天＂的＂伟大漏洞＂，处处逢凶化吉。都说＂职场如战场＂，来到了职场，每一位EXCELER，面对来自四面八方的工作压力，也算是＂处处逢凶＂了。那么能不能也有这么＂一招＂，让同学们＂随时化吉＂呢？跟上E图表述的步伐，来学习一招＂以不变应万变＂的绝技吧。
【正文】
不多废话，切入主题。同学们知道在日常表格中，有一种信息内容叫做＂序号＂吗？我们今天就来＂玩＂这个序号，我保证你会＂爱＂上这个＂小操作＂的。
添加序号的方法
在日常生活中，我们每个人添加序号的方式可能都不一样，主要归纳起来有下面几种。
这是较常规的几种添加序号的方法，但是也仅限于＂常规＂。而我们今天要学的内容，就是非常规的序号，而这样的序号可以给我们带来＂前所未有且简单＂的体验。
一、＂动态＂提取不重复的值并统计
遇到这种情况，大部分同学会使用两种方法解决：
第一种：首先复制粘贴出E列内容，然后在＂数据＂选项卡中，点击＂删除重复项＂功能键，再用SUMIF函数求和。
B22单元格函数：
=SUMIF($E$2:$E$16,A22,$F$2:$F$16)
第二种：直接使用数组函数，得到不重复的费用类别，再使用SUMIF函数求和。
A22单元格函数：
{=IFERROR(INDEX($E$2:$E$16,SMALL(IF(MATCH($E$2:$E$16,$E$2:$E$16,0)=ROW($1:$15),ROW($1:$15),99^9),ROW(A1))),＂＂)}
输入完成后，按数组函数的结束键CTRL+SHIFT+ENTER三键结束。
你会选择哪种呢？如果说我们需要在源数据更新后，也能实现自动统计的话，肯定是选择函数的做法，但是这个数组函数真的不是初学者能够驾驭的（上面的数组函数不是今天的主题，故不作展开说明），下面我们就用序号的方法来处理这个问题。
步骤1：在数据首列前插入一列＂辅助列＂，在A2单元格输入函数：=IF(COUNTIF($F$2:F2,F2)=1,MAX($A$1:A1)+1,＂＂)，下拉填充得到被引用的序号。这里使用COUNTIF函数结合绝对引用，使区域中的首个单元格固定，末单元格逐步扩大，通过IF函数判断，如果COUNTIF返回1，即为目标值第一次出现，再使用MAX函数结合绝对引用，累加出唯一出现的值所对应的顺次；
此类引用方法在之前的教程中介绍过，小伙伴们可以点击教程《同样是countifs函数，为什么同事却使得比你好？原因在这里！》学习，此处不做赘述了。
步骤2：在B22单元格输入函数：
=IFERROR(VLOOKUP(ROW(B1),$A$2:$F$16,6,0),＂＂)，下拉填充函数后，引出不重复的费用类别。使用ROW函数得到序号，用VLOOKUP逐步引出这个序号对应的费用类别，当ROW函数的序号在索引区域中没有出现时，用IFERROR函数规避错误值#N/A；
步骤3：在C22单元格输入函数：
=IF(B22=＂＂,＂＂,SUMIF($F$2:$F$16,B22,$G$2:$G$16))，当对应的B列内容不为空时，汇总各个费用类别的金额。
这样一来是不是简单了很多？而且是随数据源的更新而变动的哦~
二、＂动态＂分类提取明细
按照上面的思路，我们再看一个工作中经常会遇到的问题。如下图所示，我们需要按照不同的费用类别，提取出对应的明细数据。
步骤1：同样在数据首列前插入空白列。在A2单元格输入函数：=IF(F2=$G$19,MAX($A$1:A1)+1,＂＂)，下拉填充公式，得到满足条件的记录序号。当数据中的费用类别和被统计项G19单元格（注意使用绝对引用）相同时，利用MAX函数标记序号。
步骤2：在B22单元格输入函数：
=IFERROR(VLOOKUP(ROW(B1),$A$1:$G$16,MATCH(B$21,$A$1:$G$1,0),0),＂＂)，下拉右拉填充公式。这里依然是使用VLOOKUP函数索引ROW函数，再利用MATCH函数，找到表头的顺序号，作为VLOOKUP在索引区域中被索引的列序。当然，这个表格依旧是可以实现动态更新的。
三、＂动态＂多条件提取明细
同样的思路再来＂玩＂一个多条件的索引。如下图所示，需要提取出满足多个条件的明细数据。
步骤1：同样在A2单元格输入函数：
=IF(AND(C2>=$D$21,C2<=$D$22,E2=$F$21,F2=$F$22),MAX($A$1:A1)+1,＂＂)，使用AND函数，使IF函数形成多条件同时满足与否的判断，标记出多条件都满足的明细记录序号。
步骤2：在B25单元格输入函数：
=IFERROR(VLOOKUP(ROW(B1),$A$1:$G$16,MATCH(B$24,$A$1:$G$1,0),0),＂＂)，此函数同案例2的函数是一样的，我们就不多介绍了。看一下动态的效果吧。
四、＂动态＂将明细按金额大小排序
给一列数字排名，估计很多同学都知道用RANK函数吧，但是如果有重复的数字，就需要RANK+COUNTIF函数的嵌套使用，起到不重复排名的效果，这个案例就是利用了这个原理。
步骤1：在A3单元格输入函数：=RANK(D3,$D$3:$D$17,IF($H$1=＂降序＂,0,1))+COUNTIF($D$3:D3,D3)-1，先通过RANK函数得到数值的排名，用IF函数判断H1单元格的数据，如果是＂降序＂则返为＂0＂，如果是＂升序＂或者单元格为空，则默认返回＂1＂。COUNTIF函数是去重排名的关键，同样是利用绝对引用固定区域中开始单元格的位置，确定数字出现的次数，如果第一次出现，1-1=0，则直接返回RANK函数得到的排名。第二次出现就是2-1=1，则在RANK函数排名的基础上+1。这样就完成了重复排名增加1位次的过程。
步骤2：在F3单元格输入函数：
=IFERROR(VLOOKUP(ROW(F1),$A$2:$D$17,MATCH(F$2,$A$2:$D$2,0),0),＂＂)，这个函数的原理依然同案例2的索引过程。
通过选择升序降序的选项，做到动态罗列数据的过程，如下：
五、＂动态＂插入空白行
对于这个需求，现下最常用的方法是＂添加序号排序的方法＂，如下：
但是这种方式有一个弊端，如果插入的空白行不固定，那就需要频繁的操作，而且序号的粘贴过程也比较麻烦，插入几行就要复制几次序号。所以我们就一起来看一个函数+序号动态插入空白行的案例。
步骤1：在A列给源数据表添加序号，在A3单元格输入函数：=ROW(A1)，然后下拉公式填充。
步骤2：在F2单元格输入＂辅助＂，在F3单元格输入函数：=IF(COUNTIF($F$2:F2,F2)<$I$1+1,MAX(F2:F2),F2+1)，依然是利用了COUNTIF结合绝对引用的做法，判断从起始单元格F2到上一个单元格为止的区域中，上一个单元格的值出现了几次，如果值小于间隔行数+1，就显示已经出现的最大序号（用MAX函数引出），否则序号累计1，就是函数中F2+1的运算。然后下拉填充函数，如果数据较多，这里介绍一个利用＂名称框＂代替下拉填充的过程。
<小常识>：在一个单元格中输入函数，然后选择＂名称框＂，输入需要填充的区域地址，按回车键，再按CTRL+D（向下填充），也可以按CTRL+R（向右填充），即可完成对区域的填充了。<!--小常识-->
步骤3：在G3单元格输入函数：
=IFERROR(IF(AND($F3<>＂＂,$F3<>$F2),VLOOKUP($F3+1,$A$2:$D$17,MATCH(G$2,$A$2:$D$2,0),0),＂＂),＂＂)，使用AND函数，使IF函数形成多条件同时满足与否的判断。当F3单元格不为空，且F3单元格不等于上一个单元格的值的时候，使用VLOOKUP+MATCH函数的嵌套引用明细表内容。填充函数后就是下面的效果了。
<小常识>：上面的5个案例都是一个数据源，5类数据处理的需求我们只用了一招＂添加序号＂的方式，轻易破解，其实这个＂添加序号＂就是我们经常说的＂辅助列＂。<!--小常识-->
＂辅助列＂在原数据中，一般在首列之前，或者末列之后。目的是在不改变原数据结构的基础上，为了解决一些不能通过原数据直接处理的数据分析要求，而添加辅助计算的信息内容。
【编后语】
在这里，作者E图表述建议大家：如果大部分的情况，你都是使用函数来解决工作中的问题的话，＂使用辅助列，不丢人＂！很多人为了练习函数，甚至一些练习题，都是要求一步写出函数，但是＂学习＂和＂工作＂一定不要产生羁绊，学以致用没有错，但是你的领导不会等你学会了再安排工作。在工作上，永远是要效率第一。只有完成工作，你才有更多的时间去＂学习＂。今天的内容，希望能够在工作上帮你提速，能够有更多的时间来＂部落窝＂继续深造。
＊＊＊＊部落窝教育－excel辅助列应用技巧＊＊＊＊
原创：E图表述/部落窝教育（未经同意，请勿转载）