提出チェック表の空欄に、一つずつ「未」や「0」を打ち込んでいる。
Excel には「空白のセルだけを選ぶ」機能があります。空欄をまとめて選んだら、文字を打って Ctrl+Enter。選ばれた全部のセルに同じ値が一度に入ります。40人×10回分の空欄でも数秒。欠席者の点数欄に「欠」を入れるときにも使えます。
- 表の範囲を選ぶ
- F5(または Ctrl+G)→「セル選択」→「空白セル」→ OK
- そのまま「未」と入力する
- Enter ではなく Ctrl+Enter で確定
Excel の機能です。スプレッドシートには「空白セルだけを選ぶ」機能がないので、隣の列に =IF(B2="","未",B2) を入れて置き換えます。
未提出の子を名簿から拾い出して、連絡帳や Teams に書き写している。
FILTER で「提出欄が空」の人だけを取り出し、TEXTJOIN で「、」区切りの1つの文にまとめます。そのままコピーして声かけメモや投稿に貼れます。提出されるたびに名前が自動で消えていくので、作り直す必要がありません。
=TEXTJOIN("、",TRUE,FILTER(B2:B41,C2:C41=""))
=IFERROR(TEXTJOIN("、",TRUE,FILTER(B2:B41,C2:C41="")),"全員提出")
Excel 2021/Microsoft 365 とスプレッドシートで使えます。全員が提出済みになると Excel は #CALC!、スプレッドシートは #N/A を返すので、2行目のように IFERROR で包んでおくと安心です。
1〜3月の記録を前の年度として数えたいのに、YEAR関数だと翌年になる。
日付を3か月前に戻してから年を取り出すと、4月始まりの「年度」になります。1月10日なら3か月前は10月10日なので、前の年=正しい年度。IF で月を判定するより短く、年度別の集計や入学年度の計算にそのまま使えます。
=YEAR(EDATE(A2,-3))
=YEAR(EDATE(A2,-3))&"年度"
Excel・スプレッドシート共通。4月1日〜翌3月31日が同じ年度になります。
「80点以上は5、65点以上は4…」を IF の入れ子で書き、毎回崩れる。
XLOOKUP の5つ目の引数(一致モード)を -1 にすると「ちょうどの値、なければ1つ下の値」を探します。境目の点数を小さい順に並べておけば、基準が変わっても数字を直すだけ。境目の表をセルに作れば、ほかの先生にも基準が一目で分かります。
=XLOOKUP(B2,{0,30,50,65,80},{1,2,3,4,5},"",-1)
=XLOOKUP(B2,基準!$A$2:$A$6,基準!$B$2:$B$6,"",-1)
XLOOKUP は Excel 2021/Microsoft 365 とスプレッドシートで使えます。古い Excel は =VLOOKUP(B2,基準表,2,TRUE)(基準表の左の列は小さい順)。境目の数字は学校の基準に合わせてください。
「AAB なら4」のような学校の対応表を見ながら、1人ずつ評定を付けている。
3観点を =C2&D2&E2 で「AAB」のような1つの文字列にし、学校の対応表から XLOOKUP で引きます。並び順の違い(ABA と AAB)を同じに扱う学校なら、A と B の個数で「2A1B」のような鍵を作る方式が便利。表にない組み合わせは「要確認」と出るので見落としません。
=XLOOKUP(C2&D2&E2,対応表!A:A,対応表!B:B,"要確認")
=COUNTIF(C2:E2,"A")&"A"&COUNTIF(C2:E2,"B")&"B"
対応表は学校で決めた基準をそのまま使ってください。2行目の式で作った鍵(例:2A1B)を対応表の左の列に並べれば、並び順に関係なく引けます。
自分のテストでも偏差値を出したいが、式の作り方が分からない。
偏差値は「50+10×(得点−平均)÷標準偏差」。(得点−平均)÷標準偏差の部分は STANDARDIZE が計算してくれます。順位は RANK.EQ。平均と標準偏差は空欄(欠席者)を自動で除いてくれるので、本人の欄だけ IF で空欄にしておきます。
=IF(B2="","",50+10*STANDARDIZE(B2,AVERAGE($B$2:$B$41),STDEV.P($B$2:$B$41)))
=IF(B2="","",RANK.EQ(B2,$B$2:$B$41,0))
Excel・スプレッドシート共通。学年全体を「全員」として見るなら STDEV.P、より大きな集団の一部とみなすなら STDEV.S。
中高大学
名簿や提出の記録に、同じ人が二重に入っていても気づけない。
条件付き書式に「COUNTIF で数えて2以上なら色を付ける」数式を入れます。番号でも名前でも使え、入力した瞬間に赤くなるので、二重の提出や転記ミスがその場で分かります。消すべき行も一目で見つかります。
- A2から下を選ぶ
- ホーム →「条件付き書式」→「新しいルール」→「数式を使用して、書式設定するセルを決定」
- 下の式を入れ、「書式」で塗りつぶしの色を選ぶ
=COUNTIF($A$2:$A$200,A2)>1
スプレッドシートは「表示形式 → 条件付き書式 →(書式ルール)カスタム数式」に同じ式を入れます。Excel には「セルの強調表示ルール → 重複する値」という簡単メニューもあります。
「点数が30点未満の生徒の行」に色を付けたいのに、1マスしか色が変わらない。
表全体を選んでから、条件付き書式の数式で「列だけ」を $ で固定します。=$E2<30 の $E は「いつもE列を見る」、行の 2 に $ がないので「行ごとに判定する」という意味。これで条件を満たした行が端から端まで色づきます。
=$E2<30
=AND($E2<>"",$E2<30)
空欄は 0 とみなされて色が付くので、空欄を除くなら2行目の式を。Excel・スプレッドシート共通。
行事予定表や出席簿の土日を、毎月手で塗っている。
条件付き書式で WEEKDAY を使います。WEEKDAY は日曜が1、土曜が7。祝日や休業日は別シートに日付を並べておき、COUNTIF で「一覧にあれば色」。曜日の文字は TEXT 関数で表示できます。月が変わっても日付を変えるだけで色が付き直ります。
=WEEKDAY($A2)=1
=WEEKDAY($A2)=7
=COUNTIF(祝日!$A:$A,$A2)>0
=TEXT(A2,"aaa")
曜日の書式は Excel が "aaa"(月)/"aaaa"(月曜日)、スプレッドシートは "ddd"(月)/"dddd"(月曜日)。スプレッドシートの条件付き書式は別シートを直接参照できないので、=COUNTIF(INDIRECT("祝日!A:A"),$A2)>0 と書きます。
「終業式まで授業はあと何日?」を、カレンダーを見ながら指で数えている。
土日と、別シートに並べた祝日・休業日を除いて日数を数えます。3つ目の引数を "0000011"(月曜から日曜の順に、1が休み)のように書けば、土曜授業がある学校などにも合わせられます。テスト範囲の計画や時数の見通しに便利。
=NETWORKDAYS.INTL(TODAY(),B1,1,休日!$A$2:$A$60)
=NETWORKDAYS.INTL(A1,B1,"0000001",休日!$A$2:$A$60)
開始日と終了日も数に入ります。2行目は「日曜だけ休み」の例。休日の一覧には日付だけを入れてください。Excel・スプレッドシート共通。
出席番号に全角や「01」が混ざり、並べ替えや検索がうまく合わない。
ASC で全角の英数字を半角にし、VALUE で数値に変えます。逆に「01」の0を残したいときは TEXT(A2,"00")。名前は全角スペースを SUBSTITUTE で半角にそろえ、TRIM で前後の空白を消しておくと、XLOOKUP の「見つからない」がぐっと減ります。
=VALUE(ASC(A2))
=TEXT(A2,"00")
=TRIM(SUBSTITUTE(B2," "," "))
ASC はカタカナも半角にしてしまうので、名前の列には使わず番号の列だけに。ASC は Excel・スプレッドシートどちらにもあります。
氏名が1つのセルに入っていて、姓だけ・名だけの列が作れない。
Microsoft 365 なら TEXTSPLIT で一発です。古い版でも、隣の列に1人分だけ姓を手で打ってから Ctrl+E(フラッシュフィル)を押すと、残りの行を真似して埋めてくれます。全角スペースを先に半角にそろえておくと確実です。
=TEXTSPLIT(SUBSTITUTE(A2," "," ")," ")
=LEFT(A2,FIND(" ",SUBSTITUTE(A2," "," "))-1)
TEXTSPLIT は Microsoft 365/Excel 2024。スプレッドシートは =SPLIT(SUBSTITUTE(A2," "," ")," ")。フラッシュフィルは Excel の機能で、スプレッドシートでは「スマートフィル」が候補を出してくれます。
名簿の「ふりがな」の列を、一人ずつ打ち直している。
Excel で漢字を入力すると、変換する前の読みが一緒に記録されています。=PHONETIC(A2) でその読みを取り出せます。読みが違う人だけ Alt+Shift+↑(ふりがなの編集)で直せばOK。ひらがなにしたいときは、ホームの「ふりがな」ボタン横の▼→「ふりがなの設定」で種類を変えます。
=PHONETIC(A2)
コピーや CSV で入れた名前には読みの情報がないため、漢字のまま返ってきます(その場合は Alt+Shift+↑ で読みを入れる)。スプレッドシートには PHONETIC 関数はありません。
プルダウンの候補が多すぎて、毎回スクロールして探している。
科目の一覧の列に「国語」「数学」…と教科名の名前(名前付き範囲)を付け、2つ目のプルダウンの元の値を =INDIRECT(A2) にします。A2 で選んだ教科と同じ名前の範囲が、そのまま2つ目の候補になります。
- 別シートに、教科ごとに科目を縦に並べる(国語の列、数学の列…)
- 各列の科目部分を選び、左上の名前ボックスに「国語」のように入力して Enter
- A列:データ →「データの入力規則」→ 入力値の種類「リスト」→ 元の値「国語,数学,英語」
- B列:同じく「リスト」→ 元の値に =INDIRECT(A2)
名前は数字から始められません(「1年」などは不可)。この方法は Excel 向けです。
中高
共有した成績表の式を、誰かがうっかり消してしまう。
入力してほしいセルだけ「ロック」を外してから、シートを保護します。式や見出しは書き換えられなくなり、Tab キーで入力欄だけを順に移動できるようになります。うっかり防止が目的なら、パスワードは空欄でも十分です。
- 入力欄を選び Ctrl+1 →「保護」タブ →「ロック」のチェックを外す
- 校閲 →「シートの保護」→ OK
- 直したいときは 校閲 →「シート保護の解除」
スプレッドシートは「データ → シートと範囲を保護」で、範囲ごとに編集できる人を決める方式です。
くじ引きの班分けのために、紙を切ってくじを作っている。
RANDARRAY で人数分の乱数を作り、それを鍵に名簿を並べ替えます。並んだ順に「4人ずつ1班」の番号を SEQUENCE で振れば完成。F9 を押すたびに並びが変わるので、決まったら「値の貼り付け」で固定します。
=SORTBY(A2:A41,RANDARRAY(ROWS(A2:A41)))
=ROUNDUP(SEQUENCE(40)/4,0)
SORTBY・RANDARRAY・SEQUENCE は Excel 2021/Microsoft 365。スプレッドシートは =SORT(A2:A41,RANDARRAY(ROWS(A2:A41)),TRUE)。「この2人は離す」などの条件があるなら班分けアプリへ。
子ども向けの座席表と、教卓から見た座席表を二重に作っている。
INDEX で行と列を「後ろから」読むと、180度回転した表ができます。元の座席表を1か所直せば、反転した表も自動で変わります。下の式を反転表の左上に入れ、元の表と同じ大きさまでコピーするだけ。
=INDEX($B$2:$G$7,ROWS($B$2:$G$7)-ROW(A1)+1,COLUMNS($B$2:$G$7)-COLUMN(A1)+1)&""
$B$2:$G$7 は元の座席表の範囲に合わせて変えます。末尾の &"" は空席を 0 ではなく空欄にするため。Excel・スプレッドシート共通。
「80点台は何人?」を数えながら、分布表を手で作っている。
COUNTIFS は条件を何組でも並べられます。「下限以上」と「上限未満」の2つの条件で区間の人数を数え、組や男女の条件も足せます。区間の境目をセル(E列・F列)に書いておけば、式は1本をコピーするだけ。境目を変えれば表も変わります。
=COUNTIFS($B$2:$B$200,">="&E2,$B$2:$B$200,"<"&F2)
=COUNTIFS($C$2:$C$200,"1組",$D$2:$D$200,5)
Excel・スプレッドシート共通。この表から棒グラフを作れば、区間を自由に決めたヒストグラムになります。
所見欄に収まるかどうか、一人ずつ文字数を数えている。
LEN で文字数を数え、条件付き書式で上限を超えたセルを赤くします。セル内の改行も1文字に数えられるので、改行を除きたいときは SUBSTITUTE で消してから数えます。全員分を並べれば、長すぎる・短すぎる所見が一目で分かります。
=LEN(B2)
=LEN(SUBSTITUTE(B2,CHAR(10),""))
=LEN($B2)>150
句読点や空白も1文字。3行目は条件付き書式に入れる式(150字を超えたら色)。Excel・スプレッドシート共通。
縦割り班や委員会の名簿に生年月日しかなく、学年を一人ずつ確かめている。
早生まれ(1月1日〜4月1日生まれ)は、前の年に生まれた子と同じ学年です。生年月日を1日戻してから3か月戻すと、同じ学年の子が同じ「年」にそろいます。その年を今の年度から引けば学年が出るので、異学年の混ざった名簿でも学年の列をまとめて作れます。
=YEAR(EDATE(B2-1,-3))
=$E$1-YEAR(EDATE(B2-1,-3))-6
=$E$1-YEAR(EDATE(B2-1,-3))-12
E1 に年度(例:2026)を入れます。2行目が小学校の学年、3行目が中学校の学年(高校は -15)。4月1日生まれは前の学年になり、この式もそのとおりの結果になります。結果が日付で出たら表示形式を「標準」に。Excel・スプレッドシート共通。生年月日は個人情報なので、学校の規程に従って扱い、外部のサービスや生成AIには入れないでください。
「定期考査7割・小テスト2割・提出物1割」の計算を、電卓で一人ずつしている。
SUMPRODUCT は「掛けてから足す」関数です。2行目に重み(70・20・10 など)を並べておけば、「点数×重み」の合計を重みの合計で割るだけで、100点満点の総合点になります。割合を変えるときは2行目の数字を直すだけで、全員分が計算し直されます。
=SUMPRODUCT(C3:E3,$C$2:$E$2)/SUM($C$2:$E$2)
=ROUND(SUMPRODUCT(C3:E3,$C$2:$E$2)/SUM($C$2:$E$2),1)
1行目に見出し、2行目に重み、3行目から生徒の例。どの列も100点満点にそろえてから使います(満点が違うときは先に換算)。空欄は0点として計算されるので、欠席者の扱いは学校の決まりに合わせます。割合そのものは評価の規準に従ってください。Excel・スプレッドシート共通。
学年の成績一覧を組ごとに並べ替えては、平均や最高点を計算し直している。
AVERAGEIFS・MAXIFS・MINIFS は「条件に合う行だけ」で平均・最大・最小を出す関数です。G列に「1組」「2組」…と並べて式を下にコピーすれば、組別の一覧表になります。空欄(欠席)は平均から自動で外れるので、成績会議の資料がすぐに作れます。
=AVERAGEIFS($D$2:$D$200,$B$2:$B$200,$G2)
=MAXIFS($D$2:$D$200,$B$2:$B$200,$G2)
=MINIFS($D$2:$D$200,$B$2:$B$200,$G2)
B列が組、D列が点数の例。MAXIFS・MINIFS は Excel 2019 以降・Microsoft 365 とスプレッドシートで使えます。「1組」と「1組」のように表記がそろっていないと別の組として数えられます。成績の表は学校の規程に従って保存・共有してください。
小数第1位まで表示した平均を足したら、表に見えている数字と合計が合わない。
「小数点以下の表示桁数を減らす」ボタンは見た目を丸めるだけで、セルの中には元の値が残っています。そのまま足したり比べたりすると、見えている数字とずれます。評定平均や換算点のように丸めた値そのものを使う欄は、ROUND で本当に丸めておきます。切り上げは ROUNDUP、切り捨ては ROUNDDOWN です。
=ROUND(AVERAGE(C2:L2),1)
=ROUNDUP(B2*0.8,0)
=ROUNDDOWN(B2/3,1)
2つ目の引数は残す小数の桁数(0なら整数、1なら小数第1位まで)。どの桁で丸めるかは、調査書の記載要領など学校の決まりに合わせてください。Excel・スプレッドシート共通。
保健室の来室記録や相談の記録を、月ごと・理由ごとに数え直して報告を作っている。
1行に1件ずつ記録した表があれば、ピボットテーブルで「行に月・列に理由・値に件数」の集計表がドラッグだけでできます。日付は月ごとにまとめられ、記録を足したら「更新」を押すだけ。学校保健委員会の資料や月ごとの報告づくりが楽になります。
- 表の中のセルを1つ選び、挿入 →「ピボットテーブル」→ OK
- 右のフィールドの一覧で「日付」を行、「理由」を列へ、もう一度「理由」を値へドラッグ(件数になる)
- 月にまとまらないときは、日付のセルを右クリック →「グループ化」→「月」
- 記録を足したら、ピボットテーブルを右クリック →「更新」
元の表は見出し1行+1件1行にし、空の行や結合したセルをなくしておきます。スプレッドシートは「挿入 → ピボット テーブル」。来室記録は健康に関わる個人情報なので、保存場所と共有の範囲は学校の規程に従い、外部のサービスや生成AIに入れないでください。
担任が組ごとのシートに入れた記録を、学年でまとめるたびにコピーして貼り合わせている。
VSTACK は複数の範囲を縦につなげる関数です。各組のシートの同じ範囲を並べて書くだけで、学年全体の一覧が1つの式でできます。担任が組のシートを直せば、まとめのシートも自動で変わります。2行目の式なら、名前が空の行を除いてつなげます。
=VSTACK('1組'!A2:D41,'2組'!A2:D41,'3組'!A2:D41)
=LET(a,VSTACK('1組'!A2:D41,'2組'!A2:D41,'3組'!A2:D41),FILTER(a,ISTEXT(INDEX(a,0,2))))
VSTACK・LET は Microsoft 365/Excel 2024 とスプレッドシートで使えます。2行目は B列(名前)に文字が入っている行だけを残す式。各組のシートは列の並びをそろえておきます。名簿の入ったファイルは学校の規程に従って扱ってください。
文書や行事予定表に、和暦と曜日を毎回手で打ち込んでいる。
セルの表示形式(Ctrl+1 →「表示形式」→「ユーザー定義」)に書式の記号を入れると、入力は 2026/11/9 のままで和暦や曜日つきに表示されます。ggg が元号、e が和暦の年、aaa が曜日。TEXT 関数で同じ記号を使えば、文の中に日付を組み込めます。
ggge"年"m"月"d"日"(aaa)
m"月"d"日"(aaa)
="提出日:"&TEXT(A2,"ggge年m月d日(aaa)")
和暦にならないときは書式の先頭に [$-ja-JP] を付けます。令和元年(2019年)を「元年」と出すには別の書式が必要です(Microsoft サポート「日本の年号変更と元年」)。和暦の書式記号は Excel のものです。スプレッドシートで曜日を出すときは "ddd" を使います。
点数を打つときに「855」や文字を入れてしまい、あとで平均がおかしくなる。
「データの入力規則」で入力できる値を決めておくと、範囲外の数字や文字を入れた瞬間に止めて知らせてくれます。「入力時メッセージ」で「0〜100の半角数字」と案内を出しておけば、共有した表でもほかの先生が迷いません。
- 点数の欄を選ぶ
- データ →「データの入力規則」→「設定」タブで、入力値の種類「整数」、データ「次の値の間」、最小値 0、最大値 100
- 「入力時メッセージ」タブに、入力するときの案内を書く
- 「エラー メッセージ」タブに、はじいたときの文を書いて OK
すでに入っている値はチェックされません。「データの入力規則」の▼にある「無効データのマーク」で、範囲外の値に丸を付けて探せます。スプレッドシートは「データ → データの入力規則」で条件を「次の間にある」にします。
40人の名簿で、横に目を動かすうちに隣の行の子の欄に書き込んでしまう。
条件付き書式に MOD と ROW の式を入れると、「5行ごと」の行だけに書式を付けられます。5番・10番…の下に線を引けば、印刷したチェック表でも行を見失いません。行を足したり並べ替えたりしても、線の位置は自動で保たれます。
- 名簿の範囲(例:A2:H41)を選ぶ
- ホーム →「条件付き書式」→「新しいルール」→「数式を使用して、書式設定するセルを決定」
- 下の1行目の式を入れ、「書式」→「罫線」で下の線を選んで OK
=MOD(ROW()-1,5)=0
=MOD(ROW(),2)=0
1行目が見出しで、2行目から名簿が始まる場合の式です。2行目の式は1行おきの色分け(しま模様)用。スプレッドシートの条件付き書式では罫線を付けられないので、塗りつぶしの色で代わりにします。
右や下にスクロールすると、だれの何の欄なのか分からなくなる。
固定したい行のすぐ下、固定したい列のすぐ右のセル(見出しが1行目、名前がB列までなら C2)を選んで「ウィンドウ枠の固定」を選ぶと、上の行と左の列の両方が止まります。何十列もある成績一覧や出席簿でも、名前と見出しを見ながら入力できます。
- 固定したい行のすぐ下・列のすぐ右のセルを選ぶ(例:C2)
- 表示 →「ウィンドウ枠の固定」→「ウィンドウ枠の固定」
- 元に戻すときは、同じメニューの「ウィンドウ枠固定の解除」
「先頭行の固定」「先頭列の固定」は1行・1列だけの固定です。スプレッドシートは「表示 → 固定」で、行と列の数を別々に選びます。
縦に並んだ名簿を、横に名前が並ぶ記録表へ一人ずつ打ち直している。
コピーしてから「形式を選択して貼り付け」で「行/列の入れ替え」にチェックすると、縦と横が入れ替わって貼り付きます。元の名簿の変更に合わせて自動で変わってほしいなら TRANSPOSE 関数を使います。実技テストの記録表や係の一覧づくりに便利です。
- 名簿の範囲を選んで Ctrl+C
- 貼り付けたい場所を選び、ホーム →「貼り付け」の▼→「形式を選択して貼り付け」
- 「行/列の入れ替え」にチェックして OK
=TRANSPOSE(名簿!B2:B41)
TRANSPOSE は Microsoft 365/Excel 2021 以降とスプレッドシートでは、1つのセルに入れるだけで横に広がります。古い Excel は貼り付ける範囲を先に選び、Ctrl+Shift+Enter で確定します。
出席簿の「欠」「遅」「早」の数を、月末に指で数えている。
COUNTIF は記号ごとの数を数えられます。「*」は「何文字でもよい」という意味なので、"*遅*" なら「遅」「遅早」のように遅を含むセルを全部数えます。忌引や出席停止を「忌」「停」のように別の記号にしておけば、欠席の日数と分けて数えられます。
=COUNTIF(C2:AG2,"欠")
=COUNTIF(C2:AG2,"*遅*")
=COUNTIF(C2:AG2,"忌")+COUNTIF(C2:AG2,"停")
どの記号を何に数えるか(忌引・出席停止の扱いなど)は、学校の出席簿の決まりに合わせてください。全角・半角の違いや余計な空白があると数えもれるので、記号はプルダウンで選ぶ形にしておくと安心です。Excel・スプレッドシート共通。