Excel 速查表
一份可搜尋、可列印的 Excel 參考：必備公式、查找函式、文字和日期函式、快速鍵和樞紐分析表。免費。
Essential formulas
10=SUM(A1:A10)
Add a range of numbers
=AVERAGE(A1:A10)
Mean of a range
=COUNT(A1:A10)
Count cells containing numbers
=COUNTA(A1:A10)
Count non-empty cells
=MIN(A1:A10) =MAX(A1:A10)
Smallest / largest value
=ROUND(A1, 2)
Round to 2 decimal places
=SUMIF(B:B,">100")
Sum values matching a condition
=SUMIFS(C:C,A:A,"x",B:B,">5")
Sum with multiple conditions
=COUNTIF(A:A,"yes")
Count cells matching a condition
=AVERAGEIF(A:A,">0")
Average of cells matching a condition
Lookup & reference
9=VLOOKUP(key,A:C,3,FALSE)
Look up a key, return col 3 (exact match)
=HLOOKUP(key,1:3,2,FALSE)
Horizontal lookup across rows
=XLOOKUP(key,A:A,C:C)
Modern lookup (any direction)
=XLOOKUP(key,A:A,C:C,"N/A")
XLOOKUP with a not-found default
=INDEX(C:C,MATCH(key,A:A,0))
Flexible lookup left or right
=MATCH(key,A:A,0)
Position of a value in a range
=INDEX(A1:C10,2,3)
Value at row 2, column 3 of a range
=OFFSET(A1,2,1)
Reference offset from a cell
=INDIRECT("A"&row)
Build a reference from text
Text functions
11=LEFT(A1,3) =RIGHT(A1,3)
First / last 3 characters
=MID(A1,2,4)
4 chars starting at position 2
=LEN(A1)
Number of characters
=TRIM(A1)
Remove extra spaces
=UPPER / LOWER / PROPER(A1)
Change case
=CONCAT(A1,B1)
Join text values
=TEXTJOIN(", ",TRUE,A1:A5)
Join a range with a delimiter
=SUBSTITUTE(A1,"-","")
Replace text occurrences
=FIND("x",A1)
Position of text (case-sensitive)
=TEXT(A1,"0.00%")
Format a number as text
=TEXTSPLIT(A1,",")
Split text into columns by a delimiter
Date & time
9=TODAY() =NOW()
Current date / date-time
=YEAR(A1) =MONTH(A1) =DAY(A1)
Extract date parts
=DATE(2026,6,26)
Build a date from parts
=DATEDIF(start,end,"d")
Days/months/years between dates
=EDATE(A1,3)
Date 3 months after A1
=EOMONTH(A1,0)
Last day of the month
=NETWORKDAYS(start,end)
Working days between dates
=WEEKDAY(A1,2)
Day of week (Mon=1)
=A1-B1
Difference in days between dates
Logical & error handling
9=IF(A1>10,"hi","lo")
Branch on a condition
=IF(A1>10,IF(A1>20,"x","y"),"z")
Nested IF
=IFS(A1>20,"x",A1>10,"y",TRUE,"z")
Cleaner multi-branch logic
=AND(A1>0,B1<10)
True if all conditions hold
=OR(A1>0,B1<10)
True if any condition holds
=NOT(A1)
Invert a boolean
=IFERROR(A1/B1,0)
Return 0 on error
=IFNA(VLOOKUP(...),"")
Handle only #N/A errors
=SWITCH(A1,1,"a",2,"b","?")
Match a value to a result
References & names
9A1
Relative reference (shifts on copy)
$A$1
Absolute reference (locked)
A$1 / $A1
Lock row only / column only
F4
Cycle reference lock styles while editing
Sheet2!A1
Reference a cell on another sheet
A1:A10
A range of cells
A:A / 1:1
Whole column / whole row
Name Box: TaxRate
Define a named range for clarity
=Table1[Amount]
Structured reference to a table column
Keyboard shortcuts
11Ctrl + C / V / X
Copy / paste / cut
Ctrl + Z / Y
Undo / redo
Ctrl + Shift + V
Paste special
Ctrl + Arrow
Jump to edge of data region
Ctrl + Shift + Arrow
Select to edge of data region
Ctrl + ;
Insert today's date
Alt + =
AutoSum the column/row
Ctrl + T
Convert range to a Table
F2
Edit the active cell
Ctrl + Shift + L
Toggle filters
Ctrl + 1
Open Format Cells dialog
Pivot tables & analysis
9Insert > PivotTable
Summarize data interactively
Rows / Columns / Values
Drag fields to build the layout
Value Field Settings
Switch Sum / Count / Average etc.
Show Values As > % of Total
Display values as a share
Right-click > Group
Group dates or numbers into bins
Refresh (Alt + F5)
Reload pivot from source data
Insert > Slicer
Add visual filter buttons
=GETPIVOTDATA(...)
Reference a pivot cell in a formula
Insert > PivotChart
Chart driven by a pivot table
沒有條目符合「:q」。
關於 Excel 速查表
這份 Excel 小抄彙整了試算表工作真正倚賴的公式與快速鍵：常用公式、查找與參照函式、文字函式、日期與時間、邏輯與錯誤處理、參照與命名、鍵盤快速鍵，以及樞紐分析表與分析工具。
它回答那些會打斷實際工作的問題（查找函式的參數順序、如何搭配錯誤處理寫出 IF 公式、哪個函式能擷取文字儲存格的一部分、相對參照與絕對參照的差異）每個問題都附上可直接複製調整的公式。
此小抄免費，完全在你的瀏覽器中載入。搜尋可即時篩選每個公式，固定式目錄可在章節間跳轉，一鍵即可將任何公式複製到剪貼簿，列印按鈕則能製作一份供辦公室使用的紙本參考資料。
如何使用 Excel 速查表
- 瀏覽各章節，從常用公式、查找與參照，到樞紐分析表與分析。
- 搜尋函式名稱，例如 VLOOKUP、SUMIF 或 TEXT，即可立即篩選小抄。
- 透過固定式側邊欄跳到鍵盤快速鍵或參照與命名章節。
- 點擊公式或其複製圖示以複製，然後貼到儲存格中並調整範圍。
- 列印小抄，作為可放在螢幕旁的公式參考資料。
常見問題
共八個章節：常用公式、查找與參照、文字函式、日期與時間、邏輯與錯誤處理、參照與命名、鍵盤快速鍵，以及樞紐分析表與分析功能。
有的。查找與參照章節展示了常見查找公式的參數順序與典型用法：這是 Excel 中最常被搜尋的主題。
有的。專門章節列出了能加快試算表工作速度的編輯與導覽快速鍵，與公式章節並列。
可以。點擊公式或其複製圖示，即可放入剪貼簿；貼到 Excel 後，將範例中的範圍換成你自己的即可。
是的，免費、可搜尋、可列印，且不需註冊。
熱門搜尋
excel 速查表
excel 公式列表
vlookup 與 xlookup 差異
excel 文字函數
excel 日期函數
excel 鍵盤快速鍵
excel if 與 iferror
excel 樞紐分析表基礎
需要協助？
使用此工具時遇到問題？請告訴我們的團隊。