リスト内の一致する値に基づいて、ある範囲から値を引き出す必要があった経験はありますか？たとえば、製品の ID に基づいて製品の価格を動的に取得したいとします。
データ セットに Product ID (製品 ID) が含まれている場合は、数式を使用して価格を引き出すことができます。
この数式を列の数式として設定すると、各行 (および新しく追加された行) には製品データ セットから一致する価格が表示されます。
この数式へのさまざまなアプローチ
一致する LOOKUP 値に基づいて、ある範囲からデータを引き出すには次の 3 つの方法があります。
- VLOOKUP
- INDEX(MATCH())
- INDEX(COLLECT())
これらの各数式の使用方法をレビューし、各アプローチのメリットとデメリットについて説明します。
VLOOKUP
VLOOKUP 関数は値を検索し、同じ行の異なる (指定した) 列にある値を返します。VLOOKUP 関数の形式は、以下のとおりです。
=VLOOKUP([LOOKUP 値], [検索対象のデータ セット], [データセットの列番号], [完全に一致するニーズに基づく真偽])
VLOOKUP を使用して上記の例の価格を引き出すには、数式は次のようになります。
=VLOOKUP([Associated Product ID]@row, {Product Data | Product}, 4, false)
{Product Data | Product} のシート間参照範囲は次のようになります。
また、数式は次のように「Price (価格)」列の値を返します。
ヒント: 検索対象のデータ セットに一致するデータが見つからない場合は、IFERROR 関数で数式を囲んで解決します。この例では、数式は次のようになります。
=IFERROR(VLOOKUP([Associated Product ID]@row, {Product Data | Product}, 4, false), "No Match Found")
メリット:
- シンプル/最も早い数式。
デメリット:
- 検索対象データ セットの最初の列に LOOKUP 値が必要。
- LOOKUP 値の列の左にある値を取得できない。
- 新しい列が追加された場合、LOOKUP 値と取り込む列の間の列が削除された場合、または列が並べ替えられた場合に改行される。
INDEX(MATCH())
INDEX(MATCH()) の数式は、ある範囲を検索し、指定された条件に一致する値を収集します。INDEX(MATCH()) 関数の形式は、以下のとおりです。
=INDEX([返される値の範囲],MATCH([検索値],[検索する値の範囲],[検索タイプに応じて 0、1、または -1]))
INDEX(MATCH()) を使用して上記の例の価格を引き出すには、数式は次のようになります。
=IFERROR(INDEX({Product Data | Price}, MATCH([Associated Product ID]@row, {Product Data | Product ID}, 0)), "No Match Found")
{Product Data | Price} のシート間参照範囲は次のようになります。
{Product Data | Product ID} のシート間参照範囲は次のようになります。
前述のとおり、INDEX(MATCH()) 関数は IFERROR で囲まれ、行に一致する製品 ID が見つからない場合に「No Match Found (一致なし)」と表示されるため、数式は次のように「Price (価格)」列の値を返します。
メリット:
- 列の順序変更や、使用しない列の削除が可能。
- 検索値範囲の左または右にある列から値を引き出すことができる。
- 大規模データ セットに高速で対応。
- INDEX(MATCH(),MATCH()) は、列と行の動的な一致に使用できる。
- 通常、参照セルの合計数が少なく、シート間参照で参照されるセルの合計上限数である 10 万個に到達しない。
デメリット:
- 参照データが別のシートに存在する場合、複数のシート間参照が必要である。
- 単一の一致条件に制限されている。
INDEX(COLLECT())
INDEX(COLLECT()) の数式は、ある範囲を検索し、指定された 1 つ以上の条件に一致する値を収集します。INDEX(COLLECT()) 関数の形式は、以下のとおりです。
=INDEX(COLLECT([返される値の範囲],[条件付きの範囲],[条件],[条件付きの範囲 2],[条件], など),[返される行インデックスに 1])
INDEX(COLLECT()) を使用して上記の例の価格を引き出すには、数式は次のようになります。
=IFERROR(INDEX(COLLECT({Product Data | Price}, {Product Data | Product ID}, [Associated Product ID]@row), 1), "No Match Found")
{Product Data | Price} のシート間参照範囲は次のようになります。
{Product Data | Product ID} のシート間参照範囲は次のようになります。
前述のとおり、INDEX(COLLECT()) 関数は IFERROR で囲まれ、行に一致する製品 ID が見つからない場合に「No Match Found (一致なし)」と表示されるため、数式は次のように「Price (価格)」列の値を返します。
メリット:
- 列の順序変更や、使用しない列の削除が可能。
- 検索値範囲の左または右にある列から値を引き出すことができる。
- 通常 VLOOKUP よりも高速だが、INDEX/MATCH よりも遅い場合がある。
- COLLECT 関数内で複数の条件を使用して、複数の列で一致させたり、より複雑な条件を作成したりできる。
- 常に最初の一致を提供するのではなく、数式の最後の ", 1" を置き換えることにより、2 番目、3 番目などの一致を提供できる。
デメリット:
- 参照データが別のシートに存在する場合、複数のシート間参照が必要である。
- 特に複数の条件を使用している場合は、INDEX/MATCH を使用するよりも時間がかかる場合がある。
さらにサポートが必要な場合
さらに多くのサポート用リソースに加えて、リアル タイムで操作を練習できる関数集や、よく使われる数式と高度な数式の例を含む 100 個以上の数式を記載した数式ハンドブック テンプレートをご活用ください。
Smartsheet の他のお客様によるこの関数の使用例を検索したり、Smartsheet コミュニティで特定の使用例について質問したりすることができます。
コミュニティに質問する