2023年5月25日木曜日

数式を表示する -数学・物理系の教員による Google blogger の活用法-

 数学の授業をしていて、この Google blogger の活用方法で思いついたのが、 Blog を使っての課題解説である。もちろん、数学ではない他の教科でも使えるとは思うが、数学で使うにはどうしたらいいかと考えまして・・・


数式を表すと言ったらもちろん、 $\TeX$ を使うか、更に強化した $\LaTeX$ を使う、なんてことを思いつくのですが、そんなことが可能なのだろうか・・・


なんて考えるところなのですが、結論から言うと、使えるようになります。既に $\TeX$ とか $\LaTeX$ とか表示しているので、それは分かっていると思いますが・・・


細かい説明は省きますが、このブログにある手順でヘッダの編集画面まで移動し、 <head> と </head> の間にこの英語ブログにあるスクリプト(背景が水色になっているところ全部)をコピペすれば OK です。


OK なのですが、1点だけ注意が必要になります。ブログの“テーマ”を変更すると、このヘッダの部分が更新されてしまい、スクリプトがなくなってしまいます。なので、テーマを変更する度にスクリプトをコピペする必要があることになります。


あとは通常の $\TeX$ と同様に $\$$y=ax^2+bx+c$\$$ と入力すれば $y=ax^2+bx+c$ を出力されるので、 $\LaTeX$ で文中に数式を入れる感覚で作業ができるようになり、$\LaTeX$ ユーザとしては、非常に使いやすい環境になるわけです。


ただ、あくまでも $\$$ と $\$$ で挟まれた部分だけを数式として出力するものなので、独立させて数式だけを表示させるためには、$\yen[ ~~~ \yen]$ 

\[y=ax^2+bx+c\] 

だったり、 align* 環境

\begin{align*}y&=ax^2+bx+c\end{align*} 

も使えるようです。ただし、式変形をしようとして align* 環境で複数行を書こうとすると何故かエラーが出てしまうので、仕方がないので $\$$ と $\$$ で挟んだ数式を並べて書くようにしています。

$y=ax^2+bx+c$ 

$= a\left(x+\frac{b}{2a}\right)^2-\frac{b^2-4ac}{4a}$ 


また、 $\LaTeX$ ではコマンドベースでグラフを描くことができますが、それには大抵の場合、パッケージを使っていると思うので、それはここでは使えません。

ですが、逆に HTML ビューにしてから desmos のグラフを埋め込む、なんてことが可能になります。

こんな風に、グラフを動かすことだって可能になる。

(上の例は、 Reuleaux の三角形の等幅性を可視化したも)


今回は数学・物理なんかを前提に $\LaTeX$ で数式を記述する方法を紹介した。他の教科の立場で考えて、

  • 国語のように縦書きはできないか
  • 音楽で使えるように楽譜を入力できないか

なんてことを考えたのですが、楽譜は使えそうなサービスを見つけましたが、縦書きは・・・竹取 JSというものを使うと指定した部分を縦書きにすることが出来るようでした。私はまだ興味が湧いてこないので、それ以上は調べていませんが。

社会科の場合、各種資料は画像の挿入でなんとかなりそうですし、YouTube に上げた動画を埋め込むこともできますし、色々な教科・科目で blogger が使えそうな気がしますね。

2023年5月14日日曜日

Google Forms から名簿への出力 -未回答者を一網打尽に-

希望調査をとるときに、 Google Forms はとても便利である。対象者全員から希望をとるのに、大量の紙を印刷したり、それを人力で集計したりという手間が省ける。

本校で使っているシステムの中では、スタディサプリにもアンケート機能があるのだが、これはいまいち使い勝手がよくない(個人の感想)。自動で集計してくれるものの、全校生徒に一斉に配信するな、って言われてるし、配信したらしたで、未回答の生徒を探すのがちょっとめんどくさい。だったらクラス単位で配信すればいいのだろうが、それも設定するのがめんどくさかったりする。


そんなわけで、Google 教の信者(?)である私は、 Google Forms と Google Spreadsheet の関数でなんとかしてしまおう、という発想にたどり着くわけです。


で、実際にやってみましょう。


まずは、適当にフォームを作成しました。実際に回答できるようになっていますので、適当に回答してみてもらっても大丈夫です。

適当に回答した結果が、Spreadsheet に出力されるのが集計しやすくていいです。スタディサプリはその時点での回答状況を MS Excel ファイルに出力するだけなので、リアルタイムでの集計には不向きです。


で、回答されると、こちらのファイルに出力されます。先程の Forms と同様に、こちらも編集できるようになっていますので、適当に回答を削除して、前述の Forms から回答をしてみて、どのように変化するかを確かめてもらって大丈夫です。

この出力されたファイルから、(本来はログインしているメールアドレスで個人を確認して)個人毎の集計をとります。その結果がこちらのファイルになります。これで回答を集め、更に未回答者も一目で分かる状態になりました。


ところが、回答をしているのが生徒だからか、深く考えずに回答しているのか、後から回答を変えたい、と言ってくる場合があります。その場合、再度回答したものが反映されればいいのですが、 「vlookup」という関数は一致するものを「上から」順に探すものであり、 Google Forms の回答はどんどん「下に」追加されていきます。なので、単純に考えれば、昔の回答を削除すればいいのですが、再回答したことを知らなければ、そのまま古い回答が表示されたままになってしまい・・・


それを”関数のみで”解決する方法があります。

それは、「sort」関数を使って、回答を逆順に並べ替えたデータから探すというもの。実際に作ってみたデータはこちら


このファイルの解説をしていくと・・・

まずは「シート3」に、「importrange」で回答を引っ張ってきている。同じファイルの中で作業をするのであれば、これは必要ない。

次に「シート2」で、逆順に並べ替えている。

=sort('シート3'!A:C,row('シート3'!A:A),false)

最初の「'シート3'!A:C」は、並べ替える範囲を定義している。

「row」は、行番号を返す関数であるので、「'シート3'!A:A」のそれぞれのせるの行番号を表している。

最後の「false」は「降順」を表している。

つまり、「'シート3'!A:C」を「'シート3'!A:A」の行番号が大きい順に並べろ、という指示をしている。行番号はもちろん上から小さい順に並んでいるので、大きい順に並べたら最初と逆順になるのである。


こうすることで、Forms で回答される度に上に回答が増えていくので、あとは通常通りに「vlookup」で拾うことで最新の回答に上書きされる、という仕組みである。

2023年5月8日月曜日

関数のコピペを劇的に減らす関数、arrayformula

Spreadsheet を使っていて、同じ関数をコピペして使うことがよくある。


例えば、 A 列に年組番を入力し、 B 列にその生徒の姓を表示したいときがある。

セル A2 に「1LIA1」と入力したときに、セル B2 に

=vlookup(A2,importrange("元データの ID","データの範囲"),"データ範囲の中での「姓」の列番号",false)

とすることで、元データから 1LIA1 の生徒の姓を拾ってくることができる。この「姓」の列番号を探すのが面倒なときは、

=vlookup(A2,importrange("元データの ID","データの範囲"),match(B1,importrange("元データの ID","\$A\$1:\$1",0),false)

とすることで対応できる。

C 列にその生徒の名を表示したい場合、このままコピペをすると、恐らくエラーが発生する。参照の方法が、相対参照になっている為、そのままコピペをしたときは最初の参照するデータがセル B2 になってしまうのである。


これを防ぐ為に、相対参照にする必要がある。

例えば、前述の例で言うと、右にコピペをするときに、相対参照だとその参照元も右に移動し、絶対参照だとその参照元は動かない、という違いになります。

なので、「年組番」が入力されている A2 の部分は右には動かないで欲しい、ということになるので、右方向はアルファベットで表記しているので A を固定する為に、「\$A2」と変更します。この「\$」が、絶対参照の意味になります。

逆に、「match」の中の B1 は右にズレたときはズレてほしいので「\$」は付けない。


更に下にコピペをするときを考えると、 \$A2 のところは縦には変化して欲しいので \$A\$2 としてはいけない。逆に B1 のところは下に移動してはいけないので、 B\$1 とする必要がある。


よって、後々に右方向、下方向とコピペをすることを考えると、 B2 に入力する式は

=vlookup(\$A2,importrange("元データの ID","データの範囲"),match(B\$1,importrange("元データの ID","\$A\$1:\$1",0),false) 

とするのが良いだろう。



で、ここからが本題。タイトルにもある「arrayformula」の使い方について。

「array」は「配列」、「formula」は「公式」などと訳される。つまり、公式を定義して、それを入れず全体で対応させる、というようなイメージであろうか。

例えば、20 以下の正の整数同士の掛け算の表(小学校で学ぶ九九の拡張)を作る。

この Spreadsheet を見ながら読むと分かりやすいかと。


(シート1)まずは「掛けられる数」と「掛ける数」を並べる。

セル B2 には「=A2*B1」と入力されている。

この数式から、「arrayformula」を使って、一度に表を完成させる。


(シート2)先程の数式を、「arrayformula」で囲う。

セル B2 「=arrayformula(A2*B1)」

これだけでは何も変化がない。


(シート3)この掛け算の、A2 の部分を A2 から A21 までの範囲で行いたいので、

セル B2 「=arrayformula(A2:A21*B1)」

とすると、掛け算の結果が縦に並ぶ。


(シート4)同様に、横方向にも広げたいので、

セル B2「=arrayformula(A2:A21*B1:U1)」

とすると、縦にも横にも広がり、掛け算の表が完成する。


なので、恒例の総理大臣の一覧から、新しい名簿を作成する。

入力してある数式はセル B2 のみで、

=arrayformula(vlookup(A2:A20,importrange("https://docs.google.com/spreadsheets/d/19IOXpCTj3yxb-O-1ldFB_3rrsZKr6l0se8GD_pYQa90/edit#gid=0","$A:$D"),match(B1:D1,importrange("https://docs.google.com/spreadsheets/d/19IOXpCTj3yxb-O-1ldFB_3rrsZKr6l0se8GD_pYQa90/edit#gid=0","\$A1:\$D1"),0),false))

のみである。

ただ、「代」のところに「37」と入力しようとしたが、間違えて「378」と入力されている。すると、「vlookup」は元データの中の A 列には 378 が見つからない為、エラーが返ってきている。

この場合は直せばいいのだが、例えば複数のクラスで使うデータの場合、人数の多いクラスに合わせてシートを作成し、クラス名だけを変更して使う、なんてことがよくある。このときに、人数の少ないクラスの余ったところは「#N/A」という謎のエラーではなく空白になって欲しい。


そこで使うのが、「iferror」関数である。

「=iferror(数式等,"エラー時の返り値")」で数式を確認し、エラーになる場合は"エラー時の返り値"を返し、エラーでない場合は数式の結果を返すものである。なので、

=arrayformula(iferror(vlookup(A2:A20,importrange("https://docs.google.com/spreadsheets/d/19IOXpCTj3yxb-O-1ldFB_3rrsZKr6l0se8GD_pYQa90/edit#gid=0","$A:$D"),match(B1:D1,importrange("https://docs.google.com/spreadsheets/d/19IOXpCTj3yxb-O-1ldFB_3rrsZKr6l0se8GD_pYQa90/edit#gid=0","\$A1:\$D1"),0),false),""))

とすると、人がいないところは空白にすることができる。



「arrayformula」を使えるようになると、コピペをしなくて済むし、数式を変更する場合も1箇所直すだけで済むだけでなく、数式処理も軽くなるようである。

2023年5月1日月曜日

IMPORTRANGE -Spreadsheet が MS Excel との差別化に成功している最大の点-

 私の考える MS Excel と Google Spreadsheet の一番の違いは、ローカル(自分の PC 上)で処理をするか、サーバ上で処理をするかである。


個人的には Google Spreadsheet の方が使い勝手が良いと思っている。その根拠はと言うと、ある程度の能力があれば、ハイスペックな PC は必要ない、ということも挙げられるが、それ以上に利点になってくるのは、他のファイルからの引用が容易に出来る、ということである。


例えば、全体の名簿は共有されているが、自分には編集権限がない、なんてことがある。そんなときは、その名簿をコピーすれば自分で自由に使えるようになるのだが、名簿が更新されるようなものだったりすると、その度にコピペしないといけないし、それ以上に更新されたことに気が付かずに古いデータを扱っていて問題が発生することもある。


そんなとき、元のデータをそのまま引用することを可能にする関数がある。それが「IMPORTRANGE」である。


例えば、前回作成した歴代内閣総理大臣のデータがあり、その中から特定の代の総理大臣を抜き出して「別ファイル」に名簿を作成したい、とする。そんなときに、この関数を使う。

「=importrange("Spreadsheet の ID","範囲")」

「Spreadsheet の ID」は、Spreadsheet を開いたときの URL の中の、「d/(ID)/edit#gid=0」の部分である。具体例で言うと、前述の歴代内閣総理大臣の URL は「https://docs.google.com/spreadsheets/d/19IOXpCTj3yxb-O-1ldFB_3rrsZKr6l0se8GD_pYQa90/edit#gid=0」であるので、 ID は「h19IOXpCTj3yxb-O-1ldFB_3rrsZKr6l0se8GD_pYQa90」である。が、「importrange」の仕様として、ID の代わりに URL を入れても対応するよ、ってなっているので、わざわざ削ったりして ID にせず、 URL をそのまま入れた方が楽である。

「範囲」はその Spreadsheet のどの範囲を持ってくるかを指定する。「A1」と入力すると、そのファイルの最初のシートのセル A1 の値を返す。

この“最初のシート”というのがちょっと厄介で、複数のシートで作成しており、最初のシートではないシートにデータを作成していると、そのシート名も指定しなくてはならない。例えば、シート2 にデータを作成したときは、「"シート2!A1"」というように指定する。

この際に更に厄介なのが、シート名が「シート2」なのか、「シート2」なのか、「Sheet 2」なのか、「Sheet2」なのか、ちゃんと確認する必要がある。Spreadsheet を使っていて、「何故かエラーが出る」と相談してくる人がいるが、その人が“Mac ユーザ”の場合、大半が数字を“半角”ではなく“全角”で入力していることが原因である。なので、シート名は手入力ではなく、シートを右クリックして「名前の変更」からコピーしてきた方が良いだろう。


この「importrange」は実に便利で、上記ではセル 1 つを引っ張ったが、範囲をまとめて引っ張ることができる。なので、数式 1 つでまとめて持ってくることも可能である。

更に、範囲を持ってこれる、ということは、vlookup の引数の中の範囲に指定することも可能なので、元のリストを別ファイルにしたまま、必要な人だけを抜き出した名簿を作成することも可能である。


これを使うことで、大本の生徒データ(本校の場合は台帳用名簿)の閲覧権限があれば、あとは年組番のデータを入力するだけで選択授業の名簿を作成することが可能となる。

Spreadsheet の使い方1

 Microsoft で言うところの Excel に該当するのが、Google Spreadsheet である。

この Spreadsheet は、個人的には MS Excel よりも優れていると思っている。その優位な内容もあるが、まずはよく使う関数から。どの関数を使うかはその人の業務によって全然違うと思うが、私の場合は「vlookup」が最も使う関数である(たぶん)。


まずは、vlookup 関数の使い方について。

vlookup(「探すキーワード」,「対象とする範囲」,「返す列」,false)

これだけではイメージがつきにくいと思うので、具体的な例を作ってみる。

例えば、データとして、歴代の内閣総理大臣の氏名をまとめたものを用意する。

氏名
1伊藤博文
2黒田清隆
3山縣有朋
4松方正義
5伊藤博文
6松方正義
7伊藤博文
8大隈重信
9山縣有朋

実際のデータ

このデータから、任意の代の総理大臣の名前を拾うために、「vlookup」を使う。実際に作成したのは同じファイルのシート2に作成した。実際に、B1のセルは編集できるようになっているので、必要に応じて動作を確認してもらいたい。


では、この関数を実際に見ながら解説していく。

D1のセルには、以下のように入力されている。

=vlookup(B1,'シート1'!A:B,2,false)

この解説をする。


まず、「B1」というのは、セルB1に入力されている内容を探す、という意味である。

次に「'シート1'!A:B」が対象とする範囲である。対象とする範囲、と言っても、実際に探す範囲はこの範囲の一番左の列である。

次の「2」は、左から2列目の値を返す、ということである。

最後の「false」は、日本語にすると「間違い」とかいう意味になるが、ここでの意味は「並べ替え済みであるかどうか」である。なので、「false」にしているということは、調べるデータが並べ替え済みではないから、完全に一致するものを探す、ということになる。何百万ものデータの中から探すとなると、「false」で検索をする場合は重くなるので「true」にした方が良いが、そこまでいかない、一般的な使い方をするだけであれば、常時「false」で十分である。むしろ、並べ替えされていないデータのときに「true」を指定すると、近い値を最初に見つけたら、その近辺だけを調べて一番近い値を探し、それで検索を終了してしまう為、正確な値を返さない可能性が出てくる。


データを縦方向に探す場合は「vlookup」であったが、横方向に探す場合は「hlookup」を使う。「Vertical」と「Horizonal」の「v」と「h」である。


また、これだけなら同様の結果を返す「xlookup」という関数もある。同じシートの3行目は前述の「vlookup」ではなく、この「xlookup」で作ってある。

=xlookup(B3,'シート1'!A:A,'シート1'!B:B)

「xlookup」の方が単純で、「B3」の値を「'シート1'!A:A」の中で探し、「'シート1'!B:B」の中でその場所に対応する値を返すものである。


この「vlookup」と「xlookup」は、同じことが可能であるが、個人的には「vlookup」の方をよく使う。比較をしていくと、個人的には

  • 「vlookup」の利点
    • 複数の内容を返す際には返す行を指定し直す必要がある
  • 「xlookup」の利点
    • 返すセルが左から何列目になっているかを数える必要がない

であると思っている。


具体的に言うと、例えば台帳用のデータの中から、「姓」「名」「せい」「めい」「性別」「メールアドレス」を並べて表示させたい、という場合、「vlookup」であれば「年組番」がA列に、「姓」がU列にあるので、AからUまでを数えて21列目にあるな、ということを確認し、3番目の引数に「21」を指定する必要がある。このように、名簿の情報量が多くなり、名簿が横に長くなった場合、何列目を返すのか、一つ一つ数える必要があるので大変である。


逆に「xlookup」であれば、返す範囲を「姓」の場合は「U:U」に、名の場合は「V:V」に、など、そのまま入力できるので簡単である。


この考え方であれば、「xlookup」の方が良いように見えるが、「match」関数を使うことでこの問題を解決できる。

例えば、こんなファイルを作成する。シート1には歴代内閣総理大臣の「代」「姓」「名」「在職日数」があり、そのデータを引っ張ってくる際に、セル「B1」には

=vlookup($A2,'シート1'!$A:$D,match(B$1,'シート1'!$A$1:$D$1,0),false)

と入力してある。このmatch関数というのは、「B$1」すなわち「姓」は「'シート1'!$A$1:$D$1」の中で何番目に出てくるかを返す関数である。3番目の引数である「0」は完全一致を示しており、vlookupのときの「false」と同じように、昇順に並んでいる場合は「1(もしくは省略)」でいいが、並び方が不安な場合は「0」を指定する。こうすることで、「姓」が何列目がを数えることなく、vlookupで必要な情報を返すことができる。更に、固定を上手く使うことで、コピペだけで他のセルにも使うことができるようになる。

AIの授業等への活用方法の考察3

生成 AI を使って小論文の添削 を考えていた。 が、ちょっと考えたこともあった。 生成 AI って色々とあるけど、どれがいいのか?? この blog で扱っている内容は、今までは ChatGPT だったのですが、本校のメールシステムは Google Workspace for ...