Excelでは計算式を作成することができますが、その計算式が間違っていた場合、後々になって被害が大きくなる時があります。例えば、毎月の売り上げ集計を自動的に計算する計算書を作成した場合、そのシートはひな型となり、各月ごとに使われていきます。場合によっては複数の部署で使われます。そのシートに計算間違いがあって、誰もがその間違いに気づかず1年間が過ぎたとしたら、決算データに間違いが出て、その修正に多大な時間がかかるでしょう。
Excelの計算式は、そのようなことが起きないように、計算式を作成する都度にチェックしなければなりません。
Excelの計算式作成の間違い
Excelの計算式の間違いはいくつかのケースがあります。
計算ミス
計算式自体が間違っている場合です。関数名が間違っている場合はエラーが出ればわかりますが、例えば足し算を掛け算より優先するための「( )」忘れなどは、答えが求められてしまうので、一見すると間違いではなく見えます。
計算式のコピー忘れ
計算式はコピーすることができ、なるべく一つの計算式を作成し、範囲に対しコピーして計算するのが一般的ですが、そのコピーした範囲が足りないケースなどもあります。特にIF関数でエラーが出ないように空白にするという計算式の場合、計算式が入っていてもいなくてもセルに表示されるのは空白なので、ついうっかりしてしまうかもしれません。
絶対参照忘れ
絶対参照を忘れてしまい、そのままコピーするケースです。コピー先のセルのどこかでエラーが出たらわかるのですが、そのエラーが出ているのを見逃したり、計算式の構造によってはエラーがでなかったりする場合があります。そうすると放置されがちなまちがいになります。
関数の引数の省略
一部の関数では、引数が5つのものでも、はじめの3つを入力しただけで動作する関数があります。代表的な例はVLOOKUP関数で、完全一致かどうかを設定する4つ目の引数は省略できます。しかし、省略した場合は、近似値一致となります。VLOOKUPの場合、完全一致のケースがほとんどでしょうから、ついうっかり省略してしまうと、想定していない値が求められてしまうのです。
Excelの計算式のチェック方法5つ
Excelの計算式のチェック方法は次の通りです。それぞれどんな間違いを検出できるか違いがあります。組み合わせて効果を発揮するチェック方法もあります。それぞれのチェック方法でどの種類の間違いを見つけるのに最も効果的かというのは、次の表のとおりです。
値を入れてチェックする
その計算式のもととなるセルに値を入れ、結果がきちんと表示されるか確認します。どの間違いにも対応できる基本のチェック方法です。
数式をダブルクリックでチェックする
セル上で数式をダブルクリックすればその参照元のセルが色分けで確認できるので、その位置が合っているかをチェックします。ほかの方法と組み合わせても使える方法です。
参照元のトレース
数式タブに参照元のトレースがあるのでクリックするとアクティブセルの数式が参照しているセルを矢印で表示できます。ただし、ほかのシートを参照している場合は、参照元への矢印は表示されません。
一番上と一番下で数式チェックする
コピーした範囲でチェックするのに有効なチェック方法です。数式が入っている最初のセルで、値を入れたり、ダブルクリックでチェックしたり、参照元のトレースで数式をチェックします。それが終わったら、今度は数式をコピーした最後のセルをクリックして同様にチェックします。
コピーした範囲の途中のチェックは不要です。上と下さえ合っていれば、よほどのことがない限り、その間の計算式は正常に合っています。
一番小さな値と一番大きな値でチェックする
関数の引数を省略したときにおかしな値が出ているかチェックできます。特にVLOOKUP関数やMATCH関数などのセルを検索する関数で効果があります。
一番小さな値（検索範囲の最初の値）、一番大きな値（検索範囲の最後の値）の2つの値を入れてチェックします。その値の値ではチェックする必要がありません。最も小さい最初の値と最も大きい最後の値さえ確認できていればその間の値は問題ないのです。
もし計算式がコピーされているのであれば、一番上と一番下のセルでこの方法でチェックしてください。
チェック表の作成
Excelは手軽に計算の仕組みを作成できるのが魅力なので、どこまできっちり計算式の動作を確認するかというのは、その計算書の規模や、使われることが想定される期間によって変わってきます。
1回しか使わない計算書では、チェックするというよりも一回一回の計算式を間違わないで作成するということに集中した方がいいのかもしれません。
世の中には25年前に作成された計算書が今でも使われるというケースもあり、実際に使い始めたら無限に使うことが想定される場合もあるでしょう。そんな時はより完璧なチェックが必要です。
Excelではないのですが、プログラムを作成した場合はチェックを必ず行うのですが、そのチェックはとても厳密に行います。そのためのツールがチェック表です。
チェック表を作るには、考えうるすべてのケースに対し、どんな項目をチェックするか、それぞれの項目について、値をどのように入力し、その結果、どのようになるのかを1項目ごと考え、設定します。この項目のことをテストポイントと呼びます。そのテストポイントの設定値と結果をまとめたチェック表にします。最初のセルと最後のセルチェックや最小値と最大値のチェックを行う場合は、1つのチェックで、複数のテストポイントを設定する必要があります。
請求書の計算シートであれば、30項目程度のテストポイントになります。
まとめ
Excelで作成した計算式が合っているかどうか不安な時もあるでしょう。そんなときに役立つExcelの計算式でどんな間違いが起きやすいかと、間違いが起きていないかをチェックする方法を解説しました。
今回紹介した間違い以外にもほんのちょっとの勘違いで、間違った計算式がExcelでは作られます。そのような間違いの場合でも、今回紹介した5つのチェック方法を応用すれば、見つけることができるでしょう。
チェック表まで作成し、チェックするとなると大幅な時間がかかります。実際に中規模のExcelのシートを8時間で作成したのですが、完璧なチェックシートを作成してチェックしたら全部で1週間かかりました。
Excelはこのように作成する時間よりもチェックする時間のほうがはるかに多くなります。
しかしながら、そこまで労力をかけるかどうかは、ケースにもよります。
少なくとも、1つの計算式を作成するごとに今回の5つのチェックで細かくチェックしていき、全体ができてからの修正を少なくしておきましょう。
このようなチェック方法の知識はExcelの機能や関数よりもExcel全体に関わる基礎部分ですので、Excelを使う上でしっかり意識しておく必要があります。
コメント