【無料配布】Excel自動名簿シート解説【仕事効率化】

今回は「委員会」でも「クラブ活動」でも活用出来る超絶便利なExcel自動名簿シートを作ってみました♪

作り方の解説になるとかなり難しく大変になるので、今回は自動名簿シートの使い方や関数の解説だけにしておきます!

そしてなんと!
この記事の一番最後でExcel「自動名簿シート」データを無料配布しています!

自動名簿シートでできること・全体の名簿に数字を打ち込むだけで様々なパターンでワンクリック印刷!
・学級ごと、クラブや委員会などごとに名簿が印刷できる。
・後からの変更も1箇所変えるだけで全てに適用!

使い方解説

スクリーンショット (421)

「入力名簿」の全体像はこんな感じになっております。

スクリーンショット (422)

A.G.H列は関数の引用に使うだけで見づらくなってしまうため非表示にしてあります。

スクリーンショット (423)

「入力名簿」

まずは、緑色のセルに学校で行うクラブ名や担任、場所を入力します。

ここに入力することで、名簿全体に反映されます。

上画像のように「グラウンドスポーツ」と変えたことで(F4)などの表示が変わっています。

スクリーンショット (424)

濃い赤色には(学年、組、番号=4101)のような形式で番号を打ち込みます。

4101、4102と2つ打ったあとに2つのセルを選択し、セルの右下の正方形をドラッグ(オートフィルと言います。)をすることで、1つずつ入力しなくても自動で数字を表示させることができます。

薄い赤色のセルには名前を入れます。

元々ある学級名簿などからコピーをすると良いでしょう。



青色のセルの番号を変更することで、クラブ名も自動で変わります。

これでいちいちクラブ名を入力する必要がありません。

番号だけ打ち込めば良いようにしてあります!

スクリーンショット (425)

出席番号を認識してB列(学級列)に学級も自動仕分けして、色が変わるようになっているため、見やすくなっています!

「全体名簿」

スクリーンショット (432)

「全体名簿」はこのようになっています。

このシートはほぼ全て自動で表示されるため、1番上の「2020年」だけ毎年更新するだけです。

スクリーンショット (427)

右側は上の画像のような関数が入っていて、「vlookup」関数で名前を探しています。

どこから探してくるかと言うと…

スクリーンショット (428)

この「入力名簿」の隠れているA列です。

ここに「text」関数があり、「学級」と「クラブ名」をくっつけて表示しています。

この連結表示があることで、

スクリーンショット (429)

「vlookup」関数で探し出して名前を表示することができます。

「iferror」関数で囲まれているのは、「vlookup」に当てはまらなかったセルにエラーが表示されるのを防いで、表全体を見やすくするためです。

スクリーンショット (430)

右側は直接印刷するわけではないのですが、学級ごとでわかりやすく色分けしてます

スクリーンショット (431)

左側が実際に印刷される部分です。

右側の「vlookup」で探し出された名前を列ごと(クラブ名)ごとに空欄を詰めて表示しています。

(index)関数で指定の行、列のセルを反映します。

この行と列を(small)関数と(row)関数を使用して指定しています。

iferror(index(N$3:N$200,small(if(N$3:N$200<>””,row(N$3:N$200)+2),row()-2)),””)

(index)関数は(列,行)と指定するため、最初のN列の範囲が列を指定しています。

このあと行を指定します。

iferror(index(N$3:N$200,small(if(N$3:N$200<>””,row(N$3:N$200)+2),row()-2)),””)

(small)関数は指定した範囲から数値の小さい順に表していきます。

そして(if)関数でN3:N200の範囲で空白(””)でなければ(<>)、(row)関数(セルの行番号を出す関数)で行番号+2を表す。

という形になっています。

iferror(index(N$3:N$200,small(if(N$3:N$200<>””,row(N$3:N$200)+2),row()-2)),””)

最後のrow()-2は、(small)関数の続きで、順番を表しています。

今回の表が3行目から始まっているので、「-2をして1から小さい順に数値を出す」

という意味になっていますが…難しいですよね・・・。





それぞれの関数の意味がわかる方には良いかもしれませんが、index関数やrow関数、small関数は複雑な組み合わせで難しいものです。

今回はなんとなく関数が使われているということだけ理解してもらえればいいと思います。

「クラブごと印刷」

スクリーンショット (433)

「クラブごと印刷」のシートには、このような表が入っています。

青色のセルにクラブ番号を打ち込むと、クラブの担任、場所、児童の番号と名前が自動で入力されます。

日付を学校のクラブ日程に合わせて変更すれば、出欠表としてそのまま使えます♪

スクリーンショット (437)

A列にはこのような関数が隠れています。

これは、

スクリーンショット (435)

「全体名簿」のG列に隠れていたものを小さい数値順に反映しいます。

スクリーンショット (436)

このG列は(if)関数で「クラブ名簿の(C1)青いセル」に入ったクラブ番号を認識して、

この青山さんの行のE列のクラブ番号と一致していれば、このセルの行番号を表すというものです。

難しいですね・・・。

スクリーンショット (438)

「クラブごと印刷」のA列に先程の行番号を小さい数順に並べてあるため、

この行番号を利用して名前や学級番号を探しだします。

(index)関数で「列:全体名簿の名前の列のC、行:行番号であるA列」を読み込んでいます。

「学級ごと名簿」

スクリーンショット (439)

「学級ごと印刷」も仕組みは「クラブごと印刷」と同じです。

スクリーンショット (440)

青いセルに入力する学級名を変えることで、児童の名前とクラブが変わります。

スクリーンショット (442)

フィルターで並びかえることで、「屋外スポーツをしている児童だけ表示」等も可能です。

スクリーンショット (444)

「クラブごと印刷」と同じようにA列にに関数が隠れていて、

スクリーンショット (445)

「全体名簿」のH列から反映させています。

関数もさっきと同じで、「学級ごと印刷」の青いセルに入力された学級名に応じて、H列に行番号が現れます。




如何でしたでしょうか?

関数や仕組みが難しかったかもしれませんが、少しでも理解しようと調べたり、Excelを触ってみたりしていただけたら幸いです。

そのExcel、PCの技能は必ず役に立つでしょう♪

下のダウンロードから取って、ぜひ使ってみてください!

そして、自分で使いやすいよう編集していただければと思います。

プロフィール
この記事を書いた人
さくさく先生

iPad活用×学びあいを実践する小学校教師です。
研修や本で得た知識、経験をもとに、自分なりにまとめています。
参考にしていただけたら嬉しいです♪

さくさく先生をフォローする
Excel
さくさく先生ブログ

コメント

タイトルとURLをコピーしました