部署名を一覧から選ばせる入力規則を作った。あとから部署が増えたので一覧に1行足したのに、ドロップダウンには出てこない。元の値が=一覧!$A$2:$A$6のまま固定されているからだ。
一覧をテーブルにして、名前の定義を1つ挟むと増えるようになる。もうひとつ、入力規則は手で打つときにしか効かないので、貼り付けや取り込みで入った値は止まらない。そちらは式で拾う。
以下の操作は、2026年9月21日にMicrosoft 365のExcel（バージョン16）で実際に動かして確かめた。設定と判定はプログラムからExcelを操作して読んでいるので、画面から同じ操作をしたときに違う結果になる箇所は、そのつど書いている。
一覧に足しても、ドロップダウンは増えなかった
=一覧!$A$2:$A$6で入力規則を作り、一覧のA7に「情報システム」を足した。入力規則の元の値を読み直すと=一覧!$A$2:$A$6のままだった。範囲は自分では伸びない。
足した「情報システム」を入力のセルに入れて、Excel自身の判定を読むと不正解だった。規則の側から見れば、一覧にない値が入っていることになる。
これはピボットテーブルが更新されない話と同じ構図だ。作ったときの範囲が記録されていて、元データが伸びても範囲は伸びない。
範囲を$A$2:$A$100のように広く取る手もあるが、空白のセルまで選択肢に入る。ドロップダウンの下のほうに空行が並ぶ。
テーブル名を直接書くと受け付けられない。名前を1つ挟む
一覧をテーブルにして「部署表」と名付けた。テーブルは行を足すと自動で伸びる。実際に1行足したら、テーブルの行数が6行から7行になった。
ところが入力規則の元の値に=部署表[部署]と直接書くと、受け付けられなかった。テーブルの列をそのまま指定することはできない。
名前の定義を1つ挟むと通る。「部署リスト」という名前を作って=部署表[部署]を指させ、入力規則の元の値は=部署リストにする。この形で一覧に1行足したら、足した値がそのまま選べるようになった。
名前の定義 部署リスト = 部署表[部署]
入力規則 = 部署リスト名前を挟むのが遠回りに見えるが、一覧の場所を変えたときに直すのが1か所で済む。入力規則を設定したセルが何百とあっても、名前の参照先だけ書き換えれば通る。
別のブックにある一覧は、指定できなかった
選択肢を別のファイルに持っている会社は多い。入力規則の元の値に='[部署マスタ.xlsx]Sheet1'!$A$1:$A$5と書いてみたが、受け付けられなかった。
名前の定義で外部のブックを指す方法も試した。名前そのものは作れて=[部署マスタ.xlsx]Sheet1!$A$1:$A$3と入ったが、その名前を入力規則の元の値に指定するところで止まった。画面から設定した場合に通るかどうかは確認していない。
どちらにしても、元のブックが閉じていたり、場所が変わっていたりすると動かなくなる。配るブックなら、選択肢のシートを同じブックの中に置くのが確実だ。マスタが更新されたら貼り直す、という手順を1つ足すことになるが、壊れない。
入力規則は、手で打つときにしか効かない
ここがいちばん誤解される。入力規則は、人がセルに文字を打ち込んだときに止めるためのものだ。貼り付けや、他のシステムから流し込んだ値、マクロで書き込んだ値は止まらない。
実際にプログラムからセルへ「情報システム」と書き込んだら、一覧にない値がそのまま入った。エラーも出ない。あとからExcelの判定を読むと不正解になっているだけだ。
もうひとつ、あとから入力規則を付けても、すでに入っている値は消えない。「廃止された部署」と入ったセルに規則を付け直しても、値はそのまま残る。規則を付けたから中身がきれいになる、ということはない。
規則のあるセルをコピーして規則のないセルに貼ると、規則のほうも一緒に移る。表の外に貼ると、そこにドロップダウンが増える。表の形を整えるつもりでコピーを繰り返すと、規則が散らばっていく。
COUNTIFで拾うと、Excelの判定と同じ20件が出た
止まらないものは、あとから数えるしかない。200行の入力に、一覧にない値を20件混ぜて試した。
Excel自身の判定で不正解になったのは20件。同じ行をCOUNTIFの式で数えたら、こちらも20件だった。数は一致している。
=IF($B3="","",IF(COUNTIF(一覧!$A$3:$A$200,$B3)=0,"リストにない",""))一覧の範囲に無い値なら「リストにない」と出る。Excelの無効データのマークと違って、印刷にも残るし、件数を数えられる。
中身を見ると、6種類に分かれた。「総 務」のように間に空白が入ったものが5件、一覧にない「情報システム」が5件、前に空白が付いた「 総務」が4件、ひらがなの「そうむ」が3件、「総務部」が2件、カタカナの「ソウム」が1件。
前後の空白は別の式でも出せる。
=IF($B3="","",IF(EXACT($B3,TRIM($B3)),"","前後に空白"))この式で出たのは4件だった。「 総務」のように前に空白が付いたものだけで、「総 務」のように間に入ったものは出ない。TRIMは前後しか見ない。間の空白はCOUNTIFのほうで拾う。
表記のゆれをどこまで直すかは名簿の突き合わせで一致しないときの話と同じで、一致率を測ってから決めたほうがいい。
COUNTIFは、アルファベットの大小だけ見逃す
拾う式にCOUNTIFを使っているので、その癖も確かめた。一覧に「ABC課」「第1営業」を置いて、いくつかの値を数えてみた。
「abc課」を入れるとCOUNTIFは1を返す。一覧にある「ABC課」と同じものとして数えている。COUNTIFはアルファベットの大文字と小文字を区別しない。だから「abc課」と打っても「リストにない」とは出ない。
区別したいならEXACTで数える。
=SUMPRODUCT(--EXACT(一覧!$A$3:$A$200,$B3))同じ「abc課」でこちらは0になった。文字が1つでも違えば0を返す。
全角と半角はCOUNTIFでも別物になる。一覧の「第1営業」に対して全角の「第１営業」を入れると0だった。末尾に空白が付いた「総務 」も0で、ちゃんと拾える。
部署名や取引先名にアルファベットが入らないならCOUNTIFで足りる。商品コードのように英字が混じる一覧なら、EXACTのほうに替える。配ったブックは部署名を想定しているのでCOUNTIFで書いてある。
一覧に足すか、打ち直すかを決める
「リストにない」と出た行は、2つに分かれる。一覧のほうが足りないのか、入れ方が間違っているのか。
「情報システム」は前者だ。部署が増えたのに一覧を更新していない。一覧に足せば、その行も以後の入力も通る。
「そうむ」「ソウム」「総 務」は後者で、打ち直す。ここで一覧に「そうむ」を足してしまうと、同じ部署が2つの名前で集計されるようになる。勘定科目を摘要から振り分ける話でも書いたが、表記を足して当てにいくと、あとで集計が割れる。
配ったブックでは、この判断を促す列を1つ足した。「リストにない」なら「一覧に足すか、打ち直す」、前後に空白だけなら「空白を取る」と出る。どちらを選ぶかは人が決める。
入力規則を付ける目的は、打ち間違いを減らすことであって、なくすことではない。規則をすり抜けた分を数える列を隣に置いておくと、月末にまとめて見られる。うちは「リストにない」が月5件を超えたら、一覧の作りを見直すことにしている。
