都进来看看!这招以一敌五的Excel绝技,连透视表都甘拜下风!( 二 ) _知识经验

文章插图

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

文章插图

步骤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的索引过程。通过选择升序降序的选项，做到动态罗列数据的过程，如下：