Excelで勤務記号A・P・Nから担当者名を別表に自動表示する方法｜XLOOKUP・INDEX/MATCHで解説

Excelで勤務表を作るとき、「A＝午前勤務」「P＝午後勤務」「N＝夜勤務」のように勤務記号を入力し、その記号に対応する担当者名を別の表へ自動表示したい場面があります。人数が少ないうちは手入力でも対応できますが、毎日勤務表を更新する場合は関数で自動化したほうが入力ミスを減らせます。

たとえば、佐藤さんに「P」、井上さんに「N」、吉岡さんに「A」と入力したら、別表の「午後勤務」には佐藤さん、「夜勤務」には井上さん、「午前勤務」には吉岡さんと自動表示させることができます。

この記事では、1つの勤務につき担当者が1人という前提で、Microsoft 365やExcel 2021以降で使いやすいXLOOKUP関数と、古いExcelでも使えるINDEX・MATCH関数の両方を紹介します。

まず勤務表を「名前」と「勤務記号」の2列に分ける

最初に、元の勤務表は「佐藤P」のように1つのセルへ名前と勤務記号をまとめるのではなく、名前と記号を別々の列に分けるのがおすすめです。

たとえばA列に名前、B列に勤務記号を入力します。

このような表にしておけば、「勤務列がPの人の名前を返す」「勤務列がNの人の名前を返す」といった検索を関数で簡単に行えます。

勤務記号を別セルにすることが、自動表示を簡単にする最大のポイントです。

XLOOKUP関数なら勤務記号から名前を簡単に取得できる

Microsoft 365や比較的新しいExcelを使用している場合は、XLOOKUP関数を使うと非常にシンプルです。

元データがA2:A11に氏名、B2:B11に勤務記号として入力されている場合、午後勤務「P」の担当者を表示する式は次のようになります。

=XLOOKUP("P",B2:B11,A2:A11,"")

この式では、B2:B11から「P」を探し、同じ行にあるA列の名前を返します。該当者がいない場合は最後の""によって空白になります。

同様に夜勤務「N」は次の式です。

=XLOOKUP("N",B2:B11,A2:A11,"")

午前勤務「A」は次のようになります。

=XLOOKUP("A",B2:B11,A2:A11,"")

実際の結果は「午後勤務 佐藤」「夜勤務 井上」のように表示できる

たとえば元表が次の状態だとします。

別表に「午後勤務」「夜勤務」「午前勤務」を作り、それぞれの隣のセルへXLOOKUP関数を入れると、次のような結果になります。

元表で勤務記号を変更すると、別表の担当者名も自動的に切り替わります。たとえば佐藤さんのPを削除して上田さんへPを入力すれば、「午後勤務」の担当者も上田さんへ更新されます。

記号をセルに入力しておけば1つの式をコピーできる

勤務ごとに「A」「P」「N」を式へ直接書いても構いませんが、より管理しやすくするなら別表にも勤務記号を用意すると便利です。

たとえばD列とE列を次のようにします。

F2セルに次の式を入力します。

=XLOOKUP(E2,$B$2:$B$11,$A$2:$A$11,"")

その式を下へコピーすれば、E列の「P」「N」「A」に応じた担当者名が表示されます。勤務種類が後から増えた場合にも式を使い回しやすい方法です。

XLOOKUPが使えないExcelではINDEXとMATCHを使う

Excel 2019以前など、XLOOKUP関数が利用できない環境ではINDEX関数とMATCH関数を組み合わせることで同じ処理ができます。

午後勤務「P」の担当者を取得する式は次のようになります。

=IFERROR(INDEX($A$2:$A$11,MATCH("P",$B$2:$B$11,0)),"")

MATCH関数がB2:B11から「P」の位置を探し、INDEX関数が同じ位置のA列の名前を返します。IFERRORを付けているため、Pの人がいない場合はエラーではなく空白になります。

夜勤務の場合は「P」を「N」に、午前勤務の場合は「A」に変更するだけです。

「佐藤P」のように1セルに入力している場合はどうする？

すでに「佐藤P」「井上N」「吉岡A」のように名前と勤務記号が同じセルに入力されている場合でも検索はできます。ただし、名前と勤務を分けて管理する場合より式が複雑になります。

たとえばA2:A11に「佐藤P」などが入力され、記号が必ず文字列の最後にある場合、Microsoft 365では次のような式でPの担当者を探せます。

=XLOOKUP("*P",A2:A11,A2:A11,"",2)

ただし、このままでは結果が「佐藤P」になります。名前だけを取り出すなら、末尾の1文字を除く式と組み合わせます。

=LET(x,XLOOKUP("*P",A2:A11,A2:A11,"",2),IF(x="","",LEFT(x,LEN(x)-1)))

この方法でも可能ですが、勤務表を長期間使うなら、名前と勤務記号は別セルに分けたほうが入力・検索・集計のすべてが簡単になります。

勤務記号は入力規則のプルダウンにすると入力ミスを防げる

勤務記号を手入力していると、「P」のつもりで小文字の「p」を入力したり、余分なスペースを入れたりすることがあります。すると検索式で担当者を正しく取得できない原因になります。

そこで、勤務記号のセルにはExcelの「データの入力規則」を使い、「A,P,N」を選択肢にしたドロップダウンリストを設定すると便利です。

たとえばB2:B11を選択して「データ」→「データの入力規則」→「リスト」を選び、元の値へA,P,Nと入力します。これでセルをクリックするだけでA・P・Nを選択できるようになります。

10人程度の勤務表でも、毎日更新するのであれば入力規則を設定することで記号の打ち間違いをかなり減らせます。

同じ勤務記号を2人に付けた場合は最初の1人だけ表示される

XLOOKUPやINDEX・MATCHを使う方法では、同じ勤務記号が複数見つかった場合、基本的に最初に該当した人が返されます。

たとえば佐藤さんと上田さんの両方に「P」を入力してしまうと、午後勤務の欄には上側にいる佐藤さんだけが表示される可能性があります。

今回のように「午前勤務・午後勤務・夜勤務それぞれ1人」と決まっているのであれば、重複入力を防ぐ仕組みを追加するとより安全です。

たとえば条件付き書式でB2:B11を対象にし、=AND(B2<>"",COUNTIF($B$2:$B$11,B2)>1)のような数式を設定すれば、同じ勤務記号が2回以上入力されたセルを強調表示できます。

複数人勤務へ変更する可能性があるならFILTER関数も便利

現在は各勤務1人でも、将来「午前勤務は2人」などに変更する可能性がある場合は、Microsoft 365のFILTER関数が便利です。

たとえばPが入力された人をすべて取り出す場合は次のようにします。

=FILTER(A2:A11,B2:B11="P","")

佐藤さんと上田さんの両方にPが入力されていれば、2人の名前が縦方向へ自動的に表示されます。

ただし、勤務ごとに必ず1人だけという運用ならXLOOKUPのほうが式が分かりやすく、表も管理しやすいでしょう。

勤務表を作るならおすすめの構成

日常的に使う勤務表なら、元データと表示用の表を次のように分けると管理しやすくなります。

F2には=XLOOKUP(E2,$B$2:$B$11,$A$2:$A$11,"")を入力し、下へコピーします。これだけでB列の勤務記号を変更するたびに担当者表が更新されます。

この構成なら、名前と勤務情報が分離されているため、後から勤務人数を数えたり、勤務別に色を付けたり、月間勤務表へ発展させたりすることも簡単です。

まとめ：勤務記号から担当者名を出すならXLOOKUPが簡単

ExcelでA＝午前、P＝午後、N＝夜のような勤務記号を付け、別表へ担当者名を自動表示したい場合は、氏名と勤務記号を別々の列へ入力するのが基本です。

Microsoft 365などXLOOKUPが使えるExcelなら、午後勤務の担当者は=XLOOKUP("P",B2:B11,A2:A11,"")、夜勤務なら「N」、午前勤務なら「A」とするだけで取得できます。

古いExcelでは=IFERROR(INDEX($A$2:$A$11,MATCH("P",$B$2:$B$11,0)),"")のようにINDEXとMATCHを組み合わせれば同じことができます。

さらに勤務記号を入力規則のプルダウンにし、条件付き書式で重複を検出するようにしておけば、毎日の勤務表でも入力ミスを減らせます。1勤務につき1人という現在の条件なら、「名前列＋勤務記号列＋XLOOKUP」という構成が最もシンプルで扱いやすい方法です。