V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
MrUser
V2EX  ›  程序员

COUNTIF(range, A or B) 该怎么写?

  •  1
     
  •   MrUser · 2019-03-04 17:16:40 +08:00 · 2337 次点击
    这是一个创建于 1851 天前的主题,其中的信息可能已经有所发展或是发生改变。
    要求:统计包含 A 或 B 的行数:
    1. A
    2. AB
    3. B
    --------
    正确结果应该是 3,以下两种方法都会导致第 2 行的“ AB ”被重复计算(错误结果“ 4 ”):
    =SUM(COUNTIF(range, {"A", "B"}))
    =COUNTIF(range, "A") + COUNTIF(range, "B")
    第 1 条附言  ·  2019-03-05 09:14:41 +08:00

    我的实际场景是统计员工出勤天数,数据如下:

    需要统计:除了”假、休“外的”√、外出、加班“的天数,”√、外出、加班“都算正常出勤

    注:

    √:正常出勤,
    差:出差,
    外:外出,
    假:请假,
    班:加班,
    休:当天休息了,
    √差:”虽然出差但是打卡了“(非正常数据,正常数据只有一个字符,要么“√”,要么“差”),
    差外:”同一天同时提了出差和外出的申请,还通过审批了“(又是不正常的数据)
    

    我目前的解决办法是:

    =COUNTIF(range1,"*√*")+SUM(COUNTIF(range1,{"外","差","班"}))
    

    意思是统计所有含有”√“的单元格,再加上只有”外“字、只有”差“字、只有”班“字的单元格,

    BUG就是单元格同时有两个汉字时,这个单元格就统计不到了。

    理想的方法是:

    =COUNTIF(range1,"*[√,外,差,班]*") // 匹配数组中任意一个元素即可,可惜 wps 和 ms-office 都不支持
    

    其它方法:

    1. UDF(用户自定义函数)这个需要Excel软件的高级功能,没钱买
    2. 程序读取xls 文件,处理完再生成 xls

    谁有其它简单方便的解决方案么?

    简单方便:不安装第三方工具、使用软件现有的功能、文件发给其他人时不需要另外配置。

    12 条回复    2019-03-05 15:14:23 +08:00
    elfive
        1
    elfive  
       2019-03-04 18:21:11 +08:00 via iPhone
    COUNTIF A + COUNTIF B- COUNTIF AB
    MrUser
        2
    MrUser  
    OP
       2019-03-04 18:38:45 +08:00
    有通用些的写法吗?如果有记录有 BA、ABC、A,C、AABB 等不确定的数据时候该怎么办?
    @elfive
    PANWCS
        3
    PANWCS  
       2019-03-04 18:40:02 +08:00
    @MrUser 这已经是另一个问题了,XD
    MrUser
        4
    MrUser  
    OP
       2019-03-04 18:44:00 +08:00
    结合一下我的大标题哈,问题的重点在“或”上,我已经从 Google 回来几次了,一直找不到解决方法
    看来只能读到程序里,处理完再重新生成 xls 了……
    @PANWCS
    no1xsyzy
        5
    no1xsyzy  
       2019-03-04 20:09:39 +08:00   ❤️ 1
    =COUNTIF(A1:A3, ".*[AB].*")

    LibreOffice Calc:工具 - 选项 - LibreOffice Calc - 计算 -- 允许公式中含有正则表达式。
    krixaar
        6
    krixaar  
       2019-03-05 08:06:59 +08:00
    旁边写上
    A 1
    B 1
    然后新增一列,VLOOKUP,模糊匹配 false,IFERROR/IFNA 为 0
    然后 SUM

    或者旁边写上
    A
    B
    然后新增一列,IF(ISERROR(VLOOKUP)),0 和 1
    然后 SUM

    关键词变多了就接着往下面加
    krixaar
        7
    krixaar  
       2019-03-05 08:08:08 +08:00
    @krixaar 不对看错题了,请无视我……
    krixaar
        8
    krixaar  
       2019-03-05 08:14:12 +08:00   ❤️ 1
    和上面思路差不多,新增一列,SEARCH 关键字,然后 ISERROR 判断有没有包含关键字,OR 判断是不是满足任一个关键字,根据 OR 的结果 IF 设置有则 1 无则 0,SUM 新增的这一列。
    no1xsyzy
        9
    no1xsyzy  
       2019-03-05 13:36:10 +08:00
    使用辅助列,随后隐藏该列
    为了可扩展性可以将辅助列设为很后面的 YA, YB,... ,然后把正常列的 最后一列之后一列 到 辅助列最后一列 都隐藏

    以图为例
    设 “姓名” 在 A1
    YA2 = IF(AND(ISERROR(SEARCH("a",B2)),ISERROR(SEARCH("b",B2))), 0, 1)
    向右、下拖,共 28 列 2 行
    然后 AD2 = SUM(YA2:ZB2)
    no1xsyzy
        10
    no1xsyzy  
       2019-03-05 13:40:11 +08:00   ❤️ 1
    哦发现还是用着 a、b,这个你会改了
    大学的时候为了凑学分选的 Excel,Tutorial 一堆隐藏列解决

    还有一找叫 VBA,其实这个比较通用。
    fuxinya
        11
    fuxinya  
       2019-03-05 15:10:58 +08:00
    按位与或操作可行?
    例如,A 为 001,B 为 010,C 为 100 (二进制表示)
    SELECT xx FROM table WHERE xx & mask > 0
    其中,mask 为掩码,如果你想查询符合 A 或 B 的,那么掩码设为 011 (即十进制的 3 )
    题目没细看,不符合要求请无视
    fuxinya
        12
    fuxinya  
       2019-03-05 15:14:23 +08:00   ❤️ 1
    好吧,看错,是 excel 统计,告辞。
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   3487 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 29ms · UTC 11:00 · PVG 19:00 · LAX 04:00 · JFK 07:00
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.