這是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計算資源，再將日期列改為長日期，接著再新建一個工作表，命名為自動化周報，下一期開始自動化周報的制作。