-生涯学習で人生を楽しむ-

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

Excel勉強ログ_日間集計シート

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

エクセルで勉強時間ログ-①勉強時間入力シート
勉強を継続する一つの方法として「勉強時間のログ」を取ることがあります。 資格取得などで勉強が長期間に渡る場合、モチベーションの維持

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

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

新しいシート作成します。
西暦ごとに集計したいのでここではシート「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. 数式バー左の「関数の挿入」をクリック

エクセルで勉強時間記録_月別集計表関数2

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

エクセルで勉強時間記録_月別集計表

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

エクセルで勉強時間記録_月別集計表関数

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

エクセルで勉強時間記録_月別集計表関数

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

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

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

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

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

エクセルで勉強時間記録_月別集計表関数

時間の表示形式を変更

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

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

エクセルで勉強時間記録_月別集計表_時間表示2

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

SUMIFS関数のコピーペースト

エクセルで勉強時間記録_月別集計表_時間表示

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

エクセルで勉強時間記録_月別集計表_関数コピー

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

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

エクセルで勉強時間記録_月別集計表_関数編集

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

エクセルで勉強時間記録_月別集計表_関数編集2

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

エクセルで勉強時間記録_月別集計表_関数編集3

各日の勉強時間の合計

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

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

エクセルで勉強時間記録_月別集計表_SUM

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

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

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

エクセルで勉強時間記録_月別集計表_時間の表示

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

エクセルで勉強時間記録_月別集計表_時間の表示2

エクセルで勉強時間記録_月別集計表_時間の表示3

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

データのコピー

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

エクセルで勉強時間記録_月別集計表_データコピー

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

集計表を作成

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

エクセルで勉強時間記録_月別集計表_上部集計

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

エクセルで勉強時間ログ-③月間グラフ
次は月々のグラフを作成していきます。 目次-Contents-月間グラフの挿入グラフから曜日を削除するグラフの目盛りを変更するデー

同じカテゴリーの記事

エクセルで単語帳_印刷範囲の設定
エクセルでオリジナル単語帳を作る②
Excel勉強ログ_はじめに
勉強を記録する-どんな表やグラフを作るか
エクセルで単語帳_印刷
エクセルでオリジナル単語帳を作る①
勉強机
勉強&リモートワークがはかどる机周り
日大通信ノート
大学で単位を落とさないノート術
Excel勉強ログ_月間グラフ
エクセルで勉強時間ログ-③月間グラフ