[PPT模板]excel 公式.ppt

EXCEL中的函數與公式,=A1+B1*5%C1,公式表示方法,公式是一種方程式，執行資料的運算。包含數學運算，(如+,-,*,/)，或者比較數值(=,=,)，另外還可以結合數值。公式可以參照同一工作表上的其它儲存格、同一活頁簿中其它工作表上的儲存格、或其它活頁簿中工作表上的儲存格。,=(A1+B1)/A2*B2,相加,除,乘,建立公式,即時結果,輸入公式,輸入函數,確認公式,輸入公式時以一個等號“=”作爲開頭，後為公式的運算式。在一個公式中可以包含各種算術運算符、常量、變數、函數、儲存格位址、名稱等。 請看几個公式的實例。 =1+1 常量運算 =A1*20-B1 使用儲存格位址（變數） =ABS(A1+B1) 使用函數 (求絕對值) =SUM(A) 使用名稱(求總名稱A所含儲存格),建立公式,運算符與運算優先分級,基本數學運算符號,運算符與運算優先分級,比較運算符號,運算符與運算優先分級,其他運算符號,運算符與運算優先分級,運算符號的優先次序,1,(冒號),2,(逗號),儲存格在公式中的應用,公式可引用儲存格中的值來參與運算 儲存格直接作為數值參與運算 =A1+B1*4 (如A1中有23B1有5即公式為 =23+5*4 結果爲 43,儲存格在公式中的應用,公式的復制與相對地址在公式中的引用Excel通常是使用相對地址來引用儲存格的。所以：當復制一個有儲存格單格地址的公式到另一個位置時，公式中的儲存格地址會改掉,不想讓公式中的儲存格隨著改掉怎樣做才行?,儲存格在公式中的應用,絕對位址的引用 在這種情況下，把公式拷貝到新位置，可讓公式中的儲存格地址保持不變。通過在列號和行號前面添加美元符號“” 來達到此目的,我想讓儲存格的列不能改行可隨著改掉怎樣做才行?,儲存格在公式中的應用,相對地址與絕對地址的混用 想讓儲存格的列不能改行可隨著改掉時就在列的地址前加上“$”行地址不加即可,相對地址與絕對地址的混用結果,相對地址的結果,儲存格在公式中的應用,技巧 1點中公式中的儲存格可以按下“F4”快速改變儲存格的絕對或相對引用方式。 2儲存格的引用不但可在同一張工作表中進行更可以引用在另一張工作表中的儲存格甚至可以引用到另一個XLS文件中的某個儲存格,有關公式使用的技巧,在工作表中使用名稱 建立名稱時在Excel中遵循以下名命規則:首字符必須是字母或下划線分隔符只能使用英文句點或下划線整個名稱不能超過265個字符字母不分大小寫,可用漢字作名稱 。,有關公式使用的技巧,名稱在公式 中應用 把A1:E8命名為ASUM(A)即是SUM(A1:E8) 常數命名后在公式 中應用 把3.14159命名為PAI2*PAI即是2*3.14159 公式命名使用 把A1+B1建立名稱為add,即add/3與輸入(A1+B1)/3一樣 名稱的適用范圍 所建立的名稱默認為整個工作簿可用如只建立本工作表有效的名稱在名稱前加上工作表名如 Sheet1!add則add這個名稱只在表Sheet1中有效。,Excel函數,工作表函數是Excel預先建立用來執行各種運算的計算公式，利用函數不但能夠提高效率，同時可夠減少我們的輸入公式錯誤率，提高工作效率。,=SUM(A1,B3,C2:E5,9),“=”號開頭,函數,括號,運算引數,使用函數，都有基本相同的語法按函數的語法輸入正確的數值或參數才能得到正確的結果， 否則Excel 將顯示并指出公式中的錯誤地方。,輸入函數,手工輸入函數 , 與在單格中輸入公式的方法一樣。先輸入等號“=”，然後，輸入函數本身再輸入參數。如 =SQRT(A1) 對於參數較多或者比較複雜的函數時，建議使用插入函數來輸入不易出錯。,SUM,把所有引數數值相加，然後傳回總和 SUM(num1,num2, .num30) 如儲存格範圍 A1:E2 中所含的數值為 : 5 、 10 和 2 SUM(A1:E2) 等於 17 SUM(A1:E2, 5) 等於23,SUMIF,根據單一條件進行總和 可以根據其他區域中的參照值，使用 SUMIF 函數對區域進行加總。如，區域A1:A5 中包含一些數值 1 ，下面的公式可為區域 B15:B5 中,與A1:A5有1相對應到的儲存格進行加總。 =SUMIF(A1:A5,1,B1:B5),MAX與MIN,MAX(num1,num2,. Num30)傳回數組值中的最大值。 MIN(num1,num2,. Num30)傳回數組值中的最小值。 如果 A1:A5 中包含數字 1、7、30 和 4，則： MAX(A1:A531) 等於 31 MIN(A1:A5) 等於 1,ABS,傳回數字的絕對值。 ABS(number) 如 ABS(6) 等於 6 ABS(-6) 等於 6,SQRT,傳回數值的正平方根。 SQRT(number) Number 是您所要平方根的數字。如果此數字是負數，SQRT 函數會傳回錯誤值 #NUM!。 如 SQRT(2) 等於 1.4142136 SQRT(-2) 等於 #NUM! SQRT(ABS(-2) 等於 1.4142136,IF,IF(logical_test,value_if_true,value_if_false) IF函數可用以根據條件轉回相應的值。如果指定的羅輯結果為 TRUE，則傳回一個值，若為 FALSE，則傳回另一個值。 IF(A1A2,A1,A2),如果A1大于A2返回A1,否則返回A2,COUNTIF,COUNTIF(range,criteria),累計某個條件的出現次數,COUNTIF 工作表函數可累計特定數值在儲存格區域中出現的次數。例如，可累計區域 A1:E5 中含有數值“1”的的儲存格個數。 =COUNTIF(A1:E5,1),PRODUCT,把所有引數數值相乘，然後傳回乘積 PRODUCT(num1,num2, .num30) 如儲存格範圍 A1:E2 中所含的數值為 : 5 、 10 和 2 PRODUCT(A1:E2) 等於 100 PRODUCT(A1:E2, 5) 等於500,AVERAGE,AVERAGE(num1,num2, . num30) 傳回這些引數的平均值 (算術平均數)。 注意當計算這些儲存格的平均值時，記住空值儲存格和數值為零的儲存格所得到的值是不同的，空值的儲存格不予計算，但是數值為零的儲存格則列入計算。,看看有什么不同,=(A1+A2+A3+A5)/4,=(A1+A2+A3+A5)/5,POWER,傳回數字乘幕的結果。 POWER(num,power) Num 是底數。可以是任意的實數。 Power 是指數，即底數所要乘方的次數。 註“” 運算符可以取代 POWER 來表示 例 POWER(5,2) 等於 25 52等於 25,如何嵌套函數,函數中有函數 例 分數是89 以上 “優秀” 80 到 89 “優良” 70 到 79 “優” 60 到 69 “及格” 60 以下 “不及格” 分數大于100或100), “超出分數范圍“,IF(分數89,“優秀“,IF(分數79,“優良“, IF(分數69,“優“,IF(分數59,“及格“,“不及格“),VLOOKUP,VLOOKUP(lookup_value,table_array,col_index_num,range_lookup) Lookup_value 在陣列中搜尋的值 Table_array 是要在其中搜尋的資料表格。 col_index_num 傳回 table_array 第num欄的值Range_lookup 為 TRUE時被省略了，會傳回部分符合的數值；如果找不到完全符合的值時，會傳回僅次於 lookup_value 的值。當此引數值為 FALSE 時，VLOOKUP 函數只會尋找完全符合的數值，如果找不到，則傳回錯誤值 #N/A。,函數的錯誤值,# # # # #！ 公式計算的結果太長，儲存格容納不下，增加儲 存格的列寬可以解決這個問題 # DIV/0 除數爲零 # N/A 公式中無可用的數值或缺少函數參數 # NAME？ 使用了Excel不能識別的名稱 # NULL！ 使用了不正確的區域運算或不正確的儲存格引用 # NUM！ 在需要數位參數的函數中使用了不能接受的參數，或者公式計算結果的數位太大或太小，Excel無法表示 # REF！ 公式中引用了無效單格 # VALUE！ 需要數值或邏輯值時輸入了文本 有問題歡迎大家Mail給我Aly.liang(梁功慶),