タグ別アーカイブ: INDEX

クラウド活用

Power Appsでエクセルを超える～列方向が可変のクロス表を作成

2023年9月6日 analytic-vba コメントをどうぞ

【工夫すれば、使い慣れたエクセルのクロス表をPower Apps内で使用が可能です】

エクセルは言わずと知れた、メールと並んで多くの方に利用されているツールです。

本当に素晴らしい機能が満載です

このブログもExcellent仕事術となっています

エクセルの特徴的な使い方の一つが、列方向に可変なクロス表による管理です

上のような画像のクロス表にて、列方向に「2023/08/05、2023/08/06・・・」と増やしていく使い方は良く行われていると思います

これはエクセルの表計算の仕組みを有効活用した手法です

但し、この表は確かに見やすくて便利なのですが、表が大規模になってくると幾つか問題が出てきます

・処理がセル単位になり、行列一括で処理が行えない

・一目で分かるようにシート別に区切って管理するので、データの一元管理が行えないケースもある

ですので、このクロス表の作業をシステム化しましょうという場合もあります

これはこれで問題があります

このクロス表のシステム化は結構、ハードルが高いのです

普段何気なく使用しているフィルター作業や並べ替えも縦方向ですよね？

システムは基本的にはデータの列を固定した上で、縦に並べるようにできています

エクセル以外のソフトだと、実用的なものをそれなりの低価格で作成するのはかなり困難だと言わざる負えません

エクセルのままであれば、特殊なVBAの使い方をすれば自動化はそれなりに可能ですが、様々な問題があります

何故かというと、エクセルは本来はあくまで表計算ソフトであり、Accessなどのデータベースソフトとは違うからです

私もプログラマーとして活動する中で、この問題には度々直面してきました

特殊な在庫管理業務など、この列数可変のクロス表でないと行えない作業が現実として世の中にあるからです

システム化を行わなくてはならない場合は、前述のようにエクセルとAccessを組み合わせるなどの特殊なVBAの使い方で乗り切ってきました

ただ、作成に時間もかかり作業画面も思うようには作成できませんでした

仕組みが複雑で、他の方に説明が難しいという問題もありました

ところが、Power Appsの登場で状況が変わりました

このクロスの列方向が可変の表を、Sharepointリストと組み合わせながら比較的、簡単に作成できるようになりました

下のGIF画像はPower Appsの画面です

横軸の「日付」はSharepointリストと連動しています

2023/08/11が最終日付でしたが、「2023/08/12」を追加すると列方向に追加されます

上記のGIF画像では一見、分かりずらいかもしれませんが、連番の動きに注目して頂ければと思います

最初、12番は空欄です

Sharepointリストに2023/08/12を追加すると12番に日付が追加されます

当然、下のGIF画像のように表の中身を新規に追加・更新もできます

そして、

通常のエクセル処理でもできない列フィルターも行えます

2023/08/04を指定すると、それ以降の日付のみに表示が変わります
エクセルVBAにて列フィルターを行うには、列を非表示にするという複雑な処理を行う必要がありました

ですので、非表示にしたものを再表示するなどの処理も必要です

後で詳細は紹介しますが、Power Appsであれば列フィルターも比較的に簡単に行えます

何より、Power Appsによりクラウド上で業務が完結できるのがいいです

この記事でPower Appsの魅力及び可能性を伝えられたら幸いです

それでは、まずは最初にポイントを大まかに２つ紹介します

１つ目はIndex関数、2つ目はキーを組み合わせる複合キーです

ポイント

Index関数

エクセルのIndex関数をご存じでしょうか？

Index関数とは指定範囲から、指定位置のセルの値を抽出します

INDEX(範囲, 行番号, [列番号])

Index関数を使えると、抽出するセル位置を可変にできます

下の画像であれば、指定範囲の1行目1列目～2行目2列目をそれぞれ抽出し、

下の画像であれば、指定範囲の2行目2列目～3行目3列目をそれぞれ抽出します

今回のPower AppsではこのIndex関数を、行列の表示数を固定のまま有効活用します

複合キー

下の画像をご覧ください

行列の見出しを組み合わせて複合キーを作成しています

この複合キーにより、表の中身の位置を示すことができます

例えば、範囲の1行目1列目の「８」は「A-2023/08/01の８」と表示することができます

この複合キーの活用により、行列双方向の表を列数固定の形式に並べ直すことができます

この複合キーの仕組みは実際には「引当テーブル」の中で有効活用します

準備するもの

Sharepointリストを３つ用意します

行方向用（行見出し）

行方向に並べる商品データを用意します

列方向用（列見出し）

こちらは日付データを用意します

中身用

こちらは複合キーを交えた引当データを用意します

*CTが中身の個数になります

複合キーはｰなどの記号を入れると後で分かりやすくなります

それではいよいよ、Power Appsのキャンバスの作成に入ります

インデックス及びインデックスを動かす仕組み

前述のように、今回はIndex関数を使用します

まずはIndex関数が参照するインデックス（1,2,3・・・）とインデックスを動かす仕組みを作成します

ここで言うインデックスとは、具体的にはこちらのことになります

そして、インデックスを動かす仕組みはこちらの三角マークになります

インデックスを動かす各三角マークの「OnSelect」プロパティには、変数を設定するSet関数を埋め込みます

上の画像の下向きの矢印であれば、変数：num_Rowを１つ繰り上げて設定します

*上向き、左向きであれば１つ繰り下げます

この変数：num_Rowはインデックスの1番目/Label1_NOに反映されます

但し、Max関数を使用することで１よりも小さくならないようにします

そして、インデックスの2番目/Label2_NOと3番目/Label3_NOはインデックスの1番目に１もしくは２を足した値を表示します

こちらのインデックス/Labelを参照して、行列双方向で見出し用の値を抽出します

インデックス数/Labelは表示数を行列ともに固定にします

Index関数による見出し作成

上記のインデックス/Labelを基にして、Sharepointリストから見出し用の値を抽出します

繰り返しになりますが、こちらはIndex関数を使用します

Index関数の中身には２つ設定します

Index(テーブル,位置)

但し、上記のままだとレコードを抽出するだけなので、「.」を使用して抽出する列名を指定します

上記は行方向ですが、列方向も同じです

行方向、列方向ともに表示数は、インデックス数と同様に固定にします

クロス表の中身

クロス表の中身として、行列のインデックス数分の表示を行う仕組みを作成します

こちらは１：現状の中身を示す仕組みと２：中身を追加・もしくは更新する仕組みの２つに分かれます

１：現状の中身を示す仕組み

こちらは複合キーを作成して、SharepointリストからVLOOKUP関数を使用して値を抽出します

ポイントで前述した仕組みをまた活用します

Power Appsの場合はセルの組み合わせではなく、次の画像のようにLabelの組み合わせ、VLOOKUP関数で値をSharepointリストから抽出します

LookUp(T_引当,
　　　　fukugoukey=
　　　　　Label1_PRODUCTID.Text & “-” & Label1_DAYCOLUMN.Text
　　　　).CT

つまり、こちらのSharepointリストから、Labelを組み合わせた複合キーが一致する行を抜き出し、更に個数を表す列/CTを抽出して表示できるようにします

２：中身を追加・もしくは更新する仕組み

こちらはTextInputボックス内にIf関数を使い、新規にSharepointリストに追加する内容と更新を行う内容に区分します

If関数は「OnChange」プロパティに組み込み、入力があれば自動的にSharepointリストに反映されるようにします

条件式にはCountIf関数を使い、既に複合キーがSharepointリストに存在するかどうかを判定できるようにします

If
(
CountIf(T_引当,fukugoukey=Label1_PRODUCTID.Text &”-” & Label1_DAYCOLUMN.Text)=0,

もし、０の場合は「Patch関数」でSharepointリストに追加、０以外の場合は「CountIf関数」でSharepointリストの該当行を更新します

以下は式全体です

———————————————–

If
(
CountIf(T_引当,fukugoukey=Label1_PRODUCTID.Text &”-” & Label1_DAYCOLUMN.Text)=0,
Patch(T_引当,Defaults(T_引当),{fukugoukey:(Label1_PRODUCTID.Text &”-” & Label1_DAYCOLUMN.Text),　　　　　　　　　　　　　　　　　　　ProductID:Label1_PRODUCTID.Text,
　　　　　　　　　　　　　　　　　DAY:DateValue(Label1_DAYCOLUMN.Text),
　　　　　　　　　　　　　　　　　CT:Value(TextInput11.Text)
　　　　　　　　　　　　　　　　 }
),
UpdateIf(
T_引当,
fukugoukey=(Label1_PRODUCTID.Text &”-” & Label1_DAYCOLUMN.Text),
{
CT:Value(TextInput11.Text)
}
)
);
Reset(TextInput11)