Excel技巧合集

=SUMIF(A:A, A2, B:B) 

在A列中查找与A2单元格相同的值，然后对B列中对应的值进行求和。

删除单元格后，引用这个单元格的数值会更新为新数值

=SUM(INDIRECT("A1"))

 

校验身份证是否合法的公式

=IF(LEN(A2)<>18,“非二代身份证”,IF(MID(“10X98765432”,(MOD(SUMPRODUCT(MID(A2,ROW(INDIRECT(“1:17”)),1)*{7;9;10;5;8;4;2;1;6;3;7;9;10;5;8;4;2}),11)+1),1)=RIGHT(A2),“对”,“错”))

 

为合并单元格编号

选中所有合并单元格，按=号输入公式：=MAX(A$1:A1)+1 a1是当前格子上面那个格子的坐标

 

计算各单位平均回复时间

=SUMIF($D:$D,"市教育局",$I:$I)/COUNTIF($D:$D,"市教育局")

=SUMIF($D:$D,D2,$I:$I)/COUNTIF($D:$D,D2)

 

excel工作表计数

=INFO("numfile")

=SUBTOTAL(3,$T$2:T2)

 

多条件筛选

=COUNTIFS(Sheet1!$D:$D,"*A*",Sheet1!$E:$E,"*方亭*")

 

计算年龄

=DATEDIF(I6,"2020-3-20","y")

=DATEDIF(TEXT(MID(H3,7,8),"0000-00-00"),TODAY(),"Y")

=DATEDIF(D35,TODAY(),"Y")&"岁"&DATEDIF(D35,TODAY(),"YM")&"个月"&DATEDIF(D35,TODAY(),"MD")&"天"

 

身份证提取性别

=IF(MOD(MID(A1,17,1),2),"男","女")

 

身份证提取出生年月日

=TEXT(MID(I2,7,8),"0000-00-00")

 

excel 从出生日期算年龄的公式

DATEDIF(A1,TODAY(),"y")

 

在sheet3的f1:g16这个范围内查找本表的g1，如果找到就返回范围内第2列的值，0：不需要精确匹配。

=VLOOKUP(G1,Sheet3!$F$1:$G$16,2,0)

 

数字前面加0

C2输入=A2&TEXT(B2,"0000")并向下填充.

=CONCATENATE(LEFT(A1,2),MID(A1,4,3),RIGHT(A1,3))

 

excel 保护部分单元格

1先ctrl+1全选单元格 设置单元格格式 保护 去掉保护的勾

2选中需要保护的单元格，把保护的勾打上

3点“保护工作表” 按钮，不允许选中被锁定的单元格