Excel技巧汇总

快捷键汇总：

Ctrl+A：全选工作表

Ctrl+E：按照参考列的格式快速填充

Ctrl+Shift+A：截图，可以截取平常截不到的

enter：往下；shift+enter：往上
tab：往右；shift+tab往左

alt+enter：在光标的位置自动换行

一、Excel基础操作技巧

1.计算带单位的数据

先输入a=255+234，再下拉，选择快速填充，最后替换’a=‘为’=’

商品名称	一月	二月	总计
U0	255个	234个	a=255+234
U1	12箱	32箱	44
U2	12头	12头	24
U3	15吨	26吨	41

2.单元格内换行

光标指到要换行的位置，alt+enter

3.单元格间的移动操作

enter：往下；shift+enter：往上
tab：往右；shift+tab往左

4.快速输入√×

将字体换成wingding2，切换大写模式，再输入P、R、O、S四种打钩模式，可下拉。

二、常用操作技巧

1.批量填充空值

Ctrl+A全选表格后：在<开始>中的<查找和选择>中选定位条件“空值”，输入一个替换的值，最后Ctrl+enter完成，
也可以在完成后更改填充或字体颜色。

2.批量填充公式错误值

Ctrl+A全选表格后：在<开始>中的<查找和选择>中选定位条件“公式”，勾选“错误”，输入一个替换值，最后Ctrl+enter完成，
也可以在完成后更改填充或字体颜色。

3.分类汇总

在<开始>菜单下找到分类汇总，如果想删除分类汇总，可以点击全部删除。

4.高级筛选

高级筛选：<数据><高级>，列表区域是所有字段和数据，条件区域是筛选的字段和条件数据，条件区域可以先写上筛选的条件。

时间筛选：按CTRL+shift+L打开排序，下拉选择日期筛选。对于时间序列,EXCLE筛选功能会默认进行年,月,日的分组。

5.选择性粘贴

将两列数据合并为一列：

​ 创建两个辅助列，第一列生成1-50序列，第二列生成1.1-50.1序列；再将第二列剪切至第一列下面，升序排序；此时数据的行间生成空行，将金额列选择性粘贴到部门列，选择跳过空单元格。

选择性粘贴添加运算：需要在粘贴的目标单元格提前输入要运算的值。

​

6.数据替换

带格式的单元格匹配替换数据：

选中数据，在<开始>菜单打开“查找与替换”，点击“选项”；在格式中选中要替换的单元格，勾选“单元格匹配”，输入替换值，全部替换即可。

**不带格式的单元格替换：**同上述操作，只需要勾选“单元格匹配”即可。

引用替换：一列变五列

​ 先在单元格输入A1，右拉到A10；再下一行创建A11，右拉到A20；此时选中两行，下拉到A100，然后选中全部单元格，查找替换，将A 替换为 =A 即可。

A1	A2	A3	A4	A5	A6	A7	A8	A9	A10
A11	A12	A13	A14	A15	A16	A17	A18	A19	A20
A21	A22	A23	A24	A25	A26	A27	A28	A29	A30
A31	A32	A33	A34	A35	A36	A37	A38	A39	A40
A41	A42	A43	A44	A45	A46	A47	A48	A49	A50
A51	A52	A53	A54	A55	A56	A57	A58	A59	A60
A61	A62	A63	A64	A65	A66	A67	A68	A69	A70
A71	A72	A73	A74	A75	A76	A77	A78	A79	A80
A81	A82	A83	A84	A85	A86	A87	A88	A89	A90
A91	A92	A93	A94	A95	A96	A97	A98	A99	A100

引用替换：五列变一列

​ 打开剪切板，将这五列数据逐列复制到剪切板，最后点击A1单元格，将剪切板的数据全部粘贴。用完之后记得清空，否则剪切板数据会一直存在。

7.快速求和

用途：汇总上方数据列组合键功能。

​ 选中要小计的单元格，若不连续的，辅助Ctrl键，然后按快捷键ALT+= 快速求和

8.填充柄功能

快捷键为CTRL+E，许多格式都可以使用快速填充，汉字中提取数字、日期、时间，格式相同的填充。

知识点：
填充柄功能：双击，复制一列同类型公式，一次几百个、几万个等
如果中间单元格之间存在空行,会怎么样
公式向下移动时,公式内的单元格值发生怎样的变化
方法：
选中E2单元格，将鼠标置于右下角，光标显示为黑色十字
双击填充柄“黑色十字”，复制公式

9.条件格式

选中要查找的单元格，条件格式，新建规则，给唯一值做条件格式

在单元格中增加趋势条：选中数据，在开始菜单下选择条件格式—>数据条

10.数据验证

有时候需要确保单元格输入的数据是符合我们想要的规则时，就会用到数据验证

姓名不能超过4个字
省市自治区导入右侧列表
电话号必须11位
性别只能选择男或者女
科目1成绩0~100分

数据验证的种类如下：可以在出错警告设置相应的警告内容，如不需要，可点全部清除按钮。

数据验证：序列下拉框

​ 在<数据>菜单下选择数据验证”序列“按钮，下拉框的数据可以通过数据来源选择，但只能选择一列或一行的数据。

如果要选择多行多列数据，则需要在<公式>菜单下的名称管理器设定，具体步骤如下：

​ 第一步在<公式>菜单下的“名称管理器”中新建一个数据名为data，引用其中一列数据。

​ 第二步到数据验证中设置“序列”，数据源选择“=data”。

​ 第三步再回到名称管理器中，将data的数据增加其他列的数据。

把整个表的数据都加到数据验证的“序列”中：
第一步：设置名称管理器，先选取符合规则的数据
第二步：去数据验证设置“序列”，数据源选择“=名称管理器设置的名字”
第三步：再回来名称管理器，把“姓名”扩充引用整个表。

11.分列操作

将一列数据按 分隔符 或者 手动 切分成多列数据。

用分隔符：选取要分列的数据，在<数据>菜单下，选择分列按钮，点击分隔符号，常见分隔符号有tab、分号、逗号、空格，也可以选择其他格式（注意只能使用单个字符作为分隔符号），下一步是对分列结果的每个列做保留或丢弃，最后选择一个可粘贴结果的单元格。

用固定宽度：选取要分列的数据，在<数据>菜单下，选择分列按钮，点击固定宽度，这时需要手动在数据中划切分线，下一步是对分列结果的每个列做保留或丢弃，最后自行选择一个粘贴数据的位置。

12.合并计算

跨多个工作表合并计算，需要每个工作表计算的数据，样式一致、名称一致、位置可以不一样。

首先选取求和项，在<数据>菜单下选择合并计算按钮，引用位置选择要计算的工作表数据，每选取一个工作表要点击添加，最后标签位置要勾选上首行、最左列，点击确定。

13.锁定符号$的使用

累计求和：累计求和使用$符把要求和的数据列锁上，=SUM($B$2:B2)。

生成间断的序号：用IF函数判断单元格是否为空，如果是空单元格，则填充空值；若不是空单元格，则使用COUNTA文本计数函数，将起 始单元格锁住，终点单元格不锁。下拉即可完成。=IF(B2=“”,“”,COUNTA($B$2:B2))。

选取特殊字符：如遇到三角形▲，五角星★等特殊字符，如果字符打不出，则选择该单元格加上索$$，如：$A$1。

三、自定义格式

基础代码	代码释义	原始数据	text	显示结果	格式设定	案例
G/通用格式	常规格式，功能和单元格格式常规一样	100	135	100	G/通用格式
#	数字占位符。只显有意义的零而不显示无意义的零。小数点后数字如大于”#”的数量，则按”#”的位数四舍五入	33.1415	33.7	33.142	##.###
0	数字占位符。如果单元格的内容大于占位符，则显示实际数字，如果小于点位符的数量，则用0补足，可以显示无意义的零	3.1415	02.50	03.14150	00.00000	统一的工号位数
@	文本占位符，可以放在任何位置，代替原文本，如果多个@则重复原文本	Excel归故乡	啊菜啊菜	你好Excel归故乡Excel归故乡很棒	@@	批量加统一的前缀后缀
*	重复下一个字符，直到充满列宽。	姓名	哎呀_______________	姓名_____________	@*_
!	强制显示符号，显示一些正常输入无法显示的字符，如引号等	“Excel归故乡”	"！!	“Excel归故乡”	!“@!”
,	千位分隔符，一般会计方面使用较多	70000	70k	70k	0,k
?	数字占位符。在小数点两边增加无意义的空格，以便以小数点对齐	3.1415	70.666	3.1415	?.???	批量展示小数点对齐
颜色	用系统指定颜色显示数据	↑3.0	↓-5.0	3	[绿色]0;[红色]0;[黑色]0;[蓝色]@	红色、黑色、黄色、绿色、白色、蓝色、青色、洋红等
↓-3.0	↑66.0	-3
0	0	0
Excel归故乡	字符串	Excel归故乡
条件	类似于IF函数的判断，可以写入3个条件，并且前两个条件是明确的，最后一个条件归为其他，条件写在方括号里面	及格80	及格60	及格	[>90]“优秀”;[>60]“及格”;“不及格”	及格
不及格59	不及格58	优秀	不及格
↑优秀100	↑优秀100	优秀	优秀
日期代码	y代表年份，可以是yyyy显示4位年份，也可以yy显示后2位年份	2022/5/28	2022	2022年05月28号	分隔符可以是斜杠，短横，年月日	2021年9月6号
m代表月份，可以是mm显示2位月份，也可以是m显示自然序列月份	2022/5/28	05	May
d代表日数，可以是dd显示2位日数，也可以是d显示自然序列日数	2022/5/28	28	Saturday
aaaa显示星期几，aaa显示星期几中的几	2022/5/28	星期六	星期六	aaaa
时间代码	h代表小时；m代表分钟；s代表秒；AM/PM代表上下午（12小时制）	09:12:20 pm	12:22:05 pm	20:02:01	hh:mm:ss am/pm

四、Excel函数

1.文本转换函数text()/value()

text(value, format_text)：将value按format_text格式转换，如果是日期格式：0000-00-00，或者使用其他自定义格式符。

value(text)：将text文本转换为数字类型。

规范日期实例：TEXT(“980102”, “1900-00-00”)，注意1900是规定前两位是19开头的年份、VALUE(“980102”)，转为数值后需要修改为日 期格式、SUBSTITUTE(“980102”, “.”, “/”)*1，乘1是为了自动转为日期的格式。

使用6个公式之一来转换：（同样适用于日期）
a) = A1*1
b) = A1/1
c) = A1+0
d) = A1-0
e) = - -A1 减负运算 （第1个-是减法，第2个-是负数）
f) = VALUE（A1）
逻辑型数字转换为数值型的6个公式：（当A1是逻辑值时）
1) = A1*1
2) = A1/1
3) = A1+0
4) = A1-0
5) = - -A1 减负
6) = N（A1） （N函数）

2.去除空格函数trim()/substitute()

trim(text)：去除text文本单元格内左右两边的空格。

substitute(text, old_text, new_text, 替换第几个)：将文本中的旧值替换为新值，可选参数是找到的old_text从第几个开始替换。

3.数值函数

数学函数:
INT()	取整(向下)	3.345
MOD()	求余数	10
ROUND()	四舍五入	21.3456
ROUNDUP()	向上取整	3.882
ROUNDDOWN()	向下取整	21.981
TRUNC()	截取小数位数，不考虑四舍五入	86.688
ABS()	取绝对值	-23.6
SQRT()	算术平方根	4
RAND()	产生0-1之间的随机小数	0.2232
RANDBETWEEN()	生成指定区间的随机整数

4.文本函数

文本函数:	描述	实例	效果
MID()	字符串截取	国庆六十周年	六十
FIND()	在文本中找到某个字符的位置 (find_target, within_text)	广东省深圳市	省：3
LEFT()	从左取子串, 左边第一个字符开始取num个字符	我真的很棒啊	我真
RIGHT()	从右取子串, 右边第num个字符开始向右取全部字符	梅花香自苦寒来	苦寒来
LEN()	文本长度	国庆六十周年	6
TEXT()	数字转化文本格式	1990/12/23	1990-12-23
REPT	文本重复	ok	okokok
REPLACE	替换特定位置处的文本，按索引位置替换 (old_text, start_num, num_chars, new_text)	13027736727	130****6727
SUBSTITUTE	替换特定符号的文本	13027736727	13027736**7
2021.11.09	2021.11-09
UPPER	转为大写	ok	OK
lower	转为小写	SHOW TIME	show time
CONCAT	将多个文本拼接成字符串	A	WesleyA芜湖
用&符拼接	将多个文本拼接成字符串	Wesley	Wesley芜湖

计算数字出现次数：用len()函数减去len(substitute())。

5.计算函数

统计函数:	参数	作用
MAX()		求最大
MIN()		求最小
SUM()		求和
COUNT()		数值计数(忽略空值)
COUNTA()	统计文本个数	计数包括文本（忽略空值）
AVERAGE()		求平均(会自动忽略文本求和数值)
COUNTIF()	单个条件	条件计数
SUMIF()	单个条件	条件求和(条件要用&来引用单元格,不能直接写)
AVERAGEIF()	单个条件	条件平均
COUNTIFS()	(计数区域, 条件值…(不断重复)）	多条件计数(里面的多条件是且的关系) 条件区域不分先后
SUMIFS()	(求和区域, 条件区域1, 条件值1, …)	多条件求和 求和区域必须在前面
AVERAGEIFS()	(求平均区域, 条件区域1, 条件值1, …)	多条件平均 同上先选择统计区域,后跟条件区域.
FREQUENCY()	(data_array, bins_array)	求数据分桶分布频率，需要事先做好分桶区间的单元格， 结果为数组需要CTRL+shift+enter输入，分桶结果默认左闭右开 输入时要比分桶多一格。
RANK()	(number, reference, order by)	排名次，返回number在reference中的排名
LARGE()	(reference, 名次k)	在搜索区域中返回名次为k的数据的值

6.逻辑函数

逻辑函数:
IF()	(表达式1, if_ture, if_false)	分支判断条件，可以层层嵌套
AND()	表达式1，表达式2…	有一个为假返回假 *
OR()	表达式1，表达式2…	有一个为真则返回真 +
NOT()		非

7.日期函数（重要）

日期格式可以直接相加减。datedif函数是重要的时间日期函数

日期函数:
YEAR()	求年	2022/1/10	2022
MONTH()	求月	2022/1/11	1
DAY()	求日	2022/1/12	12
TODAY()	当前日期	2022/1/13	2022/5/28
DATE()	计算给定的日期,识别日期格式	2022/1/14	2020/5/22
weekday()	返回星期几数字格式	2022/1/10	7
weeknum()	返回一年中的周数	2022/1/11	22
NOW()	当前日期和时间(电脑系统的时间)		2022/5/28 18:25
EDATE()	指定日期前后月份的日期		2022/7/28
EOMONTH	某个月份最后一天的序列号		2022/4/30
DATEDIF()	计算日期差，(以前时间，现在时间，求差范围)		18

DATEDIF函数的使用：

起始日期	结束日期	差	公式	参数	说明	效果
2010/8/1	2012/2/4		=DATEDIF(A2,B2,“Y”)	Y	相差年数	1
2010/8/1	2012/2/4		=DATEDIF(A3,B3,“M”)	M	相差总月数	18
2010/8/1	2012/2/4		=DATEDIF(A4,B4,“YM”)	YM	一年内相差月数	6
2010/8/1	2012/2/4		=DATEDIF(A5,B5,“D”)	D	相差总天数	552
2010/8/1	2012/2/4		=DATEDIF(A6,B6,“YD”)	YD	一年内相差天数	187
2010/8/1	2012/2/4		=DATEDIF(A7,B7,“MD”)	MD	一月内相差天数	3

时间计算的例子：停车收费系统

计算天数：用IF判断，如果离开时间>停车时间，则DATEDIF函数直接相减；否则DATEDIF函数相减后需要减1。

​ =IF(离开>=停车,DATEDIF(停车日,离开日,“D”),DATEDIF(停车日,离开日,“D”)-1)

计算时间（时分秒）：用IF判断，如果离开时间>停车时间，则时间直接相减；否则（离开时间+24小时 再减去 停车时间）。

8.查找匹配函数（重要）

查找与引用函数:	描述	参数
VLOOKUP()	垂直方向查找	(查找值, 查找区域, 返回查找区域的哪一列, 匹配模式)
OFFSET()	计算偏移量	(参考单元格, 向下偏移行数, 向右偏移列数, 显示行数, 显示列数)
MATCH()	返回单元格在查找范围的位置索引，查找范围只能选取某一列	(查找值, 查找范围(列), 匹配模式)
INDEX()	按照索引返回对应的文本，索引可用单元格代替	（查找区域, 行索引, 列索引）
INDIRECT()	在当前工作表引用其他工作表的值，需要根据需求锁定单元格、用&字符拼接，实现多行多列引用。 优势：可以跨工作表引用	INDIRECT(C$21&“成绩!B”&ROW(B2))， 这里的C21是表头的名字(锁行不锁列)，这样就能匹配到和表头名字一样的工作表
ROW()	返回某个单元格的行号数字	(reference)
COLUMN()	返回某个单元格的列号数字	(reference)
HLOOKUP()	水平方向查找	(查找值, 查找区域, 返回查找区域的哪一行, 匹配模式)
XLOOKUP()	可灵活的反向查找，水平、垂直、倒序查找	(查找值, 查找区域, 返回区域, 匹配模式, 搜索模式)

VLOOKUP+INDIRECT查找不同工作表中的值，直接横拉竖拉就搞定。（注意要确定锁行和锁列）

9.联表查询例子

假设有3张表，需要用两种办法查找出表A中的系数
需要通过先查找城市归属的区域，再用归属的区域中的

表B作为中间表，映射的是城市对应的区域

归属区域1、2、3是门店的类别

Excel中演示：

方法一：（3个XLOOKUP嵌套+交叉行查找）

公式：=XLOOKUP(XLOOKUP(B3,$M$3:$M$14,$N$3:$N$14),$P$3:$P$5,XLOOKUP(C3,$Q$2:$S$2,$Q$3:$S$5))

文字版理解：XLOOKUP( XLOOKUP(B3, 表B城市, 表B归属区域 ),表C归属区域,XLOOKUP( C3,表C的123门店,表C中的系数值区域 ) )

拆解公式步骤：

1. 先用xlookup查出A表城市在B表的归属区域，结果作为最外层XLOOKUP的查找值；
2. 最外层XLOOKUP,以第一步的结果为查找值，查出它在C表的归属区域；返回值为一个XLOOKUP函数；
3. 这个XLOOKUP是查找门店类别在表C的位置，返回整个数值区域的数组。（形成交叉行列查找，类似index函数）

方法二：（index+match(套vlookup)+match查找）

公式：=INDEX($P$2:$S$5,MATCH(VLOOKUP(B3,$M$3:$N$14,2,0),$P$2:$P$5,0),MATCH(C3,$P$2:$S$2,0))

文字版理解：INDEX(整个表C,MATCH(VLOOKUP(B3,表B,2,0),表C),MATCH(C3,表C门店类别,0))

拆解公式步骤：

1. 外层套用index函数，选定数组区域为表C；
2. 返回行序数：内层先用vlookup查找表A城市在表B的归属区域，外层match以vlookup找出的归属区域为查找值，在表C中找出行序数；
3. 返回列序数：简单的match函数，查找表A的门店类别在表C中的列序数。（同样是实现了交叉行列查找）