|
昨天我们分析了万金油公式（Index small if组合）的计算原理，今天继续来看这个万金油组合最为经典的五个应用案例，对于公式还不能理解的，不妨再看看昨天的内容，或者加QQ、微信交流（联系方式在文末）。 单列提取不重复值 在单列中提取不重复值（重复的保留一个），公式为： =INDEX($B$2:$B$28,SMALL(IF(MATCH($B$2:$B$28,$B$2:$B$28,0)=ROW($1:$27),ROW($1:$27),99),ROW(A1)))，效果如图： 注： 1、这里多了一个match函数，不明白的可以先看看match函数的基本作用； 2、单列提取不重复值最简单方法其实不是公式，高级筛选或者透视表都可以实现； 3、就公式来说，单列提取不重复值也有很多方法，Index small if组合、Index match countif组合等等…… 多列提取不重复值 在多列中提取不重复值（重复的保留一个），公式为： =INDEX($A$2:$B$28,SMALL(IF(MATCH($A$2:$A$28&$B$2:$B$28,$A$2:$A$28&$B$2:$B$28,0)=ROW($1:$27),ROW($1:$27),99),ROW(A1)),COLUMN(A1))，效果如图： 注： 1、因为是多列引用，所以index多了第三参数，并且用column实现列的变化； 2、这个方法适用于数据量不大的情况，实际应用中推荐使用高级筛选或者透视表； 3、这个公式并不是效率最高的，仅仅是为了说明这个组合的应用…… 单条件查找多条记录 根据一个条件提取符合该条件的多条数据（学号为给出的条件），公式为： =(INDEX($B$2:$D$28,SMALL(IF($A$2:$A$28=$F$2,ROW($1:$27),99),ROW(A1)),COLUMN(A1))) 注：这是最为常见的一种问题，本例实际就是昨天所讲的公式，只是单元格位置发生了变化，对比昨天的内容，可以更加容易理解各个参数的含义。 多条件查找多条记录 根据多个条件提取符合该组条件的多条数据（例如英语85分以上，即为科目和成绩两个条件），公式为： =(INDEX($B$2:$D$28,SMALL(IF(($C$2:$C$28=$F$2) ($D$2:$D$28>=85)=2,ROW($1:$27),99),ROW(A1)),COLUMN(A1))) 注：这个公式用处很广，其中if的第一参数有点意思，有兴趣的朋友不妨琢磨琢磨…… 对比并提取两列数据的差异记录 根据两列数据比对，并分别提取出差异内容，公式为： =IFERROR(INDEX(OFFSET($A$2,,,COUNTA($A:$A)-1),SMALL(IF(COUNTIF(OFFSET($B$2,,,COUNTA($B:$B)-1),OFFSET($A$2,,,COUNTA($A:$A)-1)),99,ROW(OFFSET($A$2,,,COUNTA($A:$A)-1))-1),ROW(A1))),'') =IFERROR(INDEX(OFFSET($B$2,,,COUNTA($B:$B)-1),SMALL(IF(COUNTIF(OFFSET($A$2,,,COUNTA($A:$A)-1),OFFSET($B$2,,,COUNTA($B:$B)-1)),99,ROW(OFFSET($B$2,,,COUNTA($B:$B)-1))-1),ROW(A1))),'') 注：这个例子用到了两个公式，并且加入了offset这个函数，目的是动态范围。相信这两个公式会让很多朋友觉得眼花缭乱，不过只要弄明白一个，另一个也就没什么难度了。说到这个例子的应用，其实还是提问率蛮高的一个问题呢，一起看看动画就明白到底是什么作用了： 师父领进门，修行在个人，除了以上几种应用，Index small if组合还有很多小奥妙等待你在实际运用过程中慢慢发现，那种惊喜的感觉还是留给有心人慢慢体味吧 你们最想了解和掌握的Excel技能有哪些？请在留言区告诉我们。 有任何疑问欢迎加qq群交流：EXCEL基础学习群 259921244 |
|
|
