Excel - 在所有匹配行中查找列的最高值（带屏幕截图） [英] Excel - Find Highest Value of a Column in all Matching Rows (With Screenshot)

问题描述

我有一个跟踪产品的电子表格。在最简单的形式中，重要的列是名称，发布日期和最新？。可能有多个行在名称列中具有相同的值，但在发布日期中将具有不同的值。我正在寻找一个函数，首先查找行的名称列，并找到所有其他行具有相同的值。然后，比较发布日期并找到最高的。具有最高发布日期或没有任何其他匹配的名称值的行将返回YES，所有其他将返回否。我有一个截图，我想它的样子：

I have a spreadsheet that keeps track of products. In it's simplest form, the important columns are "Name", "Release Date", and "Newest?". There could be multiple rows that have the same value in the "Name" column, but they will have different values in "Release Date". I'm looking for a function that will first look in the "Name" column of the row and find all other rows that have the same value. Then, compare the "Release Date" and find the highest one. The row that has the highest "Release Date" or that doesn't have any other matching "Name" values would return "YES" and all others would return "NO". I have a screenshot of what I would like it to look like:

我知道，如果在名称列中找到另一个具有相同值的行，我将使用MATCH，而MATCH中的失败将导致我的YES输出。这是比较所有的发布日期值，我陷入困境。我希望这是一个公式，因为我没有VB的经验。

I know that to find if there is another row with the same value in the "Name" column I'd use MATCH and a failure in MATCH would result in my "YES" output. It's the comparing all the "Release Date" values that has me stumped. I would like this to be a formula as I have no experience with VB.

感谢您的帮助！

推荐答案

您可以使用以下数组公式：

You can use following array formula:

=IF(B2=MAX(IF($A:$A=A2,$B:$B)),"YES","NO")

$ b b

只需选择 C2 ，在公式栏中输入公式，按 CTRL + SHIFT + < 来评估它，然后将其向下拖动。

just select C2, enter formula in formula bar, press CTRL+SHIFT+ENTER to evaluate it, and then drag it down.

PS您可以将 $ A：$ A 和 $ B：$ B 更改为确切的范围， $ A $ 2：$ A $ 100 和 $ B $ 2：$ B $ 100 ）

P.S. you can change $A:$A and $B:$B to the exact ranges if you know them (i.g. $A$2:$A$100 and $B$2:$B$100)

这篇关于Excel - 在所有匹配行中查找列的最高值（带屏幕截图）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！