この世で一番便利なソフトは満場一致でExcelであろう。異論は認める。
多分便利さの源泉は「関数」「マクロ」「機能」の3種類に大別される。んでもって、関数を使用する際には原則がある*1。この原則を知らずにExcelを使用すると、まあろくでもない結果を招くので覚えておくといいだろう。なぜか巷のExcel講師は教えてくれない。
1.表の下に表を作らない
Excel関数を実施する際、表を参照することは多い。vlookupやxlookupなんかが典型的な例だ。問題はこの参照される表が「何行まで拡大されるか」である。たとえば8月というデータなら夏と表示させる、4月というデータなら春と表示されるような計算式を考えると、月数は12しかなく、それ以上増えることがないので「12行＋見出し1行」の表を作ってvlookupで参照すれば、解決しそうな気がする。
このように、どうやっても増えないような情報を参照させる場合の表を縦に並べるのはそれほど悪くない考えに見える。しかし、「3月下旬は春かもしれないけど、3月上旬は冬なんじゃね」とか、「9月上旬はどう考えても夏だろ」とかいう、「お偉いさんのどうでもいいが無視できない意見」があり、それに対応しなければならない時、月＋旬*2の情報で季節を管理する必要がでてくる。すると「12行×3＋見出し1行」で37行のデータになる。
こうなると「月と季節情報を掲載していた表」の下に配置していた表の位置が大幅にずれてしまう。表の位置がずれてしまうと、その表を参照している計算式を見直す必要が出てきてしまうのだ。
なので「表の下に表を作らない」というのは結構重要な原則だったりする。Excelトレーナーは教えてくれないけど。
2.検算せよ
Excelに詳しい人がバカにする習慣に、電卓で検算するという習慣がある。個人的にこれは正しい習慣だと思う。
どんなSWにもバグがある。Excelにもバグがあるが、現在の枯れまくったExcelでは「Excel関数のバグ」なんて、一生に一回遭遇するかしないかだろう。問題は「誰かの作った計算式にバグがある可能性が極めて高い」ことだ。プログラマにはテストの習慣があるが、Excelの計算式作成者にテストの習慣はないので、バグの検出は難しいし、環境の変化によって後からバグになってしまう場合、例えば「データ範囲が計算式内の指定範囲を超えてしまう(以下最終行バグ)」こともある。よくあるパターンは「計算式が想定した表の最終行よりも下の行に情報がある」パターンだ。ちなみに最終行バグについては、最終行を指定しなければよいので、「上記１の対応」＋「範囲選択を列のみ指定にする」ことで発生させないことも可能だったりする*3。
なので、検算は正しい習慣だ。もっとも、検算を電卓で実施する必要はない。Excelに検算をやらせればいいのだ。集計値の合計と生データの合計が正しいかチェックする。別の関数を用いてチェックするなど、チェックの方法はいろいろある。なので検算の必要性を説かないExcel講師は似非だと思っている。
というわけで、Excel関数を使用する際の原則でした。
