Excelで利用者名簿や予定表を管理していると、曜日や特定条件に一致する人だけを別シートへ自動表示したい場面があります。毎月人数が増減する場合でも、手作業でコピーする方法では更新のたびに手間がかかります。
Excel 2019では最新のFILTER関数は利用できませんが、INDEX関数・AGGREGATE関数・IF関数などを組み合わせることで、曜日とチェック条件に一致した人だけを自動抽出する表を作成できます。
作成したいExcel表の仕組みを整理する
今回のような名簿自動作成では、元データを入力するシートと、条件に合わせて表示するシートを分けて管理すると便利です。
例えば、シート①を利用者情報の管理用、シート②を曜日別の名簿表示用として使用します。
| シート | 内容 |
|---|---|
| シート① | 名前・利用曜日・対象者チェックを管理 |
| シート② | 指定した曜日の対象者名簿を自動表示 |
シート①の情報を毎月更新しても、シート②は曜日を入力するだけで自動的に対象者一覧を作成できます。
Excel 2019で使う基本的な設定方法
まず、シート①のデータを以下のように配置します。
- B列：名前
- E列：利用曜日（例：月・木）
- H列：対象者の場合は◯
次にシート②のE1セルへ抽出したい曜日を入力します。例えば「月」と入力した場合、月曜日を利用し、なおかつH列が◯の人だけを表示する仕組みにします。
シート②のB3セルには以下のような数式を入力します。
=IFERROR(INDEX(シート①!$B$3:$B$96,AGGREGATE(15,6,(ROW(シート①!$B$3:$B$96)-ROW(シート①!$B$3)+1)/((ISNUMBER(SEARCH($E$1,シート①!$E$3:$E$96)))*(シート①!$H$3:$H$96="◯")),ROW(A1))),"")
入力後、必要な行数まで下方向へコピーすると、条件に一致する名前が順番に表示されます。
数式の意味と動作の仕組み
この数式では、複数の条件を同時に確認しています。
- SEARCH関数：利用曜日の中に指定した曜日が含まれるか確認
- H列の条件：◯が入力されている人だけ対象にする
- AGGREGATE関数：条件に一致した行番号を取得
- INDEX関数：取得した行番号の名前を表示
例えば、E1に「木」と入力した場合、「月・木」と登録されている人も対象になります。曜日が複数登録されている場合にも対応できます。
また、対象者がいなくなった場合はIFERROR関数によって空白表示になるため、エラー表示が出ません。
毎月データが増減する場合の管理方法
毎月利用者が増えたり減ったりする場合は、データ範囲を固定しすぎないことが重要です。
今回のようにB3～B96まで使用する場合でも、余裕を持って範囲を広めに設定しておくと、人数変更時に数式を修正する手間を減らせます。
さらに便利にする場合は、シート①の範囲をExcelの「テーブル」として登録する方法があります。テーブル化すると行が追加されても自動的に範囲が拡張されます。
より簡単に作成するならPower Queryも選択肢
Excel 2019にはPower Queryというデータ抽出機能も搭載されています。大量の名簿管理をする場合は、関数よりもPower Queryの方が管理しやすい場合があります。
ただし、毎回曜日を変更してすぐ一覧を確認したい場合は、今回紹介した関数による方法の方が手軽です。
例えば、施設利用者の送迎表や教室参加者一覧など、日々条件が変わる管理表では関数による自動表示が便利です。
まとめ
Excel 2019ではFILTER関数が使えませんが、INDEX関数・AGGREGATE関数・IFERROR関数を組み合わせることで、曜日と◯印の条件に一致する名簿を自動作成できます。
元データをシート①で管理し、シート②では曜日を入力するだけにしておくことで、毎月の人数変更にも対応しやすい仕組みになります。
利用者名簿や予定表など定期的に更新する資料では、自動抽出の仕組みを作ることで作業時間を大きく減らすことができます。
コメント