Excel 在日常辦公中無處不在,但許多看似棘手的問題其實都有簡單的解決之道。本文整理了 25 個高頻實用的 Excel 技巧,涵蓋數據查找、格式調整、函數應用、防呆設置等核心場景,助你輕鬆化解表格難題,大幅提升處理效率。
使用 =MATCH(B1, A:A, 0) 即可在 A 欄中精確查找 B1 的值所在行號,配合 INDEX 函數更能實現雙向查找。
=EVALUATE(Sheet1!C1),再在單元格輸入該名稱即可顯示計算結果。=GET.CELL(6, Sheet1!C1),可獲取單元格的公式文本。編輯狀態下按 Alt + Enter 即可在同一單元格內換行,告別文字擠在一行的尷尬。
Excel 預設只保留 15 位有效數字。解決辦法有二:
'(如 '1234567890123456789)。若 B 列被隱藏,可選中 A:C 列後:右鍵選「取消隱藏」;或雙擊列寬分隔線;或將滑鼠移至 A、C 列間變雙豎線時拖動。
複製區域 → 選擇性貼上 → 勾選「轉置」 → 確定,瞬間完成行列置換。
COUNTIF 單條件用法:=COUNTIF(A1:A10, ">=90")。區間統計(如 80-90 分)用減法:=COUNTIF(A1:A10, ">80") - COUNTIF(A1:A10, ">90")。
兩種公式任選:
=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)), "錯誤身份證號")) =TEXT(MID(A2,7,6+(LEN(A2)=18)*2), "#-00-00")*1
按住 Shift 或 Ctrl 多選工作表標籤(如 Sheet1、Sheet2),在任一表輸入數據,其他表同步更新。
=COUNTIF(A:A, A1)=1。=COUNTIF(A:A, A5)>1 → 設紅色字體/填充。檔案總管 → 工具 → 資料夾選項 → 檔案類型 → 找 .xls → 進階 → 確認參數有 %1,否則手動添加。
數據 → 數據驗證 → 允許「序列」 → 來源輸入選項或引用區域。
=SUM(OFFSET($A$1, (ROW()-2)*10+1, , 10, 1)),向下填充即可將每 10 行彙總為一行。
=VLOOKUP(H1, A1:E7, MATCH(I1, A1:E1, 0), FALSE)。=INDEX(C1:C7, MATCH(H1&I1, A1:A7&B1:B7, 0))(舊版需 Ctrl+Shift+Enter)。0;-0;;@。優寫法:=SUM(Sheet1:Sheet3!D4),自動包含首尾表之間所有工作表的 D4 儲存格。
Name = GET.DOCUMENT(88);Path = GET.DOCUMENT(2)。=MID(CELL("filename"), FIND("]", CELL("filename"))+1, 255)。Function name(): name = ActiveWorkbook.Name: End Function。| 需求 | 公式/方法 |
|---|---|
| 某月最大天數 | =DAY(DATE(年, 月+1, 1)-1) 或 =DAY(B1-1)(B1 為下月 1 日) |
| 含特定字符的求和 | =SUMIF(A:A, "*某字符*", 數據區) |
| 最後一行文本/數值 | 文本:=LOOKUP(2, 1/(B:B<>""), B:B);數值:=LOOKUP(9.9999E+307, B:B) |
| 去極值平均(如去兩高兩低) | 查閱 TRIMMEAN;被去分數:=LARGE(範圍, {1;2}) / =SMALL(範圍, {1;2}) |
| 判斷最後一位是否字母 | =IF(ISERR(RIGHT(A1)*1), "字母", "數字") |
| 禁止輸入含空格字符 | 數據驗證 → 自定義:=ISERROR(FIND(" ", A1)) 或 =LEN(A1)=LEN(TRIM(A1)) |
| 隔行求和(奇/偶行) | 奇數行:=SUMPRODUCT(A1:A1000, MOD(ROW(A1:A1000), 2));偶數行:=SUMPRODUCT(A1:A1000, 1-MOD(ROW(A1:A1000), 2)) |
| 動態求和至上一行 | =SUM(INDIRECT("A1:A"&ROW()-1)) |
| 按工作表名動態引用 | =INDIRECT(A1&"!E1")(A1 存工作表名) |
ExcelRecovery 可嘗試修復損壞的工作簿。
8 的 3 次方根:=8^(1/3),通用公式 =數值^(1/次方數)。
設單元格格式 → 對齊 → 橫向對齊選「分散對齊」,需輸入全角字符效果最佳。
按 Ctrl + ~ 顯示公式,或「編輯」→「鏈接」查看/斷開外部引用。
選中區域 → Ctrl+H 替換 → 查找內容留空 → 替換為 0 → 全部替換。
掌握這 25 招,Excel 疑難雜症不再是攔路虎,辦公效率躍升看得見!