Excel (VBA)

Excel VBAに関するフォーラムです。
  • 解決済みのトピックにはコメントできません。
このトピックは解決済みです。
質問

 
(Windows 7 Professional : Excel 2010)
セルに数式を入力する関連 変数使用 [循環参照] 
投稿日時: 17/07/11 12:37:22
投稿者: 鶴の舞

セルの数式を入力するにおいて下記、回答頂きました(多謝)。
http://www.moug.net/faq/viewtopic.php?t=75951
 
ここで新たな問題が出てきました。
シートをコピーしたり、データを並べ替えたりした場合、
算式が循環参照されず、元の計算式が残ってしまいます。
 
例)  Sheet1 A列  B列  差額   差額の算式
         100   50   50  =ROUND(Sheet1!B1-Sheet1!A1,2)
 
        sheet2 にコピー
         90   50   50  =ROUND(Sheet1!B1-Sheet1!A1,2)
 
    前の算式が残っている。循環参照されない・・・・・・・
    循環参照させるための方法あるのでしょか?
    わかりましたら、ご教授ください。お願い致します。
 
 

回答
投稿日時: 17/07/11 13:31:20
投稿者: 半平太

「循環参照」って、正しい意味で使っていますか?
 
質問の内容がなんだか分かりませんが、雰囲気からすると、
自シートを常に参照させる数式かな、と・・・
 
ならば「Sheet1!」を消去する。
 
>=ROUND(Sheet1!B1-Sheet1!A1,2)
  ↓
 =ROUND(B1-A1,2)

投稿日時: 17/07/11 14:33:02
投稿者: 鶴の舞

半平太様
 
 質問がわかりにくくて申し訳ありません。
 先日教えて頂いた 下記算式の 右辺。
  シートが変わったり、行が変わっても 前の算式のままです。
  (sheet6にコピーしたり、データを削除して5行目が3行目になっても下記右辺のまま。
   循環参照のように差額の式は元が変わったら、一緒に変わるようにならないかな・・)
   という質問でございます。
 
Range("K" & 5).Formula = "=ROUND(Sheet5!J" & 5 & "-Sheet5!H" & 5 & ",2)"

回答
投稿日時: 17/07/11 14:41:23
投稿者: めんたん

循環参照という言葉の使い方が間違ってます。
 
たとえば

    A
1  =A2/A4 循環参照
2  20
3  30
4  =SUM(A1:A3)

このとき A1 は A1 自体を参照する数式(A4 の=SUM(A1:A3)を参照しているので
循環参照しています。
 
シートの指定に関しては上述の通りすでに回答が出ていますよ。

回答
投稿日時: 17/07/11 14:48:57
投稿者: めんたん

行の指定に関しては、もともとのコードが分かりませんけど、For 〜 Nextステートメントで処理しているなら
 
For I = 5 to 10
   Range("K" & i) 〜
Next
 
数字の部分を変数に変えるだけです。

回答
投稿日時: 17/07/12 20:33:26
投稿者: simple

またまた蛇足で失礼します。もうすでに適切な指摘をいただいていますが、ダメ押しで。
 
前のスレッド
http://www.moug.net/faq/viewtopic.php?t=75917
の終わりのところで、
> よくみたら、シートを指定すべきは、式のほうではなく、
> 左辺のほうではないですか?
と指摘したのですが、スキップされたようで。
 
Worksheets("Sheet1").Range("K2").Formula = "=ROUND(J2-H2,2)"
などとするのではないですか?
 
・同じシートの中での計算であれば、数式中にシートを指定する必要はない。
  (というよりも今や逆効果となっている。)
・ただし、複数のシートに数式をいれるのであれば、シートを指定するのは、
  むしろ左辺のほうではないか、
ということでした。
 
# 循環参照と言う言葉の意味も誤解しているし、失礼ながら、
# マクロの勉強と併せて、Excelの一般機能の勉強を、
# もっとされたほうがよいと思います。

回答
投稿日時: 17/07/12 21:57:29
投稿者: WinArrow
投稿者のウェブサイトに移動

確認事項
 
 
数式が
>=ROUND(Sheet1!B1-Sheet1!A1,2)
となっているので、少数点以下があるのかの確認です。
 
例示の表データは、整数値だけですが、
実際には、少数点以下の数値もあるのでしょうか?

投稿日時: 17/07/13 14:37:20
投稿者: 鶴の舞

  ご連絡有難うございます。
 48.02 とか 21775.08 とか小数点以下2位までの数字はあります。
 よろしくお願い致します。
 
 ※メールを見るのが遅れ失礼いたしました。

回答
投稿日時: 17/07/13 14:43:41
投稿者: WinArrow
投稿者のウェブサイトに移動

>小数点以下2位までの数字はあります。
了解しました。
 
ところで、「循環参照」という言葉ですが、
どのような意味で使われているのでしょうか?

投稿日時: 17/07/13 14:51:00
投稿者: 鶴の舞

 
表計算において、算式をいれておくと
シートが変わったり、ソートしたりした場合に
自動的に 式も変わっていく。
  sheet1
    A   B   B-A sheet1 で計算
  sheet2 にコピーした時
    A    B   B-A  sheet2 で計算     という意味で使っております。

回答
投稿日時: 17/07/13 15:19:43
投稿者: WinArrow
投稿者のウェブサイトに移動

↑の説明で理解しました。
 
この機能は「相対参照」といいます。
 
相対参照は、列、または、行の片方にだけ適用することもありますが、
シートを相対参照することはできませんので、シート名を外します。
 
なぜシートが相対参照できないかというと、
シートの位置は、自由に移動すること(移動させられてしまう)ができるので、
相対的な参照はできません。
相対参照できるようなシート名を設定した場合は、数式で可能ですが、それは、次の(前の)シートでなくてもよいですよね・・・

投稿日時: 17/07/14 09:45:17
投稿者: 鶴の舞

   WinArrow様
   丁寧なご回答有難うございました。
  質問する度に 新しいことを教えて頂き、本当に助かります。
  作り方をかえ、マクロを2つに分けて、シートを指定しない作りで、計算式を埋め込むことに
  しました。
    質問内容見てお解りかもしれませんが、独学で見よう見まねで作っています。
  うまくいくこともあるのですが、壁にぶつかることが多いです。
  基本テキストを購入して、しっかり勉強したいと思います。

回答
投稿日時: 17/07/15 07:03:55
投稿者: simple

結局のところ、循環参照と言う言葉を誤用していることは理解されたのですか?
(どこからこうした言葉を使われたのですか?)
 
また、
> ・同じシートの中での計算であれば、数式中にシートを指定する必要はない。
> (というよりも今や逆効果となっている。)
ということは理解されたのですか?
他人がそう言うから、それも含めて二つやることにしたということですか?
きちんと理解したうえで、よい方法を使ってください。
 
以下、くどくなりますが追記します。
 
同じsheet1のなかで、例えば、
C1セルに A1+B1の結果を作成しようとするときに、
なぜ、= Sheet1!A1 + Sheet1!B1
という式にしたいと考えたのですか?
 
必要もないのに、式が長くなるだけじゃないですか。そう思いませんか?
あなたはワークシートの計算式にすべてワークシート参照を付ける形で、
Excelを利用しているのですか?
とても大変だと思いますが。
「シートを指定しなければ、その式が書かれているシートが前提とされる」
というのが決まりです。それが一番便利じゃないですか、
 
また、これをSheet2にコピーしたときに、ワークシート参照が変化しないのは、
Excelの仕様です。このほうが使い易いと開発者が決めているのです。
(ワークシートそのものをコピーした場合はその限りではありません)
そのことをよく理解していただきたいと思います。
いずれにしても、VBAより前の話ですよね。

投稿日時: 17/07/15 22:43:40
投稿者: 鶴の舞

simple様
 丁寧なご指摘有難うございました。
 今回は、たまたま うまく行きましたが、VBAはこれからも作っていかなければならいので、
 式の左辺にシートを指定するところ等ひとつひとつ試してみます。
  ・循環参照・・・勘違いでした 相対と絶対でした
  ・シートを指定した理由としては、マクロの入っているシートも含めて3部門の計算を行います。
   下記のようにアクティブ宣言をしても A部門シートではなく、別のシート(マクロ)の入って
   いるシート の処理がされましたので、
    Worksheets("A部門帳簿").Range・・・・ と シート名を指定して確実にそのシートの
   計算が処理されるようにした次第です。
      壁にぶつかったら、そこですべて解決して次にいければいいのですが・・・・・
 
 -----------------------------------------------------------------------------
   Worksheets("A部門帳簿").Activate
  Columns("D").Delete
      
    Range("A1:N1").HorizontalAlignment = xlCenter
     
    Columns("A").ColumnWidth = 4 'セルの幅設置
    Columns("B").ColumnWidth = 8 'セルの幅設置
    Columns("C").ColumnWidth = 14 'セルの幅設置
       
     Range("B2").Select 'ウインドウ枠の固定
     ActiveWindow.FreezePanes = True
     Debug.Print Time & " - 棚卸帳簿作成終わり"
      
          
         

回答
投稿日時: 17/07/15 23:05:22
投稿者: simple

引用:
・シートを指定した理由としては、マクロの入っているシートも含めて3部門の計算を行います。
 下記のようにアクティブ宣言をしても A部門シートではなく、別のシート(マクロ)の入って
 いるシート の処理がされましたので、
 Worksheets("A部門帳簿").Range・・・・ と シート名を指定して確実にそのシートの
 計算が処理されるようにした次第です。

いえいえ、勘違いしています。
上記は、
    Worksheets("Sheet1").Range("C1").Formula = "=A1+B1"
とした理由なら納得しますが、
    Range("C1").Formula = "=Sheet1!A1 + Sheet1!B1"
のように、
シート1の中の計算で完結している計算のはずなのに、
なぜ、数式の中に Sheet1というシート参照を入れたのですか?
その違いをきちんと理解していますか?
とお尋ねしたのです。
 
・数式の書込先のシートを特定することと、
・数式中にシート参照を入れること
を混同していませんか?
少なくとも曖昧なままになっているように思えてなりません。

投稿日時: 17/07/18 09:11:12
投稿者: 鶴の舞

 simple 様
  ご心配を頂き、有難うございます。
 アドバイス頂き、しっかり理解致しました。
 
 数を重ねながら、マクロのレベルアップを図っていきたいと思います。
 
 本件、解決済とさせて頂き度。