作者:绿色风
分类:
时间:2022-08-18
浏览:154
楼主 99253415 |
Q:如何筛选条件格式之后的数据? 通过条件格式,将需要的数据都标注上了红色,现将标注颜色的数据重新整理一次.
A: 方法一: 将条件格式设置的公式运用到辅助列,然后进行筛选 D2输入数组公式下拉- =SUM((B2={"ISO9002","ISO9007"})*1)
而后筛选该辅助列
筛选条件格式之后的数据(普通辅助列).rar
方法二: 借用Excel2007以上版本的筛选功能,可以直接对条件格式的背景色进行筛选
方法三: 这是由Emily老师编写的获取条件格式的单元格信息自定义函数- Option Explicit
- Function ConditionalColor(rg As Range, FormatType As String) As Long
- 'Returns the color index (either font or interior) of the first cell in range rg. If no _
- conditional format conditions apply, then returns the regular color of the cell. _
- FormatType is either "Font" or "Interior"
- Dim cel As Range
- Dim tmp As Variant
- Dim boo As Boolean
- Dim frmla As String, frmlaR1C1 As String, frmlaA1 As String
- Dim i As Long
- 'Application.Volatile 'This statement required if Conditional Formatting for rg is determined by the _
- value of other cells
- Application.Volatile
- Set cel = rg.Cells(1, 1)
- Select Case Left(LCase(FormatType), 1)
- Case "f" 'Font color
- ConditionalColor = cel.Font.ColorIndex
- Case Else 'Interior or highlight color
- ConditionalColor = cel.Interior.ColorIndex
- End Select
- If cel.FormatConditions.Count > 0 Then
- 'On Error Resume Next
- With cel.FormatConditions
- For i = 1 To .Count 'Loop through the three possible format conditions for each cell
- frmla = .Item(i).Formula1
- If Left(frmla, 1) = "=" Then 'If "Formula Is", then evaluate if it is True
- 'Conditional Formatting is interpreted relative to the active cell. _
- This cause the wrong results if the formula isn't restated relative to the cell containing the _
- Conditional Formatting--hence the workaround using ConvertFormula twice in a row. _
- If the function were not called using a worksheet formula, you could just activate the cell instead.
- frmlaR1C1 = Application.ConvertFormula(frmla, xlA1, xlR1C1, , ActiveCell)
- frmlaA1 = Application.ConvertFormula(frmlaR1C1, xlR1C1, xlA1, xlAbsolute, cel)
- boo = Application.Evaluate(frmlaA1)
- Else 'If "Value Is", then identify the type of comparison operator and build comparison formula
- Select Case .Item(i).Operator
- Case xlEqual ' = x
- frmla = cel & "=" & .Item(i).Formula1
- Case xlNotEqual ' <> x
- frmla = cel & "<>" & .Item(i).Formula1
- Case xlBetween 'x <= cel <= y
- frmla = "AND(" & .Item(i).Formula1 & "<=" & cel & "," & cel & "<=" & .Item(i).Formula2 & ")"
- Case xlNotBetween 'x > cel or cel > y
- frmla = "OR(" & .Item(i).Formula1 & ">" & cel & "," & cel & ">" & .Item(i).Formula2 & ")"
- Case xlLess ' < x
- frmla = cel & "<" & .Item(i).Formula1
- Case xlLessEqual ' <= x
- frmla = cel & "<=" & .Item(i).Formula1
- Case xlGreater ' > x
- frmla = cel & ">" & .Item(i).Formula1
- Case xlGreaterEqual ' >= x
- frmla = cel & ">=" & .Item(i).Formula1
- End Select
- boo = Application.Evaluate(frmla) 'Evaluate the "Value Is" comparison formula
- End If
-
- If boo Then 'If this Format Condition is satisfied
- On Error Resume Next
- Select Case Left(LCase(FormatType), 1)
- Case "f" 'Font color
- tmp = .Item(i).Font.ColorIndex
- Case Else 'Interior or highlight color
- tmp = .Item(i).Interior.ColorIndex
- End Select
- If Err = 0 Then ConditionalColor = tmp
- Err.Clear
- On Error GoTo 0
- Exit For 'Since Format Condition is satisfied, exit the inner loop
- End If
- Next i
- End With
- End If
- End Function
D2输入公式下拉- =ConditionalColor(B2,"interior")
然后进一步对此辅助列进行筛选
筛选条件格式之后的数据(自定义函数).rar
如果需要对筛选结果单元复制出来可参考此帖《如何复制隐藏行或列后的可见单元格内容? 》http://www.exceltip.net/thread-19801-1-1.html
|
2楼 九尾龙 |
感谢分享,下载学习。 |
3楼 水星钓鱼 |
学习下 |
免责声明
有感于原ExcelTip.Net留存知识的价值及部分知识具有的时间限定性因素,
经与ExcelTip.Net站长Apolloh商议并征得其同意,
现将原属ExcelTip.Net的知识帖采集资料于本站点进行展示,
供有需要的人士查询使用,也慰缅曾经的论坛时代。
所示各个帖子的原作者如对版权有异议,
可与本人沟通提出,或于本站点留言,我们会尽快处理。
在此,感谢ExcelTip.Net站长Apolloh的支持,感谢本站点所有人**绿色风(QQ:79664738)**的支持与奉献,特此鸣谢!
------本人网名**KevinChengCW(QQ:1210618015)**原ExcelTip.Net总版主之一