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

牽引番号による食品情報の取得

ワークシート間のセル参照

はじめに、牽引番号から食品情報を取得する数式を実装します。牽引番号はG10:G29セルに入力します。なお、今回は「成分表」と「レシピ作成シート」の2つのワークシートを使用するため、本記事内でシート名を指定しないセル参照は、「レシピ作成シート」のセル参照であると判断してください。つまり、前述のG10:G29セルは「レシピ作成シート」のセル参照を意図しています。ところで、これまでセル参照は列番号と行番号のみで指定してきました。改めて書くと混乱するかもしれませんが、普通に「=A1」といったセル参照のことを言っています。実はこのセル参照は、省略された記載方法です。「レシピ作成シート」のA1セルを少し長く書くと「=レシピ作成シート!A1」となります。ワークシート名とセルアドレスの間に「!」(エクスクラメーション)が入っていますね。「成分表」のA1セルなら「=成分表!A1」です。このようなセル参照を使用することで、「レシピ作成シート」内で「成分表」のセルの値を使用することができます。しかし、１つのワークシート内で事が済む場合は、わざわざワークシート名を指定する必要はありません。これは、学校や職場などの会話で、自分と同じクラス又は部署の「佐藤さん」の話題をする際は、単に「佐藤さん」と言いますが、違うクラスや部署の「佐藤さん」の場合は、「3組の佐藤さん」や「総務課の佐藤さん」と言うのと一緒です。セル参照にワークシート名が指定されていない場合、Excelは暗黙的に、入力されたセルと同じワークシートのセル参照であると判断しています。

MATCH-INDEX検索

牽引番号により食品情報を取得するため、今回は牛こま切れ肉の牽引番号を手動で調べます。

ところで、”こま切れ肉”は特定の部位の名称ではなく、肉の切れ端の総称です。従って、成分表には”こま切れ肉”という食品名は存在しません。今回は便宜上、輸入牛肉のばら肉を栄養計算に使用します。「成分表」では、牽引番号はC列に、食品名はD列に入力されています。

まずは手動で、輸入牛肉のばら肉を探してみましょう。手動といっても、Excel標準の検索機能を使用します。＜ホーム＞タブの＜編集＞リボンの中から、＜検索と選択＞をクリックし、表示されるメニューから＜検索＞を選択します。もしくは、キーボードのCtrlキーを押しながらFキーを押しても良いです(慣れるとこっちの方が早いです)。

＜検索と置換＞ダイアログが表示されるため、＜検索する文字列＞テキストボックスに「ばら」と入力し、＜次を検索＞ボタンを押して下さい。アクティブセルから左下に検索して最初に「ばら」と入力されているセルが選択状態となります。

＜次を検索＞ボタンを合計5回押すと、1545行目にある「＜畜肉類＞うし　［輸入牛肉］　ばら　脂身つき　生」が選択されます。探していたのは輸入牛肉のばら肉でしたね。これを、”牛こま切れ肉”に対応する食品としましょう。牽引番号は「1542」です。行番号と値が似ていますが、混同しないようにしましょう。

「レシピ作成シート」に戻り、G10セルに先程の牽引番号「1542」を入力します。

この牽引番号を使って、食品情報を取得しましょう。これには、MATCH関数とINDEX関数を使用します。

一応、MATCH関数の構文を下記に示しますが、枠の中の内容を全て読む必要はありませんし、現時点で理解できなくても構いません。まずはMATCH関数を使ってみて、応用してみたくなったり、挙動を確認したくなったら、改めて読んでみて下さい。

では、MATCH関数を使ってみましょう。R10セルに「=MATCH( $G10 , 成分表!C:C , 0 )」と入力してEnterキーを押して下さい．セル参照$G10が列番号だけ絶対参照になっていますが、これは後ほど効果を発揮します。MATCH関数の第1引数には、いわゆる検索キーワードを指定します。今回は牽引番号を指定します。第2引数は、検索キーワードを検索する範囲で、今回は「成分表」のC列全体を指定します。第3引数は検索方法を指定する引数ですが、特別な事情が無い限り「0」を指定しておけば問題ありません。

R10セルに「1545」と表示されればMATCH関数は成功です。このセルをR29まで書式なしコピーして下さい。さて、この「1545」ですが、これは「成分表」の行番号を示しており、「成分表」の1545行目には「＜畜肉類＞うし　［輸入牛肉］　ばら　脂身つき　生」の情報が入力されています。つまりこの数式は、牽引番号「1542」を使用して、牽引番号に対応する食品情報が入力されている行番号を取得します。

MATCH関数について、もう少し詳しく解説します。MATCH関数は、第1引数で指定した値を、第2引数で指定した範囲の中から探します。今回の「=MATCH( $G10 , 成分表!C:C , 0)」の計算過程を見てみましょう。

と、その前に、複数のセル参照(成分表!C:C やA1:A10など)が数式の計算過程でどう解釈されるかを説明します。複数のセル参照は、計算過程で配列というものに変換されます。配列の定義は下記の通りです。定義はよく分からなくても大丈夫です。

配列とは、複数のデータを連続的に並べたデータ構造。各データをその配列の要素といい、自然数などの添字(インデックス)で識別される。(weblio辞書より引用)

配列とは、複数の値を1つにまとめたものです。例えばE10:E12セルを配列に変換すると、その値は「{ “牛こま切れ肉" , “玉ねぎ" , “にんじん" }」となります。F19:F21なら「{ 36 , 5 , 12 }」ですね。Excelの場合、複数の値を「 , 」で区切り、その先頭に「 { 」、末尾に「 } 」を付けると、Excelはこれを配列と判断します。配列の中の値を要素と言い、{}内の要素がいくつあっても、配列は1つと数えます。第7回では下記のように、SUM関数を例に引数の数の数え方を説明しました。

K30セルに「=SUM(K10:K29)」と入力してEnterキーを押します。この場合の引数の数は、セル参照K10:K29の1つです。セルの数は20個ですが、引数としては1個のセル参照として数えます。しかし、「=SUM(K10,K11,K12)」とした場合の引数は3つと数えます。(本連載第7回より引用)

このセル参照K10:K29を引数1つと数えるのは、計算過程で1つの配列(要素数は20個)に変換されるためです。なお、配列は数式中に直接記述することもできます。例えば、「=SUM( { 1 , 2 , 3}) 」の戻り値は「6」となります。このように、数式内で直接記述した配列を配列定数と言います。

前置きが長くなりましたが、改めて「=MATCH( $G10 , 成分表!C:C , 0)」の計算過程を見てみましょう。なお、「成分表!C:C」の要素数は2199個(厳密には1048576個)なので、書ききれない途中の要素は省略しています。中略1では、1532個の要素を、中略2では651個の要素を省略しています。

ここで、MATCH関数は第1引数で指定した値(“1542")を第2引数の配列の中から検索します。配列の中から一致するものが見つからなかった場合、MATCH関数は「#N/A」を返します。一致するものが見つかった場合、MATCH関数は一致した要素のインデックスを返します。要素のインデックスとは、配列内における要素の位置番号のことです。位置番号は、配列の先頭を1として数えます。例えば、配列{ “A" , “B" , “C" }において、要素"C"のインデックスは「3」です。

今回のMATCH関数をIF関数風に書くと下記のようになります。ここで、「"1542″」は牽引番号、太字は配列内の要素、斜体は配列要素のインデックスです。

MATCH関数の動きが理解できたでしょうか？今回の数式「=MATCH( $G10 , 成分表!C:C , 0)」では、戻り値をそのまま行番号として使用していますが、厳密に言うと、MATCH関数の戻り値は配列要素のインデックスです。MATCH関数の第2引数に指定するセル参照の範囲(今回は「成分表!C:C」)が、ワークシートの1行目を含んでいる場合、配列要素のインデックスとワークシートの行番号は同じ値となります。MATCH関数の第2引数がワークシートの1行目を含んでいない場合、例えば「成分表!C2:C9999」である場合、MATCH関数の戻り値(配列要素のインデックス)は、ワークシートの行番号より1少ない値を返します。この性質は別の機会に使用します。とりあえず覚えてほしいのは、MATCH関数は、ワークシートの行番号を返す関数ではなく、第2引数で指定した配列の要素のインデックスを返す関数であるということです。

話を本題に戻します。先程コピーしたR11:R29セルが全てエラー値「#N/A」となっていますが、特に問題はありません。MATCH関数は、キーワード(第1引数)に空の文字列「""」を指定すると「#N/A」を返します。つまり、牽引番号入力セルであるG11:G29セルが空欄であるため、R11:R29セルのMATCH関数が第1引数に「""」を受け取り、戻り値が「#N/A」となった結果です。牽引番号入力セルに有効な(成分表に存在する)牽引番号を入力すれば、対応する行番号が表示されます。

次に、U10セルにR10のセル参照を入力し、U29セルまで書式なしコピーして下さい。このU10:U29セルのタイトルは”採用行番号”と書いてありますね。現時点で、このセルはR10:R29セルと同一の値であるため、存在意義が分かりませんが、後ほど変更を加えます。具体的には、ユーザーが食品情報を検索する際、検索キーワードとして牽引番号の他に、成分表の食品名や「レシピ作成シート」の材料名(E10:E29セル)を使用できるようにするためのセルです。

さて、牽引番号を指定して、成分表から目的の食品情報が入力されている行番号を取得しました。この行番号を使用して、実際に食品情報を取得してみましょう。これには、INDEX関数を使用します。INDEX関数は、指定したセル参照範囲(領域とも言います)の中から、行番号と列番号で指定したセル参照を取得する関数です。言い換えると、通常はセルアドレス(A1など)で指定しているセル参照を、動的に指定するための関数です。例によって下記にINDEX関数の構文を示しますが、こちらも、枠の中の内容を全て読む必要はありませんし、現時点で理解できなくても構いません。

さっそく使ってみましょう。I10セルに「INDEX(」まで入力し、「成分表」をクリックします。

「成分表」の列番号「C」をクリックすると、数式に「成分表!C:C」が自動入力され、「=INDEX(成分表!C:C」となります。この操作は，「成分表!C:C」を一語一句正確に入力できるなら手入力でも構いません。

「成分表!C:C」を自動入力させた場合、数式バーをクリックして、数式の末尾にキーボードで「 , 」を入力して下さい。これは、セル参照の自動入力を確定させるためです。自動入力を確定させないまま、マウスやキーボードの矢印キーで別のセルを選択すると、そのセルの参照が自動入力されてしまいます。自動入力を確定させるために入力する文字は「 , 」に限らず何でも構いませんが、何も入力したくない場合はF2キーかEnterキーを押します。

「レシピ作成シート」に戻るためシート名をクリックすると、数式の末尾(正確にはキャレットの位置)にこのシートの参照である「レシピ作成シート!」が自動入力されます。見易さの観点から削除しても構いません。本記事では削除します。

数式の末尾に「 $U10 , 1) 」を入力して数式「=INDEX(成分表!C:C, $U10, 1)」を完成させ、Enterキーを押します。「成分表」シートのC列1545行目の値(牽引番号)「1542」が表示されればOKです。牽引番号「1542」は、既にG10セルに手入力しているため、何の意味があるか分からない方もいるかもしれませんが、もう少し読み進めると意味が分かってくると思います。

INDEX関数は、第1引数で指定したセル参照の範囲(又は配列)の中から、第2引数と第3引数で指定した行数と列数に該当する値を返します。最も簡単なINDEX関数の一例は「=INDEX(E10 , 1 , 1)で、戻り値は「"牛こま切れ肉"」です。この数式の意味は「=E10」と同じです。次に、「=INDEX(E10:E29 , 1 , 1)」の場合を考えてみましょう。最初の例と比較して、第一引数のセル参照が範囲になりましたね。この場合でも、戻り値は「"牛こま切れ肉"」で、数式の意味は「=E10」と同じです。では次に、「=INDEX(E10:E29 , 2 , 1)」ではどうでしょうか？今回は第2引数(行数)に「2」を指定しています。この場合の戻り値は「"玉ねぎ"」で、数式の意味は「=E11」と同じです。この数式を言葉にすると、E10～E29セルの中から、E10を原点として2行目、1列目に該当するセル参照を取得します。では最後に、「=INDEX(E10:E29 , 3 , 1)」ではどうでしょうか？もうお分かりですね。戻り値は「"にんじん"」、数式の意味は「=E12」と同じです。

では、「=INDEX(成分表!C:C, $U10, 1)」の計算過程を見てみましょう。今回も「成分表!C:C」の途中の要素は省略しています。なお、Excelはセル参照を配列の要素として指定できませんが、計算過程を理解してもらうため、便宜上配列の要素としてセル参照を記載しています。また、分かり易さのために、セル参照の隣にセルの値を併記しました。

ここでINDEX関数は、成分表!C1セルを原点として1545行目、1列目に該当するセル参照を返します。今回、第1引数のセル参照(成分表!C:C)は1列であるため、第3引数で指定する列数は1以外指定できません。列数に2以上の値を指定すると、第1引数のセル参照の範囲外を指定していることになるため、戻り値は「#REF!」となります。これは、行数に1048577(ワークシートの最大行数+1 )を指定しても同様です。

今回のINDEX関数をIF関数風に書くと下記のようになります。ここで、「1545」は行番号、太字は第1引数で指定したセル参照の行数(配列要素のインデックスと同じ)、斜体はセル参照です。

なお、実際のINDEX関数は、上記のIF関数風処理とは違って、インデックスを1回1回比較しません。つまり、インデックスに対応する配列要素(セル参照)を1回で引き当てます。

INDEX関数の動きが理解できたでしょうか？MATCH-INDEX検索を行う場合、MATCH関数の第2引数のセル参照の行数と、INDEX関数の第1引数のセル参照の行数は一致させる必要があります。この言葉の意味が理解できると、MATCH-INDEX検索の基本を理解できたと思ってください。

話を本題に戻します。I10セルをI29セルまで書式なしコピーして下さい。コピーしたセルは「#N/A」となりますが、これはINDEX関数の第2引数に「#N/A」を渡した結果です。

INDEX関数に限らす、多くの関数は、引数にエラー値を渡すと、戻り値は引数で渡したエラー値となります。これには例外もあり、エラー値を引数に取ることを前提としているISERROR関数やIFERROR関数などは、その関数毎の戻り値を返します。実際にIFERROR関数を試してみましょう。下記にIFERROR関数の構文を示します。

IFERROR関数は、エラー値(#N/A、#VALUE!など)に限定したIF関数のようなもので、Excel2007から登場した比較的新しい関数です(10年以上経過しているので新しくもないのかもしれませんが)。

I10セルに入力済みの数式「=INDEX(成分表!C:C, $U10, 1)」は、エラー値#N/Aになる可能性があるため、これをIFERROR関数の第1引数に指定します。具体的には、I10セルの数式「=INDEX(成分表!C:C, $U10, 1)」を「=IFERROR( INDEX(成分表!C:C, $U10, 1) , “") 」に書き換えます。このセルを再びI29セルまで書式なしコピーして下さい。

I11:I29セルの「#N/A」がIFERROR関数により「""」に変換され、エラー値の表示が無くなりました。とても見栄えが良くなりましたね。

スッキリした所で、I10:I29セルをJ10:J29セルに書式なしコピーして下さい。J10セルに、牽引番号「1542」に対応する食品名「＜畜肉類＞うし　［輸入牛肉］　ばら　脂身つき　生」が表示されました。これはある意味偶然ですが、詳しくは後述します。ついに、牽引番号から食品名を取得することが出来ました。牽引番号から食品名を取得できたということは、成分表の他の情報、つまりカロリーやたんぱく質量なども取得できそうな感じがしてきましたね。

ここで一度、数式を確認しておきましょう。I10:I29セルをJ10:J29にコピーした際、I列の数式の「成分表!C:C」が、J列では「成分表!D:D」に変化していますね。横方向にセルをコピーすると、相対参照のセル参照は列番号が変化します。横方向のコピーの場合も、$マークが付いた絶対参照$U10～$U29は変化していませんね。しかし、$U10は列番号のみの絶対参照であるため、I10セルをI11:I29セルにコピーした際、行番号は変化していたことも確認できます。つまり、I10:I29セルとJ10:J29セルの数式を比較して違うところは、INDEX関数の第1引数のみです。具体的には、I10:I29セルを1列右のJ10:J29セルにコピーしたことで、INDEX関数の第1引数が「成分表!C:C」から「成分表!D:D」に変化しました。J10:J29セルは成分表の食品名を表示する枠として事前に罫線を引いており、「成分表」のD列は偶然にも食品名が入力されてる列でした。結果として、セルのコピーだけで目的の値を取得する数式が出来ましたので、これはこのまま使用します。そして、INDEX関数の第1引数が「成分表!C:C」なら牽引番号を取得し、「成分表!D:D」なら食品名を取得するなら、この引数を書き換えることで、「成分表」の任意のセルの値を取得することが可能となります。例えば、「成分表!E:E」なら廃棄率、「成分表!F:F」ならカロリーを取得可能です。