Excel2013対応
---topics:日時の処理、条件付き書式---

其ノ12 Excelと時間 前編

タイムシートってありますよね。 作業時間をつけるアレです。 みなさんは作業時間の記録、どうしてますか? 「タイムカードを打刻して、上司にまわしてる」って方が多いですかね。 でも、打刻したタイムカードは計算する必要があるわけで。 たぶん経理担当の人が実際の集計作業をしてると思うんですが、 それにExcelが使われること、けっこうあるんです。 でね、今回は、タイムシートを作りながら、 Excelにおける時間の処理に慣れてもらおうかと思ってるんですよ。 「…いや、ウチの会社web勤怠だから、Excelで集計とかないし」とか言わないでください。 タイムシート作成は、目的ではなく手段ですから。 あくまでも主眼は、「Excelで時間を扱う」ってトコロにあるんです。なので。

ではまず、イメージ作りから。
タイムシートって、もちろん会社によって違うんだけど、 だいたいこんな感じ↓※1ですよね。

タイムシートのイメージ
※1 タイムシートのイメージ

1カ月ごとの作業時間を集計するのが目的だから、 まず必要なのは日付の欄ですよね。 あと曜日も。 で、毎日の出社時刻退社時刻。 最後に、毎日の勤務時間の合計の値を、今月の勤務時間として表示すればよい。 必須項目は、これくらいかな。
もちろん、勤務時間の集計において考えなきゃいけないことって、会社によって異なりますよね。 残業時間の計算とか、さらには深夜残業とか。 あとは、「スタート時刻を15分単位で繰り上げ、終わり時刻を15分単位で切り下げる」、 例えば、8:49にタイムカードを打刻してもスタートを9:00ちょうどで計算したり、 18:07に帰っても終わり時刻は18:00ジャストで扱われたり、といった、 その会社ごとのルールもありますよね。 その辺りは、会社入るときに最初に説明がされたと思います、きっと。 だから、会社によってはものすごく煩雑な計算が要求されたりするんでしょうけど、 今回は、すごくシンプルなヤツを想定することにしましょうか。
例えば…、単純に終わり時間から入り時間を引いたものを、1日の勤務時間の対象、としましょう。 で、それは、15分単位で計算に入れる、と。 例えば、ある日の勤務時間が8時間36分だったら、8.5時間。 8時間49分だったら、8.75時間、と計算することにします。 で、さらにそこから、休憩時間を引いたものが、 最終的な1日の作業時間になる、としましょうか。 今回はとりあえず、残業時間のこととかは考えないことにします。
あ、そうそう、上の画面はあくまでも「イメージ」ね、「イメージ」。 実際作っていくなかで、項目など変わることもあり得ますので、そういうものとしてご理解を。

では、さっそくいきましょう。
まず、新規Excelを開いてください。 で、それに適当な名前を付けて保存しましょう。 「timesheet_れんしゅう」とか、ね。
保存できたら、項目を作ります※2。 画面↓のような感じで良いと思うんですよ、差し当たっては。

項目を作る
※2 項目を作る

罫線もあらかじめ引いておきましょう。
タイムシートは1カ月分のデータの集計だから、 1カ月の最大日数、すなわち31日分、罫線を引いておけば良いですよね※3

罫線を引く
※3 罫線を引く

では、枠ができたところで、試しに日付を入れてみましょう。 仮データを入れてみた方が、イメージ、湧きますもんね。
ところで、給与計算には「締日」というのがありますよね。 15日締めとか、20日締めとか、25日締めとか。 今回はとりあえず、20日締めという想定でいきましょうか。 20日締めなので、月のスタートは21日からです。 僕はとりあえず、「5月21日」からのデータを作ってみることにしました※4

「5/21」と入力
※4 「5/21」と入力

「5/21」と入力すると、Excelの方で気を利かせて、 「5月21日」と表記されるようになります※5

「5/21」と入力すると、「5月21日」と表記される
※5 「5/21」と入力すると、「5月21日」と表記される

あとはコイツを、オートフィルの連続データで埋めていきましょう※6

1カ月分埋めた
※6 1カ月分埋めた

さて、ここで一度、入力したセルにカーソルを合わせてほしいのですが…。 入力した日付に、今年の年数の情報が西暦で付加されてますよね※7

今年の5月21日、ということになってる
※7 今年の5月21日、ということになってる

僕のケースだと、僕がこのページを書いているのが2013年なので、 「2013/5/21」ということになります。 僕が入力したのは「5/21」だけなのですが。
もし2014年に作業したとしたら、ココは「2014」になります。

実はExcelは、日付の情報を「シリアル値」という形でもっています。 シリアル値っていうのは、時間情報のIDみたいなもので、 「1900/1/1」を1として、そこから1日経過するごとに1ずつ加算されていく仕組みになっています。 だから、「1900/1/1」のシリアル値は「1」、「1900/1/2」は「2」となります。
西暦1900年って言うと、100年以上前ですね。当然僕は生まれてません。 東京オリンピックより前だし、第二次世界大戦、いやそれどころか第一次大戦よりも前。 ライト兄弟が飛行機飛ばすのにまだ成功していなかった頃だったりします。 とにかく相当な昔ですな。
…でね、シリアル値の話に戻るんですが、 ここで一度、シリアル値を体感してもらうために、表の外側に、今入力した日付のシリアル値を表示させてみましょうか。 まず、A列(日付の入っている列)をコピって、空いている列、どこでも良いので貼りつけてください※8

シリアル値を表示させてみよう
※8 シリアル値を表示させてみよう

で、書式を「数値」にしてやる※9と、 シリアル値で表示されます※10

書式を「数値」にしてやると…
※9 書式を「数値」にしてやると…
シリアル値が表示される
※10 シリアル値が表示される

できましたか?「2013/5/21」のシリアル値は「41415」になります。 同様に、「2013/5/22」のシリアル値は「41416」になります。 こんなふうにしてExcelは、日付の情報を重複しないようにもっているワケです。
あ、もし、みなさんが作業されるのが「2014年」だとしたら、画面の値とは変わってきますよ。 わかってるとは思いますが、念のため。
シリアル値が確認できましたら、この列はもう不要なので、削除しておいてください。

ちなみに、上の解説は、Windows版Excelでの話。 Macintosh版Excelでは、「1904/1/2を1とする」仕様になってます。 つまり、Win/Macでシリアル値の基準が異なってしまうんです。 なので、Win/Mac両方で作業する方は、このことを頭に入れておきましょう。

では次に、「曜日」の欄に仮データを入れてみましょう。
2013年5月21日は火曜日です。なので、「火」と入れてやりましょう。 以下は、オートフィルの連続データで、曜日が次々と埋まっていきます※11 ※12

「火」と入力し、オートフィルすると…
※11 「火」と入力し、オートフィルすると…
曜日の連続データができる
※12 曜日の連続データができる

ハイ、曜日が入力できました。
…でも、何だかのっぺりしてませんか? もうちょっと、カラフルにと言うか…、 例えば、土曜日はとか、日曜日はとか、 色が塗ってあると良いかなと思うんですよ。
なので、色を塗ってみました※13

曜日に色を塗ってみた
※13 曜日に色を塗ってみた

「土」は青の塗りつぶし、「日」は赤で塗りつぶしのうえで文字色を白にしてみました。

さて、日付と曜日を入力できました。 できたんですが…、ここでね、せっかく入力してもらったのに大変恐縮なんですが、 コイツらを1回消して、翌月のデータにしてほしいんですよ。 つまり、6月21日からのデータに。
というのも、タイムシートって、同じものを毎月使いまわしていくことになると思うので、 そのシミュレーションのためなんです。 実際に使う人の気持ちになるって、クリエイターとして、大切ですよね。 なので、お手数ですが。

というわけで、日付を全部消して、入れ直します※14 ※15

日付を一旦delete
※14 日付を一旦delete
6/21から入れ直した
※15 6/21から入れ直した

日付を変えたら、曜日も直さないとね。 2013年6月21日は金曜日です※16 ※17 ※18

曜日も一旦deleteする 色塗ったところが残ってるなあ…
※16 曜日も一旦deleteする 色塗ったところが残ってるなあ…

セルに直接色塗ってるから、一度セル色なしにしないとダメですね。

「塗りつぶしなし」にする
※17 「塗りつぶしなし」にする

一旦キレイな状態にしておいて、曜日を入れ直します。

曜日を入れ直し、色も塗り直した
※18 曜日を入れ直し、色も塗り直した

やってみて、どうでした? 曜日の色を塗り直すところ、けっこう手間だったんじゃないかと思うんですよ。 日付や曜日の入力は、オートフィルでダーッ!と一気に埋められるけど、 曜日の配置は毎月変わりますからね。 正直、コレを毎月やるのは、ちょっと面倒です。
こういう時は、「条件付き書式」を使いましょう。 まず、セル塗りつぶしの色を解除してください。 文字色も「自動」に戻しましょう※19

一度プレーンな状態に戻す
※19 一度プレーンな状態に戻す

ハイ、プレーンな状態に戻しました。
では、条件付き書式を設定します。 まず、範囲を選択しましょう。 「曜日」列のB2:B32を選択します※20。 B32セルは、現状ではblancですが、コイツも範囲に含めてください。 31日終わりの月をまたぐときは、このセルにも値が入りますので。

罫線引いたところまで、範囲に含めてくださいね
※20 罫線引いたところまで、範囲に含めてくださいね

範囲を選択したら、「ホーム」タブ→「条件付き書式」へ※21

条件付き書式を設定する
※21 条件付き書式を設定する

「条件付き書式」を使うと、「セルの値が○○だったら、色を塗る」といった、 条件に応じた表示の仕方を設定できるんです。 今回は、「曜日の値が"土"だったら、青く塗る」 「"日"だったら、赤く塗って文字色を白にする」っていうふうにしようかと。
なので、「セルの強調表示ルール」→「指定の値に等しい」を選択します※22

「指定の値に等しい」ときに色を塗る、としたい
※22 「指定の値に等しい」ときに色を塗る、としたい

こんな↓画面※23になります。 「土」のときの書式と「日」のときの書式は、それぞれ指定してあげなければなりませんので、 ひとつずつ設定していきましょう。まずは「土」から。
左の「次の値に等しいセルを~」のボックスには「土」と入力してください。
右には、値が「土」のときの書式を指定します、が、 プリセットされている書式に「青く塗りつぶす」がないので、 「ユーザー設定の書式」へ進みます。

「土」に等しいとき、青く塗りたい
※23 「土」に等しいとき、青く塗りたい

「塗りつぶし」の色に「青」を指定します※24

設定完了
※24 設定完了

これで「土」のときの条件付き書式は設定完了です。
「土」のところが青くなりましたね※25

うまくできたか、確認しましょう
※25 うまくできたか、確認しましょう

「日」についても同様に、設定しましょう※26 ※27

塗りつぶし色に赤を、フォント色に白を指定
※26 塗りつぶし色に赤を、フォント色に白を指定
結果
※27 結果

できました。
では、本当に本当にお手数なんですが、もう一回入れ直してみましょう。 また、一旦データをdeleteします※28

deleteすると、色も消える
※28 deleteすると、色も消える

データを消すと、曜日のところの色も一緒に消えてくれます。
では、今度は7月21日から日付を入れていきましょう。 そして、2013年7月21日は日曜日なのですが…※29

曜日を入力しただけで、色が付く
※29 曜日を入力しただけで、色が付く

「日」と入力しただけで、色が塗られました。 これが条件付き書式の実力です。
確認できたところで、曜日全部、埋めてくださいね※30

7/21からのデータができた
※30 7/21からのデータができた

どうでしたか? さっきよりも、曜日の入力、ちょっとだけラクになりましたよね。

…でもね、どうせならもう一段階ラクしたいなー、と思うんです。 text関数を使うと、 曜日を自動で出せるので、それを使ってみましょう。
一度曜日のところを全部消してください。
そのうえで、曜日のところにtext関数をいれます。 =text( 日付のセル ,"aaa") っていう式を入れてください※31 ※32 ※33

=text( と入力し、日付のセルを選択
※31 =text( と入力し、日付のセルを選択
表示形式はaaa
※32 表示形式はaaa
曜日が表示された
※33 曜日が表示された

この式を使うと、日付の値に対応した曜日が返されるんです。
text関数の式のかたちは、

=TEXT(,表示形式)

となっていて、 1つ目の引数「値」はベースとなる値のこと。 それを、2つ目の引数「表示形式」で指定したスタイルに変換します。 「表示形式」にはさまざまなスタイルを選ぶことができ、 今回使用した"aaa"は、「値を曜日のかたちで、しかも "月" "火" などの省略形で表示する」という意味です。 ちなみにココを、"aaaa"とaを4つにすると、"月曜日" "火曜日"というスタイルになったりします。
でね、1つ目の引数「値」には、「日付」セルを選びました。 Excelは日付をシリアル値でもっているので、そのシリアル値に従って適切な曜日が返される、というワケです。
text関数は、指定する表示形式によってさまざまなスタイルを実現できるのですが、今日はこのぐらいにして。 では、式を全セルにコピってやりましょう※34

曜日を関数で処理できた
※34 曜日を関数で処理できた

このように、曜日のところを関数で処理しておけば、日付を変えても自動的に曜日欄が埋まるようになるハズ!ですよね。
では、日付欄をもういちど5/21からのデータに戻してみましょう。 一旦日付データをdeleteすると…※35

日付欄を空白にしたら、こんなことに…
※35 日付欄を空白にしたら、こんなことに…

***おおっと***

…全部土曜日になってしまいました。 う~ん、なんかカッコわるいな…。
Excel君の気持ちもわかるんですよ。日付のところが空白になっちゃったから、 それを「ゼロが入っている」と解釈して、シリアル値「0」に対応する曜日を返しちゃった、ていう…。 (ちなみに、シリアル値「1」は1900/1/1なんですが、 シリアル値「0」は1900/1/0、つまり1899/12/31になります。 1899/12/31は土曜日、なんでしょうか? 僕は昔のカレンダーを調べてないのでわかりませんが…。)
いずれにせよ、あまりスタイリッシュじゃないですね。 例えば、このタイムシートを20人の社員に渡したとして、20人全員に 「ところでさあ、あのタイムシートの曜日のところ、何で最初は全部土曜日なの?」 とか聞かれたら、面倒以外の何物でもありません。 関数の入れ方を間違ったわけじゃないんだけどなあ…。
ですが、せっかくなので、曜日のところの関数の入れ方をひと工夫してやりましょう※36

if関数を使って空白対策
※36 if関数を使って空白対策

曜日のところの式を、こんなふうに↓変えてみました。
= IF (A2="", "", TEXT(A2, "aaa"))
式の後半は、さっきと変わりません。変わったのは前半。 if関数を使って、 「もし日付欄が空白だったら、曜日欄も空白を表示してください。 そうじゃなければ(日付欄に値が入っていたら)、text関数の計算をしてください」 という指示をしています。
""チョンチョン(ダブルクォーテーション)のあいだには何も入れないでください。 半角スペースもナシですよ。 ""チョンチョンって、=if (A2>=60, "合格", "不合格" )みたいに、文字列を指定するときに使いますよね。 そこに何も入れないということは、blancである、という意味になります。 だから、「A2=""」という式で、 「日付欄が空白だったら」という条件を設定してやることができるわけです。
では、式を曜日欄全部に入れてやりましょう※37

式を全セルにコピー
※37 式を全セルにコピー

じゃあ今度こそ!5/21から日付を埋めてください※38

空白対策終了
※38 空白対策終了

こんな感じになりました。
長くなってきたので、今回はここまで!つづきは次回!

Copyright(C)森田表計算