[[20161001150056]] 『Range表現について』(T16) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『Range表現について』(T16)

お世話になります。 [[20160927112823]] の者です。

今日も進行中の件名に対し、とても返信できるレベルになく、少々本筋から
外れるとの判断もありあえて別件をアップすること、ご容赦ください。
(多少は関係あるかとは思いますが...)

あるサイトでRange("A"&i)に対し「見苦しく、処理も遅いので使うべきでない」との主旨の記述がありました。
無学ゆえ便利なので何も考えずに多用(借用ですが、(笑))していますが、信頼を置いているサイトでもあり
(冒頭の主観部分はさておき)「遅い」が気になり確認してみたところ(※)、やはり遅かったです。

私のPCで1万行で約0.1秒の差でしたが、ごくたまに10万行を超えるデータを扱うこともあり、
知った以上看過できないかと思いましたが、理由がさっぱりわかりません。

急ぎませんが、どなたかお詳しい方のご教授をお願いします。

※確認は「前セル値+1の加算」を「Cells(i,"A")との比較で行いました

< 使用 Excel:Excel2010、使用 OS:Windows7 >


 こんにちわ。

 以下に理由も載ってます。
http://officetanaka.net/excel/vba/speed/s10.htm

(sy) 2016/10/01(土) 15:52


 理由については syさんご紹介の記事等の通りです。

 また、Cells(数値,数値) と Cells(数値,列記号文字列) でも、後者は 列記号文字列を【翻訳】するひと手間が必要になり
 前者に比べて遅いでしょう。

 ただ、たとえば Cells(数値,53) と書くと どの列なのか、可読性に劣ることになります。
 指を折って数えてコードを書いたとして、数え間違いで正しい列ではないところを参照するリスクもあり
 開発生産性でも、難ありでしょう。

 10万行の処理で、1秒であっても、遅延は許されないという ミッション・クリティカル な処理ならいざしらず
 通常の処理であれば、生産性、可読性、保守性の観点で、βは Cells(数値,53) ではなく Cells(数値,"BA") と書くのが常です。

 それと Range("A" & i) 、決して「見苦しい」ものではないと思います。
 最終行取得に、βは Range("A" & Rows.COunt).End(xlUp) を使いますが、見苦しいと感じたことはありません。

(β) 2016/10/01(土) 16:43


sy様、β様
早々にありがとうございます。
また、前質問でせっかくのご回答についていけずスミマセンでした。

syさんご紹介のサイトや、βさんのご説明でスッキリしました。

βさんと同様に、私も件の記述は今後も使い続けると思います。
ただ、今までのように“何もわからず”ではないのでTPOをわきまえられるかな..と。

これからもよろしくお願いいたします。
(T16) 2016/10/01(土) 18:33


こんにちは。
更新の衝突が起きましたがそのまま書き込みます。

Range("A"&i)では、
まず "A"& iで A5 などの文字列を作り(ここでひと手間かかる)
できた文字をRangeオブジェクトに渡しインデックスに変換させ(さらにひと手間)
往復2手間余計に掛けて、やっとRangeを特定できる段階になります。
まあ、無駄といえば無駄な手間です。美しくないと感じる人もいるでしょう。

可読性は、でも、たとえば"QPB"列といわれてもピンと来ないので
分かりやすい範囲でだけで成立することと思います。
列名で書くよりむしろ、売り上げの列とか電話番号の列とか表現するほうが
可読性は良いです。

( 佳 ) 2016/10/01(土) 19:48


  >以下に理由も載ってます。
  >http://officetanaka.net/excel/vba/speed/s10.htm
  読んでみましたが、速度に関しては微々たるもんだなぁというのが実感ですね。

  >私のPCで1万行で約0.1秒の差でしたが、ごくたまに10万行を超えるデータを扱うこともあり、
  比例でいくと1秒の差ですか? それもごくたまにある話でですか?
  そんなことより、ちゃんと動くものを書く事に気を使った方がいいような気がします。

  そういえば昔、With句を使うと速くなるか、しきりに質問していた人がいましたが、
  同じ感想を持ちましたね。

  ・・で、そのお偉いさんはどんなのが見苦しくないと言ったんですか?
  まず、それをその人に聞いてもらいたいもんです。
  私も参考にしたい。納得いけばですけども。

  またまた"そういえば"ですが、(もう見かけなくなりましたが)藤代千尋さんと言う実力者は
  Cells(x,"アルファベット") 派だったと記憶します。
  可読性がいいと言う理由で明言されていました。

  この人は、GOTOも深いループから一発で抜けるときは使い勝手がいいんだ、
  なんて言ってくれたりして 一境地に達している人は違うなぁと言う気にさせられました。

(半平太) 2016/10/01(土) 21:07


佳様、半平太様
ありがとうございます。

「そんなことより、ちゃんと動くものを書く事に気を使った方がいい..」
 ↑
ごもっともです。
本質問は単に遅くなる理由を知りたかっただけですが、「じゃぁ〜知ってどうすんだ」
となると答えようがないです..今の私には。(苦笑)

「・・で、そのお偉いさんはどんなのが見苦しくないと言ったんですか?」
 ↓
偉い人かどうかは知りませんが、おそらく「何もわからん者(私)は基本に忠実なコードを書け」
と言いたかったのだと理解しています。
「じゃぁ、基本って何だ」となると、これまた私には答えようがないですが..T T

Rangeのプロパティとかメソッドとかの概念?に関係するらしいですが、サッパリわかりません。

「一境地に達している人は違うなぁと言う気にさせられました」
 ↑
まさしく私が半平太さんに感じ入ってるのと同じ心境です。

これからもいろいろ刺激を受けながら学んでいきたいと思いますので、よろしくお願いいたします。

(T16) 2016/10/02(日) 01:40


 ループでセル参照するのは、RangeとCellsどちらも大量のデータでは遅くて使い物にならないと思います。

 RangeとCellsの速度差なんて、配列や豊富なExcelの関数や機能などと比べたら無いに等しい誤差でしかないと思います。
 それは前トピのセル参照と値参照の処理時間の差で分かったんじゃないでしょうか。

 早さを求めるなら誤差のような僅かな差を気にするより、配列や関数や機能の使い方を覚えた方が良いと思います。
 特に関数や機能はExcelと言うソフトを使ってる以上、使わないとExcelVBAの本当の実力を無駄にしてると思います。

 人に偉そうに言えるほど私は凄くないですが、私の中の基本はマクロは手操作の代わりになるものなので、
 マクロを覚えるより先に関数や機能のレパートリーを増やす事だと思ってます。
 関数や機能のほとんどはマクロ記録で作成できますから、そこから無駄な記述を整理する程度の知識があれば、
 短い記述でかなり凄いコードも書けると思います。

 マクロ記録とそこから無駄なSelectを消去するが基本だと思うんですがねぇ。

 見苦しいとかの見栄えに関しては、人の感性によると思います。
 私は見栄えより、Range()の記述は候補が出てくれて便利なので普通に使います。
 Cells()は候補が出なくなるので、積極的に使いたいとは思いません。
 Cells().Valueなどとすると、ステップ実行中にマウスを合わせても、中身が見れなくなりますし。

 私もそうなんですがメソッドやプロパティを間違えずに正確に入力できるほどマクロに詳しくなるまでは、
 見栄えよりそう言う便利機能の方が重要と思うんですが。

(sy) 2016/10/02(日) 08:14


>あるサイトでRange("A"&i)に対し「見苦しく、処理も遅いので使うべきでない」との主旨の記述がありました。
んー。あんまり敏感に反応せず、自分の判断で取捨選択したらいいと思います。
ぼくは状況に応じてかな?
ループするときは、文字列を生成・加工する手間のひと手間が気になりますが(個人的な美的感覚が大きいです)、文字列を作った方がわかりやすい時も多々ありますよね。
大量なデータが出てきて、どうしても処理速度に不満があれば検討する、程度の心構えでいいのでは?

僕がVBAを始めたころは、
「列番号を自作関数を作ってでもアルファベットに直してRangeプロパティでセルを指定する。」
と主張していた人がいたような気がしますが、
Rangeの方が遅いんですねー

>私のPCで1万行で約0.1秒の差でしたが、ごくたまに10万行を超えるデータを扱うこともあり、
>知った以上看過できないかと思いましたが、理由がさっぱりわかりません。
理由は内部でどのような処理が行われているか公開されてないと思うので、
皆さん想像で話す以外ないのでは?(間違ってたら誰か訂正してくれるはず。)
というか、そこまで知る必要がないというか、与えられた材料を上手く使いこなすしかないと。。。
看過できないほどの差ですかねぇ。。。。
全体の処理が1秒未満なら、0.1秒でも0.7秒でも使う側はあんまり気にしないかなぁと思いますが。。。
僕が無神経なだけかなぁ。。。。

>※確認は「前セル値+1の加算」を「Cells(i,"A")との比較で行いました
んと、同じコードでテストしないと、
比べる意味がないので、できればテストしたコードを提示してほしいです。
一般論で話してもしょうがないですし、
ハードは日々進化してますし、エクセルもどんどんバージョンアップしてるので、試してみないとはっきりしたことは言えないと思います。

Excel:2010
OS:win8.1
CPU:i7-5557U
メモリ:16GB

Sub test001()

    Dim i As Long
    Dim t

    Range("A:A").ClearContents
    t = Timer

    For i = 1 To 100000
        Cells(i, "A").Value = i
    Next
Debug.Print "test001", Timer - t
End Sub

Sub test002()

    Dim i As Long
    Dim t As Variant

    Range("C:C").ClearContents
    t = Timer

    For i = 1 To 100000
        Range("C" & i).Value = i
    Next

Debug.Print "test002", Timer - t
End Sub

test001 3.605469
test002 3.919922
なるほどー。。。
じゃぁ、こんな指定だと。。。

Sub test003()

    Dim i As Long
    Dim t

    Range("E:E").ClearContents
    t = Timer

    For i = 1 To 100000
        Range("E1")(i).Value = i
    Next
Debug.Print "test003", Timer - t
End Sub

test003 4.041016
ほむ。特に変化なしか。。。
For Each 〜Nextの方が
速いって聞いたことありません?

Sub test004()

    Dim c As Range
    Dim i As Long
    Dim t

    Range("G:G").ClearContents
    t = Timer

    For Each c In Range("G1:G100000")
        i = i + 1
        c.Value = i
    Next
Debug.Print "test004", Timer - t
End Sub

test004 3.316406
Cellsプロパティのインデックスを操作するより速そうですが、
比較検証の仕方がこれでいいのかは???
まぁ、値を入れるとか、値を加工するなら、配列変数に一気に代入する方法が
高速化の定番でしょう。

Sub test005()

    Dim Rng As Range
    Dim v As Variant
    Dim i As Long
    Dim t As Variant

    Range("I:I").ClearContents
    t = Timer

    Set Rng = Range("I1:I100000")
    v = Rng.Value

    For i = LBound(v, 1) To UBound(v, 1)
        v(i, 1) = i
    Next

    Rng.Value = v
Debug.Print "test005", Timer - t
End Sub

test005 0.1289063
段違いの速さですねー
やはり個別のセルに1つづつアクセスするのは、
遅いですが、配列変数は値のみの処理しか使えないですね。
でも、連番を入れるという作業は、
エクセルの標準機能でできるのでそっちを使った方が、
速いです。
手動でよく使うと思うんですが、オートフィルで連番を入れてみます。

Sub test006()

    Dim t

    Range("K:K").ClearContents
    t = Timer

    With Range("K1")
        .Value = 1
        .AutoFill Destination:=.Resize(100000), Type:=xlFillSeries
    End With

Debug.Print "test006", Timer - t
End Sub

test006 2.539063E-02
0.02秒^^
でも、連番入れるなら専用の機能がありますね^^(あまり使うことはないと思いますが^^;)

Sub test7()

    Dim t

    Range("M:M").ClearContents
    t = Timer

    With Range("M1:M100000")
        .Item(1).Value = 1
        .DataSeries Step:=1
    End With

Debug.Print "test007", Timer - t
End Sub

test007 2.539063E-02
もう、Timer関数の精度では測れない時間差ですね^^;;
エクセルの一般機能でできることはそちらに任せた方が、
もっと速い方法があるかも知れませんが、
そこそこ速くて楽です。(個人の考えです。)

まぁ、たくさんの人のコードを見て、自分でいろいろ実験研究して、
いろいろな情報の取捨選択を自分でしていったらいいと思います。

パフォーマンスが気になるなら。。。
http://excel-ubara.com/excelvba4/EXCEL215.html

こんな書籍もありますね。
https://www.amazon.co.jp/Excel-VBA-%E3%83%91%E3%83%95%E3%82%A9%E3%83%BC%E3%83%9E%E3%83%B3%E3%82%B9%E3%83%AC%E3%83%9D%E3%83%BC%E3%83%88-Something-want/dp/4904412079
興味があれば。。。^^
(まっつわん) 2016/10/02(日) 08:33


 皆さんの指摘と重なりますが・・・かつ、以下は T16 さんがそうだということではなく、あくまで「一般論」ですが。

 もちろん、操作者のストレスをミニマイズする【高速化】というテーマは、重要なものだと思います。
 でも、そもそも プログラム開発は、

 1.要件・課題を正しく解決する、「ちゃんとしたロジック」のプログラムを作る。
 2.次に、そのロジックを検証し、無駄な処理(不要な複数のループであったり、同じ値を何度も取得していたり)を
   そぎおとし、洗練させていく。
 3.【最後に】それでも、さらに高速化が求められれば、RangeかCellsかといったことも含む、コード1行毎の記述に高速化という観点でメスを入れる。

 順番は 1,2,3 です。
 1,2をないがしろにして、まず 3 を議論するケースが見受けられますが本末転倒でしょうね。
 不衛生な生活をしていて、でも、人前に出るときの身だしなみとして消臭剤を振りかけているのと同じですね。
 まずは、体を清潔に保つ、そういうことが、消臭剤の前に求められますよね。

(β) 2016/10/02(日) 09:28


 >「じゃぁ、基本って何だ」となると

 一つのセルを1回相手にする場合

 Range("A1") か Cells(1,1) ですよね? どっちの派も居るようです。
 分かり易さでは、Rangeのが上で、その中間にCells(1,"A") がある。

 1回・固定列ならどれもそんなに拘る様なもんじゃないと思います。

 問題になるのは、ループ処理をする場合ですよね。

 可変行だけなら Range("A" & i) や Cells(i,"A") で出来るし、実際それで構わないと思いますが、
 可変列が入ってくると、そうもいかないです。

 結局、Cells(i,j) の出番となる。

 なら、Cellsで統一感を出した方が(迷わなくて済むし)楽とも言えます。
 人間、慣れは恐ろしいもので、慣れるとそれが自然で美しく見える。
 (テニスのバックハンドは体にとって不自然そのものなんですが、上手くなると自然だと思えてくる)

 因みに可読性を言うなら、i , j なんて紛らわしいものを
 コンビで使うもんじゃないと私は思うのですが、結構居ます。
 これも慣れなんですかねぇ。私には理解不能。

 またまた"因みに"で、どうでもいい事ですが、"A1"はRangeの引数ですが、i,j はCellsの引数ではありません。

(半平太) 2016/10/02(日) 09:49


みなさま、 ありがとうございます!

いただいたコメントを肥やしに、ステップアップできれば..と思います。

特にまっつわんさんのコードは時間ある時に自分なりに考えながら確認したく思います。

もとより1秒そこらの時間を気にしているんではなかったんですが、「看過できない」という
文言が誤解を与える表現でした。
今回の例のようなものが「チリツモ」になればバカにならないので、ちゃんと理屈を
わかった上で使用せねば..という思いでした。

「わかった上で使う」と「わからないまま使う」では雲泥の差がありますから。

これからもよろしくお願い..と書きかけて、またまた気になるコメントが..(苦笑)

「"A1"はRangeの引数ですが、i,j はCellsの引数ではありません」

?????ですが、これも時間できた時に調べるようにします。

今から数日留守しますが、わからないときにUPさせていただきたく、
よろしくお願いいたします。

(T16) 2016/10/02(日) 17:49
追記)
まっつわんさんご紹介のサイトでした、件の記述があったのは..
(ページは思い出せませんが..)


こんにちは。

>まっつわんさんご紹介のサイトでした、件の記述があったのは..
> (ページは思い出せませんが..)

このページですね。
h ttp://excel-ubara.com/excelvba1/EXCELVBA311.html
見苦しいと断じた理由を知りたかったのですが、載っていませんで残念でした。

( 佳 ) 2016/10/03(月) 06:58


見苦しいと言われる根拠の推察なぞ。

Rangeって、セル番地を表す文字列を指定しますね。Range("A1")等が基本。
また、CellsもRangeオブジェクトと同等なのですが、こちらは数値指定します。Cells(1,1)になります。

Range("A" & i) は、i という数値を & を使うことで自動的に文字列扱いさせて連結。つまりCStr関数を省略してます。
"A1"という文字列を作り出しておき、これをRange内では行と列の数値に戻す。この、わざわざ数値→文字→数値とすることが、見苦しく、遅い、という根拠でしょうね。

私も、内部処理まで考えて無駄な事をするな、という基本的な考えは同意します。With文も、内部処理のアドレス修飾回数を減らし高速化と見やすさに貢献するので、使います。

とはいえ、所詮はインタプリンタ言語ですから、With文なんて1回だけのために使うと、修飾数が減るよりも、文字数が増えることによる速度低下が大きかったり、逆効果になるケースや、殆ど変わらない処理時間になるケースが多いのです。

なので、私は1行毎の処理にはRange("A" & i)は使いませんが、範囲消去のようなケースでは、この書き方を使いますね。

また、セルの操作は文字列連結せず、Cells(i,"A") を使います。 速度面から言えば Cells(i,1) なのですが、どの列か、という会話では、1列目と言うと行と間違える人が出てくるので、A列、と言うのが普通でしょう。この「A列」である事を強調するため、多少遅くなる(ループさせても1秒も変わらない、微々たるものですが)事よりも、見やすさ、直しやすさを優先しています。
(???) 2016/10/03(月) 11:34


こんにちは。

???さん、コメントありがとうございます。

「数値→文字→数値とすることが」美しくないのは同感です。
さらに、みてくれだけを言っても Range("A" & i) は美しくないです。
マクロの記録で作ったコードがガチャガチャして汚いのと同じ感覚です。
(もちろん感覚ですから、ちがう感覚のかたを否定するものではありません。)
そのうえでなお、「見苦しい」とはひととおりのことではありません。
そちらのサイトのかたが そこまで言う理由を知りたいものだと思います。

使うのは、美しくなくても使います。
ただ、セルは、基本 cells(i,1) です。
行・列の順のほうがわたしにはしっくり来ますし
列名より列番号のほうがしっくり来ます。
慣れですかね。
さすがに ひとと話すときはA列と言いますが(笑

( 佳 ) 2016/10/03(月) 21:46


 これじゃないですか?

http://excel-ubara.com/excel5/EXCEL813.html

 >マクロについて、かなり手慣れた人や、時にはExcelマクロの指導的立場にいる人が、
 >このような記述をしているのを見かけると、何とも嘆かわしい気持ちになります。

 とあるので、上級者の方に対しての文面だと思います。

 ???さんの
 >内部処理まで考えて無駄な事をするな、という基本的な考え

 と合わせて、それなら納得かなぁと思います。
 でも私のレベルでは、インテリセンスの支援機能が無いと、とても困るのでRange()ですね。
 失礼な言い方になって申し訳ないですけど、質問者さんのレベルでも、見栄えよりインテリセンスの方が遥かに重要と思います。

 速さ云々に関してはテストコード見ても100万件と言う現実味の無いコードで検証して数秒とか、10万件なら1秒も変わらないですね。
 それにVariant型変数に一括代入すれば100万件でも一瞬ですし、速さ云々言うなら直接参照はどっちも使うなが正解なんじゃと思いますね。

(sy) 2016/10/03(月) 22:11


皆さま、 ありがとうございます。

私レベルの者を決して突き放すことなく、かと言って甘やかすわけでもなく
前へ一歩踏み出させてくれるコメント、感謝します。

本校の教授陣は本当に素晴らしいと感銘しています。

なんか“得した気分”に浸っています。(小笑)
(今日は台風で自宅待機で一杯飲んでるわけで..は決してありません)

酔った勢いではありませんが、本校の管理人様含めご教授陣のエクセル
マスターというか習得法というか日頃何をやっているのかとか女(男)の好みとか
どうでもいいこともポツンぽつんと聞けたら最高だな〜と..

これからもよろしくお願いいたします。

(T16) 2016/10/05(水) 17:51


 もう終わっちゃったのかな?
 Rangeを使う理由。
 ABCの歌を歌いたくない。
 それもZまでだけで、後は新規ブック開いたりして確認するのがめんどい。
 1番の理由は、.を打つとプロパティやらが出てくること。
 BJ 10/05 21:57

>本校の教授陣は本当に素晴らしいと感銘しています。
皆さん、ホントに見識が高くて敬服しますね。
でも、こういう場所は、
「知らない人が聞く」
「知ってる人が答える」
とういう中で、「みんなで情報を共有していく」
という心構えでいいと思いますが、どうなんでしょうね。。。

>エクセルマスターというか習得法というか日頃何をやっているのか
入門書の通りにやれば動くけど、
自分で考えたコードは意図したとおりに動かないことも多いですよね。
そういう時は掲示板に質問が有効でしょう。

でも、独学でやってると一番困るのが、
「VBAは覚えたいが、やりたいことが思いつかない。」
ではないでしょうか?
もちろん、業務の効率化とかもあると思いますが、
内容が偏りますよね。
その点、掲示板は課題の宝庫であるので、
その中で課題を見つけるのが手っ取り早いんじゃないでしょうか?
そのなかで、「どうやるんだろう?」と調べたり試したり(時には別途質問したり)して、
コードを書いて、他の回答者さんのコードと見比べる。
回答として、書いたコードを提示するかどうかは別として、
経験値を上げる方法として有効なんじゃないかと思います。
僕は、掲示板に質問して教わった内容を、
また別の質問者さんに伝えたいなぁと思って回答してます。

>とか女(男)の好みとか
>どうでもいいこともポツンぽつんと聞けたら最高だな〜と..
ぼくもそういうの嫌いじゃないですが、
↑の方にも書きましたが、情報の共有という観点から、
雑談を入れると、
ここの管理者の方や他の回答者の見解は解りませんが、
過去ログを有効活用したいと思うなら、
不要なやりとりを長々書くのは好ましくないのでは?と思います。

話、変わります。

>>「"A1"はRangeの引数ですが、i,j はCellsの引数ではありません」
>?????ですが、これも時間できた時に調べるようにします。
「引数」という言葉はご存知でしょうか?
(Cellsプロパティに引数が無いというのは、入門書に書かれない、割と衝撃の事実だと思いますが。)
ご存知でなければ、調べてみてください。(他の用語もどんどん調べることをお勧めします)
理解されていたら、「オブジェクトブラウザ」で、「Range」を検索してみてください。
クラスがRangeのメンバーがRangeを選択して、下の方に
Property ・・・・
の箇所をコピーしてどこかに張り付けておきます。
同じく「Cells」を検索し、クラスがRangeのメンバーCellsを選択して
違いを確認します。

今度はヘルプで同じようにRangeとCellsを調べてみてください。
そこに書いてある内容とオブジェクトブラウザで見た内容を比べてみて、
おかしいなと気づいたら、脱初心者ですね^^
おかしいところに気がつかなったら、
「ヘルプを見たが解らなかった」と、もう一回聞いてみたらいいと思います。

>理由がさっぱりわかりません。
と思ったらまずは、ヘルプとオブジェクトブラウザを見ます。
初心者にはさっぱり理解できないことが多く書かれてますが、
基本公開されている情報はそれだけです。

あとは、試して確認するのみと思います。
もちろん優秀な先人の知恵に頼るのもありだと思います^^

(まっつわん) 2016/10/06(木) 10:15


あら、せっかくいいテーマだったのに、お忙しいのかな?
それとも、諦めちゃったのかな?

とりあえず埋もれないように上にあげておくのはおせっかいですかね。

参考URL>>
http://excelfactory.net/excelboard/excelvba/excel.cgi?mode=all&namber=178626&rev=0

(まっつわん) 2016/10/18(火) 09:52


コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.