Excel公式与函数

1、NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 1 Excel 公式與函數 生管組生管組 李景濤李景濤 NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 2 目錄 、建立公式 、相對參照位址與絕對參照位址 、函數的使用 、自動計算功能 、在公式中使用名稱 NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 3 、建立公式 1.1 說明：需將工作表中的數字資料做加、減、乘、 除等運算時，可把計算動作交

2、給Excel的公式去 做，省去自行運算的工夫。 1.2 公式的表示法：在A3儲存格中輸入：“=A1 + A2” Excel會將A1儲存格的值+A2儲存格的值的結果顯示 在A3儲存格中。 NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 4 、建立公式 1.3 輸入公式：輸入公式必須以等號 “ = ” 起首， 如 = A1 + A2，這樣Excel才知道我們輸入的是公 式，而不是一般的文字資料。 1.4 實例說明： NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE

3、5 、相對參照位址與絕對參照位址 2.1 說明：相對參照位址的表示法：B1、C4 絕對參照位址的表示去：在儲存格位址前 面加上“$”符號，如：$B$1、$C$4 2.2 相對與絕對參照的差異： 套用在公式上，相對參照位址會隨著公式的位置 而改變，而絕對參照位址則不管公式在什麼地方， 它永遠指向同一個儲存格 2.3 實例說明 NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 6 、相對參照位址與絕對參照位址 2.4 混合參照：我們可以在公式中同時使用相對參照 與絕對參照，這種情形稱為混合參照混合參照。例如： = $A$1 + A2

4、= $B1 + B2 絕對參照相對參照絕對參照相對參照 這種公式在複製後，絕對參照的部份(如$B1的$B)不 會變動，而相對參照的部份則會隨情況做調整 2.5 實例說明 NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 7 、函數的使用 3.1 說明:函數是Excel根據各種需要，預先設計好的 運算公式，可讓您省下不少自己設計公式的時間。 3.2 函數的格式：每個函數都包含三個部份： 函數名稱、引數、小括號。 NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 8

5、、函數的使用 SUM(Number1,Number2,) 函數名稱 小括號 引數 SUMSUM即是函數名稱，從函數名稱可大略得知函數的功能、用途。 小括號用來括住引數，有些函數雖沒有引數，但小括號還是不 可以省略。 引數是函數計算時所必須使用的資料，例如SUM(1,3,5)SUM(1,3,5)即表示 要計算1,3,5三個數字的總合，其中的1,3,5就是引數。 NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 9 、函數的使用 3.3 引數的資料類型：函數的引數不僅是數字類型而 已，它還以是文字，或是： 位址：如SUM(B1,C3

6、)SUM(B1,C3)即是要計算B1儲存格的值+C3儲存格的值。 範圍：如SUM(A1:A4)SUM(A1:A4)即是要加總A1：A4範圍的值。 函數：如SQRT(SUM(B1:B4)SQRT(SUM(B1:B4)即是先求出B1:B4的總和後，再開 平方根的結果。 3.4 實例說明 NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 10 、函數的使用 3.5 自動加總鈕：一般工具列上的自動加總鈕 ，它 讓我們快速輸入函數。例如當我們選取儲存格B8，並 按下 鈕時，便會自動插入SUM函數，且連引數都幫我 們定好了。 自動選取好函數

7、 引數，不過您也 可以自行重新選 取其他範圍 你只需按下Enter 鍵，便可算出班 費的總支出囉！ NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 11 、函數的使用 3.6 輸入其它函數插入函數交談窗：插入函數交談窗 是Excel函數的大本營，當你在函數方塊列示窗中找 不需要的函數時，就可從這裡來輸入函數。 3.7 實例說明 NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 12 、自動計算功能 4.1 說明：之前我們想要得知某範圍內的總和時，總是 要在儲存格

8、中建立公式或函數來計算。現在，使用自動自動 計算計算功能，讓您不在需撰寫公式或使用函數的情況下， 快速得到運算結果。 4.2 自動計算功能不僅會計算總合，還可以計算最大、 最小、平均值等等。假設我們現在想知 4.2 實例說明 NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 13 、自動計算功能 4.3 除了加總及平均值外，自動計算的其他功能項目說 明如下： 無：無：取消自動計算的功能。 項目個數：項目個數：計算選定範圍中，有幾個非空白的儲存格。 數字項目個數：數字項目個數：計算選定範圍中，資料為數值的儲存 格個數。 最大值：最

9、大值：找出選定範圍中，最大的數字資料。 最小值：最小值：找出選定範圍中，最小的數字資料。 NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 14 、在公式中使用名稱 5.1 說明：截至目前為止，我們都是用儲存格位址來當 作公式的運算元或函數的引數，雖然可以直接指出 計算的範圍，但卻無法一目了然公式的用途。這裡 我們要教您為儲存格取一個好記且具有意義的名稱， 以後就直接用名稱代替儲存格位址，使公式更易容 閱讀。 NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 15

10、 、在公式中使用名稱 5.2 命名的原則：當我們為儲存格定義名稱時，必須遵 守下列的命名規則： 名稱的第一個字元必須是中文、英文、或底線(_)字 元。其餘字元則可以是英文、中文、數字、底線、句 點(.)和問號(?)。 名稱最多可達255個字元。但別忘了一個中文字就佔 兩個字元。 名稱不能類似儲存格的位址，如A3、$C$5。 名稱不區分大小寫字母，所以MONEY和money視為同一 個名稱。 5.3 實例說明： NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 16 、在公式中使用名稱 5.4 刪除名稱：假如定義的名稱用不到了，相

11、要將它刪 除掉，可執行插入名稱定義插入名稱定義命令，在定義名稱 交談窗中選取欲刪除的名稱，再按下刪除刪除鈕即可。 按此鈕刪 除名稱 NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 17 函數方塊 在此輸入一個= 1.選定要輸入公式的儲存格，即E2，接著將指標移 到資料編輯列中輸入等號 = 建立公式實例說明 NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 18 建立公式實例說明 2.接著輸入=之後的公式，即“B2+C2+D2”。請在儲存 格B2上按一下，Exce

12、l便會將B2輸入到資料編輯列中 此時B2被虛線框包圍住 B2自動輸入到公式中 NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 19 建立公式實例說明 3.再來請輸入“+”，然後選取C2、再輸入“+”，選取D2， 如此公式的內容便輸入完成了。 運算元與儲存格的框線會使用 相同的顏色，以利於辨識 公式建好了 NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 20 建立公式實例說明 4.最後按下資料編輯列上的輸入鈕 或Enter鍵，公 式計算的結果馬上顯示在儲存格E2

13、中 資料編輯會顯示公式 儲存格顯示公式計算的結果 您也可以直接從鍵盤鍵人=B2+ C2+D2”,再按下Enter鍵來輸入 公式，省下滑鼠、鍵盤交替使用 的麻煩 NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 21 相對、絕對參照位址實例說明 1.選取A3,輸入公式“A1+A2”並計算出結果。此為相對 參照位址。 輸入公式“A1+A2” NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 22 相對、絕對參照位址實例說明 2.選取B3,然後在資料編輯列中輸入“=B1

14、” 3.按下F4鍵，則B1便切成$B$1，成為絕對參照位址 當然，您也可直接在資 料編輯中輸入“=$B$1” NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 23 相對、絕對參照位址實例說明 F4F4儲存格儲存格參照位址參照位址B1B1 第1次$B$1絕對參照 第2次B$1混合參照，只有列編號是絕對位址 第3次$B1混合參照，只有欄編號是絕對位址 第4次B1相對參照，還原為相對參照 4. F4鍵可循序切換儲存格位址的參照類型，每按一 次F4鍵，參照位走的類型就會改變，其切換結果 如下： NAN YA PRINTED CIRCU

15、IT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 24 相對、絕對參照位址實例說明 5.接著輸入“+B2”,再按F4鍵將B2切成$B$2，最後按下 Enter鍵，將公式建立完成 絕對參照位址 NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 25 相對、絕對參照位址實例說明 6. 選定A3:B3,拉曳填滿控點到下一列，將公式複製到 下方的儲存格中。 計算結果不同了 NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 26 相對、絕對參照位址

16、實例說明 7. 相對位址公式： A3的公式=A1+A2,使用相對位址， 表示要計算A3往上找兩個儲存格(A1、A2)的總合。 因此複製到A4後，便改成從A4往上找兩個儲存格相 加，結果就變成A2和A3相加的結果 往上找兩個儲存格往上找兩個儲存格 NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 27 相對、絕對參照位址實例說明 8. 絕對位址公式： B3的公式=$B$1+$B$2,使用絕對位址 ，因此不管公式複製到哪裡,Excel都是找出B1和B2的 值來相加，所以B3和B4的結果都是一樣的： 還是找B1和B2 NAN YA P

17、RINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 28 混合參照位址實例說明 請雙按B4進入編緝模式，將插入點移至=之後，接 著按兩次F4鍵，讓$B$1變成$B1。 2. 將插入點移至+之後，按三次F4鍵將$B$2變成B2, 按下Enter鍵。 3. 選定B4,分別拉曳填滿控點至C4及B5。 NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 29 混合參照位址實例說明 與B4同列不同欄，因此$B1 的部份不動，B2變成C2 與B4同欄不同列，因此$B1 的1變成2，B2變成B3

18、C4 B5 NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 30 輸入函數實例說明 1. 選取存放計算結果的儲存格B8,並在資料編輯列中 輸入等號=(函數也是公式的一種，所以輸入函數 時，也必須以等號=起首)。 函數方塊 選取SUM函數 NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 31 輸入函數實例說明 2. 按下函數方塊右側的下拉鈕，在函數列視窗中選取 SUM，此時會開啟函數引數交談窗來協助函數的輸入 這裡會描述此函數的功能 NAN YA PRINTED

19、 CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 32 輸入函數實例說明 3. 再來就是要設定函數的引數。請先按下第一個引數 欄Number1右側的摺疊鈕 將函數引數交談窗收起 來，再從工作表中選取B4:B6當作引數。 選取的範圍會被虛線框圍住 摺疊到這裡來 NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 33 輸入函數實例說明 4. 請按一下Number1欄右側展開鈕 ，再度將函數引 數交談窗展開。 這裡會顯示計算的結果 選取B4:B6當作引數 除了從工作表中 選取儲存格來設 定引數，你

20、也可 以直接在引數欄 中輸入引數，省 下摺疊、展開函 數引交談窗的麻 煩。 T Tipsips NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 34 輸入函數實例說明 5. 按下確定鈕，函數的計算結果就顯示在B8儲存格內： 計算結果 剛才輸入的函數 NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 35 輸入其它函數實例說明 1. 請選擇儲存格B8,然後按下資料編輯列上的插入函數 鈕 （或執行插入函數命令），你會發現資料 編輯列自動輸入等號=，並且開啟插入函數交

21、談 窗： 函數的功能敘述 列出Excel所 提供的函數 可從這裡選擇函數的類別 NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 36 輸入其它函數實例說明 2. 接著我們要從插入函數交談窗中選取SUM函數： 、按下確定鈕，開啟函數引數交談窗 、選取此類別 下的SUM函數 、選擇數學與 三角函數類別 NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 37 當你將函數存入儲存格以後，若想變更引數設定，請選取函 數所在的儲存格，然後按下插入函數插入函數鈕 ，即可展開函

22、數引函數引 數數交談窗來重新設定引數。 輸入其它函數實例說明 、指定好引數範圍 、按下確定鈕即可得到計算結果 NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 38 使用自動計算實例說明 1. 開啟Ch05-05範例檔案，接著選取B2:D2儲存格範圍， 則這三個儲存格的加總值馬上就會顯示在狀態列上： 顯示選取範圍的加總值，這就是自動計算的功能 NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 39 使用自動計算實例說明 2. 自動計算自動計算功能不僅會計算總和，還

23、可以計算最大、 最小、平均值等等。假設我們現在想知道黃傑倫和蔡 永康的音樂科平均分數為，則可如下操作： 、選取B2:B3範圍 平均值顯示在狀態列上 、於狀態列按下右鈕，於狀態列按下右鈕， 並選取快顯功能表中的並選取快顯功能表中的 平均命令平均命令 NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 40 在公式中使用名稱實例說明 請開啟Ch05-06範例檔案，現在我們要將B2:B3儲存 格範圍命名為“歷史分數”： 、按一下名稱 方塊，鍵入“歷 史分數”後按下 Enter鍵，則“歷 史分數”就代表 B2:B3這個範圍 、選定欲命名的範圍B2:B3 NAN YA PRINTED CIRCUIT BOARD CORPORATION 南亞電路板股份有限公司 PAGE 41 在公式中使用名稱實例說明 現在我們就試著用“歷史分數”這個名稱，來建立儲 存 格E2的公式：