Excel 条件格式存储结构浅析

发表于 | 分类于 | | 浏览 次
字数统计: 1.4k | 阅读时长 ≈ 7

Excel条件格式

  • 在Excel表格中进行数据分析时，您可能需要通过一些特征条件来找到特定的数据，还可能需要用更直观的方法来展现数据规律。

手写公式的条件格式

需求

  • 原始数据如下

upload successful

  • 要求
    • 公司将进行一次内部晋升，需要将年龄为30岁以下的所有“研究员” 用蓝色背景标识出来
    • 公司将进行一次内部晋升，需要将年龄为25岁以下的所有“研究员” 用红色背景色标识出来
    • 公司将进行一次内部晋升，需要将年龄为20岁以下的所有“研究员” 用下划线 标识出来（探讨 如果为真则停止 这个勾选框）

效果

  • 一个条件

upload successful

  • 二个样式冲突的条件

upload successful

  • 样式不冲突的条件

upload successful

  • 如果为真则停止

upload successful

做了几个效果图方便理解 Excel中条件格式的各种行为和设置，下面我们看存储结构吧

存储

  • sheet.xml
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet
    xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
    xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
    xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"
    xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    xmlns:etc="http://www.wps.cn/officeDocument/2017/etCustomData">
    <sheetPr/>
    <dimension ref="A1:F33"/>
    <sheetViews>
        <sheetView tabSelected="1" workbookViewId="0">
            <selection activeCell="A2" sqref="A2"/>
        </sheetView>
    </sheetViews>
    <sheetFormatPr defaultColWidth="9" defaultRowHeight="13.5" outlineLevelCol="5"/>
    <cols>
        <col min="5" max="5" width="28.25" customWidth="1"/>
        <col min="6" max="6" width="48.75" customWidth="1"/>
    </cols>
    <sheetData>
        *****
    </sheetData>
    //sqref 应用位置 expression 表达式  formula 公式  priority 优先（越小越优先） stopIfTrue 是否	 //为真停止   dxfId 对应 styles.xml中的的 <dxfs /> 下的元素下标
    <conditionalFormatting sqref="A2:D13">
        <cfRule type="expression" dxfId="0" priority="5">
            <formula>($C2=&quot;研究员&quot;)*($D2&lt;30)</formula>
        </cfRule>
        <cfRule type="expression" dxfId="1" priority="4">
            <formula>($C2=&quot;研究员&quot;)*($D2&lt;25)</formula>
        </cfRule>
        <cfRule type="expression" dxfId="2" priority="1" stopIfTrue="1">
            <formula>($C2=&quot;研究员&quot;)*($D2&lt;20)</formula>
        </cfRule>
    </conditionalFormatting>
    <pageMargins left="0.75" right="0.75" top="1" bottom="1" header="0.5" footer="0.5"/>
    <headerFooter/>
</worksheet>

  • styles.xml

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    <?xml version="1.0" encoding="UTF-8" standalone="yes"?>
    <styleSheet
        xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main">
        <numFmts count="4">
            <numFmt numFmtId="44" formatCode="_ &quot;￥&quot;* #,##0.00_ ;_ &quot;￥&quot;* \-#,##0.00_ ;_ &quot;￥&quot;* &quot;-&quot;??_ ;_ @_ "/>
            <numFmt numFmtId="41" formatCode="_ * #,##0_ ;_ * \-#,##0_ ;_ * &quot;-&quot;_ ;_ @_ "/>
            <numFmt numFmtId="42" formatCode="_ &quot;￥&quot;* #,##0_ ;_ &quot;￥&quot;* \-#,##0_ ;_ &quot;￥&quot;* &quot;-&quot;_ ;_ @_ "/>
            <numFmt numFmtId="43" formatCode="_ * #,##0.00_ ;_ * \-#,##0.00_ ;_ * &quot;-&quot;??_ ;_ @_ "/>
        </numFmts>
        ******
        //条件格式的样式
        <dxfs count="3">
            <dxf>
                <fill>
                    <patternFill patternType="solid">
                        <bgColor rgb="FF00B0F0"/>
                    </patternFill>
                </fill>
            </dxf>
            <dxf>
                <fill>
                    <patternFill patternType="solid">
                        <bgColor rgb="FFFF0000"/>
                    </patternFill>
                </fill>
            </dxf>
            <dxf>
                <font>
                    <strike val="1"/>
                </font>
            </dxf>
        </dxfs>
        <tableStyles count="0" defaultTableStyle="TableStyleMedium2" defaultPivotStyle="PivotStyleLight16"/>
        <extLst>
            <ext uri="{EB79DEF2-80B8-43e5-95BD-54CBDDF9020C}"
                xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">
                <x14:slicerStyles defaultSlicerStyle="SlicerStyleLight1"/>
            </ext>
        </extLst>
    </styleSheet>

    上述两个 xml文件 存储结构对应 效果图 还是比较好理解的

Excel自带的条件格式

  • Excel中自己带有的部分简便的条件公式。可以探讨下它的存储结构

upload successful

  • 效果图

upload successful

  • 存储
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<worksheet
    xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"
    xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships"
    xmlns:xdr="http://schemas.openxmlformats.org/drawingml/2006/spreadsheetDrawing"
    xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main"
    xmlns:mc="http://schemas.openxmlformats.org/markup-compatibility/2006"
    xmlns:etc="http://www.wps.cn/officeDocument/2017/etCustomData">
    <sheetPr/>
    <dimension ref="A1:G22"/>
    <sheetViews>
        <sheetView workbookViewId="0">
            <selection activeCell="J11" sqref="J11"/>
        </sheetView>
    </sheetViews>
    <sheetFormatPr defaultColWidth="9" defaultRowHeight="13.5" outlineLevelCol="6"/>
    *********
    
    
    // sqref 应用位置 type:dataBar和 colorScale 应该是Excel自己带的两个类型
    <conditionalFormatting sqref="A2:D13">
        <cfRule type="dataBar" priority="2">
            <dataBar>
                <cfvo type="min"/>
                <cfvo type="max"/>
                <color rgb="FF638EC6"/>
            </dataBar>
            <extLst>
                <ext uri="{B025F937-C7B1-47D3-B67F-A62EFF666E3E}"
                    xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">
                    <x14:id>{be709f9a-bb79-4f30-badf-ed91d38783ef}</x14:id>
                </ext>
            </extLst>
        </cfRule>
        <cfRule type="colorScale" priority="1">
            <colorScale>
                <cfvo type="min"/>
                <cfvo type="percentile" val="50"/>
                <cfvo type="max"/>
                <color rgb="FFF8696B"/>
                <color rgb="FFFFEB84"/>
                <color rgb="FF63BE7B"/>
            </colorScale>
        </cfRule>
    </conditionalFormatting>
    <pageMargins left="0.75" right="0.75" top="1" bottom="1" header="0.5" footer="0.5"/>
    <headerFooter/>
    //这里也有一些扩展属性
    <extLst>
        <ext uri="{78C0D931-6437-407d-A8EE-F0AAD7539E65}"
            xmlns:x14="http://schemas.microsoft.com/office/spreadsheetml/2009/9/main">
            <x14:conditionalFormattings>
                <x14:conditionalFormatting
                    xmlns:xm="http://schemas.microsoft.com/office/excel/2006/main">
                    <x14:cfRule type="dataBar" id="{be709f9a-bb79-4f30-badf-ed91d38783ef}">
                        <x14:dataBar minLength="0" maxLength="100" border="1" negativeBarBorderColorSameAsPositive="0">
                            <x14:cfvo type="autoMin"/>
                            <x14:cfvo type="autoMax"/>
                            <x14:borderColor rgb="FF638EC6"/>
                            <x14:negativeFillColor rgb="FFFF0000"/>
                            <x14:negativeBorderColor rgb="FFFF0000"/>
                            <x14:axisColor rgb="FF000000"/>
                        </x14:dataBar>
                    </x14:cfRule>
                    <xm:sqref>A2:D13</xm:sqref>
                </x14:conditionalFormatting>
            </x14:conditionalFormattings>
        </ext>
    </extLst>
</worksheet>

这里看存储结构，就没有 手写公式那么的清晰明了。涉及到Excel程序的内容就过多了