Excel BMI判定をVLOOKUP関数で行う方法

この記事ではVLOOKUP関数を用いて

BMI(ボディマスインデックス)の

判定を行う方法を紹介します。

高校生(16歳)以上、小中学生(15歳まで)、

乳幼児(5歳まで)の3基準で判定を行います。

基準は厚生労働省や日本肥満学会の

定義を使用します。

手順

サンプルファイル

こちらをダウンロードすれば

そのまま利用可能です。

高校生(16歳)以上シート

高校生(16歳)以上のシートの

C3セルに身長をセンチメートル単位、

C4セルに体重をキログラム単位で

入力すればC5セルにBMI、

C6セルの状態が表示されます。

高校生(16歳)以上のシートのサンプル
BMI状態
18.5未満低体重(痩せ型)
18.5以上、25未満普通体重
25以上、30未満肥満(1度)
30以上、35未満肥満(2度)
35以上、40未満肥満(3度)
40以上肥満(4度)

小中学生(15歳まで)シート

小中学生(15歳まで)シートは

BMIでなくローレル指数を用いますが

使い方はBMIと同様です。

C3セルに身長をセンチメートル単位、

C4セルに体重をキログラム単位で

入力すればC5セルにローレル指数、

C6セルの状態が表示されます。

小中学生(15歳まで)シートのサンプル
指標状態
100未満痩せすぎ
100〜115未満痩せ
115〜145未満ふつう
145〜160未満肥満
160以上太りすぎ

乳幼児(5歳まで)シート

乳幼児の場合はまたBMIになります。

ただし年齢によって基準が異なるので

まずC3セルで年齢を選択します。

年齢選択

そしてC4セルに身長、C5セルに体重を

入力するとC6セルにBMIと

状態が自動計算されます。

乳幼児(5歳まで)シートサンプル
年齢指数状態
3ヵ月から1歳未満16未満痩せ
 16~18未満ふつう
 18以上肥満
1歳から1歳6ケ月未満15.5未満痩せ
 15.5~17.5未満ふつう
 17.5以上肥満
1歳6ケ月から3歳未満15未満痩せ
 15~17未満ふつう
 17以上肥満
3歳から5歳まで14.5未満痩せ
 14.5~16.5未満ふつう
 16.5以上肥満

数式解説

サンプルファイルの数式を解説します。

利用するだけで十分な方は

読み飛ばして頂いて問題ありません。

高校生(16歳)以上の数式

BMIを下記の数式で計算します。

体重(Kg)/身長(M)^2

サンプルでは身長をセンチメートルで

入力しているので数式で100を割り

メートルに調整しています。

BMI計算の数式

次に算出したBMIから状態を取得します。

VLOOKUP(BMI,基準表の範囲,状態の列番号,TRUE)
BMIから状態を取得するVLOOKUP関数

4番目の引数にTRUEは滅多に使いませんが、

これがどういう動きをするかというと

検索値以下の中で最大の値を

取得する指定です。

これによりBMIの基準に該当する

状態が取得することが可能です。

小中学生(15歳まで)の数式

こちらは高校生以上と同等ですが

指標の計算が異なりBMIではなく

ローレル指数を用います。

体重(Kg)/(身長(M)^3)*10

サンプルでは身長をセンチメートルで

入力しているので数式で100を割り

メートルに調整しています。

ローレル指数の数式

乳幼児(5歳まで)の数式

乳幼児はBMIを用いますが、

判定が年齢ごとに異なります。

まずC3セルにプルダウンを設定します。

C3セルに「データ」より

「入力規則」を選択します。

「データ」より「入力規則」を選択

「入力値の種類」を「リスト」に変更し

元の値に選択肢を設定します。

3ヵ月から1歳未満,1歳から1歳6ケ月未満,1歳6ケ月から3歳未満,3歳から5歳まで

これによりセルにプルダウンを設定可能です。

データの入力規則画面

次に指標の表に範囲名を設定します。

下のように年齢ごとに設定します。

先頭に_は範囲名の規定上、

必要なため付与しています。

指標の範囲名

なお範囲名は数式バーの左にある

名前ボックスで指定します。

範囲名の設定

BMIは高校生以上と同様に計算しますが、

VLOOKUP関数の指定が少し異なります。

異なる個所は引数2で高校生以上では

セル範囲をそのまま指定していましたが

ここではINDIRECT関数を指定します。

INDEIRECT関数は文字列で

セル範囲を指定する関数です。

このようにセルの文字列から

セル範囲に変換するために使用します。

INDIRECT("_"&年齢)

アンダースコアを結合するのは

セル範囲名に合わせるためです。

乳幼児のVLOOKUP関数の設定

これにより年齢の選択によって

VLOOKUP関数の範囲が切り替わり

指数の判定に反映させることが可能です。

関連記事

息抜きぬこ動画

VLOOKUP関数の使い方・活用方法の記事一覧

文字列による柔軟なセル指定を行う(INDIRECT関数)

セルの入力値と入力規則のプルダウンメニュー(ドロップダウンリスト)を連動させる方法

セル範囲に名前を定義。設定と編集・削除方法

フォローする