王佩丰Excel 实战1800分钟, 1.Alt+； 选中可见单元格 2.Ctrl+； 输入当前日期 3.countif：=IF(COUNTIF($G$2:$G$14,A2)=0,"未体检","已体检") 4.条件格式：新建规则，使用公式，=COUNTIF($G$2:$G$14,A2)=0 5.查找18位数字重复：=COUNTIF($E$2:$E$14,E2&"*")>1 注：excel只能识别15位数字 6.禁止重复输入：=COUNTIF(C:C,C2)<2；=COUNTIF($D$2:$I$20,D3)<2 7.countifs：=COUNTIFS($E$2:$E$53,J8,$D$2:$D$53,I8) 8.sumif：=SUMIF(A:A,F3&"*",B:B) 注：excel只能识别15位数字 9.Sumif两重筛选求和可添加辅助列：=SUMIF(A:A,J5&K5,G:G) Sumifs多重筛选求和：=SUMIFS(G:G,E:E,J5,F:F,K5)（一个区域一个条件，成对出现） 10.出库单数据有效性：=SUMIF(F:F,F3,G:G)<=SUMIF(A:A,F3,B:B) 11.Vlookup查找，模糊匹配的时候只会查找与其近似的最大值，如：提成区间的查找； 将数值转成文本：=VLOOKUP(F4&"",$A$2:$C$6,3,0) 将文本转成数值：=VLOOKUP(F12*1,$A$10:$C$14,3,0)，=VLOOKUP(--F12,$A$10:$C$14,3,0) 文本数值交叉的情况，用ISNA判断是否会出现NA错误： =IF(ISNA(VLOOKUP(F20&"",$A$18:$C$22,3,0)),VLOOKUP(F20*1,$A$18:$C$22,3,0),VLOOKUP(F20&"",$A$18:$C$22,3,0)) 12.Vlookup漏洞，查找值只能从最左侧第一列，不能做从右向左，index和match联手可以解决：=INDEX(数据源!B:B,MATCH(A2,数据源!A:A,0)) Index（数据源，行数） Match（查找值，查找区域，0）返回行数 13.返回多列结果，及用match函数告诉vlookup的取值列的列数： =VLOOKUP($A3,数据源!$A:$J,MATCH(B$2,数据源!$A$1:$K$1,0),0) 14.Column（）返回当前单元格的列数 15.邮件合并 1）批量生成多个文档 2）利用word批量发送邮件 3）每页显示多条记录（插入项选择目录） 4）邮件合并的数字格式处理 数字格式 \#”#,##0.00” 日期格式 \@”M/d/yyyy”（M需要大写） 16.Date（y,m,d），year（日期），month（日期），day（日期） 时间间隔：Datedif（日期1，日期2，y/m/d/ym/yd/md） 17.Weeknum(日期,2) 第几周 Weekday（日期，2） 周几 18.将日期转成星期：Text（日期，”aaaa”） 将一串数字转成日期：text（参数，”0000-00-00”） 19.标记未来15天内过生日：=DATEDIF(C2,TODAY()+15,"YD")<15 20.判断身份证号的性别：=IF(MOD(RIGHT(LEFT(B13,17),1),2)=0,"男","女") 21.Left、right、len、lenb、find 取邮箱名：=LEFT(F2,FIND("@",F2)-1) 取邮箱域名：=RIGHT(F2,LEN(F2)-FIND("@",F2))，=MID(F2,FIND("@",F2)+1,100) 22.身份证取生日：=TEXT(RIGHT(LEFT(B2,14),8),"0000-00-00") =DATE(MID(B2,7,4)*1,MID(B2,11,2)*1,MID(B2,13,2)*1) 23.Round、roundup、rounddown、int取整、mod取余、 24.小数部分小于0.5取整、大于0.5取整加0.5： =IF(C2-ROUNDDOWN(C2,0)<0.5,ROUNDDOWN(C2,0),ROUNDDOWN(C2,0)+0.5) =IF(MOD(C2,1)<0.5,INT(C2),INT(C2)+0.5) =INT(C2*2)/2 25.复制、转置粘贴：=INDEX($A$1:$A$11,COLUMN()-2) 跳跃取值：=INDEX(E:E,(ROW()-1)*5+3) 分列：=INDEX($A:$A,COLUMN()+ROW()*3-10) 26.数组运算 =SUM(($B$2:$B$22=I15)*($C$2:$C$22=J15)*$E$2:$E$22)（ctrl+shift+enter 会出现大括号） =SUMPRODUCT(($B$2:$B$22=I15)*($C$2:$C$22=J15)*$E$2:$E$22) enter 不会出现大括号 27.Lookup 单条件：=LOOKUP(1,0/($A$2:$A$92=G4),$B$2:$B$92) 多条件：=LOOKUP(1,0/(($A$2:$A$13=I8)*($B$2:$B$13=J8)),$D$2:$D$13) 28.跳跃取值：indirect、index =INDEX(E:E,(ROW()-5)*5) 找到跳跃取值的规律 =INDIRECT("e"&(ROW()-5)*5) 创建引用地址 跨表引用中的表名的单引号不用管它：=INDIRECT(A4&"!G2") 29.名称管理器的使用：将一堆单元格组合成一个名字