excel中怎么把带填充色的数据筛选出来

我的excel中有些单元格是带黄色填充色的
能不能把这些数据单独提取出来,或者把
在excel 中只显示这部分数据

7 个解决方案

#1

执行效率是关键

#2

自定义一个函数：

Function ColorValue(CellRange As Range)
Dim RangeIndex As Integer
Dim ColorName As String

   Application.Volatile

   RangeIndex = CellRange.Interior.ColorIndex

   ColorName = RangeIndex

   Select Case True

      Case RangeIndex = 1
           ColorName = "黑"
      Case RangeIndex = 2
           ColorName = "白"
      Case RangeIndex = 3
           ColorName = "红"
      Case RangeIndex = 4
           ColorName = "绿"
      Case RangeIndex = 5
           ColorName = "蓝"
      Case RangeIndex = 6
           ColorName = "黄"
      Case RangeIndex = 7
           ColorName = "紫"
      Case RangeIndex = 8
           ColorName = "青"
      Case RangeIndex = 9
           ColorName = "褐"
      Case RangeIndex = 10
           ColorName = "绿"
      Case RangeIndex = 11
           ColorName = "蓝"
      Case RangeIndex = 12
           ColorName = "黄"
      Case RangeIndex = 13
           ColorName = "紫"
      Case RangeIndex = 14
           ColorName = "青"
      Case RangeIndex = 15
           ColorName = "灰"
      Case RangeIndex = 16
           ColorName = "灰"
      Case RangeIndex = 33
           ColorName = "蓝"
      Case RangeIndex = 34
           ColorName = "青"
      Case RangeIndex = 35
           ColorName = "绿"
      Case RangeIndex = 36
           ColorName = "黄"
      Case RangeIndex = 37
           ColorName = "蓝"
      Case RangeIndex = 38
           ColorName = "红"
      Case RangeIndex = 39
           ColorName = "紫"
      Case RangeIndex = 41
           ColorName = "蓝"
      Case RangeIndex = 42
           ColorName = "青"
      Case RangeIndex = 43
           ColorName = "绿"
      Case RangeIndex = 44
           ColorName = "黄"
      Case RangeIndex = 45
           ColorName = "黄"
      Case RangeIndex = 46
           ColorName = "黄"
      Case RangeIndex = 47
           ColorName = "紫"
      Case RangeIndex = 48
           ColorName = "灰"
      Case RangeIndex = 49
           ColorName = "蓝"
      Case RangeIndex = 50
           ColorName = "青"
      Case RangeIndex = 51
           ColorName = "绿"
      Case RangeIndex = 52
           ColorName = "灰"
      Case RangeIndex = 53
           ColorName = "黄"
      Case RangeIndex = 54
           ColorName = "红"
      Case RangeIndex = 55
           ColorName = "蓝"
      Case RangeIndex = 56
           ColorName = "灰"

   End Select

   ColorValue = ColorName

   Application.Volatile

End Function

在单元格中输入公式：  =ColorValue(A1)  可以得到A1的颜色，然后根据颜色筛选。

#3

如果是查找颜色的单元格，可以使用查找/替换。

菜单：编辑--〉查找--〉格式--〉图案

#4

使用条件格式

#5

条件格式不行吧? samson说说看怎么实现

#6

使用宏表函数getcell(24,)用它定义名称，然后使用辅助列来填充公式。最后对辅助列进行筛选

#7

谢谢大家,问题解决拉,用的公式条件筛选,最后没用颜色.

#1