今回から5回に分けて、ピボットテーブルをそのまま報告用の表に変える「新ピボットテーブル術」を解説します

集計したデータから「転記処理」や金額の表示単位変更（円⇒百万円など）、前期比などの「集計列追加」などの作業を不要にする方法を解説します

最終的には、集計対象のデータが変更になっても「更新ボタン」をワンクリックをするだけで資料を自働作成できるようにします

表を自動作成する光景が「イメージしにくい」という方は、ぜひ下のGIF画像をご覧になってください

集計対象データ（画面右）を「2017年対2016年」から「2018年対2017年」に変更したとしても、表（画面左）もそのまま「2017年対・」から「2018年対・」の表示になります

さて、

ピボットテーブルから作成される表は通常、以下の画像のようなイメージです

記事の冒頭でお見せした表と同じデータを使用していますが、全く別の表のような感じですよね

記事冒頭の表にするため、次の回から大きく分けて以下の３つのことを行います

A．表の見た目を変える

ⅰ）データの並び順を変える

ⅱ）表上の「フィルターマーク▼」を取り除く

ⅲ）表の右「総計の列」を非表示にする

ⅰ）～ⅲ）⇒ 解説記事：2回目

ⅳ）信号のアイコンを挿入する

⇒解説記事：4回目

ⅴ）表の見出し「合計 / 売上金額」から合計～を除く

ⅵ）データ更新時も列の幅を維持する

ⅴ）～ⅵ）⇒解説記事：5回目

B．元データに無い項目を作成する

ⅰ）売上金額を百万円単位で表示する

ⅱ）前期比を集計、表示する列を作成する

⇒解説記事：3回目

では次回から具体的な内容を解説していきます

にほんブログ村

にほんブログ村

前回は導入としてこの「転記・編集作業の改善シリーズ」で行うことの概要を解説しました

今回は前回紹介した内容の内、以下の内容に取り組みます

A．表の見た目を変える

ⅰ）データの並び順を変える

ⅱ）表上の「フィルターマーク▼」を取り除く

ⅲ）表の右「総計の列」を非表示にする

いずれの内容も小難しい処理はなく、基本的にはクリックで処理を進めていくことができます！

ぜひ、サンプルファイルをダウンロードして実際に手も動かしてみましょう！

ⅰ）データの並び順を変える

下のGIFのようにピボット―テーブルの表の「｢2017」にカーソルを置き、右クリックします

そして、「並べ替え」⇒「降順の順」でクリックしていきましょう

年度の並び順（2016→2017）が（2017→2016）に変わっているはずです

ⅱ）表上の「フィルターマーク▼」を取り除く

ピボットテーブルの表をそのままレポートとして活用できない理由の一つが、このフィルターマーク▼の存在です

このマークは見栄えが悪いですが、簡単に取り除くことができます

カーソルをピボットテーブル表に上に置き「ピボットテーブル分析」タブをクリックしましょう

すると右側に「フィールドの見出し」というテキストが見えるはずです

この「フィールドの見出し」をクリックすれば▼マークは消えます！

（参考）形式を表形式に変える

今回はあくまで参考としての説明になりますが、行などが2段になっている場合には、段の表示をどのようにするのかを選択することができます

今回は段をコンパクト型式から、列を分割して表示する「表型式」にしておきましょう

ⅲ）表の右「総計の列」を非表示にする

ピボットテーブルの表の上にカーソルを置いたうえで、デザインタブの総計リボンに「列のみの集計」を選びましょう

＜まとめ＞

今回は、フィルターマーク▼を取り除くことをはじめとした、ピボットテーブルの表の見た目を変える操作について解説を行いました

今回は詳細に解説しませんでしたが、ピボットテーブルの値の表示形式を桁区切りをしておくことも念のため、付け加えておきます

実はまだ見た目を変えなくてはならないところがありますが、それはもう少し表全体が固まった後の4回目、5回目の解説で行います！

それでは次回は、ピボットテーブルの売上金額の表示単位の変更（円→百万円）と前期比・列に追加について解説します！

にほんブログ村

にほんブログ村

前の回で、ピボットテーブルで作成した表の見た目が随分変わりました

今回は導入の回で紹介した内容のうち「元データに無い項目の追加」を解説します！

！今回のポイント

「 元データに無い項目の追加 」では以下の２つの作業を行います

ⅰ）売上金額を百万円単位で表示する

ピボットテーブルの表にカーソルを置くと、エクセル画面の上に「ピボットテーブル分析」タブが表示されます

このタブの中に「フィールド/アイテム/セット」ボタンがあります

このボタンにて開いた画面で、売上金額を「百万円」にて割った値を表示するフィールドを作成します

ボタンを押して開いた「集計フィールドの挿入」画面の数式欄では、計算式（売上金額÷百万円）を入力できるようになっています

ⅱ） 前期比を集計、表示する列を作成する

ピボットテーブルの表の上にカーソルを置き、右クリックすると「計算の種類」が表示されます

この「計算の種類」画面では、様々な種類の集計列を挿入することができます

今回は、計算の種類の中の「基準値との差分の比率」の集計列を表の中に挿入します

集計データが売上金額の2017年度と2016年度であれば、基準値を2016年度をとして差分の比率を算出します

ここで示す差分とは、基準値が2016年度であれば、売上金額の2017年度から2016年度を引いた値になります

実際の設定画面では、基準値については、データの中味（年度）が変わっても前期比が計算できるように「次の値」（もしくは前の値」と言う形で設定します

次の値とは、ピボットテーブルの表にて左から＜2017年度＞⇒＜2016年度＞の順で奈良でいれば＜2016年度＞が次の値です

左から＜2016年度＞⇒＜2017年度＞の順に並んでいれば＜2017年度＞が次の値です

こちらについては後程、詳細に解説します

！詳細解説

実際に解説に使用するデータのダウンロードをお願いします

ⅰ）売上金額を百万円単位で表示する

①集計フィールドの挿入画面を開く

前述の通り、ピボットテーブル画面のタブから集計フィールド画面を開きます

②作成するフィールドの名前を設定

名前欄に「売上金額（百万円）」を入力します

③数式にフィールドの値をセット

該当のフィールドを選択し、画面下にあるフィールドの挿入を押すと、数式欄に「売上金額」がセットされます

④新フィールドの追加

数式欄にて割り算を入力しましょう

割り算を入力したら追加ボタンを押して下さい

①～④を行うと円⇒百万円に単位を変えた新フィールドを作成できます

新フィールドを作成したら、エクセル画面の右側（フィールドリスト）では、単位百万円の新フィールドが選択できるようになっているのを確認できます

ⅱ） 前期比を集計、表示する列を作成する

①計算の種類の作成画面を開く

前述の通り、ピボットテーブルの表上で右クリックし、「計算の種類」を開きます

② 基準値との差分の比率

③差分の比率の計算方法

次に以下の画面が出てきますが、基準フィールドは地域から「年度」に変更し、基準アイテムは「次の値」を指定します

①～③を行うと、2016年度を基準とした前期比が表に挿入されています

ここで基準アイテムを「次の値」にする理由を説明しておきます

今まで紹介したピボットテーブル表の年度は降順で表示されています

つまり、左から前：2017⇒次：2016の順になっていますので、基準値になるのは2017年度の「次の値」の2016年度です

ですので「前の値」ではなく「次の値」にします

ちなみに、2016年度も以下の画面のように基準アイテム欄から選べるようになっています

ただ、データが変わった場合には、基準アイテムを固定しておくと色々と不都合が生じます

仮にデータが（2016年度と2017年度のセット）から（2017年度と2018年度のセット）になった場合には、2016年度がデータセットになくなるのでエラーになります

ここで、試しにピボットテーブルの表の年度表示を降順から昇順に変えてましょう！

つまり、左から前：2016⇒次：2017の順になっていますので、基準値になるのは2017年度の「前の値」の2016年度です

ですから、基準アイテムは「前の値」を指定します

＜まとめ＞

今回は２つのことを行いました

　ⅰ）売上金額を百万円単位で表示する

　　　集計フィールドの挿入画面で売上金額の単位変更を行いました

　 ⅱ） 前期比を集計、表示する列を作成する

　　　計算の種類画面で前期比の列を挿入しました

この２つの画面を有効活用になるとレポート力は格段に向上します

ピボットテーブルで集計したデータを他の表に転記して再編集したり、再計算するといった作業が必要なくなります

では次回はピボットテーブルの表に信号を挿入します！

にほんブログ村

　今回は、ピボットテーブルの表の中に、条件付き書式のアイコンを挿入して、ダッシュボード化しましょう！

　条件付き書式のアイコンとピボットテーブルを組み合わせることで、見た目にメリハリがつき、1目見たときの印象が格段によくなります！

前回でピボットテーブルの表に元データに無い項目を追加する方法を解説致しました

売上金額を百万円単位で表示にし、前期比の表示方法もあわせて解説致しました

　今回は下の図のように、条件付き書式の信号のアイコンを条件にあわせて表に挿入します

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

今回のポイント

A.値の表示順と元データ

値の表示順は以下の画像のように左から

　ⅰ）売上金額（百万円）

　ⅱ）前期比

　ⅲ）信号

となります

但し、信号は前期比を元にして作成します

条件付き書式では「アイコンのみ表示」が指定できますので、上の表では信号のアイコンのみ表示し、前期比の数字は表示していません

B.フィールド名

A.の解説にて、信号は「前期比」から作成すると解説しました

前期比も前回の解説通り、フィールドリスト上は売上金額（百万円）から作成します

ですので、３つの「売上金額（百万円）」フィールドが必要になります

フィールドリスト上に同じフィールドを並べると末尾に番号が振られますので、縦に番号順に並ぶように調整しておくと便利です

順番の調整は、値欄を大きく表示できるようにしておいてからの方がよいでしょう

そうでないと、なかなかうまく順番が変えられません

値欄を大きくする表示については、以下の２つを参考にしておこなってください

a:フィールドセレクションを適切に設定する

フィールドセレクションを上にした方が値欄を大きく使えます

b:エリアを横に拡大する

下の画像の円部分を横に広げてください

ポイントを解説したところで、詳細な解説に入ります

下のサンプルファイルを事前にダウンロードしておいて頂けると幸いです

詳細

１．値欄を再設定

話しを分かり易くするために、一度、値欄は空欄にしてください

その後、フィールドリストに「売上金額（百万円）」を３つ設定しましょう

２．条件付き書式の設定

条件付き書式の基本設定を行いましょう

まずはピボットテーブル表上の「合計/売上金額(百万円）3」の上にカーソルを置き、ホームタブから「条件付き書式」をクリックした後、「新しいルール」をクリックしましょう

次にルールの適用対象には、1番上の「選択したセル範囲」でなく、2番目に表示されている「合計/売上金額（百万円）3」を指定しましょう

セル範囲で指定した場合にはピボットテーブルの「更新」処理を行った時に設定が消えてしまいます

３．ルールを設定する

ここからは機械的にルールを設定していきましょう！

・前期比が５％以上なら青信号

・０％以上なら黄色信号

・それ以外（０％未満）は赤信号

になるようにします

①アイコン種類の選択

各種内容を以下のように設定しましょう

 ⅰ）書式スタイル ⇒ アイコンセット

 ⅱ）アイコンスタイル ⇒ 信号

 ⅲ）アイコンのみ表示　⇒ チェック

②信号表示の条件を設定する

デフォルトでは種類のところに”パーセント”が設定されていますが、数値に変更しておきましょう

さて、①②を通じて以下のような形になりましたでしょうか？

＜まとめ＞

今回は条件付き書式を使用してピボットテーブル表上に、信号のアイコンを設定する処理を行いました

条件付き書式での設定箇所で注意して欲しい点を、もう一度おさらいします

・アイコンのみ表示　⇒ チェック

チェックを入れることで、セルに数字は表示されずに信号のみが表示されます

・種類（数値or％）⇒数値

デフォルトでは％が入っていますが、数値にすることで信号の色を指定する条件（例：5%）を設定できます

上記の2点を注意して頂ければ、条件付き書式の設定はOKだと思います

次回からはいよいよ総仕上げになります

にほんブログ村

前回は条件付き書式により、信号を表の中に入れました

4回にわたり、解説してきた新ピボットテーブル術１も仕上げに近づいてきました

今回はピボットテーブルの表の見た目を向上するため、１つだけ簡単な作業を行います

●列ラベルから不必要な文字を抜く

通常、列ラベルには計算方法が入ってしまっています

それに、同じフィールドを値として使用するとこんな文字も出てしまいます

このままだと、レポートには使いにくいですよね？

そうはいっても不必要な文字を消そうとすると、「指定したフィールド名は既に使われています」というエラーメッセージが出てしまうのです

実は、このメッセージを出ないようにする裏技があります

文字の後ろに、空白（スペースを押す）を入力するだけで列ラベルから不必要な文字を消すことができるのです

列ラベルから不必要な文字を消すだけでも随分、見た目が良くなりますよね！

＜まとめ＞

今回も含めて5回にわたってピボットテーブルをそのまま報告書に盛り込める方法を解説しました

ピボットテーブルはとても便利で集計作業に重宝されていると思いますが、他の表に転記していたらとても無駄な作業になってしまします

ぜひ、今回の5回シリーズを復習して頂いて「業務削減」のヒントとして頂けると幸いです

にほんブログ村

にほんブログ村