Excel (VBA)

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

 
(Windows 7 Home Premium : Excel 2010)
(修正)行数や列数の増減に対応できるようにしたい
投稿日時: 18/05/13 12:20:14
投稿者: 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 13:02:05
投稿者: sy

私の 投稿日時: 18/05/13 12:01:36 は大丈夫ですか?
それとも今回の問題では平均値は切り捨てで計算するように指定されてるのなら良いですけど?
  

引用:
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でワークシート関数を呼び出しましょう。

 
 
後簡単なコードとしては、集計部分は特に配列を使用すると言う条件提示も無いので、こう言う方法もあると言うのを提示しておきます。
これなら件数が増えた時にもセル範囲を変えるだけなので修正が簡単です。
    Sheets("Sheet1").Range("I3:I11").Formula = "=ROUND(AVERAGE(C3:H3),0)"
    Sheets("Sheet1").Range("J3:J11").Formula = "=SUM(C3:H3)"
    Sheets("Sheet1").Range("K3:K11").Formula = "=LOOKUP(I3,{0,30,50,70},{""退学"",""留年"",""再試験"",""合格""})"

 
 

投稿日時: 18/05/13 13:30:47
投稿者: santasan1224

引用:
変数に一旦代入してからセルに一括転記するのなら以下のような記述になります。
    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 = 変数

に関してですが、Range("C3:H11")に同じ変数(i, k)が入ってしまうので、
各バラバラに数字を入れてあげるにはどのようにしたらよいのでしょうか?
 
 
 
引用:
私の 投稿日時: 18/05/13 12:01:36 は大丈夫ですか?
それとも今回の問題では平均値は切り捨てで計算するように指定されてるのなら良いですけど?
   
引用:
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でワークシート関数を呼び出しましょう。

こちらですが、割り算に\を使う指示はありませんでした。
/を使うと小数点以下が無限になると思ったので、自分の判断で
\を使っていました。
勉強不足で大変申し訳ございません。
「Cells(3 + c, 10) = WorksheetFunction.Round(Cells(3 + c, 9) / h, 0)」の
最後の0は何を意味しているのですか?

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

可変人数対応お表の考え方
 
(1)人の名前、教科名は予めシートに手入力しておきます。
  従って、test2,text3部分は、不要 
 
(2)罫線は、手入力した人数に対応して、手操作で設定します。
  どうしてもマクロで対応したいのでしたら、
  処理の最終でもよい。
 
(3)可変行(可変セル範囲)取得には、2つの方法があります。
 
  @データの入っているセルを起点に連続する領域を取得する方法
   例
   Range("B3").CurrentRegion
 
 Aデータの入っている最終行を求める方法
   例
       MaxRow = Range("B" & Rows.Count).End(xlup).Row
       Set 範囲 = Range("B3").Resize(MaxRow - 3,8)
 
 ※どちらを使うと問題ないかは、そのシートの状況で判断します。
 
(4)1つのプロシジャに纏める
 
  @の例で
 
  With Sheets("Sheet1")
        Set mYRange = .Range("B3").CurrentRegion
    '合計、平均、評価の処理は
        syさんのレスを使えば、3行で記述できます。
      必要ならば罫線の設定
    End With
 
  ※人数を固定しない方法としては、
  人のの名前を変数に入れて、それをセルに代入するなんて
  無駄なこと(愚かなこと)は考えないことです。
  手操作の方が容易で簡単、修正にも対応できる。、
  多分、10行位で記述できる処理です。
 
 
         
  
 

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

test3では、各教科の点数をランダムに設定していますが、
実際の運用としては、プログラムが勝手に点数をつけることはあり得ないので
手入力になるでしょう。
 
課題と実運用とを切り分けて考えないといけませんね・・・
セルの塗りつぶし色を設定する件についても
コードで設定する必要性はなく、目視で変更したほうが実運用に向いています。

投稿日時: 18/05/13 15:49:01
投稿者: santasan1224

様々なご指摘ありがとうございます。
 
今回は実用に置き換えて考えれば効率の悪い課題だったので
それは私自身も手入力でやった方が速いとは思っていました。
ですが、練習課題でしたので、今回はご容赦下さい。
 
ありがとうございました。