【教員Excel】とりあえずクラス名簿を作ろう【2021/5/6版】

Twitter エクセル 仕事 学校 教育 知識 趣味 関数

f:id:tohruyoshino:20210110163510p:plain

１　はじめに

先日、らいざ先生の筋トレ企画に仲間入りをした。吉野はそこそこ筋肉もあるので、筋トレはわりと好きだ。ほいほい付いていき、日々筋トレをしている。

さて、らいざ先生を見ていると、Twitterを使い、自分の趣味（筋トレ）を周りに発信している。Twitterの教員界隈にはそういった発信を行っている人が多くいる。

吉野はTwitterに特に有意義さを求めていなかったが、折角ならばもう少し有効活用してみたい。そこで何か自分でも発信してみようと思った。

 

では吉野が教員向けに何か発信できるかといえば、一番はExcelについてだろう。教員界隈ではよく、「Excel使えない教員」が槍玉にあげられている一方で、意外に基本的な使い方を知らない人も多い。

なので、そんな初学者に向けて、教員が使えそうなExcelの知識について発信していくことにする。

 

２　目標

折角発信するなら、らいざ先生の筋トレのように周りの技能が高まると嬉しい。

そのため、この発信の目標は以下のようにする。

  • Twitterの教員界隈の人がExcelと仲良くなり、ある程度扱えるようになる。
  • 発信を続けて、吉野自身のExcelの技能を高める。

 

因みに先々には個々のExcelシートについて、具体的なアドバイスができれば良いかなーと思っている。

 

３　進め方

具体性の無いことを発信しても有意義じゃないし、その場限りの知識になってしまう。

そのため今回は何か実際に現場で使えそうなものを作成しながら、その中の技術を発信していく形にする。

現在、計画をしている作成物はこんな感じ。

  • クラス名簿
  • 少人数名簿
  • 座席表
  • 自動生成する計算プリント
  • 所見や成績を管理する名簿
  • 連続で印刷できるシート（VBA）

後は思いついたら…

教員のExcelのほとんどは名簿関係でしょ（偏見）

VBAについては詳しくないので自分も結構勉強しないといけない。てか、そんな深く教えられないと思う。

 

４　クラス名簿を作る

 １　児童氏名の入力シートを作る

基本情報シートを作る

 

とりあえず今後出力してほしい基本情報を打ち込むシートを作ります。

今回は名簿なので、出力情報は以下のようにしました。

  • 学校名　年度　クラス　組　担任名
  • 児童・生徒名　ふりがな　
  • 男女

これらを入力できるシートを作り、情報を入力します。

男女に関しては、男女別の名簿を作る時に必要なのでいれました。

保健書類とかでなんやかんやあると便利なんですよねぇ。

 

シート作成時のコツとして、今後入力しない部分には色を着けると良いです。

シートを見た時に、「あ、白い部分に入力すれば良いんだな」とすぐに分かります。

 

名前順にする方法

 

他の名簿ファイルからコピペした時点で、おおむね名前順になっていると思います。

ですが、たまに名前順になっていないものもあるので、順番を揃えましょう。

 

手順としてはツイートの通りですが、少し加えます。

  1. 並び替える範囲を選択する
  2. 上の「データ」タブを選んで、その中の「並び替え」を選択する
  3. 出てきたボックスの右上に「先頭行をデータの見出しとして使用する」があるので、チェックを外す。
  4. 「最優先されるキー」を「ふりがな」を入力している列にする（今回の場合は列F）
  5. 順序を「昇順」にする
  6. OKで実行

 

先程の３番の手順を飛ばすと、「虎杖悠仁」を見出しとして認識します。

見出しとはいわゆる「名前」「ふりがな」と書いてある部分のことです。

表において見出しが移動すると大変です。

チェックをしたままだと「虎杖悠仁」が見出しとみなされるので、一番上に固定されてしまいます。

そのためチェックを外します。

 

また「最優先されるキー」ですが、漢字の名前列（今回は列E）でも基本は大丈夫だと思います。

ただ漢字だとたまーに別の読みで判定されることがあります。

例えば「麻倉（あさくら）」なのに「麻倉（まくら）」のように。

こうなると順番が変わってしまうので、ふりがな列を最優先にした方が無難です。

※漢字の並べ替えのもう少し細かい設定

漢字並べ替えは、その漢字の「ふりがな」（入力した文字）に依存します。

例えば「夜神月（やがみらいと）」と「夜凪景（よなぎけい）」はどちらも「夜」という漢字から始まります。

ただし入力の時は「夜神月（よるかみつき）」と「夜凪景（やなぎけい）」で変換しています。

この状態で漢字列をもとに並べ替えをすると、入力した文字の並びのために「夜凪景（やなぎけい）」が先に来ます。

「ホーム」タブにある「フォント」から、「ふりがな」の編集はできますが、まぁ面倒なため、ふりがな列を作って、そこを基準に並べ替えする方が楽です。

　

最後に今回は「昇順」でしたが、逆にしたいときは「降順」です。

名前順でそんな時あるかな？

 

２　男女を入力しよう

プルダウンの方法（直接入力）

 

今時だと名簿は男女混合が普通。

しかし身体計測などの保健関係や運動会での競技のために、男女別の名簿を作る時はまだある。

そのために基本情報に男女もあらかじめ入れておくと便利。

 

男女の2通りしかないしコピペの方が楽なんだけど、今回は練習も兼ねているので、プルダウンリストを使ってみる。

 

やり方は上記のツイートの通り。

  1. リストにしたい場所を選択する。
  2. 「データ」タブから「データの入力規則」を選択する（ここが一番見付けにくい）
  3. 入力値の値を「すべての値」から「リスト」にする。
  4. リストの中身の「男,女」を入力する。

４番のリストの中身については、「,」（カンマ）で区切ることでいくらでも増やせる。

「男,女,犬,猫」のように。

 

プルダウンリストを作りたいなーと思った時に、大体思い出せないのが２番のステップ。

よく使う割に地味なところにあるので、これは頑張って覚える。

忘れたら、「エクセル　リスト　作り方」とかでググる。

 

プルダウンリストの参照の仕方

 

元々リスト候補がどこかにあるとか、選択肢が多すぎて直接入力だとダルイとかそういう時は、リストの中身を参照してくる。

プルダウンリストの作り方の４番のステップで「男,女」と直接入力しているが、エクセル内のどこかを参照することで、それをリストの候補にすることができる。

 

これの良いところは参照した場所を変更すると、リスト候補も変更内容が反映されること。

例えばクラブ名簿を作る時に、クラブ一覧表からリストを参照したとする。

前年度は「野球クラブ」があったが、今年度は「外遊びクラブ」に変わったという場合、クラブ一覧表の「野球クラブ」を「外遊びクラブ」に変更すれば良い。直接入力だと、いちいち「データの入力規則」を開かないといけないが、一括で済む。

 

プルダウンリストの良いところ

最初に書いた通り、今回のような時はリストを使わず手入力しても労力は変わらない。

ただリストを使う良さとしては、こちらが意図した通りの入力しかできなくなるということ。

 

例えば児童の所属クラブを入力してもらう時に、「手芸・料理クラブ」があるとする。

入力の際に、ある先生は「手芸・料理」、別の先生は「手芸・料理クラブ」、また別の先生は「手芸料理」などと、入力値がバラバラになるケースがある。

入力値がバラバラだと、後で別の所でクラブ名を参照する時に正しく参照できなくなる。

 

これに対し、リストを使うと「手芸・料理」以外受け付けなくなるので、入力値を統一することができる。

リストを使う良さは、そういう入力の幅を制限することで入力値を統一すること。

 

３　縦に一列に名前を表示する

新しいシートの作り方と名前や色の変更

 

新しいシートの作り方は、上の通り。

ショートカットキーの「shift＋F11」は知っていると便利だけど、マウスでもそんなに手間では無いから、有用度は低いかな。

 

タブの上で右クリックをすると色々な操作ができる。

その中ではとりあえず以下のことができれば良いかと。

  • タブ名の変更
  • 色の変更
  • コピペ

 

コピーの時は「コピーを作成する」にチェックを入れる。

これにチェックをしないと、単にシートが移動するだけになる。

 

因みに「移動先ブック」という部分を別の場所にすると、違うExcelファイルにコピーすることができる。ブックはファイルと読み替えて大丈夫です。

基本情報を入力するシートは他のExcelファイルでも使ったりするので、もう一度作るのが面倒だなーと思ったりする。

そういう時に前のファイルからコピーしてくると楽ちん。

 

別の所に入力されている文字や数字を表示する

 

基本は「=(イコール)」を入力して、他のセルをクリックする。こうするとクリックしたセルを表示できる。表示形式が同じなら、小難しい方法よりシンプルな方法の方がオススメ。

同じシートの中だと「=E9」としか表示されないが、別のシートを参照すると「=児童氏名!E9」となる。"児童氏名"の部分が参照しているシート名で、”!”はつなぎの記号。

 

簡単なオートフィルのやり方は上の通り。オートフィルの解説は後述する。

 

みんな大好きvlookup

最近、Excel関数トーナメントで優勝していたり、Excel中級者の関門と言われたり、何かと話題に出るvlookup。慣れればそんなに難しい関数でも無い。でもその割に教員の仕事だと使い道が多いので、しっかり覚えておくと便利。

 

vlookupが親しめない大きな理由が、何をやっているか分かりにくい点がある。SUMとかAVERAGEとかはやっていることが分かりやすいけど、vlookupは文字列をいじくるタイプの関数なので、ちょっとなじみが薄い。 

とりあえずvlookupは「あの表の出席番号○番の人の名前を出して」という命令をしていると覚える。

 

 

範囲の設定が一番大切で、vlookupのお約束が「表の一番左側と検索値が合う所を探す」こと。今回は出席番号が検索値になるので、表を参照するときは出席番号が一番左側になるように範囲設定をしないといけない。

もし１番左側以外の列を検索値にしたい時は、index関数とmatch関数を組み合わせるとできる。それは後述する。

vlookupしかできない人は一番左に新しく検索用の列を作るのが良いかもしれない。

 

 

vlookupは検索値を表の中から見つけると、その右側を調べに行く。この場合、出席番号１番が指定されているので、その右側には「麻倉葉」「あさくらよう」がある。このどちらを表示するか選ぶのが、列番号の役割。

 

検索の型については今は覚えなくても良いと思う。初学者向けに話しているし。

一応、分かりやすく書いてあるサイトはあるので、参考にリンクを貼っておきます。

VLOOKUP 関数：TRUE を使って完全一致｜クリエアナブキのちょこテク

 

vlookupの仲間にHlookupという関数もいる。vlookupのvはvertical（垂直）で、検索値を垂直に探していくが、Hlookupは横に探していく。後は同じ仕組み。

ExcelのHLOOKUP関数の使い方｜指定の行と同じ列にある値を返す｜Office Hack

 

参照元を動かしたくない時

オートフィルは参照先が連動してくれて楽だけど、同じ表から参照する時などは表の範囲が変わってしまうと困ることがある。そんな時には絶対参照をする。

絶対参照とは、オートフィルをしても連動せず、参照元を固定する方法だ。

 

例えば「A1:A10」は「A1からA10まで」を示す。

これを普通にオートフィルすると「A2:A11」「A3:A12」と一つずつズレてしまう。

 

そのため絶対参照のマークである「$」を参照元に付ける。

こんな感じ→「$A$1:$A$10」

こうするとオートフィルをしても、ずーっと「A1からA10まで」でズレなくなる。

 

「$」を２つ付けているが、これは「A1」の「A」と「1」をそれぞれ固定しているから。

「A列を固定したいけど、数字の方は固定したくない」みたいな状況も時折出てくる。そういう時は「$A1」とすることで、Aだけ固定することができる。まぁ、これは応用編。

 

絶対参照をする時は素直に「$」を打ち込んでもいいが、対象の関数を選んでF4を押すことで簡単に付けることができる。

因みに連続でF4を押すと、「A1」→「$A$1」→「A$1」→「$A1」と変わっていく。

 

(2021/5/6 現在　ここまで解説済み)

 

・＝A１とかで良いんじゃね？（＝すると、セルを引っ張れる。）

　　・選択範囲に名前をつけちゃう

 

　４　空白でエラーになった
　・IF関数でエラーを消す（＝””で判定する）

　・IF関数でエラーを消す（ISBLANK関数で判定する）

　・IFERROR関数使おうよ

 

　５　オートフィルする

　・とりあえずオートフィルの説明

　・オートフィルでずれるから、絶対参照の仕方

　・オートフィルで書式を崩さない方法

　・右下の十字に頼らないオートフィルのやり方

 

　６　名簿の上の学校名やらクラス名を出す

　・＝文字列の打ち方

　・＆でつなげる方法

 

　７　男女別名簿を作ろう

　・新しいシートをコピーで作る（別のエクセルシートにもできるよ）

　・どういう概念（答えは男１　女１）で検索すればいいか

　・男１　女１をどこに作るか→セルの挿入　ショートカットキー

　・男女別の番号のつけ方（countif関数）

　・”片っぽだけ”に絶対参照をつけてオートフィル

　・男１の列の作り方（＝A１＆B1みたいな）

　・見た目上いらない列の隠し方（列や行の非表示方法と再表示）

　・vlookup関数での、検索の仕方（検索値を”男”＆A1とかにすること）

　・vlookup関数での、検索の仕方（検索値の”男”をB1とかのセルにもできるよ）

　

　８　印刷書式を整える

　・印刷範囲を設定する

　・「表示」方法は改ページプレビューがおススメ（ページレイアウトでもいいよ）

　　上でもできるけど、下（拡大縮小％の隣）でもできるよ

　・改ページプレビューで青点線を動かして、ページ数を減らす

　・余白設定の仕方

　・印刷前に拡大縮小で1ページに収めるの設定

　・「ページレイアウト」からの拡大縮小設定（ページに収める）

　・タイトル行設定して、タイトルが変わらないように印刷する方法

　・印刷のショートカット（control＋P）の紹介

 

　９　メンテナンス

　・シートの保護の仕方

　・入力する部分だけ保護を外す方法

 

　１０　長く使うための応用編

　・出席番号と名前の間に「男１」とかの列をもってくると、来年度消しちゃう問題

　・indexとmatch関数を使った引用の仕方

 

　？　おまけ

　・画面が見にくい時は、右下の％で拡大縮小する（control＋ホイールでもできるよ）

　・いらないシートを隠す

　・列や行を選択して、ダブルクリックでその行の一番長い文字まで拡大する

　・複数行列を選択して、まとめて行列を同じ幅にできる

　・shift＋→でずーっと選択　control＋→？で個別選択とか跳べる

　・F２で入力は死んでも覚える

　・セル内改行はAlt＋Enter

　・control＋１で書式設定は死ぬほど便利

　・ヘッダーとフッターに入力しとく

　・自動的に問題を作ろう