Excel公式与函数解析课件.ppt

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

2、工夫。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 5、相對參照位址與絕對參照位址2.1 說明：相對參照位址的表示法：B1、C4

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

4、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、函數的使用 SUM(Number1,Number2,)函數名稱小括號引數SUMSUM即是函數名稱，從函

5、數名稱可大略得知函數的功能、用途。小括號用來括住引數，有些函數雖沒有引數，但小括號還是不可以省略。引數是函數計算時所必須使用的資料，例如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)SUM(B1,C3)即是要計算B1儲存格的值+C3儲存格的值。範圍：如SUM(A1:A4)SUM(A1:A4)即是要加總A1：A4

6、範圍的值。函數：如SQRT(SUM(B1:B4SQRT(SUM(B1:B4)即是先求出B1:B4的總和後，再開 平方根的結果。3.4 實例說明輸入函數實例NAN YA PRINTED CIRCUIT BOARD CORPORATION南亞電路板股份有限公司PAGE 10、函數的使用3.5 自動加總鈕：一般工具列上的自動加總鈕 ，它讓我們快速輸入函數。例如當我們選取儲存格B8，並按下 鈕時，便會自動插入SUM函數，且連引數都幫我們定好了。自動選取好函數引數，不過您也可以自行重新選取其他範圍你只需按下Enter鍵，便可算出班費的總支出囉！NAN YA PRINTED CIRCUIT BOARD C

7、ORPORATION南亞電路板股份有限公司PAGE 11、函數的使用3.6 輸入其它函數插入函數交談窗：插入函數交談窗是Excel函數的大本營，當你在函數方塊列示窗中找不需要的函數時，就可從這裡來輸入函數。3.7 實例說明輸入其它函數實例NAN YA PRINTED CIRCUIT BOARD CORPORATION南亞電路板股份有限公司PAGE 12、自動計算功能4.1 說明：之前我們想要得知某範圍內的總和時，總是要在儲存格中建立公式或函數來計算。現在，使用自動自動計算計算功能，讓您不在需撰寫公式或使用函數的情況下，快速得到運算結果。4.2 自動計算功能不僅會計算總合，還可以計算最大、最小、

8、平均值等等。假設我們現在想知4.2 實例說明使用自動計算實例NAN YA PRINTED CIRCUIT BOARD CORPORATION南亞電路板股份有限公司PAGE 13、自動計算功能4.3 除了加總及平均值外，自動計算的其他功能項目說明如下：無：無：取消自動計算的功能。項目個數：項目個數：計算選定範圍中，有幾個非空白的儲存格。數字項目個數：數字項目個數：計算選定範圍中，資料為數值的儲存 格個數。最大值：最大值：找出選定範圍中，最大的數字資料。最小值：最小值：找出選定範圍中，最小的數字資料。NAN YA PRINTED CIRCUIT BOARD CORPORATION南亞電路板股份有限

9、公司PAGE 14、在公式中使用名稱5.1 說明：截至目前為止，我們都是用儲存格位址來當 作公式的運算元或函數的引數，雖然可以直接指出 計算的範圍，但卻無法一目了然公式的用途。這裡 我們要教您為儲存格取一個好記且具有意義的名稱，以後就直接用名稱代替儲存格位址，使公式更易容 閱讀。NAN YA PRINTED CIRCUIT BOARD CORPORATION南亞電路板股份有限公司PAGE 15、在公式中使用名稱5.2 命名的原則：當我們為儲存格定義名稱時，必須遵守下列的命名規則：名稱的第一個字元必須是中文、英文、或底線(_)字元。其餘字元則可以是英文、中文、數字、底線、句點(.)和問號(?)。

10、名稱最多可達255個字元。但別忘了一個中文字就佔兩個字元。名稱不能類似儲存格的位址，如A3、$C$5。名稱不區分大小寫字母，所以MONEY和money視為同一個名稱。5.3 實例說明：在公式中使用名稱實例NAN YA PRINTED CIRCUIT BOARD CORPORATION南亞電路板股份有限公司PAGE 16、在公式中使用名稱5.4 刪除名稱：假如定義的名稱用不到了，相要將它刪除掉，可執行插入名稱定義插入名稱定義命令，在定義名稱交談窗中選取欲刪除的名稱，再按下刪除刪除鈕即可。按此鈕刪除名稱NAN YA PRINTED CIRCUIT BOARD CORPORATION南亞電路板股份有

11、限公司PAGE 17函數方塊在此輸入一個=1.選定要輸入公式的儲存格，即E2，接著將指標移 到資料編輯列中輸入等號=建立公式實例說明NAN YA PRINTED CIRCUIT BOARD CORPORATION南亞電路板股份有限公司PAGE 18建立公式實例說明2.接著輸入=之後的公式，即“B2+C2+D2”。請在儲存 格B2上按一下，Excel便會將B2輸入到資料編輯列中此時B2被虛線框包圍住B2自動輸入到公式中NAN YA PRINTED CIRCUIT BOARD CORPORATION南亞電路板股份有限公司PAGE 19建立公式實例說明3.再來請輸入“+”，然後選取C2、再輸入“+”

12、，選取D2，如此公式的內容便輸入完成了。運算元與儲存格的框線會使用相同的顏色，以利於辨識公式建好了NAN YA PRINTED CIRCUIT BOARD CORPORATION南亞電路板股份有限公司PAGE 20建立公式實例說明4.最後按下資料編輯列上的輸入鈕 或Enter鍵，公式計算的結果馬上顯示在儲存格E2中資料編輯會顯示公式儲存格顯示公式計算的結果您也可以直接從鍵盤鍵人=B2+C2+D2”,再按下Enter鍵來輸入公式，省下滑鼠、鍵盤交替使用的麻煩返回NAN YA PRINTED CIRCUIT BOARD CORPORATION南亞電路板股份有限公司PAGE 21相對、絕對參照位址實

13、例說明1.選取A3,輸入公式“A1+A2”並計算出結果。此為相對 參照位址。輸入公式“A1+A2”NAN YA PRINTED CIRCUIT BOARD CORPORATION南亞電路板股份有限公司PAGE 22相對、絕對參照位址實例說明2.選取B3,然後在資料編輯列中輸入“=B1”3.按下F4鍵，則B1便切成$B$1，成為絕對參照位址當然，您也可直接在資料編輯中輸入“=$B$1”NAN YA PRINTED CIRCUIT BOARD CORPORATION南亞電路板股份有限公司PAGE 23相對、絕對參照位址實例說明F4F4儲存格儲存格參照位址參照位址B1B1第1次$B$1絕對參照第2次

14、B$1混合參照，只有列編號是絕對位址第3次$B1混合參照，只有欄編號是絕對位址第4次B1相對參照，還原為相對參照4.F4鍵可循序切換儲存格位址的參照類型，每按一 次F4鍵，參照位走的類型就會改變，其切換結果 如下：NAN YA PRINTED CIRCUIT BOARD CORPORATION南亞電路板股份有限公司PAGE 24相對、絕對參照位址實例說明5.接著輸入“+B2”,再按F4鍵將B2切成$B$2，最後按下 Enter鍵，將公式建立完成絕對參照位址NAN YA PRINTED CIRCUIT BOARD CORPORATION南亞電路板股份有限公司PAGE 25相對、絕對參照位址實例說

15、明6.選定A3:B3,拉曳填滿控點到下一列，將公式複製到 下方的儲存格中。計算結果不同了NAN YA PRINTED CIRCUIT BOARD CORPORATION南亞電路板股份有限公司PAGE 26相對、絕對參照位址實例說明7.相對位址公式：A3的公式=A1+A2,使用相對位址，表示要計算A3往上找兩個儲存格(A1、A2)的總合。因此複製到A4後，便改成從A4往上找兩個儲存格相 加，結果就變成A2和A3相加的結果往上找兩個儲存格往上找兩個儲存格NAN YA PRINTED CIRCUIT BOARD CORPORATION南亞電路板股份有限公司PAGE 27相對、絕對參照位址實例說明8.

16、絕對位址公式：B3的公式=$B$1+$B$2,使用絕對位址 ，因此不管公式複製到哪裡,Excel都是找出B1和B2的 值來相加，所以B3和B4的結果都是一樣的：還是找B1和B2返回NAN YA PRINTED CIRCUIT BOARD CORPORATION南亞電路板股份有限公司PAGE 28混合參照位址實例說明1.請雙按B4進入編緝模式，將插入點移至=之後，接2.著按兩次F4鍵，讓$B$1變成$B1。3.2.將插入點移至+之後，按三次F4鍵將$B$2變成B2,4.按下Enter鍵。5.3.選定B4,分別拉曳填滿控點至C4及B5。NAN YA PRINTED CIRCUIT BOARD CO

17、RPORATION南亞電路板股份有限公司PAGE 29混合參照位址實例說明返回與B4同列不同欄，因此$B1的部份不動，B2變成C2與B4同欄不同列，因此$B1的1變成2，B2變成B3C4B5NAN YA PRINTED CIRCUIT BOARD CORPORATION南亞電路板股份有限公司PAGE 30輸入函數實例說明1.選取存放計算結果的儲存格B8,並在資料編輯列中 輸入等號=(函數也是公式的一種，所以輸入函數 時，也必須以等號=起首)。函數方塊選取SUM函數NAN YA PRINTED CIRCUIT BOARD CORPORATION南亞電路板股份有限公司PAGE 31輸入函數實例說明

18、2.按下函數方塊右側的下拉鈕，在函數列視窗中選取 SUM，此時會開啟函數引數交談窗來協助函數的輸入這裡會描述此函數的功能NAN YA PRINTED CIRCUIT BOARD CORPORATION南亞電路板股份有限公司PAGE 32輸入函數實例說明3.再來就是要設定函數的引數。請先按下第一個引數 欄Number1右側的摺疊鈕 將函數引數交談窗收起 來，再從工作表中選取B4:B6當作引數。選取的範圍會被虛線框圍住摺疊到這裡來NAN YA PRINTED CIRCUIT BOARD CORPORATION南亞電路板股份有限公司PAGE 33輸入函數實例說明4.請按一下Number1欄右側展開鈕

19、 ，再度將函數引 數交談窗展開。這裡會顯示計算的結果選取B4:B6當作引數除了從工作表中選取儲存格來設定引數，你也可以直接在引數欄中輸入引數，省下摺疊、展開函數引交談窗的麻煩。T TipsipsNAN YA PRINTED CIRCUIT BOARD CORPORATION南亞電路板股份有限公司PAGE 34輸入函數實例說明5.按下確定鈕，函數的計算結果就顯示在B8儲存格內：計算結果剛才輸入的函數返回NAN YA PRINTED CIRCUIT BOARD CORPORATION南亞電路板股份有限公司PAGE 35輸入其它函數實例說明1.請選擇儲存格B8,然後按下資料編輯列上的插入函數鈕 （或

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

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

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

23、RINTED CIRCUIT BOARD CORPORATION南亞電路板股份有限公司PAGE 41在公式中使用名稱實例說明現在我們就試著用“歷史分數”這個名稱，來建立儲存格E2的公式：請選取E2，接著執行插入名稱貼上命令。在貼上名稱交談窗中選取“歷史分數”項目，然後按下確定鈕，資料編輯列和儲存格中便會出現“=歷史分數”。NAN YA PRINTED CIRCUIT BOARD CORPORATION南亞電路板股份有限公司PAGE 42在公式中使用名稱實例說明2.接著鍵入“+C2+D2”,並按下Enter鍵，如此林米奇的總分便會顯示在E2儲存格中。林米奇的總分公式的內容NAN YA PRINT

24、ED CIRCUIT BOARD CORPORATION南亞電路板股份有限公司PAGE 43在公式中使用名稱實例說明公式錯了吧？公式=歷史分數(即B2:B3這兩個儲存格)+C2+D2”,怎麼會是林米奇的3科總分呢？一般而言，公式的運算元應是單一值、單一儲存格、或參照單一儲存格的名稱。因此當我們指定一欄或一列的儲存格來參照時，Excel便會主動從範圍中選擇一個儲存格來計算，其原則如下：若若指定的範圍是一列，則選擇與公式同欄的儲存格。指定的範圍是一列，則選擇與公式同欄的儲存格。若指定的範圍是一欄，則選擇與公式同列的儲存格。若指定的範圍是一欄，則選擇與公式同列的儲存格。NAN YA PRINTED CIRCUIT BOARD CORPORATION南亞電路板股份有限公司PAGE 44在公式中使用名稱實例說明所以“歷史分數”雖然有兩個儲存格，但此處僅會選擇儲存格B2來計算：公式所在選出和公式同列的儲存格來計算指定的範圍是一欄如果Excel無法從範圍中選定一個儲存格來計算，則會出現“#VALUE!”錯誤訊息，這時就要修改公式了。返回NAN YA PRINTED CIRCUIT BOARD CORPORATION南亞電路板股份有限公司PAGE 45