みなさん，こんにちは。
シンノユウキ（shinno1993）です。
今回は，「Excelで栄養計算ソフトを自作しよう」の第５回で，
こちらの記事の続きです。
前回は食品番号からエネルギーを検索・表示して，それを重量に応じて計算しました。
今回は，それと同様のことをエネルギー以外の成分値についても行っていきます。
いよいよ栄養計算ソフトらしくなってきましたね。
やり方は非常に簡単。エネルギーの算出に使用した数式を上手くコピペすることでこれを実現できます。
では，行きましょう！
前回のおさらい
まずは前回のおさらいからです。
前回はVLOOKUP関数を使用して，食品番号からエネルギーを検索・表示して，それを重量に応じて計算しました。セルに入力した数式は以下のようなものでした：
この数式を「栄養計算シート」のセルG12に入力しました。
VLOOKUP関数で，成分値の含まれる範囲（本表!A12:BH2489）から食品番号（セルB12）を検索し，その食品番号のある行の，エネルギーのある列（5）に存在する値（食品のエネルギー値(kJ)）を算出。それを100で割り1gあたりのエネルギーに換算した後に重量（セルE12）で乗じる。
このようにすることで，食品のエネルギーを算出することができました。
今回はその続きからです。
エネルギー以外の成文値も算出しよう
エネルギーと同様に，他の栄養素の成分値も算出していきます。算出する方法はエネルギーと同様。すなわち，食品番号から値を検索し，重量から成分値を算出していきます。
そのため，エネルギーの算出に用いた計算式を上手く活用すれば，他の成分値も算出できます。
コピペでOK！ただし注意が必要
しかし，単純にコピペするだけではその目的は達成できません。少しの工夫が必要です。
まず悪い例として，単純にコピペした場合を見てみましょう。
エネルギー（kJ）の数式（セルF12）を，エネルギー（kcal）のセル（セルG12）にコピペしてみます。エラーが表示され，うまくいっていないことがわかります：
なぜエラーが出るのでしょうか。
コピペ先（セルH12）の数式を確認してみます。VLOOKUP関数の最初の引数は，本来は食品番号（B12）を参照しているはずなのに索引番号（C12）のセルを参照してしまっています。また，重量（E12）を参照するはずが廃棄率（F12）を参照してしまっています。そしてよく見ると，成分値の範囲である本表!A12:BH2489も一列右にずれて本表!B12:BI2489になってしまっていることがわかります。
つまりコピペによって，参照すべきセルが一列右にずれてしまっています。
なぜこういったことが起こるのでしょうか？
セル番地が意図しない方向に移動してしまう
これは，Excelのセル参照が，デフォルトでは相対参照であることに関係しています。
これは，Excelではセルを参照する際，絶対的なセル番地を参照しているのではなく，計算式を入力したセルからみた相対的な位置としてセルを参照しているということを意味しています。これを相対参照といいます。
相対参照は一度理解してしまうとなんてことはないのですが，一度理解するまでが大変です。下記の説明を聞いて理解できなかったとしても，時間が経てば理解できるようになることもあります。理解できない場合でもとりあえず進められるようにしますので，理解できなかったとしても安心してください。
エネルギーを算出するための数式では，セルの参照を行っています。食品番号のセルを意味するA4などですね。こういったセル参照は，A4という絶対的なアドレスにあるセルとして参照しているのではなく，数式を入力したセルからみて相対的にどこに位置するかという風に参照し認識してます。
そしてこの認識は，コピペ後も継続します。つまり一つ右のセルにコピペした場合でも100行下にコピペした場合でも，「相対的にどこに位置するか」という認識は継続してしまっているのです。そのため，先の例のように一つ右にコピペした場合には，参照が一つ右にずれてしまうのです：
絶対参照を使いこなそう
相対参照では，コピペによってセル参照の場所がずれてしまいます。
これを解決するために絶対参照を使用します。セルの参照を相対参照→絶対参照にすることで，コピペした場合でも参照するセルを固定することができます。
さらに，相対参照と絶対参照は同じ数式内で使い分けることができます。つまり，相対位置で参照したいセルには相対参照を，絶対位置（固定）で参照したいセルには絶対参照を指定することができます。さらにさらに，この相対参照と絶対参照は，行と列で別々に指定することもできます。少し話が複雑になってきましたが，これらの参照を使いわけることで，とても便利に使えるようになります。
相対参照と絶対参照の使い分けは，Excelを便利に使う上で習得しておきたい知識・テクニックの一つです。その分だけ難しくもありますが，根気よく学んでいきましょう！
さて，今回のエネルギーを算出する数式では，以下のセルを参照しています：
- B12：食品番号
- 本表!A12:BH2489：成分表の範囲
- E12：重量
これらを相対参照から絶対参照に変更していかなければなりません。ただし，全てを絶対参照にするのではなく，一部は相対参照の方が都合が良い部分もあります。
というのも，エネルギーの数式をコピペする際，横（他の栄養素）だけでなく，下（他の食品）にも同様にコピペしたいところです。その場合，参照する食品番号や重量はコピペに従って下方向にズレてくれたほうが，むしろ都合がよくなります。つまり行についてはズレてもらう必要があります。以上をまとめると，下記のようになります：
| 参照セル | 行 | 列 |
|---|---|---|
| B12：食品番号 | 相対参照 | 絶対参照 |
| 本表!A12:BH2489：成分表の範囲 | 絶対参照 | 絶対参照 |
| E12：重量 | 相対参照 | 相対参照 |
では上記にしたがって，セル参照の方法を変更していきます。
セル参照はデフォルトでは相対参照です。そのため，相対参照にしたい部分に手を加えます。絶対参照を相対参照にする方法は簡単で，絶対参照にしたい行や列の前に「＄」をつけるだけです。絶対参照を上手く使用した計算式が以下です。
それぞれ，絶対参照に変更したい列や行の手前に＄を挿入しました。
こうすることで，計算式をコピペした場合でも参照するセルを正しく指定できます。
しかし，まだ問題は残っています。VLOOKUP関数中で表示する列番号を示す「5」については栄養素によって変更する必要があります。参照する列番号なので，セルが一つ右に動くたびに１を加えていかなければなりません。しかしこれを，栄養素ごとに行うのは大変です。次はこの問題を解決していきます。
MATCH関数で複数の成分値に対応しよう
列番号の変更に対応するために，MATCH関数を使用します。
MATCH関数は，指定の範囲から指定の値を検索し，それが何番目に位置するかを返す関数です。表示したい栄養素名の成分識別子（11行目）を検索し，それが成分表の範囲の何列目に位置するのかを返します。
まず最終的な計算式を下記に示します：
赤文字の部分が，今回変更した部分です。MATCH関数で指定する引数はVLOOKUP関数と似ています。最初の引数の値を，２つ目の引数の範囲から検索します。一次元の範囲のみを指定するため，列番号は指定できません。
セルG11は栄養素名の成分識別子のエネルギー(kJ)（ENERC）を示しています。これを本表!本表!$A$11:$BH$11から検索します。つまり，本表の栄養素項目列の何番目にエネルギー(kJ)が位置するかを検索しています。ちなみに，このMATCH関数で帰ってくる値は「５」です。
MATCH関数も，VLOOKUP関数と並んでよく使う関数の一つです。この機会にぜひ習得しておきましょう！
この計算式でも，絶対参照を使用しています。成分識別子が入力されているセルG11は，コピペの際に横（列）方向にズレるのはOKです。むしろ，ズレてくれる必要があります。しかし，下（行）方向にずれてしまっては困ります。そのため行番号の前に＄をつけて絶対参照にしています。また，検索範囲である本表!$A$11:$BH$11は，横方向にも下方向にもズレてしまっては困ります。両方に$を付けています。
IFERROR関数でエラーが表示されないようにしよう
次にIFERROR関数を使用していきます。
現在の状態では，食品番号が入力されていない場合，下記のようにエラーが表示されてしまいます：
VLOOKUP関数が食品名や栄養素を検索することができないため，このようなエラーが表示されてしまいます。これは少し目障りですよね。このような場合には空白を表示するようにしたいと思います。
これには，IFERROR関数を使用すると便利です。関数の結果に応じて下記のようにします：
- 関数の結果がエラーの場合 → 何も表示しない（空白）
- 関数の結果がエラーでない場合 → 計算結果を表示する
IFERROR関数は下記のように使用します：
では，IFEEROR関数を使ってセルの計算式を修正してみましょう。例として，セルF4の計算式を修正してみます。以下のようになればOKです。
赤字の部分が修正した箇所です。もともと入力されていた計算式をIFERROR関数の最初の引数にして囲った感じになっています。IFERROR関数の2つ目の引数はダブルクオーテーション２つ（""）です。エラーの際には空白が表示されます。
少し面倒ですが，このIFERROR関数を計算式のある全てのセルに組み込んでください。
「最初からこうすればよかったじゃん！」という声もあるかもしれませんが，こういったソフトの作成の際には，一度機能を実装して，その後，細かい問題を修正していくという作業のほうが効率的に行える場合もあります。面倒ですが，すみません！
コピペして栄養計算してみよう
さて，長い道のりでしたが，ここまででコピペに使用できる数式が完成しました。
いよいよ，この数式を他のセルにコピペしてみましょう。横方向に一気にコピペして，下方向にも010行くらいコピペしてみます。Excelのオートフィル機能を使うと便利です。
セルの右下あたりをクリックして，クリックしたまま一番右まで引っ張ります。同じように下方向にも引っ張ってコピペします。多くの食品が入力できるように100行くらいまでコピペしておきましょう。下記のGIFを参照してください：
同様に，食品名を入力する列も下方向コピペしていきます。その際，数式は下記のように変更してください：
下方向にコピペするにあたって，いくつかの部分を絶対参照に変更しました。
では試しに，2つ目以降の行（5行目以降）にも，食品番号と重量を入力して計算が実施されるかやってみましょう：
上記のように栄養計算されるとOKです！
今回は少し難しく感じる部分もあったかもしれませんが，絶対参照やMACTH関数等を使用できるようになると，Excelでできる部分も飛躍的に増えていきます。わからない部分があった方は繰り返しやってみてください！
まとめ
今回は，絶対参照やMATCH関数などを使用し，計算式を整えた上でコピペすることで，エネルギー以外の成分値についても栄養計算ができるようにしました。
次回は，少し形式を整え栄養計算の合計行を追加したいと思います。
では，また次回から頑張りましょう。
次回の記事はこちら：
連載目次
- Excelで栄養計算ソフトを自作しよう！【成分表2020年版対応】
- 栄養計算ソフトのための食品成分表をExcelで作成しよう！【成分表2020年版対応】
- VLOOKUP関数で食品成分表から食品名を検索しよう！【成分表2020年版対応】
- Excelで食品成分表から食品のエネルギーを算出してみよう！【成分表2020年版対応】
- Excelで食品成分表から色々な栄養素の成分値を算出してみよう！【成分表2020年版対応】現在のページ
- SUM関数で算出した栄養素を合計してみよう！【成分表2020年版対応】
- SUMIF関数で食事区分ごとに栄養素を合計しよう！【成分表2020年版対応】
- 【完成】Excelで栄養計算ソフトを作成してみた！【成分表2020年版対応】