如何从有条件的列中获取最新值

发布于 2024-12-07 18:23:24 字数 316 浏览 2 评论 0原文

我在 Excel 中有一个表格,其中包含以下列:

  1. 日期
  2. 人员姓名
  3. 金额 (£)

该表格用于记录人们何时向我付款。通常,我可以让不止一个人在同一天向我付款。而且,随着时间的推移,同一个人会在很多天向我付款。

记录添加到表格底部,以便按日期排序,但不再按名称或金额排序。

使用公式,有没有办法检索特定人员的最新金额?

如果这太复杂或不可能,那么我可以采用以下解决方法: 将第四列添加到名为“Last”的表中。如果这是特定人员的最后一个条目,则显示 TRUE,否则显示 FALSE。

I have a table in Excel which has the columns:

  1. Date
  2. Person Name
  3. Amount (£)

The table is used to record when people pay me money. Typically, I can get more than one person paying me on the same day. Also, the same person will pay me on many days over the course of time.

Records are added to the bottom of the table so the ordering will be on date but no further ordering on name or amount.

Using formulas, is there a way I can retrieve the most recent amount for a specific person?

If this is too complicated or not possible then I can settle for the following work around:
Add a 4th column to the table called "Last". This will display TRUE if it is the last entry for a specific person, FALSE if it is not.

如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论, 你可以免费 注册 一个本站的账号。

评论(3

二手情话 2024-12-14 18:23:24

这是一种基于我在之前的SO帖子中给出的答案的方法。

=INDEX($C$1:$C$19,MATCH(MAX(IF($B$1:$B$19="PersonNameHere",$A$1:$A$19,0)),IF($B$1:$B$19="PersonNameHere",$A$1:$A$19,"")))

其中 A 是“日期”列,B 是“人员姓名”列,C 是“金额”列。您必须通过按 Ctrl+Shift+Enter 将其输入为数组公式。

Here's one way to do it based on an answer I gave in a previous SO post.

=INDEX($C$1:$C$19,MATCH(MAX(IF($B$1:$B$19="PersonNameHere",$A$1:$A$19,0)),IF($B$1:$B$19="PersonNameHere",$A$1:$A$19,"")))

Where A is the Date column, B is the Person Name column, and C is the Amount column. You must enter this as an array formula by pressing Ctrl+Shift+Enter.

遗心遗梦遗幸福 2024-12-14 18:23:24

感觉应该有一个相当直接的答案,但我发现它相当令人头疼,所以有兴趣看到答案。

在进行了一些谷歌搜索后,我发现了一个发布在专用 Excel 网站上的解决方案(在此处查看 )。 [注意 - 查看“任意查找”标题下]

将其应用到您的示例中,假设您的数据位于 A1:C10 中,并且您想要在单元格 D2 中键入名称并在单元格 D3 中返回最近的付款:

1   Date    Name    Amt    EnterName
2   20 Jul  Bob     50     <enter name here>
3   13 Sep  Susan   20     = enter formula here (see below)
4   06 Jan  Xavier  100

在单元格 D3 中输入以下数组公式(即输入公式,然后按 CTRL + SHIFT + ENTER

=INDEX($B$2:$C$10,SMALL(IF(OFFSET($B$2:$C$10,0,0,ROWS($B$2:$C$10),1)=$D$2, ROW(OFFSET($B$2:$C$10,0,0,ROWS($B$2:$C$10),1))-ROW(OFFSET($B$2:$C$10,0,0,1,1) )+1, ROW(OFFSET($B$2:$C$10,ROWS($B$2:$C$10)-1,0,1,1))+1),COUNTIF(OFFSET($B$2:$C$10,0,0,ROWS($B$2:$C$10),1),$D$2)),2)

如果您需要更多详细信息,建议您查看我提供的链接。为了清楚起见,我只是根据提供的链接调整了公式(更改了单元格引用)。

It felt like there should be a fairly straight-forward answer to this but I found it quite a head scratcher so was interested to see an answer.

Having done some googling I came across a solution posted on a dedicated excel site (view here). [NB - look under the header 'Arbitrary Lookups']

Applying it to your example, suppose your data is in A1:C10 and in cell D2 you want to type a name and return the most recent payment in cell D3:

1   Date    Name    Amt    EnterName
2   20 Jul  Bob     50     <enter name here>
3   13 Sep  Susan   20     = enter formula here (see below)
4   06 Jan  Xavier  100

In cell D3 enter the following as an array formula (i.e. type in formula and then press CTRL + SHIFT + ENTER

=INDEX($B$2:$C$10,SMALL(IF(OFFSET($B$2:$C$10,0,0,ROWS($B$2:$C$10),1)=$D$2, ROW(OFFSET($B$2:$C$10,0,0,ROWS($B$2:$C$10),1))-ROW(OFFSET($B$2:$C$10,0,0,1,1) )+1, ROW(OFFSET($B$2:$C$10,ROWS($B$2:$C$10)-1,0,1,1))+1),COUNTIF(OFFSET($B$2:$C$10,0,0,ROWS($B$2:$C$10),1),$D$2)),2)

It would recommend checking out the link I provided if you want more detail. For clarity, I have merely adapted the formula (changed cell references) from the link provided.

っ〆星空下的拥抱 2024-12-14 18:23:24

我在这里看不到一种仅用一个公式来完成此操作的简单方法。

如果您有从 A2C11 的数据。
您可以将此公式添加到第 4th 列(假设在单元格 D2 中):

{=MAX(IF($B$2:$B$11=B2,$A$2:$A$11,0))}

这是一个 数组公式 您必须使用Ctrl-Shift-Enter
这将告诉您当前人员的最后日期

然后用另一列找到最后金额(假设在单元格E2中)并使用此公式:

=INDEX($C$2:$C$11,MATCH(D2,$A$2:$A$11,0))

[编辑]我刚刚尝试将公式合并为一个和这很简单:

{=INDEX($C$2:$C$11,MATCH(MAX(IF($B$2:$B$11=B2,$A$2:$A$11,0)),$A$2:$A$11,0))}

这仍然是一个数组公式。

唉,@Excelll 更聪明(也更快),并且第一次就给出了解决方案

I can't see right here an easy way to do it in only one formula.

If you have your data from A2 to C11.
You can add this formula on the 4th column (let say in cell D2):

{=MAX(IF($B$2:$B$11=B2,$A$2:$A$11,0))}

This is an array formula you have to validate with Ctrl-Shift-Enter
This will tell you what is the last date for the current person.

And then find the last amount with another column (let say in cell E2) and use this formula:

=INDEX($C$2:$C$11,MATCH(D2,$A$2:$A$11,0))

[EDIT] I've just tried to combine the formulas into one and that simply works:

{=INDEX($C$2:$C$11,MATCH(MAX(IF($B$2:$B$11=B2,$A$2:$A$11,0)),$A$2:$A$11,0))}

and this is still an array formula.

Alas, @Excellll was smarter (and faster) and gave the solution at first shot

~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文