Excel VBA質問箱 IV

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

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


6762 / 13644 ツリー ←次へ | 前へ→

【43298】Dictionaryについて TOSHIKI 06/10/8(日) 17:54 質問[未読]
【43304】Re:Dictionaryについて neptune 06/10/8(日) 19:57 発言[未読]
【43307】Re:Dictionaryについて TOSHIKI 06/10/8(日) 20:41 発言[未読]
【43312】Re:Dictionaryについて neptune 06/10/8(日) 21:54 回答[未読]
【43313】Re:Dictionaryについて neptune 06/10/8(日) 22:02 発言[未読]
【43316】Dictionaryを使った一例です? Hirofumi 06/10/9(月) 10:48 回答[未読]
【43321】Re:Dictionaryを使った一例です? TOSHIKI 06/10/9(月) 16:24 質問[未読]
【43323】Re:Dictionaryを使った一例です? Hirofumi 06/10/9(月) 17:18 回答[未読]
【43324】Re:Dictionaryを使った一例です? Hirofumi 06/10/9(月) 17:21 回答[未読]
【43326】Re:Dictionaryを使った一例です? Hirofumi 06/10/9(月) 17:33 回答[未読]
【43325】Re:Dictionaryを使った一例です? TOSHIKI 06/10/9(月) 17:31 お礼[未読]

【43298】Dictionaryについて
質問  TOSHIKI  - 06/10/8(日) 17:54 -

引用なし
パスワード
   Dictionaryの使い方を勉強中ですが、汎用性を持たすために
ユーザーフォームを利用してコンボボックスから、キー列(キー項目)・
集計列は最高3つ、集計(合計)列1・集計(合計)列2・集計(合計)列3を
考えていますがうまくいきません。過去の質問箱では、定型の形式
の場合であり、Offsetで指定していたり、集計列が多い場合はArray関数
の使用でしたが、考えてもうまくいく方法を思いつきません。
汎用性を持たすことは無理なのでしょうか。

ご存知の方は教えていただけませんか。

【43304】Re:Dictionaryについて
発言  neptune  - 06/10/8(日) 19:57 -

引用なし
パスワード
   ▼TOSHIKI さん:
>汎用性を持たすことは無理なのでしょうか。
>ご存知の方は教えていただけませんか。
何をして汎用性というのでしょう?

MSDNより引用
Dictionary オブジェクト
このオブジェクトは、メモリ内の名前と値の組み合わせの作成、格納、取得を
サポートするために使います。Dictionary オブジェクト内の値はすべて
Variant です。これは、大概の値から Dictionary オブジェクトを作成でき
(ほかの Dictionary オブジェクトを含む)、どんな組み合わせの Variant
でも同じ Dictionary オブジェクトに格納できることを意味します。

ですから、使い方次第と思いますが・・Dictionary(辞書)というぐらい
ですからどんなキーとどんなデータを結び付けて辞書に入れるかと言う整理
の問題と思います。

【43307】Re:Dictionaryについて
発言  TOSHIKI  - 06/10/8(日) 20:41 -

引用なし
パスワード
   やりたい事は、
  A   B     C   D  E   F
取引先名 商品分類 商品名  数量 単価 合計金額
(株)A   家電  パソコン 100 99800 9980000

ユーザーフォームのコンボボックスで集計基準の項目列、集計列1、集計列2・・
と選択。
例えば、コンボボックスで基準列を「取引先名」選択し、合計列に「合計金額」
とか、基準列を「商品名」に合計列を「数量」と「合計金額」列を指定できる
というふうにDictionaryを使用してできないかということなのですが。

【43312】Re:Dictionaryについて
回答  neptune  - 06/10/8(日) 21:54 -

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

>例えば、コンボボックスで基準列を「取引先名」選択し、合計列に「合計金額」
>とか、基準列を「商品名」に合計列を「数量」と「合計金額」列を指定できる
>というふうにDictionaryを使用してできないかということなのですが。
理解しました。

どれくらいのデータ量なんでしょうか?
こういうデータの場合は、レコードの形式を取っていますから、
数千〜数万ならユーザー定義型の配列を使用することをお勧めします。
DBとして扱う場合、ユーザー定義型をレコードセットのデータの格納場所
として一般的に使用されています。
こんな感じ
type Data
  取引先名 as string
  商品分類 as string
   ・
   ・
end type

どうしても辞書なら、項目列、集計列1でそれぞれDictionaryを作成しては
どうですか?
Dictionaryは辞書ですから、1つのキーワードに対し、1つのデータしか
格納できません。
そのデータはユーザー定義型も、Dictionaryも格納できるのではないでしょうか?
MSDNからはそう読み取れます。
#私はDictionaryの前身のCollectionを使用することが多いので↑は未確認です。
CollectionではOKです。Dictionaryではやったことはないです。

もう一つ。恐らくデータ量が多くなれば、自前の集計より、
Excelの集計機能を使用する方が処理速度は速いと思います。

【43313】Re:Dictionaryについて
発言  neptune  - 06/10/8(日) 22:02 -

引用なし
パスワード
   >(ほかの Dictionary オブジェクトを含む)、
って書いてますね。

【43316】Dictionaryを使った一例です?
回答  Hirofumi  - 06/10/9(月) 10:48 -

引用なし
パスワード
   Dictionaryを使った一例です?
UserFormに以下のコントロールが配置されています
  ComboBox1〜4
  CommandButton1

以下のコードをUserFormのコードモジュールに記述して下さい

Option Explicit

'UserFormモジュール内の何処からでも参照出来る定数、変数を宣言

'Listの列数(A列〜F列)
Const clngColumns As Long = 6
'集計列の先頭位置(基準位置rngListから「数量」の列Offset)
Const clngSum As Long = 3

'Listの基準セル(「取引先名」の位置)
Private rngList As Range
'Listの行数
Private lngRows As Long
'出力先の基準セル(列見出し先頭の位置)
Private rngResult As Range
'集計項目名、位置、表示の配列(表示Table用配列)
Private vntList As Variant

Private Sub UserForm_Activate()

  If lngRows = 0 Then
    MsgBox "データが有りません", vbExclamation
    Unload Me
  End If
  
End Sub

Private Sub UserForm_Initialize()

  Dim i As Long
  Dim vntData As Variant
  Dim vntItems As Variant
  Dim vntPos As Variant
  
  'Listの基準セルを設定(「取引先名」の位置)
  Set rngList = Worksheets("Sheet1").Cells(1, "A")
  
  '出力先の基準セルを設定(列見出し先頭の位置)
  Set rngResult = Worksheets("Sheet2").Cells(1, "A")
  
  'Listの列数取得
  With rngList
    lngRows = .Offset(Rows.Count - .Row).End(xlUp).Row - .Row
    If lngRows < 1 Then
      lngRows = 0
    End If
    '列見出しを取得
    vntData = .Resize(, clngColumns).Value
  End With
  
  'ComboBox1の設定
  '表示する列の位置を設定(基準セルからの列Offset)
  vntPos = Array(0, 1, 2)
  'ItemListを作成
  ReDim vntItems(UBound(vntPos), 1)
  For i = 0 To UBound(vntPos)
    vntItems(i, 0) = vntData(1, vntPos(i) + 1)
    vntItems(i, 1) = vntPos(i)
  Next i
  With ComboBox1
    'Itemを設定
    .List = vntItems
    '表示列を1列に
    .ColumnCount = 1
  End With
  
  'ComboBox1以外の設定
  '表示する列の位置を設定(clngSumからの列Offset)
  vntPos = Array(0, 1, 2)
  '表示Table用配列を作成
  ReDim vntList(1 To UBound(vntPos) + 1, 1 To 3)
  For i = 1 To UBound(vntList, 1)
    '列見だしを代入
    vntList(i, 1) = vntData(1, vntPos(i - 1) + clngSum + 1)
    '配列の2列目に-1を代入
    vntList(i, 2) = -1
    '配列の3列目に列位置を代入
    vntList(i, 3) = vntPos(i - 1)
  Next i
  
End Sub

Private Sub UserForm_Terminate()

  Set rngList = Nothing
  Set rngResult = Nothing
  
End Sub

Private Sub CommandButton1_Click()

'  集計

  Dim i As Long
  Dim j As Long
  Dim lngIndex As Long
  Dim dicIndex As Object
  Dim vntKeys As Variant
  Dim vntData As Variant
  Dim lngPos() As Long
  Dim vntResult As Variant
  Dim vntHeader As Variant
  
  'ComboBox1の値が選択されていない場合
  ReDim lngPos(0)
  With ComboBox1
    If .ListIndex = -1 Then
      'Subを抜ける
      GoTo Wayout
    Else
      'Keyの列位置を取得
      lngPos(0) = Val(.List(.ListIndex, 1))
    End If
  End With
  
  'ComboBox1以外の値が1つも選択されていない場合
  For i = 2 To 4
    With Controls("ComboBox" & i)
      If .ListIndex > -1 Then
        '集計列位置を取得
        j = j + 1
        ReDim Preserve lngPos(j)
        lngPos(j) = Val(.Tag)
      End If
    End With
  Next i
  'ComboBox1以外の値が1つも選択されていない場合
  If UBound(lngPos) = 0 Then
    'Subを抜ける
    GoTo Wayout
  End If
  
  '結果シートをクリア
  rngResult.Parent.Cells.Clear
  
  '結果出力用配列、データ用配列を確保
  ReDim vntResult(lngRows, UBound(lngPos)), _
      vntData(1 To UBound(lngPos))
  
  'Key及び集計データを取得
  With rngList
    'Keyデータを配列に取得
    vntKeys = .Offset(1, lngPos(0)).Resize(lngRows + 1).Value
    '選択された集計列の集計値を配列に取得
    For i = 1 To UBound(lngPos)
      vntData(i) = .Offset(1, clngSum _
              + lngPos(i)).Resize(lngRows + 1).Value
    Next i
  End With
  
  '列見出しを結果出力用配列に代入
  vntResult(0, 0) = ComboBox1.Text
  j = 0
  For i = 2 To 4
    With Controls("ComboBox" & i)
      If .ListIndex > -1 Then
        j = j + 1
        vntResult(0, j) = .Text
      End If
    End With
  Next i
  
  'Dictionaryオブジェクトを取得
  Set dicIndex = CreateObject("Scripting.Dictionary")
  
  '集計
  With dicIndex
    For i = 1 To lngRows
      'DictionaryにKeyが有った場合
      If .Exists(vntKeys(i, 1)) Then
        '結果配列に集計値を加算
        For j = 1 To UBound(lngPos)
          vntResult(.Item(vntKeys(i, 1)), j) _
              = vntResult(.Item(vntKeys(i, 1)), j) _
                  + vntData(j)(i, 1)
        Next j
      Else
        'DictionaryにKeyと結果配列に列位置を登録
        lngIndex = lngIndex + 1
        .Item(vntKeys(i, 1)) = lngIndex
        '結果配列にKeyと集計値を代入
        vntResult(lngIndex, 0) = vntKeys(i, 1)
        For j = 1 To UBound(lngPos)
          vntResult(lngIndex, j) = vntData(j)(i, 1)
        Next j
      End If
    Next i
  End With
  
  '結果を出力
  rngResult.Resize(lngIndex + 1, _
      UBound(lngPos) + 1).Value = vntResult
  
Wayout:
  
  Set dicIndex = Nothing
  
End Sub

Private Sub ComboBox2_Enter()

  SetCombList ComboBox2
  
End Sub

Private Sub ComboBox2_Exit(ByVal Cancel As MSForms.ReturnBoolean)

  ItemSelecte ComboBox2
  
End Sub

Private Sub ComboBox3_Enter()

  SetCombList ComboBox3
  
End Sub

Private Sub ComboBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)

  ItemSelecte ComboBox3
  
End Sub

Private Sub ComboBox4_Enter()

  SetCombList ComboBox4
  
End Sub

Private Sub ComboBox4_Exit(ByVal Cancel As MSForms.ReturnBoolean)

  ItemSelecte ComboBox4
  
End Sub

Private Sub SetCombList(cmbControl As MSForms.ComboBox)

'  ComboBoxのListを設定

  Dim i As Long
  Dim j As Long
  Dim strItem As String
  
  'ComboBoxに就いて
  With cmbControl
    '現在のValueを保存
    strItem = .Value
    'クリア
    .Clear
    '配列の行全てを繰り返す
    For i = 1 To UBound(vntList, 1)
      'もし、配列の2列目が0若しくは、自分の番号なら
      If vntList(i, 2) = -1 _
          Or vntList(i, 2) = .TabIndex Then
        'Listに配列に1列目を設定
        .AddItem vntList(i, 1)
        'もし、配列の2列目に自分の番号が有ったら
        If vntList(i, 2) = .TabIndex Then
          'その番号を0にする
          vntList(i, 2) = -1
        End If
      End If
    Next i
    .Value = strItem
  End With
  
End Sub

Private Sub ItemSelecte(cmbControl As MSForms.ComboBox)
 
' 選択されたItmeを配列に登録

  Dim i As Long
  
  'ComboBoxに就いて
  With cmbControl
    '選択されたItemを配列に登録
    If .ListIndex > -1 Then
      '配列の行全てを繰り返す
      For i = 1 To UBound(vntList, 1)
        'もし、配列の1列目に選択された値が有ったら
        If vntList(i, 1) = .List(.ListIndex, 0) Then
          'その値の番号をTabIndexの値にする
          vntList(i, 2) = .TabIndex
          'Tagに列位置を登録
          .Tag = vntList(i, 3)
          'Forを抜ける
          Exit For
        End If
      Next i
    Else
      'Tagをクリア
      .Tag = Empty
    End If
  End With
  
End Sub

【43321】Re:Dictionaryを使った一例です?
質問  TOSHIKI  - 06/10/9(月) 16:24 -

引用なし
パスワード
   ▼Hirofumi さん:
返事が遅くなってすいません。
ComboBox2以降のリストが表示されません。
このため、集計には項目なしで 「0」になってしまいます。

【43323】Re:Dictionaryを使った一例です?
回答  Hirofumi  - 06/10/9(月) 17:18 -

引用なし
パスワード
   >ComboBox2以降のリストが表示されません。
>このため、集計には項目なしで 「0」になってしまいます。

変ですね?
一応、簡単なTestはしていますので?
コードを変更していなければ、此方では動いていますが?

エラーも何も出ないのですか?
Listの位置は、シート名も含めて合っていますか?
  Set rngList = Worksheets("Sheet1").Cells(1, "A")

Listの列数(A列〜F列)は、合っていますか?
Const clngColumns As Long = 6

集計列の先頭位置(基準位置rngListから「数量」の列Offset)は、合っていますか?
Const clngSum As Long = 3

ComboBox1の設定で「表示する列の位置を設定(基準セルからの列Offset)」は合っていますか?
  vntPos = Array(0, 1, 2)
ComboBox1以外の設定で「表示する列の位置を設定(clngSumからの列Offset)」は合っていますか?
  vntPos = Array(0, 1, 2)

Listに列見出しは有りますか?
コードの変更をしていませんか?

【43324】Re:Dictionaryを使った一例です?
回答  Hirofumi  - 06/10/9(月) 17:21 -

引用なし
パスワード
   後、Listに列見出しを含めて、結合セルが有った場合上手く動きませんよ?
(Offsetが上手く行かない為)

【43325】Re:Dictionaryを使った一例です?
お礼  TOSHIKI  - 06/10/9(月) 17:31 -

引用なし
パスワード
   ▼Hirofumi さん:
失礼しました。テスト用にとデータをコピーしたときに
A列からC列の3列しかコピーしていませんでした。
A列からF列までコピーすれば言われるとおりになりました。
それにしても、すごいですね。このようなコードは、まだまだ
書けそうにありません。
他の人の質問に答えられるようにもっと頑張ります。
ありがとうございました。

【43326】Re:Dictionaryを使った一例です?
回答  Hirofumi  - 06/10/9(月) 17:33 -

引用なし
パスワード
   怪しいのは、以下の部分の位置を間違えていませんか?

  'ComboBox1以外の設定
  '表示する列の位置を設定(clngSumからの列Offset)
  vntPos = Array(0, 1, 2)

ComboBox2〜4の設定で、vntPosに設定する値は、「数量」の位置を基準としていますので、
「数量」列が0、「単価」列が1、「合計金額」列が2と成ります

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