Excelのソート機能を使って単語テストをUnitやLessonごとに分ける方法
ランダムで表示される単語テストのシートをソート機能対応にする
単語テストのシートを作ってから問題を教科書の全範囲を入力したという方もいらっしゃると思います。そうした場合テストの出題範囲を指定するときに問題の「入力用シート」を確認して毎回範囲の番号を決めるのが面倒、という方もいらっしゃるでしょう。
今回はすでに作成した単語テストのシートを改良してLessonやUnitごとに自動でランダムに出題するようにしていきます。
必要なもの
- Excelが使えるパソコン
- 前回作ったランダムに問題が出題されるExcelのファイル
所要時間 15分ほど
そもそもランダムに表示される単語テストをExcelで作っていないという人はこちらの記事をどうぞ。
入力シートにフィルター機能をつける
まずは単純にExcelのシートのフィルター機能が使用できるようにしていきます。
そのためには1行目に見出しを入れる行を追加しなければなりません。
まず左端の「1」と書かれている部分をクリックし、右クリックして「挿入」で先頭に1行追加をします。
続いて同じように今度はB列にも一列追加をします。
まずは追加した「1行」に左から順に
「通し番号」「章」「日本語」「英語」「作業列1」「作業列1」
の順にセルに入力していきます。「作業列1」と「作業列2」にはあとで関数を入力します。
追加したB列にUnitやLesson番号を追加していきます。
今回は1-1と入力しています。入力した際に自動で日付などに変更されてしまう場合には、B列の「セルの書式設定」が必要です。先ほどB列を追加したときにクリックした「B」の部分を右クリックして今度は「挿入」ではなく、「セルの書式設定」をクリックしてリストの中にある「文字列」を選択します。これで入力した内容が変換されずにそのまま表示されるはずです。
ここから「章」の番号を入力していきますが、2つ入力して2つのセルを指定した状態で右下の■をドラッグします。
1つのセルだけでドラッグしてしまうと、セルの中の数字が増えてしまい。
このようになってしまうからです。
続けて1-2など続きの章の番号を入力していきます。なお文字で入力したり(「読み物１」など)教科書などのページ数(「p.09」など)で入力したりしても構いません。
続けて見やすいように「1行」を固定します。
固定するためには、左端の「1」をクリックしてから上のタブの「表示」をクリック、「ウィンドウ枠の固定」の右側の下向きの矢印をクリックして、「先頭行の固定」をクリックします。これでExcelの表の中をスクロールしても先頭行だけは表示され続けるはずです。
次にB列にフィルターがかかるようにします。
「B」をクリックしてB列が選択されたら、今度は上のタブのデータをクリック。漏斗のような形をしたフィルターを選択します。
これで「B1」のセルに下向きの矢印がついたボタンができ、そこからフィルターがかけられるようになります。
(ボタンを押して表示する条件の章番号を選ぶとその項目だけが表示される)
以上でフィルターの設置が完了です。
フィルターの範囲指定が問題用紙にも適応されるようsubtotal関数を利用する
上記の方法で入力シートにフィルターをかけても問題の指定の範囲には反映がされません。表示はされていない項目もセルとしては存在しているので変わらず問題用紙には出題されてしまうのです。
そこで今回は表示されていない部分は問題の範囲に入れないように、subtotal関数を利用します。
まずは入力用シートのE列(作業列1)に「テスト作成シート」のA列のrand関数を反映します。
具体的には「E2」のセルに
＝テスト作成シート!A1
と入力して、セルの右下の■を表の下までドラッグします。
「テスト作成シート!」というのは「テスト作成シート」というシート内のという意味です。
「作業列1」に「テスト作成シート」のA列のものと同じ乱数が表示されていたらOKです。
続いて「作業列2」にその乱数と同じものを表示します。ただ、「作業列1」とは違い、表示されていない状態(ソートをかけて画面には非表示になっている状態)になったら存在しないようにします。
具体的には、「F2」のセルに
=IF(SUBTOTAL(2,E2),E2,"")
と入力します。
「E1」と同じ乱数が表示されますが、SUBTOTAL関数が使われているため、非表示状態になったときにはカウントされません。
ここからは関数の説明ですので、関数どうでもいいから早く表を作りたいという人は緑の枠内は読み飛ばしてもらっても問題なく作成することができます。
という形で小計を作る関数です。
今回は集計方法を「2」個数を数えるという形にしてあるので、
=SUBTOTAL(2,E2)
この部分だけ入力すると1と表示されるはずです。
セルE2の中で入力されている数字の個数は1つだからです。
しかし、SUBTOTAL関数もうひとつ特徴があり、画面に表示されていない状態のものはカウントしないというルールがあります。
そのため(画面に表示されていないので)確認することはできませんが、ソートなどをかけて画面に映っていないものはカウントしません。
この関数は
=if(論理式,正の場合,負の場合)
という形でできています。「論理式」の部分が正しければ「正の場合」のものが、正しくなければ「負の場合」のものが表示されます。
もうひとつの特徴として、論理式の部分が式になっていない(＝や＜,＞が書かれていない場合には「正の場合」の数値が、そもそも論理式に何も書かれていない場合には「負の場合」の数値を返します。
今回は「正の場合」の部分がE2、「負の場合」の部分が“”となっているので、論理式の部分に何かがあればE2を、なにもなければ空欄になるようになっています。
SUBTOTAL関数と合わせて画面に表示されているときには「作業列1」と同じ数字が、(確認はできませんが)画面からソートで消えているときには空欄が表示されているはずです。
=IF(SUBTOTAL(2,E2),E2,"")とF2セルに入力したら今までと同じように右下の■を下までドラッグします。
F列にE列と同じものが表示されたらOKです。
これで入力用シートの作業は終了です。
次に「テスト作成シート」への作業に移ります。やることはひとつだけです。
B1セルが依然作った
=RANK(A1,OFFSET($A$1,$G$1-1,0,$G$2-$G$1+1))
となっています。この「A1」となっている部分3か所をすべて「入力用シート!F2」に変更します。$A$1の部分は入力用シート!$F$2に書き換えます。
ごちゃごちゃして難しいという人は
=RANK(入力用シート!F2,OFFSET(入力用シート!$F$2,$G$1-1,0,$G$2-$G$1+1))
この関数をB1セルにコピーペーストしてください。
あとはいつもと同じようにセルの右下の■を表の一番下までドラッグ。
これで入力用シートのフィルターの状況がテスト作成シートにも反映されるようになりました。
入力用シートの「章」の部分にフィルターをかけたときに、テスト作成シートで対象外になっている単語が「＃VALUE！」とエラーになっていたら成功です。
下の画像では「13のしかし」以降の単語がフィルターで範囲外になっています。
以上で入力用シートにフィルターでソート機能を付けたランダム単語テスト作成ツールの完成です。
問題数が少ないと表示されるエラーの「＃N/A」を消す方法
IFERROR関数を使ってエラー表示を消す
もう一点の改良として問題数が20問に満たなかったときの表を見やすくする改良をしていきます。必ず20問以上出題するという人はこの改良はする必要がありません。
出題数が足りなかった場合、「出力用シート」には「＃N/A」と表示されてしまいます。これを解消するためにエラーが起こったときには何も表示されないという関数を組んでやる必要があります。
使うのはIFERROR関数。
=IFERROR (参照,エラーを起こしたときに表示するもの)
現在1問目が出力されるようになっている「C3」セルには
=テスト作成シート!I1
と入力されていると思います。これにIFERROR関数を利用して
=IFERROR(テスト作成シート!I1,"")
としてやります。
あとはいつものように右下の■を表の下までドラッグ。
エラーを起こしたときには「””」と書いてあります。”(ダブルクォーテーション)で囲まれた部分は文字列として扱われるので「””」は間に何もなく空欄になります。
「＃N/A」と表示されていた部分をテストを作成する都度消さなくても自動的に空欄にしてくれる機能が追加できました。
ご意見・ご要望・間違いの指摘等ございましたら、該当ページの下のディスカッション欄にお願いします。
特にご要望はお待ちしております。