Excel関数がうまく機能しない、できない時に確認する点について

Excelの関数がなぜか動かない、機能しないとき

言うまでもなくExcelの関数は、とてもメジャーな機能で使用頻度がかなり高いと思います。

今回はその関数がうまく「動かない」、「機能しない」、「反応しない」などの事態が発生した場合に、弊社で確認するべきポイントを列挙しました。
(ただし下記には基本的に優先順位はあまり関係ありません。)

みなさんが(たぶん今)陥っている状況に「該当するかしないか」を一項目ずつ確認してもらえればと思います。
なお、本件は回答そのものでなく、原因を探すための説明も含んでいる内容もあります。また別途こちら(VLOOKUP関数が思うように使えない場合)でも説明している部分と重なる部分もあります。

VLOOKUP関数が思うように使えない場合
VLOOKUPでうまくいかないとき、解決が多岐に渡ることがあります。そのため、できるだけ多くの事例と説明画像をたくさん入れました。この記事の作成の頃から私も成長しました(笑)。更なる知見を詰め込んで内容をおおきく刷新しました。
ご参考までに・・少なくとも弊社の思い出せる限り関数に関しては、「Excelのバグだから動かない」は、一度も経験がありません。
ということは、関数にエラーがあるとき・・それは何らかのミスである(あった)ということになります。

エラー(トラブル)対処の基本は「問題(原因)の切り分け」

Excelや関数に限った話ではありませんが、Excelを使っていて何らかのトラブルが発生した場合に、原因を絞っていく過程が必要になります。
そういう意味において、もし下記に挙げている内容でも解決できない場合でも、少しずつ的を絞って原因を追っていくことをお勧めします。

なお、ここで挙げている内容は基本的にどの関数でも当てはまるだろう点です。それぞれの関数独自のエラーの場合はここでは示していない点に注意してください。

エラーには大きく2つある

すでに別記事(VLOOKUP関数が思うように使えない場合)でも書いていますが、結果にエラー表示が出た場合、それが持つ意味は2つあります。

  1. 式が間違えている(構文エラー、文法エラー)
  2. 式は正しい、しかし結果がおかしい(エラーではないエラー)

気を付けるべきは後者です。これはエラーと表示されていても、そのエラーは単に「式は間違ってないけど、結果が見つかりませんでしたよ」みたいな意味の場合です。
ここで「構文エラー」と判断してしまうと、解決の道が遠のきます。

(例)式は正しい、しかし結果がおかしいエラー

例えば、下記の「#N/A」はVLOOKUP関数の式が間違っているのではなく、「(商品番号:10は該当する商品名が存在しないため、)値が見つからないエラーが表示されている」、という点に気を付けます。
#N/Aは文法エラーではない

ただし、下記の場合も併せて意識した方がよいです。
これはSUM関数の場合ですが、SUMの場合はエラーにならない(エラー表示されない)パターンです。まずは通常の問題ない場合の画像です。
SUM関数を普通に使う

次に上記の内容の一部を「数値を文字に変更」しても、ご覧のようにエラーと出ずに数値部分のみ算出します。
SUM関数の引数に文字を含める

これはSUM関数では引数に含まれた内容が文字の場合、エラーと扱わずに無視(0として扱う?)する仕様のようです。ここでのポイントは先ほど説明したVLOOKUPと挙動が変わる(エラーとすら表示されない)という点です。

もしExcelの関数でうまくいってない場合、上記「2」のいわゆるエラーでないエラーかどうかも確認することをお勧めします。

「式は正しい、しかし結果がおかしいエラー」の対処について

式が間違ってない、正しい場合、まずは「絶対に答えがある」データで試してみます
これでうまくいけば、式ではなく「データ(式以外)」に問題があると判断できますので求めている結果にグッと近づけます。
絶対にある回答で試す

特にシートやブックをまたぐ関数を使っている場合は、最終的に1つのシート内でできるかまで試してみる必要があります(けっこうメンドイですが)。

関数の対象範囲がずれてないか(オートフィル、行列の追加削除に注意)

例外もありますが、通常、関数は多かれ少なかれ「引数」を必要とします。引数とは「計算対象となるセルの場所(アドレス)」です。
この場所が正しいか確認してみます。
引数が間違ってないか確認する

特にExcelの便利な機能の1つに式自体をコピーできる「オートフィル」という機能があります。この場合「絶対参照と相対参照」も一緒に指定しないと意図しない結果「式(セルの位置)がずれる」になる場合があります。

下記は数式ですが参照の部分は同じ考え方です。
数式の入っているセルをオートフィルを使ってコピーする

本来は「合計×5%」の値が表示されるはずですが・・・
オートフィルするとずれる

下記のようにセルの場所がずれてしまいます。これは相対参照になっているのが原因です。
オートフィルして対象が右に1個ずれた

なお、対象範囲となっているデータ側の行列の追加、削除にも気を付けます。こちらでも行や列の追加と削除を繰り返すと式がずれてしまいます。
VLOOKUP関数の引数で6行目まで設定されている

この表から対象範囲となっている行を1行分削除します。
指定範囲で1行を削除

下記のように設定済みのVLOOKUP関数の対象範囲も対応して行が短くなります。そのため新たに追加しようと6行目にデータを入力しても対象にならないので注意です。
再度VLOOKUP関数を確認すると行範囲が変わる

関数の結果ではなく、「式そのもの」がセル上に表示される場合

この場合は、セルの書式設定が怪しいです。セルには色々な設定をかけることができます。そのため、関数を入れようとしたセルに「文字列」の設定がかかっていると、Excelは関数とは認識してくれず、単なる文字という扱いをします。
下記はCOUNTIFの場合ですが、他の関数でも同様です。
関数がそのまま表示される

既に入力した関数が入っている状態で、セルを標準にしても変わらないため、F2キーで一度カーソルを置くなどして戻します。
カーソルを置いてEnterキー標準にする

正しい値を入れているが結果が異なる、正しくない

上記に関連しますが、セルには見た目の値実際の値を持つことができます。これは「入力した値を自在に表示できる」ような仕組みです。これ自体は非常に便利で通常はあまり意識しないで使う人も多いと思いますが、関数などを使うときには少し注意が必要となります。
表示形式に戸惑わないように気を付けます

下記のように表示形式を変えるとその意味が分かります。
表示形式に戸惑わないように気を付けます
表示形式を見ると見つからないのは値が異なっていることが原因です

関数で必要なのは実際の値の方です。間違いの原因を見た目だけに絞ってしまうと解決までに時間がかかるので普段から意識しておくとよいと思います。

ちなみに、この点を理解していると下記の画像でSUM関数の正しくない?結果の理由がわかるはずかも??
SUM関数がおかしい?
クイズ!Excel問題集より

値を入力しなおしても計算結果が反映(更新)されない

Excelには再計算というものがあります。値を入力しなおすと入力済みの関数たちが動き、自動的(勝手)に計算し直してくれる機能です。通常はオンになっていますが、これが何らかの理由によりオフになっていることがあります。
これをオンに直すと自動的に再計算されるようになります。

「ファイル」から「オプション」を選択します。
計算結果が更新されない場合、再計算も疑う

下記は再計算がオン(有効)の状態です。ここのチェックが入っていると、値を入れ直した時に自動で再計算されません(手動となる)ので注意です。
ここの手動がオンになっていると再計算されない

なお、(誰かが作ったファイルで)オンにする前に、なぜオフになっているのか理由も確認した方が良いです。例えば「=NOW()」のような式が入っていて、「(オフ時に既に表示されている)日時を更新されたくない」みたいなことがあるとやっかいなためです(こんなときは関数を使わずベタ入力してほしいものですね)。

バージョンやExcelでの初期設定に含まれてない関数

Excelはバージョンアップによって関数も影響を受けることがあります。例えば「IFERROR」、「SUMIFS」という関数がありますが、この関数は2003以前では使えません(Exceではとりわけ2003から2007へのバージョンアップは大きく変わりました)。
また、一部の関数「EDATE」、「WORKDAY」などは初期ではExcelでは使えないためアドインという形でそれらの関数を有効にする必要があります。

他のシートやブックのデータを使っている場合

この場合のエラーでよくあるのが、そのまんまですが「他のシートやブックが見つからない」です。シート連結して使用するよりもブック(連結的)の場合の方がエラーとなる可能性が高いです。
いずれにしても、まずはシート名の変更と関数の動き方を知っておく必要があります。
SUM関数の部分を別シートに移動する場合

下記は別のシート「店舗シート」の合計を「合計シートにSUM関数」で設定したところですが、ポイントはその次の画像です。
SUM関数の部分を別シートに設定したところ

元の対象となっていたシート名を変更(「店舗」→「店舗1月~3月」)すると、関数の引数も自動的に変更されます。
関数の引数になっているシート名を変更する

通常はこれで問題ないのですが、例えば後から「店舗」シートを追加したときに、このシートは先ほど設定したSUM関数の対象にはなっていない点に気を付けます。
あとから関数で使っていた名前と同じシートを追加する

上記を踏まえてブックについて言うと若干状況が異なります。
下記のように「シートをブック単位で分けた」時に注意が必要です。
シート単位でブックを分けたところ

関数の引数も大丈夫で、シート名を変更しても対応されます、ここでは・・・
参照先のSUM関数を設定
参照元のシート名を参照先が開いた状態で変更

問題はブック(合計.xlsx)を閉じたときのシート名の変更です。今度は「合計.xls」を閉じてシート名を変更します。
参照元のシート名を変更

この状態で「合計.xlsx」を開くと・・
参照元のシート名が拾えなくなった

このように「#REF(参照先が見つからない)」エラーが表示されます。これは参照先(合計.xlsx)から見ると、シート名の変更は管理外という言い方もできると思います。
極端な言い方をすれば、「店舗.xlsx」を削除すると完全にお手上げ状態ですし、保存先のフォルダを移動するだけでも同様ですね。
従い、ブック間での関数のやりとりでは、保存先にも気を配る必要があるということです。例えばメールで添付したファイルなど(受け取り側)は、同じ保存構成(パス)になっていないことが多いため、参照エラーが起こりがちです。
他者が作ったファイルや自身が作ったファイルでも昔に作成したものなどだと一見忘れがちになるので注意が必要です。

関数をネスト(入れ子)にしている場合

関数の利用が慣れてくると、一時的な作業列などを使わずに「関数の中に関数を入れる」ような使い方も出てきます。こういった場合のエラーは、内側(最初に実行される)関数から式や結果が正しいか確認します。
下記はVLOOKUP関数によくある「値が見つからなかった場合にエラー表示させない([該当なし]と表示する)」という例ですが、これはIFERROR関数を使ってVLOOKUPの結果により表示文字を変えるようにするためネストして使っています(下の方)。
ネストさせると関数が複雑になる

ネストしたエラーだけでなく、関数単体でも役に立つのが「関数ボックス」です。
ボックス上に現在の関数の状態が表示されるため、解決のヒントになることが多いためです。
関数ボックスを起動させる

ネストした場合はどの関数の状態が表示されいているのかを確認します。
関数ボックスでIFERROR関数の状態を表示
関数ボックスでVLOOKUP関数の状態を表示

このようにネストされた関数を1つずつ見ていくことで「どこの関数が悪さしているのか」を掴むことができます。
また関数ボックスを使わないでも(使ってもわからない時は)、むしろ作業列を復活させ、ネストされた式を分解して関数ごとにセルに入れていくことで確認しやすくします。

しかし・・・それでも・・・わからないとき・・・どうする!?

本ページでは、できるだけ解決できそうな点を説明してまいりました。しかしながら、ここで挙げた内容で解決方法は全てではありません。また冒頭でも言った通り、関数一つ一つの点についての説明ではないため、使用している関数独自の問題の場合は、さらに掘り下げて確認する必要も出てきます。
ともすれば、果てしなく解決できるまで調べ続けるということに対して、「果たしてそこまでする必要があるのか」、「別のアプローチはないのか」という点も考慮する必要があるかもしれません。

「まさに技術的な解決が目的」というサポート系、ヘルプスタッフ系の業務であれば、とことん時間を割いても良いかもしれません。しかし、おそらく多くの方が「これができないから先に進めない」という本来の作業の目的とは異なる状況だと思います。
この「できない」に時間をどれくらいかけられるかによって以降に与えるスケジュール、進捗等の影響も考慮する必要があります。
ただ、作業として「今後の作業でも登場するために解決は必要」というなら、勉強会や講習会などを行い理解を深める必要があるかもしれません。
Excelの関数について言えば、ちょっとした規則性や暗黙の了解的な分も理解には必要ですし、これらを知っていると関数を応用的に使えるオマケもつきます。
スキル向上や勉強等を除いた業務(案件)や作業、あるいは企業や人件費として見た場合、時間の割き方は重要です。このようなページを説明している弊社から見れば、「やむを得ず」というのは残念な話ですが、調べ物をするとそれなりに時間がかかる(稼働コストが発生する)のも事実です。
「どうしても解決できない・・」その時はこういった点を含めて検討してみてはいかがでしょうか。

タイトルとURLをコピーしました