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

EXCEL
この記事は約6分で読めます。
スポンサーリンク

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

スポンサーリンク

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

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

セルを参照する重複しないEXCELプルダウンリスト

まずは作ってみます

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

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

セルを参照する重複しないEXCELドロップダウンリスト

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

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

セルを参照する重複しないEXCELドロップダウンリスト

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

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

セルを参照する重複しないEXCELドロップダウンリスト

計算式の解説

まずは、F2セル以下の式

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

使用している関数は、

IF(論理式, [真の場合], [偽の場合])
論理式の結果(真か偽)に応じて、指定された値を返す
OR(論理式1, [論理式2],…)
いずれかの論理式が真の場合は真を返し、すべての論理式が偽の場合に偽を返す。
COUNTIF(範囲, 検索値)
指定した範囲において、検索条件に一致するセルの個数を返す。
ROW([参照])
参照の行番号を返す。参照が省略されている場合は自セルの行番号を返す。

の4つです。

EXCEL
COUNTIF(C$2:C2,C2)

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

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

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

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

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

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

E2セル以下の式

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

使用している関数は、

IF(論理式, [真の場合], [偽の場合])
論理式の結果(真か偽)に応じて、指定された値を返す
ROW([参照])
参照の行番号を返す。参照が省略されている場合は自セルの行番号を返す。
COUNT(値1, 値2,…)
範囲内において、数値が含まれるセルの個数を返す。
INDEX(配列, 行番号, [列番号])
行番号と列番号で指定される値またはセルの参照を返します。
SMALL(範囲, 順位)
指定された範囲で、順位番目に小さな値を返します。

の5つです。

EXCEL
ROW(E1)

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

EXCEL
ROW(E1)>COUNT(F:F)

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

EXCEL
SMALL(F:F,ROW(E1))

F列で n番目に小さい値

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

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

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

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

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

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

使用している関数は、

OFFSET(参照, 行数, 列数, [高さ], [幅])
参照から指定された行数と列数だけシフトした位置にあるセル範囲の参照を返す。
COUNTIF(範囲, 検索値)
指定した範囲において、検索条件に一致するセルの個数を返す。

の2つです。

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

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

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

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

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

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

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

EXCEL
スポンサーリンク
スポンサーリンク
J.をフォローする
スポンサーリンク
アトリエJ.

コメント

  1. そる より:

    ROW(E1)で1から順番にnをとるのはわかりにくくないですか…?
    その表の中の何行目かを取りたいのだろうと思うので、Row(自セル)-1 とか、行の中で自セルが何行目かを示した方がいいのでは。

    • J.J. より:

      コメントありがとうございます。
      参照するデータの範囲が2行目からになるこの場合はそれでも良いと思いますが、例えば1行目からだと Row(自セル)-1 だとエラーになりますし、3行目からだと Row(自セル)-2 になりますね。なので、ROW(E1) というように1行目を直接指定したほうが汎用的で間違いないと思いました。

  2. そる より:

    初めまして。
    試しにこちらを参考に関数を使用してみたのですが、C列の値が1つしかない場合にエラーとなってしまいます。いろいろ試してみているのですが、回避策がありましたらご協力をお願いいたします。

    • J.J. より:

      >そる様

      ご指摘ありがとうございます。

      古い記事であまり記憶に無いのですが、今、検証してみましたら、「データの入力規制」の「元の値」のCOUNTIF関数(OFFSET関数の第4引数)の“-1”が不要でした(何か意味があったのかもしれませんが…)。
      つまり、「元の値」を
      =OFFSET($E$2,0,0,COUNTIF($E:$E,”>!”),1)
      で解決するのではないかと思います。

      よろしくお願いいたします。

タイトルとURLをコピーしました