这是Excel大厂自动化报表实战第一期--高级函数与数据连接
数据资源已经与这篇博客捆绑，有需要者可以下载通过网盘分享的文件：2.4自动化报表-8月成交数据.xlsx，2.4自动化报表-8月获客数据.csv等2个文件
链接: https://pan.baidu.com/s/16nzy1ImOdWzmyHgKiuhiVQ?pwd=0623 提取码: 0623 
 

一、高级函数(VLOOKUP函数与XLOOKUP函数)

1.打开2.4自动化报表-8月成交数据.xlsx，点击战区信息表，点击视图选项卡，点击新建窗口，接着拖拽新建窗口(可以看到工作簿分别显示在两个窗口里，并且会同步两个窗口的编辑结果,非常方便我们在同一个工作簿进行对照操作),接着我们按住win键+方向键右，将当前选中的窗口放置到屏幕右侧，然后选择左侧要放置的窗口。就实现了多个窗口的同屏展示。

现在我们要做的就是根据战区名称，将战区信息表的三列数据匹配到成交数据表上，那要如何实现这个操作呢？一个个肉眼看，然后复制粘贴肯定不行，要是有一款函数可以自动识别并匹配数据，是不是就好啦！
这款函数Excel当然有，这就是大名鼎鼎的VLOOKUP！
=VLOOKUP（查找值，包含查找值的范围，包含返回值的范围中的列号，近似匹配 (TRUE) 或精确匹配 (FALSE)）
不过在开始写函数匹配数据前，我们要先把匹配的条件准备好。也就是战区这一列，怎么取出8月成交数据这个表里业务组这列的战区数据呢。用分列就好啦！
2.我们选择成交额、应收利息、逾期金额这三列，右键插入，复制业务组列，回车确认。通过分列操作取出战区，给新列命名为战区、城市、小组

3.写vlookup函数，=VLOOKUP($C2,战区信息!$A$1:$D$5,COLUMN(战区信息!B$1),0)匹配战区编号(输入第一个参数，也就是我们要查找的值，这里我们选择战区单元格，然后输入第二个参数，要进行查找的区域，选择战区信息表，然后是第三个参数，我们要返回区域内的第几列数据，这里我们先按顺序返回战区编号列，而战区编号列在区域列是第二列，可以用COLUMN(战区信息!B$1)表示2，也可以直接输入2，最后进行第四个参数，直接填0，进行精确匹配)填好后回车，这样西部战区的编号003就被正确匹配到了单元格),接着双击句柄填充函数，依次命名为战区编号、战区经理、战区等级，函数向右拉就可以依次填充。

4.复制城市信息的表头粘贴到8月成交数据战区等级右侧，删除城市名称列。此时我们不能再写vlookup函数了(vlookup函数运行逻辑是L函数，在这个L型的运行逻辑里，vlookup函数会默认在数据区域第一列查找第一个参数的数值，再向右匹配，因此作为查找条件的第一个参数必须在引用区域的第一列)。vlookup函数查找列必须在区域最左侧的第一列，无法返回查找列左侧，也就是区域外的数值，如果想要解决这个问题，必须改变数据结构，把城市名称列移动到最左侧，但是总有不能改的数据结构。我们这里有多种方法，其中可以通过Power Query解决，这将放在Power Query专栏里合并查询详细讲解，这里我们可以通过XLOOKUP函数解决！(注意XLOOKUP只有OFFICE365、Excel2021及以上版本才有)
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 
=XLOOKUP(要查找的值，查找的区域，返回的区域，默认返回的值，默认为0完全匹配，搜索模式)
一般只用XLOOKUP前三个参数
=XLOOKUP($D2,城市信息!$B$1:$B$14,城市信息!$A$1:$A$14)

5.城市经理列填写=XLOOKUP($D2,城市信息!$B$1:$B$14,城市信息!C$1:C$14)，双击填充句柄，并向右拉到平均月薪列填充函数

6.这里XLOOKUP函数除了可以返回一个单元格的数值，还能返回整行或整列
=XLOOKUP($D2,城市信息!B:B,城市信息!C:E)




这里我们还没完，我们还需要一份获客数据连接到成交数据上，关闭右侧窗口，然后ctrl+s保存进入数据连接
二、数据连接(XLOOPUP函数多条件匹配连接)
1.用excel打开获客数据发现是乱码，关闭获客数据，回到文件夹，获客数据后缀是CSV(CSV文件本质是用逗号隔开数值的文本文件).右键获客数据，打开方式选择用记事本，就能以它原本的文本格式查看到数据了(而这种文件由于只有文本信息，所以无论打开后对表格进行什么操作，CSV只保留对数值的修改)，文本文件，另存为，将编码格式选择为ANSI，再次用excel打开就不会乱码了

将获客数据分屏到右侧，准备跨表格数据连接(主页Power Query专栏里追加查询也可以解决两个表格的数据合并，并且可以实时刷新数据)这里看到两份数据都是每个业务组每天一行，并且行数都是867行，应该是可以一一匹配的，但是用于匹配的数据有两列，分别为日期和业务组，之前查找的数值和区域都是单列，现在变成了双列又如何处理呢？其实很简单，我们只需要用&连接两个查找的值和区域就可以了。
XLOOPUP函数多条件匹配连接(Power Query专栏后续也会讲到多条件匹配)
方法一:直接在函数里用&连接条件，以及查找的区域
方法二：先用&连接字段，做成辅助列，再基于辅助列匹配
因为计算资源过大，可能需要手动拖拽

2.我们直接输入函数，然后查找的单元格先选日期接着输入&再选业务组，然后逗号，先选第一个查找的区域日期列接着输入&再选业务组列，逗号，最后选择要返回数值所在的一整个区域。接着复制获客数据的列名到成交数据上，然后双击填充句柄，整个获客数据的连接就完成了。(不过由于这里多条件匹配计算量过大，直接双击句柄填充可能没反应，我们手动拖拽即可)如果害怕电脑性能无法完成运算，也可以创建一个辅助列，先将查找的值和区域用&连成一列，接着再基于辅助列进行匹配就可以了。
=XLOOKUP(A2&B3,'[2.4自动化报表-8月获客数据 - 副本 (2).csv]2.4自动化报表-8月获客数据 - 副本 (2)'!$A:$A&'[2.4自动化报表-8月获客数据 - 副本 (2).csv]2.4自动化报表-8月获客数据 - 副本 (2)'!$B:$B,'[2.4自动化报表-8月获客数据 - 副本 (2).csv]2.4自动化报表-8月获客数据 - 副本 (2)'!$C:$H)



3.关闭获客数据，不保存更改，然后保存成交数据，先新建一个工作表，命名为8月成交数据-纯数值，复制连接好的数据，粘贴为值，节省Excel计算资源，再将日期列改为长日期，接着再新建一个工作表，命名为自动化周报，下一期开始自动化周报的制作。