Excel常用函数

●SUM 加總

語法：SUM(儲存格:儲存格)連續儲存格的加總

SUM(儲存格,儲存格,儲存格)不連續儲存格的加總

例1：SUM(A2:A20) 加總儲存格A2至A20。

例2：SUM(A5,A11,A13,B7,B18,B20)加總儲存格A5,A11,A13,B7,B18,B20數值總合。

例3：SUM(A2:A20,B17,C17) 加總儲存格A2至A20後，再加上B17及C17。

●SUMIF 相同條件的加總

語法：SUMIF(選定內容的儲存格範圍,選定的內容,加總的儲存格範圍)

有時候在表格中，常常需要針對特定的內容進行加總，可以用=A1+A5+A9+A13+A17+….或=SUM(A1,A5,A9,A13,A17,….)進行所需內容的加總，若此表內容很多，可能長達數百列時，上述之方法不但費時，可能還會遺漏，此時，就需要用到SUMIF這個函數。

實際的例子：

●絕對位址($)

相對位址

實際的例子：

在匯率32.05的情況下，折合台幣都應該去乘B1這個儲存格，折合台幣1的公式直接複製下來後，在相對位址的情況下，儲存格C4的公式是乘上B2，而非B1，而B2的內容是「手續費」，結果當然不正確，C5,C6,C7的結果也都不正確；折合台幣2的公式已經將B1改成$B$1的絕對位址型式，直接複製下來後，儲存格D4的公式是乘上B1，是正確的欄位，D5,D6,D7的結果也是正確的。

●ROUND 四捨五入

語法：ROUND(計算式,小數點位數)

在儲存格中，直接打上=50/3 結果值應為16.666……循環，但用ROUND函數後，=ROUND(50/3,2) 得出的值為16.67，意思為計算至小數點後第3位，四捨五入取至小數第2位。

實際的例子：

●COUNT 計算筆數(用於數字)

COUNTA計算筆數(用於文字)

COUNTBLANK 計算空白筆數

語法：COUNT(儲存格範圍)

COUNTA(儲存格範圍)

COUNTBLANK(儲存格範圍)

實際的例子：

工號皆為數字，因此計算總人數用COUNT這個函數即可；而姓名皆為文字，所以要用COUNTA 這個函數，若用COUNT計算，得出來的答案為0；在姓別欄中，有3個空白，所以用COUNTBLANK 計算空白數。

●COUNTIF 計算相同條件的筆數

語法：COUNTIF(儲存格範圍,條件)

延用上面的例子，在統計制造部的人數時，在範圍D2:D22中，只要是”制造部”的計算人數，共有12人。

●IF 假如

語法：IF(條件式,執行符合條件式的動作,否則…)

例1：=IF(A1>0,A1*B1,””) 為如果A1大於0的話，那麼讓A1和B1相乘，否則空白。

例2：=IF(A1>0,A1*B1,”錯誤”) 為如果A1大於0的話，那麼讓A1和B1相乘，否則顯示錯誤。

例3：=IF(A1>0,A1*B1,A1*C1) 為如果A1大於0的話，那麼讓A1和B1相乘，否則讓A1和C1相乘。

例4：=IF(A1>0,ROUND(A1*B1,2),”ERROR”) 為如果A1大於0的話，那麼讓A1和B1相乘並四捨五入取小數點2位，否則顯示ERROR。

實際的例子：

製品D因為B5沒有輸入工作人數，故D5的公式則闡述如果B5等於空白，那麼D5就等於空白

，否則讓B5乘D5，並四捨五入取小數2位。

◎IF中可以再帶入IF，一個公式中可有7個IF。

語法：IF(條件式,那麼去執行符合條件式的動作,IF(條件式,那麼去執行符合條件式的動作,IF(條件式,那麼去執行符合條件式的動作,…)))

實際的例子：

因儲存格A1,B1,C1,D1,E1,F1,G1皆不是空白，因此會做A1:H1的加總動作，若將儲存格A1:H1中的其中一個數字清除，帶出來的結果就為空白。

●OR 或

AND 且

運算條件，實際的例子：

例1：產品B的公式代表必須B3和C3均為空白時，則D3也空白，否則計算結果。

例2：產品C的公式代表必須B4和C4均為空白時，則D4也空白，但C4因有單價存在，故計算結果，又因B3沒有鍵入數量，則結果顯示0。

例3：產品D的公式代表必須B5和C5其中一個為空白時，則D5也空白，否則計算結果。

●MID 擷取字元

MIDB擷取字元，用於雙位元之字元

語法：MID(對應儲存格,從第幾個字元開始對應,取幾個字元)

實際的例子：

例1：代表儲存格A1從第6個字元開始，取7個字元。(若有空格也算是1個字元)

例2：代表儲存格A2從第6個字元開始，取7個字元，中文字或全形的英文及數字佔2個Bits(位元)，此都為單純字元的英文，所以用MID或MIDB的結果是一樣的。

例3：代表儲存格A3從第6個字元開始，取7個字元，中英文字都當做1個字元。

例4：代表儲存格A4從第6個字元開始，取7個字元，因為中文是雙位元，從第6個字元開始擷取剛好為品字，故先空一格在取「質?堅」。

●LEFT 從最左邊取字元

LEFTB從最左邊取字元，用於雙位元之字元

我們來看實際的例子：

因為英文字母為單一字元，所以用LEFT或LEFTB的結果是一樣的。而中文字為雙字元，同樣是取4個字元，但LEFTB是用於雙位元之用法，所以只得出2個中文字。

●RIGHT 從最右邊取字元

RIGHTB從最右邊取字元，用於雙位元之字元

與LEFT或LEFTB的用法一樣，只是從右邊開始，在儲存格B4，雖然是從儲存格A4取5個字元，但因使用RIGHTB這個函數，是適用於雙位元之用法，若要取3個中文字則需將公式改成=RIGHTB(A4,6)，因此，只得出「磐石」。

●MOD 取兩數相除後的餘數

實際的例子：

儲存格A2除以儲存格B2，即7/5，餘2。

●VLOOKUP 資料庫內容對應(特別實用)

語法：VLOOKUP(對應儲存格,資料庫定義名稱(或範圍),資料庫定義所在欄位,FALSE)

假設產品的品名有很多，當在打資料表格時，逐筆鑑入資料既費時又費力，若能利用資料庫定義名稱，則方便許多。

實際的例子：

STEP 1：

先行將系統之資料Transfer至Excel，或在Excel中建立一個資料庫，放在Sheet 1的工作表中。

STEP 2：

將要定義的範圍反白，並在「名稱方塊」中，給予名稱，假設取名為「原料」，然後按Enter 。(若不定義名稱也可以) STEP 3：

假設在Sheet 2有一張工作表，要逐筆打入料號、品名、單位等，實在是費時費力，且容易出錯。

因此，我們可以推論C2的公式為： =IF(A2=””,””,VLOOKUP(A2,原料,3,FALSE))

只需打入料號，便帶出所要的其他欄位，在資料特別多時，增加處理速度，非常方便。 此外還有函數HLOOKUP 及LOOKUP ，在此不另外說明。 ◎ 修改定義名稱：

若資料庫的內容持續增加，已超過當初定義的範圍(目前是A2:E28，假設現在至E35)

，如此一來

，超過的部分勢必無法對應到，因此要修正定義的範圍。 選”插入”→”名稱”→”定義”

樞紐分析表

假設已經完成之報表如下：

STEP 1：

STEP 2：

STEP 3：

STEP 4：可依據自己

所需的範圍

STEP 5：

STEP 6：確定後選完成

SUBSTITUTE 將字串中的特定字串以新字串取代

語法：SUBSTITUTE(儲存格,原條件,新條件)

連續快點2

下可選擇

在儲存格B1，將儲存格A1裡的第一個小寫字母「h」，改以大寫「H」來代替。

在儲存格B2，將儲存格A2裡的「-」號，改成以文字「負數」來代替。

●AVERAGE 取平均值

語法：AVERAGE(儲存格範圍)

在Excel中，平常要算平均數，大都是先加總再除以個數，其實可以不用那麼麻煩，來看實際的例子：

要得出平均值，可以加總1~12月的金額總數後再除以12，利用AVERAGE這個函數，只要將範圍定義出即可。

●SMALL 數列中的最小值

語法：SMALL(儲存格範圍,第幾小的數字) 數字1代表最小值

實際的例子：

要得出最大值只要在範圍後利用COUNT這函數，就可以輕易算出。

●RANK資料排名

語法：RANK(儲存格,排名的儲存格區域)

實際的例子：

在儲存格F3的公式代表在儲存格E3:E15的範圍中，儲存格E3的金額大小排名第七，因儲存格E3:E15的範圍是絕對的，故加上「$」的符號。

VALUE將文字型態轉換成數字型態

語法：VALUE(儲存格)

在EXCEL中，數字以文字型態表現時，對於運算相當不便，因此，轉換成數字型態才方便運算，在使用OFFICE XP或OFFICE 2003中，則有更方便的方式。

因為數量為文字型態，故加總後仍為0，使用OFFICE XP或OFFICE 2003則直接利用

轉換成數字即可。

●UPPER將儲存格內容英文小寫轉換成大寫

語法：UPPER(儲存格)

只要是小寫的英文字母，都轉換成大寫，中文字及數字則不受影響。

●MAX數列中的最大值

語法：MAX(儲存格範圍)

得出一數列中的最大值，更快的方法可用MAX

實際的例子：

●ISERROR 判斷值是否有誤

實際的例子：

因書名的資料庫中無B016及B017，故查不到，會產生#N/A的結果，報表會不好看，利用此函

數後，則能避免。

●INT 取整數(無條件刪除小數點後的數字)

實際的例子：

儲存格B1四捨五入後取整數應該為18，但用INT取整數後，得出17。

●SLN 每年攤提的折舊數(直線法)

語法：SLN(取得成本,殘值,耐用年限)

實際的例子：

●其他

字串連結：&

公式中文字型態的內容：加上雙引號(”文字”)

以上只提供一些平時較常使用之函數，Excel的函數包含許多，甚至於日常生活中常用的英文單字，如：DATE，TODAY，TIME，HOUR，MINUTE……等，都是Excel的函數，因使用的機會較小，不做特別的說明。

在公式中運用函數，可多個函數交錯運用，使公式更靈活與嚴謹，當然必須對於函數要有一定程度的瞭解與基本的邏輯運概念，否則容易造成邯鄲學步的窘境，多多練習，自然可體會其中的涵意。

函數並非是Excel的極致表現，巨集才是Excel的高深境界，以VBA來撰寫，包含物件、屬性及方法，某些函數在巨集中也可使用，巨集中的那些函數我們概稱為指令，使用方式和寫公式時就差別很大，以IF為例，巨集中的寫法為

IF RANGE(A1)=…THEN

…………………

ELSE ……………

ENDIF

像RANGE這個指令，在函數的運用中就不包含。

以上簡單說明，函數並不難，只要多練習、多思考，每個人都可以找出自己的最佳法則。