よく保険のCMなどでよく聞くフレーズ「見直し」が、最近すごく大事だなって思い始めたComeokaです。もちろん保険を見直すわけではないのですよ。
例えばExcelで関数やVBAを勉強しはじめの頃に作成した表や集計ツール、過去よりスキルアップしていれば「見直し」でより便利なものへ修正ができますからね！
今回は、そんな見直し時にも使えるネタとして、OFFSETとCOUNTAを組み合わせた関数で、データ入力をするときの便利機能「入力規則」を項目数に応じて参照範囲を可変化するテクニックをご紹介いたします。
入力規則とは？
入力規則とはその名の通りセルへの入力に規則、制限をかける機能です。
複数人で入力を行うようなExcelファイルでは、ルールを作ってもその通りにいかないことがしばしばあります。
そんな場合に、入力規則を使用することで強制的に条件から漏れた入力をさせないようにして、誤入力を減少させ入力効率・精度を向上させることができる便利機能です。
入力時の種類
| 種類 | 説明 |
|---|---|
| すべての値 | どんな値でも入力できます。 |
| 整数 | 小数を除く数値のみ入力できます。 |
| 小数点数 | 小数を含む数値のみできます。 |
| リスト | 設定したデータからのみ、プルダウン形式で入力できます。 |
| 日付 | 日付の入力のみできます。 |
| 時刻 | 時刻の入力のみできます。 |
| 文字列(長さ指定) | 指定した文字数のみ入力できます。全半角ともに１文字とカウントします。 |
| ユーザー設定 | 複雑な条件が設定できます。条件式がTrueの場合のみ、入力できます。 |
今回は、この入力規則の中で「リスト」機能の参照範囲を自動変更するテクニックをご説明していきたいと思います。
入力規則の「リスト」とは
リストとは、事前に値を設定しておいて、プルダウン化させたセルから入力ではなく選択形式でデータ入力をする機能です。
データタブ → データの入力規則 → 入力値の種類「リスト」
からリスト機能を使用することが出来ます。
プルダウンに表示させる項目（元の値）には以下の４パターンの設定ができます。
- 「A,B,C」のようにカンマなどの区切り文字を使用し手入力で値を設定
- 「=$A$1:$A$10」のように単一の行または列で範囲参照を設定（範囲をドラッグして参照が可能）
- 関数など数式でリスト範囲を設定
- 「名前の定義」の使用
今回は4つ目の「名前の定義」を使用していきます。
3つ目の数式でリスト範囲を設定でもできるのですが、複数のリスト作成すると名前の管理画面で一覧表示でき、管理がしやすくなるのでこの方法を推奨します。
※Excel2007以降では別シートの範囲もドラッグで選択できますが、Excel2003以前はそれができないので別シートの参照を行う場合は「名前の定義」を使うか、直接別シート参照の範囲を手入力する必要があります。
なお、「名前の定義」の説明はこちらの記事を参照してください。
入力規則の参照を可変範囲化する
区切り文字を使用した直接入力や、セル範囲の指定は「Yes・No」のように増減のない項目数であればよいかもしれませんが、増減が生じるたびに「元の値」を変更しなければいけないため、管理面に優れているとはいいがたいものです。
では、セル範囲を広めに設定しておいたらどうでしょうか？
上図のようにリストに空白部分が生じてしまうので見た目も使いやすさも良くありません。
そこで項目の増減が発生しそうなものに対しては、OFFSET・COUNTAを使って参照範囲を自動変更する見た目も使いやすさもよいリストにしましょう。
OFFSET・COUNTAで可変範囲を作成
サンプルデータを使用して、「入力」シートのセル「A2」に入力規則で「リスト」シートのA列のデータを可変範囲で設定していきましょう。
「リスト」シートのA列を可変範囲とする数式になります。この数式を文章で説明していきましょう。
使用している関数はOFFSET・COUNTAの２つです。
OFFSETは基準で設定したセルより、指定された行数と列数だけシフトした位置にあるセルの参照値を返す関数。
COUNTAは範囲に含まれる空白ではないセルの個数を返す関数。
今回はCOUNTAは「A列」を範囲としているので、見出しの「支店一覧」を含めた５が戻り値となります。
文章で先ほどの数式を説明すると、 セル「A2」から０行、０列シフトした位置つまり「A2」から高さがCOUNTAの戻り値「５」から１引いた４行数、幅他１列分の範囲を参照するという意味になり、結果的にセル「A2～A５」が参照範囲となります。
名前の定義に範囲を設定
先ほどの数式を名前の定義に設定をします。
数式タブ→名前の管理 または、 Ctrl + F3 で名前の管理を開き新規作成から「新しい名前」を開きましょう。
- 「名前」に「支店一覧」を入力
- 「範囲」は「ブック」（Excel2007以降のみ）
- 「参照範囲」に「=OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)」
これで可変範囲を名前の定義に設定は完了です。
「入力規則」に名前の定義を設定
プルダウンを表示させたいセルを選択し、データタブ→データの入力規則 を開きましょう。
- 「入力値の種類」を「リスト」を選択
- 「元の値」に先ほど名前の定義で設定した「=支店一覧」を入力
入力規則「リスト」が設定されたセルを選択するとプルダウンのカーソルがセルの右側に表示され、▼を押すとプルダウンが表示され選択することができます。
では、リスト範囲が可変になっているかを確認してみましょう！
「リスト」シートのA列に「支店E」を追加すると、「入力」シートのプルダウンに「支店E」が追加されました！
今度は、「リスト」シートから「支店B」を削除すると「入力」シートに戻ると、プルダウンはちゃんと４項目になりましたね！
さいごに
このOFFSET・COUNTAは入力規則だけでなく、そのほかの場面でも利用できます。
VLOOKやSUMIFなど範囲を用いる関数と併用すればこちらも範囲が可変化されます。
例えばVOOKUPやSUMIFなどの範囲を列で指定した場合、Excel2007以降では100万行を指定していることになります。
もしデータが1,000行しか入っていなければ1,000倍のデータ範囲をしていることになります。
VLOOKUPが１つだけなら違和感を感じることはまずないですが、パソコンはその分負荷を強いられていることになります。
同じVLOOKUPという関数でも使い方によってパソコンの負荷具合は異なるという事例でした
