|
欢迎转发和点一下”在看“，文末留言互动! 置顶公众号或设为星标及时接收更新不迷路
小伙伴们好，今天来和大家分享一道最值的问题。求一组数据和一个给定值差额最小一个数据。 这类题目有个很现实的应用场景，请看下面这个例子：  根据给定的物料和采购日期，查找到距离采购日期最近的报价。注意，报价日期必须要早于采购日期，这是这道题目的隐性限定条件。 今天介绍的这三种技巧构思巧妙，非常值得收藏！ FREQUENCY函数法  用FREQUENCY函数做这道题的基本思路是，采购日期减去报价日期必须要大于等于0。于是，就可以利用FREQUENCY函数对0来计频。 根据FREQUENCY函数的特点，计频点是大于等于查找值的最小值，这样就可以找到最近的报价了。 IF($A$3:$A$14=E3,$B$3:$B$14) 通过IF函数，把所有同一物料的报价日期都找出来。 F3-IF($A$3:$A$14=E3,$B$3:$B$14) 而这部分，就是计算出采购日期和报价日期之间的日期差。 这里日期差的结果即有正数也有负数。这部分的结果是{3;44961;1;44961;-1;44961;-3;44961;-5;44961;-7;44961}。 FREQUENCY(0,F3-IF($A$3:$A$14=E3,$B$3:$B$14)) 接下来由FREQUENCY函数对0来计频。 根据FREQUENCY函数的特性，在上面“1”的位置上计频1。因为在内存数组中1表示这两个日期差最小，而内存数组是由一组正负数构成，所以对0计频，记录在“1”的位置上。 有关FREQUENCY函数，请在公众号后台查看FREQUENCY函数合集中相关内容。 0/FREQUENCY(0,F3-IF($A$3:$A$14=E3,$B$3:$B$14)) 这部分是一个常用的技巧。通过这一操作，将所有的数值转换为0，其余的转换为错误值 =LOOKUP(1,0/FREQUENCY(0,F3-IF($A$3:$A$14=E3,$B$3:$B$14)),$C$3:$C$14) 接下来LOOKUP函数二分法特性，返回正确答案。有关于LOOKUP函数的详细信息，请参看帖子总结篇-LOOKUP函数实用终极帖。 LOOKUP函数法  LOOKUP函数本身就可以处理多条件的数据查询。 0/(A$3:A$14=E3)/(B$3:B$14<=F3) 如果要采用()*()的形式，就要写成下面的这种形式。 0/((A$3:A$14=E3)*(B$3:B$14<=F3)) 这两条的结果是相同的，请大家注意。“0/”的技巧有非常厂泛的应用。日常使用中，LOOKUP函数都会嵌套这个技巧。 =LOOKUP(1,0/(A$3:A$14=E3)/(B$3:B$14<=F3),C$3:C$14) 这个公式也是利用的LOOKUP函数的二分法特性完成的。 VLOOKUP函数法  观察一下源数据，我们发现报价日期是递增排序的。因此这里也可以考虑使用VLOOKUP函数。 IF(A$3:A$14=E3,B$3:C$14) 这部分主要的作用就是返回同一物料的所有报价日期。 =VLOOKUP(F3,IF(A$3:A$14=E3,B$3:C$14),2) 由于省略了第四参数，这时VLOOKUP函数执行的是模糊查找。 在此模式下，返回小于等于查找值的最大值，也就是最接近采购日期的报价日期。 -END- |
|
|
