EXCEL – 重複も空白も無いドロップダウンリストを「データの入力規則」からセルを参照して作る方法

Excelにおいて、作業効率を高めるためにプルダウンリスト（ドロップダウンリスト）を使いたい場面は多々あります。
そのリストは、ほとんどの場合、重複しない値で、空白を含んでないほうが好ましいのではないかと思います。
「データの入力規則」で値を直接入力してもリストの作成が出来ますが、リスト表示する値が数多くある場合や変化する場合などは、シート上のセルの値を利用したくなります。
そのセルには重複する値や空欄が存在することを想定して、重複や空欄を含めない作業列を作り、それを参照してリストを作ります。

作成するドロップダウンリスト

• COUNTIF関数、INDEX関数、OFFSET関数等を使って実現します。
• 今回は、E～F列を作業列とし、C列に入力された重複のあるランダムな値から、重複のないリストをA1セルに作成します。

まずは作ってみます

C2セル以下に入力された値に対して、F2セルに下記の式を入れて下方へオートフィル（連続データ）します。

=IF(OR(COUNTIF(C$2:C2,C2)>1,C2=""),"",ROW())

そしてE2セルには、F列を参照する下記の式を入力してオートフィル（連続データ）します。

=IF(ROW(E1)>COUNT(F:F),"",INDEX(C:C,SMALL(F:F,ROW(E1))))

[データ]タブ > [データツール] > [データの入力規則]で、[データの入力規則]ダイアログを開き、[設定]タブの[入力値の種類]を「リスト」に、[ドロップダウン リストから選択する]にチェック、[元の値]には下記の式を入力して[OK]クリックで完了です。

=OFFSET($E$2,0,0,COUNTIF($E:$E,">!")-1,1)
#改めて検証しましたら以下の式で良さそうでした
=OFFSET($E$2,0,0,COUNTIF($E:$E,">!"),1)

計算式の解説

まずは、F2セル以下の式

=IF(OR(COUNTIF(C$2:C2,C2)>1,C2=""),"",ROW())

使用している関数は、

IF(論理式, [真の場合], [偽の場合])

論理式の結果（真か偽）に応じて、指定された値を返す

OR(論理式1, [論理式2],…)

いずれかの論理式が真の場合は真を返し、すべての論理式が偽の場合に偽を返す。

COUNTIF(範囲, 検索値)

指定した範囲において、検索条件に一致するセルの個数を返す。

ROW([参照])

参照の行番号を返す。参照が省略されている場合は自セルの行番号を返す。

の4つです。

COUNTIF(C$2:C2,C2)

絶対参照のC2セルを起点として“自セルと同じ行番号のC列セル”までの範囲に、“自セルと同じ行番号のC列セル”の値と同じ値がいくつあるか

OR(COUNTIF(C$2:C2,C2)>1,C2="")

その数が1つよりも多い、もしくは“自セルと同じ行番号のC列セル”が空白

=IF(OR(COUNTIF(C$2:C2,C2)>1,C2=""),"",ROW())

その条件が真の場合には空白、そうじゃない場合には行番号を返す。

これで、F列には、C列に入力された値が重複する場合でも、行番号が最も小さいセルのみの行番号が入ります。

E2セル以下の式

=IF(ROW(E1)>COUNT(F:F),"",INDEX(C:C,SMALL(F:F,ROW(E1))))

使用している関数は、

IF(論理式, [真の場合], [偽の場合])

論理式の結果（真か偽）に応じて、指定された値を返す

ROW([参照])

参照の行番号を返す。参照が省略されている場合は自セルの行番号を返す。

COUNT(値1, 値2,…)

範囲内において、数値が含まれるセルの個数を返す。

INDEX(配列, 行番号, [列番号])

行番号と列番号で指定される値またはセルの参照を返します。

SMALL(範囲, 順位)

指定された範囲で、順位番目に小さな値を返します。

の5つです。

ROW(E1)

行番号が1。下方へのオートフィルで1,2,3,…（E1,E2,E3,…）の連続データとするための便宜上。必要なのは行番号なので、必ずしもE列である必要はないが、削除や移動のないセルであるよう注意。
以下、この ROW(E1),ROW(E2),ROW(E3),… を n とする。

ROW(E1)>COUNT(F:F)

n が、F列において数値（行番号）が含まれているセルの個数より大きい。

SMALL(F:F,ROW(E1))

F列で n番目に小さい値

INDEX(C:C,SMALL(F:F,ROW(E1)))

C列において、F列でn番目に小さい値を行番号とするセルの値

=IF(ROW(E1)>COUNT(F:F),"",INDEX(C:C,SMALL(F:F,ROW(E1))))

n が、F列において数値（行番号）が含まれているセルの個数より大きい場合は空白を返し、それ以下の場合は、C列においてF列でn番目に小さい値を行番号とするセルの値を返す。

[データの入力規則]の[元の値]に入力する式

=OFFSET($E$2,0,0,COUNTIF($E:$E,">!")-1,1)
#改めて検証しましたら以下の式で良さそうでした
=OFFSET($E$2,0,0,COUNTIF($E:$E,">!"),1)

使用している関数は、

OFFSET(参照, 行数, 列数, [高さ], [幅])

参照から指定された行数と列数だけシフトした位置にあるセル範囲の参照を返す。

COUNTIF(範囲, 検索値)

指定した範囲において、検索条件に一致するセルの個数を返す。

の2つです。

COUNTIF($E:$E,">!")

E列に、空白でないセルがいくつあるか。E列の値が文字列の場合は検索値を “>!” とすることで空白以外の値の個数を返す（文字コード表で最初に現れる文字が“!” エクスクラメーション）。数値の場合は検索値を “>0″（0より大きい） や “>=0″（0以上） とすると良い。

COUNTIF($E:$E,">!")-1
#改めて検証しましたら以下の式で良さそうでした
COUNTIF($E:$E,">!")

E列において文字列を含むセルの全個数から、1行目（項目名セル）の個数1を差し引く。

=OFFSET($E$2,0,0,COUNTIF($E:$E,">!")-1,1)
#改めて検証しましたら以下の式で良さそうでした
=OFFSET($E$2,0,0,COUNTIF($E:$E,">!"),1)

E2セルを起点として、E列において項目名セル以外の文字列を含むセルの個数分の高さで幅が1の範囲を返す（幅は省略可能）。

以上、ご参考になれば幸いです。ありがとうございます。