メインコンテンツへスキップ
見出し画像

一生使える横型カレンダーの作り方!

今回は、このようなタイプの

画像

カレンダーをExcelで作っていきます^^

画像

Excel上での完成イメージです。
Excelレベルを上げながら便利なツールを作っていきましょう^^

今回使用する関数や機能、ショートカット

DATE関数
WEEKDAY関数
MONTH関数
SUBTOTAL関数
印刷の向き
書式設定の表示形式
テーブル
条件付き書式
(※マクロ)
――――――※ショートカット―――――
Ctrl+矢印‖端まで移動
Ctrl+Shift+矢印‖端まで移動し、選択
Ctrl‖複数選択
Ctrl+C‖コピー
Ctrl+V‖貼り付け
Ctrl+1‖書式設定
Ctrl+T‖テーブル化
Alt▶H▶B▶A‖罫線格子状
Alt▶H▶B▶T‖罫線太線
Alt▶H▶L▶N‖条件付き書式新しいルール
Alt▶H▶H‖背景色
Alt▶H▶FC‖文字色(フォントカラー)

(※より効率よく作るために使ってみましょう!)

上記のものの使い方も説明しながら作り方を紹介します♪

作り方

枠づくり

画像

印刷の向きを横向きにしておきます。

画像

画像のように年と月を入力しましょう。
必ずセルを分けてください!

画像

曜日も入力して、セルの幅を好きなように設定します。
【Ctrl+Space】で列を選択し、
【Shift+矢印】で画像のような範囲選択ができます!

日付の自動化

画像

日付が入る一番左上に数式を入力します。
=DATE(年、月、日)
のカレンダーでおなじみDATE関数です。
年と月は事前に入力していたセルを指定します。
このまま、日を「1」にすると…
2024/5/1
となります。
縦型カレンダーならこれでいいのですが、今回は、日曜日を左上にしたいので、数式に手を加えます。

画像

WEEKDAY関数です。
WEEKDAY(日付(シリアル値)、種類)を入力すると、曜日が数字になって出力されます。
シリアル値「2」にすると、月曜が1、火曜が2…と値がでます。

2024/5/1は水曜日なので、WEEKDAY関数の戻り値(出力されるもの)は「3」になります。

実際に2024年4月の最終日曜日は「28日」。
5/1から「-3」をしたら、確かに4/28になりそうですね。
なので、

WEEKDAY(DATE(年、月、1)、2) ▶ 3になる数式(今回は)
を
DATE(年、月、1-WEEKDAY(DATE(年、月、1)、2))
と先ほどのDATE関数に入れ込む!

画像

見た目を「年月」を消した「日」だけにしたいので、
【Ctrl+1】(またはマウスの右クリック)でセルの書式設定を開き、

d"日"

とユーザー定義で入力。

画像

あとは、最初の日付から+1していくだけ!

画像

【Shift+矢印】で範囲選択、【Ctrl+R】でコピペできると速いですね♪

画像

2週目の日曜日は、前の週の最後(土曜日)からもってきます。

画像

月曜日以降は同じなので、1週目の2日目以降(今回は29日~)を
【Ctrl+Shift+→】で選択し、
【Ctrl+C】でコピーして、
【Ctrl+V】で貼り付け!

画像

3週目以降は2週目と全く同じなので、
2週目をコピーして、3週目、4週目…に貼り付けます。

画像

余計に見えますが、6週目まで必ず作ってください。

画像

見た目の整理

罫線をお好みでつけます。
ショトカは、
【Alt▶H▶B▶A】格子状
【Alt▶H▶B▶T】太線

画像

【Ctrl】を押しながら行番号をマウスでクリックして、日付の間の行を複数選択!

画像

現在は、5月なのに4月の28,29,30日や6月の1日以降が表示されているので、消えるようにします。
日付部分の6行を選択して、条件付き書式の新しいルール。
【Alt▶H▶L▶N】

画像

=MONTH(B4)<>$D$2

と入力します。
MONTH関数は指定のセルが日付形式の時、月数のみを表示します。
今回であれば、4月の4、5月の5、6月の6となります。
「B4」と指定することで、指定された範囲のセルそれぞれが「<>D2」のとき、という条件にあてられます。
「<>D2」はD2じゃなかったら…。という条件式です。

画像

好きな色を指定して…

画像

あれ…?

画像

条件付き書式のルールの管理から確認すると、変な数字になっていますね。
B4じゃなくB14と入力していればこんなことにはなりませんでした。
まぁ、ここでB4と直せばOKです。

この対象のセルがいまいちわからない方は、この記事読んでみてください^^


画像

正しく直すと・・・。 

画像

5月じゃないときの日付が、指定の色になりました!
今回は、灰色の文字に灰色の背景なので見えなくなります!

日付がない、予定を書く入力欄も同じやり方で灰色にできます。

画像

同じように条件付き書式を付けたい範囲を選択して、

画像

同じ数式を入力します。

画像

OKを押して、うまくいかなければ、また「ルールの管理」から修正してください。

これで月数の数字を変えれば、十分使えるカレンダーになりました。

画像

「表示」タブの「改ページプレビュー」を押すと印刷範囲のみを囲って見やすくすることができます!


基本形の作成はここまでです!

(ここから下は少し難しくなるので、もう無理かなぁの人はここまででOK!お疲れさまでした♪一番下にデータがあります!)

便利なボタン

月数を自在に操るボタンをつくるために、カレンダーの下の方に仕組みをつくっていきます。

画像

オートフィルで1~12までの数字のリストを作ります。

画像

【Ctrl+T】でテーブル化し、

画像

テーブルの上に関数を入力していきます。

画像

それが、「SUBTORTAL」関数。
非表示のものをとばして、表示されてる数字のみで合計してくれます。

画像

=SUBTOTAL(9,テーブル範囲)

と入力します。
これで、1~12までを足し合わせた「78」が出力されるはず!

画像

これで、テーブルのフィルター機能を使って、表示を1つにすることで…

画像

SUBTORTAL関数の答えも同じ数字になります。

画像

テーブルを選択すると出てくる「テーブルデザイン」タブの「スライサーの挿入」をクリックします。

画像

「月数」にチェックを入れて、

画像
画像

連動するボタンが生まれました。

画像

「スライサー」タブの右の方で「列数:」を好きなように指定して、
好みのサイズ感にし…

画像

このままでは、カレンダー上部の月数が連動してないので、
「=D19」で=でつないであげます。

画像
画像

スライサーのボタンを押すと、カレンダー上の月数が変更され、
その月数に合わせてカレンダーも変わります!

画像

色付け

あとはお好み色を付けて完成!

画像



効率的なショートカットはこちら^^
【Ctrl+Shift+矢印】で範囲を指定し、
【Alt▶H▶H】で背景色設定
【Alt▶H▶F▶C】でフォントカラー設定!(バージョンで変わってるかも…

お疲れさまでした!
最後に1年分のシートを複製する

マクロ(たった4行!)

ここからはマクロボタン!
まずは「開発」タブからボタンを出しましょう。

開発タブがない!という方は↓

画像

「挿入」の左上にある白い四角をクリック!
シート内の好きなところをクリック&ドラッグでボタンを表示させます。

画像

(ボタンを右クリックして「テキストを編集」で、ボタンの名前を変えられます!)
ボタンを右クリックして「マクロの登録」を押し、
「新規作成」をクリックします。

画像

VBEというマクロのプログラムコード(VBA)を入力するエディター(特別なページみたいなもの)が開けたら、
「挿入」タブの「標準モジュール」を押します。

画像

出てきた白いページに以下のようなコードを入力します。
たった4行(+2行)なので意味も勉強してみましょう♪

画像

Sub 名前()
 ~~~
End Sub

何か動かしたいときに必ず書かなければいけない始まりと終わりの合言葉です!
SubとEnd Subの間にある~~~の部分がプログラムとして動きます。

名前はひらがなや感じがあってもいいですが、本当は英語で書けるとスタイリッシュでいい感じです。

For i=1 To 12 
 ~~~
Next i

For文という【繰り返す】動作をしたいときに使うプログラムで、今回は「i」という変数(箱のようなイメージ)に1から12まで順番に入れて、繰り返していくよ!といった意味です。
ForとNextの間にある~~~の部分のコードを繰り返していきます。

ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = i & "月"

ここがメインのコードです。
中学レベルの英語で意味がわかってしまうのではないでしょうか?
「ActiveSheet」その名の通りアクティブ(今動いているor触っている)なシートです。
この「ActiveSheet」を「.」を挟んで「Copy」(コピー)します。どこにコピーするかというと、「after」(あと(後ろ))です。このコピーされている「ActiveSheet」のね。

そして、新しくコピーされて増えたシートは、コピーされたてで今現在(最新で)動いていた(動いている)ので、このコピーされたシートが「ActiveSheet」状態になっています。
このコピーされたての「ActiveSheet」の「Name」(シートの名前)を「i月」にするよ。というコードです。
「i」は先ほど【繰り返す】ことになっていた1から12までの数字が順番に入ってくるので、1週目は「i=1」で「1月」、2週目は「i=2」で「2月」…と自動で数字が切り替わっていきます。

コピペ用↓↓

Sub カレンダー複製()
For i = 1 To 12
ActiveSheet.Copy after:=ActiveSheet
ActiveSheet.Name = i & "月"
Next i
End Sub

このコードを入力出来たらこのVBEは消してOK!
ボタンを押すと12月まで12枚のシートがコピーされるようになったはずです^^

カスタマイズ編

「祝日の色も変えたい!」
「縦型もできる?」

そんな声をいただいたので、少しずつ追加で記事を書いています。
実務で使える自分専用のカレンダーを作りたい方は、ぜひご覧ください。
また、LINEの方で、質問や相談も随時受け付けてます!

画像

祝日対応

2つ紹介します!
どちらも完成するとこのような形で、祝日に該当する日付が赤くなります。

画像

①祝日シートの作成

別シートが必要なのと、いくつか関数を使いながら作る手間がかかります。
が、ラグがなく今後もスムーズに運用できるので一回作れば楽になるかと思います。


新たなシートを作成し、DATE関数を入力します。
参照先は、カレンダーの年と月です。

画像

カレンダー作成と同じように「+1」をします。

画像

これを365行目までオートフィルします。

画像

次に、隣の列に関数を入力します。
WEBSERVICE関数です。

画像
=WEBSERVICE("http://api.excelapi.org/datetime/holiday?date="&A1)

これで、ネット上にある祝日のデータをもってこれます。「&A1」で左隣の日付を当てはめています。

下までオートフィルすると、該当する日付の横に祝日の名前がでるようになりました。

画像

カレンダーに戻り、日付部分をすべて選択して、条件付き書式の新しいルール「数式を使用~」を選択します。

画像

そこに、このような数式を入力します。

画像

VLOOKUP関数で、祝日リストのB列(祝日名)を探し出し、LEN関数で文字数が1文字以上だったら…という条件式です。

=LEN(VLOOKUP(D5,祝日!$A$1:$B$366,2,FALSE))>1

好きな書式を設定して、日付の下にあるメモ部分も同じように条件付き書式をつけたら‥

画像

完成です!

②APIの利用
条件付き書式に直接祝日を調べる関数を使いながら、祝日とわかるように色を付けます。
ただし、月数や年数を変えるごとに少し重くなります。(5秒くらいですが…)
その代わり別のシートを作成する手間もありません。

範囲を選択したら、条件付き書式の新しいルール、「数式を使用~~」で、

画像

このような数式を入力します。

=LEN(WEBSERVICE("http://api.excelapi.org/datetime/holiday?date="&D5))>1

WEBSERVICE関数で、祝日を調べるリンクを動かしています。「&D5」でそれぞれの日付が入るようになっていて、「こどもの日」などが返されるのですが、それをLEN関数で1文字以上だったら…という条件付き書式になっています。

背景色などの書式は自由に設定してもらうと…完成です。

画像

最後に

ここまで頑張った人本当にすごいです!
お疲れさまでした!

きっとExcelレベルがぐんと上がったはずです!
関数や機能に加えて、どんどんショートカットにも挑戦してみてください!

Instagramでも職場で評価バク上がりのExcel力が身に付く発信をしています♪
よければDMで感想を聞かせていただけると嬉しいです!

画像

ここまで自分で作るのは大変…という方へ。年を1つ入れるだけで12か月と年間1枚(A4横)が出来上がる完成版を、BOOTHに置いています。祝日は2025〜2035年ぶんがブックの中に入っていて、ネットにつながっていなくても色が付きます。始まりの月(4にすると4月〜翌年3月)や、毎週の定休日の曜日も選べます。マクロなし・Windows版Excelで動作を確認しています。


データ配布

📚 ほかの記事とテンプレート

ほかのExcelの記事は、テーマ別の目次にまとめています。

仕事で使うExcelのテンプレートを、会員向けにも配っています(月500円から)。

LINEでは、記事の合言葉でExcelのテンプレートを無料で配っています。「キーワード」と送ると一覧が出ます♪


 
 
\初心者OK!自動化・効率化できるExcel教室/ ◇AI×Excelの仕事時短術で自分の時間爆増! ◇自分だけの自動化ツールがつくれる! ◆元PC平凡▶半年で職場のExcel先生 ◆AIを使った自動化ツール作成数100超

あなたへのおすすめ