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

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

2、,7,4)&"-&q uot;&MID(E1,11,2)&"-"&MID(E1,13,2) 轉(zhuǎn)換 出生年月如 1986-05-23說明：E：列數(shù)E1890：第 E 列第 1890 行。輸入身份證號(hào)碼LEN （text）:返回本字符串的個(gè)數(shù)。 LEN（ “ 123）”=3 LEN(E1)=15 表示如果身份證號(hào)碼為 15 個(gè)數(shù)字&:表示相加MID （字符串， M ，N）：從該字符串第 M 位開始，取 N 位字符。 MID （ A1，3，4）=3456，從

3、“ A1”單元格中的第 “ 3位”起截取 “ 4個(gè)”數(shù)IF（條件表達(dá)式，語句 1，語句 2）：如果條件成立，那么就執(zhí)行語句 1，否則執(zhí)行語句 2LEFT（ A1,14）截取 A1 單元格前 14 位數(shù)RIGHT （ A1,14）截取 A1 單元格后 14 位數(shù)身份證號(hào)碼有兩種，如 “或”“如”果 E1 是 15個(gè) ,為 19 加上從第 7 個(gè)開始取 2 個(gè)“86”加上 -加上從第 9 個(gè)開始取 2 個(gè)“02”加上-加上從第 11 個(gè)開始取 2 個(gè) “ 13合”起來為 “ 1986-02-13”，否則為從第 7 個(gè)開始取 4 個(gè) “1986”加上 -加上從第 11 個(gè)開始取 2 個(gè)“02”加上

4、-加上從第 13 個(gè)開始取 2 個(gè)“ 13”合起來為 “ 1986-02-13”。 "19" 、"-" 為直接寫入的數(shù)。1=IF(LEN(A1)=15,"19"&MID(A1,7,4),MID(A1,7,6)轉(zhuǎn)換出生年月取如 “198606”=IF(LEN(A1)=15,CONCATENATE("19",MID(A1,7,2),".",MID(A1,9,2),IF(LEN(A1)=18,CONCATE

5、NATE(MID(A1,7,4),".",MID(A1,11,2),"身份證錯(cuò) ") 轉(zhuǎn)換出生年月取如1986.05=2010-MID(B1,1,4)-IF(MID(B1,5,2)-0)>8,1,0)計(jì)算年齡=IF(LEN(A1)=15,YEAR(NOW()-1900-V ALUE(MID(A1,7,2),IF(LEN(A1)=18,YEAR(NOW()-V ALUE(MID(A1,7,4),"身份證錯(cuò) ") 計(jì)算年齡，月數(shù)全部不算如 24 歲 2 個(gè)月和 24 歲 11

6、個(gè)月都是 24 歲=IF(LEN(A1)=15,IF(MOD(V ALUE(RIGHT(A1,3),2)=0,"女 "," 男"),IF(LEN(A1)=18,IF(MOD(V ALUE(MID(A1,15,3),2)=0," 女 "," 男")," 身份證錯(cuò) ") 轉(zhuǎn)換性別二、成績(jī)?cè)谀昙?jí)里的排名菜 RANK （）函數(shù)的使用=RANK(N2,$N$2:$N$1501,0)N2 為所要排名的單元格， $N$2:$N$1

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

8、COUNTIF(B1:B25,">=90") 算 80 到 90 分人數(shù)=MAX(C1:C25) 算最高分=MIN(C1:C25) 算最低分=AVERAGE(C1:C25) 算平均分2=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 選定所有需要改變的單元格 右鍵

9、 選擇性粘貼 選" 乘" ，用 0 加計(jì)算也行在新單元格上轉(zhuǎn)換：選定新單元格， “=所要轉(zhuǎn)換的文本單元格 -0” 在新單元格上轉(zhuǎn)換：復(fù)制 選擇性粘貼 數(shù)值 點(diǎn)擊下拉 轉(zhuǎn)換成數(shù)值五、 &的應(yīng)用=B2&C2 表示 把兩 個(gè)單元 格的數(shù) 值合為 一個(gè)如 “ 45、” “ 67合” 為 “ 4567” =$F$17&C1 表示在一個(gè)數(shù)值前加一個(gè)數(shù)，在任意單元格輸入 123 設(shè)為絕對(duì)值，再加所要添加的單元格六、 IF 的應(yīng)用（滿足其中一個(gè)條件的判斷）=IF(A2>89," 優(yōu) +&

10、quot;,IF(A2>79," 優(yōu) ",IF(A2>69,"良 +",IF(A2>59," 及格 "," 不及格 ")=IF(A2>B2," 超預(yù)算 "," 預(yù)算內(nèi) ") 是對(duì)預(yù)算執(zhí)行結(jié)果的判斷=IF(A2=100,SUM(B5:B15),"")如果 A2 數(shù)字為 100，則計(jì)算單元格區(qū)域

11、 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 時(shí)返回 1，大于 10 小于 100 返回 2，大于等于 100 小于 200 返回 3 =IF(AND(A1=B1,A1=C1),1,0) 如果 A1=B1=C1 ，則在 D1 顯示 1，若不相等則返回 0

12、=IF(C1>60,IF(AND(C1>90)," 優(yōu)秀 "," 合格 ")," 不合格 ") 如果單元格 C1 的值大于 60，則執(zhí)行第二個(gè)參數(shù)，在這里為嵌套函數(shù)，繼續(xù)判斷單元格 C1 3的值是否大于 90（為了讓大家體會(huì)一下AND 函數(shù)的應(yīng)用，寫成 AND(C1>90) ，實(shí)際上可以僅寫 C1>90 ），如果滿足在單元格 C2 中顯示優(yōu)秀字樣，不滿足顯示合格字樣，如果 C1 的值以上條件都不滿足， 則執(zhí)行第三個(gè)參數(shù)即在單元格 C

13、2 中顯示不合格字樣。=IF(A1=1,"A",IF(A1=2,"B",IF(A1=3,"C",IF(A1 =4,"D",IF(A1=5,"E",IF(A1=6,"F",IF(A1=7,&quo t;G",IF(A1=8,"G","")&IF(A1=9,&quo

14、t;Q&q uot;,IF(A1=10,"l",IF(A1=11,"k",IF(A1=12,"y",IF (A1=13,"x",IF(A1=14,"n",IF(A1=15,"m",IF(A1=16,"o","")&,為多層嵌套七、 AND 函數(shù)（同時(shí)滿足條件判斷）AND （條件一，條件二

15、 .條件三 ,) ，條件一二三 , 都成立時(shí)返回TURE ，否則返回FALSE=IF(AND(A1>60,B1>60,C1>60),"及 格 ","不 及 格") ，當(dāng) AND （A1>60 ，B1>60 ，C1>60 ） 為 TURE 時(shí)返回 “及格 ”，為 FALSE 時(shí)返回 “不及格 ”=IF(A1<60," 不 及 格 ",IF(AND(A1>60,A1<

16、70),"及 格",IF(AND(A1>70,A1<85),"良好 ",IF(A1>85,"優(yōu)秀") 可以和 IF 函數(shù)合起來使用八、 CONCATENATE 函數(shù)（將幾 個(gè)文本字 符串合并為 一個(gè)文本 字符串）=CONCATENATE(A1,B1,C1,D1)也可以用 &（和號(hào)）運(yùn)算符代替函數(shù)CONCATENATE 實(shí)現(xiàn)文本項(xiàng)的合并。九、名稱和標(biāo)志為了更加直觀地標(biāo)識(shí)單元格或單元格區(qū)域， 我們可以給它們賦予一個(gè)名稱， 從而在公式或

17、函數(shù)中直接引用。 例如 “B2:B46”區(qū)域存放著學(xué)生的物理成績(jī)， 求解平均分的公式一般是 “=AVERAGE(B2:B46)” 。在給 B2:B46 區(qū)域命名為 “物理分?jǐn)?shù) ”以后，該公式就可以變?yōu)?“=AVERAGE( 物理分?jǐn)?shù) ) ”，從而使公式變得更加直觀。4給一個(gè)單元格或區(qū)域命名的方法是 :選中要命名的單元格或單元格區(qū)域，鼠標(biāo)單擊編輯欄頂端的 “名稱框 ”，在其中輸入名稱后回車。 也可以選中要命名的單元格或單元格區(qū)域，單擊 “插入 名稱 定義 ”菜單命令，在打開的 “定義名稱 ”對(duì)話框中輸入名稱后確定即可。 如果你要?jiǎng)h除已經(jīng)命名的區(qū)域， 可以按相同方法打開 “定義名稱 ”對(duì)話框，選中

18、你要?jiǎng)h除的名稱刪除即可。由于 Excel 工作表多數(shù)帶有 “列標(biāo)志 ”。例如一張成績(jī)統(tǒng)計(jì)表的首行通常帶有 “序號(hào) ”、“姓名 ”、“數(shù)學(xué) ”、“物理 ”等“列標(biāo)志 ”(也可以稱為字段 )，如果單擊 “工具 選項(xiàng) ”菜單命令，在打開的對(duì)話框中單擊 “重新計(jì)算 ”選項(xiàng)卡，選中 “工作簿選項(xiàng) ”選項(xiàng)組中的 “接受公式標(biāo)志 ”選項(xiàng)，公式就可以直接引用 “列標(biāo)志 ”了。例如 “B2:B46” 區(qū)域存放著學(xué)生的物理成績(jī)，而 B1 單元格已經(jīng)輸入了 “物理 ”字樣，則求物理平均分的公式可以寫成 “=AVERAGE( 物理 ) ”。十、幾個(gè)常用函數(shù)=ABS(A2) ，則在 A2 單元格中無論輸入正數(shù)（如 10

19、0）還是負(fù)數(shù)（如 -100），B2中均顯示出正數(shù)（如 100）=AND(A5>=60,B5>=60) ，確認(rèn)。如果 C5 中返回 TRUE ，說明 A5 和 B5 中的數(shù)值均大于等于 60，如果返回 FALSE，說明 A5 和 B5 中的數(shù)值至少有一個(gè)小于 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)行分

20、類匯總計(jì)算。Function_Num 函數(shù)51 AVERAGE 求算術(shù)平均數(shù)2 COUNT 計(jì)算參數(shù)列表中的數(shù)字項(xiàng)的個(gè)數(shù)3 COUNTA 計(jì)算單元格區(qū)域或數(shù)組中包含數(shù)據(jù)的單元格個(gè)數(shù)。 4 MAX 求最大值5 MIN 求最小值6 PRODUCT 單元格 6女=SUMPRODUCT($B$2:$B$11=$F2)*($C$2:$C$11=G$1),$D$2:$D$11) 計(jì)算男、女分別是中一或中二的總課時(shí)數(shù) （在 15 節(jié)單元格中輸入公式） 姓名 性別 A 男 B女C女D男E女F男G女H男I男J女職稱 課時(shí)中一 15 中二 16 中一14 中一 13 中一 18 中二 15 中二16 中一 14中

21、一17 中一 18性別中二中一男15女SUMSQ 函數(shù)：計(jì)算多個(gè)數(shù)值的平方和。 如 SUMSQ (B2,C2)=B2 的平方 +C2 的平方。ROUND 函數(shù)：如 ROUND(B2,2) 就是對(duì) B2 進(jìn)行四舍五入保留2 位小數(shù)。 INT（將數(shù)字向下舍入到最接近的取整函數(shù)）IF 和 AND 嵌套使 用： =IF(AND(A1>60,B1>60,C1>60),"及格"," 不及格 ") ，當(dāng) A1 ，B1,C1 都大于 60 時(shí) 返回 “及格 ”=IF(A1<60,&a

22、mp;quot;不及格",IF(AND(A1>60,A1<70),"及格",IF(AND(A1>70,A1<85)," 良好 ",IF(A1>85,"優(yōu)秀") ，當(dāng) A1<60時(shí)返回 “不及格 ”，當(dāng) 60<A1<70時(shí)返回 “及格 ”，當(dāng)70<A1<85時(shí)返回 “良好 ”，當(dāng) A1>85 時(shí)返回 “優(yōu)秀 ”7COUN

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

24、)*C17 如果訂單總額小于50000 則獎(jiǎng)金為10%；如果訂單總額大于等于50000，則獎(jiǎng)金為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 從后面取M

25、ID("This is an apple",6,2)=is 從中間取十四、取出當(dāng)前系統(tǒng)時(shí)間/日期信息NOW() 取當(dāng)前系統(tǒng) “年月日時(shí)分 ”TODAY() 取當(dāng)前系統(tǒng) “年月日 ”YEAR(E5)=2001 取單元格的 “年”MONTH(E5)=5 取單元格的 “月 ”DAY(E5)=30 取單元格的 “日”HOUR(E5)=12 取單元格的 “時(shí)”8DATEDIF ：計(jì)算兩個(gè)日期之間的天數(shù)、月數(shù)或年數(shù)：其中計(jì)算年數(shù)為DATEDIF(A24,TODAY(),"y") ， "Y" 時(shí)間段中的整年數(shù)，時(shí) 間 段 中 的 整 月 數(shù)， "D" 時(shí) 間 段 中 的天 數(shù)， "M""MD" 為日期中天數(shù)的差，忽略日期中的月和年（直接天數(shù)相減，不夠減要向上月借一），"YM" 為日期中月數(shù)的差，忽略日期中的日和年（直接月數(shù)相減，不夠減要向上月借一） ， "YD" 為日期中天數(shù)的差。忽略日