excel函数技巧:辅助列能给函数应用带来什么便利

关注
excel函数技巧:辅助列能给函数应用带来什么便利www.shan-machinery.com

编按:哈喽,大家好!不管是在小说,还是电视剧中,主角们都有着一招制胜的法宝,这让他们每次都能“逢凶化吉”。那么回到职场中,每一位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,C2https://www.shan-machinery.com