タグ別アーカイブ: エクセル

裏技

【クラウド活用】Power BIのデータセットの内容をOneDriveのエクセルに自動転記

コメントをどうぞ

Power BIで作成した内容をエクセルで検証したい、もしくはエクセル活用したいというケースも多いと思います

しかも、クラウド上にて利用できるととても便利です

今回はPower BI Desktopからクラウドに「発行」した内容を、OneDriveのエクセルにPower Automateを利用して自動転記する仕組みを解説します

ポイント

Power Automate内で、対象となるデータセットを指定した上でDaxクエリを書くのがポイントです

基本的にはクエリの中身は、後述するようにPower BI DeskTop内で作成し、コピーするのが無難です

クエリの準備

クエリを作成するのに何をしたらいいかわからないかたは、基本的には「SummarizeColumns」関数を使うところから始めるのがよいと思います

SummrizeColumns関数により、必要な対象列を指定します

クエリのコピー

Power BI Desktopで作成したDAX式は、前述のようにPower Automateのアクションにコピーします

画像に alt 属性が指定されていません。ファイル名: image-12-644x296.png

ただ、EVALUATEと宣言するのを忘れないでください

クエリの出力内容の処理

クエリの出力内容はデータ操作のアクションで処理します

その後に、エクセルに転記します

転記内容は関数で前処理を行っておきます

＜まとめ＞

今回はPower Automateによりクラウド上のデータセットから、OneDriveのエクセルに転記する方法を解説しました

クラウドでデータを連携できると、データ活用の効率があがるので、ぜひご利用頂きたいと思います

尚、今回の仕組みだとPower Automateのフローを動かすとOneDrive上には、自動的にデータ行が追加されていきます

シナリオによっては、重複が起こらないようにデータを削除しておく必要があるかと思います

その時には、クエリを実行する前のフローに対して、繰り返しエクセル行を削除しておくフローを追加する必要があります

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

初級

データ取得～POWER BIでマイダッシュボード作成・初級_2/8～

コメントをどうぞ

【静から動へ！Power BIを活用して簡単・手軽にデータを分析可視化できるダッシュボードを作成しよう！】

前回、Power BIの魅力を紹介するのに使用したレポートを作成する方法を今回からシリーズで解説していきます

今回はPower BIの入り口である、データ取得について解説します

Power BIにとってデータはガソリンみたいなものです

ガソリンがなくてはPower BIは走れません

Power BIは全て「データ取得」というガソリン補給から始まります

インストール

データ取得の前に「Power BI Desktop」をインストールしておきましょう

クラウドのPower BIサービスとは別なので注意しておきましょう

インストーラーのダウンロードはこちらからです↓

Microsoft社HPより

使用データ

使用データは１つのエクセル・ファイルです

アイコン

PowerBI初級・取得データ

1 ファイル 32.91 KB
ダウンロード

エクセル・ファイルは３つのシートに分かれています

1つ目のシートは「売上データ」です

データの中味は次の４つです

a:日付、b:客先、c:商品、d:売上金額

２つ目のシートは「客先」です

e:客先、f:都道府県

最後のシートは「地域マスタ」です

g:都道府県、h:地域ブロック

３つのシートのデータ項目（a～h）は共通の項目があるので、次の図のように階層化できるようになっています

データの取得

インストールしたPower BI Desktopを開くと、データ取得の画面が出てきます

こちらをクリックすると、取得するデータの種類を選択する場面が出てきます

こちらで「Excel」を指定し、接続をクリックします

次に開いた画面で取得するファイルを選択します

その後は、取得するシートを指定します

今回の場合は、３つのシートを全て「チェックを入れて」指定します

その後は、ナビゲーター画面の下から「読み込み」ボタンを指定してください

＜まとめ＞

今回はPower BIの初動であるデータの取得について解説しました

データを取得されたら「少し遅いな」と感じると思います

これはPower BIではデータを取得したり更新したりする場合には、様々な処理が裏で走るからです

次回からその裏の処理も交えながら解説を行っていきたいと思います


にほんブログ村

M言語

PDF変換の応用～フォルダに入れた複数PDFから都合良くデータを切り取る方法

コメントをどうぞ

今回はフォルダに入れた複数PDFファイルから、一部の箇所だけデータを一括で取得します

扱うPDFファイルには、以下の画像のように振込先のデータが含まれています

このPDFファイルから振込先のデータだけを取得します

しかもPDFファイルは複数あり、行数が可変になっています

この処理のポイントは大きく分けて２つあります

ポイント

サンプルファイルの変換

今回の処理で主に作業するのは「サンプルファイルの変換クエリ」になります

複数ファイルの１つを変換するクエリを修正して、複数ファイル全てに変換ルールを適用します

M関数の組み合わせ

過去の記事で、見出し位置が不規則な複数ファイルからデータを取得する方法を解説しました

この際、上の画像の見出しをキーにしてList.PositionOf関数とTable.RemoveFirstN関数の組み合わせ「見出しの上の行」を削除しました

今回も同じようにList.PositionOf関数とTable.RemoveFirstN関数の組み合わせを使用します

・List.PositionOf関数 ➡ 指定文字列のList内の位置を取得

・Table.RemoveFirstN関数 ➡ テーブルから指定行の上の行を削除

フォルダから一括取得

まずは複数PDFファイルが格納されたフォルダからデータを一括取得します

「Fileの結合」画面ではテーブルではなく全ページを指定します

キーの設定

エディタで「サンプルファイルの変換クエリ」を開くと次の画像のようになっています

上の画像の黄色く印をした箇所の「お振込先・・・」以降の行から「備考」の上の行までが今回の取得対象です

「お振込先」の文字列をキーに設定した上で以降の作業を進めます

キーの抽出

キーとなる文字列「お振込先」の4文字を、列の追加タブ内の「抽出」メニューを使用して切り取ります

すると下の画像のように「お振込先」の文字列が抽出した列ができます

この「お振込先」の文字列より上の行を次から削除します

その前にステップ名は分かり易く変更しておきましょう➡Data2

キーの上の行の削除

ここからM関数を使用します

まずカスタム列作成画面にて、List.PositionOf関数を入力して「お振込先」の文字列がある行を特定します

すると10という数字が入った列ができます

「お振込先」の文字列は11行目ですが、M言語は０からはじまるので正しく設定できています

ここからTable.RemoveFirstN関数で上の行を削除します（-1などの調整は必要ありません）

上の画像の数式を文字列にすると次の通りとなります

Table.RemoveFirstN(Data2,List.PositionOf(Data2[最初の文字],”お振込先”))

この数式を作る際には、ステップ名をテーブル名に使用するのもポイントとなります

下の画像が新たに作成されたテーブルになります

テーブルが複数ありますが、必要なのは一つだけですので「行の保持」で余分なテーブルは削除します

テーブル展開

ここから作成したテーブルの展開処理を行います

まずはテーブル以外の列を削除します

この後、必要な列だけ展開します

この時点で大分、形になってきました

なお、この時点で最終アウトプットを行うクエリでエラーが発生しています

これはサンプルファイルの変換クエリで元ある列を削除しているからです

このエラーは各列の型式を変更する最終ステップを削除すれば消えます

では、変換クエリに戻ります

ここからは最終調整です

最終調整

上の画像のように、空欄や「備考」などの文字をフィルターで取り除きます

この状態から行列を入れ替えます

ここからは列名を整えるのですが、お振込先の文字は「列の分割」メニューで切り取っておきましょう

では、最終アウトプットのクエリを見てみましょう

うまくデータが複数作成できています

＜まとめ＞

今回は複数PDFファイルのデータから、キーのデータより上にある行を削除して必要な箇所だけ抽出しました

M関数のList.PositionOf関数とTable.RemoveFirstN関数をうまく組み合わせれば、意外と簡単に行えます

今回は、抽出データの下にあるデータはフィルタしただけで済みましたが、実際には複雑なパターンもあるかもしれません

その際は過去記事で紹介した「インデックス列と余り、減算の算出」の組み合わせが有効かもしれません

あわせて覚えておいていただけると幸いです

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

裏技

グラフデータを非表示にしてもグラフは表示する方法

コメントをどうぞ

グラフを人に見せる際にデータを見せるのは格好悪いと思い、データを非表示にしたらグラフの中味も消えてしまった経験はないですか？

グラフは本来、表示されているデータを元にして作成されるので、元データを非表示にするとグラフの中味は消えてしまいます

ところが、エクセルは便利にできているので、回避する方法がちゃんとあるのです

非表示および空白のセル

グラフデータが消えてしまったグラフ上で右クリックをすると、「データの選択」が見えます

こちらをクリックすると「非表示および空白のセル」という表示が下に見えます

こちらをクリックすると別なダイアログボックスが表示されます

ダイアログボックスの内容は下の画像のようになっています

こちらで「非表示の行と列のデータを表示する」にチェックを入れてください

これで元通りに表示されます

今回は短いですが以上です

最後までお付き合い頂きありがとうございました

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

power-automate-desktopアクション集

Power Automate Desktop逆引き辞典～Excel~

コメントをどうぞ

Microsoft社の無料RPA・Power Automate Desktopのアクションの中から、Excelのアクションを紹介します

　➡逆引き辞典に戻る

ファイルを開く

アクション名称：Excelの起動

新規や既存のエクセルファイルを開けます

パラメーターの選択

Excelの起動

新規のファイルと既存のファイルの両方を指定できます

次のドキュメントを開く➡ドキュメントパス

開く既存のファイルを指定できます

インスタンスを表示する

開いたファイルの表示・非表示を指定できます

セルの値の抽出（単一セル）

アクション名称：Excelワークシートからの読み取り

　上記のようにアクションを設定した場合には、開いたファイルの「選択したシート」の「3列目」「3行目」のセルの値を抽出して、変数/ExcelDataに格納します

ちなみに、開いたファイルの選択したシートは次の画像の内容です

パラメーターの選択

Excelインスタンス

通常は開いたファイルが自動設定されます

下の画像はファイルを開いたアクションの画像です

「生成された変数」はExcelinstanceにて設定されています。こちらの変数が自動設定されます

取得

単一のセルの値を設定します

先頭列・先頭行

セルの位置の列と行を設定します

サンプル

実際に使用する時には1行目のフォルダ指定を変えてください

アイコン

セルの値の抽出

1 ファイル 0.20 KB
ダウンロード

セル範囲の抽出（複数セル）

アクション名称：Excelワークシートからの読み取り

上記のように設定した場合には、2列目2行目のセルから3列目8行目までのデータを抽出します

抽出した内容は変数/ExcelDataに格納されます

パラメーターの選択

Excelインスタンス

セルの値の抽出（単一セル）参照

取得

「セル範囲の値」を設定します

先頭列～最終行

取得する範囲を設定します

詳細➡範囲の最初の行に列名が含まれます

選択した範囲の1行目を見出しにする場合は「オン」にします

セルへの入力

アクション名称：Excelワークシートに書き込み

パラメーターの選択

Excelインスタンス

セルの値の抽出（単一セル）参照

書き込む値

セルに入力する値を設定します（上の画像では変数を入力値に設定しています）

書き込むモード

入力するセル位置を指定する方法か、カーソルがある位置に入力する方法いずれかを指定できます

列～行

書き込むモードを「指定したセル上」と指定した場合にセル位置を設定します

セル範囲の選択

アクション名称：Excelワークシート内のセルの選択

パラメーターの選択

Excelインスタンス

セルの値の抽出（単一セル）参照

選択

通常は絶対位置で指定したセルで問題ありません。もう一つ相対位置で指定する方法があります（エクセル関数のOFFSET関数と同じ様な機能です）

先頭行～最終行

セル範囲を設定します

サンプル

アイコン

セル範囲の選択

1 ファイル 0.31 KB
ダウンロード

新たなワークシートの挿入

アクション名称：新しいワークシートの追加

パラメーターの選択

Excelインスタンス

セルの値の抽出（単一セル）参照

新しいワークシート名

挿入するワークシートの名称を設定します

名前を付けてワークシートを追加

新たなワークシートを既存のワークシートの後に挿入するか、前に挿入するかを設定します

サンプル

アイコン

新たなワークシートの挿入

1 ファイル 0.21 KB
ダウンロード

作業するワークシートの選択

アクション名称：アクティブなExcelワークシートの選択

パラメーターの選択

Excelインスタンス

セルの値の抽出（単一セル）参照

次と共にワークシートをアクティブ化

選択するワークシートを「名前：シート名」か「左からの位置：インデックス」で指定するかを設定します

ワークシート名/ワークシート インデックス

名前で指定した場合には「シート名」、インデックスで指定した場合は「左からの位置」を設定します

サンプル

アイコン

作業するワークシートの選択

1 ファイル 0.25 KB
ダウンロード

セルへの値の書込み

アクション名：Excelワークシートに書き込み

下のGIF画像では、ダイアログボックスに入力した値をエクセルシートの2行目2列目に入力しています

パラメーターの選択

Excelインスタンス

セルの値の抽出（単一セル）参照

書き込む値

変数を選択、もしくは直接値を設定します

書き込むモード

「指定したセル上」もしくは「現在のアクティブなセル上/カーソルの選択位置」を設定します

＊「指定したセル上」を選択した場合には下の欄でセル位置を指定します

列

値を書き込むセルの列位置を設定します

行

値を書き込むセルの行位置を設定します

サンプル

アイコン

セルに値を書き込む

1 ファイル 0.30 KB
ダウンロード

最終行＋１行の行位置取得

アクション名：Excelワークシートから列における最初の空の行を取得

　下のGIF画像では、このアクションでA列の最終行＋1行の行位置を取得して変数に格納し、メッセージボックスで変数の値を表示しています

パラメーターの選択

Excelインスタンス

セルの値の抽出（単一セル）参照

列

最終行+1行の位置を取得する列を設定します

サンプル

アイコン

最終行＋1行の行位置の取得

1 ファイル 0.50 KB
ダウンロード

最終行＋１行、最終列＋１列の行列・位置取得

アクション名：Excelワークシートから最初の空の列や行を取得

最終行＋１行の位置取得を参照

パラメーターの選択

Excelインスタンス

セルの値の抽出（単一セル）参照

Excelマクロの実行

このアクションでは、エクセルファイル内のマクロを「マクロ名」で指定して実行することができます

マクロ実行時には、マクロに引数を渡すこともできます

下のGIF画像ではダイアログボックスに入力されたメッセージをまず変数：UserInputに格納しています

その後、エクセルファイルを起動した後に、メッセージを表示するマクロ（マクロ名：test）を実行します

その際に、マクロ名：testに引数/UserInputを渡しています

メッセージ表示しているのは、RPAでダイアログボックスに入力した変数の内容です

上のGIF画像で実行したマクロは以下の画像の内容になっています

パラメーターの選択

Excelインスタンス

セルの値の抽出（単一セル）参照

マクロ

マクロ名;引数1;引数2・・・の形で実行するマクロと引数を指定できます

サンプル

ファイル位置を指定し直す必要があります

*テキスト/RPAコードと実行するマクロを含むエクセルファイルを添付しています

アイコン

Excelマクロの実行

1 ファイル 0.38 KB
ダウンロード

アイコン

Excelマクロの実行

1 ファイル 111.73 KB
ダウンロード

にほんブログ村 資格ブログ ビジネススキルへ

にほんブログ村

にほんブログ村 IT技術ブログ VBAへ

初級

データ変換～POWER BIでマイダッシュボード作成・初級_3/8～

コメントをどうぞ

【静から動へ！Power BIを活用して簡単・手軽にデータを分析可視化できるダッシュボードを作成しよう！】

前回はPower BI Desktopにデータを取得しました。今回はデータ変換を３つ行います（変換種類は２つ）

データ変換は、Power BI Desktopのレポート画面からPower Queryエディタ（以降エディタ）を開いて行います

本題に入る前に、Power BI Desktopの３つの画面とデータ変換内容について簡単に触れます

各画面の内容とデータ変換内容

Power BI Desktop画面は次の３つの画面から構成されます

・レポート画面

・データ管理画面

・リレーション管理画面

レポート画面

Power BI Desktopにデータを取得した時に一番最初に出てくるのがレポート画面です

データ管理画面

画面一番左に出ている３つのアイコンの真ん中をクリックすると、画面がデータ管理画面に遷移します

こちらで取得したデータの中味が確認できます

ここからデータ変換内容について触れます

ヘッダー

下の画像のように、取得した「地域マスタ」「顧客」は1行目をヘッダーに昇格する必要があります

抽出

売上データについては「商品」列にて、記号：＿の後の文字を抽出して「ABC」「DEF」という風に商品名を変更しましょう

リレーション管理画面

こちらについては、次回の記事で触れたいと思います

エディタでデータ変換

エディタ画面は、Power BI Desktopのホームタブから下の画像の「黄色の印の箇所」からクリックして開きます

ヘッダー

「地域マスタ」と「顧客」データについては、データの1行目をヘッダーとして使用しますので、変換タブから「1行目をヘッダーとして使用」を選択します

抽出

「売上データ」については商品列を変換しますので、変換タブの「抽出」を選択します

次に表示された画面にて、区切り記号を「＿」で指定します

変換後の商品列は次の様になっています

適用処理

通常のエディタと処理が違うのがここです

通常のエディタであれば読込処理を行いますが、Power BIでは「適用処理」となります

変換処理が終了したらこちらの処理を忘れないようにしてください

＜まとめ＞

今回は、Power BI Desktopの画面からエディタ画面を開いて３つのデータ変換を行いました

Power BI Desktopにてデータを取得した際、通常のパワークエリと同様に型式の変換が行われています

　データ取得をした際にこの型式の変換が走る点も、データを取得する際に「ちょっと処理が遅いな」と感じる要因の一つです

本来であれば、上記の３つの変換処理をエディタで行った後、そのままエディタ画面で「地域マスタ」「顧客」「売上データ」の３つのクエリをマージしたいところです

こちらについては次回解説を行いたいと思います


にほんブログ村

初級

リレーション作成～POWER BIでマイダッシュボード作成・初級_4/8～

コメントをどうぞ

【静から動へ！Power BIを活用して簡単・手軽にデータを分析可視化できるダッシュボードを作成しよう！】

今回は異なるデータ間にリレーションを作成して、一緒の仲間として有効活用できるようにします！

この初級編では最終的には、次のようなレポート/ダッシュボードを作成します

一番左上のグラフは、異なるデータを組み合わせて作成しています

元データはこちらの２つのデータです

グラフの項目である「地域ブロック」と「売上金額」は、元々のデータソースは分かれています

何故、１つのグラフで一緒に使えるかというと前述のリレーションを作成しているからです

では、本題に入っていきましょう

リレーション管理画面

リレーションの作成はリレーション管理画面で行います

但し、リレーションが自動で作成されていることもあります

データを取得した時に「少し遅い」と感じるのは、自動でリレーションを作成しているからです

ですので、リレーションを作成するのは自動で作成されていない時になります

作成方法１

ドロップ・アンド・ドラッグでリレーションを作成することができます

　作成した内容は、リレーション線を右クリックすると「プロパティ」が表示されますので、そちらをクリックすると確認できます

確認画面はパワークエリのマージ画面と一緒です

作成方法２

もう一つの方法は「リレーションシップの管理」から作成する方法です

上の文字をクリックすると、次のような画面がでてきます

こちらで「新規」のリレーションを作成することができます

＜まとめ＞

今回は「リレーション」を作成する方法を解説しました

こちらの処理を行うと、異なるデータを一緒に取り扱うことができます

ですので、VLOOKUP関数を使う必要もなくなります

但し、これだけではレポート作成の工程に移ることはできません

下の画像を見て下さい

地球儀やΣマークが付いています

本格的なレポート/ダッシュボード作成前に、次回の記事で詳細に解説を行いたいと思います


にほんブログ村

初級

グラフ作成～POWER BIでマイダッシュボード作成・初級_5/8～

コメントをどうぞ

【静から動へ！Power BIを活用して簡単・手軽にデータを分析可視化できるダッシュボードを作成しよう！】

これまでこの初級編で「データ取得」「データ変換」「リレーション作成」を行いました。ここから実際にグラフなどのコンテンツを作成していきますが、まだもう少し準備作業があります

データ管理画面での準備作業

　レポート画面にて各データをどのように使うかどうかについては、これから解説するデータ管理画面の「列ツール」で登録を行う必要があります

地域

　今回の初級では、下の画像のような都道府県別の地図グラフも作成しますので「列ツール」で登録が必要な事項があります

この地図グラフは都道府県の区分データを使用します

　この場合は上記の画像のように、このデータは「都道府県の区分データとして使用します」ということをプロパティ画面で登録する必要があります

こちらの作業はデータ管理画面の列ツールで行えます

プロパティ画面は分かりにくいですが「・・・」をクリックすると表示されます

合計処理等

まさにこの辺りはピボットテーブルと同様なのですが、集計方法を必要に応じて選択することができます

桁区切りなどもこの列ツールで行えます

グラフ作成

ではデータの方の準備が整ったので、実際にレポート画面でグラフを作成します

視覚化の箇所で横棒をクリックしてみてください

すると、次の様なボックスが表示されます

一方で「視覚化」の下に、データを配置する画面の表示も見えるはずです

上の画面の黄色の印の箇所に、データを配置します

グラフの表示・調整

ここからが特にエクセルでのグラフ作成と違う箇所です

今回はデータラベルの表示だけを行ってみます

グラフの表示調整は、データを配置した箇所の表示を切り替えて行います

上の画像の画面を下にスクロールしていくと、データラベルの表示があります

こちらをオフからオンに変えましょう！

すると、データラベルが表示されます

＜まとめ＞

　今回は、データ管理画面の「列ツール」でグラフを作成するための必要な登録を行った後、グラフを実際に作成しました

改めて感じるのは、Power BIの機能の多さです

Power BIを触りはじめて最初は戸惑うかもしれません

でも機能が多いということは「便利」ということなのです

基本的には直感的な操作で処理が行えるようになっているので、ぜひ手を動かして慣れていきましょう！


にほんブログ村

実践的マクロ・レシピ集

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

コメントをどうぞ

【カオスな現場経験 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桁）の枝番にします

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

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

＜まとめ＞

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

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

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

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


にほんブログ村

無料アドイン

２つのシート/ファイルを詳細に比較～INQUIRE～

コメントをどうぞ

　エクセル作業で「辛抱」を要する作業の一つにシート間の検証があります。今回、紹介するアドインであればワンクリックで比較が詳細に行えます！

エクセルで作業をしていると、シート間でどこが変わっているのか？そしてどのように変わっているのか？この検証作業が重要になることがあります

数式を膨大に入力して検証しても、数式自体がミスしていることもありますので、この検証作業はかなり神経を使います

実は、無料のアドインでこの検証作業は一瞬で終わってしまいます

エクセルの進化は本当にすごいですね

今回はこの検証作業を一瞬で行うアドイン「INQUIRE」を紹介します

アドインのインストール

今回のアドインは「挿入タブ」ではなく「ファイルタブ」からインストールします

ファイルタブをクリックし、次に表示された「その他」の箇所からオプションボタンをクリックします

オプションボタンをクリックした後に開いた画面では、「アドイン」をクリックしてください

「COMアドイン」からインストールしますので、下の画像の「COMアドイン」をクリックした後に「設定」をクリックします

その後、「Inquire」にチェックを入れます

すると、エクセルの画面上に「検査」というタブが表示されます

検査の実施

準備

まずは比較を行うファイルを開いておきましょう

検査アドインの起動

検査タブから「ファイルの比較」をクリックします

すると、下の画像のような画面が開きます

比較するファイルの内容を確認した後、「比較」ボタンをクリックします

しばらくすると下の画像のような画面が開きます

こちらが検査結果の画面になります

検査内容の確認

検査結果の画面は左上が修正前、右上が修正後になっています

そして、色が付いているところが変更が行われている箇所になります

色は緑とパープルに分かれていますが、色の違いにきちんと意味があり、変更方法と紐づいています

画面右下を見ると、変更方法を確認できます

緑はセル入力、パープルは数式になっています

つまり、変更前のファイルからセルを参照した後、一部のセルは入力により数値を変えたという事まで分かるのです

変更した内容については、画面下の真ん中でより詳細に分かるようになっています

検査内容のエクスポート

検査内容については、下の画像の箇所からエクセルファイルにエクスポートできます

下の画像はエクスポートした内容になります

＜まとめ＞

今回は２つのファイルを比較するアドインを紹介しました

本来であれば、膨大な作業が必要な内容ですので、このアドインを知っているだけで事務作業は格段に効率化されます

このブログでは他にも便利な無料アドインを紹介していますので、興味のある方はぜひ記事をご覧になってみてください

なお、今回のアドインを使えるバージョンは次の通りとなっております（出典：Microsoft社HP）


にほんブログ村