Skip to content

Productivity › 試算表 › Excel 基礎

Excel 基礎 (Excel Basics) ​

Excel 以格子 (grid) 為核心:輸入、整理、檢查中小型表格資料,對非工程背景的人也很友善。本篇是試算表系列的入口:基本結構、Table、常用功能與何時該換工具;函數細節見 進階函數。

核心名詞 ​

名詞意義
活頁簿 (workbook)一個 Excel 檔,含一或多張工作表
工作表 (worksheet)由列 (row) 與欄 (column) 組成
儲存格參照 (cell reference)欄字母 + 列號,如 B3
範圍 (range)A1:C10(起點到終點)、B:B(整欄)、2:2(整列)
公式 (formula)以 = 開頭,結合常數、參照與函數
絕對 / 相對參照$A$1 複製時不變;A1 隨位置平移;$A1 / A$1 只鎖欄或列
已命名範圍 (named range)幫範圍取名(Sales),公式比 A2:A500 好讀,範圍位移也不易出錯
規格上限
單張工作表1,048,576 列 × 16,384 欄(XFD)
實際能順暢開啟的大小受記憶體限制,通常遠低於上限
版本適合
Excel 網頁版 (Excel for the web)共同編輯、分享、基本操作
桌面版 (Desktop)進階功能:Power Pivot、部分 Power Query 來源、VBA、大型活頁簿

表格 (Excel Table) ​

把資料區選起來按 Ctrl + T 轉成 Table,是寫公式前最值得做的一步:

好處說明
自動延伸新增列時公式、格式、樞紐分析來源自動包含
結構化參照 (structured reference)用欄名寫公式:=AVERAGE(Students[Test Score])
內建篩選、排序、合計列 (totals row)不用自己畫
下游工具相容Power Query、樞紐分析表、Python 都能直接讀 Table

一欄只放一種資訊、一列一筆紀錄、不合併儲存格、不在資料區內留空列。這是之後所有分析、樞紐分析與 Power Query 能順利運作的前提。

常用功能 ​

想做的事功能備註
暫時只看符合條件的列篩選 (Filter)也能依儲存格顏色、字型顏色篩
依欄位值重新排列排序 (Sort)先統一型別,否則數字與文字混排
已排序資料分組小計小計 (Subtotal)正式報表用 樞紐分析表 更彈性
限制輸入內容、下拉選單資料驗證 (Data Validation)見 資料驗證
依規則自動上色設定格式化的條件 (Conditional Formatting)見 條件式格式
重複的匯入與清理Power Query見 Power Query

常用公式 ​

函數依用途整理在 進階函數;這裡只列最常用的幾個與一個實用技巧。

公式用途
=SUM(B2:B11)、=AVERAGE(B:B)、=COUNT()、=MIN()、=MAX()基本聚合
=AVERAGE(Students[Test Score])對 Table 欄位聚合
=XLOOKUP(A2, Ref[ID], Ref[Name], "N/A")依 ID 帶回名稱
=TEXTJOIN(",", TRUE, A1:A100)用逗號串接,略過空白
=IF(A1<>"", LEN(A1) - LEN(SUBSTITUTE(A1, ",", "")) + 1, "empty")計算一格中以逗號分隔的項目數

最後一式的原理:去掉逗號前後的字元數差 = 逗號數,項目數 = 逗號數 + 1。

何時該換工具 ​

訊號往哪裡移
每次更新都重做一樣的清理步驟Power Query
多張表要建立關聯、指標要重用Power Pivot、Power BI
公式跨很多工作表互相引用、難以追蹤重構,或改用 Python / R
資料量讓活頁簿明顯變慢、需要版本控管pandas、資料庫
要自動產出報表或批次處理很多檔Python 處理 Excel

Excel 會自動把 1-Mar、MARCH1、SEPT2 這類字串轉成日期(基因名稱被改壞是生資圈著名的問題),長數字 ID 也可能被轉成科學記號。匯入 CSV 時用 Power Query 或「資料 → 從文字/CSV」並把欄位型別設為文字。