第四講章運算能力強的EXCEL公式的應用講解

1、§淡江大學資料處理學門教材 作者：鄭哲斌老師 § 版 權 所 有 第四講章：運算能力強的 EXCEL 公式 的應用【範例檔案：請搭配教學平臺中的教學範例檔案 chap41r.xls 】本次講章您將學習到：公式的觀念公式的運算符號與優(yōu)先順序相對參照與絕對參照的觀念與應用 儲存格資料修改或搬移對公式的影響 善用函數(shù)以簡化公式 函數(shù)精靈的應用公式的觀念我們知道 EXCEL 最基本儲存資料的單位就是工作表中的儲存格（例女口： A1、B3、C5-等儲存格），你可以將每個儲存格想像為是數(shù)學上的一個 變數(shù)，例如將 A1 儲存格想像為 X 變數(shù)、 B3 儲存格想像為 Y 變數(shù)、 C5 儲存

2、 格想像為Z變數(shù)等等。以前我們學過儲存格可以直接存放內容值（例如： 存放 100 的數(shù)值），這種情形如果我們把他轉換為公式的情形來想，就是將 100 的數(shù)值直接放入 X 變數(shù)中（實際上是 A1 儲存格），也就是數(shù)學公式中令 X=100 的意思。這是一個數(shù)學上的代數(shù)觀念，也是 EXCEL 中一個最簡單的 公式觀念，此種觀念在 EXCEL 中稱之為參照的觀念。公式的運算符號與優(yōu)先順序§淡江大學資料處理學門教材作者：鄭哲斌老師 §探 版權所有 探公式的運算符號可分類為：儲存格位址參照、算術、文字連結、邏輯等四類。詳細的運算符號與運算的優(yōu)先順序請參考下表：優(yōu)先順序類別運算符號符號意

3、義說明與範例最高儲 存 格 位 址 參 照 八、:範圍表示連續(xù)性的儲存格範圍，也就是兩個儲存格位址 之間所有的儲存格。例如：A1:A3就是指A1、A2、A3三個儲存格。例如：A:A就是指A整欄的儲存格。例如：2:2就是指整個第2列的儲存格。依聯(lián)集表示將不連續(xù)或個別儲存格位址加以集合起來。例如：=SUM(A1,A3,A5)就是將 A1,A3,A5 相加。例如：=SUM(B1:B2,A2:B2)就是將 A2,B1,B2 相加。特別注意：上例中B2出現(xiàn)兩次，因有重疊，EXCEL 規(guī)定只能計算一次。序空白交集表示選定空白符號左右兩邊範圍的交集儲存格。 例如：B1:B2A2:B2結果就是B2儲存格。-負

4、號將內容值加上負號。例如：-5或-1 o遞算%百分比將內容值乘上0.01。例如：A1%。A指數(shù)數(shù)學的次方符號。例如：AM2(A1內容值的平方)。術*、/乘、除例如：A1*A2。例如：A1/A2。減+、-力、減例如：A1+A2。 例如：A1-A2。文字&聯(lián)結文字將兩個文字加以聯(lián)結。例如：”中華” &民國”其結 果就變成中華民國。=等於例如：A1=A2。邏< >不等於例如：A1 < > A2。>大於例如：A1>A2。輯<小於例如：A1<A2。最低>=大於等於表示大於或等於的意思。例如：A1 >= A2。<=小於等於表

5、示小於或等於的意思。例如：A1 <= A2。上表中所列的為公式中的運算符號，當然在數(shù)學上運算符號是有其順序的，例如：先算乘除後算加減、運算方向是由左而右等。若你想指定公式中§淡江大學資料處理學門教材 作者：鄭哲斌老師 § 版 權 所 有 特定的計算順序，同樣的你也可以使用數(shù)學上的小括號（）來改變運算的順 序。相對參照與絕對參照的觀念與應用當你在設計儲存格內的計算公式時 ，你就一定會使用到儲存格位址的參 照（請參考公式的觀念那一節(jié)） 。更清楚的說，儲存格位址的參照也就 是利用數(shù)學代數(shù)的關念，將儲存格位址所代表的內容值，直接代入公式中來 運算。例如 A1=A2*A3+10

6、0 ，就是將 A2 的內容值乘上 A3 的內容值再加上 常數(shù) 100後的結果放入 A1 儲存格中。公式中儲存格的參照方式可分為四大類：1. 相對參照：儲存格相對座標間的參照。2. 絕對參照：儲存格絕對座標的參照。3. 混合參照：相對參照與絕對參照的特性混合使用。4. 立體參照：多工作表間儲存格的參照。5. 外部參照：多活頁簿間儲存格的參照。相對參照在 EXCEL 中儲存格的命名是以類似座標的方式來表示的，例如 B3 儲 存格就是 B 欄中的第 3 列所交集而得的儲存格。同理， B4 儲存格亦同。但 是 B4 儲存格又可看成為 B3 儲存格向下偏移一個的儲存格 。通常我們在編制 報表時，常常會有

7、某些部份的儲存格其計算的公式是一樣，只是在計算時所 需參考的資料會隨著行列或欄位的偏移而偏移。絕對參照在相對參照中 ，公式的複製會隨著偏移量的變化而自動調整公式中相對 的列或欄的參照。而所謂絕對參照的意思就正好與相對參照相反，也就 是說公式中若有使用絕對參照，則公式複製時有使用絕對參照部份的表示式§淡江大學資料處理學門教材 作者：鄭哲斌老師 § 版 權 所 有 將不會隨著偏移量的變化而自動調整。絕對參照的寫法就是將相對參照的儲存格位址寫法 ，另外在欄標與列標 上分別加上 $符號。例如： $C$4、$D$4、 $E$4?；旌蠀⒄栈旌蠀⒄?是結合了相對參照與絕對參照的兩種特性，

8、此種參照法較 為困難，較為容易弄錯，所以必須多加的練習。之前你所學的相對參照與絕對參照的寫法 ，在欄方面的與列方面的參照 都必須一致。例如：相對參照F4,欄座標F不加$號列座標4也不加$ 號。例如：絕對參照$F$4,欄座標F與列座標4均有加$號。混合參照 的意思就是可以讓儲存格參照中的欄座標與列座標分別採 用不同的參照方式。如此將可以使公式在設計上有更大的應用空間,但特別 需注意公式設計的邏輯正確性。儲存格資料修改或搬移對公式的影響EXCEL 之所以成為歷久不墜的試算表軟體,成功的原因之一就是儲存 格內的公式計算相當?shù)木鳌＜僭O你一旦修改某儲存格內的資料時,則全部 有參照到該被修改儲存格的公式

9、都將會全部重新計算,以隨時維持資料的正 確性。也正因為這種立即更新資料的公式特性,使得你無須變動公式及檢查 資料結果的正確性。善用函數(shù)以簡化公式函數(shù)是一種由 EXCEL 所提供的內建功能。 函數(shù)就是由 EXCEL 的工程師為了達成不同目的的特定計算或處理而事先設計好的公式,使用者 以後當需要使用該函數(shù)時,只要給定該函數(shù)所需的參數(shù),則透過該函數(shù)固定 程序的計算或處理後即可得到預期的結果。使用者無須記憶各種函數(shù)中公式§淡江大學資料處理學門教材 作者：鄭哲斌老師 § 版 權 所 有 的設計內容，只需瞭解各種函數(shù)的名稱、各種函數(shù)的功用、及各種函數(shù)所需 給定的參數(shù)等，即可方便的使用該函數(shù)。函數(shù)在使用上不僅無須設計複雜的公式即可輕鬆的得到結果 ，並且只要 給定函數(shù)所需的參數(shù)，函數(shù)還可重複的使用，更增便利。事實上函數(shù)也就是 一種事先設計好的公式，所以函數(shù)可以與一般的儲存格公式搭配使用。函數(shù)精靈的應用在公式中使用函數(shù)當然很方便 ，然而往往在設計公式時要用何種函數(shù)？ 函數(shù)的名稱為何？函數(shù)的意義為何？函數(shù)中參數(shù)的意義？雖然函數(shù)非常的 方便，但這些問