Googleスプレッドシートでのデータ検索(indexとmatchとvlookupとqueryと)

(追記 2022-11-22)
2022年9月から、XLOOKUPが使えるようになったようで、もうこの記事の意味がなくなってしまったようです。新技術って素晴らしい！ｗ

↓こちらの記事参照
VLOOKUPはもうオワコン!? 新登場の最強関数XLOOKUPを使ってみた in Googleスプレッドシート

この記事は何？

上記の表において、「商品名」から「ID」を探したい ときにどうするか。
vlookupは「指定した範囲の一番左にキーがある」ことが前提なので、「キーの左側」は検索できないので、どうしようか。

について解決策を提示します。(他にもあったら教えて！)

• 解決策1: だったらキーを一番左にもってくればいいじゃないか作戦
• 解決策2: index と match を組み合わせる作戦
• 解決策3: vlookupだけで攻める作戦
• 解決策4: Query関数を使う作戦

前提知識

1) vlookup での検索ができること

の表があったときにIDをキーにして、商品名を検索できることを理解していること。

=vlookup(100, A:C, 2, false)

これで トマト が取得できる。

2) 配列{}を扱えること

↑黄色いところに

={A:C}

と書かれていますが、これにより { } 内に書かれた範囲が展開されることを理解していること。

解決策1: だったらキーを一番左にもってくればいいじゃないか作戦

この作戦では、元データを加工する必要があります。

例えば下記のようにA列を追加して、A2のセルに ={C2:C} が入っています。これによってC列をA列にコピー(正確には「参照」かな？)しています。

これなら

=vlookup("きゅうり", A:D, 2, false)

で 200 が取得できますね。

やってることはシンプルですが「元データを加工する」ところに抵抗があるかもしれませんね。
一度きりの使い捨てでいいから急ぎで処理したい、みたいなときはこれで十分かと思います。

解決策2: index と match を組み合わせる作戦

この方法の記事は index match 検索 などで検索するといっぱい出てきます。私のこの記事に書いてあることも同じことですが、自分がわかりやすいように整理してみました。

この作戦のキモとしては 行と列が交差するところ という探し方ができるところです。
これをやるには index と match を組み合わせます。

indexの説明

まず index について動きを見てみます。

下記の表があるときに

=index(A:C, 3, 1)

と書くと 200 が取得できます。この関数の意味としては

A:Cの範囲において、上から3行目、左から1行目の値を取得する

です。つまり 行数と列数がわかれば、それが交差するセルの値が取れる ことになります。
(注意するのは「A列が1列目」ではないということ。「範囲の中の一番左」が1列目です。B:Dが範囲なら1列目はB列)

• いまは「商品名からIDを検索したい」ので、「ほしい列はID列=A列」です。→ index の 第3引数の 1 は固定できます。
• 第3引数にある 3 は「商品名」の列において「きゅうり」がある列数ですが、これをどうやって特定しようか考えます。

matchの説明

ここでもう一つの match 関数が登場します。match ではこんなことができます。

=match("きゅうり", B:B, 0)

これで 3 が取得できます。

意味は下記になります。

"きゅうり"という文字列を、B:Bの範囲の中から探して、何個目に存在するかを取得する

第3引数の0は「完全一致」の0ですが、詳細は 公式リファレンスを参照ください。

indexとmatchを合体させる

最初に

=index(A:C, 3, 1)

と書いていた式の 3 を match("きゅうり", B:B, 0) に置き換えることで、

=index(A:C, match("きゅうり", B:B, 0), 1)

と書けます。これで きゅうり のIDである 200 が取得できます。
vlookupではできなかった「キーより左にある値を検索する」ができました！

解決策3: vlookupだけで攻める作戦

...でもね。index とか match を見て「ウッ」ってなる人もいるよなーって。正直、私もそっち派です。シンプルな vlookup で処理したくないですか？

vlookup についてですが、

=vlookup(100, A:C, 2, false)

というのは「100という値を、A:Cの範囲(の一番左の列)から探して、見つかった行にある2列目を取得する」です。

ポイントは 「A:Cの範囲」 です。つまり第二引数には「範囲」が指定できるんですよね。

「範囲」をつくる

ということは、表を変換(商品名を一番左に来るように)して、

↑これの「下の表」を「範囲」として指定できればいいですよね。

ここで「前提知識」のところにあった「配列」を応用するとこんなことができます。

黄色いところに下記のように書いてあります。

={B:B, A:A, C:C}

「範囲を , で区切ると横に並べてくれる」 のです。(ちなみに ; で区切ると縦に並べてくれます)
これにより、元々の「ABCの順で列が並んでる範囲」を「BACの順に並び替えた範囲」をつくることで、一番左に「商品名」が来るようにしました。

自分で作った範囲をvlookupに指定する

ポイントは 「ほしい範囲を自分で作って、その範囲をvlookupの中の範囲に設定する」 のです。

こうなります↓

=vlookup("きゅうり", {B:B, A:A, C:C}, 2, false)

これで 200 が取得できます。

...どうでしょうか。「範囲をつくる」というのがちょっとテクニカルな感じでしょうか。「メモリ上(あるいは頭の中)で範囲を作り変えて、それに対して何番目なのか」を把握しないとならないんですよね。

どこかに範囲を書き出してもいい

メモリ上ではなく「作り変えた範囲を一度別なところに書き出して、その範囲を指定する」ことも可能です。

上の図だと {B:B, A:A, C:C} を E:G に書き出しているので、

=vlookup("きゅうり", E:G, 2, false)

のようにすると表を視認できるので理解しやすくなります。(どこかに書き出すというコスト？はかかってデータ量が多いときに重くなる可能性があるかもしれない)

解決策4: Query関数を使う作戦

1～3とはちょっと質が違うかもしれませんが、「ほしい情報を検索する」という意味ではこれでも解決できます。

=query(A:C, "select A where B ='きゅうり'", 1)

(Query関数、とっても強力なので、知らなかった人は是非調べてみてください！めちゃめちゃ楽ちんですよ！)

さいごに

今回は4つの解決策を挙げましたが、どうでしょうか。

スプレッドシートでちょっと複雑なことをやろうとすると、「書いた本人ならわかるけど、将来このスプレッドシートをメンテナンスする人にもわかるようにしておきたいから、なるべく難しいことはしたくない」という気持ちと「いやいや、これくらいできるようになってよ」という葛藤が生まれます。