真正理解CHOOSE({1,2}后才顿悟CHOOSE({1,2,…...N}的神奇妙用!
个人微信号 | (ID:LiRuiExcel520)
微信服务号 | 跟李锐学Excel(ID:LiRuiExcel)
微信公众号 | Excel函数与公式(ID:ExcelLiRui)
在平时职场办公中,很多原始数据源的结构并不支持直接进行数据统计和计算,需要转换结构后再使用Excel函数公式自动计算,这个过程中经常需要构建内存数组。
除了IF({1,0}的构建方法,我们还可以使用CHOOSE({1,2}的方式根据需要构建内存数组,支持Excel函数直接可以运算出想要的结果。
由于绝大多数人还不理解这其中的奥妙所在,所以本文结合两个案例,全面介绍这里面构建内存数组的思路和方法,方便广大职场白领们在工作中能够直接套用。
Excel从右向左查询要求:
左侧是原始数据源,姓名在成绩右侧;
要求查询指定姓名的英语成绩。
场景示意图如下图所示。
要求使用Excel公式实现根据条件自动查询,当条件变更时,公式结果自动更新,如下动图演示所示。
你能想到哪些解决方案呢,自己思考一下再往下看吧。
Excel公式解决方案:
思路:使用CHOOSE构建内存数组,变换原始数据源结构,将姓名移动到成绩左侧,然后再用VLOOKUP函数按条件进行数据查询。
用到的公式如下。
=VLOOKUP(D2,CHOOSE({1,2},$B$2:$B$16,$A$2:$A$16),2,0)
公式示意图如下所示:
这个公式的关键点在于VLOOKUP函数的第二参数,所以下文专门展开讲解。
CHOOSE函数构建内存数组原理:
为了让大家更轻松理解这块的内存数组,咱们拆分开讲解,首先来看CHOOSE({1}这一部分,如下所示。
CHOOSE({1},$B$2:$B$16,$A$2:$A$16)
再看CHOOSE({2}这一部分,公式如下所示。
CHOOSE({2},$B$2:$B$16,$A$2:$A$16)
然后将前两部分合并在一起,注意合并时的放置顺序,CHOOSE({1,2}公式如下所示。
CHOOSE({1,2},$B$2:$B$16,$A$2:$A$16)
这样就将姓名从右侧移动到了成绩列的左侧,方便我们按照自己的需要进行各种各样的查询。
当在工作中遇到更多字段查询时,我们还可以扩展这种思路和方法,使其能够兼容更多种情况,以及简化公式写法,所以下文中我们继续详细介绍。
CHOOSE({1,2,......N)的扩展使用方法:
在讲解原理之前,先来看一个案例,帮你更好地理解。
现在要求继续升级,左侧的原始数据源中包括更多科目成绩,右侧需要依次按照数学、语文、英语、物理、化学的顺序进行数据查询。
只需扩展一下CHOOSE函数的内存构建方法,即可仅用1个公式完成5个字段的成绩查询,示意图如下所示:
查询条件改变时,公式结果自动更新,动图演示如下图所示。
这种方法是在CHOOSE({1,2}的基础上扩展而来,下文继续介绍具体原理。
CHOOSE函数构建内存数组扩展方法:
利用CHOOSE函数构建的内存数组,可以轻松调整数据源中各个科目的成绩所在列顺序,从而支持VLOOKUP函数依次进行多字段查询。
这里用到的公式如下。
=VLOOKUP($H2,CHOOSE({1,2,3,4,5,6},$F$1:$F$16,$D$1:$D$16,$C$1:$C$16,$A$1:$A$16,$B$1:$B$16,$E$1:$E$16),COLUMN(B1),0)
公式示意图如下所示:
这个公式原理是按照科目查询顺序,依次排布内存数组中各列的顺序,其中VLOOKUP函数的第二参数是关键点,这里用到的内存数组如下所示。
另外需要注意的是,输入公式时注意使用混合引用,目的是当公式向右填充时自动按需要转换引用位置。
比如当公式向右填充至M2单元格时,公式如下所示。
=VLOOKUP($H2,CHOOSE({1,2,3,4,5,6},$F$1:$F$16,$D$1:$D$16,$C$1:$C$16,$A$1:$A$16,$B$1:$B$16,$E$1:$E$16),COLUMN(F1),0)
公式示意图如下所示:
这些常用的经典excel函数公式技巧可以帮你在关键时刻解决困扰,有心的人赶快收藏起来吧。
希望这篇文章能帮到你!怕记不住可以发到朋友圈自己标记。