Excel 2019で、シート①の商品リストからシート②で部分一致入力に応じてプルダウンリストを表示する方法は、データ検証と動的範囲関数を組み合わせることで実現できます。この記事では、半角・全角・数字の違いにも対応できる設定方法と、無限下方向にも適用可能な手順を解説します。
1. 名前付き範囲の設定
まず、シート①の商品リストを範囲として名前を付けます。
例: シート①!A:A を選択し、数式タブ → 名前の定義 → 名前に「商品リスト」と設定します。
これにより、後で関数やデータ検証でこの範囲を参照できます。
2. 補助列で部分一致を抽出
シート②の作業列（例: 列Zなど）に、入力された文字列に応じて商品名を抽出する補助列を作ります。
例: =IF(ISNUMBER(SEARCH(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(A2,” ”, ” “),”０”,”0″),”１”,”1″), 商品リスト)), 商品リスト,””)
このようにSUBSTITUTE関数で全角・半角数字やスペースを統一すると、部分一致検索が安定します。
3. 動的なプルダウンリスト作成
補助列で抽出された結果を元に、B列にプルダウンリストを作成します。データタブ → データの入力規則 → 設定 → 元の値に補助列範囲を指定します。
動的範囲を利用すれば、新しい商品が追加されても自動で候補に反映されます。OFFSET関数やテーブル形式を活用すると便利です。
4. 下方向への無限適用
B列の最初のセルに入力規則を設定したら、セルの下方向にコピーすることで同じルールを適用できます。テーブル形式に変換しておくと、新規行にも自動で入力規則が引き継がれます。
これにより、データ量が増えても同じ条件で部分一致プルダウンが機能します。
5. 実例: 商品注文シートの運用
例えば、シート①に商品リストA～Zまで登録し、シート②で受注入力時に「ケー」と入力すると、「ケーキ」「ケーブル」などがB列のプルダウン候補に表示されるように設定しました。半角・全角・数字の違いも補助列で統一処理したため、入力ミスが減少しました。
まとめ
Excel 2019で部分一致入力によるプルダウンリストを作成するには、名前付き範囲、補助列による文字統一・部分一致抽出、動的範囲によるデータ検証の組み合わせが有効です。テーブル形式を活用することで、新規追加の商品にも自動で対応できる便利な設定になります。
コメント