セルに色がついている部分の数だけを数えたい!という時に使います。サンプルは歯科矯正装置装着時間表ですが、カレンダーやスケジュール管理などに応用できます。
表の中で色がついているセルの数を数えたい
(条件)
・セルに色がついている、複数の色分けがされている
・セルの中に数字ははいっていない
・色がついたセルの数を数えたい
EXCELの関数を使って、このようなセルの数を数えることはできませんが、ユーザー定義関数を使うことによって可能になります。
◆ 参考にしたホームページ
googleで検索する場合は「色がついたセル カウント」でいろいろ検索結果が表示されますが、とても分かりやすかったサイトをピックアップしました。
前回記事 [ 色付きセルのみを合計する ] SUMIFのように動かしたSumColorですが、今度はCOUNTIF版を作ってみます。 もともとこの定義関数を作るきっかけになったのは、次年度の勤務表を作成する為に休日日数をカウントしたい。というのが目的でした。
関連記事 [ 自分で関数を作ってみる ] ユーザー定義関数を使って色付きセルを(指定した色のみ)合計したいと思います。 簡単に言うとSUMIFの色バージョンです。
Functionの使い道はいったい何なのか?ということは誰でも一度は悩んでしまう通過点ではないでしょうか?ここでは少しでもそんな悩みの解決の種になればと説明しています。
1.VBAを開きFANCTIONプロージャの作成
挿入→モジュール
Module1に
FUNCTION~
End FUNCITON までを入力(またはコピペ)する。
コピペ用
——————– ここから ————————-
Function CountColor(hani, jouken)
Application.Volatile
CountColor = 0
For y = 1 To hani.Columns.Count
For x = 1 To hani.Rows.Count
If hani.Rows(x).Columns(y).Interior.ColorIndex = jouken.Interior.ColorIndex Then
CountColor = CountColor + 1
End If
Next
Next
End Function
——————– ここまで ————————-
(補足)
haniは計算するセル範囲です。
joukenは色を指定するセルです。
EXCELで計算式を入れる
計算結果を表示させたいセルに、計算式を入力します。
※またはFXボタン→ユーザー定義の関数から選びます。
サンプルX4に入っている計算式
=CountColor(G5:V5, $J$1)+W5
G5:J1 = 色を塗るセル
J1 = 色を指定したセル
この表では2種類の色があるので、
計算式を2つ使っています。
=CountColor(G5:V5, $J$2)+W5
※IF関数を使って条件を分けた方がシンプルで
わかりやすい式になりますが、今回は省略。
J1には薄いピンク
J2には薄い水色を塗りました。
※サンプル画像では
上あご=オレンジ色
下あご=緑色
になっていますが、途中で色を変えたので
色名を変更し忘れただけです。
(補足)
=CountColor(G5:V5, $J$1)+W5
+W5 は睡眠時間も含めたかったので計算式にたしています。
これがないと、24時間の表を作らなくてはいけないので・・・
歯科矯正装着時間の計算のためにたしているだけなので
応用で他の表を作られる場合は、不要な部分です。
◆サンプルの歯科矯正装着時間表について
お子様で歯科矯正をされている方は、歯医者さんからこのような管理表を作成するように指導されますが、手書きで作成することになるので(私のところはですが)非常に面倒だったので自作でEXCEL作りました。歯医者さんの許可をもらって、こちらを印刷して提出しています。
※紙でもらったものをEXCELに再現したので、とくに何も言われなかったです。
歯科矯正管理表以外にも、カレンダーやタイムカードなどいろいろ活用できると思います。
色付きセルを数えるサンプルファイルはヤフーマイボックスからダウンロードしてください。サンプルにはマクロが使用されています。PCでの不具合やサンプルファイルについてのお問い合わせには対応できかねますのでご了承ください。