Excelで自動化するときにセル範囲を選択する、というのは基本中のキホンです。
その範囲がいつも同じ範囲であればいいのですが、データを蓄積していけば蓄積した分、データが追加される行はどんどん変わってきます。
このような場合、VBAを使えば、Rangeの範囲を簡単に変更できるのですが、どうしてもVBAは難しい部分もあるので万人向けではないと思っています。
それでもExcelにはマクロの記録という、VBAのようなプログラム要素を全く触らずに自動化する機能があります。
しかし、それだと、範囲選択は固定したところしかできません。
でも、選択する範囲をOFFSET関数で指定すると、可変する選択指定ができるのです。
OFFSET関数を使う
OFFSET関数で範囲選択。どういうことでしょうか。
次のような表があります。
7月30日として、タコマヨの商品を2行追加したいのです。
そのためには。
セルB1をコピーして、セルA12、A13に貼り付けます。
セルB2をコピーして、セルB12、B13に貼り付けます。
でもこの12行と13行は、現在のデータ入力量と、セルB3に入力されている個数によって変化します。
その変化を捉えるためにOFFSET関数を使います。
現在のデータ入力量はA6から大目に見てA60000までの範囲のデータ数でわかるのでその範囲をCOUNTA関数で調べればOKですね。
データを追加する行数はセルB3を見ます。
それを使って日付を入れる範囲をOFFSET関数で求めると次のようになります。
=OFFSET($A$6,COUNTA(A6:A60000),0,$B$3,1)
同じように商品名を追加する範囲は次のようになります。
=OFFSET($B$6,COUNTA(B6:B60000),0,$B$3,1)
セル選択
あとはこの計算式を使ってセル選択します。
Ctrl＋Gのショートカットキーを押して、ジャンプのダイアログボックスを表示します。
参照先に先ほどの計算式を入力します。
OKボタンを押すと、選択がきちんとされているのです。
これをマクロの記録しておけば、常に適切な範囲をどんどん変更し選択できるようになるのです。
識者の方のために、VBAの記録内容は以下の通りです。
Formulaにもならす、そのまま計算式で求めたものが選択されるのですね。
もっと安全に記録するには、もしかしたらOFFSET関数を名前で登録すると良いのかもしれません。
しかし、セルが可変するような範囲を名前に登録すると、登録はちゃんとされるのですが、ジャンプのダイアログボックスにリストされないのです。
参照に、その設定した名前を入力すれば、正常にジャンプできます。
コメント