如何从具有条件的列获取最新值 [英] How to get the newest value from a column with conditions
问题描述
我在Excel中有一张表，其中列有：
I have a table in Excel which has the columns:
- 日期
- 金额（£）
该表用于记录人们何时支付我的钱。通常情况下，我可以在同一天收到不止一个人付款给我。另外，同一个人会在几天的时间内支付给我。
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?
如果这太复杂或不可能，那么我可以解决以下工作：
将第四列添加到名为Last的表中。如果它是特定人员的最后一个条目，则将显示为TRUE。如果不是，则为TRUE。
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.
推荐答案
感觉像那里应该是一个相当直截了当的答案，但我发现这是一个很好的头部抓手，所以有兴趣看到一个答案。
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.
做了一些谷歌搜索我遇到了一个解决方案发布在专门的excel网站（查看此处）。 [NB - 查找标题任意查找]
Having done some googling I came across a solution posted on a dedicated excel site (view here). [NB - look under the header 'Arbitrary Lookups']
将其应用于您的示例，假设您的数据位于 A1：C10 并且在单元格 D2 中，您要键入名称，并返回单元格 D3 中的最新付款：
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
在单元格 D3 中输入以下数组公式（即键入公式，然后按 CTRL + SHIFT + ENTER
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.
这篇关于如何从具有条件的列获取最新值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
