タグ別アーカイブ: excel

OneDriveのフォルダ内の全てのファイル・シートを自動変換する

今回はクラウド上のエクセルファイル内で使える、オフィススクリプト(旧VBA)とPower Automateの組み合わせで自動化処理を行います

やりたい事

OneDriveのフォルダに入れた「すべて」のエクセルファイルの「すべて」のシートを、事前に決められた内容に従って変換処理を行うことです

上の画像では、B2セルの①フォント文字を強調、②背景の色を黄色に、の2種類の変換が行われています

この①②の処理をすべてのファイル、すべてのシートで行います

ポイント

読者の皆様の中には、エクセルの記録マクロを使用した方が多くいらっしゃると思います

今回はエクセルマクロのクラウド版、オフィススクリプトで記録した内容を修正して活用します

修正した内容は、Power Automate/クラウド版RPAで動かします

Power AutomateでOneDriveのあるフォルダから全てのファイルの内容を取得し、エクセルファイルのみを選別して前述のオフィススクリプトを動かします

オフィススクリプト

記録操作

オフィススクリプトは「自動化」のタブから行います

自動化のタブをクリックすると「操作を記録」が行えるようになります

「操作を記録」を押した後に①フォント文字を強調、②背景の色を黄色、を行い「停止」を押します

「停止」を押すと、記録されたコードが「編集」から見れるようになります

コードで注目して頂きたいのは2つの点です

一つ目は「波括弧」です

{と}の間にB2セルを選択した処理が2つ書かれています

特に注目して頂きたいのが2点目です

こちらの「let」です

こちらはプログラミングの「変数」と同じ役割をします

「workbook.getActiveWorksheet()」すなわちファイル内の「アクティブ=選択・処理しているシート/単数」を「selectedSheet」に当てはめています

ですので、次に続くコードはselectedSheetすなわち「選択・処理しているシート/単数」 の 「getRange(“B2”)=B2セルの内容」 「getFormat=書式」 の 「getFont=フォント」 を 「setBold(true)=太くする」になります

次はこのletで設定する変数と、変数を使用していたコードの内容を変更します

コードの変更

この時点では、letで設定する「selectedSheet」は選択されているシートのみが対象になります

こちらを次のように変更します

「workbook.getWorksheets()」とすることで、ファイル内の全てのシート(sがつく複数形)にします

そして、次に取得したシートの全てに前述の①②の処理が行えるように修正を行います

要は繰り返し処理を行うのですが、繰り返し処理の構文*は次の通りとなります

*こちらのページのコレクションの箇所になります

for(let 変数2 of 変数1){

・繰り返し処理する内容1

・繰り返し処理する内容2

}

こちらの内容は英語の「All of ~」と同じになります

All of ~とすることで~の内容の全てが対象になります

この仕組みと同じです

ですので、letで取得したSheetsの全ての各シート:Sheetを変数2として設定します

ここで、では「Sheets」で設定した内容と「Sheet」の内容とは何が違うのか?と疑問に思われる方もいらっしゃると思います

対象は同じですが、Sheetsの方は複数形で全体そのものを指しています

一方、Sheetの方はあくまで全体の構成要素の一つ一つを指します

では、コードの修正の最後として「selectedSheet」を「Sheet」に変更します

こちらは「Ctrl+H」によって一気に変更することも可能です

コードの修正が完成したらコードの保存を行い、名称(allSheets)を付けます

Power Automate

Power Automateでは、フォルダ内にある全てのファイルを取得しつつ、各ファイルに対してオフィススクリプトを実行します

Power Automateではシナリオを実行するトリガー(起動条件*)をまず最初に選ぶのですが、今回は「インスタントクラウドフロー」すなわち手動でトリガーを選択します

*メールが届いた時、〇時に起動などを本来は選択できます

次にフロー名を付けて「手動でフローをトリガーします」を選択します

次の画面にて新しいステップを追加します

そして「OneDrive」を検索します

次にOneDrive関連のアクションから「フォルダー内のファイルリスト」を選択します

「フォルダー内のファイルのリスト」のアクションでは、該当のフォルダを設定します

この「フォルダー内のファイルのリスト」アクションにより、該当フォルダ内のファイルが一覧になります

ファイルの一覧は後述する「ID」という形で次のアクションに受け渡します

これで2つアクションが設定できました

次は「スクリプト(オフィススクリプト)の実行」アクションを設定します

スクリプトの実行アクションでは、前述のIDを指定します

上のGIF画像では、ファイル欄にIDを設定した途端に「Apply to each」というものが適用されています

これはeach/それぞれのファイルにスクリプトを実行するという意味になります

スクリプトは前述の「allSheets」を選択します

付け加えてスクリプトを実行するファイルは、拡張子が「.xlsx」に限定できるようにします

この限定作業には「コントロール」から「条件」を使用します

次に展開する画面で実際の条件を指定します

条件を設定したら、「スクリプトの実行」アクションは「はい」の下に移動します

これで「フォルダーから全てのファイルを取得」⇒「拡張子が.xlsxのファイルにてスクリプトを実行」のシナリオが完成しました

<まとめ>

今回はオフィススクリプトとPower Automateを組み合わせ、フォルダ内のエクセルファイルの全てのシートを変更する方法を解説しました

これは従来のVBAやRPAでは行えない処理です

クラウドの醍醐味がある処理だと思います

ぜひ試してみてください

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

にほんブログ村

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

【クラウド活用】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へ

お仕事の依頼

 実務を担う現場の方が使い慣れたエクセル。Excellent仕事術は、このエクセルを効果的に活用して、御社の業務改善やお困りごと解決の最適なスキームをご提案/ご提供します!

御社にこんな悩みはございませんでしょうか?

エクセル作業がブラックボックス化しており、膨大な人手がかかっている

分析資料は沢山あるが、業績改善に向けた課題や傾向が全く把握できない

プレゼン資料にどこかインパクトが無い

 Excellent仕事術では、これらの悩みに対して「現場重視」で現場の声を反映させながら解決策を提案します!

 ご相談自体は無料でうけたまわります。エクセルに関することであれば、どんなことでも幅広く「Excellent仕事術のガッツ鶴岡」へご相談ください!

プロトタイプ(試用版)の開発についても低価格で請負ます!

お問合先

Excellent仕事術の得意分野

VBA/マクロ開発

エクセル作業がブラックボックス化する理由は、大きく分けて3つあります。

エクセルシートのスパゲティ化

データのバケツリレー

エクセルファイルの共有

 Excellent仕事術では従来のIT部門では対応できなかったこの3つの問題を、これまで培ってきたマクロ/VBA開発力で解決します。

エクセルシートのスパゲティ化

 エクセルシートのスパゲティ化は、1シートに「データ」「計算」「作業スペース」を詰め込むことで起こります。

 異なる内容が詰め込まれている為、「どこの範囲がどの内容」で、「どの範囲とどの範囲がどのように絡み合っているのか?」が第3者に分かりにくくなります。

 スパゲティ化したエクセルシートは、作成した担当者しか扱えないだけでなく、データが不規則に蓄積されているため有効活用ができず効率化が行えません。

 スパゲティ化したエクセルシートを改善するポイントは、「データ」「計算」「作業スペース」を1シートに混ぜず、それぞれシート毎に集約することです。

 Excellent仕事術ではスパゲティ化したエクセルシートを、3つのステップで劇的に改善します。

・Firstステップ

   ➡ ヒアリング及びシートの中味の分析

・Secondステップ

   ➡ シートを「データ」「計算」「作業スペース」などの目的別に作成

・Thirdステップ

   ➡ 作業スペース・シートでの作業をVBAにより自動化

 改善したエクセルシートでの作業は効率化されているだけでなく、特定の担当者以外でも引継ぎの負担なく作業が行えるようになります。

データのバケツリレー

 エクセルファイルでデータを外部の会社や他部署とやり取りすることは、良く行われていると思います。

 データ活用の目的が外部の会社や他部署と違う為、「エクセルファイルの交換」はファイルやシート間での膨大な「データの転記処理」を発生させることになります。

 実は、VBA/マクロを活用すれば複数のファイルやシートの「データ取込み・変換処理」 を、一括で行うことができます。

一括処理のイメージ

 一括で「データ取込み・変換処理」が行えれば、膨大な転記処理から解放されるだけでなく、取り込んだデータを活用して「周辺業務」の効率化も行えるようになります。

 Excellent仕事術では、現場の声をお聞きして「データ取込み・変換処理」から「周辺業務の改善」まで徹底的にサポート致します。

エクセルの共有

 エクセル1ファイルで大量のデータを扱う場合によく出てくる問題として「共有」の問題があります。

 エクセルは表計算ソフトであり、本来は大量のデータを扱うには不向きです。1シートに蓄積できる行数に制限があり、蓄積データが大量だと動きが重たくなります。

 しかも、 1ファイルを複数の担当者で同時に共有して使用できない為、担当者に待ち時間が発生してしまい、どうしても業務効率が上がらなくなります。

 Microsoft社の製品では、データベースソフトとして「Access」があります。Accessであれば、行数に関係なく大量のデータを扱うことができ、複数の担当者で共有して使うことも可能です。

 ところが、Accessだと現場では行えない業務も中にはあります。特に「大きなクロス表での作業」などは、エクセルのような表計算ソフトを業務用にカスタマイズした表でないと効率があがりません。

 こういったエクセルでないと行えない業務を、複数担当者でエクセルファイルを共有しながら作業したい場合には抜け道があります。

エクセルとAccessをVBA技術により連携して使うという方法です。

 この方法であればエクセルとAccess、互いのメリットを組み合わせて業務を行うことが可能です。

 このエクセルとAccessn連携する仕組みでは、データはAccessに一元管理し、エクセルには都度、必要なデータだけ呼び出して活用します。

 このエクセルとAccessの連携を行う場合にポイントとなるのは「エクセルの役割」と「Accessの役割」の切り分けです。

 Excellent仕事術では、連携の仕組みを構築するだけでなく、業務を理解させて頂いた上で「連携のポイント」についても提案させて頂きます。

事例①

データ分析/統計処理

「数値/分析資料は沢山あるが、業績改善に向けた課題や傾向が全く把握できない」ことの主な原因は、鳥の目全体像を捉えることができていないことが考えられます。

 例えば「前期と比べて注文単価が何故下がったか?」について、注文単価自体の推移を見ても、単位荒すぎて「何故下がった」かはよく分からないはずです。

 逆に注文台帳を見ても、今度は単位細かすぎて「注文単価が前期と比べて何故下がったか?」はよく分からないはずです。

 では下に記述したように、注文を単価別にグループ分けして件数の推移を確認した場合はどうでしょう?

・低単価の注文件数の推移

・中単価の注文件数の推移

・高単価の注文件数の推移

 低単価の注文内容と高単価の注文内容では、注文に含まれる商品の違いや、注文した会員の行動や属性に違いが必ずあるはずです。

 低単価の注文件数の構成が高くなっているのか、もしくは高単価の注文件数の構成が低くなっているのか、を注視すれば「注文単価が下がった原因」はある程度見えてくるはずです。

 このように業績改善に向けた課題や傾向を捉えるには、必ず鳥の目でグループ別の推移を捉えることが必要です。

 このグループ別の推移を捉えるのに、必ずしも「データアナリスト」や「CRMソフト」が必要とは限りません。エクセルの集計機能と統計機能を駆使すれば、エクセルでも対応は可能です。

 Excellent仕事術では、これまで培ってきたエクセル集計技術と統計処理・技術を活用して、御社の数字分析のお手伝いをします。

会員分析

会員分析のポイントは大きく分けて3つあります。

・新規会員の獲得状況 ➡会員数自体の分母を増やせているか?

・新規会員の2回目のリピート状況 ➡新規会員が定着しているか?

・既存会員のリピート状況 ➡優良会員が確実に育成できているか?

 つまり、まずは「新規・既存」と「優良・非優良」の2つの切り口で会員のグループ分け(セグメント)を行った上で、会員のグループ間の移行状況を把握することが必要です。

 Excellent仕事術では上記の2つのグループの切り口から、更に深堀した切り口でグループ分けを行います。

そして、基本の「新規・既存」「優良・非優良」のグループに加えて、更に深堀りしたグループ間の移行率を把握するためのツールを提供します!

事例②

その他の分析

 Excellent仕事術では、発注・在庫分析や予算作成・中期計画のシミュレーションなども得意分野としております。

ぜひ、この分野についての悩み事もExcellent仕事術にお問い合わせしてみてください!

ダッシュボード化

 数字分析を行った内容については、社内で共有し、次の行動につなげていくことも大事なことです。

 Excellent仕事術では社内の効率的な業績報告、情報共有のために一目で現状を掴むことができるダッシュボードの作も行います 。

エクセルダッシュボード

プレゼン・資料作成

 競合他社とのプレゼン競争に勝つためには、「数字の魅せ方」に突破口があります。海外のエクセル活用事例をうまく取り入れた「数字の魅せ方」こだわりましょう。「数字なんてただの数でしょ?」。そんなことはありません。「目で見て、数字の変化がリアルにわかる」効率的な資料作成術を、Excellent仕事術/ガッツ鶴岡がご提供します

-シンプルで誰の目も釘付けにするグラフ関連資料の作成をいたします。

Excellent仕事術/ガッツ鶴岡の強み

業務分析の経験が豊富

-上場企業から創業期のベンチャー企業まで、様々な事業規模での業務分析の経験が豊富

-内部監査も含めた内部統制対策をとおして、業務フロー図等の作成・業務分析の経験多数あり。

数値管理の経験が豊富

-銀行や証券会社等の金融機関への数値説明・交渉の経験が豊富

-EC通販での予算管理や中期計画作成、各種分析などの経験が豊富

最新のエクセル技術

 Excellent仕事術/ガッツ鶴岡は、世界の最先端のエクセル技術を常にキャッチアップしています!

-海外では日本と違い、エクセルの新機能/便利機能をうまく使いこなして業務の効率化に成功している事例が沢山あります

-小難しいVBAコードや複雑な関数は必要ありません。Excellent仕事術/ガッツ鶴岡がお教えする世界最先端のエクセルの新機能を使って、効率よく簡単にエクセル作業ができるようになります。

-エクセルは元々、表計算ソフトです。データベースソフトではありません。しかしパワークエリという新機能を使えば、エクセルをデータベースソフトとしても使いこなせます

 Excellent仕事術のガッツ鶴岡がパワークエリとの連動を作成します/お教えします。

事例①

<商品管理システム/エクセルとAccessの連携>

 業務メンバー各自が業務に即したエクセルのクロス表を使用しながら、エクセルへの入力データはAccessファイルに蓄積して各自で共有する仕組みを構築

<改善前・課題>

★1つのエクセルシートに様々な業務データと入出力表が混在

・商品の特性上、入荷した商品を手動で分割・並べ替えて管理する必要があった

・商品の並べ替え順を各業務で使い回すため、1シート上に様々なデータが混在

データ蓄積型式が不規則になり、VBAなどの自動化手段の活用が不可能

・1ファイルをメンバー全員で共有して使用するため、業務メンバーに待ち時間が発生

<改善後・効果>

★業務データはAccessに一元管理し、エクセルの入出力画面を業務毎に分散

・データはAccessに一元管理し、ADOという技術を使用して、都度、必要なデータのみエクセルファイルに呼び出す仕組みを構築。このADOの技術により、1つ1つの業務に即した画面作成が可能になった。さらに他の業務メンバーがファイルを使っている間に発生していた待ち時間も削減できた。

・Accessに一元管理したデータを、RPAに連携して会計システムへの入力を自動化した。これにより、残業時間が1月あたり200時間減らすことに成功

事例②

EC通販における会員のセグメンテーション>

 会員の1年間の購買履歴とアンケートの集計結果を分析し、会員のセグメンテーションを実施。それまで一律にマーケティング施策を行っていたが、会員のセグメント毎の施策(例:セグメント別の割引率設定など)を実施できるようになった

実績

-商社・畜産部門向け商品管理システムの開発

 ➡エクセルとAccess及びRPAと連携処理

-客先マスタ登録業務用システム開発/エクセル・RPA連携

 ➡エクセルVBAにてRPA入力用データを成型

-EC通販会社での会員分析・マーケティング施策立案

 ➡エクセル分析ツールにて会員の購買履歴を統計処理にて分析

-EC通販会社向け在庫管理システム

 ➡適性発注率をエクセルを使用して分析

ストリートアカデミーで講師もしています

経歴

Excellent仕事術運営者:鶴岡 敦(ガッツ鶴岡)

 新卒で総合商社に入社後、ユニクロに転職。更にEC通販会社に経営企画室長として入社。

 EC通販会社にて膨大なデータ量に苦戦。必要に迫られて自らエクセル学習を重ねるうちに、 海外の活用例などを知るようになり、エクセルの可能性に目覚める

1993年慶應義塾大学商学部卒

2002年USCPA合格

大学在学中にプロボクサーとしてデビュー

現在もボクシングジムで練習を続けている

 

 

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

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

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

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

非表示および空白のセル

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

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

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

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

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

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

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

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

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

にほんブログ村

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

Power Automate Desktop逆引き辞典~Excel~

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

 ➡逆引き辞典に戻る

ファイルを開く

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

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

パラメーターの選択

Excelの起動

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

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

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

インスタンスを表示する

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

セルの値の抽出(単一セル)

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

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

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

パラメーターの選択

Excelインスタンス

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

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

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

取得

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

先頭列・先頭行

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

サンプル

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

アイコン

セルの値の抽出 0.20 KB 5 downloads

...

セル範囲の抽出(複数セル)

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

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

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

パラメーターの選択

Excelインスタンス

セルの値の抽出(単一セル)参照

取得

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

先頭列~最終行

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

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

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

セルへの入力

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

パラメーターの選択

Excelインスタンス

セルの値の抽出(単一セル)参照

書き込む値

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

書き込むモード

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

列~行

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

セル範囲の選択

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

パラメーターの選択

Excelインスタンス

セルの値の抽出(単一セル)参照

選択

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

先頭行~最終行

セル範囲を設定します

サンプル

アイコン

セル範囲の選択 0.31 KB 3 downloads

...

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

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

パラメーターの選択

Excelインスタンス

セルの値の抽出(単一セル)参照

新しいワークシート名

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

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

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

サンプル

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

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

パラメーターの選択

Excelインスタンス

セルの値の抽出(単一セル)参照

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

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

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

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

サンプル

セルへの値の書込み

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

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

パラメーターの選択

Excelインスタンス

セルの値の抽出(単一セル)参照

書き込む値

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

書き込むモード

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

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

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

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

サンプル

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

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

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

パラメーターの選択

Excelインスタンス

セルの値の抽出(単一セル)参照

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

サンプル

最終行+1行、最終列+1列の行列・位置取得

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

最終行+1行の位置取得を参照

パラメーターの選択

Excelインスタンス

セルの値の抽出(単一セル)参照

Excelマクロの実行

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

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

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

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

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

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

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

パラメーターの選択

Excelインスタンス

セルの値の抽出(単一セル)参照

マクロ

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

サンプル

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

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

アイコン

Excelマクロの実行 111.73 KB 3 downloads

...

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

にほんブログ村

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

M言語に慣れる_3回目~ダイナミックフィルタリング~

M言語は難しくない!】今回は、数式バーを編集して「ダイナミックフィルタリング」を行ってみましょう!。ここで言う「ダイナミックフィルタリング」とはフィルタリングの値を固定せず、元データの追加や修正に応じてフィルタリングの値を自動で変動させていくものです。

例えば、受注金額・全体の「最高値」などは毎月、変動していくはずです

 ダイナミックフィルタリングでは、例えば「全体最高値」の半額未満だった担当者のリストを、毎月自動で抽出を行えるようにします

 今回のダイナミックフィルタリングを行うにあたっては、M言語を一から作成するといったことはしません

 あくまで、Power Queryエディタ(以降エディタ)にて、既に数式バーに記録されているM言語を修正するだけです

マクロ作成で言えば、記録マクロ修正するような内容です

記録されているM言語を修正するだけでも、かなりの内容が行えることを肌で感じて頂ければ幸いです!

目次

今回のポイント

今回使用するデータと行いたいこと

演習1/受注金額1位の担当者を抽出

演習2/最高値の半分未満の担当者抽出

<まとめ>

今回のポイント

ステップ名変更

実は、エディタのステップ名は自分好みに修正していけます

今回の内容とは直接関係ないですが、ステップ名を変更していけると、後々、M言語の管理がとても楽になります

参照ステップ

 前々回、ステップをスキップして参照することで「参照ステップ」を作成しました

前々回は平均値を参照しましたが、今回は「最高値」を参照します

 クエリを更新する毎に、参照ステップの「最高値」も更新してフィルタリングの値とします

数式バーへの参照ステップ組み入れ

一度、数式バーにてダミー値でフィルタリングを行います

ダミーで設定をした箇所に、前述の参照ステップを組み入れます

今回使用するデータと行いたいこと

今回は次のデータを使用します

演習データ

担当者別に受注金額を管理する表です

 こちらの表の中味が変更になっても、「最高受注金額」の担当者や「最高受注金額の半分未満」の担当者のリストを自動で抽出できるようにします

演習の中で、こちらの表にデータを新たに追加したりします

今回のポイントと使用するデータを確認したところで、本格的な演習に入ります!

続きを読む M言語に慣れる_3回目~ダイナミックフィルタリング~

名前機能について

皆さんは、学生時代に友達にあだ名とかつけていませんでしたでしょうか?

例えば、佐藤という苗字の友人が複数いたら、呼ぶのに便利ですよね?

このあだ名はエクセルを使用する際にも結構、有効活用できます

エクセルのセミナーを行っていて、「あれ?あまり知られていないな・・」と感じたのが名前機能です

これは結構、便利なのでぜひ有効活用して頂きたいと思います

この名前機能を有効活用すると、次のGIF画像のようなこともできてしまいます!

名前機能とは何?

文字通り、セルや範囲に名前、すなわちあだ名をつけてあげる機能です

他人が作成したエクセルファイルは使いにくいですよね

実は自分自身でも「あれってこの数式ってどうなってたっけ?」と後から思うこともあります

常に重要なセルの箇所や範囲に名前をつけて管理できるようにしておけば、エクセルを使いやすくできます

ちなみに、付けた名前は「数式タブ」の「名前の管理」で一覧化されます

つけた名前の一覧は名前ボックスからも確認できます

名前の付け方

何種類か方法はありますが、今回は一番シンプルな方法だけ解説します

下の画像のように、「名前の管理」の右にある名前の定義から①名前と②参照範囲を設定します

上の画像の場合は、下の画像のペンギンを参照範囲として指定しています

もし、後から「ペンギン」とは何のことか忘れてしまった場合は、名前ボックスからペンギンの場所に移動することができます

関数内での名前の使用

名前は関数の中でも使用することができます

例えば、下の画像の表に「検索範囲」と名前を付けておきます

この検索用の範囲をVLOOKUP関数に使うのですが、名前で代用することが可能です

<まとめ>

短い内容でしたが、いかがでしたでしょうか?

名前機能についてのイメージは明確になりましたでしょうか?

この名前機能は様々な応用が効きます

このブログでも2つ事例を紹介しています

興味のある方はぜひそちらの記事もご参照ください

1.VLOOKUP関数の参照範囲を切り替える 記事

下のGIF画像では、VLOOKUP関数の2つの参照範囲に「レディース」「メンズ」と名前をつけています。

VLOOKUP関数内のINDIRECT関数が都度、2つの名前を切り替えながら参照しているイメージになります

2.表示画像の切り替え記事

スライサーなどで表示を切り替える際に、表示画像も変えて「何に切り替えたのか?」を分かり易くします

こちらも1と同じくINDIRECT関数を使用します


にほんブログ村

テーブル列操作関数

M関数辞典はこちらから

列結合

Table.NestedJoin

文法

キー列を基にして2つのテーブルの内容を結合します

=Table.NestedJoin(テーブル名1,キー,テーブル名2,キー,新しい列名,省略可|結合タイプ)

*結合タイプが省略された場合には、左外部結合が指定される

使用方法

列名をリストとして返す

Table.ColumnNames

文法

ハードコード/自動記録された列名リストをTable.ColumnNamesで置き換えると、列が増えても列名のリストが網羅される

使用前
使用後

実際の使用例

 ➡【豆知識】ピポット解除時の空欄の扱い

リストからテーブル作成

Table.FromColumns

文法

リストを組み合わせてテーブルを作成します

例:Table.FromColumns({[勤務日],[曜日]})

使用例

列変換(研究中)

Table.TransformColumns

文法

元データ
Table.TransformColumns
列変換後


にほんブログ村

クエリの高速化・関数

M関数辞典はこちらから

キー設定

Table.AddKey

文法

マージを行う際に、主キーを設定してクエリ処理を高速化します

*明細側(多)より集計側(1)で高速化の効果が高いです

Table.AddKey(テーブル名, 主キーにする列, 主キー有無)

使用方法

let
With.Addkey1=Table.AddKey(売上台帳,{“商品コード”},true),
With.Addkey2=Table.AddKey(商品台帳,{“商品コード”},true),
ソース = 売上台帳,
マージされたクエリ数 = Table.NestedJoin(With.Addkey1, {“商品コード”}, With.Addkey2, {“商品コード”}, “商品台帳”, JoinKind.LeftOuter),
#”展開された 商品台帳” = Table.ExpandTableColumn(マージされたクエリ数, “商品台帳”, {“販売単価”}, {“商品台帳.販売単価”}),
挿入された乗算 = Table.AddColumn(#”展開された 商品台帳”, “乗算”, each [商品台帳.販売単価] * [販売個数], Int64.Type),
#”名前が変更された列 ” = Table.RenameColumns(挿入された乗算,{{“乗算”, “販売金額”}})
in
#”名前が変更された列 “

バッファー化

マージ対象のクエリをバッファー化します

文法

=Table.Buffer(最終ステップ名)


にほんブログ村

文字列・操作関数

文字列を操作する為の関数です

M関数辞典はこちらから

文字列・型式への変換

Text.From

文法

値を文字列・型式に変換

*3であれば”3”を返す

=Text.From(値)

使用方法

実際の使用例

 ➡M言語に慣れる_2回目~カスタム列~

検索文字の位置抽出

Text.PositionOf

文法

文字列の中にある特定の文字の最初の出現位置を抽出します

注意点としては、M言語は0ベースなのでカウントが0からはじまります

例えば、1文字目に特定の文字列が見つかった場合には0を返します

この点はご注意ください

*最後の出現位置を抽出することもできます

*文字列が見つからない場合は-1を返します

= Text.PositionOf(“検索対象・文字列”,”検索・文字列”,省略可)

使用方法1
使用方法2

実際の使用例

 ➡M言語に慣れる_5回目~M関数を検索する方法~

検索文字の有無判定

Text.Contains

文法

検索対象の文字列に、検索文字が含まれているかどうかを判定

含まれている場合にはtrueを、そうでない場合にはfalseを返します

=Text.Contains(“検索対象・文字列”,”検索・文字列”)

使用方法

実際の使用例

 ➡【M言語に慣れる】_15回目_TEXT関数/文字列関数

文字列の置換

Text.Replace

文法

文字列内に出現する、指定文字を全て置き換えます

=Text(文字列,置き換える対象の文字列,置き換え後の文字列)

使用方法

実際の使用例

 ➡【M言語に慣れる】_15回目_TEXT関数/文字列関数

数値型式への変換

Number.From

文法

文字型式などの型式から数値型式に変換します

=Number.From(値)

指定桁数に揃える

Text.PadStart

文法

文字列を指定した桁数になるように、文字列を繰返し補う

=Text.PadStart(元の文字列,指定桁数,補う文字列)

実際の使用例

 ➡ 【M言語実践】グループ別にIDを設定

にほんブログ村