お世話になります。
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ランキング
-
エクセルで 自動的に◯や数字を...
-
【マクロ】2回実行したら、エ...
-
エクセルのツールバーから数値...
-
特定の文字列を含む、住所を抽...
-
Excel 2019 [オプション]の[リボンのユ...
-
祝日と土曜、日曜の合計をカウ...
-
EXCELの散布図で日付が1900年に...
-
マイクロソフトのPADを使ってい...
-
絶対参照
-
エクセルでCtrl+Tでテーブルの...
-
【マクロ】名前を保存する際に...
-
エクセルのクイックアクセスツ...
-
【マクロ】VLOOKUPにて参照元に...
-
Excel分数の表示について
-
【EXCEL】画像の黄色部分の抽出...
-
DATE関数で現在の年齢を出した...
-
Excelについて
-
マクロエクセルのブロック解除
-
Excelピボットテーブルの1行目
-
REGEXREPLACE関数について、
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
半角カタカナをヘボン式ローマ...
-
(マクロ)vlookupの元データを同...
-
エクセルで上位バイトのセルと...
-
exselの質問です
-
Excel 大小比較演算子による「...
-
Excel VBについての質問です。
-
エクセルの問題です。絶対値の...
-
非表示列の再表示に失敗
-
職場の人から聞かれており、こ...
-
Excel関数-文字列で自動作成さ...
-
Excelデータをコピペして、ペー...
-
ユーザー定義関数をアドイン登...
-
【マクロ】for next構文について
-
エクセルの日付を編集する
-
【マクロ】VLOOKUPにて参照元に...
-
exselで最小数で並び替える関数
-
libre 表計算ソフトの計算がう...
-
エクセルで表
-
エクセルの表で1年間の曜日を...
-
西暦和暦
おすすめ情報