イラスト&漫画でTOEICスコアアップ

イラスト、マンガ、図解でTOEICを解説

[Excelで勉強記録]-②日間集計シートで日々の勉強時間を集計

エクセルで勉強時間記録_月間グラフ_範囲選択

日々の勉強時間や分類、使用テキストを入力するシート「入力表」を作りました。

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

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

- 目次 -

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

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

項目は以下のとおり。

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

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

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

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

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

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

表をテーブルに変換する

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

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

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

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

テーブル名の変更

「入力表」のようにテーブルに名前をつけておきましょう。
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にしましょう。

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

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

[Excelで勉強記録]-③月間グラフで月々の勉強時間を集計
次は月々のグラフを作成していきます。 - 目次 - 月間グラフの挿入 月間のグラフの作成方法です。まずは2018年の1月のグラフを