[Excel]献立作成ソフトを作ろう　第9回　レシピ作成シートの作成(6)

食品情報検索結果の取得

方法はどうであれ、V33セルに成分検索欄の最終入力行数が取得できました。ここからは、前回学んだ検索の応用です。成分検索欄の最終入力行数から、そこに入力されている文字列をV34に取得し、検索キーワードとして使用する準備をします。V34セルに「=INDEX(G:G , V33)」と入力してEnterキーを押します。セルの値が「”にんじん　根　むき”」となれば成功です

続いて、V35に「="*"&SUBSTITUTE(SUBSTITUTE(V34," “,"*"),"　","*")&"*"」と入力してEnterキーを押します。この数式は、前回MATCH関数の第1引数に指定した数式と同じですね。もちろん、この値の使い方も一緒です。

R35セルに「=MATCH($V$35,成分表!D:D,0)」と入力してEnterキーを押します。セルの値が「621」となれば成功です。なお、成分検索欄(G10：G29セル)の最終入力行はG12なので、R35セルとS12セルは同じ値「621」となります。現時点で、なぜそうなるかが理解できない場合は、前回(第8回)を復習してみると良いでしょう。

さて、ここからが問題です。「成分表」から、キーワードに一致する複数の食品情報を取得するには、どうしたら良いでしょうか。なお、R35セルの数式「=MATCH($V$35,成分表!D:D,0)」を下のセルにコピーするだけでは不十分です。試しにやってみましょう。R35セルをR54セルまで書式なしコピーして下さい。

はい、R35：R54セルは全ての値が「621」となりました。R54セルの数式を見てみると、「=MATCH($V$35,成分表!D:D,0)」となっています。R35セルの数式と全く同じですね。同じ数式からは同じ結果が返ってくるのは当然といえば当然です(RAND関数という突っ込みは無しで)。という訳で、この数式を改良します。改良には、MATCH関数が持つ2つの性質を理解する必要があります。

(1) MATCH関数は、第1引数(キーワード)に一致する値を第2引数(検索範囲：セル参照又は配列)の中から探し、一致するものがある場合は、その要素のインデックスを返す。

(2) (1)について、第1引数に一致する値が複数存在する場合は、最初に一致した要素のインデックス(＝最も小さいインデックス)を返す。

上記(1) の性質については、前回も解説したかと思いますので、詳細はそちらを参照してください。上記(2)については、例えば「=MATCH( “*", 成分表!D:D , 0 )」という数式を実行すると分かり易いと思います。この数式は、「成分表」のD列の中から、値が入力されている全てのセルと一致します。しかし、この数式からは「3」という1つの値しか得られません。これは、「成分表」のD列を1行目(D1セル)から下に向かって2行目(D2セル)、3行目(D3セル)と順に検索した結果、3行目で値が入力されたセルを発見したので、MATCH関数は検索を終了し、「3」という結果を返しました。非常に重要なので繰り返しますが、この「3」は3行目だから「3」ではなく、検索開始位置(D1セル)を1番目として、3番目で一致したから「3」なのです。ここの理解が不十分だと、次の説明で混乱してしまうので、その違いをしっかり理解しておきましょう。

MATCH関数は上記(2)の性質により、第2引数(検索範囲)の中に第1引数(キーワード)に一致する値が複数あっても、最初に一致した値、言い換えると1番目に一致した値のインデックスしか取得してくれません。ここで発想を転換すると、第2引数(検索範囲)が1番目に一致した値を含まなければ、2番目に一致する値のインデックスを取得できます。同じように、第2引数(検索範囲)が2個目の値も含まなければ、3番目に一致する値のインデックスを取得できます。これを、「成分表」と検索キーワード「“おおむぎ”」を使用して説明します。まず、これまでに学んだ検索方法を用いて、「成分表」のD列から「”おおむぎ”」を検索します。数式は「=MATCH(“*おおむぎ*" , 成分表!D:D , 0 )」ですね。この数式の戻り値は「13」です。

次に、「”おおむぎ”」と2番目に一致する値を検索します。先程は13行目(成分表!D13)に「”*おおむぎ*”」と一致するセルがあったため、これを検索範囲から除外します。つまり、「成分表!D14:D1048576」の中から「”*おおむぎ*”」を検索すると、14行目(成分表!D14)の「”おおむぎ　押麦”」が一致します。数式にすると「=MATCH(“*おおむぎ*" , 成分表!D14:D1048576 , 0 )」となり、戻り値は「1」です。キーワードは14行目の値と一致しましたが、戻り値は「14」ではありません。検索範囲は「成分表!D14:D1048576」なので、「成分表!D14」は1番目の要素です。何度か出てきていますが、1048576という数値は、Excel2007以降のワークシートの最大行数です。

3個目以降のインデックスは、この繰り返しです。つまり、3個目のインデックスは「成分表!D15:D1048576」の中から検索し、4個目のインデックスは「成分表!D16:D1048576」の中から検索します。この考え方を実現するための関数がOFFSET関数です。

では早速、先程の考え方に従い、キーワードの2番目に一致するセルの行番号を取得します。これまでの数式と比較して少し長めですが、やっていることは単純です。R36セルに「=MATCH($V$35 , OFFSET(成分表!$D$1, R35 , 0 , 1048576 – R35 , 1) , 0) + R35」と入力してEnterキーを押してください。R36セルの値が「622」となれば成功です。R36セルをR54セルまで書式なしコピーをして下さい。

数式「=MATCH($V$35 , OFFSET(成分表!$D$1, R35 , 0 , 1048576 – R35 , 1) , 0) + R35」は、MATCH関数の第2引数(検索範囲)にOFFSET関数を指定しています。MATCH関数は既に何度も使用しているので、OFFSET関数がどんな動きをするのかを理解できれば、この数式も理解できるでしょう。OFFSET関数の構文を下記に示しますが、この関数は少し難しいので、枠内の説明は読み飛ばしても大丈夫です。

OFFSET関数は、セル参照を動的に取得する関数です。そういう意味では、INDEX関数と少し似ていますが、OFFSET関数はINDEX関数よりも柔軟です。言い換えると、INDEX関数で出来ることはOFFSET関数でも出来ますが、OFFSET関数で出来ることがINDEX関数で出来るとは限りません。それはさておき、OFFSET関数について説明します。この関数は“オフセット”と読みます。オフセットの定義は色々ありましたが、最も分かりやすいと感じたものが株式会社日立ソリューションズ様のホームページにありました(サービス終了のためリンク解除2020年03月31日)。

オフセット(offset)とは、基準となるある点からの相対的な位置のことである。offsetは英語で「差し引き計算する」という意味で、そこから必要なデータの位置を基準点からの差(距離)で表した値をオフセットと呼ぶようになった。( 株式会社日立ソリューションズより引用)

オフセットの意味から考えると、INDEX関数もオフセット値で要素の位置を指定している気もしますが、OFFSET関数も同じことができます。第1引数(基準)で指定したセル参照を基準(原点)として、第2引数(行数)と第3引数(列数)で指定した数値分だけ、セルアドレスを移動したセル参照を戻り値として返します。引数と戻り値の関係は、具体的には下表のようになります。

OFFSET関数はINDEX関数と違って、戻り値のセル参照の範囲を指定できます。OFFSET関数の第4引数(高さ)と第5引数(幅)は、戻り値となるセル参照の高さ(行数)と幅(列数)を指定する引数で、省略した場合は第1引数のセル参照の高さ(行数)と幅(列数)がそのまま使用されます。引数と戻り値の関係は、具体的には下表のようになります。

OFFSET関数がセル参照を柔軟に指定出来る印象は持てたでしょうか？では改めて、数式「=MATCH($V$35 , OFFSET(成分表!$D$1, R35 , 0 , 1048576 – R35 , 1) , 0) + R35」の計算過程を見てみましょう。ここで、念のため数式内で3回も登場しているR35セルの中身を確認しておきます。セル参照「R35」内は数式「=MATCH($V$35,成分表!D:D,0)」で、現在の第1引数(V35)は「“*にんじん*根*むき*”」で、戻り値は「621」です。つまり、「成分表」D列の621行目に、キーワード「“*にんじん*根*むき*”」に一致する最初の値の行数「621」が入っています。また、「1048576」はExcel2007以降のワークシートの最大行数です。

この計算過程の5行目にある「OFFSET(成分表!$D$1, 621 , 0 , 1047955 , 1)」を、もう少し細かく見てみましょう。第1引数(基準)は「成分表」のD1セルですね。第2引数(行数)は「621」なので、D1セルの位置から621行下のセル、つまりD622セルを指しています。第3引数(列数)は0なので無視しても良いですが、機械的な表現をすれば、D622セルの位置から0列右のセルを指していると言えます。第4引数(高さ)は1047955となっていますが、目的はOFFSET関数の戻り値(セル参照)を、ワークシートの最大行数(1048576行目)まで伸ばすことです。なので、第4引数の算出方法が「最大行数(1048576) – 第2引数と同じ値(621)」となります。第5引数(幅)は「1」ですが、これはMATCH関数で検索したい範囲がD列のみ(1列)なので「1」です。これは第1引数で指定したセル参照の列数と同じため、省略しても構いません。OFFSET関数より取得したセル参照は「成分表!$D$622:$D$1048576」ですが、これが6行目の数式でMATCH関数の第2引数(検索範囲)に渡されます。ここで、「成分表!D622」の値は「” （にんじん類）　にんじん　根　皮むき　ゆで”」であるため、MATCH関数に指定したキーワード「”*にんじん*根*むき*”」と一致します。MATCH関数の戻り値は、行番号ではなく要素のインデックスなので「1」となります。これをワークシートの行番号に変換するため、「621」を加算しています。これにより、キーワード「”*にんじん*根*むき*”」と2番目に一致するセルの行番号が取得できました。3番目以降のセルは、これと同じ考え方で取得可能です。

キーワードに一致する複数の行番号が取得できたら、あとは前回の内容の復習です。I35セルに「=IFERROR( INDEX(成分表!C:C, $R35, 1) , “")」を入力してEnterキーを押して下さい。I35セルの値が「617」となれば成功です。I35セルをI54セルまで書式なしコピーして下さい。

次に、J35セルに「=IFERROR( INDEX(成分表!D:D, $R35, 1) , “")」を入力してEnterキーを押してください。J35セルの値が「（にんじん類）　にんじん　根　皮むき　生」となれば成功です。J35セルをJ54セルまで書式なしコピーしてください。

最後に、J32セルに「="「" & V34 & “」 = “& COUNTIF( 成分表!$D:$D , V35 ) & “件"」と入力してEnterキーを押してください。J32セルの値が「"「にんじん　根　むき」＝6件"」となれば成功です。これで、検索結果の複数表示はほぼ完成です。

COUNTIF関数の構文を下記に示します。

COUNTIF関数は、第1引数で指定した範囲(セル参照など)の中から、第2引数で指定した条件に一致する値の数を返します。この条件には、MATCH関数と同じようにワイルドカード(*)が使用できます。これにより、検索キーワードに一致する値が「成分表」D列に何個あるかが一目で分かります。特に、今回の設計では検索結果が20件を超えた場合、それ以降の食品情報は表示できませんので、検索結果が丁度20件なのか、それ以上あるのかが分かるのは便利です。

第10回に進む

第8回に戻る