Excel 入力規則 リストの連動はINDIRECT×名前定義で自動化 部署→担当者の2段階ドロップダウンで選択ミス月10件→0件
「営業部の経費なのに、承認者が経理の田中さん」が月10件
去年の秋、ある製造業のお客さんで経費精算の集計表を見せてもらったときの話です。
部署列に「営業部」、担当者列に「田中」。で、田中さんは経理部の人。こういう行が、1ヶ月分のデータに10件ありました。
経理の担当者さんいわく、「毎月ぜんぶ目視で照合してます」。1件見つけるたびに申請者へ確認メール、返事待ち、修正。1件あたりざっくり15分。月10件で2時間半が消えている計算です。
ところが、この表にはちゃんとドロップダウンが付いていたんですよね。部署も担当者も、入力規則のリストから選ぶ形式。それでも間違える。
なぜだと思いますか?
答えは単純で、部署と担当者のリストが連動していなかったからです。今日はこれをINDIRECT関数と名前定義で連動させて、選択ミスをゼロにした3手順の話をします。所要時間は慣れれば15分くらい。
なぜドロップダウンがあるのに間違えるのか
原因分析から入ります。
このお客さんの担当者リストは、全社員38名がベタ並びの1本のリストでした。部署で「営業部」を選んでも、担当者欄には経理も総務も開発も、38人ぜんぶ出てくる。
人間、38件のリストをスクロールして探すと、似た名字で目が滑ります。「田中」が2人いれば、そりゃ間違えるわけで。
つまり問題は「ドロップダウンがあるかどうか」じゃなくて、「選ばせる候補が絞られているか」なんです。
正直、私も昔は同じ作りの表を納品していました。「リストにしておけば入力ミスは防げる」と思い込んでいたんですよね。今思うと甘かった。候補が多いリストは、自由入力とそんなに変わりません。
2段階ドロップダウンの仕組みをひとことで言うと
部署を選んだら、その部署のメンバーだけが担当者欄に出てくる。これが2段階ドロップダウン(連動リスト)です。
仕組みの肝は2つ。
部署名ごとに担当者リストを「名前定義」しておく
- 担当者欄の入力規則で `=INDIRECT(部署セル)` と書く
INDIRECTは「文字列をセル参照や名前として解釈する」関数です。部署セルに「営業部」と入っていれば、`=INDIRECT(B2)` は「営業部」という名前の範囲を返す。だから担当者リストが部署に応じて切り替わるわけです。
言葉だけだとピンとこないと思うので、手順に落とします。
手順1:マスタ表を「横に部署、縦に担当者」で作る
まず新しいシートを作って、名前を「マスタ」にしてください。
1行目に部署名を横に並べ、その下に各部署の担当者を縦に書いていきます。
A1「営業部」、A2以降に営業部のメンバー
- B1「経理部」、B2以降に経理部のメンバー
- C1「総務部」、C2以降に総務部のメンバー
- 以下、部署の数だけ右へ
ポイントは、1行目の見出しをそのまま「名前」として使うこと。だから見出しの表記ゆれは厳禁です。入力シート側の部署リストと、1字でも違うと動きません。「営業部」と「営業部 」(末尾スペース)で30分溶かした人を私は知っています。私です。
手順2:名前定義は「選択範囲から作成」で一気にやる
ここが一番ラクできるところ。部署が6つあっても、名前定義を6回やる必要はありません。
マスタ表全体(見出し行を含む)を範囲選択する
- `Ctrl + Shift + F3` を押す(「数式」タブ→「選択範囲から作成」でも同じ)
- 「上端行」だけにチェックを入れてOK
これで、A列の担当者範囲に「営業部」、B列に「経理部」……と、見出しの文字がそのまま名前になります。
確認したいときは「数式」タブ→「名前の管理」。部署の数だけ名前が並んでいれば成功です。
それと、部署リスト自体にも名前をつけておきます。1行目の見出し範囲(A1:F1など)を選んで、名前ボックスに「部署リスト」と入力してEnter。これで入力シートから参照しやすくなります。
手順3:入力規則にINDIRECTを書く
入力シートに戻ります。部署がB列、担当者がC列、データが2行目からという前提で進めます。
まず部署列。
B2:B100を選択
- 「データ」タブ→「データの入力規則」
- 入力値の種類:リスト
- 元の値:`=部署リスト`
次に担当者列。ここが本題です。
C2:C100を選択
- 「データ」タブ→「データの入力規則」
- 入力値の種類:リスト
- 元の値:`=INDIRECT(B2)`
B2を相対参照で書くのがコツ。C3の規則は自動的に `=INDIRECT(B3)` になります。
設定時に「元の値はエラーと判断されます。続けますか?」と聞かれることがあります。B2が空欄だとINDIRECTが参照先を解決できないだけなので、「はい」で進めて大丈夫です。
これで、B2で「営業部」を選ぶとC2には営業部の人しか出ません。38人のリストが5〜8人に減る。この差、実際に触ると「なんで最初からこうしなかったんだ」って気持ちになりますよ。
余談:部署名にスペースや記号があると詰む
ここで脱線を1つ。
私が初めてこの仕組みを現場に入れたとき、「営業 第1部」という部署でだけ動きませんでした。半角スペース入り。名前定義はスペースを含む名前を作れないので、「選択範囲から作成」がこっそり「営業_第1部」に変換してくれていたんです。
でも入力シートのB列には「営業 第1部」が入る。INDIRECTが探すのは「営業 第1部」という名前。存在しない。だから担当者欄が空っぽ。
原因に気づくまで1時間。お客さんの前で「ちょっと調べます」を3回言いました。
対処は、INDIRECTの中で置換をかませるだけです。
`=INDIRECT(SUBSTITUTE(B2," ","_"))`
「・」や「/」が入る部署名も同じ発想で、名前定義側の表記に合わせて置換してください。ちなみに名前定義は数字始まりもNGなので、「1課」みたいな部署は「_1課」になります。
こういう地味な落とし穴、Excelのヘルプにはまとまってないんですよね。現場で踏んで覚えるしかない。
Before/After:月10件→0件、照合作業は月2.5時間→0
まずは今使っている入力表の担当者リストが「1本のベタ並び」になっていないか確認してみてください。該当するなら、マスタシートに部署ごとの縦並び表を作って `Ctrl + Shift + F3` で名前定義するところまでを今日中にやるのがおすすめです。INDIRECTの設定は翌日でも構わないので、まずマスタ表だけ作って手を止めないことが一番の近道です。
導入前後で何が変わったか、数字で並べます。
導入前は、担当者リスト38名から目視で選ぶ方式。部署と担当者の不一致が月10件前後。経理の照合と差し戻しで月2時間半。しかも「今月はゼロだったか」を確かめる作業自体が毎月発生していました。
導入後は、担当者リストが部署ごと5〜8名に絞られ、不一致は3ヶ月連続で0件。照合作業そのものがなくなりました。経理担当者さんの言葉を借りると「間違えようがない表になった」。
作った側の工数は、マスタ作成と設定で最初の1回、30分弱。年間30時間の照合作業が30分の投資でなくなったと考えると、割に合わないわけがないですよね。
1つだけ残る穴と、その塞ぎ方
正直に言うと、この仕組みには弱点が1つあります。
部署を後から変更しても、担当者欄の値は自動で消えないんです。「営業部・田中」と入れたあとで部署だけ「経理部」に直すと、担当者欄には営業部の田中さんが残ったまま。入力規則は「新しく選ぶとき」しか効かないので。
私はこれを条件付き書式で塞いでいます。
C2:C100を選択
- 「ホーム」タブ→「条件付き書式」→「新しいルール」→「数式を使用して」
- 数式:`=AND(C2<>"",COUNTIF(INDIRECT(B2),C2)=0)`
- 書式:セルを赤く塗る
「担当者が空でなく、かつ現在の部署リストに含まれていない」セルが赤くなります。入力する本人が気づくので、経理まで流れません。
完全に自動で消したければVBAのWorksheet_Changeイベントになりますが、マクロ有効ブックの運用コストを考えると、私はまず条件付き書式を勧めます。赤くなれば人は直しますから。
こんな業務で使える
部署→担当者だけじゃなく、「親を選ぶと子が絞られる」構造ならぜんぶ使えます。
大分類→商品名(商品マスタからの受注入力)
- 取引先→支店名(請求書の宛先)
- 都道府県→市区町村(顧客台帳)
- プロジェクト→作業項目(工数入力)
3段階にしたいときは、2段目の値をそのまま名前にして `=INDIRECT(C2)` を3段目に書くだけ。理屈は同じです。
Microsoft 365ならFILTER関数で `=FILTER(担当者列, 部署列=B2)` をどこかのセルに書き、そのスピル範囲を入力規則で参照する方法もあります。ただ、名前定義×INDIRECTはExcel 2010でも動くので、社内のバージョンが混在している現場ではこちらが安全。私はまだこっちを標準にしています。
まとめ:今日やってほしい3つ
長くなったので、やることだけ絞ります。
部署を横、担当者を縦に並べたマスタシートを作る
2. マスタ全体を選んで `Ctrl + Shift + F3` →「上端行」で名前定義
3. 担当者列の入力規則に `=INDIRECT(B2)` を書く
まずは手元のExcelで、部署2つ・担当者3人ずつのミニ版を作ってみてください。5分で動きます。動くのを見てから、本番の表に入れる。この順番が一番失敗しません。
「リストにしてあるから大丈夫」と思っている表、あなたの職場にもありませんか? 候補が20件を超えているなら、それは連動させる価値があるサインです。
Excelスキルを資格で証明しませんか?
今回使った入力規則、名前定義、INDIRECT、条件付き書式。実はどれもMOS Excel(スペシャリスト/エキスパート)の出題範囲にそのまま入っています。とくに「データの入力規則」と「名前付き範囲の定義と参照」は、エキスパート試験では毎回のように問われる定番項目です。
現場で身につけたこういうスキルを資格という形で証明できると、転職の書類選考や社内の昇進面談で「Excelできます」が言葉だけで終わらなくなります。私自身、フリーランスになりたての頃に案件の単価交渉で資格が効いた経験があります。
もし興味があれば、MOS Excelの出題パターン攻略と実技対策をまとめた有料記事も書いています。この記事で扱った入力規則まわりも、試験でどう問われるかという角度で整理してあるので、覗いてみてください。もちろん、まずは今日のINDIRECTを職場の表に入れるところからで十分です。
