Excelのプルダウンを連動させるには？親子の候補と変更後の値を確認

※本記事にはPRを含む場合があります。

こんにちは。『脱会社員』脱サラを応援し続けるブログ、運営者の『なかびー』です。

受注台帳で「紙もの」を選んだら、隣の品目にはカードやしおりを表示したい。「布もの」ならポーチや袋から選びたい。Excelのプルダウンを連動させると、前の選択に応じた候補を次のセルで選べるようになります。

ただし、候補が切り替わることと、すでに入力した値が安全に書き換わることは別です。紙もの・カードと入力した後、区分だけ布ものへ変えたら、品目に古いカードが残る場合を考える必要があります。過去の取引を残す台帳なら、古い値を消す前に、元の組合せを保存する必要があるかも確認しましょう。

この記事では、Windowsのデスクトップ版Excelを想定し、名前の定義とINDIRECT関数を使った二段階の候補を、一つの練習表で整理します。シート名、セル、式、変更後に点検する順番を固定して説明します。画面の表示や操作はExcelの版・設定で異なることがあります。この作例は記事作成時に実機での操作を確認していないため、業務に使う前に必ずコピーした練習用ブックで試してください。

まず単独のプルダウンから作りたい場合は、公開中のExcelドロップダウンの基本と別シートの候補管理も参考になります。そちらの基本を踏まえ、この記事では二つのセルの関係と、変更後に残った値の扱いに進みます。

Excelの連動プルダウンで何を変え、何を残すか

連動プルダウンでは、一つ目のセルを「親」、その選択に応じて候補が変わる二つ目のセルを「子」と呼びます。この例の親は商品の区分、子は品目です。親が紙ものならカードとしおり、布ものならポーチと袋を候補にします。

入力シートにはB列に区分、C列に品目を置きます。二行目で紙ものを選んだ場合は、C2で紙ものの候補を確認します。三行目で布ものを選んだ場合は、C3で布ものの候補を確認します。二行目と三行目は別の取引なので、二行目の変更が三行目の組合せを変えないこともテストします。

表は横にスクロールできます。品目と取引は説明用の架空例です。

候補と入力済みの値は別のもの

候補表を直すと、次に選ぶ候補が変わります。一方、過去に選んだセルの値は、候補表そのものではありません。候補から品目を削除しても、古い入力値が記録に残る場合があります。反対に、入力セルを書き換えれば、元の取引の内容をそのセルだけから復元できなくなる可能性があります。

たとえばカードの注文を受けた記録でB2だけを布ものへ変更すると、B2とC2の意味が食い違います。別の注文に入力し直すために一時的に変更したのか、以前の注文を訂正したいのかによって、必要な扱いは違います。練習では単に選び直せますが、本番データでは変更前の組合せ、変更理由、確認した人などの履歴を残す必要があるか先に考えましょう。

この記事でいう「残す」は、すべての業種で同じ保存方法が義務という意味ではありません。帳簿や契約に関係する保存条件は内容によって変わります。業務上必要な情報を消す前に、社内の手順や専門家へ確認するという意味です。

練習用ブックから始める

新しいブックに「入力」と「候補」の二つのシートを用意します。既存の受注台帳を直接使うと、貼り付けや設定の変更で作業中のデータを傷めるかもしれません。まず架空の品目だけを入力し、設定と確認の順番を試します。

ファイルを誰かに渡す場合も、最初にテスト用と本番用を区別します。取引先の名前や住所を練習データにコピーする必要はありません。表の列を増やす前に、どのセルが候補を持ち、どのセルが入力を受けるのかを先に決めましょう。

練習の目的は、候補を二つ出すことだけではありません。三行に異なる状態を作り、「選べる」「まだ選んでいない」「以前の選択が現在の区分と合わない」を区別します。三つが混ざっていると、どの行に連絡や修正が必要なのか分かりにくくなります。

一行目の見出しを置いた下に、二行目は確認中の注文、三行目は別の取引、四行目は新規入力用の空欄という想定にします。番号や金額を記入しなくても、二つのセルの関係を追う練習はできます。最初から複雑な業務台帳を使わず、確認したい操作を一つずつ試せる大きさにしましょう。

元の注文がどの状態だったかを残すには、元の台帳の複製、日付を付けた変更記録、訂正用の別行などの方法があります。どれを使うかは、共有方法やデータの機密性、必要な記録の範囲によって変わります。単にファイルを増やせば安全になるわけではなく、どれが最新版か分かる名前と保管場所も決めます。

変更する前の確認を忘れやすい場合、入力シートの上部に「変更前の取引内容を確認」と短く書く方法もあります。これは操作上の注意を見やすくする工夫であり、誤操作を技術的に防止するものではありません。実際に入力する人がどこで迷うかを聞き、注意書きが長すぎて読まれない場合は見直してください。

親子の候補表と名前を対応させる

「候補」シートのA1に区分、A2に紙もの、A3に布ものを入力します。次にB1を紙もの、B2をカード、B3をしおりとし、C1を布もの、C2をポーチ、C3を袋にします。B1とC1は見出し、B2:B3とC2:C3は実際に選ぶ品目です。

セルの範囲には意味のある名前を付けます。A2:A3に「区分」、B2:B3に「紙もの」、C2:C3に「布もの」という名前を定義します。候補シートでA2:A3を選択し、［数式］タブの［名前の定義］を開きます。名前に「区分」、範囲に「ブック」を指定し、参照範囲が候補シートのA2:A3であることを確かめて確定します。同様にB2:B3を選んで「紙もの」、C2:C3を選んで「布もの」をブック範囲でそれぞれ定義します。名前の管理画面では、ブック内のほかの同名定義や参照先を確認します。名前はExcelの命名規則に従う必要があり、自由な文章やセル番地と同じ形の名前をすべて使えるわけではありません。

例では、親のセルで選ぶ文字「紙もの」と、子候補の範囲に付けた名前「紙もの」が対応します。表示名と定義名の間に空白が入ったり、似た文字を使ったりすると、意図した範囲につながらない可能性があります。見た目だけで判断せず、候補表の文字と名前の管理画面を照らし合わせましょう。

Microsoftは、ドロップダウンの候補を別のワークシートに置き、その範囲に名前を定義する方法を案内しています。実際の画面で選ぶ位置や表記は版によって異なるため、Microsoftの入力規則の案内も確認してください。

名前は見出しではなく範囲につける

B1の見出しだけに名前を付けると、選びたいカードとしおりの二つを含む範囲にはなりません。B2:B3を選んだ状態で名前を付け、参照先が候補シートのB2:B3になっているか確かめます。同様に布ものもC2:C3です。

候補を選択するときに見出しが含まれる場合は、定義名が指す範囲を確認します。選択肢に空欄が並ぶ場合も、予定より下の空セルまで含めていないかを見ます。候補表で見えている範囲と、名前の管理画面の参照先は別々に確認するのがポイントです。

次に「入力」シートのB1に区分、C1に品目と入力します。B2:B4が親、C2:C4が子です。B2:B4を選び、［データ］から［データの入力規則］を開き、入力値の種類をリストにして、元の値に=区分を指定します。設定後にB2を選び、紙ものと布ものが候補に表示されるか確認します。

ここでは候補を固定範囲として扱います。Microsoftはテーブルを使った候補の追加方法も紹介していますが、この記事の範囲名がいつでも自動拡張されるとは仮定しません。まず定義名と参照範囲の関係を確認できるようにしておきます。

候補表は、最初に列ごとの担当を決めてから入力します。A列は親の選択肢、B列とC列はそれぞれ一つの親に対応する子の選択肢です。A2:A3を親の入力規則に使い、B2:B3とC2:C3を子の入力規則から参照します。見出しの文字と実際の範囲名を同じにしたのは、この対応を追いやすくするためです。

名前の管理画面では、「名前」「参照先」「適用範囲」をまとめて確認します。同じブックに同名の定義がすでにある場合や、特定のシートだけに適用される名前がある場合は、意図しない定義を見てしまう可能性があります。練習用の新しいブックでブック全体の名前を一つずつ作り、元のシートと範囲をメモしておきましょう。

シート名や候補名を後から変える場合も、見えている見出しを書き換えるだけでは足りない場合があります。親の候補の文字、定義名、定義名が指すセル範囲、入力規則の式は別々の場所にあります。どこを変えたか分からなくなったら、新しい値をいくつも入れる前に、この四つを順番に見ます。

親候補の設定では、B2:B4を選んでいるつもりが一セルだけになっていないか、またはB列以外を含んでいないかに注意します。三行すべてが同じ親候補を表示できることを確認してから、子の設定へ進みます。行を増やす予定がある場合も、設定済みの範囲を記録し、末尾の新しい行まで規則が入っているか別に確かめます。

INDIRECTで同じ行の親から子の候補を選ぶ

入力シートのC2を選び、データの入力規則でリストを指定します。元の値に=INDIRECT($B2)と入力する設定を練習します。B2が紙ものなら、INDIRECTが文字「紙もの」を参照として扱い、同名の範囲につながる設計です。B2が布ものなら、名前「布もの」が指す候補へ切り替える考え方です。

INDIRECTは文字列で指定された参照を返す関数です。名前の付け方、存在しない名前、参照できない範囲では期待どおりの候補になりません。詳しい構文はMicrosoftのINDIRECT関数を確認できます。

ドル記号と数字を一緒に見る

$B2はB列を固定し、行番号を固定しない書き方です。C2ではB2、C3ではB3、C4ではB4を見る設計です。もしすべての行が$B$2を参照すれば、三行目や四行目も二行目の親を見てしまいます。

続いてC3を選び、［データ］→［データの入力規則］でリストを選んで元の値に=INDIRECT($B3)を設定します。C4も同様に、元の値を=INDIRECT($B4)として個別に設定します。親が空欄のときの警告や確定方法は版によって異なる可能性があるので、必要ならまず各行の親を選択して設定し、実際の画面で確認してください。

入力規則をコピーする場合は、セルを選び直して元の値を確認します。コピーの方法や選択した範囲によっては、入力値まで上書きするおそれがあります。練習ブックで空のセルに設定し、C2、C3、C4の式がそれぞれ同じ行のB列を見ているかを確認しましょう。既存の取引データへ直接貼り付ける前に、設定だけを扱う方法を確認します。

二行目のB2で紙もの、三行目のB3で布ものを選びます。次にC2の候補にカードとしおり、C3の候補にポーチと袋が出ることを期待します。もし両行に同じ候補が出た場合は、C3がB2を見ていないか、名前の範囲を取り違えていないかを順に調べます。この記事ではこれらを実際のExcel画面で検証していないため、利用する版で表示と動作を試してください。

親が空のときと、名前がないとき

B4が空欄の状態では、INDIRECT($B4)が子候補として使える範囲を返すとは限りません。まず親を選んでから子を確認します。空欄の親に対してどの画面や警告が出るかは、版や入力規則の設定を含めて確認が必要です。

名前の管理画面に紙ものがあっても、B2の文字が「紙 もの」のように異なれば同じ名前とは限りません。名前の一致、名前の参照範囲、入力規則の式、親セルの値の順で点検します。いきなり関数を何度も書き換えるより、どの段階の対応が崩れたのかを分けると、原因を追いやすくなります。

式の動きを言葉にすると、「この行のB列に書かれた文字を読み、その文字と対応する名前を参照する」という流れです。B2に紙ものが入ったとき、候補表のA2を直接探して次の列へ移動する式ではありません。紙ものという名前が指す範囲を使うため、親の文字と定義名の対応が重要になります。

三行目のテストでは、二行目と異なる親を選んだ後でC3を開きます。C2でカードを選べたからといって、C3の式も正しいとは限りません。C3に表示される品目、C3の入力規則の元の値、名前「布もの」の参照先を照合します。間違いが見つかったら、どこを変えたかメモし、二行目も再度確認してください。

親に入力する表示が、Excelの名前に使えない文字を含む場合は、この単純な対応をそのまま利用できないことがあります。表示名から別の定義名へ変換する設計も考えられますが、その対応表や例外処理は別途検証が必要です。この記事の練習では、名前として定義できる二つの短い文字に限定します。

別のファイルから範囲を参照する方法も、閉じたブックの扱いなど追加の条件が生じます。この作例では候補と入力を同一ブック内の別シートに置きます。候補シートを隠すことと、閲覧権限を制限して情報を保護することも同じではありません。共有してはいけない情報を候補表に置かないようにしましょう。

親の変更前に履歴を確かめ、子を選び直す

練習表に戻り、B2を紙もの、C2をカード、B3を布もの、C3をポーチとします。ここで二行目の親を布ものに変える例を考えます。作業前に、この二行目が過去のカードの取引を表すのか、未確定の入力途中なのかを確認します。過去の記録が必要なら、親と子の元の組合せを先に控え、変更の理由と時点が分かる方法を検討します。

その後B2を布ものへ変え、C2を確認します。C2にカードが残る場合は、現在の親と子が食い違っています。二段階の候補が設定されていても、親を変えた時点で既存値が必ず自動消去されるとは考えません。残り方や警告は実際の環境で点検し、古い値を必要に応じて保全してから、C2を布ものの候補にある袋へ選び直します。

親の変更前にも、子を上書きする前にも、何が失われるかを確認するのが大切です。作業途中の下書きなら選び直しで済むことがありますが、納品済みの取引行なら、修正理由や以前の内容が必要かもしれません。既存の行を上書きせず、新しい行に訂正を記録する運用が適切な場合もあります。実際の運用は利用目的と保存方針に合わせて決めましょう。

三行目が変わっていないか確認する

B2を変更したとき、B3は布もの、C3はポーチのままであることを確認します。C2を袋へ変更したことが、別の取引を表すC3まで変えたように見える場合は、セルの参照先や選択範囲を確認します。

二行目だけで成功を判断しない理由は、連動の式が複数行へ正しく設定されたかを確かめるためです。二行とも同じ親を選ぶテストだけでは、常にB2を参照している誤りを見逃すことがあります。最初に紙ものと布ものを別々の行へ置き、それぞれの子候補を確認します。

入力規則は、取引の意味や履歴まで判断する機能ではありません。表を使う人に「親を変えたら子も確認」「変更前の履歴が必要なら先に控える」と伝えます。利用者が多い表では、変更できる人や、確認が必要な項目を別の説明欄に置くと引き継ぎやすくなります。

変更の場面を時系列で書くと、最初に「紙もの／カード」という組合せがあり、次にB2だけが「布もの」へ変わります。この途中では、画面に「布もの／カード」と表示される可能性があります。そこで旧カードの記録が必要かを確認し、必要なら控えてからC2を袋へ選び直します。最後に「布もの／袋」が新しい状態です。

三つの時点を区別すると、何を変更したのか説明できます。旧値を控える前に上書きしてしまうと、そもそも紙もののカードだったのか、紙もののしおりだったのか後から分からないかもしれません。操作する人だけでなく、変更を依頼した人や確認する人が異なる場合は、記録の受渡しも考えましょう。

別の方法として、確定済み行を直接変更せず、訂正するための新しい行を設けることも考えられます。ただし、一件を二件として集計しないよう、元の行と訂正行の関係、どちらが有効かを分かる形で管理する必要があります。連動プルダウンの設定だけでこの関係を作れるわけではありません。

練習でわざと不一致を作るときは、本番の帳票で誤ったまま保存しないようにします。作業前にコピーを作り、テスト中であることが分かるファイル名を付けます。確認後は、何を試し、どの結果が想定と異なったかを記録し、利用する版で再現できるか確かめます。

空欄と貼り付け後の不一致を分けて点検する

今度は、B3に別の場所から紙ものを貼り付け、C3に元のポーチが残る場合を考えます。入力規則の警告だけに頼り、貼り付けられた値が必ず拒否されるとは決めつけません。Microsoftは、コピーやオートフィルで入力されたデータには入力規則のメッセージが表示されない場合があると案内しています。

少ない行なら、候補の対応表を横に置き、B列の区分とC列の品目を目で照合できます。紙ものにポーチが組み合わされていたら、入力者や元の取引情報を確認し、どちらを訂正すべきか判断します。見た目が合うように片方を勝手に書き換えると、元々の注文とは違う内容になるかもしれません。

B4が紙ものでC4が空欄の場合は、まだ品目が選ばれていない状態です。紙もの・ポーチのように候補と食い違う状態とは分けて記録します。空欄が許されるかは業務によります。注文受付中の未確定欄なら空欄を残し、出荷前には埋める、といったルールを自分の表に合わせて決めてください。

「無効なデータを円」で何が分かるか

Microsoftの入力規則の説明には、既存の無効な値を円で示す機能があります。設定後のセルや過去の値を点検する助けになりますが、この作例で親を変えた直後の子や、空欄の親を持つ行がどのように表示されるかは実機未確認です。円が付かないことだけで、すべての親子の組合せが正しいと判断しません。

点検を始める前に、親と子の対応表を決めます。この例では紙ものにカード・しおり、布ものにポーチ・袋です。B2:C4を一行ずつ見て、空欄、対応する組合せ、対応が確認できない組合せに分けます。対応が確認できない行は修正前の値を記録し、注文内容を確認します。

自動検出用の別の列や数式を作る方法もありますが、元の一覧にない品目や空欄をどう判定するか、数式の参照先をどこまで拡張するかで結果が変わります。未検証の式を貼る前に、まず表の責任者が親子の一覧と例外の扱いを決めることが先です。

すでに多数の行を含む本番ファイルでは、いきなり一括で修正せず、コピーで対象件数と元の値を確認します。個人情報や注文番号が含まれる場合は、確認する人に必要な範囲だけを共有し、外部への送付先も間違えないようにします。

目視で点検する際には、区分ごとに許される品目を一覧化し、行番号と確認結果を別紙に控える方法があります。三行の例なら、二行目は布ものと袋、三行目は紙ものとポーチ、四行目は紙ものと空欄です。二行目は対応表にある組合せ、三行目は確認が必要な組合せ、四行目は品目未入力として分けます。

三行目を修正すると決まった場合は、先に元の紙もの／ポーチを控える必要があるかを確認し、元の取引資料を見てから紙もの／しおりへ直す、という順番にします。これは架空例の作業手順であり、実際の注文がしおりだと推測するものではありません。取引先への連絡が必要な場合は、その会社の正式な連絡経路と社内の確認手順を使います。

空欄を許可する設定でも、後工程で品目が必要になることがあります。受付時には空欄で保存し、出荷前に別の人が確認するなら、「入力時の許可」と「出荷前の確認」は別のルールです。セルの入力規則が通ったことだけで、すべての業務上の条件を満たしたと扱わないようにします。

コピーやオートフィルの後は、値だけでなく入力規則そのものが残っているかも確認します。別のセルから貼り付けたときに書式や規則まで変わるかどうかは、操作方法や環境によるため、練習ブックで確認してください。重要なのは、警告を見なかったことと、誤りが存在しないことを同じにしないことです。

候補を追加した後も行ごとの状態を確かめる

最後に、紙ものの候補に封筒を追加する例を考えます。候補シートのB4へ封筒を入力しても、名前「紙もの」がB2:B3のままなら、B4がその名前の範囲に含まれるとは限りません。名前の管理画面で参照先を確認し、B2:B4へ変更する作業を行います。

変更後は、入力シートのB4で紙ものを選び、C4の候補としてカード、しおり、封筒が表示されることを期待します。表示が変わらないときは、まずB4が紙ものか、名前「紙もの」の参照先が候補シートのB2:B4か、C4の式がB4を見ているかを一つずつ確認します。

この例は手で定義範囲を変更する方法です。テーブルを使う場合や動的な範囲を使う場合は設定と動作が異なるため、この記事の操作結果をそのまま当てはめません。また、封筒という候補を追加しても、過去のカードを封筒へ変更する必要が生じるわけではありません。候補を追加する操作と、すでに記録された取引の修正は分けて考えます。

候補を変更・削除するとき

カードという名称を別の呼び方に変える場合は、候補表の一セルを書き換える前に、過去の入力にその名称がどれだけ使われているか確認します。名称の変更が商品そのものの変更なのか、表示だけの変更なのかによって、古い記録を残す必要が変わるからです。

候補を削除する場合も同じです。使わなくなった品目を新しい注文から選べなくすることと、古い注文の内容を消すことは別です。現在の候補にない値が過去データに残っても、その行をすぐ誤りと決めず、記録した時点と内容を確認します。

追加後の二行をもう一度比べる

封筒を追加した後、B4が紙もののときにC4を開き、候補の中に封筒があるかを確認します。次にB3を布ものとし、C3を開きます。このとき、布もの側に封筒を追加するつもりがなければ、候補表と名前の参照先を照合し、紙もの用の範囲を見ていないか確認します。

ここでも、すでにC3に入っている値と、これからC3で選べる候補は分けて見ます。候補として表示されないからといって、その時点で過去の値が消えたとは限りません。反対に、現在入力されている文字が候補に見える場合でも、その行の取引が正しいことまでは証明できません。

候補を追加した人は、利用者へ新しい品目名と、どの区分に属するかを伝えます。別の人が布ものの列にも同じ品目を追加した場合、同じ名前でも両方で扱う意図があるのか確認が必要です。名前や品目を増やすほど、誰が定義を管理し、どこに変更を残すかが大切になります。

この作例は同じ品目名が複数の区分に現れないようにしました。複数区分に同じ表示名を使う場合でも、行の親子が対応表にあるかは確認できますが、品目名だけから取引を一意に特定できるとは限りません。商品番号や取引番号など、業務に必要な識別情報は別の列で考えます。

次に、候補の範囲を元に戻した練習ブックでも挙動を見ておくと、削除後の確認に役立ちます。封筒を入力したC4が残るか、候補から除いたときにどんな表示や警告があるかは、そのExcel環境で確認してください。実施前のファイルと変更後のファイルを区別し、どちらを本番として渡すかを決めます。

確認項目を残す際は、テストしたExcelの版、確認した日、担当者、設定したセル範囲も記録します。想定と違ったときは「連動しない」だけで終わらせず、親セルの値、子セルの式、定義名の参照先、実際に見えた候補を書きます。共有の際は取引先名など不要な情報を除き、問題を再現できる架空データで相談できます。

利用者から新しい候補の追加を頼まれたら、名前だけを受け取るのではなく、どの区分に属し、いつから使うかを確認します。既存の取引に同じ名前がないか、表記が似た別の商品ではないかも調べます。確認が終わる前に候補へ追加すると、選ぶ人ごとに違う意味で使ってしまう可能性があります。

他の人へ渡す前の確認

練習ブックで、少なくとも次の五つを試します。二行に異なる親を入れて子の候補を確認すること、親変更前の記録を確認すること、変更後の子を点検すること、貼り付け後の不一致を見直すこと、候補を追加して範囲を確認することです。各結果は利用するExcelで実際に確かめ、想定と異なれば手順を直します。

引継ぎ用には、候補シートの場所、名前の管理画面で確認する範囲、対象となる入力行、変更担当を短く書きます。「候補にない値は必ず消す」といった指示だけでは、過去の取引を消してしまうかもしれません。修正前の確認先と、必要な履歴の残し方まで決めておきます。

連動プルダウンは、入力する候補を絞るための仕組みです。親と子が合っているか、過去に何が入力されたか、貼り付け後の値が正しいかを自動的に保証するものではありません。まず架空の三行で参照と変更の順番を試し、利用するExcelと実際の仕事に合う点検方法を整えてから本番の台帳へ取り入れてください。