Excel笔记

发表于 2022年09月02日 · 归类于技术 · 阅读完需 6 分钟 ·

一、基础部分

（1）公式汇总

# FLOOR # 基数向下取整 CEILING # 基数向上取整
# ROUNDUP # 位数向上取整 ROUNDDOWN # 位数向下取整 INT # 向下取整 
# MOD   # 取余     LEN     # 长度

（2）快捷键汇总

# F2                 # 编辑当前单元格
# F12                # 另存为
# Ctrl+G             # 快速定位
# Ctrl+E             # 智能填充
# Ctrl+H             # 替换
# Ctrl+PgDn          # 右侧工作表
# Alt+=              # 快速求和

（3）求和

=SUM()

（4）平均数

=AVG()

（5）总数

=COUNT()

（6）条件总数

COUNTIF(A1:A10, ">20") #用来统计范围内的符合条件的数值量

（7）字符串截取

取字符串左边1个字符

=LEFT(B34,1)

字符串拼接

=CONCATENATE("张三","李四") #张三李四

二、高级部分

（1）身份证数据计算

取出生日期

=MID(A6,7,8) #522228199907201735->19990720

计算性别

=IF(MOD(MID(A6,17,1),2),"男","女") #522228199907201735->男

计算实际年龄

=DATEDIF(TEXT(MID(E2,7,8),"#-00-00"),TODAY(),"y") #522228199907201735->23

（2）时间数据转换

时间戳转换为标准时间格式

=(A1+8*3600)/86400+70*365+19

格式为（设置单元格格式-数字-自定义-类型）

yyyy"年"mm"月"dd"日"hh"时"mm"分"ss"秒"

（3）数据校验

校验K列有没有重复值（同列查重）

=IF(COUNTIF(A:A,A2&"*")>1,"重复","不重复")

校验K列有没有重复值（异列查重）

=IF(COUNTIF(A:A,B2&"*")>0,"重复","不重复")

校验身份证号码是否正确（A2单元格）

=IF(LEN(A2)=18,IF(RIGHT(A2,1)="X",IF(CHOOSE(MOD(SUM(LEFT(RIGHT(A2,18))*7+LEFT(RIGHT(A2,17))*9+LEFT(RIGHT(A2,16))*10+LEFT(RIGHT(A2,15))*5+LEFT(RIGHT(A2,14))*8+LEFT(RIGHT(A2,13))*4+LEFT(RIGHT(A2,12))*2+LEFT(RIGHT(A2,11))*1+LEFT(RIGHT(A2,10))*6+LEFT(RIGHT(A2,9))*3+LEFT(RIGHT(A2,8))*7+LEFT(RIGHT(A2,7))*9+LEFT(RIGHT(A2,6))*10+LEFT(RIGHT(A2,5))*5+LEFT(RIGHT(A2,4))*8+LEFT(RIGHT(A2,3))*4+LEFT(RIGHT(A2,2))*2),11)+1,1,0,"X",9,8,7,6,5,4,3,2)=LEFT(RIGHT(A2,1)),"正确！","出错啦！"),IF(CHOOSE(MOD(SUM(LEFT(RIGHT(A2,18))*7+LEFT(RIGHT(A2,17))*9+LEFT(RIGHT(A2,16))*10+LEFT(RIGHT(A2,15))*5+LEFT(RIGHT(A2,14))*8+LEFT(RIGHT(A2,13))*4+LEFT(RIGHT(A2,12))*2+LEFT(RIGHT(A2,11))*1+LEFT(RIGHT(A2,10))*6+LEFT(RIGHT(A2,9))*3+LEFT(RIGHT(A2,8))*7+LEFT(RIGHT(A2,7))*9+LEFT(RIGHT(A2,6))*10+LEFT(RIGHT(A2,5))*5+LEFT(RIGHT(A2,4))*8+LEFT(RIGHT(A2,3))*4+LEFT(RIGHT(A2,2))*2),11)+1,1,0,"X",9,8,7,6,5,4,3,2)=LEFT(RIGHT(A2,1))*1,"正确！","出错啦！")),IF(LEN(A2)=15,"老号，请注意！",IF(LEN(A2)=0,"缺号码","位数不对！")))

（4）行数判断

前后两人为一组

=IF(MOD(ROW()+1,2)=1,CONCATENATE(ROW()/2,"组：",B2,A2,";",B3,A3),"")

思路：判断行数是奇数还是偶数，是偶数则获取下一行的信息，是奇数则为空

（5）获取户主身份证号码

思路：当本人为户主姓名，则取户主身份证号码；当本人不是户主姓名，则取上一个人的户主的身份证号码

（6）字典匹配

LOOKUP

要求：1匹配一年级、2匹配二年级、3匹配三年级…

=LOOKUP(D2,{1,2,3,4,5},{"一年级","二年级","三年级","四年级","五年级"})

要求：60分以下为不及格、60~80分为及格、80~90分为良好、90~100分为优秀

=LOOKUP(A2,{0,60,80,90,101},{"不及格","及格","良好","优秀"})

（7）区间统计

统计学生分数段的人数：要求10分段内统计

（1）基于10向下取整 =FLOOR(A2,10)

（2）由大到小排序

（3）分类汇总：计数

（8）联表查询

VLOOKUP

=VLOOKUP(E2,A:B,2,0)

=VLOOKUP(G2,IF({0,1},A:A,D:D),2,FALSE)

MATCH