“ひと目でわかる”エクセル表の作り方

写真拡大

■表作りの基礎

タイトル、罫線、数字の揃え方。どうすれば見やすくなるだろう……

----------

【STORY】
あなたは自動車販売店の新人営業マン。ある日、課長から呼び出された。「うちの店で扱う商品の1カ月の売り上げ状況を表にまとめてくれないか。今の管理表はゴチャゴチャしていてわかりにくいんだ」。これまで、名簿程度の表しか作ったことがないあなた。期限は明日。さあ、どうする?

----------

資料は誰かに報告するために作成するもので、相手の目線で整理することが重要です。Excelでの表作りが苦手な人は、相手が求めていることをよくわかっておらず適切に情報を整理できないために、うまくいきません。

資料作成で最初にやるべき作業はWHO、WHAT、WHYを決めることです。

WHOとは資料を見せる「相手」を意味します。たとえば業務効率を高めるITシステムの導入であれば、投資判断をする人は費用対効果の数字を求めたがりますが、現場担当者は使い方や作業負荷の軽減度合いを知りたいと考えるでしょう。

WHATはその資料を使う「ねらい」です。情報を共有する場合と、相手に意思決定を求める場合では、資料の作り方が当然変わります。

WHYは、WHOとWHATの組み合わせが妥当である「理由」です。ねらいに対して最も効果的に行動してくれる相手に向けてその資料は作られているか、よく考える必要があります。

----------

POINT●表作りの3カ条
WHO――資料を見せる「相手」を考える
WHAT――その資料を使う「ねらい」を理解する
WHY――相手とねらいが妥当である「理由」を確認する

----------

[これが正解!]
⇒ムダな罫線がなくなり、桁、単位もそろってスッキリ

売れている車種や利益が一目でわかる表が完成。数字の桁や単位を揃え、フォントを変えたことで見やすくなった。さらに、固定する数字を青、変動する数字を黒で色分けし、今後価格や利幅が変動したときに影響する数値が明確に。課長も納得の表ができた!

【表作りの基本を押さえよう】

1. タイトルは見やすく、左上に
表を作成するときには必ずタイトルを表内に入れる。位置は表の左上がベスト。簡潔に太字で記載する。表全体を見やすくするために、表の左右上下には最低1列は余白を入れたい。

2. 罫線はグレー横線のみ、行の高さも変更
罫線は、表の一番上と一番下を太めの線で強調。表の中の線は一番細い線にすることでメリハリをつける。数字を見やすくするために行の高さもデフォルトの13.5から18に変更を。

3. 数字は「Arial」、右揃えで桁合わせ
数字のフォントは「Arial」に統一すると、遠目でもくっきり。すべて右揃えにすることで縦に並ぶ数字の桁が揃い、縦線も必要ない。シート全体を選択して上のメニューバーから「,」を押すと千単位にカンマが付く。

4. 数字は単位を別立て「、千円」揃えがプロ仕様
桁数が多くてもすべて表示されるように列幅を広くとっておく。さらに、単位は別に列を作って記載することで参照しやすい。上場企業の決算資料単位は「千円」が基本なので、使い慣れたい。

5. 計算式で変動数字と固定数字を色分け
計算式によって決まる数字=黒、最初に決まっている数字=青で色分け。モノクロ印刷のときは青字部分を斜体にする。利益シミュレーションなどにも活用度大。青の数字を変更すると、黒の数字が連動して変わる。

6. マイナス数値は赤字か( )で注意喚起
3. で「,」を設定すると、自動的にマイナス数字を赤に変えてくれる。しかし、モノクロ印刷で配布される場合も考えマイナス数字を( )に入れるといい。右クリック→セルの書式設定→「表示形式」タブ→(1,234)を選択。

■データの整理

----------

課長「この表だと情報が多すぎてよくわからない。「納品地域」だけ教えてくれ」
 ⇒あまりに膨大すぎるデータ。必要な情報だけを取り出すには……

----------

Excel初心者のネックになりやすいのが、データの整理、分類です。たとえば顧客情報などの膨大なデータから欲しい情報だけを取り出したいとき、どの機能を使えばいいのかわからない。そんな人が多いのです。

同じ条件のデータだけを取り出し表示させるには、フィルター機能を使用します。上の表を例にとると、フィルター機能を使用することで、納品地域のなかから特定の地域のデータだけを抽出して表示できます。

また、車台番号に購入者や納品地域、納品時期などの詳細情報が連なっている表から、ある車台番号に紐づいている特定の情報だけを表示するときにはVLOOKUP関数を使用します。

VLOOKUP関数を使うときに、数式のコピーで失敗する人がよくいるので、注意してください。セル内の数式に含まれるセル番号が相対参照(後に説明)のままだと、数式をセルごとコピーして別セルに張り付けるときに数式内のセル番号がずれ、参照範囲も変わってしまいます。この場合セル番号を絶対参照で指定し、参照先を固定する必要があります。

【フィルター機能とVLOOKUP関数を覚えよう】

●フィルター機能
(A)たとえば、限られた「納品地域」と「納品時期」の2つの条件に当てはまる納品先情報だけ取り出したい場合、フィルター機能が便利。項目内容から条件に合致したデータを抽出できる。まずは、一番上の項目名の範囲を選ぶ。

(B)選んだ状態で「Ctrl+Shift+L」を押すか、「並べ替えとフィルター」→「フィルター」を選択すると項目欄に「▼」が現れ、条件で検索できるようになる。

●VLOOKUP関数
表の中から目的の値だけを見つけたいときはVLOOKUP関数を使う。検索値を範囲の左端の列で検索し、見つかった行の左から指定する列番号にあるセルの値を呼び出す。完全一致を探すときは右記の関数の「検索の型」に「FALSE」と入力。最も近い値を探すときは「TRUE」と入力。

----------

=VLOOKUP(検索値,範囲,列番号,検索の型)

----------

【相対参照と絶対参照を理解しよう】

●オートフィル機能
連続したデータを素早く入力するときに便利な機能。上図のD7セルには=D4*D5という数式が入力されているが、D7セルの右下にマウスポインタを合わせ右方向にドラッグすると、数式が自動入力される。

●相対参照
オートフィルを使って数式を自動入力をする際、行番号と列番号はその範囲に合わせて変化する。これが相対参照。上図の場合、E7セル、F7セルにはそれぞれ=E4*E5、=F4*F5という数式が入力された。

●絶対参照
総売り上げ価格のうち消費税額を算出したい場合、8%という固定した数字を掛け合わせる。そんなときに使うのが絶対参照で、セルの行番号、列番号の前に「$」をつける。

POINT●役立つ関数&ショートカットキー(Windows)

▼役立つ関数10
AVERAGE関数●平均を求める=AVERAGE(数値1,数値2,…)
COUNT関数●数値のセルを数える=COUNT(セル範囲1,セル範囲2,…)
IF関数●条件によって求める値を指定する=IF(条件式,成立,不成立)
ISERROR関数●選択したセルにエラーがあるか判断する=ISERROR(範囲)
MAX関数●最大値を求める=MAX(数値1, 数値2, …)
MIN関数●最小値を求める=MIN(数値1, 数値2, …)
ROUND関数●四捨五入する=ROUND(数値,桁数)
SUM関数●合計を求める=SUM(数値1+数値2+…)、または=SUM(範囲)
SUMIF関数●条件に一致するデータの合計を出す=SUMIF(範囲,検索条件,合計範囲)
SUMPRODUCT関数●選択したセルの掛け算の合計を出す=SUMPRODUCT([配列1],[配列2],[配列3],…)

▼ショートカットキー10(Windows)
行全選択●Shift+スペース
列全選択●Ctrl+スペース
矢印方向へセル複数選択●Shift+矢印
アプリケーションを終了●Alt+F4
前後のシートを表示●Ctrl+up/down
検索●Ctrl+F
置換●Ctrl+H
元に戻す●Ctrl+Z
既存のエクセルファイルを開く●Ctrl+O
複数のブックを1つずつ閉じる●Ctrl+F4

■グラフの選び方

----------

課長「新しい4車種について、台数ごとに利益がどう変化するか教えてくれ」
 ⇒どれが一番利益が出るか、パッとわかるグラフにしたい

----------

一口にグラフといっても棒グラフや円グラフ、折れ線グラフなど用途によってさまざまなものがあります。

そのなかで適切なグラフを使い分けるには、基本的に「量」「推移」「割合」の3つに分けて考えるとよいでしょう。

要素の量を表すには棒グラフを使います。要素ごとに大きさを表す棒が並ぶことで、規模の大小を感覚的に把握するのに役立ちます。

過去からの推移や量の推移を表すには折れ線グラフを使います。時点ごとのデータが線でつながれて表されるため、時間軸に沿った要素の変化を示すことに適しています。

要素間の割合を示すには円グラフを使用します。要素ごとに大きさを扇状に並べることで、分析対象データに占める上位の要素を特定したり、要素間の割合を比較するのに役立ちます。

そのほかによく使用されるグラフとしては散布図があります。これは個々のデータの分布を点として記録したもので、各要素のバラつきから特定の観点に基づく傾向を見つけるのに役立ちます。

[これが正解!]
⇒折れ線グラフなら推移の比較が一目瞭然

新しい4車種は販売台数が1〜5台のときと6台以上では利益額が異なる。折れ線グラフにすることで、各車種の台数ごとの利益額が明確に。このグラフを見ると、たとえば、10台以上売れたときに利益が最も出るのは、E車であることがわかる。ちなみに、2台以下ならH車、3〜6台ならF 車、7〜9台ならG車がそれぞれ最も利益が出ることになる。このグラフを見ながら、チームで今後の販売戦略が立てられそうだ。

【グラフ作成のためのデータを整理しよう】

1. まずは基本条件を整理
かかる初期費用、販売台数によって変わる利益を表にまとめ、情報を整理しよう。6台目以降は、E車の利益が急増することがわかる。

2. 基本条件から数値を計算
グラフに必要な数値を1.の基本条件の表から計算する。5台目以前は、「初期費用−台数×1台分(1〜5台目)の利益」の数式を設定し5台目以前に適用。

3. 数式を使って利益を計算
6台目以降も「初期費用−台数×1台分(6台目以降)の利益」の数式を設定。オートフィル機能(43ページ参照)で全車種の範囲を選んで適用する。

4. 表から簡単にグラフ作成
3.で作った表の範囲を選び、エクセルメニューの「グラフ」から適したものを選択する。今回は推移が見て取れる折れ線グラフが適切。

POINT●用途に合わせたグラフ選び

▼3大基本グラフ
[数量]要素の大きさや量を示したい→棒グラフ
[推移]量や数値の推移を示したい→折れ線グラフ
[割合]全体要素における割合を示したい→円グラフ

▼そのほかのグラフの使い分け
多数の要素における割合を示したい→面積図
時間軸に沿った総量の変化を示したい→面グラフ
量のバラつきを示したい→ヒストグラム
要素同士の関係性を示したい→散布図
平均だけでなく最大値や最小値を→エラーバーグラフ
標準値に対する差異を複数観点で示したい→レーダーチャート

----------

外資系コンサルタント 吉澤準特
ビジネスからシステムまで幅広くコンサルティングを行う。『外資系コンサルの仕事を片づける技術』ほか著書多数。

----------

(外資系コンサルタント 吉澤 準特 構成=宮内健 、岩辺みどり)