タグ別アーカイブ: クロス表

ガッツの日記 第4回 残念なエクセルに出会ったら

【上司はあなたが毎日、エクセルの最終列を探す旅に出ているのを知っていますか?】

私が出会ったエクセルの中で、残念なものの1つが「最終列を探す旅」をするエクセルです

別名は「The ウナギの寝床」です

横長になっている表はいくらでもありますが、私が出会った中で1番凄いものは列がALLを超えてきていました

何故、ここまで横長になるかというと、クロス表の信仰があるからです

表を作る方からすればクロス表にすれば見やすくなるような気がするし、横に延ばすだけで設計が要らず、作るのも楽です

ただ、その後に検索したりするのは大変です

エクセルは列方向にフィルター処理が行えないからです

ですので、最終列を探す旅になります

もちろん、この手の表で共有の管理表を作成して、後で集計するという場合はもっと大変です

各自に口頭で聞いて、集計した方が早いかもしれません

では、

この手の残念なエクセルに実際に出会った時にはどうしたらいいかでしょうか?

それが、この記事のテーマです

組織の壁

他の部署の人がこの手の表を見たら「変えたらいいじゃん」と思います

ところが、一度回りした仕事のツールを変えるのは容易ではありません

ましてや、皆で入力して管理しているエクセル表なら尚更です

「自分が変えます」と手を挙げた途端、以下のようにババを引くような形になるからです

・表を変えて万が一、うまく行かなかったら全て責任を負うような形になる

・必ず誰かは「前の方が使い慣れててよかった」と言う人がいる

・そもそも現在の表のデータを新しい表にどう移行するか?自分が手動で移行することになりそう

・そもそも本来の業務で忙しい

そうして、放置した結果、ますます皆が使い慣れていき、表を変えにくくなるという悪循環が起こります

しかも使い慣れるといっても、業務のスピードがそんなに上がるわけではないのです

大抵、

こういった「残念なエクセル」が業務効率を下げているケースでは、上司がよくそのことを認識していないケースがあります

本日、【Schoo(スクー)】の講座で「ルール(組織のしがらみ)がイノベーションの壁」を受講しました


DX推進者を物語の中の「勇者」の置き換え、DXの目的を「ラスボスを倒すこと」に置き換えて解説しているのですが、これがよく分かりやすい内容になっていました

詳細はぜひ【Schoo(スクー)】のページで確認していきたいのですが、秀逸だと思ったのはDXの推進を阻む「組織の壁」の表現です

〇現場無視系

王様/上司が現場を見ていない

無駄な気飾り/本質とかけ離れた指示

予算がおりず武器が買えない

〇遅延系

え?道具屋のチェックが必要なんですか?

ネットで武器を買おうと思ったらサイトがブロックされている

(以降省略)

この上の「上司が現場を見ていない」というケースは、表現をこの記事の趣旨に沿って変えると「たかがエクセルの表一つで業務が停滞している」ことが伝わっていないケースです

このケースは良くみかけます

先日もある方から「長年、部下がまさかこんなエクセル処理を行ってるとは思ってもみなかった」ということで相談を受けました

よく話を聞いたら、基本的なVBAの処理で済むものでした

場合によっては、管理部門から「残業が多いと注意された」、では「早く帰れ!」と上司が指示を出すこともあるでしょう

ババを引かないためにも、上司に問題の所在を説明して組織全体を巻き込んでいくのがやはり第一歩になるべきです

そうでないと、日常業務がこれまでと同じように降ってきて「業務改善」どころではありません

「そうはいっても、問題の深刻さを分かってもらえない」というケースもあるでしょう

そんな時にはIT部門の力を借りてでも粘り強く説明することが必要かもしれません

技術的対策の実際

では、実際に対策をどうたてたらいいか?

あくまで私がこれまで見てきたケースですが、残念なエクセルの根本的な問題の多くは「データそのもの」にあります

冒頭で紹介した「ウナギの寝床」はまさに典型的です

見やすさや作りやすさを優先して作られています

他の言葉で言い換えると、データの蓄積画面と作業画面を一緒に考えてしまっています

一緒にすると見やすいし、作りやすいような気がします

本来は、データの蓄積と作業は別画面にすべきです

データの蓄積であれば、ウナギの寝床を列数を固定にし、「縦縦の繰り返し」に並べ替えて蓄積します

ここがスタートです

そして作業画面を別途作成します

例えば、データを閲覧するだけであれば、ピボットテーブルで変換することも「データがきちんと蓄積」されていれば可能です

入力が伴う作業画面を別途作成する作業はAccessを使用したりするなど、技術が必要な時もあります

ただ、今ではローコードの技術もあるのでハードルが下がっています

Power Queryで各自が入力したエクセルファイルを、フォルダを通じて集約する技術なんかはオススメです

更新ボタンを押せば、ファイル内の複数ファイルが1つの場所に集約されます

あくまで、入力は「クロス表」で縦横並びにしたいという場合も、ピボット解除行列入れ替えの技術があります

<まとめ>

今回の記事では「ウナギの寝床」のような残念なエクセルに出会った場合の対策について書きました

とにかくこの手の業務を著しく停滞させるエクセルに出会った場合には、組織を巻き込んで対処するのが先決です

放置をすると、どんどん悪循環が起きます

技術的には、まずデータを規則正しく蓄積することを考えるのが先決です

Sharepointリストを活用することもかなりオススメです

3000万行保管でき、行単位でバージョン管理や変更者の特定も行えます

いや、どうしてもクロス表でしか管理できないです、ですから「エクセルじゃなきゃダメです」というケースでもPower Appsという方法があります

詳細はこちらの記事で紹介しています⇒詳細

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

にほんブログ村

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

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

【工夫すれば、使い慣れたエクセルのクロス表を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の魅力及び可能性を伝えられたら幸いです

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

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

ポイント

Index関数

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

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

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

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

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

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

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

複合キー

下の画像をご覧ください

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

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

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

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

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

準備するもの

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

行方向用(行見出し)

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

列方向用(列見出し)

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

中身用

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

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

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

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

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

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

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

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

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

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

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

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

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

Label1_NO

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

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

Label2_NOのTEXTプロパティ

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

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

Index関数による見出し作成

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

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

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

Index(テーブル,位置)

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

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

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

クロス表の中身

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

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

1:現状の中身を示す仕組み

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

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

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

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

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

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

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

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

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

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

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

もし、0の場合は「Patch関数」でSharepointリストに追加、0以外の場合は「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)


最後にReset関数で、入力した値を消去しておきます

列フィルターを行う場合

エクセルとは違い、列フィルターは1つ関数を付け足すだけで行えます

前述のIndex関数の中にFILTER関数を使用し、第一引数のテーブルの絞り込みを行います

見出しを増やす場合

話を単純にするために、行列1つとして話を進めてきました

Power Appsであれば見出しを増やすことも簡単にできます

見出しを増やす場合は、Labelを追加し、VLOOKUP関数やIndex関数を使用してSharepointリストの値を抽出します

上の画像では、Index関数を使用していますが適時、適した方を選べば大丈夫です

<まとめ>

今回は、本来はシステム化が困難なエクセルの列数が可変な表を、Power Appsで実現する方法を解説しました

ポイントは行列の表示数を固定し、Index関数と複合キーをうまく活用することです

上記のポイントを押さえれば、比較的簡単に作成が行えます

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

これからは、Power Appsでどこまで業務を変えられるか?しかも、簡単に作成できるか?に挑戦していきたいと思います

但し、今回は話をシンプルにするために省略しましたが、実際には「委任問題」など問題への対策なども必要なことも最後にお伝えしておきます

ちなみに先日、Power Appsでコネクト4というゲームも作成してみました

結構、簡単にできたので、他のゲーム作成も試してみたいです

Power Apps、本当にこれから面白くなってきます

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

にほんブログ村

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

XLOOKUP関数を使用した複数ドロップダウンリストでの検索

複数ドロップダウンリストとの連動による検索

 今回は、新関数の特徴を活かして「複数のドロップダウンリストと連動した検索作業」をサクッと行う方法を解説します

 この検索方法が行えると、上のGIF画像にあるようなクロス表からの検索がグッと楽になります

「河合さんの数学の点数は?」「中島さんの国語の点数は?」

こんな検索もドロップダウンリストを2回操作するだけで行えます! 

従来であれば、複数の種類の関数を組み合わせなければこの検索は行えませんでした

 今回は、XLOOKUP関数を一度だけ組み合わせるだけで作成する方法を解説します

尚、XLOOKUP関数の基本的な使い方に関しては、過去の記事をご確認ください

今回解説に使用するデータ

今回、解説に使用するデータは、生徒毎の3科目の点数表です

 この表から、下の図の黄色に印を付けた箇所からリストの選択を行うだけで上の表から該当の点数を検索できるようにします

今回のポイント

今回は新関数の特徴を最大限に生かします

新関数の特徴は、1つのセルへの入力で複数の値を抽出できる点です

XLOOKUP関数も新関数ですので、複数の値を抽出できます

例として今回のデータを使用して、XLOOKUP関数を「複数の値」を抽出するように2つ設定してみます

①列方向に抽出

下の図をご覧下さい

 XLOOKUP関数の3番目の引数/戻り列を、1列だけでなく「C列~E列」指定しています

 戻り列を複数指定すると、下のGIF画像のように検索値の「伊藤さん」の全ての点数を抽出します

②行方向に抽出

 XLOOKUP関数のメリットの一つは、従来のVLOOKUP関数では検索できなかった方向にも検索が行えることです

①の列方向に抽出では、「伊藤さん」の点数を全ての科目について抽出しました

今回は、全ての生徒の数学の点数を抽出してみます

今度は、3番目の引数で「複数」を指定します

すると下の図のように、全ての生徒の数学の点数を抽出します

XLOOKUP関数の組み合わせ

では、前述の①と②を組み合わせて、検索を行う関数を完成させます

 列方向に抽出する①のXLOOKUP関数の3番目の引数に、②の行方向に抽出するXLOOKUP関数を組み合わせます

このように「行列」双方向のXLOOKUP関数を組み合わせるだけで、「 複数のドロップダウンリストと連動した検索作業 」を行う関数の入力が完成です

<まとめ>

 今回は、行列双方向に抽出するXLOOKUP関数を組み合わせて「複数のドロップダウンリストと連動した検索作業」を行う関数を解説しました

 今回解説した方法はXLOOKUP関数の2つの特徴、ⅰ)複数の値を出力、ⅱ)行列双方向で検索化、を組み合わせたものといえます

XLOOKUP関数にはまだまだ紹介していない魅力があります

また次回、XLOOKUP関数の魅力について解説します!

長文に最後までお付き合い頂き誠にありがとうございまいた

参考までに今回使用したファイルを添付します

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

にほんブログ村