excel2010で個数を数えたい

質問

Windows7でexcel2010を使用しています。
曜日を入力した行とその下の行に曜日に応じてセルの背景色が変わるように条件付き書式設定をしています。曜日の行とその下の行は同じ色になる設定です。
曜日の下の行には文字を入力します。
条件付き書式設定で変更されたセルの背景色で特定の色かつ特定の文字列が入力されたセルの個数を数えたいのですが何か方法はありますでしょうか？

たとえば、A1～AE1に曜日を入力していて、平日は背景色なし、土曜日は背景色青、日曜日は背景色赤になるように条件付き書式設定をします。
A2~AE2には色々な文字を入力しますが、上のセルと同じ色になるようにしています。
A2~AE2に〇と入力されていて、且つ背景色なしのセルの個数を数えたい場合はどうすれば良いでしょうか？〇と入力されていて背景が青の場合、赤の場合のセルの個数も数えたいです。

本当は背景色ではなく文字色を条件付き書式設定で変更してその個数を数えたいのですが、色々検索したところ文字色では難しそうなので背景色を変更することにしました。
説明が下手で申し訳ありませんが、どなたかご教授しただけますと幸いです。

回答No.12

kagakusuki · ベストアンサー

今仮に、Sheet2のB列に祝日の日の一覧表が作成されているものとします。

　まず、A3セル(日付のセル)には次の様な関数を入力して下さい。

=IF(ISERROR(1/(COLUMNS($A:A)<=DAY(EOMONTH($A$4&"年"&$D$4&"月",0)))),"",DATE($A$4,$D$4,COLUMNS($A:A)+15))

　次に、A3セルの書式設定の表示形式を[ユーザー定義]の

d

に設定して下さい。
　次に、A3セルをコピーして、B3～AE3のセル範囲に貼り付けて下さい。
　次に、A1セル(曜日のセル)には次の様な関数を入力して下さい。

=A$3

　次に、A1セルの書式設定の表示形式を[ユーザー定義]の

aaa

に設定して下さい。
　次に、A1セルをコピーして、B1～AE1のセル範囲に貼り付けて下さい。
　次に、以下の様な操作を行って条件付き書式を設定して下さい。

A1セルを選択
　　↓
Excelウィンドウの[ホーム]タブをクリック
　　↓
現れた「スタイル」グループの中にある[条件付き書式]ボタンをクリック
　　↓
現れた選択肢の中にある[ルールの管理]をクリック
　　↓
現れた「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に次の数式を入力

=WEEKDAY(A$1)=1

　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
「セルの書式設定」ダイアログボックスの[フォント]タブをクリック
　　↓
現れた「色」欄をクリック
　　↓
現れた色のサンプルの中にある赤色(日曜日の文字色)の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に次の数式を入力

=WEEKDAY(A$1)=7

　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
「セルの書式設定」ダイアログボックスの[フォント]タブをクリック
　　↓
現れた「色」欄をクリック
　　↓
現れた色のサンプルの中にある青色(土曜日の文字色)の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの中にある[新規ルール]ボタンをクリック
　　↓
現れた「新しい書式ルール」ダイアログボックスの「ルールの種類を選択して下さい」欄の中にある[数式を使用して、書式設定するセルを決定]をクリック
　　↓
現れた「次の数式を満たす場合に値を書式設定」欄の中に次の数式を入力

=ISNUMBER(A$1)*COUNTIF(Sheet2!$B:$B,A$1)

　　↓
「新しい書式ルール」ダイアログボックスの[書式]ボタンをクリック
　　↓
現れた「セルの書式設定」ダイアログボックスの[塗りつぶし]タブをクリック
　　↓
現れた背景色のサンプルの中にある桃赤色(祝日の背景色)の四角形をクリック
　　↓
「セルの書式設定」ダイアログボックスの[OK]ボタンをクリック
　　↓
「新しい書式ルール」ダイアログボックスの[OK]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの「適用先」欄を3箇所とも

=$A$1:$AE$3

に変更（カーソルとマウスの左ボタンによる範囲選択が使えます）
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[適用]ボタンをクリック
　　↓
「条件付き書式ルールの管理」ダイアログボックスの[OK]ボタンをクリック

　そして、「平日で尚且つ〇が付いている日の日数」を表示させるセルには次の様な関数を入力して下さい。

=SUMPRODUCT(($A$2:$AE$2="〇")*(WEEKDAY(0&$A$1:$AE$1,2)<6)*(COUNTIF(Sheet2!$B:$B,$A$1:$AE$1)=0))

　又、「土曜日で尚且つ〇が付いている日の日数」を表示させるセルには次の様な関数を入力して下さい。

=SUMPRODUCT(($A$2:$AE$2="〇")*(WEEKDAY(0&$A$1:$AE$1)=7)*(COUNTIF(Sheet2!$B:$B,$A$1:$AE$1)=0))

　又、「『日曜日か祝日の日』で尚且つ〇が付いている日の日数」を表示させるセルには次の様な関数を入力して下さい。

=SUMPRODUCT(($A$2:$AE$2="〇")*((WEEKDAY(0&$A$1:$AE$1)=1)+COUNTIF(Sheet2!$B:$B,$A$1:$AE$1)>0))

お礼

ありがとうございました。
無事に作成することができました。
ご丁寧に説明していただきとても助かりました。

回答No.11

msMike

別解です。
B2: =DATE($A$1,$C$1,14+COLUMN())
B3: =TEXT(B2,"aaa")
F6: =SUMPRODUCT((MOD(B2:AF2,7)>1)*(B2:AF2<=DATE(A1,C1+1,15))*(B4:AF4="○")*(COUNTIF(F2016CAL,B2:AF2)=0))
F7: =SUMPRODUCT((B2:AF2<=DATE(A1,C1+1,15))*(B3:AF3="土")*(B4:AF4="○"))
F8: =SUMPRODUCT((B2:AF2<=DATE(A1,C1+1,15))*(B3:AF3="日")*(B4:AF4="○"))
F9; =SUMPRODUCT((B2:AF2<=DATE(A1,C1+1,15))*(COUNTIF(F2016CAL,B2:AF2)=1)*(B4:AF4="○"))

範囲 $B$2:$AF$4 に対する条件付き書式は次のとおり
条件1:
＿＿数式→=B$2>DATE($A$1,$C$1+1,15)
＿＿フォント色→白
条件2:
＿＿数式→=AND(B$2<=DATE($A$1,$C$1+1,15),COUNTIF(F2016CAL,B$2))
＿＿塗りつぶし色→赤
条件3:
＿＿数式→=AND(B$2<=DATE($A$1,$C$1+1,15),B$3="日")
＿＿塗りつぶし色→赤
条件4:
＿＿数式→=AND(B$2<=DATE($A$1,$C$1+1,15),B$3="土")
＿＿塗りつぶし色→青

【お断り】F2016CAL は祝日リストに付けた名前です。

お礼

昨日よりご丁寧に教えていただきありがとうございました。
このように丁寧に説明していただけてとても助かり勉強になりました。

回答No.10

bunjii

>表示形式を"aaa"にしていますので後者の数式を入力しましたが上手くいきませんでした。
その原因らしき条件は3行目の16日から翌月15日までの日付のシリアル値を算出している部分にあるようです。
A1=A3と言う条件で1行目の曜日を表示させていますのでAE1=""の状態が発生する月では#VALUE!と言うエラーが発生します。
これを防ぐには検索範囲をOFFSET関数で可変にする必要があります。
あなたの思考ではセルの塗りつぶしの色で判定するようになっていますが、Excelの関数ではセルの値で判定する必要がありますので塗りつぶしの色に拘らないようにしてください。
つまり、条件付き書式でセルの塗りつぶしの色を指定している訳ですから、その条件を利用すれば目的のセルの数をカウントできます。
A2:AE2の"〇"のセルの数は次の数式で計数できます。
=COUNTIF(A2:AE2,"〇")
1行目が「土」で2行目が「〇」のセルの数は次の数式で計数できます。
=SUMPRODUCT((OFFSET(A2,0,0,1,COUNT(A1:AE1))="〇")*(WEEKDAY(OFFSET(A1,0,0,1,COUNT(A1:AE1)),2)=6))
1行目が「日」で2行目が「〇」のセルの数は次の数式で計数できます。
=SUMPRODUCT((OFFSET(A2,0,0,1,COUNT(A1:AE1))="〇")*(WEEKDAY(OFFSET(A1,0,0,1,COUNT(A1:AE1)),2)=7))
1行目がウイークデイの祭日で2行目が「〇」のセルの数は次の数式で計数できます。
=SUMPRODUCT((COUNTIF(AK1:AK50,OFFSET(A1,0,0,1,COUNT(A1:AE1)))>0)*(OFFSET(A1,1,0,1,COUNT(A1:AE1))="〇")*(WEEKDAY(OFFSET(A1,0,0,1,COUNT(A1:AE1)),2)<6))
但し、AK1:AK50に祭日の一覧が入力されている必要があります。
1行目が土日祭日以外で2行目が「〇」のセルの数は上記の数式を応用すればカウントできます。
其々の数式を減算記号（-）で連結すると数式が長くなりますので其々の計算を何れかのセルへ設定して2行目の「〇」のセルの総数から目的の値を減算すれば分かり易くなるでしょう。

回答No.9

imogasi

#4です。
私は質問者が、根本の考え方がわかってないと思って、＃４を書いた。文字色を条件付き書式で変えるのは判っているなど、答えにもならないし。またＶＢＡならできることは判っているが、質問者はＶＢＡをやっていないだろうと思って言及しなかったのだ。
私の回答というより、勉強のヒント（根本の考え方。そのままコピペするようなやり方でない）を書いたつもりだが、質問者のピント外れの補足をいただいて、役立たなかったことがわかった。無視してもらうほかない。
すでに回答が出ている中で、関数式（ＣＯＮＴＩＦなどの式）が質問者の場合（そのままか修正して）使えるか、どう修正したらよいか勉強するしかない。

回答No.8

bunjii

>表示形式を"aaa"にしていますので後者の数式を入力しましたが上手くいきませんでした。
上手くいかなかった結果の説明が必要です。

>祝日も背景色を赤に変更するように設定しているのを忘れていまして・・・
回答No.2の補足で上記のような条件を追加していますが考慮しなくて良いでしょうか？

>やはりexcelでは難しいのでしょうか？
条件の提示が無いので的確な回答を引き出せないだけです。
質問中の「〇と入力されていて背景が青の場合、赤の場合のセルの個数も数えたいです。」については回答No.3で数式を提示していません。
背景色が青の場合は土曜日のみのため下記の数式で良いでしょう。
=SUMPRODUCT((A2:AE2="〇")*(WEEKDAY(A1:AE1,2)=6))
背景色が赤は日曜日と祝日になるのでしたらA2～AE2の"〇"のセルをカウントして平日の"〇"の数と土曜日の"〇"の数を差し引けば良いでしょう。
=COUNTIF(A2:AE2,"〇")-SUMPRODUCT((A2:AE2="〇")*(WEEKDAY(A1:AE1,2)<6)) -SUMPRODUCT((A2:AE2="〇")*(WEEKDAY(A1:AE1,2)=6))

お礼

ありがとうございます。
無事に解決いたしました。

回答No.7

kagakusuki

>祝日も背景色を赤に変更するように設定しているのを忘れていまして・・・

　それならば、祝日が入力されているセルの背景色を赤にするための条件も加味してSUMPRODUCT関数を使って個数を数えれば済む話ですので、どの様な条件になっているのかを御教え願います。

>曜日が重要なのではなく、特定の文字列と条件付き書式設定で変更した特定の背景色が合致したセルの個数を数えたいのです。はやりexcelでは難しいのでしょうか？

　もし日曜日の場合と祝日の場合で、異なる条件付き書式を用いてセルの背景色や、文字色を変更している場合には、何れにしても"別の設定"によって色が変化しているのですから、日曜日と祝日を一纏めにして数える事は最初から出来ません。(別のものを同じものとして扱う事は出来ません)
　ですから、日曜日の色を変える条件付き書式と、祝日の色を変える条件付き書式は、それぞれどのような条件や数式を使用して、色を変える様にしているのかを御教え願います。

それから、

>表示形式を"aaa"にしていますので

との事ですが、それだけでは状況が判りません。
　例え表示形式を"aaa"にしていた場合であっても、セルに入力されている値が例えば「2016/04/19」などの様な日付データとなっているのか、それとも「月」、「火」、「水」、「木」、「金」、「土」、「日」の様な文字列データとなっているのかによって状況は全く異なります。
　ですから、A1～AE1セルに入力されている値が「2016/04/19」などの様な日付データとなっているのか、それとも「月」、「火」、「水」、「木」、「金」、「土」、「日」の様な文字列データとなっているのかという事を御教え願います。

回答No.6

msMike

》 平日は背景色なし、土曜日は背景色青、日曜日は背景色赤に
》 なるように
祝日はどないしまひょ？

》 色々検索したところ文字色では難しそうなので背景色を変更…
そうは仰っても背景色変更もできないのでしょ？
そうであれば、本来ご希望の文字色変更で回答させて貰いまひょ。

A1: =DATE($A$4,$D$4,COLUMN()) ←書式は aaa とする

セル A1 に次の[条件付き書式]を設定
条件1:
＿＿数式: =AND(MONTH(A$1)=$D$4,MOD(A$1,7)=0)
＿＿フォント: 太字 斜体 青
条件2:
＿＿数式: =AND(MONTH(A$1)=$D$4,MOD(A$1,7)=1)
＿＿フォント: 太字 斜体 赤

セル A1 を A2 にオートフィルした後で、セル A2 に○を入力
範囲 A1:A2 を右方へズズーッと(列AE まで)オートフィル
２行目の全セルに入力された○の不要なものだけを Deleteキーで削除

F6: =SUMPRODUCT((A$2:AE$2="○")*(MONTH(A$1:AE$1)=D$4)*(MOD(A$1:AE$1,7)>1))
F7: =SUMPRODUCT((A$2:AE$2="○")*(MONTH(A$1:AE$1)=D$4)*(MOD(A$1:AE$1,7)=0))
F8: =SUMPRODUCT((A$2:AE$2="○")*(MONTH(A$1:AE$1)=D$4)*(MOD(A$1:AE$1,7)=1))

【蛇足】セル A4、D4 の年、月の数値を適宜変更して、１、２行目の色の位置がどのように変化するかをお楽しみください。
【お断り】祝日は貴方自身で頑張ってネ、カンタンですから！

補足

ありがとうございます。
私のとても拙い説明でこのようなご回答をいただけるとは感動です。
ちなみに、実のところを申し上げますと16日始まりのカレンダーで作成していまして、16日(A3)には"=DATE($A$4,$D$4,16)"、17日(B3)には"=IF(A3="","",IF(A3+1>DATE($A$4,$D$4+1,15),"",A3+1))"と入力し以降の日付はオートフィルで数式を入力しています。
曜日は日付のセルをそのまま反映するようにA1には"=A3"と入力しています。
この場合ですとどのようにすればよろしいのでしょうか？
祝日の設定は済んでいまして、教えていただきましたこのSUMPRODUCTで祝日の個数は正しく返ってきたのですが、平日と土曜日、日曜日の個数が正しく返されませんでした。

回答No.5

kagakusuki

回答No.2です。

　もし、A1～AE1に入力されている値が曜日ではなく、値そのものは日付となっていて、セルの書式設定の表示形式を[ユーザー定義]の

aaa

などにする事によって、表示のみを月火水木金土日の曜日としている場合には、以下の様な関数となります。

「A2~AE2に〇と入力されていて、且つ背景色なしのセルの個数を数えたい場合」の関数

=SUMPRODUCT((WEEKDAY(A1:AE1,2)<6)*(A2:AE2="〇"))

「A2~AE2に〇と入力されていて、且つ背景色が青のセルの個数を数えたい場合」の関数

=SUMPRODUCT((WEEKDAY(A1:AE1)=7)*(A2:AE2="〇"))

「A2~AE2に〇と入力されていて、且つ背景色が赤のセルの個数を数えたい場合」の関数

=SUMPRODUCT((WEEKDAY(A1:AE1)=1)*(A2:AE2="〇"))

回答No.4

imogasi

エクセルのセルの条件付き書式は、２００３にくらべて、２００７以後ずいぶん拡充されています。しかしエクセルの「関数！」は「セルの値だけ！」を問題にできて、セルの書式を条件に使うことはできず、それはいまだに実現してません。
上記の「エクセル関数はセルの値だけを問題にできて」がわかっていないエクセル初心者が多い。
ーー
だから条件付き書式で、当初に関数を使っているなら、その式の条件部分を、ＣＯＵＮＴＩＦ、ＣＯＵＮＴＩＦＳ関数の条件部分の式に移して、件数を出すことを考えてください。
あまりないケースだが、使用者が感じで色を設定したり、「より大」などの（エクセルに備わった）条件で、セルの色付けをしておれば、その部分を、質問者が、条件を調べて、改めて式で表現しなくてはならない。
＞文字色では難しそうなので背景色を変更することにしました
の意味がわからない
ＧＯＯＧＬＥで「エクセル 条件付き書式 文字色」で照会すること。
https://121ware.com/qasearch/1007/app/servlet/relatedqa?QID=017347
ほか多数の記事が出る。
「Excel 2010で入力した値によって自動的にセルの色やフォントの色を変更する方法」
に出ているではないか。初心者なんだから、もっと常時ＷＥＢ照会を活用すること。
ほとんどのエクセルの疑問は、ＷＥＢに解説有と考えて探すこと。

補足

ありがとうございます。
単に条件付き書式設定で文字色を変更する方法は知っていますが、私が知りたかったのは、あるセルに入力された文字列(この場合は"〇")と条件付き書式設定で変更した背景色(もしくは文字色)が合致するセルそれぞれの個数を数えるということです。
色々調べた結果、VBAとColorCountIfを使うという方法しか見つけられず、この方法だと条件付き書式設定で変更した背景色は反映されないのでこちらで質問いたしました。

回答No.3

bunjii

A1～AE1へ文字（月、火～土、日）が入力されているときはCOUNTIFS関数で良いでしょう。
=COUNTIFS(A2:AE2,"=〇",A1:AE1,"<>土",A1:AE1,"<>日")

A1～AE1の曜日が日付のシリアル値で表示形式を"aaa"にしてあるときは関数を変更してください。
=SUMPRODUCT((A2:AE2="〇")*(WEEKDAY(A1:AE1,2)<6))

補足

ありがとうございます。

表示形式を"aaa"にしていますので後者の数式を入力しましたが上手くいきませんでした。
ただ、曜日が重要なのではなく、特定の文字列(この場合は"〇")と条件付き書式設定で変更した背景色が合致するセルの個数を数えたいのですが、やはりexcelでは難しいのでしょうか？

回答No.2

kagakusuki

Excelには文字色や背景色の個数を直接数える事が出来る様な関数は用意されていません。
　ですから、条件付き書式を使って色を付けておられるのでしたら、その条件付き書式に設定した条件を利用して数えるしか御座いません。
　例えば、

>A1～AE1に曜日を入力していて、平日は背景色なし、土曜日は背景色青、日曜日は背景色赤になるように条件付き書式設定をします。
>A2~AE2に〇と入力されていて、且つ背景色なしのセルの個数を数えたい

という場合には、次の様な関数となります。

=COUNTIFS(A1:AE1,"<>土",A1:AE1,"<>日",A2:AE2,"〇")

　また、

>〇と入力されていて背景が青の場合

という場合には、次の様な関数となります。

=COUNTIFS(A1:AE1,"土",A2:AE2,"〇")

　また、

>〇と入力されていて
>赤の場合のセルの個数も数えたい

という場合には、次の様な関数となります。

=COUNTIFS(A1:AE1,"日",A2:AE2,"〇")

補足

ありがとうございます。

祝日も背景色を赤に変更するように設定しているのを忘れていまして・・・
曜日が重要なのではなく、特定の文字列と条件付き書式設定で変更した特定の背景色が合致したセルの個数を数えたいのです。はやりexcelでは難しいのでしょうか？