EXCEL函数：实操演示笔记汇总
身份证【提取】出生日期公式：
=MID(A1，7，8)
=RIGHT(LEFT(A1，14),8)
截取【字符串】长度:左侧字符串长度
=LEFT(A1，2)左侧字符串长度
=LEFT(A1，LEN(A1)-11)右侧字符串长度
=RIGHT(A2，11)
计算【平均值】公式：
=AVERAGE(A1:B1)
清除单元格字符串【空格】公式：
=SUBSTITUTE(TRIM(A1)," ","")
清除单元格字符串【换行】公式：
=CLEAN(A1)
计算单元格【字符数】公式：
=SUM(LENB(B1))
多个单元格【数据组合】公式：
统计EXCEL内容【重复出现次数】公式:
=COUNTIF(A:B,C1)
EXCEL【条件排序】公式：
=COUNTIF($B$1:B1,B1)
统计B1相同的数据在B列中出现的次数，在C列按照递增排序
自动按【数字大小排序】公式：
A列自动变成从小到大排列
=SMALL(A$2:A$28,ROW(1:1))
A列自动变成从大到小排列
=LARGE(A$2:A$28,ROW(1:1))
=RANK(A1,$A$1:$A$5)
=RANK(A1,A:A)
数列【自动加数】公式：
=A1&"000"&COUNTIF(A$1:A1,A1)
=TEXT(COUNTIF(A$1:A1,A1),"!"&A1&"00")
EXCEL【排名函数】公式：
A列是成绩,B列是排名
=IF(ISERR(RANK(M3,M:M)),"",RANK(M3,M:M))
=SUMPRODUCT((A$1:A$9>A1)/COUNTIF(A$1:A$9,A$1:A$9))+1
在函数中使用【通配符】：
EXCEL【跨工作表引用数据】公式：
A、B两个工作表
A列是名称、B列是编号、C列是数值三列
=A!A2 （=表名！单元格位置）
EXCEL【数据查找引用】公式：
A、B两个工作表
A列是名称、B列是数值 D列是名称、F列是数值
问：根据“D列名称”在A列中查找相同“名称”，将C列名称”对应的“F列数值”替换到C列中？
=VLOOKUP(A2,D:E,2,0)
注：
1、“A2”是查找值，就是要查找A2单元格的某个学号。
2、“SHEET2!$B:$D”是数据表，就是要在其中查找学号的表格，这个区域的首列必须是学号。
3、“2”表示我们最后的结果是要“D:E”中的第“2”列数据，从D列开始算第2列。
4、“FALSE”（可以用0代替FALSE）是匹配条件，表示要精确查找，如果是TRUE表示模糊查找。
EXCEL【跨表格引用+单/多条件计数】公式：
=COUNTIF('表'!D:D,"收录")
=SUMPRODUCT(('表'!$A$2:$A$100=$E$1)*('表'!$D$2:$D$100="收录"))
EXCEL【多单元格组合循环不重复】公式：
A、B两列
A列数值、B列数值
B列数值中数量增加，(ROW(A4),4)数字响应增加，则公式正常；
=INDIRECT("A"&INT(ROW(A4)/4))&INDIRECT("B"&MOD(ROW(A4),4)+1)
EXCEL【随机数表】公式：
C1输入公式，设置随机区间值，每双击+确认一次随机变化一次数值
=RANDBETWEEN(50,490)
EXCEL【行列转置】公式：
A列一组数值
B列按照3列一组转置成横向排列（如，B1-D1），下拉自动循环
=INDEX($A$1:$A$20,(ROW(A1)-1)*3+COLUMN(A1))
A列一组数值
B列按照A列对应编号，转置成横向排列
=OFFSET($B$1,COLUMN(A1)*3+ROW(A1)-4,0,1,1)
A为一组数值
C列根据A列前4行一组规律，转置成横向排列
=INDEX($A:$A,ROW($A1)+(COLUMN(A$1)-1)*3)
EXCEL【指定日期递增排序】公式：
A列按照5行一组，下拉自动递增+循环
=DATE(2016,12,5+INT((ROW(A1)-1)/5))
EXCEL【多条件查找求和】公式：
根据C列中数据，在A列查找相同数据，B列求和；
=SUMIF($A:$A,$D1,B:B)
EXCEL【行与列多条件查找引用】公式：
IFERROR函数判断[行与列]条件查找{是否}匹配,匹配则[引用数值]，不匹配则为[空];
根据$A$2:$G$6数组，MATCH($A10,$A2:$A6,)在行序数和MATCH(B$9,$2:$2,)列序数中查找[姓名]和[日期]，在[B10}中引用;
=IFERROR(INDEX($A$2:$G$6,MATCH($A10,$A2:$A6,),MATCH(B$9,$2:$2,)),"")
EXCEL【跨多表格+同位置+多行单元格+求和】公式：
=offsetreference,rows,cols,height,width)
=Offset（参照单元格，行偏移量，列偏移量，返回几行，返回几列）
=SUM(OFFSET('1'!D$4,ROW(1:1)*3-3,,3))+SUM(OFFSET('2'!D$4,ROW(1:1)*3-3,,3))+SUM(OFFSET('3'!D$4,ROW(1:1)*3-3,,3))+SUM(OFFSET('4'!D$4,ROW(1:1)*3-3,,3))+SUM(OFFSET('5'!D$4,ROW(1:1)*3-3,,3))+SUM(OFFSET('6'!D$4,ROW(1:1)*3-3,,3))
EXCEL【跨多表格+同位置（单行单元格）+求和】公式：
=SUM('1:6'!A1)
EXCEL-条件判断【单列条件判断+单行填充颜色】公式：
公式1=AND($C2<=TODAY(),$C2<>"") 小于等于当前日期，填充：红色
公式2=AND($C2>=TODAY(),$C2<TODAY()+30) 大于当前日期，小于当前日期+30天，填充：黄色
公式3=AND($C2>TODAY()+30,$C2<>"") 大于当前日期+30天，填充：绿色
EXCEL【单条件查找+隔列求和】公式：
公式 =SUMIF($C$2:$G$2,H$2,$C3:$G3)
逻辑：根据$C$2:$G$2定位“行范围”，H$2定位“行条件”，$C3:$G3定位求和“行范围”
EXCEL【日期自动化叠加】公式：
公式 =SUBSTITUTE(TEXT(SUBSTITUTE(B3,".","/")+7,"yyyy/m/d"),"-",".")
逻辑：根据B3日期，自动“+7”天后的日期
公式 =SUBSTITUTE(TEXT(SUBSTITUTE(B4,".","/")-6,"yyyy/m/d"),"-",".")
逻辑：根据B4日期，自动“-6”天后的日期
EXCEL【按周求和】公式：
公式=SUM(OFFSET(日报!D$3:D$9,(ROW(日报!A1)-1)*7,,7))
