エクセル、ドロップダウンリストで空白セルを非表示

質問

エクセル2013です。

Sheet1にドロップダウンリストを作成し、Sheet2のC列を参照したいです。
Sheet2のC列は日々入力され行数が増えていくのでリストの範囲を多めにしたいです。

入力規則
リスト-元の値
=Sheet2!$C$3:$C$20000

にしましたが
Sheet2のC列はまだ1,000行くらいしか入力されていない為
ドロップダウンリストでリストを表示すると
リスト内にて空白の部分の方が多く、検索が大変です。

NETで調べて

入力規則
リスト-元の値
=OFFSET(Sheet2!$C$3,0,0,SUMPRODUCT((Sheet2!$C$3:$C$20000<>"")*1))

としたのですが
ドロップダウンリストには
Sheet2のC列のセルC863までしか表示がされません。
Sheet2のC列はC1003まで値が有ります。

この関数のどこがわるいのでしょうか？
よろしくお願いします。

回答No.7

MackyNo1 · ベストアンサー

参考までに

データのある最終行を検索する数式で設定したいなら、以下のような名前定義を使用します。
(元データがA1セル以下にある場合）

=$A$1:INDEX($A$1:$A$20000,MAX(($A$1:$A$20000<>"")*ROW($A$1:$A$20000)))

お礼

Sheet2のC3～C20000を選択して
数式－名前の管理

参照範囲が「=Sheet2!$C$3:$C$20000」
と表示されているところを教えて頂いた

=Sheet2!$C$3:INDEX($C$3:$C$20000,MAX(($C$3:$C$20000<>"")*ROW($C$3:$C$20000)))

に変更して登録。
名前を「商品名」にして登録。

Sheet1のドロップダウンリストを作成したいセルに
入力規則で
リスト－参照「=商品名」と登録し
使用者の意図とする状態になりました。
どうもありがとうございます。

回答No.8

MackyNo1

>Sheet2のC3～C20000を選択し「テーブルとして書式設定」をクリックすると色々な表みたいなものが出てきてこれをどうしたらいいかよくわかりません

どの形のテーブルのレイアウトを選択しても見かけ上だけの問題ですので、どれか1つを選択すればOKですが、ポイントはC20000までではなく、現在の最終データまでの範囲を選択してテーブルにすることです。
このようにしておけば、行の挿入やデータの追加に対して、自動的にデータ範囲を認識しますので、ご希望のデータ範囲だけの入力規則のリストにできるわけです。

お礼

ご丁寧にありがとうございます。

回答No.6

MackyNo1

区分の為、空白行を所々に入れており
ドロップダウンリストではこの途中空白は
出てくれないと困るそうで、
（私も使って見てそう感じました。）

リスト内に行の挿入や追加、あるいはコピー貼り付けに自動的に範囲を拡張したいということなら、テーブルの機能を利用することをお勧めします。

リストのデータ範囲を選択して、ホームタブの「テーブルとして書式設定」し、そのまま「数式」「名前の定義」でたとえば「入力リスト」と名前をつけておき、この名前を入力規則のリストで「=入力リスト」のように設定して下さい。

このように設定しておけば（絶対参照の数式のように見えますが）テーブル内でデータの追加や削除、あるいは行の挿入などに対して、自動的にデータ範囲を変更してくれます。

お礼

>リストのデータ範囲を選択して、ホームタブの「テーブルとして書式設定」し、
>そのまま「数式」「名前の定義」でたとえば「入力リスト」と名前をつけておき、
>この名前を入力規則のリストで「=入力リスト」のように設定して下さい。

Sheet2のC3～C20000を選択し
「テーブルとして書式設定」をクリックすると
色々な表みたいなものが出てきて
これをどうしたらいいかよくわかりません
とりあえず、1個選択して名前を「入力リスト」で登録。

その後、数式－名前の定義で
「入力リスト」と登録

Sheet1のドロップダウンリストを作成したいセルに
入力規則で
リスト－参照「=入力リスト」と登録。

ですがやはりドロップダウンリストの
途中から末端までが空白でした。

私のやり方が悪いと思うのですが
どこがわるいかわかりません。

どうもありがとうございました。

回答No.5

tom04

No.3・4です。

途中に空白がある方が良いというコトですね。
仮にC34~C20000の範囲で100行毎に空白をリストに表示したい場合は
101行目202行目・・・に半角でも全角でも良いので、スペースを入力します。
そしてそのセルは「入力規則」等でスペース以外は入力できないようにしてはどうでしょうか？

尚、Sheet2のD列の数式とリストの「元の値」の欄の数式はそのままで大丈夫です。

何とかこれでご希望通りにならないでしょうか？m(_ _)m

お礼

ありがとうございます。
もう少し時間が出来たら
試してみますv。

回答No.4

tom04

No.3です。

Sheet2のD1セルの数式が間違っていました。
=IFERROR(INDEX(C$3:C$20000,SMALL(IF(C$3:C$20000<>"",ROW(C$1:C$19998)),ROW(A1))),"")
に訂正してください。

前回同様、配列数式です。m(_ _)m

お礼

いつもご配慮ありがとうございます。

回答No.3

tom04

こんばんは！

No.1さんが仰っているように途中の空白セルがある場合
入力規則の「リスト」も空白が表示されてしまいます。

そこでSheet2の使っていないセルにC列データを空白セルがないように表示させます。
仮にD列に表示させるとすると
Sheet2のD1セルに
=IFERROR(INDEX(C$3:C$20000,SMALL(IF(C$3:C$20000<>"",ROW(C$1:C$1998)),ROW(A1))),"")
これは配列数式になりますので、Ctrl＋Shift＋Enterで確定！
この画面からコピー＆ペーストする場合は
上記数式をドラッグ＆コピー　→　Sheet2のD1セルを選択　→　数式バー内に貼り付け　→　そのまま（編集可能なまま）
Ctrl＋Shiftキーを押しながらEnterキーで確定！
数式の前後に{ }マークが入り配列数式になります。
D1セルのフィルハンドルでずぃ～～～！っと下へしっかりコピーしておきます。
このD列データを入力規則のリストとして表示させます。

Sheet1のリスト表示させたいセルを選択　→　データの入力規則　→　リスト　→　元の値の欄に
=OFFSET(Sheet2!$D$1,0,,COUNTIF(Sheet2!$D:$D,"?*"))
という数式を入れOK
これで完了です。

※　余計なお世話かもしれませんが、実際にこれほどのリストデータが必要なのかどうか疑問がありますし、
本当に必要な場合でも、その中から必要データを選び出すのも大変だと思います。
そして、万一重複データがある場合等々、余計な心配が湧いてきます。m(_ _)m

お礼

C列の値に応じて、D列～AG列まで
色々なデータが入力されており
それをVLOOKで引っ張り１５シートに展開します。

途中の空白は、C列の値を区分をする為に使っているようで、
C列内途中空白の行削除は駄目だそうです。

リスト内に途中空白が有るのは好都合でして
ドロップダウンリストが表示されて
あの狭いリストの途中から末端までが空白
の方が作業がしにくいです。
今1,003行なら1,003行までだけ、今10,000行なら
10,000行までだけリストに出したいのです。

質問内容で詳細が足りず申し訳ありません。

＞実際にこれほどのリストデータが必要なのか
＞どうか疑問がありますし、
＞本当に必要な場合でも、
＞その中から必要データを選び出すのも大変だと思います。
＞そして、万一重複データがある場合等々、
＞余計な心配が湧いてきます。

私もそう思ったのですが自分で使用してみると
10,000行あっても全然大丈夫です。
目的の物を即選択できます。
また重複データを選択してもなんら問題が無いのです。
それよりドロップダウンリストが表示されて
あの狭いリストの途中から末端までが空白
の方が作業がしにくいです。
今1,003行なら1,003行までだけ、今10,000行なら
10,000行までだけリストに出したいのです。

どうもありがとうございました。

補足

会社の女性が使います。
私は常にこのファイルをメンテできません。

女性は日々Sheet2のC列にコピペで行追加します。
行挿入する場合も、行削除する場合もあります。

別のエクセルファイルで、
同じくC列に10,000行の値が有るものがあり
これをドロップダウンリストで検索してみたのですが
実際10,000行もあっても
探したい値はすぐ見つけれます。
値が検索しやすいように工夫してありました。

で、区分の為、空白行を所々に入れており
ドロップダウンリストではこの途中空白は
出てくれないと困るそうで、
（私も使って見てそう感じました。）

一番困るのは私がメンテをしな久手いいようにする為
ドロップダウンリストのリスト範囲を、
これくらいあれば足りるだろうで20,000行までにした為
あの小さいドロップダウンリストにおいて
途中から末端までが全部空白になってしまい、
リストの横のバーを少し動かすだけですぐ
空白の所に行ってしまい大変だそうです。

今は2,000行まで入っているからリスト内には
2,000行まで表示して欲しい、
今は190行しかないから、リスト内は
190行目までだけ表示して欲しい
という要求です。

回答No.2

keithin

＞Sheet2のC列は日々入力され行数が増えていく

ご説明から、シート２のＣ列には「日々手でデータを入力している」という前提でご説明するとして。

途中の空白を上手に抜いた実データだけのリストをプルダウンしたいなら、更に別列にＣ列の実データだけ上から順に再掲したリストを作成し、そちらを入力リストのネタとするような方法にします。これならサルにでも出来ますから。

手順：
シート２のＢ３に
=IF(C3="","",ROW())
と記入、下向けに沢山コピー
Ｃ列にあなたがデータを記入する都度データ番号としての数字が発生することを確認する

シート２のＥ１に
=IF(ROW(E1)>COUNT(B:B),"",SMALL(B:B,ROW(E1)))
と記入、以下コピーしＢ列のデータが上から詰めて表示されることを確認する
Ｆ１に
=IF(E1="","",VLOOKUP(E1,B:C,2))
と記入、以下コピーしＣ列の内容が漏れなく上から詰めて表示されることを確認する

リストの元の値には
=OFFSET(Sheet2!$F$1,0,0,COUNT(Sheet2!$B:$B),1)
と設定する。

＃参考

＞リスト-元の値
＞=OFFSET(Sheet2!$C$3,0,0,SUMPRODUCT((Sheet2!$C$3:$C$20000<>"")*1))

その数式では、Ｃ列に記入されている「個数分を上から」拾ってくるだけです。あなたのやり方で表示される863個をよく見てみれば、そこまでの途中の空白がしっかり載ってる事が確認できます。

＃ちなみにその数式で意図してる計算なら、わざわざそんな重たい関数にしないで
=OFFSET(Sheet2!$C$3,0,0,COUNTA(Sheet2!$C$3:$C$20000))
とした方が遥かに簡単です。

お礼

C列の値に応じて、D列～AG列まで
色々なデータが入力されており
それをVLOOKで引っ張り１５シートに展開します。

途中の空白は、C列の値を区分をする為に使っているようで、
C列内途中空白の行削除は駄目だそうです。

リスト内に途中ク拍が有るのは好都合でして
ドロップダウンリストが表示されて
あの狭いリストの途中から末端までが空白
の方が作業がしにくいです。
今1,003行なら1,003行までだけ、今10,000行なら
10,000行までだけリストに出したいのです。

質問内容で詳細が足りず申し訳ありません。
どうもありがとうございました。

補足

会社の女性が使います。
私は常にこのファイルをメンテできません。

女性は日々Sheet2のC列にコピペで行追加します。
行挿入する場合も、行削除する場合もあります。

別のエクセルファイルで、
同じくC列に10,000行の値が有るものがあり
これをドロップダウンリストで検索してみたのですが
実際10,000行もあっても
探したい値はすぐ見つけれます。
値が検索しやすいように工夫してありました。

で、区分の為、空白行を所々に入れており
ドロップダウンリストではこの途中空白は
出てくれないと困るそうで、
（私も使って見てそう感じました。）

一番困るのは私がメンテをしな久手いいようにする為
ドロップダウンリストのリスト範囲を、
これくらいあれば足りるだろうで20,000行までにした為
あの小さいドロップダウンリストにおいて
途中から末端までが全部空白になってしまい、
リストの横のバーを少し動かすだけですぐ
空白の所に行ってしまい大変だそうです。

今は2,000行まで入っているからリスト内には
2,000行まで表示して欲しい、
今は190行しかないから、リスト内は
190行目までだけ表示して欲しい
という要求です。

回答No.1

MackyNo1

以下の質問と同じように思いますが、その回答ではうまくいかなかったのでしょうか？
http://questionbox.jp.msn.com/qa8750265.html

同じ質問で再質問する場合は、以前の質問を解決処理して、回答者にこれまでの経過がわかるように上記のようにリンクを張るようにしましょう（回答者に無駄な回答をさせることになりマナー違反です）。

＞ドロップダウンリストには
Sheet2のC列のセルC863までしか表示がされません。
Sheet2のC列はC1003まで値が有ります。

さて本題ですが、リストの途中に空白セルがあるなら、この部分を削除しないと、提示された数式では空白セル以外のデータ分だけの行しか表示でいませんので、正しい範囲を取得できません。
数式を工夫すれば、空白セルをいれて関数で入力したデータの最終セルまでをリストに入れることができますが、あまりお勧めできません。

データの途中に空白セルがあるなら、この空白セルを一括削除することをお勧めします。

たとえば、そのデータ範囲を選択して、Ctrl+Gでジャンプダイアログを出して「セル選択」から「空白セル」にチェックを入れ、選択された空白セルの上で右クリックし「削除」して「上方向にシフト」で「OK」してください。
あるいは、データ範囲を選択して「データ」「並べ替え」で昇順などのようにして並べ替えるほうが、空白セルを除去できるだけなく、項目の選択も容易になると思います。

＃そもそも1000項目以上あるドロップダウンリストから選択するような操作は、あまり合理的な処理とは思えないのですが、たとえば商品名のようなものなら、大分類ー（中分類ー）最終項目のようにして選択を容易にするなどの工夫が必要なのではないでしょうか？

お礼

>同じ質問で再質問する場合は、以前の質問を解決処理して、
>回答者にこれまでの経過がわかるように上記のようにリンクを張るようにしましょう
>（回答者に無駄な回答をさせることになりマナー違反です）。

以前同じスレ内で追加質問して
ひどく叱られたので
別スレにしました。
申し訳ありません。

C列の値に応じて、D列～AG列まで
色々なデータが入力されており
それをVLOOKで引っ張り１５シートに展開します。

途中の空白は、C列の値を区分をする為に使っているようで、
C列内途中空白の行削除は駄目だそうです。

>1000項目以上あるドロップダウンリストから選択するような操作は、
>あまり合理的な処理とは思えない

私もそう思ったのですが自分で使用してみると
10,000行あっても大丈夫です。
それよりドロップダウンリストが表示されて
あの狭いリストの途中から末端までが空白
の方が作業がしにくいです。
今1,003行なら1,003行までだけ、今10,000行なら
10,000行までだけリストに出したいのです。

どうもありがとうございました。

補足

会社の女性が使います。
私は常にこのファイルをメンテできません。

女性は日々Sheet2のC列にコピペで行追加します。
行挿入する場合も、行削除する場合もあります。

別のエクセルファイルで、
同じくC列に10,000行の値が有るものがあり
これをドロップダウンリストで検索してみたのですが
実際10,000行もあっても
探したい値はすぐ見つけれます。
値が検索しやすいように工夫してありました。

で、区分の為、空白行を所々に入れており
ドロップダウンリストではこの途中空白は
出てくれないと困るそうで、
（私も使って見てそう感じました。）

一番困るのは私がメンテをしな久手いいようにする為
ドロップダウンリストのリスト範囲を、
これくらいあれば足りるだろうで20,000行までにした為
あの小さいドロップダウンリストにおいて
途中から末端までが全部空白になってしまい、
リストの横のバーを少し動かすだけですぐ
空白の所に行ってしまい大変だそうです。

今は2,000行まで入っているからリスト内には
2,000行まで表示して欲しい、
今は190行しかないから、リスト内は
190行目までだけ表示して欲しい
という要求です。