第四講章運(yùn)算能力強(qiáng)的EXCEL公式的應(yīng)用講解

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

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

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

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

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

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

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

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

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