Excel函数公式的组合应用实例精选

实例分类实例细化公式1公式2公式3公式4公式5公式6公式7公式8公式9公式10重复值筛选公式1单个区域排名多个区域排名中国式排名身份证号码提取资料身份证号码提取资料考场安排考场安排单元格提取字符01单元格提取字符02单元格提取字符03单元格提取字符04单元格提取字符05单元格提取字符06单元格提取字符07查找对应单元格位置查找对应单元格位置工资表工资条01工资条02工资条03生产制造单制造单生成生产记录单部品编码生成双级下拉菜单双级下拉菜单多条件查询多条件查询工资条生成单元格字符提取数据的小写大写转换目标：  掌握函数的组合应用排名总汇EXCEL中级教程大纲主要应用函数IF.CONCATENATE.TEXT.INT.ABSIF.TEXT,INT.ABS.ROUNDIF.TEXT,.TRUNC.ROUND.RIGHTCONCATENATE.TEXT.INT.IF.AND.CEILING.OR.FIXEDIF.AND.ISERR.FIND.TEXT.INT.SUBSTITUTEIF.TEXT.TRUNC.ABS.ROUND.ISERR.FIND.RIGHT.LEFTIF.TRUNC.AND.ISERR.FIND.TEXT.SUBSTITUTE.ABSIF.AND.INT.NUBSTRINGIF.ROUND.TEXT.INTSUBSTITUTE.IF.TEXT.TRUNC.ABS.ROUND.ISERR.FIND.RIGHT.LEFTINDEX.MATCH.COUNTIFRANK.COUNTIF.SUM.RANKCOUNTIF.FREQUENCY.SUM.IFIF.TEXT.LEN.MID.MOD.RIGHT.LEFT.DATEDIF.TODAY.REPLACE.SUM.ROW.INDIRECT.DATE.OR.VLOOKUPTEXT.INDIRECT.ADDRESS.ROW.COLUMNMID.SMALL.IF.ROW.COLUMNMID.CHOOSE.FINDMID.FIND.COUNT.ROWMID.LENB.LENLOOKUP.RIGHT.LEFT.LEN.ROWTRIM.MID.SUBSTITUTE.REPT.ROWSEARCH.TRIM.LEFT.MID.LENADDRESS.MATCH.MMULT.ROW.TRANSPOSE.NSUMIF.MOD.ROW.INDEX.COLUMNOFFSET.MOD.ROW.COLUMN.ROUNDOFFSET.CHOOSE.MOD.ROW.COLUMN.ROUNDVLOOKUP.IF.ROW.COUNTIF.INDEX.SMALL.COLUMNIF.ROW.MATCH.COUNTIF.LOOKUP.VLOOKUP.INDEXOFFSET.MATCH.COUNTIF.IF.INDEXLOOKUP.VLOOKUP.IF目标：  掌握函数的组合应用中级教程大纲公式七比较完美公式一公式二104000壹拾万肆仟元零角零分壹拾万肆仟元整5487.32伍仟肆佰捌拾柒元叁角贰分伍仟肆佰捌拾柒元叁角贰分459621.09肆拾伍万玖仟陆佰贰拾壹元零角玖分肆拾伍万玖仟陆佰贰拾壹元零玖分-12000.08负壹万贰仟元零角捌分负壹万贰仟元零捌分-125.36负壹佰贰拾伍元叁角陆分负壹佰贰拾伍元叁角陆分8.9捌元玖角零分捌元玖角整0.12零元壹角贰分壹角贰分100001壹拾万零壹元零角零分壹拾万零壹元整100001壹拾万零壹元零角零分壹拾万零壹元整108000壹拾万捌仟元零角零分壹拾万捌仟元整0负零元零角零分零元整公式八公式九NUMBERSTRING函数TEXT函数及[dbnum2]格式104000壹拾万肆仟元整壹拾万肆仟元整5487.32伍仟肆佰捌拾柒元叁角贰分伍仟肆佰捌拾柒元叁角贰分459621.09肆拾伍万玖仟陆佰贰拾壹元零玖分肆拾伍万玖仟陆佰贰拾壹元零玖分12.08壹拾贰元零捌分壹拾贰元零捌分-125.36肆分无效数值无效数值8.9捌元玖角整捌元玖角整0.12壹角贰分壹角贰分100001壹拾万零壹元整壹拾万零壹元整100001壹拾万零壹元整壹拾万零壹元整108000壹拾万捌仟元整壹拾万捌仟元整上一页返目录公式三公式四壹拾万肆仟元整壹拾万肆仟元整(￥104000.00元)伍仟肆佰捌拾柒元叁角贰分伍仟肆佰捌拾柒元叁角贰分(￥5487.32元)肆拾伍万玖仟陆佰贰拾壹元零角玖分肆拾伍万玖仟陆佰贰拾壹元玖分(￥459621.09元)负壹万贰仟元零角捌分-壹万贰仟零壹元玖角贰分(￥-12000.08元)负壹佰贰拾伍元叁角陆分-壹佰贰拾陆元陆角肆分(￥-125.36元)捌元玖角整捌元玖角整(￥8.90元)零元壹角贰分零元壹角贰分(￥0.12元)壹拾万零壹元整壹拾万零壹元整(￥100001.00元)壹拾万零壹元整壹拾万零壹元整(￥100001.00元)壹拾万捌仟元整壹拾万捌仟元整(￥108000.00元)零元整零元整(￥0.00元)公式十SUBSTITUTE、TEXT函数及[dbnum2]格式壹拾万肆仟元整伍仟肆佰捌拾柒元叁角贰分肆拾伍万玖仟陆佰贰拾壹元零玖分壹拾贰元零捌分负壹佰贰拾伍元叁角陆分捌元玖角整零壹角贰分壹拾万零壹元整壹拾万零壹元整壹拾万捌仟元整下一页公式五(较完整)昴日星君提供公式六xpm_200提供壹拾万零肆仟元整壹拾万肆仟元整伍仟肆佰捌拾柒元叁角贰分伍仟肆佰捌拾柒元叁角贰分肆拾伍万玖仟陆佰贰拾壹元零玖分肆拾伍万玖仟陆佰贰拾壹元零玖分-壹万贰仟零壹元玖角贰分负壹万贰仟元零捌分-壹佰贰拾陆元陆角肆分负壹佰贰拾伍元叁角陆分捌元玖角整捌元玖角整零元壹角贰分零元壹角贰分壹拾万零壹元整壹拾万零壹元整壹拾万零壹元整壹拾万零壹元整壹拾万零捌仟元整壹拾万捌仟元整整零元整公式七(最完整)昴日星君提供壹拾万零肆仟元整伍仟肆佰捌拾柒元叁角贰分肆拾伍万玖仟陆佰贰拾壹元零玖分负壹万贰仟元零捌分负壹佰贰拾伍元叁角陆分捌元玖角整壹角贰分壹拾万零壹元整壹拾万零壹元整壹拾万零捌仟元整亿千百十万千百十元角分亿千百十万￥10400000￥10￥548732￥￥45962109￥45￥-1200008￥-1￥-12536￥￥890￥12￥10000100￥10￥10000100￥10￥10800000￥10￥0千百十元角分亿千百十万千百十元角分400000￥10400000548732￥548732962109￥45962109200008￥-1200008-12536￥-12536￥890￥890￥12￥12000100￥10000100000100￥10000100800000￥10800000￥0￥0业务员业务员何志斌何志斌许锡文许锡文许锡文朱英杰朱英杰刘召许锡文刘招刘召陈道亚刘招李光辉刘招吴小冰陈道亚梁俊浩陈道亚蒋进才李光辉杨明静陈道亚方向前陈道亚范明军吴小冰梁俊浩蒋进才杨明静方向前范明军上一页下一页返目录单个区域排名成绩RANK降序RANK升序COUNTIF降序COUNTIF升序SUM降序SUM升序81282828754646468128282889110110110658383837546464671747474509292924910110110174656565多个区域排名班级1成绩班级2成绩对班级1成绩按全年级排名A81Q422202B75R81626C81S77252D89T621111E65U49101510F75V456186G71W479179H50X44141914I49Y58151215J74Z568138中国式排名成绩COUNTIF排名FREQUENCY排名8122753381228911656675337155507749887444RANK降序上一页下一页返目录身份证号码出生日期性别年龄全部改为18位全部改为15位退休日期1102212908152241929/8/15女90110221192908152248110221290815224########11010119700626551X1970/6/26男4911010119700626551X110101700626551########3702111980083033301980/8/30男39370211198008303330370211800830333########3717241976061918131976/6/19男43371724197606191813371724760619181########4250515253546162636465718182110101110102110103110104注：1.身份证号码随机输入的，如有雷同，纯属巧合，敬请见谅。2.所有求出的信息都是直接从身份证号码里取得的。3.退休日期按男60女55周岁计算4.有效性验证已直接放到A列的数据有效里，单独列出是为了方便查看5.年龄按周岁计算。上一页下一页返目录110105110106110107110108110109110111110112110113110114110115001102110221110222110223110224110225110226110227110228110229001201120101120102120103120104120105120106120107120108120109120110120111120112120113120114120115001202120221120222120223120224120225001301130101130102130103130104130105130106130107130108130121130122130123130124130125130126130127130128130129130130130131130132130133130181130182130183130184130185001302130201130202130203130204130205130206130221130222130223130224130225130226130227130228130229130230130281130282130283001303130301130302130303130304130305130321130322130323130324001304130401130402130403130404130405130406130421130422130423130424130425130426130427130428130429130430130431130432130433130434130435130481001305130501130502130503130504130521130522130523130524130525130526130527130528130529130530130531130532130533130534130535130581130582001306130601130602130603130604130605130621130622130623130624130625130626130627130628130629130630130631130632130633130634130635130636130637130638130681130682130683130684001307130701130702130703130704130705130706130707130721130722130723130724130725130726130727130728130729130730130731130732130733001308130801130802130803130804130821130822130823130824130825130826130827130828001309130901130902130903130904130921130922130923130924130925130926130927130928