一問一答の一歩

エクセルでの勤務表の効率化について説明する機会があったので、この場で以下の4つの勤務表の時間計算の効率化のポイントについて説明します。

• ・始業時間と終業時間の簡素化
• ・ドロップダウンメニューの作成
• ・1日の勤務時間の自動入力に休憩時間を反映させる
• ・勤務時間の合計を自動入力する

1-1入力の簡素化の方針

入力の簡素化の実装する方法としては以下の観点4つの方針が考えられる

入力を行うセルと、時刻を表示するセルを分けるか

時刻の表示は、始業時間と終業時間を分けるか

方針A:入力セルと時刻表示セルを分ける場合

→以下、この方針について解説する。

※この形式の場合、入力セルドロップダウンメニューを実装してもよいように思える

方針B:時刻表示セルに入力記号を入れる場合

→簡易入力を行ったあと、マクロで一括変換をすればよいのだが、本ページでは解説対象外とする。

1-2事前準備,Time関数について

入力セルと時刻表示セルを分けて作成する場合は、時刻表示用のセルにif関数、又はSwitch関数を入力すればよい。if関数の使用方法の概要については以下の通りである。

if("条件","条件を満たしている時の値","条件を満たしていない時の値")

※実は､条件付き書式を用いて対応する方法もあるが、今回は説明を省略する。

ここで、条件に時間を設定して上げればいいのだが、関数の中に時間の記載するにはTime型の表示用いる必要がある。

具体的な方法としてはTime関数を以下のように使用すればよい。

(例では､9時0分0秒を設定している)

Time(時,分,秒)

実際にTime関数を用いて入力したエクセルシートを載せると以下のようになる

※Time関数の結果､の表示が変だったり、見にくかったりした場合は表示形式を変更する。(具体的なやり方はページ末の補足説明を参照)

1-3入力の簡素化の実装方法

入力の簡素化を実現するには、以下のことを満たしていればよい

Aが入力された→始業時間に9時、終業時間に18時を指定する。

それ以外→何も入力しない

これば条件分岐であるので、if関数を用いれば実装することができる。具体的には下記のような関数を作成すればよい

if(入力セル="設定する記号",Time(時,分,秒),"")

始業時間について入力した図を記載すると以下の通りである。

以下のようにすると、入力セル(上記図)に設定した記号(上図の場合はA)を記載すると、始業時間と終業時間の列に勝手に値が記載されるようになる。

ドロップダウンメニュとは、下図のようにエクセルにあらかじめ指定した値を選択して入力をできる方法の一つである。

ドロップダウンメニューに関しては、以下のサイトに既に分かりやすい説明があったので、そちらを参照すると良い。

なお、補足としては、上記のリンク先では、同一シート状に選択できる値の一覧を作成しているが、以下の図のように別シートに値の一覧を作成し、て別シートの値を一覧とすることもできる。

○休憩時間を考慮したTime型の計算について

一日の勤務時間は終業時間から始業時間を引いてあげればよいのだが、そのまま引いた場合は休憩時間などは考慮されないので、以下の画像のように実際よりも多くなってしまうことが多い

業務の休憩時間は大抵1時間であるので、1時間分を引いてあげればよい。

しかし、Time型の値に1を引くと24時間分引かれてしまい、数字の指定は日にち単位で扱われる。そのため、1時間分を表すには時間で換算した1/24引いてあげればよい。

始業時間のセル-始業時間のセル-(1/24)

実際のエクセルシートに記載すると以下のようになる。

○勤務時間に休憩時間を反映させる方法

例えば、12:00～13:00に昼休みをとるような状況を考えると、以下のように考えてあげればよい。

• 始業時間が午前中であり、かつ就業時間が午後→勤務時間を一時間減らす
• 勤務時間が午前中のみ、又は午後のみ→勤務時間をそのまま使用する。

これを実現するためには、毎日の勤務時間のセルを計算するセルに、以下のif文の関数を入れ子方式で入力すれば良い。

if(始業時間のセル<Time(12 ､0､0),if(Time(12 ､0､0)<終業時間のセル,始業時間のセル-始業時間のセル-(1/24),終業時間のセル,始業時間のセル-始業時間のセル),終業時間のセル,始業時間のセル-始業時間のセル)

勤務時間の合計の算出については各日付の勤務時間のを足し合わせればよい。

そして、勤務時間を足し合わせるためには以下のSUM関数を使用すれば良い。

SUM(カウントする勤務時間の始点:カウントする勤務時間終点)

勤務時間の合計のセルに対して上記の関数を入れるので、作成した場合は以下のようになる

Time関数を用いた結果、意味不明な数字が出てきたり、秒まで表示されて見ずらいときなどがあるが、これらの現象はほどんど表示形式があっていないことに由来している。

これを修正するためには、エクセルの上部にある部分から「ホーム」→「数値」と書いてある部分の上方にあるドロップダウンメニューという順番にクリックを行う。

クリックを行うと、上図のドロップダウンメニューが出てくるはずなので、「その他の表示形式」を選択する。

すると、下図の「セルの書式設定」という画面が出てくるので、左側の「分類」を選択し、時刻の部分から「hh:mm」と書かれている表示形式を選択する。

上記の選択をして「OK」を押すと表示が直っているはずである。