最初の一日だけ入力してスケジュール表を作成するテクニック!

エクセルでスケジュール表作成

こんにちは、エクセル使いこなし隊 隊長の吉井良平です。

 

エクセルを使って、毎月のスケジュール表、シフト表を作られている方も多いと思います。会社だけじゃなく、いろんなサークルやPTAなどでも作られてますよね。

これもちょっとしたコツで毎月の作業が楽になりますので、魔九郎君と一緒に作っていきましょう!

月の最初の一日だけを入力すれば、その後の日付を入力したり、曜日ごとに色を変えたりする必要がなくなりますよ。

今回からは実務編ということで、魔九郎君のお相手は先輩の星羅月子さんになっています。

(株)マカーイは、魔界に関係した人じゃないと入れないので(?)、お察しの通りセイラームーンから付けた名前ですが、さすがにツインテールはどうかと思ったので、ガンダムのセイラさんのイメージです。

では、いつものように小芝居から・・・

 

今回使用したエクセルファイルはこちら。

download

 

営業カレンダーを作ろう

星羅
ねぇねぇ魔九郎君。
魔九郎
なんですか?星羅センパイ。
星羅
月が変わるから、営業スケジュール表を作るんだけど・・・
魔九郎
ハイ。
星羅
これ、今月から魔九郎君の仕事だからね。
魔九郎
エッ!
星羅
これは一番下っ端の仕事なの!

私は2年やったから、もう卒業よ!

魔九郎
はぁ。じゃあ、フォーマットはどこにあるんですか?
星羅
あなたが作るのよ!最初だから、勉強のために作るの!

私が教えてあげるから、一緒に作ろ♥

魔九郎
星羅センパイ・・・ グフ!
星羅
持つべきものは、使える後輩・・・!

ちょっと頼りなさそうだけど、仕込んでやるんだから!

 

まずは大枠を作ろう

星羅
ウチの会社はスポーツ系だから、土日の出勤があるんだよねー。スポーツの大会って、大体土日だもんね。

だから、土日出勤した分の代休がかぶらないよう、スケジュール表でシフトを組んでるんだ。

シフト表に必要なのは何だと思う?

魔九郎
日付と、名前ですかね?そのぐらいしか必要ないような・・・
星羅
そうそう、後はタイトルぐらいよね。曜日もあった方が良いかな。

まずはそこまで作ってみて。

日付、曜日、名前を入力したところ

日付、曜日、名前を入力したところ

魔九郎
できましたよ。
星羅
じゃあ、ここに日付を入力していってね。毎月同じことを入力しないで良いように、最初の日付だけ入力して、後は足し算で入力するのがコツだよ♥
日付も計算できる!

エクセルでは、日付は実は数値で表されています。

日付を入力すると、エクセルが勝手に「1900年の1月1日から何日経っているか」を計算してくれているのです。

その数値を、書式設定で「日付」として表示してくれています。

なので、足し算も引き算も出来ます。(使うことはありませんが、かけ算、割り算もできます)

この辺、分かりにくいと思いますので、動画で説明しますね。

魔九郎
えーと、じゃあ最初の日付だけ入力して・・・、後は1ずつ足していけば良いんですね。
エクセルで日付に足し算をする

日付に足し算をする

星羅
そうそう、31日まで作ってね。

星羅
曜日の欄は、日付の値を曜日で表示させるだけだから、入力するんじゃなくて、日付欄と同じ値が表示されるようにしようね♥
曜日の表示

曜日は入力しなくても、書式設定でエクセルが曜日として表示してくれます。便利ですよね。

セルの値を同じにする数式

曜日は、入力するのではなく、日付のセルと同じ値になるよう、数式で入力する

日付を曜日だけに表示する

日付を曜日だけの表示にする

○○”曜日”まで表示させたい場合は、aaaa にしてみてください。

魔九郎
できました!

ということは、曜日を入力する必要はないんですね。

星羅
ほとんどの場合は、入力しなくていいよ。

あとはタイトルもこの応用で、日付から自動的に表示されることにしようね。

日付タイトル

タイトルも日付にしてしまう

ユーザー定義で書式を設定する

ユーザー定義で書式を設定する

スケジュール表の表示

スケジュール表の表示になりました

日付の表示形式に関して

yyyyで西暦を表します。

年号(平成○年)を入れたい場合は、gggeです。

mは月、mmは月を二ケタで入れたい場合です。(例:07月)

その他に表示させたい文字があれば、””(ダブルコーテーション)で囲んで入力します。

魔九郎
できました!

でも、列の幅が広くなっちゃいましたね・・・

星羅
その辺は後で調整するから大丈夫よ。

次は土曜日と日曜日のセルに色をつけていこうね♥

 

・ここまでの動画

 

まずは曜日を数値で表す

星羅
何日が何曜日か、というのは、毎月変わっていくよね。

だから「土曜日と日曜日だったらセルに色を付ける」と条件を付けて書式を設定していくんだ。この機能のことを「条件付き書式」っていうんだよ。

魔九郎
星羅さん、すごいですね! 

星羅
私も素牛さんに教えてもらったのよ。

で、まず「土曜日と日曜日だったら」という条件を作らないといけないの。エクセルが日付を、土曜か日曜か分かるようにするために、曜日も数値にしていくよ。

WEEKDAY関数

日付から曜日を数値で表すためには、WEEKDAY関数というのを使います。

=WEEKDAY(日付,種類)

WEEKDAY関数のボックス

種類のところには、 1,2,3 の3種類が選べます。日曜始まりか、月曜始まりか、のどちらかですね。

今回は、土曜日と日曜日が連続した値になってないと困るので、2か3にします。(2にします)

この辺は私も詳しく覚えていないので、「曜日を数値に変換できる」ということを覚えておけば大丈夫です。

「曜日 数値」でググれば出てきます。

日付の数値を 7で割った余りがどれかの曜日になる、と考えたら分かりやすいかもしれません。

魔九郎
どこに入力していけば良いんでしょう・・・

鬼瓦課長の列でしょうか・・・ 

星羅
あとで印刷させなければ良いだけなので、最後のL列にでも入力しておけばいいよ。
魔九郎
できました!

こんな感じでしょうか?? 

WEEKDAY関数入力例

WEEKDAY関数入力例

星羅
そうそう、これで曜日を数値で表すことができたね。

 

条件付き書式を設定しよう

星羅
今、土曜日は6、日曜日は7の数字になってるよね。

なので、6と7の場合だけに色を付けるようにするよ。

条件付き書式を選ぶ

条件付き書式を選ぶ

星羅
今回は、違うセルの値を見ないといけないので、「新しいルール」を選んで、数式を入れないといけないんだ。
条件付き書式ダイアログボックス

新しいルールを選択

星羅
L列が 6と7の場合、ということで、5より大きい場合に、セルに色が付くように設定しようね。

L列の後の数字に、$マークが付いていると絶対参照になっちゃうので、$マークを外しておいてね。

数式を入力して書式設定

数式を入力して書式設定をしているところ

*絶対参照、相対参照が分かりにくい方は、こちらの記事をご覧ください。

魔九郎
できました!

ちゃんと色が付いてます! 

 

・ここまでの動画はこちら

 

休日も設定しよう

魔九郎
でも良く見ると、5月の最初はゴールデンウィークですよね・・・

僕は休んで旅行に行きますよ。 北海道新幹線に乗りにいかないと!グフ! 

星羅
そうなのよね・・・面倒くさいのよね・・・

エクセルは、祝日は調べてくれないんだよね・・・毎年変わる可能性があるからかもしれないけど。

ちょっとネットから、休日を調べて表を作ろうね・・・ あー面倒くさ。

もし会社の営業日の表があれば、それを使いましょう。その方が話が早いので。

*今回は、「こよみのページ」さんのデータを使わせていただきました。

魔九郎
できましたよ。 

星羅
じゃぁ、これをVLOOKUP関数で読み込んでいくからね・・・

祝日も色を付けるのに、もう最初から 7 の値を入力しておこうね・・・ あー面倒くさ。

*VLOOKUP関数に関しては、魔九郎君の新入社員研修5日目でも紹介しています

祝日一覧シート

祝日一覧シート

魔九郎
えーと、じゃあ次の列にVLOOKUPで入れていけばいいんですね・・・と。 

VLOOKUP関数の入力例

VLOOKUP関数の入力例

魔九郎
あっ!なんだか変な文字が出てきてますよ! 

星羅
祝日一覧の中に無い日付なので、エラーが出るよ・・・ あー面倒くさ。

えーと、そういう時は、IFERRORという関数を使うんだっけ・・・

IFERROR関数

エラーの場合に、別の値を表示させる関数です。

=IFERROR(計算式,エラーの場合の値)

慣れないと面倒くさいですが、関数を使っている限りはエラーから逃れられないのです。

慣れてください・・・ か、もしくは早めにVBA(マクロ)に挑戦しましょう。

IFERROR関数を入れてみた

IFERROR関数を入れてみた

星羅
これでエラーが無くなったね。で、次は祝日の場合は、曜日の値を 7にする・・・のよ。

えーと、IF関数を使って・・・ あー面倒くさ。

 IF関数

もし○○ならば××、○○じゃなければ△△、と、条件を付ける関数です。

=IF(条件,真の場合,偽の場合)

これも慣れないと面倒くさいですよね。

でも、これも良く使うので慣れてください。慣れしかありません!

魔九郎
えーと、祝日の値が7のときに7が出て、それ以外のときに曜日の値が出れば良いんですね・・・

なんだか頭がこんがらがりますね。 

IF関数使用例

IF関数で、表示を分けた

星羅
上手くできたみたいじゃない。私こういうの苦手なんだから、魔九郎君、今度から私の分もやってよね!
魔九郎
ズコ―!

まぁでも、条件付き書式も上手く動いて、色が変わっていったなぁ。ちょっと面白くなってきたぞ。 グフ!

本当は、この処理はわざわざ祝日判定の列を作らなくても、一回の処理で出来ます。

でも最初は頭がこんがらがるので、無理せずに一回一回、処理を分けてやった方が良いと思います。

無理して一つの式にまとめようとして、余計な時間をかけて、なおかつ間違ってるということもありますので。。。

慣れたら自然に一つの式にまとめることができるようになるので、あせらないで下さいね。

・ここまでの動画はこちら

印刷用に調整しよう

星羅
もうここまで出来たら、完成したも同然!

罫線をつけたり、というのは教えてもらったんだよね。

魔九郎
ハイ。新入社員研修の最終日にやりました。ちょっとやってみますね。

星羅
そうそう、今回はL列とM列は印刷しなくていいから、印刷の範囲設定をしないといけないよね。
印刷範囲の設定方法

印刷範囲の設定方法

印刷の設定に関しては、参考例として動画を作ってみました。

星羅
これで、毎月最初の日付だけを変えたら、スケジュール表が印刷できるようになったね。

じゃあ来月からヨロシクね、魔九郎君♥

本日の採点(おまけ)

魔九郎
えーと、これを今回から導入されたエクセル判定ロボに入れて・・・と。

でもなんだ?この手作り感あふれるロボは・・・最近は Pepper じゃないのか?

ロボ
ハンテイシマス。コンカイノアナタノサクヒンハ・・・

80テン!

魔九郎
100点じゃないのか!壊してやろうかこのロボ!

ウラミハラサデオクベキカ!

素牛
こういうファイルにありがちなのはね・・・
魔九郎
あっ!素牛さん!

素牛
せっかく作ったのに、原紙に誰かが上書きしちゃって、せっかくの設定が壊れちゃうことなんだよ。

あと、原紙に書き込むと、毎月書き込んであることをクリアしないといけないよね。

魔九郎
はぁ。

素牛
なので、ちょっとだけマクロをちょっと加えて、原紙には触らないようにすればいいよ。

あと、また後で休日を探さないでいいように、検索したホームページもどこかに書いておいたら役に立つね。

まだ魔九郎君にはマクロを教えてないから、今度のマクロ作りの教材にしよう。

魔九郎
はぁ。

素牛
また少ししたら、マクロの勉強をはじめよう。ではさらばだ!
魔九郎
もしかして、あのロボの中に素牛さんが入ってたんじゃないのか・・・???

まぁいいか!グフ!

 

今回の最終形です。(最初のダウンロードと同じものです)

ダウンロード

 

 

中の人
今回の記事は、結構気軽に作り始めた割には、だいぶ細かいことまで出てきました。

ただ、一度設定しておけば、毎月同じことを繰り返さなくてよくなるので、ぜひ試してみてくださいね。

日付を数値として扱うことが出来れば、発注から納品まで何日かかってるか、など、いろいろ応用が効きます。

条件付き書式も、MIN関数、MAX関数などと組み合わせれば、最高や最低の値が一目瞭然で分かるようになるので、いろいろ応用が出来るテクニックです。

今回は一気にやり過ぎた感がありますが、少しずつ使っていって、身につけていきましょう!

エクセルでスケジュール表作成

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

ABOUTこの記事をかいた人

昭和48年広島県生まれ。現在も広島に住んでいます。 趣味は音楽鑑賞(邦楽、洋楽問わず、クラシック、童謡も)、マンガです。エクセルを効率的に使う方法を紹介して、あなたのお役に立ちたいと思っています。