前回、企業が入社テストとして出題した「Excel問題」のその5をやってみました。
今回は、セルのエラー処理について捜査してみたいと思います。
それでは一緒にやっていきましょう。
問題6を確認
前回と同じような表ですが、関数を使うとエラーが発生するようです。
まずは「セルC5」に「VLOOKUP関数」を作成して、名称「きゃ」の「値1」が表示されるようにしてみましょう。
VLOOKUP関数で値1を表示する
前回も操作しました「VLOOKUP関数」ですね。
簡単におさらいをしてみましょう。
「検索値」の引数には、「セルB5」を設定します。
このB列の「名称」を基に、次の引数である「範囲」に設定した「セルG5からセルH30」から、名称が持つ「値1」を探ります。
「範囲(G5:H30)」の表で、「値1」は列番号が「2」なので、これが「列番号」の引数に設定されます。
「検索方法」は完全一致なので、「0」または「FALSE」を設定します。
今回は、「値1」だけなので、オートフィルは下方向だけとなります。
「範囲」の行番号だけに絶対参照をつけておけば問題ないでしょう。
範囲から名称「きゃ」が見つかり、「値1」から141を取得できました。
では、オートフィルで下方向に34行目までコピーしてみましょう。
「セルC11」の名称「じゃ」でセルにエラーが発生しましたね。
これは、検索範囲の名称に探していた「じゃ」が1つも見つからなかった場合に発生します。
同じように、範囲に名称が見つからなかったセルには、すべてエラーが表示されています。
検索値が範囲の表には1つもなかった、という場合はもちろんあるでしょう。
ただセルにエラーが表示されたままというのはよろしくないですよね。
そこで、「VLOOKUP関数」が何かしらのエラーを返しても、エラーが発生した場合の処理を書いておきます。
こういったエラーの対応に使う関数が「IFERROR関数」となります。
エラー対応の「IFERROR関数」
では、実際に関数がエラーを返した時の処理を「IFERROR関数」で処理してみたいと思います。
「セルC5」の関数を修正します。
「IFERROR関数」の引数である「値」には、先ほど設定していた「VLOOKUP関数」の内容をそのまま設定します。
関数を入れ子にする時の入力方法
上のように元々記述してあった「VLOOKUP関数」の外側に「IFERROR関数」を新たに記述する、つまり関数を入れ子にする時には、以下のように入力するのが速いでしょう。
数式の「=」と「VLOOKUP」の「V」の間に、今回外側に設置したい関数名の最初の文字を入力します。
今回は、「IFERROR」なので、「i」(大文字、小文字どらでも可)を入力します。
関数リストが表示されるので、一覧から「IFERROR」をダブルクリックします。
数式に「IFERROR」が挿入されます。
この「IFERROR」の中の任意の場所にカーソルを置き、「fx」(関数ボタン)をクリックします。
次の引数の「エラーの場合の値」には、「VLOOKUP関数」がエラーを返してきた時に、このセルに何を表示するかを設定します。
ここでは、「0」を表示するようにしています。
関数を修正できたら、オートフィルで下方向に34行目までコピーしてみましょう。
同じように、「値2」の列にも関数を設定してみましょう。
以下の関数を入力し、オートフィルで下方向へコピーしてみてください。
IFERROR(VLOOKUP(B5,J$5:K$19,2,0),0)
範囲の行に絶対参照をつけるのを忘れないようにしましょう。
XLOOKUP関数を使うと・・
「VLOOKUP関数」を使っていると、発生するエラーの多くが「検索値が見つからない」になると思います。
その場合のエラー表示は「#N/A」です。
このエラーを「IFERROR関数」で判定し、もしエラーの場合に、セルに違う数値や言葉を表示できるのがいわゆる「エラー処理」となります。
実は「LOOKUP関数」の進化系で比較的新しい「XLOOKUP関数」は、この関数自体に検索値が見つからなかった場合の対処方法ができる「引数」があるのです。
では今回の操作を「XLOOKUP関数」を使って試してみましょう。
少し「VLOOKUP関数」とは引数の設定に違いがあります。
「XLOOKUP関数」の引数には「一致しなかった場合」があるので、ここに先ほど「IFERROR関数」で設定したように、「0」を設定します。
つまり「IFERROR関数」で「VLOOKUP関数」を入れ子にしなくても、「XLOOKUP関数」であれば、この関数単独でエラー対応までできるようになったというわけです。
問題6まとめ
今回も新たに2つの関数が出てきました。
それではおさらいをしておきましょう。
- 「VLOOKUP関数」で発生するエラーを「IFERROR関数」などのエラー処理関数で対応できるか
- 「VLOOKUP関数」のエラー対応として「XLOOKUP関数」を使って対応できるか
これまで「列番号」引数で調査対象を1列しか指定できなかった「VLOOKUP関数」とは違い、「XLOOKUP関数」は表の複数列のデータを取得できるようになりました。
ただ、今回は「VLOOKUP関数」と「IFERROR関数」の代わりにエラー対応ができるところだけを確認したので、詳細な使用方法は割愛しています。
次回は問題7に進んでいきます。