【Excel】計算式の一部を他のセルに反映させたい

質問

2007利用です。

減算の式は「=100-50」と入力しますが、たとえばA1セル上でこの式を入力し、マイナスした数値50を別のセル（B1）に反映させる方法を教えてください。

回答No.10

kkkkkm · ベストアンサー

No9の訂正です。

エラーの表示はなくした方がよかったですね。
=IFERROR(VALUE(RIGHT(FORMULATEXT(A1),LEN(FORMULATEXT(A1))-FIND("-",FORMULATEXT(A1),1))),"")

式が2013用でした以下の式で

=IFERROR(VALUE(RIGHT(FM_TEXT(A1),LEN(FM_TEXT(A1))-FIND("-",FM_TEXT(A1),1))),"")

回答No.11

kkkkkm

> うーん、エラー表示をなくす式、どうも結果が反映されないです。

A1に
=200-30
として
B1に
=IFERROR(VALUE(RIGHT(FM_TEXT(A1),LEN(FM_TEXT(A1))-FIND("-",FM_TEXT(A1),1))),"")
とした場合
A1には170が、B1に30が表示され、そこでA1の式を削除してしまうとB1には何も表示されないという動きになりませんか?

また、A1に
＝C2-D2
として
B1に
=IFERROR(VALUE(INDIRECT(RIGHT(FM_TEXT(A1),LEN(FM_TEXT(A1))-FIND("-",FM_TEXT(A1),1)))),"")

としていた場合
A1にエラーが表示されているのでしたら、それはA1の式にエラー処理の関数を入れる必要があります。
たとえば
=IFERROR(C2-D2,"")
その場合B1の式も変更しないとB1には何も表示されないままになります。

なお、起動時にマクロは有効にしてください
http://www.eurus.dti.ne.jp/~yoneyama/Excel2007/FAQ/macro.html

相対参照に関しては以下のページを参考にしてください。
http://www11.plala.or.jp/koma_Excel/contents1/mame1006/mame100601.html

なお、A1って絶対値だから絶対参照じゃないのって感じた場合、式の表示をR1C1表示にすると相対、絶対参照の意味がわかります。
変更方法
http://www.relief.jp/itnote/archives/003012.php

たとえば
B3に相対参照
=A1
B4の絶対参照
=$A$1
と入力して表示を変更すると
それぞれ
B3は=R[-2]C[-1]
B4は=R1C1
と表示が変わります。Rは行Cは列を示しそのあとの数値が[]で囲まれていれば相対値を、囲まれていなければ絶対値を示します。Rのマイナスは上方向、Cのマイナスは左方向を示します。

B3の式はA1を参照するのにB3から2行上で1列左のセルという相対的な参照になっています。たいして、B4はA1を参照するのにB4からの位置ではなく表全体の1行目の1列目という絶対的な参照となっています。

また、相対参照と関数式とは比較対象になるものではありませんので「それに対して関数式」という考え方はありませんが、=100-50がただの式で=IFERROR(VALUE…のような式を関数式と考えてもいいですが、あまりセルの式で関数式という言葉は聞かないような感じもします。関数式もただの式もどちらも単に計算式(式)と言われます。

>　セルが変わったときにこのA1はどう変えればよいのでしょうか？（つまりこの例でいうとC2とD2いずれをA1に変えるのか）

そこは＝C2-D2が入力されているセルに変更します。A2に＝C2-D2と書かれているのでしたらA1をA2に変更します。

お礼

> うーん、エラー表示をなくす式、どうも結果が反映されないです。

の件は回答10の訂正を試してみた結果、正常にできました。

参考になりました。ありがとうございました。

回答No.9

kkkkkm

> A列に数式を入れないと、B列が♯VALUEとなってしまいますがVALUEとは「値」という意味ですし、それは仕方ないですよね？

エラーの表示はなくした方がよかったですね。
=IFERROR(VALUE(RIGHT(FORMULATEXT(A1),LEN(FORMULATEXT(A1))-FIND("-",FORMULATEXT(A1),1))),"")

> たとえば「＝C2-D2」などのなんとか参照？のような単純な式の場合、数式を入れないかぎりは0が表示されるままです

C2とD2にデータがない場合ということでしょうか?
上記の意味でしたら、式の中で指定したセルにデータがない場合0と認識されるので0-0になって0が表示されます。

なお、＝C2-D2のD2の値を求めたい場合
単純に=D2で求められますが、D2が一定でなく規則性がない場合には
=IFERROR(VALUE(INDIRECT(RIGHT(FM_TEXT(A1),LEN(FM_TEXT(A1))-FIND("-",FM_TEXT(A1),1)))),"")
で求められます。

補足

うーん、エラー表示をなくす式、どうも結果が反映されないです。
前回回答いただいたマクロの式は直さなくてもよいのでしょうか？

＞ C2とD2にデータがない場合ということでしょうか?

はい。

＝C2-D2のD2の値を求めたい場合

説明が足りませんでしたが、C2からD2を計算した数をE2に反映させる場合はE2に「＝」と入力し、C2とD2のそれぞれのセルを選択するとE2に、自動で式が入ります。
これが相対参照というのでしょうか？
それに対して=IFERROR(VALUE…のような式は関数式というのですか？

=IFERROR(VALUE(INDIRECT(RIGHT(FM_TEXT(A1),LEN(FM_TEXT(A1))-FIND("-",FM_TEXT(A1),1)))),"")

という場合でも、セルが変わったときにこのA1はどう変えればよいのでしょうか？（つまりこの例でいうとC2とD2いずれをA1に変えるのか）

この式も試してみたのですが、どうも結果がでません…

回答No.8

kkkkkm

> A1に「＝100-50」と入力しまして、B1にこの数式を入力しましたが「＃NAME？」とだけでてしまします…

2007でしたね、忘れてました。
FORMULATEXTは2013で追加された関数でした。申し訳ありません。

自作関数をマクロで作成して対応してください。
ALT+F11で出てくる画面でVBAProject(Book1)のBook1の部分が自分のブック名であることを確認して、そこで右クリックして添付画像のようにメニュを出し「標準モジュール」を作成してください。出てきた右側の白い画面に

Function FM_TEXT(arg As Range)
FM_TEXT = arg.Formula
End Function

上記の部分をコピー貼り付けして

B1に
=VALUE(RIGHT(FM_TEXT(A1),LEN(FM_TEXT(A1))-FIND("-",FM_TEXT(A1),1)))

お礼

できました！ありがとうございます！

A列に数式を入れないと、B列が♯VALUEとなってしまいますがVALUEとは「値」という意味ですし、それは仕方ないですよね？

たとえば「＝C2-D2」などのなんとか参照？のような単純な式の場合、数式を入れないかぎりは0が表示されるままですがそういう関数式の場合は違うのですか？

回答No.7

kkkkkm

> A2に値引き（50）の部分を反映させたいのであれば、この式はA1に貼るのでしょうか？それともB2？

式はその式の結果を表示したいセルに書くもの(他のセルに式の結果を表示させることはできません)ですので、結果を表示したいセルがA2ならA2に記載してください。質問ではB1に反映と書かれてますが、B1ならB1に記載してください。B2が何故候補に出てきたのか不明ですが・・・。

補足

B2改めB1です。
A1に「＝100-50」と入力しまして、B1にこの数式を入力しましたが「＃NAME？」とだけでてしまします…

回答No.6

kkkkkm

No5の補足です
数値として出したいのでしたら
=VALUE(RIGHT(FORMULATEXT(A1),LEN(FORMULATEXT(A1))-FIND("-",FORMULATEXT(A1),1)))

回答No.5

kkkkkm

> たとえばA1は100-50でも、B3が200-100になるような場合の式です。

必ずマイナスでしたら
=RIGHT(FORMULATEXT(A1),LEN(FORMULATEXT(A1))-FIND("-",FORMULATEXT(A1),1))

補足

A2に値引き（50）の部分を反映させたいのであれば、この式はA1に貼るのでしょうか？それともB2？

回答No.4

kkkkkm

No2の補足です
=100+50
などにも対応したい場合は
=ABS(100-A1)

回答No.3

kkkkkm

No2

=100-50の50を知りたいのでしたら

B1に
=100-A1

補足

回答ありがとうございます。

マイナスされる数100が変動しない場合はこの数式でよいのですが、常にその値が変化する場合のことです。

たとえばA1は100-50でも、B3が200-100になるような場合の式です。
たとえば価格200円から値引きされるのが100円となる場合つまり、列Aは常に変化しますが、B列は常に値引きされた数だけを反映させる式を知りたいのです。

回答No.2

kkkkkm

B1に
=A1
としてください。

補足

回答ありがとうございます。

これだと結果の50が反映されてしまいます。
「マイナスした数値50」なので、たとえば100-80＝20でA1に20の結果がでますが、減算した値80をB1に反映させたいのです。

回答No.1

paradius

逆ではだめですか？
例えでいえば
B1に「50」と入力、A1は「=100-B1」
とすれば問題ないと思いますが、どうでしょう？

補足

回答ありがとうございます。

逆ではだめなんです。