(この話題の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になります。
なお、このテーブルの作成に使ったクエリを削除すると、元のクロス集計表とリンクしていないテーブルになります。クロス集計表の更新はあまりお勧めできませんが、クエリを保持しておけば、クロス集計表の数値が変わっても、[更新] でクエリ結果のテーブルを更新することもできます。
0 コメント:
コメントを投稿