エクセルについて

質問

下記のデータについての質問です。
Ａ列に日付、Ｂ列に時間（1時～０時）そしてＣ列は空白か
もしくは数字が入っているデータが10年分あります。

10年分ですので
２４時間×３６５日×10年ですので87600行になります。
更に閏年分一回につきプラス24行になるはずなのですが

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

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

マクロでも良いので、どなたかご教授いただけませんでしょうか？

回答No.6

tsubuyuki · ベストアンサー

Sub sample2()
Dim d As Long, i As Long
Dim dt1 As Long, dt2 As Long
　　　　For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
　　　　　　　　If IsDate(Cells(i - 1, 1)) = False Then Exit For

　　　　　　　　dt1 = (Cells(i, 1) + Cells(i, 2)) * 24
　　　　　　　　dt2 = (Cells(i - 1, 1) + Cells(i - 1, 2)) * 24
　　　　　　　　d = dt1 - dt2

　　　　　　　　If d > 1 Then
　　　　　　　　　　　　Cells(i, 1).Resize((d - 1), 3).Insert Shift:=xlDown
　　　　　　　　End If
　　　　Next
End Sub

お礼

完璧です。
ありがとうございました。

回答No.5

tsubuyuki

> マクロでも良いので、どなたか

とのことですので、マクロについてはある程度おわかりなのでしょう。
と判断させていただき、無駄な解説は省きます。

Sub sample()
Dim d As Long, i As Long
　　　　For i = Cells(Rows.Count, 1).End(xlUp).Row To 2 Step -1
　　　　　　　　If IsDate(Cells(i - 1, 1)) = False Then Exit For

　　　　　　　　d = Cells(i, 1) - Cells(i - 1, 1)
　　　　　　　　If d > 1 Then
　　　　　　　　　　　　Cells(i, 1).Resize((d - 1) * 24, 3).Insert Shift:=xlDown
　　　　　　　　End If
　　　　Next
End Sub

補足

ありがとうございます。

早速試したみましたところ

日付と時間がが飛んでるとこはちゃんと空白になりますが

同じ日で時間が飛んでる個所は空白にはならないようです。

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

回答No.4

KURUMITO

マクロというよりも関数で対応するのがおすすめです。
例えばシート１に元のデータが有るとします。
A２セルから下方には年を含めた日付が、B２セルから下方には１時から０時に終わる時刻が、C列にはデータが入力されているとします。
そこですべての日付を取り込んだお求めの表をシート２に表示させるとしたらシート２のA２セルには次の式を入力します。

=IF(Sheet1!$A$2="","",IF(DATE(YEAR(Sheet1!$A$2),MONTH(Sheet1!$A$2),DAY(Sheet1!$A$2)+(ROUNDDOWN((ROW(A1)-1)/24,0)))>MAX(Sheet1!$A:$A),"",DATE(YEAR(Sheet1!$A$2),MONTH(Sheet1!$A$2),DAY(Sheet1!$A$2)+(ROUNDDOWN((ROW(A1)-1)/24,0)))))

セルの表示形式を日付から選択します。
B２セルには次の式を入力します。

=IF(A2="","",TIME(HOUR(Sheet1!$B$2)+MOD(ROW(A1)-1,24)+1,0,0))

セルの表示形式を時刻から選択します。
C２セルには次の式を入力します。

=IF(A2="","",IF(COUNTIF(Sheet1!$A:$A,A2)=0,"",INDEX(Sheet1!$C:$C,MATCH(A2,Sheet1!$A:$A,0)+MOD(ROW(A1)-1,24))))

次にA２セルからC２セルを選択して右クリックし、「コピー」を選択します。
名前ボックスにはA２と表示されていますがその窓をA２:C８８０００のように変更して決定します。
それらの範囲が選択状態になりますので右クリックをして「貼り付け」を行います。

以上で完成です。

お礼

なんとか解決いたしました。

ありがとうございました。

補足

ありがとうございます。やってみましたが

なぜか日付が変わるのが、本来０時のはずが
２時の時点になります。

またＣ列のでデータがないところが「０」で表示されてしますので
データがない個所は空白にしたいのですが

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

回答No.3

hallo-2007

別シートに
　A　　　B　　C
日付　時刻　データ
と準備して
手入力で
日付と時刻を２行分くらい入力
あとは、１０年分を下までマウスでコピー
C列は
Vlookup関数かなにかで、元データのシートから引っ張ってこれないでしょうかね。
ご希望のシートになったら、コピーと値の貼り付けで固定して、元のシートを削除します。

一回こっきりの作業なら、わざわざVBAを開発するより手間がかからない内容です。

お礼

ありがとうございます。

参考にさせていただきます。

回答No.2

MarcoRossiItaly

直す分量も少ないので、手作業なら、次式を入力。87,600 行目付近まで下方向にオートフィル。

C2　true
C3　=a2+1=a3

オートフィルタを取り付け。C 列を「FALSE」で絞り込み。表示された数十行の行番号を（ドラッグではなく）1 行ずつ Ctrl キーを押しながらクリックしていく。全部選んだら、どれか 1 行の行番号を右クリック。「行の挿入」を実行。

それか、マクロで下のほうの行から挿入していきます。条件も簡単なので、マクロというほどのこともないですが。

お礼

ありがとうございます。

参考にさせていただきます。

回答No.1

aokii

とりあえず、D列にドラッグコピーで日付を入れて比較し、Ａ列の日付が飛んでいるのがどこなのか探して修正してはいかがでしょう。

お礼

ありがとうございます。

おっしゃるとおりなのですが

かなりの数になりそうなので

できれば関数かマクロで一括処理したく

質問させていただいた次第です。