![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?5a7ff87)
画像左のようなExcelファイルがあるとします。(現在C列は空白だと考えてください。)
A1セルは、2011年04月01日の15時59分57秒に、「2」という値を得たという意味です。
その1秒後には「5」を、そしてさらに1秒後には「6」を得ていることになります。
今回、このExcelファイルから私が欲しい情報は、「1日に得ることのできた値の合計」です。
つまり、9月1日には「2655」を得て、9月2日には「1781」を得ることができた、といったデータを、C列のように出力して欲しいのです。
しかし、その合計値を表示する順番については気にしません。つまり、日付順や値の大きい順など、どんな順番で並んでいてもかまいません。
このような作業をこなすための数式などをご存知の方、是非ご教授ください。
使用しているのはExcel2010です。
![「エクセル・1秒ごとのデータを合計したい」の質問画像](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/c/956168_5497c55d4a9f7/M.jpg)
No.6ベストアンサー
- 回答日時:
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キーで再計算させると良いと思います。
![「エクセル・1秒ごとのデータを合計したい」の回答画像6](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/8/1223543_5497e938b0dea/M.jpg)
回答をくださってありがとうございます。
最小限の手間で、Excelに負担をかけずに目的の処理を行うことができました。
すばらしい回答をくださった方が多いのですが、こちらの方法で作業を進めていくことにしましたので、ベストアンサーとさせていただきます。
No.5
- 回答日時:
ご希望の操作は配列数式を利用した関数でも表示できますが、今回のように、データが数万行に及ぶようなデータで、最終的に結果を表示するセル数が多くなると、再計算に時間がかかるなどの問題があります。
また今回のようなケースで補助列を使用して多数のセルに関数(特にセルごとに範囲を変更させたり、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
- 回答日時:
数式入力をマクロ化してみた
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
添付図は、不要列を削除してない
![「エクセル・1秒ごとのデータを合計したい」の回答画像4](http://oshiete.xgoo.jp/_/bucket/oshietegoo/images/media/b/655143_5497f06c4d291/M.jpg)
No.3
- 回答日時:
データの量が多くなれば計算も重くなりますのでできるだけ簡単な式を使って、作業列を使って対応することでしょう。
次のようにしてはどうでしょう。データは2行目から下方にあるとします。
E列からH列を作業列とします。
E2セルには次の式を入力します。
=MID(A2,8,1)*1
F2セルには次の式を入力します。
=RIGHT(A2,1)*1
G2セルには次の式を入力します。
=IF(E2<>E3,MAX(G$1:G1)+1,"")
H2セルには次の式を入力します。
=IF(E2="","",IF(E2<>E1,F2,H1+F2))
お望みのデータをC列に表示させるとしてC2セルには次の式を入力します。
=IF(ROW(A1)>MAX(G:G),"",INDEX(H:H,MATCH(ROW(A1),G:G,0)))
最後のC2セルからH2セルまでを選択してから右クリックして「コピー」し、その後に名前ボックスがC2となっていますがそこを例えば50000行までのデータとしたらC2:C50000のように書き変えます。その後に「コピー」して貼り付けをすればよいでしょう。
No.2
- 回答日時:
>移動量の多い人のデータを読み込ませたところ、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
- 回答日時:
前回の質問(QNo.7078597)にも書きましたが、集計やピボットテーブルじゃダメなんですか?
EXCEL2010なら約104万行までのデータが扱えるはずだし、データ量がそれを超えるのなら、ACCESSなどのデータベースソフトを使って、同じように集計機能やピボット機能を使えば簡単に集計はできるはずですが?
この回答への補足
ご回答をくださってありがとうございます。
集計機能・ピボット機能を使うことは考えたのですが、例えば「20110401155957」といった文字列から4月1日をうまく指定する方法がわかりませんでした。
また、作業量が膨大であることから、事前に数式のみを入力したシートを用意しておいて、A列にテキストファイルからデータをコピーすれば結果が出力されるような状態にしたかったのです。
非常に勝手なお願いであることは承知していますが、Excelのスキルが乏しいためご容赦ください。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) Excel 2019で質問があります。 計測器のデータをExcelで記録したんですが、1秒刻みで記録 4 2022/09/07 22:46
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Visual Basic(VBA) 顧客ごとに違う点検案内を作成するマクロ 4 2022/09/16 05:34
- Visual Basic(VBA) VBA 毎日取得するデータを順番に反映していく方法 6 2023/08/26 16:22
- Visual Basic(VBA) 指定月分の顧客データファイルを統合して並べ替え、所定の場所に貼り付ける 3 2022/09/10 07:55
- システム CSVファイルのマッピング処理の省力化 1 2022/11/24 00:01
- Excel(エクセル) Excel VBAどこが間違ってますか? 4 2023/07/17 10:04
- Excel(エクセル) マクロか関数で処理したいのですが、教えて頂けませんか。 8 2022/10/31 15:18
- Visual Basic(VBA) ExcelからAccessのテーブルに書き込む時に時間がかかる 1 2022/10/14 20:38
- Visual Basic(VBA) VBAコードを張り付け後のエクセルの進め方 2 2023/02/07 18:24
関連するカテゴリからQ&Aを探す
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
「生産性ソフトウェア」とは何...
-
会社のOutlookにてメールを予約...
-
英数字のみ全角から半角に変換
-
Microsoft familyに追加されま...
-
Outlook で宛先が複数の場合の人数
-
【関数】○年○ヶ月と表示された...
-
エクセルでXLOOKUP関数...
-
VBAファイルの保存先について
-
Outlookを立ち上げたらGoogleロ...
-
outlookのメールが固まってしま...
-
Microsoft Formsの「個人情報や...
-
マイクロソフト 一時使用コード...
-
会社PCのメールが更新されない
-
Excel テーブル内の空白行の削除
-
teams設定教えて下さい。 ①ビデ...
-
タブレット Canva
-
office365って抵抗感ないですか?
-
VBAで横データを縦データに変換...
-
Outlook 電源OFFの受診の仕方
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
英数字のみ全角から半角に変換
-
Excelで空白以外の値がある列の...
-
会社PCのメールが更新されない
-
Excel 日付を比較したら、同じ...
-
マイクロソフト 一時使用コード...
-
ウィンドウィズ メモ帳で日付だ...
-
MicrosoftOfficeの1ユーザー2...
-
Microsoft Formsの「個人情報や...
-
Officeの字体
-
エクセルでXLOOKUP関数...
-
Microsoft365で自動保存が出来...
-
Outlookで、任意のメールアドレ...
-
outlookのメールが固まってしま...
-
Microsoft 365 の一般法人向け...
-
Office2021を別のPCにインスト...
-
Microsoft 365のディフェンダー...
-
Excelに貼ったリンクについて E...
-
MicrosoftOffice2019なんですが、
-
Outlook で宛先が複数の場合の人数
おすすめ情報