補講6-連想配列演習-取引先ごとに取引金額の合計を算出する

この講座は有料講座です。
講座の購入後ご覧になれます。

ログインアカウントの新規作成

解説

ここまでに学んだ内容のまとめして、連想配列の演習として、取引先ごとに取引金額の合計を算出する事例を紹介します。
イチからマクロを書いていく様子をお見せしつつ解説します。

基礎編では、そのような作業をするには、集計対象の取引先の名称のリストを事前に作成する必要がありました。
発展編1では、そのような作業をするには、並べ替えが必要でした。
連想配列で処理する場合は、事前調査も並べ替え作業も不要です。

いつもどおり、基礎編で学んだ「ハナコのステップ」に従って、順に問題解決していきます。
動画での作業の流れに従って何度か練習してみてください。

この教材についての過去の質問・感想

12615 : 小川慶一の回答 (2020-11-15 09:58:18)

田中 宏明さん、たかちゃんさん:

Advanced Filterについて公式ドキュメントを見てみました。

Advanced Filter メソッド (Excel)
https://docs.microsoft.com/ja-jp/office/vba/api/excel.range.advancedfilter

が、特にタイトル行についての記載はみつかりませんでした。

stackoverflowで調べてみると、「filter機能系のものはタイトルを含んでしまうけど、コピー完了してからコピー先の先頭セルを削除するてやりかたでどうだい?」みたいな回答をわずかにみつけるばかり。
https://stackoverflow.com/questions/31775564/advanced-filter-exclude-headers

まあ、タイトル行がもともと存在しないリストでAdvanced Filterを使いたい場合にはそういう感じにするんでしょうか。
あとは、僕なら、先頭行は、削除する代わりに、タイトル文字列を .Value で埋め込みたいところ。


12614 : 田中 宏明さんのコメント (2020-11-15 09:55:50)

たかちゃんさん:

AdvancedFilter この演習でリベンジしました。
実務では、素早い対応が必要とされることが大半なので、小川先生が最初に回答されたこのやり方が最良かもしれませんね。
たかちゃんさんとのやりとりを通じ、新しい手法を習得できました。

Sub GetSumAll_AdvancedFilter() 'Modified by 達人養成塾 Hiroaki Tanaka
    Dim rgBase As Range
    Set rgBase = Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row)
    '会社のリスト書き出し(タイトル行からの指定がポイント)
    rgBase.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("I1"), Unique:=True

    Dim rg As Range
    'rgBaseの範囲がタイトル行からなので、G列の指定も1行目からする
    For Each rg In Range("I2:I" & Range("I" & Rows.Count).End(xlUp).Row).Cells
        rg.Offset(, 1).Value = Application.WorksheetFunction.SumIf(rgBase, rg.Value, rgBase.Offset(, 5))
    Next
End Sub 


12607 : たかちゃんさんのコメント (2020-11-13 23:18:26)

田中さん:
さすが!今のDictionaryの理解がしっかりした頃に、Collectionについても再び調べて、こちらのコードを読みに再び戻ってきます!!!

> たかちゃんさん:
>
> 私もDictionaryとRangeオブジェクトを使わず、Collectionオブジェクトだけを使ってこの演習問題を解いてみました。実務ではこんな面倒なことはしないですが、頭の体操になりますね。結構、苦労しました。


12605 : 田中 宏明さんのコメント (2020-11-13 18:02:11)

たかちゃんさん:

私もDictionaryとRangeオブジェクトを使わず、Collectionオブジェクトだけを使ってこの演習問題を解いてみました。実務ではこんな面倒なことはしないですが、頭の体操になりますね。結構、苦労しました。

Sub GetSumAllCollection() 'Modified by 達人養成塾 Hiroaki Tanaka
    '以下では、すべてのお客さんについて、金額の合計を求める
    Worksheets("Sheet2").Activate
    
    'Collectionを2つ使ってDictionaryを模擬実現
    Dim colkey As Collection: Set colkey = New Collection 'Key
    Dim colitm As Collection: Set colitm = New Collection 'Item
    Dim st As String
    
    '取得系
    Dim cFm As Long, cNo As Long
    For cFm = 2 To 22
        st = Range("B" & cFm).Value
        If cFm > 2 Then
            cNo = keyExistsNumber(colkey, st)
            If cNo = 0 Then
                colkey.Add st
                colitm.Add New Collection
                colitm.Item(colkey.Count).Add cFm
            Else
                colitm.Item(cNo).Add cFm
            End If
        Else
            colkey.Add st
            colitm.Add New Collection
            colitm.Item(colkey.Count).Add cFm
        End If
    Next
    
    '出力系
    Dim col As Variant
    Dim cGokei As Long
    Dim cTo As Long
    cTo = 2
    For cFm = 1 To colkey.Count 'CollectionはIndexが1から始まる
        st = colkey.Item(cFm)
        cGokei = 0
        For Each col In colitm.Item(cFm)
            cGokei = cGokei + Range("G" & col)
        Next
        Range("I" & cTo).Value = st
        Range("J" & cTo).Value = cGokei
        cTo = cTo + 1
    Next
End Sub

'Collectionを検索し、見つかった文字列のItem番号を返す関数
Function keyExistsNumber(col As Collection, key As Variant) As Long
    Dim c As Long
    For c = 1 To col.Count 'CollectionはIndexが1から始まる
        If col.Item(c) = key Then
            keyExistsNumber = c
            Exit Function '見つかったら処理終了
        End If
    Next
    keyExistsNumber = 0   '見つからなかった場合
End Function 


> Dictionaryの代わりに、AdvancedFileter&Sumif使用で描いてみました。


12604 : 田中 宏明さんのコメント (2020-11-13 11:04:26)

たかちゃんさん:

私も AdvancedFilterを再度試してみました。
Windowsでもデータ範囲(B2から指定)だと、愛知販売が2つ出力されました。これはハマりますね。

> Dictionaryの代わりに、AdvancedFileter&Sumif使用で描いてみました。
> 記念に投稿します。(動作確認済み)
> 最初に、AdvancedFilterをB2から指定した為、愛知販売が2つ出力という謎の動きをしてしまいました。
> タイトル行から指定しないとダメです。
> 同様に、SumIfの合計値の範囲(G列)もG2から指定してしまうと、でたらめな金額が出力されしまい
> 気づくのにかなり悩みました。


12573 : たかちゃんさんのコメント (2020-11-04 01:13:16)

Dictionaryの代わりに、AdvancedFileter&Sumif使用で描いてみました。
記念に投稿します。(動作確認済み)
最初に、AdvancedFilterをB2から指定した為、愛知販売が2つ出力という謎の動きをしてしまいました。
タイトル行から指定しないとダメです。
同様に、SumIfの合計値の範囲(G列)もG2から指定してしまうと、でたらめな金額が出力されしまい
気づくのにかなり悩みました。
前回の動画コメント12563の先生の参考コードと見比べて、やっと気づきました。

■Chap02ー71 Sheet2の課題

Sub test()
    Dim rg As Range
    Set rg = Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row)
    '会社のリスト書き出し(タイトル行からの指定がポイント)
    rg.AdvancedFilter Action:=xlFilterCopy, copytorange:=Range("I1"), Unique:=True
    
    Dim sList As Range
    Set sList = Range("I2:I" & Range("I" & Rows.Count).End(xlUp).Row)
    Dim s As Range
    Dim c As Long
    c = 2
    'rgの範囲がタイトル行からなので、G列の指定も1行目からする
    For Each s In sList
        Range("J" & c).Value = WorksheetFunction.SumIf(rg, s.Value, Range("G1:G" & Range("G" & Rows.Count).End(xlUp).Row))
        c = c + 1
    Next
End Sub 


3日がかりのその仕事、3分で終わらせる方法教えます。ガラパゴスタディーオンライン講座 ユーザー登録

本講座の動画一覧

  1. 【動画1】 式と戻り値1-データ型のおさらい
    【動画1】 式と戻り値1-データ型のおさらい 未習得
  2. 【動画2】 式と戻り値2-「式」と「戻り値」
    【動画2】 式と戻り値2-「式」と「戻り値」 未習得
  3. 【動画3】 式と戻り値3-「式」と「戻り値のデータ型」
    【動画3】  式と戻り値3-「式」と「戻り値のデータ型」 未習得
  4. 【動画4】 式と戻り値4-「式」とは(その1)
    【動画4】 式と戻り値4-「式」とは(その1) 未習得
  5. 【動画5】 式と戻り値5-「式」とは(その2)
    【動画5】 式と戻り値5-「式」とは(その2) 未習得
  6. 【動画6】 式と戻り値6-戻り値を返す式、戻り値を返さない式
    【動画6】  式と戻り値6-戻り値を返す式、戻り値を返さない式 未習得
  7. 【動画7】 コレクションとインデックス
    【動画7】  コレクションとインデックス 未習得
  8. 【動画8】 配列
    【動画8】 配列 未習得
  9. 【動画9】 静的配列
    【動画9】 静的配列 未習得
  10. 【動画10】 動的配列1-動的配列の基本
    【動画10】 動的配列1-動的配列の基本 未習得
  11. 【動画11】 動的配列2-Preserveキーワード
    【動画11】 動的配列2-Preserveキーワード 未習得
  12. 【動画12】 動的配列3-演習
    【動画12】 動的配列3-演習 未習得
  13. 【動画13】 多次元配列の基本
    【動画13】 多次元配列の基本 未習得
  14. 【動画14】 多次元動的配列
    【動画14】 多次元動的配列 未習得
  15. 【動画15】 多次元配列のサイズを調べる
    【動画15】 多次元配列のサイズを調べる 未習得
  16. 【動画16】 配列操作の便利関数、オプション、その他
    【動画16】  配列操作の便利関数、オプション、その他 未習得
  17. 【動画17】 連想配列1-Dictionaryオブジェクトの仕様
    【動画17】 連想配列1-Dictionaryオブジェクトの仕様 未習得
  18. 【動画18】 連想配列2-Dictionaryオブジェクトの使用例
    【動画18】 連想配列2-Dictionaryオブジェクトの使用例 未習得
  19. 【動画19】 連想配列3-Microsoft Scripting Runtimeのへの参照設定
    【動画19】 連想配列3-Microsoft Scripting Runtimeのへの参照設定 未習得
  20. 【動画20】 「名前付き引数」と「名前なし引数」
    【動画20】 「名前付き引数」と「名前なし引数」 未習得
  21. 【動画21】 省略可能な引数とその初期値
    【動画21】 省略可能な引数とその初期値 未習得
  22. 【動画22】 引数のデータ型
    【動画22】 引数のデータ型 未習得
  23. 【動画23】 Functionプロシージャ
    【動画23】 Functionプロシージャ 未習得
  24. 【動画24】 引数がひとつまたは複数のFunctionプロシージャ
    【動画24】 引数がひとつまたは複数のFunctionプロシージャ 未習得
  25. 【動画25】 Functionプロシージャ演習中
    【動画25】 Functionプロシージャ演習中 未習得
  26. 【動画26】 引数として配列を受け取るFunctionプロシージャ
    【動画26】 引数として配列を受け取るFunctionプロシージャ 未習得
  27. 【動画27】 引数を受け取らないSubプロシージャと引数つきSubプロシージャ
    【動画27】 引数を受け取らないSubプロシージャと引数つきSubプロシージャ 未習得
  28. 【動画28】 Subプロシージャの基本構造
    【動画28】 Subプロシージャの基本構造 未習得
  29. 【動画29】 モジュールレベル変数との使い分け
    【動画29】 モジュールレベル変数との使い分け 未習得
  30. 【動画30】 補講1-ディクショナリーオブジェクトのさらなる活用
    【動画30】 補講1-ディクショナリーオブジェクトのさらなる活用 未習得
  31. 【動画31】 補講2-ディクショナリーオブジェクトの .Count プロパティ
    【動画31】 補講2-ディクショナリーオブジェクトの .Count プロパティ 未習得
  32. 【動画32】 補講3-連想配列によるオブジェクトへの参照設定 - 新しいキーに設定する場合
    【動画32】 補講3-連想配列によるオブジェクトへの参照設定 - 新しいキーに設定する場合 未習得
  33. 【動画33】 補講4-連想配列によるオブジェクトへの参照設定 - 既存の値を上書きする場合
    【動画33】 補講4-連想配列によるオブジェクトへの参照設定 - 既存の値を上書きする場合 未習得
  34. 【動画34】 補講5-複数セルの集合を作るための便利なメソッド2つ
    【動画34】 補講5-複数セルの集合を作るための便利なメソッド2つ 未習得
  35. 【動画35】 補講6-連想配列演習-取引先ごとに取引金額の合計を算出する
    【動画35】 補講6-連想配列演習-取引先ごとに取引金額の合計を算出する 未習得
  36. 【動画36】 補講7-ウォッチ式の活用
    【動画36】 補講7-ウォッチ式の活用 未習得
  37. 【動画37】 補講8-ウォッチ式で配列の状態を確認する
    【動画37】 補講8-ウォッチ式で配列の状態を確認する 未習得
  38. 【動画38】 補講9-たった1行のコードでセルのデータを配列に投入する
    【動画38】 補講9-たった1行のコードでセルのデータを配列に投入する 未習得

塾長 小川慶一

メニュー

コメント紹介

もっと見る

ページの先頭へ