ドロップダウンリストで空白の数式セルの非表示化方法

質問

以下の数式を入力したセルを参照して、氏名をドロップダウンリストから選べるようにしたいと考えております。しかし、下記の方法ですと、空白のセル（参照セルに値が無かった場合に空白""が返されたセル）が空白のままドロップダウンリストに表示されてしまい、目的の氏名をリストから探す時に手間がかかってしまいます。

＜想定しているシート＞

　　　Ａ　　　　　　　Ｂ　　　　　　　Ｃ　　　　　　　　Ｄ
１　　　　　　　　　　　　　　　　　　　　　　　　　　
２
３　氏名　　　　　物品　　　　　帳票氏名　　重複判定
４　Ａ川Ｂ男　　　○○　　　　　Ａ川Ｂ男　　　　　1
５　Ａ川Ｂ男　　　××　　　　　　　　　　　　　　　 0
６　Ｃ田Ｄ夫　　　▽　　　　　　Ｃ田Ｄ夫　　　　　 1
７　Ｃ田Ｄ夫　　　◆　　　　　　　　　　　　　　　　 0
５　Ｃ田Ｄ夫　　　□　　　　　　　　　　　　　　　　 0

※C４の入力数式：　＝if（D4=1,A4,""）
※Ｃ１セルの入力規則の元の値に　=offset(C4,0,0,counta(C4:C1000),1)

上記の方法でも、Ｃ１セルのドロップダウンリストには空白を伴って氏名が表示される形となってしまいます。

このように、数式が入力された結果空白になっているセルを、ドロップダウンリストで非表示にする方法はないでしょうか？

どうぞよろしくお願い申し上げます。

回答No.4

bunjii · ベストアンサー

>このように、数式が入力された結果空白になっているセルを、ドロップダウンリストで非表示にする方法はないでしょうか？
D列の重複判定の数式を以下のように変更して、C列の帳票氏名の数式も変更すると空白行を詰めることができます。
D4=IF(COUNTIF(A$3:A4,A4)=1,ROW(),"")
　最初に登場した氏名のときに行番号をセットする。
　2回目以降は""をセットする。
D4セルを必要数下へコピーします。
C4=IFERROR(INDEX(A:A,SMALL(D$4:D$8,ROWS(A$4:A4))),"")
　行番号の小さい順に氏名をセットする。
C4セルを必要数下へコピーします。
IFERROR関数はExcel 2007以降のバージョンに組み込まれています。
Excel 2003以前の場合は次の数式で対処してください。
C4=IF(ROWS(A$4:A4)<=COUNT(D$4:D$8),INDEX(A:A,SMALL(D$4:D$8,ROWS(A$4:A4))),"")

貼付画像はExcel 2013で検証した結果です。
提示された模擬データの範囲だけで処理していますので、実際のデータに合わせて対象範囲を変更してください。

お礼

ありがとうございます！！
空白を非表示にできました。

回答No.5

MackyNo1

たびたび済ません。こちらで確認したところ、No3の回答の数式にも「）」が不足する誤りがあったようです。

正しくは以下の式をC4セルに入力して下方向にオートフィルしてください。

=INDEX(A:A,SMALL(INDEX((MATCH(A$4:A$1000&"",A$4:A$1000&"",)<>ROW(A$4:A$1000)-3)*1000+ROW(A$4:A$1000),),ROW(A1)))&""

ちなみに、私の提示した数式は補助列を使用せずにC列に直接重複のないデータを詰めて表示する数式ですが、データ範囲が変わる場合、「ROW(A$4:A$1000)-3」の「3」の数字は、元データの最初の行より1つ上の行番号（項目名の行番号）を指定してください。

お礼

再度ご確認・ご教授くださいましてありがとうございます。

勉強になりました。

回答No.3

MackyNo1

No2の回答の訂正と補足です。

お分かりになると思いますが、重複のないデータの数式に誤りがありました。
正しくは以下の数式です。

=INDEX(A:A,SMALL(INDEX((MATCH(A$4:A$1000&"",A$4:A$1000&"",)<>ROW(A$4:A$1000-3)*1000+ROW(A$4:A$1000),),ROW(A1)))&""

上記の数式は配列を利用した数式ですので、元データ範囲が大きすぎたり、表示データ数（オートフィルコピーをする数）が多くなると、再計算に時間がかかりますので、データ範囲や必要以上にオートフィルコピーしないなどの対応をしてください。

お礼

回答ありがとうございます。

いただきました数式を入力してみたのですが、エラーが出てしまいました。
Ｉｎｄｅｘ関数を使ったことがなかったので、どこがエラーかを判断できませんでした。

もしよかったらまた教えてください。

回答No.2

MackyNo1

C4セルに以下の式を入力し、下方向にオートフィルコピーして重複のない名前を表示します。

=INDEX(A:A,SMALL(INDEX((MATCH(A$4:A$1000&"",A$4:A$103&"",)<>ROW(A$4:A$1000-3)*1000+ROW(A$4:A$1000),),ROW(A1)))&""

次に「挿入」「名前」「定義」(Excel2007以降なら「数式」「名前の定義」）で例えば「list」と名前を付け以下の式を入力します。

=$C$4:INDEX($C4:$C1000,SUMPRODUCT((LEN($C$4:$C$1000)>0)*1))

最後に入力規則対象範囲を選択して、入力規則の「リスト」で参照範囲に「=list」と入力します。

お礼

ご回答ありがとうございました。

入力規則の参照範囲で指定する方法を知りませんでした。勉強になりました。

回答No.1

tom04

こんばんは！

入力規則のリストの元の値の欄で一気に数式で！となると難しいと思います。
そこで一例です。
↓の画像のように別列に空白セル以外を表示させそれをリスト表示させるようにしてみてはどうでしょうか？

画像ではF1セルに
=IF(COUNTIF(C$4:C$1000,"?*")<ROW(),"",INDEX(C$4:C$1000,SMALL(IF(C$4:C$1000<>"",ROW(A$4:A$1000)-3),ROW())))
配列数式になりますので、Ctrl+Shift+Enterで確定！
この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　F1セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl+Shiftキーを押しながらEnterキーで確定！
数式の前後に{ }マークが入り配列数式になります。
F1セルのフィルハンドルでずぃ～～～！っと下へコピーしておきます。

Excel2007以降をお使いの場合は
=IFERROR(INDEX(C$4:C$1000,SMALL(IF(C$4:C$1000<>"",ROW(A$4:A$1000)-3),ROW())),"")
という数式でOKです。
（こちらも配列数式です）

最後にC1セルのリストの元の値の欄に
=OFFSET(F1,,,COUNTIF(F:F,"?*"))
という数式を入れOK

これで空白セルは表示されないと思います。m(_ _)m

お礼

早速、ありがとうございます。

やってみたんですが、Ｆセルに氏名が表示されませんでした。

配列の入力等、勉強になりました。ありがとうございます。