小狐狸事務所: Excel 函數學習筆記 (一) : 試算表基本操作

2020年1月31日星期五

Excel 函數學習筆記 (一) : 試算表基本操作

我在市圖借到一本 Excel 函數的好書 :

# Excel 公式 + 函數超級辭典

很久之前就想好好鑽研 Excel 的妙用, 心動不如行動, 就從 2020 的新春開始行動吧! 計畫是每周回鄉下時學 3~6 個函數, 此書總共有 360 個函數介紹, 所以明年的此時應該就可以學完了.

我手上還有如下幾本 Excel 函數書, 都放在辦公室書櫃, 作為參考用 :

1. 輸入公式與函數 :

Excel 的函數可看成是一種特殊的公式, 點試算表的任一儲存格, 在上方 fx 右方框裡先輸入 "=", 這是開始輸入公式的標記, 這時左方會出現函數下拉式選單可選擇要呼叫的 Excel 函數, 預設是 SUM() :

照其提示輸入要加總之參數後按 Enter, 結果會顯示在儲存格中 :

2. 運算元 (資料型態) :

Excel 的運算元 (資料型態) 有下列四種 :

數值
布林值
錯誤值
字串

這四種資料型態可以用字面值 (常數), 儲存格參照位址 (reference), 或函數產生. 由於 Excel 主要是用儲存格 (cell) 來儲存資料, 因此 Excel 公式中最常存取儲存格參照位址所存放的資料.

數值資料可用符號如下 :

0 1 2 3 4 5 6 7 8 9 + - / . , () E e % $

用法摘要如下 :

數值資料在儲存格中一律靠右顯示 (字串為靠左).
逗號用來表示千分位 (每三位數一個逗號), 注意, 若逗號後面超過三位數, 則Excel 會自動校正; 但若不足三位數則會被視為字串.
小數點用來表示浮點數, 但若小數點多於一個則視為字串.
數字需連續, 中間若有空格會被視為字串. 但表示分數時則需使用一個空格隔開整數部分與真分數部分, 例如帶分數一又四分之一應該用 1 1/4 表示, 其值為 1.25; 而 1/4 必須用 0 1/4 表示 (前面要有 0 與空格), 其值為 0.25, 若中間無空格將被視為字串, 只用 1/4 則會被視為當年的日期 1 月 4 日.
E 與 e 為科學表示法符號, 表示 10 的次方數, 例如 1234 可表示為 1.234E3 或 1.234e3. 每一個儲存格都有預設字元寬度, 超過寬度時會自動改成科學表示法, 例如輸入 123456789012 會顯示 1.23457E11.
日期與時間在 Excel 也是數值, 雖然輸入 2020/1/31, 其實 Excel 內部會轉成自 1900/1/1 至該日之日數儲存; 而輸入 16:04:17 則會轉成零時零分零秒至該時間的秒數儲存.

布林值只有 TRUE 與 FALSE 兩個值. 錯誤值用來表示錯誤訊息, 例如 #VALUE!, #NAME!, #NUM! 等.

只要不是數值, 布林值, 與錯誤值等就是字串, 用法摘要如下 :

字串在儲存格中一律靠左, 每一個儲存格最多只能容納 32000 個字元.
如果要將數值當成字串儲存, 則須在數值前面加單引號, 例如 '123456.
若要在公式中使用字串當運算元, 必須用雙引號括起來, 例如 "DAY".

3. 運算子 :

Excel 公式中的運算子也有四種 :

參照運算子 (傳回數值, 布林值, 或字串)
算術運算子 (傳回數值)
比較運算子 (傳回 TRUE/FALSE 布林值)
字串運算子 (傳回字串)

參照運算子用來參照儲存格, 以取得儲存格內之資料; 算術運算子對數值運算元進行計算; 比較運算子用來判斷運算式之真偽; 字串運算子用來串接字串.

Excel 參照運算子	說明
: (分號)	儲存格起迄範圍
, (逗號)	儲存格範圍之聯集
(空格)	儲存格範圍之交集

Excel 算術運算子	說明
+	加
-	減
*	乘
/	除
%	百分比
^	次方

Excel 比較運算子	說明
=	等於
>	大於
<	小於
>=	大於等於
<=	小於等於
<>	不等於

這些運算子混合運算時有如下之優先順序 (注意, "-" 身兼負號與減法運算子), 但可以用小括號 () 改變預設之優先順序 (括號內優先) :

順序	運算子
1.	: (儲存格參照) , (參照聯集與交集)
2.	- (負號)
3.	% (百分比)
4.	^ (次方)
5.	*, / (乘除)
6.	+, - (加減)
7.	& (字串串接)
8.	=, <, >, <=, >=, <> (比較)

可見在預設無括號下, 參照運算子優先權最高, 其次依序是負號, 百分比, 次方, 乘除, 加減, 比較運算子優先權最低. 注意, % 在 Excel 中為百分比運算子, 不是求餘數; 不等於是用 <>, 不是 !=. 例如 :

另外還有一個字串串接運算子 & (傳回字串), 例如 :

4. 儲存格參照 :

上面範例是直接在公式中輸入運算元, 但 Excel 以試算表為資料儲存與運算區域, 因此公式與函數中的運算元主要是參照 (定位) 試算表中的儲存格內容.

Excel 試算表為 2D 陣列 (矩陣) 結構, 橫軸索引 (欄) 為英文字母 A, B, C, D ..., 縱軸索引 (列) 為整數 1, 2, 3, 4, ..., 儲存格的參照就是利用前橫 (欄) 後縱 (列) 索引為座標來定位, 其方式有三種 :

絕對參照 :
座標以 $ 開頭表示絕對參照, 例如 $A$1, 複製到其他儲存格時不會改變其位置.
相對參照 :
座標不是以 $ 開頭表示相對參照, 例如 A1, 複製到其他儲存格時會改變其相對位置.
混合參照 :
座標中一個以 $ 開頭, 另一個不以 $ 開頭稱為混合參照, 例如 $A1 表示固定在 A 欄, 而 A$1 表示固定在 1 列. 複製到其他儲存格時有 $ 者不會改變其相對位置, 沒有 $ 者不會改變其位置.

例如上面範例中的公式 =SUM(1,2,3) 可以將運算元 1, 2, 3 放在 A1, A2, A3 儲存格內, 然後在 B3 儲存格輸入公式 =SUM(A1, A2, A3), 使用相對參照存取儲存格 A1, A2, A3, 結果為 6 :

接著在 B1 與 B2 分別輸入 5 與 4, 然後將 B3 複製到 C3 儲存格, 結果為 15, 且 C3 的公式變成 =SUM(B1, B2, B3), 可見相對參照在複製時會改變其參照位置 :

這是因為 B3 是參照其前一欄位的前三個儲存格, 所以把 B3 複製到 C3 時, C3 也秉持此相對性, 參照前一欄之前三個儲存格, 即 B1, B2, B3.

如果將 B3 的公式改為絕對參照 =SUM($A$1,$A$2,$A$3), 則將 B3 複製到 C3 時參照位置不變, 仍然參照到 A1, A2, 與 A3, 所以 C3 結果仍然是 6, 其公式仍為 =SUM($A$1,$A$2,$A$3) :