Office学习社区 › 公式和函数 › 统计每一列，每一种颜色的个数

唯壹 发表于 2022-2-16 13:59:54

你会吗，帮帮忙

刘老根 发表于 2022-2-18 06:00:09

Sub ColorCount()
Dim rngCell As Range, rngUnion As Range, i%
For i = 2 To Range("a" & Rows.Count).End(xlUp).Row
For Each rngCell In Cells(i, 1).Resize(1, 3)
    If rngCell.Interior.Color = 255 Then
      If rngUnion Is Nothing Then
      Set rngUnion = rngCell
      Else
      Set rngUnion = Application.Union(rngUnion, rngCell)
      End If
    End If
Next rngCell
If Not rngUnion Is Nothing Then Cells(i, 4) = rngUnion.Columns.Count
Set rngCell = Nothing
Set rngUnion = Nothing
For Each rngCell In Cells(i, 1).Resize(1, 3)
    If rngCell.Interior.Color = 10498160 Then
      If rngUnion Is Nothing Then
      Set rngUnion = rngCell
      Else
      Set rngUnion = Application.Union(rngUnion, rngCell)
      End If
    End If
Next rngCell
If Not rngUnion Is Nothing Then Cells(i, 5) = rngUnion.Columns.Count
Set rngCell = Nothing
Set rngUnion = Nothing
For Each rngCell In Cells(i, 1).Resize(1, 3)
    If rngCell.Interior.Color = 5296274 Then
      If rngUnion Is Nothing Then
      Set rngUnion = rngCell
      Else
      Set rngUnion = Application.Union(rngUnion, rngCell)
      End If
    End If
Next rngCell
If Not rngUnion Is Nothing Then Cells(i, 6) = rngUnion.Columns.Count
Set rngCell = Nothing
Set rngUnion = Nothing
Next i
End Sub

艳轻小说 发表于 2022-2-22 00:45:31

用函数怎么做

贾永超 发表于 2022-2-23 00:32:34

用函数怎么操作

瞧着卢卡斯 发表于 2022-2-25 02:01:00

颜色代号 :奌选H2-->CTR+F3-->定义名称QQ(随意 非指令均可 如 QQ ,AA…)-->参照到公式=GET.CELL(63,A2)-->确定-->H2输入公式=QQ-->右拉 下拉-->颜色改变或重开机 重新RUN与下拉一次
D2:F17=TEXT(COUNTIF($H2:$J2,CHOOSE(COLUMN(A1),3,29,6)),"0;;")

探花郎 发表于 2022-2-25 08:08:02

可以解析一下步奏？

米斯特尔旺 发表于 2022-2-26 17:56:38

看一看，对不对？

醉梦释疑 发表于 2022-3-4 18:26:55

以传附件~~~~~~~

查看完整版本: 统计每一列，每一种颜色的个数