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

UDEMYの動画コース「Power Queryを極めたい人必見!!M関数講座」の無料・勉強会の開催報告

昨日、リリース済みのUdemy動画コース「Power Queryを極めたい人必見!!M関数講座」の無料勉強会を開催しました

参加して頂いた皆様には感謝の一言です!

今後の動画コースの内容、並びに、Power Queryの技術改善に多くの示唆を得ることができました!

今回の勉強会では2点「let式を使用した複数ステップの集約」「クエリの関数化」に絞ってお話させて頂きました

長いクエリの対策

Power Queryは便利なので、Power Queryに慣れてくるとどうしても適用したステップが長くなります

ステップが長くなると、後で見返してもどこがどうなっているのかわからなくなったりします

この点が、皆でPower Queryを共有して使う時にも問題となります

今回、日常的に業務でPower Queryを使用している参加者の皆さんと議論させて頂き「長いクエリ」への対策が必要なことがよく認識できました

そして「let式を使用した複数ステップの集約」の技術が長いクエリへの対策に役立つことも、参加者の皆さんに意見や感想をお聞きしてよく認識できました

下の画像のように、カスタム列作成画面でlet式を使用すれば複数ステップを1つのステップに集約することができます

しかも、コメントなども入れれば処理が複数ステップに分かれている場合よりもステップの意図がわかりやすくなります

let式の文法に慣れる必要がありますが、この方式を積極的に使えば、長くクエリをシンプルにわかりやすくできます

クエリの関数化

「販売単価の列」「生存原価の列」と「販売個数の列」を乗算し「利益を計算する列」を作成するなどのカステム関数はこのブログでも紹介しました


今回解説したのは、クエリ自体を関数化する方法です

通常のカスタム関数では各行にて1つの値(販売単価、製造原価、販売個数⇒利益)が出力されますが、クエリの関数化の場合には値の代わりにテーブルが算出されます

今回紹介したのは、ファイル内のテーブルを取得するクエリの関数化ですが、思った以上に使い道がありそうです

複数ファイルを取得するのは、フォルダ内の複数ファイルを取得する方法でも行えます

参加者からおききしたのですが、この方法だとクエリが重くなり、フォルダからファイルを幾つか抜き出すケースもあるそうです

確かにフォルダ内のファイル一括取得は便利ですが、日常業務では確かに頻発しそうな問題だと感じました

今回紹介したデータ取得するクエリの関数化ではフォルダ内のファイルを細分化して取得することができるので、クエリが重くなる可能性を低くできます

データ取得以外にもクエリの関数化の用途がありそうなので、今後も積極的に研究していきたいと思います

<まとめ>

今回は本当に貴重な意見をいくつもお聞きすることができました

今後もユーザーの方との交流しながら、自分自身のスキルアップを図っていきたいと思います

Udemyの動画も頂いた指摘を基にして修正を行っていきたいと思います

⇒Udemyの動画・詳細はこちらから

にほんブログ村

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

複数グループ内で値違いの箇所を見つける~グループ化の応用~

先日ある方から大量の商品データの中から、価格違いの設定が起こっているところを見つけたいという依頼がありました

またルールがあり、同じ素材/同グループであれば販売する色種類が違っていても同じ価格で設定しなければいけないということでした

もちろん、目でみて判断することもできますが、大量データがある場合にはとても困難です

こういった場合はPower Queryグループ化機能で簡単に価格違いが起こっているグループを見つけることができます

今回は次のデータを使って解説を行いたいと思います

ある商品を価格設定するときに、地区内では同じ価格設定にしなければならないものとします

こちらのデータから価格違いが起こっている地区を見つけます

ポイント

Power Queryのグループ化機能では、グループ化の方法として「最小」「最大」がありますので、こちらを活用します

グループ化の適用

解説はPower Queryエディターからはじめさせて頂きます

ちなみに、上の画像では元のエクセルデータにフィルを適用していますので空欄が埋まっています

上記の画像の状態からまずグループ化をクリックします

デフォルトでは「基本」設定になっていますが、今回は「詳細設定」を指定します

こうすることで、「集計の追加」ボタンにより、グループ化の集計方法を「最小」に加えて「最大」も指定することができます

グループ化を実際に適用すると下の画像のような状態になります

グループ内にもし価格違いがあると、上の画像の黄色の箇所のように「最小:1400<>最大:1500」となります

条件列の作成

ここからは条件列を作成して、最小と最大が違うときは「X」そうでなければ「」という表示をする列を作成します

条件列は次のように設定します

価格違いがあるグループと明細リストの抽出

上の条件列を作成すると下の画像のような状態になっています

もちろん、「×」の箇所だけフィルターをすれば、グループ内で価格違いが起こっている箇所は分かります

ただ、下の画像のように店舗と価格もリストにしたいとします

目で元のデータをフィルターする方法もありますが、M関数を使い、自動的にフィルターする方法もあります

まずは下の画像の状態で、適用したステップを「myList」とするところから解説を始めたいと思います

後でこちらの「myList」を参照します

次に

関数マーク/fx*をクリックしたステップを追加した後に、元の「フィルした状態/下方向へコピー済みステップ」を参照します

*参照ステップの記事を参照

これで、グループ化を行う前の状態に戻りました

ここから「地区」列がmyListに含まれるかどうかを判定する列を「カスタム列」作成画面で作成します

List.Contains関数を使うことで、地区の列の値が「myList」ステップの地区列に含まれるかどうかを判定できます

ここから「TRUE」のみをフィルターすればリストは完成です

<まとめ>

今回は、グループ化機能の最小と最大をうまく組み合わせて、グループ内の違う価格設定を発見する方法を紹介しました

ピボットテーブルでも同じことができます

ピボットテーブルの場合には分散や標準偏差を出す方法もあります

グループ内で価格にバラツキがある場合には、分散や標準偏差が0以外になります

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

にほんブログ村

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

フィルターを一発で解除するボタンを配置する~ブックマーク機能活用~

【ブックマーク機能を活用してダッシュボードを楽々操作!】

Power BIのブックマーク機能というのがあるのをご存じでしょうか?

Power BIでのブックマークの意味合いは、日常的に使っているブックマークとは似ているようで違います

「記録しておき、再現する」という点は一緒だという点だけは意識しておいてください

このブックマーク機能を使えば、面倒なスライサーの解除作業などはクリック一つで解決できるようになります

基本的なブックマーク活用の流れとしては、1.ボタンの用意2.操作/状態の記録3.用意したボタンへの記録内容の選択・割り当てになります

ボタンイメージの用意

Power BIの挿入タブには様々なボタンが用意されています

今回はイメージから事前用意した画像を使用します

今回用意したのは次の「×マーク」です

操作/状態の記録

ここから、スライサー等でフィルターが行われていない状態を保存(ブックマーク)します

下の画像の通り、フィルターが一切かかっていないことを確認します

フィルターの状況を確認したら、表示タブにある「ブックマーク」をクリックします

ブックマークの欄がでたら「追加」ボタンをクリックします

そうすると、記録内容が追加されます

追加された記録内容は後で「選択・割り当て」が行いやすいように名前をつけます

上の画像の「・・・」をクリックすると、名前が変更できるようになります

今回は「Filter Clear」という名前にします

用意したボタンへの記録内容の選択・割り当て

フィルターが解除された状態を「Filter Clear」に記録したので、今度は、「×マーク」に「Filter Clear」に選択・割り当てます

ページに配置した×マークをクリックすると上の画像のように「アクション」が選択できるようになります

アクションを「オン」にし、型を「ブックマーク」に指定します

その後「ブックマーク」の右に△マークをクリックすると、先ほど記録した「Filter Clear」を選択・割り当てできるようになります

×マークに「Filter Clear」を選択・割り当てをしたら、Ctrlキーを押しながら×マークをクリックすると、フィルターが解除されるようになります

<まとめ>

今回はPower BIのブックマーク機能を活用してフィルターを解除する方法を解説しました

フィルター解除の設定の流れは次の通りとなります

1.ボタンの用意

2.操作/状態の記録 ⇒ブックマーク機能

3.用意したボタンへの記録内容の選択・割り当て ⇒アクションで割り当て

Power BIではエクセルと違い、ボタンによる設定できる操作は充実しています

ボタン周りの操作についてはまた別な記事で解説していきたいと思います

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

にほんブログ村

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

【パワークエリ・チャレンジ】セル結合が行列にある表をテーブル形式に変換

読者の皆さんも見やすくするためにセル結合を組み合わせた、下の画像のような表をみたことがありますよね!こちらの表をPower Queryの基礎技術を組み合わせてテーブル形式にしましょう!

この手の表は見た目がよさそうな気がしますが、データを追加したり、編集するのにとても不便です

「この表はもう加工・編集のしようがない・・」

そんなことはありません、あくまで「ピボット解除、行列入れ替え」「フィル」「列のマージ」「列の分割」などの技術の組み合わせで簡単にテーブル形式に直せます

今回は行列ともにセル結合がある表を扱いますが、行方向のみの場合は過去にも記事を書いてますので、ぜひそちらもご参照ください ⇒過去の記事

今回のポイントは列のマージと列の分割の組み合わせです!

行列を入れ替えるのと、フィルで空欄を埋めるのは行方向のみセル結合がある場合と一緒です!

小計や合計の行列を消去

解説はPower Queryエディタの状態からはじめさせて頂きます

小計の行や合計の列は必要ないので、フィルターしたり列の削除を行い、消去してしまいましょう

下方向のフィル

一つ目のセル結合はフィルで埋めます

フィルは変換タブから行います

下の方向でフィルを行えば、下の画像のようにnull/空欄が埋まります

列の結合

後でデータを扱いやすくするために、部門と四半期の列は「列のマージ」により1列にまとめてしまいます

列のマージは変換タブで行います

列のマージ時には「-」などの記号により、で分割しやすくしやすくしておくのがポイントです

列のマージを行ったことで、表がシンプルに整理されてきました

行列の入れ替え

この後、行列の入れ替えを行いますが、1つ準備が必要です

見出しをデータに降ろす必要があります

変換タブにで「ヘッダーを1行目として使用」をクリックします

下の画像が見出しをデータに降ろした状態です

ここから「関東・・・の行」も含めた形で行列の入れ替えを行います

行列を入れ替えると、再びフィルでセル結合を埋めることができます

条件列&フィル

ここからフィルで一気に空欄を埋めたいところですが、よくみるとnullでなく「列・・・」で埋まってしまっています

ここは条件列を使用して、列から始まる箇所をnullに変換しておきます

これでフィルで空欄を埋めることができるようになりました

フィルで空欄を埋めると、いよいよ最終形が見えてきます

ここからは①行をヘッダーに昇格②ピポット解除を行います

そして最後に列をマージした列を再度分割します

ピボット解除

ピボット解除を行う前に1行目をヘッダーにします

こちらも前回同様に変換タブから行います

これでピボット解除を行う体制になりました

ピボット解除は左2列を軸にして行います

「その他のピボット解除」を行うと最終形の一歩手前です

ここから、前述の通り、列の分割を行います

列の分割は、列のマージを行ったときに「列の間に入れた記号」を指定して行います

列の分割を行えば、これで最終形となります

後は列名などを調整しましょう!

<まとめ>

今回は長文にて行列双方向にセル結合がある複雑な表をテーブル形式にしました

長文ではありますが、使用している技術は基本的な内容ばかりです

ただ、一つ抜けるとすべてが狂ってしまうので、その点だけは注意頂きたいと思います

今回、記事を作成してみて感じたのは「見出し」が無い表は結局、あまり見栄えがよくはなりません

その点は日常的に意識していきたいものです

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

にほんブログ村

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

【パワークエリ・チャレンジ】パワークエリを使用して月間スケジュール表を作成する

 今回は、開始時間・終了時間・担当者を可変で指定できる月間スケジュール表(記入用)を、Power QueryM言語の技術をフル活用して作成します

4月1日であれば、13時から15時まで1時間刻みで行が自動で用意されます

担当者も可変にできます

一番のポイントは「開始」「終了」時間の判定列を、日付(1~31)と時刻(0:00~24:00)を網羅した表に作成することです

しかも判定用の列は2列用意します

判定列を作るためには、複数のクエリを用意し、クエリ内にキー列を作成しておくこともとても重要です

キー列:yyyy/MM/dd hh:mm

内容的に大きめの内容なので、今回はポイントの羅列で失礼させて頂きますので予めご了承ください

Power Queryでここまでできるのか?という点を感じて頂けたら幸いです

今回の内容の参考記事についても事前に紹介させて頂きます

複数データの組み合わせ/クエリのマージ~中級編1回目~

見出しがセル結合により2行になってしまっている表をデータ活用1~上級編2回目~

IF式を組み合わせて列作成~上級編10回目

M言語に慣れる_4回目~特殊テンプレート作成~

【パワークエリ・チャレンジ】1列のデータから複数列のテーブルデータを作成する

変換した列の追加~中級編10回目~

ポイント

各種データの用意

今回、想定する作業は以下のスケジュール表から、必要な時間だけ記載され、担当者も記載された完成スケジュール表を作成することです

スケジュール表

下の画像が完成スケジュール表です

完成スケジュール表

実際にPower Queryのクエリを作成する前に、3つの表(日付データ、時刻データ、担当者)を用意します

日付データと時刻データについては、あとで組み合わせて、次のようなクエリ(日付時刻クエリ)を作成します

こちらの結合済みの列は後でスケジュール表とマージして「開始時間」「終了時間」を紐づけるためのキー:yyyy/MM/dd hh:mmになります

日付データ

4月のスケジュールであれば、1日から30日までを用意します

*ホームタブの連続データの作成を活用するとデータの用意が早いです

時刻データ

どの月かは別にして0:00~24:00までを用意しておきます

こちらは固定になります

担当者

こちらも用意します

日付時刻クエリとスケジュール表クエリのマージ

前述のように日付と時刻を組み合わせて作成した日付時刻クエリはスケジュール表クエリとマージして、開始時刻と終了時刻を紐づけます

マージするためのキーはyyyy/MM/dd hh:mmになります

スケジュール表をマージするためにも、事前にスケジュール表はピボット解除を行っておきます

そして日付と時刻の列はマージして「yyyy/MM/dd hh:mm」のキー列を作成しておきます

フィル機能の活用

キー列をもとにして、2つのクエリをマージするとキー列に「開始時間」「終了時間」を紐づけることができます

但し、開始時間と終了時間を判定するには、判定列が1列だけでは不十分です

1列だけだと、終了⇒開始(上の画像だと16時以降)までの時間帯が判定できないからです

フィル機能を使って、下に埋めた列を作成します

2列あれば、残すべき行を特定できます

IF式

判定列を利用して、残すべき行を判定します

判定2が開始の場合には行を残します。判定1と2が終了の場合にも残します

*1の場合は行を残す、0の場合は行を削除する

逆にそれ以外は行を残しません

担当者の紐づけ

担当者はカスタム列を作成してリストで紐づけを行います

*担当者はクエリ名です

リストで紐づけを行い、展開した状態が下の画像の状態です

ピボット処理

担当者の列の内容は、下の画像のように見出しにします

この表の値欄は空欄になるので、ダミーの列を事前に追加しておくのも大きなポイントです

その後にピボット処理を行います

最後に

以上が今回のポイントなのですが、作成していくと、ところどころで並び順が変わってしまうと思います

その際には、インデックス列を追加して調整を図ったください

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

参考までに今回のサンプルファイルを添付します

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

にほんブログ村

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

オシャレなカフェでPower BIレッスン~手ぶらでレッスンを受けれます~

【未経験者・歓迎!ローコード時代の主役・Power BIの習得をカフェではじめてみませんか?】

Power BIは高度なIT知識がなくても、直感的な操作のみで高度なダッシュボードが作成できる優れたツールです

但し、通常のエクセル操作と勝手が違うので、最初は「Power BI独自」の癖をつかむのが困難です

ぜひ、この「カフェでのレッスン」をPower BI習得のきっかけにしてください!

内容:次のGIF画像のダッシュボードを作成します

内容詳細

①データ取得、②データ変換、③リレーション作成、④グラフ作成、⑤グラフ間連携

*あくまで未経験者向けの内容になっています

講師:ガッツ鶴岡(当ブログ管理人) 

*慶応義塾大学医学部が中心のコロナ制圧タスクフォースに昨年、Power BIの技術指導をした実績もあります

料金:¥5,000円(税込み)

*当日までにクレジットカードでお支払い頂きます(お茶代込み)

日時:①3月27日14時~、②3月27日16時半~

*レッスン時間は90分~最大100分の予定です

教え方:マンツーマンでのレッスンです

*PCは貸出しますので手ぶらで来ていただいて結構です

*先着順ですので、お申込みを頂いても申し込みが成立しないケースがあります。その点はあらかじめご了承ください

*使用した資料やデータは別途メールで送付いたしますので、復習に活用できます

場所茶房・はちはち

*飯田橋駅・神楽坂駅から徒歩10分ほどです(お申込みを頂いた後に、詳細な案内図をお送りします)

クイズ番組などでおなじみのフリーアナウンサー・天明麻衣子さんの父君。「テンパパ」が運営するカフェです

*紹介記事:https://www.nikkan-gendai.com/articles/view/life/234956

コロナ対策

①マスク完全着用で行います、②手の消毒、PCの消毒を徹底します

*席は並んで座ります。予めご了承ください

お申込み方法

問い合わせにメールにてお願いします

*単なる質問でも構いません

ぜひお申込みお待ちしています!

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

にほんブログ村

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

IF 式の中でOR条件とAND条件を使う方法~Power Query~

IF式の中にOR条件とAND条件を組み入れて、条件式をうまく構築しよう】

Power Queryのカスタム列ではIF式を活用することができます

過去の記事でも紹介しましたが、IF式を複数ネストすることもできます

今回の記事では、IF式の条件式にAND条件とOR条件を使用する方法を紹介します

ポイントは何気にANDORの大文字と小文字の区別です

OR条件

下の画像には「A,B,C,D,E,F,G」のいずれかが含まれた文字列を含む列があります

「A」または「B」の文字列を含む場合には「true」をIF式で出力してみましょう!

If式の中にはText.Contains関数を使用します

このM関数は指定文字が含まれている場合には「true」を出力します

以下が今回のIF式です

if (Text.Contains([文字列],”A”)=true) or (Text.Contains([文字列],”B”)) then true else false

OR条件は小文字の「or」で指定する点は注意が必要です

AND条件

今度はAND条件にて「A」且つ「B」を含む場合には「true」を出力しましょう

今回もAND条件の指定を小文字にする点は注意です

if (Text.Contains([文字列],”A”)) and (Text.Contains([文字列],”B”)) then true else false

これでAとBを両方含む1行目だけがtrueと出力されました

<まとめ>

今回はPower QueryのIF式内にAND条件とOR条件を指定する方法を解説しました

なるべくIF式はすっきり記述した方がいいので、()を入れて条件式を明確にする工夫などを行って行きましょう

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

にほんブログ村

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

パワークエリ記事一覧

M関数逆引き辞典はこちらから!

初級編

タイトル 内容
Power Queryで操作はどう変わる?~初級講座1回目 パワークエリとはなにか?そのメリットをスマホに例えて解説します
シートからデータ取得~ 初級講座2回目~ 別シートのデータをテーブル化して取得する方法を解説します
ファイルからデータ取得 初級講座3回目 別ファイルのデータをテーブル化して取得する方法を解説します
フォルダから複数ファイルデータを一括取得~初級講座4回目 フォルダ内にある複数のファイルからデータをまとめて取得する方法を解説します
ブログ管理人・Udemy講座

中級編

タイトル 内容
複数データの組み合わせ/クエリのマージ~中級編1回目~ クエリのマージ技術の概要について解説します
クエリのマージ/項目追加~中級編2回目~ 複数のクエリをマージする方法について、実際のデータで解説します
クエリのマージ/計算処理~中級編3回目~ マージした項目を活用して計算処理を行う方法を解説します
クエリの追加作成~中級編4回目~ 同じ項目のクエリを追加して結合する方法を解説します
クエリの追加・結合~中級編5回目~ クエリの追加をピボットテーブルと連携して行う方法を解説します
ピボット解除/縦横並びの変換処理~中級編6回目 縦横の使いにくいデータを、使いやすい並びに変える方法を解説します
セル分割他・変換処理~中級編7回目 複数のデータが1つのセルに入っている場合に分割する方法を解説します
セル分割他・変換処理2~中級編8回目 前回の内容を使いやすい形式にして出力する方法を解説します
エディター内のやり直し操作~中級編9回目~ ワークシートにおけるCtrl+Zに相当する処理の方法を解説します
変換した列の追加~中級編10回目~ 連番、条件列(IF関数に相当)及び列削除・移動他を解説します
グループ化1~中級編11回目~ データを階層化して集計する方法を解説します
複数条件でグループ化~中級編12回目~ データを複数の条件で階層化する方法を解説します

上級編

タイトル 内容
行削除とヘッダー行の調整~上級編1回目~ 見出し行/ヘッダーがおかしい場合の対処方法を解説します
セル結合により見出しが2行の表をデータ活用1~上級編2回目~ セル結合された使いにくいデータを使いやすくする方法を解説します
セル結合により見出しが2行の表をデータ活用2~上級編3回目~ セル結合された使いにくいデータを使いやすくする方法を解説します
各種集計/合計から四捨五入まで~上級編4回 四則演算の計算から四捨五入まで、様様な集計方法を解説します
エラー修正_処理ステップエラー~上級編5回目 ファイル保存先変更などに伴うエラーの対処方法について解説します
途中のステップを削除した場合のエラー修正処理~上級編6回目 途中のステップを削除した場合のエラー対処処理について解説します
エラー修正_データ自体のエラー~上級編7回目 データ自体がエラーになっている場合の対処方法について解説します
カスタム関数を自身で登録して使い回す~上級編8回目 ユーザーが独自で作成するカスタム関数の作成方法を解説します
カスタム関数を自身で登録して使い回す2~上級編9回目 作成したカスタム関数を呼び出して活用する方法を解説します
IF式を組み合わせて列作成~上級編10回目 条件付きカスタム列を作成する方法を解説します
「例からの列」にて変換パターン自作~上級11回目~ 入力した変換例を汲み取って変換を行う方法を解説します
「 例からの列」による桁数が規則列への対応~上級12回目~ 「例からの列」による変換方法の応用方法を解説します
エディタを開かずにソース変更処理~上級13回 「パラメーター」機能を使用してデータソースを変更する方法を解説します
シート上からデータソースを変更する方法~上級編14回目 シート上の入力を変更するだけでデータソースを変更する処理を解説します
セルの値を変更するだけで読み込みを変更する方法~上級編15回 セルの変更内容をクエリに組み入れて、クエリの内容を変更できるようにする方法を解説します
クエリのコピー・バックアップ・削除他~上級編16回 クエリのコピーやバックアップ及び削除、その他グループ化などの方法を解説します
文字列の抽出~上級編17回 RIGHT関数やLEFT関数、MID関数に相当する内容に加え、特定の文字列を抽出する裏技を解説します
文字列の追加、置換~上級編18回 前回の特定の文字列の抽出に加え、文字列の追加や置換方法について解説します
エラー発生の予防/列のデータ形式変更と削除について~上級19回~ エディタ内の操作のちょっとした工夫で、エラー発生を予防する方法を解説します

M言語編

タイトル 内容 使用するM関数
M言語に慣れる_1回目~コード構造の把握~ Mコードの基礎的なルール/文字の色、大文字の区別、ステップの参照について解説します List.Average/平均値・計算
M言語に慣れる_2回目~カスタム列~ カスタム列・作成画面でM関数を使用して新たな列を作成する方法を解説します Text.From/文字列への変換
M言語に慣れる_3回目~ダイナミックフィルタリング~ セルの変更内容をフィルタイングの内容に反映する方法を解説します List.Max/最大値の計算
M言語に慣れる_4回目~特殊テンプレート作成~ 2つのクエリを組み合わせて、1担当者に対して複数の勤務日がある表を作成する方法を解説します
M言語に慣れる_5回目~M関数を検索する方法~ ワークシート内の関数のように、M関数を参照する方法を解説します Text.PositionOf/特定の文字列の位置抽出
M言語に慣れる_6回目~3種類の括弧~ 丸括弧と角括弧そして波括弧とテーブル、リスト、レコードの関係を解説します
M言語に慣れる_7回目~空のクエリからテーブル作成~ 空のクエリから、リストやレコード、テーブルを作成する方法解説します #table/テーブル作成、Table.RenameColumns/列名変更
M言語に慣れる_8回目~eachを使いこなしてM関数作成~ 数式バーに良く出てくる「each」の用法について解説します Table.SelectRows/テーブルから特定の行を抽出、List.Select/リストから特定の値を抽出
M言語に慣れる_9回目~複数シートをコード1行で結合~ ファイル内のシートからデータをまとめるクエリを作成する方法を解説します Excel.CurrentWorkbook/ワークブックの内容を一括抽出
M言語に慣れる_10回目~グループ毎に連番作成~ 「グループ化の操作/全ての行」を活用してグループ毎に連番を作成する方法を解説します Table.AddIndexColumn/連番作成
M言語に慣れる 11回目~前行を参照して計算 連番と波括弧:{}を組み合わせて活用して前行を参照する方法を解説します
M言語に慣れる~12回目例外処理 エラーが発生した際の対処処理を行う方法を解説しています
M言語に慣れる13回~ダイナミックに連続した日付作成 ワークシート内にある連続性のない日付から、連続する日付を作成した後、他データも紐づける方法を解説します #date/「年、月、日」から日付を作成、#duration/「日、時間、分、秒」から期間値を作成します、List.Dates/「開始日、リスト個数、増分」から日付のリストを作成、Number.From/値を数値型式にして返します
M言語に慣れる14回目~複数行を1つのセルにまとめる グループ毎のテーブルを作成後、グループ別に1つのセルにテーブルの内容をまとめる方法を解説します
M言語に慣れる15回目~TEXT関数/文字列関数 テキスト関数の事例について解説します Text.Contains/文字列の有無を判定、Text.Replace/文字列の置換
M言語に慣れる16回目~List関数 リスト関数の事例について解説します List.Max/最大値の計算
不規則に散らばっているデータを一括取得~M関数~ 見出し行/ヘッダーがそれぞれ違うシートの内容を一括でまとめる方法を解説します List.PositionOf/リストの中から特定文字列のリスト内位置を抽出、Table.RemoveFirstN/テーブルの先頭から指定行数を削除します、Table.PromoteHeaders/先頭行を新しい列見出しにします
参照先バッファー化によるパフォーマンス向上~M関数~ クエリのパフォーマンスを向上させる方法を解説します Table.Buffer/参照テーブルのバッファー化
クエリの列名をダイナミックに変更する~M関数使用~ 入れ子のリストを使用して、ワークシート上の内容でクエリの列名を変更する方法を解説します Table.RenameColumns/列名変更、Table.ToColumns/テーブルからリスト(入れ子)作成
Power Queryエディタ上で複数条件付き合計処理~M関数使用 M関数を組み合わせて「SUMIFS関数」と同様の計算をエディタ上で行う方法を解説します Table.SelectRows/条件に合う行のみを抽出、List.Sum/合計値を抽出
指定した範囲の値を使用してフィルタリング M関数を利用して、ワークシート上の表にある値に一致するもののみをフィルタリングする方法を解説します List.Contains/リストに指定の値が入っているかを判定
複数リストを1対1の関係でうまく組み合わせる 1行に複数の値を持つリストを複数組合わせる場合に、リスト間で1対1の関係になるようにします(例:8月2日/月、火、水➡8月2日/月) Table.FromColumns/複数のリストからテーブルを作成
列名をダイナミックにしたピボット解除を行う 新たな列を追加した場合でも、並べ替える列とそうでない列をコントロールできるようにする Table.ColumnNames/列名リストを作成
PDF変換の応用~フォルダに入れた複数PDFから都合良くデータを切り取る方法 複数のPDFファイルから都合よい箇所だけデータを一括抽出できるようにする Table.RemoveFirstN/指定行から上の行を削除
List.PositionOf/リストの中から特定文字列のリスト内位置を抽出
【POWER QUERY裏技】フォルダにある複数ファイルから特定のシートのみを可変で取得する方法 フォルダ内のファイルを一括で取得する際に、パラメーターを使用して取得するシートを可変にします Excel.Workbook/Excel ブックの内容を返します。

分析編

タイトル 内容
POWER QUERYで手軽にABC分析 パワークエリのM関数をうまく組み合わせれば、ABC分析も行うことができます
RFM分析の「R」を集計する 注文データから注文者(会員)
別に最終注文データを集計できるようにします
RFM分析の「F」を集計する 注文データから注文者(会員)
別に注文頻度を集計できるようにします
RFM分析の「M」を集計してまとめる 注文データから注文者(会員)別に注文金額を集計できるようにします

コラム

タイトル 内容
POWER QUERYをクラウドで活用する方法~POWER AUTOMATE使用~ DataverseとPower Automateを組み合わせて、Power Queryをクラウドで活用します
(続編)2つの表にてどの行が追加、削除されている? ⇒どの列が変更になっている? ピボット解除と複合キーを活用して2つの表を比較しやすくします
どの行が追加、削除されている?~結合の6種類~ 結合の各種類をつかいこなせば、行の追加や行の削除の状況が一目でわかります
【パワークエリ・チャレンジ】セル結合が行列にある表をテーブル形式に変換 行列双方向にセル結合がある表をテーブル形式に直します
【パワークエリ・チャレンジ】行ではなく、列方向でフィルター・置き換えを行う方法 実は行列の入れ替えを行えば、列方向でのフィルター処理が可能です
【パワークエリ・チャレンジ】1列のデータから複数列のテーブルデータを作成する 余りの計算とピボット機能を組み合わせて1列のデータから複数列のテーブルデータを作成します
【M言語実践】グループ別に累計を集計する方法 グループ別に累計値を集計する方法を解説します
【M言語実践】顧客の2回目のリピート率を分析する方法 通常のエクセル処理であれば相当煩雑な分析処理をM言語を活用して簡略化します
M言語実践】グループ別にIDを設定 グループ別に連番を採番した上で桁数を揃えてIDを作成します
マスタデータの履歴管理を行う方法 マスタデータが重複している場合に、日付が最新のもののみを表示する方法を解説します
POWER QUERYのステップ数を減らす3つの工夫 「変更された型」のステップを減らすなどの工夫を解説します
エディタからデータ入力してクエリ作成 エディタ上で直接データを入力する方法を解説します
【豆知識】どこの行が変わったかを調べる方法 クエリのマージの仕方を工夫しつつ、条件列を組み合わせて、元データと修正があった箇所を特定する方法を解説します
【豆知識】ピボット/縦縦並び⇒縦横に並び替え集計 ピボット解除と別な方向で並べ替える方法を解説します
【豆知識】ピポット解除時の空欄の扱い グループ化
【分析作業用】大量データを1目で把握する パワークエリの表示タブでは大量のデータを1目で分かるようにする為の工夫がありますので、そちらを解説します
【分析作業用】グループ毎の平均と個の平均の差を自動集計する方法 グループ化機能を2段階で行うことで、グループ単位の集計と個々の集計とを同時に行う方法を解説します
クエリの複製、参照、そしてコピーとの違い~ クエリを右クリックした時に出てくる「複製」「参照」「コピー」の違いについて解説します
POWER QUERYって何?~エディタ上でのショートカットキー エディタと他ファイルを並行して開く方法も含めて、操作を早く行う方法を解説します
POWER QUERYって何??~時刻を曜日と日付に変換 時刻データを曜日や、日付データに変換する方法を解説します
POWER QUERYって何??~経過時間を一括で計算する ワークシート関数では、直接計算できない経過時間を簡単に計算する方法を解説しています
POWER QUERYって何??~日付を月や四半期単位に変換~ 日付データを簡単に月や四半期単位に変換する方法を解説します
POWER QUERYって何??~読込んだデータに行番号追加~ シートに読込んだクエリに行番号を追加する方法を解説します
文字列を記号毎に分割し、行方向に展開する方法 コンマなどの記号をもとにして文字列を分割しつつ、列方向ではなく、行方向に展開する方法を解説します
POWER QUERYって何~既存クエリのステップを一括で削除する方法 適用してステップの欄で一気に複数ステップを整理する方法を解説します
POWER QUERYって何?12~重複のないリストを一瞬で作成 一気に重複を排除したリストを作成する方法を解説します
POWER QUERYって何??11~ユニークな数の集計~ パワークエリならではの集計方法を1点だけ解説します
POWER QUERYって何?~ピボット解除(縦横並び替え)と入れ替えの違い~ 紛らわしい「ピボット解除」と「行列の入れ替え」の違いについて
POWER QUERYって何??9~WEBページからデータ取得~ Webページからエクセルデータを直接取得する方法を解説します
POWER QUERYって何8???~PDFファイルをエクセルに変換~ PDFファイルからエクセルデータを直接取得する方法を解説します
POWER QUERYって何?~列の結合/複合キー活用~ 複数の列を結合して複合キーを作成・活用する方法を解説します
文字列から空白を取り除く_POWER QUERY 文字列の前後や、文字列間に入り込んでいる空白を取り除く方法を解説します
パワークエリエディターで行コピーを行う方法 M関数を使用して行をコピーして複数にする方法を解説します
MICROSOFT POWER QUERY~ACCESSとEXCEL連携~ 特殊な方法でAccessと連携する方法を解説します
POWER QUERYって???合計を計算する グループ化による合計金額の計算方法を解説します
POWER QUERYって何???連番作成 連番を作成する方法を解説します
POWER QUERYって何??パワークエリの使用メリット パワークエリを使うメリットの一つは履歴の管理が便利な点があげられます
POWER QUERYって何???~ACCESSとエクセルの連携~ 通常の方法にてAccessと連携する方法を解説します
ピボット解除を複数列を軸にして実行する 複数列を軸にして縦縦並びを縦横並びに変える方法を解説します
IF 式の中でOR条件とAND条件を使う方法~POWER QUERY~ ANDとORの指定を小文字で行うのがポイントです

IT未経験からDX人材に!

【そのITへ取り組み、まずは身近なエクセル業務の改善、VBA、そしてRPAからははじめてみませんか?】

技術の進化により、実務の現場での「ITの力」の存在感は日々増す一方です

このブログの読者の方の中には、こんな悩みを持つ方もいらっしゃると思います

・会社からDX人材になるように求められているが、どうしたらいいのか分からない

IT部門やIT関連の打ち合わせが多いが、話しについていけない

AIRPAなどによる業務削減が進んでいて、近いうちに自分の仕事もなくなる恐怖がある

・システムエンジニアにキャリアチェンジしたいが、プログラミング知識・経験が全くない

仕事が忙しく、VBAなどで事務処理を効率化する必要があるが始め方が全く分からない

私は45歳から全く未経験のシステムエンジニアの世界に入りました

もちろんプログラミングの経験などは全くありませんでした

 6年のシステムエンジニアとして過ごす中で、後半の3年は「豊富な業務知識と経験」と研修抜きのまま、走りながら身に付けた「RPAVBAの技術」を活かして「現場のブラックボックス業務」の解消を行うプロジェクトをこなしました

 6年のシステムエンジニアとして過ごして感じたことは、ITの世界には「抜け道」があることです

・ノーコードの技術に代表されるように、専門知識があまり必要ない技術が出現している

・ITの分野は細分化されており、どんどん新たな分野が出現している

・人の仕事を代替するRPAの開発などでは、プログラミング技術より業務経験が活きるケースがある

 未経験だから・年齢が若くないから・といった理由で、ITの世界では全く通用しないということはありません

 逆に未経験だからこそ、若くないからこそ「今から」ITの世界を知る必要があります

何故なら、今の世の中ではITの力は決して欠かせないものだからです

 ぜひ、このブログで「ITへの取り組み」を本気ではじめるきっかけになれば幸いです

このブログを活用する上で、1点だけお願いがあります

このブログで記事を読んだら、ぜひ実際に手を動かしてみてください

このブログで紹介する内容は、無料で環境構築の必要がないものです

そして、手を動かす中でITの楽しさを体感して頂きたいと思います!

エクセル業務の改善

 まずエクセル業務の改善をするのが何故、ITと関係あるのかを解説したいと思います

ITとは「Information Technology」の略です

日本語に訳すと「情報技術」です

 そして、情報の基礎となるのがデータあり、データを扱う典型的な業務がエクセル業務です

エクセル業務はビジネスでつきものです

 このエクセル業務を削減すれば、日常業務をかなり削減できるだけでなく、ITに取り組む上で重要な「データ管理」について学ぶことができます

このデータ管理を本格的に行う仕組みがデータベースです

 データベースを作成すれば、ファイルやシート間の転記を行ったりする必要はなく、1か所でデータを管理できます

更にデータを様々な方で検索したり、利用することができます

 エクセル業務を効率化しようと思ったら、このデータベースの構築が1つの柱になるはずです

 本来、Microsoft社にはAccessというデータベースソフトがエクセルとは別にあります

 ところが、エクセルは進化を続けており、今ではエクセルもデータベースのように活用できるようになっています

 ぜひエクセルをデータベースとして活用してITの見識を深めつつ、業務の削減にお役立ててみてください

Power Query

 Power Queryに代表されるモダンエクセルは、エクセルを使いながらAccessなどのデータベースの技術を活用できる画期的な技術です

 コードを一から書くなどの小難しい技術は必要なく、スマホ感覚で活用できます!

 Power Queryを始める前にまずはテーブル機能に慣れて頂くと、Power Queryをより理解し易くなります

 Power Queryは従来のエクセルの使い方の様にファイルやシート単位でデータを扱うのではなく、テーブル単位でデータを扱うのが特徴になっています

初級講座 :データの取得(抽出など)、読込など

中級講座:データの結合などの変換

上級講座:縦横並びの変換などの特殊な変換処理

コラム:PDFファイルをエクセルに変換するなどのテクニックの紹介

M言語

VBA

 プログラミングをはじめるなら、エクセルVBAからはじめるのが一番てっとり早いです

 環境構築の必要はほとんどなく、身近なエクセルを動かせるのでプログラミングとは何かを理解し易いと思います

 このブログでは楽しくプログラミングが学べるように、VBAで作成したゲームを提供しています

https://youtu.be/2MoC-zCCN8k

 このゲームでは登場人物の動きに合わせてコードも表示されますので、「英語映画の字幕学習」のような感覚で自然とVBAを学べるようにする工夫も行っています

ぜひ一度、ダウンロードして試してみてください

VBA

 VBAを学んでいくと、1日要していた仕事を一瞬で処理できるようなマクロを作成することができるようになります

 そしてVBAを突き詰めていくと、単に業務削減が行えるだけでなく、新たなキャリアを作る機会も出てきます

・社内で業務改善やDXを推進する部署への移動

・他のプログラミング言語も学んでシステムエンジニアとして転職

・スキルシェアサービスなどで副業

後述するRPAなどでは、RPAと組み合わせてVBAを使う機会も多いです

これはRPAの対象業務や周辺業務がエクセル業務であるケースが多いからです

ですので、VBAを覚えるとRPA関連の仕事や副業を行える機会も出てきます

Excel VBA超入門第1弾エクセルが自動で仕事する!マクロの魔法 文系・非IT職もできるプログラミング

エクセルその他

ダッシュボード作成

グラフデザイン

裏技

RPA

 RPA(ロボティクス・プロセス・オートメーション)は人間の代わりに作業を行ってくれる技術で、今後、市場が継続的に拡大していくことが期待されています

 RPAはノーコードと言われているように、あまり小難しい技術を必要としませんので、ITが未経験の方でも取り組みやすい分野です

RPAの作成を一言で表現すると、ブロックの積上げです

 1つのブロックにはそれぞれ1つの命令が含まれており、ブロックを積み上げるとフローチャートができあがります

 ですので、RPAの作成を複数回行って行けば自然とプログラミングの力、的確な業務フローを作る力が身に付いていくというメリットもあります

 RPAの案件は実務に直結した小さな案件も多く、プログラミング技術自体よりもIT以外での業務経験が活きるケースがあります

 RPA関連のサービスも様々なものが登場しており、これまでITと縁が無かった方でもRPAを学ぶ事で、隙間時間に副業を行うなどの新たなキャリアも描きやすくなってきております

RPA HACK フリーランス

 今ではMicrosoft社のPower Automate Desktop(以降PAD)などのRPAが無料で簡単に使えます

 RPAは何かを実際に動かして試してみたい方は、ぜひ実際にPADをインストールして手を動かして試してみて欲しいと思います

無料RPA・Power Automate Desktop逆引き辞典

無料RPA・Power Automate Desktop入門

☆★オススメIT学習方法~動画活用~☆★

IT学習のコツは、楽しく手を動かすことです

但し、時間や予算は無限ではありません

限られたリソースの中で、どのような学習方法を構築していくかはとても重要なことです

今回は動画を活用した、効果的な学習方法を紹介します ⇒記事

☆★オススメIT学習方法~基本情報技術者試験~☆★

基本情報技術者試験はITの登竜門です

この試験に取り組むことで、ITのフィールドで幅広く活用する機会が広がります ⇒記事

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

にほんブログ村  

【パワークエリ・チャレンジ】1列のデータから複数列のテーブルデータを作成する

【割り算の余りの算出とピボット機能をうまく使ってVBAと同等の処理を行ってみましょう】

今回は下の画像のように1列のデータから、画像右側のようなテーブルデータを作成してみます

ポイント

ポイントは1列のデータの中で、何行目を何列目にするのかを明確にすることです

「何行目を何列目」にするかが分かれば、実はピボット機能(ピボット解除の逆)で簡単にテーブル化が行えます

インデックス列の追加と余りの計算

解説はエディタからはじめさせて頂きます

1列のデータをテーブル化してエディタを開いたら、空白をフィルターで取り除いた後に、下の画像のようにインデックス列を追加します

その後に「何行目を何列目にするか?」の印を作るために「インデックス列」を3で割り、余りを算出します

上の画像にて黄色の箇所をクリックした後、開いた画面で3を指定します

すると次の画像のような列が追加されます

引き算の追加

このままピボットをすると次の画像のようになります

空欄が混じってしまい、テーブルデータとしての規則性がありません

ですので、上の画像の「インデックス列」の代わりに3行毎集約するキーが必要になります

今回は、インデックス列から剰余の列を引くことで、下の画像のように集約キーを作成します

この引き算の処理により山田さんから始まる3行はに、木村さんから始まる行はに集約されます

引き算は余りの計算と同じ「標準」で行います

ピボット処理

では集約キーができたので、ピボット処理にて剰余の列の内容を列名になるようにします

このピボット処理は、「剰余列」を選択した上で、変換タブから行います

 列のピボットをクリックしたら、値列は列1の内容になるように指定してください

 そして、「詳細設定オプションにて」上の画像のように「集計しない」を指定してください

これで列名を変更して、無駄な列を削除したら完成です

<まとめ>

今回は、意外な方法で1列のデータから複数列のテーブルデータを作成しました

改めてPower Queryのピボットなどの機能は素晴らしいと感じました

 今回のケースは特殊であり、実際の使用機会はあまり想像がつかないですが、データ整形の際には断片的に活用できる機会も多いかと思います

ぜひ一度チャレンジしてみてください

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