☰

Excel で複数の列を合計するにはどうすればいいですか?

tag-icon • tag-icon microsoft-excel worksheet-function date-time
Excel で複数の列を合計するにはどうすればいいですか?

日付/時刻 (Excel のmm/dd/yyyy hh:mmカスタム形式としてインポート) とステータス 1 または 0 の 2 つの列を含むコンマ区切りのファイルがあります。ステータスは、機器がオンかオフかを表します。日ごとの時間の増加と減少を示すグラフを生成しようとしています。

考慮する：

1/1/2012 00:00, 1
1/1/2012 03:00, 0
1/1/2012 14:00, 1
1/3/2012 00:00, 0

これは、機器が 3 時間稼働し、11 時間停止し、その後 34 時間稼働していた (2 暦日にわたって) ことを示しています。ただし、1 日あたり稼働または停止していた時間数を示すグラフを生成したいと思います。

考慮する：

1/1 XXXXXXXXXXXXX-----------   (up 13, down 11)
1/2 XXXXXXXXXXXXXXXXXXXXXXXX   (up 24)

私には、カレンダーの日ごとにステータスごとに時間を合計したデータセットを生成する必要があるように思えますが、SUM(IF(SUMIF(...)))それを機能させるためのピボット テーブルやネストされた組み合わせを見つけることができないようです。

最も厄介なのは日付の変更を考慮することです...上記の例では、2012 年 1 月 1 日の 14:00 に開始する稼働時間が真夜中をまたぐため、2012 年 1 月 1 日で 10 時間の稼働時間が合計され、2012 年 1 月 2 日で 24 時間の稼働時間が合計されることを知っておく必要があります。

カレンダー リストを使用して日付の合計を計算できるかもしれませんが、その場合は、等しいものとして比較する方法が必要です01/01/2012。01/01/2012 03:00という方法があるはずですif(INTEGER-PORTIONS-OF-SERIAL-DATES-ARE-EQUAL,TOTAL-HOURS-IF-VALUE-IS_1,0)が、これまでのところ何も機能していません。

何か提案はありますか? 私は一日中この問題と格闘しており、新たな視点が必要です。

ありがとう

答え1

うわー、これは難しい！もっとうまく説明できれば良いのですが、どうか我慢してください。

私の解決策では、追加のスペース (正確には 3 列) が必要であり、いくつかの仮定に基づいています。

  1. タイムスタンプ データには 1 と 0 の行が交互に含まれ、説明したようにオン/オフ スイッチをエミュレートします (下の画像 1 を参照)。
  2. タイムスタンプは古いものから新しいものの順に並べられます。

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

わかりやすくするために、ここでは時間単位を使用しています。ただし、必要に応じて分単位のタイムスタンプを追加することもできます。

列E各タイムスタンプでその日の残り時間数を追跡します。私はこれを使用して、真夜中をまたぐ経過時間を「分割」しました。

ON/OFF列

ON 列の数式は次のとおりです。

=SUM(IF(AND($B3,A4<>"",INT($A4)=INT($A3)),($A4-$A3)*24,0),
     IFERROR(IF(AND($B3=1,INT($A3)<>INT($A4)),$E3,IF(AND($B2=1,INT($A3)<>INT($A2)),24-$E3,0)),0))

この数式は 2 つの値を加算します。

1.エントリがその日の最後のタイムスタンプでない場合はスイッチがオンになっている時間数。それ以外の場合は 0。

2.残り時間数それまで真夜中なら最後その日のタイムスタンプまたは経過時間数以来真夜中なら初めその日のタイムスタンプ。

OFF 列の数式も同様ですが、Status = 0 かどうかをチェックします。

=SUM(IF(AND(NOT($B3),B4<>"",INT($A4)=INT($A3)),($A4-$A3)*24,0),
     IFERROR(IF(AND($B3=0,INT($A3)<>INT($A4)),$E3,IF(AND($B2=0,INT($A3)<>INT($A2)),24-$E3,0)),$B3*($A3-INT($A3))*24))

タイムスタンプ列から日付を抽出するために、INT()関数を使用しました。Excel では、日付を表すのに整数を使用し、時間 (1 日または 24 時間の割合) を表すのに小数を使用します。たとえば、は(午前 0 時を 18 時間過ぎた時間は 18/24 で、0.75)06/01/2012 18:00に等しくなります。41061.75これがあなたの最後の段落の答えだと思います。

時間を統合する

ここに式があります営業時間列（下の画像を参照）。これは配列数式なので、Ctrl+ Shift+を使用して入力しEnter、下にコピーする必要があります。

=SUM((INT(stamps)=$G3)*hours_on)

の中に営業時間外カラム：

=SUM((INT(stamps)=$G3)*hours_off)

ここ
stampsで、名前付き範囲はタイムスタンプ範囲（列あ私の例では、
hours_on名前付き範囲はの上範囲（列Ｃ）は
hours off 、オフ範囲（列だ）

チャート

各日付の ON 時間と OFF 時間の合計が 24 になることに注意してください。

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

数式とワークブックを勉強したい場合は、次のコピーをご覧ください。http://db.tt/KZgH7SFV

答え2

Kaze の提案に従って、A3:A24 にタイムスタンプがあり、B3:B24 にステータスがあると仮定すると、ON次の式を使用して D3 の日付の[10 進] 時間を取得できます。

=(LOOKUP(D3+1,A$3:B$24)-SUMPRODUCT((INT(A$3:A$24)=D3)+0,MOD(A$3:A$24,1),B$3:B$24*2-1))*24

「ヘルパー」列は必要ありません。

答え3

あまりきれいではありませんが、解決策は次のとおりです。

2つの列の後に、day([datecolumn])として定義された3番目の列を追加します。

       A       B    C  D
   ---------------------
1 | 1/1/2012 00:00, 1, 1
2 | 1/1/2012 03:00, 0, 1
3 | 1/1/2012 14:00, 1, 1
4 | 1/3/2012 00:00, 0, 3
 ...
99| 1/31/2012 11:23, 1,31

次に、合計列を次のように定義します。

sumif(D1:D99, 1, C1:C99)

上記のケースでは、列 D (日付時刻の「日」部分として以前に定義) が 1 の場合にのみ、1 の合計が表示されます。その数式を月内の各日 (あまり好きではない部分) に対して繰り返すと、値が表示されます。

月1日の合計時間

sumif(D1:D99, 1, C1:C99)

月の2日の合計時間

sumif(D1:D99, 2, C1:C99)

月3日の合計時間

sumif(D1:D99, 3, C1:C99)

等々...

関連情報