レポーティングを目的としたデータ編集・分析から、ディスカッションを目的としたデータ編集・分析へ

レポーティングを目的としたデータ編集・分析から、ディスカッションを目的としたデータ編集・分析へ

テレワークの普及でミーティングのスタイルに変化が・・・ペーパー (静的) からアプリ (動的) なレポートへ、話すひと・聞くひとの関係 (報告会) から、お互いに議論する (ディスカッション) ミーティングへ!

データの保持方法

値をコピペする
データ保存フォルダー

 POSシステムや売上管理システムからダウンロードしたデータを画像のようにパソコンやクラウドへ保存していることが多いと思います。

 これらのデータを編集して「月次報告書」などを作成するのですが、みなさまはどのように作成されていますか?

 例えば、売上高の前年対比はレポートに必要不可欠な指標です。
・本年当月のエクセルブックから、本年当月の売上高をコピーする。
・前年同月のエクセルブックから、前年同月の売上高をコピーする。
・これらをペーストして前年対比の計算式から作成する。

レポート完成

 画像のように月ごとの定型レポートを作成して報告します。

 店舗Aの好調さが目立ちます。
・店舗Aの前月 (10月) はどうだった?
 これはセーフ。10月のシートがあります。

・店舗Aの今期累計 (4月~11月) はどうだった?
 ギリギリです。各シートの合計から算出できます。

・店舗Aの前年 (2019年) はその前年 (2018年) と比較するとどうだった?
 これはアウト。データがありません。

継ぎ足してまとめて保持する
くっつけます

 データの行数がそれほど多くないことが条件です。10店舗・月合計なら10行、年間で120行、10年間で1,200行です。
 仮に日別データであっても、その約30倍として36,000行です。パソコンに搭載しているメモリによりますが、エクセルで処理できる行数としてはその範囲内だろうと思います。

データをまとめて保持するメリット
ピボットテーブル

 ピボットテーブルを作成します。

月を変更

 前月の実績を振り返りたいときは
・スライサーの月を変更します。

 翌月になったら
・売上データのシートへ12月を追加します。
・ピボットテーブルのデータ範囲を更新して、スライサーで12月を選択します。

 月別にシートを増やしていく必要がなくなります。

累計

 期初からの累計を見たいときは
・スライサーで複数の月を選択します。

前々年

 前々年からの推移を見たいときは
・スライサーで複数の年を選択します。

ピボットテーブルのメリット

・スライサーで見たいデータ範囲を絞り込める。
・フィールド操作で見たいフィールドや値へアプローチできる。
・フィールドをまとめたり、ドリルダウンすることが容易にできる。
・計算式を挿入したセルを作成する必要がない。

・参照しているもとデータ表自体が編集・加工されることがない。
 従って、データをまとめて保持していても安全である。

ピボットテーブル作成手順

初期設定
ピボットテーブルを挿入

・ピボットテーブルに使用するデータ表のセルA1へカーソルを合わせて
・「挿入」タブ
・「ピボットテーブル」をクリックします。

 窓が開きます
・「テーブルまたは範囲選択」 = データ表のすべてになっているのかを確認
・「ピボットテーブルを配置する場所」 = 「新規ワークシート」です。
・「OK」です。

リボンを折りたたむ

 パソコンが横長ノート型のときは、リボンを折りたたみます。ピボットテーブルの操作性があがります。

日付のグループ化

・「日付」の左側のチェックボックスをクリックします。
 自動的に「行」フィールドへ「年」・「四半期」・「日付」が入ると思います。

 ピボットテーブルでは「日付」の値が自動でグループ化されます。ただし、「日付」の期間が長期にわたる場合は「月」でグループ化されないことがあります。

「日付」のフィルター

 「日付」をフィルターで確認してみると妙な値があります。「日付」の期間が長期になる場合に、このようになるようです。

グループ化を追加

・ピボットテーブルのセルを右クリック
・「グループ化」を選択します。

 窓が開きます。
・「日」「月」「四半期」「年」を選択して「OK」です。

フィールド削除

 「月」が現れれば成功です。
・フィールドから「日付」をいったん削除します。

ピボットテーブル作成
スライサーを設置

・「分析」タブ
・「スライサーの挿入」をクリックします。

 窓が開きます。
・「月」「年」をチェックして「OK」です。

ピボットテーブル作成

・「年」のスライサーで「2019年」「2020」年を選択
・「月」のスライサーで「11月」を選択します。
・「列」フィールドへ「年」
・「行」フィールドへ「エリア」と「店舗名」
・「値」フィールドへ「売上高」を設定します。

総計の削除

・ピボットテーブルのフィールド名 (列名) の「総計」を右クリック
・「総計の削除」を選択します。

前年対比 (計算式セルの挿入は不要)
売上高を値へ

・売上高を「値」へドロップします。
 ピボットテーブルへ「売上高」が2列表示されます。

値フィールドの設定

・「値」フィールドにある「売上高」の▼三角をクリック
・「値フィールドの設定」を選択します。
 窓が開きます。

計算の種類

・名前をタイプします。
・「計算の種類」タブ
・「基準値に対する比率」をドロップダウンのなかから選択
・「基準フィールド」は「年」
・「基準アイテム」は「 (前の値) 」
・「表示形式」をクリックします。

セルの書式設定

・セルの書式を整えます。
・「OK」で、すべての窓を閉じます。

ピボットテーブルを整える
列の非表示

・列Cを非表示にします。

列幅の固定

・ピボットテーブル内のセルを右クリック
・「ピボットテーブルオプション」を選択します。

 窓が開きます。
・「レイアウトと書式」タブ
・「更新時に列幅を自動調整する」のチェックを外します。

行ラベル並び替え

・「行ラベル」のフィルターをクリック
・「降順」に並び替えます。

条件付き書式

・前年対比の値をドラッグします。
・「ホーム」タブ
・「条件付き書式」
・お好みで色やアイコンを挿入します。

スライサー操作

 スライサーを操作してディスカッションしましょう。

ピボットグラフ作成手順

データテーブル作成
データテーブル

 ピボットテーブルなしピボットグラフを作成します。データ表をデータテーブル化します。
・「挿入」タブ
・「テーブル」をクリック
・開く窓の内容を確認して「OK」です。

テーブル名

・「デザイン」タブ
・テーブル名をタイプして
・エンターキーを押してください。

ピボットグラフ挿入
ピボットグラフ

・「挿入」タブ
・「ピボットグラフ」の▼三角をクリック
・「ピボットグラフ」を選択します。

外部データソース

 右の窓が開きます。
・「外部データソースを使用」のラジオボタンをクリック
・「接続の選択」をクリックします。

 左の窓が開きます。
・「テーブル」のタブ
・作成したテーブルを選択
・「開く」
・すべての窓を閉じます。

ピボットグラフ作成
前年対比

 手順はピボットテーブル作成と同じです。

 グラフの青い棒は前の値 (2018年) がスライサーで除外されているのですべての値が100%になります。ピボットテーブルでは100%の列を非表示にできますが、グラフでは非表示にできません。
 あるものは利用するということで100%の値をグラフの補助線のように使用します。

・グラフエリアを右クリック
・「グラフの種類の変更」を選択します。

組み合わせグラフ

・「組み合わせ」を選択
・2019を「折線」「第2軸」
・2020を「集合縦棒」
・「OK」です。

軸ラベル

 左右のスケールを一致させます。
・「+」プラスマークをクリック
・「軸ラベル」の▼三角をクリック
・「その他のオプション」を選択します。

軸の書式設定

 「軸の書式設定」が開いたら
・グラフエリアの右側の軸をクリック
・棒グラフのマークから
・「最小値」「最大値」へ左側のスケールと一致する値を入力します。

 完成です。

差分へ変更
シートを複製

 定型表のレポートはスライサーの操作だけで始点を変えることができるので、シートは1枚あればよいと思います。一方、グラフは操作が複雑になるため複数枚のシートを持っていた方が使い勝手がよくなります。

・売上高前年対比のシートを複製します。
・「前年対比」の「値フィールドの設定」
・名前を変更します。
・「計算の種類」タブ
・「計算の種類」を「基準値との差分の比率」へ変更
・「基準フィールド」「基準アイテム」はそのままです。

グラフの種類を変更


  シートを複製したのでグラフが「組み合わせ」になっています。「縦棒」へ変更します。

軸の書式設定

 横軸と棒が重なっているので修正します。
・「軸の書式設定」を開きます。

ラベルの位置

・「ラベルの位置」が「軸の下/左」になっているのを
・「下端/左端」へ変更します。

完成です

 スライサーを操作します。

その他のグラフ例
基準値を固定

 2018年を基準に2019年と2020年を表示。

近似曲線

 売上高へ近似曲線を追加

平均

年間平均売上高の推移

列集計に対する比率

 エリア内売上高構成比

練習用サンプルデータ

データはダウンロードまたはコピペできます。

サンプルデータダウンロード

弊社のデータ編集・見える化代行サービス