始まりは。。
ある日仕事で使っていたエクセルファイルに下のような数式が含まれていました。これはエラーにならず間違った答えを表示してしまいます。1
この時は「あれ。。結果おかしくない？」と目視で気づけたのでよかったのですが、危うく大問題の資料を世の中に出してしまうところでした。このようにExcelは非常に便利ですが、なかなかその人の個性が出てくるので人のフォーマットの地雷を気づかないうちに踏んでいることがあります。
そこで「データをもらった時に解読から始めなくてもいいシートづくり」のために必要なことをまとめました。
コメントでみなさんの普段使っているExcelファイルを作るときに気を付けていることも教えてください。
フォーマットを作るときに必ず注意すべきこと
- スタイルダイアログを使ってセルに含まれる値の意味が一目瞭然にする）
Shift+Alt+バッククォート - 非表示は使わずグループ化を使う）
Shift+Alt+→ - 使ってないシートは消す
- 図形はでたらめに配置せず、『グリッドに合わせる』機能を使う。
Altキー押しながらでも同様の機能を得る - コメントや説明図を入れてわかりやすく。
Shift+F2 - 数字にはカンマ区切りをつける。
Ctrl+Shift+1 - ポカヨケ（fool proof：フールプルーフ）を設置する。例えば、誤った値や未入力がある場合には条件付き書式を使ってセルが赤塗りつぶしになる等。
- 印刷用のシートとデータ処理用のシートは分ける。印刷用は改ページプレビューにする。
- 日本語しか入らない部分には入力規則を利用してIMEコントロールを入れる。
- データの再利用性が下がるので、無駄なセルの結合はやめる。
- フォントは特別な理由を持たずに個別にせず、レイアウトタブのフォントで「見出し・本文」を使って設定。
わかりやすい関数・数式処理を行う
よくプログラミングで後から読み返すことが到底できないようなコードを「スパゲティコード」と呼んだりしますが、そういったネストだらけな式を作らないようにします。
-
Ctrl+Shift+@でセルの数式の表示を切り替える。 - 数式入りのセルには色をつける。もしくはシートの保護をかける。
- 関数のネスト（入れ子）はなるべくやめてわかりやすい式を書く。
- シートが多いときにはハイパーリンクでつなぐ、ブックを超えたリンクは作らない。
- 数式が複雑になるときにはセルに名前をつけて使用する。その場合はユーザに「名前の管理」から確認するように促す。
エラーは必ず起こる、フェールセーフの考え方
- エラーが起こったときの対応をIFERROR関数で記述しておく。
- VLOOKUP関数やMATCH関数の「検索の型」はExcelの内部処理の仕組みがわからないと予期せぬエラーを起こしやすいのでケーステストをしっかり行う。
Excelによるエラーの種類
-
#####これはエラーではない、入力した数値や計算結果の桁数がセル幅より大きいため表示しきれない -
#DIV\/0!数値を0で割り算したときに表示される -
#N/A関数や計算式に使用できる値がないときなどに表示される -
#NAME?関数名が間違っているときなどに表示される -
#NULL!指定した2つのセル範囲に共通部分がないときなどに表示される -
#NUM!関数または計算式の数値に問題があるときなどに表示される -
#REF!計算式内で使っているセル参照に誤りがあるときなどに表示される -
#VALUE!関数の引数に誤りがあるときなどに表示される
-
エクセルではTRUEやFALSEの論理値と数値の比較ができないため、このような二重比較はできない。しかし内部の扱いとしては論理値はどんな数値よりも大きいという返り値を返すので上の例はFALSEとなる。正しい数式を書くためにはAND関数を使わなくてはならない。 ↩