Excel VBA質問箱 IV

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

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


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

【66664】集計方法を教えてください コロネ 10/9/23(木) 3:56 質問[未読]
【66666】Re:集計方法を教えてください かみちゃん 10/9/23(木) 7:59 発言[未読]
【66667】Re:集計方法を教えてください Yuki 10/9/23(木) 10:50 発言[未読]
【66668】Re:集計方法を教えてください kanabun 10/9/23(木) 10:58 発言[未読]
【66669】Re:集計方法を教えてください Yuki 10/9/23(木) 15:28 発言[未読]
【66670】Re:集計方法を教えてください Yuki 10/9/23(木) 15:39 発言[未読]
【66672】Re:集計方法を教えてください kanabun 10/9/23(木) 17:13 発言[未読]
【66678】Re:集計方法を教えてください kanabun 10/9/24(金) 9:30 発言[未読]
【66681】Re:集計方法を教えてください コロネ 10/9/24(金) 22:08 お礼[未読]

【66664】集計方法を教えてください
質問  コロネ  - 10/9/23(木) 3:56 -

引用なし
パスワード
   大分類 中分類 小分類 値  
============================
あああ ううう XXX 3
あああ ううう YYY 5
あああ ううう ZZZ 1
あああ えええ XXX 7
あああ えええ YYY 4
あああ えええ ZZZ 2
いいい おおお XXX 5
いいい おおお YYY 6
いいい おおお ZZZ 8
いいい かかか XXX 9
いいい かかか YYY 2
いいい かかか ZZZ 3


のデータをプログラミングで

大分類 中分類 XXX YYY ZZZ
========================================
あああ  計   10   9   3
いいい  計   14   8   11  


と集計したいのですが、
ピボットテーブルを使わずに、集計する方法が
分かりません。
みなさまはどう集計されますか?
ピボットテーブルがベストなのでしょうか?

【66666】Re:集計方法を教えてください
発言  かみちゃん E-MAIL  - 10/9/23(木) 7:59 -

引用なし
パスワード
   こんにちは。かみちゃん です。

>大分類 中分類 XXX YYY ZZZ
>========================================
>あああ  計   10   9   3
>いいい  計   14   8   11  
>
>
>と集計したいのですが、
>ピボットテーブルを使わずに、集計する方法が
>分かりません。
>みなさまはどう集計されますか?
>ピボットテーブルがベストなのでしょうか?

ピボットテーブルで集計すると、似た感じのデータは得られます。
ただ、中分類の列は、できないから、別の方法でということでしょうか?

それであれば、連想配列というものを使うという方法もあると思います。
「連想配列△VBA」(△は半角スペース)でWeb等を検索するといろいろ出てくると
思います。

【66667】Re:集計方法を教えてください
発言  Yuki  - 10/9/23(木) 10:50 -

引用なし
パスワード
   ▼コロネ さん:
>ピボットテーブルを使わずに、集計する方法が
>分かりません。

>ピボットテーブルがベストなのでしょうか?
多分

で配列の処理です。
Sheet1にデータでSheet2に出力です。

Sub LoopSum()
  Dim v1   As Variant
  Dim v2   As Variant
  Dim i    As Long
  Dim j    As Long
  Dim k    As Long
  Dim col   As Long
  Dim eR   As Long
  Dim eC   As Long
  
  With Worksheets("Sheet1")
    v1 = .Range("A1").CurrentRegion.Value
  End With
  
  ReDim v2(1 To UBound(v1), 1 To UBound(v1))
  v2(1, 1) = v1(1, 1)
  v2(1, 2) = v1(1, 2)
  
  For i = 2 To UBound(v1)
    For j = 3 To UBound(v2, 2)
      If v2(1, j) = "" Then
        v2(1, j) = v1(i, 3)
        col = j
        eC = j
        Exit For
      Else
        If v1(i, 3) = v2(1, j) Then
          col = j
          Exit For
        End If
      End If
    Next
    For k = 2 To UBound(v2)
      If v2(k, 1) = "" Then
        v2(k, 1) = v1(i, 1)
        v2(k, 2) = "計"
        v2(k, col) = v1(i, 4)
        eR = k
        Exit For
      Else
        If v1(i, 1) = v2(k, 1) Then
          v2(k, col) = v2(k, col) + v1(i, 4)
          Exit For
        End If
      End If
    Next
  Next
  With Worksheets("Sheet2")
    .Cells.ClearContents
    .Range("A1").Resize(eR, eC).Value = v2
  End With
End Sub

【66668】Re:集計方法を教えてください
発言  kanabun  - 10/9/23(木) 10:58 -

引用なし
パスワード
   ▼コロネ さん:
本題と関係ないことですみません m(_ _)m

▼Yuki さん:
よこからすみません

>  With Worksheets("Sheet2")
>    .Cells.ClearContents
    ^^^^^^^
ですが、.UsedRange のほうがよくないですか?

Cells プロパティは使われていないセルも含めて シートの
「すべてのセルの集合」を返すプロパティなので、処理時間が
かかると思いますけど。

【66669】Re:集計方法を教えてください
発言  Yuki  - 10/9/23(木) 15:28 -

引用なし
パスワード
   ▼kanabun さん:
>
>>  With Worksheets("Sheet2")
>>    .Cells.ClearContents
>    ^^^^^^^
>ですが、.UsedRange のほうがよくないですか?
>
試してみました。
65000行でQ列迄のデータ(Excelの大きさ15Mb)
.Cells.ClearContents   <== 0.109375
.UsedRange.ClearContents <== 0.125
という結果でした。
環境によって違いはあるでしょうけど。
理屈から言うとUsedRangeのほうが早そうですね。

【66670】Re:集計方法を教えてください
発言  Yuki  - 10/9/23(木) 15:39 -

引用なし
パスワード
   ▼Yuki さん:
>▼kanabun さん:
>試してみました。
>65000行でQ列迄のデータ(Excelの大きさ15Mb)
>.Cells.ClearContents   <== 0.109375
>.UsedRange.ClearContents <== 0.125
>という結果でした。
>環境によって違いはあるでしょうけど。
>理屈から言うとUsedRangeのほうが早そうですね。

試す毎に値が変化します。
UsedRangeが早くなったりCellsが早くなったりです。
どっちにしても体感できる値ではなさそうです。
2007や2010で大量のデータをクリアするときはどうか分からないですが。

【66672】Re:集計方法を教えてください
発言  kanabun  - 10/9/23(木) 17:13 -

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

>>理屈から言うとUsedRangeのほうが早そうですね。
>
>試す毎に値が変化します。
>UsedRangeが早くなったりCellsが早くなったりです。
>どっちにしても体感できる値ではなさそうです。

Yukiさん、検証ご足労かけます
ごめんなさい、どうチェックしたらよいか分からなくなりました...

実は、つい最近
>   With Worksheets("Sheet2")
>     .Cells.ClearContents
と同じようなコードを検証していて
ステップ実行していったら、
>     .Cells.ClearContents
の行で体感的にとても時間がかかってたので、今回の発言と
なりました。

で、こちらで以下のようなコードでテストしてみたところ
'---------------------------------------------
Declare Function timeGetTime Lib "winmm.dll" () As Long

Sub myTest1() 'Cells プロパティ
 Dim t&
 '準備
 Sheets(2).Cells.Copy Sheets(1).Cells(1)
 
 t = timeGetTime()
 Sheets(1).Cells.ClearContents
 Debug.Print "'cells"; timeGetTime() - t
 
 Sheets(1).UsedRange.Clear
End Sub

Sub myTest2()  'UsedRange プロパティ
 Dim t&
 '準備
 Sheets(2).Cells.Copy Sheets(1).Cells(1)
 
 t = timeGetTime()
 Sheets(1).UsedRange.ClearContents
 Debug.Print "'Used"; timeGetTime() - t
 
 Sheets(1).UsedRange.Clear
End Sub

結果は
'Used 15
'cells 15
'Used 16
'cells 16

'cells 0
'Used 0
'cells 0
'Used 0
というものでして、まるで差が出ませんでした。
きっとテストのしかたが悪いのだと思います。
それとも【ここが重要なところですが】
ClearContentsメソッドには Cellsプロパティで シートの
全範囲を指定しても、自動で UsedRangeに範囲限定する機能が
備わってるのでしょうか?

クリアでなく、【値Copy】のときには Cellsと UsedRangeは
たしかにちがいます。
他の掲示板で 某回答者の発言ですが、
---------------------------------- <引用>
> Cells.Value = Cells.Value

最近のPCだと余裕でできるんですかね?
当方のへっぽこPCだと、あっさりメモリ不足になりますけど。
最近のPCだとExcelで使用できるメモリ量も多いのでしょうか?

65,536×256=16,777,216個の要素を持つVariant型の二次元配列を
取得しようとする処理(右辺の評価)で、当方ではコケます。
コケなくても、相当にしんどい処理になると思いますので、
UsedRangeとかで範囲を絞った方がよろしいかと。

個人的には、コピー→値貼り付け→CutCopyMode = True/False
の方が、高速ですし、間違った処理(データ型変換)もしませんし、
接頭辞(')を落としてしまうこともないですし、データの
文字数制限(2003だと1データ911文字まで)もありませんので、
そちらをおすすめします。
------------------------------------ </引用>

この事例と ClearContents のときとは、やはり違うんですかね

ご無礼 しました。

【66678】Re:集計方法を教えてください
発言  kanabun  - 10/9/24(金) 9:30 -

引用なし
パスワード
   ▼コロネ さん:

シートの値クリアのことばかり考えてましたが、本題の表の
「統合」については
かみちゃんさんが触れられている「連想配列」をつかうと
レコードの順番が整列してなくても統合することができます。
VBAでは連想配列はVBSのDictionaryオブジェクトを使います。
外部モジュールなので、最初に
VBEメニュ−の[ツール]-[参照設定]のリストから
Microsoft Scripting Runtime にチェックを入れておいてください

Sub Try1()
 Dim i As Long
 Dim r As Range
 Dim v As Variant
 Dim n1 As Long, n2 As Long
 Dim n As Long, m As Long
 Dim dic1 As Dictionary
 Dim dic2 As Dictionary
 
 Set dic1 = New Dictionary
 Set dic2 = New Dictionary
 Set r = Worksheets(1).[A1].CurrentRegion '---(1)
 v = Intersect(r, r.Offset(1)).Value    '---(2)
 For i = 1 To UBound(v)
   If Not dic1.Exists(v(i, 1)) Then   '---(3)
     n1 = n1 + 1
     dic1(v(i, 1)) = n1
   End If
   If Not dic2.Exists(v(i, 3)) Then   '---(4)
     n2 = n2 + 1
     dic2(v(i, 3)) = n2
   End If
 Next
 ReDim tbl(dic1.Count, dic2.Count + 1)   '---(5)
 For i = 1 To dic1.Count          '---(6)
   tbl(i, 0) = dic1.Keys()(i - 1)
   tbl(i, 1) = "計"
 Next
 For i = 1 To dic2.Count          '---(7)
   tbl(0, i + 1) = dic2.Keys()(i - 1)
 Next
 For i = 1 To UBound(v)          '---(8)
   n = dic1(v(i, 1))
   m = dic2(v(i, 3)) + 1
   tbl(n, m) = tbl(n, m) + v(i, 4)
 Next
 Set dic1 = Nothing            '---(9)
 Set dic2 = Nothing
 
 With Worksheets(2)            '---(10)
   .UsedRange.ClearContents
   .[A1].Resize(UBound(tbl) + 1, _
       UBound(tbl, 2) + 1).Value = tbl
 End With
End Sub

'---(1) シート1の表領域を変数r に代入します
'---(2) 表領域から一行目を削除したセル範囲の「値」を
    変数v (配列)にコピーします
'---(3) 辞書dic1を使い、一意な「大分類」項目名を取得
'---(4) 辞書dic2を使い、一意な「小分類」項目名を取得
'---(5) 出力用配列tblを準備します (行:大分類、列:小分類項目数+1)
'---(6) tblに 行見出しを書き込みます
'---(7) tblに 列見出しを書き込みます

'---(8) 元表(配列v)の各行の「値」を tbl配列の n行、m列
    の要素位置に加算していきます。
    dic1("あああ") と問い合わせると n = 1 が得られます
    dic2("XXX") + 1 と問い合わせると m = 2 が得られます

'---(9) 使った辞書への参照を解除します
'---(10)最後に 集計の終わった出力用配列を
    シートの所定位置に貼り付けます

【66681】Re:集計方法を教えてください
お礼  コロネ  - 10/9/24(金) 22:08 -

引用なし
パスワード
   皆様

コロネです。
回答いただきまして、本当にありがとうございます。
想像していたよりもコードが複雑ですね。
1つずつ理解して頑張りたいと思います。
どうもありがとうございました。

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