[[20221031202902]]　『行の追加や削除でも数式がずれない方法』（事務員）　ページの最後に飛ぶ

『行の追加や削除でも数式がずれない方法』（事務員）

お世話になります。
a1.a10.a15に値があり、その合計をa20に表示させるには
a20cellにa1+a10+a15ですが
行の挿入や削除した場合は参照cellかズレます。
どういう計算式にすればよろしいでしょうか？
過去ログみてもイマイチでした。
絶対参照でも上手くいきません。
解決策を教えてください。

＜使用 Excel：Excel2013、使用 OS：Windows10 ＞

 このようなことだろうか？
 =INDIRECT("A1")+INDIRECT("A10")+INDIRECT("A15")
（ねむねむ） 2022/10/31(月) 21:19:55

確認してみましたが、行追加で計算結果が変な値になります
対処方法ありますでしょうか？
（事務員） 2022/11/01(火) 10:05:34

 行追加や行削除をしてもA1セル、A10セル、A15セルを計算対象にしたい、
 という意味だと判断したのだが異なるのだろうか？
（ねむねむ） 2022/11/01(火) 10:09:22

A20セルの数式が

 =A1+A10+A15

だったときに、どういう操作をしたときにどういう数式に変わって
事務員さんはどういう数式になってほしい、というものを具体的に示してください。
（西瓜） 2022/11/01(火) 10:21:05

＞行の挿入や削除した場合は参照cellかズレます。
a20 もずれますけどそれでいいんですか。
（???） 2022/11/01(火) 10:32:41

ねむねむ様西瓜様 ???様ありがとうございます

説明不足ですみません。

行の追加や削除をした時に元々のa1やa15やa10に入ってる値を
そのまま計算して計算結果をa20に出したいと言う事でした。
a20のセルは動いても構いません。

宜しくお願いします
（事務員） 2022/11/01(火) 11:19:15

それ（行の追加や削除をした時に元々のa1やa15やa10に入ってる値をそのまま計算して計算結果をa20に出す）は特別何かを追加削減しなくても実現できているはずなので、できていないのであれば希望に沿ってない現在の状況を教えてほしいのです。
（西瓜） 2022/11/01(火) 11:37:32

もしかして、20行目を別の行にコピーして
=A1+A10+A15が（たとえば21行目にコピーして）=A2+A11+A16になってしまう、ということでしょうか。
もしそうであれば、「=A$1+A$10+A$15」にすると、行の参照が絶対参照になるので大丈夫です。
事務員さんの考えと違っていたらすみません。

（下手の横好き） 2022/11/01(火) 17:30:04

下手の横好き様ありがとうございます

20行目をコピーはしてません。

西瓜様

新たにサンプル作って試してみましたが
ずれませんね！
症状がでるシートがなんか変という事でしょうね！？
ありがとうございました😊
（事務員） 2022/11/01(火) 20:07:44

 >新たにサンプル作って試してみましたが 
 >ずれませんね！
 ずれないそのシートが変なんじゃないですか。
 どのようにしたんですかね。
 その方法を伺いたいですね。
（???） 2022/11/01(火) 20:14:55

???様
私の勘違いでした、すみません。

追加で申し訳ないのですが、教えてください。
a1+a10+a15 の合計をa20に表示
c1+c10+c15 の合計をc20に表示する式が
入っています。この状態でランダムで2行挿入や
2行削除した時に例えば a3とc3のセルを
上記の式に追加する事って可能でしょうか？
宜しくお願いします

（事務員） 2022/11/02(水) 19:09:14

 とりあえずA列だけに限定して説明してください。

 下記のようにA20セルに式が入っているとして、
 ・例えば、どの行に2行挿入するんですか？
 ・そして、どこのセルの計算式が、どうなってほしいんですか？
 きちんと書いて貰わないと、相手に伝わりませんよ。

        A列
 1
 2
 3
 4
 5
 6
 7
 8
 9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20      =A1+A10+A15
 　
（γ） 2022/11/02(水) 22:55:17

γ様ありがとございます。
説明したつもりてますが、伝わらないなら
私の説明不足ですね。

2行の挿入はランダムですね！
3.4行だったり、11.12行だったりします。

例えば、3.4行に2行挿入した場合に
上記の式にa3を追加したいです。

宜しくお願いします。
（事務員） 2022/11/03(木) 11:02:32

元の式 =A1+A10+A15 に =A1+A3+A10+A15 とすればいいのでは。
（???） 2022/11/03(木) 11:21:16

???様

それはそうなんですけど！(⌒-⌒; )
自動で計算式に反映されたいです。

列も30列位ありますのでマクロ等で
実現可能ならと考えてます。

お願いします。
（事務員） 2022/11/03(木) 12:02:04

  ＞a1+a10+a15 の合計をa20に表示

  ＞ランダムで2行挿入や2行削除した時に 
  ＞例えば a3のセルを上記の式に追加する

  私はまだ分からないですねぇ・・  削除時は更に分からない。

  もう一度、数式の「セルアドレス」と「数式」および「値」の変化を説明してください。
  ※下に通常の例を書きますので、それを利用して、希望する結果図を書いてください。

  　<初期状態>　→　　　　<3、4行を挿入した場合>　　→　　<3、4行を削除した場合>>
  行 _A_                  行 _A_                          行 _A_ _
   1 201                   1 201                           1 201                        
   2   2                   2   2                           2   2                        
   3   3                   3                               3   5                        
   4   4                   4                               4   6                        
   5   5                   5   3                           5   7                        
   6   6                   6   4                           6   8                        
   7   7                   7   5                           7   9                        
   8   8                   8   6                           8 202                        
   9   9                   9   7                           9  11                        
  10 202                  10   8                          10  11                        
  11  11                  11   9                          11  11                        
  12  11                  12 202                          12  11                        
  13  11                  13  11                          13 204                        
  14  11                  14  11                          14  11                        
  15 204                  15  11                          15  11                        
  16  11                  16  11                          16  11                        
  17  11                  17 204                          17  11                        
  18  11                  18  11                          18 607                        
  19  11                  19  11                          　 ↑                         
  20 607                  20  11                          　  A18セル =A1+A8+A13 
   ↑                     21  11              
   A20セル =A1+A10+A15    22 607              
                          23 ↑               
                          24 A22セル =A1+A12+A17

（半平太） 2022/11/03(木) 12:15:29

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

行を2行追加した時のみを、お願いします
削除した場合は考えないでおきます。

 　<初期状態>　→　　　　<3、4行を挿入した場合>　　
  行 _A_                  行 _A_                         
   1 201                   1 201                                                
   2   2                   2   2                                                
   3   3                   3                                                     
   4   4                   4                                                  
   5   5                   5   3                                          
   6   6                   6   4                                              
   7   7                   7   5                                                 
   8   8                   8   6                                               
   9   9                   9   7                                               
  10 202                  10   8                                              
  11  11                  11   9                                                
  12  11                  12 202                                                
  13  11                  13  11                                              
  14  11                  14  11                                            
  15 204                  15  11                                              
  16  11                  16  11                                            
  17  11                  17 204                                               
  18  11                  18  11                                          
  19  11                  19  11                                                  
  20 607                  20  11                          　 
   ↑                     21  11              
   A20セル =A1+A1gyou     22 607              
                          23 ↑               
                          24 A22セル =A1+A12+A17

?@ 3,4行目に2行追加します。
?A A22セルにある計算式　=A1+A12+A17に
?B 自動でA3を追加し計算式を　=A1+A3+A12+A17にしたいです。
　　　　　　　　　　　　　　　　
宜しくお願いします。　

（事務員） 2022/11/03(木) 13:41:59

皆様
上記で説明したものをマクロの記録で試してみました。
その結果が以下です。
rangeが少し違うとおもいますが…。

Sub Macro2()
'
' Macro2 Macro
'

    Rows("3:4").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveWindow.SmallScroll Down:=15
    Range("A23").Select
    ActiveCell.FormulaR1C1 = "=R[-22]C+R[-20]C+R[-16]C+R[-11]C+R[-6]C"
    Range("A24").Select
    ActiveWindow.SmallScroll Down:=-21
End Sub

宜しくお願いします。
（事務員） 2022/11/03(木) 14:19:06

よくわかりませんがこういうことですか？
少なくとも行を挿入・削除したらa3を足すという動作にはなるとは思いますが

=a1+a10+a15+if(cell("address",a20)<>"$a$20",a3)
（傘） 2022/11/03(木) 15:04:57

傘様　ありがとうございます。

上記の式試してみました。挿入だけですが
確かに元々のa3を足す事は確認しました。
行の挿入後のa3は足されてはいませんでした。
う～ん！！

（事務員） 2022/11/03(木) 15:23:17

 だいぶクリアになりました。

 １．３行目に２行まとめて挿入したのに、何故A3だけが追加されるんですか？
 　　挿入した行が複数の場合、トップ行だけ数式に追加するってことなんですか？

 　　すると、一行ずつ２回に分けて挿入すれば、
 　　A22セル =A1+A3+A4+A12+A17　になるんでしょうか？

 ２．数式は、ホントにそんな簡単な数式なんでしょうか？
 　　そうじゃないなら、実際の数式をアップいただけませんか？

（半平太） 2022/11/03(木) 15:40:42

半平太様

１．３行目に２行まとめて挿入したのに、何故A3だけが追加されるんですか？

 　　挿入した行が複数の場合、トップ行だけ数式に追加するってことなんですか？

　→　はい！
　　
　　すると、一行ずつ２回に分けて挿入すれば、

 　　A22セル =A1+A3+A4+A12+A17　になるんでしょうか？

　→質問の意味がわかりません。一行ずつ２回に分けて挿入はないです。

    挿入の条件は2行が基本です。

２．数式は簡単足し算です。

　宜しくお願いします。
（事務員） 2022/11/03(木) 15:51:42

 前提
 １．初期状態において、A20セルに =A1+A10+A15　等と入力されている
 ２．A21セルに「新数式」と入れて置く
 ３．行削除は行わない

 当該シートの「シートモジュール(※)」に後記コードを貼付ける
 ※標準モジュールではない←重要

 挿入後
 (1) A22セル =A1+A12+A17+A3　’セルは順番にはなっておりません　あしからず　m(__)m
 (2) C22セル =C1+C12+C17+C3

 <初期状態>　　　　　　→　<３，４行目に行挿入>
 行  ___A___  _B_  _C_     行  ___A___  _B_  _C_
  1      201       301      1      201       301
  2        2         2      2        2         2
  3        3         3      3                   
  4        4         4      4                   
  5        5         5      5        3         3
  6        6         6      6        4         4
  7        7         7      7        5         5
  8        8         8      8        6         6
  9        9         9      9        7         7
 10      202       301     10        8         8
 11       11        11     11        9         9
 12       11        11     12      202       301
 13       11        11     13       11        11
 14       11        11     14       11        11
 15      204       304     15       11        11
 16       11        11     16       11        11
 17       11        11     17      204       304
 18       11        11     18       11        11
 19       11        11     19       11        11
 20      607       906     20       11        11
 21  新数式                21       11        11
                           22      607       906
                           23  新数式

 Private Sub Worksheet_Change(ByVal Target As Range)
     Dim Pos As Range, aCell As Range

     If Target.Rows(1).Cells.CountLarge = 16384 Then
         If Target(1, 1) <> Empty Then
             Exit Sub
         End If

         Set Pos = Columns("A").Find("新数式").Offset(-1)

         Application.EnableEvents = False

         For Each aCell In Range(Pos, Cells(Pos.Row, Columns.Count).End(xlToLeft))
             If aCell.HasFormula Then
                     aCell.FormulaLocal = aCell.FormulaLocal & "+" & Cells(Target(1, 1).Row, aCell.Column).Address(0, 0)
             End If
         Next aCell
     End If

     Application.EnableEvents = True
 End Sub

（半平太） 2022/11/03(木) 17:23:26

半平太様
　
　指定通り記載し、行を挿入したのですが
a3やc3の追加はありません。
何か？ちがいますか？
（事務員） 2022/11/03(木) 17:52:54

 ＞ 当該シートの「シートモジュール(※)」に後記コードを貼付ける
 ＞ ※標準モジュールではない←重要
 　↑
 これ、守っていますか？

（半平太） 2022/11/03(木) 18:09:43

半平太様
　
　　例えば、sheet1(sheet1)ですよね！？
（事務員） 2022/11/03(木) 18:14:09

 Sheet1の「シート見出し」を右クリック
 「コードの表示」をクリック

 VBE画面が出ますので、そこにコードを貼り付けてください。

（半平太） 2022/11/03(木) 18:24:02

半平太様

　コード貼り付けはあってます。
　貼り付け後の操作が間違ってますか？
　3,4行目を挿入すれば良いですよね！
　ちなみにex.は2019です。現在の環境はです。
（事務員） 2022/11/03(木) 18:33:17

半平太様
　
　ありがとうございました。
　sampleを作りなおしたらいけました。
　どこか？間違ったのか不明ですが。

　質問ですが、新数式と入れる意味を
　教えてください。
　本当にありがとうございます。
（事務員） 2022/11/03(木) 19:00:01

 ＞ 新数式と入れる意味

  ターゲットとなる数式がどのセルにあるか探る為の目印です。

  数式が1列に一つしかないなら、他にやりようもありますが、
  そう言う情報が不足しているので、この方式にしました。

（半平太） 2022/11/03(木) 19:07:32

半平太様

›ターゲットとなる数式

       =a+a10 ......の式ですかよね！

数式が確定したら、この新数式の行を削除しても問題ないでしょうか？

（事務員） 2022/11/03(木) 19:35:37

 ＞数式が確定したら、この新数式の行を 削除しても問題ないでしょうか？

 行挿入はもうしない、と言う状態になれば、問題ないです。

 その場合は、マクロも消去した方がいいと思いますが、
 そちらの事情が分からないので、当方はこれ以上深入りしません。

（半平太） 2022/11/03(木) 20:03:07

 「新数式」が無いときに行挿入するとトラブりますので、
  その時はスルーすべく、以下のコードに差し替えてください。

 Private Sub Worksheet_Change(ByVal Target As Range)
     Dim Pos As Range, aCell As Range, rNewFml As Range

     If Target.Rows(1).Cells.CountLarge = 16384 Then
         If Target(1, 1) <> Empty Then
             Exit Sub
         End If

         Set rNewFml = Columns("A").Find("新数式")

         If rNewFml Is Nothing Then
             Exit Sub
         End If

         Set Pos = rNewFml.Offset(-1)

         Application.EnableEvents = False

         For Each aCell In Range(Pos, Cells(Pos.Row, Columns.Count).End(xlToLeft))
             If aCell.HasFormula Then
                     aCell.FormulaLocal = aCell.FormulaLocal & "+" & Cells(Target(1, 1).Row, aCell.Column).Address(0, 0)
             End If
         Next aCell
     End If

     Application.EnableEvents = True
 End Sub

（半平太） 2022/11/03(木) 20:44:01

半平太様 A20セルに =A1+A10+A15

お世話になりました。
また教えてください。お手数おかけします。
上記のコードを応用して、A20セルに =A1+A10+A1の式と
A21セルに =A2+A11+A16の式がある場合

上記と同様に2行挿入した場合に、それぞれの計算式に同時に
ターゲットcellの追加はかのうでしょうか？
お願いします。

（事務員） 2023/01/08(日) 10:21:20

  そちらの状況がよく分かってないので、余り深入りしたくないのですが、、

  必ず２行挿入すると言うことでしたよね？

  入れる場所は、全くデタラメな位置ではないと思っているのですが、実際はどうなんでしょうか？
  例えば、必ず偶数行目から(または奇数行目から)挿入する、なんてルールがあるのでは？

  ＞上記のコードを応用して、
  ＞A20セルに =A1+A10+A1の式と
  ＞A21セルに =A2+A11+A16の式がある場合

  A21セルに関しては、単にA20の参照先のセルを一つ下にずらしたセルを足しに
  行く数式にすればいいことと解釈していいんでしょうか？

（半平太） 2023/01/08(日) 14:29:50

ありがとうございます。

そちらの状況がよく分かってないので、余り深入りしたくないのですが、、
→ 深入り願います。こちらのかってな言い分ですみません。

  必ず２行挿入すると言うことでしたよね？
  入れる場所は、全くデタラメな位置ではないと思っているのですが、実際はどうなんでしょうか？
  例えば、必ず偶数行目から(または奇数行目から)挿入する、なんてルールがあるのでは？
→ルールや規則性はないです。奇数だったり偶数だったりです。ブロックで
   まちまちです。
  ＞上記のコードを応用して、
  ＞A20セルに =A1+A10+A1の式と
  ＞A21セルに =A2+A11+A16の式がある場合
  A21セルに関しては、単にA20の参照先のセルを一つ下にずらしたセルを足しに
  行く数式にすればいいことと解釈していいんでしょうか？
→はい！

宜しくお願いします。
（事務員） 2023/01/08(日) 15:58:59

 目印の「新数式」は、A22セルに手入力して置くものとします。

 <初期状態>　　　　　　　　→　<例：10行目に2行挿入した後の結果図>
 行  ___A___  _B_  __C__       行  ___A___  _B_  __C__
  1      201         301        1      201         301
  2     5000        6000        2     5000        6000
  3        4           4        3        4           4
  4        5           5        4        5           5
  5        6           6        5        6           6
  6        7           7        6        7           7
  7        8           8        7        8           8
  8        9           9        8        9           9
  9      202         301        9      202         301
 10       11          11       10                     
 11       11          11       11                     
 12       11          11       12       11          11
 13       11          11       13       11          11
 14      204         304       14       11          11
 15       11          11       15       11          11
 16       11          11       16      204         304
 17       11          11       17       11          11
 18       11          11       18       11          11
 19        3           4       19       11          11
 20      223         323       20       11          11
 21     5022        6022       21        3           4
 22  新数式                    22      223         323
                               23     5022        6022
 　　　　　　　　　　　　　　　24  新数式

 初期状態の数式         　→　挿入後の数式
 (1) A20セル =A1+A10+A15　　　(1) A22セル =A1+A12+A17+A10
 (2) C20セル =C1+C10+C15      (2) C22セル =C1+C12+C17+C10
 (3) A21セル =A2+A11+A16      (3) A23セル =A2+A13+A18+A11
 (4) C21セル =C2+C11+C16      (4) C23セル =C2+C13+C18+C11

 ’全面差し替えのコード
 Private Sub Worksheet_Change(ByVal Target As Range)
     Dim Aim As Range
     Dim sAimAdrNew As String, sAimAdrPre As String, sTgtAdr As String
     Dim rFmlPos As Range, aCell As Range

     If Target.CountLarge <> 32768 Then
         Exit Sub
     Else
         Set Aim = Columns("A").Find("新数式")
         If Aim Is Nothing Then
             Exit Sub
         Else
             sTgtAdr = Target.Address
             sAimAdrNew = Aim.Address
             Application.EnableEvents = False
             Application.Undo '一旦、直前に戻る
             Set Aim = Columns("A").Find("新数式")
             sAimAdrPre = Aim.Address
             Application.Undo '現状復帰
             Application.EnableEvents = True

             If sAimAdrNew = sAimAdrPre Then
                 Exit Sub
             End If
         End If
     End If

     Application.EnableEvents = False

     Set rFmlPos = Range(sAimAdrNew).Offset(-2)

     For Each aCell In Range(rFmlPos, Cells(rFmlPos.Row, Columns.Count).End(xlToLeft))
         If aCell.HasFormula Then
             aCell.FormulaLocal = aCell.FormulaLocal & "+" & Cells(Range(sTgtAdr)(1, 1).Row, aCell.Column).Address(0, 0)
             aCell.Copy aCell.Offset(1)
         End If
     Next aCell

     Application.EnableEvents = True
 End Sub

（半平太） 2023/01/08(日) 16:36:33

半平太様

ありがとうございます。

思い通りの結果になりました。

（事務員） 2023/01/08(日) 19:56:44

コメント返信：

[ 一覧(最新更新順) ]

[[20221031202902]] 『行の追加や削除でも数式がずれない方法』（事務員） ページの最後に飛ぶ

[[20221031202902]]　『行の追加や削除でも数式がずれない方法』（事務員）　ページの最後に飛ぶ