函数嵌套基础实例-070515

excel工作表常用函数二个及以上配对联合运用，实现复杂功能(学习函数嵌套基础),,,,,,,,,,

函数举例：,公式目的：,分步实现法（部分函数）：,,,,一步嵌套法：,,各例子取数的数据区：,,

"len

substitute",统计I4单元格中含有字母a的个数,7,wxy,3,4,4,,姓名,年龄,评分

"sum

countif",计算j4：j8区域平均值,130,5,,26,26,,waxayaa,10,100

"offset

counta",I列数据的动态引用（定义名称为：my）,,,,,张二,,张二,20,200

,,,,,,张三,,张三,30,300

"index

match",查找年龄30岁人的对应姓名,3,,,张三,张三,,张四,50,500

"index

row",找出K列一定行次上所在的评分是多少,,,,,200,,张五,20,200

"indexmatch

large",找出K列评分最高的人的姓名,500,4,,张四,张四,,,,

"indexmatch

small",找出K列评分最低的人的姓名,100,1,,waxayaa,waxayaa,,,,

"if

and",多条件判断,,,,同时符合,不符合,,,,

"if

or",多条件判断,,,,符合,不符合,,,,

"address

rowcolumn",显示当前所处的行和列所在绝对地址,,,,,$G$13,,,,

"choose

row",按给定行号返回数值列表中的数值,,,,,4,,,,

"choose

column",按给定列号返回数值列表中的数值,,,,,2,,,,

"indirect

row",用变量单元格形式引用I8单元格,,,,,张五,,,,

"indirect

column",用变量单元格形式引用K7单元格,,,,,500,,,,

"vlookup

indirect",查找“张四”的年龄,I8,张五,,20,20,,,,

"lookup

indirect",查找“张四”的评分数是多少,,,,500,500,,,,

"offset

indirect",单元格从I7移位到K8，获得新值,,,,,200,,,,

"if

not",条件判断,,,,假,真,,,,

"isnumber

indirect",IS类函数，判断数据类型,,,,,FALSE,,,,

"isblank

indirect",IS类函数，判断数据类型,,,,,FALSE,,,,

"iserr

indirect",IS类函数，判断数据类型,,,,,FALSE,,,,

"iserror

indirect",IS类函数，判断数据类型,,,,,FALSE,,,,

"ISLOGICAL

indirect",IS类函数，判断数据类型,,,,,FALSE,,,,

"isna

indirect",IS类函数，判断数据类型,,,,,FALSE,,,,

"isref

indirect",IS类函数，判断数据类型,,,,,TRUE,,,,

"istext

indirect",IS类函数，判断数据类型,,,,,TRUE,,,,

"type

indirect",判断返回数值的类型(2是文本型),,,,,2,,,,

"index

matchmax",找出年龄最大的姓名,50,4,,张四,张四,,,,

"index

matchmin",找出年龄最小的姓名,10,1,,waxayaa,waxayaa,,,,

"day

today",求当前日期,,,,,14,,,,

"month

today",求当前月份,,,,,5,,,,

"year

today",求当前年份,,,,,2007,,,,

"now

text",求年份、月份、日、星期、时间,2007,05,14,Monday,18:34,,,,

"vlookup

ifisna",查找结果的错误处理（查找“张一”），G37单元格中找不到显示空格