レッスン-投稿一覧パソコン仕事５倍塾ポータル

投稿/コメントを表示します。

エクセルマクロ・VBA発展編２

たかちゃんさんの投稿

(投稿ID: 4918)

Mac版のエクセルで、ScriptingDictionaryが使えない為、何とか似た様な方法で解決できないか
ずっと考えていました。Collectionオブジェクトという、Dictionaryと似た様なものも調べてみたのですが
Existsメソッドもなく思った様には使えませんでした。（><）
とりあえず、配列を使って書いてみましたので、記念に投稿します。（正常動作確認済み）
Dictionaryが使えない場合、この様な動きのプログラムを書くにはどんな方法が良さそうでしょうか？

他にも、基礎編の「細巻き？」で勉強したFor文を使った方法でも書いてみましたが、名前でソートをする手間が省けるので、配列使った方が少しは早いのかなぁと思いました。

■Chap02-71 Sheet1の課題■

Sub test1()
    Dim sList() As String
    Dim mx As Long
    Dim c As Long
    '重複しないリストをC1に書き出し
    Range("A1:A273").AdvancedFilter Action:=xlFilterCopy, _
    CopyToRange:=Range("C1"), _
    Unique:=True
    
    '上記リストを配列に格納
    mx = Range("C" & Rows.Count).End(xlUp).Row
    For c = 1 To mx
        ReDim Preserve sList(1, c)
        sList(0, c - 1) = Range("C" & c).Value
    Next
    
    Dim rg As Range
    Dim cnt As Long
    '重複回数をカウントし、配列に格納
    For c = LBound(sList, 2) To UBound(sList, 2)
            For Each rg In Range("A1:A273")
            If sList(0, c) = rg.Value Then
                cnt = cnt + 1
                sList(1, c) = cnt
            End If
        Next
        cnt = 0
    Next
    
    '結果をD1に書き出し
    For c = LBound(sList, 2) To UBound(sList, 2)
        Range("D1").Offset(c).Value = sList(1, c)
    Next
    
End Sub

2020/11/02 11:07

小川慶一さんのコメント

(コメントID: 6884)

たかちゃんさん:

こんにちは。

外部ライブラリ由来の機能を使う場合、OSが異なることのデメリットがでますね。

> Dictionaryが使えない場合、この様な動きのプログラムを書くにはどんな方法が良さそうでしょうか？

試されたとおり、エクセルに一時集計用シートを用意してそこに書き出すのがいちばん簡単かなと思います。
AdvancedFilterを使うのは良い発想だと思います。

Scripting.Dictionary の機能を紹介するために作ったサンプルです。
Scripting.Dictionary を使うという制約をはずし、「なんでも良いからとにかくシンプル高速に」ということであれば、以下ですかね。

Sub CountAll()
    Dim rgBase As Range
    Set rgBase = Range("A1:A" & Range("A" & Rows.Count).End(xlUp).Row)
    rgBase.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("F1"), Unique:=True
    
    Dim rg As Range
    For Each rg In Range("F1:F" & Range("F" & Rows.Count).End(xlUp).Row)
        rg.Offset(, 1).Value = WorksheetFunction.CountIf(rgBase, rg.Value)
    Next
End Sub

2020/11/03 01:09

田中宏明さんのコメント

(コメントID: 6886)

たかちゃんさん:
私はDictionary大好き受講生です。
AdvancedFilterを使えば、Dictionaryを使わなくても簡単に重複のないリストを作成できるのですね。
とても参考になりました。

> Mac版のエクセルで、ScriptingDictionaryが使えない為、何とか似た様な方法で解決できないか
> ずっと考えていました。Collectionオブジェクトという、Dictionaryと似た様なものも調べてみたのですが
> Existsメソッドもなく思った様には使えませんでした。（><）
> とりあえず、配列を使って書いてみましたので、記念に投稿します。（正常動作確認済み）

2020/11/03 06:36

たかちゃんさんのコメント

(コメントID: 6887)

小川先生、田中さん

おはようございます。
毎回、ありがとうございます。
For eachとCountif 思いつきそうで、思いつきませんでした。（^^;
Advanced Filterは、エクセルの機能で普通にありますが、実は今回調べるまで知りませんでした。vbaと同時にエクセルの機能ももっと勉強しないとなぁと思いました。Mac版でも、恐らく誰かが作った？.clsという拡張子のファイルをクラスモジュールにインポーとして、さらに何かをするとDictionaryが使える様になるという情報がインターネットにありましたが、「さらに何かをする」の部分が全く理解できず断念しました。取り敢えず、工夫すれば他の方法でもなんとかできることが知れて安心しました。
他の受講生さんから声をかけて貰えると嬉しいです。ありがとうございます！(^^）

> たかちゃんさん:
> 私はDictionary大好き受講生です。
> AdvancedFilterを使えば、Dictionaryを使わなくても簡単に重複のないリストを作成できるのですね。
> とても参考になりました。
>

2020/11/03 07:42

小川慶一さんのコメント

(コメントID: 6888)

田中宏明さん:

「フィルターオプションの設定」ダイアログで、「重複するレコードは無視する」にチェックを入れて元の表とは違うところを出力先として指定してオートフィルターします。
この操作を自動記録しつつ行うと advancedfilter を使ったコードを得られます。

2020/11/03 13:53

小川慶一さんのコメント

(コメントID: 6889)

たかちゃんさん:

>さらに何かをするとDictionaryが使える様になるという情報がインターネットにありましたが

何なんでしょうね。

sumifもやるならたとえば以下の要領。
worksheetfunctionは引数にセル範囲を指定するときに最初戸惑うかもしれません。が、そこを乗り越えれば、ピボットテーブル的なクロス集計表もそんなに苦労せずに作れるようになります。

Sub count_and_sumif_all()
    Dim rgBase As Range
    Set rgBase = Range("B1:B" & Range("B" & Rows.Count).End(xlUp).Row)
    rgBase.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("K1"), Unique:=True
    
    Range("K1").Copy Destination:=Range("L1:M1")
    Range("L1").Value = "度数"
    Range("M1").Value = "合計"
     
    Dim rg As Range
    For Each rg In Range("K2:K" & Range("K" & Rows.Count).End(xlUp).Row)
        rg.Offset(, 1).Value = WorksheetFunction.CountIf(rgBase, rg.Value)
        rg.Offset(, 2).Value = WorksheetFunction.SumIf(rgBase, rg.Value, rgBase.Offset(, 5))
    Next
End Sub

2020/11/03 14:15

小川慶一さんのコメント

(コメントID: 6890)

たかちゃんさん:

今紹介したサンプルは、元データが「伝票作成マクロ」のシート「main」のものの場合の例です。

2020/11/03 14:17

たかちゃんさんのコメント

(コメントID: 6892)

小川慶一さん:
先生、ありがとうございます。
今日はまさにこの動きのプログラムを書こうとして試行錯誤していました。
頂いたプログラムと見比べて、何でつまづいていたのか検証しようと思います。このパターンは事務仕事で良く使いそうです。

昔、良くエクセルの小計？、集計？機能で大量のデータを集計していた時の事を思い出しました。結構、説明しても使い方が良く分からないという方が多かったのですが、vbaできたらマクロ作ってボタンを設置すれば誰でも使える様になりますね。(^^)

> たかちゃんさん:
>
> >さらに何かをするとDictionaryが使える様になるという情報がインターネットにありましたが
>
> 何なんでしょうね。
>
> sumifもやるならたとえば以下の要領。
> worksheetfunctionは引数にセル範囲を指定するときに最初戸惑うかもしれません。が、そこを乗り越えれば、ピボットテーブル的なクロス集計表もそんなに苦労せずに作れるようになります。
>

2020/11/03 19:05

小川慶一さんのコメント

(コメントID: 6893)

たかちゃんさん:

> 頂いたプログラムと見比べて、何でつまづいていたのか検証しようと思います。

公式サイトのドキュメントも参考にされると良いかと。

"worksheetfunction sumif vba" 等のキーワードで検索し、検索結果一覧から、 docs.microsoft.com のドメインのものを見つけます。
https://docs.microsoft.com/ja-jp/office/vba/api/excel.worksheetfunction.sumif

公式ドキュメントを読めるようになると、また格段に力がつきます。

2020/11/03 19:45

田中宏明さんのコメント

(コメントID: 6894)

たかちゃんさん:

Mac版のエクセルで、ScriptingDictionaryが使えないとのことで、Collectionオブジェクトでの代用を検証してみました。
重複のないリスト作成はできそうですが、CountやSumのような集計は無理だとわかりました。（Windowsで動作検証済みです。）

'Scripting.Dictionaryが使えない場合の代用検証
Sub DicSampleCollection()
    Dim dicC As Collection
    Set dicC = New Collection
    
    'Collectionへ要素の追加を行います
        'CollectionのAddメソッドはキーとアイテムが逆順
    dicC.Add 83, "小川"
    dicC.Add 75, "山田"
    dicC.Add 92, "田中"
    
    '要素数を数えます
    Debug.Print dicC.Count
    
    'キーから値を取り出します
    Debug.Print dicC.Item("小川")
    
    'キーの値に1を加算
    '【検証１】Collectionに入れたアイテムを変更できませんでした。
    '以下はエラー
    'dicC.Item("小川") = dicC.Item("小川") + 1

    '特定のキーが存在するか調べ、条件分岐します
    '【検証２】Existsは関数で代用可能
    If Not ExistsItem(dicC, "佐藤") Then
        'CollectionのAddメソッドはキーとアイテムが逆順
        dicC.Add 50, "佐藤"
    End If
    
    Dim ColItem As Variant
    
    'キーのリストを取得
    '【検証３】Collectionのキーを取得できませんでした。

    '値のリストを取得します
    '【検証４】Collectionのアイテムを取得できました。
    For Each ColItem In dicC
        Debug.Print ColItem
    Next
End Sub

'http://blog.livedoor.jp/midorityo/archives/51067902.html
Function ExistsItem(ByRef obj As Variant, ByVal index As Variant) As Boolean
    Dim dammy As Variant, rtn As Boolean
    On Error Resume Next
    Err.Clear
    If IsObject(obj) Then
        dammy = IsObject(obj.Item(index))
    Else
        dammy = obj(index)
    End If
    If Err.Number = 0 Then
        rtn = True
    Else
        rtn = False
        If Err.Number = 438 Then MsgBox ("このコレクションにItemプロパティが存在しないため、判別できません")
    End If
    ExistsItem = rtn
End Function

2020/11/03 20:18

田中宏明さんのコメント

(コメントID: 6895)

小川慶一さん:

助言ありがとうございます。やり方がわかりました。
Excel標準機能で重複のないリストが簡単にできるとは驚きです。

> 田中宏明さん:
>
> 「フィルターオプションの設定」ダイアログで、「重複するレコードは無視する」にチェックを入れて元の表とは違うところを出力先として指定してオートフィルターします。

2020/11/03 21:09

田中宏明さんのコメント

(コメントID: 6896)

たかちゃんさん:

その後、Mac版でDictionaryを使う方法を調べてみました。以下から Source code (zip) をダウンロードhttps://github.com/VBA-tools/VBA-Dictionary/releases/tag/v1.4.1

解凍された複数あるファイルのうち　Dictionary.cls　をVBEからインポートすると動きました。
（Dictionary.clsの条件付コンパイルを記述を変更し、Windows版エクセルで強制的にMac動作）

標準モジュールの変数宣言を以下のように変更します。

Dim dic As Dictionary 
Set dic = New Dictionary

お時間と興味があれば試してみてください。

2020/11/04 04:10

たかちゃんさんのコメント

(コメントID: 6897)

小川先生、田中さん
色々とありがとうございます。
私の方でも検証し、Collectionでは重複しないリストを作るところまではできましたので記念に投稿します。（動作確認済み）
カウントをしたい場合は、やはり先日教えて頂いたCountifなど別の方法でする必要があります。
Function部分のnamaeやsの変数は、For eachを使用する関係でStringではなくVariantになっています。一応動きますが、邪道な感じがしますね・・・。

■Chap02-71のSheet１課題
　C列に重複のない名前リストをCollection使用で作成した場合

Sub Sample()
    Dim dic As Collection
    Set dic = New Collection
    Dim c As Long
    Dim st As String
 
    For c = 1 To Range("A" & Rows.Count).End(xlUp).Row
        st = Range("A" & c).Value
        If c > 1 Then
            If isExists(dic, st) = False Then
                dic.Add st
            End If
        Else
            dic.Add st
        End If
    Next
    
    Debug.Print dic.Count
    
    For c = 1 To dic.Count
    Range("C" & c).Value = dic.item(c)
    Next
End Sub

Function isExists(dic As Collection, namae As Variant) As Boolean
    Dim s As Variant
    Dim hantei As Boolean
    hantei = False
    
    For Each s In dic
        If s = namae Then
            hantei = True
        End If
    Next
    isExists = hantei
End Function

> たかちゃんさん:
>
> Mac版のエクセルで、ScriptingDictionaryが使えないとのことで、Collectionオブジェクトでの代用を検証してみました。
> 重複のないリスト作成はできそうですが、CountやSumのような集計は無理だとわかりました。（Windowsで動作検証済みです。）

2020/11/04 07:23

田中宏明さんのコメント

(コメントID: 6898)

たかちゃんさん:

拝見しました。
基本に忠実なコードですね。実行しなくても検証されたこと、関数のエラー対策を理解できました。

> 小川先生、田中さん
> 色々とありがとうございます。
> 私の方でも検証し、Collectionでは重複しないリストを作るところまではできましたので記念に投稿

2020/11/04 08:00

たかちゃんさんのコメント

(コメントID: 6899)

田中さん:
色々検証して頂いてありがとうございました。
声かけて貰えなかったら、多分ここまで頑張れませんでした。
ご存知かもしレませんが、AdvancedFilter使用時はタイトル行から指定しないと、エラーになりませんが変な動きをしますのでお気をつけ下さい。今回の例はタイトルがなかったので気付きませんでした。コメント12563の小川先生が書かれたコードがとても参考になりました。
> たかちゃんさん:
>
> 拝見しました。
> 基本に忠実なコードですね。実行しなくても検証されたこと、関数のエラー対策を理解できました。

2020/11/04 10:18

田中宏明さんのコメント

(コメントID: 6901)

たかちゃんさん:

今日、会社の仕事で「重複のないリスト作成」の機会があり、さっそくAdvancedFilterを使ってみたところ、見事に失敗しました。

そこで、たまたま昨日復習した別の動画【10秒でできる！エクセルデータの「重複の削除」】のやり方で、速攻解決しました。
https://forum.pc5bai.com/lesson/page/1280

達人養成塾に入り、ここでやりとりさせていただき、本当に良かったです。

> ご存知かもしレませんが、AdvancedFilter使用時はタイトル行から指定しないと、エラーになりませんが変な動きをしますのでお気をつけ下さい。

2020/11/05 06:39

たかちゃんさんのコメント

(コメントID: 6902)

田中さん:

> 今日、会社の仕事で「重複のないリスト作成」の機会があり、さっそくAdvancedFilterを使ってみたところ、見事に失敗しました。

私も失敗して１日悩みました。。(^^;
以下の動画、今日初めてチェックしました。有益な情報ありがとうございます。この機能も初めて知りました。

> そこで、たまたま昨日復習した別の動画【10秒でできる！エクセルデータの「重複の削除」】のやり方で、速攻解決しました。
> https://forum.pc5bai.com/lesson/page/1280
>
> 達人養成塾に入り、ここでやりとりさせていただき、本当に良かったです。
>

2020/11/05 14:02

小川慶一さんのコメント

(コメントID: 6904)

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

おはようございます。
お二人とも素晴らしいです。僕の出る幕はもはやありません... (^^;

2020/11/07 14:00

たかちゃんさんのコメント

(コメントID: 6911)

田中さん、小川先生
以下、田中さんから教えて頂いた方法で、簡単なDictionaryを使ったプログラムを動かした所、動きました！
【手順】
1.Dictionary.clsをモジュールのフォルダから右クリックでインポート。
（するとクラスモジュールというフォルダが作られてその中にDictionary.clsが入ります。）
2.その状態で、Dictionaryのプログラムを書いて、宣言の所に教えて頂いた通りに変更。
Dicの所を通過する度に、Dictionary.cls内の処理を行うような感じで、動きました。とりあえず、これでDictionaryの勉強を再開してみようと思います。
ありがとうございました。(^^)

Sub test()
    Dim dic As Dictionary
    Set dic = New Dictionary
    dic.Add "小川", 83
    dic.Add "山田", 75
    dic.Add "田中", 92
    Debug.Print dic.Count
    
    Debug.Print dic.Item("小川")
    
    If Not dic.Exists("佐藤") Then
        dic.Add "佐藤", 50
    End If
    
    Debug.Print dic.Count
End Sub

> たかちゃんさん:
>
> その後、Mac版でDictionaryを使う方法を調べてみました。以下から Source code (zip) をダウンロードhttps://github.com/VBA-tools/VBA-Dictionary/releases/tag/v1.4.1
>
> 解凍された複数あるファイルのうち　Dictionary.cls　をVBEからインポートすると動きました。
> （Dictionary.clsの条件付コンパイルを記述を変更し、Windows版エクセルで強制的にMac動作）
>
> 標準モジュールの変数宣言を以下のように変更します。
>

Dim dic As Dictionary 
> Set dic = New Dictionary
>

>
> お時間と興味があれば試してみてください。

2020/11/11 06:49

田中宏明さんのコメント

(コメントID: 6912)

たかちゃんさん:

良かったです。
努力すれば道が開けるとはこのことですね。

> 田中さん、小川先生
> 以下、田中さんから教えて頂いた方法で、簡単なDictionaryを使ったプログラムを動かした所、動きました！

2020/11/11 06:53

田中宏明さんのコメント

(コメントID: 6915)

たかちゃんさん:

MacでDictionaryの学習をする場合、発展編１で学習したステップオーバーがお薦めです。ステップイン（[F8]）は、cls内の処理を通り、大変なので…

・ステップオーバー（[Shift]+[F8]）
呼び出し元にある、呼び出し先のマクロ名にカーソルがある時にステップオーバーを実行すると、その呼び出し先のマクロを実行した後、呼び出し元のマクロに戻ります。

> Dicの所を通過する度に、Dictionary.cls内の処理を行うような感じで、動きました。とりあえず、これでDictionaryの勉強を再開してみようと思います。

2020/11/12 04:47

たかちゃんさんのコメント

(コメントID: 6917)

田中さん:
色々ありがとうございます！先程、もう少し込み入ったプログラムを書きましたが、ちゃんと動きました。便利ですし簡単にかけるので最高ですね。Dictionary.clsの中身を見た時、何も理解できそうになく・・もうダメだと思いました。が、田中さんに教えてもらったあの２行で、驚くほど簡単に解決しました。本当に試して良かったです。ありがとうございます！

> たかちゃんさん:
>
> MacでDictionaryの学習をする場合、発展編１で学習したステップオーバーがお薦めです。ステップイン（[F8]）は、cls内の処理を通り、大変なので…
>
> ・ステップオーバー（[Shift]+[F8]）
> 呼び出し元にある、呼び出し先のマクロ名にカーソルがある時にステップオーバーを実行すると、その呼び出し先のマクロを実行した後、呼び出し元のマクロに戻ります。

2020/11/12 06:17

田中宏明さんのコメント

(コメントID: 6918)

たかちゃんさん:

私の経験上、実務で大量データをマクロで処理するとき、重複チェックは頻出です。Dictionaryを使えると作業効率が格段に向上しますね。

> 田中さん:
> 色々ありがとうございます！先程、もう少し込み入ったプログラムを書きましたが、ちゃんと動きました。便利ですし簡単にかけるので最高ですね。

2020/11/12 07:43

小川慶一さんのコメント

(コメントID: 6921)

田中宏明さん:

dictionaryは、vbaで使う場合手続きが多いのが(他言語に比して)やや残念ですね。

lookupの方法はいろいろあります。
それらをまとめた講座を作りたいなと思っています。(なかなか手を付けられていませんが)

2020/11/12 20:37

たかちゃんさんのコメント

(コメントID: 6923)

田中さん、小川先生

今回勉強した重複チェックパターンはしっかりメモっておきました。
Dictionaryは便利ですが、出来上がったマクロを動かすだけの人のパソコンにも設定しないといけない点が不便だなぁと思いました。
在宅ワークなどで、家のパソコンのOSがWindowsだったりMacだったりするとややこしそうです。。。
> たかちゃんさん:
>
> 私の経験上、実務で大量データをマクロで処理するとき、重複チェックは頻出です。Dictionaryを使えると作業効率が格段に向上しますね。

2020/11/13 13:27

小川慶一さんのコメント

(コメントID: 6933)

たかちゃんさん:

> 在宅ワークなどで、家のパソコンのOSがWindowsだったりMacだったりするとややこしそうです。。。

一般論としては、環境が統一されていたほうが仕事ははかどりますね。

2020/11/15 18:48