エクセルで＋、－の符号付きの金額（円）の足し算

質問

先程ここで下記の関数を教えて頂いてA列とB列でデモしてみて、解決したと思ったのですが、実際の表には空白セルが有るのでうまく行かないことが分かりました。
実際の表は行方向に、C列からL列まで、+123円、（空白）、（空白）、-456円、（空白）、（空白）、+789円、・・・というように3つのセル毎に、最初に金額、後2つのセルは空白、というように並びます。

=SUMPRODUCT(SUBSTITUTE(A1:B1,"円","")*1)
⇒実際にはC列：L列で、間に上記のように空白セルが有ります。

早合点してしまって何度もお手数をかけますが宜しくお願いします。

回答No.1

mt2015 · ベストアンサー

手抜きですがこれでも行けると思います、
=SUMPRODUCT(SUBSTITUTE(C1:L1&".0","円.0","")*1)

お礼

非常に有効な方法（関数の組合せ）を教えて頂けて感謝です。
今後とも宜しくお願い致します。

補足

これまた一発で出来ました。　
どこが手抜きかわかりませんが、2つの関数だけで、別セルの挿入も不要で処理できるのは素晴らしい！！

これまで空白セルは手間暇かけて　if（A1="",　）　で処理していたので、教えて頂いたやり方全てが参考になりました。

出来れば　".０"の　０の前の　.　と、円の後の　.０　の意味を教えて頂けませんか？
　.　は無くても行けますし、円の後の　0　は無いと桁数が1桁多く出ます。

教えて頂いた方法を今後使用するのに知っておきたく、本当に何度も申し訳ありませんが宜しくお願い致します。

回答No.8

tsubu-yuki

Ｎｏ．５の補足への追記です。

その場合は残念ながら文字列として貼り付いてしまうので、以下の手順で。

・書式（表示形式）を設定しておく。
・貼り付け先の書式に合わせて貼り付ける
・（必要ならＣ・Ｆ・Ｉ・Ｌ列を選択した状態で）
　置換機能を使って「円」を""に置き換える
　　※「置換後の文字列」欄を空白のまま置換

してやると良いですよ。
一手間増えてしまうように感じられるかもしれませんが、
慣れてしまえばそんなに苦にならないと（私は）思っていたりします。
つまり・・・
・元データをコピー
・エクセルの該当範囲の先頭で貼り付け・・・Ｃｔｒｌ＋Ｖ
　　※貼り付けた範囲が選択されている状態のはずなのでそのまま
・置換ダイアログ呼び出し・・・Ｃｉｒｌ＋Ｈ
・「検索する文字列」に「えん（変換して確定）」
・「置換後の文字列」は空白のまま「全て置換」・・・Ａｌｔ＋Ａ
・置換完了のメッセージを確認し、閉じる・・・Ｅｎｔｅｒ
・置換のダイアログを閉じる・・・Ｅｓｃ
と、一連の動作を（慣れれば）キーボードの操作だけで出来ます。

関数式で頑張るか、エクセルの機能で頑張るか、
この辺りは好みですので何とも言いづらいところですね。
ただ、個人的に使うブックなら何でも良いと思うのですが、
共有したり配布したりで他人の目にも触れるブックを作る場合、
その「見る他人」が関数式を理解できるとは限りません。
なので、私は極力「誰が見ても後で編集しやすいように」作るのを好む、
という、単なる個人的趣味から、その一手間をかけるほうを推します。

お礼

度々ありがとうございます。
上記の方法なら今まででも出来ましたが、発想が出ませんでした。
しかも、「マクロで記録」すればだれでも一発で処理出来そうですね。

以前よりアドバイスいただいている通り、私もトラぶった時に別の人でも対応できるような方法で且つミスらずに操作は簡単に、が良いと思っていますので、上記の作業方法は表に記載して、実作業はマクロですることも1案ですね。

毎回適切な方法をありがとうございます。

但し、申し訳ありませんが今回のBSは最初にしかも簡単に出来る関数の組合せを教えてくださった＃No１さんにさせて頂きます。

回答No.7

bunjii

>出来れば　".０"の　０の前の　.　と、円の後の　.０　の意味を教えて頂けませんか？
SUBSTITUTE(C1:L1,"円","") と SUBSTITUTE(C1:L1&".0","円.0","") の違いは何かと言うことですよね？
D1セルがブランク（未入力）の場合
SUBSTITUTE(D1,"円","") → SUBSTITUTE("","円","") → "" （0文字の文字列は数値化できない文字列です）
SUBSTITUTE(D1&".0","円.0","") → SUBSTITUTE(".0","円.0","") → ".0" （数値化可能の文字列で".0"は"0.0"と等価になります）
=SUMPRODUCT(SUBSTITUTE(C1:L1&".0","円.0","")*1)
と
=SUMPRODUCT((SUBSTITUTE(C1:L1,"円","")&".0")*1)
は等価です。
尚、[数字の文字列]*1 は VALUE([数字の文字列]) と等価です。
[数字の文字列]*1 、 [数字の文字列]/1 、[数字の文字列]+0 、[数字の文字列]-0 はすべて等価です。

お礼

いつもお世話になっております。
非常に丁寧な解説ありがとうございます。
最初の疑問の何故空白が入るとエラーになるのか良く分かりました。
また
＞　.　は無くても行けますし、円の後の　0　は無いと桁数が1桁多く出ます。
の　.0と０の違いもよくわかりました。

当方には
=SUMPRODUCT((SUBSTITUTE(C1:L1,"円","")&".0")*1)
の方が理解しやすく、これなら次から考えて式が作れそうです。

また、数字化するのに「*1」専門でしたが、値が変わらない演算を入れると数字化出来るのも面白いですね。（どれか1つで良いはずですが）

回答No.6

mt2015

ANo.1です。

> 出来れば　".０"の　０の前の　.　と、円の後の　.０　の意味を教えて頂けませんか？
> 　.　は無くても行けますし、円の後の　0　は無いと桁数が1桁多く出ます。

手抜きの説明をするのも恥ずかしいのですが、
・「円」が付いている金額の文字列の場合（例：＋123円）
　「＋123円.0」と言う文字列にしたうえでSUBSTITUTEで「円.0」を削除し、「＋123」にして数値化して合計します。

・空白の場合
　「.0」と言う文字列にした上で数値化（「.0」は「0.0」と同じ扱いなので値は0になる）し、合計します（値0なので影響なし）
　＃空白の場合は「円.0」と言う文字列が含まれていないのでSUBSTITUTEは無処理

「0」ではなく、「.0」にした理由は、「789」の様なそのまま数値として扱える文字列が有った場合、「0」だと7890の様に一桁大きな数値になりますが、「.0」なら789.0になり合計値に影響が出ないと考えた為です。
＃「789.1」の様な文字列があるとエラーになるのが手抜きたる所以です。

お礼

早々のご回答に感謝！！！
一発で出来たスッキリの上に、納得のスッキリです。

この方法は汎用出来そうなので大変参考になりました。

回答No.5

tsubu-yuki

ご質問の内容から少し離れてしまうのですが、
「セルの書式設定－値の表示形式」について
もう少し勉強なさることをオススメします。

端的に説明すると・・
表示形式を「ユーザー定義」にして、
「種類」の欄にお好みの「書式」を入力することで
セルの表示を色々と変えることができます。
　　※「種類」の下にサンプルが並んでいるので参考に。

で、この時、セミコロン「；」で区切ることにより、
　　正の場合；負の場合；０（ゼロ）の場合；文字列の場合
として、値によってそれぞれ別に設定することが可能です。

例えば今回のような場合、「種類」に
　　"+"#,##0"円"；"-"#,##0"円"
　　※「；」は実際は半角で入ります。
　　　コンマで桁区切りして、小数は四捨五入・・の意味です。
　　　詳細はご自身で研究なさってください。
と設定してやると、セルに「123」と入力してやると「+123円」と、
「-456」と入力してやると「-456円」と表示されていることと思います。
　　※「負」「０」「文字列」の場合は省略してもＯＫです。
　　　上記では「０」と「文字列」を省略しています。

こうなると、セルの値は「数値」だけしか入っていませんから、
普通にＳＵＭ関数で合計できてしまいます。
つまりご質問のケースだと何も考えずに
　　=SUM(C1:L1)
だけで本当は良いんです。

表計算でもなんでもそうなのですが、
「余計なモノは入れない」が大原則ですよ。

補足

いつもお世話になります。

セルの書式設定を使うことは発想も出来ませんでしたので非常に参考になりました。

ところで、恥ずかしながらの追加質問になってしまいますが、今回の場合はNETからの画像のコピーが元データなので、セルには文字として+１２３円とかの値が入ります。
コピーした画像をエクセルシートの同じ場所に上書きで貼り付けます。
その値を、セル参照で表に転記して合計を出しています。
これまでは、元データに「円」が付いていなかったので数字として貼り付けられたので問題なかったのですが、今年から数字＋「円」になってしまったので困っています。

このような場合の符号付きの数字部分だけへの変換も書式設定で出来るのでしょうか？

出来るかどうかも分からずの、おんぶにだっこの質問で済みません。

回答No.4

noname#232800

もっと言うと

単位のセルを別のセルにします。符号はどうしても欲しいでしょ？
符号付きの数字で、隣の列に単位を表示します。

そもそも、どうして符号が必要なんでしょ？

+4450 円
+2000 円
-1000 円

変数名は短く、計算を単純化させると、Googleスクリプトとかが速いです。

お礼

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

区切り位置で文字を切り離す方法も使うことが有りますが、別セルが必要になりますので何とかならないかと。。。

お金の計算なので＋-は必要です。

回答No.3

mt2015

ANo.1です。
もし、空白だけでなく金額と無関係な文字列もある場合は以下の式をCtrl+Shift+Enterで配列数式として確定してみてください。
＃Excel2007以降限定です。
=SUM(IFERROR(SUBSTITUTE(C1:L1,"円","")*1,0))

お礼

確かにご指摘のケースも有りますので非常に参考になりました。
配列数式は使っていますのでご回答の式は当方でも使えると思います。

ご丁寧な回答に感謝！！！

回答No.2

noname#232800

セルって「変数」と考えることができます。

円の面積の場合　ｒｘｒｘπ　ですが、表計算は　B2 ｘ B2 ｘ πと書くことができます。

＝if(g3="","",int(g3))

で、空白の時はそのまま（””で消す）、以外はg3　を　　h3　に入れます。

お礼

””を””で置き換える発想は参考になりました。
ありがとうございました。