玩轉(zhuǎn)EXCEL函數(shù)99M

1、作者：饒晨作者：饒晨會結(jié)合自己的工作，描述EXCEL公式、函數(shù)的優(yōu)點，喜愛電子表格能評價一個復(fù)雜函數(shù)用得是否得當(dāng)，能夠改善運行算法，會指導(dǎo)他人使用函數(shù)。會用EXCEL的自帶函數(shù)功能完成作數(shù)據(jù)的統(tǒng)計、分析，并能根據(jù)需要編輯自定義函數(shù)了解EXCEL公式、與函數(shù)的基本用法自我實現(xiàn)自我實現(xiàn)情感領(lǐng)域情感領(lǐng)域技能領(lǐng)域技能領(lǐng)域認(rèn)知領(lǐng)域認(rèn)知領(lǐng)域勤查百度：沒有人能隨時教你，指導(dǎo)你，但Baidu能做到興趣愛好：興趣愛好是最好的老師實戰(zhàn)操作：經(jīng)常練習(xí)，在使用中逐漸積累，逐漸成長學(xué)好EXCEL函數(shù)關(guān)鍵Shift+Ctrl+Enter輸入：=IF(OR(MID(D4,5,2)=宿松,安慶,樅陽),MID(D4,5,2)

2、,IF(D4=0,IF(D4=,安慶)自帶的函數(shù)公式，如=Average/sum/large/min由基本函數(shù)/公式復(fù)合得來的，如:=if(or(A1),sum(A1:H1),0)分析工具箱、自定義名稱，VBA（VB語言）定義復(fù)合復(fù)合函數(shù)函數(shù)自定自定義義基本基本函數(shù)函數(shù)數(shù)組數(shù)組公式公式基本函數(shù) 公式：是以等號“=”開頭，通過使用運算符將數(shù)據(jù)，實現(xiàn)對數(shù)據(jù)執(zhí)行計算的等式。例如 C1=A1+A2+A3+A4+B1+B2+B3+B4函數(shù)：簡而言之，函數(shù)就是預(yù)先定義好了的公式。不同的參數(shù)用逗號隔開。正在輸入的參數(shù)會用粗黑體顯示。函數(shù)函數(shù)公式公式文本邏輯信息查找統(tǒng)計時間Average/sum/large/

3、min/medianFind/left/t/mid/text/codeVlookup/offset/row/match/rows/index/chooseIserror/isna/istextAnd/or/notDate/hour/sec查找match( lookup_value,lookup_arry,match_type)Vlookup( lookup_value,table_arry,col_index_number,range_lookup)需要查找的值輸出值位置查找的范圍控制類型，可不添加查找以vlookup為例$B$2：$C$11；表示在這些單元格中找”G2”單元格的內(nèi)容“張三”，

4、返回第“2”列的內(nèi)容“$”表示鎖定其后面的“B2”中的“B”不包括“2”，可以用F4快捷鍵輸入$B$2，鎖定“B”和“2”“0”：該參數(shù)取值只能是“0”或“1”，可不輸入，省略時默認(rèn)為“0” 但其之前的“，”不可以省略。查找indirect( ref_text,a1)offest( reference,rows,cols,hight,weight)基準(zhǔn)位置文本引用指定偏移量可選參數(shù)查找以INDIRECT為例指定引用”r2c2”的內(nèi)容，即第二行第二列（B2）的內(nèi)容“&”表示：文本的連接符“，”：省略了后面一個可選參數(shù)，逗號不可以省略文本left( text,num_chars)text(

5、value,format_text)find(find_text,winin_text,star_number)mid( text,start_num,num_chars)目標(biāo)格式，如“0.00”可選參數(shù)，left/ringht默認(rèn)值是1,mid默認(rèn)為0。文本以mid為例指定引用C2的內(nèi)容：小二班2：從第二個字符“小”開始截取“2”：所截取的字符串長度為2.統(tǒng)計Sum/average(number1,num2，)countif(range,criteria)covar(arry1,arry2)Min/max(number1,num2，)可選參數(shù)，計算所有數(shù)據(jù)的平均值/和值/最大/最小值兩個數(shù)組

6、（指定區(qū)域），返回協(xié)方差參數(shù)：可以是引用單元格/數(shù)組區(qū)域/數(shù)值其形式可以為數(shù)字、表達式或文本統(tǒng)計以sum為例連續(xù)區(qū)域，D2、E2之間愛你的矩形區(qū)域所有元素D2：單個單元格引用85：數(shù)值。（本例中語文數(shù)學(xué)成績都計算了2次）統(tǒng)計以countif為例AB數(shù)據(jù)數(shù)據(jù)數(shù)據(jù)數(shù)據(jù)蘋果32柑桔54桃75蘋果86公式公式說明（結(jié)果）說明（結(jié)果）=COUNTIF(A2:A5,apples) 計算第一列中蘋果所在單元格的個數(shù) (2)=COUNTIF(B2:B5,55)計算第二列中值大于 55 的單元格個數(shù) (2)邏輯True/false( )if(logical_text,value_if_true,value_if

7、_false)Or/not/and(logical1,logical2,)邏輯參數(shù)，or/and/not一次相當(dāng)于邏輯連接詞中的：或、且、非。無參數(shù)，主要與其他電子表格兼容用Value_if_true:邏輯判斷為真時返回；value_if_false:邏輯判斷為假時返回。邏輯以if為例看D2的內(nèi)容是否不小于60如果不小于60判斷為及格如果小于60則輸出“不及格”信息Iserr/iserror/isnumber/isblank/istext/isna( value)N(value)na( )無參數(shù)：一是與其他電子表格兼容，二是避免空白單元格引起錯誤僅一個參數(shù)，判斷該參數(shù)是否屬于某種類型，返回tr

8、ue或falseValue:將該參數(shù)轉(zhuǎn)為數(shù)值類型，時間/日期轉(zhuǎn)成序列信息以Iserror為例判斷F2單元格是否為錯誤值，與iserror不同的是#N/A在這里不是錯誤時間date(year,month,day)Hour/day/month(serial_number)從一串?dāng)?shù)碼（時間/日期）中獲取相關(guān)的參數(shù)，hour獲取時，day獲取日等等輸入年月日，默認(rèn)起點是1900年1月1日，早于這個日期，則把年份作為序列+1990.如2和1992都是1992年datavalue/(date_text)將時間文本，轉(zhuǎn)成序列數(shù)，默認(rèn)單位是天，1900年1月1日對應(yīng)序列數(shù)為1時間以date為例一般使用data

9、輸入其參數(shù)，如不是，則默認(rèn)一個月最多31天，數(shù)據(jù)大于31自動減去31，直到小于或等于31基本函數(shù)復(fù)合函數(shù)l 復(fù)合函數(shù)就是將多個不同的函數(shù)嵌套起來使用，具有更強大的分析運算能力l 多層嵌套的時候，一定要注意各自函數(shù)的參數(shù)分別是誰，返回類型是否滿足外層函數(shù)的要求IfIf是復(fù)合函數(shù)中用的最多的，EXCEL默認(rèn)最大支持7層嵌套，每個函數(shù)內(nèi)容不超過255字符復(fù)合函數(shù)總是先計算內(nèi)層函數(shù)，將其結(jié)果作返回給外層函數(shù)作為參數(shù)。indirectColumn獲取了D2的列4，row獲取了C2的行2，所以整個夸號里面就是r4c2,也就是定位在B4單元格=INDIRECT主要是定位的作用，一般與row()、column

10、()復(fù)合來獲取某些變動位置的值sum這里用了多層嵌套，最終返回的是：若A列的尾數(shù)是偶數(shù)，返回G列相應(yīng)值+20，否則直接返回20.實際上可簡寫為=IF(MOD(A2,2)=0,F2+20,20)=sum（）是除了if以外最常用的的公式，主要用來統(tǒng)計“和”這樣的量，可以是數(shù)組的和，也可以是條件求和基本函數(shù)復(fù)合函數(shù)數(shù)組公式l 數(shù)組函數(shù)的大括號不能手動輸入，需要用ctrl+shift+Enter結(jié)束l 選中公式時，外層的大括號會消失，記得再次用ctrl+shift+Enter結(jié)束or這里的or函數(shù)部分，如果用純用if則需要用9層嵌套，加上外面一層共10層嵌套，顯然過于復(fù)雜。如果只用or不用數(shù)組，or函

11、數(shù)里面則需要9次left(C2,2)=“*”,數(shù)組公式可以一次性做很多運算，or()、and()就經(jīng)常用來作選言、聯(lián)言判斷，以減少多層嵌套if語句sum該公式計算的是滿足D列是小二班且數(shù)學(xué)及格的學(xué)生的語文得分之和Sum是作為數(shù)組公式用的最頻繁的，能實現(xiàn)眾多功能SumIF單條件求和函數(shù)單條件求和函數(shù)H7:H19=E,E,D,E,E,A,C,B,C,A,D,A,BM7=A，在函數(shù)中，自動轉(zhuǎn)換成數(shù)組形，在函數(shù)中，自動轉(zhuǎn)換成數(shù)組形式，即式，即A,A,A,A,A,A,A,A,A,A,A,A,AE,E,D,E,E,A,C,B,C,A,D,A,BA,A,A,A,A,A,A,A,A,A,A,A,A| | |

12、| | | | | | | | | |0,0,0,0,0,1,0,0,0,1,0,1,01）按次序逐對進行比較。）按次序逐對進行比較。2）相符（）相符（TRUE）為）為1，不相符，不相符（FALSE）為）為0?；竞瘮?shù)復(fù)合函數(shù)數(shù)組函數(shù)自定義l 數(shù)組函數(shù)的大括號不能手動輸入，需要用ctrl+shift+Enter結(jié)束l 選中公式時，外層的大括號會消失，記得再次用ctrl+shift+Enter結(jié)束分析工具庫自定義名稱VBA編程自定義函數(shù)方法分析工具庫分析工具庫Excel自帶的高級公式，默認(rèn)情況在函數(shù)里面找不到自定義名稱自定義名稱Excel自帶的高級公式，默認(rèn)情況在函數(shù)里面找不到可以到自定義的公式，可以