☰

一致する日付範囲に基づいてデータを返す

tag-icon • tag-icon microsoft-excel worksheet-function
一致する日付範囲に基づいてデータを返す

私たちのグループには、交代勤務の責任を追跡するために使用する Excel シートがあります。簡単な例は次のようになります。

  • 列A:始める各明細項目で表される期間の開始を定義する日付があります。
  • 列B:終わり各明細項目で表される期間の終了を定義する日付があります。
  • 列C:譲受人同じ行の START と END で定義された期間中に、特定の責任に割り当てられた人物を表す文字列が含まれます。

リスト自体はいつもの開始日で昇順で表示されます。ただし、シートが再配置される可能性があります。

リストで定義された期間の間に重複があってはなりません。

追加したいのは、3 つの値 (別々のセル) を持つ情報セクションです。

  • 前の現在の期間の直前の期間に対応する譲受人になります。
  • 現在現在責任を負っている譲受人になります。
  • 次現在の期間の後の期間に対応する ASSIGNEE になります。

スプレッドシートが適切な順序 (START の昇順) になっていると仮定すると、CURRENT の数式がわかれば、PREVIOUS と NEXT を返すのは簡単なはずです。しかし、どこから始めればよいのかさえよくわかりません。

答え1

私考えるこれは電流を見つけるのに有効です。ある程度テストしましたが、推測するこの場合、1 つの特性を無視しても問題はありません。

まず、B と C の間に列を追加します (Assignee は今後 D になります)。この列に次の数式を入力します。

=IF(NOW()-A2>0,IF(NOW()-B2<0,"Yes","No"),"No")

これにより、1 行が「はい」、残りが「いいえ」になります (現在のスロットの場合)。

ここで、現在の担当者のセルに次の内容を入力します。

=INDEX($C$2:$D$4, MATCH("Yes", $C$2:$C$4,0), 2)

前へ: =INDEX($C$2:$D$4, MATCH("Yes", $C$2:$C$4,0) - 1, 2)
次へ: =INDEX($C$2:$D$4, MATCH("Yes", $C$2:$C$4,0) + 1, 2)

通常、MATCHは検索列をソートすることを望みますが、列には常に1つのYesだけが存在する必要があるため、考える(これは推測の部分です) その制限は無視できます。

私のテスト用スプレッドシートは 3 行だけだったので、結果は異なる場合があります。

現在の行が最初の行である場合などに、前の行に対して何らかのエラー チェックが必要になるため、ソース範囲に名前を付けることはおそらく良い考えです。

余分な列を非表示にすることもできます。

答え2

順序付けられた入力:

現在の担当者: currRow=match(now(),A:A,1)- データが順序付けられているため、現在の行が検索されます。ヘルパー列は必要ありません。参照しやすくするために、これをカウントするセルの名前を currRow に設定します。
担当者: =indirect("C"& currRow + x)- x: 前の行、現在の行、次の行はそれぞれ -1、0、1 です。

順序なし入力:

現在の行: currRow=match(max(if(A:A>now(),"",A:A)),A:A,0)- これは配列数式なので、CTRL+SHIFT+ENTER で入力する必要があります。
現在の担当者: 前と同じ数式です。
前: =indirect("C" & match(max(if(A:A>=indirect("A" & currRow),"",A:A)),A:A,0))- これも配列数式です。
次: =indirect("C" & match(min(if(A:A<=indirect("A" & currRow),"",A:A)),A:A,0))- やはり配列数式です。

関連情報