今回は、「関数MATCH」と「交差演算子」を使って目的のデータを自動取得するテクニックを紹介していこう。ここでのポイントは、交差演算子、関数MATCH、関数INDIRECTについて、それぞれの役割をよく理解すること。それぞれの理解を深めれば、それだけデータ処理にかかわる知識も増えることになる。ぜひ、覚えておこう。

交差演算子を使ったデータのピックアップ

第18回の連載では「関数INDIRECT」、第19回の連載では「交差演算子」の使い方を紹介した。これらに「関数MATCH」を組み合わせると、さらにユニークな機能を実現できるようになる。ルックアップ系の関数より便利に活用できる場合もあるので、それぞれの基本と活用方法を把握しておくとよいだろう。

  • 交差演算子、関数MATCH、関数INDIRECTを組み合わせたテクニック

まずは、「交差演算子」を使ったデータの参照について簡単に解説しておこう。第19回の連載では、関数SUMや関数AVERAGEを使って「交わる部分」の合計や平均を算出する方法を紹介した。

これと似たような手法でセルのデータを参照することも可能だ。簡単な例を示しておこう。以下の図は、「D5:D8」と「C6:E6」のセル範囲を「半角スペース」(交差演算子)で区切って記述し、「=」(イコール)でセル参照を行った例だ。

  • 交差演算子を活用したセル参照

この場合、2つのセル範囲が重なる部分となる「D6セルの値」が参照される仕組みになっている。

  • ピックアップされたデータ

この仕組みを利用して、求めているデータを自動的にピックアップする機能を作成してみよう。今回は、47都道府県の「面積」と「住宅数」についてまとめた表を使って手順を紹介していく。

  • 「47都道府県のデータ」と「データ検索用のセル」

上に示した図は、総務省統計局の「社会・人口統計体系」(2018年度)をもとに算出した表となる。ただし、このままでは目的のデータを見つけにくいので、「都道府県」と「項目」を入力すると、該当するデータを自動取得してくれる検索機能を追加してみよう。

セル範囲を手軽に指定するための準備

「交差演算子」を活用するときは、「2つのセル範囲」を手軽に指定できるように工夫しておくのが基本だ。

まずは「面積」や「可住地面積」など、それぞれの「項目に対応するセル範囲」の指定を簡略化していこう。この手順は、前回の連載で紹介した手法と基本的に同じ。「各項目のデータ」が記録されているセル範囲に「名前」を定義してあげればよい。

たとえば、「面積」のデータのセル範囲に名前を定義するときは、以下のような手順で操作を進める。

  1. C7セルを選択する
  2. 「Ctrl」＋「Shift」＋「↓」キーを押して「データのセル範囲」を選択する
  3. 名前ボックスをクリックし、「面積」と入力する

  • セル範囲に名前を定義

同様の手順で、各列のデータに「名前」を定義していくと、そのデータが記録されているセル範囲を「名前」で指定できるようになる。

  • それぞれのセル範囲に名前を定義

これで「1つ目のセル範囲」の準備は完了。次は「都道府県に対応するセル範囲」について準備を進めていこう。こちらも「セル範囲に名前を定義して…」という手法を使えない訳ではないが、名前を定義する作業を47回も繰り返すのは少し面倒だ。そこで別の方法を探っていこう。

前回の連載で紹介したように、項目名が「6時～」や「7時～」といった「規則的な数字」であれば、数式で行番号を取得できるケースもある。しかし、今回の例は項目名が「都道府県の文字列」になっているので、そうもいかない。

このような場合は、関数MATCHを使って「探しているデータが何番目にあるか？」を調べてみるとよい。

■MATCH関数の書式
　=MATCH(検索データ, 検索範囲, [照合の型])

関数MATCHを使用するときは、3つの引数を指定する。第1引数には「探しているデータ」を指定する。第2引数には「検索するセル範囲」を指定する。そして、第3引数には「0」(ゼロ)を指定するのが基本となる(※)。

(※)第3引数に「0」(ゼロ)を指定すると、「完全一致」を条件にデータの検索が行われる。ここに「1」や「-1」といった値を指定することも可能だが、少し複雑になるので今回は詳しい説明を割愛する。

具体的な例で紹介していこう。現時点では、都道府県の検索条件(B4セル)に「山形県」が指定されている。これを関数MATCHの第1引数に指定して、「山形県が何番目にあるか？」を調べてみよう。第2引数には、都道府県の名称が入力されているセル範囲(B7:B53)、第3引数には「0」(ゼロ)を指定する。

  • 関数MATCHを使って「データが何番目にあるか？」を取得

すると、6という結果が表示された。「北海道」を1番目、「青森県」を2番目、…という具合にカウントしていくと、「山形県」は6番目になる。この数値が関数MATCHにより取得されているのを確認できるだろう。

  • 関数MATCHの結果

この数値をもとに「開始セル」のセル番号を作成する。今回の例では「北海道」より上に6つの行がある。よって、関数MATCHの値に6を足すと「行番号」に変換できる。さらに、先頭に"C"(C列)の文字を結合すると「セル番号」に変換できる。

  • 該当する「セル番号」に変換

同様の手順で「終了セル」のセル番号を求める。こちらは、先頭に結合する文字を"H"に変更すればよい。

  • 「終了セル」のセル番号を求める式の記述

これで「都道府県に対応するセル範囲」を手軽に指定するための準備が整った。あとは、これら「2つのセル範囲」を交差演算子で結ぶだけだ。

  • 「都道府県」の条件に対応するセル範囲

交差演算子を活用したデータの取得

前回の連載を読んだ方なら、以降の手順は容易に想像できるだろう。まずは、データを表示するセルを選択し、「=」(イコール)と入力する。

続いて、「項目に対応するセル範囲」を指定する。このセル範囲は「C4セルの文字」で指定できる。ただし、そのままでは文字列として扱われてしまうので、関数INDIRECTでセル範囲に変換する。

  • 「1つ目のセル範囲」の指定

続けて、交差演算子となる「半角スペース」を入力し、「都道府県に対応するセル範囲」を指定する。こちらは、G4セル(開始セル)とH4セル(終了セル)で表現できる。これらの間に「:」を挿入するように文字を結合し、関数INDIRECTでセル範囲に変換する。

  • 「2つ目のセル範囲」の指定

「Enter」キーを押すと、「山形県」の「可住地面積」のデータが自動取得されているのを確認できる。

  • 自動取得されたデータ

それぞれの検索条件を変更した例も紹介しておこう。たとえば、都道府県に「東京都」、項目に「住宅数」を指定すると、以下の図のような結果が得られる。「東京都」の「住宅数」のデータを正しく取得できているのを確認できるだろう。

  • 検索条件を変更した例(1)

今度は、都道府県に「高知県」、項目に「可住地割合」を指定した場合の例だ。このとき、以下の図のように0(ゼロ)という結果が表示されてしまうケースもある。

  • 検索条件を変更した例(2)

このような結果になるのは、データを表示するセルに「数値」(小数点以下0桁)の表示形式を指定していることが原因だ。「可住地割合」のデータは1以下の数値になるため、小数点以下を四捨五入すると、0または1という表示になってしまう。

この不具合を解消したいときは、「条件付き書式」を利用して表示形式を変更してあげるとよい。「データを表示するセル」を選択し、「条件付き書式」→「新しいルール」を選択する。続いて、以下の図のように設定して「1より小さい」を条件に指定する。

  • 条件付き書式の指定(1)

「書式」ボタンをクリックすると「セルの書式設定」が表示されるので、表示形式に「パーセンテージ」を指定する。

  • 条件付き書式の指定(2)

このように「条件付き書式」を指定すると、数値が1未満のときだけ「パーセンテージ」の表示形式で数値を表示できるようになる。

  • パーセント表示されたデータ

「桁数の多い数値」と「パーセント表示の数値」が混在しているときの対処法として、覚えておくと役に立つだろう。

そのほか、「項目」の条件(C4セル)をいちいち手入力するのが面倒な場合は、「データの入力規則」を使ってドロップダウン形式のリストを作成しておくとよい。すると、一覧から文字を選択するだけで「項目」を指定できるようになる。

  • 「データの入力規則」を使ったドロップダウン

今回の連載で紹介したように、交差演算子、関数MATCH、関数INDIRECTを組み合わせて、ルックアップ機能を実現することも可能である。データ数の多い表から「目的のデータ」を手軽に探し出すテクニックとして活用して頂ければ幸いだ。