excelを使用したVBAの問題に関する質問です。

質問

excelを使用したVBAの問題に関する質問です。 下記のサイトの問題19の解答の中で、 http://excel-ubara.com/excel-answer/EXCELVBA619A.html

'縦合計の客単価
If i = 4 Then
Cells(20, j) = Cells(18, j) / Cells(19, j)
End If

なぜ、週を表している「i」が「4」(if=だったら)なのでしょうか？
「4」だった場合、「合計」欄ではなく、
「第4週目」欄を表すのではないでしょうか？

実は以前にもこの問題19の回答について質問させて頂いております。
しかし、未熟ゆえ、未だに全体的にこの問題の回答が理解できません。

もしよろしければ、
回答全体をひとつずつ「なにがしたいのか」、「どうしてこんなことをしているのか」、について
丁寧にお教え頂けますと、とても助かります。

VBAを始めたばかりの初心者でいて、
理解力が乏しいため

お詳しい方がいらっしゃいましたら、是非、お教え下さい。

よろしくお願い致します。

回答No.2

chie65536（@chie65535） · ベストアンサー

＞なぜ、週を表している「i」が「4」(if=だったら)なのでしょうか？

このif文が成り立った時のみ「縦合計の客単価」を計算しています。

「縦合計の客単価」は「合計の売上÷合計の客数」で求めます。

さて、ここで「合計の売上」と「合計の客数」が「最終的な数値に確定する」のは、どの時点でしょうか？

それは「１～４週目の計算を順次に行なって、４週目の計算が終わった時」です。

では「４週目の計算が終わった時」とは「どの変数が、どういう値になった時」でしょうか？

それは「iが4になった時」です。

つまり

If i = 4 Then

が成り立つ時です。

＞「合計」欄ではなく、
＞「第4週目」欄を表すのではないでしょうか？

Cells(20, j) = Cells(18, j) / Cells(19, j)

は「18行目の値を、19行目の値で割って、20行目に入れる」です。

表の構造をよく見て下さい。

１行目：見出し
２～５行目：１週目の日付、売上、客数、客単価
６～９行目：２週目の日付、売上、客数、客単価
１０～１３行目：３週目の日付、売上、客数、客単価
１４～１７行目：４週目の日付、売上、客数、客単価
１８行目：合計の売上
１９行目：合計の客数
２０行目：合計の客単価

１８行目には何が入っていますか？

１９行目には何が入っていますか？

２０行目には何が入りますか？

それを踏まえて、４週目の計算が終わった時点で

Cells(20, j) = Cells(18, j) / Cells(19, j)

は、何をしていますか？

１８～２０行目は「合計に関する行」です。

「合計に関する行」について処理していて「４週目の行」には、何もしていません。

因みに、１～４週目の客単価を計算しているのは

Cells(ix + 3, j) = Cells(ix + 1, j) / Cells(ix + 2, j)

の行です。

ixは「１週目が２、２週目が６、３週目が１０、４週目が１４」と変化していき「各週の日付の行」の行番号を示す値が入っています。

表の各週は「日付」「売上」「客数」「客単価」の順に行が構成されているので「ixが日付の行」「ix + 1が売上の行」「ix + 2が客数の行」「ix + 3が客単価の行」を意味しています。

なので

Cells(ix + 3, j) = Cells(ix + 1, j) / Cells(ix + 2, j)

は「各週の、売上を客数で割って、客単価に入れている」のです。

話を戻して

Cells(20, j) = Cells(18, j) / Cells(19, j)

を見てみましょう。

実行されるタイミングは「４週目の計算が終わった時」ですが、計算しているのは、固定の「１８～２０行」であって「４週目の行」ではありません。

４週目の行なら

Cells(ix + 3, j) = Cells(ix + 1, j) / Cells(ix + 2, j)

になる筈です。ですが、プログラムは

Cells(20, j) = Cells(18, j) / Cells(19, j)

になっています。

つまり「４週目の下にある、合計の欄を計算している」のです。

タイミングは「４週目だけ」ですが「計算対象は合計の行」なのです。

因みに「forループしている最中、４週目だったら、合計を計算する」としないで「forループが終わって、合計以外がすべて確定したら、合計を計算する」でも、同じ結果になります。

その場合、プログラムは

Sub 練習問題19()
　　Dim i As Long
　　Dim j As Long
　　Dim ix As Long
　　'全体のfont色をリセット
　　Range("A1:J20").Font.ColorIndex = xlAutomatic
　　'合計欄の消去
　　Range("J2:J20").ClearContents
　　Range("C18:I20").ClearContents
　　'4週
　　For i = 1 To 4
　　　　ix = (i - 1) * 4 + 2 '日付の行位置
　　　　'単価の消去と小数設定
　　　　With Range(Cells(ix + 3, 3), Cells(ix + 3, 10))
　　　　　　.NumberFormatLocal = "0.0"
　　　　　　.ClearContents
　　　　End With
　　　　'7列
　　　　For j = 3 To 9
　　　　　　'横合計と客単価
　　　　　　Cells(ix + 1, 10) = Cells(ix + 1, 10) + Cells(ix + 1, j)
　　　　　　Cells(ix + 2, 10) = Cells(ix + 2, 10) + Cells(ix + 2, j)
　　　　　　Cells(ix + 3, j) = Cells(ix + 1, j) / Cells(ix + 2, j)
　　　　　　'縦合計
　　　　　　Cells(18, j) = Cells(18, j) + Cells(ix + 1, j)
　　　　　　Cells(19, j) = Cells(19, j) + Cells(ix + 2, j)
　　　　Next
　　　　'横合計の客単価
　　　　Cells(ix + 3, 10) = Cells(ix + 1, 10) / Cells(ix + 2, 10)
　　　　For j = 3 To 9
　　　　　　If Cells(ix + 3, j) < Cells(ix + 3, 10) Then
　　　　　　　　Cells(ix + 3, j).Font.Color = vbRed
　　　　　　End If
　　　　Next
　　　　'縦横合計
　　　　Cells(18, 10) = Cells(18, 10) + Cells(ix + 1, 10)
　　　　Cells(19, 10) = Cells(19, 10) + Cells(ix + 2, 10)
　　Next
　　'総合計の客単価
　　For j = 3 To 10
　　　　Cells(20, j) = Cells(18, j) / Cells(19, j)
　　Next
End Sub

となります。

このプログラムは、元のプログラムの最後の部分で

　　'縦横合計の客単価
　　Cells(20, 10) = Cells(18, 10) / Cells(19, 10)

とやっていたのを

　　'総合計の客単価
　　For j = 3 To 10
　　　　Cells(20, j) = Cells(18, j) / Cells(19, j)
　　Next

に変えてあります。

また、上の「For i = 1 to 4」の中にある

　　　　　　'縦合計の客単価
　　　　　　If i = 4 Then
　　　　　　　　Cells(20, j) = Cells(18, j) / Cells(19, j)
　　　　　　End If

が無くなっています。

つまり

　　　　　　'縦合計の客単価
　　　　　　If i = 4 Then
　　　　　　　　Cells(20, j) = Cells(18, j) / Cells(19, j)
　　　　　　End If

を「iのForループ」の外に追い出して移動したので、外で「jのForループ」をする必要が出ます。つまり、iのForループが終わった後の

　　'縦横合計の客単価
　　Cells(20, 10) = Cells(18, 10) / Cells(19, 10)

の部分を

　　'縦合計の客単価
　　For j = 3 To 9
　　　　Cells(20, j) = Cells(18, j) / Cells(19, j)
　　Next
　　'縦横合計の客単価
　　Cells(20, 10) = Cells(18, 10) / Cells(19, 10)

のようにする訳です。

で、この処理を良く見ると「３～９列目をループで処理してから、単独で１０列目を処理」しています。だったら「３～１０列目まで一気に処理してしまう」で構いません。

その結果、改良したプログラムは、最後が

　　'総合計の客単価
　　For j = 3 To 10
　　　　Cells(20, j) = Cells(18, j) / Cells(19, j)
　　Next

になっているのです。jのForループが「３～９」ではなくて「３～１０」になっている事に注目して下さい。

お礼

大変に分かりやすいご説明、ありがとうございました。参考にさせて頂きます。m(__)m

回答No.3

eden3616

No1に補足です。
＞回答全体をひとつずつ「なにがしたいのか」、
＞「どうしてこんなことをしているのか」、について

エクセルのVBAにはブレークポイント（プログラムの中で一時停止する箇所）を指定したり、プログラムの先頭またはブレークポイントで一時停止した箇所から1行ずつ処理を実行する「ステップ実行」というデバッグ機能があります。

F8でステップ実行しながら各行がどのようにセルに反映されていくのか
１行ずつ確認しながら実行していくことでプログラムの流れがつかめると思いますので、まずは挑戦してみてください。
http://plus1excel.web.fc2.com/learning/l301/t405.html

コード内のコメントを頼りにやはりご自身で取得、
どうしても理解できない場合に的を絞り質問されたほうが良いかと思います。

回答No.1

eden3616

1～4週の合計が確定するのが4週目だからです。
各週で合計をセルに書き込むと処理速度の低下になるためです。

For i = 1 To 4～Nextにより、18～20行の３行の情報は、
1週目から順次算出していき4週まで繰り返します。
その際の1週目、1～2週目、1～3週目、1～4週目の合計のうち、
4週目の合計がすべての合計となるためi=4（4週目）のときにだけ
合計欄の処理をしています。

たとえば、以下のように変更して各週において処理を行っても結果は同じですよ。最後に合計された4週目の値が上書きされるのでi=1～3の処理は無駄なだけですが。

If i = 4 Then
　　Cells(20, j) = Cells(18, j) / Cells(19, j)
End If
　　　↓
Cells(20, j) = Cells(18, j) / Cells(19, j)

確認のため、ifの条件式をi=1を設定したあと実行してみてください。
月曜日の合計客単価は「916.4567901」と表示されます。
実際の合計の客単価は299959/323=928.6656347なので
異なっていますよね。
これは、第1週目の月曜日74,233/81=916.4567901の時点での
合計が計算されているからです。