この記事は、IT開発Excel Advent Calender 16日目の記事です。前の記事は、エラー! 参照元が見つかりません。です。
何だかんだ言われますけど、やっぱりMicrosoft Excelは、ぼくのような職業の者にとっては必須のツール、言ってみれば「盲腸」のような存在です。使わない日はないと言っていいでしょう。
Excelは毎日使うものという前提で、では、いかに良いExcelシートを作るか、ということに注目したいと思います。
良いExcelシートの条件
体裁などの点ではなく「内容」という観点で、わたしの考える良いExcelシートとは（少なくとも）以下の2つの性質を備えています。
今日はこの(1)について考えてみます。
ここでExcelの可読性とは、「数式の意味がわかるか」ということです。様々なExcel関数を縦横無尽に駆使して、最低限のデータから必要な情報を導出する。Excel好きにはたまらない挑戦ですよね。
しかしそういう数式は、他人が読むと意味不明なのでメンテナンス困難。ヘタすると、自分でさえ読み解けないことがあります。ですから、Excelの数式はとにかく、読みやすく作るべきです。
直接参照を減らす
Excelの数式の可読性を上げるには、まずセル番地の直接参照を減らすことです。
数式にセル番地が直接書かれていることの欠点は、以下の三つです。
要は、「セマンティクス」って言ってみたかっただけです。
セル番地の直接参照を減らす方法は、二つあります。一つは、「名前」の指定。もう一つは、「テーブル」の利用です。
順に説明していきましょう。
「名前」の指定
たとえば下のように、商品idと購入日を入力すると、税込金額を計算するようなリストを作るとします*1。
このリストの「税込価格」列は、定価に消費税率をかけて計算します。消費税率は右のリストからVLOOKUP関数で引いてくると、以下のようになります。
=C3*VLOOKUP(D3,$G$2:$H$4,2,1)
ただの掛け算ですが、すでに意味不明ですね。もちろん、各セル番地が指す範囲を確認していけばわかるのですが、ひどく読みづらい式であることは否めません。
「テーブル」を使う
そこで、二つのリストをテーブルに変換しましょう。テーブルとは？とか変換の仕方についてはコチラ。
リストをテーブルに変換したうえで、あらためて数式を作ると、次のようになります。
=[@定価]*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、明日は数式を計算できません。数式のセル参照は計算結果を参照（循環参照）しています。次のいずれかを実行してください。の予定です。
