全部無料登録不要・名簿はあなたの端末の中だけ・Excel / スプレッドシート / Forms / Teams に書き出し
時短ワザ大全

スプレッドシートの時短ワザ(26)

Googleスプレッドシートならではの関数と共同編集。QUERY・FILTER・IMPORTRANGE・ARRAYFORMULA。

校種

01QUERY で「1組だけ・名前順」を1本の式で

フォームの回答から組ごとの一覧を作るのに、毎回フィルタと並べ替えをしている。

QUERY は「どの列を・どんな条件で・どの順に」を文で書ける関数です。回答が増えても自動で更新されるので、組ごとのタブに1本ずつ置いておけば担任用の一覧がいつも最新。列は A・B・C の記号で指定します。

=QUERY('フォームの回答 1'!A:F,"select B,C,E where D = '1組' order by C",1)
=QUERY('フォームの回答 1'!A:F,"select D, count(B) where B is not null group by D",1)

QUERY はスプレッドシートだけの関数(Excel にはありません)。文字の条件は '1組' のように一重引用符、数値は where E >= 60 のように引用符なし。2行目は組ごとの回答数。

02IMPORTRANGE で共有シートから自分の組だけ

学年の共有シートを開くたびに、自分の組の行を探してコピーしている。

IMPORTRANGE で別のファイルの範囲を読み込み、QUERY で自分の組だけ取り出します。元の共有シートには触れないので壊す心配がなく、元が更新されれば自分のシートも自動で更新されます。

=QUERY(IMPORTRANGE("共有シートのURL","名簿!A:H"),"select * where Col3 = '2組'",1)

初めて使うときはセルに出る「アクセスを許可」を押します。IMPORTRANGE を QUERY に入れると、列の指定が A・B ではなく Col1・Col2… になる点に注意。スプレッドシートだけの関数です。

小中高大学

03ARRAYFORMULA で式を自動で下まで伸ばす

フォームの回答が増えるたびに、計算の式を下の行までコピーし直している。

見出し行に ARRAYFORMULA を1つ書くと、列全体がまとめて計算されます。新しい回答が入った行にも自動で結果が出るので、コピーし忘れが起きません。下の式は1行目に置くと見出し「判定」も一緒に出る書き方です。

=ARRAYFORMULA(IF(ROW(A:A)=1,"判定",IF(B:B="","",IF(C:C>=60,"合格","再テスト"))))

スプレッドシートの関数。Excel(Microsoft 365)では普通の式が自動であふれ出す(スピル)ので不要です。回答シートに直接書かず、別シートで回答を参照して計算すると安全。

小中高大学

04チェックボックスで提出チェックし自動で数える

提出物を「○」「×」の文字で記録していて、提出数が数えにくい。

範囲を選んで「挿入 → チェックボックス」。チェックすると TRUE、外すと FALSE がセルに入ります。COUNTIF で TRUE を数えれば提出数、条件付き書式に =$B2=FALSE で未提出に色。選んだボックスはスペースキーでまとめてオン・オフできます。

=COUNTIF(B2:B41,TRUE)
=COUNTIF(B2:B41,FALSE)

Microsoft 365 の Excel も「挿入 → チェックボックス」でセルに入れられるようになりました(2024年からの新機能)。古い Excel の「開発」タブのチェックボックスとは別物です。

05SPARKLINE でセルの中に点数の推移グラフ

一人ひとりの点数の伸びを見たいが、40人分のグラフは作れない。

SPARKLINE はセルの中に小さな折れ線や棒グラフを描く関数です。名簿の各行の右端に置けば、表を眺めるだけで「伸びている子・下がっている子」が分かります。面談の資料にもそのまま使えます。

=SPARKLINE(C2:L2)
=SPARKLINE(C2:L2,{"charttype","column";"ymin",0;"ymax",100})

スプレッドシートの関数。Excel は関数ではなく「挿入 → スパークライン」のメニューで同じものが作れます。2行目は縦軸を0〜100点に固定した棒グラフ。

06GOOGLETRANSLATE でお便りの要点を多言語に

外国につながる家庭向けに、持ち物や日時を訳すのが大変。

=GOOGLETRANSLATE(A2,"ja","en") で隣の列に機械翻訳が出ます。言語コードを pt(ポルトガル語)、vi(ベトナム語)、tl(フィリピノ語)、es(スペイン語)、zh-CN(中国語・簡体字)に変えれば一覧表に。短い定型の文(持ち物・日時・場所)に向いています。

=GOOGLETRANSLATE(A2,"ja","en")
=GOOGLETRANSLATE(A2,"ja","pt")

機械翻訳なので、大事な連絡は人の確認を。元の文を短く主語のはっきりした「やさしい日本語」にすると精度が上がります。個人名や家庭の事情は入れないこと。スプレッドシートだけの関数です。

07「編集履歴を表示」で誰がいつ変えたか分かる

共有シートの数字が知らないうちに変わっていて、原因が分からない。

セルを右クリック →「編集履歴を表示」で、そのセルの前の値・変えた人・日時が見られます。ファイル全体は「ファイル → 変更履歴 → 変更履歴を表示」(Ctrl+Alt+Shift+H)から、好きな時点に戻すことも。成績確定などの節目には「現在の版に名前を付ける」で印を付けておくと安心です。

Excel も OneDrive/SharePoint 上のファイルなら「校閲 → 変更内容を表示」や「バージョン履歴」で確認できます。

小中高大学

08「フィルタ表示」で他の人の画面を乱さない

共有シートでフィルタをかけたら、他の先生の画面まで絞り込まれてしまった。

普通のフィルタは全員の画面に効きますが、「フィルタ表示」は自分の画面だけに効きます。名前を付けて保存でき、そのときのURLを渡せば同じ絞り込みを他の先生にも見せられます。「1年1組だけ」「未提出だけ」などを保存しておくと便利。

  1. データ →「フィルタ表示を作成」
  2. 条件を設定し、上の欄に名前を付ける
  3. 次からは データ →「フィルタ表示」から選ぶ

Excel for the web の共同編集では「表示 → シート ビュー」が近い機能です。

小中高大学

09共有シートの更新をメールで知らせてもらう

学年の共有シートに誰か記入したか、何度も開いて確かめている。

スプレッドシートの「ツール → 通知設定」で通知ルールを作ると、シートが変更されたときや、つながっているフォームに回答があったときにメールが届きます。「すぐに」か「1日1回まとめて」を選べるので、通知が多すぎることもありません。

通知は自分のアドレスにだけ届きます。チェックが要らなくなったらルールを削除しておきましょう。

小中高大学

10重複の削除・余分な空白の削除はメニュー一発

何度も貼り足した名簿に、重複や余計なスペースが残っている。

範囲を選んで「データ → データクリーンアップ → 重複を削除」。どの列で重複を判定するか選べます。同じメニューの「空白文字を削除」で前後の余分なスペースも一度に消せます。元の表を残したいなら UNIQUE 関数で重複のない一覧を別に作ります。

=UNIQUE(A2:A)
=COUNTUNIQUE(A2:A)

Excel は「データ → 重複の削除」。UNIQUE は Excel 2021/Microsoft 365 にもあります(COUNTUNIQUE はスプレッドシートだけ)。

11「名前付き関数」で学校独自の関数を作る

長い評定の式を、学期ごとにコピーして直すたびに壊してしまう。

「データ → 名前付き関数」で、よく使う長い式に名前を付けて自分だけの関数にできます。たとえば =HYOUTEI(B2) と書くだけで学校の基準の評定が返るように。基準が変わっても直すのは1か所だけで、ひな形に入れて配ることもできます。

関数名:HYOUTEI 引数のプレースホルダ:score
数式の定義:=XLOOKUP(score,{0,30,50,65,80},{1,2,3,4,5},"",-1)

Excel(Microsoft 365)は「数式 → 名前の管理」で LAMBDA を使って同じことができます(例:=LAMBDA(score,XLOOKUP(score,…)))。

12「表に変換」で列ごとの入力ルールを一括設定

記録表の列ごとに、日付やプルダウンの設定をするのが面倒。

範囲を選んで「表示形式 → 表に変換」(Ctrl+Alt+T)。列ごとに「プルダウン」「日付」「チェックボックス」などの種類を選べ、合わない値を入れると警告が出ます。見た目も自動で整い、行の追加や並べ替えもしやすくなります。

Excel の「テーブル」(Ctrl+T)に近い機能です。比較的新しい機能なので、メニューにないときは学校の環境での提供を待つか、管理者に確認を。

小中高大学

13プルダウンの選択肢ごとに色を付ける

「済・未・保留」を文字で入れていて、ぱっと見で状況が分からない。

「挿入 → プルダウン」で選択肢を作ると、選択肢ごとに色を選べます。「出席=緑・欠席=赤・遅刻=黄」のようにしておけば、表全体の状況が色で一目瞭然。選択肢は「範囲内のプルダウン」で名簿などの列から作ることもできます。

Excel は入力規則(リスト)と条件付き書式を組み合わせると同じことができます。

14大事な範囲は「警告を表示」で守る

共有シートの見出しや式を守りたいが、編集できる人を細かく管理するのは大変。

「データ → シートと範囲を保護」で範囲を選び、権限の設定で「この範囲を編集する際に警告を表示する」を選びます。編集はできますが、触ろうとすると確認が出るので、うっかりミスだけを防げます。人ごとに権限を決めるより運用がずっと楽です。

確実に守りたい範囲(成績の確定値など)は「この範囲を編集できるユーザーを制限する」を選びます。

小中高大学

15正答率の濃淡を「カラースケール」で一目に

問題別の正答率の表を見ても、どこでつまずいたかが数字だけでは分からない。

範囲を選んで条件付き書式の「カラースケール」を当てると、値の小さい所と大きい所が自動で濃淡に塗り分けられます。問題別の正答率の表に使えば、学級全体でつまずいた問題が色の帯になって浮かび上がり、次の授業で取り上げる問題がすぐ決まります。

  1. 点数や正答率の範囲を選ぶ
  2. 表示形式 →「条件付き書式」→「カラースケール」タブ
  3. 色の組み合わせを選び、最小値と最大値を「数値」にして 0 と 100 を入れ、「完了」

最小・最大を固定しないと、その範囲のいちばん低い値・高い値が基準になります。Excel は「ホーム → 条件付き書式 → カラー スケール」。色だけに頼らず数字も残しておくと、色の見分けにくい人にも伝わります。

16「1年2組」「1-2」を正規表現で「1-2」に

フォームの「学年・組」の欄が人によって書き方ばらばらで、組ごとの集計が合わない。

ASC で全角の数字や記号を半角にしてから、REGEXREPLACE で「最初の数字」と「次の数字」だけを取り出し、「1-2」の形に組み直します。「2年 3組」「2-3」なども同じ形になるので、QUERY や COUNTIF で組ごとに数えられるようになります。

=REGEXREPLACE(ASC(A2),"^\D*(\d+)\D+(\d+)\D*$","$1-$2")
=REGEXMATCH(ASC(A2),"^\d+-\d+$")

2行目は「1-2」の形になっているかを TRUE/FALSE で調べる式。数字が2つ見つからないときは、元の文字がそのまま返ります。スプレッドシートの関数です(Excel は Microsoft 365 の新しい版で REGEXREPLACE などが使えます)。次からはフォームをプルダウンで選ばせる形にすると、ばらつきそのものを防げます。

17学期ごとの列を「グループ化」でたたむ

1年分の記録を横に並べたら列が多すぎて、今の学期の欄までスクロールが大変。

1学期の列をまとめて選んで「グループ化」すると、列の上に −/+ のボタンが付き、ワンクリックでたたんだり広げたりできます。ただの非表示と違って、どこをたたんでいるかが一目で分かるので、共有したほかの先生も迷いません。行にも使えます。

  1. たたみたい列(例:1学期分)を列の見出しの所で選ぶ
  2. 表示 →「グループ化」→「列をグループ化」
  3. 列の上に出る −/+ で、たたむ・広げる

Excel は「データ → グループ化」。たたんだ列も計算にはそのまま入っています。

18見るだけの共有は「ダウンロード・印刷・コピー」をオフ

見るだけのつもりで共有した名簿が、ダウンロードやコピーをされないか心配。

共有の画面の歯車(設定)で、閲覧者と閲覧者(コメント可)にダウンロード・印刷・コピーを許す設定のチェックを外します。見るだけの人は、メニューからファイルを持ち出せなくなります。共有の相手は必要な人だけを名前で追加し、「リンクを知っている全員」にはしないのが基本です。

  1. 右上の「共有」を押す
  2. 共有の画面の右上の歯車を押す
  3. 閲覧者のダウンロード・印刷・コピーを許す項目のチェックを外す
  4. 相手の権限が「閲覧者」になっているかを確かめて閉じる

画面を撮影するなど、ほかの方法での持ち出しまでは防げません。共有フォルダの中のファイルは、フォルダの権限が優先されることがあります。ドキュメントやスライドでも同じ設定ができます。名簿の共有の範囲は学校の規程に従ってください。

20プルダウンを「複数選択」できるようにする

当てはまるものを全部選んでほしい欄に、「、」区切りで手入力してもらっている。

「@」から作るチップ形式のプルダウンは、設定で「複数選択できるようにする」をオンにすると、1つのセルで選択肢をいくつでも選べます。参加できる日、得意な楽器、係の希望などを、表記がそろったまま記録できます。

  1. セルを選んで「@」と入力 →「プルダウン」
  2. 右に出る設定の画面で選択肢を入力する
  3. 「複数選択できるようにする」をオンにして「完了」
=COUNTIF(B2:B41,"*合唱*")

選んだ値はカンマ区切りの文字としてセルに入るので、数えるときは上の式のように「*」を使います。複数選択にできるのはチップ形式のプルダウンだけです。Excel の入力規則のリストは1つしか選べません。

21締切を過ぎた未提出の行だけ赤くする

提出物の締切日と提出状況を並べているのに、遅れている物に気づくのが遅い。

条件付き書式のカスタム数式で、「締切日が今日より前」で「チェックが入っていない」行を赤にします。TODAY を使うので、毎朝開くだけで色が自動で変わります。「あと3日以内」を黄色にするルールも足せば、声をかけるタイミングも分かります。

=AND($C2<>"",$C2<TODAY(),$D2=FALSE)
=AND($C2<>"",$C2>=TODAY(),$C2-TODAY()<=3,$D2=FALSE)

C列が締切日、D列が提出のチェックボックスの例。表全体を選んでから「表示形式 → 条件付き書式 → カスタム数式」に入れます。Excel でも同じ式が使えます(チェックボックスの代わりに「済」と入れる表なら $D2="" に)。

22貼り付けた「番号,名前,組」を一発で列に分ける

システムから書き出した文字を貼ったら、全部が A列の1つのセルに入ってしまった。

範囲を選んで「データ → テキストを列に分割」を選ぶと、カンマやスペースなどの区切りで自動的に列に分かれます。うまく分かれないときは、出てくる「区切り文字」のメニューで種類を選び直します。関数で分けたいなら SPLIT を使います。

=SPLIT(A2,",")

分けた結果は右の列に上書きされるので、右側を空けておきます。「01」のような先頭の0は数値になって消えることがあります。Excel は「データ → 区切り位置」。名簿のデータは学校の規程に従って扱ってください。

23「@」で日付とファイルを入れる会議の一覧

会議の資料のリンクが長い URL のまま並び、どれが何の資料か分からない。

セルで「@」と打つと、日付やファイルなどの「スマートチップ」を入れられます。ドライブのファイルのリンクを貼って Tab を押すと、ファイル名のチップに変わり、マウスを乗せると中身のプレビューが出ます。「会議日・議題・資料」の一覧がすっきりします。

  1. 日付の欄で「@」と打ち、「日付」を選んで日付を入れる
  2. 資料の欄にドライブのファイルのリンクを貼り付け、Tab キーを押す
  3. チップにマウスを乗せると、ファイルのプレビューが出る

チップから開けるのは、そのファイルを見る権限がある人だけです。メールアドレスを貼って Tab を押すと、ユーザーのチップになります。スプレッドシートの機能です。

24座席表にいない子を TOCOL と COUNTIF で探す

席替えのあと、座席表に書き忘れた子や二重に書いた子がいないか目で探している。

TOCOL は縦横の表を1列に並べ直す関数で、座席表を名前の一覧にできます。さらに名簿と COUNTIF で突き合わせると、座席表に1回も出てこない子(2つ目の式)や、2回以上出てくる子(3つ目の式)だけを FILTER で取り出せます。

=SORT(TOCOL(B2:G7,1))
=FILTER(名簿!B2:B41,COUNTIF(B2:G7,名簿!B2:B41)=0)
=FILTER(名簿!B2:B41,COUNTIF(B2:G7,名簿!B2:B41)>1)

TOCOL の2つ目の 1 は「空白を飛ばす」。TOCOL はスプレッドシートと Microsoft 365/Excel 2024 で使えます。当てはまる子がいないとエラー(#N/A や #CALC!)になるので、IFERROR(…,"なし") で包むと見やすくなります。名前の空白の全角・半角がそろっていないと別の人として扱われます。

25備品や教材の写真をセルの中に入れて一覧に

理科室の器具や体育倉庫の備品の一覧が文字だけで、どれのことか分かりにくい。

「挿入 → 画像」で「セル内に画像を挿入」を選ぶと、画像がセルの中に収まり、並べ替えやフィルタをしても行と一緒に動きます。器具の写真つきの備品台帳や、楽器・用具の置き場所の一覧が作れます。インターネット上の画像なら IMAGE 関数でも入れられます。

=IMAGE("画像のURL")
=IMAGE("画像のURL",4,60,80)

2行目は高さ60・幅80ピクセルで表示する書き方。IMAGE 関数は Google ドライブに置いた画像の URL には使えず、SVG も表示できません。画像を入れたセルには文字を入力できません。子どもの顔が写った写真は、共有の範囲が広い表に入れないでください。

26SEQUENCE で1か月分の日付を一発で並べる

出席簿や当番表を毎月作るたびに、1日から31日まで日付を打ち直している。

月の初日を1つのセルに入れ、SEQUENCE で「その月の日数」分だけ日付を並べます。日数は EOMONTH(月末の日)から決まるので、30日の月も2月もそのまま。初日を変えるだけで来月の表になります。土日を色分けする条件付き書式と組み合わせると、毎月の表づくりがすぐ終わります。

=SEQUENCE(1,DAY(EOMONTH(A1,0)),A1)
=SEQUENCE(DAY(EOMONTH(A1,0)),1,A1)

A1 に月の初日(例:2026/11/1)を入れます。1行目は横に、2行目は縦に並べる式。数字で表示されたら表示形式を日付(例:d)にします。SEQUENCE は Microsoft 365/Excel 2021 以降とスプレッドシートで使えます。