发布时间 : 星期一 文章Excel2007函数公式收集了688个实例更新完毕开始阅读0944df5b650e52ea5518984c
=SUBSTITUTE(REPLACE(CELL(\EPLACE(CELL(\存放目录=REPLACE(CELL(\
提取第一次参赛取得最佳成绩者姓名与成绩:参赛者{=INDEX(A:A,MOD(MAX((IF(NOT(ISBLANK(C2:C11)),MATCH(A2:A11,A:A,0))=ROW(2:11))*C2:C11*100+ROW(2:11)),100))};成绩{=MAX((IF(NOT(ISBLANK(C2:C11)),MATCH(A2:A11,A:A,0))=ROW(2:11))*C2:C11)}
计算哪一个项目得票最多:{=INDEX({\N(IF(ISBLANK(B2:B11),\
根据利率、存款与时间计算存款加利息数:=FV(B2,D2,-C2,0)
计算七个投资项目相同收益条件下谁投资更少:{=MAX(PV(B2:B8,C2:C8,0,100000))}
根据利息和存款数计算存款达到1万元需要几个月:=NPER(A2,0,-B2,C2)*12 根据投资金额、时间和目标收益计算增长率:=RATE(B2,0,-A2,C2)
根据贷款、利率和时间计算某段时间的利息:=CUMIPMT(B2/12,C2*12,A2,1,24,0)
根据贷款、利率和时间计算需偿还的本金:=CUMPRINC(B2/12,C2*12,A2,1,24,0)
以固定余额递减法计算资产折旧值:=DB(A$2,B$2,C$2,ROW(A1),12) 以双倍余额递减法计算资产折旧值:=DDB(A$2,B$2,C$2,1,2) 以年限总和折旧法计算折旧值:=SYD(A$2,B$2,C$2,ROW(A1))
使用双倍余额递减法计算任何期间的资产折旧值:=VDB(A$2,B$2,C$2*12,7,12,2)
获取当前工作簿中工作表数量:=COLUMNS(sheets)&T(NOW()) 建立工作表目录与超级链接:=IFERROR(HYPERLINK(INDEX(sheets,ROW(A1))&\sheets,ROW(A1))&T(NOW()),1,FIND(\
选择最后工作表的最后非空单元格:=HYPERLINK(INDEX(sheets,COLUMNS(sheets))&\RECT(INDEX(sheets,COLUMNS(sheets))&\
引用单元格数据同时引用格式:=IF(TODAY()>A2,\格式))
分别汇总当前表以外的所有工作表数据:AcSht=GET.CELL(62);sheets=GET.WORKBOOK(1);WorkBook=GET.CELL(66);{=IFERROR(REPLACE(INDEX(sheets,SMALL(IF(TRANSPOSE(sheets)<>AcSht,ROW(INDIRECT(\),\
提取单元格的公式:名称=GET.CELL(6,Sheet1!$B1)&T(NOW())
罗列工作簿中所有名称:{=IFERROR(INDEX(名称,SMALL(IF(名称<>\名称\名称))))),ROW(A1))),\
在任意单元格显示当前页数及总页数:无拘无束的页眉=\第\横向当前页=1,纵向当前页,横向当前页+纵向当前页)&\页/共\总页&\页\纵向当前页=IFERROR(MATCH(ROW(),GET.DOCUMENT(64))+1,1)
提取单元格中的批注:批注=GET.OBJECT(12, \备注 1\
利用列表框筛选数据:筛选=IF(GET.OBJECT(78,\列表框 1\列表框 1\
判断单元格是否被图形对象覆盖:=ADDRESS(ROW(INDIRECT(左上,0)),COLUMN(INDIRECT(左上,0)))&\右下,0)),COLUMN(INDIRECT(右下,0)))
将单元格的公式转换成数值:计算=EVALUATE(Sheet1!A3) 将IP地址补足三位:IP地址=TEXT(EVALUATE(\T(EVALUATE(\
按分隔符取数并求平均:成绩=EVALUATE(SUBSTITUTE(\\
根据产品规格计算体积:体积=EVALUATE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(Sheet1!XFD4,\（L）\（W）\（H）\
计算减肥前后的三国差异:后=EVALUATE(\前=EVALUATE(\
计算各楼层空佘面积:面积=EVALUATE(SUBSTITUTE(SUBSTITUTE(Sheet1!XFD1,\))
将数据分列，提取省市县:分列=EVALUATE(\省\省\市\市\
按图书编号汇总价格:图书=EVALUATE(\
标识B列中的重复值:条件格式:=COUNTIF($B:$B,B1)>1
将数据间隔着色:条件格式:=MOD(SUM(N($B$2:$B2<>$B$1:$B1)),2)=0 隐藏错误值:单元格的公式:=VLOOKUP(A2,单价表!$A$2:$B$10,2,0)，条件格式:=ISERROR(B1)
突显前三个最大值:条件格式:=B2>LARGE($B$2:$F$10,4)
将成绩高于平均值的姓名标示“优等”:条件格式:=(B2>AVERAGE($B$2:$F$10))*MOD(COLUMN(),2)
突显奇数行:条件格式:=ISODD(ROW())
突显非数值:条件格式:=NOT(ISNUMBER(A2))*ISEVEN(COLUMN()) B列中禁止输入重复数据:数据有效性设置-自定义:=COUNTIF(B:B,B8)=1 仅允许录入英文姓名:数据有效性设置-自定义:=SUM(--(ERROR.TYPE(INDIRECT(MID(SUBSTITUTE(A2,\\\
强制录入规范化的日期:数据有效性设置-自定义:=(LEN(A2)=8)*TEXT(A2,\
建立动态下拉选单:定义名称:水果=OFFSET(单价表!$A$1,,,COUNTA(单价
表!$A:$A))
建立二级下拉选单:定义名称:省=OFFSET(参考区!$A$1,,,,COUNTA(参考区!$1:$1));市=OFFSET(参考区!$A$1,1,MATCH(Sheet1!$A$2,参考区!$1:$1,0)-1,COUNTA(OFFSET(参考区!$A$1,1,MATCH(Sheet1!$A$2,参考区!$1:$1,0)-1,1048575)))
建立不重复的下拉选单:{=INDEX(A:A,SMALL(IF(COUNTIF(Sheet1!A$1:A$8,A$1:A$8)=0,ROW($1:$8),1048576),ROW(A2)))&\(生成不重复单位);定义名称:=OFFSET(名单!$B$1,,,8-COUNTBLANK(名单!$B$1:$B$8))
让A列只能输入质数:数据有效性设置-自定义:=OR(A2=2,A2=3,PRODUCT(MOD(A2,ROW(INDIRECT(\INT(A2^0.5))))))
设置D列只能录入男职工的姓名:数据有效性设置-自定义:=VLOOKUP(D2,A:B,2,0)=\男\
禁止录入不完整的产品规格:数据有效性设置-自定义:=ISNUMBER(SEARCH(\长?*宽?*高?*\
自动记录进库时间:=IF(ISBLANK(B2),\记录历史最高值:=MAX(B:B,D2) 解一元二次方程:X+100=X^2+10
解二元一次方程:X=10X=(100-5Y)/25,Y=Y/5=(200 +4X)/4 从身份证号码提取出生日期、性别、年龄 从身份证号中提取出生日期：
=IF(LEN(A1)=15,\MID(A1,7,4)&\
从身份证号中提取性别：
=IF(MOD(IF(LEN(A1)=15,MID(A1,15,1),MID(A1,17,1)),2)=0,\女\男\用出生日期计算年龄：
=CONCATENATE(DATEDIF(B2,TODAY(),\年\个月\
由于我们用上面的公式，将“出生日期”提取到B2单元格，所以这里的公式是通过操作B2单元格的数据来得到年龄的。
函数DATEDIF是计算两个日期或时间的差值，通过第三个参数来确定所要的结果，比如“y”会得到两个日期相差的年；“ym”会得到除年外，所余的月数。 TODAY()函数的作用是得到当前系统日期，即电脑上今天的日期。
如果只需要年，可删除“年”后的公式，也可将“年”改成“岁” 最终结果见下图：
如果从网上找到前6位数所代表的地区代码列表，然后用VLOOKUP函数引用，就可以自动得到各员工的居住城市信息了.
将两列合并为一列： =A2&B2 从单元格中提取部分字段：
如从： “祥龙乡杜家坪村1组2号”提取“祥龙乡杜家坪村1组”： =REPLACE(A1,10,LEN(LEFT(A1,FIND(\组\如只提取“祥龙乡”： =REPLACE(A1,4,LEN(LEFT(A1,FIND(\组\如提取“组”以后的数据：=REPLACE(A1,1,LEN(LEFT(A1,FIND(\组\“1”是提取选择字段以后的数据，如改成N则是提取从数据开头的第N-1个数字。
查找两列或两表中的相同数据：
方法一：查找A2在B列中是否有相同项：=VLOOKUP(A2,B:B,1,0) 查找B2在Sheet1表（另一表格）B列中是否有相同项： =VLOOKUP(B2,Sheet1!B:B,1,0)
有则显示相同的数据，无则会显示“#N/A”
方法二：查找B2在Sheet1表（另一表格）B列中是否有相同项：
=IF(ISNA(MATCH(B2,Sheet1!B:B,0)),\相同\
查找B2在Sheet1表（另一表格）B列2-350行中是否有相同项： =IF(ISNA(MATCH(B2,Sheet1!$B$2:$B$350,0)),\相同\
有则会显示相同，无则不会显示
方法三：查找B2在Sheet1表（另一表格）B列中是否有相同项：
=IF(COUNTIF(Sheet1!B:B,B2)>0,\有\个相同出现\
查找B2在Sheet1表（另一表格）B列2-350行中是否有相同项：=IF(COUNTIF(Sheet1!$B$2:$B$350,B2)>0,\有\个相同出现\
有则会显示有几个相同，无则不会显示