☰

時間枠内の値を合計する

tag-icon • tag-icon microsoft-excel-2010
時間枠内の値を合計する

ここに画像の説明を入力してください毎日のフライト時間を表す 550 行のデータがあります。航空機の種類に応じて、各フライトには一定数の人員が必要です。そのタスクには 45 分かかります。したがって、特定のフライト時間には、そのフライトと、その前の 45 分以内のフライトで忙しい人がいることになります。任意のフライト時間に必要な総人数を集計します。また、45 分を入力変数として扱い、このタスク時間の変更が各フライト時間の人員需要にどのように影響するかを確認します。

データのモックアップは次のようになります。

     A            B         C.        D
1   2:30          4        0:45       4
2   2:45          3.                  7
3   3:15          2.                  9
4   3:30          4.                  9
5   3:35          5.                 11

列 A にはフライト時間が含まれています。列 B にはそのフライトに必要な人数が含まれています。セル C1 にはタスク時間の値が含まれています。列 D には、各フライト時間に必要なスタッフの総数を入力します。結果は入力された状態で表示されます。

計算例は次のようになります。行 4 を例に挙げます。フライト時間は 3:30 です。45 分のタスク時間に基づくと、2:45 以降のすべてのフライトでは同時にアクティブな人員が必要になります。そのようなフライトは 2:45、3:15、3:30 の 3 つあります。これらのフライトの列 B の人員は 3、2、4 なので、3:30 のフライト時点での合計人員要件は 9 です。これが D4 に入力されます。

C1 のタスク時間を 30 分に変更すると、3:15 と 3:30 のフライトのみが対象となるため、D4 の合計は 6 になります。

試してみましたSUMIF。SUMIFSデータ (時間) を含む行を追加し、その時間範囲内C1のすべてを合計しようとしました。列にデータを追加Bできないようです。B

私は試した

    =sumif(A:A,"<=A4",B:B)-sumif(A:A,"<=A4-C1",B:B)

答え1

ここに 1 つの解決策があります。これは D1 の数式です。これを入力して、D の他のセルにコピーします。

    =SUM(INDIRECT("b"&IF(ISNA(MATCH(A1-C$1,A$1:A1,0)),IF(ISNA(MATCH(A1-C$1,A$1:A1,1)),1,MATCH(A1-C$1,A$1:A1,1)+1),MATCH(A1-C$1,A$1:A1,0))):B1)

これが基本的なロジックです。条件を満たす最初の行を見つけ、その行と現在の行の間の B の値を合計します。最初の行を見つける方法は、MATCH 関数を使用して、列 A の時刻から C1 の時刻を引いた値を、現在の行までの行の時刻値と比較することです。MATCH には昇順の値の GE 検索がないため、LE と EQ の組み合わせを使用します。

  • LT がない場合は、行 1 が GE であることを意味し、それが最初の行になります。
  • 行が EQ の場合、それが開始行になります。
  • 行が LE だが EQ でない場合は、次の行が最初の GE であることを意味します。つまり、それが最初の行になります。

関連情報