エクセル（Excel）の技。知ってましたか・・・。

マイクロソフトアクセスのまとめ記事はこちら

人気ブログランキングへ





【閑話休題】

先日職場の方より「簡単なスケジュール表を作ってほしい」という依頼があり。

作ってみました。

・１ヶ月ごとにＡ４で予定を書き込めるように枠を大きく。というのが条件です。

まあ毎月手動で日付を入れても構わないのですが、いいチャンスなので様々な

技を使ってスケジュール表を作ってみたので掲載いたします。


・レイアウトのイメージです。



Ｋ１に作成したい月の年月日（日は１日）を入力して、下の「カレンダーの作成」ボタンを

押すとその月のカレンダーが作成されるという仕組みです。

これを作成する際に私が検討した内容を思いついたまま書いてみます。

・Ａ１には年月を記載・・・Ｋ１の内容をコピーして表示設定を「年・月」にする。

・その月の１日は何曜日なのかを検出して、２行目の同じ曜日にＫ１の日付を挿入する

・第一週は「１」を挿入した次の列から「＋１」していく。土曜日（Ｉ列）まで

・１日以前の枠に前回作成した日付が残っている場合があるため、１日以前は空白にする必要がある。

・日曜・祝日は赤文字にしたい

・６週まであるのは、もし１日が金曜日とか土曜日から始まると、

週末が２４／３１のような形にする必要があります。

またそのようにすると「予定欄」が狭くなります。

結局６週分作成する方が目的に叶うかなと思い６週にしました。


上記の条件で作成開始しました。

・最初にＡ１の設定。セルには「=K1」と入力しK1の内容をコピーします。

セルの書式設定で「yyyy”年”m”月”」とします。

そうすることによってA1にはK1の日付が取り込まれ、表示は年・月になります。



・作成月の１日の曜日を取得する

これはVBAで取得する方が簡単なので下記のようなプログラムを挿入します。

コマンドボタンを作成してそこにプログラムを書いてください。

Sheets("メイン").Range("L1").Value = Weekday(Sheets("メイン").Range("k1").Value)

まずWeekday関数で日付から曜日の数字を取得します。

取得した数字をＬ１に挿入しなさいという命令文です。

上の例でいうと「L1」に「5」が表示されていますね。

日曜日が１で木曜日が５になります。

Sheets("メイン").Range("L2").Value = WeekdayName(Sheets("メイン").Range("L1"))

今度は逆にWeekdayName関数で「L１」の数字から曜日を作成して「L2」に挿入しています。

例ではL2に「木曜日」と表示されていますね。

これで作成月の１日の曜日は取得できました。


・次にC1から順番に見ていき先ほど取得した曜日と等しい曜日の２行目に

K1の日付をコピーしたいと思います。

やり方は色々あると思いますが、分かりやすいやり方として、

C1から順番に比較する方法を取りました。

Dim n As Integer 　列の変数の宣言

For n = 3 To 9

If Sheets("メイン").Cells(1, n) = Sheets("メイン").Range("L2") Then

Sheets("メイン").Cells(2, n) = Sheets("メイン").Range("k1")

Exit For

End If

Next n

上記の内容ですが、For〜Nextを使用した繰り返し文です。

C1から見ていきL2の内容と同じ曜日の場合、２行目にK1の日付を入れよという命令文です。

同じ曜日を見つけて日付を入力したらForから出なさいということです。

・次に「1日」より前の列の入力を消去していきます。

Sheets("メイン").Range(Cells(2, 3), Cells(2, n - 1)).Select

Selection.ClearContents

これはCellsで範囲指定してその間を選択して、消去せよという命令文です。

・次に「1日」以降の列に日付を入れていく必要があります。

For n = n To 8

Sheets("メイン").Cells(2, n + 1).Value = Sheets("メイン").Cells(2, n) + 1

Next n

ここでFor n = n To 8とありますね。先ほどの１日の続きなので現在nは「５」ですね。

ですから変数ｎは５〜８ということです。

Sheets("メイン").Cells(2, n + 1)は１日の次の列を指定しています。

ここではH2からスタートします。

順番に＋１していきなさいという命令文です。

これで第一週は出来ました。

２週目以降はどうしているのかというと、下のようにC4には「=I2+1」と入力されています。

「I2」の日付に＋１するようにセルに式を入れています。



D4は下のようにC4に＋１するように式を入れています。

E4以降も同じ。第３週以降も同じ式を入れています。



これで６週目までのカレンダーが出来ました。ただしこれは日付だけの話です。

・日曜・祝日を赤文字にしたい。

日曜日の列は単純に赤文字設定にすれば問題ありません。

後は休日設定ですね。

休日に関しては少し離れた場所（N18〜O35）あたりに2016年の休日一覧表を作っています。

ここを参照して休日を赤文字にしたいと思います。

隣に2017分がありますが、これは一旦無視してください。



全部の日付の欄に条件付き書式設定を設定します。

下のように「数式を使用して、書式設定するセルを決定」にしてルールは

「=COUNTIF($N$20:$N$35,C2)」と入力し、書式は赤文字にします。

まあC2は日曜日なので関係ないのですが一応同じように書式設定しておきます。

同じようにほかの日付が入るセルに条件付き書式設定をしてください。



めんどくさいのでコピーをしようと思うのですが、例えばC2をコピーしてD2からI2まで選択して、

貼り付けるとうまく行かないと思います。

１個ずつやればうまく行きます。C2コピーしてD2に貼り付けという感じですね。

１列（C2からI2）終われば、その１列全部コピーして２週目に貼り付けるのはＯＫです。

そのようにして３週目から６週目まで貼り付けしてください。


最後にVBAの全文を記載しておきます。

Private Sub CommandButton1_Click()

Dim n As Integer

Sheets("メイン").Range("L1").Value = Weekday(Sheets("メイン").Range("k1").Value)

Sheets("メイン").Range("L2").Value = WeekdayName(Sheets("メイン").Range("l1"))

For n = 3 To 9

If Sheets("メイン").Cells(1, n) = Sheets("メイン").Range("l2") Then

Sheets("メイン").Cells(2, n) = Sheets("メイン").Range("k1")

Exit For

End If

Next n

Sheets("メイン").Range(Cells(2, 3), Cells(2, n - 1)).Select

Selection.ClearContents

For n = n To 8

Sheets("メイン").Cells(2, n + 1).Value = Sheets("メイン").Cells(2, n) + 1

Next n

End Sub

これでK1に日付を記入してコマンドボタンを押すとカレンダーが完成します。

２０１７年になれば休日一覧を２０１６年の部分に上書きしてください。

VBAの書き方は色々あると思います。

上級者はもっと違うプログラムを組むと思いますが、私のレベルではこのくらいですね。

VBAの上級者を目指して頑張って行きましょう。


以上です。

一つだけ忘れていました。各日付のセルには「セルの書式設定」で「ｄ」にしてください。

日にちだけ表示させるためです。