カテゴリー別アーカイブ: 実践的マクロ・レシピ集

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

ポイント

専用IDの作成

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

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

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

例:A-1-1 A-1-2 A-1-3

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

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

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

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

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

rowStart = ActiveCell.Row

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

rowEnd = Selection(Selection.Count).Row

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

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

枝番の自動繰り上げ

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

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

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

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

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

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

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

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

最大値シート

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

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

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

シート間の照合

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

・コビー先の最大値シートから、該当の商品コード(4桁)の最大値を取得し、取得した最大値に+1をした枝番を

 商品コード(4桁)の枝番にします

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

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

<まとめ>

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

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

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

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


にほんブログ村

とてつもなく大きい表を扱いやすくする~動く条件付き書式~

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

とてつもなく大きい表を使っているときに迷子になることがありませんか?

「今、どの行どの列セルを扱っているんだろう・・・」

しまいには、ミスが怖いので表を印刷して「定規」を当てたりしたことはありませんか?

今回、この迷子対策として参考にしたのがこの記事です

参考記事

今回は迷子対策として、上の記事を基にした「動く条件付き書式」の事例を紹介します

この動く条件付き書式では、カーソルを置いた位置の行列の色が変わるので、「迷子対策」にはバッチリです!

紹介する事例は2つあります!

1つ目の事例は、CELL関数とROW関数、そしてCOLUMN関数を使用して条件付き書式を設定します

こちらは、手動で条件付き書式を設定する内容になっています

 *イベントプロシージャ―に1行コードを書く必要があります

この方法の場合には、重たいファイルの場合だと「条件付き書式」の動きがかなり重たくなります 

 2つ目の内容は、イベントプロシージャ―を使用して、カーソルが動く都度「条件付き書式」をマクロで自動設定します

 こちらは条件付き書式の範囲をで指定するわけではなく、「行列」双方向の2本の線で範囲を設定しますので、あまり動きを重くしないですみます

 後、2つ目の方法の場合には色をシート上で設定できるようにするなどの、実践的に使えるようにする為の工夫を複数行っています

こちらは、サンプルファイルをダウンロードして、実際の動きを確認できます!!

①条件付き書式を全て手動で設定

まずは、条件付き書式を設定する範囲を手動で指定します

その後、条件付き書式の設定画面を開き、ルールの種類の選択を「数式を使用して、書式設定する設定を決定」にします

「次の数式を満たす場合に値を書式設定」には、画像のような数式を設定します

数式:=OR(CELL(“ROW”)=ROW(),CELL(“COL”)=COLUMN())

*上の数式をそのままコピーする際には、が半角になっていることをご確認ください

これで条件付き書式の設定は終了です

実はもう一つ、仕掛けが必要です

イベントプロシージャ―を使用し、セルの選択が変更になった場合に「更新処理」を行う必要があります

 更新処理を設定するには、まずは、上の画像のように該当のシートで右クリックをして「コード表示」を選択します

 次に開いたVBE(Visual Basic Editor)画面では、下の画像の黄色の印の箇所がそれぞれ「Worksheet」「SelectionCange」になっていることを確認します

プロシージャ―には1行だけコード*を入れれば、条件付き書式の更新処理が設定されます

 *Application.ScreenUpdating = True

②都度、条件付き書式の範囲をマクロで設定

動く条件付き書式

こちらは、前述のようにこの「動く条件付き書式」を実践的に使えるように様々な工夫を行っています

各種設定シートというシートを設けてあり、様々な設定が行えます

工夫一覧

1.シート上での色設定

各種設定シートにて、色を指定すれば、条件付き書式で設定する色を変更できます

2.開始行や開始列の設定

条件付き書式の設定を行う開始行と開始列はシート上で調整できます

上の画像では開始行が6行目、開始列が1列目ですので下の画像のように色が変わります

3.停止・再開処理他

この動く条件付き書式は「停止」したい時があると思います

この際は、停止ボタンを押せば停止ができる仕掛けになっています

停止処理を行った場合には、停止前に設定された色が残ってしまっています

ですので、上の画像の消去ボタンを押せば「設定残り」の色は消去できます

コード上の工夫

こちらは、ごく簡単なポイントだけを解説させて頂きます

この事例は、前述のように条件付き書式の範囲が拡大したり縮小する場合も想定しつつ、処理が重たくならないようにします

こちらの場合は、色の付け方が、1番目の方法と変えてあります

カーソルがある位置が、色のつく範囲の最終行と最終列になります(1番目の方法も可能ではありますが、今回は極力色設定する範囲を小さくするという趣旨でこの方法にしました)

ポイント1 ⇒面ではなく、行方向1行、列方向1列で範囲設定

カーソルを移動させた際は、イベントプロシージャ―にてカーソル位置を取得します(Targetを引数にして取得)

カーソル位置を取得したら、2つ範囲を設定します

1つ目は行方向です。範囲の開始列は1列目などを固定で指定して範囲設定します

2つ目は列方向です。範囲の開始行は1行目などを固定で指定して範囲設定します

ポイント2 ⇒設定済みの条件付き書式は事前にクリアしておく

重複して条件付き書式が設定されないように、設定済みの条件付き書式はクリアしておきます

ポイント3 ⇒マクロ処理の途中でイベントプロシージャ―は止めておきます

1つの範囲に条件付き書式を設定した際に、更にイベントが起動してしまいますので、次の1行のコードでイベントを中止しておきます(後で再開が必要です)

Application.EnableEvents = False

<まとめ>

 今回はとてつもなく大きい表を扱う際、迷子にならないようにカーソルがある行と列の色を変える方法を紹介しました

但し、

特に2番目の方法などが当てはまるのですが、実際に業務で活用する場合は、さらに一工夫が必要です

 例えば、2番目の方法は設定済みの条件付き書式をクリアしてしまいますので、「迷子対策」以外の条件付き書式の設定が消えてしまうケースも想定されます

ですので、別途「迷子対策以外」の条件付き書式も自動設定する必要があります

 ちなみに、2番目の事例のサンプルファイルですが、こちらは複数のシートで「動く条件付き書式」を試せるようになっています

ぜひ、各種設定シートで「シート名」を変えながら試してみてください!

なお、コードについては非公開とさせて頂いていますので、予めご了承ください 

それでは、最後まで長文にお付き合い頂きありがとうございました!

 今回紹介した仕組みは、実務では更に工夫の余地があると思いますが、エクセルVBAならではの便利さが生み出せると思います!

少しでも、大きい表を扱う際の「イライラ」を消すことに貢献できたら幸いです

もし、質問や要望等がありましたら「お問合せ」からご連絡をお願い致します


にほんブログ村


にほんブログ村