内容
このレッスンでは、関数が使用されている状況にすばやく対処する方法について説明します。 VPR (VLOOKUP) は Excel 2013、2010、2007、および 2003 で動作したくない、および一般的なエラーを特定して修正し、制限を克服する方法 VPR.
以前のいくつかの記事で、関数のさまざまな側面を探りました。 VPR エクセルで。 それらを注意深く読んだ場合は、この分野の専門家になっているはずです。 ただし、多くの Excel 専門家がそう信じているのには理由がないわけではありません。 VPR より複雑な機能の XNUMX つ。 多くの問題やエラーの原因となる多くの制限と機能があります。
この記事では、エラーの簡単な説明を見つけることができます ＃で (#N/A)、 #NAME？ (#NAME?) と ＃値！ (#VALUE!) 関数を操作するときに表示される VPR、およびそれらに対処するためのテクニックと方法。 最も一般的なケースと最も明白な理由から始めます。 VPR は機能しないため、記事に記載されている順序で例を検討することをお勧めします。
Excel の VLOOKUP 関数の #N/A エラーを修正
式では VPR エラーメッセージ ＃で (#N/A) の意味 利用できない (データなし) – Excel が探している値を見つけられない場合に表示されます。 これは、いくつかの理由で発生する可能性があります。
1. 目的の値のつづりが間違っている
まずはこちらの商品をチェックしてみてはいかがでしょうか！ タイプミスは、数千行からなる非常に大量のデータを操作する場合、または探している値が数式に書き込まれている場合によく発生します。
2. VLOOKUP で近似一致を検索すると #N/A エラーが発生する
近似一致検索条件、つまり引数で数式を使用する場合 範囲ルックアップ (range_lookup) が TRUE であるか指定されていない場合、数式でエラーが報告される場合があります ＃N / A XNUMXつの場合：
- 検索する値が、検索対象の配列の最小値より小さいです。
- 検索列は昇順ではソートされません。
3. VLOOKUP で完全一致を検索すると #N/A エラーが発生する
完全一致、つまり引数を探している場合 範囲ルックアップ (range_lookup) が FALSE で、正確な値が見つからなかった場合、数式もエラーを報告します ＃N / A. 関数を使用して完全一致と近似一致を検索する方法の詳細 VPR.
4. 検索列が左端にない
おそらくご存じのとおり、最も重要な制限の XNUMX つは VPR 左を向くことができないため、テーブルのルックアップ列は一番左にある必要があります。 実際には、これを忘れがちで、式が機能せず、エラーが発生します。 ＃N / A.
決定： 検索列が一番左になるようにデータ構造を変更できない場合は、関数の組み合わせを使用できます INDEX (INDEX) と もっと露出 (MATCH) のより柔軟な代替手段として VPR.
5.数値はテキストとしてフォーマットされます
エラーの別の原因 ＃N / A との式で VPR メイン テーブルまたはルックアップ テーブルのテキスト形式の数値です。
これは通常、外部データベースから情報をインポートする場合、または数値の前にアポストロフィを入力して先行ゼロを保持する場合に発生します。
テキスト形式の数値の最も明白な記号は、次の図に示されています。
さらに、数値は次の形式で格納できます。 （全般的）。 この場合、目立つ機能が XNUMX つだけあります。数値はセルの左端に配置されますが、デフォルトでは右端に配置されます。
決定： 値が XNUMX つの場合は、エラー アイコンをクリックして、 数値に変換 (数値に変換) をコンテキスト メニューから選択します。
数字が多い場合は、数字を選択し、選択範囲を右クリックしてください。 表示されるコンテキスト メニューで、 セルの書式設定 (セルの書式設定) > タブ 数 (数値) > 形式 数 （数値）を押して OK.
6. 先頭または末尾にスペースがある
これは、エラーの最も明白でない理由です。 ＃N / A 機能している VPR、特に大きなテーブルで作業している場合、ほとんどのデータが画面外にある場合、これらの余分なスペースを視覚的に確認するのは難しいためです。
解決策 1: メイン テーブル (VLOOKUP 関数がある場所) の余分なスペース
メイン テーブルに余分なスペースがある場合は、引数を囲むことで数式が正しく機能することを確認できます。 参照値 (lookup_value) 関数に TRIM （トリム）：
=VLOOKUP(TRIM($F2),$A$2:$C$10,3,FALSE)
=ВПР(СЖПРОБЕЛЫ($F2);$A$2:$C$10;3;ЛОЖЬ)
解決策 2: ルックアップ テーブル (ルックアップ列) 内の余分なスペース
検索列に余分なスペースがある場合 - 簡単な方法 ＃N / A との式で VPR 避けることはできません。 それ以外の VPR 関数の組み合わせで配列数式を使用できます INDEX （索引）、 もっと露出 (マッチ) → TRIM （トリム）：
=INDEX($C$2:$C$10,MATCH(TRUE,TRIM($A$2:$A$10)=TRIM($F$2),0))
=ИНДЕКС($C$2:$C$10;ПОИСКПОЗ(ИСТИНА;СЖПРОБЕЛЫ($A$2:$A$10)=СЖПРОБЕЛЫ($F$2);0))
これは配列数式なので、押すのを忘れないでください Ctrl + Shift + Enter 通常の代わりに 入力します式を正しく入力する。
エラー #VALUE! VLOOKUP を使用した式で
ほとんどの場合、Microsoft Excel はエラーを報告します。 ＃値！ (#VALUE!) 数式で使用される値がデータ型と一致しない場合。 について VPRの場合、通常、エラーには XNUMX つの理由があります。 ＃値！.
1. 探している値が 255 文字を超えています
注意: 関数 VPR 255 文字を超える値を検索することはできません。 探している値がこの制限を超えると、エラー メッセージが表示されます。 ＃値！.
決定： たくさんの機能を使う インデックス+マッチ (インデックス + 一致)。 以下は、このタスクに適した数式です。
=INDEX(C2:C7,MATCH(TRUE,INDEX(B2:B7=F$2,0),0))
=ИНДЕКС(C2:C7;ПОИСКПОЗ(ИСТИНА;ИНДЕКС(B2:B7=F$2;0);0))
2. 検索ブックへのフル パスが指定されていません
別のワークブックからデータを取得する場合は、そのファイルへのフル パスを指定する必要があります。 具体的には、ワークブック名 (拡張子を含む) を角かっこ [ ] で囲み、その後にシート名、感嘆符を付ける必要があります。 ブックまたはシート名にスペースが含まれている場合は、このすべての構造をアポストロフィで囲む必要があります。
関数の完全な構造は次のとおりです。 VPR 別の本で検索するには:
=VLOOKUP(lookup_value,'[workbook name]sheet name'!table_array, col_index_num,FALSE)
=ВПР(искомое_значение;'[имя_книги]имя_листа'!таблица;номер_столбца;ЛОЖЬ)
実際の式は次のようになります。
=VLOOKUP($A$2,'[New Prices.xls]Sheet1'!$B:$D,3,FALSE)
=ВПР($A$2;'[New Prices.xls]Sheet1'!$B:$D;3;ЛОЖЬ)
この数式は、セルの値を検索します A2 列に B シートに Sheet1 ワークブックで 新しい価格 列から対応する値を抽出します D.
テーブル パスの一部が省略されている場合、関数は VPR 動作せず、エラーが報告されます ＃値！ (ルックアップ テーブルを含むワークブックが現在開いている場合でも)。
機能の詳細については VPR別の Excel ファイルを参照する場合は、レッスンを参照してください: VLOOKUP を使用して別のブックを検索する。
3. 引数 Column_num が 1 未満
よりも小さい値を入力する状況を想像するのは困難です。 1値を抽出する列を示します。 この引数の値がネストされた別の Excel 関数によって計算される場合は可能ですが、 VPR.
したがって、その引数が発生した場合 col_index_num (列番号) 未満 1function VPR エラーも報告します ＃値！.
引数の場合 col_index_num (column_number) は、指定された配列の列数よりも大きく、 VPR エラーを報告します #REF！ (#SSYL!)。
エラー #NAME? VLOOKUPで
最も単純なケースは間違いです #NAME？ (#NAME?) – エラーのある関数名を誤って書いた場合に表示されます。
解決策は明らかです。スペルを確認してください。
VLOOKUP が機能しない (制限、警告、および決定事項)
かなり複雑な構文に加えて、 VPR 他のどの Excel 関数よりも多くの制限があります。 これらの制限のため、一見単純に見える式は VPR 多くの場合、予期しない結果につながります。 以下に、いくつかの一般的なシナリオの解決策を示します。 VPR 間違っている。
1. VLOOKUP では大文字と小文字が区別されません
演算 VPR は大文字と小文字を区別せず、小文字と大文字を同じものとして受け入れます。 したがって、大文字と小文字だけが異なる複数の要素がテーブルにある場合、VLOOKUP 関数は、大文字と小文字に関係なく、見つかった最初の要素を返します。
決定： 垂直検索を実行できる別の Excel 関数 (LOOKUP、SUMPRODUCT、INDEX、および MATCH) と組み合わせて使用します。 EXACT大文字と小文字を区別する A。 詳細については、レッスンから学ぶことができます - Excel で VLOOKUP の大文字と小文字を区別する 4 つの方法。
2. VLOOKUP は最初に見つかった値を返します
すでにご存知の通り、 VPR 最初に見つかった一致に対応する特定の列から値を返します。 ただし、必要な値の 2 番目、3 番目、4 番目、またはその他の繰り返しを抽出することができます。 重複する値をすべて抽出する必要がある場合は、関数の組み合わせが必要になります INDEX （索引）、 少なくとも (小) と LINE （行）。
3. テーブルに列が追加または削除された
残念ながら、数式 VPR 新しい列がルックアップ テーブルに追加または削除されるたびに機能しなくなります。 これは、構文が VPR では、検索の全範囲とデータ抽出用の特定の列番号を指定する必要があります。 当然、列を削除したり、新しい列を挿入したりすると、指定された範囲と列番号の両方が変更されます。
決定： そして再び関数は助けを急いでいます INDEX (INDEX) と もっと露出 （マッチ）。 式では インデックス+マッチ 検索列と取得列を別々に定義すると、関連するすべての検索式を更新することを心配することなく、必要な数の列を削除または挿入できます。
4. 数式をコピーするとセル参照が文字化けする
この見出しは問題の本質を余すところなく説明していますよね？
決定： 常にセルの絶対参照を使用します (シンボル $) レコードの範囲、たとえば $ A $ 2：$ C $ 100 or $ A：$ C. 数式バーでは、リンクの種類をすばやく切り替えることができます。 F4.
VLOOKUP – 関数 IFERROR および ISERROR の操作
エラーメッセージでユーザーを怖がらせたくない場合 ＃N / A, ＃値！ or #NAME？、空のセルまたは独自のメッセージを表示できます。 配置することでこれを行うことができます VPR 関数に IFエラー (IFERROR) Excel 2013、2010、および 2007 で、または一連の関数を使用する IF+ISエラー (IF+ISERROR) 以前のバージョンでは。
VLOOKUP: IFERROR 関数の操作
関数構文 IFエラー (IFERROR) は単純で、それ自体が物語っています。
IFERROR(value,value_if_error)
ЕСЛИОШИБКА(значение;значение_если_ошибка)
つまり、最初の引数にはエラーをチェックする値を挿入し、XNUMX 番目の引数にはエラーが見つかった場合に返す値を指定します。
たとえば、次の数式は、探している値が見つからない場合、空のセルを返します。
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"")
関数の標準エラーメッセージではなく、独自のメッセージを表示したい場合 VPR、次のように引用符で囲みます。
=IFERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE),"Ничего не найдено. Попробуйте еще раз!")
=ЕСЛИОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ);"Ничего не найдено. Попробуйте еще раз!")
VLOOKUP: ISERROR 関数の操作
機能以来 IFエラー 以前のバージョンで作業する場合は、組み合わせを使用する必要があります。 IF (IF) および エオシブカ (ISERROR) このように:
=IF(ISERROR(VLOOKUP формула),"Ваше сообщение при ошибке",VLOOKUP формула)
=ЕСЛИ(ЕОШИБКА(ВПР формула);"Ваше сообщение при ошибке";ВПР формула)
たとえば、式 IF+ISERROR+VLOOKUP、式と同様 IFERROR+VLOOKUP上に示しました：
=IF(ISERROR(VLOOKUP($F$2,$B$2:$C$10,2,FALSE)),"",VLOOKUP($F$2,$B$2:$C$10,2,FALSE))
=ЕСЛИ(ЕОШИБКА(ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ));"";ВПР($F$2;$B$2:$C$10;2;ЛОЖЬ))
それが今日のすべてです。 この短いチュートリアルが、考えられるすべての間違いに対処するのに役立つことを願っています。 VPR 数式が正しく機能するようにします。