マンガでよめる痔のこと・薬のこと

すみませんがご教授お願いします。
エクセル(Excel2016)で一覧表で計算式などを使用(SUMなどで1セルずつ設定した計算を用いてます)を作成し、
あとから項目を1行追加(行にコピーしたセルの挿入)などをすると計算式の指定したセルが1行ずれてしまいます。
一覧表が膨大な大きさで手入力で修正するのには膨大な時間がかかってしまい手を焼いています。
修正いらずで上手に項目を追加する方法などないでしょうか。
すみませんがよろしくお願い致します。

A 回答 (2件)

> ①エクセル(Excel2016)で一覧表で計算式などを使用(SUMなどで1セルずつ設定した計算を用いてます)を作成し、②あとから項目を1行追加(行にコピーしたセルの挿入)などをすると、③計算式の指定したセルが1行ずれてしまいます。



Excel2016でなくとも、そうしたことで違いは生じないと思います。

① SUMなどで1セルずつ設定した計算というのは、①-1の形ですか、①-2のように[:]を含んだ形ですか。1つずつセルを指定して計算式を作っていれば、②のように行や列、セルを挿入しても、計算式の中の元の対象セルを追っかけて行き、問題は起きないと思います。①-2のように[:]を含んだ形だと、②のように行や列、セルを挿入した場合、その挿入した位置によっては、③計算式が指定していた参照先がおかしくなってしまう可能性は高いです。
①-1 =SUM(B4,C6,D7,E8,F8,F9,F10)*15
①-2 =SUM(B4,C6,D7,E8:F10)*15

例えば、A4のセルに「=SUM(B8:F15)」と計算式を入れてあって、あとから、E6のセルでセル挿入(下方向にシフト)とやると、A4のセルの計算式は「=SUM(B8:F15)」のままですから、計算対象の参照先指定が、元の参照先とは違うのを示していることになってしまいます。

もしも計算式の中で、SUMの中の参照先を「:」で指定しているのであれば、そうした参照をやめる(連続範囲参照を個別セル参照に変える)のが手っ取り早い対応だと思います。

どうしても手書き書き直しは大変すぎるというのであれば、次の試作のマクロを動かしてみてください。
失敗するとヤバいので、試しに、元のシートを別のシートにコピーして、そこで試してください。

Sub お試し()
Set hanni = Range("A1:R900")
'上で指定した範囲のセルにある数式?で「SUM(」の中の
' 「B5:E8」のような部分を、各セル番地に書き直す
For Each セル In hanni
kf = セル.Formula
www = セル.Address
If kf <> "" Then
If InStr(kf, "SUM") > 0 Then
lkf = InStr(kf, "(") + 1: rkf = InStr(lkf, kf, ")") - lkf
skf = Mid(kf, lkf, rkf): kkk = Split(skf, ","): uskf = UBound(kkk)
zentai = ""
For i = 0 To uskf
If InStr(kkk(i), ":") > 0 Then
Set dd = Range(kkk(i))
rrr = ""
For Each rr In dd
rrr = rrr & rr.Address(0, 0) & ","
Next
kkk(i) = Left(rrr, Len(rrr) - 1)
End If
zentai = zentai & kkk(i) & ","
Next
zentai = Left(zentai, Len(zentai) - 1)
セル.Formula = Replace(kf, skf, zentai)
End If
End If
Next
End Sub
    • good
    • 1
この回答へのお礼

ご回答ありがとうございます。計算式まで記載していただき感謝です。
なるほど大変参考になりました。
あらためて感謝致します。

お礼日時:2018/04/04 11:09

それでどうして問題になるのかがピンときませんが…もちろんずれるのは問題ですけど、そういうことではなくて、行や列を挿入すれば式の内容も勝手にそれに追従するはずだからです。



例えばセルA11に =SUM(A2:A10) という式があったとして、A列に挿入をかけるとこの式はセルB11に移動しますが、それと同時に自動的に =SUM(B2:B10) に変わるはずです。

ただしセル参照に$を付けて絶対参照にしているとずれないので、その場合は$を付けるのをやめるだけで解決しますが、そういう話ではないんですか?
    • good
    • 1

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!


人気Q&Aランキング

おすすめ情報