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. 選擇「合併查詢」，將兩個數據源進行合併。指定關聯鍵為「姓名」和「部門」，合併方式為「內連接」。

通過上述操作，我們就可以得到一個包含所有員工信息的表格。