- 関数などで指定する範囲が変わるので選択し直すのが面倒
- 自動で範囲が変わってくれると良いのに
そんなあなたのために、「OFFSET関数を使って範囲を可変にする方法」についてご説明します。
Excel女子の皆さん、VLOOKUP関数は使えますか？ ビジネスでは「基本」とされるVLOOKUP関数ですが、 使ったことはあるけど忘れてしまった 実はイマイチわかっていない という方も多いのではないでしょうか[…]
OFFSET関数で最終行までを自動選択
関数で指定してる範囲、いつも最終行までを自動で範囲指定してくれたらいいのに…なんて思うことはないですか？
毎回範囲選択し直すなんてナンセンス、ミスの元です。
これからご紹介するOFFSET関数とCOUNTA関数を組合わせれば、常に最終行までの範囲選択が可能です。
SUMやVLOOKUPなどの関数の範囲に使えます。
ちょっとややこしいかもしれませんが、ぜひこの機会に習得しましょう！
OFFSET関数とは
まずは、OFFSET関数についてご説明します。
指定したセルまたはセル範囲から指定された行数と列数だけ移動した位置にあるセルまたはセル範囲を返します。
具体的にはこんな感じです。
SUM関数で利用
これをSUM関数の範囲として活用してみましょう。
下の表では、セルB3からB7の範囲を合計するためにSUM関数を使っています。
範囲「B3:B7」と指定する代わりにOFFSET関数を利用するとこうなります。
ここで、ポイントは「高さ5」の部分です。
今回はB3:B7で高さは5なのですが、
B7がB8、B9…となっていっても常に最終行までを指定するにはどうすればよいでしょう。
OFFSET関数とCOUNTA関数の組み合わせ
ここで活躍するのが「COUNTA関数」です。
COUNTA関数は範囲の中の「空白でないセルの個数」を数える関数です。
働く女子のみなさん、表の中にデータが何件あるか数える時に目で見て数えていませんか？ 件数が少なければそれでも良いかもしれませんが、 100件、1000件、10000件･･･日が暮れてしまいますね･･･。 しかも、件[…]
今回は、範囲をB列全体として利用します。
B列に「空白でないセル」が何個あるかを数えてもらうのです。
項目名にあたるセルB2の「駅前店」は除外するので「-1」とします。
これで、常にB列のデータの最終行までが範囲となります。
VLOOKUP関数で利用
ではこれを、VLOOKUP関数の範囲として活用してみましょう。
VLOOKUP関数では、参照表の範囲を選択します。
範囲が変動なければよいのですが、下記のように今後商品が増えていくことが考えられる場合、参照表の範囲を可変にしたいですよね。
ここで、先ほどのOFFSET関数とCOUNTA関数の組み合わせを使って範囲を指定します。
OFFSET関数
- 基点：セルE3
- 高さ：E列の「空白でないセル」の個数分 – 1（セルE2の項目）※COUNTA関数を使用
- 幅：EからGの3列
OFFSET関数使えば、選択範囲を可変にすることができるので、毎回選択し直す必要がなくなりますね。
＼１世代前の型落ち品を特別特価にてご提供！／
VLOOKUP関数の引数「検索方法」は、ほとんどの場合が「FALSE」完全一致です。 コードや番号などとピッタリ一致するデータを自動表示することができて便利ですよね。 [sitecard subtitle=関連記事 url=ht[…]
VLOOKUP関数を使うと「#N/A」というエラーが表示されることがあります。 参照先のセルに値がないために#N/A（ノー・アサイン）というエラーが表示されるのです。 下の表の場合、「コード」が未入力、つまり空白になっているため[…]