わえなび ワード&エクセル問題集

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

【Excel】BMI指数と肥満度の判定、標準体重比、美容体重・モデル体重の計算

統計的に人間が病気になりにくく、最も健康的に生活ができることが認められる体重のことを標準体重または理想体重といい、健康やダイエットのために体重を毎日計測して管理している人も多いようです。

標準体重や肥満度を計算する指標はいくつかありますが、その中でBMI指数は世界で広く用いられています。身長と体重だけで計算することができ、Excelでも掛け算と割り算と累乗だけで簡単に計算できます。しかし、計算したBMIから肥満度を表示するには判定するための関数が必要となります。

そこで、今回は、ExcelでBMI指数を計算する方法と体重管理表の作成方法について解説します。

 

目次

1.BMIの求め方

(1)身長と体重からBMIを求める

BMI指数(ボディマスインデックス)は、1835年にベルギーの統計学者アドルフ・ケトレーによってはじめて検討された計算方法で、大人の身長と理想体重の関係を表す体格指数です。計算方法は世界共通であり、体重(kg)を身長(m)の2乗で割って求めます。このとき、身長の単位がメートルであることに注意します。

  • BMI=体重(kg)/身長(m)^2

体脂肪率や筋肉量を考慮していないため肥満度を完全に算出できるものではありませんが、体脂肪との相関が高いことなどから、肥満度の判定基準として使われています(世界保健機関など)。なお、中学生以下の場合はBMIではなくローレル指数を使います。

 

(2)身長とBMIから目標体重を求める

BMI法では統計学的にBMI=22のときに最も病気にかかりにくいものとされています。この時の体重が標準体重であり、身長の2乗にBMIを掛けます。

  • 体重(kg)=身長(m)^2*BMI
  • 標準体重(kg)=身長(m)^2*22

 

(3)掛け算・割り算・累乗の基本

ExcelでBMIを計算するには四則演算と累乗を使います。関数は使いません。なお、掛け算、割り算、累乗の計算方法についてはこちらの記事をご覧ください。

 

2.cmをmに換算する

問題

(1)セルB1に身長(m)、セルB2に体重(kg)を入力した。セルB3にBMIを表示しなさい。
(2)セルB5に身長(cm)、セルB6に体重(kg)を入力した。セルB6にBMIを表示しなさい。

f:id:waenavi:20191108083833j:plain

 

解説

BMIを計算するときの単位は、メートルとキログラムです。身長と体重をそれぞれm、kgで入力した場合はそのままBMIの式に当てはめることができます。体重を身長の2乗で割ります。

  • =B2/B1^2

f:id:waenavi:20191108084056j:plain

 

日本では身長をcm(センチメートル)で表すことが多いです。身長をcmで入力した場合、100で割る必要があります。

  • =B6/(B5/100)^2

f:id:waenavi:20191108084139j:plain

 

3.小数第2位を四捨五入する

問題

計算したBMI値の小数第2位を四捨五入して、小数第1位までの表示にしなさい。

f:id:waenavi:20191108084236j:plain

 

解説

小数点以下の桁数を変えるには「小数点以下の表示桁数を上げる」または「小数点以下の表示桁数を下げる」のボタンを使います。いずれかのボタンを押すことによって小数点以下1桁になるように調節します。

f:id:waenavi:20191108084340j:plain

 

20.9となります。小数第2位以下は自動的に四捨五入になっています。

f:id:waenavi:20191108084410j:plain

 

*補足* 本当はROUND関数で四捨五入の処理をするべきですが、BMIはあくまで目安なのでそこまで厳密な計算をする必要はありません。

 

4.標準体重の計算

問題

現在の身長と体重を入力した。BMI法の標準体重を求めなさい(小数第1位までの表示)。また、現在との体重の差を求めなさい。

f:id:waenavi:20191108084536j:plain

 

解説

体重は身長の2乗にBMIを掛けることで求められます。

  • 体重(kg)=身長(m)^2*BMI

前述のとおり、BMI法では、BMI=22のときに統計的に病気になる可能性が最も低いとされ、理想的な体重と考えます。そこで、標準体重を身長の2乗の22倍とします。なお、身長はメートルで計算するので、cmで入力した時は100で割ります。

  • 標準体重(kg)=(身長(cm)/100)^2*22

したがって、「=(B1/100)^2*22」となります。

f:id:waenavi:20191108084800j:plain

 

小数第1位までの表示にします。

f:id:waenavi:20191108084814j:plain

 

現在の体重から標準体重を引きます(=B2-B3)。小数第1位までの表示にします(以下、省略)。この数値がプラスであれば標準体重より多く、マイナスであれば標準体重より少ないということです。

f:id:waenavi:20191108084836j:plain

 

5.肥満の判定基準

問題

現在の身長と肥満の判定基準表を入力した。BMI法に基づいてそれぞれの体重を計算しようとしている。この入力方法の問題点を指摘しなさい。

f:id:waenavi:20191108085013j:plain

 

解説

一般社団法人日本肥満学会が、BMI=22を標準とし、25以上を肥満とする肥満度の判定基準を定めています(後述)。ちなみに、肥満3度と肥満4度が危険らしいです。判定基準の表を入力しましたが、このような入力方法では体重を計算することができません。

f:id:waenavi:20191108085136j:plain

 

Excelでは、セルの中にある数値と日本語を別のセルに分けて入力しなければなりません(1セル1情報の原則、参考:【神Excel】8個の基本パターンで完全習得「リスト形式」の教科書)。例えば、次のように入力します。

f:id:waenavi:20191108085346j:plain

 

体重=身長^2*BMIなので「=($B$1/100)^2*E2」となります。身長は絶対参照です。

f:id:waenavi:20191108085432j:plain

f:id:waenavi:20191108085435j:plain

 

G4を参照します。

f:id:waenavi:20191108085512j:plain

 

これで完成です。

f:id:waenavi:20191108085539j:plain

 

6.肥満度の判定(VLOOKUP関数)

問題

現在の身長と体重、肥満の判定基準表を入力した。BMI法に基づいて肥満度の判定をしようとしている。この入力方法の問題点を指摘しなさい。

f:id:waenavi:20191108085751j:plain

 

解説

上記の日本肥満学会の判定基準では、BMIを計算することによって肥満(1度~4度)・普通体重・低体重を判定することができます。しかし、さきほどの問題と同様、判定するには判定表のBMIが「数値」でなければなりません。

f:id:waenavi:20191108085945j:plain

 

例えば、次のように入力します。VLOOKUP関数を使用するため、BMI値と体重は左側に、判定結果を右側に入力したほうが良いです。

f:id:waenavi:20191108090218j:plain

 

また、未満を使用せず「以上」で統一します。

f:id:waenavi:20191108090233j:plain

 

それぞれの体重を計算します。

  • =($B$1/100)^2*E2

f:id:waenavi:20191108090318j:plain

 

現在の体重(セルB2)を検索値として、体重と判定(G~I列)の範囲で検索します。なお、VLOOKUP関数の第4引数は省略できます。

  • =VLOOKUP(B2,G2:I7,3)

f:id:waenavi:20191108090358j:plain

 

別解

BMIから直接判定することもできます。この場合の検索値はBMI(B2/(B1/100)^2)です。

  • =VLOOKUP(B2/(B1/100)^2,E2:G7,3)

f:id:waenavi:20191108090837j:plain

 

7.栄養不良の判定・標準体重比(%IBW)

問題

現在の身長と体重から標準体重、標準体重比、栄養不良の判定を求めなさい。

f:id:waenavi:20191108091015j:plain

 

解説

日本呼吸器学会によると、標準体重(BMI=22)と比較して90%未満の体重(BMIでは19.8未満)の場合、栄養障害の疑いがあり、さらに体重が少ないと呼吸不全が進行しやすくなるそうです(後述)。この標準体重に対するパーセンテージのことを標準体重比(%IBW)と言います。

  • 標準体重比=現在の体重/標準体重

パーセントで判定するので、パーセントを左側に、判定結果を右側に入力します。

f:id:waenavi:20191108091220j:plain

 

標準体重はBMI=22なので、身長^2*22です。

  • =(B1/100)^2*22

f:id:waenavi:20191108091535j:plain

 

標準体重比は、体重/標準体重です。

  • =B2/B3

f:id:waenavi:20191108091633j:plain

 

パーセンテージにします。

f:id:waenavi:20191108091611j:plain

 

標準体重比(セルB4)を検索値として、判定表(E~G列)の範囲で検索します。なお、VLOOKUP関数の第4引数は省略できます。

  • =VLOOKUP(B4,E2:G6,3)

f:id:waenavi:20191108091723j:plain

 

8.適正体重・美容体重・シンデレラ体重・モデル体重の計算

問題

身長をcmで入力した。セル範囲B4:B8のBMI値の場合の体重を求めなさい。

f:id:waenavi:20191108092549j:plain

 

解説

女性誌や広告でダイエットを煽る目的で、美容体重、シンデレラ体重、2018年版シンデレラ体重、モデル体重など、科学的に根拠のない指標が使われているようです。前述のとおりBMIは病気のかかりにくさを表した数値なので、美容や外見、スタイルとは全く関係ありません(美容業界の人たちに怒られそうですが統計学的な事実です)。

体重=身長^2*BMIなので「=($B$1/100)^2*B4」となります。身長は絶対参照です。

f:id:waenavi:20191108092743j:plain

 

上記の判定基準によると、BMI=19.8未満が栄養不良、BMI=18.5未満が低体重という判定になるので、2018年シンデレラ体重(BMI=18)、モデル体重(BMI=17)などは健康上問題のある数値と言えます。

 

9.総合問題演習(体重管理表の作成)

問題

(1)セルB3に現在の身長を入力した。セルB4に標準体重を求めなさい。
(2)セルB7以降に体重を記録した。BMI、標準体重との差、判定を求めなさい。
(3)体重の変化をマーカー付き折れ線グラフで表しなさい。また、標準体重を表す基準線を追加しなさい。

f:id:waenavi:20191108092942j:plain

 

解説

標準体重は、身長^2*22です。

  • =(B3/100)^2*22

f:id:waenavi:20191108093218j:plain

 

BMIは、体重/身長^2です。

  • =B7/($B$3/100)^2

f:id:waenavi:20191108093248j:plain

 

なお、体重を入力していない場合に空白にするにはIF関数を使います(以下、同じ)。

  • =IF(B7="","",B7/($B$3/100)^2)

f:id:waenavi:20191108093317j:plain

 

標準体重との差は、体重-標準体重です。

  • =B7-$B$4

f:id:waenavi:20191108093340j:plain

 

判定はVLOOKUP関数です。なお、判定表G7:I12は絶対参照です。

  • =VLOOKUP(C7,$G$7:$I$12,3)

f:id:waenavi:20191108093422j:plain

 

日付と体重を選択してマーカー付き折れ線グラフを挿入します(参考:【Excel】グラフの基本操作、挿入と削除のトレーニング、作成範囲と印刷範囲)。

f:id:waenavi:20191108093537j:plain

f:id:waenavi:20191108093938j:plain

 

基準線を追加するには基準線を表示するための列が必要です(参考:【Excelグラフ】縦または横に基準線や平均値を表す線を追加する方法)。標準体重を絶対参照で参照します。

  • =$B$4

f:id:waenavi:20191108093805j:plain

 

この列をコピーします。

f:id:waenavi:20191108093850j:plain

 

グラフを選択して貼り付けます。

f:id:waenavi:20191108094006j:plain

f:id:waenavi:20191108094056j:plain

 

マーカーをなしにします。

f:id:waenavi:20191108094135j:plain

 

これで完成です。

f:id:waenavi:20191108094244j:plain

 

10.平均身長・平均体重

問題

厚生労働省が公開している身長・体重の平均値のエクセルデータを取得しなさい。

解説

厚生労働省では、健康増進法に基づく国民健康・栄養調査を毎年実施しています。その結果は「xls形式の統計表」としてe-statで公開されています。

厚生労働省・国民健康・栄養調査https://www.mhlw.go.jp/bunya/kenkou/kenkou_eiyou_chousa.html

f:id:waenavi:20191108094328j:plain

 

このなかに「身長・体重の平均値」という項目があり、Excelデータをダウンロードすることができます。

f:id:waenavi:20191108094459j:plain

f:id:waenavi:20191108094548j:plain

 

11.出典&参考文献

当サイトでは計算結果や判定についての責任を負いません。また、体重や健康について気になることがあったら病院に行って医師に相談してください(※内科か総合診療科か肥満外来です)。

日本肥満学会・肥満症診療ガイドライン2016
http://www.jasso.or.jp/contents/magazine/journal.html
http://www.jasso.or.jp/data/magazine/pdf/chart_A.pdf

日本呼吸器学会・COPD(慢性閉塞性肺疾患)診断と治療のためのガイドライン
https://www.jrs.or.jp/modules/guidelines/index.php?content_id=1

厚生労働省・国民健康・栄養調査
https://www.mhlw.go.jp/bunya/kenkou/kenkou_eiyou_chousa.html

 


解説は以上です。


 


Copyright(C)2018-2019 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]