DXable

DXable について製品カテゴリー

DXable ホーム

Smartsheetでデータを検索するための３つの数式（formula）

2022年9月12日

リスト内の一致する値に基づいて、範囲から値を引き出さなければならなかったことはありませんか。たとえば、製品のIDに基づいて製品の価格を動的に引き出したい場合があります。

vlookup_blog_1

データセットにProduct IDが含まれている場合は、数式を使用して価格を引き出すことができます。

vlookup_blog_2

この数式セットを列に適用すると、各行（および新しく追加された行）に、製品データセットから一致する価格が表示されます。

この数式へのさまざまなアプローチ

一致するルックアップ値に基づいて範囲からデータを取得するために使用できる方法は３つあります。

VLOOKUP
INDEX(MATCH())
INDEX(COLLECT())

これらの各数式の使用方法を確認し、各アプローチの長所と短所について説明します。

VLOOKUP

VLOOKUP数式は値を検索し同じ行の別の（指定された）列から値を返します。VLOOKUP数式の形式は次の通りです。

=VLOOKUP([ルックアップ値], [検索対象のデータセット], [データセット内の列番号], [完全一致させるかどうかをFalseまたはTrueで指定])

上記の例でVLOOKUPを使用して価格を取得するには、数式は次のようになります。

=VLOOKUP([Associated Product ID]@row, {Product Data | Product}, 4, false)

{Product Data | Product} シート間の参照範囲は次のようになります。

vlookup_blog_3

この数式は、次のようにPrice列の値を返します。

vlookup_blog_4

ヒント：数式をIFERROR数式でラップして、検索対象のデータセットに一致が見つからない場合を解決します。この例では、数式は次のようになります。

=IFERROR(VLOOKUP([Associated Product ID]@row, {Product Data | Product}, 4, false), “No Match Found”)

vlookup_blog_5

長所

シンプルで最速な数式である

短所

検索対象のデータセットの最初の列にルックアップ値が必要
ルックアップ値のある列の左側に値を取得できない
ルックアップ値と引き出される列の間に新しい列が追加または列が削除された場合、または列が並べ替えられた場合に中断する

vlookup_blog_6

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} シート間の参照範囲は次のようになります。

vlookup_blog_7

{Product Data | Product ID} シート間の参照範囲は次のようになります。

vlookup_blog_8

上記のように、INDEX(MATCH()) 関数をIFERRORでラップして、行に一致するProduct IDが見つからない場合に「No Match Found」と表示するようにしたので、この数式は次のように各Price列の値を返します。

vlookup_blog_9

長所

列の順序を変更したり、未使用の列を壊さずに削除したりできる
検索範囲の左または右の列から値を取得できる
大きなデータセットの場合は高速
INDEX(MATCH(),MATCH()) は、列と行のダイナミックなマッチに使用できる
通常、参照されるセルの総数はより少なくなり、「シート間参照で参照されるセルの総数は10万まで」という制約を避けるのに役立つ

短所

参照データが別のシートに存在する場合、複数のシート間参照が必要
単一の一致基準に限定

INDEX(COLLECT())

INDEX(COLLECT())数式は範囲を検索し、指定された１つ以上の条件に一致する値を収集します。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} シート間の参照範囲は次のようになります。

vlookup_blog_10

{Product Data | Product ID} シート間の参照範囲は次のようになります。

vlookup_blog_11

上記のように、INDEX(COLLECT()) 関数をIFERRORでラップして、行に一致するProduct IDが見つからない場合に「No Match Found」と表示するようにしたので、数式は次のように各Price列の値を返します。

vlookup_blog_12

長所

列の順序を変更したり、未使用の列を壊さずに削除したりできる
検索範囲の左または右の列から値を取得できる
通常はVLOOKUPよりも高速だが、INDEX/MATCHよりも遅くなる場合がある
COLLECT数式内で複数の基準を使用して、複数の列で一致させたり、より複雑な基準を作成したりできる
常に最初の一致を提供する代わりに、数式の末尾にある「, 1」を置き換えることで、２番目、３番目などの一致を提供できる

短所

参照データが別のシートに存在する場合は、複数のシート間参照が必要
特に複数の基準を使う場合、INDEX/MATCHを使うより遅くなる可能性がある

もう少し情報が必要ですか？

Formula Handbookテンプレートを使うと、より多くのサポートリソースを検索し、リアルタイムで操作を練習できる全ての関数の用語集や、一般的に使われる高度な数式の例を含む100以上の数式を表示できます。

他のSmartsheetのお客様がこの機能をどう使っているか例を見つけるか、Smartsheetオンラインコミュニティーで特定のユースケースについて質問してください。

コミュニティーに聞く

この記事はSmartsheet社のウェブサイトで公開されているものをDigital Stacksが日本語に訳したものです。無断複製を禁じます。原文はこちらです。