エクセルのマクロについて

質問

下記のデータについての質問です。

Ａ列に日付、Ｂ列に10分単位の時間（0:00～23:50）そしてＣ列は空白か
もしくは数字が入っているデータが10年分あります。

１０分単位で１０年分ですので
６×２４時間×３６５日×１０年ですので525600行になります。
更に閏年分一回につきプラス144行になるはずです。

しかし実際には途中何か所か日時が飛んでおり、またその部分が
空白になっておらず、詰まっているため行数が暦どおりより
少くなっております。

ここで質問なのですが
日時が飛んでいる個所を空白にして
行数を暦どおりにするにはどうすれば良いでしょうか?
また、この時Ｃ列も一緒にずらせるようにしたいのですが
どのようにすれば良いでしょうか？
よろしくお願いいたします。

マクロでも良いので、どなたかご教授いただけませんでしょうか？
ちなみにエクセルのバージョンは2010です。

回答No.5

cj_mover · ベストアンサー

＃１、ｃｊです。
＃１補足欄、拝見しました。明快なレスをご苦労様です。
比較的簡単な記述で済むケースでしたので、空いた時間で
既に書いてあったマクロから不要となった記述を削ってみました。

サンプルシートで実際に１０年分のデータに欠落を設けて、動作テストしました。
一応こちらが用意したサンプルデータでは、正しく動作していること検証済です。

基本的には、
[A列:日付][B列:時刻]については、飛んでいてもいいですけれど、
上から下まで空白なく連続していることが前提になります。
[A列:日付]の空白については、一部対策していますが、完全ではありません。
また、＃１の（４）～（７）の確認が違うことがあるとエラーまたは不正処理になる場合もあります。

処理に時間が掛かる処理を避ける為に、セルの値だけを書き写すように処理しています。
その為、セルの書式（フォント、塗り潰し、表示形式など）は、
値を移動した先には正しく反映されない場合があります。
既に設定済の書式などは、設定が列全体を対象にしているか確認して、
そうでない場合は事前に設定済の書式を列全体に拡張するようにしてください。
◆印でマークした２行は、日付・時刻データに対してセルの書式／表示形式を設定しています。
既に行単位で設定が済んでいる場合は、◆印の行は不要ですから削除して構いません。
　"yyyy/mm/dd"　→　2014/3/26
　"h:mm"　→　9:34
これら↑の指定方法は、Excelの表示形式と同じですので、必要なら書き換えてください。

以下、必ず、
標準モジュール（Module 1などの名前の付いた）にコピペして使ってください。
その際、以下の記述より上には、他の記述が無いように、
出来れば、他に記述が全くない状態で、貼り付けるようにしてください。

' ' **********標準モジュールに以下「ここから～ここまで」の全行を過不足なくコピペ**********

' ' **********ここから**********
Option Explicit

Private dtOrg As Date

Sub Re8528718c()
　　Dim mtxBuf
　　Dim nBtm As Long
　　Dim nRowDiff As Long
　　Dim nCurDiff As Long
　　Dim iRow As Long

' ' アクティブなブックに対して、シートを指定して選択する
　　Sheets("Sheet1").Select　　'　　■適宜指定：シート名

' ' 処理の遅さを軽減する為にアプリケーションの描画と再計算を抑止
　　Application.ScreenUpdating = False
　　Application.Calculation = xlCalculationManual

' ' 最初（最古）の日付
　　dtOrg = Cells(1, 1)　　'　　■適宜指定：確実に最初（最古）の日付が入っているセル
' ' Ａ列データ最下行（最終・最新の日付）の行位置、を整数で取得
　　nBtm = Cells(Rows.Count, 1).End(xlUp).Row
' ' 最下行データ（日付・時刻）が、本来あるべき行と、現在の行の差を求める
　　nCurDiff = DateTime2Index(Cells(nBtm, 1), Cells(nBtm, 2)) - nBtm

' ' エラートラップ
On Error GoTo ErrorOut_
' ' 以下、Ａ列データ最下行から１行めまで、下から上へと順次処理
　　For iRow = nBtm To 1 Step -1
' ' 各行データ（日付・時刻）が、本来あるべき行と、現在の行の差を求める
　　　　nRowDiff = DateTime2Index(Cells(iRow, 1), Cells(iRow, 2)) - iRow
' ' 日付データの筈が空白だった場合はその行の処理をスキップ
　　　　If nRowDiff > -1 Then
' ' 「本来あるべき行と、現在の行の差」が変化したならば、
　　　　　　If nCurDiff > nRowDiff Then
' ' 「本来あるべき行と、現在の行の差」が共通するセル範囲（Ａ：Ｃの３列）すべてについて
　　　　　　　　With Cells(iRow + 1, 1).Resize(nBtm - iRow, 3)
' ' 値配列を変数に格納しておいて
　　　　　　　　　　mtxBuf = .Value
' ' 値を消去
　　　　　　　　　　.Value = Empty
　　　　　　　　End With

' ' 変数に格納しておいた値配列を適切な位置に出力
　　　　　　　　Cells(iRow + nCurDiff + 1, 1).Resize(nBtm - iRow, 3).Value = mtxBuf

' ' 「本来あるべき行と、現在の行の差」現在の値を、直近の値として確保しておく
　　　　　　　　nCurDiff = nRowDiff
' ' 次に処理するべきセル範囲の最下行を記録
　　　　　　　　nBtm = iRow
　　　　　　End If
　　　　End If
　　Next iRow

' ' 日付・時刻データに対してセルの書式／表示形式を列単位で設定
　　Columns("A").NumberFormat = "yyyy/mm/dd"　　'　　■適宜指定：日付の表示形式　◆
　　Columns("B").NumberFormat = "h:mm"　　'　　■適宜指定：時刻の表示形式　◆

' ' エラー処理
ErrorOut_:
　　If Err.Number <> 0 Then MsgBox "不適切なデータが" & iRow & "行目にありました。" & _
　　　　　　　　　　　　vbLf & "一旦ブックをこのまま閉じ、最初からやり直してください。", _
　　　　　　　　　　　　vbExclamation
' ' アプリケーションの描画と再計算を再開
　　Application.Calculation = xlCalculationAutomatic
　　Application.ScreenUpdating = True
　　Beep
End Sub

' ' ///指定された日付・時刻のデータが本来あるべき行位置を整数で返します
Function DateTime2Index(ByVal dtDate As Date, ByVal dtTime As Date) As Long
　　DateTime2Index = (dtDate - dtOrg) * 144 + dtTime * 144 \ 1 + 1
End Function

' ' **********ここまで**********

お礼

早々にご回答くださりありがとうございます。
私の説明不足にもかかわらず、ご丁重に解説して下さり感謝いたします。

VBAで動作確認しましたら、希望の結果を得ることができました。
本当にありがとうございました

回答No.4

okormazd

#3です。

re = 2000　　　'最後のデータの行番号

として、とりあえず2000を指定しておいただけで、実際にはあなたのデータの最終行番号を指定しなければ、2000行しか処理しません。

A列の最後のデータまで空白がなく埋められているなら、最終行番号を自動的に取得するには、

re = .Cells(ri, 1).End(xlDown).Row

とします。これを、「re = 2000」と入れ替えます。
また、大きい行数を処理するので、Dim文の「integer」を「long」に変えます。

「あなたのデータの仕様が違えば修正が必要かもしれません。」と書いてあります。コードが基本的に間違っていたわけではありませんよ。コードの例が示されたら、自分で状況に応じていくらかの修正ができるように、少しVBAを勉強しましょう。

お礼

okormazd　様

何度もお手数おかけして申し訳ありませんでした。

おっしゃるとおりですね。
これを機会に少しは勉強したいと思います。
ありがとうございました。

回答No.3

okormazd

次のコードでできるはずですが、あなたのデータの仕様が違えば修正が必要かもしれません。「VBAは全くの素人」ということなので、あまりコードは示したくないのです。自分のやり方が間違っているのに、コードのせいにするのが非常に多いからです。うまくいかなかったらどのようになるのか補足してください。

Sub test()

Dim sh As Object, r As Integer, ri As Integer, re As Integer, hizuke0 As Date, hizuke As Date, jikoku0 As Date, jikoku As Date, jikoku1 As Date

Set sh = ActiveWorkbook.Sheets(1)

With sh

ri = 1　　　　　 '最初のデータの行番号
re = 2000　　　'最後のデータの行番号

hizuke0 = .Cells(re, 1).Value
jikoku0 = .Cells(re, 2).Value
jikoku0 = DateValue(hizuke0) + TimeValue(jikoku0)

For r = re - 1 To ri Step -1

hizuke = .Cells(r, 1).Value
jikoku = .Cells(r, 2).Value
jikoku = DateValue(hizuke) + TimeValue(jikoku)
jikoku1 = jikoku

While jikoku0 - jikoku1 > TimeValue("00:10:01")　　　 '間隔が10分より大きかったら、10分間隔といっても内部データがちょうど10分ではない場合があるので。
.Rows(r + 1).EntireRow.Insert
jikoku1 = jikoku1 + TimeValue("00:10:00")
Wend

jikoku0 = jikoku

Next

End With

End Sub

補足

okormazd　様

ありがとうございます。
さっそく試してみました。

2196行目以降のデータが修正されないままでした。
それ以前の行では歯抜け部分にしっかり空白行が
挿入されて正しい結果になっておりました。

お手数ですが、再度ご教授願えませんでしょうか？

回答No.2

okormazd

日付と時刻を読み込んで、連続になっていないところを連続になるように行数を挿入するだけでしょう。

VBAコードを示してくれということなのでしょうか。処理に時間がかかりそうだが、コード自体はそれほど難しくはないでしょう。

補足

okormazd　様

ありがとうございます。
VBAに関しては全くの素人ですので
コードを示していただければ大変助かるのですが

回答No.1

cj_mover

こんにちは。

マクロ（VBA）での対応になります。
条件によってはかなり複雑なものになってしまいますから、
無駄（過不足）のないものを書く為には、もう少し詳しい情報が必要です。
以下、必要な情報を整理して、質問として纏めましたので、補足をお願いします。
答えるのが難しいとか解らないとかなら、そう答えて貰えれば、
その部分は汎用化した（厚量な）もので対応します。
条件がどうであれ、必要なだけの手当てをして提示することはできると思っています。

****************************************************************************
（１）タイトル行は何行（または日付・時刻データは何行めから）ですか？
（２）"Ａ列""Ｂ列""Ｃ列"などは、実際の列位置ですか？
　　　もしも話を簡素化する為の仮のお話でしたらば、実際の列を教えてください。
（３）データは昇順ですか？降順ですか？（下の行に行くほど新しいとか、古いとか）
（４）日付は各行それぞれに、全行、入力されていますか？
　　　同じの日付が縦に１４４回（行）もれなく連続したブロックを形成しているか、
　　　それとも他の様式になっている、、、とか。
（５）日付が表示されたセルの実際の値には、時刻値は含まれますか？
　　例えば、
　　　・2014/3/26 と表示されているセルをクリックして数式バーで値を確認してみると、
　　　　実際は、2014/3/26 12:34:00 のように時刻を含んだ値が設定されているけれど、
　　　　セルの表示形式が"yyyy/m/d"に設定されているおかげで、
　　　　表示上は日付だけに見える場合もあります。（この場合は一手間余計に掛かります）
　　　・2014/3/26 と表示されているセルをクリックして数式バーで値を確認してみて、
　　　　実際の値も、2014/3/26 であれば、時刻値を含まない日付値だったことになります。（この方が簡単）
（６）時刻が表示されたセルの実際の値には、日付値は含まれますか？
　　例えば、
　　　・12:34 と表示されているセルをクリックして数式バーで値を確認してみると、
　　　　実際は、2014/3/26 12:34:00 のように日付を含んだ値が設定されているけれど、
　　　　セルの表示形式が"h:mm"に設定されているおかげで、
　　　　表示上は時刻だけに見える場合もあります。
　　　・12:34 と表示されているセルをクリックして数式バーで値を確認してみて、
　　　　実際の値も、12:34:00 であれば、日付値を含まない時刻値だったことになります。
　　　　（難易度に大差はないけれど、やり方がガラッと違って来ます）
（７）日付・時刻データのデータ形式は統一されていますか？
　　　データのすべてを確認するとかいうことではなくて、
　　　入力・編集の仕方が統一されているかどうか、です。
　　　複数の人が、めいめい勝手なやり方で入力している（下手すると日付に見える文字列値が混在しているかも）、とか、
　　　テンプレートやマニュアルがあるとか、プログラムなどで機械的に入力しているとか、、、。
　　　（統一された方が簡単、あてにならない場合でも、それなりに対策は可能です）
（８）今現在、"Ａ列""Ｂ列""Ｃ列"を計算の対象として、他のセルに書かれた数式（関数）から参照していますか？
　　　その場合現在の参照を維持することは必要ですか？
　　　　（難易度に大差はないけれど、やり方や処理速度が違って来ます。）
（９）> 日時が飛んでいる < 時刻が飛んでいる場合もある、という意味でしょうか？
　　例えば、
　　　　ある日付の時刻データが幾つか抜けている為、その日付のデータは１４４件（行）より少ない
　　　といった場合もあるのでしょうか？
（10）> 日時が飛んでいる個所を空白にして...行数を暦どおりにする <
　　　１４４行以上の空行が出来ることになりますが、扱いに困るようなことはないですか？
　　　必要なら、抜けている部分の日付・時刻を補完することも可能ではありますけれど、、、。
　　　（多少複雑にはなりますけれど）
（11）> また、この時Ｃ列も一緒にずらせるようにしたいのですが <
　　　A:C列を一緒にずらす、ということでいいのですよね？
　　　（"ずらす"ずらし方は（８）の答え次第です）
（12）最初（最古）の日付（の前日の日付）を基準に「何日目」であるか計算で求める必要があります。
　　　最初の日付が固定であるなら、その日付を、可変であるなら、どこに記されているかを、示してください。
（13）この処理は１回限りなのでしょうか？それとも幾つかのブック・シートで同じ処理をするとか、
　　　またいつか必要になるとか、、、。
（14）通常のマクロは現在表示中のシートに対して処理をします。
　　　対象のシートとは異なるシートで実行することの無いように備えたものを書いておいた方がエラーが無くなります。
　　　シートの名前が決まっているのでしたらシート名を教えてください。
（15）いつ頃までに解決したいとか、ありますか？
　　　今日は再びレスを書く暇がなさそうなので、お急ぎならごめんなさい。
****************************************************************************

結構な量になってしまいました。
この課題、解決に必要なるマクロを書くのには、条件次第で数分から数時間、
と、作成時間や難易度にかなりの開きが出ると思います。
また、結果の成否を手作業で検証することが難しい処理ですから、仕上げも慎重になります。
そういうこともあって、補足をお願いすることにしました。
目の前に実物があれば迷うこともないのですけれども、
実際ある程度のところまでは書いてはいたのですが、確認しないと決められないことが幾つもあって手が止まりました。

ひとまず、補足を待って、手が空き次第レスするつもりでいます。

補足

cj_mover様

ご質問ありがとうございます。
下記ご確認ください。
見当違いの回答があるかもしれません。ごめんなさい。

（１）一行目から日付・時刻データです。
（２）実際の位置です。
（３）昇順です。
（４）全行入力されております。
（５）クリックして数式バーで値を確認しましたが時刻は含まれておりません。
（６）クリックして数式バーで値を確認しましたが日付値は含まれておりません。
（７）統一されております。
（８）参照しておりません。
（９）時刻が飛んでる箇所もあり、その場合はご指摘のようにその日付のデータは
144行より少なくなっております。まる一日飛んでる箇所もあります。
（10）本来１４４行のはずが飛んでる箇所があるため１３９行とかになってるわけですので
その飛んでる箇所に空白行を挿入して本来の１４４行にするという意味ですので
１４４行以上にはならないと思います。挿入した行は空白のままで大丈夫です。
（11）そのとおりです。
（12）最初の日付はＡ列の一行目です。
（13）データを貼り付けて空白が挿入出来た結果をコピーして他ファイルに貼り付けて使用します。
上記作業を何回もやりますが、他のブック・シートで同じ処理をしたりはいたしません。
（14）そのままで「sheet1」です。
（15）今週中にはなんとかしたいと思っております。