Excelでクロス表から成績を決める(その2)

以前こちらで「EXCELでクロス表から成績を決める」を書きましたが,うまくいかないのと,より良さそうな方法があったので,(その2)としてアップデート版を書いておきます。使うデータとやりたいことは以前と同じ。以下のように,観点1が1行目,観点2が1列目として,両方の観点が5なら5,観点1が5で,観点2が4ならば,4を与えたい。

54321
555443
454432
343332
232221
111111

こちらのサイトの記事「【エクセルのクロス抽出】INDEX関数とMATCH関数の組み合わせ」を参考にしています。イメージとしては,以下のようになれば良い。エクセルのシートに,以下のように,A~Dは成績処理,F~Lは上のクロス表が配置されているとする。

ABCDEFGHIKL
1ID 観点1(縦) 観点2(横) 成績(縦と横) 54321
2A01555555443
3A02545454432
4A03323343332
5A04141232221
6A05513111111

上のD2に入っているのは,

=INDEX($G$2:$L$6,MATCH(B2,$F$2:$F$6,0),MATCH(C2,$G$2:$L$6,0))

INDEXは(参照, 行番号, 列番号)を引数としてとり,範囲の中から行番号と列番号のクロスしたセルの内容をとってくる。

matchは(検索値,検索範囲,照合の種類)なので,

縦に検索する値(上だとB2の5)をF2~F6の範囲で探し,完全に一致するもの(照合の種類が0)を探す。返す値は,範囲の中の列番号なので,上の場合には,F2に当たる2(行目)を返す。

横に検索する値(上だとC2の5)をG1~L1の範囲で探し,完全に一致するもの(照合の種類が0)を探す。返す値は,範囲の中の行番号なので,上の場合には,G2に当たる2(行目)を返す。

INDEXの中身は,(G2からL6の範囲のなかで,2行目,2列目)=5を返す。

Excelで指定した行ごとに数値を1増やす

たとえば,5行ごとに1増やすというような場合の話。

人力検索はてなのこちらのページに同じような質問と答えがあったので,そちらを参考にして解決しました。

https://q.hatena.ne.jp/1345074944

=INT( ( ROW() - 開始行 ) / x行毎 ) * yずつ増やす + 最初の数値

ということなので,

=INT((ROW() - 1) / 5 ) * 1 + 0
  A
1 1
2 1
3 1
4 1
5 1
6 2
10 2
11 3

Excelで複数行のテキストを1つのセルに集約

何がしたいかというと,バラバラのセルに書かれている自由記述アンケートの結果を1つのセルにまとめたい。具体的には,以下のようなアンケート結果があって,

表1:アンケート結果

  A B C
1 Class_ID Instructor_Name comments
2 1 A 良いです。
3 1 A とても良いです。
4 2 B まぁまぁ
5 2 B ダメじゃん
20 10 J 良い!

別のシートに表2のように整理したい。

  A B C
1 Class_ID Instructor_name Comments
2 1 A

良いです。

とても良いです。

3 2 B

まぁまぁ。

ダメじゃん

10 10 J 良い!

表2のC1で,以下の関数を使う。

=TEXTJOIN(CHAR(10),TRUE,IF(‘表1′!A:A=A2,’表1’!C:C,””))

TEXTJOIN(区切り文字,TRUE(空のセルは無視), if(表1のA:Aに,A2にあるClass_IDがあれば,表1のC:Cから書いてあることを取ってきて,区切り文字で分けつつ,結合する), Class_IDなければ,なにもしない)

CHAR(10)は改行を区切り文字とする関数

Excelでクロス表から成績を決める

タイトルが良く分からない感じになりましたが,以下のように,クロス表で成績を決める場合にどうするかという話。例えば,5段階評価で,横方向と縦方向で評価の観点が異なり,それぞれの合算で成績をつけたい場合を想定しています。一番上の行(A-F)と一番右の列(1-6)は,エクセルの外枠にある記号と思ってください。

54321
555443
454432
343332
232221
111111

こちらのサイトの記事「【エクセル時短】縦横で交わるデータを取り出す!「クロス抽出」を実現する関数の組み合わせとは?」を参考というか,ほとんどそのまま使わせて頂いています。イメージとしては,以下のようになれば良い。エクセルのシートに,以下のように,A~Dは成績処理,F~Lは上のクロス表が配置されているとする。

ABCDEFGHIKL
1ID 観点1(縦) 観点2(横) 成績(縦と横) 54321
2A01555555443
3A02545454432
4A03323343332
5A04141232221
6A05513111111

上の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の完全に一致))となる

Excelである範囲から適当に単語などを持ってくる

分かりにくいタイトルですが,やりたいことは,

1)Sheet1に問題文として「I have a (  ).」がある.

2)Sheet2のA1~A90に単語が適当においてあるので,A1~A10で1つ,A11~A20から1つ…と言うように,A1~A90から9つの単語を選びたい。

3)正解の単語は,Sheet1のA2に”pen”として入れてあり,A3~A12までに選んだ単語を入れる。

【方法】

・A3に以下を入れる。

=INDIRECT(“Sheet2!A”&INT(RAND()/0.1)+1)

  • INDIRECTは,セルの参照を返す
  • INTは整数を返す
  • RAND()で乱数を発生させて,0.1で割ると0~10の乱数が発生する。0だと困るので,+1をして,必ず1以上が出るようにする。

・A4には以下を入れる(注:10*RAND()の部分は,RAND()/0.1と同じ意味になる)

=INDIRECT(“Sheet!A”&INT(10+(10*RAND())+1)

・A5は20,A6は30を入れていけばよい。


7月5日追記

INTを使うと小数点以下が切り捨てられてしまうので,場合によっては,最大値が出ないことになるので,例えば,round関数を使って,小数点第1位を四捨五入すると良いかもしれない。

=INDIRECT(“選択肢プール!A”&ROUND(11+10*(RAND()),0))

とする

 

Excelを用いてBb9にテストをアップする

B9では様々な形式のテスト実施が可能であるが,テスト項目が多い場合にはオンラインで作業するよりも,オフラインで作業をして,テスト用のファイルを作り,それをアップロードするほうが手間がかからない。

アップロードできるテスト形式

以下のテスト形式でアップロードすることができる。(参考:広島大学ICT活用教育研究会 2013年度第3回Webテストの活用 隅谷孝洋 「Bb9におけるテスト機能」)

テスト形式 使用する省略形
選択問題 (択一) MC
選択問題(複数回答可) MA
短文問題 FIB
作文問題 ESS
穴埋め問題 FIB_PLUS
正誤問題 TF
文章完成問題 JUMBLED_SENTENCE
並べ替え問題 ORD
組み合わせ問題 MAT
数値問題 NUM
質問文作成問題  QUIZ_BOWL
ファイル提出問題  FIL
評価/リッカート問題  OP

注)二択問題,数式計算問題,画像の座標指定問題はアップロードでの作成はできない。

アップロードするエクセルファイルのフォーマット

ここでは,英語の授業でよく使う,選択問題(択一),作文問題,穴埋め問題,並べ替え問題,組み合わせ問題についてのみ,そのフォーマットを示す。

選択問題(択一):

フォーマット:MC | 質問文 | 解答 | correct or incorrect | 解答 | correct or incorrect | ….
例は単語テスト(日本語を与え,英語を選ぶ)
例:MC | information | 情報 | correct | 伝統 | incorrect | 差分 | incorrect | ….

作文問題:

フォーマット:ESS | 質問文 | 解答例 (なくても良い)|
例:ESS | 「今日は良い天気」を英語にしなさい | It’s a fine day today.|

穴埋め問題:

フォーマット:FIB_PLUS | 質問文 | 変数1 | 解答1| 解答2 | 変数2 | 解答1| 解答2 | ….
例:FIB_PLUS | It’s a [1] day [2]. | 1 | fine | good| 2 | today |

並べ替え問題:

フォーマット:ORD | 質問文 | 解答文 | 解答文 | 解答文 | 解答文 |…
例:FIB_PLUS | 「今日は良い天気 」| It’s | a | fine | day | today |
*解答文は正解となるように並べる。順序はランダムにBb9が並べ替える。

組み合わせ問題:

フォーマット:MAT | 質問文 | 解答文 | 一致する文 | 解答文 | 一致する文 |…
例:MAT | 英単語に合致する日本語を選びなさい| fine | 良い | day | 日 | …
*解答文と一致する文は1対1にする。順序はランダムにBb9が並べ替える。

エクセルファイルの保存

  1. 念のため,エクセルファイルのまま保存(あとで修正しやすい)
  2. 「名前を付けて保存」から「Unicodeテキスト(*txt)」で保存

ファイルのBb9へのアップロード

  1. 出題したい授業のコントロールパネルないのコースツールからテスト/アンケート/プールを選択
  2. 「テスト」をクリックし,「テストの作成」をクリック後,テストの名前や説明などを必要に応じて入力し,「送信
  3. 質問のアップロード」を選び,「参照」で作成した問題ファイルを選択質問ごとの点数を入力し「送信」。
  4. 問題の確認や質問ごとの点数を確認する。問題なければ「OK」。

Bb9での出題

  1. 教材」から「テスト/アンケート/課題」の「テスト」を選ぶ
  2. 「既存のテストの追加」から作成したテストを選び,「送信
  3. テストの実施条件などについての設定を行い,「送信

Excelで授業ごとに資料を印刷する

Excelで授業担当者ごとに渡したい資料がある場合,担当授業者で並べ替えて,ページ区切りをいれていけばよい。ただし,1人の授業担当者が複数の授業を担当している可能性があるので,授業コードで並べ替える必要がある。なので,

  1. 「授業担当者」
  2. 「授業コード」
  3. 受講生の学生番号

で並べ替えておく。

授業コードの区切りでページ区切りを入れていけばよいのだが,VBAは使いたくない。でも,多少なりとも手間を省きたい場合には,

  1. 「データ」ないの「並べ替えとフィルター」にある「詳細設定」をクリック
  2. 「リスト範囲」に授業コードの列を指定
  3. 「重複するレコードは無視する」にチェックを入れる

ここまでで,授業コード毎に1行ずつが表示されているので,

  1. 「ページレイアウト」から「改ページ」をクリック
  2. 「改ページの挿入」でページ区切りをいれる
  3. 「改ページの挿入」の繰り返しなので,(↓キーをおして)1つ下のセルに移動
  4. 「F4」を押して改ページを入れるを繰り返す

確認のため,

  1. 「データ」ないの「並べ替えとフィルター」にある「詳細設定」をクリック
  2. 「重複するレコードは無視する」にチェックを外す
  3. 「表示」から「改ページプレビュー」

で作業完了。