[Power BI Desktop] 3桁カンマをレポートのビジュアルでつけたい

セルの書式設定がある Excel からすると「なにそれ？」なタイトルですが、Power BI サービスで分析結果のデータを共有するために、レポート、ダッシュボードの準備で Power BI Desktop を使っていると、早い段階で「？？？」と、はまるネタです。数値データに3桁カンマをつけたい、です。

ちなみに、Excel で数値データに3桁カンマを付けるのは、当然リボンのコマンドでもできますが、ショートカットキーは、Ctrl+Shift+1 (つまり、Ctrl+!)です。
さらに、ちなみに（笑)、この Ctrl+Shift+1はトグルではないので、3桁カンマの解除はこのショートカットを再度押してもできません。標準の書式を設定するショートカットがあり、それが Ctrl+Shift+チルダ(~)です。チルダは、ひらがなの「へ」ですよ。標準に戻すこのショートカットは「知っていれば」意外に使う場面が多いと思います。

ExcelユーザーにとってのPower BI Desktopは「ピボットテーブル レポート & グラフ」

私はここに行き着くまで結構時間がかかりました（笑）。
というか、Power BI Desktop のレポートに、もっとも近いのは Excel の「Power View」なんですが、あまりに知名度低いし、今となっては「黒歴史」的に、無かったことにしたいような扱いの機能なので、あえて、ピボットテーブル レポートとピボット グラフ、としました・・・。技術的に Silverlight と HTML5 に翻弄されちゃいました、という感じでしょうか。

[Microsoft] Excel 2013 で Power View シートを作成する

Power View を無かったことにしたいような扱いの話はこちらで紹介してます。

[Road2CloudOffice] Power BI と Excel

さて、Power BI Desktop はもちろん、Excel のピボットテーブル レポートもそうなんですが、最終的なレポートの形が、おぼろげながらも「ぼんやり」とないと、何のデータをどこから手を付けていいかわからなくなります。

ただ、そんな中でも、Power BI Desktop で、取り込んだテーブルの列の「合計」や「個数」はビジュアル（視覚化）の「カード」を使えば、すぐに「レポート」で表示することができます。このあたりの操作性や動きはピボットテーブルに近く、数値のみの列であれば合計が表示され、数値以外の数値があれば個数が表示されます。

ピボットテーブルの「フィールドリスト」のようなウィンドウから数値の列(フィールド）を選べば、自動的に「合計」が表示されます。縦棒グラフのビジュアルが使われるので、カードのビジュアルを選ぶと、もし桁が多いと以下のように表示されます。

これは、ビジュアル(視覚化）の書式-データラベル-表示単位が「自動」になっているためで、これを「なし」に変更します。感覚的にはここに「桁区切りを使用する」がありそうなのですが、ありません。

そして、この操作手順の状態で、リボンの「モデリング」の書式設定に「桁区切り記号」があるのですが、グレーアウトして選択することができません。

このカード ビジュアルの数値に3桁カンマをつけたいのです。が、レポートでカードのビジュアルを選んだ状態だけでは、桁区切り記号のコマンドはグレーアウトしている・・・

最初のころ、これで悩みました。

そのころの対応は、いったんデータに戻って、その列（フィールド）に対して「桁区切り記号」をつけていました。データ側の列に桁区切り記号をつけると、レポートのビジュアルの数値も桁区切り記号がつきます。

ただ、この方法、独自にメジャーを作ったときに、対応する列はなく、関連する列をすべてに桁区切り記号をつけても、3桁カンマがつかないのです。

書式設定はビジュアルではなく、フィールドを選択して行う

つまり、データに戻ってフィールドに書式設定はあまり良いやり方ではななかったわけです。
レポートのビジュアルに表示されている数値に3桁カンマをつける＝レポートで「モデリング」タブの「書式設定」のグレーアウトを外すのは、表示形式を変えたいビジュアルを選択するのではなく、右端にあるフィールドリストのフィールドを選択すると、書式設定可能になるのです。

対象となるビジュアルは選択する必要はなく、作成したメジャーはデータにはありませんが、フィールドリストに表示されるので、そのメジャーを選択すると、リボンで書式設定が可能になります。

わかってしまえば、なんともない操作なのですが、結構長い間適切な操作をしていなかったので、ご紹介しました。

[追記]
[Power BI Desktop] 数値の 3桁カンマ、再び。
https://road2cloudoffice.blogspot.jp/2017/02/power-bi-desktop-3.html

Read More

[Power Query / 取得と変換] ブックにある複数のワークシートをまとめる 2

前の投稿で、複数のワークシートをまとめる、というお題を取り上げましたが、これ、「今、Excel で開いているブックにある、複数のワークシートにあるテーブルをまとめる」という処理でした。

Excel でデータを扱うのなら、Excel でブックを開くのが鉄則

ただ、この手の処理は、同じブックで処理するよりも、元データと、加工・処理するブックは「違う」パターンが多いです。元データのブックは、別の部門だったり担当者が準備したり、更新したりしていて、そこから加工するのは自分、みたいな感じです。

Excel の処理として、扱うことのできるブックは「Excelで開いている」ことが鉄則です。

そのため、基本的には Excel でデータを扱いたいのであれば、まずは Excel でブックを開く、という処理が「必須」でした。 Excel で一度開けば、Excel という Application を頂点とした、「ブック - シート - セル」という階層構造で処理することができるからです。

VBAでも同じです。ただし、裏技的にブックを開かずにデータを取り出すことが技術的には可能でしたが、広くお勧めできるものではありませんでした。

[VBA] ブックを開かないで読む (Office TANAKA)
http://officetanaka.net/excel/vba/tips/tips28.htm

Power Query でブックを開かずにデータを参照する

という前置きは置いておいて、Excel でブックを開かずに、別ブックから、そこに含まれている各ワークシートのテーブルを一気に取り込んでしまう処理は、アニメーションGIFで一気に紹介できるくらい「簡単」なんです。
 前回は、既に開いているブックのシートにあるテーブルから、それぞれクエリを作成しましたが、今回は、ブックをExcelで開くことなく、指定するだけで、そこにあるテーブルを「テーブル」という条件で絞り込み、リスト化して、一気に展開する、という流れです。

(1) ブックを選んで、テーブル選ばす

[新しいクエリ]  - [ファイルから] - [ブックから] で、Excel ブックを指定できます。このとき、ブックに複数のワークシートがあれば、ナビゲーターウィンドウにそれぞれが表示されます。テーブルも同様です。

通常はここでワークシートやテーブルを選ぶ、または、[複数のアイテムの選択] オプションをチェックして、複数のワークシートやテーブルを選びます。

複数のワークシートやテーブルを選んだ場合は、その数ぶんの「クエリ」を自動的に作成します。それぞれのクエリが作成された後の、たとえば追加（ユニオン）の処理は、前の回で紹介した手順と同じです。

このテーブルやワークシートのオブジェクトを「選択する」条件を含めてクエリ化したいのです。
もう一度、ナビゲーターウィンドウをみると「フォルダー」のようなアイコンがあります。実は、これは Excel のブックそのものを表しているんです。

このフォルダの形をした「ブックアイコン」を選択して、[編集] ボタンを押すと、ブック内のオブジェクトとその属性がテーブル形式で編集可能になります。

ここで、オートフィルターの要領で、必要なオブジェクトの絞り込みが可能になります。

アニメーションGIFでは種類(Kind)でTableを指定して、テーブルオブジェクトだけに絞り込んでいます。

もちろん、名前で「～から始まる」や「～を含む｝で絞り込みをかけることも可能です。

そして、このブックだけしか選ばない方法の最大のポイントは、作成するクエリはたった1つしかない、ということです。よって、テーブルが何個あろうが、クエリは１つであり、以下で紹介するように、複数のテーブルを追加するのは、たった1回ボタンを押すだけでいいのです。

(2) 追加(Append = Union)は「同じ形式」であることだけ気を付け、展開する

絞り込んだ後、テーブルの中のデータを取りだす（展開する）のは、ボタン１つを押すだけでOKです。

ただし、展開する前に不要な列を削除します。不要な列を削除、というよりは、「必要な列だけを残す」という作業が本質になります。

アニメーションGIFでは、展開する [Data] 列と、元のデータがどこかわかる [Name] 列を残して、他の列を削除しました。

[Data] 列の先頭行にある展開・集計するボタンで、展開します。

 

(3) 別のシートが増えても対応可能なクエリ

このクエリはクエリ内の適用済みステップで、ワークシートやテーブルを明示的に指定していません。 「指定したブックにある、テーブルオブジェクトをすべて集めて展開する」というクエリになっているからです。

元データのブックが更新されたら、加工用に用意したクエリを更新することで、最新のデータを取り込みます。もちろん、名前の知らない新しいワークシートの新しいテーブルが追加されても、クエリの編集なしにデータを取り込むことができます。

(4) 列順序は関係なく、列名が重要

テーブルオブジェクト(ListObject)は「構造化参照」されますが、このクエリにおいても構造化参照されています。列名を使って、それぞれのテーブルの列の追加を行っているのです。
そのため、列の順序、順番は関係なく、同じ「列名」を持つオブジェクトが「追加」されます。

[日付] [番号] [数値] という列順序のテーブルと、[数値] [日付] [番号] というテーブルは、問題なく追加可能だ、ということになります。

業務のほとんどは、まとめるべきワークシートの名前が決まっていることが多いですが、日付や期間、時期のようなワークシート名だと、ブックを開くたびにワークシートの数と、新たなワークシートが追加されている、というケースも少なくありません。
VBAを使えば解決していましたが、逆にいえば VBA を使わなくても、Power Query / 取得と変換の機能でここまでできることは、知っていて損はないと思います。

Read More

[Power Query / 取得と変換] ブックにある複数のワークシートをまとめる

私もよく参考にする日本マイクロソフトの「Data Platform Tech Sales Team Blog」ですが、最近の投稿で「Excelマクロから卒業?」というタイトルのブログがありました。

私も以前に紹介したことのある、上記のブログのポイントである「ピボットの解除」などは、たしかにこれまで VBA で行うことになる難しいお題でした。これがクリックだけでできるようになるのは、「これからは Power Queryか？」とか思いますよね。

特にブックやワークシートを扱う系の処理では、これまで VBA の独壇場だったエリアに Power Query の適用が可能になっているので、ますます VBA卒業？とか言われそうです（笑）。（まぁ、卒業の前に入学してから・・・みたいな話もあるのだけど）

今回はブックにある複数のワークシートを扱う（まとめる）方法をご紹介したいと思います。
2つのケースを説明しますが、いずれもデータを含む表は「テーブル」になっているものとします。もちろん、Power Query ではテーブルになっていない「セル範囲」の表も扱うことができますが、その場合必要な列や行を指定して、データだけにするステップが追加する必要があります。

テーブルにしていれば、データだけを絞り込むステップは必要ありません。

ワークシートの数も名前もわかっている場合

考え方として、各ワークシートにあるテーブルから必要なデータを取り出すクエリを作成し、それらを「追加（Append)」します。データベースの世界や Access の世界では、この「追加」を「ユニオン(Union)」と呼びます。追加やユニオンは、基本的には同じ構造（列名を含む）のテーブルをつなげるイメージです。

4つの支店や部門みたいなグループがあって、そのデータをワークシート毎に分けている、といったサンプルです。札幌、東京、大阪、福岡などのワークシートがある、という感じですね。

それぞれのワークシートに「日付」、「番号」、「数値」のデータがあって、札幌、東京、大阪、福岡からデータを抜き出す、みたいな感じですよね。（最初から、地域みたいな札幌～福岡を入れる列をいれて１つのテーブルに・・・というのはわかります。が、ここではシンプルなサンプルを使いたいので、あしからず。）

名前がわかっている（＝固定されている）ので、それぞれのワークシートから必要なデータ（もしくは全件）を条件を付けないで、全件取り込むクエリを作ります。これらから、4つのテーブルを追加（ユニオン）するクエリを作ってみます。実務では必要な「列」だけにしたり、条件を付けて行の絞り込みをすることになるでしょうね。

また、この例では、絞り込み条件なしで、各ワークシートにあるテーブルを全件読み取るクエリを「接続のみ」で作成します。元データと同じテーブルのワークシートを追加しても仕方ないですからね。さらに、どのシートから読み込んだかをわかるように新たに列を追加して、ワークシート名を追加します。

今は複数のクエリの追加(Append)は簡単になりました。以前は、2つしか追加できなかったので、2つを追加してから、その追加クエリにクエリを追加して・・・を繰り返したのですが、今は、3つ以上のクエリを追加するを選ぶと、一気に複数のクエリをAppendすることができます。

Append1というクエリが、4つのテーブルのクエリを追加＝Unionしたクエリです。
ワークシートにクエリ結果を全く展開していません。このクエリの使い方は、ピボットテーブルのデータソースにして、欲しい形でピボットテーブル レポートを作成することになります。

ここで、Excel のピボットテーブルの機能としての知識が必要になります。

ピボットテーブルの作成は「挿入」タブの「テーブル」グループにある「ピボットテーブル」から行いますが、作成のダイアログで [外部データ ソースを使用] を選び、[接続の選択] で「Append1」の接続（クエリ）を選びます。この指定で、作成するピボットテーブルの元データに作成したクエリを指定することが可能になります。

はい、これ、複数のテーブルからピボットテーブル レポートを作ることになりますが、データ モデルを使っていません。よって、データ モデルを使ったピボットテーブルの「制限」がありません。
使うかどうかは別として、「集計フィールド」と「集計アイテム」が使えます。

でも、正直なところ、今から Power Query + ピボットテーブルで集計フィールドと集計アイテムを覚える必要はないと思います。集計フィールドは Power Queryの追加の列そのものですし、Power Query とデータ モデルもしくは Power Pivot でメジャーを使うのが主流になりそうです。

このサンプルは、1回きりだと意味がありません。それぞれのテーブル データが更新される、追加されるなど、元データに変更がある場合、一度このクエリとピボットテーブルを作れば、クエリ＋ピボットテーブル更新で、最新データにすることができます。

そう考えると、このケースは「別ブック」である場合のほうが、より実務的ですよね。

次は、別ブックの場合の手順を紹介したいと思います。このとき、ワークシートの数は名前を知らなくても、取り込むことができます。となると、なんかVBAっぽいでしょ（笑).

Read More

[ピボットテーブル / リレーションシップ] 元データにない項目をピボットテーブルで表示させたい

以下のようなケースではどうすればいいでしょうか？という質問を Office TANAKA セミナーでいただきました。
ものすごく簡略化したケースで検討しますが、たとえば、アンケート結果の集計のようなもので、評価項目はA, B, C, D, Eまであるんだけど、Eを付けた人はいない、というデータがあったとします。

こんな感じのデータを想定します。

このテーブルからピボットテーブルを作ると以下のようになります。

元データのテーブルには評価で「E」というデータはありません。この元データからわかることは、AからDまでは評価のデータとして存在するが、Eもしくはそれ以降のデータがあるかどうかは判断が付きませんから、当然といえば当然です。

とはいえ、操作している側としては、Eまで評価があり、それを含めて以下のようなピボットテーブルが欲しい、と考えるのは当然ですよね。

出力用で、A～Eをあらかじめ用意し、GETPIVOTDATA関数とIFERROR関数を使って抜き出し、ゼロの対応が可能です。もちろん、簡略化しているので、この程度のサンプルならピボットを使うまでのことはなく、COUNTIFで十分だろう、というのはご容赦ください（笑）。

あくまで、ピボットテーブルの機能で対応しようとすると、評価の行ラベルが AからEまであることをピボットテーブルに伝えなければなりません。

実データを元にしてラベルを作る(=重複しないデータを作ってラベルを作っているんです）のではなく、たとえて言うなら、入力規則の元データとなるリストを元にラベルを作り、そのリストの項目からそれぞれの項目がいくつ選択されたのかを集計したい、といえます。

ということで、入力規則のリストに相当する評価テーブルのようなものを用意します。

この評価テーブルのAからEは、アンケート結果テーブルのAからEとの間でリレーションシップを組むことができます。アンケート結果テーブルの A とは「とても良い」ですよ、ということです。ワークシート関数では VLOOKUP関数を使って、「とても良い」や「良い」を参照しますよね。

このリレーションシップ、Excel　2013以降の標準機能です。
もっとも簡単な方法は、ピボットテーブル作成時に「複数のテーブルを使う」オプションを指定し、リレーションシップを自動検出させる方法です。シンプルなテーブルであれば、これで十分です。

アンケート結果のテーブルにアクティブセルを置いて、ピボットテーブルを作成します。その時、[複数のテーブルを分析する] の利用のオプションである [このデータをデータモデルに追加する] をオンにします。

フィールド リスト ウィンドウで、すべてのテーブルを表示するため [すべて] タブを選択し、AからEの評価を含む [評価テーブル] の [評価] を行ラベルとしてドロップします。

これで、AからEの行ラベルが表示された空のピボットテーブルが作成されます。

次に、アンケート結果の実データがある [アンケート結果] テーブルから個数を数えるために [名前]を[値エリア]にドロップします。すると、ウィンドウ上部にリレーションシップの自動検出ボタンが表示されます。
今回は評価 - 評価 でシンプルな例なので、自動検出を使います。もちろん、[作成] で手動で設定してもかまいません。

問題がなければ、以下のようなダイアログが表示されます。

ところが、この状態だと、あの空だったピボットテーブルは、期待した E の行が消えたものになります。

ここで、慌てず、ピボットテーブルのオプションの確認です。
少なくとも、データが入る前は、[E] が表示されていました。こういう場合は「データの無いアイテム（行・列）に関連するオプション」があるんじゃないか、とあたりをつけて探します。

実はこれに関連するオプション、普通に Excel のワークシートのデータを扱っている限りだと、使える状況になったことを見た人は少ないと思います。

これは、ピボットテーブルをやっていると良く目にする「OLAPデータ ソース」が元データだった場合に有効になるオプションでした。

オンライン分析処理の概要
https://support.office.com/ja-jp/article/15d2cdde-f70b-4277-b009-ed732b75fdd6

Excelのデータ モデル オブジェクトは、OLAPデータ ソースのキューブファイルと同じ振る舞いをするようです。事実、データ モデルを接続のプロパティでみたのが以下です。

ということで、今まで使えなかった、この「データのないアイテムを行・列に表示する」をオンにします。

すると、以下のようになります。

Eが表示されました。
しかし、空欄です。できれば 0 を表示したいところです。
これもピボットテーブルのオプション [空白セルに表示する値] で 0 を指定できます。

これらの設定をすると、ピボットテーブルが以下のようになります。

ピボットテーブルのような「機能」は、どんなものがオプションにあるか知らないと実現できないことが多いです。その意味では「機能」も習得するのが簡単ではありませんよね。

Read More

[Power Query / 取得と変換] 列の追加 - 条件列 が利用可能になりました

Power BI Desktop や Excel の Office Insider で利用可能だった [列の追加] の [条件列] が、たぶん5月のOffice 更新プログラムで一般にも公開されたようです。
それまで、あると思って開いたら無くて「あ・・・あれ？」となったことが多かったからです。

ちょっと前置きが長くなりますが、この条件列が追加されたことで、かなり使いやすくなりました。

この Power Query / 取得と変換のクエリエディタで Power Query Formula Language (M言語) をガンガン使ってクエリを書く、という使い方をマイクロソフトは想定して作っていないような気が前々からしています。

というのも、Excelの数式バーでいうところの「数式オートコンプリート」機能、または、Visual Studio でいうところの「インテリセンス」のような、入力補助機能がクエリエディタで提供されていません。

数式オートコンプリートによるドロップダウン リスト

入力補助はなく、エラーではじめて間違いがわかる

ちなみに、Excel の数式バーと、Power Query の数式バー（これも正式名称が数式バー）の左横にある [fx] ボタンは、Excel の場合は[関数の挿入ボタン]ですが、Power Queryの場合は[ステップの挿入ボタン]です。挿入するという意味では一緒ですが、かなり違う意味・使い方になるので、迷わない、使い方が期待通りにならないので、なんで？なんで？と思わないことが肝要です。(要は Power Query のほうはほとんど使えない）

そのため、素から数式を記述するというよりは、リボンから適切なコマンドを選択して、クエリを完成させる、というのが基本哲学のようなものかなぁ、と感じていました。

たとえば、[列の追加]タブには、テキスト、数値、日付を元にして新しい列を追加するなら「たぶん、これをやるでしょ？」というコマンドが用意されています。

参照元のデータがテキストで、カスタム列を追加して、MID関数のような処理をしたい、となると、参照元のデータの列を選択した状態で、[テキストから] - [抽出] - [範囲] でMID関数と同様のことが可能です。

開始と文字数を指定　ただし、開始インデックスは 0 から始まる・・・

もちろん、その操作結果は M言語で記述されています。VBAのマクロ記録、といったほうがイメージしやすいでしょう。

MID関数に相当する Text.Middle 関数が使われていることがわかる

ここでカスタム列を追加した場合に欲しかったのが「条件の指定」でした。IFを使った条件分岐ですね。

If Then Else If Then Else If ・・・ なのでネストのし過ぎには要注意ですが、それ以上に演算したい [演算子] を、データの型にあわせて利用可能なものがドロップダウン リストで表示されるのは、上述のように数式オートコンプリートがないクエリエディタにはありがたいです。

たとえば [指定した値から始まる・・・] のような場合は、ドロップダウン リストから演算を選べば、相当する関数 (Text.StartsWith()) を使った記述が記録されます。

これらの追加機能を使い、M言語を直接記述するのではなく、マクロ記録のようにやりたい処理を手で行い、クエリを保存する、という流れをメインにするのではないかと思われます。

Officeのアップデートがまだの方は、ぜひ。

Read More

[取得と変換・Power Query] 列の削除と他の列の削除は全く意味が違います

すでにお気づきの方には申し訳ないエントリーになります(笑)。

取得と変換/Power Queryのクエリエディターで [ホーム] タブの [列の管理] グループにある [列の削除] には2種類の列の削除方法があります。

[列の削除]と[他の列の削除]です。

本当に正直なところ、処理でいらない列を「たまたま」選択したんだったら[列の削除]をして、「たまたま」必要なものを選択していたら[他の列の削除]を選んでいました。

これ、それぞれの Power Query Formula Language を見ると以下になるんですよね。

列の削除

= Table.RemoveColumns(変更された型,{"区分A"})

他の列の削除

= Table.SelectColumns(変更された型,{"日付", "区分A", "数値A"})

列の削除は、そのまま「列の削除」でいいのですが、他の列の削除は、結果的に列を削除しているのですが、実は、「必要な列を選択する」という意味なんですね。

そのため、元データで「やっぱり、この列、いらないね」となって、元データからその列が削除された場合、「列の削除」を指定しているか、「他の列の削除（＝必要な列の選択）」をしているかで、クエリ更新時にエラーが出てしまうんです。

クエリで削除しようとしていた元データの列がないと、当然ですが、そんな列はありません、というエラーです。

列の削除は「これは含みたくない列だ」という強い想いがあるもの（言い過ぎですけど）、そういう強い想いがなければ、必要な列を選択して「他の列の削除」とすれば、上記のようなエラーはでない、ということです。

あまり意識しないで「列の削除」を多用していました。必要な列だけを残すために、不要な列を Ctrl キーを押しながら選択して・・・という手順です。これ、本当は必要なデータを選択したほうがいいと感じています。

もちろん、元データの必要な列が削除されれば、「そんな列は見つからない」という、削除と同じエラーになります。が、必要なデータの削除は気が付きやすいですし、そもそも必要ですから注意を払います。

Read More

[Excel 取得と変換] Power Query でクロス集計表・マトリックス表を表形式・テーブル形式に変換する

前回に引き続き、これも今は Power BI で簡単にできる、Excelユーザーにとっての長年の夢、というか、実務でよく直面する課題です。
(この話題の2018年更新版の投稿はこちらです。https://road2cloudoffice.blogspot.jp/2018/03/blog-post.html）

クロス集計表をリスト形式・テーブル形式に変換したい、というものです。これ、Power Query だと一瞬でできるんですよ。

この変換には、Power Query の機能を使いますので、Excel 2016 であればデータタブの [取得と変換]、Excel 2013 であれば Power Query をマイクロソフトのサイトからダウンロード、Excel 2010 の Professional Plus (企業向けのライセンス）ならば Excel 2013 同様に Power Query をダウンロードし利用することで、解決できます。

以下のようなクロス集計表(マトリックス表）あります。

ここから、ある日突然「A001だけの4月の合計と、地域を数値の大きい順番で・・・」など言われるわけですよね。できないことはないんですが、関数を駆使したり、コピーしたり、と手間と時間がかかります。

もし、このデータを以下のようなリスト形式で管理していたら、ピボットテーブル操作だけで完了しますよね。

クロス集計表から、このようなリスト形式のテーブルを作成するのは手間と時間がかかる、数が多ければVBAをつかって展開していました。

Power Query (Excel 2016 では 取得と変換)を使ったテーブルへの変換はアニメーションGIFでみられるほど「一瞬です」（笑）。

手順は以下です。

1) クロス集計表をテーブルにします。テーブルにする方法はアクティブセルをクロス集計表の中にいれて Cｔｒｌ＋T または L、 リボンからは同じくアクティブセルをクロス集計表にいれて、[挿入]の[テーブル]グループの[テーブル]をクリックします。

2) テーブル形式にするのは、Power Query(取得と変換)で読み込みためです。テーブル内にアクティブセルをいれて、Power Query または取得と変換の [テーブルから] をクリックします。

3) テーブルになったクロス集計表を読み込んだクエリ エディターが立ち上がります。地域の列で null になっているセルがあります。

この null へ入力するデータはその上にあるデータのコピーになります。
このような場合、[変換]タブの[任意の列]グループの[フィル]の[下]を使います。

4) 4月から9月までの列を選択します。4月の列選択は [4月] の見出しをクリックします。Shift キーを押しながら [9月] の見出しをクリックすると、4月から9月までの列を選択することができます。

5) 選択した状態で、[変換]タブの[任意の列]グループの[列のピボット解除]をクリックします。これでクロス集計表がリスト形式のテーブルに展開されます。

6) 展開された列名を「月」に変更して、[閉じて読み込む]でワークシート上にテーブルを作成します。

この手順が上のアニメーションGIFになります。

なお、このテーブルの作成に使ったクエリを削除すると、元のクロス集計表とリンクしていないテーブルになります。クロス集計表の更新はあまりお勧めできませんが、クエリを保持しておけば、クロス集計表の数値が変わっても、[更新] でクエリ結果のテーブルを更新することもできます。

Read More