現場で即使える事しかやらない超実践EXCEL講座。
今回はリスト入力の応用編です。
リスト選択の選択肢が増えても項目を増やすだけで大丈夫な方法を紹介します。
前回紹介したリスト入力のより実践的な内容になります。
リスト入力って何？という人はまずは「選択肢から候補値選んで入力するリスト入力を覚えよう」から読んでください！
さてさて、「リスト入力」活用されている方も多いと思います。
ですが、実際に運用してみると困る事があります。
選択肢が増えていくんです。
商品名もそうですよね。ずっと同じじゃなく増えていく。そうなった時に範囲指定のリスト入力にしていたとしても「選択肢も増やしてリスト入力の部分も増やして・・・」とやる必要があります。「嗚呼！面倒くさい！！」となっちゃいます。
いちいちリスト入力の設定を変えるのが面倒だと思って、最初から多めに範囲指定すると、こんな感じになってしまいます。
選択肢に不要な空白が大量に入ってきます・・・これは不便ですよねぇ。
※一度も使っていないセルを範囲にしていると空白は出てきません。一度でも値を入れるとこうなります。
実際の現場で遭遇する問題ですよねぇ。
では、この問題を解決しましょう！
難易度：★★⭐︎⭐︎⭐︎⭐︎⭐︎⭐︎⭐︎⭐︎
多めの範囲を指定しつつリストの選択肢から空白を除外する方法
さて、今後のメンテナンス性を考慮してリストの範囲は広めにしておきたい、でも余計な空白は選択肢として出したくない。その解決策をお教えします。
リストの元の値を次の様に指定します。
=OFFSET($A$1,,,COUNTA($A$1:$A$10))
通常は「=$A$1:$A$20」と指定する所を、「=OFFSET($A$1,,,COUNTA($A$1:$A$10))」と指定します。
OFFSET関数を使う事で先ほどのリストが次の様に変化します。
こちらの思惑通り空白無しですね。
この状態でリストを増やすと・・・
増えました。これでメンテナンス性も大幅に向上しました。
公式的に覚える方法
各関数の意味はわからないけど、公式的に覚えておきたい文系頭の人は次の様に覚えると良いでしょう。
これだけです。
関数が二つ出てきます。
- OFFSET関数
- COUNTA関数
この2つを駆使していますね。とにかく、この2つの関数と出てくる順番と選択肢の範囲の位置をシッカリと覚えて下さい。
関数の意味を知って論理的に把握する
ここからは関数の意味を理解して、論理的に把握したい理系の人向けの解説になります。
COUNTA関数
COUNTA関数は指定範囲のセルの空白以外のセルの数を返してくれます。
=COUNTA(範囲1,[範囲2],[範囲3],…)
COUNTA関数はわかりやすい関数ですよね。
OFFSET関数
OFFSET関数は指定したセルから列数、行数の範囲の参照を返す関数です。
=OFFSET(参照,行数,列数,[高さ],[幅])
これだけだとちょっと難しいので図解します。
=OFFSET($A$1,1,1)と指定した場合、高さと幅は初期値の1が適用されます。
この場合、OFFSET関数はどのセルの参照を返すのか赤く示しています。
$A$1を起点として1行、1列オフセットした場所のセルの参照として”B2”を返します。
続いて「=OFFSET($A$1,,,2,2)」の場合です。
コチラは行数と列数を飛ばしているので初期値として「0」が適用されています。
高さと幅に2を指定しるので、規定から2行分、2列分の範囲を指定しています。
という事で赤い部分のセルの参照が返却される訳ですね。
これがOFFSET関数になります。
ちなみに、OFFSET関数の起点となる参照を範囲で指定した場合は左上のセルが適用されます。
組合せの意味
各々の関数の意味が理解出来てくるとリスト入力で設定した次の数式の意味が理解出来てくると思います。
=OFFSET(選択肢の先頭,,,COUNTA(選択肢の範囲))
この数式を読み解くとこうなります。
”選択肢の先頭”から選択肢として指定している範囲内にデータがセットされている個数(COUNTA)の行数分の範囲の参照(OFFSET)をリスト入力としています。
赤いアンダーラインがCOUNTA関数、青いマーカーがOFFSET関数でやっている事になります。
コメント