空白のないドロップダウンリストの作り方

空白の内ドロップダウンリストを作成したいです。
数式を反映したいセルはG5セルです。

名前の定義を下記の通り設定しました。

B5～B10には名前の定義でAランクと定義づけ（データ数6個）
C5～C10には名前の定義でBランクと定義づけ（データ数5個）
D5～D10には名前の定義でCランクと定義づけ（データ数3個）


F5セルにAランクorBランクorCランクを選択できるようにしました。
Ｇ5セルでドロップダウンが出るようにしたいのですが、
F５にＡランクと入力されていたらＡランクの定義リストから選択
F５にＢランクと入力されていたらBランクの定義リストから選択

これがやりたいことです。


G５には入力規則でリストにして、=INDIRECT(F5)　と設定をしました。
これだと、Aランクを選ぶときは空欄がないのでよいのですが
Bランク・Cランクの中から選ぶとき空白が出てしまいます。

どのように設定をしたらいいか教えてください。
ＯＦＦＳＥＴとCOUNTAを組み合わせてもうまくいきませんでした。
（きっと私の知識不足でできていないだけだとは思うのですが・・・。）

実際の資料はAランクはデータが15個あり
Cランクは3つしかないので、Cランクにするとき空白だらけでみっともないです・・・。

どなた様かお力添えをお願いいたします。

No.4 ベストアンサー 回答者： mike_g 回答日時： 2024/02/21 01:47

[No.3]へのコメント、

【お断わり】No.3 には私が添付した巨大な怪しい(?)画像が[教えて!goo]の倫理(?)
審査を受審中のため公開に手間取っているみたいです。(*^_^*)
以下は問題ないので先に公開されようかと。

》 空白の内ドロップダウンリストを作成したい
「内」は「無い」の間違いかと。

セル F2 の[入力規則]メニュー内の
[元の値]:=$B$4:$D$4

セル G2 の[入力規則]メニュー内の
[元の値]:=OFFSET(I$5,,,COUNT(INDIRECT(F5)),)

式 =IFERROR(INDEX(INDIRECT(F$5),SMALL(IF(INDIRECT(F$5)<>"",ROW(INDIRECT(F$5))),ROW(A1))-4),"")
を入力したセル I5 を下方にズズーッとオートフィル
【お断わり】上式は必ず配列(CSE)数式として入力のこと

この回答へのお礼

画像付きで非常にわかりやすい説明ありがとうございます。
やりたいことが完璧にできました。

この数式使わせていただきます。

※誤字失礼しました。ご指摘ありがとうございます。

お礼日時：2024/02/21 10:08

No.3 回答者： mike_g 回答日時： 2024/02/21 01:28

添付図参照(Excel 2019)

￣￣￣￣￣￣￣￣￣￣￣￣￣↓↓↓↓↓↓↓↓↓↓↓↓↓

この回答へのお礼

お礼日時：2024/02/21 10:08

No.2 回答者： fujillin 回答日時： 2024/02/20 19:15

こんばんは

項目数が固定なのなら、初めから空白を含めた設定をしなければ良いでしょう。
必要な範囲だけを指定して、
　C5:C9をBランク、 D5:D8をCランク
と名前の定義をしておけば済みます。

項目数を可変にしたい場合は、2通りの方法が考えられます。
・名前の定義の内容を可変にしておく方法
・入力規則の範囲を可変にしておく方法
　（↑No1様の回答にある方法です）

いずれの場合も、上詰めになっている方が簡単ですが、スピル機能が使えるバージョンであればFILTER関数が使えるので途中に空白があっても簡単に対応可能でしょう。
（スピル機能が使えなくても可能ではありますが、式が相当に複雑なものになります）

この回答へのお礼

ご回答ありがとうございます。
実際に使用するファイルは、何個かの店舗が使い、
それぞれの店舗によって入る数が変わってきます。
従って可変でデータは作りたかったのです。
「名前の定義の内容を可変にしておく方法」
を検討させていただきます。

ご回答ありがとうございました。

お礼日時：2024/02/21 10:08

No.1 回答者： hyter 回答日時： 2024/02/20 18:50

https://blog-tips.sekenkodqx.jp/2019/05/01/excel …

G5の入力規則で
=OFFSET(INDIRECT(F5),,,COUNTA(INDIRECT(F5)))
とか？
↑のサイトにも書いてあるように行の途中で空白が出現するとズレますが...

この回答へのお礼

早速のご対応ありがとうございました。
無事にやりたいことができました！
行の途中の空白注意します！

お礼日時：2024/02/20 18:56