[[20160707112535]]　『列の色付きセルのかたまりを計数したい』（大）　ページの最後に飛ぶ

『列の色付きセルのかたまりを計数したい』（大）

過去ログ検索したのですがわからないので教えてください。

Ａ列に色のついたセルのかたまりがランダムに複数あります。

その色付きセルのかたまりを数えてＢ列に連番をつけたいのですが
書式を教えて頂けないでしょうか。

　　　Ａ　　　　　Ｂ
1　　 20
2　　 20
3　　 20
4　　　5色付き　　 1 連番
5　　　7色付き
6　　 15
7　　 20
8　　 15
9　　 20
・
・
50　　 11色付き　　 2 連番
51　　 9色付き
52　　 7色付き
・
152 22
153 10色付き 3 連番
154 19
・
・

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

 その色は書式設定で付けた色か、条件付き書式で付けた色のどちらだろうか。
（ねむねむ） 2016/07/07(木) 11:54

ねむねむさんへ

＞書式設定で付けた色か、条件付き書式で付けた色のどちらだろうか
→条件付き書式で付けました。

宜しくお願い致します。
（大） 2016/07/07(木) 12:53

 条件付き書式の条件はなにか？
（ねむねむ） 2016/07/07(木) 13:01

ねむねむさんへ

＞条件付き書式の条件はなにか？
→数式を使用して書式設定するセルを決定

宜しくお願い致します。
（大） 2016/07/07(木) 13:34

 その数式を聞いているのだが。

 関数では直接色を判断できないので条件付き書式の場合はその条件によって関数でできるかどうかが決まる。

 もし、関数では無理な場合はVBA（マクロ）で行うことになる。

 マクロで構わなければそう書いてくれ。
 その場合は条件付き書式の数式は示す必要はない。
（ねむねむ） 2016/07/07(木) 14:07

ねむねむさんへ

失礼しました。

＞その数式を聞いているのだが
→=A1<$C$3

お手数をお掛けします。
（大） 2016/07/07(木) 14:22

 B1セルに
 =IF(AND(A1<>"",A1<C3),1,"")

 B2セルに
 =IF(AND(A2<>"",A2<$C$3,A1>=$C$3),MAX(B$1:B1)+1,"")
 と入力してB2セルを下へフィルコピーしてみてくれ。
（ねむねむ） 2016/07/07(木) 14:50

ねむねむさんへ

できました。

どこの馬の骨ともわからんｵｲﾗの為に貴重なお時間をさいて頂き恐縮しております。

本当に感謝致します。

図々しお願いですが、

＞関数～～条件付き書式の場合はその条件によって関数でできるかどうかが決まる。
→差し障りのない範囲で後学のために教えていただけないでしょうか？

今回は大変お世話になりました。

（大） 2016/07/07(木) 15:18

 >差し障りのない範囲で後学のために教えていただけないでしょうか？ 
 条件付き書式で設定している条件が一つの場合はまず関数で対応可能（例外はあるだろうが）
 複数の条件になると対応不可能（あるいは式が長く込み入って面倒）になる可能性が高くなってくる。

 基本的に条件を聞いてその条件を関数で表現できるかを考えていき、そのうえで可能か不可能かを判断するので
 単純にこれこれこういう場合は可能、こういう場合は不可能とは説明しきれない。

 もやもやした説明だがこれでいいだろうか？
（ねむねむ） 2016/07/07(木) 15:27

ねむねむさんへ

＞複数の条件になると対応不可能（あるいは式が長く込み入って面倒）になる可能性が高くなってくる
→この内容よく覚えておきます。

図々しお願いにもかかわらずご丁寧にありがとうございました。

（大） 2016/07/07(木) 15:52

ねむねむさんへ

教えてください。

＞B1セルに
＞=IF(AND(A1<>"",A1<C3),1,"")

＞ B2セルに
＞=IF(AND(A2<>"",A2<$C$3,A1>=$C$3),MAX(B$1:B1)+1,"")
＞と入力してB2セルを下へフィルコピーしてみてくれ。

下へフィルコピーしたのですが10万行を超えるとExcelが落ちます。
何か他のやり方を教えて頂けませんか。
（大） 2016/07/11(月) 11:04

 こちらでも10万行ではリソース不足のエラーが出たため式では無理かと。

 VBAで行うことになると思うのでVBAが得意な人の回答を待ってくれ。
（ねむねむ） 2016/07/11(月) 11:20

10万行は無茶ですねぇ。数式を埋める数じゃない。最初にデータ数を書いていただきたかったところ。
というわけで、マクロ案です。

 Sub test()
    Dim i As Long
    Dim iw As Long
    Dim iCou As Long

    iw = Range("C3")

    For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
        If Cells(i, "A").Value < iw Then
            If i = 1 Then
                iCou = iCou + 1
                Cells(i, "B").Value = iCou
            ElseIf iw <= Cells(i - 1, "A").Value Then
                iCou = iCou + 1
                Cells(i, "B").Value = iCou
            End If
        End If
    Next i
 End Sub
（???） 2016/07/11(月) 12:10

 10万行ということなので。xl2010以降限定です。

 Sub Sample()
    Dim v As Variant
    Dim c As Range
    Dim flg As Boolean
    Dim x As Long

    With Range("A1", Range("A" & Rows.Count).End(xlUp))
        ReDim v(1 To .Rows.Count, 1 To 1)
        For Each c In .Cells
            If c.DisplayFormat.Interior.ColorIndex = xlNone Then
                flg = False
            Else
                If Not flg Then
                    x = x + 1
                    v(c.Row, 1) = x
                End If
                flg = True
            End If
        Next

        .Offset(, 1).Value = v
    End With

 End Sub

（β） 2016/07/11(月) 14:14

???様　β様

VBAは全く無知なので今暫くお時間をください。

むねむね様

相談ですが
Ａ列の色付きセルの各かたまりの先頭セルのみのデータを例えばＤ列の同じ行のセルに表示させることは
可能でしょうか？

Ａ4、5のセルに色がついているのでＡ4セルのデータ5をＤ4セルに表示する。
Ａ50000、50001でＡ50000の11をＤ50000に表示する

行　　　　 A　　　　　 D

 1　　    20 
 2　　    20 
 3　　    20 
 4　　　  5色付き　     5 　 
 5　　　  7色付き     
 6　　    15 
 7　　    20 
 8　　    15 
 9　　    20 
 ・ 
 ・ 
50000　　 11色付き　　 11 
50001 　  9 色付き 
 ・
120000　　 7色付き      7

（大） 2016/07/11(月) 15:45

 B1セルは
 =IF(AND(A1<>"",A1<C3),A1,"")

 B2セル以降は
 =IF(AND(A2<>"",A2<$C$3,A1>=$C$3),A2,"")
 になる。

 私のところでは10万件でもすぐに結果が出たのでMAX関数が問題だったようだ。

 であればMAX関数でも大丈夫な式ができるかもしれない。

 一つ質問だが色つきセルの塊同士は最大何行離れているだろうか？
（ねむねむ） 2016/07/11(月) 16:00

 >であればMAX関数でも大丈夫な式ができるかもしれない。
 私のやり方ではリソース不足にはならなかったが再計算にものすごく時間がかかって使えないものにしかならなかったので
 こちらは忘れてくれ。
（ねむねむ） 2016/07/11(月) 16:29

ねむねむ様

＞一つ質問だが色つきセルの塊同士は最大何行離れているだろうか？
→MAX400行ぐらいです。

＞B1セルは=IF(AND(A1<>"",A1<C3),A1,"")
＞B2セル以降は=IF(AND(A2<>"",A2<$C$3,A1>=$C$3),A2,"")
→多少時間は掛りましたが問題ありませんでした。

＞MAX関数でも大丈夫な式ができるかもしれない
→後学のため出来れば教えてください。

（大） 2016/07/11(月) 22:01

 >→後学のため出来れば教えてください。
 結局試行錯誤で
 =IF(AND(A2<>"",A2<$C$3,A1>=$C$3),IFERROR(MAX(OFFSET(B1,-500,,500)),MAX(B$1:B1))+1,"")
 の式で私の環境ではすぐに結果が出た。

 前回の
 >→多少時間は掛りましたが問題ありませんでした。 
 との件があるので大さんの環境では時間がかかるかもしれないが。

 なお、
 OFFSET(B1,-500,,500)
 の部分でMAX関数の範囲をひとつ前の行から500行上までに制限している。

 なのでもし色の塊間が500行以上あいている場合はそこでカウントが1に戻ってしまうのでその場合は-500・500をもっと
 大きい値にしてくれ。

 その前にあるIFERROR関数は行が501行より上の場合には1行目より上を指定しようとしてエラーになるのでその場合は
 1行目から自分の一行上までを見るようにするために入れている。
（ねむねむ） 2016/07/12(火) 09:18

むねむね様　???様　β様

＞=IF(AND(A2<>"",A2<$C$3,A1>=$C$3),IFERROR(MAX(OFFSET(B1,-500,,500)),MAX(B$1:B1))+1,"")
→完璧です。

＞なお～1行目から自分の一行上までを見るようにするために入れている。
→解説付きでよくわかりました。

種々、ご丁寧にありがとうございました。

（大） 2016/07/12(火) 14:11

コメント返信：

[ 一覧(最新更新順) ]

[[20160707112535]] 『列の色付きセルのかたまりを計数したい』（大） ページの最後に飛ぶ

[[20160707112535]]　『列の色付きセルのかたまりを計数したい』（大）　ページの最後に飛ぶ