エクセル表で数字の色別合計は可能ですか

質問

エクセル２０００を使用しています。
エクセル表で１ヶ月の支払い明細を作成したとします、
下段に支払い済み合計
　　　支払い予定合計
を作成したとして
明細中の数字を支払い予定は赤色（最初は全て赤色）
支払い済を黒色へ変更とした場合
それぞれの合計が色が変わる毎に
自動的に変わっていく
このようなことが出来ないでしょうか

回答No.3

ja7awu · ベストアンサー

状況から察すると、ＶＢＡを使わないと、実用には耐えられないでしょう。

ユーザー定義関数を使う方法です。

使い方は、=CSUM( 範囲 , 色番号 ) です。

ちなみに 色番号は、黒=1、赤=3　です。
範囲内のフォント色や、値を変更した場合は、<Enter>等で確定した時点で
再計算されます。

参考まで、色番号（カラーインデックス）を調べる GetCIndx 関数も付け加えます。
使い方は、セルA1を調べる色で塗り潰し、B1に =GetCIndx(A1) を入力すると
その色番号が表示されます。　この値がマイナスになった場合は、色は「自動」に
設定されていますから、通常では色番号の引数に 1 を使用します。

一応、VBAコードの設定方法を書いておきますが、ご存知でしたら読み飛ばしてください。

１．Alt + F11 で VBE(Visual Basic Editor)を開きます。
２．VBE のメニューから[挿入] -->[標準モジュール] を指定します。
３．モジュールウィンドウに下記コードをコピーして貼り付けます。
４．Alt + Q （または、右上隅の×）でウィンドウを閉じ、シートに戻ります。
５．メニューから[ツール]-->[マクロ]-->[セキュリティ]で「セキュリティレベル」を
　「中」にして[OK]します。
これで設定完了です。　CSum と GetCIndx 関数が使用できます。

Function CSum(R As Range, idx) As Double
Dim Rng As Range
Dim Cnt As Double
Application.Volatile
For Each Rng In R
　　If IsNumeric(Rng.Value) Then
　　　　If Rng.Font.ColorIndex = idx Then
　　　　　　Cnt = Cnt + Rng.Value
　　　　ElseIf idx = 1 And Rng.Font.ColorIndex = _
　　　　　　xlAutomatic Then
　　　　　　Cnt = Cnt + Rng.Value
　　　　End If
　　End If
Next Rng
CSum = Cnt

Function GetCIndx(Rng As Range)
If Rng.Count > 1 Then
　　GetCIndx = vbNullString
　　Exit Function
End If
GetCIndx = Rng.Interior.ColorIndex
End Function

回答No.7

imogasi

作業列（どこでも良いがＢ列とします）を使います。
あとで列を非表示にする手もある。
（例データ）Ａ１：Ａ６に
123
23（赤フォント）
345
56（赤フォント）
67
67
（準備）ＶＢＥの標準モジュールにコピー貼りつけ。
たった４行。
Function cl(a)
n = a.Font.ColorIndex
cl = n
End Function
（関数式）
Ｂ１に=cl(A1)と入れる。
Ｂ２以下Ｂ６まで複写。
Ｂ列は
-4105
3
-4105
3
-4105
-4105
（合計を出す関数式）
赤字分=SUMIF(B1:B6,3,A1)赤はコード３だから。
黒字分=SUMIF(B1:B6,-4105,A1)黒は色なしの－４１０５だから。
（結果）
赤字分７９
黒字分６０２

お礼

ありがとうございました

回答No.6

ja7awu

> １．回答のコピーなどの後関数を入力したら
> End Functionがないとエラー画面とコピーした画面が表示されたので、指示
> どうりにしたら動きました。これはこのとうりでよろしいでしょうか

あらまぁ～。 ごめんなさい。

回答を貼り付けるときに１行、少なく貼り付けました。

CSum = Cnt の下に　End Function　の１行を挿入してください。

> ２．このエクセルのファイルを媒体で他のＰＣにコピーした場合使用できますか
> よろしくお願いします。

勿論使用出来ます。 但し、そのユーザーがセキュリティレベルを高にしていたり、
無効で開いた場合は、動作しません。

そのときは、先にも書いたように
メニューから[ツール]-->[マクロ]-->[セキュリティ]で「セキュリティレベル」を
「中」にして[OK]します。

お礼

かさねがさねありがとうございました。
これですごく管理しやすくなりました。

回答No.5

wolv

色を使わない代案です。

A列　項目
B列　金額
C列　状態コード（支払済み=1,支払い予定=2）
D列 支払済み金額
E列 支払予定金額

のようにしてA,B,C列に入力します。D,E列は、

D10を =if(C10=1,B10,"")
E10を =if(C10=2,B10,"")

のようして自動的に値が入るようにします。

お礼

ありがとうございました
今後の参考にさせていただきます

回答No.4

ja7awu

＃３ ですが、使い方の具体例です。

> 使い方は、=CSUM( 範囲 , 色番号 ) です。

例えば、A1:A10 の範囲でフォント色が、「赤」の数字の値を合計する場合は、

　=CSUM(A1:A10,3)

同じく、黒の数字は、　=CSUM(A1:A10,1)　で求められます。
その他、関数の使い方は、一般の関数と同じです。

例えば、今、範囲内の赤色の数字であったものを黒に変更して<Enter>を押した時点で、
双方の値が加減されます。

たぶん、こういう機能を期待しているのではないでしょうか。

補足

まさに期待どうりの機能です。ありがとうございます。
おそれいいりますがこちらＶＢＡ初心者につき下記の２点補足して頂けないでしょうか。申し訳ありません
１．回答のコピーなどの後関数を入力したら
End Functionがないとエラー画面とコピーした画面が表示されたので、指示どうりにしたら動きました。これはこのとうりでよろしいでしょうか
２．このエクセルのファイルを媒体で他のＰＣにコピーした場合使用できますか
よろしくお願いします

回答No.2

shiga_3

作業列を使う方法ですが、
A1:A10に金額が入っている場合に、
B1:B10を作業列として、支払済みになった時にここに適当な文字（例えば「*」）を入れたらＡ列の金額が赤から黒に変わって、その合計がA11に、残りの支払い予定合計がA12に表示される
というのではいかがでしょうか。

具体的には
1）A1:A10を範囲選択して、書式→条件付き書式で
・「数式が」「=B1<>"*"」で書式→フォントの色を「赤」
・「数式が」「=B1="*"」で書式→フォントの色を「黒」
としてＯＫ
2）A11に =SUMIF(B1:B10,"=*",A1:A10)
A12に =SUMIF(B1:B10,"<>*",A1:A10)
と入力
となります。

お礼

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

回答No.1

fever

１つの案ですが、支払い予定の金額をマイナス表示にして、セルの書式設定で負の数の表示形式を赤色（マイナスなし）に設定されては如何でしょう。

合計は、SUMIF関数を使用して、プラス側（支払い済み）とマイナス側（支払い予定）で合計を出します。
但し、マイナス側は、『*(-1)』にして、値を負から正に直します。

お礼

早速回答ありがとうございます。
このような考え方もありですね。
今後参考にさせていただきます。