今回はいよいよ最終回「カレンダーの日数を、色で自動集計する方法」をご紹介します。
集計方法は「条件付き書式」にて自動表示された色で行います。
これ、ネットでは「できない」と言われて別の方法が公開されたりしていますが、マクロを使えばできます!
やっぱり余計な手間をかけずに一発変換したいですよね。
ぜひ「こんなに楽になるなんて!」を体験して下さい。
いよいよマクロ(VBA)を使うよ!
といってもコピペで出来る親切設計♪
各画像はクリックで拡大できるよ。
▼ 前回記事
今回やりたいこと(自動書式をカウント)
今回行うのは、日数の集計です。
ここまで作成してきたカレンダーで、以下のように
当月=黒
当月以外=灰色
祝日=赤
で表示されるようになりました。
この色を使って、
当月
営業日
休日
の各日数を自動でカウントできるようにしたいと思います。
カレンダー以外にも
色々と応用がきくので
ぜひ使ってみて下さい!
カレンダーの日数を自動集計する
今回は「CountColor」という関数を使います。
文字どおり色をカウントする関数で、
数式ではこのように表されます。
これによって、範囲内に対象の色がいくつあるかをカウントします。
では、順番に作業してみましょう!
作業➊ 関数の入力
➊ 今回カウントしたいのは、「当月の日数」「営業日」「休日」です。
カレンダーの下に作りましょう。
※各月、3行スペースをとっていたのはこのためです。
➋「G13」セルに
とコピペします。
数えたい色は「黒」なので、B4を選択しています。
➌ 同じように「G14」セルに
とコピペします
数えたい色は「赤」なので、B5を選択しています。
ここまでの作業を行っても、エラー表示になっていると思います。
が、これで合っています!
ここにマクロをはめ込めば、アッという間に機能しますのでご心配なく!
➍ 当月の日数は、単純に「営業日+休日」です。
この日数表示はなくてもいいですが、ミスに気づくためのものでもあります。
たとえば1月が「31」と表示されなかった場合、➋か➌の計算式か設定が間違っている可能性があります。
➎ E12:H14の範囲を、そのまま各月にコピペします。
続いて、VBAの作業だよ
作業➋ VBA(コピペで簡単!)
➊「開発」タブを表示します。
➊ どこでもいいので、リボンの上で右クリック
→「リボンのユーザー設定」を選択
➋「開発」にチェックを入れる→OKを押す
これで表示されます!
➋「開発」タブの「Visual Basic」を開きます。
➌ 左側、白い部分で右クリック →「挿入」→「標準モジュール」を選択
右側に表示されたスペースに、以下のコードをコピペして下さい。
Function CountColor(Rng As Range, r2 As Range) As Long
Dim myRng As Range
Dim Col_cnt As Long
Dim sh As Worksheet
Application.Volatile
Col_cnt = 0
Set sh = Rng.Parent
For Each myRng In Rng
If sh.Evaluate(“CColor(” & myRng.Address & “)”) = r2.Font.Color Then
Col_cnt = Col_cnt + 1
End If
Next myRng
CountColor = Col_cnt
End Function
Function CColor(r As Range) As Long
CColor = r.DisplayFormat.Font.Color
End Function
Sub updata()
Application.Calculate
End Sub
➎ 保存ボタンのようなものはありませんので、そのままエクセルに戻ります。
まだ表示が元のままだと思うので、どこでもいいからセルをクリックすると表示が変わります。
手入力で変更した色にも対応するコードにしてあるよ。
これで「条件付き書式で変更された色」含め、すべて自動集計できるよ!
➎ 大切なこととして、保存するときは「マクロ有効ブック」を選択しましょう!
「ファイルの種類」から「Excelマクロ有効ブック」を選択します。
ふつうにエクセルとして保存すると、せっかくのVBAコードが消えてしまいます!
更新ボタンを作ろう!
じつは上記のVBAでは、何かが変更されても自動で変わらないという欠点があります。
たとえば西暦を2024→2025にしたり、休みの日を自分で赤い色に変更しても、どこかのセルをクリックしないと表示が変わらないのです。
そこで、これを押せば一発変更という「更新ボタン」を作りましょう!
作り方はとっても簡単。
コードもコピペで大丈夫です。
作業➊ 更新ボタンの作成
➊「開発」タブにある、「挿入」の▼をクリックし「フォームコントロール」を開きます。
➋ マクロを登録するためのボタンを選びます。
今回は普通に左上の「ボタン」を選択しますが、
自分のお好みでイラストを使うこともできます。
▲ こういったイラストにもマクロ登録できるよ!
➌ 今回は、このあたりに設置します。
➍ ダイアログボックスが表示されますが、ひとまずOKで大丈夫です。
➎ ボタンが設置されました。設置後でも名前変更できます。
作業➋ VBA(コピペで簡単!)
➊「開発」タブのまま「Visual Basic」を開きます。
➋ 開くと、標準モジュールで登録したコードが表示されると思います。
その一番下をクリックすると、カーソルが点滅します。
➌ カーソルの位置に、以下のコードをコピペします。
Application.Calculate
End Sub
作業➌ マクロの割り当て
➊ 通常のエクセルに戻り、設置したボタンを右クリック→「マクロの登録」を選択
➋「updata」という項目があるはずなので、それを選択 → OK
➌ 試しに西暦を変更してみましょう。
日付は変更されても、日数は変更されないと思います。
そこで更新ボタンを押すと、日数も正しく変更されます。
これで完成です!
すべての作業は終わりました。
本当にお疲れ様でした!
最後まで本当にお疲れさまでした!
でも、きっとお役に立てる内容だったと思います。
よかったら講座の最初からカレンダーを作ってみてね。
♦今回の講座一覧
コメント