Excel自学三部曲_Part3：Excel工作场景实战（四）

四、高级函数与数据连接

1. 多窗口操作

  如何将两张子表数据（战区信息、城市信息）连接到主表数据（成交数据），增加主要数据的业务信息

• 首先直接点到战区信息表，然后点击“视图-新建窗口”，接着拖拽新建的窗口，可以看到工作簿分别显示到了两个窗口里，并且会同步两个窗口的编辑结果，非常方便我们在同一个工作簿里进行对照操作；接着我们按住“WIN+方向键右”，将当前选中的窗口放置到屏幕右侧，然后选择左侧要放置的窗口，就实现了多个窗口的同屏展示，点击边界还可以调整窗口的占比
  
  
  • 新建窗口
    • 点击视图-新建窗口
    • 可以在多个窗口同时编辑表格
    • 方便对照操作
  • 窗口同屏
    • WIN+方向键
    • 自动按方向填充窗口布局
    • 拖动边界可以调整展示比例
      
      

2. VLOOKUP函数

  接着点击“8月成交数据”，现在我们要做的就是根据战区名称，将战区信息表的三列数据匹配到成交数据表中，可以使用vlookup函数实现，不过在开始写函数匹配数据前，我们要先把匹配的条件准备好，也就是战区这一列，那怎么取出战区这一列数据呢？

• 可以使用分列，选中“成交额、应收利息、逾期金额”这三列数据，右键插入空白列，然后复制“业务组”列，接着选中业务组列，点击“数据-分列-输入分隔符-”，完成分列，然后适应一下列宽，再给新列命名
  
  
  
  
• 然后来写VLOOKUP函数
  
  
  
• 接着选中“城市信息表”，复制城市信息表的表头，准备连接并匹配城市信息表的数据，但是因为VLOOKUP的运行逻辑是L型的，所以VLOOKUP函数会默认在数据区域的第一列查找，根据第一个参数的数值再向右匹配，因此作为查找条件的第一个参数必须在引用区域的第一列。这里，我们将函数中的查找区域改为从“城市名称”列开始，函数就能正常运行了，但是改完区域，返回的第一列是“城市名称”本身，而我们要返回的是“城市名称”左侧，也就是区域外的“城市编号”列，但是VLOOKUP函数很呆，查找列必须在区域最左侧的第一列，无法返回查找列左侧，也就是区域外的数据，如果想要解决这个问题，必须要改变数据结果，把“城市名称”列移动到最左侧，选中“城市名称”列，按住Shift拖拽，就可以将列插入到第一列左侧，此时使用VLOOKUP函数就可以了。
  
  
  • VLOOKUP函数参数
    • VLOOKUP (lookup_value, table_array, colindex_num, [range_lookup])
    • VLOOKUP(要查找的值，进行查找的区域，返回区域的第几列数据，填0精确查找])
  • VLOOKUP函数运行逻辑
    • 函数按L型运行
    • 先竖：先在查找区域的第一列匹配查找值
    • 再横：接着返回匹配行对应的第n列数值
  • VLOOKUP函数匹配区域限制
    • 因为函数按L型运行
    • 查找列必须在区域的左侧第一行
      
      

3. XLOOKUP函数

   不过这样修改数据结构，虽然能达到我们的目标，但是总有些你不能改的表格，那有没有什么函数，可以在实现VLOOKUP函数功能的同时不受L型运行逻辑的限制呢？当然有，它就是XLOOKUP函数，但是该函数目前只有Office365 Excel2021及之后的版本可以使用。而XLOOKUP的函数也很简单，一共有6个，我们一般只会使用前3个，它们分别是“要查找的值，查找的区域，返回的区域”。

• 如果我们要匹配“城市编号”列，只需要先选择要查找的“城市”，然后选中查找的区域“城市”列，接着选中要返回的数值区域“城市编号”列，回车，就可以完成匹配了。并且，由于查找和返回的区域都是单独指定的，我们可以随意移动列的位置，完全不受VLOOKUP中查找区域和查找顺序的限制，非常方便
  
• 并且，XLOOKUP除了可以返回一个单元格的数值，还能返回整行或整列，这里再次输入函数，然后依次选择“城市”单元格、“城市”列，接着将返回的数值直接选择为整个表格区域，可以看到XLOOKUP函数直接溢出返回了对应的整行数据
  
• 我们将区域调整为从“城市经理”开始，让两边的字段一 一对应，这样就一次性完成了整行的匹配，再也不用为每个单元格都输入函数了。可以说学会了XLOOKUP函数，将会大大提升我们的数据匹配效率，同时替代原有的VLOOKUP、INDEX和MATCH功能组合，用更简单的逻辑实现更强大的操作，真的是所有EXCEL深度使用者的福音，到这里就完成了战区和城市信息的匹配 。
  
  • XLOOKUP函数支持版本
    • 基本可以完全替代VLOOKUP，不受区域限制匹配数据
    • 只有office365、Excel2021及之后的版本可以使用
  • XLOOKUP函数参数
    • XLOOKUP(lookupvalue, lookup array, return array, [if not foundl, [match_mode]，[search_mode])
    • XLOOKUP(要查找的值，查找的区域，返回的区域，默认返回的值，默认为0完全匹配，搜索模式)
    • 一般只使用前3个参数
    • 不仅可以返回单元格，还可以返回整行和整列，取决于区域的选择
  • XLOOKUP返回行列
    • XLOOKUP不仅可以返回单元格
    • 还可以通过选择区域返回整行和整列
    • 这样就不用为每个单元格都输入函数了
      
      

4. CSV数据格式

  不过这还不算完，还有一份获客数据需要连接到成交数据上，打开获客数据，发现居然是乱码，那这又是咋回事呢？先关闭获客数据，回到文件夹，可以看到获客数据的后缀是CSV，和成交数据的XLSX不一样，类型也不是EXCEL工作表，而是逗号分隔值文件，而这种CSV文件的本质是一个用逗号隔开数值的文本文件

• 右键表格，打开方式选择“记事本”，就能以它原本的文本格式查看到数据了，而这种文件由于只有文本信息，所以无论打开后对表格进行什么操作，CSV只会保留你对数值的修改，正是这种特性，我们很多数据系统和数据库中导出的标准数据格式，都默认为只包合数值的CSV文件。而刚才的乱码问题我们只需要用记事本打开数据后，将编码格式改为ANSI格式，再重新打开就会发现乱码问题解决了
  
  
  

• 此时，将获客数据分屏到右侧 ，准备跨表连接数据，这里我们可以看到两份数据都是每个业务组每天一行，并且行数均为867，应该是可以 一 一匹配的，但是用于匹配的数据有两列，分别为“日期、业务组”，之前查找的数值和区域都是单列，现在变成了双列又该如何处理呢？其实很简单，只需要用&来连接两个查找的值和区域就好了

• 直接输入函数XLOOKUP，然后查找的单元格先选“日期”，接着输入&再选“业务组”（即：参数1=日期单元格&业务组单元格）；逗号，再选第一个查找的区域“日期列”，接着输入&再选“业务组”（即：参数2=日期列&业务组列）；逗号，最后选择要返回数值所在的一整个区域（即：参数3=待返回区域），回车，然后双击填充，整个获客数据的连接就好了。
  
  

• 不够这里由于多条件匹配的计算量过大，直接双击填充可能会没有反应，可以手动拖拽，然后等待Excel完成计算就好了，如果害怕电脑性能无法完成运算，也可以创建一个辅助列，先将查找的值和区域用&连成一列，接着再基于辅助列进行匹配就OK了。完成连接后，关闭获客数据，不保存更改，然后保存成交数据，先新建一个工作表，命名为“8月成交数据-纯数值”，复制连接好的数据，粘贴为值，节省Excel的计算资源，再将日期列格式改回长日期；接着再新建一个工作表，命名为自动化周报，准备开始周报的制作…
  
  

  • CSV数据格式
    • 本质是一个用逗号隔开数值的文本文件
    • 只保留表格的数值，不保留Excel的操作和格式
    • 是很多数据系统和数据库中，导出的标准数据格式
  • CSV数据乱码处理
    • 右键表格，打开方式选择记事本
    • 点击文件，另存为
    • 将编码格式选为ANSI
  • XLOOKUP多条件匹配连接
    • 方法一：直接在函数里用&连接条件，以及查找的区域
    • 方法二：先用&连接字段，做成辅助列，再基于辅助列匹配
    • 因为消耗计算资源过大，可能需要手动拖拽