みなさん、こんにちは。
シンノユウキです。
今回は、「Excelで栄養計算ソフトを自作しよう」の第５回で、
こちらの記事の続きです。
前回は、食品番号からエネルギーを検索・表示して、それを重量に応じて計算し、算出しました。今回は、それと同様のことをエネルギー以外の成分値についても行っていきます。いよいよ栄養計算ソフトらしくなってきましたね。
やり方は非常に簡単で、エネルギーの算出に用いた数式を上手くコピペすることでこれを実現できます。
では、行きましょう。
目次
前回のおさらい
まずは前回のおさらいからです。
前回はVLOOKUP関数を用いて、食品番号から、食品のエネルギーを検索・表示し、それを重量に乗じることで、エネルギーの算出を行いました。
計算式は以下のようなものでしたね。
=VLOOKUP(A4,本表!B9:BO2199,5,FALSE) / 100 * D4
これを、「栄養計算シート」のセルF4に記述しました。
VLOOKUP関数で、成分値の含まれる範囲（本表!B9:BO2199）から食品番号（セルA4）を検索し、その食品番号のある行の、エネルギーのある列（5）に存在する値（食品のエネルギー値）を算出。それを100で割り1gあたりのエネルギーに換算した後に重量（セルD4）で乗じる。こうすることで食品のエネルギーを算出することができました。
今回はその続きからです。
コピペで他の成文値も算出しよう
ここからは、エネルギーと同様に他の成分値についても算出していくのですが、算出する原理はエネルギーの場合同じです。すなわち、食品番号から値を計算・表示し、重量を乗じて算出するのです。そのため、エネルギーの算出に用いた計算式を上手く活用すれば、他の成分値も算出することができます。
ただし、単純なコピペではNG!
しかし、単純にコピペするだけではその目的は達成できません。
少し工夫する必要があります。
まずは、悪い例として、単純にコピペした場合を見てみましょう。
エネルギー（kcal）の数式を、エネルギー（kJ）のセルにコピペしてみましょう。エラーが出て上手くいかないことがわかります。
エラーの原因を調べるために、数式の参照している範囲を見てみると、以下のようになっていることがわかります。
食品番号のあるセル、A4を検索しているはずが、B4になっていますし、重量が存在するはずのセル、D4がE4になってしまっています。そして、よく見ると、成分値の範囲である本表!B9:BO2199も一列右にずれてしまっていることがわかります。
なぜこういったことが起こるのでしょうか？
セル番地が意図しない方向に移動してしまう
これは、Excelにおけるセルの参照が、デフォルトの設定では相対参照であることに関係しています。
これは、Excelでは絶対的なセル番地を参照しているのではなく、計算式を入力セルからみた相対的な位置で参照しているということを意味しています。
今回のエネルギーを算出するための数式では、いくつかの項目で、セルの参照を行っています。食品番号のセルを意味するA4などですね。こういったセルの参照は、A4という絶対的なアドレスにあるセルとして参照しているのではなく、数式を入力したセルからみて相対的にどこに位置するかという風に参照してます。セルA4を例にとると、数式を入力した場所から左に５番目に位置するセルという風に相対的な位置として参照しているのですね。
そのため、今回の例のようにセルを一列右にコピペした場合には、相対的な位置として参照しているセルたちも、一列右に移動してしまうというわけなのです。
絶対参照を使いこなそう
では、どうすればよいのでしょうか。この答えは単純で、相対参照のセルを、絶対参照にしてしまえば良いのです。
今回の場合、ずれてしまっては困る、つまり絶対参照にしたいのは、以下の範囲です。
- A4（食品番号）
- 本表!B9:BO2199（成分表の範囲）
- D4（重量）
これらを絶対参照に変更していきます。
絶対参照に変更する方法は簡単です。セルアドレスの前に「$」をつければ良いのです。A4セルを例にすると、列を固定したい場合、つまりコピペした際に、列を右や左にずらしたくない場合は、列名の前に（例：$A4）、行を固定したい、つまり行を上や下にずらしたくない場合は行番号の前に（例：A$4）、列も行も固定したい場合は列名と行番号の両方に（例：$A$4）つければよいのです。こうすることで、相対参照が絶対参照になるため、コピペしても参照しているセルの範囲が移動することはありません。
相対参照を上手く使用した計算式が以下です。
=VLOOKUP($A4,本表!$B$9:$BO$2199,5,FALSE) / 100 * $D4
食品番号を示すセルA4は、列の方向（右方向）にずれてしまっては困るので、列名の前に$を付けました。また、今後、この計算式を下方向にもコピペすることを考えると、逆に、行方向にずれるのは構わないわけですよね。そのため、行番号を示す4は相対参照のままとしました。
成分表の範囲を示す本表!B9:BO2199は、列方向にも行方向にもずれてしまっては困ります。この範囲から少しでもずれてしまっては困るのですね。そのため、列名の前にも、行番号の前にも＄を付けています。
重量を示すセルD4は、食品番号と同様、列方向にずれてしまっては困りますが、行方向にずれるのは逆にOKです。そのため列名の前にのみ$を付けています。
以上が相対参照の説明です。こうすることで、計算式をコピペした際に指定しているセルがずれるという問題は解消することができました。
しかし、まだ問題は残っています。VLOOKUP関数中で、表示する列番号を示す「5」については、変更する必要があります。
しかし、5をいちいち6とか7とかに一つ一つ変更していくのは手間ですよね。次はその問題の解消法について説明します。
MATCH関数で複数の成分値に対応しよう
列番号が変更できないという問題を解消するために、MATCH関数を使いましょう。この関数は特定の範囲から特定の値を検索し、それが何番目に位置するかを返す関数です。
これで、表示したい成分名を検索し、それが成分表の範囲の何列目に位置するのかを算出します。
初めに計算式からお示しすると、以下のようになります。
=VLOOKUP($A4,本表!$B$9:$BO$2199,MATCH(F$2,本表!$B$6:$BO$6,0),FALSE) / 100 * $D4
黄色で塗りつぶした部分が、今回変更した部分です。セルF2は成分値名が表示されている行の、エネルギーの項目を示しています。これを本表!$B$6:$BO$6から検索します。こうすることで、計算式をコピペした場合でも、列番号が自動的に変更されるようになるというわけなのです。
この計算式に関しても、絶対参照を上手く使用しています。
成分値名が入力されているセルF2は、コピペの際に、列方向にずれるのはOKです。むしろそうでなくてはなりません。しかし、行方向にずれてしまっては困ります。そのため、行番号の前にだけ$を付けています。
検索範囲である本表!B6:BO6は、列方向にも行方向にもずれてしまっては困ります。そのため、両方に$を付けています。
コピペして栄養計算してみよう
ここまでで、実際に用いる数式が完成しました。
では、これを他のセルにもコピペしてみましょう。
横方向に一気にコピペして、下方向にも適当に10行くらいコピペしてみましょう。
その際には、Excelのオートフィル機能を用いると便利です。
ここをクリックして、
クリックしたまま右に引っ張る。
そうすると、こうなります。
これを一番右までおこなって、同じように下方向にもコピペすると、以下のようになります。
同様に、食品名を入力する列の下にもコピペしましょう。
その際、計算式は以下のように変更してください。
=VLOOKUP(A4,本表!$B$9:$BO$2199,3,FALSE)
下方向にコピペするにあたって、いくつかの部分を絶対参照に変更しました。
では、試しに、2つ目の行（5行目）にも食品番号と重量を入力して栄養計算してみましょう。
計算できましたでしょうか。
今回は少し難しく見える項目もあったかもしれませんが、じっくり時間をかけてやってみると大したことはありません。頑張りましょう。
まとめ
今回は、絶対参照やMATCH関数などを用いることで計算式を整え、その計算式をコピペすることで、エネルギー以外の成分値についても、栄養計算ができるようにしました。
次回は、少し形式を整え、栄養計算の合計を示す合計行を追加していきます。
では、また次回から頑張りましょう。
