前回の記事でも紹介した大塚あみさんの「#100日チャレンジ 毎日連続100本アプリを作ったら人生が変わった」という本を読んでからChatGPTを使ってExcelのマクロの練習問題を解くようになりました。
その延長で仕訳データなどでダブり（重複）するデータを検索して、もしダブったデータがあればセルに色を付けるマクロを作ってみたので備忘記録代わりに記事にしてみようと思います。
仕訳データなどの重複データに色を付けるマクロ
本を読んでからChatGPTにExcelマクロの実力を確認できるような問題をいくつか出してもらって、それに回答するという作業をここ数日続けていました。
ただ、ChatGPTが出してくれる問題は「一般的なExcelマクロの問題」になっていて、税理士業務に特化したようなものではないので、つかいどころのイメージが湧きにくいのが難点でした。
何とか税理士業務でも使えるようなExcelのマクロが作れないかと考えていたところ、「重複データを削除せよ。」という問題をChatGPTが出してきまして、「あ、これなら使えるかも」と。
重複データを「削除する」という作業は税理士業務の中ではそんなにすることはないかもしれませんが、重複データが「あるかどうかを確認する」作業というのは、freeeやマネーフォワードにすでに機能として搭載されているので、需要があるのだろうと。
なので、このマクロをある程度、汎用的にしたら少しは税理士業務の中では使えるのではないかと考えて作業をすることにしました。
具体的なイメージとしては弥生会計やfreee、マネーフォワードなどからエクスポートした仕訳データについて重複データがないか確認し重複データがあればそのデータに色を付けるマクロになります。
全体のイメージ動画とざっくりとした内容
マクロを実行した際のイメージはこんな感じになります↓
決められた場所にタイトル行とデータを貼り付けた後にボタンを押すとユーザーフォームが表示され、ユーザーフォーム上にタイトル項目がリスト形式で表示されているので
そのリスト上から重複を確認したい項目を複数選択した上でボタンをクリックすると、選んだ項目に対して重複データがあるかないかを検索し、もし重複したデータがあればセルの色を黄色に変化させるという感じになります。
動画では会計データの重複をイメージしていますが、別に会計データ以外でも大丈夫です。
例えばこんな感じで、社員名簿のようなデータでも使うことができます↓
作りたいイメージをChatGPTに放り投げる
最初から動画で紹介したようなユーザーフォームを使ってダブったデータに色をつけるというマクロ思いついてたわけではなくて、当初作業していた内容としては（ユーザーフォームなどは表示させず）画面上に設置したボタンをクリックしたら重複したデータに色をつけるというマクロを作っていました。
こんなやつです↓
それでも別に問題はなかったのですが、自分以外が使うケースを考えた時にいろんなタイトル項目であったり、データを貼り付ける「場所」などをある程度固定した方が汎用的に使えるような気がしてきて、
「じゃあユーザーフォームを追加したほうが使いやすのでは？」
という感じでユーザーフォームを追加する流れになりました。で、最終的にできたのが動画のような形態ということになります。
流れとしてはこんな感じ
- １行目にタイトル行があって、２行目以降にデータがある前提で重複データに色を付けるマクロをChatGPTに作らせる。
- 作ってみたけど、重複チェックする項目を自由に設定したいと思うようになる。
（「日付」と「金額」で重複しているデータや、「日付」と「金額」と「摘要」が重複しているデータを調べたいといった感じ） - 重複チェックする項目をユーザーフォームに表示させてリストから選択させたらいいのではと思いつきChatGPTにコードを作りなおさせる。
- タイトル行を１行目から５行目に変更し、６行目以降にデータを置くように微調整
こういった過程をChatGPTとやりとりしながら作ったのが動画のExcelマクロです。
ユーザーフォームを作成する
コード自体はChatGPTが作成してくれますが、そのコードを実行する土台となるユーザーフォームについてはこちらで作る必要があります。
ChatGPTが作成したコードがきちんと動くように、こちらで作成したユーザーフォームが下のようなものになります↓
ChatGPTから指示された必要な物なものを作るだけなので、そこまで作業としては難しくありません。
今回、ユーザーフォーム上に設置しないといけない項目としては「リストボックス」と「コマンドボタン」の二つだけでしたが、それ以外にも分かりやすいように「ラベル」を設置しています。
コマンドボタンに記入したコード
あとはChatGPTが作成したコードを指定された場所にコピペしていけば完成になります。
コマンドボタンがクリックされたときに実行されるコードがこんな感じでChatGPTが作ってきたので貼り付けます↓
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 | Private Sub CommandButton1_Click()'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝'【マクロの概要】'ユーザーフォーム上で選択した「見出し（タイトル）」に対応する列をもとに、「重複チェック」シート上の重複データを検出し、'重複しているすべての行（最初に出現した行も含む）に黄色で行全体の色を付けます。'処理の前には、前回の色付けをすべてクリアするので、繰り返し実行しても問題ありません。''― 利用例：' 氏名＋生年月日など、任意の複数列を選択して「同じ人物」をチェックしたい場合などに便利です。'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝ '＝ 使用する変数の宣言 ＝ Dim dict As Object ' 重複チェック用のDictionaryオブジェクト Dim i As Long, j As Long ' 行ループ(i)・ListBoxループ(j)用のカウンタ Dim lastrow As Long ' 名簿シートの最終行を取得する変数 Dim ws As Worksheet ' 対象となる「重複を確認する」シート Dim selectedCols As Collection ' ユーザーフォームで選択された列番号の一覧を保持 Dim serch_words As String ' 重複チェック用の連結文字列（検索キー） Dim colIndex As Variant ' 選択された各列の列番号（For Each で使うのでVariant型） Dim headerText As String ' ユーザーフォームで選択された見出しのテキスト '＝ 名簿シートの設定と最終行の取得 ＝ Set ws = Worksheets("重複チェック") lastrow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row '＝ Dictionaryと選択列のコレクションを初期化 ＝ Set dict = CreateObject("Scripting.Dictionary") Set selectedCols = New Collection '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝ '― ListBoxで選ばれた見出しから、対応する列番号（1,2,3,...）を取得して保存 '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝ For j = 0 To Me.ListBox1.ListCount - 1 If Me.ListBox1.Selected(j) Then headerText = Me.ListBox1.List(j) '― シートの５行目から同じ見出しを探し、列番号を取得 For colIndex = 1 To ws.Cells(5, ws.Columns.Count).End(xlToLeft).Column If ws.Cells(5, colIndex).Value = headerText Then selectedCols.Add colIndex Exit For End If Next colIndex End If Next j '＝ 前回の実行で塗られた色を全行からクリア ＝ ws.Rows("6:" & lastrow).Interior.ColorIndex = xlColorIndexNone '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝ '― 6行目から下を順に処理し、検索キーを作成して重複チェック '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝ For i = 6 To lastrow serch_words = "" For Each colIndex In selectedCols '― 選択列の値を連結してキーにする（Trimで前後の空白も除去） serch_words = serch_words & "_" & Trim(ws.Cells(i, colIndex).Text) Next colIndex If dict.exists(serch_words) Then '― すでに同じキーがある ⇒ 現在行＆元の行どちらにも色を付ける ws.Rows(i).Interior.ColorIndex = 6 ws.Rows(dict(serch_words)).Interior.ColorIndex = 6 Else '― 初めてのキー ⇒ 行番号をDictionaryに記録 dict.Add serch_words, i End If Next i '＝ 完了メッセージ表示 ＝ MsgBox "重複しているすべての行に色を付けました。", vbInformation '＝ ユーザーフォームを閉じる ＝ Unload MeEnd Sub |
ユーザーフォームに記載したコード
ユーザーフォームが表示されたときに、タイトル行の項目をリストボックスに表示させるためのマクロをユーザーフォームの中に記載します。
こちらもコードはChatGPTが提示してくれました。
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 | Private Sub UserForm_Initialize()'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝'【マクロの概要】'' このユーザーフォームが開かれたときに実行される初期化処理です。' 「重複チェック」シートの1行目にある各列の「見出し（タイトル）」を取得して、' ListBox（ListBox1）に一覧として追加します。'' ユーザーはこのListBoxから複数の見出しを選択できるようになっており、' 後続の重複チェック処理に使用される対象列を柔軟に選べる仕組みになっています。'＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝ '＝ 変数の宣言 ＝ Dim ws As Worksheet ' 名簿シートを参照するためのオブジェクト変数 Dim lastCol As Long ' ５行目で使われている最終列番号を格納する変数 Dim i As Long ' 列ループ用カウンタ Dim headerText As String ' 各列の見出し（セルの値）を格納する変数 '＝ 名簿シートをセット ＝ Set ws = ThisWorkbook.Worksheets("重複チェック") '＝ 5行目の最終列を取得 ＝ lastCol = ws.Cells(5, ws.Columns.Count).End(xlToLeft).Column '＝ ListBoxを初期化（前回のデータが残らないように） ＝ Me.ListBox1.Clear '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝ '― 名簿シート５行目の見出しを、ListBox1 に追加していく（空欄は除く） '＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝ For i = 1 To lastCol headerText = ws.Cells(5, i).Value If headerText <> "" Then '― 見出しテキストが空でなければ、ListBox に追加 Me.ListBox1.AddItem headerText End If Next i '＝ ListBoxの複数選択を有効化（複数列の指定を可能に） ＝ Me.ListBox1.MultiSelect = fmMultiSelectMultiEnd Sub |
ユーザーフォームを「表示させるマクロ」を作成する
ここまででユーザーフォームの機能の作成が完了したので、最後に画面上にユーザーフォームを表示させるマクロを標準モジュールで作成して、そのマクロをExcel上に設置したボタンに登録すれば作業は完了になります。
1 2 3 4 5 | Sub showDuplicateChecker() ' ユーザーフォームを表示 UserForm1.Show End Sub |
このマクロをボタンに登録します。
微調整をするときにはコードの理解がある程度必要。
当初ChatGPTに命令した形では1行目にタイトル行、2行目以降に実際のデータが並んでいることを前提としてChatGPTはマクロを作成してきたんですが、
今回最終的にはそれを微調整し、５行目にタイトル行を設置し、６行目以降のデータについて重複しているデータがないかをチェックするようなマクロになりました。
この微調整をする時に、もう一度ChatGPTに命令をしてコードを再作成するのもいいのですが、（今回のような複雑ではないマクロであれば）コードの知識や理解があれば、手動で微調整をして自分のやりたい形を表現をすることが出来ます。
ChatGPTに頼りきりではこまかな微調整やメンテナンスが難しくなるような気がするので、やはり基本的な知識や理解（コード全体を見渡してそれぞれのコードが動作するように管理する力のようなもの）は必要だと改めて感じました。
まとめ
ChatGPTが提示してきたExcelマクロの練習問題から、（使えるかどうか分からないけれども）データのだぶりがないかをチェックするExcelマクロを作ってみました。
このマクロにデータをフィルターする機能や削除する機能をつけるとより使える感じになるかもしれません。
興味がある人は一度作ってみていただければと思います。
【本日の近況報告】
お菓子のルマンドがココアジュースになって販売されていたので、物は試しと買ってみましたが、
味はどう表現していいのか難しい感じのお味でした。お菓子の味が先入観として強くあるためドリンクになると。。。
【本日の１曲】
Shaggy & Sting/Til A Mawnin
Shaggyとスティングによるこれからの季節にぴったりなレゲエナンバー。個人的にこの曲を聴いたときにLong Beach Dub Allstarsを連想しました。
スティングは知っていますが、Shaggyという人は知りませんでした。youtubeでいくつか曲を聴いたところ、 Angelという曲はどこかで聴いたことがあるような。。
