# Excel 公式 + 函數超級辭典
Source : 博客來
很久之前就想好好鑽研 Excel 的妙用, 心動不如行動, 就從 2020 的新春開始行動吧! 計畫是每周回鄉下時學 3~6 個函數, 此書總共有 360 個函數介紹, 所以明年的此時應該就可以學完了.
我手上還有如下幾本 Excel 函數書, 都放在辦公室書櫃, 作為參考用 :
- Excel函數與分析工具
- EXCEL 2002 函數與分析工具 (楊世瑩, 旗標)
- Excel 公式與函數的使用藝術 (黃景增, 知城)
- 上班族一定要會的Excel函數組合應用商務實例
1. 輸入公式與函數 :
Excel 的函數可看成是一種特殊的公式, 點試算表的任一儲存格, 在上方 fx 右方框裡先輸入 "=", 這是開始輸入公式的標記, 這時左方會出現函數下拉式選單可選擇要呼叫的 Excel 函數, 預設是 SUM() :
照其提示輸入要加總之參數後按 Enter, 結果會顯示在儲存格中 :
2. 運算元 (資料型態) :
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 則會轉成零時零分零秒至該時間的秒數儲存.
只要不是數值, 布林值, 與錯誤值等就是字串, 用法摘要如下 :
- 字串在儲存格中一律靠左, 每一個儲存格最多只能容納 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) :
混合參照時也是同樣原理, 絕對的部分參照索引固定不動, 相對的部分則會移動參照索引位置.
沒有留言:
張貼留言