項目8 合并數(shù)據(jù)

合并數(shù)據(jù)07-2月-25項目背景與目標因為【消費記錄（清洗完數(shù)據(jù)）】工作表中缺少【學(xué)生信息（清洗完數(shù)據(jù)）】工作表所包含的信息，當對【消費記錄（清洗完數(shù)據(jù)）】工作表進行分析時，將無法考慮“性別”和“專業(yè)”等指標。同時，【消費記錄（清洗完數(shù)據(jù)）】工作表存在部分無效信息，將會對后續(xù)的分析產(chǎn)生影響。因此需要合并【消費記錄（清洗完數(shù)據(jù)）】工作表和【學(xué)生信息（清洗完數(shù)據(jù)）】工作表，并進一步分析出有效的打卡記錄，統(tǒng)計出同一地點同一刷卡時間的有效消費記錄和金額。2022/9/1901>通過“校園卡號”列關(guān)聯(lián)匹配，對【學(xué)生信息（清洗完數(shù)據(jù)）】和【消費記錄（清洗完數(shù)據(jù)）】數(shù)據(jù)進行合并02>分析處理合并后的數(shù)據(jù)，得到學(xué)生在同一地點同一時間的有效消費記錄和消費金額1合并同一地點同一刷卡時間的消費記錄目錄合并工作表2VLOOKUP函數(shù)可以在表格或數(shù)值數(shù)組的首列查找指定的數(shù)值，并由此返回表格或數(shù)組當前行中指定列的數(shù)值。

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)VLOOKUP函數(shù)2022/9/19參數(shù)參數(shù)解釋lookup_value必需。表示需要查找的目標值table_array必需。表示需要查找的范圍col_index_num必需。表示table_array中待返回的匹配值的列序號range_lookup可選。表示具體解釋函數(shù)VLOOKUP返回時是精確匹配還是近似匹配。若為TURE或省略，則返回近似匹配值。若為FALSE，則函數(shù)VLOOKUP將返回精確匹配值插入空白列：在【消費記錄（清洗完數(shù)據(jù)）】工作表中，右鍵單擊“消費時間”列，選擇【插入】命令，即可插入一列空白列；按照相同的方法，再插入2列空白列；這3列空白列的第1行依次輸入“性別”“專業(yè)”“門禁卡號”。合并工作表在【消費行為分析表-處理重復(fù)值】工作簿中，使用VLOOKUP函數(shù)以“校園卡號”列關(guān)聯(lián)匹配，合并【學(xué)生信息（清洗完數(shù)據(jù)）】工作表和【消費記錄（清洗完數(shù)據(jù)）】工作表，將【學(xué)生信息（清洗完數(shù)據(jù)）】工作表的性別、專業(yè)、門禁卡號匹配至【消費記錄（清洗完數(shù)據(jù)）】工作表中合并工作表匹配門禁卡號：選擇【VLOOKUP】函數(shù)：以門禁卡號為例，選中單元格F2，在【編輯欄】中單擊按鈕，彈出的【插入函數(shù)】對話框，在【或選擇類別】中選擇【查找與引用】選項，在【選擇函數(shù)】中選擇【VLOOKUP】函數(shù)。合并工作表匹配門禁卡號：設(shè)置【函數(shù)參數(shù)】對話框：單擊【確定】按鈕，在彈出的【函數(shù)參數(shù)】對話框中，當鼠標指針在【Lookup_value】時，下方會出現(xiàn)【Lookup_value】的解釋；單擊【Lookup_value】右側(cè)的按鈕，選擇單元格B2，單擊按鈕回到【函數(shù)參數(shù)】對話框；單擊【Table_array】右側(cè)的按鈕，選擇【學(xué)生信息（清洗完數(shù)據(jù)）】工作表中的B至E列，單擊按鈕回到【函數(shù)參數(shù)】對話框；在【Col_index_num】中輸入“4”；在【Range_lookup】中輸入“0”。合并工作表匹配門禁卡號：匹配“門禁卡號”列對應(yīng)的數(shù)據(jù)：單擊【確定】按鈕，即可在單元格F2中引用函數(shù)公式；將鼠標指針移至單元格F2的右下角，當指標變?yōu)楹谏哟值摹?”時雙擊，即可匹配到其他門禁卡號。1合并同一地點同一刷卡時間的消費記錄目錄合并工作表2使用【排序】功能，根據(jù)“校園卡號”“消費時間”列進行排序。使用IF函數(shù)標記出不同的消費地點。使用IF函數(shù)分析相同卡號消費的前后時間差，若是不同卡號的消費記錄則記錄時間差為“NULL”。根據(jù)前2步的計算結(jié)果，使用IF函數(shù)標記有效的消費記錄。使用【篩選】功能查看每次消費的消費金額。項目分析在【消費記錄（清洗完數(shù)據(jù)）】工作表中有很多消費記錄，一般情況下，在一次消費事件中，可能會出現(xiàn)多條消費記錄，但是消費的時間差不會太大。例如，當學(xué)生在消費時，可能出現(xiàn)的一種情況為：前一分鐘內(nèi)消費了一份米飯，后一分鐘消費了一瓶飲料，但這兩者也屬于同一次的消費事件。因此，如果一個學(xué)生的消費時間差大于60分鐘，那么視為一次有效的消費事件。IF函數(shù)的功能是執(zhí)行真假值判斷，根據(jù)邏輯值計算的真假值返回不同的結(jié)果。

IF(logical_test,value_if_true,value_if_false)IF函數(shù)2022/9/19參數(shù)參數(shù)解釋logical_test必需。表示要測試的條件value_if_true必需。表示logical_test的結(jié)果為TRUE時，希望返回的值value_if_false可選。表示logical_test的結(jié)果為FALSE時，希望返回的值標記有效的消費事件依次按照“校園卡號”“消費時間”列進行排序：全選【消費記錄（清洗完數(shù)據(jù)）】工作表中的數(shù)據(jù)，在【數(shù)據(jù)】選項卡的【排序和篩選】命令組中，單擊【排序】圖標；在彈出的【排序】對話框中，在【主要關(guān)鍵字】中選擇【校園卡號】，在【次序】中選擇“降序”；單擊【添加條件】按鈕，在【次要關(guān)鍵字】中選擇“消費時間”字段，【次序】中選擇“升序”；單擊【確定】按鈕，即可使數(shù)據(jù)依次按照“校園卡號”“消費時間”列進行排序。標記有效的消費事件標記不同的消費地點：使用IF函數(shù)對消費地點列進行標記，如果當前記錄的消費地點（如單元格P2）與下一條記錄的消費地點（如單元格P3）相等，那么記為0，否則記為1。在單元格Q1中輸入“標記不同的消費地點”，在單元格Q2中輸入“=IF(P2=P3,0,1)”。按下【Enter】鍵，將鼠標指針移至單元格Q2的右下角，當指標變?yōu)楹谏哟值摹?”時雙擊，即可標記不同的消費地點。2022/9/19標記有效的消費事件分析相同校園卡號的刷卡時間，計算消費時間差：使用IF函數(shù)分析相同卡號的前后刷卡時間差，并返回分鐘值；若是不同卡號，則不處理。在“校園卡號”列后插入一列空白列，在單元格C1中輸入“時間差”。在單元格C2中輸入“=IF(B2=B3,(H3-H2)*24*60,"NULL")”。按下【Enter】鍵并將鼠標指針移至單元格C2的右下角，當指標變?yōu)楹谏哟值摹?”時雙擊，即可計算出相同校園卡號的刷卡時間差。2022/9/19標記有效的消費事件通過相鄰數(shù)據(jù)的消費時間差和消費地點的異同，識別出有效的消費事件：當“校園卡號”列的當前記錄的消費地點（如單元格Q2）與下一條記錄的消費地點（如單元格Q3）一樣時，且在“時間差”列中，如果當記錄（如單元格C2）顯示為“NULL”，那么這2條（如第2行和第3行）數(shù)據(jù)為不同卡號的消費事件，所以當前記錄記為1，即認為是有效的消費事件；如果“時間差”大于60分鐘，即相同“校園卡號”的消費時間差達到60分鐘以上，那么說明這段時間的消費是一個有效的消費事件，記為1；否則記為0。在“時間差”列后插入一列空白列，在單元格D1中輸入“消費標記”，在單元格D2中輸入“=IF(S2=0,IF(C2="NULL",1,IF(C2>60,1,0)),1)”;按下【Enter】鍵，將鼠標指針移至單元格D2的右下角，當指標變?yōu)楹谏哟值摹?”時雙擊，即可標記所有的消費事件。2022/9/19計算第一個有效消費的總金額。在“消費標記”列后插入一列空白列，在單元格E1中輸入“一次消費的總金額（元）”，適當調(diào)整列寬，在單元格E2中輸入“=IF(D2=1,IF(D1=0,E1+N2,N2),N2)”。按下【Enter】鍵，將鼠標指針移至單元格E2的右下角，當指標變?yōu)楹谏哟值摹?”時雙擊，即可計算每一個有效消費事件的消費總金額。計算有效消費事件的消費總金額在【消費記錄（清洗完數(shù)據(jù)）】工作表中，統(tǒng)計一個有效消費事件的總消費金額。2022/9/19篩選有效消費事件的數(shù)據(jù)：在【數(shù)據(jù)】選項卡的【排序和篩選】命令組中，單擊【篩選】圖標，單擊“消費標記”列旁的倒三角按鈕，只選擇“1”的數(shù)據(jù)，單擊【確定】按鈕。篩選有效的消費記錄標記有效的消費事件并計算每一個有效消費事件的消費總金額后，需要篩選出同一消費地點同一刷卡時間的消費記錄。當刪除的數(shù)據(jù)行數(shù)越多時，刪除的速度也會隨之越慢，此時可以通過【篩選】功能篩選出來，并復(fù)制到另一個工作表中。2022/9/19復(fù)制所有有效消費事件的數(shù)據(jù)：按【Ctrl+A】組合鍵全選所有行數(shù)據(jù)，再按【Ctrl+C】組合鍵進行復(fù)制；新建一個工作表，按【Ctrl+C】組合鍵進行粘貼。篩選有效的消費記錄2022/9/19自動調(diào)整列寬：由于粘貼后的列寬并不會自動調(diào)整，所以需要自動