[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]
『集計のマクロ記述とＶＢＡ記述の違いを教えて下さい』（万年太郎）
初めまして、VBA初心者の万年太郎です。随時ｱｯﾌﾟﾃﾞｰﾄされるﾃﾞｰﾀの平均値、最大値、最低値を その都度集計しているのですが、マクロとＶＢＡとで記述の仕方が異なる様で、ＶＢＡ記述が わからないのでどなたか教えて頂けませんか？ｻﾝﾌﾟﾙとして英語の集計を載せます。 英語マクロではうまくいくのですが。英語ＶＢＡ記述ではエラーになります。
A B C D E 受験番号 英語 10001 100 平均点 10002 85 最高点 10003 72 最低点 10004 98 10005 92 10006 42 10007 15 10008 8 10009 75 10010 88 10011 92 10012 60 10013 68 10014 78 10015 100
Sub 英語マクロ集計()
Range("E2").Select
ActiveCell.FormulaR1C1 = "=SUM(RC[-3]:R[14]C[-3])/COUNT(RC[-3]:R[14]C[-3])"
Range("E3").Select
ActiveCell.FormulaR1C1 = "=MAX(R[-1]C[-3]:R[13]C[-3])"
Range("E4").Select
ActiveCell.FormulaR1C1 = "=MIN(R[-2]C[-3]:R[12]C[-3])"
Range("E5").Select
End Sub
Sub 英語VBA集計() Dim heikinn As Integer Dim saikou As Integer Dim saitei As Integer Dim wh As Worksheet Dim LastRow As Long
Set wh = Worksheets("sheet1")
With wh
LastRow = .Range("A65536").End(xlUp).Row
heikin = Sum("B2:B" & LastRow) / Count("B2:B" & LastRow).Formula
saikou = Max("B2:B" & LastRow).Formula
saitei = Min("B2:B" & LastRow).Formula
.Range("E2").Value = heikin
.Range("E3").Value = saikou
.Range("E4").Value = saitei
End With
End Sub
Excel2000 Window2000
heikinは
heikin = Application.Sum(.Range("B2", "B" & LastRow)) / _
Application.Count(.Range("B2", "B" & LastRow))
(参考)
一発で出来ました。ありがとうございます！ 万年太郎
今、下記の集計表作成に取り組んでいますが、繰り返し表示される部分のルーチン化がよくわからないので どなたか知恵を貸して頂けますか？
A B C D E F 1 国語 数学 英語 合計 2 平均点 3 A組 最高点 4 最低点 5 平均点 6 B組 最高点 7 最低点 8 平均点 9 C組 最高点 10 最低点 11 平均点 12 D組 最高点 13 最低点 14 平均点 15学校全体 最高点 16 最低点
Sub 集計表表示テスト()
Dim i, j, k As Integer
Dim v, x, y As Variant
v = Array("国語", "数学", "英語", "合計")
x = Array("A組", "", "", "B組", "", "", "C組", "", "", "D組", "", "", "学校全体")
y = Array("平均点", "最高点", "最低点", "平均点", "最高点", "最低点", "平均点", "最高点", "最低点", _
"平均点", "最高点", "最低点", "平均点", "最高点", "最低点")
'y = Array("平均点", "最高点", "最低点")
'*** 各教科表示 ****
For i = 0 To 3
ActiveSheet.Cells(1, 5 + i).Value = v(i)
Next i
'*** 集計結果表示 ****
For k = 0 To 14
ActiveSheet.Cells(2 + k, 4).Value = y(k)
Next k
'*** 各組表示 *****
For j = 0 To 12
ActiveSheet.Cells(3 + j, 3).Value = x(j)
Next j
End Sub
y = Array("平均点", "最高点", "最低点") のみで5回表示させる記述方法を考えているのですが、思い浮かびません。
万年太郎
With ActiveSheet
.Cells(1,5).Resize(,4).Value = v
.Cells(2,4).Resize(15).Value = Application.Transpose(y)
.Cells(3,3).Resize(13).Value = Application.Transpose(x)
End With
かな？
(seiya)
seiyaさん、下記のsubｺｰﾄﾞを作成し、実行した所、下記の様な表示になりましたが、どこに問題があるのでしょうか？
Sub 集計表表示テスト2()
Dim i, j, k As Integer
Dim v, x, y As Variant
v = Array("国語", "数学", "英語", "合計")
x = Array("A組", "", "", "B組", "", "", "C組", "", "", "D組", "", "", "学校全体")
y = Array("平均点", "最高点", "最低点")
With ActiveSheet
.Cells(1, 5).Resize(, 4).Value = v
.Cells(2, 4).Resize(16).Value = Application.Transpose(y)
.Cells(3, 3).Resize(13).Value = Application.Transpose(x)
End With
End Sub
国語 数学 英語 合計 平均点 A組 最高点 最低点 #N/A B組 #N/A #N/A #N/A C組 #N/A #N/A #N/A D組 #N/A #N/A #N/A 学校全体 #N/A #N/A
万年太郎
Sub test()
With ActiveSheet
.Cells(1,5).Resize(,4).Value = [{"国語","数学","英語","合計"}]
.Cells(2,4).Resize(15).Value = [{"A組";"";"";"B組";"";"";"C組";"";"";"D組";"";"";"学校全体";"";""}]
With .Cells(3,3).Resize(3)
.Value = [{"平均点";"最高点";"最低点"}]
.AutoFill .Resize(16)
End With
End With
End Sub
では？
(seiya)
seiyaさん、ありがとうございます。記述方法、大変勉強になりました。シンプルでいいですね。 週明けに又レスします。 万年太郎
A組、B組、C組、D組、学校全体別に各教科の平均、最高、最低点を集計するﾌﾟﾛｸﾞﾗﾑを作成したのですが、 わざとＢ組のﾃﾞｰﾀをｽｷｯﾌﾟしたところ、Ａ組のﾃﾞｰﾀでストップしてしまいました。どうしたら最後まで 行くか教えて下さい。又、記述方法がまずいと思うのでそれも併せてご教示お願いします。
受験番号 組 国語 数学 英語 合計 1001 A 65 40 72 177 1005 A 45 20 15 80 1008 A 82 80 88 250 1009 A 65 70 92 227 1011 A 92 96 94 282 1002 C 72 65 80 217 1003 C 80 90 88 258 1004 C 96 100 100 296 1006 C 55 30 60 145 1010 C 88 90 94 272 1007 D 78 55 72 205
Sub 三教科集計() Dim wh As Worksheet Dim i, j, k As Integer Dim m() As Variant
Dim LastRow As Integer Dim f1, f2, f3, f4 As Integer Dim l1, l2, l3, l4 As Integer
Set wh = Worksheets("三教科の集計")
With wh
LastRow = .Range("B65536").End(xlUp).Row
'****** 三教科sheetのｾﾚｸﾄ ******
Sheets("三教科の集計").Select
'****** 組別並べ替え
Range("B2").Select
Selection.Sort Key1:=Range("B2"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, SortMethod _
:=xlPinYin
'****** 各組の各教科の平均点、最高点、最低点集計結果表の設定 ***
With ActiveSheet
.Cells(1, 11).Resize(, 4).Value = [{"国語","数学","英語","合計"}]
.Cells(3, 9).Resize(15).Value = [{"A組";"";"";"B組";"";"";"C組";"";"";"D組";"";"";"学校全体";"";""}]
With .Cells(2, 10).Resize(3)
.Value = [{"平均点";"最高点";"最低点"}]
.AutoFill .Resize(12)
End With
End With
'******* A組の国語、数学、英語の平均点、最高点、最低点集計と表示 *****
f1 = Application.Match("A", Range("B:B"), 0)
l1 = Application.Match("A", Range("B:B"), 1)
'=== 国語 C列、K列
heikin = Application.Sum(.Range("C" & f1 & ": C" & l1)) / _
Application.Count(.Range("C" & f1 & ": C" & l1))
saikou = Application.Max(.Range("C" & f1 & ": C" & l1))
saitei = Application.Min(.Range("C" & f1 & ": C" & l1))
.Range("K2").Value = heikin
.Range("K3").Value = saikou
.Range("K4").Value = saitei
'=== 数学 D列、L列
heikin = Application.Sum(.Range("D" & f1 & ": D" & l1)) / _
Application.Count(.Range("D" & f1 & ": D" & l1))
saikou = Application.Max(.Range("D" & f1 & ": D" & l1))
saitei = Application.Min(.Range("D" & f1 & ": D" & l1))
.Range("L2").Value = heikin
.Range("L3").Value = saikou
.Range("L4").Value = saitei
'=== 英語 E列、M列
heikin = Application.Sum(.Range("E" & f1 & ": E" & l1)) / _
Application.Count(.Range("E" & f1 & ": E" & l1))
saikou = Application.Max(.Range("E" & f1 & ": E" & l1))
saitei = Application.Min(.Range("E" & f1 & ": E" & l1))
.Range("M2").Value = heikin
.Range("M3").Value = saikou
.Range("M4").Value = saitei
'******* B組の国語、数学、英語の平均点、最高点、最低点集計と表示 *****
f2 = Application.Match("B", Range("B:B"), 0)
l2 = Application.Match("B", Range("B:B"), 1)
'=== 国語 C列、K列
heikin = Application.Sum(.Range("C" & f2 & ": C" & l2)) / _
Application.Count(.Range("C" & f2 & ": C" & l2))
saikou = Application.Max(.Range("C" & f2 & ": C" & l2))
saitei = Application.Min(.Range("C" & f2 & ": C" & l2))
.Range("K5").Value = heikin
.Range("K6").Value = saikou
.Range("K7").Value = saitei
'=== 数学 D列、L列
heikin = Application.Sum(.Range("D" & f2 & ": D" & l2)) / _
Application.Count(.Range("D" & f2 & ": D" & l2))
saikou = Application.Max(.Range("D" & f2 & ": D" & l2))
saitei = Application.Min(.Range("D" & f2 & ": D" & l2))
.Range("L5").Value = heikin
.Range("L6").Value = saikou
.Range("L7").Value = saitei
'=== 英語 E列、M列
heikin = Application.Sum(.Range("E" & f2 & ": E" & l2)) / _
Application.Count(.Range("E" & f2 & ": E" & l2))
saikou = Application.Max(.Range("E" & f2 & ": E" & l2))
saitei = Application.Min(.Range("E" & f2 & ": E" & l2))
.Range("M5").Value = heikin
.Range("M6").Value = saikou
.Range("M7").Value = saitei
'******* C組の国語、数学、英語の平均点、最高点、最低点集計と表示 *****
f3 = Application.Match("C", Range("B:B"), 0)
l3 = Application.Match("C", Range("B:B"), 1)
'=== 国語 C列、K列
heikin = Application.Sum(.Range("C" & f3 & ": C" & l3)) / _
Application.Count(.Range("C" & f3 & ": C" & l3))
saikou = Application.Max(.Range("C" & f3 & ": C" & l3))
saitei = Application.Min(.Range("C" & f3 & ": C" & l3))
.Range("K8").Value = heikin
.Range("K9").Value = saikou
.Range("K10").Value = saitei
'=== 数学 D列、L列
heikin = Application.Sum(.Range("D" & f3 & ": D" & l3)) / _
Application.Count(.Range("D" & f3 & ": D" & l3))
saikou = Application.Max(.Range("D" & f3 & ": D" & l3))
saitei = Application.Min(.Range("D" & f3 & ": D" & l3))
.Range("L8").Value = heikin
.Range("L9").Value = saikou
.Range("L10").Value = saitei
'=== 英語 E列、M列
heikin = Application.Sum(.Range("E" & f3 & ": E" & l3)) / _
Application.Count(.Range("E" & f3 & ": E" & l3))
saikou = Application.Max(.Range("E" & f3 & ": E" & l3))
saitei = Application.Min(.Range("E" & f3 & ": E" & l3))
.Range("M8").Value = heikin
.Range("M9").Value = saikou
.Range("M10").Value = saitei
'******* D組の国語、数学、英語の平均点、最高点、最低点集計と表示 *****
f4 = Application.Match("D", Range("B:B"), 0)
l4 = Application.Match("D", Range("B:B"), 1)
'=== 国語 C列、K列
heikin = Application.Sum(.Range("C" & f4 & ": C" & l4)) / _
Application.Count(.Range("C" & f4 & ": C" & l4))
saikou = Application.Max(.Range("C" & f4 & ": C" & l4))
saitei = Application.Min(.Range("C" & f4 & ": C" & l4))
.Range("K11").Value = heikin
.Range("K12").Value = saikou
.Range("K13").Value = saitei
'=== 数学 D列、L列
heikin = Application.Sum(.Range("D" & f4 & ": D" & l4)) / _
Application.Count(.Range("D" & f4 & ": D" & l4))
saikou = Application.Max(.Range("D" & f4 & ": D" & l4))
saitei = Application.Min(.Range("D" & f4 & ": D" & l4))
.Range("L11").Value = heikin
.Range("L12").Value = saikou
.Range("L113").Value = saitei
'=== 英語 E列、M列
heikin = Application.Sum(.Range("E" & f4 & ": E" & l4)) / _
Application.Count(.Range("E" & f4 & ": E" & l4))
saikou = Application.Max(.Range("E" & f4 & ": E" & l4))
saitei = Application.Min(.Range("E" & f4 & ": E" & l4))
.Range("M11").Value = heikin
.Range("M12").Value = saikou
.Range("M13").Value = saitei
'******* 全体の国語、数学、英語の平均点、最高点、最低点集計と表示 *****
'=== 国語 C列、K列
heikin = Application.Sum(.Range("C2 : C" & LastRow)) / _
Application.Count(.Range("C2: C" & LastRow))
saikou = Application.Max(.Range("C2 : C" & LastRow))
saitei = Application.Min(.Range("C2 : C" & LastRow))
.Range("K14").Value = heikin
.Range("K15").Value = saikou
.Range("K16").Value = saitei
'=== 数学 D列、L列
heikin = Application.Sum(.Range("D2: D" & LastRow)) / _
Application.Count(.Range("D2: D" & LastRow))
saikou = Application.Max(.Range("D2: D" & LastRow))
saitei = Application.Min(.Range("D2: D" & LastRow))
.Range("L14").Value = heikin
.Range("L15").Value = saikou
.Range("L16").Value = saitei
'=== 英語 E列、M列
heikin = Application.Sum(.Range("E2: E" & LastRow)) / _
Application.Count(.Range("E2: E" & LastRow))
saikou = Application.Max(.Range("E2: E" & LastRow))
saitei = Application.Min(.Range("E2: E" & LastRow))
.Range("M14").Value = heikin
.Range("M15").Value = saikou
.Range("M16").Value = saitei
End With
End Sub
万年太郎さん 各投稿にはHNをきちんと記入してください。 それと、半角カタカナは使用しないでください。
集計表の最後の列に「合計」とありますが、何を合計した値ですか？ (seiya)
①すみません。半角カタカナは使用しないよう、注意します。それとHNをきちんと記入する、とはどういう事でしょう？ ②合計とは国語、数学、英語の総得点です。計算式を忘れていましたが、今回はとばして結構です。
宜しくアドバイスお願い申し上げます。m(_ _)m 万年太郎
HN(Handle Name)とは、ネット上で使用するニックネームです。 署名がないと誰の投稿なのかわからなくなりますので (最近、HNを記入しないで投稿する者が横行しています）
合計の件わかりました。 試してください。
Sub 三教科集計()
Dim a, b(), i As Long, ii As Integer, n As Long
With Sheets("三教科集計")
a = Range("a1").CurrentRegion.Resize(,6).Value
' A1.CurrentRegionの６列（A:F)までの値を 変数 a に格納
ReDim b(1 To Rows.Count, 1 To 7)
' 出力用配列変数 b の次元・要素数を指定
With CreateObject("Scripting.Dictionary")
' Dictionary object を使用して「組」別のデータを加工
.CompareMode = vbTextCompare
' Non-Case-Sensitive (比較時に大文字・小文字の区別をしない)
For i = 1 To UBound(a,1)
If Not .exists(a(i,2)) Then
n = IIf(n=0,1,n + 3)
' n で配列 b 中の各「組」別の位置を指定
' 一組に３行確保 （平均、最高、最低）
.add a(i,2), n '<- 変更 IIf(n=0,1,n+3)
b(n + 1, 1) = a(i,2) : b(n,2) = "平均点"
b(n + 1, 2) = "最高点" : b(n + 2, 2) = "最低点"
For ii = 3 To 6 : b(n,ii) = a(i,ii) : Next
' 各組の一行目を夫々累計する（後に平均を算出するため）
End If
x = .item(a(i,2))
For ii = 3 To 6
b(x,ii) = b(x,ii) + a(i,ii) '<---- これ抜けてました。
b(x + 1, ii) = WorksheetFunction.Max(Val(b(x + 1, iii)),Val(a(i,ii)))
b(x + 2, ii) = IIf(b(x + 2,ii) <> "",WorksheetFunction.Min(b(x + 2,ii), a(i,ii)), a(i,ii))
Next
' 各科目の2，3行目の 最高、最低を逐次更新
b(x,7) = b(x,7) + 1
' 配列 b の7列目に各組の出現回数を保持（平均値を出すため）
Next
End With
For i = 1 To n Step 3 '<--- これ抜けてました！
For ii = 3 To 6
b(n,ii) = WorksheetFunction.Round(b(n,ii) / b(n,7), 2)
Next
Next
' 各組の一行目を平均値に置き換える
With .Range("k1")
.Resize(,6).Value = [{"組","","国語","数学","英語","合計"}]
.Offset(1).Resize(n+2,6).Value = b
End With
End With
End Sub
(seiya)
seiyaさん、有難うございます。HNの件、わかりました。前回はあわてていたので失念してしまいました。
早速,上記のSubルーチンをｺﾋﾟﾍﾟしたところ、
b(x + 2, ii) = IIf(b(x + 2,ii) <> "",WorksheetFunction.Min(b(x + 2,ii), a(i,ii), a(i,ii))
が赤文字で表示され、エラーになってしまいました。どこに記述の問題があるのでしょうか？ 万年太郎
おっと、)の数があっていませんね... WorksheetFunction.Min(b(x+2,ii),a(i,ii)), a(i,ii)) にしてみてください。 (seiya)
又のレスですみません。 今度は
For ii = 3 To 6
b(x + 1, ii) = WorksheetFunction.Max(b(x + 1, 3), a(i, ii)) '①
b(x + 2, ii) = IIf(b(x + 2, ii) <> "", WorksheetFunction.Min(b(x + 2, ii), a(i, ii)), a(i, ii))
Next
①が 実行時エラー’1004 worksheetFunctionクラスのMaxプロパティを取得できません と表示されストップしてしまうのですが・・・
万年太郎
これも b(x + 1, ii) = IIf(b(x+1,ii) <> "", WorksheetFunction.Max(b(x+1,ii),a(i,ii)),a(i,ii)) にしなくてはいけませんでしたね. (seiya)
同じくエラーになってしまいます・・・ （万年太郎）
んじゃ単純に b(x+1,ii) = WorksheetFunction.Max(Val(b(x+1,ii)), Val(a(i,ii))) では？ (seiya)
seiyaさんの洗練された構造がこちらの知識では未だ理解出来ないのでお手数ですが、順次解説して頂けませんか？ m(_ _)m
まず、ReDim b(1 To Rows.Count, 1 To 7) の意味です。 万年太郎
コードに解説をつけました。 二箇所大事なのが抜けていました。 (seiya)
ありがとうございます！じっくり解読させて頂きます。 （万年太郎）
久しぶりの登校ならぬ投稿です。じっくり勉強させて頂きました。しかし、Seiyaさんが作成したサンプルプログラムでは正常な値が出て来ないので、 お手数ですが、組別の平均、最高点、最低点集計のアリゴリスムを見直して頂けませんでしょうか？これがネックになっており、配列変数等の 理解のさまだけになっております。 宜しくお願いします。 m(_ _)m 万年太郎
一行変更してみましたので試してください。 (seiya)
早速のレスありがとうございます。 .add a(i,2), n '<- 変更 IIf(n=0,1,n+3) 変更しましたが、平均点の算出がうまくいきません。 万年太郎
どのようにうまくいかないのでしょう？ (seiya)
すべての平均点欄に平均点でなく、組毎の各科目の合計点らしき値が出るのですが、手計算の値と一致しません。 万年太郎
平均点の計算部分は
b(n,ii) = WorksheetFunction.Round(b(n,ii) / b(n,7), 2)
ですので、b(n, 7) が 1 になっているのでしょうか？ Step debug できますか？ もしできなければ、その一行下に MsgBox b(n,7) と入れて実行してみてください。 (seiya)
おっと!
b(i,ii) = WorksheetFunction.Round(b(i,ii) / b(i, 7), 2)
n -> i
に変更してください。 (seiya)
この集計部分がこちらにとって今後の業務に大きく影響する重要部分ですので、なんとしても理解したいと思います。 seiyaさんの解説に従って修正したVBAを下記に載せますので、どこに問題があるかみて頂けませんでしょうか？
Sub 三教科集計3()
Dim a, b(), i As Long, ii, x As Integer, n As Long
With Sheets("三教科の集計")
a = Range("A1").CurrentRegion.Resize(, 6).Value '--- A1.CurrentRegionの６列（A:F)までの値を 変数 a に格納
ReDim b(1 To Rows.Count, 1 To 6) '--- 出力用配列変数 b の次元・要素数を指定
With CreateObject("Scripting.Dictionary") '--- Dictionary object を使用して「組」別のデータを加工
.CompareMode = vbTextCompare '--- Non-Case-Sensitive (比較時に大文字・小文字の区別をしない)
For i = 2 To UBound(a, 1)
If Not .exists(a(i, 2)) Then
n = IIf(n = 0, 1, n + 3) '--- n で配列 b 中の各「組」別の位置を指定
'--- 一組に３行確保 （平均、最高、最低）
'.Add a(i, 2), IIf(n = 0, 1, n + 3)
.Add a(i, 2), n '<- 変更 IIf(n=0,1,n+3)
b(n + 1, 1) = a(i, 2)
b(n, 2) = "平均点"
b(n + 1, 2) = "最高点"
b(n + 2, 2) = "最低点"
For ii = 3 To 6: b(n, ii) = a(i, ii): Next '--- 各組の一行目を夫々累計する（後に平均を算出するため）
End If
x = .Item(a(i, 2))
For ii = 3 To 6
b(x, ii) = b(x, ii) + a(i, ii) '<---- これ抜けてました。
b(x + 1, ii) = WorksheetFunction.Max(Val(b(x + 1, ii)), Val(a(i, ii)))
b(x + 2, ii) = IIf(b(x + 2, ii) <> "", WorksheetFunction.Min(Val(b(x + 2, ii)), Val(a(i, ii))), Val(a(i, ii)))
Next '--- 各科目の2，3行目の 最高、最低を逐次更新
b(x, 6) = b(x, 6) + 1 '--- 配列 b の7列目に各組の出現回数を保持（平均値を出すため）
Next
End With
For i = 1 To n Step 3 '<--- これ抜けてました！
For ii = 3 To 6
b(i, ii) = WorksheetFunction.Round(b(i, ii) / b(i, 6), 2)
'MsgBox b(i, 7)
Next
Next '--- 各組の一行目を平均値に置き換える
With .Range("J1")
.Resize(, 6).Value = [{"組","","国語","数学","英語","合計"}]
.Offset(1).Resize(n + 2, 6).Value = b
End With
End With
End Sub
の結果が下記の様に出ます。
組 国語 数学 英語 合計 平均点 0 0 0 1 A 最高点 92 96 94 282 最低点 45 20 15 80 平均点 0 0 0 1 B 最高点 100 100 100 300 最低点 75 65 58 198 平均点 0 0 0 1 C 最高点 96 100 100 296 最低点 55 30 60 145 平均点 0 0 0 1 D 最高点 78 55 72 205 最低点 78 55 72 205
b(i,ii) = WorksheetFunction.Round(b(i,ii) / b(i, 7), 2) の７を６にしてあります。 ７ではエラーになり、ストッップしてしまいますが・・・ 万年太郎
1) 出力用の配列変数の第二次元は 7 でないといけません。
7 列目は出力しませんが平均値を算出するための分母を格納します。
ReDim b(1 To Rows.Count, 1 To 7)
にしておかないと、エラーになりますよ。
2) b(x,7) = b(x,7) + 1 で 7列目に分母を計算 (6はだめ)
3) 合計/7列目(分母) = 平均
b(i,ii) = WorksheetFunction.Round(b(i,ii)/ b(i,7), 2)
(seiya)
ReDim b(1 To Rows.Count,1 To 7)に修正し、
For i = 1 To n Step 3
For ii = 3 To 6
b(i, ii) = WorksheetFunction.Round(b(i, ii) / b(i, 7), 2)
Next
Next
上記のように修正して実行したら、 b(i, ii) = WorksheetFunction.Round(b(i, ii) / b(i, 7), 2) の部分で黄色いマーカーが出て 「実行時：エラー ０を除算しました」というエラーメッセージが出てストップしてしまいます。
万年太郎
b(x,7) = b(x,7) + 1 に変更してありますか？ (seiya)
すいません。今、変更します。万年太郎
b(x,7) = b(x,7) + 1 に変更たところ、うまく動きましたが、やはり平均点欄がおかしいようです。
組 国語 数学 英語 合計 平均点 82.8 69.2 86.6 239 A 最高点 92 96 94 282 最低点 45 20 15 80 平均点 112 115 111 338 B 最高点 100 100 100 300 最低点 75 65 58 198 平均点 96 88 104 287 C 最高点 96 100 100 296 最低点 55 30 60 145 平均点 156 110 144 410 D 最高点 78 55 72 205 最低点 78 55 72 205
A組の平均点が手計算とあわない B組の平均点が最高の１００点をうわまわっており、ありえない点数が出ている D組は１人であるにもかかわらず平均点は２倍
万年太郎
それでは 7 列目も表示してみましょう。
最後の .Offset(n + 2, 6).Value = b の 6 を 7 に変更してください。 (seiya)
合計点を2重に計算しているようです。
For ii = 3 To 6 : b(n, ii) = a(i, ii) : Next '各組の.... の2行を削除してみてください。 (seiya)
やっとうまくいきました。ありがとうございます。落ち着いて解読できますので、しばしSet debugしながら解読してみます。 seiyaさん、お忙しい中、おつきあい頂き、大変感謝しております。 万年太郎
うまくうごいてくれて良かったです。 (seiya)
早速ですが、
For i = 2 To UBound(a, 1)
If Not .exists(a(i, 2)) Then
n = IIf(n = 0, 1, n + 3) '--- n で配列 b 中の各「組」別の位置を指定
'--- 一組に３行確保 （平均、最高、最低）
.Add a(i, 2), n
あたりの意味ですが、最初のループ回数 i=2のとき、a(2,2)が存在しなければ、 ｎ＝０が真ならｎ＝１を返し、n=0 でなければ、n+3を返し、Add ａ(2,2)、１ ・・・のあたりで Add ａ(2,2)、１ の意味がよくわからないので説明頂けませんか？
万年太郎
n は Long型の変数なので初期値は 0 です。 最初の"組"(n = 0 の時)には 1 行目から3行分、次は n = (n = 1) + 3 = 4 行目から3行分,,, を出力用配列 b に割り当てています。
そして、その"組"の行IndexをDictionaryのitemに読み込ませておけば、そこから3行分の処理 ですみますね？
x = .Item(a(i,2)) の時の x を見ていただければわかります。 (seiya)
よう、わかりました。それとの関係ですが、Ａ組、Ｃ組、Ｂ組、Ｄ組の順に出ているのですが、 これは出現回数の多い順に並べ替えているのですね？ 万年太郎
いいえ、それはあくまでも元データの出現順になります。 それをソートするにはもう一工夫必要です。 (seiya)
わかりました。しばし解読します。ありがとうございます！ （万年太郎）
こんなのもいかがでせうか？
いいえぇな、seiyaはんのんが上手いこといかんうちにと思うて手がけたんですけど、
間にあわんかったんで一旦ほかすしたマクロですワ・・・（笑
（弥太郎）
Sub ほかすつもり()
Dim LastRow As Long, i As Long, m As Long, n As Integer, x, f1, f2
With Sheets("三教科の集計")
LastRow = .Range("B65536").End(xlUp).Row
.Range("a2").Resize(LastRow - 1, 6).Sort Key1:=.Range("b2"), Order1:=xlAscending
ReDim x(1 To LastRow * 3 + 1, 1 To 6)
.Range("k1").Resize(, 4) = Array("国語", "数学", "英語", "合計")
i = 1
Do While i < LastRow
i = i + 1
f1 = Application.Match(.Cells(i, 2), .Cells(1, 2).Resize(LastRow), 0)
f2 = Application.Match(.Cells(i, 2), .Cells(1, 2).Resize(LastRow), 1)
m = m + 1
x(m, 2) = "平均": x(m + 1, 1) = .Cells(i, 2) & "組": x(m + 1, 2) = _
"最高点": x(m + 2, 2) = "最低点"
For n = 3 To 5
x(m, n) = Round(WorksheetFunction.Sum(.Cells(f1, n).Resize(f2 - (f1 - 1))) / (f2 - (f1 - 1)), 1)
x(m, 6) = x(m, 6) + x(m, n)
x(m + 1, n) = WorksheetFunction.Max(.Cells(f1, n).Resize(f2 - (f1 - 1)))
x(m + 1, 6) = x(m + 1, 6) + x(m + 1, n)
x(m + 2, n) = WorksheetFunction.Min(Cells(f1, n).Resize(f2 - (f1 - 1)))
x(m + 2, 6) = x(m + 2, 6) + x(m + 2, n)
Next n
m = m + 2: i = f2
Loop
.Cells(2, 9).Resize(m, 6) = x
End With
End Sub
ひぇーっ、すごいサンプル提供ありがとうございます！弥太郎様 これも含めて勉強させて頂きます！ 万年太郎
早速コピペして試したのですが、 x(m, n) = Round(WorksheetFunction.Sum(.Cells(f1, n).Resize(f2 - (f1 - 1))) / (f2 - (f1 - 1)) 1) あたりで構文エラーになり、 ストップしてしまいました。 確認お願いします。弥太郎様 （万年太郎）
それがしが提供するマクロはすべからく検証した上（尤も検証不足のマクロもあります
が）呈示しとります。
せやからこのマクロも万年太郎はんの為に生を受けた筋金入りのマクロですわ（笑
まぁ、冗談はさておいて、何故エラーが出るのか？？？ですわ。
Round関数は2000でも使える筈なんですけど・・・。
（弥太郎）
万年太郎さんの記載ミスでしょうか？？？
>x(m, n) = Round(WorksheetFunction.Sum(.Cells(f1, n).Resize(f2 - (f1 - 1))) / (f2 - (f1 - 1)) 1) あたりで構文エラーになり、
~~~~
「,」が無くなっているのが気になります・・・
（キリキ）（〃⌒o⌒)b
あ、ホンマや。
こんなんでイチャモン付けられたら、かなわんなぁ・・・（笑
せんせぇ、おおきに～。
（弥太郎）
VB の Round関数は WorksheetFunctionのRound関数とは違うような？ (seiya)
キリキさん、弥太郎さん、ごめんなさい。こちらの勘違いでした。弥太郎さんはA,B,C,D順にソートするアリゴリズムまで 作ってくれたのですね。それを確かめずに（元データの部分のちょっと下の数行にあるほかのデータが混在した状態で）実行し、 再実行した為、エラーになった様です。それにしてもすごいですね。seiyaさんと弥太郎さんのプログラムをじっくり勉強させて 頂きます。まずはお詫びとお礼まで m(_ _)m m(_ _)m m(_ _)m 万年太郎
VB の Round WorksheetFunction.Round 2.5 -> 2 2.5 -> 3 VBの方は、近い「偶数」になると思いましたが？ (seiya)
ヘルプより 機能 指定された小数点位置で丸めた数値を返します。 構文 Round(expression [,numdecimalplaces]) と、こうありますけど、これやとworksheetfunctionと同じと解釈してもよろしいんと ちゃいまっか？ が、しかし、これは小数点を表示するばやい有効で、整数で表示すると確かに偶数に まるめられますなぁ、メモメモ。 （弥太郎）
少数の場合は良かったのですね、間違えました。 すみません。 (seiya)
久し振りの投稿です。 弥太郎さんが作成して下さったプログラム中のある部分について、小生の解釈が間違っていないか不安なので 恥を忍んで質問させて頂きます。
f1 = Application.Match(.Cells(i, 2), .Cells(1, 2).Resize(LastRow), 0) f2 = Application.Match(.Cells(i, 2), .Cells(1, 2).Resize(LastRow), 1)
①MatchはMatch（検索条件、データ表の範囲、検索方法）ですよね？
②Match(.Cells(i, 2), ････ の .Cells(i, 2)はSheets("三教科の集計")を略して”．”で
記述しているのですね？
”Ａ”組の場合、 f1 = Application.Match(.Cells(2, 2), .Cells(1, 2).Resize(LastRow), 0) = 2 f2 = Application.Match(.Cells(2, 2), .Cells(1, 2).Resize(LastRow), 1) = 6
と解釈していいですか？ (万年太郎）
万年太郎さん、まだ見て居られますかね？ どうやら弥太郎さんは、お忙しいようですので・・・。
VBEでローカルウィンドウを開き [F8]でコードを実行させていくと その時点で変数に入力された値が確認出来ますので やってみられてはどうでしょう。
想定と同じ数字が入っていくと思いますが。
(HANA)
HANA様、レスありがとうございます。 ローカルウィンドウで確認作業していますが、目下配列変数の概念を勉強中ですので、 x(m,n)内の数値と記述との関連を確認したく、質問した次第です。 特に小生に判らないのは略の方法で”.”の前が何なのかが判らないので解読につまずいて いるのが現状です。 （万年太郎）
HANAはんフォローおおきに～ 万年太郎はん、遅くなってすんまへん。 いや、仕事がいそがしい上に世界陸上に参加しとりましたもんで（観戦にデスヨ＾＾） ここんとこPCとは縁を切っとりました。
えと、 f1 = Application.Match(.Cells(i, 2), .Cells(1, 2).Resize(LastRow), 0) この意味がわかりまへんのんでっか？ このスレをず～～っと遡ってみておくんなはれ。 これはあんさんが使うてはるコードを少し書き換えただけのもんでっせ。（笑 データがつまっていれば有効な手段と言えます。
"." ←については、その何行目か上の行にWithとして括ってありまっしゃろ？ Withの真ん中辺りにカーソルをもっていってF1キイを叩いて貰うたらその詳細が載って ますから、ここでとやかく言う必要はないんですけど、コードを作成する際
万年太郎.財布の中身=1,000,000円
万年太郎.身長=2メートル
万年太郎.イケメン度=80
と書くのを
With 万年太郎
.財布の中身=1,000,000円
.身長=2メートル
.イケメン度=80
End With
と 万年太郎を省略して書けるというだけの話ですワ。
お分かりいただけました？
ついでに
With 世界陸上
.金メダル=1
.銀メダル=0
.銅メダル=1
.痙攣度=....
End With
（弥太郎）
世界陸上行ってきたんですか！うらやましいな～
さて、本題に入りますが、上記の鮮やかな解説のおかげで”.”の意味がよーわかりました。 ありがとうございます！それはそれとして、配列変数についての質問です。
ReDim x(1 To LastRow * 3 + 1, 1 To 6) の記述ですが、小生の浅薄な知識では 配列変数の範囲設定の仕方がよーわからないので解説頂けませんか？ "LastRow * 3 + 1" と設定した根拠がよーわからんのです。 （万年太郎）
あぁ、このことでっか？ 例えばA組B組C組D組と有って、それぞれの人数が一人ずつとしまへんか。 例え一人ずつでも平均、最高、最低と３種類のデータを抽出せなあきまへんわなぁ。 つまり一人３個のデータがLastRow*3で、一行目は項目が入りますから+1としとりまん ねん。
なんやら自分で説明しとってもよう分からんから、太郎はんにも理解でけへんかもネ。
受験番号 組 国語 数学 英語 合計 1001 A 65 40 72 177 1005 B 45 20 15 80 1008 C 82 80 88 250 1009 D 65 70 92 227
のデータは 組 国語 数学 英語 合計 平均点 65 40 72 177 A 最高点 65 40 72 177 最低点 65 40 72 177 平均点 45 20 15 80 B 最高点 45 20 15 80 最低点 45 20 15 80 平均点 82 80 88 250 C 最高点 82 80 88 250 最低点 82 80 88 250 平均点 65 70 92 227 D 最高点 65 70 92 227 最低点 65 70 92 227
となりますわなぁ。 ですからLastRow(15)*3+1で数が合い・・・まへんわなぁ（笑 (LastRow-1)*3+1が正しいんでっしゃろか、ハハハ。 ま、とにかく変数の最低限必要な要素数確保の式が 1 To LastRow * 3 + 1 でして、それぞれの組の人数が多くなりますと全く関係なくそれより遙かに多い要素数 確保になりますからそう神経質になることもありますまい。 そんな芸術的な要素数確保をせずに、あっさり1 To Rows.Countなんぞにすれば見た目 も粋な仕上がりになりますなぁ。（メモリーは食いますけど、お宅のPCやから） （弥太郎）
なるほど、大雑把な要素数確保の手段として1 to LastRow * 3 + 1 を導入したのですね。 こちらは厳密な要素数確保をしているのかな？と思っていたので・・・ このケースの場合、空き要素が目だってもこれは無視してよい、という解釈でいいですか？ （万年太郎）
又のレスです。Ａ、Ｂ，Ｃ，Ｄのアルファベット順の組をモデルにしたケースを学校別にしたら、 受験生が1人しかいない学校だと、 エラーになってしまいます。
受験番号 学校 国語 数学 英語 合計 1001 馬込 65 40 72 177 1002 牛島 72 65 80 217 1003 鳥山 80 90 88 258 1004 牛島 96 100 100 296 1005 馬込 45 20 15 80 1006 牛島 55 30 60 145 1007 豚湖 78 55 72 205 1008 馬込 82 80 88 250 1009 馬込 65 70 92 227 1010 牛島 88 90 94 272 1011 馬込 92 96 94 282 1012 鳥山 100 100 100 300 1013 鳥山 75 65 58 198 1014 犬海 100 95 100 295
並べ替えまではうまくいくのですが、最初のMATCHあたりでエラーになり、ストップして しまいます。 （万年太郎）
エラーとはどんなエラーでっしゃろか？ 当方の結果はこうなりますけどなぁ。 国語 数学 英語 合計 平均 77.8 71.2 83.5 232.5 牛島組 最高点 96 100 100 296 最低点 55 30 60 145 平均 100 95 100 295 犬海組 最高点 100 95 100 295 最低点 100 95 100 295 平均 85 85 82 252 鳥山組 最高点 100 100 100 300 最低点 75 65 58 198 平均 78 55 72 205 豚湖組 最高点 78 55 72 205 最低点 78 55 72 205 平均 69.8 61.2 72.2 203.2 馬込組 最高点 92 96 94 282 最低点 45 20 15 80
あ、それと厳密な要素数確保なら
Sub ほかすつもり()
Dim dic As Object, LastRow As Long, i As Long, m As Long, n As Integer, x, f1, f2, tbl
Set dic = CreateObject("scripting.dictionary")
With Sheets("三教科の集計")
LastRow = .Range("B65536").End(xlUp).Row
.Range("a2").Resize(LastRow - 1, 6).Sort Key1:=.Range("b2"), Order1:=xlAscending
.Range("k1").Resize(, 4) = Array("国語", "数学", "英語", "合計")
tbl = .Range("b2").Resize(LastRow - 1)
For i = 1 To UBound(tbl, 1)
dic(tbl(i, 1)) = Empty
Next i
ReDim x(1 To dic.Count * 3 + 1, 1 To 6)
とすればそれこそ厳密な要素数を確保でけます。
（弥太郎）
あんさんがお使いのf1,f2などという変数名はセル番地と同じになりますから、f_1とか
に変更すべきかもしれまへん。
あれれ、うまく行くのですか！？明朝、もう一度検証してみます。
ううっ難解な概念の Set dic = CreateObject("scripting.dictionary")が出たあっ！
これも明日、ローカルウィンドウで1ステップで解読してみますが、理解できるか心配。
ともあれ、ありがとうございます。台風が接近しているので気を付けて下さい。（万年太郎）
検証して、エラーの原因がわかりました。元データをコピーして別ｼｰﾄに貼り付ける時、単なる貼付では 「実行時エラー’１００４’ アプリケーション定義またはオブジェクト定義のエラーです。」 が表示されるのですが、形式を選択して貼付→値 の順に貼り付けると、うまくいくようです。 何故単なるコピペでは駄目なのかはわかりませんが・・・・ （＞＜） (万年太郎）
>何故単なるコピペでは駄目なのかはわかりませんが・・・
さいでんなぁ。そればっかしはエクセル君のご機嫌伺いしながら使いこなさなしょうお
まへんしなぁ。
もし、何かが障るとしたら
f1=App.....
f2=App....
の下の行に
If IsError(f2) Then f2 = f1
を挿入すると、機嫌が直ってくれるかもです。
（弥太郎）
久し振りの投稿です。 不肖小生の必死の解読にも関わらず、どーしても理解でけへん部分があるんですわ。 f1 = Application.Match(.Cells(2, 2), .Cells(1, 2).Resize(LastRow), 0) = 2 f2 = Application.Match(.Cells(2, 2), .Cells(1, 2).Resize(LastRow), 1) = 6
x(m, n) = Round(WorksheetFunction.Sum(.Cells(f1, n).Resize(f2 - (f1 - 1))) / (f2 - (f1 - 1)), 1) x(m, 6) = x(m, 6) + x(m, n) x(m + 1, n) = WorksheetFunction.Max(.Cells(f1, n).Resize(f2 - (f1 - 1))) x(m + 1, 6) = x(m + 1, 6) + x(m + 1, n) x(m + 2, n) = WorksheetFunction.Min(Cells(f1, n).Resize(f2 - (f1 - 1)))
に出て来るResizeの意味がさっぱりわからんのです。VBA文法の本、EXCEL学校の全文検索を片っ端から読んではいるのですが、 弥太郎先生の様な鮮やかな解説がなく理解が遅々として進みません。
f1の .Cells(1, 2).Resize(LastRow)とは？ x(m,n)=Round(WorksheetFunction.Sum(.Cells(f1, n).Resize(f2 - (f1 - 1))) / (f2 - (f1 - 1)), 1) のResize って何？ という具合につまずいています。
(><) 万年太郎
こんばんは！
弥太郎さんではありませんがお許しを・・・
ところで、ワークシート関数のOffsetはご存知ですか？
簡単にいうとVbaのResizeはその後半の部分のことなのです。
つまり
ワークシート関数の
OFFSET("A1",1,1,3,3)
とVbaの
Range("A1").Offset(1,1).Resize(3,3)
は同じことなのです。
わかりましたか？詳しくは↓を参考にお勉強してください。
http://www.officetanaka.net/excel/function/function/offset.htm
では、では、またねv(=∩_∩=)v
（SoulMan）
SoulManさん、早速のレスありがとうございます！猛勉させて頂きます！ 昔、別名ハンドルでご教示受けた事あるんで、なつかしーです。（生きていらしたんですか！何ちゃって）
m(_ _)m m(_ _)m m(_ _)m 万年太郎
Manちゃんフォローおおきん～（笑 >（生きていらしたんですか！何ちゃって） こらこら・・・＾＾ （弥太郎）
>Manちゃんフォローおおきん～（笑 弥太郎さん、お久しぶりです。相変わらずお元気そうですね。 先日、フォローして頂いていたのもですから、、ちょっとお邪魔しました。 >（生きていらしたんですか！何ちゃって） はぁ～、、なんや回答も連続して出来ないもんで長引きそうなのは、、パスです。 陰ながら応援していますので頑張ってくださいね。 では、では、またねv(=∩_∩=)v （SoulMan）
SoulMan先生、大変失礼致しました。紹介して下さったOffset関数のＨＰのおかげで 今まで、ずーっとつまずいていたResizeの意味が氷解し、目からうろこが出るようです！
明日、Resize部分を丹念に解読してみます。本当にありがとうございます！
（万年太郎）
あら～っ、＞弥太郎さん になっとる。 さては ししょうとおだてると年貢米の督促状が舞い込むと警戒してまんねんな（笑 コホン、あのぅ、手練れの弟子を抱えておるから光っとるだけのししょうなんですから ししょうを破門にしたら只の下手郎になってまいまんがな。（笑
[ 一覧(最新更新順) ]
YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki.
Modified by kazu.