【エクセル中級者向け】周りと差をつける関数&テクニック

【エクセル中級者向け】周りと差をつける関数&テクニック

あなたのエクセルレベルはどのくらいでしょうか。

マクロまでは組めないけど、IFやVLOOKUPは使える。一般的に中級者レベルがそのあたりといわれているらしい。仕事をする上では、それで充分問題ないかもしれませんが、エクセルには過去の実績値やデータを元に未来推計ができる便利な関数があります。

例えば、過去の決算情報から今後の売上高や営業利益を推計したり、データを元に価格を推計したり…など。

今回は、データ分析や提案作成の作成時に根拠や説得材料として使える関数やテクニックを紹介していきます。知っておいて損はありません。むしろ周りと差をつけられるはず。

FORECAST関数

業務スーパーでお馴染みの生活食料品などを製造・販売する株式会社神戸物産の決算情報を元に、2021年の営業利益推計を出してみたい。

それには、FORECAST.LINEARという関数を用います。

株式会社神戸物産IR・投資家情報|財務情報

・Xには、2021年10月のセル
・既知のyには、過去5期の年度のセル
・既知のxには、過去5期の営業利益のセル

年々売上、営業利益ともに増加傾向であることが分かりますが、2021年の営業利益は25,653(百万円)という推計値が出た。

これは、単回帰分析という方法で、2021年10月の営業利益(目的変数)を過去5期の営業利益(説明変数)の動きで予測しており、最も都合が良い直線(回帰直線)を引いていくというものです。

当然ながら、企業の売上や営業利益はコロナウイルスのような世界的なイベント、市況や景気に左右されるので、今年も来年もこれまでと同じような直線を描いていくということはあまり考えにくい。

しかし、例えばこれが広告費と販売数の予測だったらどうでしょうか。広告費はコントロール可能な説明変数なので、その予測はかなり精度が高いものになります。施策等を議論する分析としては、非常に実務で強力な武器になり得る関数だと言えます。

TREND関数

次に不動産賃貸の賃料相場を推計していく。今度は、TREND関数を使います。

名古屋の繁華街である栄駅周辺の賃貸物件のデータを抽出しています。

これらのデータから築45年、最寄り駅から徒歩7分、専有面積25㎡という物件の家賃がいくらになるのかを周辺の賃料相場から推計していく。

・既知のyには、周辺の賃料のセル
・既知のxには、周辺の築年数、距離、面積のセルを範囲選択
・新しいxには、予測したい物件の築年数、距離、面積のセルを範囲選択

結果、4.9万円という値が返されました。

先ほどのFORECAST関数では、過去の営業利益という1つのデータを元に推計を出しましたが、TREND関数は築年数や距離、面積といった複数の説明変数を元にしています。これは、重回帰分析という方法で、予測要因が1つであれば単回帰分析(FORECAST関数)、複数であれば重回帰分析(TREND関数)を使います。

例によって、賃貸物件の賃料は木造や軽量鉄骨、RC造といった構造にもよるので、一概には決して言えないものの予測要因がこれだけ多くても複雑な式を組み合わせることなく、関数1つで済むのです。

注意したいのは、いずれもあくまで傾向を掴むという点に留めたいこと。

回帰直線は過去のそれぞれのデータが一番近くなる点を繋げて直線を作っているから、データ量が少なければ精度は低くなるし、推計の元となるデータの信憑性も精査する必要があります。

CORREL関数

物事は因果関係で説明することが多いですが、ビジネスマンたるもの相関関係もきっちり抑えておきたい。相関関係には、CORREL関数を使います。

相関と聞くと途端に拒否反応が出てしまう人は多いと思いますが、ちょっと待ってほしい。

百聞は一見にしかず、血液検査のγ-GTPの値と1週間あたりの運動時間の関係を例に見ていく。※γ-GTPはアルコールの飲酒や運動不足などで脂肪肝と判定されると数値が高くなり、正常値は50以下と言われています。

・配列1には、γ-GTPのセル
・配列2には、運動時間のセル

これは、実際の過去13回の私の献血の値と、1週間あたりの運動時間のデータを元にしているのですが、相関係数は-0.94だった。

相関係数は-1~1の間の値で、0に近ければ近いほど相関関係が弱い、-1に近いほど負の相関が強く、1に近ければ正の相関が強い。今回は、-0.94なので「運動時間が増える」と「γ-GTPの値が下がる」という負の相関関係があるということが分かる。※ちなみに、気温が上がるとアイスクリームの販売数が増えるというのは正の相関です。

散布図も作成していますが、“挿入”タブからグラフ選択、データ列を範囲選択するだけで簡単に作成することができる。グラフの+から近似曲線のチェックを入れると直線が引かれるので視覚的にもとても分かりやすい。

この直線が右上がりだと正の相関、下がっていると負の相関があるということになる。提案資料の根拠であったり、方針を決める説得材料の一つとしても活用できるので、何か複数のデータを取り扱う際には相関関係があるのか?という視点で見ていくと面白い発見があるかもしれない。

ゴールシーク

【問題】1,980円の価格の商品を1,000個仕入れた。原価は1個780円。さて、何個売れば粗利が0になるか。

答えは394個になる。

こういう時に便利なのがゴールシークだ。“データ”タブからWhat-If分析→ゴールシークにある。

“数式入力セル”に粗利益のセルを選択し、“目標値”を0にする。そして、“変化させるセル”に売上率を選択。これだけ。

粗利を0にするための売上率は39%なので394個売れば仕入れ額をペイできるということになる。

数式を使って=780*1000/1980で簡単に分かるじゃないか。と思われるかもしれないが、計算式に固有値を含んだ計算式を入力してしまうと汎用性が著しく下がる。

粗利額を〇円にした場合、粗利率が〇%の場合、売上が〇円の場合など色々と試行錯誤したいときには対応できない。単純に数値を知りたい場合は良くても、一覧性のあるシミュレーション表ではそうもいきません。

いくら売上を上げれば、または、いくつ売れば損益が0になるか。または、営業利益〇%を達成するために必要な売上額はいくらかなど、計算結果から逆算したいときが1度ならず何度もこれまであったはずだ。

数式入力セル、目標値、変化させるセルを変えれば、売上額でも粗利率でも逆算して値を求めることができるので、数値を何回も入れ直して近い値から目標値を探っていくということをしなくてもよくなる。

計算式の参照元のセルの数値を何度も打ち込んでいた人はきっと少なくないでしょう。

さいごに

他にも便利だけど、意外と使われていない、使いこなせていない機能や数式はその存在を知らないだけで実はたくさんある。

後から教えてもらったり、自分で調べてみて、こんな簡単にできる方法があったのか…という発見は誰もが経験があることでしょう。今目の前にある事務作業ももしかすると大幅に短縮できたり、やらなくていいなんてこともあるかもしれない。

また、また別の機会にまとめていきたい。

ナレッジ・ノウハウカテゴリの最新記事