excelで任意の列について名前を定義する際に、途中に含まれる空白セル(数式が入っています)をはじきたいです。

Question

前提・実現したいこと

excelで任意の列について名前を定義し、それを別の箇所でプルダウンリストとして用います。
列には途中空白セル(数式が入っています)が含まれており、
プルダウンのリストになる際に空白セルについては弾く仕様にしたいです。

試したこと

{=IFERROR(INDEX(sheet1!A:A,SMALL(IF(sheet1!A:A<>"",ROW(sheet1!A:A)),ROW(sheet1!A1))),"")}

別に作業シートを作り、配列数式を用いる方法を試しましたが、大変重くなってしまったため
それを用いずに実現できる方法をご相談できましたら嬉しいです。

OFFSETを用いて連続するデータセルの最終行までを"名前の定義"することはできましたが、
途中に空白(かつ数式が含まれる)がある場合については実現できておりません。

=OFFSET(sheet1!$A$1,,,COUNTA(sheet1!$A:$A),1)

データの数としては名前を定義する列は30~50,
各リストの要素は300ほどと予想しておりますが、
excelは一度他人の手元に渡るため念のため
データ数が増えても問題のないような仕様が理想的です。

Answer 1

データの数の最大数が分かっているのなら、配列数式のA:AのところをA1:A300とかにしてみてはどうでしょうか？
配列数式が重いのは列全体を計算しているせいかもしれません。

Comments

[20200713]

ご回答ありがとうございます！
現時点でだいたいのデータ数予測になるのですが、
excelが出来上がった後いちど私の手元を離れるため
やはり対象のセルはもし増えても問題がないようにしておきたい理想です。

名前の定義のみで空白セルを弾くのはやはり難しいでしょうか…？

[vann_2921]

エクセルのプルダウンには「空白を無視する」みたいなオプションがあるんですが空白文字列("")には対応してくれないんですよね...
マイクロソフトが対応してくれないと、現状は空白がないように並べ替えるしかないかと思います。

なので現状の問題を「並べ替えが原因で重くなる」ということに絞ってみますと、
①計算量を減らす(データ数を絞る)
②並べ替えた結果のみを使用する(人力もしくはマクロを使って配列数式の結果だけを保存する)
今思いつくのはこんな感じです。

①なら例では300としましたが1000でもいいかと思います。行全体を計算すると16,384行計算することになるので1,000/16,384 = 0.061 で計算量は6%くらいになるかと思います。

[20200713]

返答いただきありがとうございます！
いただいたアドバイス参考に、①で実装はできたので
次に、やはりマクロを使うこと考えてみようと思います…！

この度はご相談きいていただきありがとうございました！