相対参照から絶対参照に変換する方法

質問

エクセルで作った関数の相対参照から絶対参照に変換する方法について検索したところ、ＯＫＷｅｂ内で以前回答があり、それを参考に実行したところ、ほとんどの相対参照が絶対参照に変換されたのですが、一部で「#VALUE!」となってしまいます。なぜでしょうか。
ちなみに、その関数というのが以下の通りです。

=IF(SUMIF(L108,"",L109)+SUMIF(L127,"",L128)+SUMIF(L155,"",L156)+SUMIF(L181,"",L182)+SUMIF(L238,"",L239)+SUM(L238,L181,L155,L127,L108)=L90,"",SUMIF(L108,"",L109)+SUMIF(L127,"",L128)+SUMIF(L155,"",L156)+SUMIF(L181,"",L182)+SUMIF(L238,"",L239)+SUM(L238,L181,L155,L127,L108))

力業で作った関数なので、少し長くなってスミマセン。

よろしくお願いします。

回答No.4

noname#29107 · ベストアンサー

＃３です。ロジック自体は一緒ですが、よけいなオブジェクト参照をしないように変更しました。少しは速くなっていると思います。

Sub test()
Dim c As Range, myrng As Range, s1, s2, fml, cnvfml
For Each c In Selection
If c.HasFormula Then
　　cnvfml = Application.ConvertFormula(Formula:=c.Formula, _
　　　　FromReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)
　　If Not IsError(cnvfml) Then
　　　　c.Formula = cnvfml
　　Else
　　　　fml = c.Formula
　　　　For Each myrg In c.Precedents
　　　　　　s1 = myrg.Address(ReferenceStyle:=xlA1, _
　　　　　　rowabsolute:=False, columnabsolute:=False)
　　　　　　s2 = myrg.Address(ReferenceStyle:=xlA1)
　　　　　　fml = Replace(fml, s1, s2)
　　　　Next
　　　　c.Formula = fml
　　End If
End If
Next

End Sub

お礼

ありがとうございました。
非常に感動しております。
問題は全てクリアーされました。
これで仕事がはかどります。

本当にありがとうございました。

回答No.5

maruru01

こんにちは。maruru01です。

質問欄の数式のような一定の条件の場合のみの方法ですが。
数式の入ったセルに対して、メニューの[編集]→[置換]で、
「L」→「$L$」
で置換してはどうでしょうか。
L列以外がなく、数式で使用している関数名にも"L"が入っていないので、出来ると思います。

回答No.3

noname#29107

お示しのマクロだと、一定以上の長さの関数の場合、うまく変換できないようです。
ConvertFormula自体の制限と思われますので、回避するためには、別のロジックを追加する必要があります。
とりあえず、作ってみましたのでおためし下さい。
（長い関数がたくさんあると変換に少々時間がかかります）

Sub test()
Dim c As Range, myrng As Range
For Each c In Selection
If c.HasFormula Then
　　If Not IsError(Application.ConvertFormula(Formula:=c.Formula, _
　　　　FromReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)) Then
　　　　c.Formula = Application.ConvertFormula(Formula:=c.Formula, _
　　　　　　FromReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)
　　Else
　　　　For Each myrg In c.Precedents
　　　　　　s1 = myrg.Address(ReferenceStyle:=xlA1, _
　　　　　　rowabsolute:=False, columnabsolute:=False)
　　　　　　s2 = myrg.Address(ReferenceStyle:=xlA1)
　　　　　　c.Formula = Replace(c.Formula, s1, s2)
　　　　Next
　　End If
End If
Next

End Sub

回答No.2

macchan1

>ＯＫＷｅｂ内で以前回答があり、それを参考に実行したところ、ほとんどの相対参照が絶対参照に変換されたのですが、一部で「#VALUE!」となってしまいます。

具体的にどのような方法で絶対参照に変更したのでしょうか？
多分F4キーまたは置換操作で絶対参照にしたと思われますが、「#VALUE!」というエラーが出るのは数式内で文字列などが入ったセルを参照しているものと思われます。

この原因となっている数式を見つけるには、数式バー内の、たとえば「SUMIF(L108,"",L109)」の部分をカーソルで選択してF9キーを押して数値が返ってくるか調べてみてください。
数値が返っていた場合はその部分の数式には問題は無いのでEscキーを押して、同様に次の部分の数式を選択してF9キーで「#VALUE!」というエラーがでる数式を調べていきます。

補足

以下の問題に対する回答で、一部解決しました。

【問題】

　数式を絶対参照に一括で変換をする方法はあるでしょうか？
　例えば =A1+B1 といった数式を =$A$1+$B$1 へとしたいのですが、一箇所のセルだけではなく、複数のセルの数式の絶対参照への変換を一括で行う方法はないでしょうか？

【回答】

Sub test()
Dim c As Range

For Each c In Selection
If c.HasFormula Then
c.Formula = Application.ConvertFormula(Formula:=c.Formula, _
FromReferenceStyle:=xlA1, ToAbsolute:=xlAbsolute)
End If
Next

End Sub

【解説】

　ToAbsolute:=　のところで、変換後の参照の種類を指定します。

xlAbsolute　　　　 行列とも絶対参照に
xlAbsRowRelColumn　行だけ絶対参照に
xlRelRowAbsColumn　列だけ絶対参照に
xlRelative　　　　 行列とも相対参照に

というように、マクロを組み解決しました。
だいたいおっしゃっていることはわかったのですが、
「#VALUE!」とでるのは、数式内にでるのではなく、
カーソル全てが「#VALUE!」になってしまうのです。

原因がよくわからないのです。
しかし、おっしゃるように文字列がないかは確認したいと思います。

回答No.1

hyde19

絶対参照から相対参照にするのは、セル参照から単に＄を外すだけですよね。
しかし、コピーをしたときに正しく値を参照できてないのではないでしょうか？
ようするに取っては行けない＄もあるのかなと推測します。
＄は列と行で別々に付けられますので、右にコピーをするときは、参照が左の列にあるときは列には＄が必要です。
例：「＄Ａ１」

まずは落ち着いて、長い数式を幾つかに分けてはいかがでしょうか。
そうすればどこに問題があるか分かってきます。