NPO 法人いきいきネットとくしま 第 116 回定例勉強会 森の日 2014 年 2 月 26 日 担当 : 角尾治子 メタボリック予防に活躍! 健康管理表作成 年々気になる体重や血圧 EXCEL で長期間使える健康管理表を作成しながら関数の学習をします さらに一目で変化が把握できるグラフを作成してみましょう 学習すること 1. エクセルの起動 2. データの入力 3. 名前を付けて保存 4. 日付の入力 5. 条件付書式 6. 月の最終日以降の数値の フォントを白に設定 7. 標準体重を求めます 8. BMI を求めます 9. 肥満度を求めます 10. オートフィルコピー 11. 平均値 最大値 最小値を求めます 12. グラフを作成 13. グラフの編集
エクセルの起動 エクセル (Excel) の起動をしましょう 1 左下隅 ( スタートボタン ) を 3 4 クリックします スタートメニューが表示されます 2[ すべてのプログラム ] をポイントします 3 [ 右のスクロールバーを上下に移動して [Microsoft Office] フォルダを探します 確認できましたら [Microsoft Office] フォルダをクリックして開けます 4[Microsoft Office Excel 2007] をクリックします Excel が起動して [Microsoft Excel 2007] ウィンドウが表示されます 2 1 BMI の計算式は世界共通ですが 肥満の判定基準は国により異なります BMI(Body mass index) は 身長の二乗に対する体重の比で体格を表す指数です BMI= 体重 kg /( 身長 m) 2 この BMI が男女とも 22 の時に高血圧 高脂血症 肝障害 糖尿病障害等の有病率が最も低くなるということがわかってきました BMI=22 となる理想体重が標準体重です 標準体重 = 22 ( 身長 m) 2 データの入力 今回はデスクトップの 管理表 を開きます 1
名前を付けて保存 1 [office] ボタン [ 名前を付けて保存 ] ボタンをクリックします [ 名前を付けて保存 ] ダイアログボックスが開きます 2 [ ファイル名 ] に 管理表 の前 ( 管の前 ) にカーソルを移動して 健康 を入力します 例 健康管理表 3 カーソルを末尾に移動して [ 保存 ] ボタンをクリック します タイトルバーは健康管理表のファイル名に変更されました [ 半角 / 全角キー ] を押して 日本語入力をオンにします 言語バーの入力モードが A から あ に変更されたことを確認しましょう 注 ) エクセルの起動時は 日本語入力システムはオフになっています 2
日付の入力をします 数式の入力は [ 半角 / 全角 ] キーを押して半角で入力します 1 セル [ A7] をアクティブにし [ 数式 ] タブ [ 関数ライブラリー ] グループ [ 日付 / 時刻 ] ボタン [DATE] を選択します [ 関数の引数 ] ダイアログボックスが表示されます 2 年 : セル [C3] をクリックし Tab キーを押し 月 : セル [C4] をクリックし Tab キーを押して 日 :1 を入力し [OK] ボタンをクリックします セル [ A7] に 2014/2/1 と表示されました 3
日付の表示形式の変更 1 セル [A7] をアクティブにし [ ホーム ] タブ [ 数値 ] ダイアログボックス起動ツールボタンをクリックします [ セルの書式設定 ] ダイアログボックスが表示されます 2 表示形式 タブで 自分で形式を作ることができる ユーザー定義 を選びます 3 [ 種類 ] に d を入力します サンプルが [1] になっていることを確認して [OK] ボタンをクリックします セル [A7] に 1 と表示されました 4 セル [A8] に = を入力し セル [ A7] をクリックし + 1 を入力し [Enter] キーを押します セル [A8] に 2 と表示されました 曜日の入力は TEXT 関数を使います 5 セル [ B7] をアクティブにし [ 数式 ] タブ [ 関数ライブラリー ] グループ [ 文字列操作 ] ボタン [TEXT] を選択します 4
6 [ 値 ] 枠のカーソルを確認してセル [ A7] をクリックし [Tab] キーを押して [ 表示形式 ] 枠に カーソルを移動し aaa と入力します 戻り値 [= 土 ] と表示されたことを確認して [OK] ボタンをクリックします セル [ B7] には 土 と入力されました 7 セル [ B7] をセル [ B8] にコピーします 8 セル範囲 [A8:B8] の右下隅の ( フィルハンドル ) をセル範囲 [A37:B37] までドラッグして 数式コピーします 条件付書式 土曜日のフォントを青色にします 1 セル範囲 [A7:B37] を選択して先に太字ボタンを押します 2 [ ホーム ] タブ [ スタイル ] グループ [ 条件付き書式 ] をクリックし 最下位の [ ルールの管理 ] ボタンをクリックします [ 条件付き書式ルールの管理 ] ダイアログボックスが 表示されます 3 [ 新規ルール ] ボタンをクリックします 5
[ 新しい書式ルール ] ダイアログボックスが表示されます 3 [ 数式を使用して 書式設定するセルを決定 ] をクリックします 4 次の数式を満たす 書式設定枠の中に =$B7=" 土 " と入力します 5 [ 書式 ] ボタンをクリックします =$B7=" 土 " [ セルの書式設定 ] ダイアログボックスが表示されます 6 [ フォント ] タブ [ 色 ] の選択ボックス ボタンをクリックして標準の色 [ 青 ] を選択し [OK] ボタンをクリックします [ 新しい書式ルール ] ダイアログボックスが表示されます 7 プレピューを確認して [OK] ボタンを クリックします 6
[ 条件付き書式ルールの管理 ] ダイアログボックスに戻ってきます 8 [ 適用 ] ボタンをクリックします 日曜日のフォントを 赤色 にします 1 [ 新規ルール ] ボタンをクリックします [ 新しい書式ルール ] ダイアログボックスが表示されます 2 [ 数式を使用して 書式設定するセルを決定 ] をクリックします 3 次の数式を満たす 書式設定枠の中に =$B7=" 日 " と入力します 4 [ 書式 ] ボタンをクリックします =$B7=" 日 " 7
[ セルの書式設定 ] ダイアログボックスが表示されます 5 [ フォント ] タブ [ 色 ] の選択ボックス ボタンをクリックして標準の色 [ 赤 ] を選択し [OK] ボタンをクリックします [ 新しい書式ルール ] ダイアログボックスが表示されます 6 プレピューを確認して [OK] ボタンをクリックします [ 条件付き書式ルールの管理 ] ダイアログボックスに戻ってきます 2 つの条件付き書式が設定されました 7 [ 適用 ] ボタンをクリックし [OK] ボタンをクリックします 8
月の最終日以降の数値のフォントを白に設定します 表示月の最終日以降の数値を背景色と同化させることで非表示と同じ効果になります ( ここでは 2 月以外の日付 ) が白くなり見えなくなります 1 [ 新規ルール ] ボタンをクリックします [ 新しい書式ルール ] ダイアログボックスが表示します 2 [ 数式を使用して 書式設定するセルを決定 ] をクリックします 3 次の数式を満たす 書式設定ボックスの中に =MONTH($A7)<>$C$4 と入力します 4 [ 書式 ] ボタンをクリックします =MONTH($A7)<>$C$4 左アングルブラケット 右アングルブラケット [ セルの書式設定 ] ダイアログボックスが表示されます 5 [ フォント ] タブ [ 色 ] の選択ボックス ボタンをクリックして [ 白 ] を選択し [OK] ボタンをクリックします 9
[ 新しい書式ルール ] ダイアログボックスが表示されます 6 プレピューを確認して [OK] ボタンを クリックします [ 条件付き書式ルールの管理 ] ダイアログボックスに戻ってきます 3 つの条件付き書式が設定されました 7 [ 適用 ] ボタンをクリックし [OK] ボタンをクリックします セル [C4] の月でない日付 ( ここでは 2 月以外の日付 ) は 白色になり見えなくなります 標準体重を求めます 標準体重 (Kg)= 身長 (m) の 2 乗 22 身長の 2 乗は [^2] べき乗を使います べき乗 [^] は ほかの演算よりも優先です 身長の単位は (m) です 100 で割って計算します 10
1 セル [G4] に = と入力し ( 括弧を入力して セル [G3] をクリックします 2 次に / を入力し 100 と入力して ) 括弧を閉じます (= は Shift+ ほ ) 3 べき乗 [^] は キーボードの へ を押します 続いて 2*22 と入力します (* は Shift+ け ) 4 =(B3/100)^2*22 と入力されていることを確認し [Enter] キーを押します セル [B6] には 計算結果 (59.895) が表示されました 標準との差を求めます 標準体重 - 体重 =C7-$G$4 計算結果は 7.105 と表示され ました 11
1 標準との差セル [D7] をクリックし 数式バーの先頭をクリックしてカーソルを表示します 2 名前ボックスの ボタンをクリックして [IF] 関数を選びます ( 一覧にない場合は [ その他の関数 ] をクリックして [IF] を選択します 関数の引数ダイアログボックス が表示されます 3 [ 論理式 ] のボックスにカーソルがあることを確認しセル [C7] をクリックします 4 次に = を入力し ( = は[Shift] キーを押しながら [ ほ ] のキーを押します ) を入力します ( は空白のことです ) C7= 5 [Tab] キーを押して [ 真の場合 ] にカーソルを移動し を入力します 6 数式バーで )= を選択し, カンマを入力します 7 計算式の末尾をクリックしてカーソルを移動します 12
8 [OK] ボタンをクリックします BMI( 肥満度の指数 ) を求めます 身長の 2 乗は [^2] べき乗を使います 身長の単位は (m) です 100 で割って計算します BMI= 体重 身長 2 1 セル [E7] をアクティブにし = を入力します 2 セル [C7] をセル参照し / を入力します 3 次に ( を入力しセル[G3] をクリックし [F4] キーを1 回押して /100 と入力して ) 括弧を閉じます 4 次にべき乗 [^] は へ のキーを押し 次に 2 を入力します 5 =C7/($G$3/100)^2 と入力されていることを確認して[Enter] キーを押します 計算結果は 24.60973 と 表示されました 標準との差と同じ操作で数式バーで訂正してみましょう 13
肥満度 ( 標準体重に対する割合 ) を求めます 肥満度 = 体重 (Kg) 標準体重 (Kg) 1 セル [F7] をアクティブにし = を入力します 2 セル [C4] をセル参照し / を入力します 3 つぎにセル [G4] をクリックし [F4] キーを1 回押します =C7/$G$4 と入力されたことを確認して[Enter] キーを押します 計算結果は 1.118624 と 表示されました BMI と同じ操作で数式バーで訂正してみましょう 標示形式の設定 1 セル [F7] をアクティブにします 2 [ ホーム ] タブ [ 数値 ] グループ [ パーセントスタイル ] ボタンをクリックします 112% と表示されました 14
3 セル [D7:E7] を選択し [Ctrl] キーを押して セル [G4] をクリックします 4 [ 数値 ] グループ [ 小数点以下の表示桁数を減らす ] ボタンを 2 回クリックします 下記図のように小数点以下 1 桁に設定できました オートフィルコピーをします セル範囲 [ D7:F7] の数式をセル範囲 [ D37:F37] にコピーします 1 セル範囲 [ D7:F7] を選択します 2 セル [ D7:F7] の右下隅の ( フィルハンドル ) をセル [ D37:F37] までドラッグします 15
平均値 最大値 最小値を Σ ボタンを使って求めます [ ホーム ] タブ [ 編集 ] グループ [Σ] ボタンをクリックします 平均を求めます 1 セル [D38] をアクティブにします 2 [Σ] ボタンをクリックし [ 平均 ] をクリックします 3 正しい選択範囲 ( セル [D7]~セル[D34] ) をドラッグして訂正します 点線で囲まれ セル [D38] には AVERAGE(D7:D34) と表示されます 4 [Enter] キーを押します 平均値 7.0 が表示されます 5 最大値 最小値も同じ操作で求めて BMI と肥満度にオートフィルコピーをしましょう 肥満度は % の設定をします 16
グラフを作成します 数値の増減が視覚的に把握できるように 体重 血圧 歩数の推移をグラフにしましょう 1 セル範囲 [A6:A37] を選択し [Ctrl] キーを押しながらセル範囲 [C6:C37] セル範囲 [G6:H37] をクリックします 2 [ 挿入 ] タブ [ グラフ ] グループ [ 折れ線 ] ボタンを [ マーカ付き折れ線 ] をクリックします 下記図のようにグラフが表示されました グラフの編集 2 軸を作成します 1 [1 日の歩数 ] の系列の上をクリックします 17
2 [ レイアウト ] タブ [ 現在の選択範囲 ] グループ [ 選択対象の書式設定 ] をクリックします データ系列の書式設定のダイアログボックスが表示されます 3 使用する軸を [ 第 2 軸 ] を選択して [ 閉じる ] ボタンをクリックします 左側に表示される値軸が主軸 右側に表示される値軸が 第 2 軸になります 下記図のように第 2 軸ができて系列の表示が変わりました 18
グラフタイトルの入力をします 6 [ レイアウト ] タブ [ ラベル ] グループ [ グラフタイトル ] [ グラフの上 ] をクリック します 7 グラッフタイトルが選択状態になっていることを確認します 8 数式バーに [ = ] を入力しセル範囲 [C4:D4] をドラッグで選択し [Enter] キーを押します するとグラフタイトル枠内に 2 月 が表示できました 19
8 [ デザイン ] タブ [ 場所 ] グループ [ グラフの移動 ] をクリックします 9 表示されたグラフの移動ボックスで [ 新しいシート ] を選択すると Graph1 と表示されます [OK] ボタンをクリックします Graph1 のワークシートが作成されました 折れ線のすぐ横にデータ名を表示します 1 最高血圧の赤の系列を 2 回クリックします 右端のマーカが単独で選択されます 2 [ レイアウト ] タブ [ データラベル ] [ その他のデータラベルオプション ] を選択します 20
右端のマーカの横に数値が表示され 書式設定画面の ラベルオプション が開きます 3 [ 値 ] のチェックを外して [ 系列名 ] にチェックを入れて [ 閉じる ] ボタンをクリックします 凡例と同じ ( 系列名 ) が表示され グラフが書き換わっても常に 右端マーカに表示されます 4 データラベルが選択された状態のまま [ フォント ] の色を系列の色と同じ [ 濃い赤 ] にします 5 ほかの折れ線にも同様の手順でデータ名を表示しましょう 6 フォントサイズ :12Pt 太字に設定します 7 凡例は削除します 縦軸の値を変更します 1 縦値軸をクリックします 2 [ レイアウト ] タブ [ 現在の選択範囲 ] グループ [ 選択対象の書式設定 ] をクリックします 21
[ 軸の書式設定 ] ダイアログボックスが表示されます 軸のオプション [ 最小値 ] の [ 固定 ] の選択ボタンをクリックして 30 に設定し [ 閉じる ] ボタンをクリックします プロットエリアを中方向に少し狭くして見栄えよく配置します グラフの完成図です 22