赤文字の入ったセル数をカウントしたい。

質問

社員勤務のシフト表カレンダーを、EXCEL2013で作ろうとしています（BOX型）。

　作ったカレンダーは、給与が20日締めですから、例えば、9月のカレンダーは、8月21日始まり、9月20日終了という形式です。 祝祭日と日曜日の日にちは、赤文字で表示してあります。

　次に、月間総労働時間を算出するために、該当のセル範囲（G20：S20）の中から、労働時間から除外すべき日数＝【『赤文字で表示した祝祭日と日曜日』が入った（セルの数）】を、カウントし、例えば、セルE26に、＝○○（G20：S20,3）の形式を用いて表示させたいと思います。VBAを使いたいのですがうまく作れません。ご教授下さい。

《整理》
1.セル範囲（G20：S20）中の月間総労働時間を算出したい。
2.労働時間から除外すべき祝祭日と日曜日は、赤文字で表記させている。
3.この赤文字の入ったセル数をカウントしたい。
4.表示形式は、＝○○（G20：S20,3）
5.VBAを使いたい。

《追記》
祝祭日と日曜日を赤文字に表記させた作業工程は、次の通りです。
(1)日曜日は、予めFontを赤に設定、(2)祝日は、「条件付き書式」を使いFontを赤に設定、(3)8月21日より、9月20日までの期間外の日にちは、Fontを白に設定し、見えないようにしました。

＜ 使用 Excel：Excel2013、使用 OS：Windows10 ＞

回答No.7

real beatin（@realbeatin）

No.6です。

> 想像するところ、解決できてない一因は、カレンダーを作成するに当たって、
> ＝DATE(M6,T6,Y6)-(WEEKDAY(DATE(M6,T6,Y6),1)-1)
> のような関数を利用しているため、計算過程を表示させると、
> Prange欄にシリアル値が表示されるようなることが
> 関係しているように思います。
"Prange欄"とは何でしょう？という疑問は残りますが、
その数式自体は、日付のx日前の日付を求める、という内容ですので、
戻り値がシリアル値、セルの表示形式が日付、
になるということでしたら、それは正常な動作ですね。
"解決できてない一因"として影響があるとは思えませんけれども。

> 残念ながら、今回は、未だ解決に至っておりません。
出来れば、条件付き書式のルールに設定した数式、を
提示して欲しかったのですけれども。
先に挙げたUDFのプロットを一段階だけ進めて、
数式の書き方に対する汎用性を高めてみましたので、
よければ、試してみて下さい。
（最初っから、こう書けばよかったのに、といった内容ですが）
具体的には、
数式内のセル参照を、相対参照として正しく書換える為に、
Application.ConvertFormulaメソッドに２回通しています。
論理値[TRUE|FALSE]を返す数式が正しく設定されていれば、
普通に使えるようになると思うのですが、、、。

> なお、日曜・祝祭日の合計値を、関数を利用して計算することは、自力でできますので、
> 取りあえず、そのように対処致します。
それが現実的な対応として十分なのであれば、
その方が安心でしょうね。
No.2にてtsubu-yukiさんご紹介のNETWORKDAYS.INTL()関数みたいな
無駄のないスッキリした数式が書けるように、
扱い易いシート設計を目指した方が良さそうにも思いますが、、、。

条件付き書式の設定内容について、一々再確認するのは面倒だけど、
条件付き書式のルールに設定した数式による判定を参照したい、
といったニーズに関する質問は、過去に数十件程度見たことがあります。
このうち、UDFで、というニーズは初めて見ました。
何故、UDFにする必要があるのか、という理由について、
今一度、ご自身で確認してみた方が総合的に吉なような気がします。
経験的を踏まえての想像ですが、
シート設計に難がある、といった場合である可能性が高いような、、、。

以下、改良版です。
シート上では、
　=CountDisplayFontColor(G20：S20,3)
のように数式を設定して使います。
' //
Function CountDisplayFontColor(Target As Range, ColorIdx)
Dim c As Range
Dim oFormatCondition As FormatCondition
Dim sFormula As String
Dim cn As Long
Dim flg As Boolean
　　For Each c In Target
　　　　flg = False
　　　　For Each oFormatCondition In c.FormatConditions
　　　　　　If oFormatCondition.Font.ColorIndex = ColorIdx Then
　　　　　　　　sFormula = oFormatCondition.Formula1
　　　　　　　　sFormula = Application.ConvertFormula( _
　　　　　　　　　　　　　　　　Formula:=sFormula, _
　　　　　　　　　　　　　　　　FromReferenceStyle:=xlA1, _
　　　　　　　　　　　　　　　　ToReferenceStyle:=xlR1C1, _
　　　　　　　　　　　　　　　　RelativeTo:=oFormatCondition.AppliesTo(1))
　　　　　　　　sFormula = Application.ConvertFormula( _
　　　　　　　　　　　　　　　　Formula:=sFormula, _
　　　　　　　　　　　　　　　　FromReferenceStyle:=xlR1C1, _
　　　　　　　　　　　　　　　　ToReferenceStyle:=xlA1, _
　　　　　　　　　　　　　　　　RelativeTo:=c)
　　　　　　　　If Evaluate(sFormula) = True Then
　　　　　　　　　　flg = True
　　　　　　　　　　Exit For
　　　　　　　　End If
　　　　　　End If
　　　　Next
　　　　If Not flg Then flg = c.Font.ColorIndex = ColorIdx
　　　　If flg Then cn = cn + 1
　　Next
　　CountDisplayFontColor = cn
End Function
' //

回答No.6

real beatin（@realbeatin）

こんにちは。

どうも全体的に話が噛み合っていないみたいですけれども、
ここら辺↓のことですよね？
『Range.DisplayFormat Property (Excel)』
https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-displayformat-property-excel
『VBA function to test if cell is conditionally formatted in Excel - Stack Overflow』
https://stackoverflow.com/questions/22366145/vba-function-to-test-if-cell-is-conditionally-formatted-in-excel

例えば、
' //
Sub test1()
Const ColorIdx = 3
Dim c As Range, cn As Long
　　For Each c In Range("G20:S20")
　　　　If c.DisplayFormat.Font.ColorIndex = ColorIdx Then cn = cn + 1
　　Next
　　MsgBox cn
End Sub
' //
Sub test2()
　　MsgBox Hoge(Range("G20:S20"), 3)
End Sub
Function Hoge(Target As Range, ColorIdx)
Dim c As Range, cn As Long
　　For Each c In Target
　　　　If c.DisplayFormat.Font.ColorIndex = ColorIdx Then cn = cn + 1
　　Next
　　Hoge = cn
End Function
' //
のように、test1もtest2も正しく求める結果が得られるのに、
Function Hoge() を、シート上のUDFとして使おうとすると、
#VALUE! エラーになってしまうけれど、
これを回避して、
条件付き書式の結果を含む、表示上のフォントカラーを
カウントするようなUDFを書けないだろうか？
といった内容のご質問かと存じます。
何ていうか、質問する時に、
質問内容の特殊性みたいなことをご自分で評価出来ない場合は、
これまでに得た情報とか、既に試したこととか、
ここまでは出来ているとか、
そういった質問者さんにしか解らない現状を
なるべく伝えるようにすると
回答に直結して話が早くなるように思います。

本来、range.DisplayFormatプロパティは、
値の取得専用のプロパティですから、
UDFからの呼び出しに対しても、
普通にリターンがあっても良さそうなものなのですが、
恐らくは、開発者にしか解らない何等かの事情があってのことなのでしょう。
UDFでのrange.DisplayFormatプロパティは、
普通のVBA技法（VBAだけ）では使えません。

DisplayFormat以外の、他の方法を探ることになります。
とはいえ、未知のアドインをインストールする程のレベルのもの
でもないように思います。
とりあえず、条件付き書式のフォント色とルールの判定結果を元に
UDFとして機能させるような、関数をプロットとして書いてみました。

各セルの
　条件付き書式（.FormatCondition）の
　フォント色（.Font.ColorIndex）が 指定値（3）ならば、
　　条件付き書式のルールに設定された数式（.Formula1）を取得し、
　　条件付き書式適用範囲の先頭セル（.AppliesTo(1)）を参照して書かれた
　　　数式を、各セルを参照する数式に書換えて
　　Evaluate メソッドで数式の判定結果を取得して、
　　　この結果がTRUEである場合、
　　　または、セルの書式のフォント色が.Font.ColorIndex）が 指定値（3）ならば、
　　　　カウントする
といった内容です。

あくまでもプロットですので、各種条件に合わせて、
工夫したり、汎用を目指すならより精緻なものに書き上げる必要
はありますが、基本的なやり方を示すにはこれぐらいで十分でしょう。
このままでは期待する結果を得られない可能性も低くないですが、
もしも、ご自分で書き換えるのが難儀でしたら、
関連する（G20:S20に設定されている）"すべて"の
条件付き書式について、
　適用範囲
　ルールに設定された数式
等の詳細を提示して貰えれば、こちらで対応可能です。
他、こちらで提示したものに対する疑問等あれば、
補足して貰えればお応えします。（但し明日まで。）

以下が、直接の回答としての、UDFのプロットです。
こちらで作成したサンプルシートでは、正しい結果を得られています。
' //
Function CountDisplayFontColor(Target As Range, ColorIdx)
Dim c As Range
Dim rngT As Range
Dim oFormatCondition As FormatCondition
Dim sFormula As String
Dim sRef1 As String
Dim cn As Long
Dim flg As Boolean
　　For Each c In Target
　　　　flg = False
　　　　For Each oFormatCondition In c.FormatConditions
　　　　　　If oFormatCondition.Font.ColorIndex = ColorIdx Then
　　　　　　　　sFormula = oFormatCondition.Formula1
　　　　　　　　If InStr(sFormula, "$") Then sFormula = Replace(sFormula, "$", "")
　　　　　　　　sRef1 = oFormatCondition.AppliesTo(1).Address(0, 0)
　　　　　　　　If Evaluate(Replace(sFormula, sRef1, c.Address(0, 0))) = True Then
　　　　　　　　　　flg = True
　　　　　　　　　　Exit For
　　　　　　　　End If
　　　　　　End If
　　　　Next
　　　　If Not flg Then flg = c.Font.ColorIndex = ColorIdx
　　　　If flg Then cn = cn + 1
　　Next
　　CountDisplayFontColor = cn
End Function
' //

お礼

realbeatin様

当方の意図を丁寧にご検討下さった上で、詳細なご回答を賜り、有難うございました。

初めてこの掲示板を利用するため、質問の立て方が良く分からず、申し訳ありませんでした。

これまでVBAでプログラムを組むために、沢山の解説書を購入しており、参考にして問題の解決に当たって参りました。残念ながら、今回は、未だ解決に至っておりません。私の、理解力、応用力不足が原因です。

想像するところ、解決できてない一因は、カレンダーを作成するに当たって、＝DATE(M6,T6,Y6)-(WEEKDAY(DATE(M6,T6,Y6),1)-1)のような関数を利用しているため、計算過程を表示させると、Prange欄にシリアル値が表示されるようなることが関係しているように思います。

なお、日曜・祝祭日の合計値を、関数を利用して計算することは、自力でできますので、取りあえず、そのように対処致します。

海外情報も含めて、継続して、学習を続けたいと思います。
繰り返しになりますが、realbeatin様、本当に有難うございました。

回答No.5

bunjii

>『Kutools for Excel 』を使われた方がおいでになりますか？
>おいでになれば、使い勝手を教えて下さい。
ご提示のアドインソフトをインストールして動作を確認してみましたが条件付き書式でフォントの色を変えたセルについては検出できずカウント対象になりません。
勿論、セルの塗りつぶしについても同様に条件付き書式での塗りつぶしをカウントできませんでした。
FontColor検出の場合は次のような関数で手動で着色したセルのみカウントできました。
=COUNTBYFONTCOLOR([対象範囲],[色番号])

ご自身で試用しなかったのですか？
本来は、ご自身で試用して使い方が分からなかったとき、どのような試し方をしたかを示すのが礼儀です。

回答No.4

imogasi

確かに１セルでもセルの書式をとらえるエクセル関数は無い。
Ｅｘｃｅｌ関数はセルの値（ＶＢＡで言えばＶａｌｕｅ）だけを対象にします。Ｃｅｌｌ関数というのがありますが、ＬＯＴＡＳ由来らしいが、多分使えるケースはない。
それでＶＢＡ利用に思いが行ったのだと思います。
しかし書式を設定した前提に戻れば、セルの書式はセルの値に基づいて設定する（されている。設定できる）場合が多いです。たとえば、土や日という文字列のセルに色を付けるとかＷＥＥＫＤＡＹ関数のコード６，７の場合などです。
初心者のうちは、この観点に戻って、そのセルの値でＣＯＵＮＩＦ関数などで
件数をカウントすればよい。
ＶＢＡに慣れて来たら本件のような質問はしなくなる。
ＶＢＡのコードそのものは簡単だから。
ＶＢＡでＣｏｕｎｔｉｆ関数は使えるが、書式を指定できないので、Ｆｏｒ　Ｅａｃｈ　Ｎｅｘｔで繰返す方法がいちばんやさしいだろう。
下記例は、セル範囲Ｂ２：Ｄ５のセル範囲の赤文字を問題にして、文字色を判定している。
ただし色の設定と判定方法は色々あって、どう設定したかもよるので、その辺をよく勉強する必要がある。
Sub test01()
n = 0
For Each cl In Range("b2:d5")
If cl.Font.Color = vbRed Then
n = n + 1
End If
Next
MsgBox n
End Sub
を参考にしてください。
ーー
＞VBAを使いたいのですがうまく作れません。ご教授下さい。
どう、うまく行かなかったかを書かないで質問する人が多いが、質問としておかしいのでは？。
質問文は、自分の場合の個別ケースについて長々と述べて、長い文章だが、もっとパターン化して、ポイントを絞って、質問できるようになってほしい。それには、いろいろエクセルやＶＢＡの勉強を日頃しておく必要があるが。

回答No.3

bunjii

>社員勤務のシフト表カレンダーを、EXCEL2013で作ろうとしています（BOX型）。
七曜（日、月、火、水、木、金、土）型のカレンダーですよね。

>祝祭日と日曜日の日にちは、赤文字で表示してあります。
「日曜日は、予めFontを赤に設定」と言うことは日曜日のセル範囲に書式で予め赤に指定しているのですよね？

>月間総労働時間を算出するために、該当のセル範囲（G20：S20）の中から・・中略・・カウントし、
セル範囲が矛盾します。
G20:S20ではセル数が13なので1ヶ月分の日数にならず、折り返して複数行が対象なら曜日の列が一定になりません。
一般的な七曜型カレンダーは7列×最大6行の矩形になっています。
1列目が日曜日のタイプと1列目が月曜日のタイプを見かけます。
カレンダーの形が処理手順に影響しますので実際の状態を明確にしてください。

>VBAを使いたいのですがうまく作れません。
VBAに拘りますか？
フォントの色を検出しなくても論理を組み立てればExcelの関数で目的の処理が可能になると思います。

>4.表示形式は、＝○○（G20：S20,3）
意味不明です。

>(3)8月21日より、9月20日までの期間外の日にちは、Fontを白に設定し、見えないようにしました。
条件付き書式でフォントの色を変えているのでしょうか？
カレンダーの作成方法にもよりますが該当月に含まれないセルは空白にされた方が処理し易いと思います。

考え方としては日曜日はWEEKDAY関数でフォントの色の代替が可能です。
また、祝日は条件付き書式で使っている数式を参考にすれば関数でカウントできるでしょう。
計数方法の再考をお薦めします。

補足

早速、有難うございました。言葉足らずの点をお詫びいたします。

私がお尋ねしたい件は、「関数を使用して、Excelで塗りつぶしまたはフォントの色でセルを数える/合計する 」という内容のFunctionプロシージャの書き方です。

手法を変えれば、例えば、1か月内の（日曜日の日数）+（祝日の日数）等でカウントできるのかもしれませんが、VBAのユーザー定義関数として組むことを望んでいます。

ネット上では、日本語でも、本件にん関する様々な『Functionプロシージャのプログラム』が掲載されており、試してみましたが、うまく行きません。

現在は、それら『Functionプロシージャのプログラム』を読めるように学習中です。

なお、「Using a function to count / sum cells by fill or font color in Excel」で検索をかけると、『Kutools for Excel 』というソフトが対応している様です。

外国でできているのですから、何か解決方法があるように思います。
『Kutools for Excel 』を使われた方がおいでになりますか？
おいでになれば、使い勝手を教えて下さい。

回答No.2

tsubu-yuki

ご自身でも「冷静に」読み返していただきたいんですが、
条件が伝わってこないなぁ・・というのが本音でして。
なのでちょっとしたツッコミから入りますが・・

> 1.セル範囲（G20：S20）中の月間総労働時間
この範囲は・・１３セルありますが、
日付が入っているのか、労働時間が入っているのか、どちらでしょ？
それによって考え方が大きく変わりますね。

> 2.労働時間から除外すべき祝祭日と日曜日
「労働（出勤）日数」の間違いですか？
それとも、「１日（の労働時間）＝８時間」など固定して考えるのでしょうか？
まぁ、ここは「日数」が出れくれば掛けるだけなのでどうとでもできますが。

> 4.表示形式は、＝○○（G20：S20,3）
・・・？入力形式の間違いですかね？

> 5.VBAを使いたい。
ＶＢＡを学習中なので色々試したい、ということでしょうか。
ＶＢＡにこだわる必要はないような気がする命題なのですが・・

というわけで、ご自身の学習のために、であれば私が解る範囲で。
ひとまず・・
> 祝祭日と日曜日は、赤文字で表記
これをどのようにして「赤文字」にしているか？で処理が分かれます。
１）手作業でコツコツ頑張って「赤文字」にしている場合
　　文字の色は「対象.Font.Color」で取得できます。
　　本当に「赤（RGB(255,0,0)」なら「255」が返ってきます。
２）条件付き書式で例えば「WEEKDAY"セル番地"=1」と設定している場合
　　条件に合致しているか？をVBAで直接調べるのは困難です。
　　　※条件付き書式は「印刷・表示」ようにセルを修飾する機能なので
　　　　コレを調べる手段は用意されていないようです。
　　なので、VBAでやるなら、条件付き書式の「式」と比較してやります。
　　ex) If Weekday(対象) = 1 Then 　　など。
個人の感覚でモノを言うのは失礼なのですが、
「赤文字」を数える前者より、「日曜日」を数える後者の方が
正確さと効率は間違いなく良いと思いますよ。

そんなわけで、これを対象範囲全ての日付において回します。
決して模範解答とは言えませんし、
「赤文字をカウント」とも違うやり方ですが
Function ○○(開始日 As Date, 終了日 As Date, 祝日範囲 As Range) As Integer
　　For i = 開始日 To 終了日 Step 1
　　　　If Weekday(i) = 1 Or WorksheetFunction.CountIf(祝日範囲, i) > 0 Then
　　　　　　myCount = myCount + 1
　　　　End If
　　Next i

　　○○ = myCount
End Function
例えばこんな感じで標準モジュールに追加。
日曜日、あるいは祝日範囲（一覧）の中に日付があれば「１」を加算、
開始日～終了日間で繰り返し、最終的にワークシートに返します。
ワークシート内で
　　=○○(開始日,終了日,祝日範囲)　　※すべて省略不可
　　=○○(G20,S30,Sheet2!A:A)
　　=○○(”2017/8/21","2017/9/20",Sheet2!A:A)
のように使えるユーザー定義関数です。

> 4.表示形式は、＝○○（G20：S20,3）
な形ではないですけどね(笑)。

そうじゃないなら「NETWORKDAYS.INTL」という関数の方が早いです。
　　ex:=NETWORKDAYS.INTL(開始日,終了日,週末種類,祝日)
　　　　=NETWORKDAYS.INTL("2017/8/21","2017/9/20",11,B:B)
　　　　　8/21～9/20で、日曜日のみ週末休、
　　　　　Ｂ列に用意した祝日一覧に存在する日を除外した日数を返す

回答No.1

skp026

文字の色を取得できるプロパティです。
http://web.pi-ppi.com/vba/m01/000358.html
繰り返し処理
http://web.pi-ppi.com/vba/b01/000297.html
セルの参照について
http://www.k1simplify.com/vba/tipsleaf/leaf22.html
これらを組み合わせたら実現できると思います。

※
このような考え方もあります。
祝日なのに色の変更しわすれとか、赤に似た別の色にしてたり
などの操作ミスがありえます。
祝日フラグ(1または0)を意味する列または行を追加し、
1ならば条件付き書式で文字色を変えたりできます。
そしてsumifなどで条件付き合計ができます。

ご存じだったり参考にならなかったらごめんなさい。