函數(shù)嵌套基礎(chǔ)實(shí)例-070515VIP

excel工作表常用函數(shù)二個(gè)及以上配對聯(lián)合運(yùn)用，實(shí)現(xiàn)復(fù)雜功能(學(xué)習(xí)函數(shù)嵌套基礎(chǔ)),,,,,,,,,,

函數(shù)舉例：,公式目的：,分步實(shí)現(xiàn)法（部分函數(shù)）：,,,,一步嵌套法：,,各例子取數(shù)的數(shù)據(jù)區(qū)：,,

"len

substitute",統(tǒng)計(jì)I4單元格中含有字母a的個(gè)數(shù),7,wxy,3,4,4,,姓名,年齡,評分

"sum

countif",計(jì)算j4：j8區(qū)域平均值,130,5,,26,26,,waxayaa,10,100

"offset

counta",I列數(shù)據(jù)的動(dòng)態(tài)引用（定義名稱為：my）,,,,,張二,,張二,20,200

,,,,,,張三,,張三,30,300

"index

match",查找年齡30歲人的對應(yīng)姓名,3,,,張三,張三,,張四,50,500

"index

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

"indexmatch

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

"indexmatch

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

"if

and",多條件判斷,,,,同時(shí)符合,不符合,,,,

"if

or",多條件判斷,,,,符合,不符合,,,,

"address

rowcolumn",顯示當(dāng)前所處的行和列所在絕對地址,,,,,$G$13,,,,

"choose

row",按給定行號返回?cái)?shù)值列表中的數(shù)值,,,,,4,,,,

"choose

column",按給定列號返回?cái)?shù)值列表中的數(shù)值,,,,,2,,,,

"indirect

row",用變量單元格形式引用I8單元格,,,,,張五,,,,

"indirect

column",用變量單元格形式引用K7單元格,,,,,500,,,,

"vlookup

indirect",查找“張四”的年齡,I8,張五,,20,20,,,,

"lookup

indirect",查找“張四”的評分?jǐn)?shù)是多少,,,,500,500,,,,

"offset

indirect",單元格從I7移位到K8，獲得新值,,,,,200,,,,

"if

not",條件判斷,,,,假,真,,,,

"isnumber

indirect",IS類函數(shù)，判斷數(shù)據(jù)類型,,,,,FALSE,,,,

"isblank

indirect",IS類函數(shù)，判斷數(shù)據(jù)類型,,,,,FALSE,,,,

"iserr

indirect",IS類函數(shù)，判斷數(shù)據(jù)類型,,,,,FALSE,,,,

"iserror

indirect",IS類函數(shù)，判斷數(shù)據(jù)類型,,,,,FALSE,,,,

"ISLOGICAL

indirect",IS類函數(shù)，判斷數(shù)據(jù)類型,,,,,FALSE,,,,

"isna

indirect",IS類函數(shù)，判斷數(shù)據(jù)類型,,,,,FALSE,,,,

"isref

indirect",IS類函數(shù)，判斷數(shù)據(jù)類型,,,,,TRUE,,,,

"istext

indirect",IS類函數(shù)，判斷數(shù)據(jù)類型,,,,,TRUE,,,,

"type

indirect",判斷返回?cái)?shù)值的類型(2是文本型),,,,,2,,,,

"index

matchmax",找出年齡最大的姓名,50,4,,張四,張四,,,,

"index

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

"day

today",求當(dāng)前日期,,,,,14,,,,

"month

today",求當(dāng)前月份,,,,,5,,,,

"year

today",求當(dāng)前年份,,,,,2007,,,,

"now

text",求年份、月份、日、星期、時(shí)間,2007,05,14,Monday,18:34,,,,

"vlookup

ifisna",查找結(jié)果的錯(cuò)誤處理（查找“張一”），G37單元格中找不到顯示空格