『色付きセルを数値化し、それを合計する方法について』

たとえば、赤色セルを1、青色セルを2、緑色セルを3とした場合、それを合計できるような関数の作成方法を教えてください。

質問者からの補足コメント

• 回答ありがとうございます。
  Excelは2010となります。
  
  私がやりたい作業としては、月ごとに添付のような画像があるとして（月ごとの縦列のセル数は減ることも増えることもあります。８個とは限りません）、手作業でセルを塗り替えた際に、下の赤枠部分に自動で日ごとの合計が出るようにできたらいいなということです。いまは、都度計算しながら、たとえば２日は5とかいうかんじで手入力しています。
  
  当方Excel初心者で大変申し訳ございませんが、もしわかればお願い致します。

  
  No.3の回答に寄せられた補足コメントです。 補足日時：2015/02/07 21:11

No.10 回答者： WindFaller 回答日時： 2015/02/15 23:03

こんばんは。

#8,#9 の回答者です。どうやら、あまりの量に、呆れてしまったかもしれません。
もし、そうでしたら、大変に申し訳ないことをしました。事実、条件付き書式でまかなえるものなら、
そのほうがよいとは書きましたが、どうにも、そのままにしておけなくなったからです。

No.9 回答者： WindFaller 回答日時： 2015/02/09 21:13

こんばんは。

#8の回答者です。

結局は、マクロそのものになってしまいました。
色が3色でしたら、条件付き書式で十分だと思います。もし、数字がでないようにするなら、書式のユーザー設定で[;;;]すれば、数字は見えなくなります。むろん、合計はそのまま数字となって出せます。

以下は、お使いにならないかもしれませんが、色パターンを入れるための支援マクロです。
行きがかり上、ついでに作ってしまいました。
「色付けが、手動で入れるとしたら、結構面倒だなということは想像にかたくありません。」
なるべく簡素化した内容のものにしましたが、#8のコードと共に使うものです。

サンプルパターン色を使わずに、
　Const CLRINDX As String = "33,6,3" '色番号をコンマ切りで入れる
と直接色番号の数字を入れるものにしました。このコードの下に、WhatColorIndex　という色番号を出すユーザー定義関数を作ってしまいました。

'//
'シートモジュール(タブを右クリック－コードの表示）で貼り付けます。
　Const HANNI_L As String = "AF8" '入力最終セル名
　Const HANNI_F As String = "A1" '範囲の最初のセル名
　Const CLRINDX As String = "33,6,3" '色番号をコンマ切りで入れる(順序は、青,黄,赤と色番号を入れる
　Dim myRng As Range
　Dim arClr As Variant

Private Sub Worksheet_Change(ByVal Target As Range)
　Dim i As Long
　Dim j As Integer
　Application.CalculateFullRebuild
　On Error GoTo EndLine
　j = UBound(arClr) + 1
　If Target.Count > 1 Then Exit Sub
　If Intersect(Target, myRng) Is Nothing Then Exit Sub
　i = Target.Value
　With Target
　　Application.EnableEvents = False
　　If .Value > 0 And .Value < (j + 1) Then
　　　.Interior.ColorIndex = arClr(i - 1)
　　　.ClearContents
　　ElseIf .Value = 99 And .Interior.ColorIndex > 0 Then
　　　.Interior.ColorIndex = xlColorIndexAutomatic
　　　.ClearContents
　　End If
　End With
EndLine:
　If IsArray(arClr) = False Then
　　Call createHANNI
　　Resume Next
　End If
　　Application.CalculateFullRebuild
　　Application.EnableEvents = True
End Sub
Sub createHANNI()
　arClr = Split(Trim(CLRINDX), ",")
　Set myRng = Range(HANNI_F & " : " & HANNI_L)
End Sub

'これは、前回の=GET.CELL(63,B3)+NOW()*0 のマクロ型ユーザー定義関数
'これで、色番号が出せます。
Function WhatColorIndex(rng As Variant) As Integer
　WhatColorIndex = (rng.Cells(1, 1).Interior.ColorIndex)
End Function

No.8 回答者： WindFaller 回答日時： 2015/02/08 14:49

こんにちは。

#5の回答者です。

色付けが、手動で入れるとしたら、結構面倒だなということは想像にかたくありません。

今、条件付け書式の中で、問題があるとすれば、この先、色が増えるということです。
色の数をどこまで求められているのか分かりませんから、増えた時に、現状の方法では対処できなくなってしまいます。

ただ、これらは、ご相談に応じて、パターンの色付けに関しては解消できます。

最終的にご質問者様の選択の範疇にはあると思います。ただ、私は、経験的に、ある一定の考えの中で解決を導き出そうとしていますが、それは、アタリあり、ハズレありです。

さて、前回の私の話の中で
「静的に、その合計値を計算通りになっているか見たい場合、今の私の書いたものに代わる、複数のセルを検査するユーザー定義関数を提供することも出来ます。」
ということで、公開させていただきます。

-----------------------------------
取り付け方
Alt + F11 で、VBEditorを開き、上部メニューー[挿入]－[標準モジュール]をクリツクすると、
白い画面が開きますので、そこに以下のコードを貼り付けます。
Alt + Q で、Excelの画面に戻りますから、一旦、保管をしてください。

一般の関数として使えます。「CINDEX関数」というユーザー定義関数です。
多少、複雑なのは、引数(パラメータ）が、3つ、ないし4つあります。

使い方：
例：
=SUM(cIndex(B$3:B$6,$G$3:$H$5,2,0))

ユーザー定義関数の引数
= cIndex([対象の範囲], [色見本と得点の範囲], [色見本の得点の列], [色見本/０にすると、色全部])

・[対象の範囲]とは、検索される側のセルの範囲
例：　B$3:B$6　列方向にコピーするので、行固定しています。

・[色見本と得点の範囲]とは、色パターン付きのセルと得点の列というのは、パターンの右隣りの数字が入っている列。通常なら、2ですが、、増やしても可能です。

例：$G$3:$H$5
色番号よりも、色見本のほうが分かりやすいはずです。1列目に色見本を置き、その右隣の数字は、得点数としています。

・[色見本/０にすると、色全部]は、省略可能です。検索する側の色のこと。省略すると、「0」の扱いになり、全部の2列目の数値が内部に出ます。色(Index)番号は使わなくても良いようにしました。

上記の数式では、「0」にしていますが、
=SUM(cIndex(B$3:B$6,$G$3:$H$5,2,$A8))
A8にコピーして色を貼り付けてもよいです。

計算は、入力した時だけにしています。そうでないと、その都度再計算し、ブックが重くなってしまいます。
再計算のショートカットは、[CTRL+ALT + F9]です。

なお、戻り値は、配列になっていますが、SUM関数で合計が出せます。
つまり、=INDEX(cIndex($B$3:$B$6,$G$3:$H$5,2,0),1,ROW(A1))
このような数式で、下にドラッグ・コピーすれば、戻り値を全部取り出せます。
その時に、第4引数は、0にしています。

どこのブックでも使えるようにするには、アドイン化にしなくてはいけませんが、そうでなかったら、そのブックだけでお使いになってください。

あくまでも、ご質問者さんの選択の範囲としての提示です。


'//
Function cIndex(rng As Range, ColorSample As Range, col As Long, Optional rw As Variant = 0)
　'rng 色を計算する範囲, ColorSample 色見本,色見本の参照列,特定の色の行(なくても可）
　Dim rCnt As Long
　Dim cIndx As Long
　Dim c As Variant
　Dim i As Long, j As Long, k As Long, x As Long, y As Long
　Dim a As Long
　Dim ar, ar2
　ar = ColorSample.Value
　rCnt = rng.Rows.Count
　'色見本数値化
　With ColorSample
　　y = UBound(ar)
　　x = UBound(ar, 2)
　　ReDim ar2(1 To rCnt)
　　For i = 1 To y
　　　For j = 1 To x
　　　　If j = 1 Then
　　　　　ar(i, j) = .Cells(i, j).Interior.ColorIndex
　　　　ElseIf j > 1 Then
　　　　　ar(i, j) = .Cells(i, j).Value
　　　　End If
　　　Next j
　　Next i
　'オプションの整合化
　End With
　If IsObject(rw) Then
　　For i = 1 To y
　　　If rw.Interior.ColorIndex > 0 Then
　　　If ar(i, 1) = rw.Interior.ColorIndex Then
　　　　cIndx = i
　　　　Exit For
　　　End If
　　　End If
　　Next
　ElseIf IsNumeric(rw) Then
　　 cIndx = rw
　End If
　'計算配列化
　With rng
　　For k = 1 To rCnt
　　　If cIndx > 0 Then
　　　　If .Cells(k).Interior.ColorIndex = ar(cIndx, 1) Then '色サンプルから参照
　　　　　ar2(k) = ar(cIndx, col)
　　　　　Exit For
　　　　Else
　　　　　ar2(k) = 0
　　　　End If
　　　Else
　　　For a = 1 To y
　　　Debug.Print .Cells(k).Interior.ColorIndex & " : " & .Cells(k).Address
　　　 If .Cells(k).Interior.ColorIndex > 0 Then
　　　　If .Cells(k).Interior.ColorIndex = ar(a, 1) Then '色サンプルから参照
　　　　　ar2(k) = ar(a, col)
　　　　　Exit For
　　　　Else
　　　　　ar2(k) = 0
　　　　End If
　　　　End If
　　　Next a
　　End If
　Next k
End With
cIndex = ar2
End Function

No.7 回答者： yokomaya 回答日時： 2015/02/08 10:06

質問者さんの意図通りにお答えされてますけど

これは色付けの方を自動的にすれば簡単になると
思うのです。なんの条件で色付けをされてるのか
わかりませんけど、その条件を納めるセルを
用意してそれに従った条件書式で色付けすれば
そのセルの方をCountifするとか一気に簡単に
なります。色付け作業って結構煩雑ではありませんか？
セルの色はEXCELから扱いにくいものですから
扱いやすいデータに応じた自動色付けが
方向として正統ではないでしょうか？

No.6 回答者： esupuresso 回答日時： 2015/02/08 02:33

セルの色が、青＝１、黄色＝２、赤＝３と決まっているのでしたら、条件付き書式を使ってセルに色を付けることで合計を出せる方法はあります。

添付画像より説明いたします。
セルB3～AF14までを範囲選択しておき、「ホーム」タブの「スタイル」グループ内より「条件付き書式」をクリックします。
表示メニューより「ルールの管理」をクリックして「新規ルール」をクリックします。
ルールの種類より「指定の値を含むセルだけを書式設定」を選択し、「セルの値の間」と書いてある部分を「次の値に等しい」へ変更し、右隣の入力窓に「1」を入力。
次に「書式」と書いてある所をクリックし、「セルの書式設定」の画面上にある「フォント」をクリック。
フォントの色は、ここでは「１」なので「青」を選択。
次に、「塗りつぶし」タブをクリックし、下の標準カラーパレットから「青」をクリック。
ここまで出来たら「OK」を押して、もう一度「OK」を押すと、最初の「条件付き書式ルールの管理」画面に戻りますので、次に黄色の設定を同じようにやっていきます。
「条件付き書式ルールの管理」画面に戻った状態で「新規ルール」をクリック。
ルールの種類より「指定の値を含むセルだけを書式設定」を選択し、「セルの値の間」と書いてある部分を「次の値に等しい」へ変更し、右隣の入力窓に「2」を入力。
次に「書式」と書いてある所をクリックし、「セルの書式設定」の画面上にある「フォント」をクリック。
フォントの色は、ここでは「2」なので「黄色」を選択。
次に、「塗りつぶし」タブをクリックし、下の標準カラーパレットから「黄色」をクリック。
設定が終わったら「OK」→「OK」でまた最初の「条件付き書式ルールの管理」画面に戻りますので、次に赤色の設定を同じようにやっていきます。
「条件付き書式ルールの管理」画面の「新しいルール」をクリック。
「ルールの管理」をクリックして「新規ルール」をクリックします。
ルールの種類より「指定の値を含むセルだけを書式設定」を選択し、「セルの値の間」と書いてある部分を「次の値に等しい」へ変更し、右隣の入力窓に「3」を入力。
次に「書式」と書いてある所をクリックし、「セルの書式設定」の画面上にある「フォント」をクリック。
フォントの色は、ここでは「3」なので「赤」を選択。
次に、「塗りつぶし」タブをクリックし、下の標準カラーパレットから「赤」をクリック。
ここまで出来たら「OK」を押して、もう一度「OK」を押すと、最初の「条件付き書式ルールの管理」画面に戻りますので、最後の画面を「OK」で閉じます。
これで1＝青、2＝黄色、3=赤の設定ができたので、色を付けたいセルに番号を振っていくと、その番号に合った色が塗りつぶされます。
後はセルB15を選択し『=SUM(B3:B14)』の数式を入れ「Enter」キーで合計が出ますので、B15に入れた数式をセルAF15までオートフィル機能を使い式をコピーして終了です。

尚、セルB3～AF14の範囲内には1,2,3のどれかの数値を入れるというのが条件となります。

＞月ごとの縦列のセル数は減ることも増えることもあります。８個とは限りません
合計値を最下行に出す場合、セルの行数が増えることを想定して多めに作っておくと後々楽です。

No.5 回答者： WindFaller 回答日時： 2015/02/07 22:26

こんばんは。

#2の回答者です。
「No.3の回答に寄せられた補足コメント」について。
だいぶ、大掛かりのようですから、私が提示したものでまかなえるかどうか、不安を感じました。
つまり、今の状態ですと、一つのセルに一つの関数しか対応できませんから、作業列などを確保して数式を置かなくてはなりません。今、私は、アドインで色番号を出せる関数を加えてみました。その場合は、xlsmのマクロ付きにしなくてもよいのですが、慣れていない人には、設定自体に手間が掛かりますし、会社などのPCには、管理者権限が必要になる場合があります。

静的に、その合計値を計算通りになっているか見たい場合、今の私の書いたものに代わる、複数のセルを検査するユーザー定義関数を提供することも出来ます。動的には、tom04さんのマクロも良いと思います。

以下は、補足を見る前に書いたもので、当面のお答えだけしておきます。

>当方Excel初心者のため初歩的な質問で申し訳ございませんが、
>たとえばスカイブルーが33番だとしたら、その33という数字はどの式のどこにいれたらいいのでしょうか？(・_・;)

ちょっと私の説明が抜けていたことに気が付きました。すみません。

=IF(色=5,1,IF(色=6,2,IF(色=3,3,0)))
　　　↓
=IF(色=33,1,IF(色=6,2,IF(色=3,3,0)))
5が、33に変わりました。

ということになりますね。

代表的な色(カッコ内が、その色番号）
黒(1),白(2),赤(3),黄緑(4),青(5),黄色(6),ピンク(7),水色 (8), 茶(9), 緑(10)

これで、数式が読めますね。
=IF(色=5,1,IF(色=6,2,IF(色=3,3,0)))
これを分解すると、
色=5は　1,　色=6は　2,　色=3は　3

5は青。6 は黄色。3は赤。

もっと数が多い場合は、INDEX関数を利用すると良いです。

IFの式の代わりに、F3から、以下のように置き換えることも可能です。

=VLOOKUP(色,$J$1:$K$6,2,0)

データは、[色番号,点]のタイトルを除いたデータ範囲「J4:K6」

色番号　　点
3　　　3
5　　　1
6　　　2

昨今は、パステルカラーなどが好まれるようになりました。
スカイブルー(33),淡い水色(34),黄緑 (35),薄い黄色(36),薄い緑(35)

昔、Excelで色番号が出せる一般的な関数ができると聞いていましたが、未だに現れる様子がありません。

No.4 回答者： tom04 回答日時： 2015/02/07 21:39

No.1・3です。

色付けは手作業で行っているというコトですので、
↓の画像のような配置にします。
（A1～B3セルは色に対する数値の対応表にしておきます）
画像では11行まで・集計行は15行目としていますが、集計行はもっと下の行でも良いですし、
11行までのデータももっと増えても構いません。
その辺は実状に合わせてください。

今回もSheetモジュールです。
（やり方はNo.1で説明した通りです）
↓のコードにしてマクロを実行してみてください。

Sub Sample3()
Dim i As Long, j As Long, k As Long
Range("A15").EntireRow.ClearContents '←15行目（集計行）を一旦消去★
For j = 1 To Cells(4, Columns.Count).End(xlToLeft).Column '←A列～4行目のデータがある最終列まで★
For i = 5 To 11 '←5行目～11行目まで（実際のデータ「これ以上データはない！」という行数までにする★
For k = 1 To 3 '←A1～A3セルまで★
If Cells(i, j).Interior.Color = Cells(k, "A").Interior.Color Then
Cells(15, j) = Cells(15, j) + Cells(k, "B")
End If
Next k
Next i
Next j
End Sub

※　関数でないのでデータ変更があるたびにマクロを実行する必要があります。m(_ _)m

No.3 回答者： tom04 回答日時： 2015/02/07 20:03

No.1です。

前回は勘違いしていました。
＞赤色セルを1、青色セルを2、緑色セルを3とした場合
というコトですので色を数値化して、その合計を表示したい！というコトですね。
画像では　青＝１・黄色＝2・赤＝3　となっているので
↓の画像のようにA列に色を付けて、B列に数値を入力しておきます。

今回はE1～G4セルの色は手作業で付けているとします（条件付き書式ではありません）
画像の配置でのコードにしていますので、範囲指定も不要です。

↓のコードにしてマクロを実行してみてください。

Sub Sample2()
Dim i As Long, j As Long, k As Long

Range("E5:G5").ClearContents
For j = 5 To 7 'E列～G列
For i = 1 To 4 '1行目～4行目
For k = 1 To 3 '1行目～3行目（A列参照用）
If Cells(i, j).Interior.Color = Cells(k, "A").Interior.Color Then
Cells(5, j) = Cells(5, j) + Cells(k, "B")
End If
Next k
Next i
Next j
End Sub

何度も失礼しました。m(_ _)m

No.2 回答者： WindFaller 回答日時： 2015/02/07 20:02

こんばんは。

図にしたがって、
青－1, 黄－2,赤－3　にさせていただきます。

色付きセルは、B3:D6にあるとします。

まず、アクティブセルを色付けした右位置に平行移動して、F3 に置きます。
数式－名前の定義－新しい名前

名前：色
範囲：ブック
参照設定欄には、
=GET.CELL(63,B3)+NOW()*0

という数式を置きます。

後は、F3に、
=IF(色=5,1,IF(色=6,2,IF(色=3,3,0)))
として、
F3を基点にして、H6まで、数式をコピーします。

後は、SUM関数で合計を出します。
B7:からD7まで。
=SUM(F3:F6)

を貼りつければよいです。

ただし、パターンの色の色(インデックス)番号は、数式で出してください。
　　=色
とすれば、色番号が出てきます。(数式の出ている画像を見てください。）

例えば、掲示板の青は、スカイブルーのようですから、33番かと思われます。

なお、Get.Cell関数は、マクロ関数と呼ばれるもので、保管の際には、マクロブックとして、xlsmの拡張子が求められます。もし、それが嫌でしたら、コピーして、そのまま同じ位置で、貼り付け－値をしてください。そして、最後は、名前も削除すれば、普通のファイルとして保管することが可能です。積極的にお勧めするものではありませんが、一時期は、このような方法も使われていました。

これは、今から、20年ぐらい前のマクロ関数と呼ばれ、一時期は、抹消されてしまうと噂されていましたが、未だに使え、近年、新たなコマンドも加えられました。

この回答へのお礼

回答ありがとうござます。
当方Excel初心者のため初歩的な質問で申し訳ございませんが、
たとえばスカイブルーが33番だとしたら、その33という数字はどの式のどこにいれたらいいのでしょうか？(・_・;)

お礼日時：2015/02/07 20:34

No.1 回答者： tom04 回答日時： 2015/02/07 18:27

こんにちは！

セルの色の判別に対応できる関数はおそらくないはずです。

VBAであれば可能ですが、条件付き書式で色が付いている場合は
その条件をコードに記載してやる必要があります。

ただし、お使いのExcelのバージョンが2010以降の場合は条件付き書式の色を取得できます。

仮に↓の画像のようにD1～G6セルが条件付き書式で色が付いている場合、
A1～A3セルと同じ色のセル数をB列に表示させるコードの一例を載せておきます。

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面のカーソルが点滅しているところに
下のコードをコピー＆ペースト　→　Excel画面に戻り（VBE画面を閉じて）マクロを実行してみてください。
（Alt＋F8キー　→　マクロ　→　マクロ実行です）

Sub Sample1() 'この行から
Dim i As Long, c As Range, r As Range
Range("B1:B3").ClearContents
For Each c In Selection
For i = 1 To 3
If c.DisplayFormat.Interior.Color = Cells(i, "A").Interior.Color Then
Cells(i, "B") = Cells(i, "B") + 1
End If
Next i
Next c
End Sub 'この行まで

※　A1～A3セルはあらかじめ検索したい色に塗りつぶしておいてください。
※　必ずD1～G6(範囲はどこでも良い）を範囲指定してマクロを実行してください。
※　単に手作業で塗りつぶしている場合はもう少し簡単になります。

尚、条件付き書式で色が付いていて、お使いのバージョンがExcel2007までの場合は
最初に記載したように、条件付き書式の条件をそのままコードに記載する必要があります。

まずはこの程度で・・・m(_ _)m