Excel (VBA)

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

 
(指定なし : 指定なし)
配列を使って合計の出し方について
投稿日時: 19/01/02 04:08:49
投稿者: R2D2

配列を使ってデータの合計を出す方法を試しているのですが、うまくいかず質問させて頂きました。
 
ExcelのA1セルからA100セルまでyyyy/mm/dd hh:mm:ss形式で日付と時刻が記入されています。
下記のコードでA1セルからデータを1回ずつ配列に入れて、B1セルへ書き出す事はできました。
これを、月ごとに件数を集計して任意(例えばB1セル)のセルに書き出すには、どの様にすれば良いのでしょうか?
 
    A列        B列(結果表示)
2018/1/1 10:10:10   2018/1月 3件
2018/1/2 10:10:10   2018/2月 3件
2018/1/3 10:10:10
2018/2/1 10:10:10
2018/2/2 10:10:10
2018/2/3 10:10:10
 
'コードここから
Private Sub CommandButton1_Click()
    Dim ary() As Variant
    Dim length As Date
    Dim i As Long
 
' ReDim ary(0 To 50000, 0 To 2)
  
   'アクティブシートの、A6からデータを読み込む
  With ActiveSheet
    length = Intersect(.UsedRange, .Range("A1")).Value
  End With
   
    '配列の要素数を確定
    ReDim ary(length)
     
    '「配列の要素数」回、繰り返す
    For i = 0 To length
     
        '配列にA6からの値を格納
        ary(i) = Cells(i + 1, 1)
         
        '配列に格納した値をB1から書き込む
        Cells(i + 1, 2) = ary(i)
     
    Next i
 
End Sub

回答
投稿日時: 19/01/02 06:56:24
投稿者: simple

こうした集計をコードで実現しようとした場合、
dictionary(連想配列ともHashとも言われます)というデータ構造を使うと便利です。
「キー」と「値」の組(ペア)を 保有する仕組みです。
 
コードの一例です。

Sub test()
    Dim dic As Object
    Dim d   As Date
    Dim ym  As String
    Dim k   As Long
    
    Set dic = CreateObject("Scripting.Dictionary")
    
    For k = 1 To Cells(Rows.Count, "A").End(xlUp).Row
        d = Cells(k, "A").Value
        ym = year(d) & "/" & month(d) '年/月 をキーとして、
        dic(ym) = dic(ym) + 1         '出現回数をItem(値)に集計
    Next
    
    '結果を書き出し
    Range("B1").Resize(dic.Count, 1) = Application.Transpose(dic.keys)
    Range("C1").Resize(dic.Count, 1) = Application.Transpose(dic.items)
End Sub

 
ネット上で、"VBA Dictionary"などと検索して調べてみて下さい。
 
========================
以下の別法もあります。こちらからトライされたほうがよいと思います。
・既に指摘がありましたように、「注文月」のダミー列を作り、
・重複を除いた注文月の一覧を B列に作成(「重複の削除」機能もしくはフィルタオプションを利用)
・CountIf関数で、注文月別の個数を C列 に 集計します。
こうした作業を自動で行ってくれるのがピボットテーブルなんですが・・・・。
 
========================
提示されたコードについてのコメント。
    Dim length As Date ネーミングが変ですし、
    length = Intersect(.UsedRange, .Range("A1")).Value の意図がわかりません。
    A1セルの内容(日付)がなぜ lengthなのか。
 
    また、lengthには、A1セルの日付(実質は43101.4237268519といった実数)がセットされ、
    ReDim ary(length) は 43101を要素の上限とする配列となります。
    意図がわかりません

投稿日時: 19/01/03 01:16:00
投稿者: R2D2

simpleさん
 
早々の回答ありがとうございます。
集計したいとおりの結果が表示されました。
 
>提示されたコードについてのコメント。
VBAを勉強し始めて日が浅い為、意味不明な内容となっておりました。
このあたりについても、少しづつ理解を深めてまいります。
 
下記の値を書き込む部分で質問させてください。
Range("B1").Resize(dic.Count, 1)、のところ
調べたところ、Range.Resize(RowSize, ColumnSize)とあるのですが、
(dic.Count, 1)は、どの様な処理をしているのでしょうか?
 
 '結果を書き出し
    Range("B1").Resize(dic.Count, 1) = Application.Transpose(dic.keys)
    Range("C1").Resize(dic.Count, 1) = Application.Transpose(dic.items)

回答
投稿日時: 19/01/03 01:45:27
投稿者: simple

上記の例では、dicという dictionaryは

  key         item
"2018/1"   ->  3
"2018/2"   ->  3
という2つのデータ(ペア)から成っています。
 
dic.Countはdictionaryの要素の数、この場合 2 を返します。
 
したがって、
Range("B1").Resize(dic.Count, 1)は
Range("B1").Resize(2, 1) としているのと同じです。
 
B1セルを起点として、2行、1列のセル範囲に、
Application.Transpose(dic.keys)つまり、dictionaryのキーから成る配列を縦にしたもの
をセットしています。

投稿日時: 19/01/04 07:09:27
投稿者: R2D2

simpleさん
 
詳しい説明ありがとうございます。
簡単には使いこなせませんが、教えて頂いた内容を応用して、
いろいろと試してみます。
 
今後も宜しくお願いします。