Excelで初心者でも簡単に作れる単語テスト作成ツールの作り方
Excelで簡単に作れる、自動単語テスト作成ツール
学校の先生や塾の講師、家庭教師などをしていると、日々の単語テストなどを作るのが非常にめんどくさいと感じることはありませんか？ ここではExcelで誰でも簡単に作れる自動単語テスト作成ツールの作り方を解説していきます。
もちろん、学習意欲の高い学生さんも自分で小テストを作ってみてはいかがでしょうか？
必要なもの
- Excelが使えるパソコン
- 紙に印刷できるプリンター
所要時間 30分ほど
どうしてExcelで単語テストを作るのか?
Excelを使うことの利点は、
- Windowsの入っているほとんどのパソコンに入っているため、専用のアプリが入っているPCでなくてもどこでも編集・入力が可能。スマートフォンやUSBメモリなどに入れてデータを常に持ち運べる。
- 一度関数を組んでしまえば、翌年以降も利用可能。また関数のコピーペーストで他学年でも同じシステムが使える。
- Excelそのものを単語テストを作る過程で学ぶことができる。
- ExcelはWindowsのマイクロソフトのアプリなので今後サービスが終了する可能性が低い。また、Googleスプレッドシートでも似た操作、関数で同じようなものを作ることができる。
- 「sheet1」→「入力用シート」
- 「sheet2」→「テスト作成シート」
- 「sheet3」→「出力用シート」
と、いいことずくめです。Excelって何か苦手なんだよね……という人も細かに操作方法まで説明してあるので、記事を片手に是非一度単語テストを作ってみて下さい。
シートを作る
この自動単語テスト作成ツールは一度作ったらさまざまな小テストの作成に利用できます。そのため、問題を入力するシート、問題を自動で作ってくれるシステムのシート、そして実際に出力に使うシートの三枚に分けます。
シートを設定して名前を付ける
まず、エクセルを開き、シートを三枚に増やします。
エクセルのウィンドウの左下にある「sheet1」の横にある「+」ボタンを二回押し、シートを三枚にします。
このシートのタブはクリックすることでシートを切り替えることができます。また、一度クリックしてから右クリックをすることでシートの名前を変えることができます。
どのシートかわかりやすいように
としておきます。
入力用シートを作る
入力用シートは問題を入力するためのシートです。たとえば単語テストの場合、出題する単語を入力するものです。
左上のAのセルは通し番号を入れておくと後々管理がしやすくなります。
「A1」に「1」、「A2」のセルに「2」を入力したら、左クリックしたまま、「A1」と「A2」のセルを範囲指定します。
範囲指定をしたセルの右下の■をクリックしたまま、ずっと下にスクロールすると、そのまま番号が増えていきます。
番号は多少行き過ぎてもかまいません。
B列にはひたすら問題を書いていきます。この場合には単語の意味が書いてあります。また「単語→意味」以外に将来的に「意味→単語」のテストを利用する可能性がある場合には、C列に単語そのものも入力しておくと便利です。
例として30番まで単語が入力してあります。例として作っているので、問題の訳が雑になっているのはご容赦ください。
ここまでできたら入力シートは完成です。
また入力シートはあとで問題を追加することもできます。その際には、テスト作成シートを少し調節することで、対応することができます。
テスト作成シートを作る
今回の作業の中で一番難しいのがこのテスト作成シートです。
最初に、左下の「テスト作成シート」のタブをクリックします。すると今までの「入力シート」から新しいシートに切り替わります。
ランダムに問題を選択してくれるよう関数を作る
まず、C1のセルに、
=入力用シート!A1
と入力します。パソコンでこの記事を読んでいる方は、そのままC1のセルにコピーペーストしていただいても構いません。
「=(セルの<行の文字><列の番号>)」を入力すると他のセルが自動的にコピーされて表示されます。
セルの行と列を入力前に「<シート名>！」と入力すると別のシートのセルを持ってくることができます。
先の例の場合、「=入力用シート!A1」となっているので、同じExcelファイルの中の「入力用シート」というシートの中の「A1」セルの中身をそのセルに反映するという意味になります。
そしてそのセルの右下の■を右に2セル分ドラッグします。すると先ほど、入力用シートに入力したものが出てきます。
そしてその範囲を下にずっとドラッグしていくと先ほど作ったテストの内容がすべて「テスト作成シート」の方に反映されます。
テストの内容がすべて反映されたら、A1のタブに戻り、
=rand()
と入力します。
※ ( )の中には何も入力する必要はありません。
通常=rand()は後ろに整数をかけて使用しますが、今回は各問題に振られた数字の大小を使って問題をランダムに並び替えています。
続けてF1のセルに「問題はじめ」、F2のセルに「問題おわり」と入力します。F3のセルには「問題パターン」と入力します。
問題数に合わせてピックアップした問題を書き出す関数を作る
続いてHの列に問題数の数分だけ上から番号を振ります。
例では、20問の小テストを想定しているため、上から20番までの数字が振ってあります。
G列は問題の範囲を調節するために使います。まずは1番(例では「名前・name」)から20番(例では「桃・peach」)までをテスト範囲とします。G1に「1」G2に「20」と入力します。
またD列(日本語)を問題として出題する場合には、F3セルに「0」を、E列(英語)を問題として出題する場合には「1」を入力します。今回の場合には「1」を例として入力しています。
続いて問題をランダムで選択して並べる関数を作ります。
B1セルに
=RANK(A1,OFFSET($A$1,$G$1-1,0,$G$2-$G$1+1))
と入力します。
これが最も難しく、ミスしやすい部分です。途中の「$」や「,」、「-」やカッコなどを間違えないように入力しましょう。
PCでこの記事を見ている場合には上記の式の部分をコピー＆ペーストした方が失敗が少ないかもしれません。
何もエラーが表示されず、B1セルに「1」と表示されたら成功です。
$を使った行番号や列番号はドラッグをしても固定されたままになります。
そのため上で入力したもののうち、ドラッグをしても変化するのは最初に入力した「A1」だけになります。
続いて、A1セルとB1セルを範囲指定して右下の■を問題の最後の行までドラッグします。
問題の範囲に指定されていない番号(今回の場合は20番までで問題を指定しているので21番以降)は「#N/A」と表示されます。
最後に、I1セルに
=VLOOKUP(H1,$B$1:$E$999,3+$G$3,FALSE)
(1000問以上問題を作った場合には、関数内の$999の部分には入力した問題の数を入れてください。)
と入力します。
vlookup関数は
=vlookup(検索値,範囲,列番号,検索の型)という形で使います。
「検索値」=探す数字や文字
「範囲」=今回使う表の範囲
「列番号」=その結果として返す列(今回使う表の左から何列目か)
という意味です。
こちらも非常に複雑なので「$」や「+」を入れ間違わないようにしましょう。
I1セルにうまく文字が表示されたら成功です。
I1セルにカーソルを合わせて、問題数の数までドラッグします。
問題の範囲を切り替えるときには、「問題はじめ」と「問題おわり」の右のセルの数字を調節します。
例えば1～30の中から出題したいときには、1と30を入力します。6～28までをテスト範囲にしたいときには6と28を入力します。
また現在「英語→日本語」の問題になっていますが、「日本語→英語」の問題を出題したいときには「問題パターン」の右の数値を1から0に変えます。
それぞれの操作をして、I列が自動で切り替わるのを確認してください。
問題を追加したときに行う操作
あとから問題を追加した場合には、
A～D列を問題数のところまで下にドラッグする
I列の問題の合計数(例では30だった＠の部分の数字)を変更してもう一度ドラッグする
ことによってテストの対応範囲を改めて指定し直すことができます。
以上で「テスト作成シート」の入力は終了です。
最後に出力用のシートを作成します。
出力用シートを作る
残すは実際のテスト用紙を作るだけです。テスト用紙については、それぞれの学校や学年などで使っている形式があると思います。
ここでは簡単な一例を紹介します。
まず、セルB列全体を範囲にするために、「B」と書かれている部分をクリックします。そして右クリックをしてウィンドウを開きます。
続けて「セルの書式設定」→「表示形式」→分類の中の「文字列」を選択して「OK」を押します。これはB列に問題番号を( )付きで入力するためです。
Excelのセルは通常の状態でそのまま( )付きの数字を入力すると負の整数として表示されてしまうからです。続けてB3セルに「（1）」と入力し右下の■を自分が作ろうとしている問題数だけドラッグします。例の場合では20問出題するため、(20)まで枠を作ってあります。
20問以上になる場合にはE列に同じようにセルの書式を変更して(21)以降の問題を作ると良いでしょう。
B列の数字がセルの中央に来るように、B列を指定した状態でセンタリングのアイコンを押します。
少し番号の幅が大きいのでB列の横の部分でセルのサイズを調節します。
C3のセルに
=テスト作成シート!I1
と入力します。
これで前もって「テスト作成シート」に入力した問題が反映されます。これを問題の数までセルの右下の■を使って伸ばします。
C列とD列もB列の幅を調節したように調節します。
続いてB2からD2のセルを選択し、上のツールバーの中の「セルを結合して中央揃え」を選択します。
また、問題の下のB列からD列も同じように結合します。
結合した上の段にはタイトルを、下の段には氏名欄などを作ります。センタリングがかかっているのでちょうどいい場所にスペースなどを使って移動させると良いでしょう。
最後にツールバーにある罫線などを利用してテストらしく仕上げます。印刷するときにサイズが合わない場合には、先ほど列のサイズを調節したときの要領で適切なサイズにしてやることで使用する用紙に合わせることができます。
このExcelの便利な点は一度入力すれば、テスト作成シートの「問題はじめ」と「問題おわり」の横のセルの数字を変えてやることで自動で調節することができる点、そして問題パターンを0と1で切り替えることによって、「日本語→英語」と「英語→日本語」を切り替えることができる点です。
また、ファイルをコピーして入力シートを書き換えてやることで他の学年や、他の教科の一問一答などのテストに利用することもできます。
一度作成しておけばテストを作る手間が省けるので利用してみてはいかがでしょうか。
Excelで作った単語テストの改良例
Excelで作ったツールは既存のツールと違い改良可能、自作なのでいくら使っても費用がかからない！
Excelは多くのパソコンに入っているアプリケーションです。そのため一旦ファイルを作ったり、関数の使い方を覚えたりすることでより便利に、より多くのツールを作ることができます。
単語テストをより使いやすく改良した例です。
自動で答え合わせ用の用紙を作成してくれる機能を追加してみました。
ソート機能を追加してより単語の範囲の指定を簡単にしてみました。
自身の学習のため、生徒の管理のため、生徒を指導する手助けのため、Excelをさまざまな形で利用してみてはいかがでしょうか。基礎さえある程度分かっていれば、関数を知ることとそれを組み合わせることで無限に便利なツールを作ることができます。
Excelで他にも色々なものを作ってみる
作るものが決まっている場合には、「作りたいもの」や「必要なシステム内容」で検索をすれば、多くの方がアイディアを公開してくれているはずです。
またExcel関数辞典などをパラパラとめくりながら、「この関数を使ってこんなツールを作ったら便利かもしれない！」と考えてみるのもいいかもしれません。
紙の書籍はこちらから
kindle版はこちらから
なお、「時短の王道 Excel関数全事典 改訂版」の方はkindle unlimited会員であれば無料でkindle版で読むことができます(2021年3月現在)。
ご意見・ご要望・間違いの指摘等ございましたら、該当ページの下のディスカッション欄にお願いします。
特にご要望はお待ちしております。