Excelで栄養計算ソフトを自作しよう!第7回:食事区分別に合計を表示しよう

シェアする

  • このエントリーをはてなブックマークに追加

みなさん、こんにちは。
シンノユウキです。

今回は、「Excelで栄養計算ソフトを自作しよう」の第7回で、

みなさん、こんにちは。 シンノユウキです。 今回は、「Excelで栄養計算ソフトを自作しよう」の第6回で、 こちら...

こちらの記事の続きです。

今回は、朝・昼・夕の食事区分別に、それぞれの合計を表示するということをやってみようと思います。

では、行きましょう。

前回のおさらい

まずは前回のおさらいからです。

前回は、関数を用いて、1日に摂取した栄養素の合計を算出する作業を行いました。

もちろん、これだけでも栄養計算ソフトの体裁はなしているのですが、1日分の食事の栄養計算を行う場合には、少し物足りない感があります。食事を入力する際にも、栄養計算結果を用いて栄養指導などを行う際にも、朝・昼・夕などの食事区分ごとに栄養計算できれば便利ですよね。

今回は合計を、朝・昼・夕の食事区分ごとに算出する方法をご紹介します。

食品を100程度入力できるようにしましょう

入力欄は多めに用意しておく

食事区分ごとに合計を算出する前に、まずは食品を入力する行を増やしていきましょう。

いまとのころ、食品を入力できる場所が多くありません。

そのため、とりあえず100程度の食品を入力できるように、範囲を広げていきましょう。合計行の手前に行を100行程度挿入してください。

そして、VLOOKUPなどの計算式を下方向にコピーしておきます。

100程度というと、少し多いようにも思いますが、後から減らすのは簡単ですが、増やすのは少し手間です。なので、少し多めに用意しておくのですね。

エラーの場合は空白を表示するようにする

その際、まだ食品が入力されていない場所には、

のように入力されてしまいます。食品番号が入力されていないため、VLOOKUP関数が食品名や栄養素を検索することができないために、こういった表示になっています。ただ、これは少し目障りですよね。では、こういった表示はされないようにしましょう。

方法は簡単です。

IFERRORという関数を用います。この関数を用いることで、関数の結果がエラー(食品番号や重量が未入力の場合)なら何も表示しないようにします。現在セル上にある計算式のすべてに、この関数を適用しましょう。初めに、IFERROR関数の構造を見てみます。以下のようになっています。

=IFERROR(計算式,エラーの際の表示する値)

では、この関数を実際に存在する計算式に適用してみましょう。例として、セルF4の計算式を修正してみます。以下のようになればOKです。

=IFERROR(VLOOKUP($B4,本表!$B$9:$BO$2199,MATCH(F$1,本表!$B$5:$BO$5,0),FALSE),””)

つまり、もともとセルにある計算式をIFERROR関数の第一引数とし、その計算式がエラーの際に表示する値を第二引数、””(空白)とします。こうすることで、計算式がエラーの際、つまり食品番号や重量が入力されていない場合に「#N/A」が表示されてしまう際に、そのエラー表示が目障りになってしまうという問題を解決することができます。

すこし面倒ですが、このIFERROR関数による処理をすべてのセルに行ってください。「最初からこうすればよかったじゃん!」という声もあるかもしれませんが、こういったソフトの作成の際には、一度機能を実装して、その後、細かい問題を修正していくという作業のほうが効率的に行える場合もあります。面倒ですが、がんばってください。

食事区分を入力しよう

まずは新しく列を挿入しよう

では、ここから食事区分を入力し、その食事区分ごとに合計を表示していくための作業を行います。

はじめに、食事区分を入力するための列を挿入しましょう。食品について、これは朝食の、これは昼食のと入力するための列ですね。これは、一番最初の列にある方が、なにかと便利です。そのため、一番左側に新しく列を挿入し、項目名として「食事区分」とでもしておきましょう。

このような感じですね。

入力規則を用いて入力しやすく

次に、「入力規則」という機能を用いて、食事区分を入力しやすくします。

もちろん、1つ1つのセルに「朝食」とか「夕食」とか入力していってもいいのですが、やはり手間がかかってしまいます。

今回の場合は、あらかじめ入力する値が決まっているわけですし、逆に決まった値以外を入力されていは困るわけです。そのため、入力規則という機能を用いて、食事区分を入力しやすく、また決まった値(朝食、夕食など)以外の値の入力を受け付けないように設定しましょう。

これには、「入力規則」という機能を用います。入力規則を反映したいセル範囲(食事区分を入力するセル範囲)を選択した状態で、「データ」タブの「データの入力規則」という項目をクリックしてください。

この○で囲っているところですね。

これをクリックすると、データの入力規則ダイアログが表示されるかと思いますが、それを以下のように設定してください。

設定する項目は、

  • 入力値の種類→リスト
  • 元の値→朝食,昼食,夕食,間食

です。

入力値の種類をリストにすることで、下側の元の値のところにカンマ区切りで入力した値を、リストで入力できるようになります。また、この値以外の入力があった際にはエラーを表示させることができるようにもなります。

では、この入力規則を設定したセルで、実際にリストから入力してみましょう。設定したセルを選択し、その右側に出ている矢印をクリックすることで以下のようなリストが表示されるかと思います。

このリストは、今のようにセルの右に表示される矢印をクリックすることでも表示できますが、そのセルをアクティブにした状態で、Alt+↓を入力することでも表示できます。いちいち値を入力する手間がなくなりますので、簡単に入力できるようになりましたね。

これで、食事区分をリストで入力できるようになりました。

この状態で、今入力している食品に、食事区分を入力しておいてください。これから、ここで入力した食事区分ごとに合計を表示していきます。

食事区分ごとに合計を表示しよう

では、いよいよ食事区分ごとに合計を表示していきます。

これには、SUMIF関数を用います。この関数の構造は、以下のようになっています。

=SUMIF(検索する範囲,検索条件,合計する範囲)

検索する範囲には、食事区分を入力した範囲を選択します。検索条件には、合計する食事区分を入力します。つまり、食事区分:朝食の合計を算出したい場合には、ここに朝食を入力するのですね。合計する範囲には、成文値が入力された範囲を入力します。つまり、エネルギーの合計を出したい場合は、エネルギーが入力されている範囲を入力するのですね。

と、SUMIF関数を用いる前に、合計するための列を入力しておきましょう。今回は、朝食・昼食・夕食・間食の4つの食事区分ごとに合計するため、全体の合計を表示している行の前に、新しく4つの行を挿入してください。そして、合計と入力している列の上に食事区分を入力してください。以下のような感じですね。

では、いよいよエネルギーなどの成文値を、SUMIF関数を用いて、食事区分ごとに計算してみましょう。例として、朝食におけるエネルギーの合計を算出する計算式を以下に示します。

=SUMIF($A$4:$A$100,$B101,H$4:H$100)

  • 第一引数:食事区分が入力されている範囲($A$4:$A$100)
  • 第二引数:検索する食事区分が入力されている範囲(H$4)
  • 第三引数:エネルギーの成文値が入力されている範囲(H$100)

絶対参照を上手く使っているため、昼食などの別の食事区分についても、たんぱく質などの別の成文値についても、この計算式をコピペするだけでOKです。

こんな感じですね。これで食事区分ごとに合計を算出することができました。

まとめ

今回は、食品を入力できる行を増やし、食事区分を入力するための列を新しく作成し、その食事区分ごとに合計を算出できるようになりました。

今回はここまでです。また次回から頑張りましょう。

連載目次