目次
OFFSETが難しいワケ
こんにちは。伊川(@naonaoke)です。
今回は、OFFSETについて、説明します。
このトリッキーな関数は、本当に初心者泣かせです。
しかし、一旦、このOFFSETを理解すると、VBAのコードに必ず使用される関数になります。
問題提起
（株）IKAWAの柄久 瀬瑠子(エク セルコ)は、OFFSETを理解できなくて困っています。OFFSETという言葉を聴いただけで、パニックになります。基本書を読んでも理解できません。
どうしたらいいだろうか？
解決策
・OFFSET関数を使いこなせるように、カンタンな問題を練習してみる。
OFFSET関数とは
一般的なことは説明しません。言葉では上手く説明ができないのが、OFFSET関数です。
OFFSETを理解するにあたり、一番効果的な方法は、SUM関数と組合わせて、1時間くらい遊ぶと理解できます。
では、カンタンにOFFSET関数を説明します。
- 真ん中の列の一番後ろの席
- 近藤君の後ろの席
- 土方君の隣の席
- 坂本君の隣の席
- 桑田君から数えて、4つ目の席
2～5までの指定の方法が、OFFSET関数で、指定する方法になります。
近藤君の後ろの席をOFFSET関数で指定した場合は、下記のようになります。
ちなみに、OFFSETは日本語訳で、埋め合わせする、相殺する。という意味です。
（例）This will offset the loss. これで損失が埋め合わされる。
カンタンな練習問題をやってみる
解答手順 その1 そのコード、日本語でよめますか？
セルA15～セルA27にセルH1の伊川直助を転記するという問題です。
日本語でいうとこうなります。
セルA14の後ろから、セルC27から、左に3つずれたところまで。
少し、難しいです。
セルA14の後ろから、セルC27とA列の後ろを揃える。
つまり、VBAのコードは書きのようになります。
A列の最後に文字が入っている後ろから Cells(Rows.Count, “A”).End(xlUp).Row
C列の最後をA列に揃える Cells(Rows.Count, “C”).End(xlUp).Row
解答手順 その2 そのコード、本当に動きますか？
検証する方法は、イミディエイトウィンドウを使います。
では、検証します。
？Cells(Rows.Count, “A”).End(xlUp).Rowと入力して、Enterキーを押すと、14と数字が返ります。
つまり、Cells(Rows.Count, “A”).End(xlUp).Rowは、こう言っています。
A列の最後は、14番目です。
Cells(Rows.Count, “C”).End(xlUp).Rowは、27と返ってくるはずです。
返ってきました。
Cells(Rows.Count, “C”).End(xlUp).Rowは、C列の最後は、27番目と言っています。
解答手順 その3 イミディエイトウィンドウの結果を、VBAのコードで表現できますか？
Range(“A15:A27”)= Range(“H1”)を、変数、a,c,OFFSET関数を使って表現したいのです。
a = Cells(Rows.Count, “A”).End(xlUp).Row
c = Cells(Rows.Count, “C”).End(xlUp).Row
上のように、a,cと変数を使いました。
上の図のように、コードを読めるでしょうか？
Sub 転記()
a = Cells(Rows.Count, "A").End(xlUp).Row
c = Cells(Rows.Count, "C").End(xlUp).Row
Range(Cells(a, 1).Offset(1), Cells(c, 1)) = Range("H1")
End Sub
極限にカンタンにコードを記載した場合は、上の図のようになります。
ここがポイント
OFFSET関数は、トリッキーな動きをするので、OFFSETと聴いただけで、アレルギー反応を示す人が大半です。暇なときに、今日でOFFSET関数を憶えると決めてください。OFFSET関数を理解すると、VBAは面白くなります。
まとめ
上の図が実行結果です。EcxelのVBAを操作するに当たって、最終列の横、最終行のすぐ下は、必須のスキルになります。頑張って、みなさんのスキルにしてください。
今回のサンプルファイルは、No45です。
わからない事を延々と考えるのは、無駄です。
- なんで作動かないの？
- もうやだ！VBAなんか嫌い！
- ネットで調べても情報がない！
必ず作動するコードが、ここにあります。
