読者です 読者をやめる 読者になる 読者になる

シャイニング丸の内日報

転職や仕事術、仕事へのスタンス

絶対身につけて欲しいエクセル術(前編)

こんにちは、シャイニング丸の内です。クライアントとかと接しながらいつも思うのですが、エクセルすら出来ない人多すぎ。あなたたちが作った適当なフォーマットと手打ちで苦労するの誰だと思ってるの。そこで今回は私の「社会人であればこんくらいは最低限出来てよ」というフラストレーションをぶつけた「これだけ最低限知ってろ、エクセル編」です。

・エクセルで出来ること

・あるべきフォーマット/作ってはならないフォーマット

・頻出ショートカット

・関数

・ピボットテーブル

・グラフ化

という構成で作ろうと思いますが書いていたらあまりにも長くなったので前編後編に分けて書きます。まずはショートカットまで!

1.エクセルで出来ること

エクセルは素晴らしい分析ツールであります。10万レコード以内で出来ることはかなり網羅的に出来るようになっています。例えば
・コスト体質の変革
・セグメント別または事業別の利益を算出し注力領域を定める
・販売数量と利益のシミュレーション
・新規事業でのシミュレーション作成による投資計画策定

コストから成長戦略まで幅広く使える、ふわふわとした主張ではなく数値を元にした主張をするための基本ツールがエクセルです。数値に強いはビジネスに携わる者としては必須条件にしてもらいたいものです。皆さんが今後恥をかかないために是非こちらに書かれているテクニックはマスターして下さい。読むだけではだめでサンプル・データなどを使って練習しましょう。2週間もかからず身につけられるはずです。

*エクセルマスターの皆さんへ:他にもいろいろあるぜ!という方いらっしゃると思いますが、最低限のボリュームで抑えてます

2.あるべきフォーマット/作ってはならないフォーマット

まず最初にこれ、フォーマットです。結合されたセル、入力されてない単位、縦線だらけの汚いテーブル・・・見ただけで発狂しそうになる。さらに図の挿入でテキストボックスだぁ!?君のお母さんはそんなことも教えてくれなかったのか。まず最初にエクセル禁則事項を集積させたシートを見てみましょう。

f:id:shiningmaru:20160505180604p:plain

これのクソな部分はなんでしょうか。

・セルが結合されている

・単位が入力されていない

・後にソートで分析することを一切考慮していないフォーマット

・見やすさのために罫線を入れているのかもしれないがかえって醜い

・入力されている数値が計算値なのかベタ打ちなのか分からない

あー発狂する。これをあるべき姿にしてみましょう。まず重要なことですが、エクセルはなんとなく作ってはなりません、常に「このシートは何故作るのか、何を分析したいのか」というゴールを設定して作成しましょう。

*ちなみに会社によって教えこむフォーマットは少々異なる

さて直していきましょう

まずフォント、上記はMSPゴシックをすべて使っているが英数のMSPゴシックを好まない人は多い、Verdanaというフォントの数値はウケが良いので変更してみましょう。下記フォント毎の比較ね。ただしこれは結構好み、優先度は低いかな。

f:id:shiningmaru:20160505181036p:plain

次、これよくあるのですがセルの結合!ヤメテくれ!あとから並べ替えしようにも出来なくなってしまう、セルの結合している人はエクセルを主に印刷して使うものと捉えている人が多いかもしれない。それではエクセルの力の10%も発揮出来ない、数値をぐりぐりいじって分析出来るところにエクセルのバリューがある。

次、よくあるけど単位ない!グレーで万円とか書くのが礼儀である。

次、フォーマット全体についてであるが上のエクセルの失敗は細かい部分のみならず軸をあのように設定した時点で失敗している。どうするべきなのか、基本はマスターとなるデータは縦軸、横軸を設定し並べ替え可能にするべきだ。ここでは経営分析用に使うためのマスターとなるデータを作成するというゴールで考えてみよう。簡単にするためすごくシンプルにすると・・・

 

f:id:shiningmaru:20160505183404p:plain

こんな感じ。上のものと変わっている点

・単位入れた

・なんのデータかを明示するために左上に表の名前挿入

・数値の種類によって色を分けた

・罫線の入れ方を変えた

・軸を変更した(最重要)

変更の中で最も重要なのは軸の変更である。いじる前のエクセルには縦軸横軸という考え方がない、だからセルを結合して印刷して見れればいいやと投げやりなエクセルになる。こういうエクセルを作ると後々、ジュニアがデータのリフォーマットだけで激しい工数を割くことになる。最初から整えて!(切実に)。軸さえ整っていれば、粗利率もさくっと計算出来る、月別のバラつき原因を探るための入り口になる、前月比較の数値もすぐに出せる、ソートするにも一発でソート出来るフォーマットになっている。横軸縦軸の徹底をしよう。また罫線、これも会社や業界によって好みがあることは理解しているけど私はこれが好き、白背景、上部下部に太めの線を入れて表中には縦線や実線は入れず点線で区切る。具体的なやり方はショートカットの項目で書きますね。

文字の色が変更になっているけどこれなにと思うと思います

f:id:shiningmaru:20160505183123p:plain

仮定とは後でいじったりするシミュレーションの入力値でよく使いますが、ファクトではなくよく分からんから仮置きしたもの、こちら黄色背景に青文字。ベタ打ちは文字通りベタ打ちの値、多用するべからず。計算値は例えば上の表なら粗利(売上-原価=粗利)。他のシートから持ってきた値については緑文字にする。

 長くなりましたがフォーマットについてのまとめ。

・何を目的としたシートかを考え横軸縦軸を設定

・セルの結合はダメ絶対

・文字の色を数値の種類によって分ける

・単位を入れる

・背景白地にする

 

3.頻出ショートカット

エクセルといえばショートカット、プロはマウスを使うと恥だそうです。全部記載すると膨大にあるけど頻出のショートカットを書きますね。

 

コピペ関連)

・alt + e + s(alt系は順番に押す) :形式を選択して貼り付け

いつまでもctrl+c,ctrl+vだけのそこのキミ。値貼り付けをしてくれと言われたらどうする?数式をそのままコピペすると数式のままコピーしちゃうでしょ。数値をベタ打ちとして貼り付けたい場合のalt+e+sの登場です。

例えば先ほどのエクセルシートの粗利を何故か横にコピーしたいとしましょう。

f:id:shiningmaru:20160505191220p:plain

あらーなんか数値ずれちゃった、なぜかというとこれは数式をコピーしているから粗利の数式は「2つ左のセルから一つ左のセルを引け」という式、これをそのままコピーすると右の領域のようになってしまうわけね。

f:id:shiningmaru:20160505191404p:plain

そこで粗利の部分を選択したらalt+e+sで形式を選択して貼り付け画面の登場

「値」を選択してokをすれば・・・

f:id:shiningmaru:20160505191523p:plain

めでたく数値が貼り付けられました、ちなみにこの形式を選択して貼り付け、様々な場面で登場します。「行列を入れ替える」で転置出来たり書式もコピーするかなど選択出来る万能系。

・範囲選択のshift,ctrl

あとこれも知らない人結構いて驚いたのが範囲選択時のshift,ctrlの使い方。

f:id:shiningmaru:20160505214523p:plain

shiftを押しながら矢印キーで移動すると移動した範囲を選択、ctrlを押しながら移動すると表の端から端へ一回のキーで移動出来る。つまり上の画像のような範囲を選択したいならまず5にカーソルを合わせ、shiftとctrlを押しながら一度下の矢印キーを押すと9まで一気に選択、右を次に押せば表全体を選択出来る。

・列の挿入:ctrl+spaceで列を選択、shift+ctrl+plus(+)で挿入

また例えば「原価率」という列を原価と粗利の間に挿入したくなったとしましょう。粗利の列にカーソルをあわせctrl+space(同時押し)、すると列が選択されます。

f:id:shiningmaru:20160505214938p:plain

↑これね

その後shift+ctrl+space(同時押し)

f:id:shiningmaru:20160505215024p:plain

めでたく列が挿入されました。こういった操作も一瞬で出来ます。ちなみに行の挿入も同じ流れで出来ます。そのときは行を選択するのでshift+spaceです。

フォーマット用)

・alt+w+v+gで罫線非表示

背景の罫線をどうやって消すのかって?alt+w+v+gと順番に押してみよう、消えます。

・shift+ctrl+1でカンマ表示,shift+ctrl+5で%表示

数字にカンマを入れたい?0.02とかになっているものを%で表示したい?おまかせあれ、

- shift+ctrl+1、三桁毎にカンマ挿入

- shift+ctrl+5、小数点のものを%表示

出来ると格好いいです。

・ctrl+1でセルの書式設定

罫線を自由に配置したい

f:id:shiningmaru:20160505215308p:plain

セルの書式設定におまかせあれ。ctrl+1でこの画面が現れ罫線を自由に配置出来ます。

前編はとりあえずここまで、後編は

4.関数
5.ピボットテーブル
6.グラフ化

のコンテンツ書きますね、みんな勉強しようぜ。

ちなみに私が使っている参考書は次の二冊。一冊目はエクセル教室やってた熊野さんのもの。二冊目はエクセルが好きになったら是非やってみて、趣味的に楽しい。ちなみに私はたまにマクロ使うけど普通は必要ないよ、むしろマクロ使うと他の人がいじれなくなるからあまり使うのは推奨されないよ。

 

 

EXCELビジネス統計分析 [ビジテク] 第2版 2013/2010/2007/2003対応

EXCELビジネス統計分析 [ビジテク] 第2版 2013/2010/2007/2003対応