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を返す。

スクリーンの半分をパワポ,半分を自分を出す

Atem Mini Proを持っているので,パワーポイントを映しながら,自分の顔を出すこともできる。しかし,Atem Mini Proとか使わなくてもできそう…と思っていたら,それを察したAtem Mini Proが不調になり,結局,検索してやる羽目になった。OBSも考えたけど,今回はありもののアプリでできるならそれに越したことはないということで。

参考にしたのは,How to present your video and content side by side in a Microsoft Teams meetingと言うサイト。単純にいうと,スクリーンの半分にスライドショーを,もう半分にカメラアプリで自分を写すというもの。参考にしたサイトの手順でほぼできたのだが,パワポの設定で1つだけ追記があるので,その点を含めて紹介。

  1. パワポの「スライドショーの設定」で「出席者として閲覧する(ウィンドウ表示)」を選んでおく。こうしないと,パワポのスライドショーがフルスクリーンになってしまうので,画面に半分だけ写すことができない。
  2. スライドショーを実行して,スクリーンの半分程度にしておく
  3. Windowsであれば,カメラアプリを起動する。右下の検索ウインドウからカメラを探すとすぐに出てくる。
  4. カメラが起動したらモードを動画にしておくと良い。静止画のままだと,顔認識の枠が出てくる。
  5. カメラの画面をスクリーンの残り半分にする。
  6. ZoomやMS Teamsでこのパワポとカメラの画面を共有する。

注意

  • パワポの文字などの大きさの関係で,必ずしも画面を半分にする必要はないが,うまくやらないとデスクトップが映るので,デスクトップのアイコンなどは整理(パワポのスライドショーの裏に寄せる)
  • 標準のカメラアプリは背景設定はできないので,自分の後ろが丸見えになる
  • ZoomやMS Teamsでカメラをオンにしてから,アプリのカメラをオンにすることはできない。逆も然りで,アプリのカメラがオンの状態で,ZoomやMS Teamsのカメラをオンにしようとすると警告が出る。

Nicklin, C., & Vitta, J. P. (2021)

Nicklin, C., & Vitta, J. P. (2021). Effect‐Driven Sample Sizes in Second Language Instructed Vocabulary Acquisition Research. The Modern Language Journal, 10.1111/modl.12692

本研究では、指導による第二言語語彙獲得(L2 IVA)の研究81件を2つの段階に分けて分析した。第1段階では、研究の効果量を分類し、コード化した。基本的な被験者間デザインと被験者内デザインの二分法では、観察された効果の不均一性を捉える感度が不足していたため、より詳細なアプローチを採用した。被験者間デザインと被験者内デザインの両方において、指導と比較の対照は、L2 IVA実験で最も関心の高い比較を最もよく表しており、効果量(g)の中央値は0.62(被験者間)と0.25(カウンターバランスを取った被験者内)であった。第II段階では、第I段階で観察された効果量の集計値を、一般的なL2 IVA分析のおおよそのサンプルサイズを示すために、事前に行うパワー分析のシミュレーションに利用した。保守的なパワーを与えた被験者間デザインでは、シミュレーションでは292~492人のサンプルサイズが提案された。カウンターバランスを取った被験者内デザインでは、反復測定の間に想定される相関関係に応じて、95~203人の被験者が必要であった。これらのシミュレーションの包括的な意味合いは、将来のL2 IVA実験では、先行研究の効果量を参照して,より大きなサンプルが必要であることを示唆している。本研究では,より大きなサンプルを得るという問題に対して3つの可能性のある解決策を提示している。

オンライン授業の記事3部作

コロナ禍に翻弄された2020年度は,たまたまお声がけいただいて,大修館の『英語教育』にオンライン授業関連の記事を3回寄稿させていただいた。大修館の担当者から,「3部作ってことで」と言われ,書いてみたものの,どこが3部作なのか…。さておき,2020年の振り返りもしていなかったので,3部作(笑)で何を書いたのかを振り返って,去年の振り返りにしてしまおう。

6月号(発売は5月)の英語教育では,「【緊急特別記事】オンライン教材を利用した英語授業で気を付けたいこと」として,オンライン授業をする際の留意点を5点ほど紹介した。記事を書いているのは4月上旬の時点で,勤務校でオンライン授業を行うことが決まった直後であり,さて,どうしようと考えていたところであった(勤務校でのオンライン授業への方針決定などは,こちらの記事を参照)。紹介した留意点は,

  1. 自学自習で済ませてはいけない
  2. オンライン教材の入り口は1つに
  3. 学習時間ではなくて,学習成果で判断
  4. コントロールできないものはしない
  5. 手を変え,品を変え,飽きさせないように

という5点で,バタバタした中で書いたにしては,まともなことを書いている(ような気がする)。実質,数日で書いて,寝かせて,即校了だった記憶がある。

これらの留意点のいくつかを広げたのが,第2作と第3作なっているので,オンライン授業元年の基本方針になりうるものを含んでいたのだと今思ったりする。

第2弾は,夏の終わりの別冊『英語教育2020年10月別冊 英語教師のためのオンライン授業・動画配信ガイド』に寄稿させていただいものである。タイトルは「オンライン授業におけるフィードバックの工夫:広島大学における実践例」としてあり,主に,6月号で述べた留意点の1に関するものである。記事を書いているのは,7月なかばくらいなので,前期の終わりが見え始めた頃だった。実践報告を書くつもりが,フィードバックに重心を置いてほしいということだったので,依頼を受けてから方向転換をした記憶がある。自分としても,ライブ型授業であれ,オンデマンド型授業であれ,いずれにせよフィードバックをどうしようかと悩んでいたので,実践報告として書きながら自分の頭を整理することができた。ライブ型授業とオンデマンド型授業で分けてフィードバックの具体的な方法を書いたが,細かいTipsのようなところで,意外と役に立ったところがあったようで,安心した記憶がある。

第3弾は,2月号(1月発売)で,「オンデマンド型授業における成績評価の工夫」という記事。6月号の3と4の留意点を振り返りつつ,別冊の内容も含んでいる。評価というのは,対面式授業でも困るのに,オンラインだと尚困るということで,試行錯誤を記事にしたというもの。特に大学でのオンデマンド授業を想定しているので,成績処理と出欠のあり方をメインにした。私の記事の前後にもオンライン授業での評価についての記事があり,どちらも,ちゃんと形成的評価という用語を使って,詳しく説明をされている。大変に為になったと同時に,「あ,形成的評価とか,総括的評価とか使えばよかった」と反省した。

そんなこんなで,オンライン授業の記事を3つ書いて,それで終わったような2020年だったけれど,それだけ,オンライン授業でお困りの先生方が多くいたということでしょう。何せ,オンライン授業の専門家でもない私に寄稿依頼が来るくらいですから。たまたま,勤務校でオンライン授業を以前から進めていたので,試行錯誤で学んでいたところを共有することができたのですね。今となっては,あ当たり前になったことを書き散らした感もありますが,どこかでお困りの先生のお役に立てたなら幸いです。

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

Uchihara, T., Webb, S., & Yanagisawa, A. (2019)

Uchihara, T., Webb, S., & Yanagisawa, A. (2019). The Effects of Repetition on Incidental Vocabulary Learning: A Meta-Analysis of Correlational Studies. Language Learning, 69(3), 559–599. https://doi.org/10.1111/lang.12343

このメタ分析の目的は、遭遇回数と語彙学習との間の相関係数を報告した主要研究をメタ分析することで、反復と第二言語(L2)の付随的語彙学習との間の複雑な関係を明らかにすることである。26の研究(N = 1,918)から45の効果量を合成して定量的に分析し、頻度と学習の関係の平均効果量を算出し、この研究で注目した10の変数がこの関係をどの程媒介するかを調べた。その結果、反復学習には中程度の効果(r =.34)があることが示された。その後の媒介分析の結果、反復効果の大きさのばらつきは、学習者変数(年齢、語彙知識)、処遇変数(間隔をおいた学習、視覚的サポート、エンゲージメント、遭遇回数の範囲)、方法論の違い(非単語使用、理解度テストの予告、語彙テストの形式)によって説明されることが明らかになった。これらの結果をもとに、今後のL2付随語彙学習研究の方向性を示唆する。

Webcam Setting

WebカメラやPC内蔵のカメラなどの設定を行えるソフト。

研究室のPCは窓に向いているので,どうしても背景が明るく,自分の顔が逆行気味になるので,暗くなってしまう。それを解消するために導入。

  1. こちらからダウンロードをして,(デスクトップなど)適当なところに解凍
  2. WebCamSetting.exeをダブルクリック
  3. PCにカメラとして認識されているカメラの一覧が出てくるので,そのCamName以下のカメラ名をダブルクリック
  4. 設定プロパティが出るので,調整(Zoomなどで画面を見ながら調整ができる)

Soxで2回リピートの音源を作る

英語単語を読み上げている音声ファイルを作り,それを1単語ずつに切り分ける方法は,以前の記事で取り上げた。このファイルは1回の読み上げなので,2回の読み上げをしたい。以下の手順について忘備録として書いておく。

  1. Soxの準備
  2. 2回リピートのsoxコマンド

Soxの準備

Soxの準備については,こちらのページで詳細を説明してくれているので,参照のこと。

WIndows10への導入の概要としては以下の通り

  • Soxのダウンロード(こちらが配布サイト。中ほどのDownloadからSourceforge’s File Release System.に進み,ダウンロード(今回は,Sox/14.4.2/sox-14.4.2-win32.exe)
  • インストール。exeファイルをダブルクリックで自動的にインストール。デフォルトでは,C:¥Program Files (x86)¥sox-14-4-2に入る。
  • このままだと,mp3は使えないので,app.box.comからlibmad-0.dll_and_libmp3lame-0.dll.7zipをダウンロードして,中身をsoxのファイルに入れておく。
  • Pathを通す(上記の説明サイトを参照)か,soxを入れたフォルダで作業する。

2回リピートのコマンド

Soxのコマンドについては,こちらのサイトを参照。

今回は,以下のコマンドで良い。

sox 入力ファイル(対象としたいファイル) 出力ファイル(書きだしたいファイル名)repeat 1

*一瞬ハマったのは,リピートを2回させたいので,最後を2にしたら,元の音声+リピート2回で3回読み上げられた。

ファイルが大量にあるので,コマンドプロンプトでforで処理。出力ファイルは,同じフォルダにtwiceというフォルダを作り,そこに入れる。

for %f in (*.mp3) do (sox %f ./twice/%f repeat 1)

Audacityで大量の音声ファイルの切り分け

Globalvoice Englishで100単語を含む音声ファイル(.wav)を1つ作成し,それをAudacityで100の音声ファイル(.mp3)にしたときのAudacityの設定の話。

  1. 「編集」-「設定」ー「取り込み/書き出し」にある「メタデータエディタを書きだしの前に表示(h)」の前のチェックを外す。(これをしないと,のちにメタデータエディタで100連打することになる)
  2. Audacityへ音声ファイルを置く(ドラッグアンドドロップで良い)
  3. 「解析」-「Silence Finder」で出てきた画面でとりあえず,OK
  4. 「ファイル」-「複数ファイルの書きだし」
  5. 「書きだし場所」で任意のフォルダを選ぶ
  6. 「ラベル」の下にある,「最初のラベル以前のオーディオデータを含む」の前のチェックを入れる。(これをしないと,1つ目の単語が飛ばさせる)
  7. 「ファイルの命名」で「ラベル/トラック名の前の番号付加」を選び「書きだし」

これで100個の音声ファイルができる。あとは,バッチファイルなんかで,ファイル名を一括変更すればよい。

Microsoft Streamで動画の限定公開

大学の公式LMSであるBb9が大量の動画アップロードとその視聴のために授業日になると非常に重くなったり、動画が見られないと言うことがある(2020年4月21日現在)。そこで、広島大学が契約しているMicrosoftから、Streamと言う動画配信サービスを使って必要な動画を配信する。しかし、デフォルトのままで使うと、全公開か、会社(=広大)の中での公開となってしまい、ちょっ恥ずかしい。そこで、Microsoft Teamsでチームを作って、そこに属する人々だけが見られるようにする。

手順としては、以下の通り。

  1. Microsoft Teamsで授業チームを作る
  2. 学生を貼り付ける(これCSVとかでできないかな。今は1人ずつ入力)
  3. 1日くらい待つ
  4. Streamのマイ・コンテンツからグループを選び、作成した授業グループ名があるのを確認する
  5. Steamに動画を上げる
  6. マイ・ビデオの画面から、授業グループに見せたい動画の横にある「チャンネル/グループに追加」を選ぶ
  7. グループの検索から、授業グループ名を検索し、表示にチェックをいれる。
1 3 4 5 6 7 13