☰

日付範囲に基づいたデータの検索と挿入

tag-icon • tag-icon microsoft-excel worksheet-function
日付範囲に基づいたデータの検索と挿入

で別の投稿エントリに関連付けられた日付が別のシートの日付よりも小さい場合に、エントリを検索して挿入するのに役立つ数式が指定されました。数式は次のとおりです。

=IF(

    INDEX([LargerSheet!*Range with Dates*],

       MATCH(

         IF([SmallerSheet!*First Date in Range*]<[LargerSheet!*First Date in Range],
         [SmallerSheet!*Range with Dates]),

       [SmallerSheet!*Range with Dates*],0)

    )>0,1,"")

しかし、これは期待通りには機能しませんでした。私の問題が少し違うだけなのではないかと思っています。基本的に、SmallerSheet の特定のエントリについて、LargerSheet の複数のオブジェクトに関連付けられた日付をチェックし、Most Recent Date < SmallerSheet Date の場合にのみ、LargerSheet から最新の日付を返すことを実行したいと思います。上記の数式が現在行っていることは、SmallerSheet の日付が LargerSheet の日付の少なくとも 1 つよりも新しいかどうかをチェックすることだと思います。その結果、各 SmallerSheet エントリには、LargerSheet 内の非常に過去に遡る複数のエントリが関連付けられているため、ステートメントは常に true になります。これは簡単に修正できますか?

答え1

LargerSheet 内のデータを並べ替えることができると仮定すると、次のように問題を解決できます。

まず、LargerSheet でカスタム ソートを実行します。最初に名前 (AZ) でソートし、次に日付 (古い順から新しい順) でソートします。これで、同じ名前のエントリがすべてグループ化され、各グループの最後のエントリがその名前の最新の日付になります。

--A-- --B-- Alice 2003-08-20 Alice 2005-01-01 Alice 2006-05-16 Bob 2001-08-19 Bob 2003-01-01 Bob 2004-05-15 Charlie 2004-08-19 : :

次に、SmallerSheet の各名前の横の列で、次の数式を使用します (LargerSheet と同様に、名前が列 A にあり、日付が列 B にあると仮定します)。空白とコメントを削除します。

=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.

これは、LargerSheet の最新の日付が SmallerSheet の日付 (名前ごと) より小さい場合はその日付を返し、そうでない場合は SmallerSheet の日付を返します。

日付の選択にさらに条件を追加する必要がある場合は、「AND」および「OR」論理関数を使用するか、テスト自体を変更してください。たとえば、次の数式の変更により、SmallerSheet の日付から -2 日以内であり、かつ LargerSheet の C 列の値が 0 より大きい場合に限り、LargerSheet から最新の日付が返されます。ご覧のとおり、数式は扱いにくくなっているため、括弧を一致させ、構文をチェックするように注意する必要があります。複数の列を使用して数式を段階的に分割すると役立つ場合があります。

=IF(AND(INDIRECT("LargerSheet!$B"&MATCH($A1,LargerSheet!$A:$A,1))>=($B1-2),INDIRECT("LargerSheet!$B"&MATCH($A1,LargerSheet!$A:$A,1))<$B1,INDIRECT("LargerSheet!$C"&MATCH($A1,LargerSheet!$A:$A,1))>0),INDIRECT("LargerSheet!$B"&MATCH($A1,LargerSheet!$A:$A,1)),$B1)

最後に、この問題に別の方法でアプローチすることもできます。つまり、SmallerSheet 内の名前の単一エントリを検索する列を LargerSheet に追加し、テストを実行して、テストに基づいて TRUE/FALSE 値を返します。

答え2

MATCH は最初の関数を返すため、リストしたものとは異なる動作をする数式を紹介します。

この数式は配列数式なので、次のように入力します。Ctrl+Shift+Enter キー。

これは、大きなテーブルが列 A にあることを前提としています。比較する日付はセル E2 にあります。

=MAX(IF($A$1:$A$33<E2,$A$1:$A$33,0))

大きなテーブルの各セルに対して、if ステートメントを実行します。セルが E2 より小さい場合は、その日付 (Excel では数値と​​して保存されます) を返し、セルが E2 より大きい場合は 0 を返します。これらすべての if ステートメントの最大値を取得して、E2 より小さい最大の日付を返します。

関連情報