[[20100709150607]]　『特定範囲のみ再計算』（あゆ）　ページの最後に飛ぶ

『特定範囲のみ再計算』（あゆ）

 たびたびお世話になっています。
過去ログを探しましたが、ぴったりのものがなくて、
どうしてもうまくゆかないので、質問します。

 入力中は、参照式の自動計算は行わず入力範囲の条件付き書式のみ
リアルタイム（自動再計算）で動くようにするにはどうしたらよいのでしょうか。

 シート内に別シートからの参照計算が沢山あり、
自動計算だと、入力後にセルを移るたびに「待ち」が出て
わずらわしいので、手動再計算にしています。

 入力セルには、入力もれも防ぐために条件付き書式で、
ある条件になるとセルの色が変わるようにしています。

 しかし、自動計算ではないので、「F9」を押すまで
セルの色は変わりません。
入力は、多数行にわたるので、画面に表示されてない行に
入力もれがあったとしても、見過ごす恐れがあり、
条件付き書式を入れている意味がなくなってしまいます。

 そこで、次のマクロを書きました。

 Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  If Target.Address = "$a$3:$q$690" Then

    Target.Calculate

  End If

  End Sub

 しかし、範囲内でセル移動しても、マウスでクリックしても、
うまくセルの色が変わりません。
色が変わったり変わらなかったり、色が変わったセルも、条件をクリアしても
色なしになってくれません。
「F9」を押すと、条件付き書式は正常に働いています。

 他に考えが浮かびません。よろしくお願いします。

 ちょっと分からない部分がありますので、逆にお尋ねします。

 そのコードで条件付き書式がワークしたとして、
 その時、Target.Address はどのようなものになっているハズと思っていますか？
 (ご提示のコードは、Target.Address が "$a$3:$q$690"　の時である、と云っていますけど？)

 普通考えると、Targetが、Range("$a$3:$q$690")のどこかにある場合、
 つまり、Target.Address がA3でも、C600でもマクロを起動させる、と云うことになるのではありませんか？

 もしそうなら、こうなります。
 　　　　　　　　↓
  Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     If Not Intersect(Target, Range("a3:q690")) Is Nothing Then
       Range("a3:q690").Calculate
     End If
   End Sub

 でも、再計算させるのは Range("a3:q690")内のどこかが変更された時ではないのですか？
 上のコードですと、Q690セルを変更すると、Target.Addressは「Q691かR690」となるのが普通であり、
 再計算されません。それでいいのですか？

 (半平太) 2010/07/09 16:52

半平太様　回答ありがとうございます！

 >普通考えると、Targetが、Range("$a$3:$q$690")のどこかにある場合、
 >つまり、Target.Address がA3でも、C600でもマクロを起動させる、と云うことになるのではありませんか？

 そうなんですが・・・なんかまちがってますか？
ターゲット範囲は、データを入力する領域で、計算式は入っていませんが条件付き書式が入っています。
このマクロで再計算させたいのは、条件付き書式だけです。
計算式は再計算に時間がかかるので、手動計算にしておいて、入力時の「再計算待ち」を回避しようとしています。

 >Q690セルを変更すると、Target.Addressは「Q691かR690」となるのが普通であり、
 >再計算されません。それでいいのですか？

 前述しました通り、再計算したいのは条件付き書式の入っているセルのみです。
計算式の結果は入力時には関係ないので（計算式はR列～AE列にまとめています）
入力後に手動で再計算でもかまわないのです。

 記入いただいたコードでも、元のコードでも、ついでに言えばマクロ自体がなくても
手動計算の設定の下ではまったく同じ動きです。
（色が付くセルもあれば付かないセルもある。条件をクリアしても色が「なし」にならない　など）

 どうやったら実現できるのでしょう・・・

 (あゆ)

 > >普通考えると、Targetが、Range("$a$3:$q$690")のどこかにある場合、
 > >つまり、Target.Address がA3でも、C600でもマクロを起動させる、と云うことになるのではありませんか？
 >
 > そうなんですが・・・なんかまちがってますか？
 だって、Target.Address が、"$A$3"だったら、"$A$3:$Q$690" とはイコールになり得ないです。

 >ターゲット範囲は、データを入力する領域で、計算式は入っていませんが条件付き書式が入っています。
 >このマクロで再計算させたいのは、条件付き書式だけです。
 実験してみると、条件付き書式を設定したセルを再計算しても、
 条件を再評価させる効果はなさそうです。

 では、条件付きの判定を再評価させるきっかけは何かと云うことになりますが、
 バージョンによって異なるようで、厄介です。

 2003や2007で実験すると、計算式が入った任意のセルを1つ再計算させれば
 再評価してくれるようです。
 なのでテストとして、Q690セルに=A1000　なんて式を入れてみたら如何でしょう？

 ※当然、条件付き書式の評価が正しくなる状況になっていなければなりません。
 　もし、手動にしていることでそういう状態にならないとしたら、
 　条件付き書式が再評価されても何の意味も為さないことになります。

 >どうやったら実現できるのでしょう・・・
 　今まで、ご提示いただいている情報だけでは、判断できないと思います。
 　　バージョンはなにか？
 　　どんな条件付き書式なのか？
 　　条件を再評価すると、手動計算モードにおいて表示されているデータでも、
 　　正しい真偽評価が下せる状態なのか？

 　これらが分からなければ始まりません。

 (半平太) 2010/07/09 23:22

半平太様

 >だって、Target.Address が、"$A$3"だったら、"$A$3:$Q$690" とはイコールになり得ないです。

 すみませんが、いまいち言われてる事が理解できません。
質問をしてもいいか不安ですが（怒り口調で回答いただいているので）、
SelectionChangeは、セルの選択を変更したとき発生するイベントと理解していいですか？
マクロには詳しくないので、おかしな質問かもしれませんが、
私の理解としては、設定したターゲットの範囲内でセルの選択を変更したら
イベントが発生すると考えています。
そもそもここが間違いですか？

 もうひとつ理解があやふやな部分は、再計算（Calculate）ですが、
部分的な再計算ってできるものなのですか？
自分で言っておいてなんですが、いろいろ試しているうちに出来ないのではないかと思うようになりました。

 条件付き書式は、入力もれを防ぐための策として設けています。
1行1レコードになっているので、途中のセルの入力もれをなくしたいのが目的です。

 A3にはAND(COUNTA(B3:Q3)<>0,A3="")　ならば　セルの色を赤に
 B3にはAND(COUNTA(A3,C3:Q3)<>0,B3="")　ならば　セルの色を赤に
 C3にはAND(COUNTA(A3:B3,D3:Q3)<>0,C3="")　ならば　セルの色を赤に
 ・
 ・
 ・
 と、Q3まで同じように設定し、それが690行目まであります。
つまり、条件付き書式の条件は、計算式の結果ではなく、セルに入力があったかどうかです。
 F9を押して再計算させた時と自動計算モード下では正常に（狙ったとおりに）表示されます。

 エクセルのバージョンはPCによってまちまちです。（2000、2003、2007があります）
ですので、2000で動けば、すべて動くのではないかと考えています。

 この情報で分かりますか？
よろしくお願いします。

 （あゆ）

 「イコール」と「範囲内」は全く異なります。

 ヘルプより引用
 > Calculate メソッド
 > 計算を実行します。計算の対象となるのは、開かれているすべてのブック、ブック内
 > の特定のワークシート、ワークシート内の指定されたセル範囲のいずれかです。
 セル範囲の再計算も可能です。
 (aaa)

 指定範囲の一部分だけを計算したいのであれば、

 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Intersect(Target, Range("$A$3:$Q$690")) Is Nothing Then Exit Sub

    Intersect(Target, Range("$A$3:$Q$690")).Calculate
 End Sub

 のような感じでどうでしょうか。
 (Mook)

 >>Target.Address が、"$A$3"だったら、"$A$3:$Q$690" とはイコールになり得ないです。
 > すみませんが、いまいち言われてる事が理解できません。
 １．東京都は関東ですが、関東は東京都ではないです。両者はイコールじゃないです。

 　　何処かのセルに「="$A$3"="$A$3:$Q$690"」と入力してみれば、FALSEが返るのが分かります。

 　　この議論は、条件付き書式の本質的な問題ではなく、
 　　プログラミング上のテクニックの問題に関することなので、これで終了させて頂きます。

 >SelectionChangeは、セルの選択を変更したとき発生するイベントと理解していいですか？
 ２．正しいです。
 　　ですが、A3セルを選択して何か入力すると、隣のセル(A4かB3)が選択されるのが普通です。
 　　従って、この場合、SelectionChangeは2回発生します。A3で一回、隣のセルで一回。

 　　でもA3で発生した時はまだ新しいデータになっていませんから、再計算しても意味はないです。
 　　A3に何か入力して、隣のセルが選択された時、再計算が実施されます。

 　　それで何も問題はないじゃないかと思われるかも知れませんが、
 　　もしこのA3が、Q690だったらどうでしょうか？
 　　Q690の変更を反映させようにも、変更後のSelectは"$A$3:$Q$690"の範囲外のセルに
 　　なっているので、SelectionChangeイベントは発生してくれません。

 　　この議論も、条件付き書式の本質的な問題ではないので、これで終了させて頂きます。

 > もうひとつ理解があやふやな部分は、再計算（Calculate）ですが、
 >部分的な再計算ってできるものなのですか？
 ３．ここは、他の回答者が答えられているのでスルーします。(出来ます)

 >1行1レコードになっているので、途中のセルの入力もれをなくしたいのが目的です。
 > A3にはAND(COUNTA(B3:Q3)<>0,A3="")　ならば　セルの色を赤に
 > B3にはAND(COUNTA(A3,C3:Q3)<>0,B3="")　ならば　セルの色を赤に
 > C3にはAND(COUNTA(A3:B3,D3:Q3)<>0,C3="")　ならば　セルの色を赤に
 ４．もっと複雑なものなのかと懸念していたのですが、シンプルだったので、ほっとしています。
 　　それなら、手動モードでも、データはあるがままで正しい条件判定ができます→見通し明るいです。＾＾

 　　※ ちなみに、ご提示の条件式は、A3:Q690を一括選択して、条件式を
 　　  これに設定したものと同じだと思います。（一括設定できますのでこっちの方が楽だと思います)
 　　　 ↓
   　　=AND(A3="",COUNTA($A3:$Q3)>0)　→意味:自セルは「空」かつ全体では何か1つは入っていたら(赤にする)

 >エクセルのバージョンはPCによってまちまちです。（2000、2003、2007があります）
 >ですので、2000で動けば、すべて動くのではないかと考えています。
 ５．上の前提で、2000と2007について実験してみました。
  (1)2007は、再計算させる必要もなく、常に正しい色を付けてきました。
 　　　 つまり、マクロでCalculateする必要もなく赤に変わってくれた（と思います）。

  (2)2000は、どうしても安定的に条件付き書式を反映させることはできませんでした。
     でも、この解説によると、条件付き書式は再計算の対象であると云っていますので、何かおかしい　(^^ゞ
 　　　　　　↓
 【Excel 2007 におけるパフォーマンスの改善】
http://msdn.microsoft.com/ja-jp/library/aa730921.aspx#aa730921_topic9
 >　・・・・再計算時に、次の要素だけを再計算します。・・・・
 >　　　・セル、数式、値、名前のうち変更が行われたもの、または再計算が必要であるというフラグが設定されているもの
 >　　　・再計算が必要なセル、数式、名前、値に対する依存関係を持つセル
 >　→　・可変的な関数と条件付き書式

 そこで、もしかして正しく再評価されているのに、画面に反映されていないだけではないか？
 そう思い、Range.Calculateの後に、ScreenUpdatingをTrueにしてみました。
 すると、私の環境では、必要なセルが赤に変わってくれました。

 以下のコードですが、そちらの環境で試してみていただけませんか？

 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
     If Not Intersect(Target, Range("A3:R691")) Is Nothing Then    '一行/一列余分に範囲指定してあります。
        Range("A3:Q690").Calculate
        Application.ScreenUpdating = True
      End If
 End Sub

 (半平太) 2010/07/10 18:38

 気になったので、もう一度XL2000で実験してみました。

 単にこれでよさそうでした。。。（Calculateは要らないかもです?!）
 　　　↓
 Private Sub Worksheet_SelectionChange(ByVal Target As Range)
 　　　　Application.ScreenUpdating = True
 End Sub

 (半平太) 2010/07/11 07:48

aaa様、Mook様、半平太様

 みなさま回答ありがとうございました。
お礼が遅くなりすみませんでした。

 いろいろな情報、検証をいただき深く感謝し、感動しています。
少しだけ賢くなれた気がします。みなさまのおかげです。

 さて、私の質問ですが、結論から言いますと、
半平太さんの方法で、うまくいきました！！（2000、2007とも）
Calculateを入れて書いてくださったコード（Application.ScreenUpdating = True　付き）
でも問題なく動作しますし、
ご指摘のように、CalculateなしでApplication.ScreenUpdating = Trueのみのコードで
動作しました。

 こんなにシンプルでよかったなんて・・・！とびっくりです。
とにかく、解決できてこんなにうれしい事はありません。
半平太さんはじめ、ご協力いただいたみなさま　本当にありがとうございました。
心から感謝します！！

 （あゆ）

コメント返信：

[ 一覧(最新更新順) ]

[[20100709150607]] 『特定範囲のみ再計算』（あゆ） ページの最後に飛ぶ

[[20100709150607]]　『特定範囲のみ再計算』（あゆ）　ページの最後に飛ぶ