作成したExcelシートの動作を確認する方法

2018.04.18基礎・入門関数

Excelで作成した計算シートは、何度も繰り返して使うことができ、その都度計算式を入力する必要はなくなります。
元のデータを置き換えるだけで結果が再計算され求められるので、業務処理の単純化、短時間化が可能なのです。
一方で、計算シートを作成した時に、計算方法を間違えたり、セルの指定範囲を間違えたりしていると、その計算シートを使って作成したものは、すべて結果が間違えている可能性があります。
そこでチェックが必要なのですが、すべてのケースを想定したチェックをすれば完璧ですが、無限にあるパターンを一つ一つチェックすると大変な時間がかかります。
そこで、ポイントを抑えたチェックが必要になってきます。
今回は、Excelで作成した計算シートのチェックの方法を計算方法別に解説します。

SUM関数

SUM関数は、指定した範囲の合計を求める関数です。
セルA1からA5までの合計を求めるには次の計算式になります。
=SUM(A1:A5)
非常に単純な計算方法ですが、最も多い間違いは参照したセルを間違えていることです。
関数の参照元のセルがどこかを簡単に知るためには、関数の入力されているセルをクリックして、F2キーを押して計算式を表示させれば、参照元のセルを色分けで表示することができます。

SUM関数の場合

 

この場合、セルB4が合計の範囲に入っていないことがわかります。
AVERAGE関数、COUNT関数でも同じようにチェックすることができます。

IF関数

IF関数は、ある条件に当てはまっている場合とそうではない場合で仕分けする関数です。
セルA1が100を超えていたらA、超えていなければBと表示するには次の計算式です。
=IF(A1>100,"A","B")
この場合、実際にセルA1に100を超えた値と超えていない値を入力すればチェックができますが、ではどんな数値で確認すればよいでしょう。
今回は100が境界線で超えているかの判断をしますので、100と101の2種類、さらに、100を含んだ境界もチェックするために、99もチェックしておいて3種類でもよいでしょう。

AND関数、OR関数の入ったIF関数

IF関数とAND(なおかつ)、OR(または)関数を組み合わせるととても多くのパターン分岐することができます。しかし、動作チェックをする時には、パターンの多い分チェックするのが大変です。
そこで、はじめに入力するパターンをすべて一覧表に書き出すとよいでしょう。例えば次のような出張管理表があります。

AND、ORの場合

 

出張期間が4日までは係長の承認があって許可になります。課長の承認がある場合は出張期間に関わらず許可になります。
この場合の二行目に対する計算式は次の通りになります。
=IF(OR(AND(B2<=4,C2="許可"),D2="許可"),"許可","不許可")
条件を分岐するセルがB2、C2、D2と3つあり、その組み合わせにより条件が分かれます。出張期間は4以上かどうかを判断するので、4と5のみチェックすればよいです。
それぞれのセルに入力される項目の組み合わせパターンを表にすると次の通りになります。

真理値表

 

全部で8パターンあることがわかりました。
このように、どこがどうだったら、どうなるかの一覧表のことを真理値表と呼びます。
今回はこの8パターンのみ、チェックすればよいです。

VLOOKUP関数

VLOOKUP関数は、検索値を一覧表から探し出し、その対応する値を求める関数です。
次のような計算書があります。

VLOOKUP関数

 

商品名を一覧表から検索し、それに対応する単価を求める2行目に対する計算式は次の通りです。
=VLOOKUP(A2,$F$2:$G$6,2,FALSE)
4つめの引数がFALSEで設定する完全一致で検索するVLOOKUP関数は、一覧表の中から値を検索しますが、その一覧表内の検索値の一番上と一番下をチェックすれば、その間は間違いなく検索されますので、チェックは2点のみでOKです。
つまり、商品名がA001で商品単価が100、商品名がE005で商品単価が150であることのみ確認すればよいです。
VLOOKUP関数では次の式のように検索値が空白だったらというエラー回避をする場合があります。
=IF(A2="","",VLOOKUP(A2,$F$2:$G$6,2,FALSE))
この場合は、一覧表内の検索値の一番上と一番下の他に、空白だったら空白になることも確認します。

まとめ

Excelシートを作成したら、計算式のチェックをしましょう。
今回は、そのためのチェック方法を説明しました。
計算式の構造を確認して、ポイントを絞ってチェックすることで、チェックする回数を最小限に考えることができます。
また、Excelには数多くの数式をチェックする機能があります。
その中でもトレース矢印はもっともポピュラーなチェック方法です。
トレース矢印の詳細は詳細はこちらで詳しく解説しています。

Excel業務をもっと「ラク」にするなら

Excel業務を自動化したい!
Excelをデータベース化したい!

 

Back to top