① 1件目のデータ登録 “日本”, “Tokyo” ①A列のデータ（勘定科目）を全て読み取ります。 ③辞書登録（配列に登録）されている一覧データをD列（キー）「銀行コード」・E列（アイテム）「銀行名」を転記します。, 【連想配列の登録（追記）について】 ③ ３件名のデータ登録 ”英国”, “London” WordPress Luxeritas Theme is provided by "Thought is free". Dictionaryはとても高速に動作するため大量のデータを使う場合には有用な手段の１つになります。, Dictionaryオブジェクトはキーと値で１セットとなるデータ形式を持ちます。他の言語であれば連想配列やハッシュマップなどの言い方をされます。, キーは値を検索するために使う「しおり」の役割を持ちます。食券式の店の場合、食券を買って料理を受けとりますが、このときの食券がキーで、料理が値になります。, 同じDictionaryオブジェクトの中でキーと値のセットは複数持つことも可能ですが、同じキーの重複は出来ません。, Dictionaryオブジェクトを利用するには２通りの方法があります。１つはCreateObject関数を使う方法で、もう１つは参照設定を行う方法です。どちらでも動作は変わりませんが、参照設定を行うと多少高速なこととメソッドやプロパティが入力候補で表示されます。, VBA画面のツールメニュー→参照設定を選び、参照設定ダイアログで「Microsoft Scripting Runtime」にチェックを付けます。, 厳密には１行で書かない方がいいのですが、普通に利用する分では問題は発生しませんので１行でも構いません。私自身も１行で書くことが多いです。, このことについての詳細は「VBAでクラス変数の宣言とNewを１行で書いてよいか」をご参照ください。, Dictionaryオブジェクトには以下のメソッドとプロパティがあります。使い方は後述のサンプルコードに書いています。, Dictionaryオブジェクトはキーの重複は認められませんが、値の重複はありえるため、Itemsメソッドで取得した配列内で値が重複していることはありえますしエラーにもなりません。, Itemプロパティなどで使うキーがDictionaryオブジェクトに格納されているキーと厳密に同じでなければならない場合はBinaryCompareを指定し、大文字・小文字、ひらがな・カタカナ、全角・半角の区別せずに行う場合はTextCompareを指定します。, あくまでも検索する際のキーの条件のため、テキスト比較を設定してもキーの重複が許されるわけではありません。, Dictionaryオブジェクトにデータがセットされている状態ではエラーになります。, たとえばキー”1″を”2″に変更したい場合は「Dictionary.Key(“1”) = “2”」のように記述します。, Dictionaryクラスの各メソッドやプロパティには書き方が分かりにくいものもありますので、不明なものはコードをご参考ください。, Dictionaryクラスとよく比較対象とされるのが配列です。Dictionaryと配列との違いは、キーがあるかないかですが、それによりデータの格納方式にも違いがあります。配列は先頭から順にデータが並んだ状態でデータを格納しますが、Dictionaryにはデータの並び順は持っておらず、あくまでもキーが基準になります。そのため、並び順が重要な意味を持つ場合には後述するキーのソートを行うか、もしくは、Dictionaryクラスよりも配列を利用した方がよい場合があります。, また、配列はデータを格納するための領域をRedim構文やRedim Preserve構文で確保する必要がありますが、Dictionaryにはその必要がありません。Addメソッドを実行すると領域が拡張されてキーと値が追加されます。, Dictionaryは配列と違ってキーでデータを特定できるため、高速にデータの選別が可能であるという特性があります。そのため、多数のデータの中から特定のデータを抽出する、という処理を行う場合は配列よりも圧倒的にDictionaryクラスを使った方が高速に処理できます。, もし配列でのデータ抽出を繰り返し行うような処理で遅いと感じることがあるのであれば「VBAで配列を連想配列Dictionaryに変換する」をご参照ください。, Dictionaryを使う際に、キーにとして扱うデータは数値や文字列が多いと思いますが、オブジェクトを指定することが可能で、値にはオブジェクト（例：Range(“A1”)）の他にも配列や戻り値を返す関数（例：Timer）などを指定することができます。ただし、キーに配列をセットすることは出来ません。, オブジェクトをキーに設定できるため、以下のように、キーにRangeオブジェクト、値にワークシートや配列をセットすることが出来ます。, また、キーに使う数値と文字列は別物として扱われます。以下のコードにもありますが、1と”1″は重複せずにどちらもキーとして設定されます。, Dictionaryオブジェクトに未登録のキーだけを追加したい場合は、事前にExistsメソッドで対象のキーが追加済みかどうかを判定した上で、未追加であれば追加するようにします。, 追加済みのキーに対してAddメソッドを実行するとエラーになります。他の言語のハッシュマップなどではAddメソッドのような追加メソッドで直接上書きが出来るものもありますが、VBAのDictionaryクラスではそれは出来ません。そこでVBAでは疑似的に別の方法で上書きを行うことになります。, 上書きの方法には２通りあります。Itemプロパティを使って値の書き換えを行う方法と、Removeメソッドで削除してからAddメソッドで追加する方法です。どちらの方法でも構いませんがItemプロパティでの書き換えの方が手間が少ない分、処理速度も速いです。, Dictionaryオブジェクトを関数の引数としてやり取りを行う場合に、渡した関数側での処理が呼び出し元にも反映されます。, Integer等の数値型やStringの文字列型の場合は関数の引数にByVal指定をすると呼び出し元には関数内の編集が引き継がれませんが、DictionaryはByValを付けていても渡した関数での編集内容が呼び出し元にも反映されます。, 以下のサンプルは呼び出し元で1と2を追加し、関数を呼び出してその関数内部で3を追加し、呼び出し元でDictionaryの全てのキーに対応する値を出力しています。, 出力すると呼び出し先の関数で追加された3も一緒に出力され、関数間で引き継がれていることが分かります。, Dictionaryクラスのキーはソートの仕組みがありません。ただ、場合によってはキーがソートされていた方が都合がいい場合があります。そのままではソートはできませんので、Keysプロパティで全てのキーを配列として取得して、その全キー配列をソートする方法を行います。, よく紹介されるソート方法はワークシートの指定セル範囲を並べるSortメソッドを使う方法だと思いますが、個人的にはこの方法は避けた方がよいと思っています。理由は、ソートのためにワークシートやセルを用意しなければならず、使ったあとに削除も必要になりますし、セルへの値の設定や操作は処理が遅いため、せっかくのDictionaryクラスを使っている高速性の利点が犠牲になってしまうためです。, そこで、高速にソートを行うためには２通りの方法があります。１つはソート処理を自分で実装する方法で、もう１つは.NETのArrayListクラスのSortメソッドを利用する方法です。どちらを使ってもいいのですが、.NETの利用はあまりメジャーな方法ではないため、ここではクイックソートを実装する方法を紹介します。, クイックソートについての詳細は「VBAの配列をクイックソートで並べ替え」ご参照ください。ここでは利用しませんがArrayListクラスのSortメソッドについては「VBAの配列を.NETのArrayListのSortで並べ替え」をご参照ください。, コードで使っているクイックソートの関数は上記リンク先のコードをそのまま使っています。, キーのあいまい検索を許可するかどうかをCompareMethod列挙型の定数で指定します。. 仕事の事・育児の事・投資の事について発信していきます(^^)/ Twitterもやっているので、よかったらフォローお願いします。, よくわかる 仕事に使えるExcel関数ブック 2016/2013/2010/2007 対応, WealthNavi（ウェルスナビ）の運用実績2020年11月1週【+24,412円】, WealthNavi（ウェルスナビ）の運用実績2020年10月4週【+3,208円】. あいまい検索だと検索条件を含んでいれば完全に一致しなくても表示されます。 あいまい検索には前方一致、後方一致、部分一致の3種類があります。 前方一致検索. これはFindメソッドが指定した範囲の2番目のセルから検索を始めるためで、1番目セルは最後に検索され表示されています。 順番が重要になる場合は、あとで説明する「複数の条件で検索する方法」の「Or検索」のサンプルコードのようにFindメソッドを使って1セルずつ検索する必要があります。 ④ 登録されたデータをメッセージボックスに順番に表示します。, 下記のサンプルプログラムは、A列の「勘定科目」が重複登録されています。このA列のデータを元に、B列に重複されていない勘定科目データを表示するサンプルプログラムです。, 【プログラム説明・処理手順】 Dictionary オブジェクトの中から検索するキーの値を指定します。 Itemsメソッド [object.Items] Dictionary オブジェクトのすべての項目に含まれる配列を返します。 Keysメソッド [object.Keys] Dictionary オブジェクトにあるすべてのキーに含まれる配列を返します。 次はいよいよ、紹介する検索方法の速度が発揮されます。 100回の検索で配列は5秒、Findは15秒でした。 ワークシート名・・・「データ一覧」には、A列「支店名」・B列「勘定科目」・C列「金額」 まずは、If 文の使い方について、通常の使い方とあいまい検索をする方法を紹介します。, 例えば、セルA1 の文字列が「消費税」の場合に、メッセージボックスで「OK」と表示させたければ以下のようにコードを書きます。, セル A1 に「消費税」と入力した状態で実行すると、以下のようにメッセージボックスが表示されることが確認できます。, 例えば、セルA1 の文字列に「消費税」というキーワードが含まれる場合に、メッセージボックスで「OK」と表示させたければ以下のようにコードを書きます。, セルA1 の文字列が「消費税」とぴったり一致する場合を条件とするときは以下のように書きました。, それに対して、「消費税」というキーワードを含むことを条件とする場合は以下のように書きます。, こうすることで、消費税という文字の前後に他の文字が含まれる場合も条件を満たすことになります。, 例えば、セルA1 に「消費税の計算を行います」と記述した状態で実行してみましょう。, 「消費税」というキーワードの後ろに他の文字列があっても、以下のようにメッセージボックスが表示されることが確認できます。, 「消費税」というキーワードの前に他の文字列があっても、以下のようにメッセージボックスが表示されることが確認できます。, お題として、摘要を入力した状態で実行すると勘定科目と科目コードを割り当てるコードを考えてみます。, 例えば、摘要欄に「〇〇商店」というキーワードがある場合には、勘定科目は「仕入高」、科目コードは「712」というように自動的に割り当てれるようにするという意味です。, 何回も出てくる取引内容だと、このようにすれば勘定科目と科目コードを手入力せずに済みます。, さて、下準備としてまず現金出納帳の余白にキーワード、勘定科目、科目コードの対応が分かる表を作っておきます。, これを実行すると、以下のようにだいたいの勘定科目と科目コードが自動的に割り振られます。, すべての摘要のキーワードを想定すると量が多くなりすぎるので、絞る必要はあるわけですが、頻繁に出てくるものを設定しておくだけで勘定科目と科目コードを入力する手間が省けます。, If Tekiyou Like “*” Cells(j + 11, Keyword_Line) “*”, 今回のようにキーワードを順番に読み込む必要がある場合には、キーワードを「Cells(j + 11, Keyword_Line)」のように変数を使って表さなければなりません。, そのような場合に Like を使うとすると、先の “*消費税*”のように “” で囲われる中に全てを表示することができません。, これで、「Cells(j + 11, Keyword_Line)」というキーワードを含む文字列を表すことができます。, 税理士試験についてのまとめはこちらです！ ・既に登録されている場合は、F列に登録されている勘定科目と同じ行に、金額を加算します。, ③最後まで、データを順番に読み取り終わったら、F列・G列の1行目に項目名（勘定科目：合計金額）を付けます。 ③辞書登録（配列に登録）されている一覧データをB列に転記します。, 下記のサンプルプログラムは、上記のサンプル②と同じ重複データを削除するサンプルプログラムです。今回のサンプルプログラムは、キーとアイテムに部分にデータを登録を行い、キーデータ部分に対して重複データの削除を行います。 ①A列のデータ（キー）「銀行コード」とB列のデータ（アイテム）「銀行名」部分を全て読み取ります。 2020年6月に次男が生まれて2児の父親になりました(^O^) ④F列・G列の最終行に合計値を記入して、集計された勘定科目範囲に、囲線を引きます。, 下記のサンプルプログラムは、選択したデータを読み取り、重複データ（勘定科目）の削除を行い、勘定科目ごとに金額を集計するサンプルプログラムです。全体的な処理内容は、サンプル④と同じような作りですが、今回は、集計方法を「支店名＋勘定科目」の組み合わせで集計を行い、結果を別シートに作成します。, 【プログラム説明・処理手順】 税法2科目免除大学院についてのまとめはこちらです！, 異業種から会計事務所へ転職して税理士になった私の経験をもとに、30代半ばから日商簿記1級、税理士試験（簿記論、財務諸表論、国税徴収法）、大学院を経て税理士になったノウハウを体系化した書籍です↓, 税理士試験の税法2科目免除のために大学院で税法論文（修士論文）を書いた経験を元に、大学院生としての目線から税法論文の書き方を体系化した書籍です↓. Excel VBAのページへ戻る マニュアルのトップページへ戻る トップページのメニューへ戻る Like演算子. ②ワークシート「データ一覧」・B列のデータ（キー）「支店名」＋「勘定科目」を1件ずつ確認して既に登録されていないかを確認します。, 【新規登録の場合】 Copyright © 2020 AKIRA SITE（EXCEL VBA初心者・入門者向きサイト） All Rights Reserved. 今回はDictionary(連想配列)の使い方をご説明します。 Dictionaryは字のごとく、辞書の様に扱います。 Key(キー)とItem(データ)をセットで格納して、リストなどを作成するときに使用します。 Dictionaryは処理が非常に高速ですが、記述方法によってはとても遅くなります。 検索条件に指定した文字が先頭と一致する値を検索する方法です。 （新規の場合は、辞書登録（配列に登録）・既に登録されている場合は、何もしない） 'http':'https';if(!d.getElementById(id)){js=d.createElement(s);js.id=id;js.src=p+'://platform.twitter.com/widgets.js';fjs.parentNode.insertBefore(js,fjs);}}(document, 'script', 'twitter-wjs'); Excel の IF 関数と VBA の IF 文（IF ステートメント）の使い方. ワークシート名・・・「合計表」. EXCEL VBA フォルダー・ファイルのショートカット作成（アプリケーション・共有フォルダー・URLのショートカット）, 連想配列の登録・表示方法 （メッセージボックスに表示：Scripting.Dictionary), Sub Dictionary01() '登録した連想配列データをメッセージボックスに表示します。, '******** AKIRA55.COM ******* https://akira55.com/associative_array_ki/, '配列データの0～2まで繰り返す。（配列のスタートは０から：Count登録件数３なので、－１）, ●実行前～実行後 ※プログラム実行後、順番に取り込んだ配列データを順番にメッセージボックスに表示しました。, ' '******** AKIRA55.COM ******* https://akira55.com/associative_array_ki/, ●実行前～実行後 ※プログラム実行後、A列の重複されているデータをB列に重複されていない勘定科目一覧が表示されました。, 重複データの削除（キー：Key、アイテム：Item）一意データの表示 (Scripting.Dictionary), '重複データが無ければ、辞書登録（配列）Keyは、Tmp01 Itemは、Tmp02で追加登録, ●実行前～実行後 ※プログラム実行後、A列の「銀行コード」とB列の「銀行名」を読み取り（配列登録）、D列に「銀行コード」と「銀行名」が重複データの削除されて表示されました。, 連想配列を使用した集計（合計値）項目ごとの集計・重複データの削除 (Scripting.Dictionary), Sub Dictionary04() '勘定科目一覧データから勘定科目毎に集計します。, ●実行前～実行後 ※プログラム実行後、選択したデータを読み取り、重複データ（勘定科目）の削除を行い、F列に勘定科目・G列に金額ごと集計され、合計表示されました。, 連想配列を使用した集計（合計値）項目ごとの集計・重複データの削除・結果を別シートに転記(Scripting.Dictionary), Sub Dictionary05() '勘定科目一覧データから支店名・勘定科目ごと、別シートに集計します。, ●実行前～実行後 ※プログラム実行後、ワークシート「データ一覧」を元に、「支店名ー勘定科目」ごとに一意データのデータとして集計され、別シート「合計表」へ集計結果が表示されました。, EXCEL VBA Google Chrome(グーグルクローム)の操作・webスクレイピング・情報取得（Seleniumの設定・準備）, EXCEL VBA Replace関数 複数の文字列の置換（セルの値・指定文字列位置・置換リスト・数式の置換）, EXCEL VBA 請求書・領収書の作成・自動判定・自動押印（電子印鑑）丸印・角印（テクニック）, EXCEL VBA Goto ステートメント（行ラベル・行番号）指定ラベルへジャンプ, EXCEL VBA Replace メソッド 複数の文字列の置換（セル範囲・一括変換・置換リスト）, EXCEL VBA Substitute メソッド 複数の文字列の置換（大量・一括変換・置換リスト）, EXCEL VBA Weekday関数・WeekdayName関数・日付から曜日を取得・祝日判定, EXCEL VBA RangeオブジェクトOFFSETプロパティ（基準セルからの行・列移動・相対参照・セル範囲を移動する）, EXCEL VBA 連想配列で合計・グループ集計・別シート転記・重複削除 (Scripting.Dictionary), EXCEL VBA 文字列の読み上げ・セルのデータを読み上げる（読み上げ機能：RangeオブジェクトのSpeakメソッド）, EXCEL VBA オーバーフローしました。（実行時エラー：６）エラー原因・修正・回避・対処・解決, EXCEL VBA InternetExplorerの操作 住所から最寄り駅を検索する・自動収集（テクニック）, EXCEL VBA フォルダー内のファイル名をシートに一覧表示（サブフォルダー含める：再帰呼び出し）テクニック, EXCEL VBA ハイパーリンクの追加・設定 別のシートへジャンプ・Webページを表示（Hyperlinkオブジェクト）, EXCEL VBA InternetExplorerの操作（ブックマーク・お気に入り登録・管理）テクニック, EXCEL VBA InternetExplorerの操作（Webページ操作・監視・タイトル名取得・URL取得・ブックマーク）②, EXCEL VBA ワークシートの追加・複数追加・先頭・最後（Worksheets.Addメゾット）, EXCEL VBA 条件に合う集計・一致する集計・合計の集計「Sumif・Sumifs」の使い方, EXCEL VBA Sheet(シート)上にコンボ ボックスの設置（ComboBox）, EXCEL VBA WSH .Popupメソッドの使い方（メッセージボックスを自動に閉じる・時間経過で閉じる）, Dictionary オブジェクトに新しいキー/アイテムのペアを追加します。（登録するデータは、キー・アイテム一緒に登録します。キーのみでも登録可能）, 指定したキーが Dictionary オブジェクト内に同じデータが存在するかどうかを返します。（存在する場合は、True 存在しない場合は、False を返す）, オブジェクト内のすべてのアイテム（Items）の配列を返します。アイテムの場合は、データ重複しても大丈夫です。, オブジェクト内のすべてのキー（Keys）の配列を返します。キー（Keys）については、重複したキーを登録するは出来ません。, オブジェクト内の全てのキー/アイテムのペアを削除します。（登録したデータがクリアされます。）, Dictionary オブジェクト内でキーを比較するために比較モードを設定するか返します。キーを比較する際にあいまい検索を許可するか指定します。. あいまい検索をするには、Like演算子とアスタリスク(*)を使ったワイルドカードでできます。Recordsetで検索するには、FindFirst・FindPrevious・FindNext・FindLastのメソッドを使用します。本格的なアプリにする場合、[最初から（FindFirst）]や[後方検索（FindPrevious）]などを追加します Excel でよく使う関数の1つに IF 関数があります。そして、VBA でも同様に IF文（IF ステートメント）というものがあります。この両者を使えば同じ計算をすることができ、VBA を新たに学習し始めた方にとっては関数と照らし合わせながら学習すると効果的です。今日は... 以下については、リンク先よりお願い致します。----------------------------------------------------------------, Like によるあいまい検索で現金出納帳の摘要から科目を割り当てる VBA コード, 3、まとめ ② ２件名のデータ登録 ”米国”, “Washington D.C” Excelに入力されている大量のデータから特定の文字が含まれているセルを探したいということはないでしょうか。 データ量が多ければ多いほど探したいデータを見つけるのが大変で時間がかかってしまいます。, 本日はExcelでデータの検索をしたい方に、Excel関数を使ってあいまい検索をする方法を紹介します。, Excelで検索をする時、通常の検索だと検索条件に指定した文字と完全に一致しないと検索結果として表示されません。あいまい検索だと検索条件を含んでいれば完全に一致しなくても表示されます。, Excelという文字列を前方一致した場合、【Excel・・・】という値は合致されますが、【・・・Excel】という値は合致しません。, 【・・・Excel】という値は合致しますが、【Excel・・・】という値は合致しません。, 前方一致、後方一致の間ような感じで、検索条件が文字の中に含まれていれば合致します。前方一致、後方一致で合致する文字の他に【・・・Excel・・・】という文字列も合致します。, 先頭文字が「E」の文字をカウントする前方一致検索をする場合、検索条件には「E*」と入力します。 最後の文字が「t」の文字を検索する後方一致検索をする場合、検索条件には「*t」と入力します。文字の中に「E」が含まれる文字をカウントする部分一致検索をする場合、検索条件には「*E*」と入力します。, CountIF関数を使って、前方一致検索・後方一致検索・部分一致検索をしてみます。, A列の中で1文字目が【W】という文字がいくつあるかという前方一致検索をおこなうには、【=COUNTIF(A1:A8,"w*")】とセルに入力します。A列には「Word」という文字が2つあるので、【2】という結果が表示されます。, B列の中で最後の文字が【T】という文字がいくつあるかという後方一致検索をおこなうには【=COUNTIF(B1:B8,"*T")】とセルに入力します。B列には「PowerPoint」というTで終わる文字が1つあるので【1】という結果が表示されます。, C列の中で、【E】が含まれる文字がいくつあるかという部分一致検索をおこなう場合には【=COUNTIF(C1:C8,"*E*")】とセルに入力します。C列に【E】が含まれる文字は「Access、Excel、PowerPoint」の3種類で合計5つあるので、【5】という結果が表示されます。, Vlookupであいまい検索をしたい場合は、検索値にあいまい検索の条件を入力していきます。, 先頭の１文字目が【千】という文字を検索して２列目を表示させる前方一致検索をする場合は、【=VLOOKUP(D1,A1:B12,2,FALSE)】と入力します。セルに検索値を入力してその値を参照することもできるので、今回はセルD1に検索値を入力しています。, IF関数の条件指定でアスタリスクなどのワイルドカードを使用することはできません。 どうしてもIF関数でワイルドカードを使ってあいまい検索をしたい場合はCountIF関数と合わせることで使うことができます。, 始めまして。