主页 / computer / 问题 / 1877234
Accepted
burnersk
Asked: 2025-02-07 17:53:17 +0800 CST

根据给定的开始/结束日期范围，如何仅使用 Excel 公式查找匹配的单个日期？

  • 772

我有一个包含两个工作表的 Excel 文件（单个缺勤日期列表、以及带有日历周开始/结束日期和可用状态的日历周列表）。

（日期书写格式：年-月-日）

工作表中缺勤单次日期列表（Absence）：

工作表中缺勤单次日期列表

日历周的工作表列表，其中包含日历周的开始/结束日期和可用性状态Availability：

日历周的工作表列表，包括日历周的开始/结束日期和可用性状态

我的目标是让工作表Status中的列Status显示包含日期范围内（Monday至Sunday列）的不同天数（0-7）。

例如，给定一个日历周：

  • 当工作表列表中没有日期Absence介于当前给定日历周之间（含）时，则该Status列应显示0，
  • 当工作表列表中有一个不同的日期Absence位于当前给定日历周之间（含）时，则该Status列应显示1，
  • 当存在一个不同的日期，但同一日期列出两次时，在Absence工作表列表中，该日期位于当前给定日历周之间（包括当前日历周），则该Status列应显示1，
  • 当工作表列表中有两个不同的日期Absence位于当前给定日历周之间（含）时，则该Status列应显示2，
  • 当工作表列表中有七个不同的日期（整个星期）Absence位于当前给定日历周之间（包括）时，该Status列应显示7。

重要提示：上限（Sunday）可能会更改为星期五，因此，最好使用动态函数，而不是六次添加一天并再次检查。

独特性：由于用户错误，用户可能会多次输入相同的日期。公式应采取措施，两次或三次提及2025-12-31不应计为2 次，而应计为1 次。

在给定的示例中，CW1 将显示Status“1”（CW 内的一个不同日期），而所有其他日期将显示“0”（在示例中，没有二月/三月/四月的 CW）。

稍后，0-7 之间的数字将成为突出显示整个日历周行的指示，但这不是本问题的主题。

我知道LOOKUP/VLOOKUP函数，但我的情况是“反向查找逻辑”，我甚至不知道如何开始编写公式/函数来解决我的问题。也许我想得太多了，有一个简单的解决方案，但我现在看不到。

microsoft-excel

2 个回答

  1. Best Answer

    以下是一种方法：

    COUNT(FILTER(UNIQUE(Absence!$D$2:$D$365),(UNIQUE(Absence!$D$2:$D$365)>=C2)*(UNIQUE(Absence!$D$2:$D$365)<=D2),""))

    样本：

    结果

    或者稍微容易维护一点：

    =LET(_abs,UNIQUE(Absence!$D$2:$D$365),
_FILTER,FILTER(_abs,(_abs>=C2)*(_abs<=D2),""),
COUNT(_FILTER))

    两者都需要被拖下来。

    <编辑：稍微改变公式以包括开始和结束日期：<=

    编辑2：重复日期现在只计算一次；

    • 3

  2. 如果您使用标准 ISO 周数编号，也可以使其更简单一些：
    输入S2：=SUM(--(ISOWEEKNUM(UNIQUE(Absence))=P2)) 并复制下来。
    “缺席”表示缺席日期范围。 缺席

    如果您只想包含一周内选定的几天，您可以这样做（这里的工作日是周一到周五，周末是周六和周日），但在这种情况下不会更短（或者只会短一点点）：
    =LET(ua,UNIQUE(Absence),SUM((ISOWEEKNUM(ua)=P2)*NETWORKDAYS.INTL(ua,ua,"0000011")))

    • 2

相关问题