本記事はGW Advent Calendar6日目の記事です。

  先日Twitterで、ひょんな事からExcelの話で盛り上がりました。GW期間中だけどで特に用事がなかったこともあり、一度座談会をやってみようという話になり、急遽ZoomでExcel座談会をやることになりました。
  僕自身は、Excelを資料作成や設計書の作成、テストデータの作成などで利用しています。簡単な関数を利用したり、たまにマクロを書く程度のスキルレベルですが、紹介された機能として、知らないショートカットキーや関数も多く、大変ためになりました。まさか2時間もExcelだけで話していられるとは思いませんでした。

  今回はその中で学んだ、INDEX関数とMATCHを使った検索機能について紹介します。ググればあっさり出たりしますが、自分の頭を整理すると言う上でも改めて書きます。

VLOOKUP関数の欠点

例えばマスタデータを検索するような処理を関数で実装する場合、僕はVLOOKUP関数を使っていたのですが、VLOOKUP関数では、検索キーとして1番左側の列しか使えないという欠点があります。また検索を都度行うため、列が増えるほど処理効率が悪くなります。

  これらの問題を解消するために、INDEX関数とMATCH関数を組み合わせて使い、より検索を柔軟にし処理効率も改善する方法があります。

INDEX関数とMATCH関数

  INDEX関数は、指定された行と列が交差する位置にあるセルまたはセルの参照を返す関数です。

  例えば、このようなデータがあった場合、

=INDEX(サンプルシート!A2:E12,4,4)

  と書くことで、A2からE12を範囲として、4行目4列目の値を取得できます。

  MATCH関数は、指定した範囲内で探したい数値や文字列がどの行にあるかを返す関数です。例えば前述のサンプルデータであれば、

=MATCH("A1",サンプルシート!A2:A12,0)

と書くことで、「2」が返ってきます。「A1」がセルA2:A12のどこにあるかを検索し、A3(つまり、A2から数えて2行目)にあるため、「2」を返します。

このINDEX関数とMATACH関数を組み合わせると、例えば「商品名」から「商品コード」を取得する関数はこのように書けます。

=INDEX(サンプルシート!A2:E12,MATCH(A4,サンプルシート!B2:B12,0),1)

（「A4」は、検索したい商品名を入力するセルです)

MATCH(A4,サンプルシート!B2:B12,0)で、商品名が該当の列の何行目に当たるかを取得します。これが、INDEXで指定すべき行番号となります。後は、検索範囲と取得したい列番号を指定します。今回は「商品コード」を取得したいので、1を指定します。

結果、商品名から商品コードを取得できました。

Excelコンテストのススメ

  座談会をやってみて思ったのですが、「これはみんな知ってるだろう」という小技も、意外と知られていないことが多かったです。Excelはとても機能が多く、人によって利用する機能の範囲が違うから、こういうことが起こるのかもしれません。

  また、印象的だったのは、持ち込み課題に対して、「こういう機能はどうだろう」とか「こうすればできるのではないか」と、皆が親切に話していたことです。参加者の中に、Excelをあまり知らず「こういうことをしたいんだけど、どうやったらいいのかわからない」という課題を抱えている方がいました。当初は小技やよく使う関数をお互い紹介するという趣旨だったのですが、課題が持ち込まれると、皆でその課題をどうやって解決していくかという話で盛り上がりました。技術というのは課題を解決するためにあるのだということが、改めてよくわかりました。大変楽しかったです。

  プログラミングにはプログラミングコンテストというのがありますが、同じように課題を解決するためのExcelコンテストというのがあっても良いのかなと思いました。

  Excelコンテストは、以前に日経がやっていたようですが、最近は無いようです。草の根的にExcelコンテストをやっても面白いかもしれませんね。