Excel自定義公式

1、Excel自定義公式數(shù)據(jù)計(jì)算（1）自定義公式方式（2）函數(shù)方式什么是公式 Excel中的公式是一種對工作表中的數(shù)值進(jìn)行計(jì)算的等式，它可以幫助用戶快速的完成各種復(fù)雜的運(yùn)算。公式以“=”開始，其后是公式的表達(dá)式，如“=A1+A2”例如:公式”=(A2+67)/SUM(B2:F2)”單元格引用單元格引用=(A2+67)/SUM(B2:F2)除法運(yùn)算符除法運(yùn)算符函數(shù)函數(shù)加法運(yùn)算符加法運(yùn)算符常量常量單元格區(qū)域引用單元格區(qū)域引用 公式通常由公式通常由運(yùn)算符運(yùn)算符和參與運(yùn)算的和參與運(yùn)算的操作數(shù)操作數(shù)組成。組成。 運(yùn)算符算術(shù)運(yùn)算符含義示例+（加號(hào)）加法3+3（減號(hào)）減法負(fù)數(shù) 3-1 -1*（星號(hào)）乘法 3*3

2、/（正斜杠）除法 3/3%（百分號(hào)）百分比 20%（脫字號(hào)）乘方32比較運(yùn)算符含義示例=（等號(hào)）等于 A1=B1（大于號(hào)）大于A1B1（小于號(hào)）小于A1=（大于等于號(hào)） 大于等于A1=B1=（小于等于號(hào)） 小于等于A1=B1（不等號(hào)）不等于A1B1文本運(yùn)算符含義示例&（與號(hào)）將兩個(gè)文本值連接或串起來產(chǎn)生一個(gè)連續(xù)的文本值 (North&wind)引用運(yùn)算符含義示例:（冒號(hào)）區(qū)域運(yùn)算符，生成對兩個(gè)引用之間的所有單元格的引用，包括這兩個(gè)引用 B5:B15,（逗號(hào)）聯(lián)合運(yùn)算符，將多個(gè)引用合并為一個(gè)引用 SUM(B5:B15,D5:D15)（空格）交叉運(yùn)算符，生成對兩個(gè)引用共同的單元格的

3、引用B7:D7 C6:C8EXCEL函數(shù)的結(jié)構(gòu)wExcel 函數(shù)即是預(yù)先定義，執(zhí)行計(jì)算、分析等處理數(shù)據(jù)任務(wù)的特殊公式特殊公式。Excel 函數(shù)結(jié)構(gòu):也有一些函數(shù)是沒有參數(shù)的,如ROW()左右括號(hào)成對出現(xiàn)單一結(jié)構(gòu)嵌套結(jié)構(gòu)參數(shù)與參數(shù)之間使用半角逗號(hào)進(jìn)行分隔函數(shù)參數(shù)常用符號(hào)或表示方法n函數(shù)公式中的文本必須用半角引號(hào)，如：東南汽車;而非直接輸入 東南汽車或“東南汽車”n連接符: & 如：東南& 汽車的值為 東南汽車n空值/空格的表示法: n 空值：n 空格： n相關(guān)數(shù)學(xué)符號(hào):（不等于）;=（大于等于）;=3)；countif(I:I, =3)- countif(I:I, =5)n例例3

4、 3:檢測到職編號(hào)是否有重復(fù)值 countif(A:A, A2)Countaw用途：返回參數(shù)組中非空值的數(shù)目。利用函數(shù)COUNTA 可以計(jì)算數(shù)組或單元格區(qū)域中數(shù)據(jù)項(xiàng)的個(gè)數(shù)。w語法：COUNTA(單元格區(qū)域1,單元格區(qū)域2) 說明：參數(shù)的個(gè)數(shù)為130 個(gè)。Counta的思考案例n例1：如果A1=6.28、A2=3.74，其余單元格為空，則公式“=COUNTA(A1:A7)”的計(jì)算結(jié)果等于?。n例2：統(tǒng)計(jì)C列的非空白單元格的表示方法為：？; 若整張EXCEL表的每個(gè)單元格都有數(shù)據(jù),前述函數(shù)公式的結(jié)果為?MOD：取余w用途：返回兩數(shù)相除的余數(shù)，其結(jié)果的正負(fù)號(hào)與除數(shù)相同。w語法：MOD(被除數(shù)，除數(shù))

5、w參數(shù)：除數(shù)不能為零。Mod函數(shù)的案例n實(shí)例1：公式“=MOD(14，4)”返回?；“=MOD(-5，-2)”返回？。n實(shí)例2:如何利用MOD函數(shù)，將整張EXCEL表的偶數(shù)行都標(biāo)上底色 Vlookupw返回表格或數(shù)組當(dāng)前行中指定列處的數(shù)值。w語法：VLOOKUP(索引值，數(shù)據(jù)區(qū)域，列序號(hào)，查找方式)w參數(shù)： 索引值為需要在數(shù)據(jù)表第一列中查找的數(shù)值，它可以是數(shù)值、引用或文字串。 數(shù)據(jù)區(qū)域?yàn)樾枰谄渲胁檎覕?shù)據(jù)的數(shù)據(jù)表，可以使用對區(qū)域或區(qū)域名稱的引用。 列序號(hào) 指數(shù)據(jù)區(qū)域 中待返回的匹配值的列序號(hào)。它等于1 時(shí)，返回?cái)?shù)據(jù)區(qū)域 第一列中的數(shù)值；它等于 2時(shí)，返回?cái)?shù)據(jù)區(qū)域第二列中的數(shù)值，以此類推。 查找

6、方式為一邏輯值,指明函數(shù)VLOOKUP 返回時(shí)是精確匹配還是近似匹配。如果為1或省略，則返回近似匹配值，也就是說，如果找不到精確匹配值，則返回小于索引值 的最大數(shù)值；如果為0，函數(shù)VLOOKUP 將返回精確匹配值。如果找不到，則返回錯(cuò)誤值#N/A。字符函數(shù)Left/Right：截取字符串函數(shù)w用途：根據(jù)指定的字符數(shù)返回文本串中的第一個(gè)或前幾個(gè)字符。此函數(shù)用于雙字節(jié)字符。w語法：LEFT(文本, 返回的字符數(shù)) Right(文本, 返回的字符數(shù))w參數(shù)：返回的字符數(shù)必須大于或等于0。w實(shí)例：如果A1=電腦愛好者，則LEFT(A1，2)返回? ;Right(A1,3)返回?Left與right的綜

7、合運(yùn)用 如何利用Left及Right從身份證號(hào)中提取出生年份。提示：假如A2存放身份證號(hào)，提取年份的做法：=RIGHT(LEFT(A2,10),4)Mid：截取字符串函數(shù)w用途：MID 返回文本串中從指定位置開始的特定數(shù)目的字符，該數(shù)目由用戶指定。w語法：MID(文本串,開始位置,返回字符的個(gè)數(shù)) w參數(shù)：開始位置是文本中要提取的第一個(gè)字符的位置，文本中第一個(gè)字符的開始位置為1，以此類推。w實(shí)例1：如果a1=東南(福建)汽車工業(yè)有限公司，則公式“=MID(A1，4，2)”返回“福建” 。w案例:利用MID從身份證號(hào)中提取出生年份。 GoReplace：替換指定位置處的任意文本w用途：REPLA

8、CE 使用其他文本串并根據(jù)所指定的字符數(shù)替換另一文本串中的部分文本。w語法：REPLACE(需替換其部分字符的文本，被替換字符的起始位置，替換的字符個(gè)數(shù)，用于替換到原文本中去的字符) 。w思考：EXCEL本身就有替換功能，為什么要用replace函數(shù)？-解決替換字符多變的問題Replace的案例案例一：請分別說出下列兩個(gè)函數(shù)公式的返回值案例二：如何用replace取出身份證號(hào)中的出生年月LEN：計(jì)算字符長度。w用途：LEN 返回文本串的字符數(shù)。w語法：LEN(text) 。w參數(shù)：Text 待要查找其長度的文本。w注意：此函數(shù)用于雙字節(jié)字符，且空格也將作為字符進(jìn)行統(tǒng)計(jì)。w實(shí)例：如果A1=電腦愛

9、好者，則公式“=LEN(A1)”返回？If、Len及mid函數(shù)綜合運(yùn)用如果A列里CM后沒有“00”的話，就把“00“加到CM后,但是如果CM后有”00“話，就不加了（產(chǎn)生的效果如B列所示），怎么做？公式提示：=IF(MID(A1,3,2)00,CM00&MID(A1,3,LEN(A1)-2),A1)SUBSTITUTE：替換指定文本w用途：在文字串中用新文本替代舊文本。如果需要在一個(gè)文字串中替換指定的文本，可以使用函數(shù)SUBSTITUTE；如果需要在某一文字串中替換指定位置處的任意文本，就應(yīng)當(dāng)使用函數(shù)REPLACE。w語法：SUBSTITUTE(需要替換其中字符的文本，需被替換的舊文本

10、，用於替換的文本，第幾次出現(xiàn)的舊文本)。w參數(shù)： 第幾次出現(xiàn)的舊文本為一數(shù)值，用來指定以新的文本替換第幾次出現(xiàn)的舊文本；如果指定了該參數(shù)，則只有滿足要求的舊文本被替換；否則將用新文本替換所有出現(xiàn)的舊文本。SUBSTITUTE、MID、LEN的綜合運(yùn)用案例 將左邊的物料代碼中無序空格,處理為右邊的統(tǒng)一格式：ROUND：對數(shù)值進(jìn)取四舍五入。w用途：按指定位數(shù)四舍五入某個(gè)數(shù)字。w語法：ROUND(需四舍五入的數(shù)字，保留的小數(shù)位)w注意：如果保留的小數(shù)位大于0，則四舍五入到指定的小數(shù)位；如果等于0，則四舍五入到最接近的整數(shù)；如果小于0，則在小數(shù)點(diǎn)左側(cè)按指定位數(shù)四舍五入。w實(shí)例：如果A1=65.25，則公式“=ROUND(A1，1)”返回65.3；=ROUND(82.149，2)返回82.15；=ROUND(21.5，-1)返回20；Round(A1,0)返回65。INT:無條件取整w用途：將任意實(shí)數(shù)向下取整為最接近的整數(shù)。w語法：INT(實(shí)數(shù))w參數(shù)：Number 為需要處理的任意一個(gè)實(shí)數(shù)。w實(shí)例：如果A1=16.24、A2=-28.389，則公式“=INT(A1)”返回16，=INT(A2)返回-29。YEARM