项目8 合并数据

合并数据07-2月-25项目背景与目标因为【消费记录（清洗完数据）】工作表中缺少【学生信息（清洗完数据）】工作表所包含的信息，当对【消费记录（清洗完数据）】工作表进行分析时，将无法考虑“性别”和“专业”等指标。同时，【消费记录（清洗完数据）】工作表存在部分无效信息，将会对后续的分析产生影响。因此需要合并【消费记录（清洗完数据）】工作表和【学生信息（清洗完数据）】工作表，并进一步分析出有效的打卡记录，统计出同一地点同一刷卡时间的有效消费记录和金额。2022/9/1901>通过“校园卡号”列关联匹配，对【学生信息（清洗完数据）】和【消费记录（清洗完数据）】数据进行合并02>分析处理合并后的数据，得到学生在同一地点同一时间的有效消费记录和消费金额1合并同一地点同一刷卡时间的消费记录目录合并工作表2VLOOKUP函数可以在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列的数值。

VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)VLOOKUP函数2022/9/19参数参数解释lookup_value必需。表示需要查找的目标值table_array必需。表示需要查找的范围col_index_num必需。表示table_array中待返回的匹配值的列序号range_lookup可选。表示具体解释函数VLOOKUP返回时是精确匹配还是近似匹配。若为TURE或省略，则返回近似匹配值。若为FALSE，则函数VLOOKUP将返回精确匹配值插入空白列：在【消费记录（清洗完数据）】工作表中，右键单击“消费时间”列，选择【插入】命令，即可插入一列空白列；按照相同的方法，再插入2列空白列；这3列空白列的第1行依次输入“性别”“专业”“门禁卡号”。合并工作表在【消费行为分析表-处理重复值】工作簿中，使用VLOOKUP函数以“校园卡号”列关联匹配，合并【学生信息（清洗完数据）】工作表和【消费记录（清洗完数据）】工作表，将【学生信息（清洗完数据）】工作表的性别、专业、门禁卡号匹配至【消费记录（清洗完数据）】工作表中合并工作表匹配门禁卡号：选择【VLOOKUP】函数：以门禁卡号为例，选中单元格F2，在【编辑栏】中单击按钮，弹出的【插入函数】对话框，在【或选择类别】中选择【查找与引用】选项，在【选择函数】中选择【VLOOKUP】函数。合并工作表匹配门禁卡号：设置【函数参数】对话框：单击【确定】按钮，在弹出的【函数参数】对话框中，当鼠标指针在【Lookup_value】时，下方会出现【Lookup_value】的解释；单击【Lookup_value】右侧的按钮，选择单元格B2，单击按钮回到【函数参数】对话框；单击【Table_array】右侧的按钮，选择【学生信息（清洗完数据）】工作表中的B至E列，单击按钮回到【函数参数】对话框；在【Col_index_num】中输入“4”；在【Range_lookup】中输入“0”。合并工作表匹配门禁卡号：匹配“门禁卡号”列对应的数据：单击【确定】按钮，即可在单元格F2中引用函数公式；将鼠标指针移至单元格F2的右下角，当指标变为黑色加粗的“+”时双击，即可匹配到其他门禁卡号。1合并同一地点同一刷卡时间的消费记录目录合并工作表2使用【排序】功能，根据“校园卡号”“消费时间”列进行排序。使用IF函数标记出不同的消费地点。使用IF函数分析相同卡号消费的前后时间差，若是不同卡号的消费记录则记录时间差为“NULL”。根据前2步的计算结果，使用IF函数标记有效的消费记录。使用【筛选】功能查看每次消费的消费金额。项目分析在【消费记录（清洗完数据）】工作表中有很多消费记录，一般情况下，在一次消费事件中，可能会出现多条消费记录，但是消费的时间差不会太大。例如，当学生在消费时，可能出现的一种情况为：前一分钟内消费了一份米饭，后一分钟消费了一瓶饮料，但这两者也属于同一次的消费事件。因此，如果一个学生的消费时间差大于60分钟，那么视为一次有效的消费事件。IF函数的功能是执行真假值判断，根据逻辑值计算的真假值返回不同的结果。

IF(logical_test,value_if_true,value_if_false)IF函数2022/9/19参数参数解释logical_test必需。表示要测试的条件value_if_true必需。表示logical_test的结果为TRUE时，希望返回的值value_if_false可选。表示logical_test的结果为FALSE时，希望返回的值标记有效的消费事件依次按照“校园卡号”“消费时间”列进行排序：全选【消费记录（清洗完数据）】工作表中的数据，在【数据】选项卡的【排序和筛选】命令组中，单击【排序】图标；在弹出的【排序】对话框中，在【主要关键字】中选择【校园卡号】，在【次序】中选择“降序”；单击【添加条件】按钮，在【次要关键字】中选择“消费时间”字段，【次序】中选择“升序”；单击【确定】按钮，即可使数据依次按照“校园卡号”“消费时间”列进行排序。标记有效的消费事件标记不同的消费地点：使用IF函数对消费地点列进行标记，如果当前记录的消费地点（如单元格P2）与下一条记录的消费地点（如单元格P3）相等，那么记为0，否则记为1。在单元格Q1中输入“标记不同的消费地点”，在单元格Q2中输入“=IF(P2=P3,0,1)”。按下【Enter】键，将鼠标指针移至单元格Q2的右下角，当指标变为黑色加粗的“+”时双击，即可标记不同的消费地点。2022/9/19标记有效的消费事件分析相同校园卡号的刷卡时间，计算消费时间差：使用IF函数分析相同卡号的前后刷卡时间差，并返回分钟值；若是不同卡号，则不处理。在“校园卡号”列后插入一列空白列，在单元格C1中输入“时间差”。在单元格C2中输入“=IF(B2=B3,(H3-H2)*24*60,"NULL")”。按下【Enter】键并将鼠标指针移至单元格C2的右下角，当指标变为黑色加粗的“+”时双击，即可计算出相同校园卡号的刷卡时间差。2022/9/19标记有效的消费事件通过相邻数据的消费时间差和消费地点的异同，识别出有效的消费事件：当“校园卡号”列的当前记录的消费地点（如单元格Q2）与下一条记录的消费地点（如单元格Q3）一样时，且在“时间差”列中，如果当记录（如单元格C2）显示为“NULL”，那么这2条（如第2行和第3行）数据为不同卡号的消费事件，所以当前记录记为1，即认为是有效的消费事件；如果“时间差”大于60分钟，即相同“校园卡号”的消费时间差达到60分钟以上，那么说明这段时间的消费是一个有效的消费事件，记为1；否则记为0。在“时间差”列后插入一列空白列，在单元格D1中输入“消费标记”，在单元格D2中输入“=IF(S2=0,IF(C2="NULL",1,IF(C2>60,1,0)),1)”;按下【Enter】键，将鼠标指针移至单元格D2的右下角，当指标变为黑色加粗的“+”时双击，即可标记所有的消费事件。2022/9/19计算第一个有效消费的总金额。在“消费标记”列后插入一列空白列，在单元格E1中输入“一次消费的总金额（元）”，适当调整列宽，在单元格E2中输入“=IF(D2=1,IF(D1=0,E1+N2,N2),N2)”。按下【Enter】键，将鼠标指针移至单元格E2的右下角，当指标变为黑色加粗的“+”时双击，即可计算每一个有效消费事件的消费总金额。计算有效消费事件的消费总金额在【消费记录（清洗完数据）】工作表中，统计一个有效消费事件的总消费金额。2022/9/19筛选有效消费事件的数据：在【数据】选项卡的【排序和筛选】命令组中，单击【筛选】图标，单击“消费标记”列旁的倒三角按钮，只选择“1”的数据，单击【确定】按钮。筛选有效的消费记录标记有效的消费事件并计算每一个有效消费事件的消费总金额后，需要筛选出同一消费地点同一刷卡时间的消费记录。当删除的数据行数越多时，删除的速度也会随之越慢，此时可以通过【筛选】功能筛选出来，并复制到另一个工作表中。2022/9/19复制所有有效消费事件的数据：按【Ctrl+A】组合键全选所有行数据，再按【Ctrl+C】组合键进行复制；新建一个工作表，按【Ctrl+C】组合键进行粘贴。筛选有效的消费记录2022/9/19自动调整列宽：由于粘贴后的列宽并不会自动调整，所以需要自动