数年前に自動で計算50問テストができるエクセルファイルを作ったことがある。
元々は2年生を担任していたので、最初はかけ算の習熟のために作ったものだ。
なので一番最初に作った段階では、かけ算の問題が50問自動でできるだけであった。
ところが実際に使ってみたら思いのほか便利で効果があったので、今度は四則計算が全て自動生成されるように修正をした。
以降、うちのクラスでは算数の最初の5分でひたすら50問テストを解くのが習慣になっている。
修正の動機
このシステムに大きな問題があるかと言えば、あまり無い。
小テストで実施することを前提に作られているので、細かい設定の必要が無かった。
では現実的な運用に耐えらえれるシステムを何故修正しようかと思ったかと言えば、1つはその細かい設定ができない部分が気になったことだ。
そしてもう一つがエクセルの技術が未熟な頃に作ったので、関数がごちゃごちゃいるのが気に食わないからだ。
なので今回は細かい設定にも耐えうるシンプルな設計を目指して修正していく。
問題点の概要
では「細かい設定」とは何か。
一番大きなものは、「たし算とかけ算」とか「ひき算とわり算」のように特定の組み合わせの自動生成ができないことだ。
どの演算を自動生成するかは、先ほどの画像のこの部分を設定するのだが、
ピンクの「ここから」「ここまで」に数字を入れると、下の黄色のマスの範囲から演算が選ばれる。
つまり『１から３まで』のようにすると、たし算、ひき算、かけ算が問題に現れる。
そのため「たし算とかけ算だけ」という選び方ができない。
現実問題としてそういう選び方は殆どしないのだが、できそうなのにやっていないのが気に食わない。
あと数字設定が雑。
ここで数字を設定するのだが、たし算とかけ算の時は前と後ろの数をランダムで生成する。
ところがひき算とわり算でそれをやると、答えにマイナスや分数が出てしまう。
パッとやって計算力を上げる小テストなので答えは自然数で出したい。
そこで苦肉の策だが、後ろの数字はランダムで生成するが、
前の数字に関しては、ひき算はランダムで出てきた数を後ろの数字に足すようにし、わり算は後ろの数字に掛けるように設定している。
なんかごちゃごちゃした感がある。気に食わない。
そして最後、この設定のせいで関数がなんだかごちゃごちゃしているのだ。
前の数字に入っている数式はこんな感じだ。
=IF(C8=$V$17,D8*ROUNDUP(RAND()*($U$8-$U$9)+$U$9,0),
IF(C8=$V$15,D8+ROUNDUP(RAND()*10,0),ROUND(RAND()*($U$8-$U$9)+$U$9,0)))
なーんかごちゃごちゃ感がある。もっと何とかなるやろ。
まとめると今回の修正点はこんな感じだ。
- 「たし算とかけ算」のように任意の演算を選べるようにしたい
- もうちょい上手い数字設定にしたい
- 関数をきれいにしたい
そんなわけで以上の三点を修正していく。
問題点① 任意の演算を選ぶ
今までは１がたし算、２がひき算と、それぞれに番号が付いていた。
単純に考えれば、これが並び変わればいいだけなので、リスト化すればいいか。
別のシートに今と同じような表を作っておく。
出来上がったのはこちら。
こんな感じで、どの計算をするかリストで選べるようにする。
とりあえずここはこれでいい。
問題点② 数字設定を考えつつ、関数をきれいにする
数字の問題と関数の問題は切っても切れないので、まとめて考える。
後ろの数字の関数をきれいにする
後ろの数字には今はこんな関数が入っている。
「=ROUND(RAND()*($V$8-$V$9)+$V$9,0)」
Round関数の中に、任意の乱数を出す関数が入っている。
赤字の部分は任意の乱数を出すときの基本のような形なんだけど、実はもっと簡単な方法があった。
Randbetween関数だ。
これは最小値と最大値を設定すると、その中で任意の乱数を出してくれる。
変えるとこんな感じ。
「=RANDBETWEEN($V$8,$V$9)」
入れ子構造が無くなったので、割とすっきりした。
特定の演算子をランダムで出したい
演算子はさっきの表から選べるようにした。
問題は選んだ演算子をランダムで選択できるようにすること。
今まではこんな関数が入っている。
「=VLOOKUP(ROUND(RAND()*($U$12-$U$13)+$U$13,0),$U$14:$V$17,2,TRUE)」
分からなくはないが、ごちゃごちゃ感もある。気に食わない。
もっとシンプルに行ける気がする。
要は「ある範囲からランダムに選択する」数式ができればいいわけだ。
ある範囲（列）から特定の文字を引っ張ってくるのは、まあIndex関数だな。
「=INDEX($U$12:$U$15,1,)」
赤の部分は演算子の範囲。今は行番号１なので「＋」が引っ張ってきている。
この行番号をランダムにしたい。じゃあRandbetween関数やん。
「=INDEX($U$12:$U$15,RANDBETWEEN(1,4))」
でもこれだと１行目～４行目でランダムになる。
今だと、３，４行目は空白だからでなくていい。
じゃあ最大値を２で止めるには？
とりあえずCountA関数を使えば「空白でないセルの個数を返してくれる」ので、行けるかと思ったが、なんか上手くいかない。
あ、表の演算子の部分は空白に見えて、数式があるからか…
ならば解決策が２つある。
1つ目が演算子の左隣りの「たし算」「かけ算」との表をCountA関数の範囲にする。
2つ目がCountbrankを使って、空白（数式も空白とみなされる）を数えて、４から引く。
1つ目：=INDEX($U$12:$U$15,RANDBETWEEN(1,COUNTA($T$12:$T$15)))
2つ目：=INDEX($U$12:$U$15,RANDBETWEEN(1,4-COUNTBLANK($U$12:$U$15)))
うーん、どちらもあまり変わらない！
しかし２つ目は数式を定義すれば、もっと短くなる気がする！
「$U$12:$U$15」の範囲を「演算子」と設定する。すると、こうなる。
「=INDEX(演算子,RANDBETWEEN(1,4-COUNTBLANK(演算子)))」
最近知ったのだけど、名前定義はとても便利。
慣れている人ならすぐに活用すべきテクニック！
kokodane.com
最後は前の数字だ。
前の数字を細かい設定しつつ、関数をきれいにする
前の数字は演算によって、それぞれ数字を設定したい。
とりあえずマスを分けてみる。
たし算とかけ算の場合は簡単。
ここに入力した範囲内でランダムに数字が出るようにすればいい。
単純なRandbetween関数をする。
ひき算とわり算の場合は難しくて分からなくなったので、こちらで整理して考える。
tohruyoshino.hatenablog.com
結論として答えの範囲を設定することにしたので、表をこんな感じにする。
方針が決まったところで、実際に関数を組み立てていく。
それぞれの四則計算についてどうなるかと言えば、こういう計算をする。
- たし算・・・表の範囲で数字をランダムに出す。
- かけ算・・・表の範囲で数字をランダムに出す。
- ひき算・・・表の範囲で数字をランダムに出し、それを後ろの数字に足す。
- わり算・・・表の範囲で数字をランダムに出し、それを後ろの数字に掛ける。
そのため、まず「表の範囲で数字をランダムに出す」ところを組み立てる。
基本はRandbetween関数でいく。
まずたし算（＋）の場合。
「=RANDBETWEEN(U8,U9)」
以下かけ算（×）、ひき算（－）、わり算（÷）の場合とずらしていきたい。
じゃあさっきの表があるからHLOOKUP関数だな。
「=RANDBETWEEN(HLOOKUP(C8,$U$7:$X$9,2,),HLOOKUP(C8,$U$7:$X$9,3,))」
こんな感じになった。これで「表の範囲で数字をランダムに出す」ことができた。
この数式に「ランダム数字」という名前を付けておく。
あとは条件分岐なので、以下のように設定。
- たし算orかけ算 → ランダム数字
- ひき算 → ランダム数字+後ろの数字
- わり算 → ランダム数字×後ろの数字
出来上がった数式がこちら。
「=IF(OR(C8=$U$7,C8=$V$7),ランダム数字,IF(C8=$W$7,ランダム数字+D8,ランダム数字*D8))」
よーし、そこそこシンプル！！
数式の名前定義ってめちゃくちゃ大事だね！！
無かったら、こうよ。
「=IF(OR(C8=$U$7,C8=$V$7),RANDBETWEEN(HLOOKUP(C8,$U$7:$X$9,2,),HLOOKUP(C8,$U$7:$X$9,3,)),IF(C8=$W$7,RANDBETWEEN(HLOOKUP(C8,$U$7:$X$9,2,),HLOOKUP(C8,$U$7:$X$9,3,))+D8,RANDBETWEEN(HLOOKUP(C8,$U$7:$X$9,2,),HLOOKUP(C8,$U$7:$X$9,3,))*D8))」
うーん、えぐい。
仕上げ作業
後は細かいレイアウトを調整して、印刷サイズを増やす。
今はA４版で作っていたので、B5版とB4版も作る。
B4版はもちろん１００問設定。
そして完成！
やったー！できたー！
完成してみると分かる。
前とほとんど変わらねぇ。１日何をやっていたんすかねぇ
しかし中身を見てみると以前よりはるかにスマートに細かい設定ができるようになった。
関数も短くなって、満足。
これも欲しい方がいたらツイッターとかでDMしてくれれば、差し上げるのでどうぞどうぞ。
今日はここまで。
