電子表格函數(shù)公式使用集錦

1、電子表格函數(shù)公式使用集錦怎么讓excel表格公式只能看不能被改：選定不需要保護(hù)的的單元格，單擊右鍵設(shè)置單元格格式取消鎖定選定需要保護(hù)的的單元格，單擊右鍵設(shè)置單元格格式鎖定工具保護(hù)保護(hù)工作表輸入密碼確定。一、電子表格中由身份證號自動導(dǎo)出年月日的公式=IF(LEN(E1)=15,"19"&MID(E1,7,2)&"-"&MID(E1,9,2)&"-"&MID(E1,11,2),MID(E1,7,4)&"-"&MID(E1,11,2)&"-&quo

2、t;&MID(E1,13,2)轉(zhuǎn)換出生年月如1986-05-23說明：E：列數(shù)   E1890：第E列第1890行。輸入身份證號碼LEN（text）:返回本字符串的個數(shù)。LEN（“123”）=3LEN(E1)=15表示如果身份證號碼為15個數(shù)字&:表示相加MID（字符串，M，N）：從該字符串第M位開始，取N位字符。MID（A1，3，4）=3456，從“A1”單元格中的第“3”位起截取“4”個數(shù)IF（條件表達(dá)式，語句1，語句2）：如果條件成立，那么就執(zhí)行語句1，否則執(zhí)行語句2LEFT（A1,14）截取A1單元格前14位數(shù)RIGHT（A1,14）截取A1單元格后

3、14位數(shù)身份證號碼有兩種，如“352124860213541”或如果E1是15個,為19加上從第7個開始取2個“86” 加上-加上從第9個開始取2個“02” 加上-加上從第11個開始取2個“13”合起來為“1986-02-13”，否則為從第7個開始取4個“1986” 加上-加上從第11個開始取2個“02” 加上-加上從第13個開始取2個“13” 合起來為“1986-02-13”。 "19"、"-"為直接寫入的數(shù)。=IF(LEN(A1)=15,"19"&MID(A1,7,4),MID(A1

4、,7,6)轉(zhuǎn)換出生年月取如“198606”=IF(LEN(A1)=15,CONCATENATE("19",MID(A1,7,2),".",MID(A1,9,2),IF(LEN(A1)=18,CONCATENATE(MID(A1,7,4),".",MID(A1,11,2),"身份證錯")轉(zhuǎn)換出生年月取如1986.05=2010-MID(B1,1,4)-IF(MID(B1,5,2)-0)>8,1,0)計算年齡=IF(LEN(A1)=15,YEAR(NOW()-1900-VALUE(MID(A1,7,2),IF(L

5、EN(A1)=18,YEAR(NOW()-VALUE(MID(A1,7,4),"身份證錯")計算年齡，月數(shù)全部不算如24歲2個月和24歲11個月都是24歲=IF(LEN(A1)=15,IF(MOD(VALUE(RIGHT(A1,3),2)=0,"女","男"),IF(LEN(A1)=18,IF(MOD(VALUE(MID(A1,15,3),2)=0,"女","男"),"身份證錯")轉(zhuǎn)換性別二、成績在年級里的排名菜RANK（）函數(shù)的使用=RANK(N2,$N$2:$N$1501

6、,0)N2為所要排名的單元格，$N$2:$N$1501為從N2列到N1501列，0表示為按照降序排列的列表，不為零為按照升序排列的列表=RANK(C1,$C$1:$C$10)為10個學(xué)生中的第一個的排名三、利用函數(shù)統(tǒng)計考試成績=COUNTA(A1:A25)算有數(shù)值的單元格個數(shù)應(yīng)考人數(shù)=COUNT(B1:B25)和上面的一樣用處算出考試人數(shù)=COUNTBLANK(B1:B25)算出缺考人數(shù)=COUNTIF(B1:B25,">=90")算90分以上人數(shù)=COUNTIF(B1:B25,">=80")- COUNTIF(B1:B25,"&g

7、t;=90")算80到90分人數(shù)=MAX(C1:C25)算最高分=MIN(C1:C25)算最低分=AVERAGE(C1:C25)算平均分=COUNTIF(C1:C25,">=90")/COUNT(C1:C25)90分以上占百分比=MEDIAN(B1:B25)算中位數(shù)=MODE(B1:B25)算眾數(shù)=STDEVP(B1:B25)算標(biāo)準(zhǔn)差四、文本格式轉(zhuǎn)換成數(shù)值格式在原單元格上轉(zhuǎn)換：在任一空白單元格輸入1復(fù)制1選定所有需要改變的單元格右鍵選擇性粘貼選"乘"，用0加計算也行在新單元格上轉(zhuǎn)換：選定新單元格，“=所要轉(zhuǎn)換的文本單元格-0”在新單元格上

8、轉(zhuǎn)換：復(fù)制選擇性粘貼數(shù)值點(diǎn)擊下拉轉(zhuǎn)換成數(shù)值五、&的應(yīng)用=B2&C2表示把兩個單元格的數(shù)值合為一個如“45”、“67”合為“4567”=$F$17&C1表示在一個數(shù)值前加一個數(shù)，在任意單元格輸入123設(shè)為絕對值，再加所要添加的單元格六、IF的應(yīng)用（滿足其中一個條件的判斷）=IF(A2>89,"優(yōu)+",IF(A2>79,"優(yōu)",IF(A2>69,"良+",IF(A2>59,"及格","不及格")=IF(A2>B2,"超預(yù)算",

9、"預(yù)算內(nèi)")是對預(yù)算執(zhí)行結(jié)果的判斷=IF(A2=100,SUM(B5:B15),"") 如果A2數(shù)字為100，則計算單元格區(qū)域B5:B15，否則返回空文本("")=IF(AND(A1>0,A1<=10),1,IF(AND(A1>10,A1<100),2,IF(AND(A1>=100,A1<200),3,"") 當(dāng)A列中的數(shù)值大于0小于等于10時返回1，大于10小于100返回2，大于等于100小于200返回=IF(AND(A1=B1,A1=C1),1,0)如果A1=B1=C1，則在

10、D1顯示1，若不相等則返回0=IF(C1>60,IF(AND(C1>90),"優(yōu)秀","合格"),"不合格")如果單元格C1的值大于60，則執(zhí)行第二個參數(shù)，在這里為嵌套函數(shù)，繼續(xù)判斷單元格C1的值是否大于90（為了讓大家體會一下AND函數(shù)的應(yīng)用，寫成AND(C1>90)，實(shí)際上可以僅寫C1>90），如果滿足在單元格C2中顯示優(yōu)秀字樣，不滿足顯示合格字樣，如果C1的值以上條件都不滿足，則執(zhí)行第三個參數(shù)即在單元格C2中顯示不合格字樣。=IF(A1=1,"A",IF(A1=2,"B&qu

11、ot;,IF(A1=3,"C",IF(A1=4,"D",IF(A1=5,"E",IF(A1=6,"F",IF(A1=7,"G",IF(A1=8,"G","")&IF(A1=9,"Q",IF(A1=10,"l",IF(A1=11,"k",IF(A1=12,"y",IF(A1=13,"x",IF(A1=14,"n",IF(A1=15,

12、"m",IF(A1=16,"o","")&為多層嵌套七、AND函數(shù)（同時滿足條件判斷）AND（條件一，條件二.條件三)，條件一 二 三 都成立時返回TURE，否則返回FALSE=IF(AND(A1>60,B1>60,C1>60),"及格","不及格")，當(dāng)AND（A1>60，B1>60，C1>60） 為TURE時返回 “及格”，為FALSE時返回“不及格”=IF(A1<60,"不及格",IF(AND(A1>60,A1&l

13、t;70),"及格",IF(AND(A1>70,A1<85),"良好",IF(A1>85,"優(yōu)秀")可以和IF函數(shù)合起來使用八、CONCATENATE函數(shù)（將幾個文本字符串合并為一個文本字符串）=CONCATENATE(A1,B1,C1,D1)也可以用 &（和號）運(yùn)算符代替函數(shù) CONCATENATE 實(shí)現(xiàn)文本項(xiàng)的合并。九、名稱和標(biāo)志為了更加直觀地標(biāo)識單元格或單元格區(qū)域，我們可以給它們賦予一個名稱，從而在公式或函數(shù)中直接引用。例如“B2:B46”區(qū)域存放著學(xué)生的物理成績，求解平均分的公式一般是“=AVERAG

14、E(B2:B46)”。在給B2:B46區(qū)域命名為“物理分?jǐn)?shù)”以后，該公式就可以變?yōu)?“=AVERAGE(物理分?jǐn)?shù))”，從而使公式變得更加直觀。給一個單元格或區(qū)域命名的方法是:選中要命名的單元格或單元格區(qū)域，鼠標(biāo)單擊編輯欄頂端的“名稱框”，在其中輸入名稱后回車。也可以選中要命名的單元格或單元格區(qū)域，單擊“插入名稱定義”菜單命令，在打開的“定義名稱”對話框中輸入名稱后確定即可。如果你要刪除已經(jīng)命名的區(qū)域，可以按相同方法打開“定義名稱”對話框，選中你要刪除的名稱刪除即可。由于Excel工作表多數(shù)帶有“列標(biāo)志”。例如一張成績統(tǒng)計表的首行通常帶有“序號”、“姓名”、“數(shù)學(xué)”、“物理”等“列標(biāo)志”(也可以

15、稱為字段)，如果單擊“工具選項(xiàng)”菜單命令，在打開的對話框中單擊“重新計算”選項(xiàng)卡，選中“工作簿選項(xiàng)”選項(xiàng)組中的“接受公式標(biāo)志”選項(xiàng)，公式就可以直接引用“列標(biāo)志”了。例如“B2:B46”區(qū)域存放著學(xué)生的物理成績，而B1單元格已經(jīng)輸入了“物理”字樣，則求物理平均分的公式可以寫成 “=AVERAGE(物理)”。十、幾個常用函數(shù)=ABS(A2)，則在A2單元格中無論輸入正數(shù)（如100）還是負(fù)數(shù)（如-100），B2中均顯示出正數(shù)（如100）=AND(A5>=60,B5>=60)，確認(rèn)。如果C5中返回TRUE，說明A5和B5中的數(shù)值均大于等于60，如果返回FALSE，說明A5和B5中的數(shù)值至少

16、有一個小于60=COLUMN(B11)，確認(rèn)后顯示為“2”（即B列）十一、與求和有關(guān)的函數(shù)1、=SUM(H3:H12)求H3至H12的和2、SUBTOTAL(function_num,ref1,ref2,) 分類匯總Function_num 為 1 到 11 之間的數(shù)字，指定使用何種函數(shù)在數(shù)據(jù)清單中進(jìn)行分類匯總計算。Function_Num 函數(shù) 1 AVERAGE 求算術(shù)平均數(shù)2 COUNT計算參數(shù)列表中的數(shù)字項(xiàng)的個數(shù)3 COUNTA計算單元格區(qū)域或數(shù)組中包含數(shù)據(jù)的單元格個數(shù)。4 MAX求最大值 5 MIN 求最小值6 PRODUCT 單元格內(nèi)的乘積7 STDEV 估算樣本的標(biāo)準(zhǔn)偏差，反映相

17、對于平均值的離散程度8 STDEVP整個樣本總體的標(biāo)準(zhǔn)偏差9 SUM求和10 VAR計算基于給定樣本的方差11 VARP計算基于整個樣本總體的方差例：“=SUBTOTAL(9,A2:A5) 對A2至A5列使用 SUM 函數(shù)計算出的分類匯總 (303) ”，“=SUBTOTAL(1,A2:A5) 對A2至A5列使用 AVERAGE 函數(shù)計算出的分類匯總 (75.75)” 3、SUMIF根據(jù)指定條件對若干單元格求和例：=SUMIF($C$3:$C$12,"銷售部",$F$3:$F$12)，“$C$3:$C$12”指部門名稱單元格，"銷售部"指計算其中的“銷售

18、部”部門，“$F$3:$F$12”指部門名稱相應(yīng)的數(shù)值單元格。4、SUMPRODUCT在給定的幾組數(shù)組中，將數(shù)組間對應(yīng)的元素相乘，并返回乘積之和=SUMPRODUCT(A15:A16,B15:B16)表示“A15*B15+A16*B16”=SUMPRODUCT(B2:C4*D2:E4)表示“B2*D2+C2*E2+ B4*D4+C4*E4”=SUMPRODUCT($B$2:$B$11=$E2)*($C$2:$C$11=F$1) 計算符合2個及以上條件的數(shù)據(jù)個數(shù)（4人的單元格輸入公式）姓名性別職稱性別中一中二A男中一男4B女中二女C女中一D男中一E女中一F男中二G女中二H男中一I男中一J女中一=

19、SUMPRODUCT($B$2:$B$11=$F2)*($C$2:$C$11=G$1),$D$2:$D$11)計算男、女分別是中一或中二的總課時數(shù)（在15節(jié)單元格中輸入公式）姓名性別職稱課時性別中二中一A男中一15男15B女中二16女C女中一14D男中一13E女中一18F男中二15G女中二16H男中一14I男中一17J女中一18SUMSQ函數(shù)：計算多個數(shù)值的平方和。如SUMSQ (B2,C2)=B2的平方+C2的平方。ROUND函數(shù)：如ROUND(B2,2)就是對B2進(jìn)行四舍五入保留2位小數(shù)。INT（將數(shù)字向下舍入到最接近的取整函數(shù)）IF和AND 嵌套使用： =IF(AND(A1>60,

20、B1>60,C1>60),"及格","不及格")，當(dāng)A1，B1,C1 都大于60時 返回“及格”=IF(A1<60,"不及格",IF(AND(A1>60,A1<70),"及格",IF(AND(A1>70,A1<85),"良好",IF(A1>85,"優(yōu)秀")，當(dāng) A1<60 時返回“不及格”，當(dāng)60<A1<70 時返回“及格”，當(dāng) 70<A1<85時返回“良好”，當(dāng)A1>85時返回“優(yōu)秀”COU

21、NTIF函數(shù)：計算其中滿足條件的單元格數(shù)目，如COUNTIF(B4:B10,">90")，計算B4到B10這個范圍各科成績中有多少個數(shù)值大于90的單元格。如COUNTIF($C$2:$C$13,A17)，計算$C$2:$C$13這個范圍有多少個A17（A17存放的是姓名）SUMIF($C$2:$C$13,A17,$B$2:$B$13)計算其中（A17）的銷售獎金，$C$2:$C$13是銷售人員的姓名，A17是其中的一個姓名，$B$2:$B$13是銷售金額區(qū)域，IF(C17<50000,10%,15%)*C17如果訂單總額小于 50000則獎金為 10%；如果訂單

22、總額大于等于 50000，則獎金為 15%十二、字母大小寫轉(zhuǎn)換LOWER (A1)將A1文字串中的所有字母轉(zhuǎn)換為小寫字母。UPPER (A1)將A1文本轉(zhuǎn)換成大寫形式。PROPER (A1)將A1文字串的首字母及任何非字母字符之后的首字母轉(zhuǎn)換成大寫。將其余的字母轉(zhuǎn)換成小寫。十三、取出字符串中的部分字符LEFT("This is an apple",4)=This從前面取RIGHT("This is an apple",5)=apple從后面取MID("This is an apple",6,2)=is從中間取十四、取出當(dāng)前系統(tǒng)時間/日

23、期信息NOW()取當(dāng)前系統(tǒng)“年月日時分”TODAY()取當(dāng)前系統(tǒng)“年月日”YEAR(E5)=2001取單元格的“年”MONTH(E5)=5取單元格的“月”DAY(E5)=30取單元格的“日”HOUR(E5)=12取單元格的“時”DATEDIF：計算兩個日期之間的天數(shù)、月數(shù)或年數(shù)：其中計算年數(shù)為DATEDIF(A24,TODAY(),"y")，"Y" 時間段中的整年數(shù)，"M" 時間段中的整月數(shù)，"D" 時間段中的天數(shù)，"MD"為日期中天數(shù)的差，忽略日期中的月和年（直接天數(shù)相減，不夠減要向上月借一），"YM"為日期中月數(shù)的差，忽略日期中的日和年（直接月數(shù)相減，不夠減要向上月借一），"YD&quo