魔道学研究所 エクセルで複数範囲のリストを入力規則として指定する

スポンサーサイト

上記の広告は1ヶ月以上更新のないブログに表示されています。
新しい記事を書く事で広告が消せます。

エクセルで複数範囲のリストを入力規則として指定する

やってみただけ。実用性なんて知らない

Excelの入力規則には指定範囲をドロップダウンリストにするという機能がありますがカンマなどで分けられた複数範囲を指定することが出来ません。
ただし1行、1列に並んでいれば他のセルから参照してきた値でも構いません。

というわけで作ってみました。
へるめも1

A3に入っている式は
=IF(COUNTBLANK($B$3:$B3)<1,INDIRECT("B"&ROW()),IF(COUNTBLANK($C$3:$C3)-COUNTA($B$3:$B$1000)<1,INDIRECT("C"&ROW()-COUNTA($B$3:$B$1000)),INDIRECT("D"&ROW()-COUNTA($B$3:$B$1000)-COUNTA($C$3:$C$1000))))

となっています。意味合いとしては

=IF(COUNTBLANK($B$3:$B3)<1
'Date1の先頭(B3)からリストに表示されているX個の中に空白があるか

,INDIRECT("B"&ROW())
'空白が無い場合B列で今のセルと同じ高さの位置のセルの値を取得

,IF(COUNTBLANK($C$3:$C3)-COUNTA($B$3:$B$1000)<1
'空白がある場合C列でB列と同じ検査を実施、後ろのCOUNTAはB列のレコード数分だけマイナスの意

,INDIRECT("C"&ROW()-COUNTA($B$3:$B$1000))
空白が無い場合C列で今のセルと同じ高さ-B列のレコード数の位置のセルの値を取得

,INDIRECT("D"&ROW()-COUNTA($B$3:$B$1000)-COUNTA($C$3:$C$1000))))
空白がある場合D列で今のセルと同じ高さ-BとC列のレコード数を合わせた位置のセルの値を取得



とこんな感じですがリスト途中でいらなくなってセルを削除したり、ソートを行ったりした場合おかしくなってしまうため全てINDIRECT関数で指定した方が良いです

=IF(COUNTBLANK(INDIRECT("$B$3:$B"&ROW()))<1,INDIRECT("B"&ROW()),IF(COUNTBLANK(INDIRECT("$C$3:$C"&ROW()))-COUNTA(INDIRECT("$B$3:$B$1000"))<1,INDIRECT("C"&ROW()-COUNTA(INDIRECT("$B$3:$B$1000"))),INDIRECT("D"&ROW()-COUNTA(INDIRECT("$B$3:$B$1000"))-COUNTA(INDIRECT("$C$3:$C$1000")))))

INDIRECT関数…INDIRECT(文字列)で指定でき、文字列に入力された値の位置を取得してかえします (INDIRECT("A1:A500")ならA1:A500を指定したのと同じ) 別のブック、別のシート、パスを指定も可でROWなりCOUNTなりで数を入れたりも可能
式の最後にINDIRECT(文字列,1)で指定するとR1C1形式で指定に切り換え(R[現在の位置よりいくら下か]C[現在の位置よりいくら右の値か] )COLUMN()関数とか使うならこっち
文字列なので当然セルの削除などに無関係で指定位置の情報を取得できます。


次に名前を作成します。名前を作成することによって下に付く無駄な空白を消せます、また名前を指定しないとリスト範囲は別のシートを参照できません。
CTRLキーを押しながらF3キーを押します
名前の定義の画面が出てくるので適当に名前を付けてそのまま追加を押します。

今回の場合だと
=OFFSET(INDIRECT("Sheet1!$A$3"),,,COUNTIF(INDIRECT("Sheet1!$A$3:$A$1000"),"<>0"))
と指定します(シート名をSheet1とすると)

OFFSET関数…OFFSET(基準となる位置(A1など),その位置からどれだけ下の位置か,その位置からどれだけ右の位置か,基準位置を含めて何セル下までのセルを範囲とするか,基準位置を含めて何セル右までのセルを範囲とするか)
この場合A3の位置からA1000までの範囲で値が入力されている所を入力範囲とします。


後はリスト入力したいところに名前の定義で作った名前を入れればOK めんどうくさいね。


あとリストの途中に空白があるとおかしくなるのでこんな感じでマクロを使ってボタンとかにしていると便利です。

Sub 空白を上に詰める()
Range("B3:D1000").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Selection.Delete Shift:=xlUp
End Sub

やってる内容は
範囲指定→ジャンプ(CTRL+G or F5のアレ)→セルの選択→空白セルを選択→セルの削除(上方向にソート)
並び替えでもいいす。

とりあえずシートおいとくす→

コメントの投稿

非公開コメント

プロフィール

めいたむ

Author:めいたむ
過去に書いたことが半年後には黒歴史あると思います。

カテゴリ
最新記事
最新コメント
最新トラックバック
月別アーカイブ
カウントァー!
ブログ内検索
RSSリンクの表示
リンク
ブロとも申請フォーム

この人とブロともになる

上記広告は1ヶ月以上更新のないブログに表示されています。新しい記事を書くことで広告を消せます。