这才是Excel函数中的神器,名副其实的万金油公式! Excel中有一个几乎“万能”的函数组合,在N多种问题的解决过程中都少不了她的身影,她就是Index+small+if组合! 这个Excel万金油组合在工作中应用广泛,我专门挑选了她的几种典型应用,扩展大家的思路和方法。 今天的内容涉及数组运算,还未晋升到中级的同学们可能不易理解,不过没关系,大家可以先收藏教程,工作中按此套用。 如果对教程中部分知识点理解不了,想系统学习,点击文尾的“阅读原文”参加系统训练营,快速提升自己。 1 从单列提取不重复值 在黄色区域输入公式,提取a列的不重复值 定义名称a=OFFSET(数据源!$A$2,,,COUNTA(数据源!$A:$A)-1) C2单元格输入以下数组公式,按<Ctrl+Shift+Enter>组合键 =IFERROR(INDEX(A:A,SMALL(IF(MATCH(a,a,)=ROW(a)-1,ROW(a),4^8),ROW(A1)))&"","") 2 从单列提取重复值 在黄色区域输入公式,提取a列的重复值 定义名称a=OFFSET(数据源!$A$2,,,COUNTA(数据源!$A:$A)-1) C2单元格输入以下数组公式,按<Ctrl+Shift+Enter>组合键 =IFERROR(INDEX(A:A,SMALL(IF(MATCH(a,a,)=ROW(a)-1,4^8,ROW(a)),ROW(A1)))&"","") 在公众号后台回复“index”获取此函数的史上最全教程。 3 按要求提取数据 结合前两种技术,配合Excel中的控件,可以定制工作中的数据查找模板,如下图 定义名称a=OFFSET('1'!$A$2,,,COUNTA('1'!$A:$A)-1) C2单元格输入以下数组公式,按<Ctrl+Shift+Enter>组合键 =IFERROR(INDEX(A:A,SMALL(IF(B$1=1,IF(MATCH(a,a,)=ROW(a)-1,ROW(a),4^8),IF(MATCH(a,a,)=ROW(a)-1,4^8,ROW(a))),ROW(A1)))&"","") 在公众号后台回复“万金油”获取此案例模板。 4 一对多查找所有符合条件的数据 DATESTRING也是Excel中的隐藏函数,她的作用是转换日期格式为年月日的形式~ E2单元格输入以下数组公式,按<Ctrl+Shift+Enter>组合键 =INDEX(B:B,SMALL(IF(A$2:A$11=D$2,ROW($2:$11),4^8),ROW(A1)))&"" 5 比对并提取两列数据的差异记录 DATESTRING也是Excel中的隐藏函数,她的作用是转换日期格式为年月日的形式~ 定义名称a=OFFSET('1'!$A$2,,,COUNTA('1'!$A:$A)-1) 定义名称b=OFFSET('1'!$B$2,,,COUNTA('1'!$B:$B)-1) D2单元格输入以下数组公式,按<Ctrl+Shift+Enter>组合键 =IFERROR(INDEX(a,SMALL(IF(COUNTIF(b,a),4^10,ROW(a)-1),ROW(A1))),"") F2单元格输入以下数组公式,按<Ctrl+Shift+Enter>组合键 =IFERROR(INDEX(b,SMALL(IF(COUNTIF(a,b),4^10,ROW(b)-1),ROW(A1))),"") 师父领进门,修行在个人,除了以上几种应用,Index+small+if组合还有很多小奥妙等待你在实际运用过程中慢慢发现,那种惊喜的感觉还是留给有心人慢慢体味吧~ 如果你想系统学习,点击文尾的“阅读原文”,首期Excel训练营开始招生,我亲自授课,除了满满的干货技术还赠送所有课件,外加社群打卡互动点评,限时特惠哦~ 干货每天都有,如果你想每天第一时间收到, 如下操作,两秒搞定: 新朋友:点页面右上角三个点→查看公众号→关注 老朋友:点页面右上角三个点→查看公众号→置顶公众号 李 锐 微软全球最有价值专家MVP 新浪微博Excel垂直领域第一签约自媒体 百度名家,百度阅读认证作者 每日分享职场办公技巧原创教程 高效工作,快乐生活! 微博 @Excel_函数与公式 微信公众号(ExcelLiRui) 并置顶公众号 每天都可以收到最新的Excel干货教程 点击“阅读原文”参加首期系统训练营,限时特价 |
|