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 cheat sheet
excel formulas list
vlookup vs xlookup
excel text functions
excel date functions
excel keyboard shortcuts
excel if and iferror
excel pivot table basics
需要協助？
使用此工具時遇到問題？請告訴我們的團隊。