常用EXCEL函数详解及实例-信息、逻辑函数.xls

zylkfxy常常用用ExcelExcel函函数数详详解解及及应应用用实实例例五五、信信息息函函数数序序号号函函数数函函数数定定义义页页码码1Cell返回某一引用区域的左上角单元格的格式、位置或内容等信息12Error.Type返回某一错误值的数字或文本字符串来取代错误值23Info返回有关当前操作环境的信息24Isblank查看是否值为空白单元格25Iserr查看是否为“#N/A”以外的错误26Iserror查看是否为错误27Iseven查看数值是否为偶数(空白单元格为偶数）28Islogical查看数值是否为逻辑值29Isna查看值是否为错误值“#N/A”(值不存在)210Isnontext查看是否为文本211Isnumber查看是否为数值212Isodd查看数值是否为奇数213Isref查看是否为单元格引用214Istext查看是否为文本215N返回转换为数字的值316Na返回错误值#N/A(错误值#N/A表示无法得到有效值)417Type返回数值的类型4六六、逻逻辑辑函函数数序序号号函函数数函函数数定定义义页页码码1And查看是否满足所有条件12False用来表示满足条件的逻辑值FALSE(假),返回值为FALSE13If指定要执行的逻辑检测14Not对参数的逻辑值求反25Or检测任意一项条件是否为真，如果任一参数为TRUE，则返回TRUE36True用来表示满足条件的逻辑值TRUE(真),返回值为TRUE3整整理理日日期期：20132013年年常常用用ExcelExcel函函数数详详解解及及应应用用实实例例zylkfxy五五、信信息息函函数数目录5!C41.1.查查看看单单元元格格信信息息函数定义：返回某一引用区域的左上角单元格的格式、位置或内容等信息.使用格式：CELL(info_type,reference)格式简义：CELL(文本值,获取其有关信息的单元格)参数定义：Info_type为一个文本值,指定所需要的单元格信息的类型Reference表示要获取其有关信息的单元格.如果忽略,则在info_type中所指定的信息将返回给最后更改的单元格.下表描述info_type为format,以及引用为用内置数字格式设置的单元格时,函数CELL返回的文本值.注意事项：如果CELL公式中的info_type参数为format,而且以后又用自定义格式设置了单元格,则必须重新计算工作表以更新CELL公式.Info_type返回address引用中第一个单元格的引用,文本类型.col引用中单元格的列标.color如果单元格中的负值以不同颜色显示,则为1,否则返回0.contents引用中左上角单元格的值：不是公式.filename包含引用的文件名(全部路径),文本类型.如果包含目标引用的工作表尚未保存,则返回空文format本()与单元格中不同的数字格式相对应的文本值.下表列出不同格式的文本值.如果单元格中负值以不同颜色显示,在返回文本值的结尾处加-；若单元格中为正值或所有单元格均加括号,则在文本值的结尾处返回()parenthesesparentheses如果单元格中为正值或全部单元格均加括号,则为1,否则返回0.prefix与单元格中不同的标志前缀相对应的文本值.如果单元格文本左对齐,则返回单引号()；如果单元格文本右对齐,则返回双引号()；如果单元格文本居中,则返回插入字符()；如果单元格文本两端对齐,则返回反斜线()；如果是其他情况,则返回空文本().protect如果单元格没有锁定,则为0；如果单元格锁定,则为1.row引用中单元格的行号.type与单元格中的数据类型相对应的文本值.如果单元格为空,则返回b.如果单元格包含文本常量,则返回l.如果单元格包含其他内容,则返回v.width取整后的单元格的列宽.列宽以默认字号的一个字符的宽度为单位.如果MicrosoftExcel的格式为CELL返回值如果MicrosoftExcel的格式为CELL返回值常规G0S20F0#?/?或#?/?G#,#0,0yy-m-d或yy-m-dh:mmD40F2d-mmm-yy或dd-mmm-yyD1#,#0.00,2d-mmm或dd-mmmD2$#,#0_);($#,#0)C0mmm-yyD3$#,#0_);Red($#,#0)C0-dd-mmD5$#,#0.00_);($#,#0.00)C2h:mmAM/PMD7$#,#0.00_);Red($#,#0.00)C2-h:mm:ssAM/PMD60P0h:mmD90P2h:mm:ssD8例1中华人民共和国=CELL(prefix,B44)(对齐格式(左对齐)中国人民=CELL(prefix,B45)(对齐格式(居中)中国人民=CELL(prefix,B46)(对齐格式(右对齐)2009/1/8D1=CELL(format,B47)(为日期格式)例2提取本工作簿的文件名当前的文件名是:常用excel函数详解及应用实例（新版）.xlsm=MID(CELL(filename),FIND(,CELL(filename)+1,FIND(,CELL(filename)-FIND(,CELL(filename)-1)例3用函数和条件格式来显示含有公式单元格单元格内容左边实际内容1515 首先插入定义名称(选中B56):2.5=5/2输入名称:GS5=5=get.cell(48,b56)1212 然后设置条件格式:ABCDEFGHI1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859第 2 页，共 9 页常常用用ExcelExcel函函数数详详解解及及应应用用实实例例zylkfxy60=ROW()=GS=TRUE1212在“格式”中选取有公式的单元格所显示的颜色，这样就标出了含有公式的单乙乙元格了5252FALSE=425),符合,不符合)(女生.年龄25的)=IF(AND(C14=男,E1475,F1475),符合,不符合)(男生，语文.数学都75的)例2 将“账龄区间设计”划分为3060天、6090天和90天以上三档，分析收账情况应收金额已收金额到期日期账龄分析30-6060-9090天以上1858.00500.002013/5/121358.004101.001000.002013/4/183101.004188.001000.002013/3/203188.00121.0050.002013/10/12以后1000.5088.002013/10/20以后=IF(AND(TODAY()-$D2630,TODAY()-$D2660,TODAY()-$D2690,$B26-$C26,以后)(90天以上到期)目录5!C252.FALSE2.FALSE 逻逻辑辑值值假假 函数定义：用来表示满足条件的逻辑值FALSE(假),返回值为FALSE.使用格式：FALSE()格式简义：不需要参数参数定义：不需要参数注意事项：1.可以直接在工作表或公式中输入文字FALSE,Microsoft Excel会自动将它解释成逻辑值FALSE2.当在括号()中指定参数时显示错误,使用函数时要注意确认不输入参数.例3FALSE=FALSE()5967FALSE=E42=F42目录5!C263.IF3.IF 判判断断是是与与不不是是。函数定义：根据条件满足与否返回不同的值使用格式：IF(logical_test,value_if_true,value_if_false)格式简义：IF(条件,与条件一样时运算这个,与条件不同时运算这个)参数定义：logical_test条件.必需.计算结果可能为 TRUE 或 FALSE 的任意值或表达式value_if_true可选.logical_test参数的计算结果为TRUE时所要返回的值.value_if_false可选.logical_test参数的计算结果为FALSE时所要返回的值.要点：1.IF 函数语法具有下列参数(参数:为操作、事件、方法、属性、函数或过程提供信息的值.)2.2003版最多嵌套7个IF函数.2007版最多嵌套64个IF函数.例4 判断数值是否大于5050数值结果公式显示ABCDEFGHI123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354第 6 页，共 9 页常常用用ExcelExcel函函数数详详解解及及应应用用实实例例zylkfxy100大于=IF(B1450,大于,小于或等于)25小于或等于=IF(B15D12,大于,小于或等于)100大于=IF(B1650,大于,)10FALSE=IF(B17D12,大于)例5 按完成比例计算发放奖金的数量完成任务比率情况表任务奖标准姓名完成任务ifif,vlookup比例60%奖金0赵一96.16%20020060.00%50钱二88.31%15015070.00%100孙三76.85%10010080.00%150李四106.32%25025090.00%200周五59.95%00100.00%250吴六112.65%300300110.00%300王八60.00%5050陈十109.35%250250=IF(C63G$63,0,IF(C63$G$64,$H$63,IF(C63$G$65,$H$64,IF(C63$G$66,$H$65,IF(C63$G$67,$H$66,IF(C63$G$68,$H$67,$H$68)(IF法)=IF(C63=$E$76,$E$76,D78)-IF(C78=$E$76,$E$76,D78)-IF(C78=$C$76,$C$76,C78)*24=4,达标,)(是否达标)例7 按工龄长短计算年假姓名工龄ifvlookup工龄分段享受年假张三1530天30天不足2年00李四920天20天不足5年210王五410天10天不足10年520赵六210天10天10年以上1030吴七1.50天0天=IF(C882,0,IF(C885,10,IF(C885)孙三女9FALSE在编年数不是大于5的人数：李四女2TRUE3=SUM(-NOT(D103:D1065)例9如果产品名称为洗衣机,且颜色为白色，其调价后为原来单击加50元,如果产品名称为洗衣机，且颜色为白色以外的其余色，其调价后为原来单击加200元,其余产品价格不变产品名称颜色单价调价后洗衣机001白色19802030ABCDEFGHI555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111第 7 页，共 9 页常常用用ExcelExcel函函数数详详解解及及应应用用实实例例zylkfxy洗衣机001银灰色19802180冰箱红色10251025洗衣机002红色25002700=IF(NOT(LEFT(B111,3)=洗衣机),D111,IF(AND(LEFT(B111,3)=洗衣机,NOT(C111=白色),D111+200,D111+50)目录5!C285.OR5.OR 或或(检检测测任任意意一一项项条条件件是是否否为为真真)函数定义：在其参数组中,任何一个参数逻辑值为TRUE,即返回TRUE；任何一个参数的逻辑值为FALSE,即返回FALSE使用格式：OR(logical1,logical2,logical30)格式简义：OR(要判断的单元格)参数定义：logical参数必须能计算为逻辑值,如TRUE或FALSE,或者为包含逻辑值的数组或引用.如果数组或引用参数中包含文本或空白单元格,则这些值将被忽略.要点：1.如果指定的区域中不包含逻辑值,函数OR返回错误值#VALUE!.2.可以使用OR数组公式来检验数组中是否包含特定的数值.若要输入数组公式,请按Ctrl+Shift+Enter组合键.注意事项：Logical1,logical2,logical30表示待检测的1到30个条件值,各条件值可为TRUE或FALSE。例10 判断性别为女或者年龄大于25的条件的人员姓名性别年龄结果赵一女23TRUE=OR(C131=女,D13125)钱二男26TRUE孙三女19TRUE李四男24FALSE例11 判断两门课中只要其中一门课程在80分以上的人员为为合格,反之为不合格.姓名语文数学结果公式显示赵一8692合格=IF(OR(C13880,D13880),合格,不合格)钱二9255合格孙三6578不合格李四8375合格周五8080不合格目录5!C296.TRUE6.TRUE 真真(满满足足条条件件的的逻逻辑辑值值)函数定义：用来表示满足条件的逻辑值TRUE(真),返回值为TRUE.使用格式：TRUE()格式简义：不需要参数参数定义：不需要参数要点：除了可以做函数外,还可以用于在单元格或公式中只输入TRUE.注意事项：1.可以直接在单元格或公式中键入值TRUE,而可以不使用此函数.函数TRUE主要用于与其他电子表格程序兼容.2.当在括号()中指定参数时显示错误,使用函数时要注意确认不输入参数.例12数字1数字2结果公式显示TRUEexcelFALSE=TRUE()AbabTRUE=B154=C1541221FALSEABCAbdFALSE中国人民中国人民TRUEABCDEFGHI112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158第 8 页，共 9 页信信息息、逻逻辑辑函函数数例例题题标标题题zylkfxy例01查看单元格对齐格式例02提取本工作簿的文件名例03用函数和条件格式来显示含有公式单元格例04判断单元格内容的种类例05计算当前活动工作表数量、当前文件夹路径等例06IS类函数各种类型计算例07各种数据类型转化为数值型例08在单元格中返回错误值（#N/A）例09查看单元格数据类型例01分别计算女生并年龄大于25的人数和男生语文和数学成绩同时大于75的人数例02将“账龄区间设计”划分为3060天、6090天和90天以上三档，分析收账情况例03计算两单元格是否相等例04判断数值是否大于50例05按完成任务的不同比例计算发放奖金的数量例06计算在规定的时间内上班时间，并标明是否达到规定例07按工龄长短和规定假期标准计算年假例08计算两个单元格完全不相等例09根据产品名称和颜色的不同来进行不同的调整产品价格例10判断性别为女或者年龄大于25的条件的人员例11判断两门课中只要其中一门课程在80分以上的人员为为合格,反之为不合格.例12计算两单元格完全相等返回为TRUE