エクセル・１秒ごとのデータを合計したい

質問

画像左のようなExcelファイルがあるとします。（現在Ｃ列は空白だと考えてください。）
Ａ１セルは、2011年04月01日の15時59分57秒に、「2」という値を得たという意味です。
その１秒後には「5」を、そしてさらに１秒後には「6」を得ていることになります。

今回、このExcelファイルから私が欲しい情報は、「１日に得ることのできた値の合計」です。
つまり、9月1日には「2655」を得て、9月2日には「1781」を得ることができた、といったデータを、Ｃ列のように出力して欲しいのです。

しかし、その合計値を表示する順番については気にしません。つまり、日付順や値の大きい順など、どんな順番で並んでいてもかまいません。

このような作業をこなすための数式などをご存知の方、是非ご教授ください。
使用しているのはExcel2010です。

回答No.6

MackyNo1 · ベストアンサー

No5の回答ですが、区切り位置で対処する場合の添付画像を忘れていました。

ちなみに、配列数式で対応する場合は以下のような数式になります。
（A1セルが開始日で1万行までのデータの場合）。

=SUM((LEFT(A1:A10000,8)=TEXT(TEXT(LEFT($A$1,8),"0000!/00!/00")-1+ROW(A1),"yyyymmdd"))*(IF(A1:A10000<>"",RIGHT(A1:A10000,1)))*1)

配列数式ですので、入力後Ctrl+Shift+Enterで確定して下方向にオートフィルしてください。

再計算に時間がかかる場合は、「ファイル」「オプション」「数式」からブックの再計算を「手動」にしておいて、コピー貼り付け後にF9キーで再計算させると良いと思います。

お礼

回答をくださってありがとうございます。
最小限の手間で、Excelに負担をかけずに目的の処理を行うことができました。
すばらしい回答をくださった方が多いのですが、こちらの方法で作業を進めていくことにしましたので、ベストアンサーとさせていただきます。

回答No.5

MackyNo1

ご希望の操作は配列数式を利用した関数でも表示できますが、今回のように、データが数万行に及ぶようなデータで、最終的に結果を表示するセル数が多くなると、再計算に時間がかかるなどの問題があります。
また今回のようなケースで補助列を使用して多数のセルに関数（特にセルごとに範囲を変更させたり、COUNTIFやMATCHなどの検索関数など）を入力すると、多量のメモリーを消費するためエクセルがハングアップするなどの問題が発生しますので関数だけで対応することはあまりお勧めできません（表示データ数が少ない場合は、配列数式を用いて計算したい時に再計算することで対応したほうが簡単な場合もあります）。

今回のケースでは、区切り位置の機能を利用してデータ処理し、それを簡単な関数で集計されることをお勧めします。

集計用シートのE1セルに以下の式を入力して下方向にオートフィルしておきます。
=TEXT(TEXT($A$1,"0000!/00!/00")-1+ROW(A1),"yyyymmdd")*1

同様にF1セルに以下の式を入力して下方向にオートフィルします。
=SUMIF(A:A,E1,C:C)

このように準備しておいたシートのA列に元データをコピー貼り付けし、「データ」「区切り位置」で「スペースによって右または左・・・・・」にチェックを入れ「次へ」をクリックし、日付と時刻の間と時刻と数字の間に区切りを入れ「完了」します。

この操作を自動的に行いたいなら、マクロの記録で、A列を選択して上記の区切り位置の操作を行い、これをボタンに登録させるなどするのが良いかもしれません。

回答No.4

某HN クロメート(Chromate)（@CoalTar）

数式入力をマクロ化してみた
Sub 数式で集計する()
　 　 '変数定義と最終行のセット
　 　 Dim n As Long
　 　 Dim i As Long
　 　 n = Cells(1, 1).End(xlDown).Row

　 　 '日付を取出し、数値化
　 　 With Range("B1:B" & n)
　 　 　 　 .Formula = "=TEXT(LEFT(A1,8),""0000-00-00"")*1"
　 　 　 　 .Value = .Value
　 　 End With

　 　 '目的の値を取出し、数値化
　 　 With Range("C1:C" & n)
　 　 　 　 .Formula = "=RIGHT(A1,LEN(A1)-FIND("" "",A1))*1"
　 　 　 　 .Value = .Value
　 　 End With

　 　 '連番を割り振り、数値化
　 　 Range("D1") = 1
　 　 With Range("D2:D" & n)
　 　 　 　 .Formula = "=(B2<>B1)+D1"
　 　 　 　 .Value = .Value
　 　 End With

　 　 '一個ずつ集計
　 　 For i = 1 To Range("D" & n)
　 　 　 　 Range("F" & i) = i
　 　 　 　 With Range("G" & i)
　 　 　 　 　 　 .FormulaR1C1 = "=SUMIF(C4,RC[-1],C3)"
　 　 　 　 　 　 .Value = .Value
　 　 　 　 End With
　 　 Next i

　 　 '不要列の削除
　 　 Columns("F:F").Delete
　 　 Columns("B:D").Delete
End Sub
添付図は、不要列を削除してない

お礼

回答をくださってありがとうございます。
マクロで処理する方法について、大変勉強になりました。感謝いたします。

回答No.3

KURUMITO

データの量が多くなれば計算も重くなりますのでできるだけ簡単な式を使って、作業列を使って対応することでしょう。次のようにしてはどうでしょう。
データは２行目から下方にあるとします。
E列からH列を作業列とします。
E２セルには次の式を入力します。

=MID(A2,8,1)*1

F２セルには次の式を入力します。

=RIGHT(A2,1)*1

G２セルには次の式を入力します。

=IF(E2<>E3,MAX(G$1:G1)+1,"")

H２セルには次の式を入力します。

=IF(E2="","",IF(E2<>E1,F2,H1+F2))

お望みのデータをC列に表示させるとしてC２セルには次の式を入力します。

=IF(ROW(A1)>MAX(G:G),"",INDEX(H:H,MATCH(ROW(A1),G:G,0)))

最後のC２セルからH２セルまでを選択してから右クリックして「コピー」し、その後に名前ボックスがC２となっていますがそこを例えば５００００行までのデータとしたらC2:C50000のように書き変えます。その後に「コピー」して貼り付けをすればよいでしょう。

お礼

回答してくださってありがとうございます。
数式をシンプルにする工夫に、目から鱗が落ちる思いです。

回答No.2

kagakusuki

>移動量の多い人のデータを読み込ませたところ、Excelがハングアップしてしまいました。

　ハングアップと言いますか、1秒間隔で1ヶ月分という事は、処理すべき元データが多過ぎて、処理にとんでもなく時間が掛かっているストールの状態の様な気もします。
　ですから、

>元データのスリム化、求めるデータの単純化を図り、再度質問を投稿させていただきました。

とは言っても、元データの数自体は変わりありませんから、ストールに陥らないとは言い切れませんが、取り敢えず、スリム化したデータに合わせて、前回の質問（QNo.7078597）のANo.4で述べた方法を、修正した方法を回答させて頂きますので、御試し下さい。

　まず、E1セルに次の数式を入力して下さい。

=IF(AND(ISNUMBER(1/(LEFT(INDEX($A:$A,ROW()),14)+0>19010101000000)),ISNUMBER(REPLACE(INDEX($A:$A,ROW()),1,FIND(" ",INDEX($A:$A,ROW())),)+0)),SUBSTITUTE(TEXT(LEFT(INDEX($A:$A,ROW()),14),"????-??-?? ??A??A??"),"A",":")+0,"")

　次に、F1セルに次の数式を入力して下さい。

=IF(ISNUMBER($E1),REPLACE(INDEX($A:$A,ROW()),1,FIND(" ",INDEX($A:$A,ROW())),)+0,"")

　以降は、QNo.7078597のANo.4と同一になります。

　次に、G1セルに次の数式を入力して下さい。

=IF(COUNTIF($E$1:$E1,">="&INT((0&$E1)+0))>1,IF(COUNT(INDEX($E:$E,ROW()-1),$E1,$F1)=3,$F1*($E1-INDEX($E:$E,ROW()-1))*24/3600,""),"")

　次に、H1セルに次の数式を入力して下さい。

=IF(OR($E1="",INT((0&$E1)+0)=INT((0&$E2)+0)),"",SUMIF($E:$E,"<"&INT($E1)+1,$G:$G)-SUMIF($E:$E,"<"&INT($E1),$G:$G))

　次に、C1セルに次の数式を入力して下さい。

=IF(ROWS($1:1)>COUNT($H:$H),"",LARGE($H:$H,ROWS($1:1)))

　次に、C1～H1の範囲をコピーして、同じ列の2行目以下に貼り付けて下さい。

　後は、元のテキストファイルの文字列をまとめてコピーして、A列に貼り付けて下さい。
　すると、C列に1日の移動量が、日付に関わらず、移動量が多い順に表示されます。

お礼

２度も回答してくださってありがとうございます。
回答をくださったことに感謝します。

回答No.1

-9L9-

前回の質問（QNo.7078597）にも書きましたが、集計やピボットテーブルじゃダメなんですか？
EXCEL2010なら約104万行までのデータが扱えるはずだし、データ量がそれを超えるのなら、ACCESSなどのデータベースソフトを使って、同じように集計機能やピボット機能を使えば簡単に集計はできるはずですが？

補足

ご回答をくださってありがとうございます。
集計機能・ピボット機能を使うことは考えたのですが、例えば「20110401155957」といった文字列から4月1日をうまく指定する方法がわかりませんでした。

また、作業量が膨大であることから、事前に数式のみを入力したシートを用意しておいて、Ａ列にテキストファイルからデータをコピーすれば結果が出力されるような状態にしたかったのです。

非常に勝手なお願いであることは承知していますが、Excelのスキルが乏しいためご容赦ください。