Excel VBAの問題19に関する質問

このQ&Aのポイント
  • Excel VBAの問題19の回答の中で、「i=4」の意味と目的を教えてください。
  • 以前にも同じ問題19の回答について質問しましたが、理解できていません。回答全体の詳細を教えてください。
  • VBA初心者で理解力が乏しいため、問題19の回答について詳しく教えていただけると助かります。
回答を見る
  • ベストアンサー

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を始めたばかりの初心者でいて、 理解力が乏しいため お詳しい方がいらっしゃいましたら、是非、お教え下さい。 よろしくお願い致します。

  • jl777
  • お礼率36% (21/58)

質問者が選んだベストアンサー

  • ベストアンサー
  • chie65535
  • ベストアンサー率43% (8482/19300)
回答No.2

>なぜ、週を表している「i」が「4」(if=だったら)なのでしょうか? このif文が成り立った時のみ「縦合計の客単価」を計算しています。 「縦合計の客単価」は「合計の売上÷合計の客数」で求めます。 さて、ここで「合計の売上」と「合計の客数」が「最終的な数値に確定する」のは、どの時点でしょうか? それは「1~4週目の計算を順次に行なって、4週目の計算が終わった時」です。 では「4週目の計算が終わった時」とは「どの変数が、どういう値になった時」でしょうか? それは「iが4になった時」です。 つまり If i = 4 Then が成り立つ時です。 >「合計」欄ではなく、 >「第4週目」欄を表すのではないでしょうか? Cells(20, j) = Cells(18, j) / Cells(19, j) は「18行目の値を、19行目の値で割って、20行目に入れる」です。 表の構造をよく見て下さい。 1行目:見出し 2~5行目:1週目の日付、売上、客数、客単価 6~9行目:2週目の日付、売上、客数、客単価 10~13行目:3週目の日付、売上、客数、客単価 14~17行目:4週目の日付、売上、客数、客単価 18行目:合計の売上 19行目:合計の客数 20行目:合計の客単価 18行目には何が入っていますか? 19行目には何が入っていますか? 20行目には何が入りますか? それを踏まえて、4週目の計算が終わった時点で Cells(20, j) = Cells(18, j) / Cells(19, j) は、何をしていますか? 18~20行目は「合計に関する行」です。 「合計に関する行」について処理していて「4週目の行」には、何もしていません。 因みに、1~4週目の客単価を計算しているのは Cells(ix + 3, j) = Cells(ix + 1, j) / Cells(ix + 2, j) の行です。 ixは「1週目が2、2週目が6、3週目が10、4週目が14」と変化していき「各週の日付の行」の行番号を示す値が入っています。 表の各週は「日付」「売上」「客数」「客単価」の順に行が構成されているので「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) を見てみましょう。 実行されるタイミングは「4週目の計算が終わった時」ですが、計算しているのは、固定の「18~20行」であって「4週目の行」ではありません。 4週目の行なら Cells(ix + 3, j) = Cells(ix + 1, j) / Cells(ix + 2, j) になる筈です。ですが、プログラムは Cells(20, j) = Cells(18, j) / Cells(19, j) になっています。 つまり「4週目の下にある、合計の欄を計算している」のです。 タイミングは「4週目だけ」ですが「計算対象は合計の行」なのです。 因みに「forループしている最中、4週目だったら、合計を計算する」としないで「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) のようにする訳です。 で、この処理を良く見ると「3~9列目をループで処理してから、単独で10列目を処理」しています。だったら「3~10列目まで一気に処理してしまう」で構いません。 その結果、改良したプログラムは、最後が   '総合計の客単価   For j = 3 To 10     Cells(20, j) = Cells(18, j) / Cells(19, j)   Next になっているのです。jのForループが「3~9」ではなくて「3~10」になっている事に注目して下さい。

jl777
質問者

お礼

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

その他の回答 (2)

  • eden3616
  • ベストアンサー率65% (267/405)
回答No.3

No1に補足です。 >回答全体をひとつずつ「なにがしたいのか」、 >「どうしてこんなことをしているのか」、について エクセルのVBAにはブレークポイント(プログラムの中で一時停止する箇所)を指定したり、プログラムの先頭またはブレークポイントで一時停止した箇所から1行ずつ処理を実行する「ステップ実行」というデバッグ機能があります。 F8でステップ実行しながら各行がどのようにセルに反映されていくのか 1行ずつ確認しながら実行していくことでプログラムの流れがつかめると思いますので、まずは挑戦してみてください。 http://plus1excel.web.fc2.com/learning/l301/t405.html コード内のコメントを頼りにやはりご自身で取得、 どうしても理解できない場合に的を絞り質問されたほうが良いかと思います。

  • eden3616
  • ベストアンサー率65% (267/405)
回答No.1

1~4週の合計が確定するのが4週目だからです。 各週で合計をセルに書き込むと処理速度の低下になるためです。 For i = 1 To 4~Nextにより、18~20行の3行の情報は、 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の時点での 合計が計算されているからです。

関連するQ&A

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

    excelを使用したVBAの問題に関する質問です。 下記のサイトの問題13の解答の中で、 http://excel-ubara.com/excel-answer/EXCELVBA613A.html 1, If InStr(Cells(i, 1), "(") > 0 Then とありますが、 "(") はどんな意味を表していて、なんなのでしょうか? 2、 StrPref = Left(Cells(i, 1), InStr(Cells(i, 1), "(") - 1) とありますが、 "(") - 1)のところでなぜ「-1」をしているのでしょうか? 3, If Left(strPref, Len(strPref) - 1) = strCity Then のところについて、 ここでもなぜ「-1」をしているのでしょうか? VBAを始めたばかりの初心者でいて、 理解力が乏しいため お詳しい方がいらっしゃいましたら、この3点につきまして是非、お教え下さい。 よろしくお願い致します。

  • ExcelのVBAで質問です。

    以下のようなシートがあります。 A列  B列  C列  D列  E列  F列  G列  H列 NO  名前  確認 日付1 日付2  確認 日付1 日付2 6行目からデータを入れる予定です。 C列には○印を入力します。 C列~E列までデータが入った行は、 F列~H列まで同じ処理をします。 (セルの背景色を変えます。) 以後同じことを列方向で繰り返します。 以下のようなVBAを組みましたが、 ○の個数を数える部分でエラーがでます。 Private Sub Worksheet_Change(ByVal Target As Range) Dim staff As String Dim kakunin As String Dim date1 As Date Dim date2 As Date Dim i As Long Dim j As Long Dim cnt As Long '100件分ループ For i = 6 To 105 '○の数をカウント cnt = WorksheetFunction.CountIf(ActiveSheet.Range(Cells(i, 2), Cells(i, 256)), "○") 'jとは確認の列番号のこと j = 3 * cnt staff = Cells(i, 2) kakunin = Cells(i, j) date1 = Cells(i, j + 1) date2 = Cells(i, j + 2) 'スタッフ名が入力されたら If staff = "" Then Range(Cells(i, j), Cells(i, j + 50)).Interior.ColorIndex = 15 Else Cells(i, j).Interior.ColorIndex = xlNone '○が入力されたら If kakunin = "○" Then Range(Cells(i, j + 1), Cells(i, j + 2)).Interior.ColorIndex = xlNone Else Range(Cells(i, j + 1), Cells(i, j + 2)).Interior.ColorIndex = 15 End If If Cells(i, j + 1) <> "" And Cells(i, j + 2) <> "" Then Cells(i, j + 3).Interior.ColorIndex = xlNone End If End If Next i End Sub ご教授いただけたら、幸いです。 よろしくお願いいたします。

  • Excel ワークシート関数をVBAで使用したい

    お世話になります。 Excelでワークシート関数をVBAで使用したいのですが、うまくいきませんでした。 関数ですと「ISERROR(FIND(V$10,R$11))=FALSE」のような式をVBA上で使用したいと思い、以下のようにコードを書いてみましたが If Application.WorksheetFunction.IsError(Application.WorksheetFunction.Find(Cells(i, j), Cells(i, 18))) = False Then Cells(i, j).Select End If 「実行時エラー'1004' WorksheetFunction クラスのFindプロパティを取得できません」となります。 入れ子が問題なのでしょうか。 よろしくお願いします。

  • エクセルvbaで、同じ番号の請求書の金額をまとめる方法 2

    エクセルvbaで、同じ番号の請求書の金額をまとめる方法 2 すみません、前回質問して良い回答をいただいたのですが、こちらの手違いで 用件がひとつぬけていました。 A    B     I    K    L     M 11/5 B575    3000  7500 5000 13500 11/5 B575    4500      8500 11/6 B578    3000   3000 4000 40000 上記のように A日付 B請求書番号 I金額 K金額合計 が入力されています。 (IからKにとんでいるのは間違いではありません) M列にも同じようにL列の同じ請求書番号の金額の合計をセルを結合して中央揃えで表示したいのです。 以前のプログラムに加筆することで可能になるでしょうか。 下に貼り付けます。 Dim i As Long, j As Long Dim buf As Variant, ret As Double For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row buf = Val(Cells(i, 9).Value) '修正 If Cells(i, 2).Value <> Cells(i + 1, 2).Value Then If j = 0 Then j = i With Range(Cells(j, 11), Cells(i, 11)) .MergeCells = True .HorizontalAlignment = xlCenter .VerticalAlignment = xlCenter End With If buf + ret > 0 Then Cells(j, 11).Value = buf + ret End If Cells(j, 11).NumberFormat = "#,##0" ret = 0: j = 0 Else If j = 0 Then j = i ret = buf + ret End If Next ''合計欄 'With Cells(i, 4) ' .NumberFormat = "#,##0" ' .HorizontalAlignment = xlCenter ' .Formula = "= SUM(R1C:R[-1]C)" 'End With ご多忙の中申し訳ございませんがよろしくお願いします。

  • EXCEL VBA2010 MsgBox

    Sub 重複() Dim i As Long, j As Long For i = 6 To 500 For j = 3 To 3 If WorksheetFunction.CountIf(Range("C6:C500"), Cells(i, j)) > 1 Then Cells(i, j).Interior.ColorIndex = 6 End If Next j Next i End Sub このVBAに重複が何件ありますよというメッセージを出したいです。 MsgBoxの入れ方を教えてください。

  • VBAでの質問

    お世話になります。 下記の記述で、「←」の矢印の記述で、 Cells.(5,2)がブランクでなければ、 「→」から進めたいのですが、 どの様に記述すれば宜しいでしょうか ご教示お願いします。 Sub 表記入() Dim Data As Range Dim i As Integer Dim j As Integer Dim k As Integer Set Data = Sheets("集計").Range("A2").CurrentRegion j = 16 k = 0 With Sheets("表") For i = 3 To Data.Rows.Count If Data.Cells(i, 51) <> "" Then .Cells(5, 2) = Data.Cells(i, 3)  ← .Cells(5, 7) = Data.Cells(i, 4) → .Cells(j, 2) = Data.Cells(i, 10) .Cells(j, 6) = Data.Cells(i, 11) & Data.Cells(i, 12) .Cells(j, 14) = Data.Cells(i, 51) Else End If k = k + 1 If k = 10 Then j = j + 18 k = 0 Else j = j + 1 End If End If Next i End With End Sub

  • EXCEL VBAのFor...Nextについて

    VBA初心者です。よく理解していませんので、質問も的を得ていないかもしれませんが、ご指導宜しくお願いいたします。  現在、For...Nextを使った表計算をしています。 A列に「す」という文字が含まれていたら、B列の「す」の行に「あ」と「い」と「え」「か」のセルの合計をだす。C列、D列・・・最終列まで計算する。 上記VBAを作成する方法を教えて下さい。 A  B  C  D  E   F  G  H  I  J  K  L 1 2    3    4   5    6    7    8    9    10    11 12 あ  1 2 3 4   5 6 7 8 9 10 11 い 10 20 30 40  50 60 70 80 90 100 110 う 20 30 40 50   60 70 80 90 100 110 120 え 40 50   60  70 80 90  10 20 120 130 30 お 50 60   70  80 90 10  20 30 130 140 40 か 60 70   80  90 10 20  30 40 140 150 50 す 私は表に1~12まで数字をインプットし下記のようなコードを考えました。 Sub 列合計() Dim i, k, l, m, n As Long j = 2 For i = 6 To 120 For k = 6 To 120 For l = 6 To 120 For m = 6 To 120 For n = 6 To 120 If Cells(i, 1) = "す" And Cells(k, 1) = "あ" And Cells(l, 1) = "い" And Cells(m, 1) = "え" And Cells(n, 1) = "か" Then Do While j <= Range("A2").End(xlToRight) Cells(i, j) = Cells(k, j) + Cells(l, j) + Cells(m, j) + Cells(n, j) j = j + 1 Loop Else: End If Next n Next m Next l Next k Next i End Sub この内容だとエラーが出てしまいます。 補足ですが、あいうえおかの順番はかわったり、間に他の行が入ったりします。 また今回はL列の間としましたが、もっと列が増え、最終列まで計算する方法を知りたいのですが、どうぞ宜しくお願い致します。 ※ofice2013です。

  • ExcelのVBAがオーバーフローに!?

    読んで頂きありがとうございます。 以前に質問しました時に回答を頂き凄く助かっていたのですが、何故か「オーバーフロー」と表示されてしまいます。 ひょっとしたら桁なのかも知れません。 下記のVBAを教えて頂きしようしていました。 Option Explicit Sub Test() Dim c(), p(), q(), i, j, l, r, k As Long r = Range("B2").End(xlDown).Row l = Range("D2").End(xlDown).Row ReDim c(r - 2), p(r - 2), q(r - 2) For i = 1 To r - 2 c(i) = Cells(i + 2, 2).Value p(i) = Cells(i + 2, 3).Value q(i) = i + 2 Next i For i = 1 To r - 3 For j = i + 1 To r - 2 If c(i) > c(j) Then k = c(i) c(i) = c(j) c(j) = k k = p(i) p(i) = p(j) p(j) = k k = q(i) q(i) = q(j) q(j) = k End If Next j Next i For i = 1 To r - 2 Cells(i + 2, 7).Value = c(i) For j = 3 To r If Cells(j, 4).Value = c(i) Then Cells(i + 2, 8).Value = p(i) - Cells(j, 5).Value If p(i) - Cells(j, 5).Value <> 0 Then Range("B" & q(i)).Interior.ColorIndex = 6 Range("D" & j).Interior.ColorIndex = 6 End If Exit For End If Next j Next i For i = 3 To r k = 0 For j = 3 To l If Cells(i, 2).Value = Cells(j, 4).Value Then k = 1 Exit For End If Next j If k = 0 Then Range("B" & i).Interior.ColorIndex = 35 End If Next i For i = 3 To l k = 0 For j = 3 To r If Cells(i, 4).Value = Cells(j, 2).Value Then k = 1 Exit For End If Next j If k = 0 Then Range("D" & i).Interior.ColorIndex = 35 End If Next i End Sub デパックのClickすると15行目辺りの「k = c(i)」が黄色くなります。 正直言ってマクロとか全く解りませんが、コピーして使っていました。 どこをどう変更すれば良いのか教えて頂ければ助かります。 よろしくお願いします。

  • エクセルVBAの繰り返し処理の質問

    C列にある項目とG列にある項目を比較して、 一致し、H列にある数字が10以上ならば、B列にフラグ1を立てる という処理を行いたいんですが、 下記ぐらいまでしか作れず、うまくいきません・・・ Sub フラグを立てる処理() Dim i As Integer Dim j As Integer Dim k As Integer i = 1 j = 1 Do j = j + 1 Do i = i + 1 If Cells(j, 8) > 9 Then Cells(i - 1, 4) = 1 End If Loop Until Cells(i, 3) <> Cells(j, 7) Or Cells(i, 3) = "" Loop Until Cells(j, 7) = "" End Sub わかる方がいらっしゃいましたら、お願いします。

  • エクセルVBAを修正したい

    数字を入力すると記号に変換になるマクロを 元ファイルを修正して作成したいのですが、 以下の記述が理解できません。 具体的にどのような処理をしているのか教えて下さい。 Do While Len(Range("C" & CStr(I)) & Range("D" & CStr(I))) > 0 For J = StartCol To EndCol If Len(ActiveSheet.Cells(12, J).Value & ActiveSheet.Cells(13, J).Value) > 0 Then tmp = "" If ActiveSheet.Cells(I, J).Value = "×" Or ActiveSheet.Cells(I, J).Value = "中止" Then ' ActiveSheet.Cells(I, J).Value = "中止" 'ActiveSheet.Cells(I, J + 1).Value = "" Else If Len(ActiveSheet.Cells(I, J).Value) = 0 Then K = -1 Else K = ActiveSheet.Cells(I, J).Value End If Select Case K Case 0 tmp = "×" Case 1 To 9 tmp = "△" Case Is >= 10 tmp = "○" Case Is < 0 tmp = "**" End Select