利益が減少した原因を分析する。 エクセルの機能 x 分析の鉄則 Vol.1
エクセルのフル活用 ＋ 分析スキルの習得 ⇒ 実践で有効
本稿では、利益が減少してしまった原因を分析する際に有効なエクセル機能を紹介します。
本稿の特徴は、単に分析に使用するエクセル機能を紹介するだけではなく、利益減少の原因を分析する過程がストーリー仕立てになっているので、一緒に分析の手順と考え方も学習できるようになっています。
エクセル機能だけ、或いは、分析の基本知識だけでは、ビジネスの現場では役に立ちません。
両方を理解することで、実践で通用する技能となります。
それでは、始めましょう。
ストーリー
あなたは、この４月に課長に昇格するとともに、大阪支店から東京支店の営業三課に異動になった新米マネージャーです。
4月が終わり、業績が出そろったGW明けのある朝、支店長から呼び出された。
支店長：「4月、営業三課の利益が落っこちているぞ。早急に原因を調査して、報告するように。昼から本社に行かなければならないので、午前中に頼む。」
あなたは、心の中でつぶやく。「おっと、いきなり来たな。今朝、課員はみんな営業で出払っている。支店長に試されているのかも。」
営業三課の4月業績は、下表の通り。
「売上は予算を過達しているものの、利益は未達となっている。利益率も2%下がっている。。。」
「確かに、4月は引越、前任課長からの引継ぎ、顧客挨拶で、まともに業績のこと見てなかったな。早急に調査して、支店長に報告せねば。」
ということで、あなたは利益減の分析を始めることとなりました。
手元にあるデータは、前任の課長から引き継いだ「月次顧客別売上・利益予算表」と社内システムからダウンロードした「4月の請求データ」。
それでは、早速、分析を開始することにしよう！
分析のゴールは何？
今回、支店長の指示は、「4月の利益が下落した原因を調査せよ」というもの。具体的には、
売上は10,039千円で139千円の過達なのにもかかわらず、
利益は185千円の未達、利益率にして2%ダウンだった。
この2%ダウンがどんな原因で生じたかを説明するのが分析の目的だ。
＜分析の鉄則①＞
着手する前に、分析の目的を明確にすること。
抽象的な言葉ではなく、具体的な数値やあるべき状態にまで落とし込むこと。
本ストーリーでは、「２%ダウンがどんな原因で生じたか」という具体的数値が分析のゴールとなっている。
情報の整理⇒分析の道筋を立てる
改めて「月次顧客別売上・利益予算表」を見てみる。
営業三課の顧客は、９社。売上は、あかさ社の190万円がトップ、一番下はディーイーエフ社と真矢羅社の50万円。
利益率は、各社ともに30%前後。
「そうだ、前任の課長は、三課は扱っている商品が単一のカテゴリーだから、営業数値は非常にシンプルだ、と言っていたな。」
一応、本当にそうなのか、チェックしておこう、
「4月の請求データ」ファイルを開いて、データ上の任意のセルをクリックして、[Ctrl] + [Shift] + [L]を押して、フィルターを起動。商品の列の[▼]をクリックして、ドロップダウンリストを表示する。
「うん、確かに、商品Aの型式違いしか扱っていないな。」
「じゃあ、取扱商品は、分析の対象から外してもよさそうだな。顧客に原因がありそうだ。顧客ごとの収支をまずは見てみよう。」
＜機能①＞フィルター
■機能：条件に合う文字列や数値などのデータを抽出する機能です。
ここでは、所定の列に、どのような文字列（数値）が入力されているかを集約して、プルダウンリストで表示してくれます。データの上から下まで、見ていく必要はありません。
■やり方：データの任意のセルを選択して、「データ」タブ ⇒ フィルター
■ショートカットキー：データの任意のセルを選択して、[Ctrl] + [Shift] + [L]
＜分析の鉄則②＞
データをいじる前に、ある程度当たりをつける。
分析とは、闇雲にデータをこねくり回すことではない。
最初に設定した分析のゴールに到達するために、データのどの部分をどのようにいじるか、道筋を立てる必要が有る。仮説を立てることに非常に似ている。
ピボットテーブルで顧客別売上・利益を集計する
4月の顧客ごとの収支を出すために、「4月の請求データ」を使う。
「請求データ」には、請求日、請求先顧客、商品、売上、利益が網羅されている。
このデータから、エクセルのピボットテーブルを活用して、顧客別に売上、利益を集計すれば、何かわかるかもしれない。
＜機能②＞ピボットテーブル
■機能：売上明細や販売データ等のデータを基に、素早く集計表を生成する機能です。
また、見出しを入れ替えることにより、集計表の内容を変更（組み換え）することができます。
・大量のデータも瞬時に集計できる
・項目を変えて、瞬時に再集計ができる。
以上の特徴により、手作業だと時間と労力を要する集計と組み換え作業を瞬間に完了することができる。
■やり方：データの任意のセルを選択して、「挿入」タブ ⇒ 「ピボットテーブル」
■ショートカット：データの任意のセルを選択して、[Alt] → [N] → [V]
「請求データ」は、単に社内システムからデータを抽出しただけだから、見出しが無い。
これでは、うまくピボットできないので、「請求データ」の一行目に見出しを追記しておこう。
一行目を選択して、[Ctrl] + [Shift] + [+]を押して、行を挿入する。（行の挿入ショートカット）
そして、左の列から、「請求日」「顧客名」「商品」「売上」「利益」と順に入力した。
＜解説①＞ピボット出来るデータの条件
ピボットテーブルで使用するデータは、正しくは「リスト」と呼ばれ、リストの一行目には見出し（「フィールド名」）が必要です。
ピボットテーブルの機能は、リスト（データ）の一行目をフィールド名（見出し）と認識してしまいます。見出しの無いデータをピボットすると、一行目の内容がフィールド名と認識され、集計の対象から外れてしまいます。
ピボットテーブルで使用するリスト（データ）は、以下の要件を満たしている必要が有ります。
＊＊＊ピボットテーブル データの要件＊＊＊
①リスト（データ）の一行目はフィールド名（見出し）となっていること。
一行目にタイトルが入っているのも、NG！
②空白のフィールド名（見出し）はダメ。
③リスト（データ）は、空白の行と列で囲まれていること。
③1枚のエクセルシートにリスト（データ）は、一つであること。
④表の中に空白の行を入れないこと。
⑤セル結合が含まれるリスト（データ）はNG
よし、これでピボットの下ごしらえは、ＯＫだな。
「4月の請求データ」（＝リスト）上のセルをクリックし、「挿入」タブの「ピボットテーブル」をクリック。
「ピボットテーブルの作成」ダイアログボックスが現れたら、「ＯＫ」をクリック。
新しいシートが作成され、右側に「ピボットテーブルのフィールド」ウインドウが表示される。
今やろうとしているのは、4月の顧客ごとの売上・利益を出すことだな。
「ピボットテーブルのフィールド」ウインドウの上部ボックスから「顧客名」を下部の「行」ボックスへドラッグ。
そして、今度は「売上」と「利益」を「値」ボックスへドラッグ。
これで、4月の売上と利益が、顧客ごとに集計された。
「いつもながら、ピボットテービルの数値は、カンマ(,)が無くて、見にくいな。」
ピボットテーブルの「合計 / 売上」列の任意のセルを選んで右クリックし、「値フィールドの設定」ダイアログボックス開き、「表示形式」を押下。「分類」から「数値」を選び、「桁区切り(,)を使用する」のチェックボックスをクリックし、「OK」を押す。
「合計 / 利益」列も、同じ手順でカンマを打つ。
「よし、これで数値が見やすくなったぞ。」
＜機能③＞ピボットテーブルの数値にカンマを打つ
■機能：ピボットテーブルは、デフォルトではカンマが表示されません。このままだと、数値が見にくいので、カンマを打ちます。
■やり方①：カンマを打ちたい列の任意のセルを選んで右クリックし、「値フィールドの設定」⇒「表示形式」⇒「分類」：「数値」を選択⇒「桁区切り(,)を使用する」をチェック⇒「OK」
■やり方②：「ピボットテーブルのフィールド」上のカンマを打ちたい「フィールド」をクリック⇒「値フィールドの設定」⇒やり方①に同じ
■やり方③：カンマを打ちたい数値を選択して、[Ctrl] + [Shift] + [1]を押す。（普通のカンマを打つためのショートカットと同じです）
「顧客ごとの売上、利益は集計できたけど、これだけではただの数値の羅列にすぎない。利益率を出して、異常をあぶりだそう。」
「合計 / 利益」列の右隣りに、利益率を追記しよう。
ピボットテーブル上の任意のセルを選択して、「ピボットテーブル分析」タブの「フィールド/アイテム/セット」をクリック、「集計フィールド」を選択。
すると、「集計フィールドの挿入」ダイアログボックスが現れる。「名前」の欄に「利益率」を入力。次に「数式」の欄の「= 0」の「0」を消し、「フィールド」から「利益」を選んで「フィールドの挿入」をクリック。
「数式」の欄が、「=利益」となるので、「利益」のすぐ後ろに「/」を入力し、「フィールド」から「売上」を選んで「フィールドの挿入」をクリック。
「追加」を押して、「OK」でダイアログボックスを閉じる。
これで、「合計 / 利益」列の右隣りに、「合計 / 利益率」列が生成されました。
＜機能④＞ピボットテーブルに独自の集計列を追加する
■機能：ピボットテーブルによって集計された数値同士を演算する集計列を追加する機能。
ピボットテーブルで集計された数値は、セルに「=」と打って行う通常のやり方では計算がうまくいきません。
■やり方：ピボットテーブル上の任意のセルを選択して、「ピボットテーブル分析」タブの「フィールド/アイテム/セット」をクリック、「集計フィールド」を選択。
「名前」の欄に任意でフィールドめーを入力。「数式」の欄の「= 0」の「0」を消し、「フィールド」と演算子（+ , – , * , /）を組み合わせて計算式を作る。その後、「追加」⇒「OK」と押す。
しかし、数値が「0」と表示されてしまいます。パーセント表示に直します。
先ほど、カンマを打ったのと同じ手順で、「合計 / 利益率」列の任意のセルを選んで右クリックし、「値フィールドの設定」ダイアログボックス開き、「表示形式」を押下。「分類」から「パーセンテージ」を選び、「OK」を押下。
＜機能⑤＞ピボットテーブルの数値をパーセント表示にする
■機能：ピボットテーブル上の数値をパーセントで表示する。
■やり方①：％表示にしたい列の任意のセルを選んで右クリックし、「値フィールドの設定」⇒「表示形式」⇒「分類」：「パーセント」を選択⇒「OK」
■やり方②：「ピボットテーブルのフィールド」上のカンマを打ちたい「フィールド」をクリック⇒「値フィールドの設定」⇒やり方①に同じ
■やり方③：カンマを打ちたい数値を選択して、[Ctrl] + [Shift] + [5]を押す。（普通の％表示のショートカットと同じ）
＜分析の鉄則③＞
数値は比較しやすい形に加工する。
本稿の例のように、売上、利益の数値とにらめっこしても、何も見えてきません。
増加/減少した数値（差）や利益率・増減率のようなパーセンテージ表記等に変換して、数値を吟味します。
ピボットテーブル上で、利益率が％で表示された。この中に利益率が悪い顧客がいるはずだ。利益率が悪い順に並べ替えたら、一目瞭然だ。
「合計 / 利益率」列のどれかのセルを選択して右クリックし、「並べ替え」⇒「昇順」を選択。
＜機能⑥＞ピボットテーブルの並べ替え
■機能：ピボットテーブル上の数値を大きい順、小さい順に並べ替える機能。
■やり方：並べ替えを行いたい列の任意のセルを選んで右クリックし、「並べ替え」を選択。並べ替えの方式（「昇順」「降順」その他）をクリック。
「昇順」：小さい順、「降順」：大きい順
「株式会社あかさ」が20%、2番手は「株式会社123」の28%。以降の利益率は29%、31%、32%、33%と続く。
「極端に、株式会社あかさの利益率だけが、悪いな。これが、悪さしているとみて、ほぼ間違いないだろう。」
支店長に報告しようと立ち上がったが、「いや待てよ。お昼までにまだ時間がある。本当にこれで正しいか裏を取ろう。」
再び、PCと対峙するのであった。
＜STUDY MORE＞
利益が減少した原因を分析する。 エクセルの機能 x 分析の鉄則 Vol.2
利益が減少した要因を分析する方法 サラリーマン算数(2)
データを素早くまとめるテクニック3選 － エクセル機能の応用
伝わる表の作り方（Excel） － ひと手間かけて差をつける
＜おすすめツール＞
データ分析は、疲労との戦い！マクロ搭載マウスでスピードアップ＆工数削減！！
＜ピボットテーブルをもっと学びたい方へ＞ ＊オススメ本
