ExcelのSVERWEIS関数は、検索範囲の最初の列を検索する多目的関数です。
Excel SVERWEIS – 効果的に使用し、効率的に作業する
Excel の SVERWEIS は、大規模な表から個々の値を抽出する際に最もよく使用される機能の一つです。この機能を信頼性高く使用するためには、正しい数式だけでなく、データの構造とエラーの原因を正確に把握することが重要です。
- マトリックスは左から開始する必要があります。SVERWEIS は、選択した領域の最初の列のみを検索します。たとえば、顧客番号が D 列にある場合、検索領域も D 列から開始する必要があります。つまり、「A:F」ではなく「D:F」です。最初の列は常に比較基準として使用されます。これを無視すると、誤った結果や結果が表示されないことがあります。
- 表オブジェクトを使用して作業を安定させる：データを「表」としてフォーマットすると（ショートカットキー：Ctrl + T）、Excel はその後の拡張に自動的に反応します。新しい行を追加しても、SVERWEIS 式は有効のままです。式 =SVERWEIS(A2;Tabelle1;3;FALSCH) は、更新されたデータに正しくアクセスします。これは、リストが拡大する場合に最適です。
- FALSCH ではなく WAHR – 検索動作を意図的に制御： 正確な一致がない場合、不正確な結果になるおそれがあります。式の第 4 桁（いわゆる範囲参照）は、ほとんどの場合、FALSCH に設定する必要があります。そうすることで、Excel は正確な一致を検証します。WAHR は、ソートされたデータにのみ適しています。しかし、日常業務では、ソートが確実に実行されていることはほとんどありません。
- データ形式を確認し統一する：テキスト形式の数値と数値形式の数値は、見た目は同じですが、動作が異なります。リストに値があるにもかかわらず、VLOOKUPが一致しない場合は、ほぼ必ず形式の衝突が原因です。=ISTTEXT(A2)または=ISTZAHL(A2)を使用すると、問題の箇所を迅速に特定できます。=GLÄTTEN() や =WERT() などのツールを使用すると、自動的に整理されます。
- 絶対参照と相対参照を適切に使用する：数式をコピーすると、セル参照も一緒にコピーされます。行列の指定では、これは致命的な結果になる可能性があります。そのため、式を確実に上から下へ、または表全体にコピーするために、$ 付き（例：$A$2:$D$100）の絶対セル参照を使用してください。そうすることで、範囲が「ずれる」ことを防げます。
- エラーをエレガントにキャッチ： 連番の文書やダッシュボードに「NV」と表示されるのは誰にとっても不快です。VLOOKUPとIFERRORを組み合わせて、代替の出力を生成します：=IFERROR(VLOOKUP(A2;Table1;3;FALSE); 「Not found」)
- これは見た目の問題ではなく、機能的な問題です。たとえば、エクスポートや自動化されたデータ処理の場合などです。
- SVERWEIS の制限を理解する: 列が移動したり、表の構造が変更されたりすると、SVERWEIS は誤った結果を返します。また、右から左への逆検索もできません。このような場合は、XVERWEIS または INDEX/VERGLEICH を使用することをお勧めします。
XVERWEIS、INDEX など – より制御性の高い代替手段
Microsoft 365 および Excel 2021 以降では、SVERWEIS の多くの弱点を克服したより堅牢なバリエーションである XVERWEIS が利用可能です。INDEX/VERGLEICH の組み合わせも柔軟性が高く、特に Excel のバージョンや要件が変更される場合に有効です。
- XVERWEIS：逆方向検索を直接実行：最大の違い：XVERWEISは、検索語句の左側の列からの返却も可能です。例：=XVERWEIS(B2;Tabelle1[Name];Tabelle1[Kundennummer];「Kein Treffer」)。これにより、構造が最適でないテーブルも検索可能です。SVERWEIS では、この検索は失敗します。
- 列インデックスが不要になり、エラーが減少：XVERWEIS では、検索範囲と返す範囲の 2 つの具体的なセル範囲を指定します。SVERWEIS のように、列の番号（3 列目、4 列目など）を覚えておく必要はありませんこれにより、エラーが減って見やすくなるよ。
- エラーメッセージを意図的に制御：XVERWEIS では、4 番目の引数「wenn_nicht_gefunden」が WENNFEHLER の代わりになるよ。読みやすさ、入力ミスを減らし、直接アクセス – この関数は、空のセルや誤った値に対してよりスリムに反応します。
- 検索方向と比較動作を制御：XVERWEIS には 2 つの追加パラメータがあります。「比較モード」（0、-1、1）と「検索モード」（1 は上から、-1 は下から）。これにより、例えば価格帯や重複するエントリをターゲットに検索することができます。これは SVERWEIS では不可能な機能です。
- XVERWEIS をモバイルデバイスとウェブで: Microsoft Excel Mobile では XVERWEIS は基本的に機能しますが、複雑な動的式は制限されています。Excel Online では、セル形式が正しい限り、SVERWEIS と XVERWEIS は安定して動作します。モバイルでは、一部の制御要素が欠落しているため、シンプルな構造を使用することをおすすめします。
- INDEX/VERGLEICH: 多機能でバージョンに依存しない: INDEX は値を取得し、VERGLEICH は一致する位置を検索します。式例: =INDEX(D2:D100;VERGLEICH(A2;B2:B100;0))
SVERWEISとは異なり、この組み合わせは列の順序を変更しても機能します。複雑な計算や、XVERWEISが利用できない場合（例：Excel 2016）に最適です。
大規模なデータセットでのパフォーマンス：数千件のデータを扱う場合、INDEX/VERGLEICHまたはXVERWEISはSVERWEISよりも迅速に結果を得られます。どちらも、特に動的なダッシュボードやサードパーティシステムからのエクスポートにおいて、大量のデータをより効率的に処理します。
実践例 – SVERWEIS のステップバイステップ
多くの人は毎日 Excel を使用していますが、SVERWEIS では誤解が生じやすいものです。特に、数式を日常的に使用していない場合です。以下の例では、シンプルな構造と少ない手順で機能する SVERWEIS を作成する方法を示します。目的は、顧客リストから対応する電話番号を自動的に取得することです。
- 元の表を構造化します：3 列の表を作成します。「顧客番号」、「名前」、「電話番号」です。各行には 1 つのデータセットのみが含まれます。重要：検索する値（この例では顧客番号）を含む列は、一番左に配置します。SVERWEIS は、検索範囲の最初の列のみと比較します。例：「1002 Langer 0157 9876543」
- クエリ用の 2 番目の表を設定します。右側、または別の表に、「顧客番号」と「電話番号」の 2 列からなる小さなリストを作成します。「顧客番号」列に、検索する値（たとえば「1002」）を入力します。2 列目は、最初は空のままにしておき、後で自動的に入力されます。
- SVERWEIS を正しく設定します：結果を表示するセル（例：セル F2）をクリックします。次に、次の数式を入力します：=SVERWEIS(E2;A2:C100;3;FALSCH)
- この式の意味は、セル E2（ここでは「1002」）の値を、範囲 A2 から C100 の最初の列で検索します。その後、同じ行の 3 列目（C）の値を返します。末尾の「FALSCH」は、正確な一致のみを受け入れるようにします。
- 式をテストして結果を確認します。Enter キーを押すと、Excel はセル F2 に「0157 9876543」と自動的に表示します。これは、検索した顧客の電話番号です。E2 の値（たとえば「1003」）を変更すると、F2 の結果も自動的に変更されます。式は入力値に動的に適応します。
- よくあるエラーの原因を回避する：検索領域で数値が実際に数値としてフォーマットされていることを確認してください。多くの場合、「NV」エラーの原因は、E2 の値が数値としてフォーマットされているが、元の表ではテキストとしてフォーマットされていることです。これは視覚的にはほとんど目立ちませんが、技術的な影響があります。「=ISTTEXT(A2)」を使用して確認できます。必要に応じて、「=WERT()」を使用してセルの内容を修正してください。
- 複数の行を使用する場合、セル参照を固定する： 式を下にドラッグして他の顧客を検索する場合は、ドル記号で範囲を固定します：=SVERWEIS(E2;$A$2:$C$100;3;FALSCH)
これにより、式をコピーしたり下にドラッグしたりしても、検索範囲は変更されません。
エラーメッセージの捕捉、出力の改善
「NV」の代わりに「顧客が見つかりません」のようなわかりやすいメッセージを表示するには、式を次のように追加します：=WENNFEHLER(SVERWEIS(E2;$A$2:$C$100;3;FALSCH); 「顧客が見つかりません」)
これにより、表が整理され、ユーザーは結果がない場合にすぐに気づくことができます。
