pickup

目次

OFFSETが難しいワケ

こんにちは。伊川(@naonaoke)です。

今回は、OFFSETについて、説明します。

このトリッキーな関数は、本当に初心者泣かせです。

しかし、一旦、このOFFSETを理解すると、VBAのコードに必ず使用される関数になります。

問題提起

 

(株)IKAWAの柄久 瀬瑠子(エク セルコ)は、OFFSETを理解できなくて困っています。OFFSETという言葉を聴いただけで、パニックになります。基本書を読んでも理解できません。

どうしたらいいだろうか?

解決策

回答
sesuo2.jpg
・OFFSET関数を使いこなせるように、カンタンな問題を練習してみる。

 

OFFSET関数とは

一般的なことは説明しません。言葉では上手く説明ができないのが、OFFSET関数です。

OFFSETを理解するにあたり、一番効果的な方法は、SUM関数と組合わせて、1時間くらい遊ぶと理解できます。

では、カンタンにOFFSET関数を説明します。


  1. 真ん中の列の一番後ろの席
  2. 近藤君の後ろの席
  3. 土方君の隣の席
  4. 坂本君の隣の席
  5. 桑田君から数えて、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

極限にカンタンにコードを記載した場合は、上の図のようになります。

ここがポイント

 

ここがポイント
Function8.png
OFFSET関数は、トリッキーな動きをするので、OFFSETと聴いただけで、アレルギー反応を示す人が大半です。暇なときに、今日でOFFSET関数を憶えると決めてください。OFFSET関数を理解すると、VBAは面白くなります。

 

まとめ


 

 

今回のまとめ
sesuko3.jpg
上の図が実行結果です。EcxelのVBAを操作するに当たって、最終列の横、最終行のすぐ下は、必須のスキルになります。頑張って、みなさんのスキルにしてください。

今回のサンプルファイルは、No45です。

毎日の業務が、3時間短縮できます

わからない事を延々と考えるのは、無駄です。

  • なんで作動かないの?
  • もうやだ!VBAなんか嫌い!
  • ネットで調べても情報がない!

必ず作動するコードが、ここにあります。

スポンサーリンク

Twitterでフォローしよう

おすすめの記事