Excelでリストを作成したのはいいけど、後から項目を追加したいときってありますよね。
そうなると、またリストを追加する設定をしないといけなくなり、とても面倒くさいです。
そこで今回は、後から簡単に自動でリストの項目を増やす方法を紹介します。
OFFSET関数とCOUNTA関数を組み合わせた設定にする
結論だけ先に言うと、リストの値に「OFFSET関数とCOUNTA関数を組み合わせた数式」を入れることで、自動的にリストの項目を追加出来るようになります。
それぞれの関数について詳しく説明しますが、まずは以下の手順通りに追加してみて下さい。
手順1. リストを作成する
まずは、新しいシートを作成して、以下のようにリストの項目を作成しましょう。
ここでは「リスト」というシート名で、以下のように項目を記載しました。
また、項目を書く位置も重要です。
ここではB2セルから下に、「未着手」「進行中」「完了」と、書いていますので、記載する位置も画像と同じように書いてほしいです。
手順2. データの入力規則で数式を入力する
「リスト」シートに項目を記入したら、リストにしたいセルにカーソルを当てましょう。
そしてExcelの上の方にある、[データ]タブ → [データの入力規則] の順でクリックすると、以下のような画面になります。
そして、[入力値の種類]を「リスト」に変更した後、キャプチャ画像の赤枠のように、OFFSET関数とCOUNTA関数を組み合わせた数式を入力します。
手順1と同じように「リスト」シートに値を記入した場合は、数式は以下そのままでOKです。
=OFFSET(リスト!$B$2,0,0,COUNTA(リスト!$B:$B),1)
これで以下画像のように、リスト化されたと思います。
さて、では「リスト」シートに「保留中」という言葉を追加してみましょう。
すると、ちゃんと自動的に「保留中」という言葉が項目として追加されていると思います。
これでリストに自動的に反映されるようになりました！
【解説】OFFSET関数とCOUNTA関数について
ではここからは、先ほど使用したOFFSET関数とCOUNTA関数について詳しく解説していきます。理解を深めるためにも、ぜひ読んでみてほしいです！
OFFSET関数について解説
OFFSET関数ってあんまり使わないですよね。OFFSET関数は、以下のような意味合いになっています。
=OFFSET(基準のセル、基準からずらす行数、基準からずらす列数、範囲の行数、範囲の列数)
う～ん、ちょっと分かりにくいですよね。もう少し噛み砕いて説明していきます！
OFFSET関数は、セルの範囲を指定したいときに使用します。セルの範囲とは、A1からC3まで(A1:C3)といった範囲のことです。
そして「offset」という用語ですが、これは元々「基準からズレの値を示す」という意味があります。
なので、OFFSET関数というのは、「基準のセルから○○だけずらした範囲」を指定することができます。
実際に、OFFSET関数に値を入れながら説明していきますね！
例えば、以下のOFFSET関数の式があったとします。
=OFFSET(B2,2,3,5,2)
まず、=OFFSET(B2,2,3,5,2) より、基準のセルがB2なので、以下のオレンジ色のセルを指定しています。
=OFFSET(B2,2,3,5,2) より、基準のセルB2から、下に2行、右に3列にずらします。すると、オレンジ色のセルから、緑色セルの場所にズラすことになります。
セルの基準をズラせば、後はセルの範囲を指定するだけです。
=OFFSET(B2,2,3,5,2) より、行数が5行、列数が2列になっているので、最終的にOFFSET関数は以下の緑色セルの範囲を指定することになります。
これでOFFSET関数で範囲を指定できました。
つまり、今回のOFFSET関数の答えは、[E4:F8]となるわけですね。
ちなみに、OFFSET関数のみをセルに入力しても、#VALUE!と出力されてエラーになると思います。
これはOFFSET関数がExcelの範囲を指定する関数で、OFFSET関数の答えがセルの表示には適さないものだからです。
なのでOFFSET関数は単体では使用せず、他の関数と組み合わせて使用します。
COUNTA関数について解説
COUNTA関数は、指定したセルの範囲内で、空白以外のセルの数を数える関数です。
=COUNTA(セルの範囲)
例えば、以下画像のようにB列を指定した場合、空白以外のセルは、[未着手]・[進行中]・[完了]の3つ存在するので、COUNTA関数の答えは”3″になります。
OFFSET関数とCOUNTA関数を組み合わせるとどうなるのか？
OFFSET関数とCOUNTA関数は理解出来ましたか？
では、改めてOFFSET関数とCOUNTA関数を組み合わせた数式を見ていきましょう。
=OFFSET(リスト!$B$2,0,0,COUNTA(リスト!$B:$B),1)
まず、=OFFSET(リスト!$B$2,0,0,COUNTA(リスト!$B:$B),1)より、基準となるセルは、「リスト」シートのB2セルになります。
そのため、「リスト」シートの、以下オレンジ色のセルの場所が基準になります。
そこから、=OFFSET(リスト!$B$2,0,0,COUNTA(リスト!$B:$B),1) より、下に0行、右に0列ずらします。両方0になっているので、今回はB2セルからずらさないということになりますね。
さて、ここからです。
=OFFSET(リスト!$B$2,0,0,COUNTA(リスト!$B:$B),1) より、行数がCOUNTA関数行、列数が1列になっています。
行数について、今の状態だと[未着手]・[進行中]・[完了]の3つ存在するので、COUNTA関数の答えは”3″になります。
つまり、セルの範囲の行数 = COUNTA関数 = 3行になるので、以下の緑色セルの範囲を指定することになります。
リストの範囲が[B2:B4]になり、そこには[未着手]・[進行中]・[完了]の3つが存在するため、リストの項目が3つ出てくるというわけですね。
では、もし[保留中]という項目を増やしたら、どう変わるのでしょうか？
[未着手]・[進行中]・[完了]・[保留中]の4つ存在するので、COUNTA関数の答えは”4″になります。
すると、セルの範囲の行数 = COUNTA関数 = 4行といった感じで、行数が4行に変わるので、以下の緑色セルの範囲を指定することになります。
リストの範囲が、[B2:B5]に拡張されるので、[保留中]という項目も自動的に追加されたというわけです。
これが、リストの項目を増やしても自動的に項目が追加される仕組みです。一つ一つ見ていけば、意外と簡単に理解出来たのではないでしょうか？
最後に
OFFSET関数とCOUNTA関数を組み合わせた、リストの項目を自動的に追加する方法でした。
結構、色んな場面で活用できると思いますので、ぜひ仕組みも理解して今後も使ってもらえればと思います！
