|
|
| ⑪ (L2)に赤池さんの５教科の合計の順位を計算する関数を入力します。順位は比較です。その点は特定の範囲の中で何番かということです。したがって、順位を計算する関数は、 =RANK(合計のセル:比較の範囲)というようになります。赤池さん合計のセルはK2です。合計の範囲はK2:K20です。範囲はオートフィルで変えたくないので$をつけます。つまり、 =RANK(K2,K$2:K$20) となります。オートフィルで全員の順位を出してください。（$はF4キーを押すと自動で入力される裏技があります。） ※ 個数（人数）を数える作業はとても重要です。個数を数える関数は数種類あります。COUNTは数字が入ったセルの個数を数えます。COUNTAは文字や数字など、とにかくデータが入っているセルの個数を数えます。COUNTIFはある条件を満たしたセルの個数を数えます。COUNTIFSは複数の条件を満たしたセルの個数を数えます。 ⑫ (D21)に全体の人数を数える関数を入力します。この場合は、=COUNTA(範囲)を使います。範囲は一応氏名の列にします。 =COUNTA(E2:E20) となります。 ⑬ (D22)に男子の人数を数える関数を入力します。この場合は、=COUNTIF(範囲,条件) です。範囲は性の列D2:D20です。条件は男です。男は文字なので"男"とします。 =COUNTIF(D$2:D$20,"男") となります。2と20の前に$をつけたのは次の女子の人数を数えるときにコピーして利用できるように範囲を変えないためです。 ⑭ (D23)に女子の人数を数える関数を入力します。セルD22をコピーしてください。そして"男"を"女"に変えます。 =COUNTIF(D$2:D$20,"女") となります。 ⑮ (D24)に１組の男子の人数を数える関数を入力します。使う関数は、=COUNTIFS(条件１の範囲,条件１,条件２の範囲,条件２）です。条件１の範囲は組の列でB$2:B$20です。条件１は"1"です。条件２の範囲はD$2:D$20です。条件２は"男"です。$をつけたのはコピーして利用できるようにするためです。つまり、 =COUNTIFS(B$2:B$20,"1",D$2:D$20,"男") となります。 ⑯ (D25)の１組の女子の人数、⑰(D26)の２組の男子の人数、⑱(D25)の２組の女子の人数は、セルD24をコピーして、"男"を"女"に変えたり、"1"を"2"に変えたりします。 ⑯ (D25)は =COUNTIFS(B$2:B$20,"1",D$2:D$20,"女") です。 ⑰ (D26)は =COUNTIFS(B$2:B$20,"2",D$2:D$20,"男") です。 ⑱ (D27)は =COUNTIFS(B$2:B$20,"2",D$2:D$20,"女") です。 ⑲ (P23)に合計が２００点以上の人数を数えます。使う関数は、=COUNTIF(範囲,条件)です。範囲はK$2:K$20です。範囲には$をつける習慣をつけましょう。条件は">=200"と表示します。200をすぐ左のセルO23に入力しておくと">="&O23と表示することもできます。後者を使うと、この下の得点分布を計算するのに便利です。入力する関数は =COUNTIF(K$2:K$20,">="&O23") となります。 ⑳ (P24)に合計が１５０点以上２００点未満の人数を数える関数を入力します。使う関数は、=COUNTIFS(条件１の範囲,条件１,条件２の範囲,条件２)です。条件１の範囲はK$2:K$20です。条件１は１５０点以上です。すぐ左のセルO24に150が入力されているので、">="&O24です。条件２の範囲は同じでK$2:K$20です。条件２は２００点未満です。すぐ左の上のセルO23に200が入力されているので、"<"&O23です。まとめると、 =COUNTIFS(K$2:K$20,">="&O24,K$2:K$20,"<"&O23) となります。 ㉑ (P25)に１００点以上１５０点未満、㉒(P26)に５０点以上１００点未満、㉓(P27)に０点以上５０点未満の人数を数えます。セルP24をオートフィルで一気にセルP25からセルP27までコピーしてください。それぞれ、 =COUNTIFS(K$2:K$20,">="&O25,K$2:K$20,"<"&O24) =COUNTIFS(K$2:K$20,">="&O26,K$2:K$20,"<"&O25) =COUNTIFSK$2:K$20,">="&O27,K$2:K$20,"<"&O26) となります。最初のO24がO25、O26、O27と変化し、最後のO23がO24、O25、O26と変化し、それぞれの正確に人数を数えてくれます。 ㉔ (M2)に赤池さんの順位を計算するRANK以外の関数を入力します。使用する関数はCOUNTIF（=COUNTIF(範囲,条件)）です。赤池さんの得点より高い得点の数を数え、それに１を加えれば、赤池さんの順位になります。範囲は合計の列K$2:K$20です。オートフィルで範囲を変えたくないので、数字の前に$つけます。条件は">"&K2です。最後に+1です。まとめると、 =COUNTIF(K$2:K$20,">"&K2)+1 となります。オートフィルで下にコピーして全員の順位を出してください。 ㉕ (N2)に赤池さんの１組の中での順位を計算する関数を入力します。使用する関数はCOUNTIFS（=COUNTIFS(条件１の範囲,条件１,条件２の範囲,条件２)）です。条件１の範囲は組の列B$2:B$20、条件１は、B2に１が入力されていますので、B2です。条件２の範囲は得点の列K$2:K$20です。条件2は">"K2です。最後に１を加えます。 =COUNTIFS(B$2:B$20,B2,K$2:K$20,">"&K2)+1 となります。オートフィルで下にコピーします。組のB列が２の生徒は２組の中の順位が計算されます。（RANKでは、RANKIFやRANKIFSはないので、条件付きの順位を出すことはできません。） ㉖ (O2)に赤池さんの男女別順位を計算する関数を入力します。使用する関数は㉕(N2)と同じです。条件１の範囲は性の列D$2:D$20です。条件１はD2に男が入力されているので、D2です。条件２の範囲は合計の列K$2:K$20です。条件２は">"K2です。最後に１を加えます。 =COUNTIFS(B$2:B$20,B2,K$2:K$20,">"&K2)+1 となります。これで赤池さんの男子の中の順位が計算されます。オートフィルで下にコピーします。性のD列が女の生徒は女子の中の順位が計算されます。 ㉗ (P2)に赤池さんの合計が２００点以上ならばＡ、１００点以上ならばＢ、それ以外（つまり１００点未満）はＣ、という評価を表示する関数を入力します。使用する関数は、=IF(条件１,表示１,IF(条件２,表示２,表示３))です。条件１はK2>=200です。表示１は"A"です。条件２はK2>=100です。表示２は"B"です。表示３は"C"です。Ａ・Ｂ・Ｃは文字なのでダブルクォーテーションで囲みます。まとめると、 =IF(K2>=200,"A",IF(K2>=100,"B","C")) となります。オートフィルで下にコピーして、全員の評価を表示してください。ダブルクォーテーションの中は、甲・乙・丙でも、優・良・可でも、何でもよいです。今回は３段階でしたが、段階を増やしていくときはIFを増やしていきます。赤池さんの合計が２００点以上ならば５、１５０点以上ならば４、１００点以上ならば３、５０点以上ならば２、それら以外は１という関数は、 =IF(K2>=200,"5",IF(K2>=150,"4",IF(K2>=100,"3",IF(K2>=50,"2","1")))) となります。IFが４つあるので、最後のカッコも４つになります。 |
