萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 辦公室軟體 >  >> Excel 教學 >> Excel 綜合

Excel 25 大必學技巧:從基礎操作到進階公式一次掌握

前言

彙整 25 條高頻 Excel 技巧,涵蓋查找、公式、格式、資料處理、列印等面向,適合各級使用者查漏補缺。

技巧清單

  1. 兩欄找相同值位置:=MATCH(B1, A:A, 0)
  2. 已知公式求結果 / 已知結果反推公式:定義名稱用 EVALUATE / GET.CELL(6, ...)(需巨集啟用)。
  3. 儲存格強制換行:Alt+Enter
  4. 超過 15 位數字輸入:儲存格設為文字,或輸入前加單引號 '。
  5. 取消隱藏欄位:選取相鄰欄 > 右鍵「取消隱藏」;或雙擊欄寬分隔線;或拖曳隱藏邊界。
  6. 行列互換(轉置):複製 > 選擇性貼上 > 勾選 轉置。
  7. Excel 加密:另存新檔 > 工具 > 一般選項 設密碼;或 檔案 > 資訊 > 保護活頁簿。
  8. COUNTIF 單一條件 / 區間條件:=COUNTIF(A1:A10, ">=90");區間用減法 =COUNTIF(..,">80")-COUNTIF(..,">=90")。
  9. 身分證字號萃取出生日期:=IF(LEN(A1)=18, DATE(MID(A1,7,4),MID(A1,11,2),MID(A1,13,2)), ...) 或 =TEXT(MID(A2,7,6+(LEN(A2)=18)*2),"#-00-00")*1。
  10. 跨工作表同步輸入:按 Ctrl 或 Shift 多選工作表標籤,編輯一張同步更新所有。
  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)) (CSE)
  16. 隱藏零值:自訂格式 0;-0;;@ 或 選項 > 進階 > 取消勾選「顯示儲存格中的零值」。
  17. 多工作表同儲存格加總:=SUM(Sheet1:Sheet3!D4)(三維參考)。
  18. 取得工作表名稱:定義名稱 GET.DOCUMENT(88)、GET.DOCUMENT(2)、CELL("filename") 搭配 MID/FIND,或 VBA 自訂函數。
  19. 取得月份最大天數:=DAY(DATE(2002,3,1)-1) 或 =DAY(B1-1)(B1 為該月 1 號)。
  20. 含特定字元的加總:=SUMIF(A:A, "*某字*", 金額欄)
  21. 找最後一筆非空資料:文字 =LOOKUP(2,1/(B:B<>""),B:B);數值 =LOOKUP(2,1/(B:B<>0),B:B)。
  22. 去極值平均:=TRIMMEAN(範圍, 比例);被去除值用 LARGE/SMALL(範圍, {1,2})。
  23. 判斷最後一字是否為字母:=IF(ISERR(RIGHT(A1)*1), "字母", "數字")
  24. 禁止輸入空白:資料驗證 > 自訂 =ISERROR(FIND(" ", A1)) 或 =LEN(A1)=LEN(TRIM(A1))。
  25. 間隔列求和:=SUMPRODUCT((A1:A1000)*MOD(ROW(A1:A1000),2))(奇數列),NOT(MOD(...))(偶數列)。
  26. 動態求和至上一列:=SUM(INDIRECT("A1:A"&ROW()-1))
  27. 檔案修復工具:ExcelRecovery。
  28. 開方運算:=8^(1/3)(8 的三次方根)。
  29. 分散對齊:文字格式 + 全形輸入,或設定儲存格格式 > 對齊 > 分散對齊。
  30. 查找連結來源:Ctrl+~ 或 編輯 > 連結。
  31. 空白批次填 0:Ctrl+H 尋找空白、取代為 0。
  32. Word 表格轉 Excel:複製 > 目標儲存格設文字 > 選擇性貼上 > 數值。

學習建議

建議依使用頻率分批練習,先掌握前 10 項(日常高頻),再挑戰進階公式與陣列技巧。每項技巧嘗試在實際工作檔案中應用一次,才能真正內化為能力。

copyright © 萬盛學電腦網 all rights reserved