Excel函數(shù)應用大全(篇)

Excel函數(shù)應用大全目錄一、concatenate十、TEXT二十二、DCOUNTA二、EXACT十一、TRIM二十三、DMAX三、LEFT或LEFTB十二、UPPER二十四、DMIN和right用法一樣十三、value二十五、DSUM四、LEN或LENB十四、AVERAGE二十六、mod函數(shù)五、LOWER十五、AVERAGEA二十七、SUMIF六、MID或MIDB十六、COUNT二十八:ISERROR七、REPLACE或十七、COUNTAandfind函數(shù)REPLACEB十八、COUNTBLANK二十九、rank排名八、SUBSTITUTE十九、COUNTIF函數(shù)九、T二十、DAVERAGE三十、rand隨機函二十一-、DCOUNT數(shù)一、CONCATENATE用途：將若干文字串合并到一個文字串中，其功能與運算符相同。語法：CONCATENATE(textl,text2,...)參數(shù)：Textl,text2,...為1到30個將要合并成單個文本的文本項，這些文本項可以是文字串、數(shù)字或?qū)蝹€單元格的引用。實例：如果Al=98、A2=千米，則公式'CONCATENATE(Al,A2)”返回“98千米”，與公式“=A1&A2”等價。即將兩個單元格數(shù)據(jù)合并在一起。舉例：調(diào)資將一級教師加100元，二級教師加80元。=if(bl=M一級教師”，concatenate(cl+100),concatenate(cl+80))說明：B列是職稱，C列是工資。如果再有三級教師，高級教師在進行if語句疊加。=if(bl=M一級教師”,concatenate(cl+100),if(bl=M高級教師”,concatenate(cl+120),if(bl="二級教師"，concatenate(cl+80),concatenate(cl+60)),可以疊加三層。本公式意思是：如果B1單元格中是一級教師則C1+100元，如果是高級教師則C1+120元，如果B1單元格是二級教師，則C1+80元，其他+60元.加稱呼：=if(Al="男"，concatenate(Bl,“先生"),concatenate(Bl,“女士”))例：提取身份證信息CONCATENATE(MID(A2,7,4),MID(A2,11,2),MID(A2,13,2))▲二、EXACT用途：測試兩個字符串是否完全相同。如果它們完全相同，則返回TRUE;否則返回FALSE。EXACT函數(shù)能區(qū)分大小寫，但忽略格式上的差異。語法：EXACT(textl,text2)?參數(shù)：Textl是待比較的第一個字符串，Text2是待比較的第二個字符串。實例：如果Al=物理、A2=化學A3=物理，則公式?=EXACT(A1,A2)”返回FALSE,=EXACT(Al,A3)返回FALSE,=EXACT("word","word")返回TRUE.舉例：如測試兩列數(shù)據(jù)是否完全一致，可用此公式：=exact(Al：Bl),如果一樣則顯示true,否則顯示FALSE.舉例：檢查兩個字符是否相同：=Exact(textl,text2)三、LEFT或LEFTB和right用法一樣。用途：根據(jù)指定的字符數(shù)返回文本串中的第一個或前幾個字符。此函數(shù)用于雙字節(jié)字符。語法：LEFT(text,num-chars)或LEFTB(text,num_bytes).參數(shù)：Text是包含要提取字符的文本串；Num_chars指定函數(shù)要提取的字符數(shù)，它必須大于或等于0。Num.bytes按字節(jié)數(shù)指定由LEFTB提取的字符數(shù)。實例：如果Al=電腦愛好者，則LEFTQ1,2)返回“電腦”，LEFTB(A1,2)返回“電”。舉例：提取姓=if(len(Cl)=4,left(Cl,2),left(Cl,1))即：檢查cl單元格如果是四個字，則提取左邊兩個字，否則提取左邊一個字。提取名=if(len(Cl)=2,right(Cl,1),right(Cl,2))即：檢查Cl單元格如果是兩個字則，提取右邊第一個字，否則提取右邊第一和第二個字。例如：分解單元格為多個部分A1單元格數(shù)據(jù)15yearsold分解成15yearsold三部分分別存放于B1,Cl、D1單元格中Bl=left(A1,2)Cl=mid(Al,3,2)Dl=right(Al,8)中文姓名若是3個字Bl=left(Al,3)Cl=mid(Al,4,2)Dl=right(Al,8)四、LEN或LENB用途：LEN返回文本串的字符數(shù)。LENB返回文本串中所有字符的字節(jié)數(shù)。語法:LEN(text)或LENB(text)。參數(shù)：Text待要查找其長度的文本。注意：此函數(shù)用于雙字節(jié)字符，且空格也將作為字符進行統(tǒng)計。實例：如果Al=電腦愛好者，則公式“=LEN(AD”返回5,=LENB(A1)返回10。說明：檢測的字符數(shù)含空格。五、LOWER用途：將一個文字串中的所有大寫字母轉(zhuǎn)換為小寫字母。語法：LOWER(text).語法：Text是包含待轉(zhuǎn)換字母的文字串。注意：LOWER函數(shù)不改變文字串中非字母的字符。LOWER與PROPER和UPPER函數(shù)非常相似。實例：如果AbExcel,則公式“=LOWER(AD”返回excel.▲六、MID或MIDB用途：MID返回文本串中從指定位置開始的特定數(shù)目的字符，該數(shù)目由用戶指定。MIDB返回文本串中從指定位置開始的特定數(shù)目的字符，該數(shù)目由用戶指定。MIDB函數(shù)可以用于雙字節(jié)字符。語法：MID(text,start-num,num_chars)或MIDB(text,start-num,num.bytes).參數(shù)：Text是包含要提取字符的文本串。Start-num是文本中要提取的第一個字符的位置，文本中第一個字符的start-num為1,以此類推;Num-chars指定希望MID從文本中返回字符的個數(shù);Num_bytes指定希望MIDB從文本中按字節(jié)返回字符的個數(shù)。實例：如果al=電子計算機，則公式U=MID(A1,3,2)”返回“計算”,=MIDB(A1,3,2)返回“子”。例1、提取身份證中的出生日期和性別信息：=TEXT(MID(Al6,7,8),"00-00-00")即：提取身份證信息中A16單元格中的從第七位開始的六位數(shù)中間用-分隔開。僅限于18位的身份證。如果是15為的改為=TEXT(MID(A16,7,6)00-00-00"),如71-01-07如果想讓71前面加上19,可以這樣：19&A17.即將19合并到某個單元格中。=TEXT(IF(LEN(A1)=15,"19",)&MID(Al,7,IF(LEN(Al)=18,8,6)),"####-##-##")假如身份證號數(shù)據(jù)在Al單元格，在B1單元格中編輯公式=IF(LEN(A1)=15,"19"&MID(A1,7,2)&MID(A1,9,2)&MID(A1,11,2),MID(A1,7,4)&MID(A1,11,2)&MID(A1,13,2))這樣輸出格式就都是19821010這種格式了。例2,提取性別信息：假定身份證號在Al單元格，則在B1單元格中編輯公式=IF(AND(LEN(Al)=15,MOD(MID(Al,15,1)*1,2)=1),IF(AND(LEN(Al)=18,MOD(MID(Al,17,1)*1,2)=1),"男"，"女"))=IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,"男","女")LEN(C2)=15：檢查身份證號碼的長度是否是15位。MID(C2,15,1)：如果身份證號碼的長度是15位，那么提取第15位的數(shù)字。MID(C2,17,1)：如果身份證號碼的長度不是15位，即18位身份證號碼，那么應該提取第17位的數(shù)字。MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)：用于得到給出數(shù)字除以指定數(shù)字后的余數(shù)，本例表示對提出來的數(shù)值除以2以后所得到的余數(shù)。IF(MOD(IF(LEN(C2)=15,MID(C2,15,1),MID(C2,17,1)),2)=1,"男"，"女")：如果除以2以后的余數(shù)是1,那么B2單元格顯示為“男”，否則顯示為“女。簡單公式提取出生年月：=mid(Al,7,if(len(Al)=15,6,if(len(Al)=18,8,0)))▲七、REPLACE或REPLACEB用途：REPLACE使用其他文本串并根據(jù)所指定的字符數(shù)替換另一文本串中的部分文本。REPLACEB的用途與REPLACE相同，它是根據(jù)所指定的字節(jié)數(shù)替換另一文本串中的部分文本。語法：REPLACE(old.text,start-num,num.chars,new.text),REPLACEB(old_text,start-num,num_bytes,new.text)o參數(shù)：Old-text是要替換其部分字符的文本;Start-num是要用new.text替換的old_text中字符的位置；Num_chars是希望REPLACE使用new_text替換old-text中字符的個數(shù)；Num-bytes是希望REPLACE使用new.text替換old-text的字節(jié)數(shù);New-text是要用于替換old-text中字符的文本。注意：以上兩函數(shù)均適用于雙字節(jié)的漢字。實例：如果Al=學習的革命、A2=電腦，則公式^REPLACE(A1,3,3,A2)"返回“學習電腦"，=REPLACEB(A1,2,3,A2)返回“電腦的革命”。即：用A2單元格即“電腦”代替了“的革命”完成了文本的替換。再如：=REPLACE(Al,7,4,"xxxx")370784199804185234370784xxxx04185234八、SUBSTITUTE用途：在文字串中用new-text替代old-text。如果需要在一個文字串中替換指定的文本，可以使用函數(shù)SUBSTITUTE;如果需要在某一文字串中替換指定位置處的任意文本，就應當使用函數(shù)REPLACE。語法：SUBSTITUTE(text,old-text,new.text,instance-num)?參數(shù)：Text是需要替換其中字符的文本，或是含有文本的單元格引用；OlcLtext是需要替換的舊文本;New-text用于替換old-text的文本；Instance-num為一數(shù)值，用來指定以new-text替換第幾次出現(xiàn)的old_text;如果指定了instance-num,則只有滿足要求的old-text被替換；否則將用new.text替換Text中出現(xiàn)的所有old-texto實例：如果Al=學習的革命、A2=電腦，則公式^SUBSTITUTE(Al,"的革命"，A2,D”返回“學習電腦”九、T用途：將數(shù)值轉(zhuǎn)換成文本。語法:T(value).參數(shù)：value是需要進行測試的數(shù)據(jù)。如果value本身就是文本，或是對文本單元格的引用，T函數(shù)將返回value;如果沒有引用文本，則返回""(空文本)。實例：如果A1中含有文本“電腦”，則公式“=T(AD”返回“電腦”?！EXT用途：將數(shù)值轉(zhuǎn)換為按指定數(shù)字格式表示的文本。語法：TEXT(value,format.text)?參數(shù)：value是數(shù)值、計算結(jié)果是數(shù)值的公式、或?qū)?shù)值單元格的引用；format.text是所要選用的文本型數(shù)字格式，即“單元格格式”對話框“數(shù)字”選項卡的“分類”列表框中顯示的格式，它不能包含星號。注意：使用“單元格格式”對話框的“數(shù)字”選項卡設(shè)置單元格格式，只會改變單元格的格式而不會影響其中的數(shù)值。使用函數(shù)TEXT可以將數(shù)值轉(zhuǎn)換為帶格式的文本，而其結(jié)果將不再作為數(shù)字參與計算。例：將20060606格式轉(zhuǎn)換成2006-06-06的樣式：可以這樣操作假設(shè)數(shù)據(jù)在A2單元格，在B2單元格中輸入公式-text(Al,"????-??-??")回車即可.例：簡單公式15位的=TEXT(MID(A16,7,6),"00-00-00");如果是18位的則-TEXT(MID(A16,7,8),"00-00-00").或在15位的基礎(chǔ)上再用公式：-"19"4A1A十一、TRIM用途：除了單詞之間的單個空格外，清除文本中的所有的空格。如果從其他應用程序中獲得了帶有不規(guī)則空格的文本，可以使用TRIM函數(shù)清除這些空格。語法:TRIM(text).參數(shù)：Text是需要清除其中空格的文本。實例：如果Al=FirstQuarterEarnings,則公式“=TRIM(A1)”返回"FirstQuarterEarnings”。十二、UPPER用途：將文本轉(zhuǎn)換成大寫形式。語法：UPPER(text).參數(shù)：Text為需要轉(zhuǎn)換成大寫形式的文本，它可以是引用或文字串。實例：公式“=UPPER("apple")”返回APPLE?！?、value用途：將表示數(shù)字的文字串轉(zhuǎn)換成數(shù)字。語法：value(text).參數(shù)：Text為帶引號的文本，或?qū)π枰M行文本轉(zhuǎn)換的單元格的引用。它可以是Excel可以識別的任意常數(shù)、日期或時間格式。如果Text不屬于上述格式，則value函數(shù)返回錯誤值#value!。注意：通常不需要在公式中使用value函數(shù)，Excel可以在需要時自動進行轉(zhuǎn)換。value函數(shù)主要用于與其他電子表格程序兼容。實例：公式“=value("￥ 1 , 000") ”返回1000;=value("16:48:00")-value("12:00:00")返回0.2,該序列數(shù)等于4小時48分鐘?！?、AVERAGE用途：計算所有參數(shù)的算術(shù)平均值。語法：AVERAGE(numberl,number2,...)o參數(shù)：Numberl.number2、?..是要計算平均值的1?30個參數(shù)。實例：如果A1:A5區(qū)域命名為分數(shù)，其中的數(shù)值分別為100、70、92、47和82,則公式“-AVERAGE(分數(shù))”返回78.2?！?、AVERAGEA用途：計算參數(shù)清單中數(shù)值的平均值。它與AVERAGE函數(shù)的區(qū)別在于不僅數(shù)字，而且文本和邏輯值(如TRUE和FALSE)也參與計算。語法：AVERAGEA(valuel,value2,...)參數(shù)：valuel、value2、...為需要計算平均值的1至30個單元格、單元格區(qū)域或數(shù)值。實例：如果Al=76、A2=85、A3=TRUE,則公式U=AVERAGEA(Al:A3)"返回54(即76+85+1/3=54)?！?、COUNT用途：返回數(shù)字參數(shù)的個數(shù)。它可以統(tǒng)計數(shù)組或單元格區(qū)域中含有數(shù)字的單元格個數(shù)。語法：COUNT(valuel,value2,...).參數(shù)：valuel,value2,...是包含或引用各種類型數(shù)據(jù)的參數(shù)(1?30個)，其中只有數(shù)字類型的數(shù)據(jù)才能被統(tǒng)計。實例：如果Al=90、A2=人數(shù)、A3=""、A4=54、A5=36,則公式tt=COUNT(Al:A5)"返回3?！?、COUNTA用途：返回參數(shù)組中非空值的數(shù)目。利用函數(shù)COUNTA可以計算數(shù)組或單元格區(qū)域中數(shù)據(jù)項的個數(shù)。語法:COUNTA(valuel,value2,...)說明：valuel,value2,...所要計數(shù)的值，參數(shù)個數(shù)為1?30個。在這種情況下的參數(shù)可以是任何類型，它們包括空格但不包括空白單元格。如果參數(shù)是數(shù)組或單元格引用，則數(shù)組或引用中的空白單元格將被忽略。如果不需要統(tǒng)計邏輯值、文字或錯誤值，則應該使用COUNT函數(shù)。實例：如果Al=6.28,A2=3.74,其余單元格為空，則公式“=COUNTA(Al:A7)”的計算結(jié)果等于2?！榭磫卧袷欠窨罩禃r用=counta(),若返回1,有數(shù)據(jù)，。則無數(shù)據(jù)。十八、COUNTBLANK用途：計算某個單元格區(qū)域中空白單元格的數(shù)目.語法：COUNTBLANK(range)參數(shù)：Range為需要計算其中空白單元格數(shù)目的區(qū)域。實例：如果Al=88、A2=55、A3="\A4=72、A5="",則公式U=COUNTBLANK(Al:A5)M返回2?！?、COUNTIF用途：計算區(qū)域中滿足給定條件的單元格的個數(shù)。語法：COUNTIF(range,criteria)參數(shù)：Rang為需要計算其中滿足條件的單元格數(shù)目的單元格區(qū)域。Criteria為確定哪些單元格將被計算在內(nèi)的條件，其形式可以為數(shù)字、表達式或文本。統(tǒng)計某個數(shù)據(jù)的個數(shù)如統(tǒng)計一級教師的個數(shù)=countif(a2：tl23,“一級教師”)統(tǒng)計某些單元格數(shù)據(jù)區(qū)域符合指定條件的數(shù)據(jù)的個數(shù)：=countif($A$1:$A$24,"一年級”)。即求：在Al：A24數(shù)據(jù)區(qū)域中，是一年級的數(shù)據(jù)的個數(shù).再如統(tǒng)計>98的學生人數(shù)=COUNTIF(C2:C36,">98")注意:條件除純數(shù)字外必須用“”引起來。兩個條件求個數(shù)

>10并且<=15=SUM(COUNTIF(data,⑵ >=10>10并且<=15=SUM(COUNTIF(data,⑵ >=10并且<15=SUM(COUNTIF(data,">="&{10,15})*{1,-1})⑶ >=10并且<=15=SUM(COUNTIF(data,{">=10",">15"))*{1,-1))(4) >10并且<15-SUM(COUNTIF(data,{">10",">=15"})*(1,-1})舉例：防止輸入重復身份證號碼在單位制作員工花名冊等表格時，員工的身份證號碼應該是唯一的，我們可以通過“數(shù)據(jù)有效性”來防止重復輸入：選中需要輸入身份證號碼的單元格區(qū)域(如D2至D101),執(zhí)行“數(shù)據(jù)~有效性”命令，打開“數(shù)據(jù)有效性”對話框，在“設(shè)置”標簽下，按“允許”右側(cè)的下拉按鈕，在隨后彈出的快捷菜單中，選擇“自定義”選項，然后在下面“公式”方框中輸入公式:=COUNTIF(D:D,D2)=L確定返回。以后在上述單元格中輸入了重復的身份證號碼時，系統(tǒng)會彈出提示對話框(如圖1),并拒絕接受輸入的號碼。再如：在excel表中，比較B列和A列的數(shù)據(jù)，把A列中在B列包含的數(shù)據(jù)在C列中表示出來=IF(COUNTIF(B:B,A1&"*"),A1,"")如果是讓B列中在A列包含的數(shù)據(jù)在C列中顯示出來只要修改一下公式即可：=IF(COUNTIF(A:A,B1&"*"),Bl,"") ▲二十、DAVERAGE參數(shù)：返回數(shù)據(jù)庫或數(shù)據(jù)清單中滿足指定條件的列中數(shù)值的平均值。語法：DAVERAGE(database,field,criteria)參數(shù)：Database構(gòu)成列表或數(shù)據(jù)庫的單元格區(qū)域。Field指定函數(shù)所使用的數(shù)據(jù)列。Criteria為一組包含給定條件的單元格區(qū)域。二十一、DCOUNT參數(shù):返回數(shù)據(jù)庫或數(shù)據(jù)清單的指定字段中，滿足給定條件并且包含數(shù)字的單元格數(shù)目。語法：DCOUNT(database,field,criteria)參數(shù)：Database構(gòu)成列表或數(shù)據(jù)庫的單元格區(qū)域。Field指定函數(shù)所使用的數(shù)據(jù)列。Criteria為一組包含給定條件的單元格區(qū)域。二十二、DCOUNTA參數(shù):返回數(shù)據(jù)庫或數(shù)據(jù)清單指定字段中滿足給定條件的非空單元格數(shù)目。語法：DCOUNTA(database,field,criteria)參數(shù)：Database構(gòu)成列表或數(shù)據(jù)庫的單元格區(qū)域。Field指定函數(shù)所使用的數(shù)據(jù)列。Criteria為一組包含給定條件的單元格區(qū)域。二十三、DMAX參數(shù):返回數(shù)據(jù)清單或數(shù)據(jù)庫的指定列中，滿足給定條件單元格中的最大數(shù)值。語法：DMAX(database,field,criteria)參數(shù)：Database構(gòu)成列表或數(shù)據(jù)庫的單元格區(qū)域。Field指定函數(shù)所使用的數(shù)據(jù)列。Criteria為一組包含給定條件的單元格區(qū)域。二十四、DMIN參數(shù):返回數(shù)據(jù)清單或數(shù)據(jù)庫的指定列中滿足給定條件的單元格中的最小數(shù)字。語法：DMIN(database,field,criteria)參數(shù)：Database構(gòu)成列表或數(shù)據(jù)庫的單元格區(qū)域。Field指定函數(shù)所使用的數(shù)據(jù)列。Criteria為一組包含給定條件的單元格區(qū)域。二十五、DSUM參數(shù):返回數(shù)據(jù)清單或數(shù)據(jù)庫的指定列中，滿足給定條件單元格中的數(shù)字之和。語法：DSUM(database,field,criteria)二十六、mod函數(shù)MOD返回兩數(shù)相除的余數(shù)。結(jié)果的正負號與除數(shù)相同。語法MOD(number,divisor)MOD函數(shù)語法具有下列參數(shù)：Number必需。被除數(shù)。Divisor必需。除數(shù)。說明如果divisor為零，函數(shù)MOD返回錯誤值#DIV/0!.函數(shù)MOD可以借用函數(shù)INT來表示：MOD(n,d)=n-d*INT(n/d)舉例：=IF(MOD(MID(A2,17,1),2)=1,"男"J女")假設(shè)數(shù)據(jù)單元格在A2,存放性別在C2。=(MID(A2,17,1),2)=1,"男"，"女"說明：提取A2單元格從第17位開始的1位數(shù)字；=MOD(MID(A2,17,1),2)=1,說明：17位數(shù)字除以2,如果余L整個公式就是如果檢測A2單元格的17位開始的后一位數(shù)字被2除余1則在C2中顯示“男”，否則顯示“女”?！摺UMIFsumif函數(shù)的用法是根據(jù)指定條件對若干單元格、區(qū)域或引用求和。sumif函數(shù)語法是：SUMIF(range,criteria,sum.range)sumif函數(shù)的參數(shù)如下：第一個參數(shù)：Range為條件區(qū)域，用于條件判斷的單元格區(qū)域。第二個參數(shù)：Criteria是求和條件，由數(shù)字、邏輯表達式等組成的判定條件。第三個參數(shù)：Sum.range為實際求和區(qū)域，需要求和的單元格、區(qū)域或引用。當省略第三個參數(shù)時，則條件區(qū)域就是實際求和區(qū)域。criteria參數(shù)中使用通配符(包括問號(?)和星號(*))。問號匹配任意單個字符；星號匹配任意一串字符。如果要查找實際的問號或星號，請在該字符前鍵入波形符C).舉例：求某些單元格中數(shù)值的和：=sumif(Al:A24,100).即求Al：A24單元格中分數(shù)是100分的所有人數(shù)的總和。注意：此處的100,因為是數(shù)字，可以不用"，如果是非數(shù)字必須用引起來.求報表中各欄目的總流量如下圖:啟“｛二ERIT F，2:F，1。.F.3.「二：113"—,- [ - F日期2010年4月1日欄目辦公軌件2010年4月2日2010年4月3日期2010年4月1日欄目辦公軌件2010年4月2日2010年4月3日201。車4月4日2010軍4月5日網(wǎng)站運營工具軟件452010年5月6日2010軍5月7日2010年5月8日電^醫(yī)院辦公軟件辦公軟件網(wǎng)絡(luò)安全66138212-861O8O■3445533欄目辦公軟件網(wǎng)站運營電腦醫(yī)院工具軟件網(wǎng)絡(luò)安全55辦公軟件網(wǎng)站運營電腦醫(yī)院1415201碎6月13日201迷6月140162010年6月15日172010年6月16日12201迷6月17B192010年6月18日202010年5月9日2010年5月10日2010年5月11日南站運營1087電腦醫(yī)院 791網(wǎng)站運營721辦公牛件 675_網(wǎng)站運營鞋魂觸1電腦醫(yī)院11601T部落離ee.ittx-ibalwo.cqib^,供此實例選中F2單元格，輸入公式：=SUMIF(B2:B19,E2,C2:C19),輸入公式完成后，按鍵盤上ctrl+shift+enter組合鍵(必須按此ctr1+shift+enter組合鍵數(shù)組公式才能得到正確結(jié)果)，即可統(tǒng)計出辦公軟件欄目的總流量.以此類推，選中F3單元格，輸入公式：=SUMIF(B2:B19,E3,C2:C19),輸入公式完成后，按鍵盤上ctrl+shift+enter組合鍵，可以求得網(wǎng)站運營欄目的總流量。選中F4單元格，輸入公式：=SUMIF(B2:B19,E4,C2:C19),輸入公式完成后,按鍵盤上ctr1+shift+enter組合鍵，可以求得電腦醫(yī)院欄目的總流量。選中F5單元格，輸入公式：=SUMIF(B2:B19,E5,C2:C19),輸入公式完成后,按鍵盤上ctr1+shift+enter組合鍵，可以求得工具軟件欄目的總流量。二十八：ISERRORandfind函數(shù)實例：在Excel表中，查找某一列的數(shù)字中哪些數(shù)字中含有4,然后挑選出來成一列1、假定原數(shù)據(jù)在A列，請在B1單元格輸入如下公式：=IF(ISERROR(FIND(4,A1,1A1)用填充柄將公式向下復制到相應行。2、選定B列點“復制”，在-一新工作表中的A1單元格點鼠標右鍵，點“選擇性粘貼/數(shù)值”，點“確定

說明：含有4的數(shù)字在B列和A列相應位置列出，如圖:3、在新工作表B1單元格輸入公式:=IF(A1="",0,1)用填充柄將公式向下復制到相應行。4、4、將新工作表按B列遞減排序，再刪除B列，A列就是你要的結(jié)果。▲4I:包?▼上LlftuuuK世H.uuum?.22pNs.二十九、rank排名函數(shù)rank函數(shù)是返回一個數(shù)在一組數(shù)中的排名位置。語法為：RANK(number,ref,order)Number：為需要找到排位的數(shù)字。即數(shù)據(jù)所在單元格。Ref：為數(shù)字列表數(shù)組或?qū)?shù)字列表的引用(要使用絕對引用即用$鎖定)。Ref中的非數(shù)值型參數(shù)將被忽略。Order：為一數(shù)字，指明排位的方式。如果order為0(零)或省略，Excel對數(shù)字的排位是基于ref為按照降序排列的列表。如果order不為零，Excel對數(shù)字的排位是基于ref為按照升序排列的列表。函數(shù)RANK對重復數(shù)的排位相同。但重復數(shù)的存在將影響后續(xù)數(shù)值的排位。例如，在一列按升序排列的整數(shù)中，如果整數(shù)10出現(xiàn)兩次，其排位為5,則11的排位為7(沒有排位為6的數(shù)值)。假如A列從A1單元格起，依次有數(shù)據(jù)80、98、65、79、65?在B1中編輯公式=RANK(A1,$A$1:$A$5,0)回車確認后，向下復制公式到B5單元格。效果：從B1單元格起依次返回值為2、1、4、3、4O注意：公式中的參數(shù)0(零)或省略，Excel對數(shù)字的排位是把數(shù)據(jù)從大到小的降序排列，也就是說數(shù)值最大的排名第1。再試試看：在C1中編輯公式=RANK(A1,$A$1:$A$5,1)回車確認后，向下復制公式到B5單元格。此時從C1單元格起依次返回的值是4、5、1、3、1。也就是說，此時A列中數(shù)據(jù)是按從小到大排列名次的，最小的數(shù)值排位第1,最大的數(shù)值排位最末。再如：A列有某班級的分數(shù)，在B列得到名次，假設(shè)A1\B1是表頭，到A30是最后一個人，應該在B2單元格插入函數(shù)=好位(A2,$A$2：$A$30),分數(shù)?般是按降序排，所以rank的第三個參數(shù)可以省略，再用填充柄填充下去再舉例：A列有某些人的身高，要在B列按從矮到高的順序拍名次的話，應在B2單元格插入函數(shù)=四欣(A2,$A$2：$A$30,1),第三個參數(shù)非0就表示升序，省略或0代表降序的名次，再用填充柄填充下去三十、rand隨機函數(shù)在某個單元格中輸入公式=l+rand()*49,按F9鍵(一定是F9鍵)，即顯示1到50的所有隨機數(shù)，如果是顯示隨機整數(shù)，請設(shè)定單元格格式-數(shù)值-小數(shù)位數(shù)為0即可。如果是從2到100,改成=2+rand()*99即可。該函數(shù)不需要參數(shù)?！C合篇1、錯誤身份證快速找出作為辦公人員，經(jīng)常要編寫關(guān)于職工信息檔案的表格，其中最常輸入的數(shù)據(jù)就是身份證號碼，由于人員眾多，輸入確實麻煩，一不小心就可能把員工的出生日期弄錯了，這可是關(guān)系到每個員工的切身利益，如工齡、社會保險金等。要是逐一驗證那可是?件讓人頭疼的事情。下面筆者就教大家?招，通過此方法就能夠很快查找出Excel表中錯誤的身份證號碼。下面以“XXX公司員工信息統(tǒng)計表”為例(圖Do首先從B列的身份證號碼(注：輸入前必須將該列單元格格式統(tǒng)一為“文本”)中提取出生日期到C歹U，以C3為例，選中C3單元格，然后在“編輯欄”中輸入如下公式：=TEXT(IF(LEN(B3)=15,"19”,)&MID(B3,7,IF(LEN(B3)=18,8,6)),〃####-##-##〃)，該公式運用了文本函數(shù)，可智能判斷18位和15位的號碼，并從中提取出生日期。待全部提取完之后，大家就會發(fā)現(xiàn)，出生日期中竟出現(xiàn)了“1980T3T5”(C4),請問有誰能在這樣的日期出生呢？很顯然這是錯誤的信息。接下來我們要做的事情就是快速地把所有錯誤信息查找出來。全選C列的出生日期C3:C4并單擊“復制”按鈕，然后把光標定位到D列單元格，點擊工具欄中“粘貼''按鈕右側(cè)的小箭頭并選擇“值''選項(圖2)。

將所有日期復制到D列之后，D列的出生日期全部處于選中狀態(tài)，并且在D3單元格左側(cè)出現(xiàn)一個智能提示符號，點擊提示符右側(cè)的小箭頭并選擇“轉(zhuǎn)換為數(shù)字(C)”選項(圖3)。一?： ita?一?： ita? ?*:，%工oSTr?； ■口！ *~Ctu￡1BjUEB3x.CDm|>fr*AIAlhu..kl?A*1974-10-24XXX公司員工信息統(tǒng)計表非三工四非三工四XXXmi97410244425XXXXXX198013154421 12Ilzk!k2￡1闞T3-15這樣D列的數(shù)據(jù)就由文本格式轉(zhuǎn)換成了真實的“日期”格式，細心的你就會發(fā)現(xiàn)只有正確的日期才能成功轉(zhuǎn)換，其對齊方式為“右對齊”，而不正確的日期則仍是文本格式，對齊方式仍為“左對齊''(圖4)。有了比較，大家…眼就能看出不正確的身份證號碼，更正起來那是相當?shù)娜菀琢恕?、電子表格中輸入已有的文本數(shù)據(jù)一外部數(shù)據(jù)一導入數(shù)據(jù)一按照提示操作即可，文本文件變成單元格輸入的數(shù)據(jù)。3、excel如何操作才能按長度長短排序如：達到下列效果。1:XXXXX2：xxx3：xxxx4：xx如何操作排序才能達到這樣的效果1:XX2：xxx3：xxxx4：xxxxx數(shù)據(jù)在A列，B1輸入=LEN(A1)回車并向下填充點選B列任一格——工具欄——升序排序補充:你沒有按照我講的方法操作。函數(shù)“LEN”就是返回單元格文本字符串中的字符數(shù)。xxxxx 5、xxx 3o選B列排序，就是按A列字符數(shù)長短排序。(B列是個“敲門磚”，排序后可以刪除)3、兩字姓名中插入空格=IF(LEN(C2)=2,REPLACE(C2,2,,""),C2)遇至IJ4個字名字的也不會插入空格了。=if(len(a1)=3,a1,left(a1)&""&right(a1)),此公式意思是檢查單元格的名字如果是3個字的，保持不變，否則是將2個字的左右兩邊鍵入空格。&“”&

*jMBs <V IHB。>g*■*jMBs <V IHB。>g*■“s ??S?=IF(LEN(B3)=2,LEFT(B3,1)&"BRIGHT(B3