Excelでクロス表から成績を決める
タイトルが良く分からない感じになりましたが,以下のように,クロス表で成績を決める場合にどうするかという話。例えば,5段階評価で,横方向と縦方向で評価の観点が異なり,それぞれの合算で成績をつけたい場合を想定しています。一番上の行(A-F)と一番右の列(1-6)は,エクセルの外枠にある記号と思ってください。
5 | 4 | 3 | 2 | 1 | |
5 | 5 | 5 | 4 | 4 | 3 |
4 | 5 | 4 | 4 | 3 | 2 |
3 | 4 | 3 | 3 | 3 | 2 |
2 | 3 | 2 | 2 | 2 | 1 |
1 | 1 | 1 | 1 | 1 | 1 |
こちらのサイトの記事「【エクセル時短】縦横で交わるデータを取り出す!「クロス抽出」を実現する関数の組み合わせとは?」を参考というか,ほとんどそのまま使わせて頂いています。イメージとしては,以下のようになれば良い。エクセルのシートに,以下のように,A~Dは成績処理,F~Lは上のクロス表が配置されているとする。
A | B | C | D | E | F | G | H | I | K | L | |
1 | ID | 観点1(縦) | 観点2(横) | 成績(縦と横) | 5 | 4 | 3 | 2 | 1 | ||
2 | A01 | 5 | 5 | 5 | 5 | 5 | 5 | 4 | 4 | 3 | |
3 | A02 | 5 | 4 | 5 | 4 | 5 | 4 | 4 | 3 | 2 | |
4 | A03 | 3 | 2 | 3 | 3 | 4 | 3 | 3 | 3 | 2 | |
5 | A04 | 1 | 4 | 1 | 2 | 3 | 2 | 2 | 2 | 1 | |
6 | A05 | 5 | 1 | 3 | 1 | 1 | 1 | 1 | 1 | 1 |
上のD2に入っているのは,
=VLOOKUP(B2,$F$2:$K$6,MATCH(C2,$G$1:$L$1,0),FALSE)
vloolupは(検索値,範囲,列番号,検索の型)であり,縦に検索する値(上だとB2の5)をFの列から探し,F2~L6の四角の範囲の中で,列番号で指定された場所にあるものを完全一致(False)で取ってくる。
matchは(検索値,検索範囲,照合の種類)なので,横に検索する値(上だとC2の5)をG1~L1の範囲で探し,完全に一致するもの(照合の種類が0)を探す。返す値は,範囲の中の列番号なので,上の場合には,1(列目)を返す。
関数の中身は,vlookup(検索値(B2=5), 範囲(F2~K6), match(検索値(C2=5), 検索範囲(G1~L1), 検索の型(完全一致=0)), False(vlookupの完全に一致))となる