らくらくIT活用術
PC活用講座 - Excel
「Excelの便利機能活用術」
「ドロップダウン」で選択肢の変更を自動化するワザ
ITライター 立山秀利
セルにドロップダウンで入力可能にする「データの入力規則」は便利な機能だが、選択肢が追加された場合に設定を変更するのは大変だ。そこで今回は、選択肢の増減に自動対応するテクニックを紹介する。
列全体を指定すればよさそうだが……
「データの入力規則」機能を利用すれば、ドロップダウンから選択肢を選ぶだけでセルにデータを入力でき、手間と時間、入力ミスのリスクを最小化できることは、以前の記事で取り上げた。その際、選択肢はあらかじめセル上に用意しておき、そのセル範囲を指定する方法を紹介した。だが、この方法ではもし選択肢が増減したら、指定するセル範囲を変更しなければならないという問題が発生する。
例を挙げて解説しよう。今回は以下のように、4つの選択肢をワークシート「Sheet2」のA2～A5セルに用意したとする。先頭行のA1セルは見出しとして、「商品」を入力したとする。
図1
「データの入力規則」はワークシート「Sheet1」のC4～C24セルに対して、選択肢のセル範囲に「Sheet2!$A$2:$A$5」と、ワークシート「Sheet2」のA2～A5セルを設定したとする。そうすると、ドロップダウンの選択肢は全部で4つとなる。
図2
（画像をクリックすると拡大表示します）
その後、選択肢「USBメモリ 32GB」が1つ増え、ワークシート「Sheet2」のA6セルに追加したいとする。
すると、ワークシート「Sheet1」のC4～C24セルした選択肢のセル範囲は、ワークシート「Sheet2」のA6セルを含むようにしなければならない。終点セルをA5からA6に変更し、「Sheet2!$A$2:$A$6」と修正すればよいのだが、選択肢が増減する度に修正するのは手間がかかる。
そこで利用したいのが、見出しのA1セルを除いた形で選択肢のセル範囲を自動で取得するテクニックだ。自動で取得できれば、選択肢が増減しても設定変更せずに済む。
2つの関数でセル範囲を自動取得
選択肢のセル範囲を自動で取得するには、OFFSET関数とCOUNTA関数を組み合わせて指定すればよい。
OFFSET関数は指定した位置と大きさのセル範囲を取得する関数である。
------------------------------
OFFSET(参照, 行数, 列数, 高さ, 幅)
参照 基準のセル
行数 移動する行数
列数 移動する列数
高さ セル範囲の高さ
幅 セル範囲の幅
------------------------------
取得する位置は、引数「参照」に指定したセルを基準として、そこから引数「行数」と「列数」に指定した行と列だけ移動したセルを始点とする。取得する大きさは、行数を引数「高さ」で、列数を引数「幅」で指定する。
COUNTA関数は指定したセル範囲におけるデータ数を取得する関数である。データ数とは、何かしらのデータが入力されたセルの数のことだ。
------------------------------
COUNTA(セル範囲)
------------------------------
追加した選択肢をドロップダウンに含めるためには、OFFSET関数とCOUNTA関数を使って、「Sheet1」のC4～C24セルにおける選択肢のセル範囲は次のように指定すればよい。
------------------------------
=OFFSET(Sheet2!$A$2, 0, 0, COUNTA(Sheet2!$A:$A)-1, 1)
----------- -- -- ---------------------- --
参照, 行数, 列数, 高さ, 幅
------------------------------
図3
OFFSET関数の引数「参照」には、選択肢の先頭セルであるワークシート「Sheet2」のA2セルを指定している。引数「行数」と「列数」はともに0を指定して移動しないことで、始点のセルを基準セルのA2セルに設定している。A2セルを始点として取得するセル範囲は、列数は引数「幅」に1を指定することで1列としている。
引数「高さ」には「COUNTA(Sheet2!$A:$A)-1」を指定している。COUNTA関数の引数にA列全体を指定することで、A列に入力されているデータ数を取得する。そのデータ数には見出しのA1セルも含まれるので、選択肢の数はA1セル分の1を引いた数となる。
A2セルを始点として、大きさは行が選択肢の数、列が1列のセル範囲を取得できるようになったので、見出しのA1セルを含まずに、選択肢「USBメモリ 32GB」がドロップダウンに含まれるようになった。
図4
このように選択肢のセル範囲を指定すれば、選択肢の増減があっても設定を変更せずに済むため、作業の効率や精度をさらに向上できる。
その他の【Excel】を見る
立山秀利（たてやま・ひでとし）
カーナビのソフトウェア開発、Webプロデュース業務を経て、現在は、システムやネットワーク、Microsoft Officeを中心に執筆中。 主な著書に『Excel VBAのプログラミングのツボとコツがゼッタイにわかる本』などがある。
（監修：日経BPコンサルティング）