☰

値のリストを返し、製品のタイプと一致させます

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

これを実現する 1 つの方法は、LET()中間結果を保存できるステートメントを使用することです。こうすることで、データを正しい形式で取得し、条件 (期限切れのトレーニングの場合は date < today) に基づいてフィルター処理することができます。

手順をより簡単に理解できるように、名前とモジュールの数を変えて例を少し拡張しました。データは次の範囲に保存されます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 回だけ指定すればよいため、ほとんどの場合これで十分です。その他はすべて自動的に計算されます。

次に、モジュールと行の数を、「データ」の列/行の数 - 1 として計算することから始めます。ただし、「名前」と「ヘッダー」は関連するモジュールまたは行ではありません。次のステップでは、モジュールのそれぞれの名称 (例: Training1-Training3) を として抽出しますtmpM。モジュールは、関連するモジュールと行の数に基づいて x 回繰り返す必要があります。これを実現するには、 と を組み合わせますINDEX()。MOD()同じSEQUENCE()手順を繰り返して、モジュールに基づいて必要な回数だけ名前を繰り返すそれぞれの名称シーケンスを作成します。最後に、すべての日付を 1 つの列に垂直に積み重ねる必要があります。これは、マトリックスにするのではなく、このために主に 関数を使用しますTOCOL()。さらに、 と を使用して、日付のみ (つまり、名前とヘッダーなし) を選択しますCHOOSEROWS。CHOOSECOLSこれが完了したら、 を使用して「名前」、「モジュール」、および「日付」を水平に積み重ねますHSTACK()。結果のテーブルは次のようになります。

あ B Ｃ
ジョン トレーニング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()。このために、3 番目の列、つまり日付に基づいて「combinedTable」をフィルターします。フィルター ステートメントでこの情報を使用するには、関数を使用しますINDEX()。

FILTER()今後 45 日以内のデータをフィルターする場合は、日付 >TODAY()および日付 <=を持つケースのみをフィルターするように、ステートメントを次のように調整する必要がありますTODAY()+45。

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

データがフィルタリングされたら、結果を収集します。つまり、関連するヘッダーを含め、ステートメントを使用してVSTACK()ヘッダーとフィルタリングされたデータを結合します。出力は、すべての関連情報を含む単一のスピル配列であり、1 つのセルに 1 つの数式のみが必要です。最終的な出力は次のようになります。

期限切れ：

名前 モジュール 有効期限
請求書 トレーニング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」など）に置き換えるだけです。

関連情報