[入力規則番外編]OFFSETとCOUNTAを使ったリスト化が便利[関数応用編]

みなさん、こんにちはケンケンです。

 

以前、入力規則でリストを作る方法をご紹介しました。

入力規則復習記事

こんにちはケンケンです。 今回は、前回に引き続きエクセルを使いこなす一歩目として、請求書作成方法をご紹介していきます。 前回の記事をご覧になっていない方は下記をどうぞ。 請求書作成初級編① […]

実は関数と組み合わせると利便性をアップさせることができます。

COUNTA関数とOFFSET関数を覚える必要がありますが、費用対効果は充分にあると思いますよ。

それでは、詳しく見ていきましょう。

 

この記事をご覧いただくと、

  • 入力規則でリスト化する方法
  • COUNT関数とCOUNTA関数の基礎
  • OFFSET関数の基礎
  • OFFSET・COUNTA関数と入力規則の合わせ技

以上が分かります。

関数のことはよくわからないな、という方はこちらの記事を参考にしてください。

筆者もこの方法を会得してからは、リスト選択が楽になり、なによりリスト作成からくるミスがかなり減ったのでおすすめできます。

 

入力規則を使ったリスト化の復習と問題点

みなさん、入力規則でプルダウンリストを使うことってありませんか。

けっこうメジャーな方法ですが、このリスト、OFFSETとCOUNTA関数を使って作ると、元データを追加・削除しても過不足なく選択できるようになるのです。

かなり便利な技なので、今回ご紹介していきます。

以前にもリスト化をご紹介しているので復習がてら今回使う資料を作っていきましょう。

A列に部署一覧を作成しました。

そして、C~E列にそれぞれ社員のパーソナルデータを入力していきます。

今回はD列(部署)をリスト化しますのでD2セルを起点として以下のように作って行きます。

上記のとおり、条件を設定してOKすると、

上記のとおり、プルダウンで検索できるようになりますね。

ここまでが復習です。

ただ、この方法だとA列のリストに増減があった場合、D列の入力規則には反映されません

上記では、追加した人事部が反映されていませんね。

今回は、A列のリストが増減した場合にも入力規則に連動できるようにします。

これを可能にするために必要な関数が2つあります。

それは、

COUNTA関数とOFFSET関数です。

それでは、解説していきます。

 

COUNTA関数を使ってみよう

入力規則を自動化するために必要な関数の一つ目がCOUNTA関数です。

データの数を数えるのに便利な機能ですので覚えておきましょう。

■COUNT関数とCOUNTA関数

まずはCOUNT関数の使い方を学びましょう。

COUNT関数の使命は、

対象の範囲に数値や日付などがいくつあるか数えて僕らに教えることです。

では、COUNTA関数はというと

対象の範囲内にデータがいくつあるか数えて僕らに教えるです。

2つの関数の違いは扱うデータの種類が違うということです。

今回は、部署データの数を数えたいのでCOUNTA関数を使います。ちなみに引数は同じです。

capture-20190421-163045.png

引数に関して知識があいまいだという方は、こちらをどうぞ。

では、実際にCOUNTA関数を使ってみましょう。A列の部署一覧にデータが何個入力されているか計算します。以下ご覧ください。

capture-20190421-163713.png

H2セルに

=COUNTA(A2:A6)と入力すると

営業部から開発部まで何個データが入っているか計算して結果「5」を返してくれます。

これが、COUNTA関数の使い方です。

OFFSET関数の基礎

聞きなれない関数がでてきましたね。

僕も最初はとっつきにくく理解しづらいし、覚えてどうするの?みたいに感じたこともあったのですが、応用が効く関数ですので、がんばって覚えましょう。

まず関数の使命を言葉で言うと

基準のセルから指定した分だけ縦横移動したセルを参照して教えてくれるです。

具体的に見ていきましょう。

capture-20190421-170052.png

引数が複数あるため、実際に例をお見せしましょう。

capture-20190421-170108.pngH2セルに

「=OFFSET(C2,2,2)」と入力すると「2018/2/28」という日付が返ってきます。

分解して解説しますと、

①「田中太郎」という値が入っているC2セルを起点に

②縦に2つ移動し(C4)

③横にも2つ移動する(E4)

④そこ(E4)にあった値「2018/2/28」を数式を入力したH2セルに返すということです。 

 

OFFSET関数とCOUNTA関数でリスト作成

それでは、2つの関数を組み合わせて、過不足に対応できるリストを作成していきましょう。

入力規則画面でD2セルに以下のように入力してください。

capture-20190421-171707.png=OFFSET($A$2,0,0,COUNTA($A:$A)-1,1)

これを、分解して解説していきます。

OFFSET関数の第一引数は「$A$2」ですね。

第一引数は、基準となるセルでしたからA2セルを基準にするという意味ですね。

ちなみに、参照する値が動かないように絶対参照にしています。

第二、第三引数は両方「0」ですね。

これは、縦と横に動きませんよ。という意味です。

ちなみに関数がこのままだと結果はどうなると思いますか。

そうです、A2セルを起点として縦にも横にも動いていませんから、答えは「営業部」という文字列しか返ってきません。

欲しいデータはA6の「開発部」までですよね。 

ここで、第四引数と第五引数が必要になってきます。

任意の引数なので先ほどの例では使わなかったのですが、第四引数は[高さ]を表します。

この例で言うとA2セルから何行参照して値を返しますか

と問われていると思ってよいです。

引数が「4」だったらA2からプラス4行してA2からA6が参照されます。

ただ今回は、引数にCOUNTA関数を使っています。

解説します。

COUNTA($A:$A)-1

これは、A列にあるデータの数を数えてからマイナス1をするという意味です。

ちなみに列は絶対参照にして固定します。

なぜマイナス1するかというと、マイナスしないとA1セルの見出しまでカウントしてしまうからです。

今回欲しいのは、実際のデータ数なのでその分マイナスします

そして、最後の引数は「幅」ですね。

これは簡単で、値として取りたいデータはA列の1列だけなので「幅は1」となります。

以上が関数の説明となります。ためしに、部署を増減させて試して下さい。プルダウンに過不足なく表示されていれば成功です。

 

capture-20190421-174347.png

まとめ

いかがでしたか、関数を組み合わせると便利な機能を作れる例をご紹介しました。

 

新しい関数を覚えるのは、けっこうしんどいですが一度ものにすると応用が効くので頑張って理解してください。

今回ご紹介した、COUNTA関数とOFFSET関数はもちろん単体でも使用価値は高いですし、ほかの関数ともコラボできる可能性があるので、みなさんもいろいろ組み合わせて試してみてください。

 

おすすめ記事

エクセルを初歩から勉強したい方

みなさんの中にはエクセルを使いこなしたいな、と考えている方も少なくないと思います。しかし、何から手を付けてよいかわからない。もしくは、知識を手に入れても応用が利かない。というような経験はないでしょうか。 そんな方に向けて、[…]

関数を基礎から勉強したい方

みなさん、こんにちはケンケンです。 今回は、請求書作成の中級編と題しまして、関数を使うための前提知識と重要関数VLOOKUPを主にご紹介します。 質問ですが、 日頃からストレスなく関数を使えてますか? 相対参照と絶対参照の違いを[…]

エクセルVBAを勉強したい方

こんにちはケンケンです。   みなさん、エクセルVBAを使ったことがありますか? 存在は知っている方が多いかと思いますが、実際に使ったことがあり実務で行かせている方はなかなかいないのではないでしょうか。   V[…]