利益が減少した原因を分析する。　エクセルの機能 x 分析の鉄則　Vol.2

＊＊「利益が減少した原因を分析する。エクセルの機能 x 分析の鉄則　Vol.1」からの続き

4月の請求データをピボットして、顧客ごとに4月中の売上と利益を集計した結果、利益率が2%下落した原因は、株式会社あかさにあると思って間違いないだろう。
支店長に報告する前に、ダブルチェックしておこう。

予算と実績の比較

それには、さっき作ったピボット集計表と「月次顧客別売上・利益予算表」とを比較すればいいだろう。
作業を楽にするために、ここはVLOOKUP関数を活用しよう。顧客名をキーにして「月次顧客別売上・利益予算表」の横に先ほどピボットして作成した4月の売上・利益集計データを貼り付けよう。

＜分析の鉄則④＞
数値は単体では何も語らない。
数値データとにらめっこしても、数値はそれ単体では何も教えてくれません。
同類の数値データを比較して、初めて何かを語ってくれます。
今回のような月次の業績データであれば、前月や前年同月の業績データと比較する。他支店、他の営業課の同月の業績データと比較してみる。或いは、予算と比べてみる。

詳しいことは、「業績数値を理解しよう（１）」「業績数値を理解しよう（２）」で説明しているので、併せて参考にしてください。

＜機能⑦＞VLOOKUP関数の応用
■機能：数字列、文字列をキーにして、別の表からデータを検索して、表示する関数です。（表引き関数）
VLOOKUP関数は、通常、検索値に数字列・文字列を入力して、それをキーに別の表から指定したデータを拾って、セルに表示するという使い方をします。
下図を見ていただくと、VLOOKUPの機能をお分かりいただけると思います。

データの下準備（データのクレンジング）

「月次顧客別売上・利益予算表」ファイルを開く。
「うっ、予算表と4月の請求データでは、顧客名の表記が一致していない。予算表の顧客名には、株式会社、㈱等が付いていない。
それに、予算表の顧客名は、全角文字が使われている。でも、請求データの方は、カタカタ、アルファベット、数字が半角になっている。」

「このままでは、VLOOKUPは完全一致だから、検索がヒットしないぞ。」

「いや、まだあるぞ。予算表の方の数値は、単位が千円になっている。単位が違うから、単純に比較できない。」
「でも、大丈夫。表記を合わせればいいだけのこと。」
ピボットで集計した顧客別売上・利益の表（4月の請求データ）をコピーして、「月次顧客別売上・利益予算表」に張り付ける。この際、値で貼り付ける。（[Ctrl] + [Alt] + [V]→[V]と押す。値の貼り付けのショートカットキー）
（普通の貼り付けをすると、ピボットテーブルを維持したまま張り付く。この後の工程で扱いにくい。）

[Ctrl] + [H]を押す。（置換のショートカットキー）
「検索する文字列」に「株式会社」と入力、「置換後の文字列」には何も入力せず、「すべて置換」を押す。これにより、すべての「株式会社」が、削除される。

同様に、「㈱」「（株）」も置換機能で削除する。

＜機能⑧＞「置換」で不要なデータを一括削除
■機能：エクセルシート状の文字、数値を別の文字、数値に置き換える機能。
本稿では、文字列を空白に置き換える目的（＝削除）で使用している。
（一か所ずつ、「Back Space」「Del」で文字列を消していては、大変。）
この置換の機能は、別のデータ分析に特化した機能ではないが、分析作業に入る前にデータの手直し（データのクレンジング）に活用することが多い。
■やり方①：「ホーム」タブ⇒「検索と選択」⇒「置換」
やり方②：[Ctrl] + [H] （置換のショートカットキー）

次に、半角のカタカタ、アルファベット、数字を全角に変換する。
ピボットで集計した顧客別売上・利益表上の「行ラベル」の右側に空欄を作りたい。
「合計 / 売上」列をすべて選んで、行の挿入を行う。

行/列の挿入ショートカット[Ctrl] + [Alt] + [+]を押す。「挿入」ウインドウが開くので、「右方向にシフト」を選択して、「OK」を押す。

出来た空欄列に、半角⇒全角に変換する関数を仕込む。
B16セルに「=JIS(A16)」と入力する。単に「あかさ」と表示されるだけで、何も変わらない。（あかさは、全角だから）

B16セルの右下角にマウスカーソルを持っていき、カーソルが「+」に変わったら、ダブルクリックする。これで、B16の値が、表の下までコピーされる。（オートフィル機能）
すべての顧客名が、全角になった。

＜機能⑨＞セル内の文字、数字を変換・置換する関数シリーズ（JIS関数等）
■機能：セル内の文字、数字を一定の方法で変換・置換する。
本稿で使用したのは、JIS関数で、半角の英数字、カタカナ、記号を全角にする機能を持つ。
比重に似た機能を持った関数をまとめたので、以下を参照してください。

＜機能⑩＞ダブルクリックのオートフィルで一気に入力
■機能：連続データの入力やコピーができるオートフィル機能を、マウスのドラックではなく、ダブルクリックだけで実行する。
マウスのドラックだと、マウス操作によって疲労したり、選択範囲が行き過ぎてしまったりすることがありますね。
■やり方：コピー元セルの右下角にマウスカーソルを持っていき、カーソルが「+」に変わったら、ダブルクリックする。

＊＊＊ダブルクリックのオートフィルの制限＊＊＊
・縦方向（下方向）にしかオートフィルできない
・隣接した列にデータが入力されている必要がある

続いて、数値の単位を揃える。
ピボットで集計した顧客別売上・利益の数値を千円単位に変換して、予算表に合わせよう。
どこでもいいから、表の近くの空白セルに、「1000」と入力する。そして、[Ctrl] + [C]でコピーする。

顧客別売上・利益の数値部分をドラッグして選択し、右クリック。
「形式を選択して貼り付け」⇒「形式を選択して貼り付け」と進んで、クリック。
「形式を選択して貼り付け」ウインドウが開くので、「除法」を選んで、「OK」を押下。

これで、顧客別売上・利益の数値が千で割られて、結果として数値が千円単位の表記変わる。

＜機能⑪＞「形式を指定して貼り付け」の「演算」の活用
■機能：コピーした数値をセルに張り付ける際に、演算（加算、減算、乗算、除算）を付加する機能。
例えば、数値50をコピーして、数値100が入っているセルに、「形式を指定して貼り付け」の「演算」の「加算」を選択すると、そのセルの数値は150となる。
■やり方①：まず演算した数値をコピー（[Ctrl] + [C]）、貼り付け先のセルを選んで右クリックし、「形式を選択して貼り付け」⇒「形式を選択して貼り付け」で押下。そして、「形式を選択して貼り付け」ウインドウ中、使用したい「演算」を選んで、「OK」をクリック。
やり方②：まず演算した数値をコピー（[Ctrl] + [C]）、貼り付け先のセルを選んで、[Ctrl] + [Alt] + [V]を押して、「形式を選択して貼り付け」ウインドウを開く。そして、使用したい演算のアルファベット（加算：D、減算：S、乗算：M、除算：I）を押して、[Enter]を押す。（ショートカットキーで行う場合）

以上で、VLOOKUP関数の下準備（データのクレンジング）が整った。

＜オススメの書＞　データ分析の基本が演習を通して身に付きます。

VLOOKUP関数でデータを貼り付ける

予算と4月の実績を比べるために、予算表の右横に4月の実績を貼り付け、比較表を作るのです。
単に2つの表を横に並べるのではなく、予算表の顧客名に対応するよう（顧客名の順番に）、4月の実績を貼り付けます。この作業に、VLOOKUP関数を応用するのです。
（VLOOKUP関数を応用して、データを並べ替えながら、2つのデータを合体）

F4セル（予算表の最初の顧客の右横）に、下図の通りVLOOKUP関数を仕込む。

F4セルに、下の表（4月の売上・利益の集計）より「ABC」社の売上を引っ張ってきた。
このVLOOKUP関数を下方向へコピー。

「おやっ、一番下の「合計」に対して、数値が引っ張られない。なぜだ？？」
よく見ると、予算表は「合計」という表現を使っており、一方、４月の集計表（下の表）では、ピボットテーブルの名残で「総計」と表わされている。
「無理やり合わせに行けばいいこと」と、予算表の「合計」を「総計」へ手修正する。
これで、合計値もVLOOKUP関数で4月の実績値を引っ張ってきた。

次に、「利益」も同様に、VLOOKUP関数で「顧客名」をキーにして、下の表から実績値を引っ張ってくる。
G4セルを選択して、[Ctrl] + [R]（右隣りへコピー、RはRight（右））で、F4の数式をコピー。
このままだと、F4と同じ数を引っ張ってしまうので、VLOOKUP関数の「列番号」を「2」から「3」に入れ替える。これで、下の表の利益の数値を引っ張ってくるようになりました。

同様に、下まで数式をコピーします。今回は、すぐ左隣にデータがあるので、ダブルクリックでオートフィルが可能です。

利益率に関しても、上記の利益と同じようにVLOOKUP関数をコピーします。
すべて「0」と表示されてしまうので、[Ctrl] + [Shift] + [5]を押して、パーセント表記にします。

分析作業

これで、予算表の右横に、4月の顧客別売上・利益・利益率が張り付きました。後から見ても分かり易いように、見出しを打っておこう。

予算と4月の実績が比較しやすいように、4月の実績の右隣りに、実績から予算を差し引いて増減額を表わそう。
これで、顧客ごとに売上、利益（利益率）が増えたのか、或いは減ったのかが一目瞭然となる。

算出した増減額を見ると、各顧客とも予算に対して多少の売上・利益・利益率の増加/減少は確認できるが、やはり最たるものは、「あかさ」社であることが確証をもって判明した。

「一応、検算しておこう。」
あかさ社の4月の売上実績に予算の利益率をかけてみる。
4月の売上は、予算通りの利益率であったら、591千円を稼いでいた。4月の実績に比べ、198千円多い。この198千円を4月の利益に加算して、4月の売上で割って、利益率を出すと、30%となる。
これで、利益率2%ダウンの原因は、あかさ社一社であると断定できる。

あかさ社の営業担当者、Aさんだ。外出中のAさんを携帯で捕まえて、あかさ社の利益率が下がっている理由をヒアリングした。
「他社から強烈な売り込みがあり、売値を下げないと、取引継続が難しいとあかさ社より言われ、4月より料金改定となり、利益率が下がった」とのことだった。
「これで、支店長に明快な報告ができるが、一方で4月から利益率ダウンでスタートか。。。うかうかしてられないな。早急に手を打たないとな。」

＜一緒にチェック　関連記事＞
利益が減少した原因を分析する。　エクセルの機能 x 分析の鉄則　Vol.1
利益が減少した要因を分析する方法　サラリーマン算数(2)
利益と利益率から売上を計算する方法　サラリーマン算数(1)
業績数値を理解しよう（１）
おすすめビジネス・ツール　計算機　－　カシオの「ツイン液晶」計算機

＜分析に欠かせない支援ツール＞　＊分析は疲労との戦い