タグ別アーカイブ: パワークエリ

Power Queryならではのタイムテーブル活用方法

Power BIやPower Pivotではタイムテーブルの活用が欠かせません。ただPower Queryでもタイムターブルの作成と活用を行うことができます。今回の記事ではPower Queryならではのタイムテーブルの活用方法を紹介します

タイムテーブルを活用することで、集計作業で必ず必要になる「前期比での対比」「四半期単位への変換」などを簡単に行えるようになります

ボタンのワンクリック、もしくはたった1行の簡単なコード入力だけで、連続した日付が網羅されたカレンダーテーブルを作成できるのは本当に便利です

Power Queryでも空のクエリから1行のコードを入力するだけで、全く同じようにカレンダーテーブルを作成できます

今回は作成したカレンダーテーブルを活用して、担当者のスケジュール表を作成してみましょう

ポイント

別テーブル作成

カレンダーテーブルを作成するのはもちろんですが、カレンダーテーブルとは別に担当者テーブルを作成してマージします

担当者テーブルを作成する時には、マージする際のキーにする列「」を作成するのもポイントです

もちろんカレンダーテーブル側にも同じ内容のキー列を準備しておく必要があります

ピボット

カレンダーテーブルは当然、下の画像のように縦方向で作成されます

こちらはピボット処理によりに方向を変えます

上の画像の各日付の列には空欄が入力されています

この点については詳細を後述します

カレンダーテーブルの作成

Power Queryのカレンダーテーブルは、空のクエリから波括弧:{}と複数のM関数を組み合わせて作成します

・波括弧:{}⇒..と組み合わせて連続データを作成

#date関数⇒日付データを作成

Number.From⇒数字データを作成

上記の3つを組み合わせてカレンダーリストを作成します

= {Number.From(#date(2022,4,1))..Number.From(#date(2022,4,30))}

リストを作成したらテーブルに変換します

更に日付形式に変換します

担当者クエリの作成

こちらはポイントで前述したように、キー列を設定したうえで作成します

担当者の人数が少なければ、Power Queryエディタ上からの入力も可能です

カレンダーテーブルのピボット処理

前述のカレンダーテーブルには、ピボット処理(縦⇒横)の前に2つの列を追加しておきます

キー列の追加

カスタム列・作成画面にて担当者クエリで作成したキー列と同じ内容の列を作成します

空欄列の追加

こちらの追加処理はとても重要な処理です

この空欄列は、連続日付データをピボット()に並べ替えた際に表の中身となり、スケジュール表の入力欄になるものです

空欄の作成はキー列と同じくカスタム列・作成画面で行います

ピボット処理

ピボット処理は変換タブから行います

上記の「列のピボット」をクリックする前に、連続日付データがある列をカーソルで選択しておきます

「列のピボット」をクリックしたらダイアログボックスが表示されます

こちらで2つ指定します

・値列(表の中身)⇒空欄列

・詳細設定オプション⇒値の集計関数⇒集計しない

上記のピボット処理により、下の画像のような形になります

クエリのマージ

前述の2つのクエリをマージすると、担当者別スケジュール表が完成します

ただキーの列は削除しておきましょう

<まとめ>

今回はPower Queryならではのカレンダーテーブルの活用方法を解説しました

カレンダーテーブルは時系列のデータを集計する上でとても便利なものです

本来はPower Queryではカレンダーテーブルの機能はありませんが、M言語の簡単な仕組みを組み合わせれば簡単に作成することができます({}、..、#date、Number.From)

今回紹介した活用方法は「担当者スケジュール表」でしたが、他にも活用方法があるはずです

ぜひ、自分なりの活用方法を開発してみてください!

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

にほんブログ村

にほんブログ村 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 Query裏技】フォルダにある複数ファイルから特定のシートのみを可変で取得する方法

【Power Queryのパラメーターの仕組みを使い、複数のファイルから特定のシートのデータを可変で取得しよう】

Power Queryのフォルダにある複数ファイルから一括でデータを取得する仕組みは驚くほど便利です

この仕組みで大量の転記処理から解放された人も多いのではないでしょうか?

ただこの仕組みには一つだけ問題があります

ファイル内に複数シートがある場合や、シート名が変更になる場合です

今回の記事では、必要に応じてファイル内で取得するシート名を変更できる方法を解説します

今回使用するデータ

今回使用するファイルには次の画像のように複数のシートが入力されています

このような複数シートがある複数ファイルを一つのフォルダに格納しておきます

こちらのフォルダを指定して複数ファイルのデータを一括で取得します

ポイント

パラメーター

今回は過去の記事で解説したパラメーターを使用してシート名を指定します

今回は上の画像にある「sheetName」というパラメーターを作成し、こちらでシート名を可変で指定できるようにします

ダミーの値によるフィルター

こちらも過去記事で紹介した方法です

簡単に可変の値でフィルターをかける方法です

一度、ダミーの値でフィルターをします

その後、前述のようにフィルターをかけた値に可変にするパラメーターを組み入れます

不必要な列の削除、および行のフィルター

今回の処理では、不要な列の削除と行のフィルターを行う箇所が随所にでてきます

フォルダからデータ取得

従来の「フォルダからデータを取得してエディタを開くフロー」と一部違う点があります

今回は上の画像の箇所ではなく、次の箇所から直接エディタを開き、シート選択のステップは省略します

データ成型

エディタを開いて「適用したステップ」を確認すると、次の画像のようにステップが1つしかありません

エディタの中味はというと、次の画像のようにまだファイル内のデータを取得していない状態になっています

こここから、カスタム列作成画面でM関数を使用してデータを成型していきます

その前に、不要なファイルや列を削除していきます

~で始まるファイルはフィルター条件を指定して除いておきます

その後、「Content」「Name」以外の列を削除します

不要なファイルや列を削除したら、カスタム列作成画面でM関数を使用します

M関数:Excel.Workbookでファイル内のデータを取得できるようになります

次の画像が上記の「M関数を含むカスタム列」を挿入した後の画面です

ファイル内のデータを取得した内容を展開できるようになっています

こちらを展開処理します

ただ1回展開処理しても、まだ次の画像のようにデータ成型したと言える状態になっていません

ここから再び不要な列を削除した後、データ/Data列を展開処理します

ここから、データの1行目をヘッダーにします

その後は不必要な列は削除し、不必要な行はフィルターしておきましょう

*シート名の列は残しておきましょう

パラメーターの作成及びシート名でのフィルター処理

ここからはポイントの項で前述したパラメーターを作成します

上の画像の「新しいパラメーター」をクリックして次の画像の画面を開きます

現在の値には取得したいシート名を設定します

これでシート名を可変にするパラメーターは作成できました

その後、ダミーの値でシートをフィルターします

ここで数式バーに注目してください

ダミーでフィルターした値が数式の中に組み込まれています

こちらを設定したパラメーターで置き換えます

フィルターされたシートがData_202202➡Data_202201に変わりました

これで処理は終了です

試しにパラメーターの設定値を他の値に変えてみます

パラメーターの値に合わせてフィルターされるシート名が変更になっています

<まとめ>

今回は複数ファイルの中にある複数シートの中から、特定のシートのデータのみを一括で取得する方法を解説しました

パラメーターとダミーでフィルターを行う仕組みを組み合わせると、意外と簡単にシート名を可変で取得する仕組みが構築できます

ただ、無駄な列の削除や不要な行のフィルター処理が多い点が難点です

この点さえクリアーすれば、一度作成したパラメーターを何度でも使い回せますので、シートの管理がとても楽になります

ぜひ試してみてください

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

にほんブログ村

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

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

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

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

ポイント

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

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

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

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

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

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

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

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

引き算の追加

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

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

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

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

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

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

ピボット処理

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

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

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

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

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

<まとめ>

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

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

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

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

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

パワークエリ記事一覧

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

初級編

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

中級編

タイトル 内容
複数データの組み合わせ/クエリのマージ~中級編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で手軽にABC分析 パワークエリのM関数をうまく組み合わせれば、ABC分析も行うことができます
RFM分析の「R」を集計する 注文データから注文者(会員)
別に最終注文データを集計できるようにします
RFM分析の「F」を集計する 注文データから注文者(会員)
別に注文頻度を集計できるようにします
RFM分析の「M」を集計してまとめる 注文データから注文者(会員)別に注文金額を集計できるようにします

コラム

タイトル 内容
【パワークエリ・チャレンジ】セル結合が行列にある表をテーブル形式に変換 行列双方向にセル結合がある表をテーブル形式に直します
【パワークエリ・チャレンジ】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へ

にほんブログ村  

【M言語実践】グループ別に累計を集計する方法

過去2回の記事でM言語を実践的に活用する事例を紹介しました

この2回の記事で共通するのは「グループ毎に連番を付与」する技術を使用していることです

今回も「グループ毎に連番を付与」する技術を有効活用して、グループ毎に累計を集計します

M関数のList.FirstNと連番をうまく組み合わせれば、意外と簡単に行えます

ポイント

List.FirstNとは?

List.FirstNは指定されたリストから、指定した条件のリストを作成します

文法としては「=List.FirstN(リスト,条件)」と書きます

例えば、

=List.FirstN(リスト,1)とした場合は1個の値が含まれるリストを作成します

=List.FirstN(リスト,2)とした場合は2個の値が含まれるリストを作成します

このList.FirstN関数の第二引数の条件のところに、グループ別の連番を指定することで、累計の元となるリストを作成します

元データ

今回解説に使用する元データは、下の画像のデータです

こちらのデータのグループに、エディタ内で連番をまずは付与します

上記の画像のようにM関数/Table.AddIndexColumnの第一引数にした列/テーブルは、後でList.FirstN関数の第一引数にしますので削除せずに残しておいてください

List.FirstNによるリスト作成

元データに連番を付与したところで、Power Queryエディタ(以降、エディタ)上で、詳細/テーブルの受注金額をM関数の第一引数、連番を第二引数にしてカスタマイズ列を作成してみます

するときちんとリストが作成できています

では、作成されたリストの中味を見てみます

連番(東京)が1の時には、作成した行の値のみが含まれています

では、連番(東京)が2の時はどうでしょう?

1つ上の行の値と該当行の値がリストに含まれています

今度は、大阪の連番1の場合を見てみましょう!

きちんと大阪の1番目の値/1700000のみが含まれています

作成されたリストの合計

List.FirstNにて各行に作成されたリストは累計の元になるリストになります

最後の仕上げとして、こちらのリストをM関数/List.Sumで合計します

すると、下の画像のように累計が算出されます

<まとめ>

 今回は、M関数/List.FirstNとグループ別の連番を組み合わせて、グループ別の累計を集計しました

今回の集計には、行別のリストが度々登場します

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

この辺りは、手を動かして直感的に理解していった方が習得が早いと思います

とにかく手を動かして身に付けていきましょう!
にほんブログ村

【M言語実践】グループ別に指定桁数にて連番を付与してIDを設定する裏技

 パワークエリはとても便利です。利用者もどんどん増えていると思います。ただ、パワークエリの言語のM言語となると「何ができるの?」となると思いますので、今回は実践での活用例を紹介したいと思います

今回は下の画像の表にある商品に、「グループ別に1から始まる番号」で採番した連番を付与した商品IDを付けたいと思います

今回のポイント

今回はM言語を使用して「グループ別に連番」を付与した後に、”0”で桁数を揃えます

グループ別に連番を付与する方法については過去の記事を参照してください!

こちらは、グループ化機能とM関数のTable.AddIndexColumnを組み合わせます!!

では「”0”で桁数を揃える」という点について、詳細に解説します

例えば、連番が2の場合には「002」にします。もし10の場合には「010」とします

 こちらの桁数を揃える方法については、「例からの列」機能を活用する方法もありますが、今回はM関数の「Text.PadStart」を使用します

このText.PadStartは下のように使います

=Text.PadStar(文字列,桁数,”補う文字列”)

今回は、”0”を補って下の画像のように指定の桁数にします

上の画像では、”1”に”0”を4つ補って5桁にしています

それでは、今回のポイントとなるM関数/Text.PadStartを解説したところで、実際の商品コードの設定に入りたいと思います

文字コードの付与

 今回は、商品グループがCDの場合には「A」、DVDの場合には「B」という風に、商品コードの一桁目を商品グループで表現します

この場合は、条件列を使用するのが一番、手っ取り早いです

例えば、商品グループ名がCDに等しい場合は、Aを出力するといった具合で条件列を設定します

グループ別連番の付与

こちらについての詳細については、過去の記事をご参照願います

概要だけを解説しますと、まずは「すべての行」にてグループ化を行います

すると、グループ別にテーブルが作成されます

その後、カスタム列・作成画面でTable.AddIndexColumnを追加してグループ別に連番を作成します

上の画面でカスタム列を作成した結果が次の画像です

M関数により”0”を補う

 では、グループ別に連番を作成できたので、グループコード/A,B,Cと連番を組み合わせて4桁の商品コードを作成します

グループコード以降は”0”を補って3桁(全体で4桁)にします

M関数/Text.PadStartは次のように使用します

=Text.PadStart(Text.From([連番]),3,”0″)

1番目の引数で「Text.From関数」にて文字列にするのがポイントです

これで”0”を補った3桁が作成できました

この3桁をグループコード/と結合(列のマージ)させれば4桁の商品コードは完成です

次の画像が4桁の商品コードの完成後です

<まとめ>

今回はM言語を使用して、グループ別に1からはじまる連番を採番した上で商品コードを作成しました

ポイントとしてはM関数/Text.PadStartを使用して”0”を指定桁数になるように補う点になります

M関数を使用しない方法もありますが、M関数を使用した方がミスなく作成が行えます

最後に、グループ別に連番を採番する技術はとても実践的な技術です

ぜひ早めに習得しておきましょう!


にほんブログ村