整理 25 個 Excel 高頻操作技巧,涵蓋資料比對、公式轉換、格式調整、進階查找、陣列運算等,助你大幅提升試算表效率。
| # | 技巧 | 關鍵操作/公式 |
|---|---|---|
| 1 | 兩欄資料找相同值位置 | =MATCH(B1,A:A,0) |
| 2 | 已知公式求結果 / 已知結果求公式 | 定義名稱 =EVALUATE(Sheet1!C1) / =GET.CELL(6,Sheet1!C1) |
| 3 | 儲存格強制換行 | Alt + Enter |
| 4 | 輸入超過 15 位數字 | 設為文字格式,或輸入前加單引號 ' |
| 5 | 取消隱藏欄位 | 選取相鄰欄右鍵「取消隱藏」或雙擊欄寬分隔線 |
| 6 | 行列互換 | 複製 → 選擇性貼上 → 勾選「轉置」 |
| 7 | Excel 加密 | 另存新檔 → 工具 → 一般選項 / 工具 → 選項 → 安全性 |
| 8 | COUNTIF 多條件 | 單一條件 =COUNTIF(A1:A10,">=90");區間用相減 =COUNTIF(A1:A10,">80")-COUNTIF(A1:A10,">90") |
| 9 | 身分證字號擷取出生日期 | =IF(LEN(A1)=18,DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)),IF(LEN(A1)=15,DATE(MID(A1,7,2),MID(A1,9,2),MID(A1,11,2)),"錯誤")) |
| 10 | 跨工作表完全引用 | 按 Shift/Ctrl 多選工作表後編輯 |
| 11 | 防止重複輸入 / 標示重複值 | 資料 → 驗證 → 自訂 =COUNTIF(A:A,A1)=1;條件格式 =COUNTIF(A:A,A5)>1 |
| 12 | 無法直接開啟 .xls | 檔案總管 → 工具 → 資料夾選項 → 檔案類型 → 進階 → 確認參數 %1 |
| 13 | 下拉選單 | 資料 → 驗證 → 序列 |
| 14 | 10 欄合併成 1 欄求和 | =SUM(OFFSET($A$1,(ROW()-2)*10+1,,10,1)) |
| 15 | 雙條件查找 (VLOOKUP+MATCH / INDEX+MATCH) | =VLOOKUP(H1,A1:E7,MATCH(I1,A1:E1,0),FALSE) / =INDEX(C1:C7,MATCH(H1&I1,A1:A7&B1:B7,0)) (陣列公式) |
| 16 | 隱藏儲存格 0 值 | 自訂格式 0;-0;;@ 或 選項 → 檢視 → 取消「零值」 |
| 17 | 多工作表同儲存格加總 | =SUM(Sheet1:Sheet3!D4) |
| 18 | 取得工作表名稱 | 定義名稱 =GET.DOCUMENT(88) 或 VBA ActiveWorkbook.Name |
| 19 | 取得月份最大天數 / 最後一筆資料 / 修剪平均 | =DAY(DATE(2002,3,1)-1);文字 =LOOKUP(2,1/(B:B<>""),B:B);=TRIMMEAN |
| 20 | Excel 檔案修復工具 | ExcelRecovery |
| 21 | 開方運算 | =8^(1/3) 表示 8 的立方根 |
| 22 | 分散對齊 | 設為文字格式,全形輸入 |
| 23 | 查找連結 | Ctrl + ~ 或 編輯 → 連結 |
| 24 | 空白儲存格批次填 0 | 尋找空值 → 取代為 0 |
| 25 | Word 表格轉 Excel | 複製 → 選擇性貼上 → 值 / 表格轉文字 → 分欄 / 存為文字檔再開啟 |
熟練這些技巧,處理大量資料、複雜報表將不再費時費力。