excel 公式

EXCEL中的函數與公式公式表示方法公式是一種方程式，執行資料的運算。包含數學運算，(如+,-,*,/)，或者比較數值(=,,=,)，另外還可以結合數值。公式可以參照同一工作表上的其它儲存格、同一活頁簿中其它工作表上的儲存格、或其它活頁簿中工作表上的儲存格。=(A1+B1)/A2*B2相加除乘建立公式即時結果輸入公式輸入函數確認公式輸入公式時﹐以一個等號“=”作爲開頭，後為公式的運算式。在一個公式中可以包含各種算術運算符、常量、變數、函數、儲存格位址、名稱等。請看几個公式的實例。=1+1常量運算=A1*20-B1使用儲存格位址（變數）=ABS(A1+B1)使用函數(求絕對值)=SUM(A)使用名稱(求總名稱A所含儲存格)建立公式運算符與運算優先分級基本數學運算符號算術運算符意義範例+加法1+1-減法﹑負2-1*乘3*1/除法4/1%百分比5%^乘冪6^2（相當於6*6）運算符與運算優先分級比較運算符號比較運算符意義範例=等於A1=B1大於A1B1小於A1B1=大於或等於A1=B1=小於或等於A1=B1不等於A1B1運算符與運算優先分級其他運算符號文字運算符意義範例&把文字成連續文字“不”&“行”﹐產生“不行”參照運算符﹕(冒號)範圍運算符B1:B5,（逗號）聯合運算符，可以將多個參照位址結合成一個參照位址SUM(A1:A5,B1:B5)運算符與運算優先分級運算符號的優先次序算術運算符優先次序-(負號)3%（百分比符號）4^（冪符號）5*和/（乘除）6+和-（加減）7&(文字連接符)8=,,,=,=,91﹕(冒號)2﹐(逗號)儲存格在公式中的應用公式可引用儲存格中的值來參與運算儲存格直接作為數值參與運算=A1+B1*4(如A1中有23﹐B1有5﹐即公式為﹕=23+5*4結果爲43儲存格在公式中的應用公式的復制與相對地址在公式中的引用﹐Excel通常是使用相對地址來引用儲存格的。所以：當復制一個有儲存格單格地址的公式到另一個位置時，公式中的儲存格地址會改掉不想讓公式中的儲存格隨著改掉﹐怎樣做才行?儲存格在公式中的應用絕對位址的引用在這種情況下，把公式拷貝到新位置，可讓公式中的儲存格地址保持不變。通過在列號和行號前面添加美元符號“＄”來達到此目的我想讓儲存格的列不能改﹐行可隨著改掉﹐怎樣做才行?儲存格在公式中的應用相對地址與絕對地址的混用想讓儲存格的列不能改﹐行可隨著改掉時﹐就在列的地址前加上“$”﹐行地址不加即可相對地址與絕對地址的混用結果相對地址的結果儲存格在公式中的應用技巧﹕1﹑點中公式中的儲存格﹐可以按下“F4”快速改變儲存格的絕對或相對引用方式。2﹑儲存格的引用不但可在同一張工作表中進行﹐更可以引用在另一張工作表中的儲存格﹐甚至可以引用到另一個XLS文件中的某個儲存格有關公式使用的技巧在工作表中使用名稱建立名稱時﹐在Excel中遵循以下名命規則:首字符必須是字母或下划線﹐分隔符只能使用英文句點或下划線﹐整個名稱不能超過265個字符﹐字母不分大小寫,可用漢字作名稱。對不對a33a_a1a1或a-1a.1_2a,1有關公式使用的技巧名稱在公式中應用把A1:E8命名為A﹐SUM(A)即是SUM(A1:E8)常數命名后在公式中應用把3.14159命名為PAI﹐2*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和2SUM(A1:E2)等於17SUM(A1:E2,5)等於23SUMIF根據單一條件進行總和可以根據其他區域中的參照值，使用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:A5﹐31)等於31MIN(A1:A5)等於1ABS傳回數字的絕對值。ABS(number)如﹕ABS(6)等於6ABS(-6)等於6SQRT傳回數值的正平方根。SQRT(number)Number是您所要平方根的數字。如果此數字是負數，SQRT函數會傳回錯誤值#NUM!。如﹕SQRT(2)等於1.4142136SQRT(-2)等於#NUM!SQRT(ABS(-2))等於1.4142136IFIF(logical_test,value_if_true,value_if_false)IF函數可用以根據條件﹐轉回相應的值。如果指定的羅輯結果為TRUE，則傳回一個值，若為FALSE，則傳回另一個值。IF(A1A2,A1,A2)如果A1大于A2﹐返回A1,否則返回A2COUNTIFCOUNTIF(range,criteria),累計某個條件的出現次數,COUNTIF工作表函數可累計特定數值在儲存格區域中出現的次數。例如，可累計區域A1:E5中含有數值“1”的的儲存格個數。=COUNTIF(A1:E5,1)PRODUCT把所有引數數值相乘，然後傳回乘積PRODUCT(num1,num2,...num30)如儲存格範圍A1:E2中所含的數值為:5、10和2PRODUCT(A1:E2)等於100PRODUCT(A1:E2,5)等於500AVERAGEAVERAGE(num1,num2,...num30)傳回這些引數的平均值(算術平均數)。注意﹕當計算這些儲存格的平均值時，記住空值儲存格和數值為零的儲存格所得到的值是不同的，空值的儲存格不予計算，但是數值為零的儲存格則列入計算。看看有什么不同=(A1+A2+A3+A5)/4=(A1+A2+A3+A5)/5POWER傳回數字乘幕的結果。POWER(num,power)Num是底數。可以是任意的實數。Power是指數，即底數所要乘方的次數。註“^”運算符可以取代POWER來表示例﹕POWER(5,2)等於25﹐5^2等於25如何嵌套函數函數中有函數﹐例﹕分數是89以上“優秀”﹐80到89“優良”﹐70到79“優”﹐60到69“及格”﹐60以下“不及格”﹐分數大于100或=0,“超出分數范圍”。=IF(OR(AND(分數=0),AND(分數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(梁功慶)