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

ガッツの日記 第5回 ChatGPTと残念なエクセルについて議論してみた

【ChatGPTを活用して、日常的なエクセル業務の問題を解決する糸口にしよう!】

今日、【Schoo(スクー)】のライブ授業「議論のプロと考える ChatGPTのプロンプト」を受講しました

ChatGPTの素早い回答を糸口に議論をすすめるのは、なかなか有効だなと感じました

もちろん全てがChatGPTで解決するわけではないですが、ChatGPTとの壁打ちをすることで自分の頭の中もかなり整理されていくのがわかります

完璧にこだわらずにChatGPTと対話を続けるのが有効活用のポイントのようです!

今回は早速、ChatGPTと「残念なエクセル」について議論しました

議論の様子を皆さんにも共有したいと思います

ちなみに本日の講師の方のプロフィールがとてもユニークでした

やはり優秀な方は色々な仕事を生み出していけるのですね!

残念なエクセルの定義について聞いてみた

以下がChatGPTに投げたプロンプトです

箇条書きで・と指定すると個人的には分かりやすい答えが返ってきやすいです

ChatGPTの答えは以下です

↓↓↓

業務効率を下げるような残念なエクセルの定義を以下に箇条書きで示します。これらの要因は、エクセルの不適切な使用や設計に起因するものです。

  1. 複雑な数式と関数:
    • 複雑で理解しにくい数式や関数が多用され、他の人がシートを理解しにくくなる。
  2. 使い捨てのデータ入力:
    • 一時的なデータをエクセルに保存してしまい、データベースなどの適切なツールを使わない。
  3. 繰り返しの手作業:
    • 自動化できる作業が手作業で行われ、時間の無駄になる。
  4. 不十分なデータ整理:
    • データの整理やクリーニングが不十分で、エラーや不正確な情報が含まれる。
  5. 複数人の同時編集の混乱:
    • 複数の人が同時にエクセルファイルを編集し、競合や混乱が発生する。
  6. ファイルの適切なバージョン管理の欠如:
    • 適切なバージョン管理が行われず、誤ったデータや情報が広まる。
  7. セキュリティの脆弱性:
    • 機密情報が適切に保護されておらず、セキュリティの脆弱性が存在する。
  8. 標準化の不足:
    • 統一されたフォーマットやスタイルガイドが不足し、一貫性が欠ける。
  9. バグやエラーの無視:
    • ファイル内のバグやエラーが無視され、問題の修正が行われない。
  10. 過度な依存:
    • エクセルに過度に依存し、他の有用なソフトウェアやツールの活用が不足している。

これらの要因がエクセルの不適切な使用に関連しており、業務効率を低下させる可能性があります。効果的なエクセルの使用には、適切なトレーニング、データ管理、セキュリティ対策、標準化などが必要です。

↑↑↑

2番目に「使い捨てのデータ入力」とあるのですが、これは良く残念なエクセルの特徴を拾い上げているな、と思いました

こちらをChatGPTに再度、聞いてみます

↓↓↓

  1. 会議の出席者リスト:
    • 会議の出席者リストをエクセルに入力し、会議後に使わなくなる。
  2. 電話メッセージの記録:
    • 受けた電話のメモや伝言をエクセルに一時的に記録し、後で削除する。
  3. 一時的な計算結果:
    • 特定の計算を行い、その結果をエクセルに入力して後で必要なくなる。

*一部省略

↑↑↑

この回答だと意味合いは分かるのですが、何が問題かが明確に分からないので再度問いかけてみます


↓↓↓
はい、正確です。使い捨てのデータ入力は、データの規則性や整合性を損なう可能性があります。これは、一時的なデータがエクセルに入力されたり、削除されたりすることで、データの一貫性や整合性が維持されなくなることを指します。具体的には以下の点に注意が必要です:

  1. フォーマットの一貫性の喪失: 使い捨てのデータ入力が行われると、データのフォーマットや構造が一貫性を失い、後でデータを分析や処理する際に問題が生じる可能性があります。
  2. 不正確なデータの記録: 一時的なデータが正確に記録されなかったり、誤った情報が入力されたりすると、データの信頼性が低下します。

*一部省略

↑↑↑

何度か問いかけを繰り返すことで、なかなかシャープな表現が返ってきますね

「表現を変えながら何度も問いかけること」これがChatGPTとの対話のコツですね

解決策について聞いてみた

次に具体策を聞いてみます


↓↓↓
データの規則性を維持し、使い捨てのデータ入力に関連する問題を解決するための具体的な解決策を以下に箇条書きで示します:

  1. データ入力の標準化:
    • データ入力のフォーマットや構造を標準化し、一貫性を保つためのガイドラインを策定します。
  2. 自動化:
    • 一時的なデータ入力が自動化できる場合、スクリプトやツールを使用して手作業を最小限に抑えます。
  3. データベースの活用:
    • 使い捨てのデータ入力よりも長期的なデータ管理のためにデータベースを活用します。データベースはデータの整合性を維持しやすく、クエリや分析にも適しています。

*一部省略

↑↑↑

かなり具体的に聞かないとやはり明確な答えはかえってこないですね

ただ、解決の糸口はそれなりに網羅した答えは返ってきます

ちなみに「ウナギの寝床」横に長いエクセルについても聞いてみました

やはりピボットというキーワードはきちんと入っていますね

結論から言うと、どう解決策を聞くかによるのですが、議論のための資料作りとかには十分役に立つと思います

<まとめ>

今回はChatGPTと「残念なエクセル」について議論してみました

今回はテーマが大きすぎましたが、壁打ちのごとく対話をすればそれなりの答えが返ってくると思います

少なくとも、会議資料みたいなものを作るには十分役に立つと思います

ChatGPTから返ってくる表現は非常にシャープで、網羅性が高いです

会議資料を作成する時には、キーワードを紙に書き出したりするのですが、そのキーワードの網羅作業はChatGPTで十分です!

個人的にはもっとChatGPT、AIを活用していきたいです

【Schoo(スクー)】では「その作業はAIにやらせてみよう」をシリーズで授業がありますので、ぜひスケジュールを確認してみてください!

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

にほんブログ村

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

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

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

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

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

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

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

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

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

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

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

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

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

では、

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

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

組織の壁

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

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

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

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

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

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

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

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

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

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

大抵、

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

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

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

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

〇現場無視系

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

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

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

〇遅延系

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

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

(以降省略)

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

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

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

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

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

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

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

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

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

技術的対策の実際

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

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

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

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

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

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

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

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

ここがスタートです

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

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

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

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

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

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

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

<まとめ>

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

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

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

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

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

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

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

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

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

にほんブログ村

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

ガッツの日記 第3回 私が愛した残念なエクセルを紹介1

【エクセルを見た途端に’絶望’したことはないですか?何回かに分けてこれまで出会った残念なエクセルを紹介していきたいです】

今回の記事では私が実際に作業したエクセルの中で、衝撃を受けた2つのエクセルを紹介します

最後まで記事を読んで頂いても参考になるかは分かりませんが、エクセルの面白さなどを感じて頂けたら幸いです

ちなみに最近、このチャネルが気に入っていて「絶望」という言葉に凝ってます

https://www.youtube.com/watch?v=rwBy8Z5-ONk

10万行にチャレンジ

まず最初は以前、ある会社で毎月作業をするように引継ぎを受けたエクセル表です

記憶も定かでないでないので、詳細は割愛しますがパズルのような作業を10万行分行います

次の画像をご覧ください

Cの列にまだ値は入力されていません

A列とB列の値を見てから、C列に入るものを判断して値を入力します

例えばこんな感じです

・A列:A、B列:A⇒C列:〇

・A列:A、B列:B⇒C列:△

この作業を10万行分行います

ちなみにこのA列とB列の組み合わせは20通りぐらいあります

もちろん、値が同じ行が連続してたり、空白が続く行があったりするので純粋に10万行入力するわけではないです

ただ、それでも物凄い量です

前任者は月に1回は丸一日、この作業に没頭していたようです

「月1回我慢すればいいよ・・・」

と言われても、この手の作業は苦手なので「絶望」の2文字が頭をよぎりました

もう考えるだけで憂鬱です・・・

後から振り返ると、前任者に「こうやっている」と断言されたので視野が狭くなっていたかもしれないですね・・・

しばらくするとすぐに解決策が思い浮かびました

複合キーを作成してマスタを作成することです

こんな感じです

マスタを作成したら元の表でも&を使用して複合キーを作成します

最後にVLOOKUP関数で先ほどのマスタを参照します

IFERROR関数も組み合わせるとなおいいですね!

更にテーブル機能を活用すると、関数は1つのセルに入力するだけで済みます

関数の入力に苦しんでいる方にはぜひ活用して欲しい機能です

ということで、前任者が丸一日かかっていた作業を1分で行えるようになったのですが、何より絶望感から解放されたのがよかったですね

今はエクセルでもいろいろな機能があるので、残念なエクセルに出会っても必ずしも絶望する必要はないですね

セルの値が消えた!?

以前、勤めていた会社では長く受け継がれたエクセルがあり、予算を作成した後にその伝統のエクセルにもコピーし、毎月実績をメンテナンスする必要がありました

誰がどう見ているのかが不思議な内容でしたが、やはり伝統は守るためにあるものです

時間が限られている中で、その伝統のエクセルに予算をコピーする際、不思議なことが起こりました

上期の予算、4月~9月分を伝統エクセルにコピーしました

するとコピー先にコピーしたい内容が反映しないのです

9月まで反映せず、本来7月分の箇所に8月分が反映しているのです

とにかく時間が無いので焦りました

何度もコピーしたのですが、うまくコピーできません

実は

列が非表示になっていました

ここまで引っ張って「それだけ?」と思われた方もいらっしゃると思いますが、まだエクセルになれておらず、しかも時間が無い中なので全く気づけず焦るだけでした

伝統エクセルの非表示の箇所には四半期の数字が入っており、上期小計は1Q+2Qを合計する数式が入っておりました

こうしておいてもらえれば、1年分を一括で入力できたんですがね・・・

後から

前任者から「俺も最初気づけなくて参ったよ・・・」と苦笑いされたことも付け加えておきます

<まとめ>

今回はこれまで出会った残念なエクセルの第一弾として2つの事例を紹介しました

今回の事例は残念なエクセルというより、残念なエクセルの使い方という方が正しいかもしれません

少なくとも1つ言えることは、セル結合もそうですが「非表示」は不用意に使わない方がいいですね

非表示にした本人も忘れていることもあります

話は一見変わりますが、本日、【Schoo(スクー)】田中亨先生のエクセル授業を受けました

内容は、スピル関数からデータモデル、Pythonなど多岐にわたるものでした

本ブログでもPower Queryについてはかなり力を入れて紹介していますが、今のエクセルはかなり多機能です

絶望せずに解決策を探せば、案外簡単に解決策が見つかることもあります

新しいエクセル機能を研究してみると役に立つことも多いです

最後に、

エクセルの引継ぎは、常日頃からきっちり行えるようにしておきたいものです!

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

にほんブログ村

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

Sharepointリストにエクセルファイルから一括でデータを大量追加する

【Power Automateを使えば、Sharepointリストを一気に操作することができます!】

Sharepointoリストはエクセルのように大量にデータを追加できない?そう思われている方も多くいらっしゃることと思います

実はPower Automate/クラウドフローを使えば簡単にエクセルから大量データを追加できます

但し、注意点も多いです

エクセルからPower Automateを使ってSharepointリストに流す方法を知っていても、256行で処理が止まってしまったという経験をされた方も多いと思います

行数の制限を回避する方法も後ほど解説したいと思います

自動追加フロー

準備するもの

追加するデータが含まれたエクセルファイルをSharepointのドキュメント内、もしくはOneDrive内に用意してください

但し、必ずテーブル化を行っておいてください

名前は後で参照するので、分かりやすいものが良いです

デスクトップの場合と違い、テーブル化の場合はショートカットキー/Ctrl+Tが使えないので挿入タブから操作を行ってください

Power Automateフロー

トリガー

「手動でフローをトリガーする」をトリガーにし、いつでもフローを起動できるようにします

エクセルファイルからデータ取得

「表内に存在する行を一覧表示」アクションにて追加するデータを取得します

アクション内では、ファイルとテーブルを指定します

リストへのデータ追加

取得したエクセルデータのリストへの追加は、Sharepointコネクタの「項目の作成」アクションで行います

こちらのアクションにて、取得したエクセルデータを列単位で指定します

下の画像のように、客期コードにカーソルを置くと現れる動的コンテンツから実際の指定は行います

一つでも動的コンテンツを指定すると、自動的に繰り返し処理/Apply to eachが適用されます

つまり、このアクションではN行エクセルデータを取得したらN回項目の作成を繰り返し、リストに追加を行っていきます

注意点

実は処理行数に制限があり、エクセルの追加行数が256行より多い場合でも処理が「256」で止まります

この場合は設定の変更で上限数を増やすことができます

改ページを「オン」にし、行数を増やします

理論上は10万行までは指定できるようです

但し、制限を外せてもかなりパフォーマンスは落ちるケースがありますのでご注意を

(参考)256行制限のまま大量の行数に対応するフロー

最後に、1度にエクセルから取得する行数を「256」に絞りながら処理するフローを紹介します

下の図のように、うまく取得位置をスキップさせながら制限行数の範囲内で処理を繰り返していきます

スキップ数の箇所は変数にして可変にするのもポイントです

こちらの変数は繰り返し処理/Do Untilの中で、繰り返し256行を増やしていきます

Do until内では別途変数(プール値:trueで初期化)を使用し、falseになるまで処理を継続するようにします

こちらの終了判定する変数は、処理の残り必要行数が256未満の時にfalseにします

処理の残り必要行数は「length関数」を使用して取得します

length(body(‘表内に存在する行を一覧表示’)?[‘value’])

残りの行数を取得し、256行未満であれば最後の「項目作成」を行い、変数をfalseに変えます

<まとめ>

今回はPower Automateを使用してエクセルのテーブルからまとめてSharepointリストにデータを追加する方法を解説しました

フロー自体は簡単なのですが、行数が増えてくると扱える行数に制限がかかったりするのが厄介ですので、そちらの対策も解説させて頂きました

最後に、

フローの実行が長引くと「フローがタイムアウト・・・」と出るケースがあります

但し、下の画面/実行履歴を確認すると実際にはまだ動いていることがありますので注意が必要です

この疑似的なものも含めてタイムアウトを防ぐために、下の設定を変えることで対策が行えます

この設定内に「期間」を設定する箇所があります

以下に記載例も示しておきます
PT1M ー1分

PT1H-1時間

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

にほんブログ村

にほんブログ村 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へ

Power BIで抽出したデータをエクセルに転記するボタンを作成する

【今回はボタン1つでPower BIの内容を既存のエクセルファイルに追加できるようにします】

Power BIはエクセルからデータを取得してダッシュボードを作成できます。実は、Power BI内で整理したデータをエクセルにエクスポートし直すこともできます

上記の画像の「エクセル出力」ボタンを押すと、OneDrive内にダッシュボードに表示されているデータが追加されます

準備すること

事前にOneDrive、もしくはSharepointにエクセルファイルを準備してください

列名は事前に固定する必要があります

そして、Power Automateから操作ができるようにテーブル化をお願いします

Power BI DesktopにPower Automateの追加

まずビジュアルのビルドの下にある「Power Automate」のボタンをクリックします

すると以下のような内容がページに追加されます

ここでPower Automateで出力するデータフィールドが指定できるようになります

Power Automateフローの設定

データフィールドを設定したら、三点リーダーから編集をクリックします

次に開いた画面では「新規」をクリックします

内容はインスタントクラウドフローを選択します

ここから実際にフローを作成するのですが、既にこちらの画像のアクションはデフォルトで設定されてあります

次にエクセルコネクタから「表に行を追加」アクションを追加します

「表に行を追加」アクションでは、まず事前設定したテーブル名までを設定します

後は、事前にPower BIからの抽出項目として設定した内容をそれぞれ設定します

「表に行を追加」アクションの設定が終わると「Apply to each」が自動的に設定されます

そしたら保存適用をクリックし、ボタンがページ内にできていることを確認します

発行処理

Power Automateフローの作成が終わり、ボタンを追加したら発行処理を行い、クラウド上でレポートを開きます

これでPower BIからエクセルにエクスポートできるようになります

<まとめ>

今回はPower BIからエクセルにエクスポートする仕組みを解説しました

紹介した内容はとにかくとても簡単です

Power Platformには簡単な事例が詰まっていることがよく分かります

継続的に今回のような有用なネタを投稿していきます

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

にほんブログ村

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

POWER AUTOMATE DESKTOPでエクセルをうまく活用する方法~

Power Automate Desktopをうまく活用すれば、VBAを覚えるよりも簡単にエクセル操作を自動化することができます

今回はPower Automate Desktop(以降、PAD)でエクセル操作をうまく行う方法を紹介します

ショートカットキーの操作

一般操作

PADのエクセルメニューは実際の操作内容と比べると豊富とは言えません

但し、ショートカットキーをうまく使うことでカバーが可能です

例として列の並び替えを行ってみましょう

下の画像のA列を降順にします

降順にするショートカットキーを確認するため、まずは「Altキー」を押します

すると、キー操作でエクセルを操作できるようになります

そのまま並び替えを行うと「Alt⇒A⇒SD」というショートカットキーであることが分かります

このショートカットキーを「キー送信アクション」に設定します

設定したキー送信前に並び替え範囲上にカーソルを置くことも重要です

これでショートカットキーで並び替えが行われるようになります

SUM関数

SUM関数をPADで操作する場合「=SUM()」とセルに入力する方法があります

実はPADの「キー送信」アクションでも行えます

ショートカットキーでは「Alt⇒Shift⇒=」ですが、PADでは=ではなく「OemMinus」と記述します

変数によるシート名設定

PADでは、操作するシートを選択する際には「アクティブなエクセルワークシートの設定」アクションを使用します

シート名の指定が頻繁に起こるようであれば、フローの最初で変数で指定できるようにしましょう

そうすれば、後でエクセル上でシート名を変更した際にPADの修正が楽になります

繰り返し処理

シート分割/ワークシート関数で余りを計算

下のGIF画像では1シートの内容を指定行数分で区切り、新シートに追加しています

最初はAllシートに18行あります

こちらのAllシートの18行をそれぞれ3行に分けて新規シートを追加することで、結果的に6シート追加されます

仕組みとしては繰り返し処理する行数(行位置)をカウントアップする際に、NO(1,2,3)を別シートに書き込んでいます

そしてMOD関数により3で割った余りを計算しています

つまり、繰り返し処理の際に次のような公式が成り立ちます

余り1(例:1や4)⇒別シートへのコピー開始行

余り3(例:3や7)⇒別シートへのコピー終了行

ですので、次の画像のような条件分岐処理を行えば指定行単位でのシート分割が行えます

上記画像の「intMod」は算出した余りの値です

intModが1の時には、コピー開始行の変数:rowStartに行位置を割り当てます

intModが0の時には、コピー終了行の変数:rowEndに行位置を割り当て、サブフローでシートを追加しコピーを実行します

この処理がそのまま他の業務で使えるとは思いませんが、役に立つと思える点が2つあります

1つはPower Automate DesktopではVBAのようにコード内での計算が行えません

ですので、今回のようにワークシート関数を埋め込んだ別シートで計算するのがとても有効なケースがあるはずです

2点目は、何かと余りを計算するMOD関数は使用機会が多いです

余りを活用すれば、思わぬ形で業務の定型化が行えます

下からの繰り返し処理

繰り返し処理は通常、上の行から行います

実は下から処理を行った方がうまく行くケースも多いです

一番分かりやすいのは、行削除を伴う繰り返し処理を行うケースです

上の画像で列1の値が「a」の場合に行を削除するとします

問題は5行目と6行目です

仮に繰り返し処理を行う際の行位置(行数)を変数:myRowで表現するとします

変数:myRowが5になった際には、行削除が行われます

*2行目が削除されるので、実際には4の時です

ここで、行6にあったaは行5に自動的に繰り上がります

一方、myRowは6になり、行6の処理に移ります

ですので、結果的に6行目(下のGIF画像では最初5行目*)のaが未処理で残ります

*上のGIF画像は2行目が削除された状態から始まっています

上のGIF画像では下のaが削除されてもbとcの下のaが残っているのがよくわかると思います

これが、繰り返し処理を下から行うと問題はなくなります

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

行位置を表すmyRowが6になった時に行削除行われ、次にmyRowが5になった時にも削除が行われるからです

つまり、行削除を行った時の以降の行の動きと繰り返し処理の方向が一致するからです

実際にフローについて解説します

下から繰り返し処理を行うために「変数を小さくする」アクションをうまく活用します

行位置の初期値は列の最終行にし、繰り返し変数を小さくしていき、変数が2未満になったら終了するようにします

繰り返し処理がうまく行かないケースは、この下からの繰り返し処理もぜひ検討してみてください

にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村
にほんブログ村 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合格

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

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