Excel公式与函数

NAN YA PRINTED CIRCUIT BOARD CORPORATION,南亞電路板股份有限公司,PAGE,45,按一下以編輯母片標題樣式,按一下以編輯母片,第二層,第三層,第四層,第五層,Excel,公式與函數,生管組 李景濤,目錄,、,建立公式,、相對參照位址與絕對參照位址,、函數的使用,、自動計算功能,、在公式中使用名稱,、建立公式,1.1,說明：,需將工作表中的數字資料做加、減、乘、,除,等運算時，可把計算動作交給,Excel,的公式去做，省去自行運算的工夫,。,1.2,公式的表示法：,在,A3,儲存格中輸入：,“,=A1 + A2”,Excel,會將,A1,儲存格的值,+A2,儲存格的值的結果顯示在,A3,儲存格中。,、建立公式,1.3,輸入公式：,輸入公式必須以等號 “,= ”,起首，如,= A1 + A2,，,這樣,Excel,才知道我們輸入的是公式，而不是一般的文字資料。,1.4,實例說明：,建立公式實例,、相對參照位址與絕對參照位址,2.1,說明：,相對參照位址的表示法：,B1,、,C4,絕對參照位址的表示去：在儲存格位址前,面加上“,$”,符號，如：,$B$1,、,$C$4,2.2,相對與絕對參照的差異：,套用在公式上，相對參照位址會隨著公式的位置,而改變，而絕對參照位址則不管公式在什麼地方，,它永遠指向同一個儲存格,2.3,實例說明,相對、絕對參照位址實例,、相對參照位址與絕對參照位址,2.4,混合參照：,我們可以在公式中同時使用相對參照,與絕對參照，這種情形稱為,混合參照,。例如：,= $A$1 + A2,= $B1 + B2,絕對參照,相對參照,絕對參照,相對參照,這種,公式在複製後，絕對參照的部份(如$,B1,的$,B),不,會變動，而相對參照的部份則會隨情況做調整,2.5 實例說明,混合參照位址實例,、函數的使用,3.1,說明,:,函數是,Excel,根據各種需要，預先設計好的運算公式，可讓您省下不少自己設計公式的時間。,3.2,函數的格式：,每個函數都包含三個部份：,函數名稱、引數、小括號。,、函數的使用,SUM(Number1,Number2,),函數名稱,小,括號,引數,SUM,即是函數名稱，從函數名稱可大略得知函數的功能、用途。,小括號用來括住引數，有些函數雖沒有引數，但小括號還是不可以省略。,引數是函數計算時所必須使用的資料，例如,SUM(1,3,5),即表示要計算1,3,5三個數字的總合，其中的1,3,5就是引數。,、函數的使用,3.3 引數的資料類型：,函數的引數不僅是數字類型而已，它還以是文字，或是：,位,址：,如,SUM(B1,C3),即是要計算,B1,儲存格的值+,C3,儲存格的值。,範圍：,如,SUM(A1:A4),即是要加總,A1：A4,範圍的值。,函數：,如,SQRT(SUM(B1:B4,),即是先求出,B1:B4,的總和後，再開,平方根的結果。,3.4 實例說明,輸入函數實例,、,函數的使用,3.5 自動加總鈕：,一般工具列上的自動加總鈕 ，它,讓我們快速輸入函數。例如當我們選取儲存格,B8，,並,按下 鈕時，便會自動插入,SUM,函數，且連引數都幫我,們定好了。,自動選取好函數引數，不過您也可以自行重新選取其他範圍,你只需按下,Enter,鍵，便可算出班,費的總支出囉！,、,函數的使用,3.6 輸入其它函數插入函數交談窗：,插入函數交談窗,是,Excel,函數的大本營，當你在函數方塊列示窗中找,不需要的函數時，就可從這裡來輸入函數。,3.7 實例說明,輸入其它函數實例,、,自動計算功能,4.1 說明：,之前我們想要得知某範圍內的總和時，總是,要在儲存格中建立公式或函數來計算。現在，使用,自動,計算,功能，讓您不在需撰寫公式或使用函數的情況下，,快速得到運算結果。,4.2 自動計算功能不僅會計算總合，還可以計算最大、最小、平均值等等。假設我們現在想知,4.2 實例說明,使用自動計算實例,、,自動計算功能,4.3 除了加總及平均值外，自動計算的其他功能項目說明如下：,無：,取消自動計算的功能。,項目個數：,計算選定範圍中，有幾個非空白的儲存格。,數字項目個數：,計算選定範圍中，資料為數值的儲存,格個數。,最大值：,找出選定範圍中，最大的數字資料。,最小值：,找出選定範圍中，最小的數字資料。,、在,公式中使用名稱,5.1 說明：,截至目前為止，我們都是用儲存格位址來當,作公式的運算元或函數的引數，雖然可以直接指出,計算的範圍，但卻無法一目了然公式的用途。這裡,我們要教您為儲存格取一個好記且具有意義的名稱，,以後就直接用名稱代替儲存格位址，使公式更易容,閱讀。,、在,公式中使用名稱,5.2 命名的原則：,當我們為儲存格定義名稱時，必須遵守下列的命名規則：,名稱的第一個字元必須是中文、英文、或底線(_)字元。其餘字元則可以是英文、中文、數字、底線、句點(.)和問號(?)。,名稱最多可達255個字元。但別忘了一個中文字就佔兩個字元。,名稱不能類似儲存格的位址，如,A3、$C$5。,名稱不區分大小寫字母，所以,MONEY,和,money,視為同一個名稱。,5.3 實例說明：,在公式中使用名稱實例,、在,公式中使用名稱,5.4 刪除名稱：,假如定義的名稱用不到了，相要將它刪,除掉，可執行,插入名稱定義,命令，在定義名稱,交談窗中選取欲刪除的名稱，再按下,刪除,鈕即可。,按此鈕刪除名稱,函數方塊,在此輸入一個=,1.選定要輸入公式的儲存格，即,E2，,接著將指標移,到資料編輯列中輸入等號 =,建立公式實例說明,建立公式實例說明,2.接著輸入=之後的公式，即“,B2+C2+D2”。,請在儲存,格,B2,上按一下，,Excel,便會將,B2,輸入到資料編輯列中,此時,B2,被虛線框包圍住,B2,自動輸入到公式中,建立公式實例說明,3.再來請輸入“+”，然後選取,C2、,再輸入“+”，選取,D2，,如此公式的內容便輸入完成了。,運算元與儲存格的框線會使用相同的顏色，以利於辨識,公式建好了,建立公式實例說明,4.最後按下資料編輯列上的輸入鈕 或,Enter,鍵，公,式計算的結果馬上顯示在儲存格,E2,中,資料編輯會顯示公式,儲存格顯示公式計算的結果,您也,可以直接從鍵盤鍵人=,B2+C2+D2”,再按下,Enter,鍵來輸入,公式，省下滑鼠、鍵盤交替使用,的麻煩,返回,相對、絕對參照位址實例說明,1.選取,A3,輸入公式“,A1+A2”,並計算出結果。此為相對,參照位址。,輸入公式“,A1+A2”,相對、絕對參照位址實例說明,2.選取,B3,然後在資料編輯列中輸入“=,B1”,3.,按下,F4,鍵，則,B1,便切成$,B$1，,成為絕對參照位址,當然，您也可直接在資,料編輯中輸入“=$,B$1”,相對、絕對參照位址實例說明,F4,儲存格,參照位址,B1,第1次,$B$1,絕對參照,第2次,B$1,混合參照，只有列編號是絕對位址,第,3次,$B1,混合參照，只有欄編號是絕對位址,第,4次,B1,相對參照，還原為相對參照,4.,F4,鍵可循序切換儲存格位址的參照類型，每按一,次,F4,鍵，參照位走的類型就會改變，其切換結果,如下：,相對、絕對參照位址實例說明,5.接著輸入“+,B2”,再按,F4,鍵將,B2,切成$,B$2，,最後按下,Enter,鍵，將公式建立完成,絕對參照位址,相對、絕對參照位址實例說明,6. 選定,A3:B3,拉曳填滿控點到下一列，將公式複製到,下方的儲存格中。,計算結果不同了,相對、絕對參照位址實例說明,7. 相對位址公式：,A3,的公式=,A1+A2,使用相對位址，,表示要計算,A3,往上找兩個儲存格(,A1、A2),的,總合。,因此複製到,A4,後，便改成從,A4,往上找兩個儲存格相,加，結果就變成,A2,和,A3,相加的結果,往上找兩個儲存格,往上找兩個儲存格,相對、絕對參照位址實例說明,8. 絕對位址公式：,B3,的公式=$,B$1+$B$2,使用絕對位址,，因此不管公式複製到哪裡,Excel,都是找出,B1,和,B2,的,值來相加，所以,B3,和,B4,的結果都是一樣的：,還是找,B1,和,B2,返回,混合參照位址實例說明,請雙按,B4,進入編緝模式，將插入點移至=之後，接,著按兩次,F4,鍵，讓$,B$1,變成$,B1。,2.,將插入點移至+之後，按三次,F4,鍵將$,B$2,變成,B2,按下,Enter,鍵。,3. 選定,B4,分別拉曳填滿控點至,C4,及,B5。,混合參照位址實例說明,返回,與,B4,同列不同欄，因此$,B1,的部份不動，,B2,變成,C2,與,B4,同欄不同列，因此$,B1,的1,變成2，,B2,變成,B3,C4,B5,輸入函數實例說明,1. 選取存放計算結果的儲存格,B8,並在資料編輯列中,輸入等號=(函數也是公式的一種，所以輸入函數,時，也必須以等號=起首)。,函數方塊,選取,SUM,函數,輸入函數實例說明,2. 按下函數方塊右側的下拉鈕，在函數列視窗中選取,SUM，,此時會開啟函數引數交談窗來協助函數的輸入,這裡會描述此函數的功能,輸入函數實例說明,3. 再來就是要設定函數的引數。請先按下第一個引數,欄,Number1,右側的摺疊鈕 將函數引數交談窗收起,來，再從工作表中選取,B4:B6,當作引數。,選取的範圍會被虛線框圍住,摺疊到這裡來,輸入函數實例說明,4. 請按一下,Number1,欄右側展開鈕 ，再度將函數引,數交談窗展開。,這裡會顯示計算的結果,選取,B4:B6,當作引數,除了從工作表中,選取儲存格來設,定引數，你也可,以直接在引數欄,中輸入引數，省,下摺疊、展開函,數引交談窗的麻,煩。,T,ips,輸入函數實例說明,5. 按下確定鈕，函數的計算結果就顯示在,B8,儲存格內：,計算結果,剛才輸入的函數,返回,輸入其它函數實例說明,1. 請選擇儲存格,B8,然後按下資料編輯列上的插入函數鈕 （或執行插入函數命令），你會發現資料編輯列自動輸入等號=，並且開啟插入函數交談窗：,函數的功能敘述,列出,Excel,所提供的函數,可從,這裡選擇函數的類別,輸入其它函數實例說明,2. 接著我們要從插入函數交談窗中選取,SUM,函數：,、按下確定鈕，開啟函數引數交談窗,、,選取此類別,下的,SUM,函數,、選擇數學與,三角函數類別,當你將,函數存入儲存格以後，若想變更引數設定，請選取函數所在的儲存格，然後按下,插入函數,鈕 ，即可展開,函數引數,交談窗來重新設定引數。,輸入其它函數實例說明,、指定好引數範圍,、按下確定鈕即可得到計算結果,返回,使用自動計算實例說明,1. 開啟,Ch05-05,範例檔案，接著選取,B2:D2,儲存格範圍，則這三個儲存格的加總值馬上就會顯示在狀態列上：,顯示選取範圍的加總值，這就是自動計算的功能,使用自動計算實例說明,2.,自動計算,功能不僅會計算總和，還可以計算最大、最小、平均值等等。假設我們現在想知道黃傑倫和蔡永康的音樂科平均分數為，則可如下操作：,、,選取,B2:B3,範圍,平均值顯示在狀態列上,、,於狀態列按下右鈕，並選取快顯功能表中的平均命令,返回,在公式中使用名稱實例說明,請開啟,Ch05-06,範例檔案，現在我們要將,B2:B3,儲存格範圍命名為“歷史分數”：,、,按一下名稱方塊，鍵入“歷史分數”後按下,Enter,鍵，則“歷史分數”就代表,B2:B3,這個範圍,、,選定欲命名的範圍,B2:B3,在公式中使用名稱實例說明,現在我們就試著用“歷史分數”這個名稱，來建立儲存,格,E2,的公式：,請選取,E2，,接著執行插入名稱貼上命令。,在貼上名稱交談窗中選取“歷史分數”項目，然後按下,確定鈕，資料編輯列和儲存格中便會出現“=歷史分數”。,在公式中使用名稱實例說明,2. 接著鍵入“ +,C2 + D2”,並按下,Enter,鍵，如此林米奇,的總分便會顯示在,E2,儲存格中。,林米奇的總分,公式的內容,在公式中使用名稱實例說明,公式錯了吧？,公式=歷史分數(即,B2:B3,這兩個儲存格)+,C2+D2”,怎麼,會是林米奇的3科總分呢？,一般而言，公式的運算元應是單一值、單一儲存格、,或參照單一儲存格的名稱。因此當我們指定一欄或一,列的儲存格來參照時，,Excel,便會主動從範圍中選擇一,個儲存格來計算，其原則如下：,若,指定的範圍是一列，則選擇與公式同欄的儲存格。,若指定的範圍是一欄，則選擇與公式同列的儲存格。,在公式中使用名稱實例說明,所以“歷史分數”雖然有兩個儲存格，但此處僅會選擇,儲存格,B2,來計算：,公式所在,選出和公式同列的儲存格來計算,指定的範圍是一欄,如果,Excel,無法從範圍中選定一個儲存格來計算，則會,出現“#,VALUE!”,錯誤訊息，這時就要修改公式了。,返回,