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 数据透视表基础
需要帮助？
使用此工具时遇到问题？请告诉我们的团队。