案件管理表を「探せる・絞れる」形にする基本設計
Excelで案件管理表を作るとき、最初に意識したいのは「ただ入力する表」ではなく、あとから探しやすく、条件で絞り込みやすい表にしておくことです。XLOOKUPやFILTERは非常に便利な関数ですが、元になる表の作り方がバラバラだと、うまく機能しなかったり、運用が面倒になったりします。
まず、案件管理表には最低限、次のような項目を用意しておくと使いやすくなります。
- 案件ID
- 会社名・顧客名
- 案件名
- 担当者
- ステータス
- 受注予定日
- 見込み金額
- 備考
特に重要なのが案件IDです。会社名や案件名だけで管理すると、似た名前の案件が出てきたときに判別しにくくなります。案件ごとに「A001」「A002」のような一意のIDを付けておけば、XLOOKUPで正確に案件情報を呼び出せます。
また、FILTERで絞り込みやすくするためには、担当者名やステータスの表記を統一することも大切です。たとえば「進行中」「対応中」「作業中」のように似た意味の言葉が混在していると、ステータス別に抽出したいときに漏れが発生します。ステータスは「未着手」「進行中」「提案中」「受注」「失注」など、あらかじめ選択肢を決めておきましょう。
表の形式は、1行目に項目名、2行目以降に案件データを入力するシンプルな形がおすすめです。このとき、途中に空白行を入れたり、セルを結合したりするのは避けてください。見た目は整って見えても、関数やフィルター機能では扱いづらくなります。
さらに、案件管理表はExcelのテーブル機能に変換しておくと便利です。表の範囲を選択して「Ctrl + T」を押すだけでテーブル化できます。テーブルにしておくと、新しい案件を追加したときに範囲が自動で広がるため、XLOOKUPやFILTERの参照範囲を毎回修正する手間が減ります。
このように、最初の設計段階で「IDで検索できる」「担当者やステータスで絞れる」「データを追加しても壊れにくい」形にしておくことが、実用的な案件管理表を作る第一歩です。次章からは、この基本設計をもとに、XLOOKUPで案件情報を自動表示する方法を見ていきます。
XLOOKUPで案件情報を自動表示する方法
案件管理表の基本設計ができたら、次はXLOOKUPを使って、案件IDを入力するだけで案件情報が自動表示される仕組みを作っていきます。毎回一覧表から目視で探す必要がなくなるため、案件確認や上司への報告資料作成がかなりスムーズになります。
たとえば、案件一覧をExcelのテーブル機能で管理し、テーブル名を「案件一覧」にしているとします。列には「案件ID」「会社名」「案件名」「担当者」「ステータス」「受注予定日」「見込み金額」などが並んでいる状態です。
別シート、または同じシートの右側に、検索用のエリアを作りましょう。
- B2：案件IDを入力するセル
- B4：会社名を表示
- B5：案件名を表示
- B6：担当者を表示
- B7：ステータスを表示
- B8：見込み金額を表示
たとえば、B2に入力した案件IDをもとに会社名を表示したい場合は、B4セルに次のような式を入力します。
=XLOOKUP($B$2,案件一覧[案件ID],案件一覧[会社名],"該当なし")この式は、「B2に入力された案件IDを、案件一覧テーブルの案件ID列から探し、一致した行の会社名を表示する」という意味です。最後の“該当なし”は、検索した案件IDが見つからなかった場合に表示するメッセージです。エラー表示のままにするより、利用する人にとって分かりやすくなります。
同じ考え方で、案件名や担当者も自動表示できます。
=XLOOKUP($B$2,案件一覧[案件ID],案件一覧[案件名],"該当なし")
=XLOOKUP($B$2,案件一覧[案件ID],案件一覧[担当者],"該当なし")
=XLOOKUP($B$2,案件一覧[案件ID],案件一覧[ステータス],"該当なし")ポイントは、検索値のセルを$B$2のように絶対参照にしておくことです。これにより、式をコピーしても検索する案件IDのセルがズレません。表示したい項目だけを変更すれば、同じ案件IDから複数の情報をまとめて呼び出せます。
また、XLOOKUPは従来のVLOOKUPと違い、検索列が左端になくても使えます。案件ID列の右側にある情報だけでなく、表の並び順を変更した場合でも比較的壊れにくいのがメリットです。案件管理表のように、あとから列を追加したり並び替えたりすることが多い表では、XLOOKUPのほうが実務向きです。
さらに使いやすくするなら、B2セルにデータの入力規則を設定し、案件IDをプルダウンで選べるようにしておくと便利です。案件IDの入力ミスを防げるため、「該当なし」が出る原因を減らせます。
このように、XLOOKUPを使えば、案件IDを起点に必要な情報を一瞬で確認できる検索画面を作れます。次章では、特定の担当者やステータスに該当する案件をまとめて抽出できるFILTER関数の使い方を見ていきます。
FILTERで担当者別・ステータス別に案件を抽出する方法
XLOOKUPが「1つの案件をピンポイントで探す」ための関数だとすると、FILTERは条件に合う案件をまとめて一覧表示するための関数です。案件管理では、「自分が担当している案件だけ見たい」「進行中の案件だけ確認したい」「担当者かつステータスで絞り込みたい」といった場面がよくあります。こうした確認作業を手作業でフィルターするのではなく、関数で自動化できるのがFILTERの便利なところです。
たとえば、案件一覧テーブルの名前を「案件一覧」として、別シートに抽出用のエリアを作るとします。B2セルに担当者名を入力し、その担当者の案件だけを表示したい場合は、抽出結果を表示したいセルに次の式を入力します。
=FILTER(案件一覧,案件一覧[担当者]=B2,"該当する案件はありません")この式は、「案件一覧の中から、担当者列がB2セルの値と一致する行だけを表示する」という意味です。たとえばB2に「佐藤」と入力すれば、佐藤さんが担当している案件だけが一覧で表示されます。最後の“該当する案件はありません”は、条件に合うデータがない場合に表示されるメッセージです。
ステータス別に抽出したい場合も考え方は同じです。B3セルにステータスを入力し、「進行中」や「提案中」などの案件だけを表示したい場合は、次のように書きます。
=FILTER(案件一覧,案件一覧[ステータス]=B3,"該当する案件はありません")これだけで、ステータスに応じた案件一覧を自動で作成できます。営業会議の前に「進行中の案件だけ確認する」「失注案件を振り返る」といった使い方がしやすくなります。
さらに実務でよく使うのが、担当者とステータスを組み合わせた抽出です。たとえば、B2セルに担当者、B3セルにステータスを入力し、「佐藤さんの進行中案件」だけを表示したい場合は、次の式を使います。
=FILTER(案件一覧,(案件一覧[担当者]=B2)*(案件一覧[ステータス]=B3),"該当する案件はありません")FILTER関数では、複数条件をすべて満たす場合に「*」を使います。これは「AかつB」という意味で、担当者も一致し、ステータスも一致する案件だけが抽出されます。逆に「担当者が佐藤、またはステータスが進行中」のように、どちらか一方を満たせばよい条件にしたい場合は「+」を使います。
=FILTER(案件一覧,(案件一覧[担当者]=B2)+(案件一覧[ステータス]=B3),"該当する案件はありません")注意点として、FILTERの抽出結果は入力したセルから下方向・右方向に自動で広がります。この動きをスピルと呼びます。抽出結果が表示される範囲にすでに文字や数式が入っていると、正しく表示されないことがあります。そのため、FILTERを使うエリアの周辺はあらかじめ空けておきましょう。
また、担当者名やステータスの入力ミスがあると、抽出結果に漏れが出ます。1章で触れたように、担当者やステータスは表記を統一しておくことが重要です。できれば抽出条件を入力するセルも、手入力ではなくプルダウンにしておくと、より安定して運用できます。
FILTERを使えば、案件一覧から必要な条件のデータだけを瞬時に取り出せます。案件数が増えても、担当者別・ステータス別の確認がラクになるため、毎日の進捗チェックやチーム内の共有にも役立ちます。
XLOOKUP×FILTERで作る実用的な案件管理テンプレート
ここまで紹介したXLOOKUPとFILTERを組み合わせると、「条件で案件を絞り込み、気になる案件の詳細をすぐ確認できる」案件管理テンプレートを作れます。単なる一覧表ではなく、検索画面と抽出画面をセットにしたミニダッシュボードのような形です。
おすすめの構成は、元データ用の「案件一覧」シートとは別に、「案件確認」シートを作る方法です。「案件確認」シートには、上部に抽出条件、中央にFILTERで抽出した案件一覧、右側にXLOOKUPで詳細表示するエリアを配置します。
- B2：担当者を選択
- B3：ステータスを選択
- A6以降：FILTERで抽出結果を表示
- J2：詳細確認したい案件IDを入力
- J4以降：XLOOKUPで案件詳細を表示
たとえば、B2に担当者、B3にステータスを入力して、条件に合う案件だけをA6セルから表示する場合は、次の式を使います。
=FILTER(案件一覧,(案件一覧[担当者]=B2)*(案件一覧[ステータス]=B3),"該当する案件はありません")この抽出結果を見ながら、詳しく確認したい案件の案件IDをJ2セルに入力します。そして、J4セル以降にXLOOKUPを設定しておけば、選んだ案件の詳細情報を自動で表示できます。
=XLOOKUP($J$2,案件一覧[案件ID],案件一覧[会社名],"該当なし")
=XLOOKUP($J$2,案件一覧[案件ID],案件一覧[案件名],"該当なし")
=XLOOKUP($J$2,案件一覧[案件ID],案件一覧[見込み金額],"該当なし")
=XLOOKUP($J$2,案件一覧[案件ID],案件一覧[備考],"該当なし")この形にしておくと、まずFILTERで「佐藤さんの進行中案件」などを一覧表示し、その中から気になる案件IDを選んで、XLOOKUPで詳細を確認できます。つまり、FILTERで広く絞り込み、XLOOKUPで深く確認する流れを作れるわけです。
実務では、すべての列をFILTERで表示すると横に長くなり、見づらくなることがあります。その場合は、FILTERで表示する項目を「案件ID」「会社名」「案件名」「ステータス」「見込み金額」などに絞り、詳細な備考や次回アクションはXLOOKUP側で確認する設計にすると使いやすくなります。
たとえば、抽出一覧は会議中にざっと見るための画面、詳細表示は個別案件を確認するための画面、と役割を分けるイメージです。これにより、案件数が増えても画面がごちゃつきにくく、必要な情報に素早くアクセスできます。
また、テンプレートとして使うなら、条件入力セルや詳細確認用の案件IDセルには色を付けておくと親切です。「ここを入力すればよい」と一目で分かるため、自分以外のメンバーにも共有しやすくなります。XLOOKUPとFILTERを組み合わせることで、確認・抽出・詳細表示までを1枚のシートで完結できる、実用的な案件管理表になります。
入力ミスを減らして運用しやすくする改善ポイント
XLOOKUPとFILTERを使った案件管理表は便利ですが、運用でつまずきやすい原因の多くは入力ミスや表記ゆれです。関数そのものが正しくても、「担当者名が違う」「ステータスの書き方がバラバラ」「案件IDが重複している」といった状態になると、検索や抽出の精度が落ちてしまいます。
まず設定しておきたいのが、データの入力規則です。担当者やステータスは手入力ではなく、プルダウンから選べるようにしておきましょう。たとえば、別シートに「担当者マスタ」「ステータスマスタ」を用意し、そこに入力候補を一覧化します。
- 担当者：佐藤、鈴木、田中
- ステータス：未着手、提案中、進行中、受注、失注
そのうえで、案件一覧の「担当者」列や「ステータス」列に入力規則を設定すれば、表記ゆれを防げます。「進行中」と「対応中」が混在するような状態を避けられるため、FILTERで抽出したときの漏れも減ります。
次に重要なのが、案件IDの重複チェックです。XLOOKUPは基本的に最初に見つかった値を返すため、同じ案件IDが複数あると正しく管理できません。案件ID列には条件付き書式を設定し、重複している値に色が付くようにしておくと安心です。
=COUNTIF(案件一覧[案件ID],[@案件ID])>1このようなルールを設定しておけば、同じ案件IDを入力したときにすぐ気づけます。案件IDは管理表全体のキーになる項目なので、ここだけは特に厳しくチェックしましょう。
また、入力必須の項目には空欄チェックを入れるのもおすすめです。案件ID、会社名、担当者、ステータスなどが空欄のままだと、あとから検索や集計がしづらくなります。条件付き書式で空欄セルに色を付けておけば、入力漏れを視覚的に確認できます。
日付や金額の列も、形式を統一しておくと運用しやすくなります。受注予定日は日付形式、見込み金額は通貨または数値形式に設定しておきましょう。文字列として金額を入力してしまうと、合計や並び替えがうまくできない場合があります。
さらに、関数が入っているセルは誤って上書きされないように注意が必要です。FILTERやXLOOKUPを設定したシートでは、入力するセルだけ色を付け、関数セルは保護しておくと安全です。Excelの「シートの保護」を使えば、編集してよいセルと触ってはいけないセルを分けられます。
チームで使う場合は、入力ルールをシート上にメモしておくことも効果的です。「案件IDはA001形式で入力」「ステータスはプルダウンから選択」「完了案件も削除せず受注・失注で管理」など、簡単なルールを書いておくだけで、メンバー間の運用ブレを防げます。
案件管理表は、作って終わりではなく、日々更新されることで価値が出ます。入力規則、条件付き書式、セル保護、運用ルールを組み合わせることで、XLOOKUPとFILTERが安定して機能する管理表になります。ミスを減らす仕組みまで整えておけば、忙しい営業現場でも使い続けやすい案件管理テンプレートとして活用できます。
コメント