特殊な引き算（エクセル2010）

質問

エクセル2010です。
知識不足で　教えていただきたいのですが

B6：B30　まで　表示形式　数値で　赤1234　となっております。

入金を　マイナス数値で入力
出金を　プラス数値で入力

B6：B30　の　入力で　

例えば　B6～↓に　以下の数値が入力されているとしますと

100 文字色赤
100
10000　文字色赤
10000
5000　文字色赤
(200）表示形式を　赤（）に変えたもの

などで　ランダムに数値を入力しております。

B2に　-数値の合計（文字色赤の合計）
B3に　整数値の合計
B4に　B2－B3の数式
B5に　B4の計算結果から　(1234）と　赤（）付に変更している数値を引いた数値

※　表示形式を　赤（）に変えたものは　入金扱いにするが計算からは除外したいために
　　B5で再計算させたい。

上の例では

B2　15100
B3 10100　文字色赤
B4 5000
B5　5000-（200）となるので4800　

と　結果を反映させたいのですが、
どういった関数　数式が考えられますでしょうか？

よろしくお願いしたします。

回答No.3

mar00

関数、数式だと(200）を除外するのは入力したままだと無理ではないかと思います。
マクロでやってみました。
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(Target, Range("B6:B31")) Is Nothing Then Exit Sub
Range("B2:B5").ClearContents
For i = 6 To 30
If Left(Range("B" & i).Text, 1) = "(" Then
Range("B5") = Range("B5") + Range("B" & i)
End If
Select Case Range("B" & i)
Case Is < 0
Range("B2") = Range("B2") + Range("B" & i) * -1
Case Is > 0
Range("B3") = Range("B3") + Range("B" & i) * -1
End Select
Next i
Range("B2") = Range("B2") + Range("B5")
Range("B4") = Range("B2") + Range("B3")
Range("B5") = Range("B4") + Range("B5")
End Sub

回答No.2

tom04

こんばんは！
外しているかもしれませんが・・・

B6～B30内の（赤文字表示）というのはセルの表示形式でマイナス値が「赤」になっているという前提です。
（200）の部分も値はマイナスで表示形式だけを変えているものとします。

セルの表示形式は関数では判断できないと思いますので、VBAでの一例です。

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面が出ますので
↓のコードをコピー＆ペーストしてB6～B30のデータを色々変えてみてください。

Private Sub Worksheet_Change(ByVal Target As Range) 'この行から
If Intersect(Target, Range("B6:B30")) Is Nothing Then Exit Sub
Dim i As Long
Dim vl1, vl2, vl3 As Variant
For i = 6 To 30
If Cells(i, 2).NumberFormatLocal <> "0_);[赤](0)" And Cells(i, 2) < 0 Then
vl1 = vl1 + Cells(i, 2)
ElseIf Cells(i, 2) > 0 Then
vl2 = vl2 + Cells(i, 2)
Else
vl3 = vl3 + Cells(i, 2)
End If
Next i
Range("B2") = Abs(vl1)
With Range("B3")
.Value = vl2
.Font.ColorIndex = 3
End With
Range("B4") = Abs(Range("B2")) - Abs(Range("B3"))
Range("B5") = Range("B4") - Abs(vl3)
End Sub 'この行まで

※　B2・B3セルは関数だけでも対応できると思いますが、ついでにVBAでやってしまいました。
※　質問文内の「赤文字」部分はマイナス値だとして判断していますので
的外れならごめんなさいね。m(_ _)m

回答No.1

keithin

「赤文字」を使っているのでプラスとマイナスの勘違いがある様子にも見えますが，ご相談はキチンと正しく考えて間違えずに書いてあるものと仮定します。

「赤(1234)」を計算から除外することはできないので，今のままでは計算する方法はありません。
簡単にできることとしては，書式で区別するのを諦めて
’(200)
などのように（この例では「文字列」で記入することで）区別できる記入のしかたをします。
以下はその前提で。

Ｂ２：Ｂ６には「赤1234」の設定が施してある
Ｂ２：
=-SUMIF(B6:B30,"<0")
Ｂ３：赤文字
=-SUMIF(B6:B30,">0")
Ｂ４：
=-SUM(B6:B30)
Ｂ５：
=SUMPRODUCT(--B6:B30)-2*SUM(B6:B30)