EXCEL2013で質問です。
入力規則のリストで、他のシートにある連続入力されていない列の値を空白行を除いてリスト表示させたいです。
また、マクロやワークセルは使用しない考えています。
値が連続で最後の値以降の空白行を除くのであればOFFSETとCOUNTAの組み合わせでできるし、
ワークセルを使用するのであればこちらで実現できました。
http://excel.syogyoumujou.com/function/list.html
イメージ的には添付ファイルのような感じになります。
実現方法をご存知の方がいらっしゃいましたらご教授いただけると幸いです。
A 回答 (2件)
- 最新から表示
- 回答順に表示
No.1
- 回答日時:
こんばんは!
リスト候補として表示したい列はセル結合されているのですね?
一案です。(やり方だけ)
前提条件として1行目が項目行でデータは2行目以降だとします。
↓の画像のように作業列を使って空白以外のデータを表示させます。
画像ではI2セルに
=IFERROR(INDEX(B$1:B$1000,SMALL(IF(B$1:B$1000<>"",ROW(B$1:B$1000)),ROW(A2))),"")
配列数式になりますので、Ctrl+Shift+Enterで確定!
この画面からコピー&ペーストする場合は
上記数式をドラッグ&コピー → I2セルを選択 → 数式バー内に貼り付け → そのまま(編集可能なまま)
Ctrl+Shiftキーを押しながらEnterキーで確定!
数式の前後に{ }マークが入り配列数式になります。
I2セルのフィルハンドルでずぃ~~~!っと下へコピーしておきます。
これでB列のリスト候補だけが表示されます。
最後にG3セルの入力規則 → リスト → 元の値の欄に
=OFFSET(I1,1,,COUNTIF(I:I,"?*")-1)
という数式をいれるとB列2行目以降のデータのみがリスト候補に表示されるはずです。
※ 複数セルリスト設定する場合は「元の値」の数式を
絶対参照にしてください。m(_ _)m
さっそくの回答ありがとうございました。
配列数式を使っても作業列(ワークセル)が必要になってしまいますかね。
可能であればこの作業列を使わずに実現したいのですが。
何かいい方法は無い物でしょうか。
No.2
- 回答日時:
No.1です。
質問文に
>また、マクロやワークセルは使用しない考えています。
とありますが
入力規則のリスト設定を行う場合、ある範囲を指定するのが一般的だと思います。
(もしくは「元の値」の欄に直接リスト候補を入力する)
また、No.1のお礼欄に
>可能であればこの作業列を使わずに実現したいのですが。
とありますので、どうしても作業列なしで!
というコトがご希望のようですので、お望みでないVBAであれば可能です。
とりあえず同一SheetのG列にリスト設定しています。
画面左下の操作したいSheet見出し上で右クリック → コードの表示 → VBE画面のカーソルが点滅しているところに
↓のコードをコピー&ペースト → Excel画面に戻り(VBE画面を閉じて)
G列セルを選択してみてください。
Private Sub Worksheet_SelectionChange(ByVal Target As Range) 'この行から//
Dim i As Long
Dim myStr As String, myList As String
If Intersect(Target, Range("G:G")) Is Nothing Or Target.Count > 1 Then Exit Sub
For i = 2 To Cells(Rows.Count, "B").End(xlUp).Row
If Cells(i, "B") <> "" Then
myStr = myStr & Cells(i, "B") & ","
End If
Next i
myList = Left(myStr, Len(myStr) - 1)
With Target.Validation
.Delete
.Add Type:=xlValidateList, Formula1:=myList
End With
End Sub 'この行まで//
B列のデータがリスト候補として表示されるはずです。
(G列のリスト設定は不要です)
※ 作業列もVBAもダメなら無視してください。m(_ _)m
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) データ入力規則リスト 空白を無視 3 2022/07/13 15:11
- Excel(エクセル) VLOOKUP が機能しない、その原因は何 ? 8 2022/10/19 12:06
- Visual Basic(VBA) VBA ドロップダウンリストを残して値のみクリア 2 2022/10/27 05:42
- Excel(エクセル) ユーザー定義について質問です。 2 2023/06/28 13:21
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Excel(エクセル) 列の複数ある空白セルを飛ばして、セルに並べて表示したい 3 2023/02/12 16:49
- Excel(エクセル) エクセルの数式について教えてください。 7 2023/06/18 10:16
- Excel(エクセル) エクセルについて教えてください。 2 2023/06/14 11:11
- Excel(エクセル) [オートフィルター]機能について 3 2023/02/04 14:32
- Excel(エクセル) Excelでnullになるような式のセルをマクロで空白行と認識させるにはどうすればいいですか? 3 2023/03/13 13:42
このQ&Aを見た人はこんなQ&Aも見ています
-
性格の違いは生まれた順番で決まる?長男長女・中間子・末っ子・一人っ子の性格の傾向
同じ環境で生まれ育っても、生まれ順で性格は違うものなのだろうか。家庭教育研究家の田宮由美さんに教えてもらった。
-
結合されたセルをプルダウンのリストにする方法は?
Excel(エクセル)
-
ドロップダウンリストで空白の数式セルの非表示化方法
Excel(エクセル)
-
エクセル、ドロップダウンリストで空白セルを非表示
Excel(エクセル)
-
-
4
Excel 入力規則_リスト 連続しないデータの空白行削除
Excel(エクセル)
-
5
【Excel】セル結合すると、ドロップダウンの表示文字が小さくなる
Windows Vista・XP
-
6
エクセル2010 ドロップダウンリストに空白を表示したくない
Excel(エクセル)
-
7
Excelの入力規則で2列表示したい
Excel(エクセル)
-
8
空白セル内の数式を残したままで空白セル扱いとしたいのですが
Excel(エクセル)
-
9
エクセルでセルを結合したあとも条件適用できますか?
Excel(エクセル)
-
10
【ExcelVBA】マクロの入ったシートをコピーしても新しいシート内でマクロを動作させるには?
Excel(エクセル)
-
11
エクセルのエラーメッセージ「400」って?
Visual Basic(VBA)
-
12
VBAでエクセルシートを更新(リフレッシュ)する方法を教えて下さい。
Excel(エクセル)
-
13
EXCELの条件付き書式で数式を空白と認識してくれる方法
Excel(エクセル)
-
14
【エクセル】 入力規則:リストで空欄を防ぎたい
Excel(エクセル)
-
15
プルダウン 項目が多いので先頭文字とかで検索したい
JavaScript
-
16
EXCEL マクロ 条件の合ったシートだけ印刷したい
Excel(エクセル)
-
17
【Excel】「データの入力規則」の範囲指定方法
Excel(エクセル)
-
18
ドロップダウンリスト 自動表示したい
Excel(エクセル)
-
19
数式による空白を無視して最終行を取得するマクロ
Excel(エクセル)
-
20
名前をつけて保存した後、元のファイルに戻るには
その他(コンピューター・テクノロジー)
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
Excel 日付を比較したら、同じ...
-
英数字のみ全角から半角に変換
-
会社PCのメールが更新されない
-
Outlookを立ち上げたらGoogleロ...
-
outlookのメールが固まってしま...
-
Excelで空白以外の値がある列の...
-
ウィンドウィズ メモ帳で日付だ...
-
Excelに貼ったリンクについて E...
-
Microsoft Formsの「個人情報や...
-
Excelで時間計算(負)
-
microsoft office
-
【Excel VBA】PDFを作成して,...
-
エクセルでXLOOKUP関数...
-
マイクロソフト 一時使用コード...
-
Outlookでの時間指定送信機能に...
-
【スプレドシート】目標達成の...
-
Googleのスプレッドシートでシ...
-
Microsoft Officeを2台目のPCに...
-
MicrosoftOfficeについて質問で...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【スプレドシート】IMPORTRANGE...
-
【スプレッドシート】指定の日...
-
英数字のみ全角から半角に変換
-
会社PCのメールが更新されない
-
マイクロソフト 一時使用コード...
-
Office 2021 Professional Plus...
-
エクセルで例えば、関数を使っ...
-
Microsoft Formsの「個人情報や...
-
1つのPCに「Excel 2010」「Exc...
-
エクセルで例えば、A1に㈱ベ...
-
理由を教えてください。
-
エクセルでXLOOKUP関数...
-
マイクロソフト オフィスについて
-
VLOOKUP関数について
-
teams設定教えて下さい。 ①ビデ...
-
Googleのスプレッドシートでシ...
-
【Excel VBA】PDFを作成して,...
-
Microsoft365で写真をアルバム...
-
Outlook で宛先が複数の場合の人数
-
Excel テーブル内の空白行の削除
おすすめ情報