从Contacts返回各自名称的MAX值登录到Contacts工作表 [英] Returning MAX Value for a Respective Name From ContactsLog to Contacts Sheet

问题描述

在Excel中，我有两张纸:

In Excel I have two sheets:

• 联系人
• ContactsLog

工作表联系人"包含与众不同的联系人. B列包含联系人的姓名，例如"Doe，John".

The sheet Contacts contains distinct contacts. Column B contains the name of the contact, for example 'Doe, John'.

ContactsLog工作表包含有关与联系人的每次交互以及与联系人联系的日期的记录.相应的日期存储在联系日期"列中.

The sheet ContactsLog contains records about every interaction with contact and the respective date when the contact was contacted. The respective date is stored in the column 'Contact Date'.

我想在联系人"表中的另一列称为最后联系日期".我该如何写一个公式来查找联系人"工作表中联系人的姓名，找到有关该特定联系人互动的所有记录，并从联系人日期"列中获取最新数据，以及将其写回最后联系日期"列中?

I would like to have another column in the sheet 'Contacts' called 'Last Contact Date'. How can I write a formula which looks up the name of the contact in the 'Contacts' sheet, finds all the records about the interaction of that particular contact, and brings be back the most recent data from the 'Contact Date' column, and writes it back into 'Last Contact Date' column?

感谢您的帮助.

最良好的祝愿！

推荐答案

让我们假设名称在各自工作表的A列中，而日期在C列中.

Lets assume the names are in Column A on their respective sheets and the dates are in Column C.

如果您具有Office 365 Excel，请使用MAXIFS():

If you have Office 365 Excel, Use MAXIFS():

=MAXIFS(Sheet2!C:C,Sheet2!A:A,A2)

如果您有2010年或更高版本，请使用Aggregate():

If you have 2010 or later use Aggregate():

=AGGREGATE(14,6,Sheet2!C2:C100/(Sheet2!A2:A100 = A2),1)

如果不使用MAX(IF())作为数组公式.

If niether use MAX(IF()) as an array formula.:

=MAX(IF(Sheet2!A2:A100 = A2,Sheet2!C2:C100))

退出编辑模式时，请按Ctrl-Shift-Enter而不是Enter确认.

Confirmed with Ctrl-Shift-Enter instead of Enter when exiting edit mode.

这篇关于从Contacts返回各自名称的MAX值登录到Contacts工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！