エクセルで抽出作業を行うにはマクロの設定がおすすめ【コピペして使えるサンプルマクロを公開】
豆父ちゃん
こんな要望にお応えします。
結論からいうと、抽出マクロを設定する！です。
抽出作業といってもいろんな方法があるので、業務内容に合った処理を行うマクロが必要になります。
私の会社では次の2つの処理を行う抽出マクロがよく利用されます。
- A列に入力されたテキストの一部を抽出し、B列に表示させる。
- A列に入力されたテキストの一部を抽出し、抽出したテキストをそのセルに表示させる。
抽出マクロを利用すれば上記2つのような処理も簡単に処理することができます。
この記事では、仕事でよく使われる抽出作業の自動化をする抽出マクロの作り方とコピペして使えるサンプルマクロについて紹介しています。
エクセルを使って数百、数千ものデータ編集を行なっていると、よく指示される業務内容といえばデータの統計をまとめてほしい！という内容です。指示される業務毎に抽出マクロを作成しておけば、少しアレンジするだけでいろんな業務に対応することが可能になり仕事の効率を格段にアップさせることができます。
職場からの依頼で私が作成した3つの抽出マクロを例にして、処理内容やサンプルマクロを紹介していきます。
- 【】内のテキストを抽出する
- 「0001」というテキストが含まれる設備の数量を集計する
- 検査日が同じ設備を抽出し集計する
上記3種類の抽出マクロがあれば大概の抽出作業の処理が可能になるはずです。抽出マクロが設定されたエクセルファイルを下記のサイトでダウンロードしてお使いいただくことができます。気になる方は是非サイトをチェックしてみてください。
目次
【】内のテキストを抽出するマクロ
長いテキストの中で、【】や「」内のテキストを抽出する処理を行うマクロです。カッコの他にも ×(バツ) や -(ハイフン) の前後のテキストを抽出することも可能です。マクロを使えば簡単に業務内容に合った処理にアレンジすることが可能です。
サンプル内容
A列に入力されたテキストの中から【】を検索し、【】内のテキストのみを抽出してE列に表示させるマクロです。
サンプルマクロ
| 1 | Sub カッコテキスト抽出() |
| 2 | On Error Resume Next |
| 3 | gyo = Cells(Rows.Count, 1).End(xlUp).Row |
| 4 | Range(Cells(2, 5), Cells(gyo, 5)).ClearContents |
| 5 | For i = 2 To gyo |
| 6 | ds1 = InStr(Cells(i, 1), “【") |
| 7 | ds2 = InStr(Cells(i, 1), “】") |
| 8 | If ds1 <> 0 And ds2 <> 0 Then |
| 9 | df = Mid(Cells(i, 1), ds1 + 1, ds2 – (ds1 + 1)) |
| 10 | Cells(i, 5) = df |
| 11 | Else |
| 12 | End If |
| 13 | Next |
| 14 | End Sub |
サンプルマクロの6行目と7行目で【】というテキストを指定しています。【】を変更したい場合は6行目と7行目のプログラムを編集してください。
「0001」というテキストが含まれる設備の数量を集計する
長いテキストの中で、指定するテキストが含まれているものの数量を集計するマクロです。
職場では数百個から数千個もの電気設備の台帳を編集する業務を行っています。フィルター機能を使って数量の集計をとる方法を行っていたのですが、効率が悪く集計ミスが多発する問題が発生していました。誰でも簡単に扱える集計マクロの導入で問題を解決しています。
サンプル内容
指定するテキストを入力(H2セル)という欄に指定するテキストを入力しマクロを実行します。
指定するテキストが全て含まれるA列の設備名称と数量が抽出されE列とF列に表示され、数量の合計が集計されます。
サンプルマクロ
| 1 | Sub 指定テキスト抽出() |
| 2 | On Error Resume Next |
| 3 | gyo = Cells(Rows.Count, 1).End(xlUp).Row |
| 4 | Dim Mo1 As String |
| 5 | Dim Mo2 As Long |
| 6 | Dim Mo3 As Long |
| 7 | Mo1 = Cells(2, 8) |
| 8 | Mo2 = Len(Mo1) |
| 9 | Range(Cells(2, 5), Cells(gyo, 6)).ClearContents |
| 10 | For i = 2 To gyo |
| 11 | gyo2 = Cells(Rows.Count, 5).End(xlUp).Row |
| 12 | Mo3 = Len(Cells(i, 1)) |
| 13 | For j = 1 To Mo3 |
| 14 | If Mid(Cells(i, 1), j, 1) = Left(Mo1, 1) And Mid(Cells(i, 1), j, Mo2) = Mo1 Then |
| 15 | Cells(gyo2 + 1, 5) = Cells(i, 1) |
| 16 | Cells(gyo2 + 1, 6) = Cells(i, 2) |
| 17 | Else |
| 18 | End If |
| 19 | Next |
| 20 | Next |
| 21 | gyo3 = Cells(Rows.Count, 6).End(xlUp).Row |
| 22 | Cells(gyo3 + 1, 5) = “合計" |
| 23 | Cells(gyo3 + 1, 5).Font.Bold = True |
| 24 | Cells(gyo3 + 1, 6) = Application.WorksheetFunction.Sum(Range(Cells(2, 6), Cells(gyo3, 6))) |
| 25 | Cells(gyo3 + 1, 6).Font.Bold = True |
| 26 | End Sub |
この抽出マクロは「0001」以外のテキストを検索しデータを抽出することができます。
指定するテキストが含まれるデータを全て抽出することができます。この抽出マクロを使えば、抽出漏れや集計ミスなどを防ぐことができます。
検査日が同じ設備を抽出し集計する
検査日が同じ設備を抽出し数量を集計するマクロです。
検査日に限らず、設置日や取替日などの項目があれば簡単に設定を変更することが可能です。
抽出作業では、同じ日付でまとめて抽出するパターンや同じテキスト内容でまとめて抽出するパターン、同じ背景色でまとめて抽出するパターンなどいろんなパターンがあります。基本となる抽出マクロを設定していると、業務内容に合ったアレンジを簡単に行うことができるので、下記で紹介しているサンプルマクロを使って設定してみてください。
サンプル内容
検査日が同じ設備を抽出し、数量を集計するマクロです。
１日にどれくらいの設備を検査したのか統計をとるためにこの抽出マクロを開発し導入しました。
フィルター機能や並び替え機能を使ってデータを集計している方が多いと思いますが、この抽出マクロの方がはるかに簡単に処理することができ、編集ミスの恐れがありません。
誰でも簡単に集計することができ、操作ミスが発生しにくい！ということから職場ではとても好評なマクロになっています。集計作業が多いという方は導入を検討してみてはいかがでしょうか。
サンプルマクロ
| 1 | Sub 検査日抽出() |
| 2 | On Error Resume Next |
| 3 | gyo = Cells(Rows.Count, 1).End(xlUp).Row |
| 4 | Range(Cells(2, 5), Cells(gyo, 6)).ClearContents |
| 5 | For i = 2 To gyo |
| 6 | gyo2 = Cells(Rows.Count, 5).End(xlUp).Row |
| 7 | If Cells(i, 3) = Cells(2, 8) Then |
| 8 | Cells(gyo2 + 1, 5) = Cells(i, 1) |
| 9 | Cells(gyo2 + 1, 6) = Cells(i, 2) |
| 10 | Else |
| 11 | End If |
| 12 | Next |
| 13 | gyo3 = Cells(Rows.Count, 6).End(xlUp).Row |
| 14 | Cells(gyo3 + 1, 5) = “合計" |
| 15 | Cells(gyo3 + 1, 5).Font.Bold = True |
| 16 | Cells(gyo3 + 1, 6) = Application.WorksheetFunction.Sum(Range(Cells(2, 6), Cells(gyo3, 6))) |
| 17 | Cells(gyo3 + 1, 6).Font.Bold = True |
| 18 | End Sub |
抽出マクロの設定の方法
上記で紹介しているサンプルマクロの設定方法とマクロの実行方法を紹介します。
サンプルマクロの設定方法
・「開発」→「Visual Basic」を選択する
・VBAProjectで右クリック→「挿入」→「標準モジュール」を作成
・標準モジュールに上記で紹介しているサンプルマクロをコピペする
以上でサンプルマクロの設定は完了です。
マクロ実行方法
次に設定したサンプルマクロを実行するための方法を紹介します。
・ボタンの作成
・作成したボタンにマクロを登録
登録したいマクロを選択して「OK」で設定が完了です。あとは作成したボタンをクリックするだけで抽出マクロを使用することができます。
マクロを実行する方法はボタンを利用する以外にもユーザーフォームを設定する、セルをクリックしてマクロを実行するなどの方法があります。下記の記事では簡単にVBAを実行する３つの方法を詳しく紹介しています。参考にしてみてください。
VBA初心者が効率化マクロを導入する方法
VBAなんて扱ったことないけどエクセル作業を自動化するマクロを導入したい！
このような問題を抱えながら日々のエクセル業務をこなしている方がおおくいらっしゃるのではないでしょうか。
こんな問題を解決する方法がこちら
- マクロの開発を外注に依頼する
- マクロが設定されたエクセルファイルをダウンロードする
- 参考書やネットでVBAスキルを身に付ける
VBAの勉強を始める前は上記の方法で効率化マクロを導入していました。
完成されたマクロを使ってエクセル業務を処理する
↓
作業効率が２倍以上アップ！！
↓
便利すぎて自分でもマクロを設定したいと考え始める
↓
参考書やネットの情報でVBAスキルを身に付ける
↓
マクロ開発を代行できる程のスキルを習得
VBAは
他のプログラミングに比べて参考書やネットで紹介されている情報量が多いので初心者でも導入しやすい
という特徴があります。
毎日のルーティーン作業にうんざりしている、だれでもできる単純作業に時間を費やしている、という方はあなたに合った方法でVBAを導入しちゃいましょう。
マクロの開発を外注に依頼する
マクロの開発を外注に依頼する導入方法は
VBAを全く扱えない方でもエクセル作業を自動化できる最も簡単な方法
です。
「こんなエクセル作業を自動化したい」「作った資料を自動的にPDFで保存したい」「決まった様式に写真を自動で貼り付けたい」
こんな感じで実現したい内容を依頼するだけで業務内容に合ったマクロを開発してくれます。
ガッツポーズの人
デメリットは、費用がかかることだけ。
開発内容、依頼先によってかかる費用は大きく異なります。
私が会社員の頃利用していた業者さんは１マクロあたり2万円～3万円の費用がかかりました。
疑問がある人
ちなみに、マメBlogでもエクセルマクロ開発代行サービスを承っています。
開発内容の確認、VBAコードの設定、動作確認後の調整、など全ての工程を私（マメ父ちゃん）が行っているので、
費用が安い！スピード納期！！
でやらせてもらっています。
ほとんど独学で身に付けたVBAスキルなので、内容によっては開発できないこともあるのでご了承ください。
ご相談、見積もり依頼は完全無料ですので気になる方は下記のリンクよりお問合せ下さい。
マクロが設定されたエクセルファイルをダウンロードする
VBA初心者でも『マクロが設定されたエクセルファイルをダウンロード』ですぐにエクセル作業を自動化することができます。
たとえば、
A４用紙に写真を貼り付けてコメントを入力する作業を自動化したい！という場合、
『写真を貼り付けるマクロ』が設定されたエクセルファイルをダウンロードすれば問題解決です。
実際に、『写真を貼り付けるマクロ』をダウンロードして使っている様子がこちら
ガッツポーズの人
『マクロが設定されたエクセルファイルをダウンロード』する導入方法は、
VBA初心者でもマクロを導入できる、費用が安い、
というメリットがあります。
しかし
VBAに関する情報が多く公開されているとはいえ、ネット上で想定するマクロは見つけることが難しい
というデメリットもあります。
ガッツポーズの人
個人ブログやファイルを販売できるサイト「note」や「Tips」ではいろんなマクロの情報が掲載されているので是非参考にしてみてください。
今までマメBlogで受注した効率化マクロを下記のnoteで掲載していますので併せてチェックしてみてください。
参考書やネットでVBAスキルを身に付ける
先程も紹介しましたが、
VBAは他のプログラミングに比べて参考書やネットで紹介されている情報量が多いので初心者でも導入しやすい
プログラミングです。
言い換えると、
情報量が豊富なので自分で勉強できちゃう
ということです。
私のVBAスキルはほとんど独学で身に付けたもので、
考えた処理のほとんどをVBAで表現できるレベルにまで到達した！
と思っています。
私の作ったマクロをプロのプログラマーが見れば、
お前の書くコードはインチキだ！「変数の宣言」とか「引数」を正しく使えよ！！
と感じるはずです。
でも、
私からすればどうでもいい！！！想定した内容を正しく処理できればよくないですか！？
独学であっても『写真を貼り付けるマクロ』『全てのシートをPDF出力するマクロ』『連番を振るマクロ』など、
業務内容に合ったマクロを開発できるようになれます。
独学でVBAスキルを身に付けるコツは、
エラーが発生してもいいからひたすらコードを書く、なんでもいいからエクセル作業をマクロ化してみる
です。
とは言っても、全くの知識0の方は何をすればいいかわからないですよね。
VBA知識0の初心者の方は、参考書『たった1秒で仕事が片づくExcel自動化の教科書【増強完全版】』を読んでみてください。
VBAの知識0の方でもすいすい読み進められる内容になっています。
やさしい内容にも関わらず「え！！VBAを使えばこんなことできるの！？」と感動すると思います。
実際に私はこの参考書を読んでからVBAの勉強を始めました。
ひらめく人
VBAの魅力を発見した後は、
ひたすら自動化したい処理を実現するコードを書くだけ
です。
【VBA 写真を貼る】、【VBA PDF出力】、のようにネット検索すれば知りたいコードをすぐにゲットできます。
実現したい処理に向かって、「調べる→書いてみる→エラーを改善する→調べる」を繰り返していると
自然といろんなマクロが作れるようになっているはずです。
VBAに興味が湧いてきたという方は今からVBA学習をスタートさせちゃいましょう。