歩数を視覚化されている方がおられる。
| 「花だ〜いすき」さんの例 | 「ロビーのほほ笑み」さんの例 |
|---|---|
 |
 |
見栄えはこの際、横に置いておき、私ならどんなグラフを作るだろうか‥‥ Windows7/Excel2010 で試作してみた。
「花だ〜いすき」さんのデータで作成
Excel をより深く知る必要があり難しくもなるが、是非とも挑戦してほしい。
§１．歩数データ
単なる記録・グラフ作成用ではなく、日記を兼ねたような形にする。
説明の都合上、シート名を「歩数表」とする。
- リスト形式にする。
- A列： 日付
- 入力は yyyy/mm/dd で行う。
- 右図の「曜日」はセルの書式設定で行ったもので、入力していない。
- B列： 歩数
- C列： 天気（任意）
- 歩数が少ない言い訳に使う等。
- D列： 備考（任意）
- 歩数が多い説明に使う等。（例えば、“○○ウォーキングに参加” とか...）
≪この形式の利害≫
- データが増えると、かなりスクロールせねばならない。
が、対策は種々ある。- 日付を Ctrl＋↓ で対応可能。
- 上書き保存する前に、最終行にカーソルを移しておく。
- 2行目をコピーし、2行目に［コピーしたセルの挿入］する。
グラフを作る時に並べ替える必要があるが、思わぬ利点もあった。（まだ秘密）
- ［フォーム］−［新規］で
- 日付に曜日を表示することが出来る。グラフにも表示可能。
- 1日に計測を 2回行っても対応可能。（例えば、午前は○○に参加。午後は△△に参加等）
- 並べ替えを前提にすれば、入力順は問わない。
§２．日付順推移グラフデータの作り方
例えば、「日付順」という名のシートを作り、ここにグラフデータを作る。
≪項目≫
- 日付
- グラフを作る月 ‥‥ ここでは、7月分と 8月分
- ［歩数表］シートで［フィルター］をオンにし、
- 日付の［▼］をクリックし、
- 7月のみ選択し、
- ［OK］
- 表示された日付，歩数のセルを選択し、コピーする。
例えば、(1)先頭の日付と歩数を選択して、(2)Shift＋Ctrl＋↓。(3)Ctrl＋Ｃ。
- ［日付順］シートの先頭セル（この場合はセルA2）に貼り付ける。
- ［歩数表］シートに戻り、
- 日付の［▼］をクリックし、
- 8月のみに選択し変えて、
- ［OK］
- 表示された日付，歩数のセルを選択し、コピーする。
- ［日付順］シートの
- 未入力の先頭セル（7/31 の下のセル）に貼り付ける。
- 歩数のセル（B列）に選択し替え、
- 8月の列（この場合 C列）に移動する。（右図）
- 未入力の先頭セル（7/31 の下のセル）に貼り付ける。
結果として、日付が 7月の歩数は 7月（B列）に、8月の歩数は 8月（C列）に入れればよい。
§３．月別日付順グラフデータの作り方
「花だ〜いすき」さんや「ロビーのほほ笑み」さんが入力している形式と同じです。（たぶん）
これを歩数表から、数式を使って作ります。
数式だらけの表です。チョッとややこしいですが、一度作っておくと、任意の月の 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
- 解説： 歩数表の日付（A列）が「セルB1の年月，セルA4の日」に等しい歩数（B列）の合計
- セル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月」が対象になる。- 0 のセルを Delete すると数式も消える。（数式を消去するので何も表示されなくなる）
- 歩数表に歩数を入力しても、この表には（数式は消去しているので）表示されない。
- こうなった場合、数式を再度コピーすれば「回復」する。
- 歩数表の A，B列を何行まで見ればよいか‥‥ の件
- 今回は 400 としたが、このまま歩数表の入力を続け 2年分溜まったとする。しかし、見ているのは 400行までなので‥‥ 401行目以下は「無視」される。
- そこで、4000 とすると 10年間は心配なくなる。
しかし、毎回毎回 10年間分のデータを探すことになるので「重たくなる」。 - ところが歩数表のデータを 1行 削除すると、400 が 399 になることがわかった。10行追加すると、400 が 410 になることもわかった。 この辺に、解決策のヒントがありそう...
グラフデータを追加した時
グラフデータを追加した時、グラフデータを選択し直せばよく、グラフを作り直す必要はありません。
- グラフを作る時が複雑だった場合は、作り直した方が早いでしょう。（たぶん）
- グラフを作るのが簡単だった時は、グラフデータの選択し直しも簡単です。
§日付順推移グラフの場合
グラフデータの範囲が変わるので、データの範囲を設定し直す必要があります。
- ［デザイン］タブ/データGr.の［データの範囲］をクリックする。
- 表示された「グラフデータの範囲」“=日付順!$A$1:$D$67” が現在の範囲。
- ［日付順］シートを見ると、選択されている範囲が 点線で囲まれている*1 ことが判る。
- 例えば 68行と 69行を追加したのであれば、グラフデータの範囲は、セルA1〜D69 になるので、
グラフデータの範囲を “=日付順!$A$1:$D$69” に変更する。 - ［OK］
