☰

日付が別の日付より大きく、n 日以内の場合に日付範囲をインデックスする

tag-icon • tag-icon microsoft-excel worksheet-function
日付が別の日付より大きく、n 日以内の場合に日付範囲をインデックスする

で前の記事特定のエンティティに関連付けられた最新の日付を見つけ、その日付が Sheet2 の関連付けられた日付より大きい場合に、その値を Sheet2 に返す方法について質問しました。しかし、本当の問題は、条件が満たされた場合に最新の日付を返すことではなく、参照日より 2 日以内 (または通常は n 日以内) で、参照日より大きい場合に日付を返すことであると気付きました。

以前に生成された式は次のようになります。

=IF(
     INDIRECT("LargerSheet!$B"&        //Cell starting with "$B" and ending with
     MATCH($A1,LargerSheet!$A:$A,1)    //row of the last date for the name.
     )<$B1,                            //Compare with SmallerSheet date
     INDIRECT("LargerSheet!$B"&        //"Then" return LargerSheet date, 
     MATCH($A1,LargerSheet!$A:$A,1)
     ),$B1)                            //"Else" return SmallerSheet date.

< $B1INDIRECT() に別の条件を追加して、それが であるだけでなく、日付までの距離が であるかどうかも尋ねる方法はないでしょうか<2?

次の式を試してみましたが、うまくいきませんでした。

=IF(AND(ABS(INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1))-$A2)<2,
INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1))>$A2,INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1))>$A2),INDIRECT("'LargerSheet'!$L"&MATCH($K2,'LargerSheet'!$B:$B,1)),$A2)

サンプルシートは以下にありますここ。

答え1

問題がどこにあるのかを知るために、あなたの一連の質問を遡る必要がありましたが、（わずかな修正のみで）@Hannu の公式ほぼ正しいです。実装時に発生する問題は、スプレッドシートのレイアウトが@jbmorchの指定したレイアウトと一致していないことです。前の質問への回答。

レイアウトは @jbmorch の回答で非常に明確に指定されており、いくつかの理由で重要です。

  • 質問では、疑似数式のうちシート名を含む部分を除いて、スプレッドシートのレイアウトについてあまり詳しく指定していませんでした。そのため、@jbmorch や他のユーザーは、適切な数式をまとめるために推測して独自のレイアウトを作成する必要がありました。
  • 回答で指定されたとおりにシート内の列を順序付けないと、調整しない限り、シート内の列への参照は正しくなりません。
  • LargerSheet での並べ替え順序は、MATCH 関数の仕組み上重要です。回答で指定されたとおりに行が並べ替えられていない場合、MATCH は正確な結果を生成しません。
  • @jbmorch の回答でも、データが 1 行目から始まっていると想定されています (他に情報が提供されていなかったため)。したがって、回答はそのようなスプレッドシート用に書かれたものであり、実際のレイアウトに合わせて調整しないと誤った結果が生成されます。

他に注意すべき問題がいくつかあります。

  • すべての日付/時刻エントリが実際に日付と時刻として書式設定されていることを確認する必要があります。そうしないと、Excel は適切な比較を実行できません。これは、セル プロパティの書式オプションで確認できます。また、セルに対して計算を実行して確認することもできます。(例: A2 に が含まれ1/7/2003、B2 が の場合=A2+2、B2 の値は に解決される必要があります1/9/2003)。
  • サンプル シートには、LargerSheet から値を返すために探している条件に実際に一致する Sheet1 のエントリがほとんどありません。このため、トラブルシューティングが少し面倒になります。実際、一致するのは行 9 だけでした。(Sheet1 のその行の DAILY, JIM の日付は、実際には LargerSheet の彼の最新の日付から 2 日以内でした。)

とはいえ、必要な数式は次のとおりです。Sheet1 の C2 にドロップしてコピーします。

=IF(AND(INDIRECT("LargerSheet!$B"&MATCH($A2,LargerSheet!$A:$A,1))>B2,INDIRECT("LargerSheet!$B"&MATCH($A2,LargerSheet!$A:$A,1))<B2+2),INDIRECT("LargerSheet!$B"&MATCH($A2,LargerSheet!$A:$A,1)),$B2)

もう一度、シートのフォーマットに注意してくださいその通り以下のように、調整しないと数式は機能しません。

  • すべての日付しなければならないテキストや数値ではなく、日付としてフォーマットする必要があります。
  • 両方のシートしなければならない列 A に名前、列 B に日付があり、実際のデータは行 2 から始まります。
  • 大きいシートしなければならない名前（昇順）と日付（昇順）の両方で並べ替えられ、並べ替えの優先順位が設定されているその順番で。

他に注意すべき点は、「2 日以内」と「48 時間以内」の違いです。この数式では後者を使用します。つまり、5/6/2012 03:00:00Sheet1 に時間があり、LargerSheet の対応する値がある場合5/8/2012 03:00:01、IF ステートメントは FALSE と評価され、LargerSheet の値ではなく Sheet1 の値を返します。「48 時間以内の任意の時間」ではなく「今後 2 日間の任意の時間」を一致させたい場合は、数式を大幅に変更する必要があります。

また、IF ステートメントでは、以上ではなく排他的より大きい ( >) が使用されるため、完全に一致する場合は FALSE と評価されます。完全に一致する場合に TRUE と評価されるようにするには、>を に置き換えます>=。

答え2

ここでの注意: 私は IF() を表示して条件文を変更しているだけです。
これがうまくいかない場合は、少なくとも書き方や変更方法のアイデアが得られるかもしれません。

=IF(
     AND(INDIRECT("LargerSheet!$B"& //"$B"で始まり、"$B"で終わるセル
     MATCH($A1,LargerSheet!$A:$A,1) //名前の最終日付の行。
     )<$B1, // SmallerSheet の日付と比較
     INDIRECT("LargerSheet!$B"& //"$B"で始まり、"$B"で終わるセル
     MATCH($A1,LargerSheet!$A:$A,1) //名前の最終日付の行。
     )<($B1+2)), // SmallerSheet (date-2) と比較
     INDIRECT("LargerSheet!$B"& //"Then" は LargerSheet の日付を返します。
     MATCH($A1,LargerSheet!$A:$A,1)
     ）、$B1)

... 重要な追加は、とIF の最初のAND( condition1, condition2 )間です。IF(,

関連情報