エクセル 色のついた行番号を取得する

質問

データの集計表で、オレンジ色の行は、下の行の合計を入れます。

1行目.オレンジ色の行（Colorindex＝44）
2行目.（無色）
3行目.（無色）
4行目.オレンジ色の行
5行目.（無色）
6行目.（無色）
7行目.（無色）
8行目.（無色）
9行目.オレンジ色の行
　　　　・
　　　　・
　　　　・
といった形に、ランダムに集計行があります。
さらに、
列A,列B,列C
大分類,中分類,売上金額
集計行（オレンジ色）
肉,（空白）,1000
肉,生肉,500
肉,ハム,300
肉,ソーセージ,200
魚,（空白）,750
魚,たい,450
魚,いか,200
魚,えび,100
集計行（オレンジ色）
野菜,（空白）,1250
野菜,キャベツ,950
野菜,トマト,300
漬物,（空白）350
集計行（オレンジ色）
　　　　・
　　　　・
　　　　・

列Bが空白の場合は、その下の中分類の集計行なので、列Bが空白の行だけの合計を、列Cのオレンジ色のセルにSUM関数で集計したいのです。
また、次のオレンジの集計行まで、全ての列Bが空白の場合もあります。

まず、オレンジ色の行から、次のオレンジ色の行範囲を確認して、無色の行だけをSUM関数で集計できるのか、さらに列Bに文字が入っていた場合、それを除外してSUM関数で集計できるのか。を教えていただけますでしょうか。

以上宜しくお願い致します。

回答No.4

Wendy02 · ベストアンサー

こんにちは。Wendy02です。

早速の返事ありがとうございます。

このご質問は、今まで、類のないものです。もちろん、今まで出ていた内容を組み合わせて出来ますが、この処理を一括のユーザー定義関数で処理するというのは、思うほど簡単なものではありません。

出来上がったコードを見せられれば、何だ簡単とは思うかもしれませんが、今までにはナイモノをアルモノにするというのは簡単じゃありませんでしたね。なお、現在、縦に対して計算しますが、複数列は選べません。ちょっと手を加えると、縦横、どちらでも計算できるようになります。試行錯誤の作ですから、まだ、どこかヘンなところがあるかもしれません。

Function SUMIFCOLOR(範囲1 As Range, _
　　　　　　　　　　 条件 As Variant, _
　　　　　　　　　　 範囲2 As Range, _
　　　　　　　　　　 Optional 条件色 As Integer)
　 Dim rnum As Long, cnum As Integer, i As Long, j As Integer
　 Dim sum As Double, SumArray() As Double, k As Long
　 Application.Volatile

　 If 範囲1.Count = 1 Or 範囲2.Count = 1 Then
　　　 If 範囲1 = 条件 Then
　　　　SUMIFCOLOR = 範囲2: Exit Function
　　　 Else
　　　　SUMIFCOLOR = 0: Exit Function
　　　 End If
　End If

　 rnum = UBound(範囲1.Value, 1)
　 cnum = UBound(範囲2.Value, 2)

　 If Not ((rnum > 1 And cnum = 1) Or (rnum = 1 And cnum > 1)) Then
　　　SUMIFCOLOR = CVErr(xlErrNum)
　　　Exit Function
　 End If
　 For j = 1 To cnum
　　　For i = 1 To rnum
　　　　 If 範囲1.Cells(i, j).Interior.ColorIndex = 条件色 _
　　　　　　Or i = rnum Then
　　　　　　ReDim Preserve SumArray(k)
　　　　　　SumArray(k) = sum
　　　　　　sum = 0
　　　　　　k = k + 1
　　　　 ElseIf 条件 = 範囲1.Cells(i, j).Value _
　　　　 And InStr(2, 範囲2.Formula(i, j), "SUMIF", 1) = 0 Then
　　　　　　If VarType(範囲2.Cells(i, j)) = vbDouble Then
　　　　　　sum = sum + 範囲2.Cells(i, j).Value
　　　　　　End If
　　　　 End If
　　　Next i
　 Next j
　 SUMIFCOLOR = SumArray(LBound(SumArray))
End Function
ユーザー定義関数の取り付け方はご存知かと思います。

この式の使い方は、ちょっと工夫で便利になります。
B2:
=SUMIFCOLOR(B3:$B$17,"",C3:$C$17,44)

B3:$B$17
　　　↑
　　　データの最終行を選択します。
↑
相対参照で、1つ下を選びます。

この式を1つコピーして、後は全てどこに貼り付けても、計算します。

'============ 補足 =======================
なお、「集計行」という行を探すのでしたら、ユーザー定義関数はいりませんね。ただし、最後の集計行だけは、「集計行」という言葉が見つからないので、エラーになります。

B2に式を入れる場合
=SUMIF(B3:INDEX($A$1:$C$18,MATCH("集計行",$A3:$A$18,0)+ROW()-1,2),"",C3:INDEX($A$1:$C$18,MATCH("集計行",$A3:$A$18,0)+ROW()-1,3))
．

お礼

ご回答ありがとうございました。
まだまだ勉強中なので、いただいた回答を理解するのに、今まで時間がかかってしまいました。
すばらしいです!!決してお世辞などではなく、感動しております。

>なお、「集計行」という行を探すのでしたら
本当は、集計行にはその大分類の上の「部門名」が入るので、いただいた補足内容では今回に関しては処理は無理ですね。ですけど、これも非常に参考になります。
また、別の機会に活用させていただきます。

本当に、ありがとうございました。

回答No.3

imogasi

時々この手の質問があるが、今のエクセルの関数の、原理的な点から、できません。それは色のコードを関数で捕らえる関数がないことによります。
そこで、そういう関数があるとか、ないとかいう回答が過去に出ました。旧いバージョンの関数を使うためです。
しかしないとしておきます。
簡単なVBAでユーザー関数を使うと、色コードを数値化できる関数が作れますのでそれを使えばよい。しかし、「色という書式」と「関数が扱う値というものは」世界が違うことを認識してください。
http://okweb.jp/kotaeru.php3?q=1006740
や「エクセル　セル　色　カウント」などで照会すれば出てきます。

お礼

ありがとうございます。
色々なパスワードで検索したつもりでしたが、不足だったようで、たどりつけませんでした。
いつもお手数をおかけして申し訳ございません。

回答No.2

Wendy02

こんばんは。Wendy02です。

質問を読んでみて、疑問に感じたのは、「次のオレンジ色の行範囲を確認して、無色の行だけをSUM関数で集計」とある場合に、下側のセルに向かってというのは、ひじょうにやりにくいことに気がつき、再度確認したほうがよいと思いました。

中分類　　売上金額
(オレンジ行)(1)
　　　　　 1000　 ----
生肉　　　　500　　↑
ハム　　　　300　　｜
ソーセージ　200　　この空白行の条件合計はどこに？
　　　　　　750　　 (1) か (2)か？
たい　　　　450　　｜
いか　　　　200　　↓
えび　　　　100　　-----
(オレンジ行)　(2)
　　　　　 1250　　-----
キャベツ　　950　　 ↑ ここは、(2)か(3)か？
トマト　　　300　　 ↓
　　　　　　350　　-----
(オレンジ行) (3)

ユーザー定義関数を作ってみて、その条件によってコードが換わります。ただし、上の表では、(1),(2),(3)の計算式を入れる場所はずれていますが、C列です。

補足

ご回答ありがとうございます。
おっしゃる通り、集計行のSUM（SUMIF）は下側を参照しています。
別の人間が作ったフォーマットに間違いがないように関数を入れるのが目的なので、このような形になっています。
集計行数がランダムな為、現状手作業で入れていくしかないのですが、それではミスが発生してしまいますし、作業カラムに集計行であることを判別するフラグを立てて、それで範囲を確定する方法もあるのですが、出来る限り現状のフォーマットを崩したくないのです。フォーマットを作った人がワガママなので（笑）

回答No.1

Wendy02

こんにちは。Wendy02です。

＞列Bが空白の行だけの合計を、列Cのオレンジ色のセルにSUM関数で集計したいのです。

例えば、
C12:
=SUMIF(B4:B11,"",C4:C11)

というような具合で良いのではありませんか？
それとも、まだ、他にあるのかな？

補足

・・・おっしゃる通りですね。気づきませんでした(^^;
後、計算範囲を、オレンジ色の行から次のオレンジ色の行までにする方法さえ分かれば問題解決なのですが。

御礼；先日ご紹介いただきました、「かんたんプログラミング EXCELVBA」3冊とも購入しました。以降、本を頼りに大分一人で解決できるようになりました。ありがとうございました。