Excelの計算式では、思いがけずに間違えることがあります。特に、計算式をコピーするときには絶対参照を設定しないといけない場合があり、この絶対参照を意識せずに数式を作成した場合は大きな間違いになります。
また、数式を作成し、そのまま安心してしまうこともよくあります。
しかし、間違いは、しようと思ってするものではなく、思いがけずになってしまうもので、自信があったとしても、間違うこともあるのです。
そのようなことを防ぐためには、Excelのスキルを上げる努力よりも、Excelの数式が合っているのか確認することと、その原因を探るということができるかどうかが重要なのです。
エラー表示
Excelでは、よくあるおかしなパターンの時に緑三角表示でエラーを表示する場合があります。これが最も簡単なエラーを見つけることができる方法です。
エラーのあるセルを選択すると、そのセルの横に注意を示す四角形が出てきて、それをクリックすると、一番上にエラーの原因の説明文が表示されます。
ただ、そのエラーの中には、エラーではないものまでエラーにする場合もあるので、画面に表示されているエラーの説明も少し説明不足なので、代表的なエラー表示を解説します。
数式は隣接したセルを使用していません
このエラーは、説明の日本語がわかりにくいのですが、数値が連続している範囲で合計を求めるといった場合に、全部の範囲を合計の範囲に含めていないのではないかというエラーです。
この表では、セルE6のセルを見てみると、「=SUM(C6:D6)」となっていてC6からD6のセル範囲を合計しています。これは2月から3月の合計ということになっていますが、1月のデータがB列にも入っていることから、正解の計算式はB列を含んで「=SUM(B6:D6)」になるべきだということを示しています。
このエラーが間違いを起こすパターンは、次のようなパターンです。
1月から3月の合計をしているにもかかわらず、エラーになっています。
なぜでしょうか。
実はExcelは日付も数値データの一種ととらえます。B列の値も数値の連続している反なのだけど、合計に入れなくてもいいのか、ということを聞いてきています。もちろん集計日は合計に入れる必要はないのですが、エラーが表示されています。
もっと単純なパターンではＢ列に目標値の数値があるようなときにも合計には含めなくていいのにエラーになります。
矛盾した集計列の数式
このエラーも、日本語の説明がわかりにくいです。上下の計算式とは違い計算式が入力されているので、おかしいのではないかという意味です。
次の表では、単価と作業時間を掛け合わせたものが料金として計算されています。Excelは計算式をコピーして使うものなので、それ以外の例外が発生するのであれば、きちんとIF関数で例外用の計算になるような計算式を作らなければなりません。同じ料金を求める毛計算式の中で特別なことをすれば、それは間違いにも繋がります。
このケースでは、6月3日の計算式が、単価×作業時間の料金に1.1倍するために、セルE6の計算式だけ書き換えたことにより発生しました。
本来であれば、Ｆ列に「割り増し」という項目を設け、セルＥ4の時点で「=C4*D4*(1+F4)」のような割増料金を計算できる仕組みを取り入れるべきなのです。
しかし、それも一回の例外のためにすることとしては大げさなので、このように1つだけ計算式を変えるということをする時があります。
この場合も本来のエラーではないのにエラー表示になります。
ちなみに、このように1つだけ例外的に計算式を変えた場合、並べ替えるとその例外の計算式が違うところを計算する場合もあるので、注意しましょう。
数値が文字列として保存されています
書式設定が文字列になっているセルに数値を入力するとその数値は数値ではなく、文字列になります。文字列になると普通であれば自動で左揃えになるのですが、配置の設定で右揃えになっているのであれば、右揃えになり、一見、数値なのか文字なのか判定できなくなります。
次の例は、そのように設定してあるセルに「2,300」と入力したケースです。
このように文字列として入力されている数値は、Excelの四則計算ではエラーにならずに四則計算をしてくれます。しかし、関数になると、ただの文字列として判定され、SUM関数の場合であれば合計に含まれない、VLOOKUP関数の検索値になっている場合では検索値が見つからないということになります。
もちろん、中には数値ではなく文字として数値を入れたいという場合もあります。商品番号などのコード番号は「A10C」や「Z1YU」といった、数値というよりただの文字の羅列という意味合いが強いのでその一つとして「0133」という番号があったときにこれをExcelに入力すれば「133」という数値になってしまうので、こういう時には文字として入力させるために表示形式を文字列にして入力します。そのような時にはエラーでもないのにエラー表示されます。
数式表示して色で確認
数式を変更するには、その数式の入っているセルをダブルクリックしたり、選択してF2キーを押したり、数式バーをクリックするのですが、その状態になると、参照しているセルを色で確認することができます。
こうすることによって、計算式の行ずれや列ずれをチェックできます。
さらにこのずれは、参照先のセルの周りの色のついた線にマウスを合わせると上下左右の矢印状態になるので、これをドラッグすることで参照先を移動・修正できます。
非常に簡単に数式を修正できるので、僕の数式の修正のほとんどはこの方法で行っています。
コピーした一番上と一番下で確認
数式を縦方向や横方向にコピーした場合、Excelは確実にその内容をその範囲に合わせてコピーします。最初と最後の間にある計算式を突然1つだけ違う法則の計算式を作成してしまうことは絶対にありません。
そのように考えれば、チェックするのは、コピーした一番上と一番下の2か所の計算式だけ間違いないことを確認すれば、その間は間違いないことが保証されます。
この考え方はセルの色のチェックと組み合わせると効果を発揮するでしょう。
この方法は、VLOOKUP関数の絶対参照忘れのチェックに効果を発揮します。
この確認方法は、目や電卓で検算するときも同じで、一番上からコピーした計算式は、一番上と一番下だけをチェックすればよいのです。
IF関数は境界の2パターンで確認
IF関数は条件によって2つに分ける関数です。IF関数の計算式では、その2パターンで確認すればよいです。「〇」なのか空白なのかという条件なら、その2通りでチェックすればよいです。
例えば50以上という、ある数値と比較しその大小で判定する場合でも2つでよいです。この場合は、結果が変わる49と50で確認すればよいです。厳密に行うのであれば49.99と50で確認しましょう。
ただ、この場合、チェックに使う値を入れるセルが、何かの計算の結果だとちょっと複雑になります。
以下のような成績判定だと、国語と算数の成績を合計した値を求め、その値を使って判定しています。
159と160で判定する必要がありますが、そのためには国語と算数のどちらかの値を逆算しなければいけません。単純にはどちらとも80にしたパターンと、80と79の組み合わせにすればよいのですが、もっと複雑な計算方法だったり、合計の元になるセルが多かったりする場合はなかなか逆算が難しいでしょう。
そんな時は、どっと高度な使い方になりますが、データタブのWhat-If分析の中にある、ゴールシークを使うと、集計値を160にするために逆算したいセルの値をいくつにするか調べられるので、活用できます。
「数式入力セル」は集計値を計算しているセル、目標値は境界となる値、変化させるセルは集計地の元となるセルのうち一つのセルを指定します。
結果が出ると、最終的なIF関数の結果も自動で変わります。ここで確認ができます。
この結果の画面でOKボタンをクリックすると、ゴールシークで求めた値になってしまうので、キャンセルしてゴールシーク前の値に戻しましょう。
VLOOKUP関数は元の一覧表の一番上と一番下で確認
VLOOKUP関数の場合は、完全一致であれば元の表の一番上の値と、一番下の値だけ確認すれば、その間の値の動作は保証されます。
下の表の場合、1つのVLOOKUP関数を作成した時点で動作確認しましょう。参照しているのは「単価表」テーブルなので、この一番上である「碓井鉄鋼向け」と「菱谷産業」の2つだけチェックすれば、その間の「北道路ヒーター」「堀井産業」のチェックは不要です。
近似一致の場合はちょっと複雑かもしれません。その前にVLOOKUP関数の近似値一致の動作を知っておかなければいけないでしょう。
次の表だと、「1200」ならポイント数を見つけますが、「120」は#N/Aになり、ポイント数を見つけません。
作成したときは、1000円以下は0ポイントにするつもりで作成したのだと思いますが、元の一覧表範囲の検索する数値の一番上は考えうる限りの最小値、つまりこの場合は「0」を指定します。
この間違いをチェックするためには、「0」を入力すれば確認できます。また、最大値は30000となっているのですが、これは30000以上なら上限なしで探します。ただ最大値はIF関数のように「以上」なのか「より大きい」なのかわかりにくいので、想定通りになっているかを確認するには「30001」「30000」「29999」の3を入力し、それぞれのポイント数が想定通りか確認します。
また、ポイントが分かれる境界線がいくつもあるので、その全部の境界線をチェックする必要があるように思いますが、ちょうど真ん中の5000円のところで「5001」「5000」「4999」で想定通りになっているか確認します。
計算途中を確認する
関数の中に関数が入っているような複雑な計算式の場合、その数式の途中経過を確認したくなる時があります。
Microsoft365のExcelであれば、数式バー上で、確認したい数式をドラッグすれば、その結果が表示されます。
この機能は、数式タブのワークシート分析グループの中の数式の検証でもできます。こちらはかなり昔のExcelから搭載されている機能です。
数式の入っているセルを選択して、数式の検証機能を呼び出し、「検証」ボタンをクリックするたびに、ステップを追いながら計算途中の結果を表示します。
この２つの方法は、1つのセルごとにしか確認できないので、ある範囲に入力されている計算式の途中経過を、その範囲の分、確認するというのには不向きです。
その場合は、計算式のセルをクリックして数式バー上で結果を知りたい部分だけの計算式を範囲選択し、コピーします。コピーしたらESCキーを押して入力をキャンセルします。
その計算式が入っている行の、テーブルや一覧表範囲から少し離れた列に「=」を入力して、コピーした数式を貼り付け、Enterキーを押します。
必要に応じて、下にコピーします。
これで範囲に対して計算の途中経過を知ることができます。
まとめ
Excelの計算書は完成してしまうと、多くの人に、長い間使われるようになります。製作段階で間違えた場合で、100人が100日使って、それぞれで保存すれば、修正しなければいけない計算書は10000枚になります。
このような事態は実はExcelを使っているとよく起きることです。それが起きたときの被害が大きくなるのがExcelの欠点です。
この間違いは人の手によるもので、完全になくすことはできませんが、Excelの機能を使って、人間自身がチェックすることで被害を最小に食い止めることができます。
そのためには、エラーのメッセージを確認したり、エラーが出なくても間違っていないか両極端なデータで確認したり、境界でのデータで確認することがとても有効です。
Excel初心者の皆さんは、Excelの計算書の便利さを知ることに合わせて、Ｅｘｃｅｌ計算書の怖さも知っておいてほしいのです。それを防ぐためにはどうしたらいいかということを考えたときに、今回の知識が役に立つはずです。
