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

2020年4月19日

献立作成ソフトを作ろう第9回目は、食品の検索結果を複数表示します。前回までの食品検索でもそこそこ使えますが、入力したキーワードに一致する食品一覧が出てくると実用性が飛躍的に上がります。今回は、前回に引き続き難易度が高めです。しかし同時に、今回が本連載最大の山場でもあるため、ここを乗り切れば、あとはこれまでに学んできたことの応用になります。頑張っていきましょう。

検索結果表示欄の作成

食品の検索結果を複数表示させるためには、専用の表示欄を用意する必要があるので、まずはこれを作成します。表示欄の基本構造は、栄養計算表を流用します。I8:P29セルをコピーしてI33セルに貼り付けます。

貼り付けた表には数式がコピーされているため、これ(I35:K54セルの数式)を削除します。

<セルを結合して中央揃え>を使用して、下図の様にJ列の“成分表名”セルをK列まで拡張します。

I33:I54セルをコピーし、R33セルへ貼り付けます。

I32セルに「"成分検索結果"」、R32セルに「"成分検索結果作業表"」と入力し、これらの文字を太字にします。太字にする方法は色々ありますが、可能であればキーボードショートカットのCtrl+Bで設定するようにして下さい。続いて、R33セルに「"成分表行番号"」と入力し、“成分表”と“行番号”の間にキャレットを合わせてキーボードのAltキーを押しながらEnterキーを押して下さい。これにより、セル内で文字列の改行ができます。セル内の改行は多用するものではありませんが、知っていると便利なので覚えておきましょう。

これで、検索結果表示欄の完成です。

次に、検索キーワードの入力欄を用意します。GoogleやYahoo!では、キーワード入力欄は専用の入力欄が1つあるため、これを真似るのであれば、適当なセルをキーワード入力欄として罫線で囲めば良いでしょう。これでも問題はありませんが、今回は成分検索入力欄(G10:G29セル)に入力されたキーワードの内、一番下に入力されているキーワードを自動的に検索する仕組みを作ります。この理由は、ユーザーの入力の手間を減らすためです。仮に、専用のキーワード入力欄を用意した場合を考えてみましょう。そして、専用の入力欄に「たまねぎ」と入力すると、検索結果の一番上に目的の食品情報が表示されたとします。これならば、最初から「たまねぎ」を成分検索入力欄(G10:G29セル)に入力しておけば、入力は1回で済んだはずです。また、専用のキーワード入力欄と成分検索入力欄(G10:G29セル)が別々の場合、入力カーソルを行ったり来たりする必要があるため、煩雑です。よって、最初から成分検索入力欄(G10:G29セル)に入力された文字をキーワードとして自動的に検索された方が効率が良くなります。多くの場合、セルへの入力は上から下に進むので、成分検索入力欄(G10:G29セル)の一番下に入力された文字が、ユーザーが入力した最新の文字である可能性が高いため、これを検索キーワードに用います。

という訳で、成分検索入力欄(G10:G29セル)に入力された一番下のキーワードを自動的に検索する仕組みを作っていきます。まず、この仕組みを実現するための作業表を作ります。下図の赤枠内の作業表を作ってみてください。ちなみに、作業表のタイトル列はセル結合をしていますが、これは表示面積を確保するためで、それ以上の意味はありません。

成分検索欄の最終入力行の取得

最終入力行を取得する2つのパターン

成分検索欄の最終入力行(行番号)をV33セルに取得します。今回は、最終入力行を取得する手法を2つ解説します。1つは関数を組み合わせた普通の数式、もう1つは配列数式です。配列数式は、Excel初心者に立ちはだかる大きな壁の1つで、Excelの使用経験が長い方でも理解していない人は多いと思います。“意味の分からない数式なんて使いたくない”という有望なExcel初心者のためにも、配列数式を理解できるよう丁寧な解説を心がけますが、実は配列数式は理解できなくとも問題の無い機能でもあります。というのも、配列数式でないと実現できない計算は原則としてありません(TRANSPOSE関数というものもありますが、無くても何とかなります)。それでも配列数式という機能があるのは、配列数式を使用した方が、効率が良いからです。この場合の効率とは、数式を作る工数、セルの使用数、計算速度などを意味しています。そんなこともあり、私は配列数式のほうが好きです。実際には、これらの効率が体感的に影響を与える状況は殆ど無いと思いますので、どちらでも好きな方を採用して下さい。

配列数式を使用しない場合

まず、配列数式を使用しないパターンですが、成分検索欄の最終入力行を取得するために途中計算が必要なので、途中計算用の枠を作ります。W7セルに「”成分検索欄 最終入力行 作業表”」、W8セルに「“行番号”」と入力します。W8:W9セルを結合し、W8:W29セルを格子罫線で囲めば完成です。表のタイトル行を下二重罫線にするのは私の好みなので、引かなくても問題ありません。

W10セルに「=IF( G10 = “", “", ROW() )」と入力してEnterキーを押してください。W10セルの値が「10」になれば成功です。W10セルをW29セルまで書式なしコピーして下さい。

ROW関数という新しい関数が出てきましたね。ROW関数は、セルの行数を返す関数で、INDEX関数と相性が良いです。高度な数式を記述する際はよく登場するので、しっかり覚えておきましょう。ROW関数の構文を下記に示します。

説明

引数として指定された配列の行番号を返します。

書式

ROW([参照])

引数 説明
[参照] 省略可能です。 行番号を調べるセルまたはセル範囲の参照を指定します。

  •   範囲を省略すると、ROW 関数が入力されているセルの行番号が返されます。
  • 範囲がセル範囲に対する参照で、ROW 関数が縦方向の配列として入力されている場合、範囲の行番号は縦方向の配列となります。
  • 範囲に複数の選択範囲 (連続しない複数のセルまたはセル範囲) を指定することはできません。

(Microsoftより一部変更して引用)

上記引用の繰り返しになりますが、ROW関数は引数で指定したセル参照の行番号を返します。つまり、セル参照の数値部分(A1なら1、Z99なら99の部分)を返します。ROW関数の引数を省略すると、関数が入力されているセルの行数を返します。つまり、先程W10セルに入力した数式「=IF( G10 = “", “", ROW() )」は、「=IF( G10 = “", “", ROW(W10) )」と同じ結果を返しますので、引数で指定するセル参照と、ROW関数を入力するセルが同じ行になる場合、どちらの書き方をしても問題ありません。

次に、V33セルに「=MAX( W10:W29) 」と入力してEnterキーを押して下さい。V33のセルの値が成分検索欄の最終入力行「12」となれば成功です。

MAX関数は、指定した引数の最大値を返す関数です。下記に構文を示します。

説明

一連の引数のうち、最大の数値を返します。

構文

MAX( 数値 1, [数値 2] , ・・・ , [数値 254] , [数値 255] )

引数 説明
数値1~255 数値 1 は必ず指定します。数値 2 以降は省略可能です。 最大値を見つける対象となる数値を最大 255 個まで指定します。

(Microsoftより一部変更して引用)

つまりこの一連の数式は、成分検索欄(G10:G29セル)に値が入力されている行番号を全て列挙し、その最大値を取得しています。この最大値が成分検索欄の最終入力行数です。

配列数式を使用する場合

今度は、配列数式を使用するパターンです。先程V33セルに入力した数式は削除し、V33セルに「=MAX(IF( G10:G29 = “", “", ROW(G10:G29)))」と入力し、キーボードのCtrlキーとShiftキーを押しながらEnterキーを押して下さい。V33セルに「12」と表示されれば配列数式の入力は成功です。この数式1つだけで、成分検索欄の最終行が取得できました。これが配列数式の威力です。なお、V33セルが「#VALUE!」なら数式が配列数式になっていないので、V33セルを(F2キーなどで)編集状態にし、再度Ctrl + Shift + Enterキーを押してください。配列数式は、このCtrl + Shift + Enterの頭文字を取って、CSE数式とも呼びます(下記引用を参照)。

配列数式は、CSE (Ctrl+Shift+Enter) 数式と呼ばれることもあります。これは、Enter キーを押す代わりに、Ctrl キーと Shift キーを押しながら Enter キーを押して数式を完成させるためです。(Microsoftより引用)

V33セルにカーソルを合わせて数式バーを確認すると、先程入力した数式を挟み込む形で「 { } 」が付加されています。これは、数式が配列数式であることを明示するもので、Excelが自動的に付加します。

この配列数式「=MAX(IF( G10:G29 = “", “", ROW(G10:G29)))」について説明します。この数式で使用している関数は、前述の配列数式を使用しないパターンで使用した関数と一緒ですね。IF関数の第1引数で指定している論理式(G10:G29 = “")も、何となく似ています。しかし、論理式やROW関数の引数に複数のセル参照を指定していますね。このような指定は、通常の数式ではエラーになります。しかし、配列数式にする(数式をCtrl+Shift+Enterで確定する)と、Excelは下記のような数式が指定されたものと解釈します。

=MAX( IF ( G10 = “" , “", ROW(G10) , IF ( G11 = “" , “", ROW(G11) , IF ( G12 = “" , “", ROW(G12) , IF ( G13 = “" , “", ROW(G12) ,  ・・・(中略)・・・ , IF ( G29 = “" , “", ROW(G29) )

このMAX関数の中の引数1つ1つをよく見ると、前述の配列数式を使用しないパターンでW10:W29セルに入力した数式と一緒ですね。具体的な計算過程を見てみましょう。

=MAX( IF ( G10  = “" , “", ROW(G10) , IF ( G11  = “" , “", ROW(G11) , IF ( G12  = “" , “", ROW(G12) , IF ( G13  = “" , “", ROW(G13) , ・・・(中略)・・・, IF ( G29 = “" , “", ROW(G29) )

=MAX( IF ( 1542 = “" , “", ROW(G10) , IF ( たまねぎ”  = “" , “", ROW(G11) , IF ( にんじん 根 むき”  = “" , “", ROW(G12) , IF ( “" = “" , “", ROW(G13) , ・・・(中略)・・・ , IF ( “" = “" , “", ROW(G29) )

=MAX( IF ( 1542" = “" , “", ROW(G10) , IF (たまねぎ”  = “" , “", ROW(G11) , IF ( にんじん 根 むき  = “" , “", ROW(G12) , IF ( “" = “" , “", ROW(G13) , ・・・・(中略)・・・, IF ( “" = “" , “", ROW(G29) )

=MAX( IF ( FALSE,  “" , ROW(G10) , IF ( FALSE , “", ROW(G11) , IF ( FALSE , “", ROW(G12) , IF ( TRUE , “", ROW(G13) , ・・・・(中略)・・・, IF ( TRUE , “", ROW(G29) )

=MAX( ROW(G10) , ROW(G11) , ROW(G12) , “" , ・・・・(中略)・・・, “" )

=MAX(10 , 11 , 12 , “" , ・・・・(中略)・・・, “" )

=12

配列数式は、今回のようにセル参照のみ異なる複数の数式の結果を集計する際に、よく使用されます(それ以外の用途もあります)。つまり、配列数式が使用できる条件が揃えば、途中計算に使用しているセルが不要になります。

余談として、入力最終行を取得する別の配列数式を紹介します。W33セルに「=MAX( ROW( G10:G29 ) * (G10:G29 <> “")) 」と入力して、Ctrl+Shift+Enterを押してください。前述の配列数式と同じ結果が得られます。この数式は、Excelが内部的にTRUEを1、FALSEを0として計算することを利用した数式です。直感に反する数式なので個人的にはあまり好きではありませんが、インターネット上ではこのような数式を見かけることがあるので解説します。計算過程は下記の通りです。

=MAX( ROW(G10) * (G10 <> “") , ROW(G11) * (G11 <> “") , ROW(G12) * (G12 <> “") , ROW(G13) * (G13 <> “") , ・・・(中略)・・・, ROW(G29) * (G29 <> “") )

=MAX( ROW(G10) * (“1542" <> “") , ROW(G11) * (たまねぎ" <> “") , ROW(G12) * (にんじん 根 むき" <> “") , ROW(G13) * (“" <> “") , ・・・(中略)・・・, ROW(G29) * (“" <> “") )

=MAX( ROW(G10) * (“1542" <> “") , ROW(G11) * (たまねぎ" <> “") , ROW(G12) * (にんじん 根 むき" <> “") , ROW(G13) * (“" <> “") , ・・・(中略)・・・, ROW(G29) * (“" <> “") )

=MAX( ROW(G10) * TRUE , ROW(G11) * TRUE , ROW(G12) * TRUE , ROW(G13) * FALSE , ・・・(中略)・・・, ROW(G29) * FALSE )

=MAX( ROW(G10) * 1 , ROW(G11) * 1 , ROW(G12) * 1 , ROW(G13) * 0 , ・・・(中略)・・・, ROW(G29) * 0 )

=MAX(10 * 1 , 11 * 1 , 12 * 1 , 13 * 0 , ・・・(中略)・・・, 29 * 0 )

=MAX(10 , 11 , 12 , 0 , ・・・(中略)・・・, 0 )

=MAX(10 , 11 , 12 , 0 , ・・・(中略)・・・, 0 )

=12

解説した2つの配列数式を見て気付いた人もいるかもしれませんが、1つの配列数式中に指定する”複数のセル参照(領域)”が2つ以上ある場合、これらは原則として全て同じ行数・列数である必要があります。異なる行数・列数で指定出来なくも無いですが、その数式はエラーを内包してしまいます。例えば、今回使用した配列数式「=MAX(IF( G10:G29 = “", “", ROW(G10:G29)))」は、20行・1列のセル参照を2つ指定していますね。これを「=MAX(IF( G10:G28 = “", “", ROW(G10:G29)))」とした場合、これを展開・計算すると下記のようなイメージになります。なお、一部の関数を除き、引数にエラー値を渡された関数は同じエラー値を返します。

=MAX( IF ( G10  = “" , “", ROW(G10) , IF ( G11  = “" , “", ROW(G11) , ・・・(中略)・・・, IF ( G28 = “" , “", ROW(G28) , IF ( 配列範囲外 = “" , “", ROW(G29) )

=MAX( IF ( G10  = “" , “", ROW(G10) , IF ( G11  = “" , “", ROW(G11) , ・・・(中略)・・・, IF ( G28 = “" , “", ROW(G28) , IF (#NA , “", ROW(G29) )

=MAX( 10 , 11 , ・・・(中略)・・・, “" , #NA )

= #NA

食品情報検索結果の取得

方法はどうであれ、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(基準, 行数, 列数, [高さ], [幅])

引数 説明
基準 必ず指定します。オフセットの基準となる参照を指定します。参照は、セルまたは隣接するセル範囲を参照する必要があり、それ以外の場合は、エラー値 #VALUE! が返されます。
行数 必ず指定します。基準の左上隅のセルを上方向または下方向へシフトする距離を行数単位で指定します。行数に 5 を指定すると、オフセット参照の左上隅のセルは、基準の左上隅のセルから 5 行下方向へシフトします。行数に正の数を指定すると開始位置の下方向へシフトし、負の数を指定すると開始位置の上方向へシフトします。
列数 必ず指定します。結果の左上隅のセルを左方向または右方向へシフトする距離を列数単位で指定します。列数に 5 を指定すると、オフセット参照の左上隅のセルは、基準の左上隅のセルから 5 列右方向へシフトします。列数に正の数を指定すると開始位置から右方向へシフトし、負の数を指定すると開始位置から左方向へシフトします。
[高さ] 省略可能です。オフセット参照の行数を指定します。高さは正の数である必要があります。
[幅] 省略可能です。オフセット参照の列数を指定します。幅は正の数である必要があります。

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

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

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

数式 戻り値
=OFFSET(A1 , 0 , 0 ) A1
=OFFSET(A1 , 1 , 0 ) A2
=OFFSET(A1 , 2 , 0 ) A3
=OFFSET(A1 , 0 , 1 ) B1
=OFFSET(A1 , 0 , 2 ) C1
=OFFSET(A1 , 1 , 1 ) B2
=OFFSET(A1 , 2 , 1 ) B3
=OFFSET(A1 , 2 , 2 ) C3
=OFFSET(A1:B99 , 0 , 0 ) A1:B99
=OFFSET(A1:B99 , 1 , 0 ) A2:B100
=OFFSET(A1:B99 , 0 , 1 ) B1:C99
=OFFSET(A1:B99 , 1 , 1 ) B2:C100
=OFFSET(B2:C100 , 1 , 1 ) C3:D101
=OFFSET(B2 , -1 , -1 ) A1
=OFFSET(A1 , -1 , 0 ) #REF!
=OFFSET(A1 , 0 , -1 ) #REF!

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

数式 戻り値
=OFFSET(A1 , 0 , 0 , 1 , 1) A1
=OFFSET(A1 , 0 , 0 , 2 , 1) A1:A2
=OFFSET(A1 , 0 , 0 , 3 , 1) A1:A3
=OFFSET(A1 , 0 , 0 , 1 , 2) A1:B1
=OFFSET(A1 , 0 , 0 , 1 , 3) A1:C1
=OFFSET(A1 , 0 , 0 , 2 , 2) A1:B2
=OFFSET(A1 , 0 , 0 , 3 , 2) A1:B3
=OFFSET(A1 , 0 , 0 , 3 , 3) A1:C3
=OFFSET(A1:B99 , 0 , 0 , 1 , 1) A1
=OFFSET(A1 , 0 , 0 , 0 , 1) #REF!
=OFFSET(A1 , 0 , 0 , 1 , 0) #REF!

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以降のワークシートの最大行数です。

=MATCH($V$35 , OFFSET(成分表!$D$1, R35 , 0 , 1048576 – R35 , 1) , 0) + R35

=MATCH(”*にんじん*根*むき*” , OFFSET(成分表!$D$1, 621 , 0 , 1048576 – 621 , 1) , 0) + 621

=MATCH(”*にんじん*根*むき*” , OFFSET(成分表!$D$1, 621 , 0 , 1048576 – 621 , 1) , 0) + 621

=MATCH(”*にんじん*根*むき*” , OFFSET(成分表!$D$1, 621 , 0 , 1047955 , 1) , 0) + 621

=MATCH(”*にんじん*根*むき*” , OFFSET(成分表!$D$1, 621 , 0 , 1047955 , 1) , 0) + 621

=MATCH(”*にんじん*根*むき*” , 成分表!$D$622:$D$1048576 , 0) + 621

=MATCH(”*にんじん*根*むき*” , 成分表!$D$622:$D$1048576 , 0) + 621

=1 + 621

=1 + 621

= 622

この計算過程の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(範囲, 検索条件)

引数 説明
範囲 必ず指定します。数えるセルのグループ。範囲には、数値、配列、名前付き範囲、(数値を含む) 参照が入ります。空の値とテキスト値は無視されます。
検索条件 必ず指定します。個数の計算対象となるセルを決定する条件を、数値、式、セル参照、または文字列で指定します。

たとえば、数値として 32、比較演算子として “>32″、セル参照として B4、文字列として “リンゴ" などを指定できます。

COUNTIF で指定できるのは、単一の検索条件のみです。 複数の検索条件を指定する場合は、COUNTIFS を使います

(Microsoftより一部変更して引用)

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

第10回に進む

第8回に戻る