对带有六个条件的图标集使用条件格式 [英] Using Conditional Formatting with Icon Sets with six conditions

问题描述

我正在使用条件格式，我已经使用条件格式几天了，但是我找不到想要的响应。

我希望根据输入的标记在单元格中显示一个彩色圆圈。但是问题是我有六个条件，但我认为Excel仅支持五个条件。这可能吗？

  0-20红色圆圈
 21-39绿色圆圈
 40-54蓝色圆圈
 55-64黄色圆圈
 65-79橙色圆圈
 80-100粉色圆圈
  

解决方案

如果您只能使用带有图标集的条件格式设置规则：

• ，如果您不必使用圆圈，则可以使用6条规则如果您需要在CF规则中使用4个以上的彩色圆圈，则可以按照下面的图像轻松设置




• ：

  
  
  

  ---

  
  
  

  注意：

  
  
  
  
  
  • VBA代码应与小数据一起使用
  
  
  • 它会生成大量形状，这会使所有其他操作变慢
  
  
  
  
  

  一个约有1,000行和20个列的测试：圈子总数 19,250 ；持续时间： 47.921875秒

  
  
  

  ---

  
  
  

  编辑：对进行了2次更新sub setIcon（）

  
  
  1. 自清洁
  
  

  2. 如果该单元格不包含错误，则仅处理数字值

     
     
     
     
     
     • 它排除文本为空的单元格单元格或日期
     
     
     • 感谢@EEM的建议
     
     
  
  

  
  

  I'm using Conditional Formatting, I've been playing around with Conditional Formatting for a couple of days but I can't get the response I'm looking for.

  I'm wanting a colored circle to appear in cell based on the marks entered. But the problem is I have six conditions but Excel supports only five I think. Is this possible?

  0-20  red color circle
  21-39 green color circle
  40-54 blue color circle
  55-64 yellow color circle
  65-79 orange color circle
  80-100 pink color circle
  

  解决方案

  If you are limited to Conditional Formatting rules with Icon Sets:

  • and if you don't have to have circles, your 6 rules can be easily set up as in the image bellow

  • if you need more than 4 colored circles in CF rules: Create Your Own Excel Icon Set

  If you can use VBA, the code bellow will create stylized circles similar to native CF circles

  • Open VBA: Alt + F11
  • Create a new module: menu item Insert > Module and paste the code
  • Click anywhere inside the first sub testIcons() and press F5 to run it

  ---

  Option Explicit
  
  Public Sub testIcons()
     Application.ScreenUpdating = False
     setIcon Sheet1.UsedRange
     Application.ScreenUpdating = True
  End Sub
  
  Public Sub setIcon(ByRef rng As Range)
     Dim cel As Range, sh As Shape, adr As String
  
     For Each sh In rng.Parent.Shapes
        If InStrB(sh.Name, "$") > 0 Then sh.Delete
     Next: DoEvents
     For Each cel In rng
        If Not IsError(cel.Value2) Then
           If Val(cel.Value2) > 0 And Not IsDate(cel) Then
             adr = cel.Address
             Set sh = Sheet1.Shapes.AddShape(msoShapeOval, cel.Left + 5, cel.Top + 2, 10, 10)
             sh.ShapeStyle = msoShapeStylePreset38: sh.Name = adr
             sh.Fill.ForeColor.RGB = getCelColor(Val(cel.Value2))
             sh.Fill.Solid
           End If
        End If
     Next
  End Sub
  
  Public Function getCelColor(ByRef celVal As Long) As Long
     Select Case True
        Case celVal < 21:    getCelColor = RGB(222, 0, 0):    Exit Function
        Case celVal < 40:    getCelColor = RGB(0, 111, 0):    Exit Function
        Case celVal < 55:    getCelColor = RGB(0, 0, 255):    Exit Function
        Case celVal < 64:    getCelColor = RGB(200, 200, 0):  Exit Function
        Case celVal < 80:    getCelColor = RGB(200, 100, 0):  Exit Function
        Case celVal <= 100:  getCelColor = RGB(200, 0, 200):  Exit Function
     End Select
  End Function
  

  ---

  ---

  Note:

  • The VBA code should be used with small data
  • It can generates a large number of shapes which will make all other operations slow

  A test with approx 1,000 rows and 20 cols: Total circles 19,250; duration: 47.921875 seconds

  ---

  Edit: made 2 updates to sub setIcon()

  1. Self-cleaning

  2. If the cell doesn't contain an error, it processes numeric values only

     • It excludes cells with text, empty cells, or dates
     • Thanks for the suggestion @EEM

  这篇关于对带有六个条件的图标集使用条件格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！