Excel中如何计算出多列日期表中每行最后更新的日期? 图文

2018年5月30日08:40:55Excel中如何计算出多列日期表中每行最后更新的日期? 图文已关闭评论 2,189 views

这应该是一个典型的match函数查找最大非空行列的函数套路应用,我做个简单的示范如下

Excel中如何计算出多列日期表中每行最后更新的日期?

函数简单讲解,如果没有数字的情况下,可以用下面的公式,计算效率高一点

  • =INDEX($C$2:$H$2,MATCH("座",C3:H3))


注意这个函数只适用于没有数值型单元格的情况,如果有数字会出现错误,比如下图

  • MATCH("座",C3:H3) 利用match查找的特性,座是一个字符排序比较靠后的字符,绝大部分字符串都会在它之前出现,那么match会查找到与他最接近的一个值的位置,当然就是最后一个非空单元格的位置,这里体现的就是C3:H3中的序号,比如第一行就是5

  • index根据match查找的序号返回第一行对应的日期数据即可

    Excel中如何计算出多列日期表中每行最后更新的日期?

    match查找数字出现的位置出错

    Excel中如何计算出多列日期表中每行最后更新的日期?

    正确结果

    • 如果要同时考虑有数字和字符出现的情况要用另一个公式

    • 这个公式强大,但是如果数据多的话非常耗费资源,请谨慎使用

    首先看一个实例

    • =LOOKUP(1,0/(C12:H12<>""),$C$11:$H$11)

    • 这个公式就是我们常说的万能条件查找函数"LOOKUP(1,0/" 的套路

    • 下面重点讲解一下lookup(1,0/)的计算原理

    我们来看一个公式:=lookup(1,0/((A2:A7=E2)*(B2:B7=F2)),C2:C7)

    这个公式的意思就是根据E2和F2的信息,在G2生成需要的结果。

    Excel中如何计算出多列日期表中每行最后更新的日期?

    当然,这个方案还有其他的解法。我们不在这里讨论这些。只是通过这个例子来看。

    首先我们来复习一下lookup的用法

    向量型查找=lookup(lookup_value,lookup_vector,result_vector)

    =lookup(查找的值,查找的范围,返回值的范围)

    数组型查找= lookup(lookup_value,array)

    =lookup(查找的值,数组)

    原理解析

    0/这个结构在lookup里应用还是比较广泛的。

    我们首先看看(A2:A7=E2)*(B2:B7=F2)运算之后的结果到底是什么。

    在Excel中,如果A和B的值相等,=A =B,会返回true,也就是1.如果不相等,会返回false,也就是0

    上图就是比较直观的图示。再来一张图。

    Excel中如何计算出多列日期表中每行最后更新的日期?

    通过逻辑值的运算,我们可以得到G列的结果,这个相信大家都能明白。

    现在我们可以知道,逻辑值相乘结果等于1的那一行,就是我们需要的结果,现在我们的任务就是提取出来。

    lookup要想准确地找到,这组值需要升序排列,很明显,(A2:A7=E2)*(B2:B7=F2)的结果是不能满足需求的。

    重点逻辑

    天才的高手们想出了0/的方法把结果由1和0构成的一组数值,变成了一组由0和错误值的数值。符合结果的就是0所在的行,其余的都是错误值。

    lookup最牛叉的就是查找的时候可以忽略错误值。这样,其余的错误值都被忽略了,只剩下0.我们要做的就是,使用一个大于等于0的数值查找即可。

    =lookup(1,0/((A2:A7=E2)*(B2:B7=F2)),C2:C7)。

    总结

    “0/”的目的就是把符合条件的变成0,其余的都是错误值。

    如果你条件只有一个满足的,那刚好就会返回这个0对应的位置所在数据

    敲黑板!! 上面都是前奏,下面才是高潮!

    如果条件有多个满足,那么就会返回最后一个计算出来的0对应位置的数据,这就是本文上面查找最后一列的逻辑!

    Excel中如何计算出多列日期表中每行最后更新的日期?


    • 微信扫码免费学习
    • 免费学习48小时
    • weinxin
    • 我的微信公众号
    • EXCEL880
    • weinxin