☰

返回值列表并与产品类型匹配

tag-icon • tag-icon microsoft-excel search-indexing
返回值列表并与产品类型匹配

我正在寻找一个公式来创建某些培训的访问权限到期的报告。

目前我的表格如下所示：

姓名 培训1 培训2 培训3
约翰 2023 年 1 月 8 日 2023 年 1 月 8 日 2023 年 1 月 8 日
标记 2023 年 1 月 8 日 2023 年 1 月 6 日 2023 年 1 月 10 日
史蒂夫 2023 年 1 月 6 日 2023 年 1 月 6 日 2023 年 1 月 6 日

我想创建一种机制，可以向我显示从现在起 45 天内培训是否到期以及是否已到期，并显示名称列表 - 模块和日期，例如：

45 天内到期：

姓名 模块 到期日期
约翰 培训1 2023 年 1 月 8 日
约翰 培训2 2023 年 1 月 8 日
约翰 培训3 2023 年 1 月 8 日
标记 培训1 2023 年 1 月 8 日

已到期：

姓名 模块 到期日期
标记 培训2 2023 年 1 月 6 日
史蒂夫 培训1 2023 年 1 月 6 日
史蒂夫 培训2 2023 年 1 月 6 日
史蒂夫 培训3 2023 年 1 月 6 日

我已经设法获得列出名称的公式，但我无法获得如何在标题或日期中匹配培训。

=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(B:D)/(B:D<TODAY()+45), ROW(1:1))),"") / expiring within 45 days
=IFERROR(INDEX(A:A,AGGREGATE(15,6,ROW(B:D)/(B:D<TODAY()), ROW(1:1))),"") / expired

如何重建公式来匹配每个人的培训标题和日期？

答案1

实现此目的的一种方法是使用LET()允许您存储中间结果的语句。这样，您就可以以正确的形式提供数据，然后根据您的条件进行过滤，例如，对于已过期的培训，日期 < 今天。

我稍微扩展了您的示例，使其具有不同数量的名称和模块，以使该过程更容易理解。数据存储在以下范围内A1:D5：

姓名 培训1 培训2 培训3
约翰 2023 年 1 月 8 日 2023 年 1 月 8 日 2023 年 1 月 8 日
标记 2023 年 1 月 8 日 2023 年 1 月 6 日 2023 年 1 月 10 日
史蒂夫 2023 年 1 月 6 日 2023 年 1 月 6 日 2023 年 1 月 6 日
账单 2023 年 1 月 6 日 2023 年 1 月 8 日 2023 年 1 月 10 日

该LET()声明如下：

=LET(
data,$A$1:$D$5,

nMod,COLUMNS(INDEX(data,1,))-1,
nRow,ROWS(INDEX(data,,1))-1,

tmpM,CHOOSEROWS(TRANSPOSE(INDEX(data,1,)),SEQUENCE(nMod,,2)),
modules,SORT(INDEX(tmpM,MOD(SEQUENCE(nMod*nRow)-1,nMod)+1)),

tmpN,CHOOSEROWS(INDEX(data,,1),SEQUENCE(nRow,,2)),
names,INDEX(tmpN,MOD(SEQUENCE(nMod*nRow)-1,nRow)+1),

dates,TOCOL(CHOOSEROWS(CHOOSECOLS(data,SEQUENCE(nMod,,2)),SEQUENCE(nRow,,2)),0,TRUE),

combinedTable, HSTACK(names,modules,dates),
filteredData,SORT(FILTER(combinedTable, INDEX(combinedTable,,3)<TODAY()),1),

result, VSTACK(HSTACK("Name", "Module", "Expiring on"), filteredData),
result)

例如，在本例中，您首先要指定数据的范围A1:D5。这可能会进一步自动化，包括COUNTA()语句或类似语句，但考虑到范围只需指定一次，在大多数情况下这就足够了。其他一切都是自动计算的。

接下来，我们首先计算模块和行的数量，即“数据”中的列/行数 - 1，假设“名称”和“标题”没有相关的模块或行。在下一步中，我们提取模块的相应名称（例如，Training1-Training3）作为tmpM。根据相关模块和行的数量，模块需要重复 x 次。为此，我们将INDEX()和MOD()和结合起来SEQUENCE()。重复相同的过程以创建相应的名称序列，该序列根据模块根据需要重复名称。最后，我们需要将所有日期垂直堆叠在一列中，而不是将其作为矩阵。为此，我们主要使用函数。此外TOCOL()，我们还使用和仅选择日期，即没有名称和标题。完成后，我们使用水平堆叠“名称”、“模块”和“日期” 。结果表如下所示：CHOOSEROWSCHOOSECOLSHSTACK()

A 乙 C
约翰 培训1 2023 年 1 月 8 日
标记 培训1 2023 年 1 月 8 日
史蒂夫 培训1 2023 年 1 月 6 日
账单 培训1 2023 年 1 月 6 日
约翰 培训2 2023 年 1 月 8 日
标记 培训2 2023 年 1 月 6 日
史蒂夫 培训2 2023 年 1 月 6 日
账单 培训2 2023 年 1 月 8 日
约翰 培训3 2023 年 1 月 8 日
标记 培训3 2023 年 1 月 10 日
史蒂夫 培训3 2023 年 1 月 6 日
账单 培训3 2023 年 1 月 10 日

下一步是一个简单的FILTER()语句，用于过滤日期为 < 的数据TODAY()。为此，我们希望根据第三列（即日期）过滤“combinedTable”。要在过滤语句中使用此信息，我们使用函数INDEX()。

如果要过滤未来 45 天内的数据，则需要FILTER()按如下方式调整语句，以便仅过滤 date >TODAY()和 date <=的情况TODAY()+45：

filteredData,SORT(FILTER(combinedTable,
(INDEX(combinedTable,,3)>TODAY())*(INDEX(combinedTable,,3)<=TODAY()+45)),1),

一旦数据被过滤，我们就收集结果，即包括相关的标题并使用语句VSTACK()将标题与过滤后的数据相结合。输出是一个包含所有相关信息的单一溢出数组，并且只需要在单个单元格中使用一个公式。最终输出如下所示：

已到期：

姓名 模块 到期日期
账单 培训1 2023 年 1 月 6 日
标记 培训2 2023 年 1 月 6 日
史蒂夫 培训1 2023 年 1 月 6 日
史蒂夫 培训2 2023 年 1 月 6 日
史蒂夫 培训3 2023 年 1 月 6 日

45 天内到期：

姓名 模块 到期日期
账单 培训2 2023 年 1 月 8 日
约翰 培训1 2023 年 1 月 8 日
约翰 培训2 2023 年 1 月 8 日
约翰 培训3 2023 年 1 月 8 日
标记 培训1 2023 年 1 月 8 日

如果您想显示中间步骤，您可以简单地将公式中的最后一个“结果”替换为定义的任何其他名称，例如“combinedTable”、“dates”等。

相关内容