Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


1988 / 13645 ツリー ←次へ | 前へ→

【70639】行及び列の集計 おおば 11/12/11(日) 10:52 質問[未読]
【70640】Re:行及び列の集計 UO3 11/12/11(日) 11:25 発言[未読]
【70641】Re:行及び列の集計 おおば 11/12/11(日) 11:42 回答[未読]
【70642】Re:行及び列の集計 UO3 11/12/11(日) 16:52 発言[未読]
【70643】Re:行及び列の集計 UO3 11/12/11(日) 17:45 発言[未読]
【70648】Re:行及び列の集計 おおば 11/12/12(月) 8:31 回答[未読]
【70652】Re:行及び列の集計 UO3 11/12/12(月) 11:24 回答[未読]
【70653】Re:行及び列の集計 おおば 11/12/12(月) 12:29 お礼[未読]

【70639】行及び列の集計
質問  おおば  - 11/12/11(日) 10:52 -

引用なし
パスワード
   行の合計点、平均、数値の個数を計算して出力をしようと下記のように書きましたが全て被ってしまい 最後の”数値の個数”のみが表示されてしまいました
1行ずつずらして表示する方法を教えていただきたいのですが
また、列の集計方法も知りたいのでよろしく御願いいたします


Sub 点数集計()
  Dim sht As Worksheet
  Set sht = Workbooks("学力試験.xls").Worksheets("試験結果")  
  sht.Activate  With ActiveSheet.ListObjects.Add(SourceType:=xlSrcRange, Source:=Range("B7").CurrentRegion)
     .Range.Sort key1:=Range("K8"), order1:=xlDescending '降順
     .ShowTotals = True
 '------合計点を記入する---------
     .ListColumns("国語").TotalsCalculation = xlTotalsCalculationSum
     .ListColumns("英語").TotalsCalculation = xlTotalsCalculationSum
     .ListColumns("社会").TotalsCalculation = xlTotalsCalculationSum
     .ListColumns("数学").TotalsCalculation = xlTotalsCalculationSum
     .ListColumns("理科").TotalsCalculation = xlTotalsCalculationSum
     .ListColumns("合計点").TotalsCalculation = xlTotalsCalculationSum
 '------平均点を記入する---------
     .ListColumns("国語").TotalsCalculation = xlTotalsCalculationAverage
     .ListColumns("英語").TotalsCalculation = xlTotalsCalculationAverage
     .ListColumns("社会").TotalsCalculation = xlTotalsCalculationAverage
     .ListColumns("数学").TotalsCalculation = xlTotalsCalculationAverage
     .ListColumns("理科").TotalsCalculation = xlTotalsCalculationAverage
     .ListColumns("合計点").TotalsCalculation = xlTotalsCalculationAverage
  '------数値データーの数を記入する---------
     .ListColumns("国語").TotalsCalculation = xlTotalsCalculationCountNums
     .ListColumns("英語").TotalsCalculation = xlTotalsCalculationCountNums
     .ListColumns("社会").TotalsCalculation = xlTotalsCalculationCountNums
     .ListColumns("数学").TotalsCalculation = xlTotalsCalculationCountNums
     .ListColumns("理科").TotalsCalculation = xlTotalsCalculationCountNums
     .ListColumns("合計点").TotalsCalculation = xlTotalsCalculationCountNums
  End With
End Sub

【70640】Re:行及び列の集計
発言  UO3  - 11/12/11(日) 11:25 -

引用なし
パスワード
   ▼おおば さん:

こんにちは。
2003で「鳴り物いり」で登場したListObjectも、最近は、ほとんど事例を見かけなくなっています。
便利なようで、ちょっと機能が中途半端といった印象で、通常の処理のほうが小回りがきくようにも思えます。

シートのレイアウトを教えていただけますか?

【70641】Re:行及び列の集計
回答  おおば  - 11/12/11(日) 11:42 -

引用なし
パスワード
   ▼UO3 さん:
>▼おおば さん:
>シートのレイアウトを教えていただけますか?
シートのレイアウトは以下のようになっています
よろしくおねがいします
受験番号    受験者    国語    英語    社会    数学    理科    合計
A-0001    柴田 美鈴    77    66    82    90    49    
A-0002    高原 千代    92    99    89    69    70    
A-0003    戸田 賢治    87    51    77    41    50    
A-0004    奈良橋 勧    68    81    60    81    70    
A-0005    松野 理恵    92    100    100    89    90    
A-0006    宮川  翠    70    81    71    76    77    
A-0007    村上 伊代    25    49    48    32    40    
A-0008    浅野 真紀    70    70    69    70    70    
A-0009    石原 勇太郎    78    92    22    88    67    
A-0010    植野 玲人    88    92    96    100    99    
A-0011    岡崎 由美    59    71    66    71    50    
A-0012    金谷  実    100    100    100    100    100    
A-0013    久慈 恵輔    62    88    66    55    44    
A-0014    佐々木 真    44    55    66    77    88    
A-0015    仁科 恭子    99    77    55    33    80    
A-0016    丸山  昇    72    72    69    70    50    
A-0017    平下 修一    77    45    90    88    86

【70642】Re:行及び列の集計
発言  UO3  - 11/12/11(日) 16:52 -

引用なし
パスワード
   ▼おおば さん:

レイアウトありがとうございます。
このリストの開始の行と列を知りたいのですが。
Source:=Range("B7").CurrentRegion から、おおらくは左上隅がB7なんだろうなと想像していますが。
(絶対ではないですけどね)
ちょっとわからないのが、リスト領域をソートするのにキーをK列にしておられるところ。
なんとなく、これも、合計点でのソートなんだろうなと思うのですが、
アップいただいたリストイメージをB列から配置すると、合計列はI列になるので。

【70643】Re:行及び列の集計
発言  UO3  - 11/12/11(日) 17:45 -

引用なし
パスワード
   ▼おおば さん:

もう1つ(というか、2つ)質問です。

最終的には、後々の追加入力も含めてListObjerctを有効にしておきたいということでしょうか?
それとも、合計が入ればいいということでしょうか?

で、かりに、どうしてもListObjectということだとして、エクセルは203ですか?
それとも2007以降ですか?
ListObjectでの【振る舞い】が、両者では、少し異なったりしているようですので。

【70648】Re:行及び列の集計
回答  おおば  - 11/12/12(月) 8:31 -

引用なし
パスワード
   ▼UO3 さん:
>▼おおば さん:
>
1:合計列は”I”でした。
2:各列(国語;英語 など)の合計;平均:個数を記入
3:2003です
よろしく御願いします

【70652】Re:行及び列の集計
回答  UO3  - 11/12/12(月) 11:24 -

引用なし
パスワード
   ▼おおば さん:

こんにちは

コード案です。
合計行は1つのみ設定可能ですから平均点と件数はリスト外(リストから2行あけた1の行)に表示します。
留意点があります。できあがったリストはListObjectですから行の追加が可能です。
追加の行の合計列には自動的に計算式が入りますし、平均点、件数も、追加を反映します。
ただし、リストが下に拡大して下いくわけですので、この平均点、件数の行の上にかぶさってしまいます。
なので、たとえば行を3行追加するということであれば、追加前に、リストと平均点の行の間に、
3行程度の行をインサートしておいてください。

Sub Sample()
  Dim fCol As Long, tCol As Long
  Dim zCol As Long
  Dim fRow As Long, tRow As Long
  Dim zRow As Long
  Dim sht As Worksheet
  
  Set sht = Workbooks("学力試験.xls").Worksheets("試験結果")

  sht.Activate
  If ActiveSheet.ListObjects.Count = 0 Then
    ActiveSheet.ListObjects.Add SourceType:=xlSrcRange, Source:=Range("B7").CurrentRegion
  End If
  With ActiveSheet.ListObjects(1)
    fCol = .ListColumns("国語").Range.Column
    tCol = .ListColumns("理科").Range.Column
    zCol = .ListColumns("合計点").Range.Column
    fRow = .Range.Row + 1
    tRow = .Range.Row + .Range.Rows.Count - 2
    zRow = tRow + 4
 '------合計列の計算式セット---------
    With .ListColumns("合計点").Range
      .Cells.Offset(1).Resize(.Cells.Count - 2).FormulaR1C1 = _
                  "=SUM(RC" & fCol & ":RC" & tCol & ")"
    End With
    .Range.Sort key1:=Range("I8"), order1:=xlDescending '降順
    .ShowTotals = True
 '------合計点を記入する---------
    .ListColumns("国語").TotalsCalculation = xlTotalsCalculationSum
    .ListColumns("英語").TotalsCalculation = xlTotalsCalculationSum
    .ListColumns("社会").TotalsCalculation = xlTotalsCalculationSum
    .ListColumns("数学").TotalsCalculation = xlTotalsCalculationSum
    .ListColumns("理科").TotalsCalculation = xlTotalsCalculationSum
    .ListColumns("合計点").TotalsCalculation = xlTotalsCalculationSum
  End With
 '------平均点を記入する---------
  Cells(zRow, 1).Value = "平均点"
  Cells(zRow, fCol).Resize(, zCol - fCol + 1).FormulaR1C1 = _
                "=AVERAGE(R" & fRow & "C:R" & tRow & "C)"
  '------数値データーの数を記入する---------
  Cells(zRow + 1, 1).Value = "件数"
  Cells(zRow + 1, fCol).Resize(, zCol - fCol + 1).FormulaR1C1 = _
                "=COUNT(R" & fRow & "C:R" & tRow & "C)"
                
  Set sht = Nothing
  
End Sub

【70653】Re:行及び列の集計
お礼  おおば  - 11/12/12(月) 12:29 -

引用なし
パスワード
   ▼UO3 さん:
有難うございました
早速トライします
又、何かありましたらよろしく
御願いいたします

1988 / 13645 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free