Excel2013で1日2回測定の体重の折れ線グラフを作る

 4月9日の拙ブログ「ダイエットのために体重推移グラフを作ろう−初心者のためのOffice講座」で

『「初心者のためのOffice講座」(はまちゃんらんど)はあまり面白くないので今一なので俺流」で作る』
と言ったが‥‥ 言い過ぎだった。ごめんなさい。
逆に、切っ掛けをもらい、「初心者のためのOffice講座」の記事を参考に、次のようなグラフを作った。

§1.朝と夜および平均の高低線折れ線グラフ

◆入力データ

 入力データは「初心者のためのOffice講座」より極力引用した。見えないデータは適当に作った。

日付 平均
3/6 54.3 54.4
3/7 53.7 54.3
3/8 53.8
3/9 53.3
3/10 53.5 53.4
3/11
3/12 53.1 52.7
3/13 52.4 53.4
3/14 52.2 52.5
3/15 52.4 53.7
3/16 52.7 52.8
3/17 52.4 52.9
3/18 51.9 52.5
3/19 52.1 52.7
3/20 52 53.1
3/21 52.5 52.6
3/22 52 52.6
3/23 53
3/24 51.8
3/25 51.6
3/26 51.4
3/27 51.6
3/28 51.9
3/29 52.1
3/30 51.9
3/31 51.9
4/1 51.7
4/2 51.7
4/3 51.6
4/4 52
4/5 52

 上記をエクセルにコピペした。

◆入力データを完成させる
  • 平均値を計算する
    • 基本的には「初心者のためのOffice講座」と同じで、例えばセルD2は
      =IF(COUNT(B2:C2)=0,NA(),AVERAGE(B2:C2))
      とした。
  • 条件付き書式を使ってエラーを見えなくする
    • 「#N/A」を見えなくする‥‥ は行わなかった。
    • ∵ 原因を作ったのは自分。「隠す前にすることがあるんじゃないの?」
  • 曜日の表示
    • 書式/ユーザー定義を “gee/mm/dd(aaa)” とし、
    • [条件付き書式]で
      • A列 を対象に、
      • 日曜日だったら、セルを薄いピンクで塗りつぶした。(右図)
◆折れ線ブラフ
  • 高低線
    1. グラフエリアを選択する。
    2. デザイン・タブ/グラフのレイアウトの[グラフ要素を追加]⇒ 線 ⇒ 高低線 を選択する。
    3. 朝と夜の折れ線は、書式設定 ⇒ 塗りつぶしと線 ⇒ 線 で、◉線なし とした。
  • グラフの炭を丸くする
    • グラフを新しいシートに移動させると無効になる。

§2.月別平均体重推移

◆入力データ

 元データは§1.と全く同じ。今回は入力データから直接グラフは作らないので、シートを見出しを「入力データ」とした。

◆グラフデータ … シート見出し

 今回は入力データからグラフを作る表(グラフデータ)を作成する。

  1. 項目名
    • セルA1:日
    • セルB1:“3/1”(3月1日)を入力し、書式/ユーザー定義を“ge年m月”とする。(任意)
    • セルC1:“4/1”(4月1日)を入力し、書式設定はセルB1と同じ。
  2. 入力
    • A列:
      • セルA2〜A32まで、1から始まる連続データを入力する。
      • セルA2〜A32の書式設定/ユーザー定義を “0日” とする。
    • B列:
      1. セルB2に
        “=VLOOKUP(DATE(YEAR(B$1),MONTH(B$1),$A2),入力データ!$A$2:$D$395,4,FALSE)”
        を入力(関数のExcel ヘルプは後述)し、
      2. セルB32まで(3月は大の月のため)コピーする。
    • C列:
      1. セルB2の数式をセルC2にコピーする。
      2. セルC2の数式をセルC31まで(4月は小の月のため)コピーする。
◆折れ線ブラフの作成
  • 下降線を入れるには
    1. グラフエリアを選択する。
    2. デザイン・タブ/グラフのレイアウトの[グラフ要素を追加]⇒ 線 ⇒ 下降線 を選択する。
  • 今回は3〜4月分までのグラフに対応している。5月分に対応するにはD列を作る必要がある。
Excel ヘルプ
  • 数式:=VLOOKUP(DATE(YEAR(B$1),MONTH(B$1),$A2),入力データ!$A$2:$D$395,4,FALSE)
     説明
    1. セルB1の年,セルB1の月およびセルA2の値(日)から日付(シリアル値)を作り、
    2. この日付に等しい「入力データ」の日付を検索する。
    3. 一致するものがあれば「平均」の値(4列目)を返す。
       一致するものが無ければ #N/A となる。

      • 入力データシートの日付(A列)は昇順に並べて置く必要がある。
      • 上記数式では、検索の範囲は セルA2〜A395 となっている。
         これは、1年間分のデータに対応したため。

  • VLOOKUP関数のヘルプ
    VLOOKUP関数
     表や範囲から行ごとに数値や文字列などを検索するには、検索/行列関数の 1 つ、VLOOKUP を使用します。たとえば、部品番号によって自動車部品の価格を検索できます。
    その最も簡単な形式で、VLOOKUP 関数は次のようになります。

    = VLOOKUP (検索する値, 値を検索する範囲, 戻り値を含む範囲の列の番号, 完全一致か近似一致か - 0/FALSE か 1/TRUE で指定)。

  • DATE関数のヘルプ
    DATE 関数
     3 つの独立した値を受け取り、それらを組み合わせて日付を作成する必要がある場合は、Excel の DATE 関数を使います。
    技術的詳細
     DATE 関数は、特定の日付を表す連続したシリアル値を返します。

    書式: DATE(年,月,日)

    DATE 関数の書式には、次の引数があります。
    •  必ず指定します。年引数には、1 〜 4 桁で年を指定します。年引数の解釈は、コンピューターで使用されている日付システムによって異なります。WindowsMicrosoft Excel の標準では 1900 年日付システムが使われます。つまり、最初の日付は 1900 年 1 月 1 日です。

      ヒント: 不適切な結果が生成されるのを防ぐため、年引数には 4 桁の数値を使用してください。たとえば、"07" を使用すると、年の値として "1907" または "2007" が返されます。4 桁の年を使用すれば混乱が防げます。
      • の値が 0 から 1899 の範囲の場合、1900 を加えた値が実際の年になります。たとえば、DATE(108,1,2) は 2008 (1900+108) 年 1 月 2 日を返します。
      • の値が 1900 から 9999 の範囲の場合、その値が実際の年になります。たとえば、DATE(2008,1,2) は 2008 年 1 月 2 日を返します。
      • の値が負の値または 10000 以上の場合、エラー値 #NUM! が返されます。
    •  必ず指定します。月を表す正または負の整数を指定します。ただし、返される値の範囲は 1 〜 12 (1 月から 12 月) になります。
      • に 12 より大きい数値を指定すると、指定した年の最初の月に、月引数を加えた月を指定したと見なされます。たとえば、DATE(2008,14,2) は 2009 年 2 月 2 日を表すシリアル値を返します。
      • に 1 より小さい数値を指定すると、指定した年の最初の月から、月引数の絶対値に 1 を加えた月数を減算した月を指定したと見なされます。たとえば、DATE(2008,-3,2) は 2007 年 9 月 2 日を表すシリアル値を返します。
    •  必ず指定します。日を表す正または負の整数を指定します。ただし、返される値の範囲は 1 〜 31 になります。
      • 指定した月の最終日より大きい数値を日に指定すると、その月の最初の日に日引数を加えた日を指定したと見なされます。たとえば、DATE(2008,1,35) は 2008 年 2 月 4 日を表すシリアル値を返します。
      • に 1 より小さい数値を指定すると、指定した月の最初の日から、日引数の絶対値に 1 を加えた日数を減算した日を指定したと見なされます。たとえば、DATE(2008,1,-15) は 2007 年 12 月 16 日を表すシリアル値を返します。

追伸(2018.04.28)

  1. §1.のグラフと§2.のグラフは別の Excel ファイルで作った。
     一方『§2.の入力データは§1.と同じ』とも書いた。それならば1っの Excel ファイルで作くれるはず‥‥ なので、1っにまとめた。(Excelで体重管理.xlsx)
  2. 月内の入力データが増えると、§1.の「グラフデータの範囲」と§2.のグラフデータの「VLookup関数の範囲」を変える必要がある。特に後者は、気が使かないかも...
     そこで、
    • 入力データの範囲に「データ範囲」という名前にし、マクロ(VBA)で変更出来るようにした。(Excel体重管理.xlsm)
    • 今後の参考(修正を含む)にするため、下記に載せた。
  3. 上記2っのファイルは、圧縮し、まりふのひとの公開OneDrive\ZipLibに「Excelで体重管理」にアップする。
※ 参考)VBAコード
  1. Sub データ範囲の変更()
  2. Const conNamae As String = "データ範囲"
  3. Dim rang As Range
  4. Dim nam As Name
  5. Dim lastRow As Long
  6.  
  7. lastRow = Cells(Rows.Count, 1).End(xlUp).Row
  8. 'グラフ1のデータ範囲を変更する。
  9. ActiveSheet.ChartObjects(1).Chart.SetSourceData Range("A2:D" & lastRow)
  10.  
  11. '名前:データ範囲(月別平均体重グラフのデータ範囲)を変更する。
  12. With ActiveWorkbook
  13. ' 2行目以降を範囲名「範囲2」にする
  14. Set rang = .ActiveSheet.Range("A2:D" & lastRow)
  15.  
  16. On Error Resume Next    'エラー時には次の処理へ
  17. Set nam = .Names(conNamae)
  18. '名前:データ範囲があれば、一旦、落とす。(範囲を変更するコードが上手く動かなかったため)
  19. If Err = 0 Then .Names(conNamae).Delete
  20. '名前:データ範囲を定義する。
  21. .Names.Add Name:=conNamae, RefersTo:="=入力データ!" & rang.Address
  22. nam.Visible = True
  23. Err.Clear
  24. End With
  25.  
  26. End Sub