officeba > 单独文章


如何使用SUMPRODUCT找到一个Excel列表中最后一个项目

介绍如何使用SUMPRODUCT函数找到一个Excel列表中最后一个连续的项目。

查曾与商业试算表自1979年以来,已写在电子表格的麦格劳一希尔和使用微软出版社的书籍。你可以在他的网站,ExcelUser.com更多有用的技巧和查理和其他Excel MVP的职位。



假设你有一个项目的顺序列表,在这里的第二个表。并假设你要显示的列表中的第一个和最后生效之日。你会怎样做呢?

这个问题不仅限于日期,顺便说一句。该序列表可以显示数字或文字。你将如何写在范围内使用B4的公式:C6至返回日期的第一个和最后一个项目?

许多Excel用户可以快速编写一个公式,返回的每个项目在列表中第一次约会。他们将使用所显示的细胞像此索引匹配公式:

B4的:=指数(日期,匹配(A4区项目,0))

然而,指数这场比赛将不会帮助我们找到列表中的每个项目最后一次出现。这是因为匹配(与比赛型等于零)返回一个未排序的列表中的第一个项目。有没有版本,返回最后一个项目。

不过,建议作为本短文的标题,Excel的SUMPRODUCT函数只提供我们所需的电力。下面是显示的单元格公式:

C4的:=指数(乙:乙,SUMPRODUCT(马克斯((项目= A4号)*行(项目))))

即使您使用SUMPRODUCT了很多,你可能很难理解这个公式乍一看。因此,让我们在这一步的时候...



SUMPRODUCT工程与数据数组。但它不局限于单纯的乘法的“产品”的名称的一部分,意味着。事实上,这里就是我倾向于认为SUMPRODUCT:

SUMPRODUCT =(虽然做的是输入公式作为数组,然后返回结果的东西)

我知道,这不是一个很科学的解释,但它不是一个坏的经验规则。

上述内部SUMPRODUCT公式,寻找让我们开始:(编号= A4号)

在这里,每一个功能比较了在项目范围(细胞格A10:A19中)在单元格A4的文本单元值(“帽”)。在其记忆体时,Excel建立一个TRUE其中一个项目等于“帽子”和FALSE数组,它没有。

现在考虑:(编号= A4号)*行(项目)

在这里,我已经告诉函数乘以在真/假值的数组对应每个项目行数。

因为真正作为一评估,并在公式为0 FALSE时,该产品将返回一个数字数组。这些数字等于零,那里的项目并不等于“帽子最多。”但是,在一个项目不等于“帽子”,该数组包含行号码,该项目被发现。

下一步:马克斯((项目= A4号)*行(项目))

这很简单。最大的回报,为在数字1和0,这就是“帽子”出现在列表的最后数组最大行数的值。

而下一步:SUMPRODUCT(马克斯((项目= A4号)*行(项目)))

SUMPRODUCT只是告诉Excel中的函数作为数组的处理以前的计算。我们用这个公式返回的第一个简单的数字:在您的工作表在“帽子”,似乎最后的行的数目。

下面是整个公式再次:

C4的:=指数(乙:乙,SUMPRODUCT(马克斯((项目= A4号)*行(项目))))



这个公式现在应该开始做更有意义。这里只有一个步骤了去:

指数(乙:二,[为“帽最后一排数”])

在这里,期待指数在B列中的所有电子表格。在其他信息,包括在B列的顺序名单最新资料。索引返回日期找到由SUMPRODUCT函数中指定的行数。而该行的数目是那里的“帽子”最后一次出现可以找到。

最后,您可以考虑该公式的变化,一个变化,消除了一个危险的方面这种方法:显示的单元格C4引用整列以上的公式。

引用一整列是一个问题,因为在这种情况下,如果您剪切和粘贴顺序名单一些其他位置,该公式仍然将引用列B,但名单不会在它。而该公式将失败。

一种改进的公式解决了此问题:

C4的:=指数(日期,SUMPRODUCT(马克斯((项目= A4号)*行(项目)))
          列(日期)+1)

而不是引用B列,该公式引用命名的日期范围。为了让我们为这个指数值范围的需要,我们减去第一行的行数,然后加1。 (由于项目和日期范围总是必须在同一行,我们也可以利用该项目的第一个行号范围。)

在单元格中输入公式,然后复制C4的范围格B4:C4的下两行。当你这样做,新的公式将返回第一个和鞋袜,去年分别发生。


声明:欢迎各大网站转载本站文章,还请保留一条能直接指向本站的超级链接,谢谢!

时间:2010-08-09 20:29:42,点击:65824


【OfficeBa论坛】:阅读本文时遇到了什么问题,可以到论坛进行交流!Excel专家邮件:342327115@qq.com(大家在Excel使用中遇到什么问题,可以咨询此邮箱)。

【声明】:以上文章或资料除注明为Office自创或编辑整理外,均为各方收集或网友推荐所得。其中摘录的内容以共享、研究为目的,不存在任何商业考虑。如有任何异议,请与本站联系,本站确认后将立即撤下。谢谢您的支持与理解!


相关评论

我要评论

评论内容