タグ別アーカイブ: テーブル

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

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

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

ポイント

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

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

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

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

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

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

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

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

引き算の追加

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

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

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

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

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

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

ピボット処理

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

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

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

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

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

<まとめ>

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

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

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

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

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

ピボットテーブル

ピボットテーブルはとても便利です!

様々な切り口でデータを集計することができます

読者の中には「ピボットテーブルを使わないと、毎月の会議は無事に乗りきれない」という方もいらっしゃると思います

実はピボットテーブルの機能は「データの集計」だけではありません

データ集計以外の機能も有効活用すれば、集計後の業務を大幅に改善しつつ、表自体もプレゼンにそのまま使える「見やすい表」にすることができます!

1.転記・編集作業の改善

 【改善前】集計データを別表に転記し、編集作業

 【改善後】集計データから会議用の資料をそのまま作成

集計データから表作成
新ピボットテーブルデザイン

   解説記事は⇒こちらから

2.グラフ作成作業の改善

 【改善前】集計データを再度編集してグラフを作成

 【改善後】集計データからそのままグラフを作成

   解説記事は⇒こちらから

3.複数資料の作成

 【改善前】集計データから部門A、B用など同じような資料を作成

 【改善後】スライサーで集計結果をフィルタリング

   解説記事は⇒こちらから

4.更新処理

 【改善前】データ更新の度にピボットテーブルを更新

 【改善後】データ更新時にピボットテーブルを自働更新

   解説記事は⇒こちらから

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

テーブル列操作関数

列結合

Table.NestedJoin

文法

キー列を基にして2つのテーブルの内容を結合します

=Table.NestedJoin(テーブル名1,キー,テーブル名2,キー,新しい列名,省略可|結合タイプ)

*結合タイプが省略された場合には、左外部結合が指定される

使用方法

列名をリストとして返す

Table.ColumnNames

文法

ハードコード/自動記録された列名リストをTable.ColumnNamesで置き換えると、列が増えても列名のリストが網羅される

使用前
使用後

実際の使用例

 ➡【豆知識】ピポット解除時の空欄の扱い

リストからテーブル作成

Table.FromColumns

文法

リストを組み合わせてテーブルを作成します

例:Table.FromColumns({[勤務日],[曜日]})

使用例

列変換(研究中)

Table.TransformColumns

文法

元データ
Table.TransformColumns
列変換後


にほんブログ村

M言語に慣れる_6回目~3種類の括弧~

【M言語は難しくない】Power Queryを扱っていると、3種類の括弧が出てくるのが分かります。 「M言語が難解」と感じる理由の一つは、この3種類の括弧が組み合わせられて出てくるからです。

・丸括弧 / ()

・角括弧 / []

・波括弧 / {}

特に下の2つの括弧の[]{}は、初めて見たときは意味合いが全く不明です

実は、この2つの括弧M言語を使いこなすための重要な鍵です

M言語を学ぶ目的の一つは、行列単位での一括処理からの脱却です

シート上でセルを扱うように、Power Queryエディタ上でも1つの値を扱いたいのです

少し、シート上でのセル操作についてお話したいと思います

セル名は例えば「A8」であれば、Aは列名で8は行番号です

この列名と行番号を組み合わせれば、望みのセルにたどり着きます

実は角括弧:[]はシート上の列名に、そして波括弧:{}行番号に相当します

今回はこれらの括弧に、次の2つの方法を通じて慣れて頂くとともに、それぞれの意味合いを掴んて頂きたいと思います!

なお、解説は今回を1回目として、2回に分けて行います

・既存のクエリから角括弧 / []と波括弧 / {}を使い、1つの値を参照する ➡1回目

・空のクエリから3種類の括弧を使って、テーブルを作成する ➡2回目

この3つの括弧の意味合いを掴んだ時には、M言語への苦手意識が薄れているはずです!

目次

今回のポイント

括弧を使ってデータ参照

<まとめ>

今回のポイント

今回の解説にあたっては、そもそも、Power Queryとは何なのか?という点がとても重要です

エクセルはもともと表計算ソフトであり、データベースではありません

Power Queryは、エクセルをデータベースとして使用できるようにするツールです

ですので、エディタ内ではデータベースとして使用するための「データの構造化」が行われています

この構造化が「Power Queryは何か?」を理解するためのキーワードです

まずは、構造化には4種類の概念があることを常に頭に入れておきましょう!

①値

値の種類には、主に次のようなものがあります

・1/ 整数(数値)

・b / テキスト(文字列)

・2021/03/04(日付)

今回の解説で使用する値の概念は、もっと本源的なものです

例えば、1は数字ですが、データベース上は2つの型式になりうります

今回解説する値の概念に照らすと、1は整数でもない文字列でもない、ただの「1」です

②リスト

①の値が、列方向に順次並べられたものです

通常の列とは違います

その点は後で解説します

③レコード

行方向に、複数の②のリストを1行で組み合わせたものです

④テーブル

上の②と③を組み合わせたものです

これらの4つの概念を頭に入れたところで、本格的な解説をはじめます

続きを読む M言語に慣れる_6回目~3種類の括弧~

M言語に慣れる_7回目~空のクエリからテーブル作成~

【M言語はむずかしくない!】今回は、空のクエリから、3種類の括弧を使ってテーブルを作成します。テーブルを作成した後は、M関数も作成を行います!その際、M関数を使いやすくする方法についてもあわせて解説します!!

前回は2種類の括弧を使い、エディタ内で各種参照を行いました

角括弧 / [] ➡ リスト、列の抽出

波括弧 / {} ➡ レコードの抽出

前回で、M言語における2種類の括弧の役割がおぼろげながら見えてきたと思います

 今回、3種類の括弧を使ってテーブルを作成した後は、過去に紹介した方法とは別の方法でM関数を参照する方法についても解説します!

3種類の括弧に慣れると、M言語がグンと理解し易くなりますし、応用も効きます!

ぜひ、実際に手を動かして取り組んでみてください

まずは、空のクエリを作成するところから行います

なお、

他のクエリも後程参照したいので、演習を行う際には、既存クエリが存在するファイルを使用してください

目次

準備作業/空のクエリを作成

リスト作成

レコード作成

テーブル作成

M関数の用法を参照・作成/列名変更

<まとめ>

準備作業/空のクエリを作成

まずは空のクエリから作成します

「データタブ」➡「データの取得」の順でクリックします

その後、下の画像にて黄色に印をつけた箇所を上から順にクリックします

そして、下の画像にある「空のクエリ」をクリックします

するとエディタが開き、空のクエリが立ち上がります

リスト作成

リストは波括弧 / {}を使用して作成します

①値が1個

では、数式バーに={“a”}と入力してみます

リストができ、値がリストに1つ入りました

②値が2個

では、={1,2}と入力してみます

リストができ、値がリストに2つ入りました

続きを読む M言語に慣れる_7回目~空のクエリからテーブル作成~

【M言語に慣れる】_14回目_複数行を1つのセルにまとめる

【M言語は難しくない】今回は複数行に拡散している値を、次のGIF画像のように「記号」をつなぎ目にして、1つのセルにまとめます

目次

今回のポイント

今回使用するデータ

グループ化の実施

カスタム列の追加

<まとめ>

今回のポイント

今回のポイントは2つあります

1つ目は過去記事で紹介した「すべての行」によるグループ化です

この「すべての行」によるグループ化により、グループ別にテーブルを作成します

2つ目はリストの「値の抽出 / 1つのセルにリスト化」機能の活用です

こちらは、実際の例で見てみましょう!

空のクエリで次の画像のように、2つのリストを作成したとします

上の画像のリストを一旦、テーブル化した後、黄色に印を付けた「展開マーク」をクリックをすると「値を抽出する」が選択できます

上のGIF画像のように「値を抽出する」を選択した後は、リストの「区切り記号」を指定できます

「区切り記号」を指定した後は、下の画像のように1つのセルにリストの値が出力されます

今回使用するデータ

今回使用するデータは、次の画像のデータです

名前の列をグループ化し、グループ毎に「商品列に含まれる商品」を1つのセルに出力します

グループ化の実施

今回の解説は、使用データをエディタで開くところから始めます

上の画像にて、黄色の印を付けた「グループ化」をクリックします

なお、事前に「注文日」の列は削除しておきます

グループ化の内容は次の画像のように指定します

・グループ化項目:名前の列をグループ化します

・新しい列名:詳細

・操作:前述のように「すべての行」を指定します

・列:空欄のままでOKです

上記のように指定してOKボタンを押すと、エディタ画面は次のようになります

グループ化された名前毎に、テーブルが作成されています

次はグループ毎に、上の画像の商品列の内容をリスト化します

カスタム列の追加

前述のグループ毎にリスト化するには、カスタム列・作成画面を開くところから始めます

カスタム列・作成画面で、次の画面のように詳細列を指定すると、過去の記事の通りグループ毎にテーブルが作成されます

次の画像の「詳細.1」列が、上の画像から出力されたカスタム列の内容です

今回はテーブルではなく、各テーブル内の商品リストを出力します

リストを出力するには、カスタム列・作成画面にて次の画像のようにリストになる列を加えます

これで、商品リストが各テーブル毎に出力されます

各リストにカーソルをあてると「リストの中味」が次の画像のように見れます

こちらを前述の「今回のポイント」にて紹介したように、「展開マーク」をクリックし、「値を抽出する」から1つのセルにリストを出力します

値を抽出する」をクリックした後は次の画像のように、値を区切る記号を指定する画面が出てきますので、そちらで「記号」を指定してください

記号を指定したら、次の画像のようにグループ毎に1つのセルにリストが出力されています

<まとめ>

今回は複数の行の内容を、1つのセルに出力する方法を解説しました

 ポイントは2つあり、まず1つ目はグループ化において「すべての行」を指定することです

 この「すべての行」を指定すると、グループ毎にテーブルが作成されます

 グループ毎にテーブルを作成したら、「カスタム列・作成画面」にて各テーブルからリストを作成しておきます

 2つ目のポイントは、各リストの内容を「値を抽出する」機能にて1つのセルにリストを出力することです

今回紹介した方法は、それほど使用頻度は高くないと思います

 ただ、今回の内容は、M言語の重要概念であるテーブルとリストを体感するのにいい内容だったと思います

 特にグループ化において、「すべての行」を指定してグループ毎にテーブルを作成するパターンは色々と応用できそうです!

ぜひ、実際に手を動かしてみて試してみてください

記事を最後まで見て頂き誠にありがとうございました

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

次回は、M関数のText関数について2つ事例を紹介します

https://analytic-vba.com/power-query/m-code/begin-text-function/
にほんブログ村 資格ブログ ビジネススキルへ
にほんブログ村 IT技術ブログ VBAへ

にほんブログ村

Office365小技集~右クリックで一発処理~

 今回は、Office365で「右クリック」を使用して、エクセル処理を省力化する技を紹介します

1.Power Query

表の上で右クリックすると、次の画像の箇所がクリックできるようになります

表をテーブル化していない場合には、次の画像の画面でOKボタンを押せば、そのままPower Queryエディタが立ち上がります

もちろん、表をテーブル化してあればそのままPower Queryエディタが開きます

これでPower Queryがより簡単に処理できるようになりました!

2.テーブル

1.と同じ様に右クリックすれば、集計行を出したり範囲に変換することができます

これでわざわざ画面上のタブを操作しなくて済みます

3.フィルター

こちらもとても便利です

わざわざ画面上でフィルターをかけた後に、フィルターをかける値を選択しなくて済みます

今回は以上です

右クリックを活用して、エクセルの単純作業はぜひ爆速化していきましょう!

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

にほんブログ村

クエリのマージ/項目追加~中級編2回目~

中級編2回目は前回紹介したマージの技術を、実際にサンプルを使用して解説を行います

下の図にある商品台帳売上台帳が解説に使用するデータです

こちらの表2つを組み合わせる(マージする)ことにより、2つの表に分かれていた項目を組み合わせて活用できるようにします

下の図のように、売上台帳に商品台帳の「商品名」と「販売単価」を追加します

今回のポイント

A.キー列を設定します

2つの表を組み合わせるためのキー列を指定します

B.組みあせる項目、組み合わせ方を選択します

下記の画面で組み合わせる項目と組み合わせ方を指定します

上の図に”集計”とありますが、集計については次回に解説します

では早速、下記にサンプルファイルをダウンロードして演習をすすめていきましょう!

1.クエリ作成

商品台帳と売上台帳ともに、次の①~③の処理を行い、クエリを作成しておきます

①Power Query Editorを開く

データタブから”テーブルまたは範囲から”を指定して、Power Query Editorを開きます

範囲を指定する際、自動的に題名の部分(下の図であれば商品台帳)がテーブルの見出しとして範囲に入ってしまうケースがあります

こちらについては、題名部分を除いて範囲を指定し直してください

②Power Query Editor上の処理

Power Query Editorの画面右にて、クエリに名前をつけましょう!

マージ処理をする時にクエリー名が分かり易くつけてあると、処理がとても進めやすいです

③読込処理

ホームタブの右上の「閉じて読み込む」ボタンの右に▼マークがあるので、こちらをクリックしてください

すると、次の画面のように「閉じて次に読込む」ボタンが出てきますので、こちらをクリックします

そうすればデータのインポート画面が開くので、読込先を指定できるようになります

下の図のようにデータのインポート画面が開いたら、読込先は”接続の作成のみ”を指定してください

ここまで行えば、クエリが画面右に出来ています!

上の①~③の処理を通じて2つのクエリを作成したら、2.のマージ作業に移ります

2.マージ作業

2つのクエリをマージする場合、事前に2つの方法が選択できます

ⅰ)既存のクエリにマージする方法

ⅱ)新規にマージ用のクエリを作成する方法

今回はⅱ)で行います

➀売上台帳クエリを参照

新規にクエリを作成するといっても、全く一からクエリを作成するわけではありません

売上台帳クエリを基にして新規のクエリを作成します

まずは、売上台帳クエリ上にカーソルを置いて右クリックし、更に”参照”をクリックします

「参照」をクリックしたら、Power Query Editorが開きます!

➁クエリのマージをクリック

新しいクエリのクエリ名は、次の画面のように変更しておきましょう!

③クエリのマージ

次に画面上にある、「クエリのマージ」ボタンをクリックします

④マージするテーブルを指定

マージをクリックした後に、新規に次のGIFにあるように「マージ」方法を指定する画面が立ち上がります

この画面では マージするテーブルとキー列を次のように指定します

テーブル⇒商品台帳、キー列⇒商品コード

⑤マージする項目、方法を指定

④が終了すると下記の画像のように、商品台帳の内容が一番右の列に追加されます

次に、商品台帳の右横にあるマーク(2つの矢印の組み合わせたマーク)をクリックし、実際に組み合わせる項目と組み合わせ方法を指定します

上のGIFのように、マークをクリックした段階では全てにチェックが入っています

ここでは商品名と販売単価はチェックを入れたままにしましょう!

「元の列名をプレフィックスとして使用します」についてはチェックを外しましょう

チェックを入れたままだと下の図のように、余計な名前(商品台帳)が商品名と販売単価の左横に表示されるようになってしまいます

⑥読込処理

今回は”閉じて読み込む”を指定し、接続の作成のみでなく、実際にシートに読み込みます

これでマージ処理は完成です!

<まとめ>

はじめてのマージ処理はいかがだっだでしょうか?

2つの表を組み合わせるにあたり、VLOOKUP関数を2つの列 (商品名、販売単価 で作成する)よりずっと簡単だったのではないでしょうか?

もし、”しまった、他の項目も追加が必要だった”ということになっても、関数を追加したりしなくても、簡単に追加が行えます

具体的な方法については次回、詳細に解説します

次回は単に項目の組み合わせだけでなく、計算処理も行います!

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

にほんブログ村
</a

テーブルを作成しよう

エクセルの表で何か作業をするときには、まずはテーブルを作成しましょう

 最新のエクセルバージョンでは、エクセルのあらゆる機能がテーブル機能を前提に作られているので、テーブル化をしておくのと、そうでないのとでは作業効率がかなり違ってきます

 例えば、下のGIF画像の「今日の日付」の列であれば、テーブル化をしておくと関数を1つ入力するだけで「列のすべてのセル」に関数が反映されます

 通常のエクセルの使い方だと処理はあくまで「セル単位」ですが、テーブル化を行うと「行列単位」になるので表内のデータの扱いがとても楽になります

テーブル化

まずは作業する表のどこかにカーソルを置き、Ctrl+Tを押しましょう

そして、下のGIF画像のように開いたダイアログボックスにて、”先頭行をテーブルの見出しとして使用する”にはチェックを入れたままにしておきましょう

表がテーブル化されると、右下に下の画像のようなマークが入ります

行列の自動拡大

テーブル化を行っておくと表の行列が自動拡大するのでとても便利です

行方向も、既に入力されている関数も含めて自動拡大します

名前の定義

表をテーブル化すると、自動的に名前が付きます

この自動で付いた名前は、画面上のテーブルデザインタブで分かり易い名前に変更することができます

ファイル内でテーブル化されている表はショートカットキー:Ctrl+F3を押すと、下の画像のように一覧で確認できます

関数入力での活用

①自動反映

記事の冒頭で紹介したように、表をテーブル化しておけば「関数の1つのセルへの入力」で列全体に関数が反映されます

②関数での参照

 表をテーブル化しておくと、テーブル外でテーブル内のデータを参照する時にとても楽です

参照範囲を入力することなく、クリック一つで済むようになります

テーブル全範囲

一度、テーブル外のセルで「=テーブル名」を入力してみてください

テーブルの全範囲が反映されます

 こちらの参照の仕組みは、関数の入力時にも活用できるのですが、テーブル名を入力しなくてもクリック一つで活用できます

テーブル範囲の左上にカーソルを置くと、矢印が右下に向きます

矢印が右下に向いている状態でエンターキーを押すと、全範囲が指定できます

*全範囲には見出しがついているケースとついていないケースの2つがあります。それぞれ区別して指定できます

テーブル列範囲

テーブル外のセルでテーブル内のセルを参照してみてください

すると下の画像のように数式に@が入っています

この@を数式から消してみて下さい

すると上の画像のように列参照になります

つまり、テーブル名+角括弧:[列名]で列参照になります

 関数の中で「テーブル名+角括弧:[列名]」を記入して列を参照する方法もありますが、クリック一つで参照する方法もあります

参照したい列の見出しの上あたりにカーソルをおくと、矢印が下に向きます

その状態でエンターキーを押すと列全体が参照できます

行列の挿入・削除及び通常範囲への変換

①挿入・削除

テーブルの上で右クリックすると、挿入・削除が表示されるので、こちらで操作を行います

②通常範囲への変換

テーブルを通常の範囲に戻すには、テーブルデザインタブにて下の画像の箇所をクリックします

ピボットテーブルとの連携

テーブルはそのままピボットテーブルの元データにすることができます

ピボットテーブルの元データの範囲が変更になった際には、本来は範囲の指定し直しが必要ですが、テーブル化をしておけば、自動的に範囲が拡大するので「範囲の指定し直し」は必要ありません

<まとめ>

今回は、テーブル機能の各種操作やそのメリットについて解説させて頂きました

エクセルは元々は表計算ソフトであり、大量のデータを扱うようには作成されていませんでした

ただ、このテーブル機能をフル活用すれば大量のデータも扱いやすいので、ぜひ有効に活用していきましょう

後、Power Queryを活用する際にもテーブルの概念はとても重要になります

 Power Queryの基本的な考え方は、エクセルをセルやシート単位ではなく、テーブル単位で扱う想定で作成されています

Power Queryを学習する際には、ぜひテーブルの機能には慣れておきましょう!

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

Filter関数&テーブル機能でのデータ検索

前回、検索付きリストの中で紹介した”Filter関数”は 1つの関数で複数の値を出力できるのも魅力ですが、テーブル機能と連携など、他にも便利な点がいっぱいあります!

さらに、#とあわせて使うと驚くべき結果が出力されます!

<Filter関数を使うメリット>

!複数の条件指定をAND条件とOR条件にて、簡単に使い分けられる

 ピボットテーブルと違い、AND条件に加えてOR条件も使えます。しかも、フィルター機能等より、簡単に条件指定が行えます。但し、ANDやORという指定ではなくて、*と+にて指定します

 試しに、以下のデータからAND条件でデータを取り出してみます

()内の条件(製品別=トラック、売上金額<40000)を*で組み合わせています

 次にOR条件でやってみます。こちらは+で()内を結び付けます

<Filter関数をテーブル機能と組み合わせて使うメリット>

!カーソルの簡単な操作だけで、大量のデータでも一気に各種の指定ができます

➀範囲指定⇒に注目してください

テーブル範囲の左隅にカーソルを置くと↘がでてきますので、そこでクリックするだけで、全範囲をまるごと指定することができます

➁条件範囲指定

こちらは、テーブルの見出しを指定するだけです、今度は見出しの上、↓のところでクリックするだけで条件範囲を指定できます

最後に、個人的に最も驚いた機能は#です

Filter関数が入力されているセルを指定し、後ろに#をつけるだけで、Filter関数の出力を再利用できます!

 前述の検索付きリストはこの#による再出力データを利用しています!

これはなかなか便利です

今後、他の新たな関数ともあわせて講座を用意してみたいと思います!

最後までお読みくださりありがとうございました!

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

にほんブログ村