没想到！Vlookup函数还能拆分数据

今天分享一个Vlookup函数模糊查询的应用实例：将一条出差记录按出差天数拆分为多条记录。具体如下图，人力系统导出的员工出差记录

01 解决思路

• 计算每条出差数据应该拆分成几条，以及每条出差记录拆分后首次出现的位置

• 利用Vlookup函数查询每条出差数据相关信息

• 计算拆分后每条记录的开始日期、结束日期

02 具体操作

• 计算每条出差数据应该拆分成几条，及每条出差记录拆分后首次出现的位置

每条出差记录需要拆分的条数就是出差天数，出差天数之所以能用结束日期-开始日期+1计算，是因为日期型数据本质上就是数字。A列求和，得到总共需要拆分成85条。

每条记录拆分后首次出现的位置，就是前面拆分条数加1

• 利用Vlookup函数查询每条出差数据相关信息

  在“拆分后”sheet中，A列加辅助列，输入1-85，表示拆分后的第1条至第85条

  D3单元格输入公式 VLOOKUP($A3,拆分过程!$B$1:$I$16,D$1,1)，注意，此处第四参数为1，表示如果在查询区域（“拆分过程”sheet的$B$1:$I$16）查询不到想要查询的值，则返回小于等于查询值的最大值所对应的第七列的值。

  以D5单元格为例， 公式VLOOKUP($A5,拆分过程!$B$1:$I$16,D$1,1)，在查询区域（“拆分过程”sheet的$B$1:$I$16）查询不到查询值3，则返回小于等于3的最大值，也就是1，所对应的开始日期2024/9/9

  用同样的方式，可以从“拆分过程”sheet中查询到“原始顺序”、“结束日期”、“工号”、“姓名”、“成本主体编码”、“部门编码”的值

• 计算出拆分后的每条记录的开始日期和结束日期

在“拆分后”sheet中，C列加辅助列,输入公式=COUNTIFS($B$2:B3,B3)计算出拆分后的每条记录是“拆分过程”sheet中每次出差的第几天。

拆分后的开始日期/结束日期=D3+C3-1

03 Vlookup 函数模糊查询使用注意事项

使用Vlookup函数模糊查询功能时，查询值和查询区域必须是数值，并且查询区域值必须是升序排列。