ID登録せずに、無料で質問できる♪ 教えて!gooアプリ>>

エクセルで作った関数の相対参照から絶対参照に変換する方法について検索したところ、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複数のエクセル相対参照を絶対参照に一発変換したい。

一つの相対参照を例えば横方向にいくつか複写して、
複写された各セルの相対参照を、絶対参照化する時
各セルにポイントをおいて「F4」プッシュで処理
しています。これを範囲指定して一度に変換する方法があれば大変助かるのですが。
良い方法があったら教えて下さい。

Aベストアンサー

下記参考URLに相対参照を絶対参照にするマクロが書いてあります。
http://www2.odn.ne.jp/excel/waza/macro.html#SEC22

でもひょっとしたらこれだけでは使い方がわからないかもしれないので,
使い方例を1つだけ紹介しておきます。
わかっていたら以下は無視してください。


「ツール」→「マクロ」→「マクロ」から
マクロダイアログボックスを出し,
「マクロ名」の欄に「test」と記入→「作成」をクリック。
するとVisual Basicエディタが開き,すでにModule1に
------------------
Sub test()

End Sub
------------------

というものができていると思うので,そこを

-----------------
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
-----------------

にかきかえ,Visual Basicエディタえを閉じます。
再び「ツール」→「マクロ」→「マクロ」から
マクロダイアログボックスを出し,
「オプション」から,マクロオプションでショートカットキーを割り当てれば,
Ctrtキー+割り当てたキーでそのマクロが実行されます。

使い方は,
For Each c In Selectionですから,
取りあえず変換したいセルを全て選び,
Ctrtキー+割り当てたキーを押すというものです。

参考URL:http://www2.odn.ne.jp/excel/waza/macro.html#SEC22

下記参考URLに相対参照を絶対参照にするマクロが書いてあります。
http://www2.odn.ne.jp/excel/waza/macro.html#SEC22

でもひょっとしたらこれだけでは使い方がわからないかもしれないので,
使い方例を1つだけ紹介しておきます。
わかっていたら以下は無視してください。


「ツール」→「マクロ」→「マクロ」から
マクロダイアログボックスを出し,
「マクロ名」の欄に「test」と記入→「作成」をクリック。
するとVisual Basicエディタが開き,すでにModule1に
------------------
Sub test()...続きを読む

Q多数のセルに入力してある数式を$を使わずに絶対参照コピーする方法

 初めて質問いたします。
 技術系計算をエクセルで組み立てる際、多数のセルに入力されている相当長い数式等を$を使用することなくコピーする方法を教えてください。
 以前他のサイトで、単にコピーしたいセルを選びCtrl+C Ctrl+V 程度の操作で一括絶対参照コピーができる(コピーした先のセル内の数式にも$がつていなかったように記憶しています)を覚えたのですがその操作方法を忘れてしまいました。 
 多数のセルを対象にしていますのでF4 キーによる変換では相当な時間がかかるので投稿しました。
 宜しくお願いいたします。

Aベストアンサー

こんばんは。

#3 の回答者ですが、#2さんのテクニックが上手く行かないということで、あえてそう書いたまでなのです。

>以前他のサイトで、単にコピーしたいセルを選びCtrl+C Ctrl+V 程度の操作で一括絶対参照コピーができる(コピーした先のセル内の数式にも$がつていなかったように記憶しています)を覚えたのですがその操作方法を忘れてしまいました。 

私は、何年も掲示板を読んでいますから、ほとんどは見ているつもりではいるものの、この種のご質問は難しいですね。私の知らないものもあるのかもしれませんが、現実に困っていない状況から、はっきりしていない記憶の中から、他人の独特のテクニックを導きだすからです。

以下の方法も定番とはいえません。

一旦、文字化させておいて、貼り付け、それを「区切り位置」ボタンで、一気に数式化させるというテクニックかもしれません。

具体的には、数式を置換で、「=」 --> 「'=」 と全体を全て置換します。そして、その範囲をコピーして、目的の場所に貼り付け、「区切り位置」で、「完了」をクリックします。
コピー元も同じく、「区切り位置」で元に戻します。ただし、これは一列に限ります。

他にも同種の特殊なテクニックもありますし、概ね、マクロ化も可能ですが、こういうことは、めったに必要ありません。実は、左側の数式は、右の数式と等価ですから、数式はひとつだけでよいわけです。

 A1方式  R1C1方式
--------------------------
=B1+C1   =RC[1]+RC[2]
=B2+C2   =RC[1]+RC[2]
=B3+C3   =RC[1]+RC[2]

こんばんは。

#3 の回答者ですが、#2さんのテクニックが上手く行かないということで、あえてそう書いたまでなのです。

>以前他のサイトで、単にコピーしたいセルを選びCtrl+C Ctrl+V 程度の操作で一括絶対参照コピーができる(コピーした先のセル内の数式にも$がつていなかったように記憶しています)を覚えたのですがその操作方法を忘れてしまいました。 

私は、何年も掲示板を読んでいますから、ほとんどは見ているつもりではいるものの、この種のご質問は難しいですね。私の知らないものもあるのか...続きを読む

Qある範囲のセルから任意の値を検索して、その隣のセルの値を取得するという関数はありますか?

Excelの関数について質問します。
ある範囲のせるを検索して、その隣のセルの値を取得するという関数を探しています。
なければユーザー定義で作りたいと思っています。
VLOOKUP関数では一番左端が検索されますが、
それをある範囲まで拡張して、
その右隣の値を取得できるようにしたいのです。
どうかお知恵をお貸しください。

Aベストアンサー

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場合によっては、IFをかぶせてCOUNTIFで確認した方が良いかもしれません。
 ex. =IF(COUNTIF(A1:F200,X1)=1,【上記数式】,"えらー")

ちなみに、VBAでやるならこんな感じになるかと。

動作の概要
 【検査範囲】から【検査値】を探し、
 最初にHITしたセルについて、右隣のセルの値を返す。
 ex. =Sample(X1,A1:F200)

'--------------------------↓ココカラ↓--------------------------
Function Sample(ByVal 検査値 As Variant,ByVal 検査範囲 As Range)
 For Each セル In 検査範囲
  If セル = 検査値 Then Exit For
 Next セル
 Sample = セル.Offset(0, 1)
End Function
'--------------------------↑ココマデ↑--------------------------

いずれもExcel2003で動作確認済。
以上ご参考まで。

●X1セルの値を範囲A1:F200の中から探して、その右隣のセルの値を返す

 =OFFSET(A1,SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1))-1,SUMPRODUCT(COLUMN(A1:F200)*(A1:F200=X1)))

※最初のA1はワークシートの左上隅を示すものなので、検索範囲に関わらずA1固定
※SUMPRODUCT(ROW(A1:F200)*(A1:F200=X1)) ⇒ A1:F200で値がX1と一致するセルの行番号

>その「ある範囲」の中には検索したい値が入っているセルは1つしかありません。
というのが前提です。複数のセルがHITすると関係ないセルの値が返るので、
場...続きを読む

Qエクセルで条件に一致したセルの隣のセルを取得したい

下のような「得点」という名前のシートがあります。
(「田中」のセルがA1です。)

 [ 田中 ][ 10 ][ 200 ]
 [ 山田 ][ 21 ][ 150 ]
 [ 佐藤 ][ 76 ][ 250 ]
 [ 鈴木 ][ 53 ][ 350 ]

別のシートのA1セルに、「佐藤」と入力すると、

 [ 佐藤 ]

「得点」シートから「佐藤」の列を見つけて、B1、C1に

 [ 佐藤 ][ 76 ][ 250 ]

のように表示させたいのですが、B1、C1にはどのような式を書けば良いのでしょうか。
「得点」シートでは氏名が重複する事はありません。
IF文を使うと思うのですが、いまいち良く分かりませんでした。

よろしくおねがい致します。

Aベストアンサー

こんにちは!
VLOOKUP関数で対応できます。
IF関数と併用すればエラー処理が可能です。

Excel2007以降のバージョンであれば
B1セルに
=IFERROR(VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0),"")
としてC1セルまでオートフィルでコピー!
そのまま下へコピーすると行が2行目以降でも対応できます。

Excel2003までの場合は
=IF($A1="","",VLOOKUP($A1,得点!$A:$C,COLUMN(B1),0))

としてみてください、m(_ _)m

Qエクセル関数で「=A1」と「=$A$1」$がつくのとつかないのでは何が違うのでしょうか。

エクセル関数で「=A1」と「=$A$1」$がつくのとつかないのでは何が違うのでしょうか。ご存じのかたに教えていただきたいです。よろしくお願いいたします。

Aベストアンサー

「$」は絶対値、という意味です。
式をコピーしてもその値は変化しません。

固定したいセルの上で「F4」を押すと
「$A$1」→「A$1」→「$A1」→「A1」と変化します。

例えば「=$A$1+B1」という式があったとします。
その式をドラッグして行コピーしていくと、
次の行の式は「=$A$1+B2」次は「=$A$1+B3」になりますからA1の数値は絶対値として変化しません。
絶対値が付いていない場合は、「=A2+B2」「=A3+B3」になっていきます。

何か上手く説明出来ない・・・
すみません。

Qエクセルで、条件に一致した行を別のセルに抜き出す方法

エクセルで、指定した条件に一致するセルを含む行をすべて抜き出す方法が知りたいです。

たとえば、

<A列> <B列> <C列>
7/1 りんご 100円
7/2 ぶどう 200円
7/2 すいか 300円
7/3 みかん 100円

このような表があって、100円を含む行をそのままの形で、
別のセル(同じシート内)に抜き出したいのですが。

7/1 りんご 100円
7/3 みかん 100円

抽出するだけならオートフィルターでもできますが、
抽出結果を自動的に、別の場所に、常に表示させておきたいのです。

初歩的な質問だと思いますが、検索しても分からなかったので、よろしくお願いします。

Aベストアンサー

同じ質問が結構よく出てますが、そんなに初歩的でもありません
別シートのA1セルに「100円」と入力し、そのシートの任意のセルに以下の式を貼り付けて下さい。後は、下方向、右方向にコピー。
日付のセル書式は「日付」形式に再設定してください

=IF(COUNTIF(Sheet1!$C:$C,$A$1)>=ROW(A1),INDEX(Sheet1!A:A,LARGE(INDEX((Sheet1!$C$1:$C$500=$A$1)*ROW(Sheet1!$C$1:$C$500),),COUNTIF(Sheet1!$C:$C,$A$1)-ROW(A1)+1)),"")

データ範囲は500行までとしていますが、必要に応じて変更して下さい

Qエクセル 0や空白のセルをグラフに反映させない方法

以下の点でどなたかお教えください。

H18.1~H20.12までの毎月の売上高を表に記載し、その表を元にグラフを作成しています。グラフに反映させる表の範囲はH18.1~H20.12の全てです。
そのためまだ経過していない期間のセルが空白になり、そこがグラフに反映され見づらくなります。
データを入力する都度グラフの範囲を変更すればいいのですが、うまく算式や設定等で空白や0円となっているセルをグラフに反映させない方法はありますか?

お手数ですが、よろしくお願いいたします。

Aベストアンサー

売上高のセルは数式で求められているのですよね?
それなら
=IF(現在の数式=0,NA(),現在の数式)
としてみてください。
つまり、0の場合はN/Aエラーにしてしまうんです。N/Aエラーはグラフに反映されません。

QRange("A1")⇔cells(1,1)の変換。

EXCELのマクロについて質問します。
Range("A1")⇒(1,1)
cells(1,1)⇒("A1")
に一発変換する方法を教えて下さい。
時間があるかた宜しくお願いします。

Aベストアンサー

こんなのでよろしいでしょうか?
range("A1") → Range("A1").Row & "," & Range("A1").Column
cells(1,1) → Cells(1, 1).Address(False, False)
()や""は付けていません

Q別のシートから値を取得するとき

Worksheets("シート名").Activate
上記のを行ってから別シートの値を取得するのですが、
この処理を行うと指定したシートへ強制的にとんでしまいます。。。

※イメージ
For ~ To ~
  Worksheets("シートA").Activate
  シートAの値取得
       :
  Worksheets("シートB").Activate
  シートBの値取得
Next

このイメージ処理を行うとものすごい勢いで画面がチカチカします。。。
シートを変えずに他のシートから値を取得する方法はないのでしょうか。
教えてください!

Aベストアンサー

Worksheets("シートA").Range("A1")

みたいな感じでできませんか?

Qエクセルで空白セル”” と未入力セルの違い

関数で セルA1 に=if(B1=0,"","入力済")
としました。
セルの選択を素早くするためにショートカットキー「Ctrl+↓」を
利用しています。
通常の未入力空白セルは上記のショートカットで飛ばされて選択
されません。
これに対して上記関数で ""  として入力されたものを
コピーして値だけを貼り付けたとしても
上記ショートカットキーで飛ばされず選択されてしまいます。
見た目は 全く同じ 空白セルです。(関数も値貼り付けをしているので消えています。)
未入力の空白セルと 関数で""と入力されて値貼り付けを受けたセルではどこが違うのでしょうか。
また、未入力の空白セルに 戻すには関数でどのように入力させればいいのでしょうか。もちろんDeleteKeyを使って未入力の空白セルに
戻るのですがそれではショートカットキーでとばせるメリットがなくなります。関数で大量に処理したいのです。
おわかりの方お教えください。

Aベストアンサー

>未入力の空白セルと 関数で""と入力されて値貼り付けを受けたセルではどこが違うのでしょうか。

未入力の空白セルは「ISBLANK関数」で「真」を返します。

一方、値としての""が貼り付けられたセルは「ISBLANK関数」で「偽」を返します。

>また、未入力の空白セルに 戻すには関数でどのように入力させればいいのでしょうか。
不可能です。「式」は「値」を返すので「空白セル」と言う「状態」を返す事は出来ません。

>もちろんDeleteKeyを使って未入力の空白セルに
>戻るのですがそれではショートカットキーでとばせるメリットがなくなります。関数で大量に処理したいのです。

つまり「セルを1つづつ選択してDeleteキーで1つづつ空白セルに戻すのが面倒」なので「全部を一気に空白セルに戻す良い方法は無いか?」と言う事ですね?

だったら簡単です。

すべての式を
=if(B1=0,"","入力済")
ではなく
=if(B1=0,1/0,"入力済")
にしておきましょう。

すると、未入力の所は、空白("")にならず「#DIV/0!」になります。

そう表示されたら、シート全体を選択し「CTRL+G」を押して「ジャンプ」を出します。

「ジャンプ」のダイアログが出たら「セル選択」を押し、セル選択のダイアログを出します。

セル選択のダイアログが出たら「数式」を選び、その下のチェックは「エラー値」だけにします。

「OK」を押してダイアログを閉じると「#DIV/0!」の表示になったセルのみが選択状態になります。

「#DIV/0!」の表示になったセルのみが選択状態になったら、Deleteキーを押して、それらのセルを一気に「空白セル」にして下さい。

もちろん、大量にあっても、手順は変わりません。

>未入力の空白セルと 関数で""と入力されて値貼り付けを受けたセルではどこが違うのでしょうか。

未入力の空白セルは「ISBLANK関数」で「真」を返します。

一方、値としての""が貼り付けられたセルは「ISBLANK関数」で「偽」を返します。

>また、未入力の空白セルに 戻すには関数でどのように入力させればいいのでしょうか。
不可能です。「式」は「値」を返すので「空白セル」と言う「状態」を返す事は出来ません。

>もちろんDeleteKeyを使って未入力の空白セルに
>戻るのですがそれではショ...続きを読む


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

人気Q&Aランキング