こんにちはゲストさん。会員登録(無料)して質問・回答してみよう!

解決済みの質問

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

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

投稿日時 - 2012-09-15 12:27:42

QNo.7698699

困ってます

質問者が選んだベストアンサー

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

投稿日時 - 2012-09-15 16:20:40

お礼

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

投稿日時 - 2012-09-15 19:21:48

このQ&Aは役に立ちましたか?

0人が「このQ&Aが役に立った」と投票しています

回答(3)

ANo.3

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

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

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

投稿日時 - 2012-09-15 16:59:41

お礼

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

投稿日時 - 2012-09-15 19:20:44

ANo.1

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

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


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

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

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



いま、評点がそもそも0~10とか0~100点で記録されています。

ここで、ある項目は「4点から7点」で●だとします
次の項目は「31より小さい」で●だとすると、これは言い換えると「0から30」で●だということです
別の項目が「69より大きい」で●だとすると、これは言い換えると「70から100」で●だということです
#評点に100点までのような上限が無い場合は、9999点などのありえない大きい数字までとみなします

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

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

投稿日時 - 2012-09-15 13:06:49

お礼

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

投稿日時 - 2012-09-15 19:18:36

あなたにオススメの質問