先生が使える  「順位はこうして求められる」


匠への技

「匠への技」 シリーズ開始です。 VOL-1は、成績表をベースとした順位、検索等を関数で指定する方法です。
匠への技は、関数を匠に使いこなすことを目的としており、VOL-1では11種類の関数で構成しています。

 シナリオは、「定番の成績表」と題したシートを作成します。
教科科目の縦横の計、平均といった決まったスタイルへちょっとひと工夫!
見れば、役立つ使い道が連想されるでしょう。  (言い過ぎ・・・・・・・・・・・・ ^^ゞ)


 頭に描く作表イメージ

タイトル「定番の成績表」です。
行11は、見出し行です。行12から26までが生徒の教科毎の成績と評価欄です。
行27は行の平均または合計です。
E列の合計は3教科の点数計です。F列の平均は3教科の平均点です。G列の順位は総合順位です。H列の国語順位以降各教科毎の順位です。 下図「定番の成績表」を基本に作表を開始します。



 ※後続の章「シナリオに基づく関数の説明」への戻り先です。

■シナリオ1■  ■シナリオ2■  ■シナリオ3■  ■シナリオ4■  ■シナリオ5■  ■シナリオ6■


 使用する関数の種類
  1. IF(論理式,真の条件,偽の条件)
  2. AVERAGE(数値1,数値2,...)
  3. MAX(数値1,数値2,・・・・)
  4. ISERROR(テストの対象)
  5. MATCH(検査値,検査範囲,照合の型)
  6. INDIRECT(参照文字列,参照形式)
  7. SUM(数値1,数値2,...)
  8. SUMIF(範囲,検索条件,合計範囲)
  9. COUNTIF(範囲,検索条件)
  10. ROUND(数値,桁数)
  11. RANK(数値,範囲,順序)


 表作成のシナリオ
  1. 生徒毎の国語、数学、英語の得点の3教科合計点とその平均点
  2. 教科毎の平均点
  3. 総合順位と各教科毎の順位
  4. 順位から該当生徒名、教科毎の順位の検索
  5. 上位5位までの総合計と平均
  6. 教科毎の平均点より低い点数の生徒をセル色を変えて区分


 シナリオに基づく関数解説
■ シナリオ 「1」                   》 イメージ表へJUMP

@生徒毎の国語・数学・英語の点数を集計する。
 セルE12が生徒名テスト1の合計セルとなります。
集計は、SUM関数を使います。
  =SUM(B12:D12)  と書式入力を行います。  は〜の意味で、B12からD12を指します。 セルC12の入力を省略することができます。 また、2つ以上に跨ぐセルを集計する場合は , で区切り設定します。例えば、B12〜B30、D20からD22、S15を集計する場合は、 =SUM(B12:B30,D20:D22,S15) と定義します。)
入力後、E12のフィルハンドル(セルの右隅にマウスカーソルをポイントするとマウスの形状が黒十字に変化する)をドラッグしてオートフィル機能で最終行GTO(グレートティーチャ・・・・ではなく総合計)セルE27まで書式をコピーします。

A生徒毎の平均点数を計算する。
 セルF12が生徒名テスト1の平均点セルとなります。
平均は、AVERAGE関数を使います。
  =ROUND(AVERAGE(B12:D12),1) を入力します。小数点以下が発生するのでROUND関数との組み合わせで定義します。平均を四則演算式で表すと 合計÷合計の個数 で計算されますがAVERAGEは÷除数の数がなく、指定した範囲の値の入力されている個数を自動的に認識し平均を計算します。空欄の場合は、平均個数にカウントされませんので留意してください。
ROUNDは、与えられた数値の小数点の位取りを行います。この場合は、AVERAGEで計算した値に対して ,1 小数点第1位四捨五入を指定しています。ちなみに円未満の場合は ,0 となります。
これは、関数の組み合わせの簡単な例ですが、関数は組み合わせることにより、その持ち合わせの機能以上の威力を発揮します。 以上@同様に書式をコピーします。


■ シナリオ 「2」                   》 イメージ表へJUMP
B国語・数学・英語の平均点を求める。
 各教科の平均点は、GTOの欄(国語:セルB27 数学:セルC27 英語:セルD27)に求めます。
シナリオ「1」のAVERAGE関数を使います。
  =ROUND(AVERAGE(B12:B26),0)  国語のセルB27の関数式です。国語の先頭行B12から末尾行B26までの平均を計算してROUND関数小数値の丸め 0 を指定しているので円未満四捨五入の設定です。
数学・英語も同様に関数式を入力します。


■ シナリオ 「3」                   》 イメージ表へJUMP
C総合順位を求める。
 順位は、RANK関数を使い求めます。表上の書式を設定する位置は列Gの行12から26までです。
  =RANK(F12,$F$12:$F$26)  先頭の生徒テスト1のセルF12の関数式です。RANK関数のかっこ内のF12は順位付けする基準値(テスト1の平均点)です。 , の後の$F$12:$F$26は、F12からF16のセル範囲を示していますがどうしてか$マークが付いています。何故? 皆さんからすれば常識ですね。でも、わからない人のために解説すると、$マークの付いたセルを「絶対セル」と呼び、通常オートフィルでコピーすると行列が連続してドロップポイントまで変化(コピー方向による)しますが絶対セルは変化しません。単純にF12:F26と定義するとオートフィルで1つ下の行にコピーするとF13:F27と行が1個移動します。順位を求める範囲が上下1づつずれてしまう結果になるので正確な順位を求めることができません。
そこで、コピーしても自動で変化しないようにするのが絶対セルの定義です。何行コピーしても、F12:F26は変化しません。
入力の時に$マークを付すもよし、F12:F26と入力する時に F12 → F4キー → : → F26 → F4キー の順で入力するもよし。 通常は、F4キーで絶対セルを指定します。関数式を入力後、F26まで関数式を複写します。

各教科毎の順位も同じ理屈となりますので省略します。


■ シナリオ 「4」                   》 イメージ表へJUMP
D総合順位の指定から生徒名、3教科の順位を検索する。
 総合順位入力セルB3へ総合順位を入力することにより、該当する生徒名をセルD4(D4:E4)、国語順位をセルH4、数学順位をセルI4、英語順位をセルJ4へ表示させます。
まず、セルD4の生徒名の関数式です。
   =IF(MAX(G12:G26)<B4,"指定誤り",IF(ISERROR(MATCH(B4,G12:G26,0)),
                              "指定誤り",INDIRECT("A"&MATCH(B4,G12:G26,0)+11)))


ここは、要チェック!です。 説明が複雑になりますので分解して解説します。

=IF(MAX(G12:G26)<B4  最大値を求めるMAX関数。セルG12:G26は総合順位の範囲でその中の最大値を求めています。
 次の <B4 は入力値です。順位の最大値<入力値 とは、MAX関数で求めた最大値より入力した値が大きい時を判定しています。要するに上限の入力チェックです。

<真の場合> 入力値がMAX関数で求めた値より大きい時 "指定誤り" の文字列をセルD4(生徒名表示セル)へ表示しエラーを促します。

<偽の場合> IF(ISERROR(MATCH(B4,G12:G26,0)) 再びIF関数の論理式です。IFに重ねてIFを定義することをネストする(ネストIF)と呼んでいます。条件は、ISERROR関数とMATCH関数の組み合わせです。MATCH関数がエラー値を返しているかどうかの判断がISERROR関数です。MATCH関数は、例題の総合順位の最小値より小さい場合(0以下、もしくは数値以外)に #N/A を返します。
エラーが生じた場合はISERROR関数のエラー判定により真の条件となります。
 MATCH関数は、検索・行列関数に属しており指定された照合の型に従って検査範囲内を検索し、検査値と一致する要素の範囲内で相対的な位置を表す数値を返します。要するに総合順位の範囲セルG12:G26からセルB4の入力値に該当する検査範囲の相対的な行数を求める関数ということです。ちなみにセルG12が該当した場合は1、G26が該当した場合は15となります。
最後の ,0 は完全一致の指定です。

<真の場合>
  MATCH関数がエラーを引き起こしてる(入力ミス)ことになり "指定誤り" の文字列をB4へ表示します。

<偽の場合>
  INDIRECT("A"&MATCH(B4,G12:G26,0)+11)  INDIRECT関数は、指定される文字列への参照を返します。参照文字列によって指定されるセルに入力されている文字列を介して、間接的なセルを指定することができます。
MATCH関数で該当する行を求め1行目から11行目までの非明細の行数分 +11 を加算して該当するセルの行列位置を求めています。例えばMATCH関数でセルG12が該当すれば 1 が返され11行を加算していますので12となり絶対行が求められるます。INDIRECT関数により()内"A"は、生徒名の列 A を指し、&(アンパーサンド)で後続の文字列MATCH関数で求めた相対値+11の値と連結して、検索値順位に該当するセルの行列が求められる仕組みです。

以上が総合順位の入力から生徒名を引用する関数の説明です。

次に各科目の順位です。
国語の順位を求めるセルH4の関数式です。
   =IF(D4<>"",IF(D4="指定誤り","",INDIRECT("H"&MATCH(B4,G12:G26,0)+11)),"") 

判断は、総合順位ではなく生徒名の検索した結果で判定しています。ここは前例と同様ですので説明を省略します。前例を参考し思考してみてください。


■ シナリオ 「5」                   》 イメージ表へJUMP

F上位5位までの合計と平均を求める。
条件による集計関数SUMIF関数を使います。
セルE6へ1位から5位までの総科目の合計を求めます。
   =SUMIF(G12:G26,"<=5",E12:E26)  総合順位のG12:G26を対象に "<=5" 5位以下(5位を含む)の順位に該当する行を検索し、3教科合計セルの範囲 E12:E26 の該当する行の値を加算しなさい。といった関数式です。


次に1位から5位までの平均です。
関数式から見てみましょう。

   =SUMIF(G12:G26,"<=5",E12:E26)/COUNTIF(G12:G26,"<=5")/3  SUMIF関数で求めた1位から5位までの総合計を求めCOUNTIF関数で求めた件数を除して総平均を求め、3で除して1教科当たりの平均を求めます。
1位から5位までなのでCOUNTIFで件数を求めるまでもなく5で除せばよいのですが同点数の生徒が存在する場合がありますので該当者を含めた全ての件数が必要になるため関数COUNTIFで対応します。
COUNTIF関数は、SUMIF関数と同じ機能です。セルG12:G26を検索範囲とし、"<=5" 5より以下小さい順位(=が指定されているので5位を含む)により該当件数を求めます。


■ シナリオ 「6」                   》 イメージ表へJUMP
G教科毎の平均点より低い該当生徒のセル色を変える。
この要件は、関数を使いません。Excelの「条件付き書式の設定」で行います。

その1 書式を設定するセルをドラッグします。
     まず、国語から実行しますのでセルB12からB26をドラッグします。

その2 「メニューバー」 → 「書式」 → 「条件付き書式」 を選択します。

      

その3 条件付き書式の設定ダイヤログの項目を下図のように指定して「OK」ボタンをクリックします。

      


こんな感じになります。

       

平均より小さい時に指定色に塗りつぶされます。


「匠への技」 VOL-1 いかがでしたか?
次回、VOL-2では販売屋さんが使える「売上管理」をテーマに掲載します。

−おことわり−
都合により一部、もしくは全部の内容が変更となる場合があります。
ご了承下さい。


 もひとつ おまけ
テストデータとして使う国語、数学、英語の点数を考えながら入力するのは非常に面倒ですよね。
そこで、おまけとして紹介するこの関数は、非常に便利です。
耳を研ぎ澄ませて下さい。
  RAND()  乱数を求める関数を使います。

○使い方1 0から100までの乱数を求める。
   =RAND()*100

○使い方2 40から100までの乱数を求める。
   =RAND()*(100-40)+40

数値のみならず、アルファベットも求めることができることをつけ加えておきます。
今回求めたのは、40点以上100点以下を範囲とし生徒数分科目毎にRAND関数で求めコピー&ペースト(値のみ)によりテストデータを作成しています。

おっと、肝心なことを忘れていました。
乱数は、小数点以下まで求められますので丸め処理が必要です。四捨五入関数との組み合わせです。

  =ROUND(RAND()*(100-40)+40,0)

RAND関数により求めた値の小数点以下に対してROUNDにより小数点以下四捨五入した値を求めています。
・・・・ 40, の後の0が丸め対象とする小数点の位取り位置で円未満四捨五入の意味です。小数点第1位までであれば、1を指定します。ちなみに、切り上げはROUNDUP関数、切り捨てはROUNDDOWN関数を使います。



○「匠への技」VOL-1で作成したExcelブックのダウンロード

  演習できるExcelブックを用意しています。下図のダウンロードボタンをクリックして下さい。

     Excel Book Name: takumivol-001.xls  Download file: takumivol-001.lhz