Excel 入力規則_リスト 連続しないデータの空白行削除

質問

お世話になります。
Excel2003を使用しています。
あるブックのSheet1に"リストデータ"と名前のついている範囲があります。
Sheet2の任意のセルで、リストデータを基に入力規則から
リスト選択をさせています。
このリストデータにはユーザが任意でデータを入力するのですが
必ずしも連続するデータにはならず
データの途中に空白セルが生じたり
データの最下行が人によって異なってしまいます。
そこで、不要な空白を削除したいのですが
VBAを使わない方法で何かいいアイディアはありますでしょうか。
皆様のお知恵を拝借いたしたく、よろしくお願いいたします。

回答No.4

ham_kamo · ベストアンサー

No.2です。補足拝見しました。

> Sheet2の空いている列（たとえばＢ列）を作業列として使います。
失礼しました。この記述がSheet1の誤りでした。名前を定義する数式の方は質問文に書いたとおりです。

> 名前を定義し、入力規則のリストを設定する際
> "数式はエラーと判断されます。続けますか？"
> のメッセージが出てしまいます、どうしたらよいでしょうか。

あれ、おかしいですね。エラーになってしまいましたか。
そのままコピーして貼り付ければ、私の環境ではエラーにはならないのですが…。

いろいろパターンを変えて試してみたのですが、こちらではそのようなエラーが出ないので、ちょっと原因がわかりません。もう一度コピーした数式を見直していただけますでしょうか？数式の入力中に矢印キーなどを使うと、カーソルが移動せずに移動先のセル番地が入力されてしまい、数式が狂ってしまいますが、そのようなことはないでしょうか。

どうしてもエラーが出るようでしたら、名前「リストデータ」の定義は
=Sheet1!$B:$B
だけでも空白を削除したリストを利用できます。ただし、リストの一番下の項目の下に空白行がたくさんついてきてしまいますが…。

お礼

度々のご回答、ありがとうございました。
> =OFFSET(Sheet1!$B$1,,,COUNTA(Sheet1!$B:$B)-COUNTIF(Sheet1!$B:$B,""))
やはりここでエラーが返ってきてしまいましたが
=OFFSET(Sheet1!$B$1,,,65536-COUNTIF(Sheet1!$B:$B,""))
とすることで、希望がかないました！
Excel機能+関数でここまで出来るとは感激です。
どうもありがとうございました。

回答No.5

nishi6

＿＿Ａ
１
２　ａｂ１
３　ｃｄ２
４
５　ｅｆ４
６
７　ｇｈ６　　A2:A7 がリストデータ
８

上のような状態を想定し、リストデータはセル範囲A2:A7だとします。

まず、名前ボックスから「リストデータ」を指定し、「リストデータ」を選択状態にします。
次に、メニューから、
　編集→ジャンプ→セル選択→選択オプションで空白セルを選択し、ＯＫ
　選択された空白セルで右クリック→削除→上方向にシフト→ＯＫ
これで下のようになります。

＿＿Ａ
１
２　ａｂ１
３　ｃｄ２
４　ｅｆ４
５　ｇｈ６　　A2:A5 がリストデータ　

マクロなど使わず、セル範囲「リストデータ」から空白セルを削除できますが、このような質問ではない？

お礼

ご回答、ありがとうございました。
No.3さま同様、こちらの言葉足らずで意図とは少し異なっていました。
お手数をおかけしてすみませんでした。どうもありがとうございました。

回答No.3

zap35

Sheet1「リストデータ」の一番上の行で、空いている列に
　=COUNTA(A10:J10)
の式を入れます（この式はリストデータはA列～J列で10行目から始まる場合の例です）
この計算式はリストデータ行分だけしたにコピーします。

今追加した作業列の一番上のデータを選択し「データ」→「フィルタ」→「オートフィルタ」を選択します。プルダウンをクリックして計算式の値が０の行だけを表示させ、表示された行番号をまとめて選択し「右クリック」→「行削除」

これで空白行は削除できませんか？　もし空白行ではなく「空白セルがある行を削除したい」ということであれば０ではなく「オプション」→「ｎ以外（ｎはリストデータの列数）」を選べばよいでしょう。

意味が違っていたらごめんなさい

なお「リストデータ」範囲外にデータを追加されたら自動でリストデータ範囲を訂正するのにはマクロが必要です。もしそれが必要ならもう少し条件を補足してください（リストデータの範囲、最終行判断ルールなど…）

お礼

ご回答、ありがとうございました。
こちらの言葉足らずで意図とは少し異なっていました。
ユーザの入力はそれぞれで、リストデータ入力後の操作は
ないと言うことが前提でした。
また、"不要な空白を削除"とは
入力規則_リストで、プルダウンメニューに表示される空白の削除
という意味です。
お手数をおかけしてすみませんでした。どうもありがとうございました。

回答No.2

ham_kamo

まず元データが入っている範囲の名前「リストデータ」を、「リストデータA」に変更します。その際、範囲はデータが入るMAXの行よりも大きくしておく必要があります。

Sheet2の空いている列（たとえばＢ列）を作業列として使います。
B1に、

=IF(ROW(A1)>COUNTA(リストデータA),"",INDEX(リストデータA,SMALL(IF(リストデータA<>"",ROW(リストデータA),10^5),ROW(A1))))

という数式を入力し、配列数式なのでCtrl+Shift+Enterで確定します。
そのセルをマウスで下にコピーすると、リストデータAの空白行が削除された列になります。念のため大きめの範囲をコピーしておいてください。

名前の定義で、改めて「リストデータ」という名前を定義します。その際、参照範囲には
=OFFSET(Sheet1!$B$1,,,COUNTA(Sheet1!$B:$B)-COUNTIF(Sheet1!$B:$B,""))
という数式を入れておきます。

これで入力規則で「リストデータ」の名前を指定すると、空行が削除されたリストが出てくるようになります。

補足

ご回答、ありがとうございます。
記述いただいた
> =OFFSET(Sheet1!$B$1,,,COUNTA(Sheet1!$B:$B)-COUNTIF(Sheet1!$B:$B,""))
のSheet1は
> Sheet2の空いている列（たとえばＢ列）を作業列として使います。
より、Sheet2の間違いでしょうか？
名前を定義し、入力規則のリストを設定する際
"数式はエラーと判断されます。続けますか？"
のメッセージが出てしまいます、どうしたらよいでしょうか。
引き続き、ご教示いただけますでしょうか。

回答No.1

imogasi

VBAを使わないと難しいでしょう。
リストが変更される都度操作をやり直すなら
Sheet２で
最下行まで範囲指定
編集ージャンプーセル選択ー定数ー文字を残す等ーOK
編集ーコピー
空き列に、形式を選択して貼り付けーリンク貼り付けーOk
（または空白を無視するーOK）
その範囲に名前をつけておく（仮にccとする）
後は
Sheet1に戻って、入力（するであろう）セル列を範囲指定
データ
入力規則
リスト
範囲の入力ボックスに
=CC　前記で定義した名前
といれてOK
Sheet2のリストに使う範囲のセルの値を変えても対応OK
しかしスペースのセルが変わると入力規則の操作をやり直しする必要あり。

お礼

ご回答、ありがとうございました。
やはりVBA必要ですね。
可能なら数式で、と思ったのですが難しいですね。