Excel/歩数を(日付順推移/月別日付順)グラフにするためのグラフデータ例

 歩数を視覚化されている方がおられる。

「花だ〜いすき」さんの例 「ロビーのほほ笑み」さんの例


 見栄えはこの際、横に置いておき、私ならどんなグラフを作るだろうか‥‥ Windows7/Excel2010 で試作してみた。

「花だ〜いすき」さんのデータで作成

Excel をより深く知る必要があり難しくもなるが、是非とも挑戦してほしい。

§1.歩数データ

 単なる記録・グラフ作成用ではなく、日記を兼ねたような形にする。
説明の都合上、シート名を「歩数表」とする。

  • リスト形式にする。
  • A列: 日付
    • 入力は yyyy/mm/dd で行う。
    • 右図の「曜日」はセルの書式設定で行ったもので、入力していない。
  • B列: 歩数
  • C列: 天気(任意)
    • 歩数が少ない言い訳に使う等。
  • D列: 備考(任意)
    • 歩数が多い説明に使う等。(例えば、“○○ウォーキングに参加” とか...
≪この形式の利害≫
  1. データが増えると、かなりスクロールせねばならない。
     が、対策は種々ある。
    • 日付を Ctrl+↓ で対応可能。
    • 上書き保存する前に、最終行にカーソルを移しておく。
    • 2行目をコピーし、2行目に[コピーしたセルの挿入]する。
       グラフを作る時に並べ替える必要があるが、思わぬ利点もあった。(まだ秘密)
    • フォーム]−[新規]で
      入力する方法もある。
  2. 日付に曜日を表示することが出来る。グラフにも表示可能。
  3. 1日に計測を 2回行っても対応可能。(例えば、午前は○○に参加。午後は△△に参加等)
  4. 並べ替えを前提にすれば、入力順は問わない。


§2.日付順推移グラフデータの作り方

 例えば、「日付順」という名のシートを作り、ここにグラフデータを作る。

≪項目≫
  • 日付
  • グラフを作る月 ‥‥ ここでは、7月分と 8月分



  1. [歩数表]シートで[フィルター]をオンにし、
    1. 日付の[▼]をクリックし、
    2. 7月のみ選択し、
    3. [OK]
    4. 表示された日付,歩数のセルを選択し、コピーする。
       例えば、(1)先頭の日付と歩数を選択して、(2)Shift+Ctrl+↓。(3)Ctrl+C。
  2. [日付順]シートの先頭セル(この場合はセルA2)に貼り付ける。
  3. [歩数表]シートに戻り、
    1. 日付の[▼]をクリックし、
    2. 8月のみに選択し変えて、
    3. [OK]
    4. 表示された日付,歩数のセルを選択し、コピーする。

  4. [日付順]シートの
    1. 未入力の先頭セル(7/31 の下のセル)に貼り付ける。
    2. 歩数のセル(B列)に選択し替え、
    3. 8月の列(この場合 C列)に移動する。(右図)


 結果として、日付が 7月の歩数は 7月(B列)に、8月の歩数は 8月(C列)に入れればよい。


§3.月別日付順グラフデータの作り方

 「花だ〜いすき」さんや「ロビーのほほ笑み」さんが入力している形式と同じです。(たぶん)
これを歩数表から、数式を使って作ります。
 数式だらけの表です。チョッとややこしいですが、一度作っておくと、任意の月の 3ヶ月分のグラフが作れます。データが入力してあれば「昨年同月」のグラフも作れる‥‥ はずです。

 シートの基本構造は右図。シート名は任意であるが、ここでは、“月別歩数” とした。

≪1行目≫
  • セルA1: タイトル。ここでは “作成月” とした。
  • セルB1, C1, D1‥‥ :グラフを作成する月
    • 入力は yyyy/mm/1(日は「ついたち」に固定)形式で入力する。
    • セルの書式は、入力内容が判るような書式が望ましい。
≪2行目≫

 空行。3行目以下を「リスト形式」表にするための「区切り行」で必須。行の高さは関係ないので、小さくしてもよい。

≪3行目≫
  • セルA3: タイトル。ここでは “日” とした。
  • セルB3: グラフの凡例に使う名前(文字列である必要がある)
    • 数式: 下記を入力する。
      =YEAR(B1)&"年"&MONTH(B1)&"月"
    • 説明: セルB1の年と、“年”と、セルB1の月と、“月” を結合する。
  • セルC3, D3‥‥
    • セルB3 の数式をコピーする。
≪4行目以降≫
  • セルA4〜A34: グラフの横軸になる名前
    • 入力は、“2000/1/1”〜“2000/1/31” とする。(年/月は任意で、大の月であればよい)
    • セルの書式設定−ユーザー定義で、“d日” を入力する。
       入力を確定すると、漢字の前後にダブルクォーテーション(")が付く。
  • セルB4 ‥‥ 歩数表の日付が 2014/7/1 の歩数の合計値
    • 解説: 歩数表の日付(A列)が「セルB1の年月,セルA4の日」に等しい歩数(B列)の合計
       課題は、歩数表のA, B列を何行まで見ればよいか‥‥ で、とりあえず 400 とした。(ヒント参照)
    • 入力: 下記
      =SUMIFS(歩数表!B2:B400,歩数表!A2:A400,DATE(YEAR(B1),MONTH(B1),DAY(A4)))
    • 補正後: “=SUMIFS(歩数表!$B$2:$B$400,歩数表!$A$2:$A$400,DATE(YEAR(B$1),MONTH(B$1),DAY($A4)))”
      • 歩数表は常に固定なので、すべて絶対番地にする。
      • セルB1 の年月は(数式を)
         (1)下にコピーした時は B1
         (2)右にコピーした時は C1
         にする必要があるので、行のみ固定する。 → B$1
      • セルA4 の日は(数式を)
         (3)下にコピーした時は A5
         (4)右にコピーした時は A4
         にする必要があるので、列のみ固定する。 → $A4
  • セルC4 ‥‥ 歩数表の日付が 2014/8/1 の歩数の合計値
    • セルB4 の式をコピーして作る。
  • セルD4 ‥‥ 歩数表の日付が 2014/9/1 の歩数の合計値
    • セルB4 の式をコピーする。
  • セルB5〜B34
    • セルB4 の式をコピーする。
  • セルC5〜C34
    • セルC4 の式をコピーする。
  • セルD5〜D34
    • セルD4 の式をコピーする。
ヒント
  • SumIfs 関数: SUMIFS(合計範囲, 条件_範囲 1, 条件 1, [条件範囲 2, 条件 2], ...)
    • 合計範囲 必ず指定します。 合計する 1 つまたは複数のセル (数値か、数値を含む名前、範囲、またはセル参照など) を指定します。 空白と文字列は無視されます。
    • 条件範囲 1 必ず指定します。 対応する条件による評価の対象となる最初の範囲を指定します。
    • 条件 1 必ず指定します。 加算の対象となる条件範囲 1 のセルを定義する条件を数値、式、セル参照、または文字列で指定します。 たとえば、検索条件は 32、">32"、B4、"Windows"、または "32" のようになります (式および文字列を指定する場合は半角の二重引用符 (") で囲む必要があります)。
    • 条件範囲 2, 条件 2, ... 省略可能です。 追加の範囲と対応する条件です。 最大 127 組の範囲/条件のペアを指定できます。
  • 歩数が 0 であると折れ線もゼロになるので、0 を消去(Delete)する必要がある。
     この例では「2014年9月」が対象になる。
    1. 0 のセルを Delete すると数式も消える。(数式を消去するので何も表示されなくなる)
    2. 歩数表に歩数を入力しても、この表には(数式は消去しているので)表示されない。
    3. こうなった場合、数式を再度コピーすれば「回復」する。
  • 歩数表の A,B列を何行まで見ればよいか‥‥ の件
    1. 今回は 400 としたが、このまま歩数表の入力を続け 2年分溜まったとする。しかし、見ているのは 400行までなので‥‥ 401行目以下は「無視」される。
    2. そこで、4000 とすると 10年間は心配なくなる。
       しかし、毎回毎回 10年間分のデータを探すことになるので「重たくなる」。
    3. ところが歩数表のデータを 1行 削除すると、400 が 399 になることがわかった。10行追加すると、400 が 410 になることもわかった。

       この辺に、解決策のヒントがありそう...


グラフデータを追加した時

 グラフデータを追加した時、グラフデータを選択し直せばよく、グラフを作り直す必要はありません。

  • グラフを作る時が複雑だった場合は、作り直した方が早いでしょう。(たぶん)
  • グラフを作るのが簡単だった時は、グラフデータの選択し直しも簡単です。
§日付順推移グラフの場合

 グラフデータの範囲が変わるので、データの範囲を設定し直す必要があります。

  1. [デザイン]タブ/データGr.の[データの範囲]をクリックする。
    • 表示された「グラフデータの範囲」“=日付順!$A$1:$D$67” が現在の範囲。
    • [日付順]シートを見ると、選択されている範囲が 点線で囲まれている*1 ことが判る。
  2. 例えば 68行と 69行を追加したのであれば、グラフデータの範囲は、セルA1〜D69 になるので、
    グラフデータの範囲を “=日付順!$A$1:$D$69” に変更する。
  3. [OK]


*1:三色ねじり棒のイメージ