こんにちは、朱夏です。
今回は、工数入力用のシートを作っていきたいと思います。
出来上がりのイメージはこんな感じです。
前提条件として、シート名は「【テンプレート】工数シートYYYYMMDD」とします。
これをシートコピーして、以下の例の様にリネームして１日１シートずつ工数を入力する仕様にしました。
例：工数シート20230601
日付、名前、工数合計、勤務時間、残業欄は自動で表示される様にするので黄色で色付けしました。それでは、各機能について解説していきます。
日付
日付が表示されるセルは、工数が入力されている場合にシート名を元に日付を表示します。
MID関数でシート名の最後から8文字を取得して日付を形成する関数を使っています。
上記関数を日本語訳するとこんな感じになります。
CELL関数でファイル名を取得すると、フルパス付ファイル名+シート名で構成されますので、下記のファイル名の太字部分を取り出して使っていることになります。
実際に工数を入力すると、以下の様に表示されます。
今はシートをリネームしていないため、日付はYYYY/MM/DDと表示されます。
あとは、オートフィルで下まで複製すれば、日付は完成です。
名前
名前欄もCELL関数を使用してファイル名から自動的に表示しています。
入力している関数はこんな感じです。
CELL関数で取得したフルパスのファイル名から、”(“から”)”までの文字を表示する様に組んでいます。
ポイントはファイル名からFIND関数で”(“を検索していますが、開始位置を「個人用工数入力ファイル」の文字列が出てきたところからに指定することで、上位のフォルダで”(“が使われていても、名前部分を取得できるように工夫しました。
といっても万能ではないので、上位フォルダに「個人用工数入力ファイル」が含まれている場合、ファイル名の”(“をうまく拾えないという可能性は完全に0にできているわけではないですけどね。
こちらも同じく下まで複製します。
工数
工数欄は、15分単位で入力してもらう様に入力規則を指定します。
工数を入力する欄をすべて選択した状態で、「データ」タブのリボンから、「データの入力規則」を選択します。
表示された画面で、入力条件を指定していきます。
設定
「設定」タブから。
- ユーザー設定を選択
- 空白を無視するにチェック
- 数式に=MOD($D3,15)=0を入力
入力時メッセージ
「入力時メッセージ」のタブでは、セルを選択した時にポップアップメッセージを表示させるかと、その内容を設定できます。
今回は、15分単位で入力する様案内メッセージを表示させる設定にしました。
エラーメッセージ
「エラーメッセージ」タブでは、入力規則通りに入力されなかった時にメッセージを表示させるかと、そのメッセージ内容などを設定できます。
今回は、エラーとして入力を「停止」させ、15分単位で入力できていないことを伝えるメッセージを表示させる設定にしました。
工数合計、勤務時間、残業
次は、工数の集計部分を一気に解説していきます。
集計方法は、分数を合計したもの、HH:MM形式に変換したもの、残業時間をHH:MM形式にしたものの3種類を表示させる様にしました。
工数合計
工数合計は、単純にSUM関数で合計を出しています。
その日の工数が1つも入っていない場合（0の場合）は表示しないようにしています。
勤務時間
勤務時間は、工数合計を変換して表示します。
入力している関数は以下の様なものになります。
工数合計の分数を表示しているD22セルの数値を取得して、24時間と60分で割っているといったイメージですかね？
工数合計510分を取得した場合、勤務時間を数値でそのまま表示させると以下の様になります。
0.3541666666666667
この状態で表示形式を変更すると、ちゃんと時間を表示してくれます。
残業
残業は、1日の勤務時間が8時間を超えた場合は、超えた分を残業として表示させる様にしています。
関数は以下の様になります。
勤務時間の合計が8時間未満の場合は、残業時間を”0:00”と表示させる様にしています。
勤務時間を表示しているセルを参照して、8時間以上となていた場合、勤務時間から8時間を引いた時間を表示する様にしました。
数値として8を引いてもうまく8時間を引くことはできないので、TIME関数で8:00:00を指定して引いています。
カテゴリ
「カテゴリ」欄は、工数の作業内容のカテゴリをプルダウン形式で選択できるようにしました。
後々のメンテナンスがしやすいように、プルダウンリストの指定にはOFFSET関数を使ってリストの増減や修正だけでプルダウンに反映されるようにしました。
まずはプルダウンシートを作成して、カテゴリのリストを作成しました。
プルダウンに使用するリストは、1つのシートにまとめておくとメンテナンスがしやすくてよいと思います。
そして、工数シートのカテゴリ入力欄を選択した状態で「データの入力規則」を設定します。
入力値の種類は「リスト」を選択します。
元の値に入力した関数は以下になります。
OFFSET関数を使用して、プルダウンシートの$B$3から0行、0列ずらした場所を起点として、COUNTA関数でB列にデータが入力されているセル数分をプルダウンとして表示させるという様に記載しました。
空欄であるB3セルをプルダウンに表示させると、データが入っていないので1セルずれますが、B列全体としてタイトルの「カテゴリ」が入力されているB2セルもCOUNTA関数で参照されているため、+-0としてプルダウンを取得てきています。
備考
「備考」欄は、フリーテキストを入力できる部分なので、特に何もせず、枠だけ作っておきます。
まとめ
というわけで、工数入力シートを作成しました。
工数シートのフォーマットは元のフォーマットに合わせて作っているので、他にも色々改善点はありそうですが、今回はこんな感じでまとまりました。
さて、次は個人で入力した各ファイル、各シート、の工数を1つのファイルに集計していきます。
集計にはPowerクエリを使用していきます。
それでは、次回もよろしくお願いします。