☰

EXCEL：自上次访问以来的天数

tag-icon • tag-icon microsoft-excel worksheet-function vlookup
EXCEL：自上次访问以来的天数

我正在尝试计算某个人上次访问某个地点以来的天数。

查看以下示例数据集，最好的方法是什么？

样本数据集截图

我尝试创建一个索引列并使用 vlookup 返回索引，然后进行简单的减法，但 vlookup 仅限于返回名称的第一个实例而不是最新的实例。

谢谢

编辑：我的问题不是找出两个日期之间的天数，而是无法确定日期 A 在数据集中的位置，即此人上次访问的时间。如果我能找到日期 A（上次访问日期），我就可以从日期 B（当前访问日期）中减去它。

编辑 2：感谢大家的回答。每个回答都有助于我增长知识。感谢你们强调在问题中包含了期望结果，下次我会记住这一点。

答案1

最好的方法？取决于你想要的结果，而你在问题中没有说明

如果你

  • 希望表格按照您显示的方式填写，
  • 你说的意思是距离上次访问DAYS SINCE LAST VISIT 间隔多少天，today

然后，假设 DATE 列中的日期是“真正的 Excel 日期”，而不仅仅是文本字符串：

（使用表格和结构化引用，如果愿意，您可以将其更改为常规寻址）：

=TODAY()-MAX(([NAME]=[@NAME])*[DATE])

在此处输入图片描述

算法

  • 将公式行上的名称与所有名称进行比较

  • 这将返回一个数组，如果它们匹配，则返回 TRUE，否则返回 FALSE

  • 将每个数字乘以日期。

    • 如果为 TRUE，则返回实际日期，否则0

  • 结果是与名称匹配的所有日期的数组

  • 从 TODAY() 中减去匹配日期的最大值

  • 您可以TODAY()用任何其他日期替换以进行比较。

    • 如果这样做，我建议您使用输入日期的特定单元格，或者，如果您将其硬编码到公式中，请使用该DATE函数以避免由于 Windows 区域设置而产生的歧义。

  • 如果这些日期不是真实日期，而仅仅是文本字符串；并且如果 MDY 顺序与窗口区域短日期设置相同，那么要首先将它们转换为真实日期，您可以使用：

     =TODAY()-MAX(([NAME]=[@NAME])*SUBSTITUTE([DATE]," ","-"))

如果你的意思还有其他，请更具体一点并提供一个例子。

笔记： 其中一个问题是您还没有明确您想要的结果。

下面的屏幕截图显示了除上面用公式显示的结果之外的几种可能结果。较大的表格是使用 Power Query 生成的。

其他结果是可行的，但您需要详细地决定您真正想要的是什么。

在此处输入图片描述

答案2

在此处输入图片描述

确保您的日期确实存储为日期。屏幕截图中的 C 列将空格分隔的日期部分转换为日期

=DATE(RIGHT(A2,4),MID(A2,4,2),LEFT(A2,2))

上次访问的计算是“过滤此人当前行日期之前的所有日期。然后获取该日期列表中的最大值。如果出现错误，则表示没有其他日期，因此返回空白”

=IFERROR(MAX(FILTER($C$2:$C$6,($B$2:$B$6=B2)*($C$2:$C$6<$C2))),"")

自上次访问以来的天数的计算就是上次访问减去当前行的日期。

=IF(D2<>"",C2-D2,"")

您当然可以将所有这些组合成一个公式。

答案3

使用辅助列和一些数组（CSE）公式解决了该问题：

：警告：

  • 下面显示的方法查找自上次访问以来的天数，任何次数的访问。
  • 在下面使用的示例数据库中，Alex 有 4 次访问，因此应该计算天数差异在 vist4 和 visit3 之间。

在此处输入图片描述

怎么运行的：

  • 对于辅助值，单元格 J2 中的公式为：

    =I2&COUNTIF($I$2:$I2,I2)

注意： 这使得查找人员的访问工作更加容易。

  • 单元格 K2 中的 Ar 数组（CSE）公式：

    {=IFERROR(INDEX($I$2:$I$10, MATCH(0,COUNTIF($K$1:K1, $I$2:$I$10), 0)),"")}

  • 要查找最后访问日期，请在单元格 L2 中使用数组（CSE）公式：

    {=IF(MAX(IF($I$2:$I$10=K2,$H$2:$H$10))=0,"",MAX(IF($I$2:$I$10=K2,$H$2:$H$10)))}

  • 现在单元格 M2 中的最终数组（CSE）公式：

    {=IF(IF(L2<>"",L2-LARGE((($H$2:$H$10)*(--($I$2:$I$10=K2))),2))=0,"",(IF(L2<>"",L2-LARGE((($H$2:$H$10)*(--($I$2:$I$10=K2))),2),"")))}

注意：

  • 日期格式为mm/dd/yy

  • 使用以下方式完成数组（CSE）公式Ctrl+Shift+Enter& 向下填充。

  • 在单元格 M2 中使用带公式的函数时LARGE，始终将钾按正确顺序排列的值。

    =LARGE((($H$2:$H$10)*(--($I$2:$I$10=K2))),2)

  • 在哪里钾th 值1查找最新日期，在 ALEX 使用的数据集中，最新日期是06/02/2020，钾th 值2查找第二个最新日期，即05/05/20其他日期也是如此。

您可以根据需要调整公式中的单元格引用。

相关内容