エクセル Do～LoopかFor Nextで・・・・

質問

いつもお世話になります。
記録マクロで行った部分です
Columns("R:R").Select
Selection.Insert Shift:=xlToRight
Range("R2").Select
ActiveCell.FormulaR1C1 = "=IF((RC[-4]=1),((RIGHT(RC[-1],8)*1)),(IF((RC[-4]>R[-1]C[-4]),(RIGHT(RC[-1],8))-(RIGHT(R[-1]C[-1],8)),(R[-1]C))))"
Selection.NumberFormatLocal = "[$-F400]h:mm:ss AM/PM"
Selection.AutoFill Destination:=Range("R2:R65536")
Range("R2:R65536").Select
Columns("R:R").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
これでいきますと、R列全てに対して計算してしまうので
隣が入力されている所までを判断して次の処理に入りたいと思っているのですが、いまいち分からないのです。
どうかよろしくお願いします。

回答No.9

Wendy02 · ベストアンサー

こんにちは。

一応、今回のものでコツがわかっていただけたと思います。

いくらVBAプログラミングとは言っても、今回の切り抜け方は、Excel独特の方法ではないかと思っています。

実は、まだ、ここらあたりには裏ワザがあります。それは、一般数式で可能な範囲はその方がよいのですが、配列数式は、Evaluate関数の中では、配列確定、Alt+Ctrl -> Enter をなしに使えます。非常にコードが少なくて便利なことがあります。

また、数式で入れる場合は、Excel 2003 以下は、配列数式は、セルの計算に使う延べ数　5,500個を目途にしたほうがよいことと、それ以上になると、スワップメモリが働いてしまいます。

一般数式は、仮に、列を全部範囲指定しても、データ範囲（Ctrl+Endの最終行）までしか認識しませんし、まったく空(Empty)状態の場合は、メモリに格納されません。ひとつのセルで、約20byte +文字数です。数式も文字数として換算されます。配列数式は、その数式で使われたセル数すべてがメモリの対象となります。ここが、配列数式との大きな違いです。

一般数式の場合は、値を格納していますから、参照セルの変更のあったところのみの、数式の結果が反映されます。だから、意外にメモリの使用量が少ないのですが、配列数式は、値を格納できませんので、一気に値を計算しなおそうとしてメモリを使用してしまいます。メモリというのは、仮想メモリのことで、物理メモリではありません。

今回、試験的にExcel 2007(Vista) で行ってみました。配列の上限数はなくなって、計算には、1つの演算プロセッサが使われたようですが、しかし、違った現象が現れました。

=IF(COUNTIF($A$2:$A$33930,A2)=1,b2,AVERAGE(IF($A$2:$A$33930=a2,$B$2:$B$33930)))｝

の式を、全部消して、一般の数式に直そうとしたら、ハングしてしまいました。何が原因かは分かりませんが、この数式は、無理だったのかなって思いました。

なお、
>dimの宣言を冒頭にしていませんが別段問題なく動作していますので・・・・

なくても問題はないのですが、Dimの宣言というのは、本質的には省メモリのためなのです。上記で書いたように、20byte +αというのは、Variant 型の変数のことです。それを特定の型で指定することで、消費メモリを減らすことができます。

しかし、本当に、Dim の宣言が必要になる時は、厳密な計算が必要になる場合です。もしも、計算が変だということに出会ったら、その時に、改めてDim宣言についてお調べになってもよいのですが、VBAの教本には詳しいことが出ていないかもしれません。「浮動小数点丸め誤差」という問題に対処しなくてはならない時があります。ワークシートは、ある程度の補正処理をしていますが、VBAでは、そういう補正がありませんので、自分で解決しなければなりません。

お礼

ありがとうございます。
今回の件は他方に色々と使えそうでもっとジックリ理解を深めたいと思います。　また、byte数の事まで考えて処理を考えてられるのは凄いですね
また、色々と質問をすることがあると思いますが　見かけたときはアドバイスよろしくお願いします。
　諸般の事情により以前よりレスポンスが悪くなっていますがご容赦ください。

回答No.8

Wendy02

こんにちは。

>今回教えていただいた数式を同じように組み込もうと思ったのですが
>範囲選択の仕方がわかりません。

(1)
=IF(COUNTIF($A$2:$A$33930,A2)=1,b2,AVERAGE(IF($A$2:$A$33930=a2,$B$2:$B$33930)))｝
---この数式、かなりの関数の猛者が作ったような内容ですが、配列にする必要がありませんでしたね。

(2)
= SUMIF($A$2:$A$33930,A2,$B$2:$B$33930)/COUNTIF($A$2:$A$33930,A2)

一度、試してみていただければよいのですが、(1)と(2)は、似ているようで、メモリの使い方が違っています。だから、(1)のような範囲のシビアさはないはずなのですね。でも、それでは直接の回答にはなっていませんから、ひとまず提案として、このようにしたらどうでしょうか。

A列に対して、右に４つ = Offset(,4) = E列　の場合は、
A列を起点にして、このようにして範囲を取ります。（ただし、こういうマクロは、標準モジュールのほうがエラーが少ないです）

Dim rng As Range
Set rng = Range("A2", Range("A65536").End(xlUp))
rng.Offset(, 4).Formula = _
"=SUMIF(" & rng.Address & ",A2," & rng.Offset(, 1).Address & ")/COUNTIF(" & rng.Address & ",A2)"
'　rng.Offset(, 4).Value =rng.Offset(, 4).Value　'数式から値に変更する
End Sub

だいたい、状況はつかめましたので、もし、うまくなかったら、全面的に内容を変えます。この質問は、Excel独特というか、VBAのみできる人では、うまくは、できない問題だと思います。

お礼

こんばんは、ありがとうございます。
いつも大変ありがたく思っています。
最終的にはこの様な形にしました
dimの宣言を冒頭にしていませんが別段問題なく動作していますので・・・・
'R列に挿入後時間計算を実行
Columns("R:R").Insert Shift:=xlToRight
With Range("Q2", Range("Q65536").End(xlUp)).Offset(, 1)
.NumberFormatLocal = "[$-F400]h:mm:ss AM/PM"
.Formula = "=IF(RC[-4]=1,RIGHT(RC[-1],8)*1,IF(RC[-4]>R[-1]C[-4],RIGHT(RC[-1],8)-RIGHT(R[-1]C[-1],8),R[-1]C))"
.Value = .Value
End With

's行に平均値を求める

Dim rng As Range
Columns("s:s").Insert Shift:=xlToRight
Set rng = Range("e2", Range("e65536").End(xlUp))
rng.Offset(, 14).Formula = _
"=SUMIF(" & rng.Address & ",e2," & rng.Offset(, 13).Address & ")/COUNTIF(" & rng.Address & ",e2)"
rng.Offset(, 14).Value = rng.Offset(, 14).Value '数式から値に変更する

あっという間に答えが出ているのでびっくりしました。
今回のように処理する範囲選択を自動で出来ると大変ありがたく思います
この後の処理としては集計を用いてそれぞれをマトメ選択元1つ1つの時間を抽出する事になりますが　現状でもかなり時間短縮できました。
関数計算でもそこそこの時間を必要としていましたが、本当に目が点でした。

ありがとうございます

回答No.7

Wendy02

こんばんは。

>000000:15:00と出力されているため時刻に戻すため右から8桁としています

割り切れる数だと良いのですが、Excelには、無限小数は存在しないけれども、小数点第16位から、数えることになりかねませんので、INTで切ったほうがよいと思いました。

>Ｓ列に行挿入をして「配列関数？」を使いE列を基準としてR列のデータをまとめたいと思い同じ様な処理で可能かと考えたからです

配列数式は、マクロに置き換えると、目からテンになるほど軽くなりますが、以下の数式は、

>=IF(COUNTIF($A$2:$A$33930,A2)=1,b2,AVERAGE(IF($A$2:$A$33930=a2,$B$2:$B$33930)))｝

= SUMIF($A$2:$A$33930,A2,$B$2:$B$33930)/COUNTIF($A$2:$A$33930,A2)

ということだと思います。

お礼

ありがとうございます。
数式確かに同じ結果になりました。
若干早いのかも知れません。
前回教えていただいた内容はとても早くてびっくりです。
今回教えていただいた数式を同じように組み込もうと思ったのですが
範囲選択の仕方がわかりません。

回答No.6

Wendy02

こんばんは。

相対参照式の入力には、ループ系は使いません。

しかし、元の数式自体が良く分かりません。時間を計算するはずなのに、RIGHT(RC[-1],8) という文字列を取得するというのは、疑問です。文字列になっているなら分かりますが、文字列のままというのもExcelでは扱いにくいです。

例：
2008/12/17 0:12:00

これ（シリアル値）から時間を取るなら、=Q2-INT(Q2)　となります。

それと、"[$-F400]h:mm:ss AM/PM"　は、このようなロケール指定子はあっているのでしょうか。"[$-411]h:mm:ss AM/PM" なら、日本を指します。

Columns("R:R").Insert Shift:=xlToRight
With Range("Q2", Range("Q65536").End(xlUp)).Offset(, 1)
　 .NumberFormatLocal = "[$-F400]h:mm:ss AM/PM"
　 .Formula = "=IF(RC[-4]=1,RIGHT(RC[-1],8)*1,IF(RC[-4]>R[-1]C[-4],RIGHT(RC[-1],8)-RIGHT(R[-1]C[-1],8),R[-1]C))"
　 .Value = .Value
End With

なお、VBAのコーディングでは、FormulaR1C1　の数式を限定する必要はありません。

お礼

ありがとうございます。
いつもお世話になっています。
>RIGHT(RC[-1],8) という文字列を取得するというのは
取得するのは確かに時刻なのですが出力された時点で
000000:15:00と出力されているため時刻に戻すため右から8桁としています
>"[$-F400]h:mm:ss AM/PM"
ですが、マクロ記録で行ったため　わかっていません（汗
loop系として問い合わせをしたのは、同じようにこの次の作業として
Ｓ列に行挿入をして「配列関数？」を使いE列を基準としてR列のデータを
まとめたいと思い同じ様な処理で可能かと考えたからです
現在はE列とR列を新規シートに貼り付け
=IF(COUNTIF($A$2:$A$33930,A2)=1,b2,AVERAGE(IF($A$2:$A$33930=a2,$B$2:$B$33930)))｝で求めていますが
これも、必要部分まで届いているのかどうかを確認して動かしていますので
範囲選択方法を理解するためにもと思いloop処理での問合せをしました
配列関数を使うとやたらと時間がかかるのでちょっと困ってもいるのですが・・・・

回答No.5

hige_082

＞Do～LoopかFor　Nextで・・・・
使わなくてもいけますよ

Sub test()
Dim i As Long
i = Range("q65536").End(xlUp).Row
Columns("R:R").Select
Selection.Insert Shift:=xlToRight
Range("R2").Select
ActiveCell.FormulaR1C1 = "=IF((RC[-4]=1),((RIGHT(RC[-1],8)*1)),(IF((RC[-4]>R[-1]C[-4]),(RIGHT(RC[-1],8))-(RIGHT(R[-1]C[-1],8)),(R[-1]C))))"
Selection.NumberFormatLocal = "[$-F400]h:mm:ss AM/PM"
Selection.AutoFill Destination:=Range("R2:R" & i)
Range("R2:R" & i).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

お礼

ありがとうございます。
欲しい結果は出ています

回答No.4

ja7awu

こんな感じで如何でしょうか。

Do～LoopかFor　Nextではありませんが・・・

Sub test()
Columns("R:R").Insert
Range("R2").FormulaR1C1 = _
　　"=IF(RC[-4]=1,RIGHT(RC[-1],8)*1,IF(RC[-4]>R[-1]C[-4],RIGHT(RC[-1],8)-RIGHT(R[-1]C[-1],8),R[-1]C))"
Range("R2").NumberFormatLocal = "[$-F400]h:mm:ss AM/PM"
With Intersect(Range("R2:R65536"), ActiveSheet.UsedRange)
　　Range("R2").AutoFill Destination:=Range(.Address)
　　.Copy
　　.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone
　　Application.CutCopyMode = False
End With
End Sub

あと、最初セルR2に設定する計算式 括弧の数が必要以上に多いように思いましたので削りました。

お礼

ありがとうございます。
欲しい結果になっています

回答No.3

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

No.1,No.2です。
そのままでは使用できないので、思い直しましたm(_ _)m
No.1、No.2は(参考にはなると思いますが)無視してください

1. S65536セルをクリックして
2. [Ctrl]+[↑] で最終行が選択できます。
3. VBEにおいて Selection.Row で最終の行番号が取得できます

なんか、回答がボロボロですみませんm(_ _)m
記録マクロのあとで[Alt]+[F11]VBEで検証して下さい
もう少し高度なものは自分で会得するか他回答者にお任せします

お礼

ありがとうございます。
十分参考になっています。

回答No.2

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

No1.です。すみません、一つ抜けてました。マクロの記録の場合です。
1.5 記録終了ツールバーから[相対参照]ボタンをクリック

回答No.1

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

記録マクロの範囲で
1. S1セル(Q1セルなのかな？)選択
2. [Ctrl]+[Shift]+[↓]
3. [Shift]+[←]
4. [Shift]+[Tab]
5. [Shift]+[←]

高度なものは他の回答者にお任せしますm(_ _)m