
以下の数式を入力したセルを参照して、氏名をドロップダウンリストから選べるようにしたいと考えております。しかし、下記の方法ですと、空白のセル(参照セルに値が無かった場合に空白""が返されたセル)が空白のままドロップダウンリストに表示されてしまい、目的の氏名をリストから探す時に手間がかかってしまいます。
<想定しているシート>
A B C D
1
2
3 氏名 物品 帳票氏名 重複判定
4 A川B男 ○○ A川B男 1
5 A川B男 ×× 0
6 C田D夫 ▽ C田D夫 1
7 C田D夫 ◆ 0
5 C田D夫 □ 0
※C4の入力数式: =if(D4=1,A4,"")
※C1セルの入力規則の元の値に =offset(C4,0,0,counta(C4:C1000),1)
上記の方法でも、C1セルのドロップダウンリストには空白を伴って氏名が表示される形となってしまいます。
このように、数式が入力された結果空白になっているセルを、ドロップダウンリストで非表示にする方法はないでしょうか?
どうぞよろしくお願い申し上げます。
No.4ベストアンサー
- 回答日時:
>このように、数式が入力された結果空白になっているセルを、ドロップダウンリストで非表示にする方法はないでしょうか?
D列の重複判定の数式を以下のように変更して、C列の帳票氏名の数式も変更すると空白行を詰めることができます。
D4=IF(COUNTIF(A$3:A4,A4)=1,ROW(),"")
最初に登場した氏名のときに行番号をセットする。
2回目以降は""をセットする。
D4セルを必要数下へコピーします。
C4=IFERROR(INDEX(A:A,SMALL(D$4:D$8,ROWS(A$4:A4))),"")
行番号の小さい順に氏名をセットする。
C4セルを必要数下へコピーします。
IFERROR関数はExcel 2007以降のバージョンに組み込まれています。
Excel 2003以前の場合は次の数式で対処してください。
C4=IF(ROWS(A$4:A4)<=COUNT(D$4:D$8),INDEX(A:A,SMALL(D$4:D$8,ROWS(A$4:A4))),"")
貼付画像はExcel 2013で検証した結果です。
提示された模擬データの範囲だけで処理していますので、実際のデータに合わせて対象範囲を変更してください。

No.5
- 回答日時:
たびたび済ません。
こちらで確認したところ、No3の回答の数式にも「)」が不足する誤りがあったようです。正しくは以下の式をC4セルに入力して下方向にオートフィルしてください。
=INDEX(A:A,SMALL(INDEX((MATCH(A$4:A$1000&"",A$4:A$1000&"",)<>ROW(A$4:A$1000)-3)*1000+ROW(A$4:A$1000),),ROW(A1)))&""
ちなみに、私の提示した数式は補助列を使用せずにC列に直接重複のないデータを詰めて表示する数式ですが、データ範囲が変わる場合、「ROW(A$4:A$1000)-3」の「3」の数字は、元データの最初の行より1つ上の行番号(項目名の行番号)を指定してください。
No.3
- 回答日時:
No2の回答の訂正と補足です。
お分かりになると思いますが、重複のないデータの数式に誤りがありました。
正しくは以下の数式です。
=INDEX(A:A,SMALL(INDEX((MATCH(A$4:A$1000&"",A$4:A$1000&"",)<>ROW(A$4:A$1000-3)*1000+ROW(A$4:A$1000),),ROW(A1)))&""
上記の数式は配列を利用した数式ですので、元データ範囲が大きすぎたり、表示データ数(オートフィルコピーをする数)が多くなると、再計算に時間がかかりますので、データ範囲や必要以上にオートフィルコピーしないなどの対応をしてください。
回答ありがとうございます。
いただきました数式を入力してみたのですが、エラーが出てしまいました。
Index関数を使ったことがなかったので、どこがエラーかを判断できませんでした。
もしよかったらまた教えてください。
No.2
- 回答日時:
C4セルに以下の式を入力し、下方向にオートフィルコピーして重複のない名前を表示します。
=INDEX(A:A,SMALL(INDEX((MATCH(A$4:A$1000&"",A$4:A$103&"",)<>ROW(A$4:A$1000-3)*1000+ROW(A$4:A$1000),),ROW(A1)))&""
次に「挿入」「名前」「定義」(Excel2007以降なら「数式」「名前の定義」)で例えば「list」と名前を付け以下の式を入力します。
=$C$4:INDEX($C4:$C1000,SUMPRODUCT((LEN($C$4:$C$1000)>0)*1))
最後に入力規則対象範囲を選択して、入力規則の「リスト」で参照範囲に「=list」と入力します。
No.1
- 回答日時:
こんばんは!
入力規則のリストの元の値の欄で一気に数式で!となると難しいと思います。
そこで一例です。
↓の画像のように別列に空白セル以外を表示させそれをリスト表示させるようにしてみてはどうでしょうか?
画像ではF1セルに
=IF(COUNTIF(C$4:C$1000,"?*")<ROW(),"",INDEX(C$4:C$1000,SMALL(IF(C$4:C$1000<>"",ROW(A$4:A$1000)-3),ROW())))
配列数式になりますので、Ctrl+Shift+Enterで確定!
この画面からコピー&ペーストする場合は
上記数式をドラッグ&コピー → F1セルを選択 → 数式バー内に貼り付け → そのまま(編集可能なまま)
Ctrl+Shiftキーを押しながらEnterキーで確定!
数式の前後に{ }マークが入り配列数式になります。
F1セルのフィルハンドルでずぃ~~~!っと下へコピーしておきます。
Excel2007以降をお使いの場合は
=IFERROR(INDEX(C$4:C$1000,SMALL(IF(C$4:C$1000<>"",ROW(A$4:A$1000)-3),ROW())),"")
という数式でOKです。
(こちらも配列数式です)
最後にC1セルのリストの元の値の欄に
=OFFSET(F1,,,COUNTIF(F:F,"?*"))
という数式を入れOK
これで空白セルは表示されないと思います。m(_ _)m

早速、ありがとうございます。
やってみたんですが、Fセルに氏名が表示されませんでした。
配列の入力等、勉強になりました。ありがとうございます。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Excel(エクセル) ユーザー定義について質問です。 2 2023/06/28 13:21
- Excel(エクセル) 【再度】Excelの関数について教えてください。 4 2023/07/28 13:06
- Excel(エクセル) Excel 特定セルの数値を参照したセルの0表示が空白にならないのはどうしてか? 3 2022/04/28 22:23
- Excel(エクセル) WORKDAY関数 4 2023/06/08 13:23
- Visual Basic(VBA) VBA ドロップダウンリストを残して値のみクリア 2 2022/10/27 05:42
- Excel(エクセル) エクセルで値ではなく関数を参照する方法 6 2023/03/19 00:50
- Excel(エクセル) エクセルの関数式を教えてください。 2 2022/11/29 21:09
- Excel(エクセル) Excel 関数 数式 について 2 2022/09/02 21:45
- Excel(エクセル) 列の複数ある空白セルを飛ばして、セルに並べて表示したい 3 2023/02/12 16:49
- Excel(エクセル) エクセルの散布図で新たに入力した値のデータラベルが空欄になる現象 1 2022/04/26 09:31
このQ&Aを見た人はこんなQ&Aも見ています
-
それもChatGPT!?と驚いた使用方法を教えてください
仕事やプライベートでも利用が浸透してきたChatGPTですが、こんなときに使うの!!?とびっくりしたり、これは画期的な有効活用だ!とうなった事例があれば教えてください!
-
歳とったな〜〜と思ったことは?
歳とったな〜〜〜、老いたな〜〜と思った具体的な瞬間はありますか?
-
もし10億円当たったら何に使いますか?
みなさんの10億円プランが知りたいです!
-
人生でいちばんスベッた瞬間
誰しも、笑いをとろうとして失敗した経験があると思います。
-
思い出すきっかけは 音楽?におい?景色?
記憶をふと思い出すきっかけは 音楽、におい、景色 どれですか?
-
空白のないドロップダウンリストの作り方
Excel(エクセル)
-
データ入力規則リスト 空白を無視
Excel(エクセル)
-
EXCEL入力規則のリスト表示から空白行を表示させない方法
その他(Microsoft Office)
-
-
4
エクセル2010 ドロップダウンリストに空白を表示したくない
Excel(エクセル)
-
5
エクセル、ドロップダウンリストで空白セルを非表示
Excel(エクセル)
-
6
数式による空白を無視して最終行を取得するマクロ
Excel(エクセル)
-
7
INDIRECT(空白や()がある文字列のセル!セル番号)がある時の仕様を知りたい
Excel(エクセル)
-
8
エクセル 空白セルを詰めたい
その他(Microsoft Office)
-
9
エクセルで数式の答えを数値として参照したい
その他(Microsoft Office)
-
10
Excelで数式の入っているセルを空白と認識させたい
Excel(エクセル)
-
11
【Excel VBA】CSV取込時、数字の先頭の0を消えないようにするには?
Excel(エクセル)
-
12
UNIQUE関数が使えないバージョンで重複削除したい。
Excel(エクセル)
-
13
Excel 入力規則_リスト 連続しないデータの空白行削除
Excel(エクセル)
-
14
VBAでエクセルシートを更新(リフレッシュ)する方法を教えて下さい。
Excel(エクセル)
-
15
EXCELの条件付き書式で数式を空白と認識してくれる方法
Excel(エクセル)
-
16
IF関数で空欄(")の時、Nullにしたい
その他(Microsoft Office)
-
17
VBAでブックを非表示で開いて処理して閉じる方法
Excel(エクセル)
-
18
Excel MATCH関数で検索範囲内に同じ値の検索値が複数ある場合
Access(アクセス)
-
19
OFFSET関数と空白に0がでない関数組み合わせ
その他(Microsoft Office)
-
20
結合されたセルをプルダウンのリストにする方法は?
Excel(エクセル)
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・一番好きなみそ汁の具材は?
- ・泣きながら食べたご飯の思い出
- ・「これはヤバかったな」という遅刻エピソード
- ・初めて自分の家と他人の家が違う、と意識した時
- ・いちばん失敗した人決定戦
- ・思い出すきっかけは 音楽?におい?景色?
- ・あなたなりのストレス発散方法を教えてください!
- ・もし10億円当たったら何に使いますか?
- ・何回やってもうまくいかないことは?
- ・今年はじめたいことは?
- ・あなたの人生で一番ピンチに陥った瞬間は?
- ・初めて見た映画を教えてください!
- ・今の日本に期待することはなんですか?
- ・集中するためにやっていること
- ・テレビやラジオに出たことがある人、いますか?
- ・【お題】斜め上を行くスキー場にありがちなこと
- ・人生でいちばんスベッた瞬間
- ・コーピングについて教えてください
- ・あなたの「プチ贅沢」はなんですか?
- ・コンビニでおにぎりを買うときのスタメンはどの具?
- ・おすすめの美術館・博物館、教えてください!
- ・【お題】大変な警告
- ・洋服何着持ってますか?
- ・みんなの【マイ・ベスト積読2024】を教えてください。
- ・「これいらなくない?」という慣習、教えてください
- ・今から楽しみな予定はありますか?
- ・AIツールの活用方法を教えて
- ・最強の防寒、あったか術を教えてください!
- ・歳とったな〜〜と思ったことは?
- ・モテ期を経験した方いらっしゃいますか?
- ・好きな人を振り向かせるためにしたこと
- ・スマホに会話を聞かれているな!?と思ったことありますか?
- ・それもChatGPT!?と驚いた使用方法を教えてください
- ・見学に行くとしたら【天国】と【地獄】どっち?
- ・これまでで一番「情けなかったとき」はいつですか?
- ・この人頭いいなと思ったエピソード
- ・あなたの「必」の書き順を教えてください
- ・14歳の自分に衝撃の事実を告げてください
- ・人生最悪の忘れ物
- ・あなたの習慣について教えてください!!
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
同一セルに日時があるものを日...
-
Excelで指定した条件と一致する...
-
EXCELでCSVファイル保存すると...
-
1年分のデータから特定の月分...
-
OFFSET関数を使用した印刷範囲...
-
COUNTIFの反対の関数はあるので...
-
エクセルVBAで、複数セルのデー...
-
1つのセルにまとまっている情報...
-
EXCELで年月日の表記から日付部...
-
【VBA】指定フォルダに格納中の...
-
webクエリのurlの変更方法
-
エクセルで数字、文字列混在の...
-
複数のシートの日付データを、...
-
色なしセルの合計値の出し方に...
-
エクセル
-
Excel 空白セルの掛け算で答え...
-
エクセルで特定の行を除いて計...
-
Excelのシートごとの日付の追加
-
エクセル 特定の条件で特定の...
-
5つとびのセルの集計
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
同一セルに日時があるものを日...
-
1年分のデータから特定の月分...
-
EXCELでCSVファイル保存すると...
-
Excelで指定した条件と一致する...
-
エクセルVBAで、複数セルのデー...
-
COUNTIFの反対の関数はあるので...
-
ドロップダウンリストで空白の...
-
エクセルでセルを一括で右詰に...
-
OFFSET関数を使用した印刷範囲...
-
1つのセルにまとまっている情報...
-
EXCELで年月日の表記から日付部...
-
excelで平均差を出したい
-
Excelで連続データを行飛ばしで...
-
エクセルで数字、文字列混在の...
-
Excelのシートごとの日付の追加
-
色なしセルの合計値の出し方に...
-
エクセルVBA オートフィルの最...
-
エクセル数式・等間隔ごとの範...
-
エクセル
-
エクセルで特定の行を除いて計...
おすすめ情報