强强联合VLOOKUP函数构建数组的经典, Excel数组公式高级技巧 图文

2020年9月5日08:51:49强强联合VLOOKUP函数构建数组的经典, Excel数组公式高级技巧 图文已关闭评论 193 views
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页

在职场很多数据分析中,会出现求取大于一个值的月份等问题,特别是考核中,如果手工找,有个上万条,直接就累苗条了,下面小编分享一个实例技巧,要求用公式求出每人在12个月中,第一次大于B21单元格值的月份,别说直接VLOOKUP偏移哈,不是求值!!

强强联合,VLOOKUP函数构建数组的经典,能看懂就是办公能手

实例1

效果图:强强联合,VLOOKUP函数构建数组的经典,能看懂就是办公能手

制作步骤:1,在单元格B22输入公式,

=IF(A22="","",IFERROR(INDIRECT(CHAR(MIN(IF(VLOOKUP(A22,$A$2:$M$20,{2;3;4;5;6;7;8;9;10;11;12},FALSE)>$B$21,{2;3;4;5;6;7;8;9;10;11;12},999))+64)&1),""))

而后三键结束转换为数组

公式思路,

  1. 因为要和姓名匹配后偏移,所以要用到VLOOKUP,
  2. 因为带条件,且偏移位置不固定,每个人的都不一样,所以要想法用VLOOKUP构建一个内存数组,而后配合IF判断
  3. 当判断好后,我们要提取符合条件的值第一次出现的位置,所以用min数组
  4. 当值有了,就获得了月份的列,而如果要转化为月份引用,需用char函数,计算出所在的列的字幕
  5. 有列的字幕,而且行在第一行,配合indirect就可以求出值

char函数结果附图

强强联合,VLOOKUP函数构建数组的经典,能看懂就是办公能手

公式解释:

  1. VLOOKUP(A22,$A$2:$M$20,{2;3;4;5;6;7;8;9;10;11;12},FALSE) 这里,是用VLOOKUP得出一个所有月份值组成的数组,按F9键结果为{13;14;20;16;13;12;19;9;9;12;19}就是张无忌1所有月份成绩组成的数组
  2. IF(VLOOKUP(A22,$A$2:$M$20,{2;3;4;5;6;7;8;9;10;11;12},FALSE)>$B$21,{2;3;4;5;6;7;8;9;10;11;12},999)可以写成IF({13;14;20;16;13;12;19;9;9;12;19}>$B$21,{2;3;4;5;6;7;8;9;10;11;12},999)相当于如果值大于标准值,取{2;3;4;5;6;7;8;9;10;11;12}中对应位置的值,如果小于,就写为999,按F9键我们得到结果{999;999;4;5;999;999;8;999;999;999;12},而后公式就可以简化为INDIRECT(CHAR(MIN({999;999;4;5;999;999;8;999;999;999;12})+64)&1)
  3. MIN({999;999;4;5;999;999;8;999;999;999;12})得出第一个最小值为4,公式可以继续简化为INDIRECT(CHAR(4+64)&1)
  4. CHAR(4+64)得出的结果为D,公式继续简化为INDIRECT(D&1)
  5. INDIRECT(D1)结果为3月

附言: 这种问题最好学点 vba入个门就能轻松虚循环+字典解决 公式解决此类问题实在太过繁琐

  • 郑广学老师微信号
  • EXCEL880B
  • weinxin
  • 我的微信公众号
  • EXCEL880
  • weinxin
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页
表格 定制  数据 合并 处理 分析 VBA 编程 开发 网页