部署を選ぶと担当だけが出るリストは、名前の定義とINDIRECTで作る方法がよく紹介されている。ただしこのやり方は、部署名が名前として登録できる文字でないと作れない。
8個の部署名で試したら、4個は名前にできなかった。名前を使わずOFFSETで作る形にすると、8個とも動いた。
以下の数は、Microsoft 365のExcel（バージョン16）で実際に作って確かめたものだ。確認日は2026年9月22日。Windows 11で確かめている。使った部署名は次の8個にした。
営業 総務部
営業 1課 R&D
営業-2課 経理
1課 製造部部署名8個のうち4個は名前にできない
名前の定義を作ろうとして、できたものとできなかったものを分けた。
できたのは営業、総務部、経理、製造部の4個だ。できなかったのは、営業に半角スペースを挟んで1課を続けたもの、営業とハイフンで2課をつないだもの、1課、R&Dの4個になる。
落ちる理由は4通りある。名前に空白は使えない。ハイフンも使えない。数字で始まる名前は付けられない。アンパサンドのような記号も使えない。
実務の部署名はここに当たりやすい。第1営業部を1課と略したり、部署コードを頭に付けたり、全角スペースで区切ったりする。社内の呼び方をそのまま使うと、半分くらいが名前にならない。
INDIRECTは参照できないと返す
名前が作れなかった4個について、INDIRECTで引けるかも見た。
ISREFで確かめると、4個とも参照できないと返った。名前が存在しないので、INDIRECTが参照のエラーを返している。
気をつけるのは、COUNTAで数えると1と返ることだ。COUNTAはエラー値を1つとして数えるので、件数だけ見ていると候補が1件あるように見える。ドロップダウンを開くと何も出ない、という状態になる。
名前を使わないOFFSET版なら8個とも動く
一覧を横持ちではなく縦持ちにすると、名前が要らなくなる。
A列に部署、B列に担当を並べて、同じ部署の行をまとめて置く。担当の入力規則にはこう書く。
=OFFSET(一覧!$B$2,MATCH($B3,一覧!$A$2:$A$201,0)-1,0,COUNTIF(一覧!$A$2:$A$201,$B3),1)MATCHでその部署が最初に出てくる位置を探し、COUNTIFで件数を数えて、その高さぶんだけ切り出す。名前を通らないので、部署名の文字は何でもよい。
8個の部署名すべてで、候補がその部署の3件だけになった。空白の入ったものもR&Dも1課も同じように出る。
部署のほうのリストも名前なしで作れる。重複を除いた一覧を作業列に出して、文字の入っているセルの数だけ切り出す。
=OFFSET(一覧!$H$2,0,0,MAX(1,COUNTIF(一覧!$H$2:$H$201,"?*")),1)COUNTAではなくCOUNTIFに?*を渡すのは、式が返す空文字をCOUNTAが数えてしまうからだ。COUNTAで数えると候補が200件になり、193件の空白が並ぶ。
部署を選び直しても、担当は前のまま残る
絞り込みができても、これは直らない。
部署を営業にして担当を選んだあと、部署を経理に変えた。担当の欄は営業の人のまま残る。入力規則は値を入れるときにしか働かないので、あとから条件が変わっても消してくれない。
だから入力規則だけに頼らず、組み合わせが合っているかを別に数える。
=IF(COUNTIFS(一覧!$A$2:$A$201,$B3,一覧!$B$2:$B$201,$C3)=0,"合っていない","合っている")一覧にその組み合わせが無ければ合っていないと出る。貼り付けで入った値も同じように拾える。入力規則が貼り付けをすり抜ける話はエクセルの入力規則に書いた。
一覧の末尾に足すと、件数は合うのに別の人が出る
OFFSET版の弱点はここだ。
経理の3人が並んだ一覧の末尾に、経理担当04を足して試した。候補の件数は4件になる。数だけ見ると正しい。
中身を見ると、経理担当01、経理担当02、経理担当03、製造部担当01だった。足した人は出ず、次の部署の1人目が混ざっている。
MATCHは最初に見つかった位置を返し、COUNTIFはシート全体の件数を返す。その2つを組み合わせて高さを決めるので、部署の行が離れた場所にあると、間の行を巻き込む。
件数が合っているせいで気づきにくい。重複を数えるときと同じで、数が合うことと中身が合うことは別になる。
一覧がまとまっているかを検査する
離れているかどうかは式で分かる。その部署が最初に出てくる位置と最後に出てくる位置の差が、件数と合うかを見る。
最初 =MATCH($A2,$A$2:$A$201,0)
最後 =LOOKUP(2,1/($A$2:$A$201=$A2),ROW($A$2:$A$201))-ROW($A$2)+1
件数 =COUNTIF($A$2:$A$201,$A2)
判定 =IF($E2-$D2+1=$F2,"まとまっている","散らばっている")最後の位置はLOOKUPで下から探す。1/(範囲=値)は当てはまらない行を0で割るエラーにするので、LOOKUPが最後に当たった行を拾う。
さっきの末尾に足した例でこの式を入れると、経理だけが散らばっていると出た。ほかの部署はまとまっていると出る。
直し方は2つある。A列で並べ替えるか、その部署の行の続きに挿入して足す。どちらでも直る。
FILTERは入力規則に直接入らない
365ならFILTERで書けそうに見える。試すと入力規則に入らなかった。
=FILTER(一覧!$B$2:$B$26,一覧!$A$2:$A$26=$A2)この式をデータの入力規則に指定するとエラーになる。同じ範囲でOFFSETの式は入る。入力規則はスピルする式を受け付けない。
セルにFILTERを書いてスピルさせ、その結果をスピル参照で指定する方法はある。ただし行ごとに条件が違う表では、行の数だけスピル先を用意することになって現実的でない。
古い版でも動くことを考えると、OFFSETで組むほうが持ち回りやすい。
名前が使えるときは名前でもよい
部署名が名前にできる文字ばかりなら、名前の定義とINDIRECTのほうが式は短い。
=INDIRECT($B3)これだけで済む。読む人にも意味が伝わりやすい。部署が増えないなら、この形で困らない。
困るのは増えたときだ。名前の定義は範囲を固定で持つので、一覧に行を足しても候補が増えない。部署が1つ増えるたびに、名前を1つ追加する作業が要る。
部署が10個あれば名前が10個並ぶ。名前の管理を開くと、どれが生きているのか分からなくなる。使わなくなった部署の名前も残り続ける。
OFFSETで組む形にしておくと、一覧に行を足すだけで済む。名前は1つも作らない。管理する場所が一覧の1か所に寄る。
配ったブックの使い方
3枚入れてある。入力のシート、一覧のシート、使い方だ。
一覧のシートにはA列とB列を埋める。右側に最初の位置、最後の位置、件数、並びの判定が出る。散らばっていると出た行は赤くなる。
入力のシートは、部署を選ぶと担当の候補が絞られる。見本の6行のうち1行はわざと合わない組み合わせにしてあり、1行は担当を空にしてある。検査の欄に1件ずつ出る。
セル結合をやめる話と同じで、見た目を作り込む前に、値がそろっていることを機械で確かめられる形にしておく。
確かめていないこと
3段階以上の絞り込みは試していない。部署から課、課から担当と重ねる形でも同じ考え方で組めるはずだが、確かめてはいない。
Web版のExcelとGoogleスプレッドシートでは試していない。入力規則の式の書き方が違う。
一覧が1,000行を超えたときの重さも測っていない。OFFSETは再計算のたびに動く式なので、行が増えると遅くなる可能性がある。
