エクセルで抽出作業を行うにはマクロの設定がおすすめ【コピペして使えるサンプルマクロを公開】
豆父ちゃん
こんな要望にお応えします。
結論からいうと、抽出マクロを設定する！です。
抽出作業といってもいろんな方法があるので、業務内容に合った処理を行うマクロが必要になります。
私の会社では次の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を実行する３つの方法を詳しく紹介しています。参考にしてみてください。
エクセル作業の効率を上げるにはマクロがおすすめ
実際の仕事でよく使われる抽出マクロについて紹介してきました。
エクセルマクロはデスクワークの効率を上げてくれるとても便利なツールです。さらにパソコンの中にExcelのソフトが入っていれば誰でもすぐにマクロを利用することができるので、手軽に始められるプログラミングとも言われています。
デスクワークの効率を上げたい！と考えている方はエクセルマクロの導入に挑戦してみてはいかがでしょうか。
抽出マクロをアレンジして使ってみたい！という方は下記の送信フォームからお問い合わせ下さい。あなたの業務に合った抽出マクロを開発してご提供させて頂きます。
マメBlogではエクセルマクロの開発サービスを行っています。マメBlogで紹介されているエクセル関係の記事を見て、こんな処理をするマクロを導入したい！という方はお気軽にお問い合わせください。迅速に対応させていただきます。またエクセルマクロの開発について詳しく知りたい！という方は下記の記事を参考にしてみてください。
