以一敌五,它才是excel中最牛逼的技巧!( 二 ) _知识经验

按照上面的思路，我们再看一个工作中经常会遇到的问题。如下图所示，我们需要按照不同的费用类别，提取出对应的明细数据。

步骤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在索引区域中被索引的列序。当然，这个表格依旧是可以实现动态更新的。

3
“动态”多条件提取明细
同样的思路再来“玩”一个多条件的索引。如下图所示，需要提取出满足多个条件的明细数据。

步骤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的函数是一样的，我们就不多介绍了。看一下动态的效果吧。

4
“动态”将明细按金额大小排序

给一列数字排名，估计很多同学都知道用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的索引过程。
通过选择升序降序的选项，做到动态罗列数据的过程，如下：

5
“动态”插入空白行
对于这个需求，现下最常用的方法是“添加序号排序的方法”，如下：

但是这种方式有一个弊端，如果插入的空白行不固定，那就需要频繁的操作，而且序号的粘贴过程也比较麻烦，插入几行就要复制几次序号。所以我们就一起来看一个函数+序号动态插入空白行的案例。

步骤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中最牛逼的技巧!( 二 )

猜你喜欢