エクセルで分析を行う際に心がけなければならないこと

前書き

　筆者は今でこそまでこそPythonやRなどを用いてデータ分析を行っているが、一昔前はエクセルを使うことが非常に多かった。そして、いまでも多用している。また、pythonなどでしかできない分析はpythonでやるべきだが、そうでない分析は基本的にエクセルで済ませてしまうべきだとも思っている。その理由は、エクセルでの分析はpythonを利用した分析に比べ他者への共有が容易であるためである。そのため、コードを書いて分析を行うことが多くなった今でも、エクセルを用いた分析からは逃れられないのだと感じている (もっとも、私は所謂データサイエンティストではないので、データ分析の専門職の方々がエクセルを用いた分析を行っているのかは分からないが)。今回は、エクセルで分析を行うにあたって必ず意識すべきである点をまとめた。様々な細かなポイントにも触れているが、要は見やすく分かりやすく、ミスを減らすということが"良いエクセル"を設計するための要諦である。

目次

1. 設計思想
2. 設計思想を貫く基本的な作法
3. 具体的な作業

1. 設計思想

　エクセルで分析を実施するにあたってまず重要になるのが設計思想である。根本的な思想を持たないままに、こまごまとしたテクニックだけを学んで作られたエクセルには実際に分析を行うにあたって様々な不都合が起きる。具体的には①ミスがある、②ミスはなかったとしても、他の人が読みにくかったり引継ぎが困難になる、である。①に関しては、一貫した思想が無ければミスが起きやすくなることは想像に難くないだろうし、②に関しても考え方が一貫していないエクセルは読みにくいことこの上ない。仮に自分しか見ないエクセルであっても、3か月後に見たら何が書かれているか全くわからなくなることも容易に想像できる("良いエクセル"は3か月後に読んでも30秒で何をどう計算しているのかを再確認できる)。大事になる設計思想とは、一言で言えば見やすく読みやすいエクセルである。当然、見やすく読みやすいエクセルを作る目的は①ミスを減らす②他者と共有できるようにする、の2点である。この後、様々なテクニックや工夫を述べていくが、その根底にはこの基本思想があることを理解して欲しい。

2. 設計思想を貫く基本的な作法

1.文字の色のルールを徹底的に守る
　私が後輩にエクセルについて指導する際には、まず文字やセルの色のルールからたたき込む。文字の色は何色でも良いわけではなく、1つ1つの色に意味が無ければならない。何色にどのような意味づけをするのかは何でもよいが、大事なのは各色に意味を持たせることと、そのルールを100%遵守することである。1か所でも守られていないルールはそもそもルールが無い方がマシである、くらいの心持ちで設計したい。
　例えばであるが、私の場合普通の計算式は黒色、数字のベタ打ちは色1、他のシートから参照している場合は色2というように分けている。このように、色に意味を持たせせることによって、他の人が数字がどのように流れているのかを追いやすくなる。
　そして、この色の設定は必ず入力した直後に毎回行わなければいけない。「あとでまとめて色を変える」というのは絶対に漏れが出る。色に意味を持たせることは大事だが、その色付けを100%守り切ることが大事なのである。ちなみに、この色の設定は頻繁に行う作業であるので、可能であるならばマクロで色を変更するショートカットを作成することを強くお勧めする。

2.数式には数字のベタ打ちをしない
　エクセルは表計算ツールなので、基本的には各セルに数式が入る。しかし、数式だけではなく数字をベタ打ちして入力しなければならないことも発生する。その際、数式の中に数字を直接入力してはいけない。
　例えば、A3セルに入っている単位が円の金額データを、単位を百万円にしたければ、「=A3/1000000」と書いてはいけない。A2に1000000と入力し、「=A3/A2」と入力すべきである。
　このルールが守られていないエクセルは第三者目線から見て非常に分かりにくくなるだけでなく、「この式の中に使われている数字はどこから来たのか？」が分からなくなりがちである。上記の例だと1000000は単位変換のためのものであると分かってくれそうであるが、実務上ではどこから来た数字なのかが分からなくなりがちである。数字のベタ打ちが必要な際は、数式の外に出し、その数字の入ったセルを参照するという形で計算を行うのが分かりやすくて良いエクセルである。そしてその際、その数字が何のためのものなのかの簡潔な説明を隣のセルにするのが他者へのマナーである。
　ただし例外があり、boolean、すなわち識別のための0と1は記号とみなし、数式の内部に入れても良い。

3.他のシートからデータを参照する際は、可能な限り式を簡略化する
　他のシートからデータを参照する際に、その数式が複雑だと、非常に計算が追いにくい。極力計算はせずに、単純に"="でデータを引っ張ってくるだけにとどめ、計算は別のセルで行うべきである。理由としては、他シートからの参照は式が長くなりがちで分かりにくくなる、数式中に他シート参照があると視覚的な一貫性がなくなり、どのシートから参照してきたのかが分かりにくくなる、というものがある。とにかく、第三者目線から見て分かりやすいエクセルを作ることを徹底的に心がけることが重要である。

4.他のシートからデータを参照しない場合でも、可能な限り式を簡略化する
　とにかく式は短いに越したことはない。例えばA2のデータとB3のデータを足し合わせたものに、C3の数字を掛けたいとき、"=(A2+A3)C3"とするよりも、D3に"=A2+A3"と入力し、"=C3D3"とした方が簡潔な式となる。この例はかなり極端だが、とにかく数式は可能な限り短くするという執念が見やすく分かりやすいミスの出にくいエクセルを作るにあたって重要となる。式が長くなりすぎた場合は途中計算を格納するセルを設けるべきである。

5.欲しい機能は必ず存在する
　Microsoft様はさすが天下のMicrosoft様。凡人が考えるようなことはとっくに考え尽くしている。そのため、欲しい機能や関数は99%存在するだろうと思っておいた方が良い。分析を行うにあたって煩雑な繰り返し作業が発生した場合は、その作業を簡略化できる何かしらの機能が無いかをGoogle検索するべきである(そして、それを解消する機能は大抵ある)。煩雑な繰り返し作業は時間がかかるだけでなくミスも誘発するので極力何らかの機能・関数を使って避けるべきである。

6. テーブル間での一貫性を保つ
　同じシートに複数のデータテーブルが存在するとき、その縦の列には同種のデータ、横の列にも同種のデータが入っていることを意識する。また、基本的に1つのテーブルには同じ関数が入力され、左上のセルを右下にコピー&ペーストするだけでデータテーブルが完成するように設計されていることが望ましい。理想的には、1つのテーブル内だけでなく、それが複数のテーブルにわたって同じ式になっていることが望ましい。これは作業スピードが格段に上昇するだけでなく、第三者から見ても最初のセルだけを確認すればよいのでとても読みやすいエクセルとなる。

7. データの流れは一方向にする
　基本的にエクセル内でのデータの流れは右から左に流れていくように設計することを心がけたい。データが様々な方向から参照されているエクセルは第三者からすると非常に読みにくい。例えば、一番右のシートに生データ、その左のシートに計算用シート、一番左に結果シート、と言ったように配置すれば、データが綺麗に右から左に流れて読みやすく分かりやすい分析となる。シート間だけでなく、シート内であっても参照は右から左、上から下にデータの流れが統一されていることが望ましい。

8. セルの結合はしない
　各所でもさんざん言われていることだが、セルの結合をしてはいけない。理由としては、Ctrl + Spaceによる列選択ができなくなってしまうからである。(巷では、単純に"セル結合はするな"と言われているが、理由まで理解している人は実はあまりいない)
　なお、複数列に共通のコラムタイトルを設定したい場合は「セルの書式設定」->「配置」タブ->「横位置」->「選択範囲内で中央」を選択した後で、「セルの書式設定」->「フォント」タブ->「下線」->「下線(会計)」を選択することで設定が可能である。(ただしこの操作はやや面倒であるので、可能であるならばマクロでショートカットとして登録してしまいたい)

9. 他ファイルからの参照は絶対に行わない
　エクセルには便利?な機能として他のファイルからデータを引っ張ってくる機能があるが、極力この機能は使わない。様々な理由があるが、主には他の人にエクセルを共有した際に、その人がリンク先のファイルを見れない可能性があるからであり、引継ぎを行う際に失敗しやすい。そんなことは分かってるし自分は他ファイルからの参照なんてしないと思っている人もいるが、ピボットテーブルのコピー＆ペーストをする際に図らずしも他のファイルのを参照してしまうことが発生する可能性があるので注意したい。

10. 罫線を引くのは最小限に
　ビジュアルにこだわるのは悪いことではないが、原則として枠線の使用は避けたい。理由としては、行や列を挿入するにあたって枠線がずれてしまい、再度引き直す手間が発生するからである。この手間は想定以上に自分の時間も相手の時間も奪うことになる。

11. 行や列の非表示はしない
　行や列を非表示にすることはしない。理由は、非表示にしたことに気づかずに誤って式を消してしまうリスクが大きいためである。セル数が多くなり、見栄えをよくするためにセルを非表示にしたい場合には、グループ化を利用する。グループ化によって列を非表示にする場合は普通に非表示にする場合と比べて上部(行の場合は左部)に+マークがつくので非表示にしたことに気づきやすい。

12. 小まめにセーブする
　特に重いエクセルを扱うときにはできるだけ小まめなセーブを心がけたい。エクセルは、特に重いデータを扱ったり複数のエクセルを開いている時によく落ちる。エクセルが落ちると運よく復旧できることもあるが復旧できないこともある。大量の作業が水の泡に帰すことの悲しみは私もよく分かる(本当によく分かる)ので小まめなセーブを心がけたい。エクセル上級者ほど、アドインやマクロで自分のエクセルをカスタマイズしているので落ちやすくなっていることに注意したい。また、こまめなセーブを行う際にはバージョン管理を徹底したい。たとえマイナーチェンジであっても同じ名前でファイルの保存はせず「名前を付けて保存」を心がけたい。

3. 具体的な作業

　これまで書いてきたことが、読みやすく分かりやすく、ミスを減らすためのエクセルの基本である。一見簡単なことのように思えるが、99%のエクセル初心者が上記のルールの徹底ができない。とにかく上記の思想を完璧に順守することがエクセル上級者への第一歩であると心得たい。そのうえで、その思想を実現するにあたっての作業上の細かなテクニックがある。そのいくつかをここで紹介したい。

1. よく使う作業についてはショートカットを覚える
　エクセルの操作はピボットテーブルを除き基本的にはキーボードのみでの操作が可能である。すべてのショートカットを覚える必要はないし、それはほぼ不可能であるが、よく使う機能については頭でなく指で憶えておいた方が作業スピードは格段に向上する。

2. セルの中の編集はF2で編集する
　ショートカットの中で個人的に最も有用だと思っているものが「F2」である。これは、セルの編集に使うショートカットで、マウスで数式をバーを選択せずとも、F2を押すと数式を編集できる。これに伴い、「ファイル」->「オプション」->「詳細設定」->「セルを直接編集する」のチェックボックスはオフにする。

3. クイックアクセスツールバーを活用する
　よく使う一方で、特定のショートカットが無くAltで選択しなければならないコマンドについてはクイックアクセスツールバーを設定する。こうすると、長いショートカットでなく「Alt + 数字」の簡単なショートカットで操作が可能である。
　何をクイックアクセスツールバーに設定するのかはその人が行う作業にもよるし個人の好みもあるが、個人的には以下の機能を設定している。
　・参照元のトレース
　・参照先のトレース
　・すべてのトレース矢印を削除
　・電子メール
　・詳細を表示しない
　・詳細データの表示
　・重複の削除
　・塗りつぶしの色
　・What-if分析
　・やり直し
　・フォントの色

4. 便利な関数は把握しておく
　誰も知らない特殊な関数を使う必要はなく、基本的な関数を網羅的に抑えておくことが重要である。ただし、普段使わないような関数であっても、便利な関数は一通り頭の中に入れておくと、「ああ、ああいう関数があったな」と思い出してGoogle検索して関数を見つけることができる。特に、LEFT関数、RIGHT関数、MID関数、LEN関数、ROW関数、COLUMN関数、INDIRECT関数、ADDRESS関数などはあまり使っている人を見かけないが実際はかなり使い勝手が良い関数なので頭の片隅に入れておきたい。

5. 便利な機能は把握しておく
　ゴールシーク、配列関数、区切り位置指定ウィザード、単入力・複入力テーブルなど、その他便利な機能はいくつかあり、実際にどのような機能なのかを頭の片隅に入れておくと、いざ使いたくなった時にGoogle検索すればすぐ使えるようになる。(これはそのような機能があるということを知っておくことが重要であるのであり、それらの機能の使い方を覚える必要はない)

6. よく使うショートカット例
　以下はよく使うショートカットの例であり、便利だなと思ったものは指に覚えさせると良い。また、良く行う一連の作業はマクロとして登録しておくことも作業の効率化につながる。しかし、再三述べるが、エクセルのスキルというのは多くの関数やショートカットを知っていることではなく、いかにして綺麗で見やすく分かりやすいエクセルを作るか、そのための設計思想を守り続ける力のことである。

操作	内容
Shift Drag&Drop	セル範囲移動&挿入
Ctrl 矢印	次データにジャンプ
Ctrl Shift 矢印	上記＋途中セル選択
Shift Space	行選択
Ctrl Space	列選択
Ctrl PgUp/Dn	シート移動
Ctrl Home	シート先頭へ移動
F2	セルの中を選択
Ctrl [	セルの参照先に移動
F5	特定のセルを選択
Ctrl Tab	エクセルブックの切替
Ctrl A	表全体を選択
Alt ↓	プルダウン
Alt ;	可視セル選択
Alt d p	ピボットテーブル
Alt d t	データテーブル
Ctrl D/R	上/左のセルをコピー
複数セル選択してCtrl Enter	複数セル全てに代入
Alt Shift →	グループ化
Ctrl Shift l	オートフィルタをかける/はずす
Ctrl ;	現在の日付
Ctrl :	現在の時刻
Alt ↓	上のセルと同じ内容を入力
Ctrl -	行、列の削除
Ctrl Shift ;	行、列の挿入
Ctrl Alt V	形式選択ペースト
F12	名前を付けて保存
Ctrl Shift 5	%フォーマット変換
Ctrl Shift P	フォントサイズ変更
Shift F10	右クリック
Shift F11	新シート作成
Alt o d	条件付き書式
Alt w f	ウィンドウ枠固定
Alt t u t	リンク元の表示
Alt t u a	リンク表示の消去
Ctrl 1	セルの書式固定
Ctrl shift 6/7	セルの周りの罫線
Alt t u d	リンク先の表示
Ctrl 6	打消し線