Reading Progress in Microsoft Teams：Excel

【´･ω･`】：こんなリクエストがあった。

 

************************************************************************************

Microsoft Teams というサービスを教育活動で使うようになりました。

その機能の一つに、「音声の練習」というものがあります。
音読すると、AIが評価します。
https://www.youtube.com/watch?v=NilBP_YwXDw

その評価の集計を「Excelにエクスポート」できます。

私たちが使っている生徒名簿に、集計結果を転記したいのです。

************************************************************************************

 

【´･ω･`】：「Excelにエクスポート」した表は下のようなカンジだ。

 

 

 

【´･ω･`】：C列（メールアドレス）が学習者、D列が課題名、H列が評価を表している。

（●｀ε´●）：どこが問題なんだ？

【´･ω･`】：C列（メールアドレス）の重複を無くして、課題名ごとに評価を振り分けたいという事らしい。

（●｀ε´●）：下のような表を作りたいということか？

【´･ω･`】：そう。　　　　

 

 

（●｀ε´●）：瞬殺だな。

【´･ω･`】：

うん、　集計ファイル(main.xlsx)のG2に下記の数式を入力し、必要な範囲にコピペすればいい。

=SUMPRODUCT(($F2='[data.xlsx]Reading Progress'!$C$2:'[data.xlsx]Reading Progress'!$C$2:$C$113)*(G$1='[data.xlsx]Reading Progress'!$D$2:$D$113)*('[data.xlsx]Reading Progress'!$H$2:$H$113))

【蛇足】数式の意味

=SUMPRODUCT((main.xlsxのメールアドレス=data.xlsxのメールアドレス)*(main.xlsxの課題名=data.xlsxの課題名)*(評価値範囲))

A＝Bが真ならば1，偽ならば0になる。

従がってmain.xlsxのメールアドレス=data.xlsxのメールアドレスとmain.xlsxの課題名=data.xlsxの課題名が共に成立する時

=(main.xlsxのメールアドレス=data.xlsxのメールアドレス)*(main.xlsxの課題名=data.xlsxの課題名)

=1*1

・・・となる。

しかし、main.xlsxのメールアドレス=data.xlsxのメールアドレスとmain.xlsxの課題名=data.xlsxの課題名が共に成立するのは

評価値範囲では一つしかない。

残りの＝(main.xlsxのメールアドレス=data.xlsxのメールアドレス)*(main.xlsxの課題名=data.xlsxの課題名)は

=0*0

=0*1

=1*0

・・・のどれかになるのだ。

よって・・・

 

=SUMPRODUCT((main.xlsxのメールアドレス=data.xlsxのメールアドレス)*(main.xlsxの課題名=data.xlsxの課題名)*(評価値範囲))

＝0+0+0+･･･+1*1*評価値

＝評価値

 

・・・となる。

 

 

参考ファイル： Mainをダウンロード（main.xlsx 集計用）

　　　　　　　 Dataをダウンロード（data.xlsx Reading Progress in Microsoft Teamsよりエクスポート）

 

使用上の注意：main.xlsxとdata.xlsxは同じフォルダにいれて使ってください。

 

★改訂版を公開しました。（2022/05/12）

Excel Maniacs: Reading Progress in Microsoft をExcelで集計 改訂版 (typepad.jp)

Comments

[投稿情報: SQS]

「瞬殺」の関数に、瞬殺されました(@_@)
気絶しそう。

解読に何時間かかるかしら。。。

楽しいです！うれしいです！！

わたしのエクセルの神様、くまぷー先生。
崇めます〜m(_ _)m

勉強します〜〜

[投稿情報: くまぷー]

＞わたしのエクセルの神様、くまぷー先生。
＞崇めます〜m(_ _)m

照れます。(^^ゞ
「関数で出来ますか～？」に比べれば子供だましです。

・・・質問です。

1.マイクロソフトのReading Progressを取り入れている学校は多いのですか？

2.Reading Progressから出力されるエクセルファイルは全てあの形式になるのですか？

[投稿情報: SQS]

ありがとうございました！できました！！

二つのファイルだとうまく行かなかったので、くまぷー先生のファイルにdataタブを作って、Reading Progressのデータを「貼り付ければいいだけ」にしました(^^;)

もう少ししたら、本番になります。不具合があったら・・・助けてください(T_T)

1 Reading Progressを取り入れている学校はまだ多くはないと思います。たまたまうちの県がMicrosoftとの契約して、そこについていたサービスだと思います。どこも使っていると聞いたことはありません。もちろん研修もありません。Googleの県もあるから、そういうところは使っていないと思います。

2 Reading Progressから出力されるエクセルファイルはあの形式ですが、もっとタブがあって、いろいろな解析がなされています。送りますが、ちょっと時間をください。いま、ちょっと仕事がどどっときました(T_T)

[投稿情報: くまぷー]

＞二つのファイルだとうまく行かなかったので

どうしたんでｓかね？

リンクを張っているので・・・

＞使用上の注意：main.xlsxとdata.xlsxは同じフォルダにいれて使ってください。

あと、Data.xlsxのファイル名、シート名を変えるのは不可です。

質問の回答、有難うございます。

あと、追加の質問です。
ExcelのVersionは何ですか？スピルが使えるタイプですか？

＞もう少ししたら、本番になります。不具合があったら・・・助けてください(T_T)

了解　(''◇'')ゞ

[投稿情報: SQS]

くまぷー先生

Reading Progressのdata について質問です。

くまぷー先生にお尋ねしたとき、dataの範囲が、$H$2:＄H$113　でした。

mainの関数は、

=SUMPRODUCT((F2='[data.xlsx]Reading Progress'!$C$2:'[data.xlsx]Reading Progress'!$C$2:$C$113)*(H$1='[data.xlsx]Reading Progress'!$D$2:$D$113)*('[data.xlsx]Reading Progress'!$H$2:$H$113))

でしたが、生徒の提出が増えて、$H295 にデータが増えていました。

だから、

=SUMPRODUCT((F2='[data.xlsx]Reading Progress'!$C$2:'[data.xlsx]Reading Progress'!$C$2:$C$295)*(H$1='[data.xlsx]Reading Progress'!$D$2:$D$295)*('[data.xlsx]Reading Progress'!$H$2:$H$295))

と、書き替えました。

計算できました。

これから、どんどんデータが増えていきます。
自動的に、dataの末尾が取れる関数はないでしょうか(；一_一)

[投稿情報: くまぷー]

＞動的に、dataの末尾が取れる関数はないでしょうか(；一_一)

=COUNTA(C:C)

・・・でC列の最終行番号取得。

ただし、C列に空行なしの場合。

=INDIRECT("C" & COUNTA(C:C))

でC列の最終行の内容を取得できます。

[投稿情報: SQS]

ありがとうございます！　Indirectの関数確認しました。

今、上のmain の　sumproduct関数に組み込もうとコピペ、トライ中です。

dataファイルにindirect関数で値をとって、mainに引用すればいいと思いましたが、dataは更新されるので、

できれば
フォルダに、先生の指示通り、dataとmainが入れば、処理してくれるようにしたいです(^_^)