中学生向け単語テスト対策に！エクセルでオリジナル英語テストを作成しました（キクタン編）

我が家の中学生、

英単語の記憶に苦戦しております！！

そんな我が子のためにエクセルでオリジナル単語プリント（テスト）を作りました。

英語は教科としては小学校からスタートしていますが、やはり本格的になるのは中学生から。

文法や記述が出てきて、とたんに「勉強」の色が濃くなります。

我が家の場合、英単語は耳では覚えていて言えるのですが、スペルがぐちゃぐちゃで書けません。

d, bですら怪しい。。”ア”という発音にはぜんぶ”a”を当てはめようとする。。

娘はオンラインで英会話レッスンをしていることもあって会話は上達してきたのに。。。残念すぎです。

スペル、アルファベットになると・・・

にじこ

ぐっちゃぐちゃ・・・

フォニックスを少しずつ学習すれば単語の並びがわかるはずなのですが、母の言うことは反抗期のため拒否。

とりあえず目の前の単語テストをなんとかせねば！！

そんなわけで、単語テスト対策！のためオリジナル単語テストのフォームを作ることにしました。

今回はキクタンの英単語をもとに、エクセルで作成しています。

普段使わないエクセルを、あちこちネットで関数を調べながら作成したので、正しいやり方ではないと思いますが、私ができる範囲でオリジナルテストフォームを作成したのでご紹介します。

キクタン単語プリントをエクセルで作ろう

こんなプリントをつくりました！

我が家の場合は単語を聞いて意味は大体分かるので、単語のスペルを書かせるプリントを作りました。

・問題用

・マル付け用（答えあり）

の２タイプ作成しております。

＜問題用プリント＞

dayの下の黄色いマスに数字を入力すると、キクタンのDay〇で学習する単語がズラッと反映される表です。

＜答えプリント＞

まるつけ用に作成しました。Day〇日目のところに入力すると、問題（日本語）と答え（英語）が表示されます。これを見ながらマル付けできるので、本をチェックする手間がはぶけます。

１．エクセルでファイルを作ります

まず、エクセルを開きます。
エクセルのウィンドウの左下にある「sheet1」の横にある「+」ボタンをクリックし、シートを２枚にします。

シートの名前は、

・Sheet1　→「キクタン単語一覧」

・Sheet2　→「キクタン問題」

にしました。

２．「キクタン単語一覧」シートを作成します

単語リスト作成前に！エクセルで準備しておきたいこと

エクセルにはちょっとおせっかい機能が付いています。

そう、オートコンプリートです。

セルに文字を入力している途中で、それまでに入力されている文字列が入力候補に表示される機能のことです。

入力の手間が省ける一方で、入力ミスにもなりやすいです。

「can」と入力したいのに、それ以前に「carry」を入力してたりすると、caをタイプした時点でcarryが候補に出てきちゃいます。気づかずにそのままenterにしちゃうのでこの機能は解除しておくといいと思います。

＜オートコンプリート解除の方法＞

エクセルのメニューから

ファイル→オプションを選びます。（オプションは「その他」に隠れていることもあります）

そして①「詳細設定」→②「オートコンプリートを使用する」のチェックを外し③「OK」ボタンを押します。

これで完了です！

「キクタン単語一覧」シートに単語一覧の表を作成し、入力します

にじこ

入力が大変ですが、がんばりましょう！

キクタンの単語をザーッとエクセルファイルに入力するのですが、その前にまずフォーマットを作ります。

シート「キクタン単語一覧」に、見出しを入力します。見出しは５項目です。

• Week名
• day
• NO.（←通し番号）
• 意味
• words

シートはこんな感じに入力しています。あとは、ひたすら入力します。合計約1,350単語あるようです。

キクタンは、Weekごとにユニットが分かれていて、さらにDay1～Day７に加えておまけユニットまであります。

通し番号が振られていますので、入力漏れしにくくて助かります。ただおまけユニットには通し番号がないため「時1」のように独自に番号を振りました。

にじこ

１３５０単語は多いです・・ちょっとずつ追加していこうっと。

入力後はスペルチェックをしよう

手入力なのでどうしても入力ミスが出てきます。

なるべくミスは減らしたいので、エクセルの機能「スペルチェック」でチェックしておきましょう。

①エクセルのリボンから「校閲」を選択し

②「スペルチェック」をクリックします。

明らかなスペルミスはここでチェックしておきましょう。

３．「キクタン問題」プリントシートを作ります

問題プリントの表（フォーマット）を作成

「キクタン問題」シートを開き、テストプリントを作成します。

キクタンは１日１６単語なので、１６行ずつ区切った表を作成しました。

１ユニットは１６行×３列で作成しています。

• 列→３列、（Day、問題入力欄、解答入力欄として）
• 行→１６行

・A4サイズ１ページに４ユニット配置しました。

また、各Weekは、月～日曜日の７日間＋１トピックの、合計８ユニットから成っています。

なのでA4用紙１枚に４ユニット分の問題×２枚、で１Week分の表を作成しました。

Dayに色付けする

フォーマット作成時に、表の左上に”Day”と記入しました。

そして、Dayの下のマスに色付けします。

ここに抽出したい日にちの数字（Day〇日の部分）を入力するので、わかりやすく色を付けました。

問題を表示させる列の一番上のセルに数式を入れる

ここが一番難しいポイントです。

XLOOKUP関数を使いました。１つのセルに入力するだけで、続くデータを引っ張ってきてくれます！

具体的にみていきます。

B2セルに「XLOOKUP(“意味”,キクタン単語一覧!B1:E1,FILTER(キクタン単語一覧!B2:E1400,キクタン単語一覧!B2:E1400=A3))」を入力します。

（合計約1,350単語ということなので、”B2:E1400”と範囲は1400までにしています。）

（関数の[見つからない場合] , [一致モード] , [検索モード]は省略しました。）

詳しく見てみますと。。。

今回はfilter関数でA3セルに入力した値（下の場合だとA３に入力されているのは”1″なので、Day１の問題）が反映されます。

B1に関数を入力すれば、Dayのユニットの単語がそれ以降のセルにズラッと続いて出てきます。

私は８ユニット分フォームを作ったので、そのほかのフォームにも入力していきます。

E２セルに、先ほどの関数の最後の部分をテキストコピーし（絶対参照していないのでセルごとコピーしません）、末尾のA3→D3に変更します。

そのほか、黄色にしているセルをフィルタ値として指定したいので、”Day”の隣のセルに（以下の表参照）XLOOKUP関数を入力していきます。

やることまとめ

以下、入力する内容をまとめました。

それぞれ青字のセルに入力したday〇日にあわせて、問題が反映されるようになると思います。

問題シート完成

完成です！

学習したいDayナンバーを入力して問題プリントを作成してください。

４．解答シートをつくります

問題シートをコピーし、解答シートをつくります

まるつけしやすいように、答え合わせ用解答シートもつくります。

「キクタン問題」シートを右クリックしコピーします。

①右クリックし、「移動またはコピー」を選択します

②「コピーを作成する」にチェックし、OKをクリックします。

③シートのコピーが完成するので、「キクタンこたえ」に名前を変えておきましょう。

解答セルに数式を入力します。

先ほどのXLOOKUP関数を利用します。

先ほどの問題シートからコピーしたフォームの回答欄に数式を入れていきます。

XLOOKUP関数をふりかえると、

でしたね。

今回抽出したいのは、”意味”ではなく、”words”の列の値です。wordsの列を参照するために・・・

隣の問題列セル（B2）の関数のテキストをまるっとコピーし、解答セル（C2）にコピーします。そして

①検索値→”意味”ではなく”words”に変更します。

以上です。

入力まとめ

以下、入力する内容をまとめました。

５．印刷して完成！

以上で、問題プリント、解答プリントの完成です。

こまめに振り返ろう

単語を覚えるには、反復が大事です！

中間や期末の直前に覚えようと思っても大変すぎます。

親は大変ですが、そこはまだ中学生。

苦手意識が付く前に対処するのはとっても大切なので何度も繰り返して学習して定着させていきましょう。

ではまた！

間違った単語だけ印刷したいときのプリントの作り方はこちらです。↓

間違った単語だけを印刷したい！

中学生向け英単語プリント作成～間違った単語だけ選んでテストを作りたい！（キクタン編）

おすすめ無料アプリはこちら

スペルを覚えたい！中学生の無料英単語アプリならWordHolicがおすすめ