エクセルで作った関数の相対参照から絶対参照に変換する方法について検索したところ、OKWeb内で以前回答があり、それを参考に実行したところ、ほとんどの相対参照が絶対参照に変換されたのですが、一部で「#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))

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

よろしくお願いします。

このQ&Aに関連する最新のQ&A

A 回答 (5件)

#3です。

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

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
    • good
    • 0
この回答へのお礼

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

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

お礼日時:2005/04/19 01:05

こんにちは。

maruru01です。

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

お示しのマクロだと、一定以上の長さの関数の場合、うまく変換できないようです。


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
    • good
    • 0

>OKWeb内で以前回答があり、それを参考に実行したところ、ほとんどの相対参照が絶対参照に変換されたのですが、一部で「#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!」になってしまうのです。

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

補足日時:2005/04/17 23:03
    • good
    • 0

絶対参照から相対参照にするのは、セル参照から単に$を外すだけですよね。


しかし、コピーをしたときに正しく値を参照できてないのではないでしょうか?
ようするに取っては行けない$もあるのかなと推測します。
$は列と行で別々に付けられますので、右にコピーをするときは、参照が左の列にあるときは列には$が必要です。
例:「$A1」

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

このQ&Aに関連する人気のQ&A

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

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Q千葉銀行グループまたは系列のネット銀行

千葉銀行のグループまたは系列のネット銀行はどこがありますか?

Aベストアンサー

千葉銀行グループに証券会社はありますが系列銀行というのはないと思います。
ネットバンキングサービスがあります。

=>http://www.chibabank.co.jp/myaccess/internet/index.html

Qエクセルで同じA列の中に、1,2,3,4,5,6,2,3,4,2,1,3,2,2,2,3,1という

エクセルで同じA列の中に、1,2,3,4,5,6,2,3,4,2,1,3,2,2,2,3,1,3,1というような順番で数字が並んでいます。

その数字の中で、1→3と3→1になるものがいくつあるか求めたいのですが、出す関数・マクロがわかりません。
教えていただけないでしょうか?

よろしくお願いします。

Aベストアンサー

こんばんは!

>A列の中に
とは行方向に数値が入っている!というコトでしょうか?

そうであれば
表示したいセルに
=COUNTIFS(A1:A1000,1,A2:A1001,3)+COUNTIFS(A1:A1000,3,A2:A1001,1)
としてみてください。

「1」の次の行が「3」の場合と
「3」の次の行が「1」の場合をプラスしています。m(_ _)m

Q千葉銀行のカードでのセブン銀行手数料

セブンイレブンにあるセブン銀行のATMは千葉銀行のカードも使えるようですが、引き出し手数料はかかるのでしょうか?

Aベストアンサー

質問者が、スーパーカード使用者やポイントサービス
「ひまわり宣言」で120点以上じゃなければ、手数料が
課金されるようです。

スーパーカードや「ひまわり宣言」って何?
ってな質問は千葉銀行のサイトをご覧下さい。

Q=IF((L75>1.9)*AND(L75<=2),1,0)の*はないといけないのでしょうか?

L75が1.91のとき、L75が1.9以上かつ2以下のとき、真なら1を、偽なら0を出す式を書きました。
=IF((L75>1.9)AND(L75<=2),1,0)
こう入力するとエラーが出て*をつけるよう聞いてきます。
すなわち、エクセルの修正後は
=IF((L75>1.9)*AND(L75<=2),1,0) 結果は1(真)になりました。
というものです。
ところで、*は必要ですか?ないといけないのでしょうか?
そして、*があっても同じ結果となるのでしょうか?
*は掛け算しかわからず、よろしくお願いします。

Aベストアンサー

#02です。#01さんの補足に対する回答です
COUNTIF関数には検索条件をAND( )でつなげて記述することはできません

複合条件で件数を数えたいなら
 =SUMPRODUCT((L76:L85>0.9)*(L76:L85<=1)*1)
のような関数になります。(本当は配列の積を求める関数ですが、個数のカウントにも使えます)

Q千葉銀行・オーバーアロットメントによる売り出し

標記の案内が証券会社から寄せられました。「発行売り出し価格」は未定との事です。千葉銀行の株価は現在900円後半を上下していますが、この場合、旧来の千葉銀行とは別の株式会社の(人気のある新規発行株)と理解して良いのですか。初心者のため、証券会社からの勧誘をどのように受け止めるべきか判断しかねております。アドバイスをお願いします。

Aベストアンサー

ちなみに計算上で言えば、
総株数が増えるので株価は下落要因になります。

ただし、ディスカウント(数%)と手数料無料があるので、そのかねあいで微妙と言うことでしょう。

私だったら、一応買っておくかな?

QEXCEL2000 SUMIF(範囲,"TODAY()",合計範囲) だけでは出来ないので

本日契約成立の株の売買が1個の場合→売買手数料3150円です。
複数売買の場合→売買手数料は3150円を分割します。(機械的にセルの数だけ均等分割可。1日が終了し金額確定してからその金額で計算式を上書き訂正します。別案として契約金額に応じて按分出来ればなおうれしいです)
ただし手数料は1日の契約合計300万円ごとに3150円となる。300万=3150 301万=6300円 601万=9450円
帳簿の現状
セルB買い年月日 C買い数量 D買い単価 E支払金額手数料含む F品名(銘柄) G売り年月日 H売り数量 I売り単価 J売上金額手数料含む K売買損益

過去から未来に渡る売買帳です。Eの支払金額欄とJの売上金額欄に前もって数式を入れて置きたいのです。

Aベストアンサー

約定金額を300万以下、600万以下、900万以下、900万を超えるにしました。

当日以外の日は、スペースにしました。

それとこの式は、ファイルが重くなる見本のような式なので(電卓でこの式どおり計算するとわかります)、変えました。

E2に・・・
=IF(TODAY()<>B2,"",IF($B$1=0,,IF($B$1<=3000000,C2*D2*3150/$B$1,IF($B$1<=6000000,C2*D2*6300/$B$1,IF($B$1<=9000000,C2*D2*9450/$B$1,C2*D2*12600/$B$1)))))

L2に・・・
=IF(TODAY()<>I2,"",IF($B$1=0,,IF($B$1<=3000000,J2*K2*3150/$B$1,IF($B$1<=6000000,J2*K2*6300/$B$1,IF($B$1<=9000000,J2*K2*9450/$B$1,J2*K2*12600/$B$1)))))

B1に=SUM(A:A)・・・(B1はA列以外だったらあいてるセルならどこでもいい)C1だったら、上の式は$C$1にしてください。

約定金額を300万以下、600万以下、900万以下、900万を超えるにしました。

当日以外の日は、スペースにしました。

それとこの式は、ファイルが重くなる見本のような式なので(電卓でこの式どおり計算するとわかります)、変えました。

E2に・・・
=IF(TODAY()<>B2,"",IF($B$1=0,,IF($B$1<=3000000,C2*D2*3150/$B$1,IF($B$1<=6000000,C2*D2*6300/$B$1,IF($B$1<=9000000,C2*D2*9450/$B$1,C2*D2*12600/$B$1)))))

L2に・・・
=IF(TODAY()<>I2,"",IF($B$1=0,,IF($B$1<=3000000,J2*K2*3150/$B$...続きを読む

Q千葉銀行事件(昔の話)

かなり昔の事件ですが、千葉銀行事件というのがありました。頭取(古荘四郎彦氏)による乱脈融資と本に書いていたとおもいますが、頭取が辞めたあと、どんな経営者が頭取になったのですか?千葉銀行は、旧川崎財閥で、古荘氏も川崎系の人間と本に書いてありましたが、後任の頭取も川崎系の方だったのでしょうか?

Aベストアンサー

古荘氏の後任は大久保太三郎氏。
日本銀行の監事からのいわゆる天下りです。
副頭取も日銀人事部長だった岩城長保氏。
このあと4代約30年日銀出身の頭取を頂くことになります。

Aベストアンサー

左側に1列挿入して、その列に「1,2,3,4,5,1,2,3・・・」と縦に数字を入れます。
縦計を取りたいセルで、
=SUMIF(A1:B20,1,B1:D20) 
のように入力すると、B1,B6,B11,B16のセル計が取れます。
同様に検索条件の「1」を「2」に変えると、
B2,B7,B12,B17のセル計が取れます。

この関数の意味は
=SUMIF(範囲、検索条件、合計範囲)です。

Q千葉銀行は

千葉銀行は給料振込に指定すると通帳に給料と記載されるでしょうか?

Aベストアンサー

「○○シヨウジ (カ キュウヨ」と言ったような感じです。

Qエクセル2000にてSUM,SUMIF,IF関数のどれかを用いた集計方法

エクセル2000にてA1 からA10列には1000円から2000円までの金額、B1からB10列にはA列の金額に応じた手数料が入力されているとします。関数を使用し金額が1200円から1700円における手数料の合計を求めるにはどのように入力したらよいでしょうか?SUM関数、SUMIF関数、IF関数のいずれかを使用した場合を教えてください。

Aベストアンサー

(テストデータ)
A1:B10
12001
10002
13003
10004
13005
17006
18007
19008
20009
150010
(関数式)
C1に出すとして
C1に
=SUMIF(A1:A10,"<=1700",B1:B10)-SUMIF(A1:A10,"<1200",B1:B10)
(結果)25


このQ&Aを見た人がよく見るQ&A

人気Q&Aランキング