[Excelで勉強記録]-②日間集計シートで日々の勉強時間を集計
日々の勉強時間や分類、使用テキストを入力するシート「入力表」を作りました。
次は入力した勉強時間のデータを集計するシートを作ります。
－ 目次 －
勉強時間集計するシートを作成
新しいシート作成します。
西暦ごとに集計したいのでここではシート「2018」を作りました。
2018年の勉強データを集計するシートです。
項目は以下のとおり。
- 日付：1/1～12/31まで
- 曜日：=(TEXT(A3,”aaa”))
- 各分類：勉強内容を分類（科目など）
- 合計：毎日の勉強時間合計
私は英語の勉強をログにしているので、リスニング（L)、リーディング（R)のように分類。
学校の勉強であれば、「日本史」「数学」などの科目名にするといいですね。
日付は集計したい年の1/1と入力しておきましょう。（例：2018/1/1）
「入力表」のように書式の表示を変更します。
曜日は「=TEXT(A3,”aaa”))」のように入力しておきます。
表をテーブルに変換する
「入力表」のように表をテーブルに変換しておきましょう。
更に列幅や文字列の配置をお好みで変更してください。
テーブル名の変更
「入力表」のようにテーブルに名前をつけておきましょう。
2018年の勉強時間を集計する表なので「集計2018」にしました。
SUMIFS関数で勉強時間を集計
分類には分類ごとの勉強時間の集計SUMIFS関数を使います。
SUMIFS関数はSUMとIFの合体した関数で、条件に合致するデータを合計します。
SUMIFは条件は一つ、SUMIFSは複数の条件を設定できます。
今回は日付の条件と、分類の条件と複数の条件を設定します。
- 分類の集計したいセルをアクティブにし「=sum」と入力
- 一覧から「SUMIFS」をダブルクリック
- 数式バー左の「関数の挿入」をクリック
- シート「入力表」に切り替える
- 「合計対象範囲」にテーブル「入力表」の「時間」を指定
※「時間」のフィールド全体を指定するためにはフィールド名「時間」の上で黒い下向き矢印の状態でクリックし選択します。
関数の参照先は以下を参考にしてください。
合計対象範囲は合計する範囲なので、「入力表」シートの時間の列。
入力表はテーブルなので、データが追加されれば関数内にも自動的に参照されます。
条件範囲1は一つ目の条件です。まずは日付を検索します。
範囲は「入力表」シートの日付の列です。
条件1は日付なので「2018」シートの日付が入ったセル。
条件範囲2は二つ目の条件です。「入力表」シートの分類の列。
条件2は「2018」シートの表の項目名を使います。
これで日々の勉強時間や科目を入力する「入力表」シートから該当日と分類を検索し、その日の勉強時間を集計し、結果を「2018」シートの表に表示します。
1/1の分類「単」のデータが集計されました。
時間の表示形式を変更
時間の表示形式に変えておきましょう。
- 表示を変えたいセルをアクティブ
- 「ホーム」タブ
- 数値グループの右下のダイアログボックス起動ツールをクリック
- 時刻
- 「13：30」の形式を選択
SUMIFS関数のコピーペースト
テーブルはオートフィルでコピーするとずれるので、コピーペーストを使います。
先ほど入力したSUMIFS関数が入っているセルをCtrl+Cでコピーし、Ctrl+Vで他の分類にもペーストします。
このままではすべて「単」の分類だけを集計しているので、編集していきます。
- 「G」の関数が入ったセルをクリック
- 「関数の挿入」をクリック
- 条件2をテーブル「集計2018」の見出し「G」に変更
同様に他の分類のSUMIFS関数の条件2をそれぞれのテーブル見出しに修正します。
各日の勉強時間の合計
[合計]に毎日の勉強時間の集計を出すSUM関数を入力します。
- 合計のセルをアクティブ
- ホームタブ
- 「編集」グループの「Σ」ボタンをクリック
時間の表示形式を変更する
時間の表示形式は24時間以上を超える場合は別途指定が必要です。
1日の勉強時間の集計で24時間を超えないので上述と同じ時間の表示形式でOKですが、勉強をしなかった「0：00」表示が目についてしまいます。
気になる方は0だった場合は表示しない設定にしていきましょう。
- 勉強時間のセルをすべて選択
- 「ホーム」タブ
- 数値グループの右下のダイアログボックス起動ツールをクリック
- ユーザー定義
- 「種類」に「[=0]””;h:mm」と入力
これで勉強時間が0：00の場合は非表示になりました。
データのコピー
1/1のデータを選択しオートフィルで12/31までコピーしましょう。
これで「入力表」シートに勉強した時間や分類を入力していくと、毎日の勉強時間を自動的に集計するようになります。
集計表を作成
その年の各分類の集計をそのシート上で見たい場合は、上部に作ると見やすいです。
各分類ごとにSUM関数で集計するだけなのですが、注意点は時間の表示形式です。
集計だと24時間を超えるのでこの場合[=0]””;[h]:mmにしましょう。
次は月間グラフを作成します。
