![](http://oshiete.xgoo.jp/images/v2/pc/qa/question_title.png?5a7ff87)
お世話になります。
Excel2003を使用しています。
あるブックのSheet1に"リストデータ"と名前のついている範囲があります。
Sheet2の任意のセルで、リストデータを基に入力規則から
リスト選択をさせています。
このリストデータにはユーザが任意でデータを入力するのですが
必ずしも連続するデータにはならず
データの途中に空白セルが生じたり
データの最下行が人によって異なってしまいます。
そこで、不要な空白を削除したいのですが
VBAを使わない方法で何かいいアイディアはありますでしょうか。
皆様のお知恵を拝借いたしたく、よろしくお願いいたします。
No.4ベストアンサー
- 回答日時:
No.2です。
補足拝見しました。> Sheet2の空いている列(たとえばB列)を作業列として使います。
失礼しました。この記述が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
- 回答日時:
__A
1
2 ab1
3 cd2
4
5 ef4
6
7 gh6 A2:A7 がリストデータ
8
上のような状態を想定し、リストデータはセル範囲A2:A7だとします。
まず、名前ボックスから「リストデータ」を指定し、「リストデータ」を選択状態にします。
次に、メニューから、
編集→ジャンプ→セル選択→選択オプションで空白セルを選択し、OK
選択された空白セルで右クリック→削除→上方向にシフト→OK
これで下のようになります。
__A
1
2 ab1
3 cd2
4 ef4
5 gh6 A2:A5 がリストデータ
マクロなど使わず、セル範囲「リストデータ」から空白セルを削除できますが、このような質問ではない?
ご回答、ありがとうございました。
No.3さま同様、こちらの言葉足らずで意図とは少し異なっていました。
お手数をおかけしてすみませんでした。どうもありがとうございました。
No.3
- 回答日時:
Sheet1「リストデータ」の一番上の行で、空いている列に
=COUNTA(A10:J10)
の式を入れます(この式はリストデータはA列~J列で10行目から始まる場合の例です)
この計算式はリストデータ行分だけしたにコピーします。
今追加した作業列の一番上のデータを選択し「データ」→「フィルタ」→「オートフィルタ」を選択します。プルダウンをクリックして計算式の値が0の行だけを表示させ、表示された行番号をまとめて選択し「右クリック」→「行削除」
これで空白行は削除できませんか? もし空白行ではなく「空白セルがある行を削除したい」ということであれば0ではなく「オプション」→「n以外(nはリストデータの列数)」を選べばよいでしょう。
意味が違っていたらごめんなさい
なお「リストデータ」範囲外にデータを追加されたら自動でリストデータ範囲を訂正するのにはマクロが必要です。もしそれが必要ならもう少し条件を補足してください(リストデータの範囲、最終行判断ルールなど…)
ご回答、ありがとうございました。
こちらの言葉足らずで意図とは少し異なっていました。
ユーザの入力はそれぞれで、リストデータ入力後の操作は
ないと言うことが前提でした。
また、"不要な空白を削除"とは
入力規則_リストで、プルダウンメニューに表示される空白の削除
という意味です。
お手数をおかけしてすみませんでした。どうもありがとうございました。
No.2
- 回答日時:
まず元データが入っている範囲の名前「リストデータ」を、「リストデータA」に変更します。
その際、範囲はデータが入るMAXの行よりも大きくしておく必要があります。Sheet2の空いている列(たとえばB列)を作業列として使います。
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の空いている列(たとえばB列)を作業列として使います。
より、Sheet2の間違いでしょうか?
名前を定義し、入力規則のリストを設定する際
"数式はエラーと判断されます。続けますか?"
のメッセージが出てしまいます、どうしたらよいでしょうか。
引き続き、ご教示いただけますでしょうか。
No.1
- 回答日時:
VBAを使わないと難しいでしょう。
リストが変更される都度操作をやり直すなら
Sheet2で
最下行まで範囲指定
編集ージャンプーセル選択ー定数ー文字を残す等ーOK
編集ーコピー
空き列に、形式を選択して貼り付けーリンク貼り付けーOk
(または空白を無視するーOK)
その範囲に名前をつけておく(仮にccとする)
後は
Sheet1に戻って、入力(するであろう)セル列を範囲指定
データ
入力規則
リスト
範囲の入力ボックスに
=CC 前記で定義した名前
といれてOK
Sheet2のリストに使う範囲のセルの値を変えても対応OK
しかしスペースのセルが変わると入力規則の操作をやり直しする必要あり。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) VBA ドロップダウンリストを残して値のみクリア 2 2022/10/27 05:42
- Excel(エクセル) データ入力規則リスト 空白を無視 3 2022/07/13 15:11
- Excel(エクセル) Excel 値を返す数式についてです 3 2022/11/21 20:08
- Excel(エクセル) エクセルについて教えてください。 2 2023/06/14 11:11
- Excel(エクセル) VLOOKUP が機能しない、その原因は何 ? 8 2022/10/19 12:06
- Excel(エクセル) Excel ドロップダウンリスト(入力規則)に関してです データの入力規則で元データ79000行のド 3 2023/07/17 10:06
- Excel(エクセル) [オートフィルター]機能について 3 2023/02/04 14:32
- Excel(エクセル) Excel あらかじめ予定表があり、その月毎のセルに、リストの連続データを入れたい 2 2022/04/07 14:20
- Excel(エクセル) 別シートの表の値を参照したい 2 2022/03/30 15:11
- Excel(エクセル) Excel VBA 空白行があるセル範囲に色を付ける 3 2022/06/13 15:58
このQ&Aを見た人はこんなQ&Aも見ています
-
プロが教える店舗&オフィスのセキュリティ対策術
中・小規模の店舗やオフィスのセキュリティセキュリティ対策について、プロにどう対策すべきか 何を注意すべきかを教えていただきました!
-
EXCEL入力規則のリスト表示から空白行を表示させない方法
その他(Microsoft Office)
-
【Excel】「データの入力規則」の範囲指定方法
Excel(エクセル)
-
エクセル、ドロップダウンリストで空白セルを非表示
Excel(エクセル)
-
-
4
ドロップダウンリストで空白の数式セルの非表示化方法
Excel(エクセル)
-
5
エクセルで飛び飛びのセルを参照するには
Excel(エクセル)
-
6
結合されたセルをプルダウンのリストにする方法は?
Excel(エクセル)
-
7
ドロップダウンリスト 自動表示したい
Excel(エクセル)
-
8
【エクセル】 入力規則:リストで空欄を防ぎたい
Excel(エクセル)
-
9
エクセル2010 ドロップダウンリストに空白を表示したくない
Excel(エクセル)
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
Excelデータをコピペして、ペー...
-
スプレッドシート、Excelでの数...
-
エクセルの関数について教えて...
-
スプレッドシートで使う数式を...
-
Microsoft Officeの中古は信用...
-
エクセルで会社の従業員のデー...
-
Excelで50個のセルに同じ文字を...
-
エクセルの表で1年間の曜日を...
-
エクセルでの特別な文字を上に...
-
エクセルで不等号記号(≠)が上に...
-
エクセルでB列でフィルターをか...
-
エクセルでセルに標準で入力さ...
-
Excelで、項目の種類ごとにカウ...
-
【マクロ】アクティブセルにブ...
-
EXCELの質問です 119から足した...
-
【マクロ】アクティブセルの、...
-
【マクロ】アクティブセルの行...
-
エクセルの空欄をつめて、次の...
-
Excelの数式について教えてくだ...
-
非表示列の再表示に失敗
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
エクセルVBA、別ブックへ転記す...
-
エクセルでの作業計算方法について
-
時間によってファイル名が変わ...
-
【関数】適切な文字数の数字を...
-
Excelについて教えてください
-
エクセル初心者です 関数の入れ...
-
【マクロ】ファイル名の変更に...
-
UNIQUE関数が使えないバージョ...
-
エクセルの計算
-
【関数】先頭だけにある、半角...
-
Excelで、決まった行を繰り返し...
-
Excelでセルの値が同じか...
-
LOOKUP関数を使えばいいのでし...
-
Excel
-
はがきについて。
-
エクセルの条件付き書式につい...
-
エクセルのデーターが2か月前の...
-
エクセル②
-
エクセルで「-0.0」と表示さ...
-
Microsoft1Officeの互換ソフト...
おすすめ情報