エクセルのシートの条件付データをある印に一括置換

質問

エクセル２００３を使っている関数初心者です。普段の受業での評価に基づいて成績処理をしようと思います。以下のような場合、エクセルでどのようにすれば簡単に処理ができるか教えてください。
　(1)Ｂ１セルからＫ１セルまでには、横並びで10人の生徒の名前が入っています。
(2)Ａ２セルからＡ１１セルまでには、縦並びに10回の評価名が入っています。
(3)Ｂ２セルからＫ１１セルには、それぞれの得点が入力されています。
(4)２行目から１１行目までの各生徒の得点は、「条件付き書式」によって、それぞれ異なる条件　　で印がつけられています。（現在は、「セルの強調表示ルール」で、「指定範囲より大きい」「指　　定範囲より小さい」「指定の範囲内」など様々な条件が付けられています。そしてその結果は、　　「濃い赤の文字・明るい赤の背景」によってセルが色付けされています。）
(5)以上のようなシートの色付部分だけを「●」に置換したいのです。●印に置換することによ　　って●印の数によって今後、評定を算出するようにしたいのです。
(6)各行ごとの条件によって●を出力するようにすればよいのですが、対象の行数が多いことと、　　学期ごとに評価対象が変わることがありますので、基本のシートを作っておきたいと思っています。どなたかお分かりでしたらお願いします。

回答No.2

KURUMITO · ベストアンサー

シートの色つき部分を●に置換してその数を数えたいとのことですが色を判断し●にするなどは関数を使って対応することはできません。
色つきにするための判断基準そのものを式に導入して●に相当する数を求めることにすればよいでしょう。しかしその判断基準となるものは行によって違ってくる可能性もあるとのことですからそれぞれの行ごとに判断基準が分かるようにしそれを式に取り込むようにしてはいかがでしょう。また、セルの条件付き書式でもそれらの判断基準を取り入れた形で設定できるようにすることでしょう。
例えば次のようにします。
お示しの表でB1セルからK1セルには氏名が、A2セルから下方には試験などの項目などが入力されているとします。B列からK列の2行目から下方には項目名に対応した試験などの点数が入力されているとします。
そこで各行での判断基準となる条件ですがN1セルには「以上」、O1セルには「以下」、P1セルには「以上から」、Q1セルには「以下まで」の文字をそれぞれ入力します。
そこで例えば2行目では90以上が該当すればN2セルには90，30以下が該当すればO2セルに30，50以上で60以下が該当すればP2せるに５０、Q2せるに60と入力します。3行目も同じ条件であればそれぞれのデータを下方にコピーすればよいでしょう。
そこで●に相当する該当数ですが行ごとに表示させるとしてL1セルには赤色該当数とでも入力します。
L2セルには次の式を入力して下方にドラッグコピーします。

=IF(COUNT(B2:K2)=0,"",IF(N2="",0,COUNTIF(B2:K2,">="&N2))+IF(O2="",0,COUNTIF(B2:K2,"<="&O2))+IF(P2="",0,COUNTIF(B2:K2,">="&P2))-IF(Q2="",0,COUNTIF(B2:K2,">"&Q2)))

なお、セルの条件付き書式の設定ですがB2セルからK列の下行のセルを範囲として選び、その後に「ホーム」タブの「条件付き書式」から「新しいルール」をクリックします。
「数式を使用して・・・・」にチェックをして数式の窓には次の式を入力します。

=OR(B2>=$N2,B2<=$O2,AND(B2>=$P2,B2<=$Q2))

「書式」をクリックして「塗りつぶし」のタブから赤色などを指定してOKすればよいでしょう。

お礼

ご丁寧に解説していただきありがとうございました。参考にさせていただきます。助かりました。ありがとうございました。

回答No.3

KURUMITO

回答No2です。
条件付き書式の式では次の式にするのがよいでしょう。

=OR(AND($N2<>"",B2>=$N2),AND($O2<>"",B2<=$O2),AND($P2<>"",$Q2<>"",B2>=$P2,B2<=$Q2))

このようにすることでN列、O列、P,Q列などのセルを空にした場合には空にしたセルの条件が無視できるようになっています。常にN列からQ列までのすべての条件が入力されている必要もありません。

お礼

なるほど、そうゆう方法もあるのですね。参考になりました。ありがとうございました。

回答No.1

keithin

まず、とりあえずあなたがやってみたかったことですが。
＞以上のようなシートの色付部分だけを「●」に置換したいのです

エクセルでは、条件付き書式の「結果」を直接調べたり、何かに置換したりといった事は全くできません。
そのアプローチは諦めてください。

次に、実際にあなたがやりたいことですが。
＞●印の数によって今後、評定を算出するようにしたい

●に見合う点数の数を、生徒ごとに直接計算で求めます。
そこでご質問は評価項目ごとに判断基準が異なる事ですが、簡単には次のように整理します。

＃参考
全ての評価項目が一律な判定基準（たとえば７０点以上で●）なら、シンプルにCOUNTIF関数で計算します

いま、評点がそもそも０～１０とか０～１００点で記録されています。

ここで、ある項目は「４点から７点」で●だとします
次の項目は「３１より小さい」で●だとすると、これは言い換えると「０から３０」で●だということです
別の項目が「６９より大きい」で●だとすると、これは言い換えると「７０から１００」で●だということです
＃評点に１００点までのような上限が無い場合は、９９９９点などのありえない大きい数字までとみなします

つまりすべての評価は、必ず「下限と上限の間なら●」という具合に書きなおすことが出来ます。
Ｂ列に各評価項目の●の下限値を記入します
Ｃ列に各評価項目の●の上限値を記入します

Ｄ列の生徒のＤ２からＤ１１までの点数の「下限から上限までに入っている個数」は
Ｄ１２：
=SUMPRODUCT(($B$2:$B$11<=D2:D11)*(D2:D11<=$C$2:$C$11))
のように計算する事ができます。
右にコピーして生徒ごとの●の数を数えます。

お礼

早速、ご丁寧に教えていただきありがとうございました。教えていただいたことを参考にやってみます。