｢StiLL｣Excel情報487　関数--オートフィルターを使わずにリスト表示のフィルターを実現

■オートフィルターを使わずに、入力規則のリストで条件にあったリスト表示を行う方法のご案内です。
データ入力規則のリスト表示で、一方のリストを選択すると、それに連動して他方のリストの表示も変更させる方法をExcel関数を使ってご紹介します。

☆対応方法：
使う関数は、OFFSET,MATCH,COUNTIFです。

ご注意：
組織テーブルの事業部、所属事業部は名前順に並べ替えておく必要があります。

データの入力規則で、リスト表示を行う際に可変条件に沿ってリスト表示も変えたい等の

経験があるかと思いますが、Excel関数を使えば簡単に表示切替ができます。

例えば組織テーブルより事業部を選択し、営業所の入力では選択した事業部の営業所リストのみを

表示させたい場合です。

[ 組織テーブル ]

[ 部門入力 ]

【考え方】

例えば、「B事業部」を選択した時に、「営業所」リストの先頭から、「営業所B-1」～「営業所B-3」まで

参照をずらす(シフトする)ことを考えます。

範囲をシフトするのは、OFFSET関数を使います。

OFFSET関数は、以下の引数で構成します。

=OFFSET(基準, 行数, 列数, [高さ], [幅])

今回は、各引数に以下の内容を当てはめます。

=OFFSET(「①営業所の先頭セル」, 「②B事業部がある先頭の行」, 0, 「③B営業所の数=高さ」, 1)

②は、MATCH関数を使い、選択した事業部の先頭セルを、所属事業部の列範囲から求めます。

=MATCH(「選択した事業部のセル」,「所属事業部の列範囲」,0)

③は、COUNTIF関数を使い、所属事業部の列から、選択した事業部の数を求めれば、

高さと同じ事になります。

=COUNTIF(「所属事業部の列範囲」,「選択した事業部のセル」)

【手順１】組織テーブルの事業部、所属事業部に名前を定義します（任意）

【手順２】入力セルにデータの入力規則を設定します

データの入力規則の設定は、入力セルを選択した状態で、

[メニュー] → [データ] → [データの入力規則]を選択します

① 事業部の入力規則設定

[入力値の種類]を "リスト"

[元の値]を "=事業部" と入力します

[OK]を押す

② 営業所の入力規則設定

[入力値の種類]を "リスト"

[元の値]に

=OFFSET($C$3,MATCH($G$3,所属事業部,0),0,COUNTIF(所属事業部,$G$3),1)

と入力します

[OK]を押す

MATCH($G$3,所属事業部,0)　：　①の事業部で選択した名称の所属事業部の先頭位置

COUNTIF(所属事業部,$G$3)　：　①の事業部で選択した名称の所属事業部の個数

OFFSET( )　：　上記関数で求めた値で営業所リスト範囲を設定

③ 完成

各「事業部」を選択して、「営業所」のプルダウン内容が変更されるかご確認ください。

ご注意

組織テーブルの事業部、所属事業部は名前順に並べ替えておく必要があります。

（Excel関数の設定内容の詳細はExcelのヘルプをご確認下さい）