Excel技巧汇总_xlookup嵌套month

快捷键汇总：

Ctrl+A：全选工作表

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

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

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

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

一、Excel基础操作技巧

1.计算带单位的数据

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

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单元格，将剪切板的数据全部粘贴。用完之后记得清空，否则剪切板数据会一直存在。

7.快速求和

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

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

8.填充柄功能

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

9.条件格式

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

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

10.数据验证

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

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

数据验证：序列下拉框

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

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

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

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

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

11.分列操作

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

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

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

12.合并计算

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

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

13.锁定符号$的使用

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

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

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

三、自定义格式

四、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是为了自动转为日期的格式。

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

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

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

3.数值函数

4.文本函数

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

5.计算函数

6.逻辑函数

7.日期函数（重要）

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

DATEDIF函数的使用：

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

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

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

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

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

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函数）
1
2
3

方法二：（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中的列序数。（同样是实现了交叉行列查找）
1
2
3