☰

返回值列表並與產品類型匹配

tag-icon • tag-icon microsoft-excel search-indexing
返回值列表並與產品類型匹配

我正在尋找一個公式來建立某些培訓的過期存取權限的報告。

目前我的桌子看起來像：

姓名 培訓1 培訓2 培訓3
約翰 2023年1月8日 2023年1月8日 2023年1月8日
標記 2023年1月8日 01/06/2023 2023年1月10日
史蒂夫 01/06/2023 01/06/2023 01/06/2023

我想創建一個機制，該機制將向我顯示從現在起 45 天內的培訓到期時間和到期時間，並包含名稱列表 - 模組和日期，例如：

45 天內到期：

姓名 模組 到期日
約翰 培訓1 2023年1月8日
約翰 培訓2 2023年1月8日
約翰 培訓3 2023年1月8日
標記 培訓1 2023年1月8日

已到期：

姓名 模組 到期日
標記 培訓2 01/06/2023
史蒂夫 培訓1 01/06/2023
史蒂夫 培訓2 01/06/2023
史蒂夫 培訓3 01/06/2023

我已設法獲取列出名稱的公式，但無法取得如何匹配標題或日期中的培訓。

=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日 01/06/2023 2023年1月10日
史蒂夫 01/06/2023 01/06/2023 01/06/2023
帳單 01/06/2023 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()函數。此外，我們也只選擇日期，即不使用另外的CHOOSEROWS和來選擇名稱和標題CHOOSECOLS。完成此操作後，我們使用 將“名稱”、“模組”和“日期”水平堆疊在一起HSTACK()。結果表如下：

A 乙 C
約翰 培訓1 2023年1月8日
標記 培訓1 2023年1月8日
史蒂夫 培訓1 01/06/2023
帳單 培訓1 01/06/2023
約翰 培訓2 2023年1月8日
標記 培訓2 01/06/2023
史蒂夫 培訓2 01/06/2023
帳單 培訓2 2023年1月8日
約翰 培訓3 2023年1月8日
標記 培訓3 2023年1月10日
史蒂夫 培訓3 01/06/2023
帳單 培訓3 2023年1月10日

下一步是一個簡單的FILTER()語句，用來過濾日期為 < 的資料TODAY()。為此，我們要根據第三列（即日期）過濾「combinedTable」。要在過濾器語句中使用此信息，我們使用該INDEX()函數。

如果要過濾未來 45 天內的數據，則需要FILTER()如下調整語句，以便僅過濾日期 >TODAY()和日期 <=的案例TODAY()+45：

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

一旦資料被過濾，我們就收集結果，即包含相關的標題並使用語句VSTACK()將標題與過濾後的資料組合起來。輸出是一個具有所有相關資訊的單一溢出數組，並且在單一單元格中只需要一個公式。最終輸出如下：

已到期：

姓名 模組 到期日
帳單 培訓1 01/06/2023
標記 培訓2 01/06/2023
史蒂夫 培訓1 01/06/2023
史蒂夫 培訓2 01/06/2023
史蒂夫 培訓3 01/06/2023

45 天內到期：

姓名 模組 到期日
帳單 培訓2 2023年1月8日
約翰 培訓1 2023年1月8日
約翰 培訓2 2023年1月8日
約翰 培訓3 2023年1月8日
標記 培訓1 2023年1月8日

如果要顯示中間步驟，只需將公式中的最後一個「結果」替換為定義的任何其他名稱，例如「combinedTable」、「dates」等。

相關內容