analytic-vba のすべての投稿

【フィルターがどうもおかしい？と思ったらまずは今回の内容を疑ってみましょう！】

今回の記事では２つに分かれているテーブルを、リレーションにより一緒のマトリックスで使用します

上の画像の「発注数の合計」「納品数の合計」はそれぞれ別のテーブルのものです

そして、別のテーブルの内容がそれぞれ同じく「商品ID」にてフィルターされています

ちなみにここで言うフィルターとはエクセルのフィルター機能とは似て若干異なります

エクセルのフィルター機能は主に絞り込むために使用します

今回の記事のフィルターは「絞り込む」だけでなく「区分」や「選択」の意味がありますので、その点はご留意ください

実はこの「別テーブル共存」「同フィルター」の状態を作るのがとても重要なことなのです

どう作るかについては王道があります

１．参照マスタを作成する

２．それぞれのテーブルとの間で参照マスタとのリレーションを「１対多」「単一方向」で作成する

３．参照マスタの内容でフィルターする

この３つが王道です

何故ここで王道という表現を使うのか？

これは他にもリレーションやフィルタをする方法があるからです

但し、他の方法だと不正確だったり、曖昧さが残ります

この不正確になったり、あいまいさが何故発生するのかを理解し、Power BIのフィルターがどういうものなのかを考察するのが今回の記事の目的です

では、ここから王道も含めて他の方法を取った場合を実際に見ていきたいと思います

今回使用するデータ

発注テーブル

商品ID、発注数、発注日の3列から構成されます

商品IDは１～１０まであります

発注日は１つの商品IDに対して複数あります

ちなみに発注数の合計は１９０になります

納品テーブル

商品ID、納品数、納品日の3列から構成されます

商品IDは発注されてまだ全く納品されてないものもあるので、１～７までになります

納品日は１つの商品IDに対して複数あります

発注された10種類のIDの商品の中にはまだ全く納品されていない商品がある、というのが後々大きなポイントになります

ちなみに納品数の合計は８０になります

商品IDマスタ

全部で10行あります

発注テーブルと納品テーブルの商品ID数は以下のようになります

発注テーブル：10 > 納品テーブル：7

ですので商品IDマスタの行数は、両テーブルを漏れなくダブりなく網羅した１０となります

王道の場合

王道の場合のリレーションは次のようになります

「発注テーブルと商品IDマスタ」そして「納品テーブルと商品IDマスタ」それぞれの間に「１対多」のリレーションがあります

何故、１対多の関係になるかというと、前述のように１商品IDに対して発注日や納品日が複数に跨るからです

（注）ここでの*は多の意味になります

そしてそれぞれのリレーションは「単一方向」で作成します

以上が王道のリレーションのパターンです

マスタでフィルター

ではビジュアルはどうなるかを次に説明します

最大のポイントは商品IDマスタでフィルタすることです

マスタ以外でフィルター

では、次のように発注テーブルの商品IDでフィルターするとどうなるかを見てみましょう？

発注テーブルから取得する「発注数」は問題ないですが、納品テーブルから取得する「納品数」の方は全ての行が合計数になってしまい、商品ID別のフィルターが効いていません

理由はフィルタの方向が「商品IDマスタ」⇒「発注、納品テーブル」の方向の「単一」であるためです

その為、発注テーブルのフィルターが商品IDマスタを通じて納品テーブルまで波及していないからです

更に、次の画像のように納品テーブルの商品IDでフィルターしてみましょう！

こちらも発注数が合計になってしまいます

加えて、商品IDの８以降が欠けてしまいます

ですので、あくまでフィルターは参照マスタの内容で行うのが確実なのです

単一ではなく双方向でフィルターした場合

前述の王道ではクロスフィルターを単一で行っていました

では、クロスフィルターを双方向に変えてみたいと思います

この場合は実は、前述はうまく行かなかった発注テーブルからのフィルターが正しく行えます

何故正しくフィルターが行えるか？他の言葉で言い換えると何故、発注テーブルの内容で正しく納品テーブルの内容をフィルターが行えるかというと、フィルターが波及するからです

フィルターが波及する点は、納品テーブルの内容でフィルターした時も同様です

但し、この場合は結果が曖昧になります

商品IDが納品テーブルの内容の7迄になるからです

ですので、リレーションを双方向にて２つのテーブルをつなぐ場合は「曖昧」な結果になる可能性があるということになります

多対多

最後に、商品IDマスタを介さずに多対多でリレーションする場合を見てみましょう

ここで、本題に入る前に多対多の関係について詳細に見ておきましょう

上の画像で１が上下のテーブルで複数出現します

前回の商品IDマスタと発注、納品テーブルの関係では１⇒「1、1、・・・」となっていました

ところが、今回の多対多の関係では「1、1」⇔「1、1」ということになります

以下の画像のように、参照マスタに参照する値が複数あるのとは違い、元々複数の値があるテーブルどうしなのでそんなに問題が無いかのように感じます

実はこの場合も曖昧な結果になります

下の画像は「発注テーブル」の商品IDにてフィルタしたケースです

この場合は確かに問題がありません

今度は納品テーブルにてフィルタしてみます

この場合、納品テーブルの商品ID数までしか表示されないので曖昧な結果になります

ですので、やはり王道の方法が一番確実なのです！

＜まとめ＞

今回は参照マスタを使用して、２つのテーブルを連携する「王道」について解説しました

記事の途中で「２つのテーブルの内、どちらかの方がID数が多いのが分かっていれば問題ないのでは？」と思った方もいらっしゃると思います

確かにその通りなのですが、王道の方法であれば「ミスに気づきやすい」なども含めて総合的に確実性が担保されるので、王道の方法を実践するのがベストです！

最後に、

今回の記事は前回の内容：クロスフィルターの方向を単一もしくは双方で調整とも踏まえてみて頂くと理解が深まると思います

ぜひ前回の記事も目を通してみてください

にほんブログ村

ガッツの日記

意外にも知られていないエクセルの便利機能・便利技～ガッツの日記第13回

2024年7月9日 analytic-vba コメントをどうぞ

【エクセルはあまりに機能が多すぎるので、知られていない便利機能・便利技があります】

仕事柄、色々なところで色々なエクセルの活用の仕方に出会います

エクセルの活用者と話していて気づくのは、意外にも知られていない機能や便利技が結構あることです

今回の記事では「便利」だけど意外に「あまり知られていない」機能や便利技について、私の独断と偏見でピックアップして紹介します

計算の手動化

ワークシート内に数式を膨大に使用している場合、セルに入力するたびに膨大な計算が走ります

ですので、ワークシートが所謂「重たい」という状態になります

この場合、解決方法としては本来はデータベース等を導入することが考えられます

ただすぐに導入できる方法もあります

それが「計算の手動化」です

文字通り、数式の計算を止めます

そして、必要な時だけ計算します

数式タブには「計算方法の設定」というメニューがあり、こちらで数式の計算を止めることができます

ちなみに、通常は「自動」になっています

そして必要な時だけ「F9」ボタンを押せばファイル内（Shiftを押しながらだとシート内）の関数が計算されます

いや、入力するたびに計算結果が見たいんです、もしくは数式の計算をシート内に絞っても計算が遅いんです、という場合もあると思います

この場合は、VBAを使う方法があります

詳細はこの記事では書きませんが、VBAコードを入力というイベントに応じて動かす方法があります（イベントプロシージャーと言います）

イベントプロシージャーは、該当シートで右クリックすると表示される「コードの表示」で設定できます

次に開いた画面では、下の画像のように画面上の２つの欄を「Worksheet」「Change」に設定します

これで、入力したセルの行位置及び列位置を取得できます

Private Sub Worksheet_Change(ByVal Target As Range)

Dim myRow As Long
Dim myCol As Long

myRow = Target.Row　←行位置
myCol = Target.Column　←列位置

下の画像の事例では「手動で計算」の設定のままC列に入力した場合、１つ右のセルだけ計算されます

実際のVBAコードは次のようになります

If myCol = 3 Then
ActiveSheet.Cells(myRow, myCol + 1).Calculate

End If

入力位置が3列目（C列）だった場合、該当シートの入力位置がの一つ右を再計算（Calculate)

するわけです

これであれば、入力位置の行だけを計算できますので、かなり計算は軽くなります

テーブル機能

この機能を知ったのは恐らく5年ぐらい前だと思いますが、意外と浸透していないという実感があります

データが集まった表をテーブルにすると、何かと表内のデータを便利に扱えるようになります

下の画像では、１つのセルに関数を入力したことで、全ての行に関数が反映されています

つまり、データを扱う単位がセルではなく行単位になっています

テーブル機能には、上記のような便利な機能が満載なのです

この「満載」を具体的な一言で言い換えると、位置から名前への変換ということかもしれません

従来、エクセルを使おうとしたらセル単位という縛りの中でしか動けません

A列の5行目、C列の10行目といった感じでセルの位置をいちいち指定するしかありません

例えば、以下の画像のB列の合計を計算しようとしたら、マウスを動かしてC列の該当範囲を選択するしかありません

しかも、C列の範囲が拡大したら選択し直す必要があります

ところが、テーブル機能を使えば名前でセル位置を指定できます

テーブル機能では、作成したテーブルに名前を付けることができ、テーブル内の列名も自動的に活用できるからです

実は列名の指定もワンクリックでできます

上の画像のように、テーブルの該当「列名」の上にカーソルを置き、すっと上に少し動かすと、下向きの矢印がでます

これでテーブルの列名が指定できます

テーブルの列名で指定すれば、列を移動しようが、行の範囲が拡大しようが適切な範囲を合計できます

このようにテーブル機能は便利な機能が満載ですが、表をテーブルにするのは「Ctrl+T」のショートカットキー一つだけで行えます

とにかく簡単にテーブル機能は活用できますので、ぜひ今日から使用してみてください（→詳細記事）

表をテーブルにしたら、テーブルに名前を付けるのもお忘れなく！

最後に、もう一点だけ付け加えさせてください

表をテーブルにするとMicrosoft のメニュー自体をうまく扱えるようになるという点です

これは具体的にはどういうことかというと、Microsoft のPower Platformではエクセルのデータをテーブルで使用するように設計されているからです

Power Appsなんかもテーブルからアプリがワンクリックで作れるようになっています→関連記事

ぜひテーブル機能は使いこなしていきましょう！

クエリの関数化

こちらはPower Queryの活用の仕方の話です

関数というと、SUM関数とかVLOOKUP関数を思い浮かべる方が多いと思います

数学でいうと、Xを使った（X＋１）ｘ（Ｘ＋２）などの公式が関数と言えます

Xの値が何になっても一度作成した関数を使いまわして計算が行えます

クエリの関数化とは、一度作成したクエリをXを絡める関数にして使いまわすという意味です

Power Queryは大分、浸透したと思いますが、こちらのクエリの関数化はまだ知名度はいまいちという感じです

どういう場面で使うかというと、一番使えるのがシート別もしくはファイル別にデータが散らばっているケースです

例えば、以下のようにシート別にデータが散らばっていたとします

しかも、欲しいデータの上に無駄なデータがあります

これがもし、100シートあったとしたらデータをまとめあげる作業はどう行いますか？

手動であればお手上げだと思います

ところが、

クエリの関数化であれば1シートに対して行った処理を、ほぼワンクリックで複数シートに使いまわすことにより、簡単にデータの一元化が行えます

詳細についてはこちらの記事（→クエリの関数化）を参照して頂くとして、今回の記事では概念だけ解説いたします

上の画像は1シートからデータを取得し、無駄な2行を取り除いてテーブルを出力するクエリのソースコードです

実は黄色の箇所に「A」というシート名が入っています

つまり、ファイルの中のシートを取得した中からAシートを処理しているという訳です

この黄色の箇所を数学の関数でいうXに代えるわけです

以下の画像は関数化したクエリの一部です

このXにどういう風に各シートを連続的に割り当てるかというと、このソースステップの列を使用します

今回の事例では、実際にはItem列にある各シート名をフィルターして使用します

これでシート別などにデータが分散している問題をVBAなしで一気に解決できます

フリーアドイン

フリーアドイン機能もあまり知られていないと思います

スマホの便利アプリを利用するような形で、外部から便利機能を補って使用できます

こちらについては、アドインが沢山あるので「Formula Forge」というアプリの概要だけ今回は紹介します

例えば、こんな複雑なIF関数があったとします

読者の皆さんの中には「こんなの序の口だよ」と思われた方もいらっしゃると思います

Formula Forgeを使うと複雑な数式でも図解を一瞬でしてくれます

前任者の複雑なエクセルを引きついで苦しんでいる方にはおすすめのアドインです

＜まとめ＞

今回は意外と知られていない便利機能・便利技を大きく分けて4種類を紹介しました

とにかく知っているか知っていないかでこの辺りは差が大きく出ますのでぜひ押さえておいて欲しいところです

ところで、

私が思うに、エクセルの機能を最大限使いこなすために一番大事なことは「エクセルの限界」を知ることだと思います

どうしてもエクセルの方が相対的に劣る機能があります

例えば、エクセルは表計算ソフトなので大量のデータを扱うのはあまり得意でないですし、Power Platformなどかなり便利なクラウド機能も出てきています

ですので、最終的にはエクセルの機能を深堀するだけでなく「正しい脱エクセル」も意識する必要があると思っています

この点はまた別途詳しく書きたいと思います

にほんブログ村

コラム

フォルダ内の最新ファイルのみを処理して出力するクエリを作成する

2024年6月26日 analytic-vba コメントをどうぞ

【取得するファイルを自動で可変にする、究極のPower Query術です！】

この記事を見ている読者の方の中には、毎日システムから出力されるファイルを処理しているという方もいらっしゃると思います

この場合、Power Queryで出力ファイルを処理する場合は、既存のクエリのデータソースを変更するか、新規にクエリを作成するしかありません

実は、フォルダ取得のクエリ技術を利用すれば驚くほど簡単に「常に最新」のファイルを取得できます

フォルダ取得

下の画像のフォルダには拡張子が「xlsx」のファイルと「csv」のファイルが１つづつ格納されています

こちらのフォルダを取得先にしてクエリを作成してみます

上の画像では、3行目のcsvのファイルに対応する箇所がエラーになっています

ソースステップでフィルター

上の画像では最終ステップがエラーになっていました

ここで最初のステップ、「ソース」を選択してみます

そうすると、フォルダ内のファイルが一覧化されています

では、もっと右にスクロールしてみます

そうすると「Date created」という列があります

こちらの列では該当のフォルダにファイルが格納された時刻が記載されています

今、記事を書いている時間は「6月26日17時01分」なのですが、試しにフォルダに新たなファイルを格納してみます

そして、Power Queryエディターのプレビューの更新処理を行ってみます

すると新たなファイルが「17時01分」に追加されています

ですので、こちらの「Date created」の列でフィルターを行うと、常に最新のファイルだけを処理することができるのです

ここで「最も早い」「最も遅い」という日本語訳が分かりにくいかもしれません

あくまで「最も遅い」、つまりファイルが格納された時刻が最も遅いものが最新のものになります

これで最新のファイルを取得する仕組みは完成です

もし、エクセルファイル(xlsx)の中でフィルターしたいという場合には、先に拡張子でのフィルターを入れておきましょう

その後に「Data created」列のフィルターを入れます

では、もう一度試してみましょう

今、フォルダがこの状態です

このフォルダに「担当_田中.xlsx」ファイルを格納します

そうすると、エクセルファイルで最新の「担当_田中」のデータだけがクエリに反映されます

＜まとめ＞

今回は最新のファイルだけを反映するクエリを作成しました

ポイントは２つです

１．フォルダからファイルを取得すること

２．作成したクエリのソースステップでフィルタ処理をすること

驚くほど簡単ですよね

最後に、場合によってはファイルの名前、つまり「name」列でフィルターが必要なケースがあることもお伝えしておきます

にほんブログ村

クラウド活用

CSVファイルの内容をSharepointリストに格納する～基本パターン解説～

2024年6月20日 analytic-vba コメントをどうぞ

【CSVファイルを扱う基本パターンが分かれば、実はそれほど難解でもないです】

今回の記事ではPower AutomateのトリガーにてアップロードしたCSVファイルの内容を解析し、Sharepointリストに格納します

読者の方の中には「これの何が難しいの？」と思った方もいらっしゃると思いますので、次の画像を見てみてください

今回使用するCSVファイルをメモ帳で開いたものです

＊使用するCSVファイルは、https://www.mhlw.go.jp/stf/covid-19/open-data.htmlから取得いたしました

エクセルで見かけるセルに格納されたデータではなく、データがカンマ区切りされながら繋がっています

もちろん、行や列はありません

こちらを今回の記事では、大きく分けて２段階でSharepointリストに格納できる、行や列にあるデータにします

１．改行コードを基にして、行がある構造にする

見出し改行コードAAA改行コードBBB改行コードCCC　

⇒

見出し

AAA

BBB

CCC

２．見出しをスキップしてデータを抽出する

AAA

BBB

CCC

この２つをセットで行うのがCSVを変換する基本パターンです

今回の内容は本当に最低限の内容です

使用される文字コードの種類やCSVの中身の状況により、今回の記事に加えて追加処理が必要なケースもあります

但し、基本パターンを押さえておけばあまり混乱する必要はありません

では、詳細に処理を進めていきましょう！

ファイルのアップロード

まずは手動でCSVファイルをアップロードします

入力ファイルの種類は「ファイル」を選択し、三点リーダーから必須に指定しましょう

base64ToString関数による変換

前述のアクションでアップロードしたファイルの内容だと実は全く読み取れない状態なので、データ操作コネクタを使って変換処理を行います

データ操作コネクタ内の「作成」アクションにて、Base64 ToString関数により人間が読み取れる形式にします

*Base64による変換はメールなどで行われる処理です。今回はBase64に変換されたものを再変換（デコード）する形になります

ちなみにBase64ToString関数による変換を行わないと、CSVファイルの内容は下の画像のような内容になっています

＊Power Automateの実行履歴からトリガーの出力内容を一部スクショしたものです

ですので、あくまで機械同士でやり取りできる状態ということです

本題に戻り、Base64ToString関数での変換は以下のように行います

base64ToString(triggerBody()[‘file’][‘contentBytes’])

上の内容の関数の中身は「triggerBody()・・・」となっていて分かりにくいですが、要は動的コンテンツでトリガーの内容を指定したものです

では、関数を作成したらフローを動かしてみて実行履歴の中身を見てみます

なんとか人間が読み込める形になっています

ちなみにデータ操作コネクタのアクションは名前の付け方に注意しましょう

後続処理で必ず参照しながら使用しますので、読みやすい名前を付けるように心がけましょう

Split関数による分割（改行）

こちらでは前述の次の処理をします

見出し改行コードAAA改行コードBBB改行コードCCC　

⇒

見出し

AAA

BBB

CCC

こちらの分割処理を行うのにSplit関数を使用します

Split関数は２つの内容を引数に使用します

Split(分割するデータ,分割位置を指定する文字)

今回は分割位置を指定する文字として「改行コード」を指定します

改行コードについては、使われる文字コードに違っていたりするなど、詳細に説明しようとすると膨大な内容になります（私も残念ながら専門外です）

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

本題に戻ります

今回、実際には次のようにSplit関数を指定します

split(outputs(‘CSV’),decodeUriComponent(‘%0D%0A’))

第一引数は動的コンテンツの選択から前アクションを指定してください

第二引数は、改行コードをdecodeUriComponent関数で再変換したものを指定します

それでは、

ここまでの内容でフローを実行してみます

上記の画像のように、完全に行別に分割されています

見出しの１行をスキップ

今回はCSVの内容をSharepointリストに格納しますので、見出し列の内容は必要ありません

ですので、前述のように１行スキップする必要があります

スキップ処理には文字通り、skip関数を使用します

skip(outputs(‘分割’),1)

それではこれまでの内容でフローを実行してみましょう

下が今回、フローを動かしたときの画像です

前フローを動かした時には、下の画像のように見出しがありましたので完全に１行をスキップできています

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

空白行の削除

いよいよ、Sharepointリストに格納！と言いたいところですが、もう一点処理が残っています

もう一度、フローの実行履歴を見てみましょう

空白行があります

こちらは「アレイのフィルター処理」で削除しておきましょう！

上の画像が空白行を削除するアクションです

差出人の箇所は「１行スキップ」のアクションの結果を動的コンテンツで選択します

そして、以降の処理は「１行スキップ」の結果から１行１行を「ITEM関数」にて抽出してフィルターをするという流れになります

ですので、フィルターの条件式には以下のような式を入れます

empty(item())

上の分では、empty関数で抽出結果が空白かどうかを判定しています

そして「false」、つまり、空白というのが成り立たないという条件式を作成します

これで空白行をフィルター処理できます

Sharepointリストに格納

これからいよいよSharepointリストにデータを格納します

空白行を削除した段階では、データは以下のような形になっています

[ “2020/1/16,1,0,0,0,0,0,0,0,0,0,0,0,0,0,1,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 ,0,0,0,0,0,0,0,0,0,0,0”, “2020/1/17,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0 ,0,0,0,0,0,0,0,0,0,0,0”,（省略）

“2023/5/7,14344,658,127,116,293,133,117,188,106,266,175,774,709,2345,886,384,212,214,88,97,278,306,185,863,102,113,267,1098,423,159,52,105,32,122,217,114,66 ,150,105,40,687,87,114,133,121,95,168,254”, “2023/5/8,9489,577,87,68,208,57,107,143,236,113,128,350,372,1331,567,332,111,97,66,202,316,148,260,425,230,42,111,547,408,76,111,47,0,173,434,72,27 ,64,45,23,289,30,27,55,94,47,86,150”]

ですので、１行単位を更に「,」で区切っていく必要があります

これには前述のSPLIT関数とITEM関数を使用する必要があります

つまり以下のような仕組みを作成する必要があります

１．分割

１行データ：①,②,③

⇒

①　

②　

③　

２．割り当て

①⇒１列目、②⇒２列目、③⇒３列目

この仕組みを作成するには、まず、Apply to eachアクションにより空白行を削除したデータを繰り返し抽出できるようにします

実際にはITEM関数で抽出するのですが、抽出したデータはSPLIT関数にて「,」文字により分割します

split(item(),’,’)

これでデータは

①

②

③

という形になります

更にこの分割したデータを項目の作成アクションで各列に割り当てます

この割り当て処理には？と[]を使用します

split(item(),’,’)?[0] ⇒ split(item(),’,’)?[1] ⇒ split(item(),’,’)?[2]

[]の中に数字を入れて、列の位置を指定する形になります

これでSharepointリストの各列に分割したデータを割り当てることができます

ちなみに、数字は０から始まる点にご注意ください

＜まとめ＞

今回はCSVファイルを解析してSharepointリストに格納するフローについて解説しました

あくまで今回の内容は基本パターンです

ここから改行コードが別なもののパターン、分割したデータからカンマを更に切り取るパターンなどがでてきます

但し、どのパターンになっても今回の内容があくまで基本となると考えますので、ぜひ参考にして頂きたいと思います

最後に、今回の参考になる過去記事を紹介させて頂きます

参考記事１：難解そうなPOWER AUTOMATEのデータ操作コネクタの概要に触れる

参考記事２：POWER AUTOMATEでエクセル関数のように文字列（表示）操作をする方法

にほんブログ村

初級

メジャー初心者のための人事ダッシュボードの作成～フィルター依存を卒業する～

2024年6月14日 analytic-vba コメントをどうぞ

【フィルター使用は一見、簡単なのですが、後でとても分かりにくくなります・・・】

今回の記事では、メジャーを活用して人事データの要約を示すダッシュボードを作成します

よくフィルターを活用したダッシュボードを見かけますが、色々と問題があります

フィルターを開けないと詳細が分からず、一目で正確性を確認できません

後、フィルターをかけるビジュアルを間違えてしまったり、ミスが起こりやすいです

そして、そもそも、割合などはフィルターでは計算できません

それに対して、メジャーを活用すると、計算過程がとてもシンプルに分かりやすくなります

今回の記事では、メジャーを「シンプル」に活用した人事データのダッシュボードを作成します

ご紹介するメジャーの内容は、実際のダッシュボードを作成する場面でかなり使いまわしていける内容だと思います

但し、今回はメジャーを中心に解説させて頂き、詳細はあまり深く説明していませんので予めご了承ください

ポイント

メジャー専用テーブルの作成

前述のようにメジャー専用のテーブルを作成して、メジャーを集約すると後で分かりやすくなります

まず最初に「データの入力」を行います

仮の値をテーブルに入力しておきます（後で削除）

テーブル名はメジャーを集約するのに相応しい名前にします

こちらのテーブルに新たに作成したメジャーを集約していきます

CALCULATE関数

このDAX関数を知っておくだけで、かなりダッシュボード作りは変わってきます

エクセルのワークシート関数でSUMIFS関数です

SUMIFS関数では、条件を設定して合計を計算します

CALCULATE関数では、合計するSUM関数だけでなく、他の計算を行うDAX関数も使用できます

文法は次のようになっています

CALCULATE（計算方法,条件）

詳細については後述させて頂きます

カード

今回のダッシュボードではメジャーで算出した数値をカードで表示し、数値を強調します

そして、カードのタイトルを使用し、数値の中身を一目で分かるようにします

タイトルはビジュアルの書式設定の「全般」から指定します

作成するダッシュボード

今回は人事データを要約したダッシュボードを作成します

ビジュアルはカードを中心に使用して、要約データを表示します

使用するデータは以下のエクセルファイルを使用します

社員数カード

社員数はテーブルの行数から算出します

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

テーブルの行数は「COUNTROWS関数」により算出します

文法は（）の中にテーブル名を入れるだけになります

社員数 = COUNTROWS(‘人事データ’)

男性・女性社員数

男性・女性の社員数はCALCULATE関数にて条件を付けて算出します

先ほどCOUNTROWS関数を紹介したので、CALCULATE関数とCOUNTROWS関数を組み合わせるイメージを持たれた方もいらっしゃると思います

その方法も正解なのですが、前述のメジャーで社員数を算出しているので第一引数にそちらのメジャーを使います

男性社員数 = CALCULATE(‘MEASURE'[社員数],’人事データ'[性別]=”男”)

第二引数では条件（列名=内容）を指定します

男性・女性構成比

構成比は割り算で算出します

割り算は「DIVIDE関数」で算出します

男性社員割合% = DIVIDE(①’MEASURE'[男性社員数],②’MEASURE'[社員数])

①は割り算の分子、②は分母になります

昇進済み・未昇進

未昇進数は「昇進してからの経過年数」が2年以上の人の数で定義します

そして、昇進済みについては「社員数-未昇進」を算出するメジャーを作成します

未昇進

CALCULATE関数の第二引数を＞＝で条件を指定します

未昇進 = CALCULATE(COUNTROWS(‘人事データ’),’人事データ'[昇進してからの経過年数]>=2)

昇進済み

前述の通り引き算で算出します

昇進済み = [社員数]-[未昇進]

その他

こちらは「年」の表示がデータにはないのに入っています

これはPower Queryエディターで「例からの列」で「年」の表示を入れます

「例からの列」の詳細は過去の記事で紹介していますので、ぜひご活用ください

＜まとめ＞

今回は、メジャーを活用したダッシュボードの作成方法を紹介しました

メジャーを使用すれば、フィルターを使用するより修正方法などが分かりやすくなり、ミスも減ります

ちなみに、

こちらは画像を使用しています

この画像はパワーポイントで作成し、イメージから挿入しています

画像はパワーポイントの「アイコン」からスライドに挿入して作成します

スライドに画像を挿入した後、ファイルの拡張子を指定して保存します

今回は以上です

にほんブログ村

虎の巻

エクセルVBA虎の巻

2024年7月20日 analytic-vba コメントをどうぞ

この記事では、私がこれまでVBAを使用してきた中で「これは使える」と思ったコードを抜粋したものです

ファイル保存

エクセルシートのCSV出力

出力したいシートをコピーして、新しいファイルを作成し、ファイル名を指定して保存します

ThisWorkbook.Worksheets(“出力”).Copy
ActiveWorkbook.SaveAs “D:\TEST.csv”, FileFormat:=xlCSV
ActiveWorkbook.Close

文字列操作

分割

文字列の分割にはSplitを使用します

Variant型で設定した変数をSplitで分割し、変数(0)、変数(1)、変数(2)で受け取ります

例えば、シートのB1セルに「600-1」の文字列があるとします

こちらの文字列を「-」で分割し、それぞれをメッセージで表示するとします

その場合は、次のコードで実現できます

Dim mySTRING As Variant
Dim myWORD As Variant
Dim myYEAR As String
Dim myMONTH As String

mySTRING = Worksheets(“Sheet1”).Range(“B2”).Value

myWORD = Split(mySTRING, “-“)

myYEAR = myWORD(0)

MsgBox myYEAR

myMONTH = myWORD(1)

MsgBox myMONTH

まず、myWORD(0)で600を取りだします

次にmyWORD(1)で1を取り出します

固定文字が含まれているかを判断する

Instr関数を使用し、０以上なら含まれていると判断します

Dim myCountWaritsuke As Long ‘固定の文字が含まれいる位置
myCountWaritsuke = InStr(“myTEST1”, “TEST”)

MsgBox myCountWaritsuke

上のコードの場合は、myTESTの文字列の3文字目にTESTの文字列が含まれているので3とメッセ―ジが出力されます

文字列への変換

数値の文字列への変換にはStr関数を使用します

Str関数を交えた下のコードを実行してみます

Dim myVal As Long

myVal = 1

Worksheets(“Sheet1”).Range(“B2”).Value = “A-” & Str(myVal)

するとB2セルに以下のような文字列が入力されます

ここで1点問題があります

半角の空白がStr関数で変換した文字列に入ってしまっています

こちらが気になる場合はCstr関数を使用します

Worksheets(“Sheet1”).Range(“B2”).Value = “A-” & CStr(myVal)

これで空白は入らないようになります

数値への変換

こちらはVal関数を使用します

Dim myVal As String

myVal = “1”

Worksheets(“Sheet1”).Range(“B2”).Value = Val(myVal) + 1

上のコードを実行すると「２」がB2セルに入力されます

数値かどうかの判定

対象の’文字’が数値かどうかの判定は「IsNumeric」を使用します

試しに次のコードを実行してみます

MsgBox IsNumeric(123)

そうすると次の画像のようにTRUEが出力されます

次に以下のコードを実行してみます

MsgBox IsNumeric(“TEST”)

この場合はFALSEが出力されます

注意して頂きたいのは、次の場合はTRUEが出力されます

MsgBox IsNumeric(“１２３”)

これはあくまで１２３が数値文字だからです

メール関連

表をメールの本文に入れる

シート内の表の内容をHTMにするのがポイントです

＊＊＊HTMLの作成＊＊＊

With Worksheets(“MAIL”)

myLast = .Cells(Rows.Count, 2).End(xlUp).Row

For i = 6 To myLast

myProduct = .Cells(i, 2).Value
myQuantity = .Cells(i, 3).Value
myPrice = .Cells(i, 4).Value

myData = myData & “” & “” & myProduct & “” & myQuantity & “” & myPrice & “”

＊＊＊＊＊＊＊＊＊＊＊＊

そして、作成したHTMLをメールの本文に入れます（メールは送付せず、表示のみにしています）

*正確には事前に見出しを作成しておき、中身を変数で付け加える形です

‘メールの作成

       'OutlookのMailitemオブジェクトを取得する
          Dim objOutlook As New Outlook.Application
          Dim objMailitem As Outlook.MailItem
            Set objMailitem = objOutlook.CreateItem(olMailItem)

        'メールの各種設定をする
           With objMailitem
            .To = "analytic@analytic-vba.com"     '宛先
            '.CC = myCc     'CC
            .Subject = "TEST"
            '.SentOnBehalfOfName = mySenderAddress
            .HTMLBody = "<font face=""遊ゴシック""><font size=""2.5"">" & _
            "<body><table border=1><tr>" & "<th>Product</th><th>Quantity</th><th>Price</th>" & "　　
　　　　　　</tr>" & _
            myData & "</font></table></body>"

            .Display    '新規メール画面を表示

           End With 'objMailitem

これでエクセルシート内の表がメールの本文に入ります

ファイルを添付して送付

.Attachments.Add+ファイル名の一文を上記のコードに追加します

 Dim objMailitem As Outlook.MailItem
 Set objMailitem = objOutlook.CreateItem(olMailItem)
    
 'メールの各種設定をする
 With objMailitem
   .To = "analytic@vba.com"     '宛先
    （省略）
 　.Attachments.Add "C:\Users\＊＊＊\Downloads\download.csv"

   .Display    '新規メール画面を表示

 End With 'objMailitem

ユーザーフォーム

ユーザーフォームについてはこちらの記事もご参考にしてください

処理中表示

マクロの処理に時間がかかる場合は「処理中」の表示をしておきたい場合があります

こちらの表示にはユーザーフォームを使いたいところですが、ユーザーフォームを表示すると後続処理が止まってしまいます

ですので、以下のように「vbModeless」「Repaint」を使用します

Sub test()

UserForm1.Show vbModeless
UserForm1.Repaint

MsgBox “TEST”

Unload UserForm1

End Sub

上記のコードであれば、ユーザーフォームを表示しながらメッセージボックスも表示されます

ファイルの扱い

ファイルパスを指定して開く

Openメソッドを使用します

Dim myFileNameBefore As String

myFileNameBefore = “D:\Copy元.xlsx”

Workbooks.Open myFileNameBefore

ファイルを別名保存（コピー）する

SaveAsメソッドを使用します

Dim myFileNameBefore As String
Dim myFileNameAfter As String

myFileNameBefore = “D:\Copy元.xlsx”
myFileNameAfter = “D:\Copy先.xlsx”

Workbooks.Open myFileNameBefore

ActiveWorkbook.SaveAs Filename:=myFileNameAfter

ActiveSheet.Range(“B2”).Value = “TEST”

重複削除

重複削除を行うにはRemoveDuplicatesを使用します

複数列をキーに重複削除を行う際には、第二引数のArrayの中に列番号を複数指定します

例えば、次の表を「A」「B」の2列で重複削除を行うとします

この場合は、次のようなコードを書きます

Dim myLast As Long ‘最終行

myLast = Worksheets(1).Cells(Rows.Count, 1).End(xlUp).Row

Range(“A1:C” & myLast).RemoveDuplicates (Array(1, 2))

これで次のように削除されます

シート操作

全ページ操作

ファイル内の全シートを一律に操作するには「For Each ~ in ~ Next」構文を使用します

次の画像では、各シートのA1セルに値が入っています

そして、シートB以外はA1セルの値を読んでメッセージを表示するようにします

コードは一度、シート変数：wsTESTを宣言した後に次のように書きます

For Each wsTEST In Worksheets

If wsTEST.Name <> “B” Then

wsTEST.Activate
MsgBox Range(“A1”).Value

End If

ちなみにSheet変数.nameでシート名が取得できます

印刷設定

印刷範囲設定

Dim myLast As Long
‘最終行取得
myLast = Worksheets(“Sheet4”).Cells(Rows.Count, 1).End(xlUp).Row
With Worksheets(“Sheet4”).PageSetup

‘印刷範囲設定
.PrintArea = “A1:M” & myLast

End with

全列を全て印刷

Application.PrintCommunication = False
With Worksheets(“Sheet4”).PageSetup
‘*全ての列を印刷
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
Application.PrintCommunication = True

改ページプレビューに設定

ActiveWindow.View = xlPageBreakPreview

にほんブログ村

ガッツの日記

エクセルVBA～ユーザーフォームの不思議～ガッツの日記第12回

2024年6月5日 analytic-vba コメントをどうぞ

【エクセルはとにかく機能が豊富です。その象徴がユーザーフォームです】

エクセルVBAの初級者か、そうでないかの境目の一つは「ユーザーフォームが使えるか？」だと思っています

ユーザーフォームを使うことで、エクセルファイル内の利便性は格段に向上します

ユーザーフォームを使えると、入力と表示を明確に切り分けることができるのでデータを有効活用できるからです

例えば、下の画像を見てください

これはG２セルとG７セルに移動して、会社名をそれぞれ入力しています

つまり、宛先を表示するために、宛先の表示場所に移動して入力作業をしています

これが、ユーザーフォームを使うとどうなるでしょう？

ユーザーフォーム内に表示されている内容から「選択」を行い、「登録」ボタンを押すだけです

これは、繰り返しになりますが、入力と表示が分かれているから行えます

便利なユーザーフォームですが、実際に活用するとなると最初はかなりとっつきにくいと思います

通常のエクセルと考え方が違うからです

後、とにかく機能が多いです

今回の記事ではユーザーフォームを使う中で、躓きやすい「不思議な箇所」、並びにこれは便利だと思った機能を解説したいと思います

VBAを使った事が無い方、VBAを使ったことがあるけどユーザーフォームを使ったことが無い方も、これを機会にユーザーフォームの魅力、エクセルの奥深さを感じて頂けたら幸いです

ちなみに上記のユーザーフォームのコードは以下の通りとなります

初期表示

＊＊＊

With ListBox1　’リストボックスのオブジェクト名
.Font.Size = 9
.ColumnCount = 3
.ColumnWidths = “20;50;100”
.TextAlign = fmTextAlignCenter

‘リストボックスに情報を追加
Dim i As Long
Dim myLast As Long
myLast = Worksheets(“①”).Cells(Rows.Count, 2).End(xlUp).Row
For i = 3 To myLast
.AddItem
.List(.ListCount – 1, 0) = Worksheets(“①”).Cells(i, 2).Value
.List(.ListCount – 1, 1) = Worksheets(“①”).Cells(i, 3).Value
.List(.ListCount – 1, 2) = Worksheets(“①”).Cells(i, 4).Value

End With

＊＊＊

登録ボタン

＊＊＊

With ListBox1
Dim myValue As String
myValue = .List(.ListIndex, 1)

Worksheets("①").Range("G2").Value = .List(.ListIndex, 2)
Worksheets("①").Range("G7").Value = .List(.ListIndex, 2)

End With

＊＊＊

リストボックスの10列超を表示できない

例えば、こんな横長な表があったとします

こちらの表をリストボックスで表示してみます

すると上の画像のような不思議なエラーになります

使用しているコードは「AddItem」になります

実はこの「AddItem」を使用する時は10列までしかリストボックスに表示できません

この場合は「RowSource」か「List」を使用し、直接エクセルの範囲を指定します

RowSource

RowSourceの場合は、関数で別シートを参照する時のような書き方で範囲を指定します

これで、B3セルからM列の最終行までをリストボックスで表示できます

しかも、ColumnHeadsを使用して指定範囲の1つ上の行を見出しとして表示できるのが便利です

List

Listで範囲指定する場合には、下のように通常のVBAの書き方をして、最後にValueを付け加えます

.List = Worksheets(“②”).Range(“B2:M” & myLast).Value

但し、RowSourceと違い、見出し設定はできませんのでご注意ください

リストボックスから行削除が行えない

これまた不思議なのですが、.RemoveItemを使用してもリストボックスの行を削除できないことがあります

しかもエラーメッセージが不思議な内容ででます

実はこれ、RowSourceを使用してリストボックスを表示すると起こる現象です

RowSourceを使用する場合であれば、削除はデータ元のエクセルで行う必要があります

他の方法でリストボックスを作成している場合はこの現象はありません

リストボックスを複数選択にする

これはユーザーフォームではこんな事もできるという紹介です

上の画像の「MultiSelect = fmMultiSelectMulti」を追加するだけで、リストボックスは複数選択にできます

これだとどこが選択されているか、どう選択したらいいか分かりにくい、という場合は次の一行を足します

そうすれば、次の画像のように各行にチェックボックスが表示されます

複数選択できるのは分かったけど、どう選択されたものを取得するの？

と思われた方も多いと思います

複数選択されたものを取得するには、リストボックスの各行を繰り返し処理で取得します

そしてポイントは「Selected」を使用して、選択行を特定することです

＊＊＊

With ListBox1
For i = 0 To .ListCount – 1 ‘ListBoxの各行
If .Selected(i) Then ‘行が選択されている場合
MsgBox .List(i, 0)
.Selected(i) = False
End If
Next i

End With

＊＊＊

これで複数行を取得できます

テキストボックス内で文字列を折り返す

テキストボックス内でセルと同じように折り返せないか？と思った方もいらっしゃると思います

この場合はプロパティで「MultiLine」をFalseからTrueに変更します

後、もう一か所関係がある箇所があります

WordWrapがTrueに設定してある必要があります

こちらは通常はTrueに設定されています

これで文字列は折り返されます

入力位置遷移（カーソル遷移）

テキストボックス間での遷移を変更したい時があります

以下のGIF画像では横にカーソルが動いています

この遷移を変えたい時には「TabIndex」を変更します

今回の場合で言えば、下のテキストボックスを３から１に変更します

このようにTabIndexを変えれば、カーソル遷移は変わります

＜まとめ＞

今回はエクセルVBAのユーザーフォームについて記事を書きました

私は正しい脱エクセルをかかげていますが、最近、改めてエクセルの浸透の深さに驚いています

他の言葉で言い換えれば、様々な企業で貴重なエクセル資産があります

わざわざ資産を他に移し替えるのはもったいないケースも多々ありますので、エクセルの研究は引き続き続けていきたいと思います

その研究テーマの一つが今回のユーザーフォームですね

ユーザーフォームは本当奥深いですね

今後も研究は続けていきたいものです

にほんブログ村

クラウド活用

難解そうなPower Automateのデータ操作コネクタの概要に触れる

2024年5月15日 analytic-vba コメントをどうぞ

【データ操作コネクタにはデータの扱いそのものに強くなれる機能が満載です】

Power Automateには「データ操作」コネクタがあります

このコネクタを使いこなせると、Formsで受け取ったデータの扱いやCSVの扱いなど、データ操作自体に強くなれます

今回は、実際のアクション別に概要に触れていきます

記事の最後にはこのコネクタを使う隠れた秘訣も２つ解説します

作成アクション

作成アクションでは様々なデータを作成できます

このアクションの特徴は形式を自動的に認識してくれることです

実際に下の画像のようなデータを入力してフローを動かしてみます

そして、実行結果から「未加工出力の表示」をクリックします

するときちんとダブルクォーテーションが入力され、文字列と認識されているのが分かります

次は配列を入力してみます

今度もちゃんと配列で出力されています

結合アクション

結合アクションでは、配列を指定した区切り文字で結合処理を行うことができます

例えば、前述の「作成２」の配列を；で結合したいとします

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

この場合は下の画像のように結合アクションを作成します

指定する2段の箇所の内、1番目の段には結合する配列を動的コンテンツにて指定します

２番目の段には区切り文字「；」を指定します

そして、実際にフローを動かすと配列内の文字列が結合されます

選択アクション

以下のようなオブジェクト＊が、作成アクションを通じて作成してあるとします

*文脈によっては配列という場合もあると思いますが、ここでは分かりやすくするためにオブジェクトとします

オブジェクトの項目は名前、身長、体重の３つがあります

こちらを１つなどに絞り込むのに、選択アクションが使えます

上の画像の「開始」には動的コンテンツで配列が作成されたアクション名を指定します

そしてマップの名前にはオブジェクトの項目名（キー名）を指定します

右側にはitem関数にて値を設定します

このItem関数を使うのがこのアクションのポイントです

このItem関数により、取得したオブジェクトの項目を抽出します

項目の仕方は「？」を追加した後、項目名を[”]内に指定します

それでは実際にフローを実行します

アレイのフィルター処理

選択アクションでは３つの項目がある配列から１つに絞り込みました

「アレイのフィルター処理」では条件に合う配列の内容に絞り込みます

上記の画像は実際のアレイのフィルター処理の内容です

「差出人」という表現になっていますが、こちらでは動的コンテンツにて配列を指定します

差出人の下の欄では前述のItem関数を使用して条件を指定します

次の画像の実際にフローを実行した際の出力内容です

オブジェクトの内容が「Aさん」のものに絞り込まれています

CSVテーブルの作成

CSVテーブルの作成アクションでは、オブジェクトからCSVファイルを作成することができます

今回は、前述の「アレイのフィルター処理」を動的コンテンツで指定しています

「作成したCSVファイル」アクションで出力された内容は「ファイルの作成」アクションを通じ、Sharepointサイトに出力します

実際にフローを動かすと、以下の内容が「作成したCSVファイル」アクションが出力されます

そして、ファイルの作成アクションを通じて、指定フォルダー内に以下のようなCSVファイルが作成されます

HTMLテーブルの作成

こちらはCSVテーブルの作成とほぼ同じ内容になりますので、詳細は割愛します

実際の使用例としてメール内に出力してみます

本文内では動的なコンテンツで「HTMLテーブルの作成」を設定します

実際にフローを動かすと「HTMLテーブルの作成」アクションで出力された内容が、送信したメール本文内に出力されます

JSONの解析

JSONの解析については、作成とセットで解説させて頂きます

最終的には、一見利用できないようなデータから必要なデータを抽出できるようにします

コンテンツ

JSONの解析ではまず、解析する内容を指定します

今回は前述の作成３を使用します

こちらの内容は、JSONの解析の「コンテンツ」に動的コンテンツにて設定します

スキーマ

まずフローを実行した履歴から「作成３」出力結果をコピーしておきます

その後、「サンプルから生成」をクリックします

すると、次のGIF画像のように新たな入力画面が開きます

入力画面はに、前述コピーした内容を貼り付けます

作成

作成アクションでは「JSONの解析」の内容を繰り返し処理により取得します

下のGIF画像のように、作成アクションで「JSONの解析」の内容を動的コンテンツで取得すると「Apply to each」が自動的に適用されます

下の内容が実際にフローを動かしたときの作成アクションの内容になります

＜まとめ＞

今回はPower Automateの「データ操作コネクタ」の概要について解説しました

このコネクタの操作に慣れると、Power automate活用の幅は格段に広がります

実際の使用例についてはまた別途、記事を書こうと思います

最後にこのコネクタを上手に使うための隠れたポイントを２つ解説します

ポイント１

「データ操作コネクタ」を動的コンテンツで取得するケースがはとても多いです

通常だと「作成～」となってしまい、区別があまりつかなくなってしまいます

ですので、アクション名については明確に区別がつく名前をつけておきましょう

ポイント２

「データ操作コネクタ」では、他のデータ操作コネクタを動的コンテンツを通じて参照するケースが多いです

ところが、参照したものが「出力」や「本文」というような２文字に集約されて表示されるので、何を参照したかよくわからなくなってしまいます

この場合はアクションの右上の三点リーダーから「コードのプレビュー」を使用しましょう！

どのアクションを参照したかが良く分かります

ちなみに、上の画像ではアクション名は「作成 3」なのに「作成_3」と表示されています

これはアクション名に空白がある場合は、_で置き換えられることを意味します

今回の記事では関数は扱いませんでしたが、関数でアクション名を使用する際は_を意識しないとエラーになるケースがあるので注意が必要です

にほんブログ村

クラウド活用

Power Automateでエクセル関数のように文字列（表示）操作をする方法

2024年5月4日 analytic-vba コメントをどうぞ

【Power Automateでもエクセルと同じように、関数を使いこなして文字列操作しよう！】

Power Automateを使用する際に、エクセルを使用している時と同じように「LEFT関数」や「RIGHT関数」を使いたい時があると思います

Power Automateでも関数は使えるのですが、エクセルとは少し勝手が違います

今回の記事では「エクセルとの勝手の違い」を交えながら、日付を含む文字列などの表示操作について解説していきたいと思います

文字列の切り取り

Power AutomateではLEFT関数やRIGHT関数はありません

ですので、エクセルで言えばMID関数に相当するSubstring関数を使用します

MID関数では文字位置を指定して、指定した長さを切り取ります

Power AutomateのSubstring関数も同様の機能です

Substring関数の文法は次のようになります

Substring(①対象文字列,②開始位置,③切り取り文字列の長さ)

LEFT関数

例えば、文字列「123456789」から左側4文字を切り取りたいとします

この場合、Substring関数の引数②開始位置を0で指定するのがポイントです

上の画像では変数：操作対象文字列に文字列「123456789」が設定されています

こちらを変数の設定アクション内でSubstring関数を使用し、左4文字を切り取ります

上の画像の式の中身は以下の通りとなります

substring(variables(‘操作対象文字列’),0,4)

「variables~文字列’)」の箇所は動的コンテンツで取得するのもよし、直接variablesと書いても大丈夫です

前述の通り、0で開始位置を指定し、第三引数で切り取りたい文字数を指定しています

これで下の画像のように、Power Automateを動かすと文字列1234が123456789から切り取りできます

RIGHT関数

LEFT関数と比べると少し難易度があがりますが、パターンは決まっています

LENGTH関数で文字列の長さを取得し、開始位置を指定します

開始位置は（文字列の長さ-切り取る長さ）となります

今回で言えば、9文字：123456789から4文字：6789を引いた5が開始位置になります

*この関数は0からカウントが始まります。ですので、エクセルで言えば6が開始位置です

但し、ここでエクセルとの違いがでてきます

エクセルにて引き算をする際は、ーを使用すればOKです

Power Automateの場合はSub関数を使用します

ここが少し面倒なところです

ですので、式は以下のようになります

substring(variables(‘操作対象文字列’),sub(length(variables(‘操作対象文字列’)),4),4)

上の式から第二引数を抜き出すと次の通りとなります

sub(length(variables(‘操作対象文字列’)),4)

length関数で「操作対象文字列」の長さを取得しているのは分かりやすいと思います

ここで取得した長さから４を引くのに、前述のsub関数の第二引数にて４を指定しています

これで下の画像のように、Power Automateを動かすと文字列6789が123456789から切り取りできます

数値の桁区切り

エクセルでは数値の桁区切りは関数で処理しません

画面上にメニューがちゃんとあります

Power Automateの場合は、関数で処理を行います

使用する関数はformatNumber関数です

エクセルとの違いは、formatNumber関数により文字列に変換されます

ここが注意点です

次が実際の式です

formatNumber(variables(①’操作対象文字列’),②’#,#’,③’ja-jp’)

①が操作対象です

こちらは整数形式のものを指定します

②で表示形式を指定し、③は固定の指定になります

これで数値の桁区切りになります

注意点

formatnumber関数にもし、０を入力した場合を見てみましょう！

結論としては、関数からの出力が空欄になってしまいます

こちらは次のフローで見てみます

「手動でフローをトリガーします」で入力したものを「変数を初期化する」アクションにおいて、formatnumber関数により桁区切りをしています

formatnumber(int(triggerBody()[‘text’]),’#,#’,’ja-jp’)

このフローのトリガーに０を入力してみます

そうすると下の画像のように、空欄になってしまいます

それではここから、対策について解説致します

if(equals(triggerBody()[‘text’],’0′),0,　formatnumber(int(triggerBody()[‘text’]),’#,#’,’ja-jp’))

条件式にて値が「０」の場合は、０を出力するようにします

これでformatnumber関数からの空欄出力は回避できます

日付形式

Power Automate内でよく下の画像のような日付を見ることがあると思います

TやZが入っていいるのもあって、そのままでは表示データとしては使えません

ただし、この記事ではこれ以上はタイムゾーンに触れず、純粋にこの表示形式を変える方法を解説します

このTやZは、タイムゾーンと関連します

この日付表示を変えるにはformatDateTime関数を使用します

例えば、タイムゾーンに関係なくutcNowという関数で現在時刻を出したとします

この場合は前述のような表示になります

こちらをTやZがない「yyyy/MM/dd形式」にするには、次のようにformatDateTime関数を使用します

formatDateTime(①utcNow(),②’yyyy/MM/dd’)

①は変換対象です

②で表示形式を指定するのですが、シングルクオーテーションを使用します

ここがエクセルとの違いであり、注意点となります

文字列の発見

エクセルにはFIND関数という関数があり、文字列に含まれる指定文字列の位置を取得できます

Power automate でも同じような機能があります

関数ではないですが、ぜひこの記事で紹介しておきたいです

文字列で発見するには、「テキストの位置の検索」アクションを使用します

「テキストの位置の検索」アクションでは２つの項目を使用します

テキスト：操作対象文字列（123456789）

検索テキスト：検索文字列（6789)

上の画像では、テキストと検索テキストの内容は変数で事前に設定してあります

「テキストの位置の検索」アクション内では、動的コンテンツで設定した変数を参照しています

では、この「テキストの位置の検索」を実際に動かすとどうなるでしょうか？

上の画像では６ではなく「５」という結果になりました

これは０からカウント方法によります

このアクション内では１では０からカウントが始まるからです

＜まとめ＞

今回はPower Automateにて、文字列や表示を変換する方法を紹介しました

エクセルと方法が少し違いますが、違うパターンは決まっているのですぐに慣れていくと思います

特にSubstring関数はCSVを変換する時などよく使うので、今回を機会にぜひ慣れて頂くといいと思います

とにかくクラウド作業を省力化するにはPower Automateは欠かせません

ぜひ有効活用してみてください！

にほんブログ村

ガッツの日記

エクセルのような表をアプリで簡単に作成してPDF化する方法～ガッツの日記第11回

2024年3月26日 analytic-vba コメントをどうぞ

【エクセルでシート別にわざわざ表を作成しなくても、簡単に表は作成できるんです！】

先日、PDFでデータを送付するため、わざわざエクセルのシート別にデータを管理している事例をみました

確かに、エクセルだと罫線が入った表をきれいに作成できるので仕方ない部分もあります

ただPDFファイルを作成するためだけにデータを一元管理しないのはデメリットも大きいです

今回はPower Appsにてデータを一元管理しつつ、PDFファイルを「エクセルで作成したように」作成する方法を解説します

具体的にやりたい事

元データは下の画像のエクセル表になります

こちらをPower Appsでフィルターしながら、Galleryに表示します

合計金額は別途ラベルで計算しておきます

そして上記の内容をPDFで別途出力します

ポイント

Print関数

Power Appsにて、新しい画面をクリックすると「縦長で印刷」を選択できます

実際に「縦長で印刷」の画面を選択すると、「Print関数」が仕込まれた画面が設定されます

上の画像の「印刷」をクリックすると、PDFで画面を保存することができる画面に切り替わります

しかも「印刷」ボタン自体はPDF画面に反映されません

何故ならVisibleプロパティに以下の内容が仕込まれてるからです

ですので、戻るボタンを作成する場合には、印刷ボタンをコピーしてOnSelectプロパティを変えるか、Visibleプロパティを変えましょう！

CONCAT関数＆HTML

前述のPrint関数でPower AppsからPDFファイルを作成する方法は解説しました

では、エクセルのような表形式はどのように行うのか？気になるところだと思います

こちらは、通常ではデータテーブル機能を使用する方法が一般的だとは思います

ところが、Power Appsのデータテーブルだとあまり「表」らしくありません

ですので、HTMLで表を作成します

表の可変行の箇所は、CONCAT関数を使うのも大きなポイントです

今回は、Galleryの内容をCONCAT関数で抽出します！

データ抽出画面の作成

前述のPrint関数はPDFを作成する出力画面で使用します

それとはまた別の画面を作成し、出力画面で使用するデータを抽出できるようにします

こちらは「具体的にやりたい事」で前述した内容ですので、割愛させて頂きます

HTML

今回の内容はPrint関数で出力するのもポイントなのですが、HTMLで出力するところが最大のポイントとなります

特に行数が可変になるGalleryの内容をCONCAT関数を通じてHTMLで表現するところが難所です

基本構文

専門的な事は、専門サイトの方をご参照して頂きたいのですが、基本的な内容だけは紹介したいと思います

HTML

1 ファイル 0.86 KB

ダウンロード

今回使用したHTMLをメモ帳で添付したので、そちらを修正して使いまわすことも十分可能だと思います

構文は大きく分けて３つに分かれます

１．普遍的なHTMLの内容の宣言、２．テーブルの構造の指定、３．見出し・行列の内容の指定

１．の普遍的な内容（”<!DOCTYPE html>～<body>）についてはここでは割愛させて頂きます

２．のテーブルの構造の指定については以下のように指定します

<table border=1 cellspacing=0 cellpadding=10 width=700 table style=font-size:15pt;>

「tablre border=1~cellspacing=0」で一般的な罫線を指定しています

そして、widthで表の幅、font-sizeでフォントサイズを指定しています

３．の見出し・行列の内容の指定については、見出しから解説します

<tr>

<th width=25%>日付</th>

<th width=25%>商品</th>

<th width=25%>客先</th>

<th width=25%>売上金額</th>

</tr>

<tr>で始まり、</tr>で終了するのですが、<th>~</th>内に各見出しを指定します

widthは列幅の指定になります

次に一番肝心の行列の指定です

こちらは前述のようにCONCAT関数で指定します

引数は２つになるのですが、第一引数はGalleryの内容になります

Gallery1.AllItemsを第一引数にすることで第二引数内で、ThisRecordによりGalleryの内容をHTML内に抽出することができます

次に、行列の内容ですが、こちらは見た目は小難しそうですが、<tr>と<td>の出現位置に注目して頂くとかなりシンプルなのがお分かりいただけると思います

まず、<tr>で1行目が始まり、各列が<td>で始まります

ここで、ルールが１つあるのですが、</~>で終了します

繰り返しになりますが、<tr>と<td>の出現位置に注目すれば、かなりシンプルに行列が表現できます

それでは、

ここまでで基本的な内容が指定できたと思います

ここから応用ですが、空白行を追加します

空白行は「” ”」で表現します

“<tr><td>” & “ ” & “</td><td>” & “ ” & “</td><td>” & “ ”& “</td><td>” & “ ”

更に合計行は「” ”」を交えながら表現します

& “</td><td>” & “ ” & “</td><td>” & “ ”& “</td><td align=right>” & Label_合計.Text & “</table>

これで、表を更にエクセルライクにすることができました

注意点

注意点は２つあります

１つは、HTMLはラベル内に記述するのではない点です

記述は下の画像のHTMLテキスト内で行います

次に注意点の2点目です

記述するHTMLはあくまで文字列で指定します

ですので、”ではじまり”で終わります

途中でも「文字列」が成立するように”の位置と＆の位置には注意する必要があります

上記の内容に注意すれば、エクセルライクな表をPDFファイル内に設定できるようになります

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

＜まとめ＞

今回は、Power Appsにてエクセルのような表をPDFファイルで出力する方法について解説しました

今回の紹介した仕組みを有効活用すれば、エクセルの過度なシート別の使用も減らすことができます

尚、

今回はHTMLについては簡単な内容だけ紹介しました

ぜひ、専門的なサイトの内容も参考にして頂きたいと存じます

個人的には「SAMURAI ENGINEER BLOG」様を参考にさせて頂きました

https://www.sejuku.net/blog/49377

にほんブログ村