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

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

読者の皆さんも見やすくするためにセル結合を組み合わせた、下の画像のような表をみたことがありますよね!こちらの表を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へ

【パワークエリ・チャレンジ】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へ

にほんブログ村  

データ変換~POWER BIでマイダッシュボード作成・初級_3/8~

静から動へ!Power BIを活用して簡単・手軽にデータを分析可視化できるダッシュボードを作成しよう!】

前回はPower BI Desktopにデータを取得しました。今回はデータ変換を3つ行います(変換種類は2つ)

データ変換は、Power BI Desktopのレポート画面からPower Queryエディタ(以降エディタ)を開いて行います

本題に入る前に、Power BI Desktopの3つの画面とデータ変換内容について簡単に触れます

各画面の内容とデータ変換内容

Power BI Desktop画面は次の3つの画面から構成されます

・レポート画面

・データ管理画面

・リレーション管理画面

レポート画面

Power BI Desktopにデータを取得した時に一番最初に出てくるのがレポート画面です

データ管理画面

画面一番左に出ている3つのアイコンの真ん中をクリックすると、画面がデータ管理画面に遷移します

こちらで取得したデータの中味が確認できます

ここからデータ変換内容について触れます

ヘッダー

下の画像のように、取得した「地域マスタ」「顧客」は1行目をヘッダーに昇格する必要があります

抽出

売上データについては「商品」列にて、記号:_の後の文字を抽出して「ABC」「DEF」という風に商品名を変更しましょう

リレーション管理画面

こちらについては、次回の記事で触れたいと思います

エディタでデータ変換

エディタ画面は、Power BI Desktopのホームタブから下の画像の「黄色の印の箇所」からクリックして開きます

ヘッダー

地域マスタ」と「顧客」データについては、データの1行目をヘッダーとして使用しますので、変換タブから「1行目をヘッダーとして使用」を選択します

抽出

「売上データ」については商品列を変換しますので、変換タブの「抽出」を選択します

次に表示された画面にて、区切り記号を「_」で指定します

変換後の商品列は次の様になっています

適用処理

通常のエディタと処理が違うのがここです

通常のエディタであれば読込処理を行いますが、Power BIでは「適用処理」となります

変換処理が終了したらこちらの処理を忘れないようにしてください

<まとめ>

今回は、Power BI Desktopの画面からエディタ画面を開いて3つのデータ変換を行いました

Power BI Desktopにてデータを取得した際、通常のパワークエリと同様に型式の変換が行われています

 データ取得をした際にこの型式の変換が走る点も、データを取得する際に「ちょっと処理が遅いな」と感じる要因の一つです

本来であれば、上記の3つの変換処理をエディタで行った後、そのままエディタ画面で「地域マスタ」「顧客」「売上データ」の3つのクエリをマージしたいところです

こちらについては次回解説を行いたいと思います


にほんブログ村

【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関数を使用した方がミスなく作成が行えます

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

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


にほんブログ村

マスタデータの履歴管理を行う方法

パワークエリの「クエリのマージ機能」はとても便利で、エクセル関数のVLOOKUP関数より使いやすいです

「クエリのマージ機能」を有効活用すれば、参照表(マスタデータ)の活用もかなり手軽に行えます

 今回の記事では、「クエリのマージ機能」に加えて「グループ化機能」も活用して、マスタデータを更に有効に活用できるようにする方法を解説します!

マスタデータの履歴管理

マスタデータには顧客マスタや商品マスタなど様々なものがあります

種類は様々でも、絶対的なルールが一つあります

これは、マスタデータには重複があってはならないということです

もし、マスタデータのIDが「1,2,3・・・」と採番されていたとしたら、IDの1が2つあってはならないということです

ところが、

マスタデータの変更履歴を残す必要がある場合があります

例えば、以下の画像のケースです

顧客マスタ

上の画像は顧客マスタです

ID:1番の中尾さんのマスタデータが重複しています

何故かというと、IDが1番の中尾さんが名古屋市から東京に引っ越ししているからです

ただし、データ管理上は前の住所も残しておく必要があります

この場合は、日付が最新(最大)のもののみマスタデータとして表示できるように工夫する必要があります

では次から、日付が最新(最大)のもののみをマスタデータとして表示する為のポイントを2つ紹介します

ポイント

グループ化

パワークエリにはグループ化という機能があり、重複を排除してグループ化しつつ合計処理などの操作を行えます

 今回はグループ化機能の操作を「最大」で指定して、IDの重複がある場合には「最大の日付」のものを抽出できるようにします

複数キーによるマージ

エクセル関数のVLOOKUP関数では、検索値は一つのみ指定できます

パワークエリのマージ機能では、実は、複数列を照合列として指定できます

手順

マスタデータからクエリ作成

解説は、前述のマスタデータをテーブル化してエディタを開くところからははじめさせて頂きます

エディタを開いたら、下の画像の日付の列が時刻表示になってしまっています

こちらは、日付形式に直しておいてください

日付形式に直したら、クエリ名を「顧客マスタ_元」としてください

グループ化による最大日付の抽出

まずは「顧客マスタ_元」クエリを「複製」してクエリ名を「顧客マスタ_グループ化」に変更してください

この後、ポイントで前述したように下の画像のような設定でグループ化を行ってください

グループ化(最大)

グループ化を行うと、前述のID:1番の方の日付が最新の日付になっているはずです

クエリのマージ処理

 では、次にクエリのマージを指定しますが、下の画像の「新規としてクエリをマージ」を指定して、新規にクエリを作成できるようにします

マージ対象のクエリは下の画像のように「顧客マスタ_元」を指定します

ポイントの章で前述したように、複数の列を照合列として指定します

 今回のケースの場合は、IDだけでなく「日付」も指定することで、IDに重複がある場合には、日付も一致したマスタデータのみをマージできるようにします

下の画像のように照合列として複数列を指定する場合には、Ctrlキーを押しながら指定します

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

マージする条件を指定した後は、マージされたクエリの中かから「ID列と日付」以外を展開します

マージされた列を展開した後は、列の順番等を整えてください

<まとめ>

 今回は、パワークエリのグループ化機能とマージ機能をうまく組み合わせてマスタデータの履歴管理を行う方法を解説しました

今回の最大のポイントはグループ化機能です

グループ化を行う際に、操作を「最大」にして日付が最大のもののみを抽出できるようにします

今回解説した内容で一点、注意点があります

マージする際に、グループ化した内容は1番目にくるようにしてください

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

元の顧客マスタは、上の画像にて黄色の印をつけた箇所のように、2番目に来るようにしてください

この順番が狂うと、IDが重複したままになってしまいますので注意が必要です


にほんブログ村