☰

3つ前のイベントの日付を検索

tag-icon • tag-icon microsoft-excel worksheet-function
3つ前のイベントの日付を検索

基本的に、この数式では、タスクの最新の完了 3 件を見つけて、そのうち最も古い完了日を出力する必要があります。つまり、各タスクの 3 番目に最近の完了日です。

説明がわかりにくい場合は、次の例をご覧ください。

Date    | Task   | Count of Task
--------------------------
6/30/18 | Task A | 2
6/30/18 | Task B | 3
7/01/18 | Task A | 2
7/02/18 | Task B | 1
7/03/18 | Task B | 1
7/03/18 | Task B | 1
7/05/18 | Task A | 2
7/09/18 | Task C | 7

数式は以下を返す必要があります。

Task A: 7/1/18
Task B: 7/2/18
Task C: 7/9/18

タスク A: 7/5 (最新) に 2 件の完了があったため、3 番目に新しいのは 7/1 です。
タスク B: 7/3 (最新) に 2 件の完了があったため、3 番目に新しいのは 7/2 です。
タスク C: すべて同じ日付であったため、3 番目に新しいのは 7/9 です。

データ セットにはこのようなエントリが含まれるため、数式では同じ日付で同じタスクの 2 つの別々の行を処理できる必要があります。

解決策としては、Vlookup と sumif の組み合わせが必要になると思いますが、これは現在の私のスキルの範囲を超えています。

答え1

ここに画像の説明を入力してください

セルにC10この配列数式を書き込み、Ctrl+Shift+Enter& で終了します。

{=MIN(IF($B$3:$B$8=A10,$A$3:$A$8))}

答え2

  1. ピボットテーブルを作成します。

    • データを強調表示します。
    • [挿入] > [表] > [ピボットテーブル] に移動します。
    • テーブルを配置する場所を選択します。
    • 日付、タスク、カウントを確認します。
    • 「日付」を行に、「タスク」を列に、「カウント」を値にドラッグします。そして、カウントの合計を選択します (まだ選択されていない場合)。

  2. 日付 (行ラベル) を降順で並べ替えます。

  3. 累積合計を作成します。F3 に数式を入力します=SUM(B$3:B3)。そして H8 まで拡張します。
  4. 合計が 3 以上であることを示すブール値を作成します。I3 に数式を入力します=F3>=3。そして K8 まで拡張します。
  5. VLOOKUP では右側への検索が必要なので、日付を繰り返します。L3 に数式を入力します=$A3。そして N8 まで拡張します。
  6. VLOOKUPを作成します。I9に数式を入力します=VLOOKUP(TRUE, I3:L8,4,FALSE)。K9まで拡張します。

答えはI9からK9にあります。

このソリューションは多くのセルを占有しますが、設定は簡単です。ステップ 5 の数式を N8 に拡張する代わりに、J9 の VLOOKUP を 3 番目の列を参照するように変更し、K9 の VLOOKUP を 2 番目の列を参照するように変更するだけでよいことに注意してください。

値を含むスプレッドシート:

値を含むスプレッドシート

数式を含むスプレッドシート:

数式付きスプレッドシート

答え3

私の以前の回答と Rajesh S の回答を組み合わせたものです。

  1. 各タスクに関する累積合計を作成します。セルにD2書き込み=SUMIF($B2:B$9, B2, $C2:C$9) >= 3、セルまで入力しますC9。

  2. TRUE手順 1 でフラグが付けられた各タスクの最大日付を見つけます。セルにD11次の配列数式を書き込み ( を押しますCtrl+Shift+Enter)。

    =MAX(IF(($B$2:$B$9=B11)*($D$2:$D$9),$A$2:$A$9))

  3. D13 まで埋めます。

注意: ソリューションが機能するには、タスクが日付の昇順になっている必要があります。

数式付きスプレッドシート

関連情報