まりふのひと

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

Excel VBA

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

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

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

◆入力データ

　入力データは「初心者のための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. 朝と夜の折れ線は、書式設定 ⇒ 塗りつぶしと線 ⇒ 線 で、◉線なし とした。
  • グラフの炭を丸くする
    • グラフを新しいシートに移動させると無効になる。

§２．月別平均体重推移

◆入力データ

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

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

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

  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 桁で年を指定します。年引数の解釈は、コンピューターで使用されている日付システムによって異なります。Windows 版 Microsoft 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. §１．のグラフと§２．のグラフは別の Excel ファイルで作った。
    　一方『§２．の入力データは§１．と同じ』とも書いた。それならば１っの Excel ファイルで作くれるはず‥‥ なので、１っにまとめた。（Excelで体重管理.xlsx）
  2. 月内の入力データが増えると、§１．の「グラフデータの範囲」と§２．のグラフデータの「VLookup関数の範囲」を変える必要がある。特に後者は、気が使かないかも...
    　そこで、
    • 入力データの範囲に「データ範囲」という名前にし、マクロ（VBA）で変更出来るようにした。（Excel体重管理.xlsm）
    • 今後の参考（修正を含む）にするため、下記に載せた。
  3. 上記２っのファイルは、圧縮し、まりふのひとの公開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