ソフトウェアの品質を学びまくる2.0

ソフトウェアの品質、テストなどについて学んだことを記録するブログです。旧ブログからゆっくり移行中です。http://blog.livedoor.jp/prjmng/

可読性の高いExcelシートを作ろう!#excelAdvent2013

 この記事は、IT開発Excel Advent Calender 16日目の記事です。前の記事は、エラー! 参照元が見つかりません。です。

 何だかんだ言われますけど、やっぱりMicrosoft Excelは、ぼくのような職業の者にとっては必須のツール、言ってみれば「盲腸」のような存在です。使わない日はないと言っていいでしょう。
 Excelは毎日使うものという前提で、では、いかに良いExcelシートを作るか、ということに注目したいと思います。

良いExcelシートの条件

 体裁などの点ではなく「内容」という観点で、わたしの考える良いExcelシートとは(少なくとも)以下の2つの性質を備えています。

  • 可読性が高い
  • 情報が冗長でない
  •  今日はこの(1)について考えてみます。
     ここでExcelの可読性とは、「数式の意味がわかるか」ということです。様々なExcel関数を縦横無尽に駆使して、最低限のデータから必要な情報を導出する。Excel好きにはたまらない挑戦ですよね。
     しかしそういう数式は、他人が読むと意味不明なのでメンテナンス困難。ヘタすると、自分でさえ読み解けないことがあります。ですから、Excelの数式はとにかく、読みやすく作るべきです。

    直接参照を減らす

     Excelの数式の可読性を上げるには、まずセル番地の直接参照を減らすことです。
     数式にセル番地が直接書かれていることの欠点は、以下の三つです。

  • セル番地は単なる位置情報でしかなく、そこに入っている値が何を意味するかというセマンティクスを持っていない。
  • 縦に読むか横に読むかによって、セル番地指定のどっちに「$」を付けるかといった本質的でない悩みにさらされる。
  • 移動すると番地が変わる。
  •  要は、「セマンティクス」って言ってみたかっただけです。

     セル番地の直接参照を減らす方法は、二つあります。一つは、「名前」の指定。もう一つは、「テーブル」の利用です。
     順に説明していきましょう。

    「名前」の指定

     たとえば下のように、商品idと購入日を入力すると、税込金額を計算するようなリストを作るとします*1

    https://farm5.staticflickr.com/4689/39474698261_019e2af146.jpg

     このリストの「税込価格」列は、定価に消費税率をかけて計算します。消費税率は右のリストからVLOOKUP関数で引いてくると、以下のようになります。

    =C3*VLOOKUP(D3,$G$2:$H$4,2,1)

     ただの掛け算ですが、すでに意味不明ですね。もちろん、各セル番地が指す範囲を確認していけばわかるのですが、ひどく読みづらい式であることは否めません。

    「テーブル」を使う

     そこで、二つのリストをテーブルに変換しましょう。テーブルとは?とか変換の仕方についてはコチラ。

    allabout.co.jp

     リストをテーブルに変換したうえで、あらためて数式を作ると、次のようになります。

    =[@定価]*VLOOKUP([@購入日],テーブル7,2,1)

     この時点で、セルの直接参照がすべて排除されていることがわかります。二つが「@列名」という形(で表され、もう一つは「テーブル7」という形で表されていますね。*2

     この数式であれば、「税込価格」が二つの要素からなること。一つが定価で、もう一つが「購入日」をキーに、別のテーブルから引いてきた何らかの情報であることがわかります。
     「@列名」という表現は、リストをテーブル化したご利益の一つで、この例では「税込価格を求めようとする対象の行にある」という意味です。「@定価」とあれば、1行目の税込価格の場合に1行目の「定価」をもってきます。*3

    「名前」を使う

     次は「名前」です。これは、特定のセル範囲に対応する識別子となります。セル範囲は、この「名前」で詳細を隠蔽して、間接的に参照しましょう。
     実は、リストをテーブル化した時点で名前が自動的に付与されている(上の例でいう「テーブル7」)のですが、これでは単なる連番なので、適切な名前を付けます。
     「適切」とする要素は二つ。
     何を目的とするテーブルであるかがわかること。最低限の長さであること。たとえば右のテーブルを、「T_適用税率」としましょう。*4

     これによって、数式は以下のように改善されます。

    =[@定価]*VLOOKUP([@購入日],T_適用税率,2,1)

     ここまで来れば、第2項が「購入日をキーに求めた適用税率」ということまで何となく推測できます。
     ついでに、各商品がいくつ購入されているかは以下のような式になります。

    =COUNTIF(T_販売[商品id],[@商品id])

     「販売」テーブルの商品idが、個数を求めようとしている商品idに一致する場合にカウントする、ということがすぐにわかります。
     これを見てもわかるように、テーブルの列名もまた、簡潔で要を得たものである必要があります。

     数式が複雑になればなるほど、「名前」と「テーブル」を組み合わせて、「何をしている数式なのか」を理解してもらえるように書くことが大切です。

     Advent Calander、明日は数式を計算できません。数式のセル参照は計算結果を参照(循環参照)しています。次のいずれかを実行してください。の予定です。

    *1:「定価」についてはもちろん、別のリストから引いてきていると考えてください。

    *2:ちなみにこの数式では、購入日に1997年3月31日以前を指定すると #N/A になるなどの問題があるので、エラーハンドリング(笑)も必要ですよね。

    *3:Excel2007では「#この行」と表示されています。

    *4:Excelでセル範囲に名前をつける方法はコチラ