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

データの保持方法

値をコピペする

　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年を表示。

　売上高へ近似曲線を追加

年間平均売上高の推移

　エリア内売上高構成比

練習用サンプルデータ

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

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