Excel 统计数据（去除重复）

很久没有更新博客，最近用 Excel 次数越来越多，也总结了一些用法。网上信息很多，良莠不齐，因此我将自己的案例写在这里，以供网友参考。

countifs 的用法剖析

Excel 中的 countifs 函数功能很强大，公式是 countifs(criteria_range1,criteria1,criteria_range2,criteria2,…) 这样的。也就是说可以进行多个条件进行限制，换成中文可以理解为 countifs(检索区域1,检索条件1, 检索区域2,检索条件2,…)。

不过，如果统计的数据中有重复的条目，那就没有这么简单了。

=SUM(
IFERROR(
    1/COUNTIFS(
        检索区域1,"检索词1",
        检索区域2,"*检索关键词2",

        检索区域3（该区域作为UID可以去重）,检索区域3)*

    (检索区域1="检索词1")*
    (RIGHT(检索区域2)="检索关键词2")
,0))

根据网友介绍，COUNTIFS负责统计出现的次数，但是不负责是否存在重复，因此需要将每一个限定条件都通过*链接进行限定，如上述公式需要增加分子条件，改为(已有统计公式)*(检索区域1="检索词1")*(RIGHT(检索区域2)="检索关键词2") 才能避免因统计表数据重复填写导致的重复统计。

上述检索，当然用 SUMPRODUCT 也是可以的。例如

=SUMPRODUCT(IFERROR((检索区域1="检索词1")*
    (RIGHT(检索区域2)="检索关键词2")/
    COUNTIFS(检索区域1,"检索词1",
        检索区域2,"*检索关键词2",

        检索区域3,检索区域3)

,0))

对于检索词，可能存在多种情况，这里也进行统计一下：

1.完全匹配，使用=直接匹配，就是 检索区域1,"检索词1" 和 (检索区域1="检索词1")。
2.部分匹配，需要使用ISNUMBER + SEARCH 函数，就是 检索区域2,"检索关键词2" 和 (ISNUMBER(SEARCH("检索关键词2",检索区域2)))。
- 如果关键词在首端、末端，则可以使用 left、right函数，例如 检索区域2,"*检索关键词2"和 (RIGHT(检索区域2)="检索关键词2")。
- 如果关键词是数值范围，那么使用 <、<=、>、>= 进行匹配。如检索区域2,"<="&DATE(YYYY,M,D)和 (检索区域2<=DATE(YYYY,M,D))*。

如果同一个检索区域需要检索多个条件呢，那么可以使用多个 ISNUMBER + SEARCH 函数叠加，如下：

=SUM(
IFERROR(
    1/COUNTIFS(
        检索区域1,"检索词1",
        检索区域2,{"*检索关键词2.1*","*检索关键词2.2*","*检索关键词2.3*"},

        检索区域3,检索区域3)*

    (检索区域1="检索词1")*
    (ISNUMBER(SEARCH("检索关键词2.1", 检索区域2)) + 
     ISNUMBER(SEARCH("检索关键词2.2", 检索区域2)) + 
     ISNUMBER(SEARCH("检索关键词2.3", 检索区域2)) 
     > 0),
,0))

countifs 的用法示例

countifs计算统计表

以上表为例，那么如果要统计A列公司1、F列授权、D列实用新型和发明专利，并根据B列专利名称排除重复填报的条目，公式应当如下（数组公式，需要 Ctrl + Shift + Enter 录入生效）：

{
=SUM(IFERROR(
    1/COUNTIFS(
        INDIRECT("专利!$A2:$A"&F6),"*"&$C6&"*",
        INDIRECT("专利!$D2:$D"&F6),{"发明","实用新型"},
        INDIRECT("专利!$F2:$F"&F6),"授权",
        INDIRECT("专利!$B2:$B"&F6),INDIRECT("专利!$B2:$B"&F6)
    )*
    (INDIRECT("专利!$A2:$A"&F6)=C6)*
    (INDIRECT("专利!$D2:$D"&F6)={"发明","实用新型"})*
    (INDIRECT("专利!$F2:$F"&F6)="授权"),
    0))
}

或者：

{
=SUMPRODUCT(IFERROR(
    1/COUNTIFS(
        INDIRECT("专利!$A2:$A"&F7),"*"&$C7&"*",
        INDIRECT("专利!$D2:$D"&F7),{"发明","实用新型"},
        INDIRECT("专利!$F2:$F"&F7),"授权",
        INDIRECT("专利!$B2:$B"&F7),INDIRECT("专利!$B2:$B"&F7)
    )*
    (INDIRECT("专利!$A2:$A"&F7)=C7)*
    (INDIRECT("专利!$D2:$D"&F7)={"发明","实用新型"})*
    (INDIRECT("专利!$F2:$F"&F7)="授权"),
    0))
}

如果要分年度统计，或者多个条件统计呢，可以具体看一下附件中的案例：

countifs计算源表