美文网首页韩老师讲Office工具癖
Excel209 | OFFSET函数提取销量前三位所在整列信息

Excel209 | OFFSET函数提取销量前三位所在整列信息

作者: bitterfleabane | 来源:发表于2017-11-09 08:43 被阅读8次

    上一篇Excel208 | OFFSET函数提取最大销量所在整列信息,有位朋友后台留言:韩老师,只查找第一列的信息,在我们单位是不够的,我们需要查找前三名的数据进行分析,怎么用公式实现?

    我们只需把昨天公式中的最大值函数换成LARGE函数就好了。

    公式实现

    在B9单元格输入公式:

    =OFFSET($A$2,ROW($A1)-1,MATCH(LARGE($B$5:$K$5,COLUMN(A1)),$B$5:$K$5,0))

    公式向下、向右填充,即得销售量第一、第二、第三位的整列信息。

    公式解析

    LARGE($B$5:$K$5,COLUMN(A1)):

    在B5:K5,所有的销量中,查找第一大的数值。

    在B9单元格,COLUMN(A1)的返回值是1,即第一大的值;公式向右填充时,变为COLUMN(B1)、COLUMN(C1),返回值自动变为2、3,即第二、第三大的值。

    MATCH(LARGE($B$5:$K$5,COLUMN(A1)),$B$5:$K$5,0):

    在B5:K5区域,匹配最大值所在列数,公式向右填充,得第二、第三大值所在列。

    OFFSET($A$2,ROW($A1)-1,MATCH(LARGE($B$5:$K$5,COLUMN(A1)),$B$5:$K$5,0)):

    以A2为基准点,向下偏移ROW($A1)-1行,向右偏移最大值所在列数。

    本示例中:

    公式在B9单元格时,ROW($A1)-1的结果是0,最大值1844,以A2为基准点,偏移0行,偏移到1844所在的列,即是单元格“销售2部”;

    公式向下填充,ROW($A1)-1自动变为ROW($A2)-1、ROW($A3)-1、ROW($A5)-1、ROW($A5)-1,则自动变为偏移1、2、3、4行,即得最大值1844列所有的信息。

    公式向右填充,即得第二、第三大的值所在列信息。

    相关文章

      网友评论

        本文标题:Excel209 | OFFSET函数提取销量前三位所在整列信息

        本文链接:https://www.haomeiwen.com/subject/gltpmxtx.html