Excel数据关联详解

一、VLOOKUP函数

VLOOKUP函数是Excel中数据关联最常用的一种方法之一，常用于在一个表格中查找与另一个表格中相关的值。具体来讲，VLOOKUP函数可以通过指定一个值，在另外一个表格中查找该值是否存在，并返回该值所在行对应的指定列的值。

VLOOKUP函数的基本语法如下：

=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

其中lookup_value是待查找的值，table_array是表格的数据区域，col_index_num是值在目标表格中所在列的相对位置（如果目标表格是左边的表格，则该值为负数，如果是右边的表格，则为正数），range_lookup表示是否需要查找近似匹配的值。

以下是一个示例：

   A           B           
1  产品编号   产品名称
2  A001       iPhone X
3  A002       MacBook Pro
4  A003       iPad
5  A004       iMac
6  A005       Apple Watch

   A         B    
1  订单编号   产品编号 
2  1001      A001    
3  1002      A004    
4  1003      A005 

=VLOOKUP(B2,$A$2:$B$6,2,FALSE) 
//返回值为 "iPhone X"

二、INDEX-MATCH函数

INDEX-MATCH函数也是Excel中数据关联的一种常用方法，和VLOOKUP不同，INDEX-MATCH使用MATCH函数来查找目标值在数据区域中的位置，再使用INDEX函数来返回该位置对应的值。

INDEX-MATCH函数的基本语法如下：

=INDEX(return_array,MATCH(lookup_value,lookup_array,match_type))

其中return_array是目标值所在的数据区域，lookup_value是待查找的值，lookup_array是需要查找的数据区域，match_type表示需要查找精确匹配值还是近似匹配值。

以下是一个示例：

   A           B           
1  产品编号   产品名称
2  A001       iPhone X
3  A002       MacBook Pro
4  A003       iPad
5  A004       iMac
6  A005       Apple Watch

   A         B    
1  订单编号   产品编号 
2  1001      A001    
3  1002      A004    
4  1003      A005 

=INDEX($B$2:$B$6,MATCH(B2,$A$2:$A$6,0))
//返回值为 "iPhone X"

三、使用数据透视表进行数据关联

除了函数外，Excel还提供了一种更加直观简单的数据关联方法，即使用数据透视表。数据透视表是一种简单的分析工具，可以快速对大量数据进行透视分析和汇总（如数据关联、交叉表分析等）。

以下是一个示例：

假设我们有一个包含销售数据的表格：

   A           B          C
1  日期       产品名称    销售量
2  2021/1/1   iPhone X    100
3  2021/1/2   iPhone X    200
4  2021/1/1   MacBook Pro 300
5  2021/1/2   MacBook Pro 400
6  2021/1/1   iPad        500
7  2021/1/2   iPad        600 

我们可以使用数据透视表将销售数据按产品进行分类汇总，得到下面的结果：

   行标签     列标签    求和 
1  产品名称          
2  iPad        1100    
3  iPhone X    300     
4  MacBook Pro 700     

通过上述数据透视表的分析，我们可以很轻松地实现按照产品名称进行销售数据汇总的功能。

四、条件格式

Excel的条件格式功能也可以实现数据关联的效果。例如，在一个表格中，我们可以根据某一列的数值大小来改变该单元格的格式（如颜色、字体等），以区分该单元格所对应的内容是否符合我们设定的条件。

以下是一个示例：

假设我们有一个包含学生考试成绩的表格：

   A        B        C
1  学生姓名   英语成绩   数学成绩
2  张三      80       90
3  李四      70       60
4  王五      90       85
5  赵六      55       65 

我们可以使用条件格式功能，以不同颜色区分学生的英语成绩和数学成绩是否高于平均成绩：

   A        B        C
1  学生姓名   英语成绩   数学成绩
2  张三      80(高)   90(高)
3  李四      70       60
4  王五      90(高)   85(高)
5  赵六      55       65

//条件格式设置
=B2>AVERAGE($B$2:$B$5) 
//将英文成绩高于平均成绩的单元格设为绿色
=C2>AVERAGE($C$2:$C$5) 
//将数学成绩高于平均成绩的单元格设为绿色 

五、使用Power Query进行数据关联

Power Query是Excel 2010之后的版本中自带的一款ETL工具，它可以帮助用户将数据源进行解析和转换，提供了一种方便快捷的数据处理方式。

以下是一个示例：

假设我们有两个包含员工信息的表格，每个表格包含员工的姓名、部门和入职日期。

我们可以使用Power Query来将这两个表格进行关联，从而得到员工的详细信息表。

首先，我们需要在Power Query中创建两个数据源：

创建数据源1：

   A        B         C
1  姓名     部门       入职日期
2  张三     研发部     2021/1/1
3  李四     财务部     2021/1/2
4  王五     行政部     2021/1/3 

创建数据源2：

   A        B         C
1  姓名     部门       入职日期
2  王六     研发部     2021/2/1
3  赵七     财务部     2021/2/2 

接下来，我们需要将这两个数据源进行合并。具体操作如下：

1. 在Power Query中，选择“从表格/范围”选项，读入两个数据源。
2. 对于每个数据源，选择“添加列” > “自定义列”，添加一列为“公司名称”，值为当前数据源的名称。
3. 选择“合并查询”，将两个数据源进行合并。指定关联键为“姓名”和“部门”，合并方式为“内连接”。

通过上述操作，我们就可以得到一个包含所有员工信息的表格。