名簿を見比べるのをやめる
XLOOKUP
申込と名簿を一人ずつ見比べて、氏名を転記していませんか。コードから名簿の氏名を表示し、見つからない申込者について、次に確かめることを考える練習です。
この講座が向いているか確かめる
基本的な内容になる方：XLOOKUPで名簿を照合し、未一致の意味や参照範囲を日常的に点検できる人。Excel 2019以前だけを使う人には本講座の操作を実施できない
この講座でできるようになること
コードを検索値にしてXLOOKUPで名簿の氏名を表示し、検索範囲と戻り配列を固定して下へコピーできる。見つからないコードを『未登録』と表示し、別題材で未登録の人数・申込者氏名を答えられる。未登録を対象外の判定とせず、コード・名簿の版・参照範囲を確かめる次の行動を言える
ページ更新：2026-10-14
まずは全体像
概要動画
クリックするとYouTubeの埋め込みプレーヤーを読み込みます。YouTubeで開く
動画は講座の概要です。このあとの「解説・練習」では、練習ブックを使った操作と結果の確かめ方を説明します。
ここから講座の本文
解説・練習
01〜08が講座の本文です。申込のコードから名簿の氏名を表示し、見つからない申込者を確かめます。その後、列の並びが違う名簿で自力課題に取り組みます。
01練習の準備
練習ブックを入手する（XLSX）から手元へ保存し、Microsoft Excelで開きます。架空のデータなので、実際の名簿を持ち込む必要はありません。元の配布ファイルを残しておきたいときは、作業する写しに別の名前を付けます。
- 「使い方」シートを読み、「参加申込」を開きます。元のA〜C列は残し、空欄のD列「名簿の氏名」に結果を出します。
- 申込はA001〜A018の18人。「対象者名簿」は20人、2〜21行目です。二つの一覧は並び順が違います。
- 「参加申込」のB列は対象者コード、C列は申込者氏名です。「対象者名簿」ではA列が対象者コード、B列が氏名です。名簿の見出しと最終行を確認してから「参加申込」へ戻ります。
Excel 2019以前ではXLOOKUPを使えません。この講座はMicrosoft Excel 2021以降を使用します。
02コードで同じ人を探す
二つの一覧の同じ行番号を結び付けても、同じ人になるとは限りません。申込のP007を名簿のコード列で探し、その行の氏名を返します。
対象者名簿には「松井健」が2人います。P007とP008は別コードです。氏名だけで照合すると区別できないため、今回の練習ではコードを手がかりにします。
XLOOKUPで指定する三つの場所を、ブックで確認します。名簿側の二つの範囲は、同じ2〜21行目を使います。見出しの1行目は含めません。
- 検索値：「参加申込」のB2。探す対象者コードです。
- 検索範囲：「対象者名簿」のA2:A21。コードを探す列です。
- 戻り配列：「対象者名簿」のB2:B21。見つかったコードと同じ行から氏名を取り出す列です。
今回はコードが一意で、氏名はすべて記入済みの名簿を使います。XLOOKUPはこの式では完全一致で探し、最初に一致した行を返します。結果を使う前に、実際の名簿でも重複や氏名の空欄を確かめる必要があります。
03D2へ式を入力する
1. D2へ式を入力する
「参加申込」のD2をクリックします。次の五つの文字片を続けて入力し、最後まで入力したらEnterで確定します。途中ではEnterを押しません。
- 関数を始める：
=XLOOKUP( - 探すコードを指定する：
B2, - 名簿のコード列を指定する：
対象者名簿!$A$2:$A$21, - 同じ行の氏名列を指定する：
対象者名簿!$B$2:$B$21, - 見つからない場合の表示を指定して閉じる：
"未登録")
$・カンマ・括弧・二重引用符は半角です。$は直接入力で構いません。検索値のB2には$を付けず、名簿側の二つの範囲の列・行に$を付けます。
=XLOOKUP(B2,対象者名簿!$A$2:$A$21,対象者名簿!$B$2:$B$21,"未登録")
2. 氏名と数式バーを確かめる
D2に松井健が表示されたら、最初の1人を照合できました。D2を選び直し、数式バーの式も確かめます。
4番目の指定"未登録"は、コードが指定範囲に見つからなかったときの表示です。「参加できない」と判断する式にはしていません。
04範囲を固定して、下へコピーする
コピーする前に予測してみます。次の申込者ではB2がどこへ動くでしょうか。名簿側の二つの範囲は、同じ範囲のままでよいでしょうか。
1. 次の行へコピーし、式の変化を確かめる
- D2を選び、セル右下の小さな■（フィルハンドル）をD3までドラッグします。
- D3を選び直し、数式バーを見ます。検索値だけがB3になり、名簿の二つの範囲は2〜21行目のままです。D3の表示は高橋紗季です。
2. 最終行までコピーし、コードと氏名を照合する
- 確認できたら、もう一度D2を選び、その右下の■をD19までドラッグします。全18人のD列に式を入れます。
- D19を選び直します。検索値はB19、名簿の範囲はD2と同じです。先頭・途中・最終行のコードを元名簿で探し、返された氏名と比べます。
=XLOOKUP(B19,対象者名簿!$A$2:$A$21,対象者名簿!$B$2:$B$21,"未登録")
- 先頭：2行目 A001・P007 → 松井健
- 途中：10行目 A009・P002 → 佐藤遥
- 最終：19行目 A018・P015 → 河野理沙
13行目のA012・P008も松井健と表示されますが、先頭のP007とは別コードです。黄色の塗りもコピーで広がるため、色だけでは完成を判断できません。氏名・「未登録」の表示と、コピー先の式を見ます。
05「未登録」の人を確かめ、全員表示へ戻す
1. 「未登録」だけを表示する
「参加申込」の見出しにはフィルターが用意されています。別講座を受けていなくても、次の手順で「未登録」だけを取り出せます。
- D1「名簿の氏名」の▼を開きます。
- 一覧の「すべて選択」のチェックを外します。
- 一覧を下へスクロールし、最後の「未登録」にチェックを付けます。
- OKを押します。D列が「未登録」の申込者だけが表示されます。
2. 人数・申込者氏名と、次に確かめることを書く
表示された行を数え、C列「申込者氏名」を読みます。人数・申込者氏名・次に確かめることを、回答欄A24:C24へ書いてから、次の結果と比べてください。A24が人数、B24が氏名、C24が次の確認です。
- A004・P025・森田葵
- A010・P031・岸本航
- A016・P044・岩田結衣
氏名が返る15人＋未登録3人＝申込18人です。参照する名簿にいるが申込をしていない人は、この照合の対象ではありません。
3. 全員の表示へ戻す
回答後は、全員を見られる状態に戻します。D1の▼を開き、「名簿の氏名」からフィルターをクリアを選びます。
A001〜A018の18人に戻ったことを確かめます。式は消さず、絞り込みだけを解除します。
06別の名簿で、自力で照合する
ここからは12分の自力課題です。研修では35〜47分に取り組みます。共通演習の手順は参照できます。完成ブック・カード・スライド15ページ以降と、下の答えはまだ開かず、列の見出しを自分で選びます。
課題：研修申込の職員名を表示し、次の確認を書く
- 練習ブックの「研修申込」と「職員名簿」を開きます。研修申込は12人、職員名簿は15人です。名簿の列の並びは共通演習と違います。
- 申込側のコード、名簿側のコード列、返したい職員名列を自分で選びます。二つの範囲の開始行・終了行をそろえ、固定します。
- 「研修申込」のD2にXLOOKUPの式を作り、D13までコピーします。先頭・最終行のコードと職員名、コピー先の式を確認します。
- D1の▼から「未登録」だけに絞り、C列の申込者氏名を読みます。
- 回答欄A18:C18へ、人数・申込者氏名・次に確かめることを一つ書きます。A18が人数、B18が氏名、C18が次の確認です。
自力課題の答えを見る
自分の回答を書き終えたら開きます。研修では47分から、完成ブック・カード・スライド15〜16ページと照合します。
「職員名簿」のC列が職員コード、A列が職員名です。コードの左側の列も返せます。どちらの範囲も2〜16行目を固定します。
=XLOOKUP(B2,職員名簿!$C$2:$C$16,職員名簿!$A$2:$A$16,"未登録")
D13では検索値だけがB13になります。先頭R001・E011は山本晴、最終R012・E007は小林律です。
職員名が返る9人＋未登録3人＝研修申込12人。未登録の申込者は次の3人です。
- R003・E019・坂口蓮
- R007・E022・大野真帆
- R010・E027・服部悠
次の確認の記入例は「申込のコードと、職員名簿の更新日・最終行を確認する」。同じ文言でなくても、コードや名簿の根拠を確かめる行動を書けていればよい回答です。未登録の理由は、この結果だけでは確定できません。
答え合わせ後はD1の▼からフィルターをクリアし、R001〜R012の全12人の表示へ戻します。
余裕があれば、氏名だけの照合について考える
共通演習のP007とP008を、氏名だけで照合すると何が起きるでしょうか。同じ「松井健」でも別コードなので、同一人物としてまとめてはいけません。また、参照元のコードが重複すると、今回の式は最初に一致した行を返します。元名簿を変更して試す必要はありません。結果を使う前にコードの一意性を点検します。
07式が入らない・結果がおかしいとき
開く閉じる
まず先頭の式を直し、結果を確認してから下へコピーし直します。研修では、分からない式とセルを選んで講師へ示します。
式が入力できない
等号・カンマ・括弧・半角の二重引用符を確認します。入力片の途中でEnterを押していないかも見ます。
#NAME?になる
Excel 2021以降か、関数名の綴りがXLOOKUPかを見ます。Excel 2019以前ではこの関数を使えません。
#N/Aになる
4番目の引数に"未登録"を指定したかを見ます。文字を半角の二重引用符で囲みます。
コピー後だけ未登録が増える
名簿の二つの範囲に$が付き、最終行まで固定されているかを数式バーで見ます。共通なら2〜21行目のままです。先頭の式を直してコピーし直します。
#VALUE!になる
コード列と氏名列の行数を見ます。二つの範囲の開始行・終了行をそろえます。
違う氏名や0が返る
返す列が氏名列か、行の対応が合うか、氏名に空欄がないか、コードに重複がないかを確認します。元名簿を確かめ、結果のセルだけを書き換えないでください。
登録済みのはずなのに未登録になる
コードの文字列・数値の違い、余分な空白、名簿の版と参照範囲を確認します。原記録を確かめてから直します。式を全行へ入れただけでは、元名簿の正しさは保証できません。
08自分の仕事に置き換える
開く閉じる
答え合わせ後、カードの転用欄や手元のメモに、次の項目を書きます。実データを持ち込まず、一覧と列の名前で考えます。
- 結果を出す一覧
- 参照する名簿と版
- 二つの一覧に共通するコード
- 名簿から表示したい列
- 未登録なら、まず確かめること
- 明日試すこと
例：事業参加申込と対象者名簿を対象者コードで照合し、氏名を表示する。未登録ならコードと名簿の更新日を確認する。
明日は結果を出す一覧の写しの数行で試します。参照元は名簿の全データ行を範囲に含め、版・最終行・コードの重複・氏名の空欄を先に確認します。氏名が返る行と未登録の行を元記録と比べ、結果の扱いを決めます。
ここからは復習用の教材
復習・職場で使う
自力課題に取り組んだ後、完成見本とカードで振り返ります。研修では47分から使う資料です。
09教材を使う
4点開く閉じる
復習や職場の勉強会に使う資料です。完成ブックとカードは、自力課題に取り組んでから開くと答え合わせに使えます。
練習ブック
共通演習・別題材の自力課題の開始状態。最初に用意します。
完成ブック
共通・自力の式と結果の完成見本。研修では47分の答え合わせから開きます。
講座スライド
全19ページ。復習・庁内勉強会用。自力課題の答えは15ページ以降です。
困ったときカード
A4・1ページ。手順と確認場所の要点集。研修では47分から使います。
非営利での改変と、庁内研修参加者への配布ができます。外部への再配布は禁止です。有償受託研修での利用は事前の個別許諾が必要です。 教材の利用条件を確認する
参考資料
操作の説明は既存教材とMicrosoftの公式資料を参照しています。
公式資料の確認日：2026-10-14
職場での実践を、一緒に進めたい方へ
庁内の少人数研修や、担当課の台帳を使った業務改善をご相談いただけます。職員の経験や業務に合わせて、研修の設計・実施から、仕事に使うところまで支援します。
研修・伴走支援について相談する