先日は基本的なリスト入力の方法をご紹介しましたが、本日は選択肢が増減する場合のリスト入力の設定方法をご紹介したいと思います。
例えば、担当者マスタを作成していても、新しい担当者が追加になって、態々指定する範囲を再設定しなおしたりした経験はないでしょうか？選択肢の増減があまりないのであれば、たまにメンテナンスするくらいでいいかと思いますが、頻繁に元の値の範囲が増減するようだと大変ですよね。その場合の設定方法をご紹介いたします。
基本的なリスト入力の方法は別記事で紹介しておりますので、興味がある方はそちらもどうぞ。
選択肢が増減することがよくあるリスト入力設定方法
担当者が新しく増えた場合、担当者マスタに追加しました。けれど、このままではリストに追加されません。
現在のA2セルのデータ入力規則の設定はこんな感じです。
D7セルを元の値に指定する範囲に含める再設定をする必要があります。頻繁に元の値の範囲が増減するようだと大変なので、この元の値の増減にリスト入力が自動的に対応してくれたら楽ですよね。
その為には参照範囲に次のような式を入力して設定しましょう。
=OFFSET($D$1,1,0,COUNTA($D:$D)-1,1)
OFFSET関数
- 基準となるセルを決めて、そこから参照するセルをずらす。
- 基準としたセルからずらした先を起点にしてセル範囲を指定できる
=OFFSET(基準セル、ずらす行数、ずらす列数）
第一引数で指定したセル（基準セル）から第二引数で指定した数だけ上か下に、さらにそこから第三引数で指定した数だけ右か左にあるセルの値を持ってくるという関数です。第二引数が正の数であれば基準より下、負のカスであれば上の値を見にいきます。第三引数が正の数であれば基準セルより右、負の数であればひだりの値を見にいきます。
一つずつ解説していくよー。
=OFFSET($D$1,1,0,COUNTA($D:$D)-1,1)
D1セルを基準にして、下へ1、右へ0ずらした先のセル＝D2セルを起点に範囲を作成します。
その範囲の行数を指定するためにCOUNTA関数を使用します。
COUNTA関数
指定したセル範囲の中で何か値が入力されているセルの個数（空白ではないセルの個数）を数えてくれる関数。
では続きを行きましょう。
=OFFSET($D$1,1,0,COUNTA($D:$D)-1,1)
このCOUNTA関数ではD列全体のデータ件数から1を引いています。なぜなら、D列全体のデータ件数を数えてしまうと1行目の担当者マスタという項目名のセルも含んでしまうことになるからです。そのセルのカウントを除外するためにマイナス1としています。最後に第五引数で1列分の範囲（幅）を指定しています。こんな感じ↓↓↓
第四引数のCOUNTA関数が常にD列のデータ件数マイナス1の数字をとるのでＤ列に担当者が追加されたら自動的にリスト入力の選択肢にでてくるようになります。
参考になりましたら幸いです。
おすすめ書籍を貼っておきますので、興味がある方はのぞいてみてください。
 |
新品価格 |
コメント