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

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

Excelにおけるナンバリング技法の発展

 Excelで大きな表を作ること、よくありますよね。その表の一番左の列、そう、「No.」とか「項番」とか「#」とかいう列名が付けられているあの列。みなさんはどのように入力していますか?
 本エントリは、表の各行に番号をつける「ナンバリング」という行為を巡る冒険の物語。わたしのExcelヒストリアの中で、ナンバリングがどう進化してきたかをお伝えします。どうでもいいですよね。

Excelでナンバリング

1. 素朴な手作業

 最も牧歌的な入力方式で、「1」「2」「3」・・・と地道に入力していきます。30行を超えたあたりから人生に疑問を感じますが、ここで間違った方向に開眼し、この作業のためにUSB型テンキーを購入するという狼藉に及ぶ剛の者もいます。

2. オートフィル

 Excelの入力テクの1つ、「オートフィル」によって、連続データの入力は格段に楽になります。
 データ行の最初のセルにだけ「1」と入力しておいて、そのセルの右下反転部分を右ドラッグ&ドロップ。現れたメニューで「連続データ」を選択すれば、ドラッグした範囲に「1」の連続データ、つまり「2」「3」・・・が埋められます。「1」と次の「2」まで埋めてあれば、左ドラッグ&ドロップでも、Excelが1のインクリメントと判断してくれます。

03-01_ae886228

 はっきりいって、ナンバリングの方法としてはこれで十分です。
 しかし・・・ どうなんでしょう。
 3, 6, 9, 12, と来て、次に16が来るなら、数値で表現するしかないかも知れない。でも単純に3, 6, 9, 12, 15, …と3ずつ増えていくことが確実なら、数列で3*iと表現したい。
 値のロジックが分かっているのに、数値そのもので表現するというエントロピーの大きさに、やりきれなさを覚えますね?それがExcelistってもんです。
 数式を、使いましょうよ。
※以下に示す数式は、1行目にタイトル行、2行目からデータ行が始まるような表を想定しています。数式内のセル番地は、セルA2に入力する場合のものです。

3. インクリメント

 では、項番のロジックを素直にExcelで表現してみましょう。
 項番のロジックとは、「データ行i=1の項番は1。行i(i>1)の項番は、行i-1の項番に1を加えたものである」数学的帰納法みたいな話になってきましたが、つまり、1行目にだけ「1」という数値を入力ておき、2行目には「=A2+1」と入力、3行目以降は2行目をコピーとすればよい。Excelの相対参照により、たとえばデータ行の5行目では「A2」は自動的に「A5」に修正され、「=A5+1」、つまり「5」となります。
 これは、よく見かけるやり方じゃないでしょうか。

4. ROW関数の利用

 3.の気持ち悪いところは、1行目にだけ直接、数値を入力しなくてはならない点です。何とか、すべての行を同じ式で表現して、自動計算させたい!という欲求から生まれたのが、行の番号を利用するという方法。数式は、行番号を取得する関数ROWを使って、
=ROW(A2)-1
とします。表のデータ行は2行目から始まるので、ROW(A2)=2から1を減じることで、「1」となります。
 なおROW関数は、引数を省略すると自動的に自分自身のセルを評価しますので、
=ROW()-1
でもよいです。

5. ISNUMBER関数の利用

 4.には致命的な弱点があります。それは、「データ行が始まるのが何行目か」ということを常に意識しなくてはならないということ。
 表タイトル行が1行目、データ行が2行目から始まるのであれば上の式でよいのですが、データ行が102行目から始まるのであれば
=ROW()-101
のように、いちいち数式を変更しないといけない。これは、カッコ悪い。3にはなかった弱点です。
 シートのどこから表が始まってもいいという3.の長所と、1つの関数で表現できるという4.の長所を併せもった表現はないものか・・・。
 3.の欠点をもう一度考えてみましょう。それは、1行目だけ特別扱いになること。逆に言うと、「1行目において、それが1行目であることを認識させる」ことができれば、条件分岐IFが使えます。
 では、1行目の特徴とは何でしょう。それは、「1行目の上にくるのは列名だから、(多分)数値じゃない」ということです。
 よって、「自分の上にあるセルの値が、数値がどうか」を判定させればよい。数式は、以下です。
=IF(ISNUMBER(A1),A1+1,1)
 ISNUMBERは、引数が数値かどうかを判定する関数。この数式では、自分の上のセルが数字じゃないなら、(1行目と判断して)「1」を。数字なら、その数字を1だけインクリメントする。という動作になります。この表をそのまま他の場所にコピペしても、項番はズレません。素晴らしいですね!

6. OFFSET関数の利用

 残念ながら、5.にもまだ問題点があります。それは、カット&ペーストに弱いというところです。これは、5.だけでなく、関数を使った3・4・5すべての弱点。
 5.の数式で、セルA2は「1」と計算されています。しかし行2をカットし、最後の行にペーストすると・・・。それは「1」のままです。
 これは、Excelにおけるコピー&ペーストとカット&ペーストの大きな違い。コピーの場合はコピー先に応じて数式の参照先を修正しますが、カットではカット元の参照先を修正しない。つまり、「自分以外のセル」を参照していると、カット&ペーストによる悲劇が起こってしまうのです。
 では、どうする。数式中に「自分以外のセル」を使わずに、これまでの「自分の1つ上を見る」というロジックを、表現できるのか!?
 20年に渡る苦悩の末生まれたのが、次の数式です。
=IF(ISNUMBER(OFFSET(A2,-1,0)),OFFSET(A2,-1,0)+1,1)
 基本的な考え方は5.と同じ。ただ、「A1」という「自分以外のセル」を使うのをやめて、OFFSET関数により「自分」(セルA2)の1行上のセルを見ることを表現しています。
 この表であれば、どこにカット&ペーストしても、もとの表の項番が維持されます。もちろんコピー&ペーストも問題ありません。そして何より、どの行にも同じ数式が入っています。
 現時点では、この数式がわたしの中で最強です。が、「こっちの方が上だぜ!」というナンバリング数式があれば、教えてください。挑戦を待つ・・・が、誰も来ないだろうな。

Wordでナンバリング

 Wordでは、Excelのようなオートフィルもできないし、数字の入力面倒過ぎだよ・・・仕方ない、ExcelでオートフィルってからそれをWordにコピペするか・・・という方のために、Word表のナンバリング数式も考えておきましたよ!
 あまり使用されていませんが、Wordでも表中に数式を使うことができます。図表番号を自動的に付与するときに使う、「フィールドコード」の機能です。
 といっても、フィールドコードが提供する関数はシンプルなものばかりで、ROWもISNUMBERもOFFSETもないもんだから・・・悩みました。みなさん心して使ってください。
 まず、フィールドコードの入力方法から。
 表のどこかを選択した状態で「表ツール」から「レイアウト」リボン-「データ」-「計算式」を選択(Excel2007)すると、計算式のダイアログが現れます。

03-02_914d1697

 そこに、以下の式を入力してください。
=COUNT(ABOVE)+1
 「COUNT」はExcelと同様、数字の入ったセルを数えます。「ABOVE」は、「自分より上の範囲」を表します。
 結果、データ行の1行目であれば、上にはタイトル行しかなく、そのセルにはおそらく数字以外が入っているので、「COUNT(ABOVE)」は0。1足して「1」になります。3行目であれば、1行目の「1」と2行目の「2」、2つの数字が入っていることから、「3」となりますね。
 行の挿入・削除で項番がズレてしまった場合でも、このコードを入れておけばいつでもリナンバリングできます。ただし、図表番号と同じく、明示的にリフレッシュ(選択して[F9])しないと値の再評価はされないので、注意ですよ。
 ちなみに、フィールドコードの評価結果でなく、数式を直接見たい場合は、[Alt]+[F9]で表示の切り替えができます。Excelでいえば[Ctrl]+[Shift]+[@]に相当します。
 さあ、これで、みなさんの生産性も1、インクリメントされましたね?これからも表を作りまくってください!