半自動でデータを仕訳をするためのマクロ～複数行・カーソル選択

【カオスな現場経験 x 分析力を組み合わせた、少し尖ったマクロのレシピ集】

　今回はデータと現物、もしくは他のデータを照合しながら「データの仕分け」を行いたい時に役に立つマクロ/VBAを紹介します　

　複数行をカーソルで選択した後、ショートカットキーでマクロを起動し、カーソルで選択した範囲の内容を自動で変更します

全てのデータを一括で処理できるわけではないですが、手動で行うよりは相当な業務削減になります

尚且つ、後でデータベースに取り込む想定なので、IDを自動で変更する内容になっています

上のGIF画像はシート上のコードを自動で変えるところですが、ユーザーフォームで内容も変えることができます

　ユーザーフォームは前述の通りショートカットキーで開くのですが、ユーザーフォームが開いた時には自動で修正後の商品コードが反映されます

以前、私は肉関連の仕事をした時があります

この仕事のデータ管理上の特徴は、入荷した肉を部位の特性に応じて仕分けをすることでした

全く同じ質の肉が入荷するわけではないので、こういった仕分けが必要になっていました

　私が関わる前は、現場担当者が書面上で仕分けを行い、別な担当者がエクセルの列を手動で分けて「肉の仕分け状況」をデータ上で表現していました

この一連の作業をするのに毎週、複数人で相当な時間をかけていました

　つまり、列で仕分けをしているということは、人間が目で見ないと「入荷データが区分されている/IDが違っている」ことに気づけないのです

　他の言葉で言い変えると、「仕分け情報」をコードで表現できておらず、データベースに入荷データを取り込めない形になっていました

ですので、その後の作業も完全に手動になってしまっていました

仕分けした入荷データをデータベースに取り込めるようにする際に、活用したのが今回紹介するマクロです

以下が今回紹介するマクロの流れです

複数行・選択➡ショートカットキー/マクロ起動➡ユーザーフォームで内容確認・仕分け実行

　手動でデータを仕分けするよりはかなり高速で処理を行えるだけでなく、データベースにも取り込める形になっています

　このマクロは肉などの生き物関連の他にも、意外と活用機会があるのではないかと思いますので、役に立つ機会があれば幸いです

ポイント

専用IDの作成

　今回は現実にそんな場面が実際にあるかは別として、入荷データと現物の肉を見ながら商品コードを設定していく内容になっています

商品コードは3か所に分け、「ハイフン」で繋ぎます

最後の枝番は１をデフォルトに設定しておいて、現物を見ながら変えていくイメージです

例：A-1-1 ➡ A-1-2 ➡ A-1-3

カーソルの選択範囲を取得

カーソルの選択範囲は、開始行と終了行の２つの行位置で範囲を取得します

ショートカットキーに紐づけたマクロを起動した後は、開始行と終了行の位置を取得して変数に格納します

開始行と終了行の位置を取得するコード

カーソルの選択範囲の開始行は次のコードで取得します

rowStart = ActiveCell.Row

カーソルの選択範囲の終了行は次のコードで取得します

rowEnd = Selection(Selection.Count).Row

ショートカットキーでマクロ起動

ショートカットキーは次の画面で割当することができます

枝番の自動繰り上げ

前述の通り、ユーザーフォームが開いた時には修正後の商品コードがユーザーフォームに反映されます

　商品コード（5桁）を別途、4桁で管理した上で（例：A-2-2➡A-2-）、4桁別に枝番の最大値を別シートで管理できるようにしておくのがポイントです

以下の流れで枝番の自動繰り上げを設定します

①ショートカットキーが押された時

・まずシートの内容を全て別シートにコピーします

　コピー元シート名：仕分けシート、コピー先シート名：最大値

・コピーされた内容は商品コードを4桁に変換します（例：A-1-1➡A-1-）。その上で重複を排除します

　同時に下の画像のように枝番最大値を１に設定します

・コピー元のシートの全ての行とコピー先のシートをMATCH関数により照合して、コピー先の最大値シートの枝番最

　大値を置き換えます。例えば、下の画像のようにコピー元の仕分けシートにてA-2-2という風に既に「A-2-」の枝

　番の最大値が２になっている場合には、コピー先の最大値シートの枝番最大値を２に変更します

②ユーザーフォームの実行ボタンを押した時

・コビー先の最大値シートから、該当の商品コード（4桁）の最大値を取得し、取得した最大値に＋１をした枝番を

　商品コード（4桁）の枝番にします

ユーザーフォームの内容をシートに反映

ユーザーフォームの実行ボタンを押したら、取得した開始行位置と終了行位置で指定する範囲にユーザーフォームの内容を反映します

＜まとめ＞

今回は、カーソルで選択した範囲を自動的に変換するマクロについて解説を行いました

カーソルで選択した範囲の開始行と終了行の位置を取得するのが最大のポイントです

このマクロを実際に使用する時には「処理を間違えた時にどうするの？」という疑問が出ると思います

間違えた時に修正を行えるようにするには、元のデータを別シートにコピーしておくのがいいと思います

にほんブログ村