日報を1枚のエクセルに集約してデータ集計から分析までの流れを解説

NipoPlusを使えば日報やチェックシートの集計やまとめが簡単に行なえます。 例えば、次のような日報テンプレートを使ってスタッフに日報を書いてもらうとします。書き終えた日報のイメージは次のような見た目をしています。

スタッフが営業日報を書き、管理者が承認したあとの画面。この日報を様々な角度で集計・分析する方法を考える
スタッフが営業日報を書き、管理者が承認したあとの画面。この日報を様々な角度で集計・分析する方法を考える

少し日報の内容を棚卸ししてみましょう。

入力項目概要
業務内容この項目は時間ごとに作業内容を入力する可変長のフォームです。日によって行数が変わるため、集計には少し工夫が必要になります
業務内容ー時間経過時間を記載している項目です。単位を揃えればエクセルでも集計できます
業務内容ー区分架電・新規訪問・リピート訪問の3つから選んで入力する項目です。決まった文字列なので集計ができそうですね
業務内容ー備考備考欄は集計には適していません。この項目は集計不可能
新規契約数その日の契約数です。ただの数値で、前述のような可変長もないため集計は簡単にできそうです
更新契約数更新契約数。集計は簡単にできそうです
解約数解約数。更新契約数。集計は簡単にできそうです

この日報テンプレートは何かしらの契約を取る営業日報をイメージしています。そのため新規契約や更新契約といった成績を日報に書くことになりますが一つの例として捉えてください。

たくさんの営業日報を1枚のエクセルにまとめよう(CSV出力)

日報を俯瞰(高いところから見渡すこと)しにくい理由は、報告書という1枚1枚の分かれた形で提出されていくためです。 日報という性質上、これはやむを得ないことですが、ある程度蓄積した日報をまとめて1枚のエクセルシートに出力すれば全体を俯瞰しやすくできます。 例えば当月中に溜まった日報を月末に1枚のエクセルにまとめて見ましょう。

全8件の日報をCSVで出力し、エクセルで開いた画面のイメージ。見やすいように色分けをしています
全8件の日報をCSVで出力し、エクセルで開いた画面のイメージ。見やすいように色分けをしています

ほんの数クリックでたくさんの営業日報を1枚のエクセルシートにまとめることができました。
(詳しい手順は操作ガイドのCSV出力(一括)を参照してください)
CSVは本来色情報などを持ちませんが、見やすくするために色分けと罫線を設定しています。 1つの日報ごとに色分けしており、上の例では8件の日報が含まれていることがわかります。

キャラクター

行番号が18までありますが、これは可変長フォームを使っているため、1つの日報が複数行に分けて出力た結果です

さて、1枚のエクセルに複数の営業日報が表示されることで全体を俯瞰出来るようになりました。 ここから更に踏み込んで、これらの日報を様々な角度から集計、分析をしてみましょう

たくさんの日報をエクセルに手書きで書き写す手間をまるごと省略できたことに注目してください

今回の例では8件の営業日報を1枚のエクセルにまとめていますが、バラバラの日報を1枚のエクセルにまとめるのは本当にたった数クリックで行うことができます。 ワードや紙の日報を1枚のエクセルシートにまとめるのは結構大変ですが、日報システムであればCSVエクスポートを使うだけ。 もしいま、日報を手動でエクセルに書き写している方がいればその仕事は手動でやるべき仕事では有りません。

営業日報内の契約件数を集計する

さてここからは具体的な操作のお話です。まずは肩慣らし。簡単なところから見てみましょう。例えばこの営業日報にある契約件数を単純に集計してみます。 エクセルでの集計はSUM関数を使います。といってもSUM関数は頻出するためワンクリックで設定できる機能があらかじめエクセルに用意されています。 集計したい範囲+1行を選択しておきΣ(オートサムボタン)をクリックします。これで日報の中の「新規契約」「更新契約」「解約」の合計が簡単に集計できます。

  1. 集計したい範囲+1行を選択しておく
  2. オートサムボタンをクリック
全8件の日報をCSVで出力し、エクセルで開いた画面のイメージ。見やすいように色分けをしています
全8件の日報をCSVで出力し、エクセルで開いた画面のイメージ。見やすいように色分けをしています

合計の他にも、平均や最大値など簡単な集計であればオートサムボタンから設定できます。

日報内の作業時間を計算して業務の時間を算出する

CSV出力されたエクセルシートには時間の書かれた列があります。見出しでいうと「業務内容ー時間」の項目です。 CSV上では開始時間(自)と終了時間(至)の2列で出力されていますので、開始から終了までにかかった時間をエクセルで算出してみましょう。

エクセルを使って時間計算をします。まず先に作業用に1列挿入する必要があります
エクセルを使って時間計算をします。まず先に作業用に1列挿入する必要があります

1列追加されたら、その列の2行目にカーソルを持ってきて、次のような式を入力します

計算式を入れます
計算式を入れます

この式はキーボードとマウスだけで簡単に作れます。 流れを動画で説明できればスムーズなのですが、このサイトは静止画オンリーのためご了承下さい。

  1. 計算を出したいセルで先に 「=」と打つ
  2. 1つ左隣のセルをクリックする(またはカーソルキーの左矢印を1回押す)
  3. キーボードから「-」と打つ
  4. 2つ左隣のセルをクリックする(またはカーソルキーの左矢印を2回押す)
  5. エンターを押下

はい。たったこれだけです。慣れれば式を入れるのに1〜2秒程度で書き上げることが可能です。

キャラクター

目指せ!エクセルマスター

計算結果が正しく表示されましたか?これが作業時間の計算結果になります。

表示形式で見た目を変更したいとき

エクセルのバージョンによって変わるかもしれませんが、筆者の環境では時間の計算結果が「3:30」といった形式で表示されました。 今私達は経過時間の計算をしているのでわかりますが、他の人が見た時これを「3時30分」と読み間違えてしまう可能性があります。 エクセルの表示形式を変更すれば、この表示を「3:30」から「3時間30分」に表示形式を変更できます。

表示形式を変更したいセルを選択し、右クリック>書式設定を選択します
表示形式を変更したいセルを選択し、右クリック>書式設定を選択します
表示形式を指定します。h時間mm分と入力します
表示形式を指定します。h時間mm分と入力します

「h」や「mm」は特殊な記号なので必ず半角で打つようにしてください。雰囲気からわかりますが、hは時間を、mmは分を表しています。

最後に、下のセルにも式をコピーしておきましょう。フィルコピーが便利ですが、数百行あるとドラッグするだけでも億劫です。フィルハンドルをダブルクリックすることでも式のコピーが可能ですので、 覚えておくと役に立ちます。

アクティブセルの右下にある小さな四角がフィルハンドルです。このハンドルをダブルクリックすることで連続する下のセルまで書式や式をコピーできます
アクティブセルの右下にある小さな四角がフィルハンドルです。このハンドルをダブルクリックすることで連続する下のセルまで書式や式をコピーできます
ダブルクリックでコピーが完了しました。色分けは残念ながら単一カラーになってしまいましたね。回避策はまた別の機会にでも。
ダブルクリックでコピーが完了しました。色分けは残念ながら単一カラーになってしまいましたね。回避策はまた別の機会にでも。

テーブルに変換して並べ替えやフィルターを自在に操る

エクセルにはテーブル化という便利な機能が備わっています。 テーブル化すると項目を大きい順や小さい順に並べ替えたり(ソートといいます)、条件に一致する項目だけを表示したり(フィルタといいます)できます。 データの分析にはとても有益な機能ですので、使ったことが無い方は是非使ってみてください。

テーブルでは並べ替えを使いますが、並べ替えをすると元の順番に戻せなくなる可能性があります。そのため予めもとに戻すためのキーとなるデータを用意しておきましょう。 このキーは連番が望ましいです。A列を挿入し、連番のデータで埋めておきましょう

キャラクター

別に戻す必要が無いならこの手順はスキップしてもOK。NipoPlusでまたCSVエクスポートすれば済む話です

  1. A列を右クリック>列の挿入をクリック
  2. A2セルに「1」と入力
  3. フィルハンドルをダブルクリックしてコピーする(A列のデータが1で埋められます)
  4. フィルコピーオプションで「連続データ」を選びます
並べ替えでもとに戻すためのキーを追加しておきます。
並べ替えでもとに戻すためのキーを追加しておきます。

これでA列を昇順ソートすればいつでもこの並び順に戻すことができるようになりました。続いてテーブル化を行っていきましょう。

  1. セルA1をクリックしてアクティブセルをA1に移動します
  2. 挿入タブをクリック
  3. テーブルをクリック
  4. 範囲が表示されるのでOKをクリック(A1をアクティブセルにしておくことでA1と連続性のある範囲が自動で指定される)
キャラクター

色分けはテーブルの色分けを使いたいので、この時点で筆者が指定した色分けは解除しました

CSVのデータをテーブルに変換します。
CSVのデータをテーブルに変換します。

これでテーブル化ができました。自動で色分けがされ、見た目がだいぶ華やかになりましたね。

テーブル化された直後のエクセル画面。自動で色分けがされています
テーブル化された直後のエクセル画面。自動で色分けがされています

経過時間の多い順に並べ替えをしてみよう。ソート機能の使い方

早速テーブルの機能を使ってみましょう。まずは王道の並べ替えです。経過時間の多い順に並べ替えをしてみましょう。

経過時間の多い順に並べ替えを実行します
経過時間の多い順に並べ替えを実行します
並べ替え後の画面。経過時間の多い順に並びが変わっています。もとに戻すにはNo順に並べ替えをすればOKです
並べ替え後の画面。経過時間の多い順に並びが変わっています。もとに戻すにはNo順に並べ替えをすればOKです

経過時間の多い順に並びが変わりました。これがソート機能です。日付や金額、あるいはあいうえお順などでも並べ替えが可能です。 本来であれば日報提出日順に並べ替えをすればもとに戻せるのですが、今回の例で使用したCSVは「可変長の2行目以降の日付を出力しない」という設定をONにしているため、日付が空欄の箇所がありました。 (詳しくはCSV出力のオプションを参照してください)
これでは日付を並べ替えのキーとして使用できません。そのため別途Noという並べ替えのキーを用意しました。

キャラクター

CSV出力設定によってはNoの列は不要ってことです。この辺はデータによりけりですね

特定のスタッフだけを抜き出して見よう。フィルター機能の使い方

今回の営業日報は「A部長」と「管理者」、二人の営業日報が混在したCSVファイルになります。ここから「A部長」の日報だけを抽出してみましょう。 抽出に使用するフィールドは「提出者名」が最適ですが、もし同姓同名のスタッフが居るとうまく機能しないのでその際は提出者IDを使ってください。

特定のスタッフが書いた日報だけを抽出したいときはフィルター機能を使います
特定のスタッフが書いた日報だけを抽出したいときはフィルター機能を使います

これで「A部長」の日報のみが抽出されました。他にも「日報が承認済み」で抽出するなど、様々な使い方ができます。

フィルター後のデータだけを集計する

さて、フィルターしたあとの結果だけをみて集計してみましょう。エクセルのSUM関数では非表示の項目も集計の対象になってしまいますが、テーブルの「集計」機能を使えば現在抽出されているデータだけの集計を取ることができます。

キャラクター

つまりA部長だけの契約件数や経過時間の集計ができます

現在表示されているA部長の経過時間のみを合計した結果が表示されました。
現在表示されているA部長の経過時間のみを合計した結果が表示されました。

A部長の経過時間である合計が計算されていますね。SUM関数だと非表示中の「管理者」の経過時間も集計されてしまいますが、この機能では表示中のデータしか集計されないため、スタッフ別の合計を出したいときに便利です。 内部的にはSUBTOTALという関数が使用されていますので興味のある方は調べてみてください。

さらなる高みへ。エクセルマスターへの道

いかがでしたでしょうか?営業日報の集計にフォーカスを当ててきましたが、ここで紹介した機能はエクセルのほんの一部に過ぎません。 他にもまだまだたくさん便利な機能が隠されています。例えば契約件数が5件を下回ったら赤文字にするといった条件付き書式や、表を縦列に自由に組み替えて分析可能なピポットテーブルなどなど。 インターネットにはエクセルの使い方を解説するサイトが数多く存在しますが、より体系的に学びたいのであれば書店でExcelに関する本を購入するのもオススメです。 どうしてもWebサイトは書き手によって品質の差がありますし、執筆者の好みに左右されるところも多くあります。 一方で書籍はきちんとした校閲がされ、体系的で学びやすくまとまっています。

キャラクター

本を選ぶときは数ページ読んで自分にあったものを選びましょう

数百件件の日報を一瞬で1枚のエクセルシートにまとめます。あなたの代わりに

エクセルの便利な機能を使う上で欠かせないもう一つのアイテムがデータです。集計をするにしても集計のもととなるデータが無ければエクセルの機能は役に立ちません。 このページではNipoPlusのCSVエクスポートを使いデータを用意しましたが、他の多くのシステムでもCSVエクスポートという機能はあるはずです。 紙で提出されたデータを1件1件手動でエクセルに書き込むのはあまりにも非効率です。時間がかかる上に転記ミスの可能性もあります。 何よりも「スタッフが日報を書き、それをエクセルに書き写す」、という同じ作業を2回やることが大きな無駄です。 エクセルを使う腕はこのデータをいかに効率よく調達できるかも、大きなポイントでしょう。

語弊を覚悟で言えばデータは食材、エクセルは調理器具です。NipoPlusでは食材であるデータを提供します。 その食材をどのようにアレンジし、調理するかはあなた次第です。

nipoplusアプリ起動ボタン

一切の企業情報を入力せずに手軽に体験できます

  • メール・パスワードの設定なしで体験可能(匿名機能
  • クレジットカードの登録も不要
  • Webアプリだからインストール不要
nipoplusアプリ起動ボタン
ios App Store
Android Google Play Store
最終更新日: 2022/11/14