エクセルで勉強時間ログ-②日間集計シート

日々の勉強時間や分類、使用テキストを入力するシート「入力表」を作りました。
まだ読んでいない方はこちらからどうぞ。

次は入力した勉強時間のデータを集計するシートを作ります。

勉強時間集計するシートを作成

新しいシート作成します。
西暦ごとに集計したいのでここではシート「2018」を作りました。
2018年の勉強データを集計するシートです。

項目は以下のとおり。

1. 日付：1/1～12/31まで
2. 曜日：=(TEXT(A3,”aaa”))
3. 各分類：勉強内容を分類（科目など）
4. 合計：毎日の勉強時間合計

私は英語の勉強をログにしているので、リスニング（L)、リーディング（R)のように分類。
学校の勉強であれば、「日本史」「数学」などの科目名にするといいですね。

日付は集計したい年の1/1と入力しておきましょう。（例：2018/1/1）
「入力表」のように書式の表示を変更します。

曜日は「=TEXT(A3,”aaa”))」のように入力しておきます。

表をテーブルに変換する

「入力表」のように表をテーブルに変換しておきましょう。

更に列幅や文字列の配置をお好みで変更してください。

テーブル名の変更

「入力表」のようにテーブルに名前をつけておきましょう。
2018年の勉強時間を集計する表なので「集計2018」にしました。

SUMIFS関数で勉強時間を集計

分類には分類ごとの勉強時間の集計SUMIFS関数を使います。
SUMIFS関数はSUMとIFの合体した関数で、条件に合致するデータを合計します。
SUMIFは条件は一つ、SUMIFSは複数の条件を設定できます。

今回は日付の条件と、分類の条件と複数の条件を設定します。

1. 分類の集計したいセルをアクティブにし「=sum」と入力
2. 一覧から「SUMIFS」をダブルクリック

1. 数式バー左の「関数の挿入」をクリック

1. シート「入力表」に切り替える
2. 「合計対象範囲」にテーブル「入力表」の「時間」を指定

※「時間」のフィールド全体を指定するためにはフィールド名「時間」の上で黒い下向き矢印の状態でクリックし選択します。

関数の参照先は以下を参考にしてください。

合計対象範囲は合計する範囲なので、「入力表」シートの時間の列。
入力表はテーブルなので、データが追加されれば関数内にも自動的に参照されます。

条件範囲1は一つ目の条件です。まずは日付を検索します。
範囲は「入力表」シートの日付の列です。
条件1は日付なので「2018」シートの日付が入ったセル。

条件範囲2は二つ目の条件です。「入力表」シートの分類の列。
条件2は「2018」シートの表の項目名を使います。

これで日々の勉強時間や科目を入力する「入力表」シートから該当日と分類を検索し、その日の勉強時間を集計し、結果を「2018」シートの表に表示します。

1/1の分類「単」のデータが集計されました。

時間の表示形式を変更

時間の表示形式に変えておきましょう。

1. 表示を変えたいセルをアクティブ
2. 「ホーム」タブ
3. 数値グループの右下のダイアログボックス起動ツールをクリック

1. 時刻
2. 「13：30」の形式を選択

SUMIFS関数のコピーペースト

テーブルはオートフィルでコピーするとずれるので、コピーペーストを使います。
先ほど入力したSUMIFS関数が入っているセルをCtrl+Cでコピーし、Ctrl+Vで他の分類にもペーストします。

このままではすべて「単」の分類だけを集計しているので、編集していきます。

1. 「G」の関数が入ったセルをクリック
2. 「関数の挿入」をクリック

1. 条件2をテーブル「集計2018」の見出し「G」に変更

同様に他の分類のSUMIFS関数の条件2をそれぞれのテーブル見出しに修正します。

各日の勉強時間の合計

[合計]に毎日の勉強時間の集計を出すSUM関数を入力します。

1. 合計のセルをアクティブ
2. ホームタブ
3. 「編集」グループの「Σ」ボタンをクリック

時間の表示形式を変更する

時間の表示形式は24時間以上を超える場合は別途指定が必要です。
1日の勉強時間の集計で24時間を超えないので上述と同じ時間の表示形式でOKですが、勉強をしなかった「0：00」表示が目についてしまいます。
気になる方は0だった場合は表示しない設定にしていきましょう。

1. 勉強時間のセルをすべて選択
2. 「ホーム」タブ
3. 数値グループの右下のダイアログボックス起動ツールをクリック

1. ユーザー定義
2. 「種類」に「[=0]””;h:mm」と入力

これで勉強時間が0：00の場合は非表示になりました。

データのコピー

1/1のデータを選択しオートフィルで12/31までコピーしましょう。

これで「入力表」シートに勉強した時間や分類を入力していくと、毎日の勉強時間を自動的に集計するようになります。

集計表を作成

その年の各分類の集計をそのシート上で見たい場合は、上部に作ると見やすいです。
各分類ごとにSUM関数で集計するだけなのですが、注意点は時間の表示形式です。
集計だと24時間を超えるのでこの場合[=0]””;[h]:mmにしましょう。

次は月間グラフを作成します。