マクロでExcelシートを操作する時に便利なOFFSET関数

ExcelVBAを扱っている方は、セル範囲の指定をすると思うのですが、データ数によって指定する範囲が変わるような仕組みを作ることもあると思うのです。
VBAで入力されているセルの数を調べることもできますが、今回はExcelのOFFSET関数を使って、データが入力されている範囲を指定する方法を紹介します。

OFFSET関数とは

OFFSET関数はExcel関数の中でもとても特殊な関数で、何かの答えをセルに出すという関数ではなく、その範囲自体を求めるという関数で、単体で使ってもあまり意味がありません。
書式は次の通りです。

=OFFSET(基準のセル, 基準のセルから見た縦のスタート位置,基準のセルから見た横のスタート位置,縦のセル数,横のセル数)

1セルだけ指定する場合は、縦のセル数、横のセル数は指定しなくてOKです。
非常に単純な使い方だと、次のような例が考えられます。

=SUM(OFFSET(A1,0,0,10,1)

これでA1からA10まで範囲の合計が求まります。

この式の10の部分をB1にすれば、縦の合計するセル数を変更できます。

どんなシステムで便利か

OFFSET関数はどんなケースで役に立つでしょうか。
今回、次のような出金伝票の入力画面を考えてみました。

件数が10件まで入れられるようになっています。
そしてそのデータを次のようなデータベースシートに蓄積していくとします。

入力画面の番号1の行のA列からL列までの範囲をデータベースシートに貼り付けていきます。
その場合、まず、入力画面のデータ10件を全部入れる必要はありません。データの入力された件数だけコピーできればいいのです。
また、データベースシートのデータの挿入位置の行ですが、これも蓄積されたデータ件数によってどんどん変わっていきます。
この2つのセルの位置をOFFSET関数で指定すれば、選択が楽になります。
このように、変化するセル位置、セル範囲を指定するのにOFFSET関数は便利なのですね。

実際のOFFSET関数

例えば、入力画面であれば、品名の数を数えてその行数だけ、セルA7スタートの横方向に12個の範囲を指定すればいいのです。
OFFSET関数にすると次のようになります。

=OFFSET(A7,0,0,COUNTA(C7:C16),12)

さらに、データベースシートの挿入位置は、セルA1スタートの、A列のデータ個数からセルA1の項目名のセル1つ分を引いた数に1足した場所になります。
ちょっと単純化するために1足して1引く部分を相殺すると、単純にA列のセル個数で指定すればいいわけです。
そうすると、次のOFFSET関数になります。

=OFFSET(A1,COUNTA(A:A),0)

OFFSET関数は名前で指定すると超便利

セルのジャンプなどの移動で、OFFSET関数で指定したところに移動しようという時は、名前として登録しておくと便利です。
変化するセル指定に対して、一つの名前を指定すれば、常にその範囲にジャンプできますから。
では実際に名前に登録してみましょう。
Alt→M→N→Alt+Nの順番でキーボードを押します。そうすると、名前の登録画面になります。
まず、入力画面の範囲を名前に登録しますので、「コピーデータ」という名前で「=OFFSET(A7,0,0,COUNTA(C7:C16),12)」の計算式を入力し、OKボタンをクリックしましょう。

ここでAlt+Nのキーを押してまた名前の登録画面にします。
今度は「データ挿入位置」という名前で「=OFFSET(A1,COUNTA(A:A),0)」の計算式を入力し、OKボタンをクリックしましょう。

名前の管理画面は、閉じるボタンで、Excelの画面に戻ります。
これで名前が登録されました。
Ctrl＋Gのキーでジャンプを呼び出して、ジャンプ先に「コピーデータ」に入力したらコピーするデータの範囲を範囲指定しますし、「データ挿入位置」に入力したらデータをコピーする先を選択します。
また、VBAで範囲指定する場合も、Range(コピーデータ).Selectで選択できるようになります。

OFFSET関数を名前登録するとマクロの記録がブーストする！

実は、OFFSET関数で動くセル範囲を名前登録する上記の方法の実力は、マクロの記録をする時に発揮します。
変化する範囲を指定するのはVBAでないとなかなか難しいです。
しかし、マクロの記録で登録した名前でジャンプできるので、記録が楽ですし、なによりもVBAでしかできないと思っていたことができるようになります。
今回マクロの記録をする作業の流れは、次の通りです。

  1. 入力画面のコピー元を選択
  2. コピー
  3. 貼り付け先を選択
  4. 貼り付け（または値貼り付け）
  5. 入力画面に戻る
  6. 入力した内容を消す

この1、3の選択の手順で登録した名前を使います。
VBAでも同じ流れなのですが、私であれば、VBAで作成すると10分くらいかかりそうですが、マクロの記録であれば1分で記録できます。
VBA、マクロの記録、どちらでするかは好みの問題ですが。
登録したマクロをボタンに登録しておけば、そのボタンをクリックしただけで登録ができるようになります。

まとめ

最後にまとめを書いているこの時点で、今回の記事って、自動化することに対してものすごいことを書いているような気がしてきました。
これがわかっているかわかっていないかで業務効率化ができるかどうか、そういうツールを作ること自体の効率がものすごく上がります。業務効率化するうえで、そのツールの速度も大事ですが、ツールを作成する効率もとても大事です。
まずはExcelのキホンを抑えなければならないですけど、ちょっとExcelのことがわかってきて、マクロの記録で動作を記録できるというのがわかるようになったら、こういうテクニックを研究するのもいいかもしれないですね。
今回は、OFFSET関数とそれを名前に登録することによって、マクロ作成が楽になったりVBAでしかできないこともマクロの記録でできる、ということを解説しました。
OFFSET関数という、一見表計算の機能としてはお門違いな関数にも思えますが、表計算ではなく、Excelとして考えたらこんなふごい関数が実装されててよかったなと、本当に思います。

コメント

タイトルとURLをコピーしました