各教科の基準点以上と合計点数で、ランク(アルファベット S〜D、不合格)を付ける。ワークシート実践編
ものすごく、手間がかかり、面倒かもしれませんが
できるだけ、できた物を汎用が効くように作りました。
至らない点が多々あるかと思いますが、よろしくお願いします(_ _)
↓サンプルダウンロードファイル
sample0003.xlsx
前回のサンプルより高度にしていますが、
ほとんど、前回のワークシート関数をコピペで作る方法とやり方は変わりません。
参考までにということで、念のために載せています。
※すごく長い内容なので、ご注意ください。
サンプルファイルには、最終の式までの工程の式を消さずに載せています。
※投稿内容は、サンプルを参考にやっていきますので、サンプルを表示したあと、
新規作成で新たにワークブックを作っていただき、サンプルのデータをコピペしていただいて、
比べながらされてください。
サンプルを元にしますので、全て入力等は半角でしてください。
流れの工程、
①各教科の点数を各教科の基準点以上かどうかをそれぞれ判断する式を作る
②すべて基準点以上かの判定式のAND関数で作る
③それぞれのランク判定式の作成
④式を合成して、完成させる
①各教科の点数を各教科の基準点以上かどうかをそれぞれ判断する式を作る
※絶対参照と相対参照がありますので、ご注意を・・・
G列の合計のところは、普通にSUM関数を使ってます。
セルJ12に
= を入力し、セルC6をクリックし、すぐにF4キーを入力（絶対参照）し、
そのまま<=を入力し、セルD9をクリックしF4キーを3回（相対参照）押す。
すると
=$C$6<=$D9
なったと思います。
同様に
セルK12に
=$C$6<=$E9
セルL12に
=$C$6<=$F9
を作ります。
これで、それぞれの点数を変えてみて、TRUE または、FALSEがでるか試してみてください。
②すべて基準点以上かの判定式のand関数で作る
セルJ12 〜 セルL12までの式の先頭の = を削除し、
$C$6<=$D9
$C$6<=$E9
$C$6<=$F9
にする
セル J11 に
and() ※半角入力です。
を入力、
そして、J12をクリックしてコピーしておき、
セルJ11 をクリックして、F2キーで文字を挿入できる状態にして、←キーを一回押し、
（式のバーのその場所をマウスでクリックしてもオッケーです。）
, を入力して、貼り付けします
and($C$6<=$D9,)
になっていれば、オッケーです。
そして、同様にセルK12、セルL12をJ11にコピペして
and($C$6<=$D9,$C$6<=$E9,$C$6<=$F9)
になっていれば大丈夫です。
そして、先頭に = を付けて式のバーが
=and($C$6<=$D9,$C$6<=$E9,$C$6<=$F9)
で、エンターし、andの部分が自動で、ANDになると思います。
=AND($C$6<=$D9,$C$6<=$E9,$C$6<=$F9)
なっていればオッケーです。では、各教科の基準点や、教科の点数をいろいろと入れてみてください。
条件通りに、TRUE やFALSE に変わればオッケーです。
そして、式がセルを見てわかるように念の為、 = を削除して、
AND($C$6<=$D9,$C$6<=$E9,$C$6<=$F9)
にしておいてください。
③それぞれのランク判定式の作成
IF関数で、クラス(S 〜 D)の判定式を作ります。
IF関数は、全部で4つ作ります。※すこし難しいやり方でします。
まずは、S判定から
セルJ9 に
=IF(D$3<=$G9,D$2,"儀")
を作ります。
セルJ9に直に=IF()を書き、
論理式のところで
セルD3をクリックしF4キー を2回押し $D3にして、Dのところだけを$を付けて、
<=
を入れて
セルG9 をクリックし、F4キーを3回押しGのところだけ$を付けます。
もしかしたら、F4キーを何回か押して、どのように$がつくか試したらいいかもしれません。
式のバーで、
=IF(D$3<=$G9,｜)
↑カーソルの位置
なってればオッケー
※一番右の ,（コンマ） がない場合は 入れてください。$ は直接セルに入力しても良いです。
そして、TRUE の場合の処理のカーソルの場所で、
セルD2をクリックしF4キーを2回押しD$2にし、
=IF(D$3<=$G9,D$2,)
なってれば、大丈夫です。
で、FALSEの場合の処理をします。カーソルを動かし、
=IF(D$3<=$G9,D$2,｜)
↑カーソルの位置
ここには、文字列を何でも良いので入れていてください。
実は、表計算ソフトによっては、IF関数で=入れずに文字列として式のIF()を作っていき
""を使って、その後 = を付けて=IF(...,"","")みたいにしたらエラーがでることがあります。
なので、今回は、関数式でやっています。
例:
=IF(D$3<=$G9,D$2, "儀")
あとは、この式をセルJ9〜セルM9 までフィルハンドルでコピーしてください。
そして、最後の セルM3の式は、セルH2をクリックして、F4キーで$H$2にします。
=IF(G$3<=$G9,G$2,$H$2)
点数を変えていくと、それぞれのセルのランクが変わっていくと思います。
では、式を見やすくしたり、コピペが簡単にできるように
セルJ9 〜 セルM9 までの = をそれぞれ削除しくてください。
④式を合成して、完成させる
いったん、IF関数は、式として作っているので、あとは、最後の方からコピペしていくだけです。
セルL9 をセルJ8にコピペし、"儀"のところを空欄にする
IF(F$3<=$G9,F$2,)
セルM9 をコピーし、) の前でカーソルをクリックし
ペースト、セルJ8が
IF(F$3<=$G9,F$2,IF(G$3<=$G9,G$2,$H$2))
になっていれば大丈夫です。
では、セルJ8 の先頭に = を入れて数値を変え試してみてください。
=IF(F$3<=$G9,F$2,IF(G$3<=$G9,G$2,$H$2))
そして、また、セルJ8の=を削除しておいてください。
続いて、セルK9をコピーして、セルJ7へ ペースト
セルJ7の
IF(E$3<=$G9,E$2,"儀")の
"儀"を削除して
IF(E$3<=$G9,E$2,)
にします。
セルJ8 をコピーして、
セルJ7の ) の前にペースト
IF(E$3<=$G9,E$2,IF(F$3<=$G9,F$2,IF(G$3<=$G9,G$2,$H$2)))
= を付けて試してみてください。
そして、セルJ9 をコピーして セルJ6へ ペーストして、これの"儀"を削除
IF(D$3<=$G9,D$2,)
で、セルJ7 コピーして、セルJ6 の ) の前にペースト
最終合成します。
セルJ5 に
if()
と入力、
セルJ11 のAND(...)をコピーして、セルJ5に貼り付け
IF(AND($C$6<=$D9,$C$6<=$E9,$C$6<=$F9))
この状態で、=を付けて数値を変えて、試してください
そのあと、また、= を削除してください。
そして、 , を一番最後の)の前に入力していただき、
IF(AND($C$6<=$D9,$C$6<=$E9,$C$6<=$F9),)
にして、,と ) の間に
セルJ6 をコピぺする
IF(AND($C$6<=$D9,$C$6<=$E9,$C$6<=$F9),IF(D$3<=$G9,D$2,IF(E$3<=$G9,E$2,IF(F$3<=$G9,F$2,IF(G$3<=$G9,G$2,$H$2)))))
= を付けて試してみてください。
=IF(AND($C$6<=$D9,$C$6<=$E9,$C$6<=$F9),IF(D$3<=$G9,D$2,IF(E$3<=$G9,E$2,IF(F$3<=$G9,F$2,IF(G$3<=$G9,G$2,$H$2)))))
科目の点数が基準点を下回ると FALSE になるかと思います。
なので、不合格を最後に表示させます。
セルJ5を = のついた状態で ) の前に , を入力して、そのままセルI2をクリックして、絶対参照にします。
=IF(AND($C$6<=$D9,$C$6<=$E9,$C$6<=$F9),IF(D$3<=$G9,D$2,IF(E$3<=$G9,E$2,IF(F$3<=$G9,F$2,IF(G$3<=$G9,G$2,$H$2)))),$I$2)
では、いろいろと試してみてください。
最後にランクの列にコピーします。
コピーするときは、セルJ5の式の頭の = を削除して、文字列の状態にして、セルH9にコピーします。
これは、= （式）の状態でコピーしたらセルのアドレスが変わってしまいますので、
= （式）を削除し、コピペする必要があります。
コピペしたら、セルH9に再度 = を付けてください。
※ランクのS 〜 不合格までの値を変えてもいけます。
たとえば、A B C D E 不合格とかです。
それと、セルG9とセルH9を範囲指定して下にフィルハンドルでコピーしたら、
複数の人数分、判定ができます。
長々すみません。
もしよければ参考までによろしくお願いします。