Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7 Home Premium : Excel 2010)
行数や列数の増減に対応できるようにしたい
投稿日時: 18/05/13 03:47:53
投稿者: santasan1224

こんばんは。
長文になりますが、最後までよんでいただけると幸いです。
 
ある問題の条件で、
(1)下図のように、B2〜K11までの範囲までの10行×10列の表を描画するVBAのコードを標準モジュール「Module1 」に書いて下さい(外枠は太い線、中は普通の細い線)。
 
(2)(1)の表を使って、以下の条件でテストの点数一覧を作ってください。この処理は標準モジュール「 Module2」に書いてください。
・B3からB11に9人分の名前を配列で入力(名前は AAAAA でもなんでも可)
・C2からK2セルに教科を配列で入力する(左から順に「国語/英語/数学/理科/社会/美術/平均/合計/評価とする)
・名前の列を色番号4番、教科の行を番号6番でセルを塗りつぶす。
・一つのプロシージャ内で完結させる
 
(3)(2)の表を元に以下の条件で点数と評価を入力するコードを標準モジュール「Module3」に書いて下さい。点数は各教科で100点満点です。
・乱数を使って6教科の点数を入力する(乱数は「変数=Int(Rnd * 100)」を使う)。
・9人それぞれの6教科の合計と平均を計算し「合計」列、「平均」列にそれぞれ入力する。
・9人それぞれの平均点を元に評価を「評価」列に以下のルールに従って入力する。
*70点以上→ 合格
*50点以上 69点以下→ 再試験
*30点以上 49点以下→ 留年
*29点以下→ 退学
・一つのプロシージャ内で完結させる
 
となっていまして。
 
以下がその私が作成したものです。
ここから
↓↓↓↓↓↓
 
モジュール1
Sub test1()
 
    With Worksheets("sheet1").Range("B2:K11")
     
        .Borders(xlEdgeTop).Weight = xlThick
        .Borders(xlEdgeLeft).Weight = xlThick
        .Borders(xlEdgeBottom).Weight = xlThick
        .Borders(xlEdgeRight).Weight = xlThick
        .Borders(xlInsideVertical).LineStyle = xlContinuous
        .Borders(xlInsideHorizontal).LineStyle = xlContinuous
     
    End With
        
End Sub
 
モジュール2
Sub test2()
 
    Dim hairetsu(8) As String
     
        hairetsu(0) = "A"
        hairetsu(1) = "B"
        hairetsu(2) = "C"
        hairetsu(3) = "D"
        hairetsu(4) = "E"
        hairetsu(5) = "F"
        hairetsu(6) = "G"
        hairetsu(7) = "H"
        hairetsu(8) = "I"
         
         
    Dim i As Integer
     
     
        i = 0
         
        For i = i To 8
         
        Cells(3 + i, 2) = hairetsu(0 + i)
         
        Cells(3 + i, 2).Interior.ColorIndex = 4
         
        Next i
         
         
    Dim kyouka(8) As String
     
        kyouka(0) = "国語"
        kyouka(1) = "英語"
        kyouka(2) = "数学"
        kyouka(3) = "理科"
        kyouka(4) = "社会"
        kyouka(5) = "美術"
        kyouka(6) = "合計"
        kyouka(7) = "平均"
        kyouka(8) = "評価"
         
    Dim t As Integer
     
        t = 0
         
        For t = t To 8
         
        Cells(2, 3 + t) = kyouka(0 + t)
         
        Cells(2, 3 + t).Interior.ColorIndex = 6
         
        Next t
        
End Sub
 
モジュール3
Sub test3()
 
    Dim g As Integer, r As Integer
     
        For g = 3 To 11
     
        For r = 3 To 8
     
            Cells(g, r).Value = Int(Rnd * 100)
         
        Next r
         
        Next g 'C3〜H11までを乱数発生'
         
         
         
       '合計と平均を入力する'
        
       Dim c As Integer
        
       Dim h As Integer
             
            For c = 0 To 8
        
            h = 6
        
            Cells(3 + c, 9) = Cells(3 + c, 3) + Cells(3 + c, 4) + Cells(3 + c, 5) + Cells(3 + c, 6) + Cells(3 + c, 7) + Cells(3 + c, 8)
        
            Cells(3 + c, 10) = Cells(3 + c, 9) \ h
        
        
       Next c
        
       'ここまでが合計と平均'
        
        
                 
       'ここから評価を繰り返し処理でif文を作る'
                 
         Dim x As Integer
         
        For x = 3 To 11
             
                 
            If 70 <= Cells(x, 10) And Cells(x, 10) <= 100 Then
                Cells(x, 11).Value = "合格"
                 
            ElseIf 50 <= Cells(x, 10) And Cells(x, 10) <= 69 Then
                Cells(x, 11).Value = "再試験"
                 
            ElseIf 30 <= Cells(x, 10) And Cells(x, 10) <= 49 Then
                Cells(x, 11).Value = "留年"
                 
            ElseIf 0 <= Cells(x, 10) And Cells(x, 10) <= 29 Then
                Cells(x, 11).Value = "退学"
                 
            Else
             
             
            End If
             
        Next x
                 
             
             
End Sub
 
↑↑↑↑↑↑↑↑
ここまで
 
 
 
 
この結果で目的の結果は得られるのですが、
例えば、モジュール2にて、人数が100人となると、コードを100行書かないといけないと思います。
それと同様にモジュール3では、合計や平均を算出するくだりで、人数や科目数の増減になるべく
簡単に対応できるような方法はありますでしょうか?
 
非常に読み辛くて大変申し訳ございませんが、どうかご教授いただけますでしょうか?
よろしくお願い致します。

回答
投稿日時: 18/05/13 09:14:14
投稿者: WinArrow
投稿者のウェブサイトに移動

考え方
 
「先に表(罫線をひくという意味)を作成してからデータを格納する」
って方法よりも
 
データを格納してから、データの件数に対応した表を
作成するというような考え方にする
方法を提案します。

回答
投稿日時: 18/05/13 09:49:24
投稿者: simple

こんにちは。
 
もともと、テーマそのものは、VBAでやるより、ワークシート上で普通に式を使えばよい
テーマと思います。
それを学習用に仕立てているところに、もともと無理があるわけですが、
学習用の問題のようなので、まずはその目的だけを考えてコーディングを
すればよろしいかと思います。
 
量の問題に懸念を感じているようですが、
・氏名や科目については、実際の運用では別の方法を採るはずです。
  シートに入力されたものを変数に取り込むということになるでしょう。
・合計や平均などは、ワークシート関数の活用を考えるのが実際的でしょう。
 
学習用と割り切った上でコメントすると、
(1)

 Cells(3 + c, 9) = Cells(3 + c, 3) + Cells(3 + c, 4) + Cells(3 + c, 5) + Cells(3 + c, 6) + Cells(3 + c, 7) + Cells(3 + c, 8) 
Cells(3 + c, 10) = Cells(3 + c, 9) \ h 
のところは、
・繰り返しを使って加算するとか(二重ループの使用)、
・WorksheetFunction.Sum,WorksheetFunction.Averageの活用
などのパターンも書いてみるとよいでしょう。
 
(2)
  t = 0 
  For t = t To 8 
こういう書き方は推奨されません。
 
(3)以下のところはSelect Caseステートメントの活用も考えてみてはどうでしょうか。
    For x = 3 To 11
        If 70 <= Cells(x, 10) And Cells(x, 10) <= 100 Then
            Cells(x, 11).Value = "合格"
        ElseIf 50 <= Cells(x, 10) And Cells(x, 10) <= 69 Then
            Cells(x, 11).Value = "再試験"
        ElseIf 30 <= Cells(x, 10) And Cells(x, 10) <= 49 Then
            Cells(x, 11).Value = "留年"
        ElseIf 0 <= Cells(x, 10) And Cells(x, 10) <= 29 Then
            Cells(x, 11).Value = "退学"
        Else
        End If
    Next x

(4)
セルのValueプロパティを省略していますが、それについての指導方針はどんなものでしたか?
普通は(質問掲示板等では)、省略しないほうがよいということが言われますが。
 
(5)
変数の型宣言の位置は今どきなのかも知れませんね。(プロシージャの頭にまとめず、
使う直前に宣言するという方式のことです。私は頭にまとめるスタイルが好きですが)
ただし、インデントのつけかたには違和感があります。
変数名が同じカラム位置になることを目指している印象ですが、
余り標準的な感じはしません。
頭を揃えたほうがよいのではないですか?

回答
投稿日時: 18/05/13 10:30:52
投稿者: sy

まず質問の回答の前に気になる点を、
モジュール1のコードはシート名まで指定しているのに、モジュール2とモジュール3のコードは記述してないので、コードが標準モジュールに記載されてるので、ActiveSheetが対象になってますよね。
全部のコードにシート装飾を施しましょう。
 
 
モジュール3の以下の部分ですけど、「変数=Int(Rnd * 100)」と変数に代入するように指定されてるのに、コードは直接セルに代入になってるけど大丈夫かな?

santasan1224 さんの引用:
・乱数を使って6教科の点数を入力する(乱数は「変数=Int(Rnd * 100)」を使う)。
        For g = 3 To 11
        For r = 3 To 8
            Cells(g, r).Value = Int(Rnd * 100)
        Next r
        Next g 'C3〜H11までを乱数発生'
変数に一旦代入してからセルに一括転記するのなら以下のような記述になります。
    Dim 変数(8, 5) As Long
    Dim i As Long
    Dim k As Long

    For i = 0 To 8
        For k = 0 To 5
            変数(i, k) = Int(Rnd * 100)
        Next k
    Next i
    Sheets("Sheet1").Range("C3:H11").Value = 変数

でもこれ問題が間違ってますね。
santasan1224 さんの引用:
点数は各教科で100点満点です。
と言うのならrndは0〜1未満なので、
rnd*100じゃ0〜99までしか乱数を生成できないから100点の人がいなくなります。
0〜100にする場合は本当なら int(rnd * 101) にしないと駄目ですね。
 
 
モジュール2の人数が多い時の名前欄の短い記述と言う事ですけど、
実際の業務で(2)番の問題のような作り方する事はまず有り得ないので、気にしなくて良いですよ。
本来名前欄などは名簿データがあれば其方から直接転記するものですし、
名簿データが無い場合は、直接セルに手入力する方がコード内に記述するよりも遥かに早くて効率的です。
名前欄の部分に関しては、勉強の為に敢えて実務としては無駄な事もしてるくらいに思ってて良いです。
 
 
短い記述と言うなら以下のようにも書けますけど、Array関数で配列で記載したデータをセルに一括代入と言うのが問題文の「配列」に該当するかと言えば、先生次第と思います。
配列としか指定されてないから私なら正解ですけど、配列で定義した変数を使わないと間違いとか言いそうな頭の固い先生もいてるでしょうし。
他にもArrayを教えていないから間違いとか、「教えていない事は合ってても間違い」と言う訳の分からない持論の先生も最近多いですよね。
    With Sheets("Sheet1").Range("B3:B11")
        .Value = WorksheetFunction.Transpose(Array("A", "B", "C", "D", "E", "F", "G", "H", "I"))
        .Interior.ColorIndex = 4
    End With

    With Sheets("Sheet1").Range("C2:K2")
        .Value = Array("国語", "英語", "数学", "理科", "社会", "美術", "平均", "合計", "評価")
        .Interior.ColorIndex = 6
    End With

 
ちょっと長くなったので一旦切ります。

投稿日時: 18/05/13 10:46:21
投稿者: santasan1224

WinArrow さんの引用:
考え方
 
「先に表(罫線をひくという意味)を作成してからデータを格納する」
って方法よりも
 
データを格納してから、データの件数に対応した表を
作成するというような考え方にする
方法を提案します。

 
ご回答ありがとうございます。
 
学習をし始めてまだ間もないのでわからないことだらけなのですが、
「データを格納してから、データの件数に対応した表を作成する」という
のは、先にデータを変数に入れてから、それに合わせて表を作る
という認識なのでしょうか?

回答
投稿日時: 18/05/13 10:54:36
投稿者: simple

評価判定のところですが、例えば、
ElseIf 50 <= Cells(x, 10) And Cells(x, 10) <= 69 Then
は冗長です。
Elseということは、69以下と決まっていますから、
Cells(x, 10) <= 69 は不要です。
間違いではないのですが。
 
で、Select Caseを使うというのは、

   For x = 3 To 11
        Select Case Cells(x, 10).Value
        Case Is >= 70
            s = "合格"
        Case Is >= 50
            s = "再試験"
        Case Is >= 30
            s = "留年"
        Case Else
            s = "退学"
        End Select
        Cells(x, 11).Value = s
    Next x
というようなことです。

回答
投稿日時: 18/05/13 11:15:25
投稿者: WinArrow
投稿者のウェブサイトに移動

santasan1224 さんの引用:

 
学習をし始めてまだ間もないのでわからないことだらけなのですが、
「データを格納してから、データの件数に対応した表を作成する」という
のは、先にデータを変数に入れてから、それに合わせて表を作る
という認識なのでしょうか?

 
まず、これは、VBA勉強の初歩としての課題ですか?
 
若し、課題でしたら、その範囲内でコードを作成すればよいです。
 
100人になったら・・・とか、
今後のメンテナンスを考えるとすれば、
セルにデータを入力すること(例、氏名など)を
変数に入れて、それをセルに格納するなんてことは時間の無駄です。
なぜ?って思われると思いますが、人が増えるたびにコードの修正が必要になるからです。
直接セルに入力したほうが、簡単・早い。修正も可単。
罫線も手操作で引いた方が早い。
 
 
 
 
 
 
 
 

投稿日時: 18/05/13 11:23:32
投稿者: santasan1224

ご回答ありがとうございます。
 
simpleさんのテーマそのものにつきましては仰る通りで、
学習用の問題であり、色々と無理があるところも多々あります。
 

引用:

(1)
 Cells(3 + c, 9) = Cells(3 + c, 3) + Cells(3 + c, 4) + Cells(3 + c, 5) + Cells(3 + c, 6) + Cells(3 + c, 7) + Cells(3 + c, 8)
Cells(3 + c, 10) = Cells(3 + c, 9) \ h
のところは、
・繰り返しを使って加算するとか(二重ループの使用)、
・WorksheetFunction.Sum,WorksheetFunction.Averageの活用
などのパターンも書いてみるとよいでしょう。

・二重ループの使用というのは、For〜Next文を入れ子にして使うということでしょうか?
・お伝えし切れなくて大変申し訳ございませんが、学習の指導上SumやAverageなどの関数は使わず
演算子を使い四則演算のパターンでやらなければいけないということでこのパターンを書いております。
(私もsimpleさんと同じパターンを考えていたのですが上記のパターンで書かせていただいております)
 
 
 
引用:

(2)
  t = 0
  For t = t To 8
こういう書き方は推奨されません。

このような書き方が推奨されないのはどうしてですか?
VBAを始めて間もないので理由を教えていただけますでしょうか?
 
 
(3)につきましては、
If文を使う指示があったのでその部分のみであれば
間違っていないというご指導をいただきました。
 
 
(4)
Valueプロパティの省略についてですが、指導方針としては
特に何も指摘はありませんでした。
省略しない方が良いというのは初めてです。
 
 
(5)
インデントの付け方は特に意識をしていなかったので、
頭を揃えてみます。
ご指摘ありがとうございます。

回答
投稿日時: 18/05/13 12:01:36
投稿者: sy

simple さん
 
valueプロパティに関しては、私個人の感想ですけど、訳も分からず何でもかんでも付けるのは、訳も分からず全く付けないのと同じくらい駄目と思います。
付ける付けないに関しては、どちらでも偏りすぎたら不具合が出たケースがあったと思うので(良い例が思い浮かばないのですいません)、
要は付けなければいけない場合と付けてはいけない場合を理解する事が重要と思います。
(どちらでも問題ない時は可読性が極端に悪くなるなどでしたら、良い方を選ぶべきとは思います)
今回のcellsの場合などはvalueをつけると、ステップ実行でカーソルを合わせた時に中身が見れなくなるので、
いちいちイミディエイトに書き出すのは無駄なので、無い方が良いと思います。
 
 
本題に戻りますけど、

santasan1224 さんの引用:
Cells(3 + c, 10) = Cells(3 + c, 9) \ h
これ割り算に\を使うように教わりましたか?
\を使うとint(a / b) と同じ結果になります。
intは切り捨てなので、評価判定で本来よりも低く評価してしまうケースが出来ますよ。
またclnt()などとして値の型変換を利用したり、Integer型やlong型の変数に代入して、四捨五入をEXCELに任せると、偶数丸めになるので、今回のようなケースでは此方も使用してはいけません。
平均値を四捨五入して整数に丸めるなら、銀行丸め以外では必ずワークシート関数のROUND関数を使用しないといけません。
        Cells(3 + c, 10) = WorksheetFunction.Round(Cells(3 + c, 9) / h, 0)
と\は使用せずに、/で割って、WorksheetFunction.Roundでワークシート関数を呼び出しましょう。

投稿日時: 18/05/13 12:04:35
投稿者: santasan1224

ご回答ありがとうございます。
 

引用:
モジュール1のコードはシート名まで指定しているのに、モジュール2とモジュール3のコードは記述してないので、コードが標準モジュールに記載されてるので、ActiveSheetが対象になってますよね。
全部のコードにシート装飾を施しましょう。

こちらにつきましては、修正いたしました。ご指摘ありがとうございます。
 
 
引用:
モジュール3の以下の部分ですけど、「変数=Int(Rnd * 100)」と変数に代入するように指定されてるのに、コードは直接セルに代入になってるけど大丈夫かな?

「変数=Int(Rnd * 100)」の使用は特に指導者の指示がなかったので、
直接セルに代入しています。
 
 
引用:
でもこれ問題が間違ってますね。
santasan1224 さんの引用:
点数は各教科で100点満点です。
と言うのならrndは0〜1未満なので、
rnd*100じゃ0〜99までしか乱数を生成できないから100点の人がいなくなります。
0〜100にする場合は本当なら int(rnd * 101) にしないと駄目ですね。

こちらに関しては、よく見れば仰る通りでした。
指導者に修正お願いしておきます。ご指摘ありがとうございます。
 
 
 
 
引用:
モジュール2の人数が多い時の名前欄の短い記述と言う事ですけど、
実際の業務で(2)番の問題のような作り方する事はまず有り得ないので、気にしなくて良いですよ。
本来名前欄などは名簿データがあれば其方から直接転記するものですし、
名簿データが無い場合は、直接セルに手入力する方がコード内に記述するよりも遥かに早くて効率的です。
名前欄の部分に関しては、勉強の為に敢えて実務としては無駄な事もしてるくらいに思ってて良いです。

直接セルに手入力した方が早いのは私自身も理解しておりますが、
syさん仰る通り課題としてあげられているものなので
勉強の一つとして捉えてやっています。
 
 
引用:
短い記述と言うなら以下のようにも書けますけど、Array関数で配列で記載したデータをセルに一括代入と言うのが問題文の「配列」に該当するかと言えば、先生次第と思います。
配列としか指定されてないから私なら正解ですけど、配列で定義した変数を使わないと間違いとか言いそうな頭の固い先生もいてるでしょうし。
他にもArrayを教えていないから間違いとか、「教えていない事は合ってても間違い」と言う訳の分からない持論の先生も最近多いですよね。

確かに配列としか指定がなかったので、Array関数で配列記載する方法も考えられますが、
先生から頂いているテキスト(先生作成)にはArray関数を教えていない前提で課題を与えられているので
一度、Array関数で提出してみます。
ご指摘ありがとうございます。

投稿日時: 18/05/13 12:13:28
投稿者: santasan1224

今回の課題はIf文で指定なのですが、
Select Caseのパターンでも提出してみようと思います。
ご指摘ありがとうございました。