excel函数技巧:辅助列能给函数应用带来什么便利
![excel函数技巧:辅助列能给函数应用带来什么便利](http://imgit.jingyanla.com/240625/0Q5431128-0.jpg)
编按:哈喽 , 大家好!不管是在小说,还是电视剧中 , 主角们都有着一招制胜的法宝,这让他们每次都能“逢凶化吉” 。那么回到职场中 , 每一位EXCELER在面对各种各样的问题时,能不能也有一招能“碾压四方”的绝技呢?赶紧来看看吧!
*********
【前言】
在武侠小说里,经常会有一种情况——带着主角光环的人 , 总是凭借“一招鲜 , 吃遍天”的“伟大漏洞” , 处处逢凶化吉 。都说“职场如战场”,来到了职?。?每一位EXCELER,面对来自四面八方的工作压力,也算是“处处逢凶”了 。那么能不能也有这么“一招”,让同学们“随时化吉”呢?跟上E图表述的步伐,来学习一招“以不变应万变”的绝技吧 。
【正文】
不多废话,切入主题 。同学们知道在日常表格中,有一种信息内容叫做“序号”吗?我们今天就来“玩”这个序号 , 我保证你会“爱”上这个“小操作”的 。
添加序号的方法
在日常生活中 , 我们每个人添加序号的方式可能都不一样 , 主要归纳起来有下面几种 。
![excel函数技巧:辅助列能给函数应用带来什么便利](http://imgit.jingyanla.com/240625/0Q5434643-1.jpg)
这是较常规的几种添加序号的方法 , 但是也仅限于“常规” 。而我们今天要学的内容 , 就是非常规的序号 , 而这样的序号可以给我们带来“前所未有且简单”的体验 。
一、“动态”提取不重复的值并统计
![excel函数技巧:辅助列能给函数应用带来什么便利](http://imgit.jingyanla.com/240625/0Q5435358-2.jpg)
遇到这种情况 , 大部分同学会使用两种方法解决:
第一种:首先复制粘贴出E列内容 , 然后在“数据”选项卡中 , 点击“删除重复项”功能键,再用SUMIF函数求和 。
B22单元格函数:
=SUMIF($E$2:$E$16,A22,$F$2:$F$16)
![excel函数技巧:辅助列能给函数应用带来什么便利](http://imgit.jingyanla.com/240625/0Q5434500-3.jpg)
第二种:直接使用数组函数,得到不重复的费用类别,再使用SUMIF函数求和 。
![excel函数技巧:辅助列能给函数应用带来什么便利](http://imgit.jingyanla.com/240625/0Q543Kc-4.jpg)
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三键结束 。
你会选择哪种呢?如果说我们需要在源数据更新后,也能实现自动统计的话,肯定是选择函数的做法,但是这个数组函数真的不是初学者能够驾驭的(上面的数组函数不是今天的主题,故不作展开说明),下面我们就用序号的方法来处理这个问题 。
![excel函数技巧:辅助列能给函数应用带来什么便利](http://imgit.jingyanla.com/240625/0Q5431927-5.jpg)
步骤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列内容不为空时,汇总各个费用类别的金额 。
【excel函数技巧:辅助列能给函数应用带来什么便利】这样一来是不是简单了很多?而且是随数据源的更新而变动的哦~
![excel函数技巧:辅助列能给函数应用带来什么便利](http://imgit.jingyanla.com/240625/0Q5436410-6.jpg)
二、“动态”分类提取明细
按照上面的思路,我们再看一个工作中经常会遇到的问题 。如下图所示 , 我们需要按照不同的费用类别,提取出对应的明细数据 。
![excel函数技巧:辅助列能给函数应用带来什么便利](http://imgit.jingyanla.com/240625/0Q5436152-7.jpg)
步骤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在索引区域中被索引的列序 。当然,这个表格依旧是可以实现动态更新的 。
![excel函数技巧:辅助列能给函数应用带来什么便利](http://imgit.jingyanla.com/240625/0Q543B39-8.jpg)
三、“动态”多条件提取明细
同样的思路再来“玩”一个多条件的索引 。如下图所示,需要提取出满足多个条件的明细数据 。
![excel函数技巧:辅助列能给函数应用带来什么便利](http://imgit.jingyanla.com/240625/0Q5436259-9.jpg)
步骤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的函数是一样的,我们就不多介绍了 。看一下动态的效果吧 。
![excel函数技巧:辅助列能给函数应用带来什么便利](http://imgit.jingyanla.com/240625/0Q5432435-10.jpg)
四、“动态”将明细按金额大小排序
![excel函数技巧:辅助列能给函数应用带来什么便利](http://imgit.jingyanla.com/240625/0Q5436431-11.jpg)
给一列数字排名,估计很多同学都知道用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的索引过程 。
通过选择升序降序的选项,做到动态罗列数据的过程 , 如下:
![excel函数技巧:辅助列能给函数应用带来什么便利](http://imgit.jingyanla.com/240625/0Q5434251-12.jpg)
五、“动态”插入空白行
对于这个需求,现下最常用的方法是“添加序号排序的方法”,如下:
![excel函数技巧:辅助列能给函数应用带来什么便利](http://imgit.jingyanla.com/240625/0Q54324S-13.jpg)
但是这种方式有一个弊端,如果插入的空白行不固定 , 那就需要频繁的操作,而且序号的粘贴过程也比较麻烦,插入几行就要复制几次序号 。所以我们就一起来看一个函数 序号动态插入空白行的案例 。
![excel函数技巧:辅助列能给函数应用带来什么便利](http://imgit.jingyanla.com/240625/0Q543D17-14.jpg)
步骤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(向右填充) , 即可完成对区域的填充了 。
![excel函数技巧:辅助列能给函数应用带来什么便利](http://imgit.jingyanla.com/240625/0Q5433Z4-15.jpg)
步骤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函数的嵌套引用明细表内容 。填充函数后就是下面的效果了 。
![excel函数技巧:辅助列能给函数应用带来什么便利](http://imgit.jingyanla.com/240625/0Q5433R6-16.jpg)
<小常识>:上面的5个案例都是一个数据源,5类数据处理的需求我们只用了一招“添加序号”的方式,轻易破解,其实这个“添加序号”就是我们经常说的“辅助列” 。
“辅助列”在原数据中,一般在首列之前,或者末列之后 。目的是在不改变原数据结构的基础上 , 为了解决一些不能通过原数据直接处理的数据分析要求,而添加辅助计算的信息内容 。
【编后语】
在这里,作者E图表述建议大家:如果大部分的情况 , 你都是使用函数来解决工作中的问题的话,“使用辅助列 , 不丢人”!很多人为了练习函数,甚至一些练习题,都是要求一步写出函数,但是“学习”和“工作”一定不要产生羁绊,学以致用没有错,但是你的领导不会等你学会了再安排工作 。在工作上,永远是要效率第一 。只有完成工作,你才有更多的时间去“学习” 。今天的内容 , 希望能够在工作上帮你提速,能够有更多的时间来“部落窝”继续深造 。
****部落窝教育-excel辅助列应用技巧****
原创:E图表述/部落窝教育(未经同意,请勿转载)
更多教程:部落窝教育(www.itblw.com)
微信公众号:exceljiaocheng