Excel常用公式合集

这是一篇关于Excel常用公式合集的思维导图， Excel是大家每天都会接触到的东西，但对于Excel里面的基本概念和操作，又有多少人能够全部了解呢？看看下图。

常用公式合集

• 统计函数
  • vlookup
    • VLOOKUP(A1,B:C,2,0)
  • 满足多条件计数countifs
    • COUNTIFS(C:C,A1,D:D,B1)
    • 说明：求满足c列中含有A1，且满足D列中含有B1的个数
    • eg：A1=1，B1=2 则，同时满足c列中含1，且D列中含2的个数并求和
  • 满足多条件求和sumifs
    • sumifs(A:A,C:C,B1,D:D,F1)
    • 说明： 多个条件并返回基于这些条件的值 求(同时满足C列中含有B1,且D列中含有F1)的A列的和
    • eg：A列为1-10，C列为姓名（张三李四王五），D列为日期（1号，2号，3号） 则，满足张三1号对应A列数字的求和
• 转换类函数
  • 日期函数
    • 某日期提取 2021/12/23
      • 提取日
        • day(A1)
          • eg:23
      • 提取周
        • WEEKNUM(A1,2)
          • eg:52
      • 提取月
        • MONTH(A1)
          • eg:12
      • 提取年
        • year(A1)
          • eg:2021
      • 组合年月日
        • DATE(B2,B3,B1)
          • eg:2021/12/23
    • 计算某日期天数、月数、年数
      • 计算日
        • DATEDIF(开始日期,结束日期,"D")
          • eg:=DATEDIF(A1,A5,"D")
          • 说明：2021/12/24到2021/12/31,间隔7天（不包括24日）
      • 计算月
        • DATEDIF(开始日期,结束日期,"M")
          • 示例如日
      • 计算年
        • DATEDIF(开始日期,结束日期,"Y")
          • 示例如日
    • 计算某两个日期间的天数（刨除周六日）
      • NETWORKDAYS(开始日期,结束日期,1)
        • eg：=NETWORKDAYS(A1,A5,1)
        • 说明：2021/12/24到2021/12/31,间隔6天（包括24日，但不包括周六日）； 其中1代表周六日，2代表周日周一，可按个人需求更改；
    • now展示当前时间
      • now()
    • today展示当天日期
      • today()
  • 符号变换
    • 横杠变斜杠
      • SUBSTITUTE(L144,"-","/")
        • eg："2021-12-08 19:28"转换"2021/12/08 19:28"
      • SUBSTITUTE(LEFT(L144,10),"-","/")
        • eg："2021-12-08 19:28"转换"2021/12/08 "
• IF函数
  • 单条件判断返回值
    • =IF(A1>20,"完成任务","未完成")
    • 说明，如果A1大于20为完成任务，否则为未完成
  • 多重条件判断
    • =IF(A1="是","现金",IF(A1="否","应收票据"，IF(A1=123,"原材料")))
    • 说明：当A1为是则记为现金，当A1为否则记为应收票据，当A1为123则记为原材料
  • 多区间判断
    • =IF(A1<60,"不及格",IF(A1<80,"良好","优秀"))
    • =IF(A1>=80,"优秀",IF(A1>=60,"良好","不及格"))
    • 说明： A1<60，不及格 60<=A1<80，良好 A1>=80，优秀
  • 多条件并列判断
    • =IF(AND(A1>60,B1<100),"合格","不合格")
    • =IF(OR(A1>60,B1<100),"合格","不合格")
    • 注： and()表示括号内的多个条件要同时成立--且 or()表示括号内的多个条件任一个成立--或
  • 复杂的多条件判断
    • =IF(OR(AND(A1>60,B1<100),C1="是"),"合格","不合格")
      • 说明：满足（A1>60且B1<100）或满足C1=是，则合格，否则为不合格
    • =IF(AND(OR(A1>60,B1<100),C1="是"),"合格","不合格")
      • 说明：满足（A1>60或B1<100）且满足C1=是，则合格，否则为不合格
  • 判断后返回区域
    • =VLOOKUP(A1,IF(B1=1,C:D,F:G),2,0)
    • 说明：如果B1=1则v值选择范围是C:D，否则v值选择范围为F:G
  • if、vlookup组合
    • IF(VLOOKUP(A144,'!B:B,1,0)<>"",1,0)
    • 说明：如果满足（v某个值且该值不为空）则记为1，否则记为0
  • if、countifs组合
    • IF(COUNTIFS(mini!AP:AP,AP3)>=5,"点亮","未点亮")
    • 说明：如果满足（特定条件下计数且该数值>=5）则记为点亮，否则记为未点亮
  • 好长示例
    • =IF(AND(AD9="含餐饮",AG9="是",AH9="含热食",AI9="含预包装",AJ9="含自制饮品"),"A",IF(AND(AD9="含餐饮",AG9="是",AH9="含热食"),"B",IF(AND(AD9="含餐饮",AG9="否"),"C",IF(AND(AD9="不含餐饮",AG9="否"),"D","待补"))))
• 文本类函数
  • 清除某单元格内所有非打印字符
    • clean()
  • 判断该单元格内的字符长度
    • =len(A1)
    • eg：A1单元格内容为：今天天气真好！！ 说明：包括叹号在内，A1单元格内共有8个字符，即=len(A1)返回值为8
  • 单元格内-字符截取
    • 从左向右截取
      • =left（A1,3)
        • 说明：从左数取前三个字符 eg：A1=123456，则=left（A1,3)返回值为123
    • 从右向左截取
      • =right(A1,4)
        • 说明：从右数取前四个字符 eg：A1=123456，则=right（A1,4)返回值为3456
    • 从中间截取
      • =MID(A1,2,3)
        • 说明:从中间截取，从第二个字符开始从左到右取三个字符 eg：A1=123456，则=MID(A1,2,3)返回值为234
• 排行
  • 单一排行
    • RANK(G1,$G$1:$G$20,0)
    • 说明：G1单元格在G1-G20中的排行
  • 满足某条件下的排行
    • INDEX($D$1:$D$20,MATCH(LARGE($G$1:$G$20,1),$G$1:$G$20,0))
      • 说明：G1-G20单元格中第一大的数字，显示对应D1-D20的值
      • 示例： D1-D20单元格为张三、李四、王五； G1-G20单元格为12、16、8 则：最大数字为16，对应展示的姓名为李四
    • INDEX($D$1:$D$20,MATCH(LARGE($G$1:$G$20,2),$G$1:$G$20,0))
      • 说明：G1-G20单元格中第二大的数字，显示对应D1-D20的值
      • 同上述示例，第二大数字为12，对应展示的姓名为张三