條據(jù)書信 isnumber函數(shù)的使用方法及實例.doc

1、isnumber函數(shù)的使用方法及實例isnumber函數(shù)的使用方法及實例Excel函數(shù)使用實例唱潤剛1快捷輸入ALT+178(M),ALT+179(M)Alt+10(代表excel中alt+enter)2excel只能顯示小數(shù)點后6位,多于6位會自動四舍五入到小數(shù)點后1位（0.0999999會顯示成0.1）或整數(shù)位（0.9999999會顯示成1）。在自動篩選時，會按實際數(shù)據(jù)進行大小對比，比如，0.0999999，當篩選條件為大于等于0.1時，這個數(shù)據(jù)無法被篩選出來，按0.09999990.1對待。3引用單元格的各種方法對比1)Indirect(ref_text,a1)簡單引用，使用Indire

2、ct($a$1)；表示列號和行號的方式同時為文本時，使用”a1”樣式；不同時為文本，比如一個是文本一個為引用（或函數(shù)），或者兩者都是引用（或函數(shù)），可以用連字符，連接列號和行號，如”a”b1；”a”row()；a1”2”；a1row()。如：需要引用a1的值，則為=indirect(“a1”)，如果需要使用a1單元格內(nèi)的內(nèi)容作為參數(shù)，則=indirect(a1a2),其中，a1中應為a、b等字母，a2中應為數(shù)字。2)Index(reference,row_num,column_num,area_num)Reference對一個或多個單元格區(qū)域的引用，格式為：(A1:B4,D1:E4,G1:H4

3、)，然后利用area_num參數(shù)表示需要返回的區(qū)域數(shù)，用1、2、3等數(shù)字表示。=index(a:a,1)表示a1單元格。=index(1:1,2)表示b1單元格。3)Address(row_num,column_num,abs_num,a1,sheet_text)abs_num：1或省略，絕對引用；2絕對行號，相對列標；3相對行號，絕對列標；4相對引用indirect可以返回單一的單元格，沒有數(shù)組功能index函數(shù)的行號和列號是相對于引用范圍而言的address利用列號和行號就可以引用某個單元格，這里的行號和列號是相對于整個表格的4替換掉單元格內(nèi)空格1)保留兩個符號中間的一個，去掉前后的所有空

4、格。因為在trim公式中，兩個字符之間有一個單元格的ASCII碼值為160，而其他普通空格是32。=trim()。2)替換掉單元格內(nèi)所有空格需使用substitute函數(shù)，先替換出里面的保留空格。char(32)普通空格char(160)特殊空格CLEAN函數(shù)被設計為刪除文本中7位ASCII碼的前32個非打印字符（值為0到31）。在Unicode字符集（Unicode：UnicodeConsortium開發(fā)的一種字符編碼標準。該標準采用多（于一）個字節(jié)代表每一字符，實現(xiàn)了使用單個字符集代表世界上幾乎所有書面語言。）中，有附加的非打印字符（值為127、129、141、143、144和157）。C

5、LEAN函數(shù)自身不刪除這些附加的非打印字符。clean可以清除0到31的非打印符號，所以可用以下公式替換單元格中所有的空格變成非打印符號以清除空格。其中的7可以換成0到31中的任意數(shù)。=clean(substitute(a1,char(32),char(7)。5替換函數(shù)有兩個：1)replace()替換掉指定位置的任意字符，將“001-107”數(shù)字1加上2。=REPLACE(D2,FIND(-,D2)-1,1,MID(D2,FIND(-,D2)-1,1)+2)。2)substitute()替換掉指定字符：=SUBSTITUTE(A1,123,456,3)。第三個參數(shù)用來確定替換掉第幾次出現(xiàn)的指

6、定字符，例中指第三次123，用456替換。如不指定，將替換所有的123。isnumber函數(shù)的使用方法及實例.6Replace和substitute的區(qū)別例如：隨機生成身份證號碼中的月、日=REPLACE(A1,IF(LEN(A1)=15,9,11),4,TEXT(INT(RAND()x(12-31-1-1),MMDD)利用文本函數(shù)得日期格式，返回由rand函數(shù)隨機產(chǎn)生日期，由替換函數(shù)replace替換原來的日期數(shù)據(jù)。REPLACE(old_text,start_num,num_chars,new_text)第二個參數(shù)確定從原文本的第幾個字符開始搜索。這里用replace就是因為，它利用舊數(shù)據(jù)

7、在原文本中的相對位置為依據(jù)，替換掉指定數(shù)目的數(shù)據(jù)，符合本題只能確定替換文本的相對位置的要求。SUBSTITUTE(text,old_text,new_text,instance_num)SUBSTITUTE函數(shù)是利用查找到的文本出現(xiàn)的次數(shù)為依據(jù)來替換的，比如a在abcabc中出現(xiàn)兩次，那么如果只替換第二次出現(xiàn)的a為d，那么函數(shù)可以用=SUBSTITUTE(abcabc,a,d,2)，返回值為abcdbc。當確切知道要替換的舊數(shù)據(jù)出現(xiàn)的次數(shù)時，用此函數(shù)。當然，如果不指定第四個參數(shù)，那么它將替換掉所有符合條件的數(shù)據(jù)。7Countif應用，統(tǒng)計兩列單元格中的不同在C1中輸入下面公式，找出b中有a中也

8、有的，每有一個重復增加1。=countif(a:a,b1)注：countif第一個參數(shù)沒法對直接輸入或者通過引用計算得到的數(shù)組進行操作，必須是單元格區(qū)域才行。Countif第二個參數(shù)可以設置通配符，但是查找范圍內(nèi)的格式就必須設置成非數(shù)字格式。8Match函數(shù)最后一個參數(shù)為0時，可以設置通配符查找。默認為1，這時需對查找區(qū)域排序，否則可能出現(xiàn)錯誤。9將數(shù)字進行舍入1)將數(shù)字向上舍入要執(zhí)行此任務，請使用ROUNDUP、EVEN或ODD函數(shù)。ROUNDup(number,num_digits)遠離零值，向上（絕對值增加的方向）舍入數(shù)字。EVEN或ODD都是向上舍入到最接近的偶數(shù)或奇數(shù)。即返回沿絕對值

9、增大方向取整后最接近的偶數(shù)（奇數(shù)）。2)將數(shù)字向下舍入ROUNDDOWN(number,num_digits)靠近零值，向下（絕對值減小的方向）舍入數(shù)字。與int功能類似，區(qū)別在于，（1）負數(shù)時，int遠離0舍入，rounddown接近0舍入；（2）它功能更強大一點，可以指定舍入的位數(shù)，即規(guī)定舍入到小數(shù)點前后幾位。INT(number)將數(shù)字向下舍入到最接近的整數(shù)。注意，負數(shù)舍入時也是向下，即遠離0的方向，而正數(shù)時是接近0的方向。3)將數(shù)字舍入到最接近的數(shù)字，即四舍五入法ROUND(number,num_digits)返回某個數(shù)字按指定位數(shù)取整后的數(shù)字。按絕對值四舍五入。4)將數(shù)字的小數(shù)部分截

10、去，返回整數(shù)TRUNC(number,num_digits)函數(shù)TRUNC直接去除數(shù)字的小數(shù)部分，而函數(shù)INT則是依照給定數(shù)的小數(shù)部分的值，將其四舍五入到最接近的整數(shù)。函數(shù)INT和函數(shù)TRUNC在處理負數(shù)時有所不同：TRUNC(-4.3)返回-4，而INT(-4.3)返回-5，因為-5是較小的數(shù)。5)將數(shù)字舍入到0以上的有效位要執(zhí)行此任務，請使用ROUND、ROUNDUP、ROUNDDOWN、LEN和INT函數(shù)。6)將數(shù)字舍入為指定的倍數(shù)MROUND(number,multiple)將第一個參數(shù)四舍五入到最接近第二個參數(shù)倍數(shù)的數(shù)，如果數(shù)值number除以基數(shù)的余數(shù)大于或等于基數(shù)的一半，則函數(shù)M

11、ROUND向遠離零的方向舍入。CEILING(number,significance)將參數(shù)Number向上舍入（沿絕對值增大的方向）為最接近的significance的倍數(shù)。無論數(shù)字符號如何，都按遠離0的方向向上舍入，但是第一個參數(shù)和第二個參數(shù)的符號必須保持一致，與他相反的函數(shù)是FLOOR(number,significance)。10統(tǒng)計一列數(shù)據(jù)（a列）中出現(xiàn)相同次數(shù)的數(shù)據(jù)的個數(shù)=SUMPRODUCT(-(COUNTIF($A$1:$A$10,$A$1:$A$10)=COLUMN(A:A)/COLUMN(A:A)輸入到b1，向右拖拉，依次能夠得到出現(xiàn)1、2、3次等等的數(shù)據(jù)統(tǒng)計。這里，用CO

12、LUMN(A:A)來控制統(tǒng)計的次數(shù)，以便實現(xiàn)向右拖拉時可以自動增加。11統(tǒng)計一列數(shù)據(jù)中數(shù)字出現(xiàn)的頻率比如1234，和4567，其中在出現(xiàn)一次一欄中顯示123567等幾個數(shù)字。isnumber函數(shù)的使用方法及實例.需借助輔助列，在e:l行里面輸入公式=MID($A2,COLUMN(A:A),1)拖拉到所有區(qū)域，最大可以統(tǒng)計8位數(shù)據(jù)。然后下面為主公式。=SUMPRODUCT(LARGE(IF(FREQUENCY(LARGE(IF(COUNTIF($E$1:$L$1000,$E$1:$L$1000)=COLUMN(A:A),1,0)xIF($E$1:$L$1000)=,0,$E$1:$L$1000)

13、,ROW(1:10),LARGE(IF(COUNTIF($E$1:$L$1000,$E$1:$L$1000)=COLUMN(A:A),1,0)xIF($E$1:$L$1000)=,0,$E$1:$L$1000),ROW(1:10)=0,0,LARGE(IF(COUNTIF($E$1:$L$1000,$E$1:$L$1000)=COLUMN(A:A),1,0)xIF($E$1:$L$1000)=,0,$E$1:$L$1000),ROW(1:10),ROW(1:10)x10(ROW(1:10)-1)上面公式輸入到出現(xiàn)一次的欄里面，然后向右拖拉，一次可以顯示出現(xiàn)兩次、三次，等等。12自定義格式中,輸

14、入1顯示早餐,輸入2顯示午餐,其余顯示晚餐=1早餐;=2午餐;晚餐13Ran()函數(shù)產(chǎn)生大于等于0小于1的函數(shù)，注意上限沒有等于如需要取107內(nèi)的隨機數(shù)，函數(shù)：=10+(rand()-rand()x7如果10+7不包含10，可以寫成：=17-rand()x7如果是107內(nèi)的隨機數(shù)，不包含10，其中odd為四舍五入取奇數(shù)（無論數(shù)字符號如何，都按遠離0的方向向上舍入。），目的為了得到正負一。=10+ODD(RAND()-RAND()x(1-RAND()x7或者：=10+ODD(RANDBETWEEN(-1,1)x(1-RAND()x714將“001”“001-107”數(shù)字前后都加上2，變成“003

15、-109”=IF(ISNUMBER(A1),A1+2,TEXT(LEFT(A1,3)+2,000)-TEXT(RIGHT(A1,3)+2,000)如果有8位數(shù)字，則需要進行為數(shù)判斷，使用find()函數(shù)15excel轉(zhuǎn)置自動填充的問題1)A列為a、b、c，b列為1、2、3，c列為11、22、33，d列為111、222、333，e列為aa、bb、cc2)A列為a、b、c、d、e、f、g、h、i，b列為1、2、3、4、5、6、7、8、9篇二:SUMPRODUCT函數(shù)用法詳解Excel多條件求和SUMPRODUCT函數(shù)用法詳解轉(zhuǎn)Excel多條件求和SUMPRODUCT函數(shù)用法詳解龍逸凡日常工作中，我

16、們經(jīng)常要用到多條件求和，方法有多種，第一類：使用基本功能來實現(xiàn)。主要有：篩選、分類匯總、數(shù)據(jù)透視表、多條件求和向?qū)?；第二類：使用公式來實現(xiàn)方法。主要有：使用SUM函數(shù)編寫的數(shù)組公式、聯(lián)用SUMIF和輔助列（將多條件變?yōu)閱螚l件）、使用SUMPRODUCT函數(shù)、使用SUMIFS函數(shù)(限于Excelxx及以上的版本)，方法千差萬別、效果各有千秋。本人更喜歡用SUMPRODUCT函數(shù)。由于Excel幫助對SUMPRODUCT函數(shù)的解釋太簡短了，與SUMPRODUCT函數(shù)的作用相比實在不匹配，為了更好地掌握該函數(shù)，特將其整理如下。龍逸凡注：歡迎轉(zhuǎn)貼，但請注明作者及出處。一、基本用法在給定的幾組數(shù)組中，將

17、數(shù)組間對應的元素相乘，并返回乘積之和。語法：SUMPRODUCT(array1,array2,array3,.)Array1,array2,array3,.為2到30個數(shù)組，其相應元素需要進行相乘并求和。公式：=SUMPRODUCT(A2:B4,C2:D4)ABCD1Array1Array1Array2Array2isnumber函數(shù)的使用方法及實例.234273866741953isnumber函數(shù)的使用方法及實例.公式解釋：兩個數(shù)組的所有元素對應相乘，然后把乘積相加，即3x2+4x7+8x6+6x7+1x5+9x3。計算結(jié)果為156二、擴展用法1、使用SUMPRODUCT進行多條件計數(shù)語法

18、：SUMPRODUCT(條件1)x(條件2)x(條件3)x(條件n)作用：統(tǒng)計同時滿足條件1、條件2到條件n的記錄的個數(shù)。實例：=SUMPRODUCT(A2:A10=男)x(B2:B10=中級職稱)公式解釋：統(tǒng)計性別為男性且職稱為中級職稱的職工的人數(shù)2、使用SUMPRODUCT進行多條件求和語法：SUMPRODUCT(條件1)x(條件2)x(條件3)x(條件n)x某區(qū)域)作用：匯總同時滿足條件1、條件2到條件n的記錄指定區(qū)域的匯總金額。實例：=SUMPRODUCT(A2:A10=男)x(B2:B10=中級職稱)xC2:C10)公式解釋：統(tǒng)計性別為男性且職稱為中級職稱的職工的工資總和（假設C列為

19、工資）贊4xx-12-xx:01回復catyoung184位粉絲2樓三、注意事項1、數(shù)組參數(shù)必須具有相同的維數(shù)，否則，函數(shù)SUMPRODUCT將返回錯誤值#VALUE!。2、SUMPRODUCT函數(shù)將非數(shù)值型的數(shù)組元素作為0處理。3、在SUMPRODUCT中，xx及以下版本不支持整列（行）引用，必須指明范圍，不可在SUMPRODUCT函數(shù)使用A:A、B:B，Excelxx及以上版本可以整列（列）引用，但并不建議如此使用，公式計算速度慢。4、SUMPRODUCT函數(shù)不支持“x”和“？”通配符SUMPRODUCT函數(shù)不能象SUMIF、COUNTIF等函數(shù)一樣使用“x”和“？”等通配符，要實現(xiàn)此功能可以用變通的方法，如使用LEFT、RIGHT、ISNUMBER(FIND()或ISNUMBER(SEARCH()等函數(shù)來實現(xiàn)通配符的功能。如：=SUMPRODUCT(A2:A10=男)x(B2:B10=中級職稱)x(LEFT(D2:D10,1)=龍)xC2:C10)=SUMPR