Calc VLOOKUP vs XLOOKUP: 適切な検索式を選択する

ブックをLibreOffice 24.8以降で開く場合は、ほとんどの新規検索にXLOOKUPを使用してください。XLOOKUPはデフォルトで完全一致検索を行い、検索範囲と結果範囲を個別に指定できます。古いバージョンのCalcとの互換性が必要な場合、または検索値がコンパクトテーブルの最初の列にある場合は、VLOOKUPを使用してください。VLOOKUPを使用する場合は、完全一致検索のために4番目の引数に0またはFALSEを指定してください。

これらの関数は、製品コードなどのキーを検索し、別のセルから関連情報を返すという、同じ基本的な問題を解決します。重要な違いは、各数式がどのように範囲を定義するか、オプションが省略された場合に何を想定するか、そしてファイルが古いソフトウェアでも動作する必要があるかどうかです。

実際的な違いは何ですか？

VLOOKUP関数は、長方形の表の最初の列から下方向に検索し、その右側の番号付き列から値を返します。XLOOKUP関数は検索範囲と戻り値の範囲を分離するため、戻り値はキーのどちら側にも配置できます。また、XLOOKUP関数は一致しない場合にカスタム値を指定でき、逆順で検索することも可能です。

例えば、A2:A5に製品IDが、B2:B5に価格がそれぞれ入っているとします。セルF2には検索対象のIDが入っています。Calcで完全一致検索を行うVLOOKUP関数は次のようになります。

=VLOOKUP(F2;A2:B5;2;0)

同等のXLOOKUP関数は次のとおりです。

=XLOOKUP(F2;A2:A5;B2:B5;"Not found")

どちらの式も、一致する価格を返します。Calc では、ロケール設定に応じて関数引数の間にセミコロンが表示されるのが一般的です。お使いの環境で数式にカンマが使用されている場合は、Calc が想定する区切り文字を使用してください。例では、1 つのセルの結果と 1 次元の検索範囲を使用しています。

XLOOKUP関数はどのような場合に使うべきでしょうか？

受信者がLibreOffice 24.8以降を使用していることがわかっている場合は、新しいワークブックを作成する際にXLOOKUP関数を選択してください。公式のXLOOKUPヘルプページによると、この関数はLibreOffice 24.8で利用可能になりました。デフォルトの一致モードは完全一致なので、完全一致キーが欠落していても、類似の値が暗黙的に返されることはありません。

関数のパターンは です=XLOOKUP(search_criterion;search_array;result_array;[if_not_found];[match_mode];[search_mode])。最初の 3 つの引数は、キー、検索対象の 1 次元範囲、および戻り値の範囲を指定します。 などの 4 番目の引数は、"Not found"一致するものがない場合に通常表示される #N/A の結果を置き換えます。

左隣の列の値を返す

VLOOKUP関数は、最初のテーブル列の左側の値を返すことはできません。一方、XLOOKUP関数は、検索範囲と結果範囲が独立しているため、左側の値を返すことができます。例えば、IDがC2:C100、部署名がA2:A100にある場合は、次のように使用します。

=XLOOKUP(F2;C2:C100;A2:A100;"ID not found")

このレイアウトでは、脆弱な列インデックスも回避できます。VLOOKUPテーブルに列が挿入された場合、ハードコードされたインデックスが別のフィールドを指してしまう可能性があります。XLOOKUPの明示的な結果範囲により、目的の戻り値列を簡単に確認できます。

最も最近一致したエントリを返します

重複するキーが存在し、最後の一致結果を取得したい場合、XLOOKUP は検索モードで逆検索をサポートします。垂直範囲の場合、最後の引数は-1最後から最初へ検索します。

=XLOOKUP(F2;A2:A100;B2:B100;"Not found";0;-1)

これは、最後に完全に一致する結果を返します。行の順序に明確な意味がある場合（例えば、最新のレコードが最後に表示される場合など）にのみ使用してください。重複をエラーとして扱う必要がある場合は、暗黙的に1つの出現を選択するのではなく、データをクリーンアップまたは検証してください。

VLOOKUP関数が依然として最適な選択肢となるのはどのような場合か？

ファイルをXLOOKUPが含まれていない古いバージョンのLibreOfficeで開く必要がある場合、または目的のアプリケーションがXLOOKUPを認識しない可能性がある場合は、VLOOKUPを保持してください。Calcの公式ドキュメントには、VLOOKUPのテーブル引数とインデックス引数、およびオプションのsorted-range引数について説明されています。公式のスプレッドシート関数リファレンスを参照してください。

完全一致の場合、パターンは です=VLOOKUP(key;table;column_index;0)。インデックスは、選択したテーブルの左端から列数を数えます。1 は最初の列、2 は 2 番目の列を意味します。検索キーは最初の列に存在し、返されるフィールドはその右側に存在する必要があります。

4番目の引数は重要です。これを省略するかTRUEに設定すると、VLOOKUP関数は近似一致を使用し、最初の列が昇順でソートされていることを前提とします。この動作は、各行が下限値を示す税率区分などの範囲には便利です。ただし、ID、名前、およびソートされていないリストには適していません。これらの場合は、0またはFALSEを指定してください。

一致時とエラー時の挙動はどのように異なるのか？

• 完全一致: XLOOKUP はデフォルトで完全一致を使用します。VLOOKUP で完全一致を確実に検索するには、4 番目の引数を 0 または FALSE に設定する必要があります。
• キーが不足しています: XLOOKUP は、4 番目の引数に選択したメッセージまたは値を返すことができます。VLOOKUP は通常 #N/A を返します。カスタム メッセージが必要な場合は、IFNA で囲んでください。
• 近似一致：どちらも範囲ベースのマッチングをサポートできますが、データと選択されたモードが意図したルールに適合している必要があります。必要に応じてデータをソートし、境界ケースを確認してください。
• 検索方向： XLOOKUP関数は、search-mode引数を使用することで下から検索できます。VLOOKUP関数は通常、上から下へ検索する場合、最初に一致した行を返します。

VLOOKUP でカスタムのキー欠落時の応答を提供するには、 を使用します=IFNA(VLOOKUP(F2;A2:B5;2;0);"Not found")。これにより、完全一致設定が表示されたままになり、使用できないエラーのみが処理されます。無効な範囲参照など、無関係な問題も隠したい場合を除き、式を広範なエラー ハンドラーで囲むことは避けてください。

ワークブックを共有する前に確認すべきことは何ですか？

LibreOfficeのヘルプには、XLOOKUP関数はバージョン24.8以降で利用可能であると記載されています。また、XLOOKUP関数はOpenDocumentバージョン1.3のOpenFormula標準には含まれておらず、相互運用性のためにMicrosoftの関数名前空間が指定されていることも明記されています。つまり、ファイル形式だけでは、古いバージョンやサードパーティ製の表計算ソフトでこの関数が正しく計算されるとは限りません。複数のソフトウェアが混在する環境では、最も古いアプリケーションでワークブックをテストするか、レイアウトが適合する完全一致のVLOOKUP関数など、確立された数式を使用してください。

また、配列数式に関する注意事項として、XLOOKUP関数の結果配列がセル範囲である場合、関数を配列数式として入力する必要があります。上記の簡単な例では、対応する値が1つだけ返されます。複数のセルを返す数式が必要な場合は、バージョン固有のヘルプを参照し、対象アプリケーションが配列結果をどのように処理するかを確認してください。

結果をどのように検証できますか？

1. 存在するキーをテストし、返された値をそのキーに対応する元の行と比較します。
2. 存在しないキーをテストします。XLOOKUP が選択したフォールバックを表示するか、VLOOKUP が期待どおりの #N/A または IFNA メッセージを返すことを確認してください。
3. VLOOKUP関数で完全一致検索を行う場合は、最後の引数が0またはFALSEであることを確認してください。近似一致検索を意図的に使用する場合は、ソート順と、境界値のすぐ上、境界値上、境界値のすぐ下の値を確認してください。
4. 左側に返される列を指定するXLOOKUP関数、または逆検索の場合は、既知の結果を持つキーをテストし、重複が存在する場合は、どの結果を返すかを確認します。
5. 受信者が使用している最も古いバージョンのCalcまたは表計算ソフトで、ワークブックを保存して再度開いてください。お使いのコンピューターで正しい結果が得られたとしても、古いバージョンでも同じ機能がサポートされているとは限りません。

ほとんどの新しいCalcシートでは、XLOOKUP関数の方がより明確な範囲指定と安全な完全一致のデフォルト値を提供します。下位互換性が重要な場合、または最初の列を右端に配置するレイアウトが既に適切な場合は、VLOOKUP関数を使用し、一致モードを明示的に指定してください。