【コピペOK】手書き日報を5分刻みのExcel日報に｜関数だけで業務区分を自動集計

紙に手書きしていた作業日報を、Excelに置き換えました。作業を選んで時間を入れるだけで、「検査に何時間」「段取りに何時間」が勝手に計算される日報です。

僕は工場で総務とパソコンまわりの担当をしています。エンジニアではありません。むずかしいプログラム（マクロ）は使わず、Excelの計算式だけで作りました。だから、ふつうのExcelがあればどのパソコンでも動きます。

ファイルの配布はしていません。そのかわり、計算式はぜんぶコピーして貼るだけで使える形で載せています。この記事の順番どおりに貼っていけば、同じ日報ができあがります。

手書きの作業日報で困っていたこと

うちの工場の日報は、紙に5分きざみで手書きしていました。検査などの決まった作業がいくつかあって、あとは「その他」がちょっとある、よくある日報です。

困っていたのは、この2つです。

• 合計が出せない：検査に何時間かかったか知りたくても、紙をぜんぶめくって電卓で足すしかない
• 書いて終わり：毎日書いているのに、その記録がどこにも使われていない

そこで、やりたいことを3つに決めました。

1. 書くだけで、作業の種類ごとの合計時間が自動で出る
2. 日報から、その作業で使うフォルダ（検査の基準書など）をすぐ開ける
3. むずかしいプログラム（マクロ）は使わない。開くと警告が出るファイルにしない

完成したExcel日報のしくみ（シートは7枚）

1つのファイルに1週間分（月〜金）が入っています。毎週、元のファイルをコピーして、月曜の日付だけ入れかえて使います。

シート（画面下のタブ）	何をするところか
月・火・水・木・金	1日1枚の日報。現場の人が書くのはここだけ
週集計	1週間分の合計と、書き忘れの日のお知らせ
マスタ	作業の名前などの一覧表。ここを直すと月〜金の5枚ぜんぶに反映される

日報シートの色の決まりは2つだけです。黄色＝書くところ、グレー＝自動で計算されるところ（さわらない）。現場の人には、これだけ伝えれば使えます。

はじめに：計算式のコピーと貼り方

この記事では「C35に貼る」のように、セルの場所で説明します。セルの場所は、上の列（A・B・C…）と左の行（1・2・3…）の組み合わせです。C35なら「C列の35行目」です。

シートの名前は記事と同じ「月」「火」「マスタ」などにしてください。計算式の中でシートの名前を使っているので、名前が違うとエラーになります。

日報シートのどこに何を置くか

このあとの計算式は、ぜんぶ下の配置で作る前提です。同じ場所に作れば、式は貼るだけで動きます。

場所	書くもの
C3 / F3 / I3	日付 / 名前 / ライン（A3・E3・H3には「日付」などの見出し）
C4 / F4 / I4	始業の時刻 / 終業の時刻 / 休憩（分）（A4・E4・H4に見出し）
6行目	表の見出し：A=No、B=開始、C=終了、D=分、E=作業、F=業務区分（作業の種類）、G=品番・製番、H=数量、I=不良、J=備考、K=チェック、N=開く
7〜31行目	1日の記録を書く25行
L列・M列	計算用。あとで見えないようにかくす
O6〜AA17	色で見る1日の流れ（5分マス）
A34〜E44	作業の種類ごとの合計と、書き忘れチェック

手順1：一覧表（マスタ）シートを作る

最初に、作業の名前などをまとめた一覧表を作ります。シートの名前は「マスタ」にしてください。

列	3行目に書く見出し	4行目から下に書くもの
A	作業名	日報で選べる作業の名前（63行目まで、最大60個）
B	業務区分	その作業の種類（E列から選ぶ）
C	リンク先	「▶開く」で開きたいフォルダの場所。なければ空のまま
E	業務区分	作業の種類の一覧（E4〜E11の8種類）
G	時刻	6:00〜22:00を5分きざみで（G4〜G196）

作業の種類と作業の名前は、うちの工場に合わせて下のようにしました。自分の職場に合わせて書きかえてください。

作業の種類（E列）	作業の名前（A列）の例	色
検査	受入検査／工程内検査／最終検査／外観検査／寸法測定	#8EC5FF（水色）
成形・加工	成形作業／仕上げ・バリ取り	#A8E6A1（黄緑）
段取り	段取り替え／金型交換	#FFD39A（オレンジ）
梱包・出荷	梱包／出荷準備	#D9B8FF（うす紫）
運搬	材料・製品運搬	#FFB3B3（ピンク）
事務・打合せ	朝礼・ミーティング／書類作成・記録	#C9D6DF（グレーがかった青）
清掃・5S	清掃／設備点検	#B5EAEA（うす水色）
その他	待機／その他	#D0D0D0（グレー）

5分きざみの時刻の一覧を作る（G列）

G4に下の式を貼って、G196まで下に引っぱります。6:00、6:05、6:10…と5分ずつ増えて、G196でちょうど22:00になります。

=TIME(6,(ROW()-4)*5,0)

「0.25」のような小数で表示されたら、G列を選んで右クリック →「セルの書式設定」→「時刻」で「13:30」の形を選んでください。

B列を「選ぶだけ」にする

B4〜B63を選んで、上のメニューの「データ」→「データの入力規則」を押します。「入力値の種類」を「リスト」にして、「元の値」に下を貼り、OKを押します。これでB列は、▼を押して作業の種類を選ぶだけになります。

=$E$4:$E$11

手順2：日報シートに「選ぶだけ」の入力欄を作る

「月」という名前のシートを作り、さきほどの配置表どおりに見出しを書きます。B列とC列（開始・終了）は、右クリック →「セルの書式設定」→「時刻」で「13:30」の形にしておきます。

時刻を▼から選べるようにする（B7〜C31）

B7〜C31を選んで、「データ」タブの「データの入力規則」を押します。

「入力値の種類」を「リスト」にして、「元の値」に下を貼ります。

=マスタ!$G$4:$G$196

これで時刻は▼から選ぶだけになり、「8:03」のような5分きざみ以外の時刻は入れられなくなります。

作業も▼から選べるようにする（E7〜E31）

E7〜E31も同じやり方で、「元の値」に下を貼ります。

=マスタ!$A$4:$A$63

一覧表にない作業は入れられなくなるので、「最終検査」「最終けんさ」のように人によって書き方がバラバラになることがなくなります。

作業をふやしたいときは、マスタのA列の下に書き足すだけ。日報の▼にもすぐ出てきます。

手順3：時間を自動で計算する式を入れる

ここが日報のしくみの中心です。どの式も7行目に貼って、31行目まで下に引っぱるのが基本です。開始時刻（B列）だけはちがっていて、B7は手で入れ、B8から式を入れます。

開始時刻を自動で入れる（B8に貼ってB31まで）

=IF(C7="","",C7)

1つ上の行の「終了」の時刻が、次の行の「開始」に自動で入ります。だから現場の人は、「終了」の時刻と「作業」を選ぶだけで書き進められます。

お昼休みのあとのように時間が飛ぶときだけ、開始時刻を自分で選び直します。選び直すと式は消えますが、毎週元のファイルからコピーして使うので困りません。

計算用の列を作って、かくす（L7とM7に貼って31行目まで）

L7：=IF(B7="",-1,ROUND(B7*1440,0))
M7：=IF(C7="",-1,ROUND(C7*1440,0))

開始と終了の時刻を「0時から何分か」という数字に直しています（8:15なら495）。時刻のまま計算すると、まれに1分ずれることがあるので、先に数字にしておくためです。

見なくていい列なので、L列とM列の上の「L」「M」をまとめて選び、右クリック →「非表示」でかくしておきます。

何分かかったかを出す（D7に貼って31行目まで）

=IF(OR(B7="",C7=""),"",M7-L7)

終了から開始を引いて、かかった時間を「分」で出します。開始か終了が空のときは何も表示しません。

作業の種類を自動で入れる（F7に貼って31行目まで）

=IF(E7="","",IFERROR(INDEX(マスタ!$B$4:$B$63,MATCH(E7,マスタ!$A$4:$A$63,0)),"未登録"))

選んだ作業を一覧表で探して、その作業の種類を持ってきます。「最終検査」を選べば「検査」、「段取り替え」なら「段取り」が自動で入ります。

一覧表で作業の種類を書き忘れていると「未登録」と出るので、書き忘れにもすぐ気づけます。

書きまちがいを知らせる（K7に貼って31行目まで）

=IF(OR(B7="",C7=""),"",IF(M7<=L7,"時刻NG",IF(MOD(L7,5)+MOD(M7,5)>0,"5分単位外",IF(E7="","作業未入力",""))))

まちがいがあると、K列にこんな文字が出ます。

• 時刻NG：終了が開始と同じか、開始より前になっている
• 5分単位外：5分きざみではない時刻が入っている
• 作業未入力：時刻はあるのに、作業を選んでいない

作業のフォルダを開く「▶開く」を出す（N7に貼って31行目まで）

=IF(E7="","",IFERROR(IF(INDEX(マスタ!$C$4:$C$63,MATCH(E7,マスタ!$A$4:$A$63,0))="","",HYPERLINK(INDEX(マスタ!$C$4:$C$63,MATCH(E7,マスタ!$A$4:$A$63,0)),"▶開く")),""))

僕がいちばん気に入っている機能です。一覧表のC列にフォルダの場所を書いておいた作業だけ、「▶開く」が出ます。押すと、その作業で使うフォルダ（検査の基準書や記録の置き場所など）がそのまま開きます。

フォルダの場所は、そのフォルダを開いて、いちばん上の場所の欄（アドレスバー）をクリックし、出てきた文字をコピーしてマスタのC列に貼るだけです。

「Z:\検査」のように英字1文字で始まる場所だと、ほかの人のパソコンでは開けないことがあります。「\\サーバーの名前\検査」のように「\\」で始まる形で書いてください。分からなければ、社内のパソコン担当の人に聞くのが早いです。

手順4：作業の種類ごとの合計と、書き忘れチェックを作る

シートの下のほう（34〜44行目）に、その日の合計表を作ります。34行目に「業務区分」「分」「時間」「割合」の見出しを書き（Aに業務区分、Cに分、Dに時間、Eに割合）、35〜42行目に8種類を並べます。

合計表の式（35行目に貼って42行目まで）

A35：=マスタ!E4
C35：=SUMIF($F$7:$F$31,A35,$D$7:$D$31)
D35：=C35/60
E35：=IF($C$43=0,0,C35/$C$43)

• A列：作業の種類の名前を、一覧表から持ってくる
• C列：その種類の作業の時間を、ぜんぶ足す（分）
• D列：分を「時間」に直す（90分なら1.5）
• E列：その日の中で何％だったか

E列は右クリック →「セルの書式設定」→「パーセンテージ」にすると「39%」のように表示されます。

書き忘れを見つける式（43〜44行目）

A43：記入合計
C43：=SUM(C35:C42)
D43：=C43/60

A44：実働（始業〜終業−休憩）
C44：=IF(OR(C4="",F4=""),"",ROUND((F4-C4)*1440,0)-I4)
D44：=IF(C44="","",C44/60)
E44：=IF(C44="","",IF(C43=C44,"OK","差 "&(C44-C43)&"分"))

「書いた時間の合計」と「始業から終業までの時間（休憩をのぞく）」を比べます。同じなら「OK」、ちがえば「差 30分」のように出ます。

「差 ◯分」が出たら、どこかの時間を書き忘れているということ。紙の日報ではだれも気づけなかった抜けが、その場で分かります。

手順5：作業の種類ごとに色を付ける

F列（作業の種類）に、種類ごとの色が自動で付くようにします。

1. F7〜F31を選ぶ
2. 「ホーム」タブの「条件付き書式」→「新しいルール」を押す
3. いちばん下の「数式を使用して、書式設定するセルを決定」を選ぶ
4. 下の式を1つ貼る
5. 「書式」→「塗りつぶし」で色を選んでOK
6. これを8種類ぶんくり返す

=$F7="検査"          → 塗りつぶし #8EC5FF
=$F7="成形・加工"    → 塗りつぶし #A8E6A1
=$F7="段取り"        → 塗りつぶし #FFD39A
=$F7="梱包・出荷"    → 塗りつぶし #D9B8FF
=$F7="運搬"          → 塗りつぶし #FFB3B3
=$F7="事務・打合せ"  → 塗りつぶし #C9D6DF
=$F7="清掃・5S"      → 塗りつぶし #B5EAEA
=$F7="その他"        → 塗りつぶし #D0D0D0

貼るのは「→」より左だけです。色は好きな色を選んでかまいません。同じ色にしたいときは、「塗りつぶし」→「その他の色」→「ユーザー設定」の「16進」の欄に、#のあとの6文字（8EC5FFなど）を入れます。

手順6：色で見る「5分マスの1日の流れ」を作る

紙の日報になれている人は、数字の表より「1日の流れ」をパッと見たいものです。そこでシートの右側に、1時間を12マス（5分×12）に分けたマス目を作りました。日報を書くと、その時間のマスが作業の種類の色で自動でぬられます。

マス目のわくを作る

O6に「時」と書き、P6からAA6まで横に 0、5、10、15 … 55 と数字を書きます。O7には下の式を貼って、O17まで下に引っぱります。始業が8:00なら、8時〜18時の数字が縦に並びます。

O7：=HOUR($C$4)+ROW()-7

マスに作業の種類を出す（P7に貼ってAA17まで）

P7に下の式を貼ったら、右へAA7まで引っぱり、そのまま下へ17行目まで引っぱります。

=IF(SUMPRODUCT(($L$7:$L$31<=$O7*60+VALUE(P$6))*($M$7:$M$31>$O7*60+VALUE(P$6)))=0,"",INDEX($F$7:$F$31,SUMPRODUCT(($L$7:$L$31<=$O7*60+VALUE(P$6))*($M$7:$M$31>$O7*60+VALUE(P$6))*(ROW($F$7:$F$31)-6))))

長い式ですが、やっていることは「このマスの時刻に何の作業をしていたか」を日報から探して、作業の種類を出しているだけです。中身はわからなくても、そのまま貼れば動きます。

マスに色を付けて、文字を見えなくする

P7〜AA17を選んで、手順5と同じやり方で8つの色のルールを作ります。ちがうのは2つです。

• 式の「$F7」が「P7」になる（下の式をそのまま貼ればOK）
• 「塗りつぶし」の色と同じ色を、「フォント」（文字の色）にも付ける

=P7="検査"          → 塗りつぶし・文字の色とも #8EC5FF
=P7="成形・加工"    → 塗りつぶし・文字の色とも #A8E6A1
=P7="段取り"        → 塗りつぶし・文字の色とも #FFD39A
=P7="梱包・出荷"    → 塗りつぶし・文字の色とも #D9B8FF
=P7="運搬"          → 塗りつぶし・文字の色とも #FFB3B3
=P7="事務・打合せ"  → 塗りつぶし・文字の色とも #C9D6DF
=P7="清掃・5S"      → 塗りつぶし・文字の色とも #B5EAEA
=P7="その他"        → 塗りつぶし・文字の色とも #D0D0D0

文字と背景を同じ色にすると、マスの中の「検査」などの文字が見えなくなり、色だけが残ります。紙の日報で時間をぬりつぶしたのと同じ見た目になります。列のはばをせまくすると、もっとマス目らしくなります。

手順7：火〜金のシートと、1週間の合計シートを作る

火〜金は「月」をコピーして3か所だけ変える

画面下の「月」のタブを右クリック →「移動またはコピー」→「コピーを作成する」にチェックしてOK。できた「月 (2)」のタブをダブルクリックして、名前を「火」に変えます。水・木・金も同じように作ります。

コピーしたシートの日付・名前・ラインのセルに、下の式を貼ります。

火シート
C3：=月!C3+1
F3：=月!F3
I3：=月!I3

水シートは C3：=月!C3+2（木は+3、金は+4）
F3・I3 は火と同じ

これで、月曜のシートに日付・名前・ラインを書くだけで、金曜まで自動で入ります。ちがう人が書く日や、ラインがちがう日だけ、そのシートで書き直せばOKです。

1週間の合計シートを作る

「週集計」という名前のシートを作り、4行目のA〜Iに「業務区分／月(分)／火(分)／水(分)／木(分)／金(分)／週合計(分)／週合計(h)／割合」と見出しを書きます。

A2：="週の始まり："&TEXT(月!C3,"yyyy/m/d")&"　氏名："&月!F3&"　ライン："&月!I3

▼5行目に貼って、12行目まで下に引っぱる
A5：=マスタ!E4
B5：=月!C35
C5：=火!C35
D5：=水!C35
E5：=木!C35
F5：=金!C35
G5：=SUM(B5:F5)
H5：=G5/60
I5：=IF($G$13=0,0,G5/$G$13)

▼13行目：書いた時間の合計（C13〜F13は、式の「月」を「火」「水」「木」「金」に書きかえる）
A13：記入合計
B13：=IF(月!C43="",0,月!C43)
G13：=SUM(B13:F13)
H13：=G13/60

▼14行目：働いた時間（同じく曜日だけ書きかえる）
A14：実働
B14：=IF(月!C44="",0,月!C44)
G14：=SUM(B14:F14)
H14：=G14/60

▼15行目：チェック（B15に貼って、右へF15まで引っぱる）
A15：チェック
B15：=IF(B13=0,"未記入",IF(B13=B14,"OK","差 "&(B14-B13)&"分"))

B〜F列（月〜金）は、右に引っぱってコピーしても曜日は変わりません。1つずつ貼って、式の中の「月」を「火」「水」…と書きかえてください。

手順8：式を消されないようにして、印刷の設定をする

現場の人がまちがって式を消さないように、黄色のセル以外は書きかえられないようにします。

一覧表（マスタ）のシートには、わざとカギをかけていません。作業を足したり、フォルダの場所を直したりは、各ラインのリーダーが自分でできるようにしています。

印刷すると字が小さくて読めない、というときはこちらの記事も見てください。

作ってみて分かったイマイチな点

計算式だけで作っているので、できないこともあります。使う前に知っておいてください。

夜勤（日付をまたぐ勤務）には使えない

22:00〜翌6:00のように日付をまたぐと、終了が開始より前になってしまい「時刻NG」が出ます。時刻の一覧も6:00〜22:00までです。夜勤がある職場では、このままでは使えません。

1日に書けるのは25行まで

作業をこまかく分けて書く人だと、25行では足りない日もあります。あとから行をふやすと、たくさんの式を直すことになって大変です。足りなそうなら、最初から行を多めに作っておくのがおすすめです。

時間が重なると、色のマス目がおかしくなる

同じ時間に2つの作業を書いてしまうと、マス目にちがう作業の色が出ることがあります。チェック列でもこのまちがいは見つけられないので、「同じ時間に2行書かない」と現場に伝えています。

「▶開く」はパソコンのExcelでしか使えない

会社の共有フォルダは、スマホのExcelやブラウザで開くExcelからは開けません。会社のパソコンに入っているExcelで使ってください。はじめて開くときに、確認のメッセージが出ることもあります。

このExcel日報が向いている職場・向かない職場

向いている	向かない
会社でふだんExcelを使っていて、新しいソフトは入れにくい	夜勤など、日付をまたぐ勤務が多い
作業の種類がだいたい決まっている（60個以内）	スマホやタブレットだけで日報を書きたい
会社のルールで、マクロ入りのファイルが使えない	何人分もの日報を、その場で1か所に集めたい
パソコンにくわしい人が社内に少ない	作業が毎日バラバラで、一覧表にまとめられない

何人分もの日報をまとめて見たい、スマホで書きたい、となってきたら、Excel以外の方法のほうが向いています。現場にタブレットとQRコードを入れたときの話は、こちらに書いています。

よくある質問

Excelのファイルはダウンロードできますか？

ファイルの配布はしていません。そのかわり、計算式と設定はぜんぶこの記事に書いています。手順1から順番に作れば、同じものができあがります。自分で作っておくと、職場に合わせて直すときにも迷いません。

古いExcelでも動きますか？

動きます。Excel 2007のころからある計算式だけで作っているので、最新のExcel（Microsoft 365）でも、会社にある少し古いExcelでも同じように使えます。

「#NAME?」や「#REF!」と出てしまいます

シートの名前が記事とちがうことがほとんどです。「月」「火」「水」「木」「金」「週集計」「マスタ」になっているか、画面下のタブを確かめてください。式の一部だけコピーしていないかも見てください。

10分きざみや15分きざみにできますか？

できます。マスタG列の式の「*5」を「*10」や「*15」に変え、K列の式の中の「5」（2か所）も同じ数字に変えます。行の数は少なくて済むので、G列は22:00が出たところで止めてかまいません。

まとめ：Excel日報は「書くことを減らす」と「一覧表にまとめる」で長く使える

手書きの日報をExcelにするとき、いちばん気をつけたのは現場の人が書くことをふやさないことでした。開始時刻は自動、作業は選ぶだけ、日付は月曜に書くだけ。実際に選ぶのは「終了の時刻」と「作業」の2つです。

• 作業の名前・種類・フォルダの場所は、一覧表（マスタ）1か所にまとめる。作業がふえても一覧表を直すだけ
• 日報から作業のフォルダを開けるようにする。ただし記録そのものは今ある用紙のまま
• 日報にはカギをかけ、一覧表は現場にまかせる
• 5分マスの色で、紙と同じように1日をふり返れる

このあとは、品番ごとに何分かかったかの合計や、現品票のバーコードを読み取って品番を入れるしくみも足していく予定です。1か月分をまとめて見るシートも作りたいと思っています。

ほかのExcelの便利なワザは、こちらにまとめています。