Excel 2019下定量重复数据如何拆分合并

在日常工作中，经常需要进行数据输入格式的定量数字转换。比如，如图所示表中的A列是某商场促销日各个业务员汇总上报的数据，因为是使用手机记录在微信群中的汇报数据，因此汇报格式是按照“品牌+销量”的简单形式，根据公司的要求，现在每种产品需要按销售时间进行汇总，具体格式如表中B、C、D列所示（图1）。那么，如何才能完成格式的转换呢？下面以Excel 2019为例介绍具体操作。

用活LOOKUP函数 数据简单拆分

我们可以看到，最终数据是要按照原始数据中的销量数字，在C列中定量进行拆分。比如小米电视4台，那么就按照销售时间（开单时间）分为4行排列。对于这样的定量重复数据的整理，可以借助于LOOKUP函数来完成。

定位到B2并输入“小米B48H4K超高清电视”，切换到B3，接着按下Ctrl+E，智能填充会将产品名称数据提取到B2:B4。同样，在C2中输入“4台”，使用智能填充将产品销售数据提取到C2:C4，然后使用查找替换将“台”字替换为空，最终在B、C列得到产品名和销售数量（图2）。

小提示：

这里注意不要在C2直接输入4，因为型号中也有数字，智能填充会提取错误（会提取成4、5、6）。如果A2:A4的原始数据中没有“台”（如A2原始数据是“小米B48H4K超高清电视 4”），那么可以新建一个辅助列B，在B2输入“台”填充，然后在C2输入公式“=A2&B2”。这样再使用上述智能填充方法，就可以在D、E列提取到正确的产品名和销售数字。对于类似文字、数字混杂的数据，大家要善于对原始数据进行加工，从而使用智能填充快速提取需要的数据。

定位到D2输入“1”，D3输入公式“=C2+D2”，表示累加，公式下拉填充到D4后显示累加数字，然后将这个数字作为LOOKUP函数引用实现定量重复填充。定位到E2并输入公式“=LOOKUP(ROW(B1),$D$2:$D$5,$B$2:$B$4)”，然后下拉填充到E10即可（图3）。

公式解释：

ROW(B1)：这里先使用ROW函数获取行号，然后将行号数值作为LOOKUP需要查找的值，这样下拉后依次提取1、2、3行号。$B$2:$B$4为要获得的值，$D$2:$D$5则为需要查找的数据区域，因为这里显示的是累加值，所以LOOKUP在提取数据时就会根据累加数值，重复提取B2:B4区域的数据填充到E列，从而实现定量填充。

最后根据图1的示例，插入新列并输入开单时间，并在G2:G10填充数字1，将A、B、C、D列隐藏，最终完成原始数据的整理。当然这里都是公式引用的数据，为了方便使用，可以直接复制E1:G10数据，新建工作表后点击“开始→粘贴→选择性粘贴→值”即可（图4）。

用好SUM函数 数据合并更简单

上述操作是将简单汇总的数据进行拆分，然后使用LOOKUP函数定量重复填充拆解数据。很多时候，我们还可能需要进行反向操作，比如原始数据就是图4显示的格式，现在需要根据销售日期对每种产品的销量进行汇总显示（图5）。

对于此种操作，可以使用SUM函数对销售数据进行汇总求和，然后使用连字符整理数据即可。复制A2:A10数据到C2:C10，选中C2:C10数据，点击“数据→删除重复值→在当前选定的区域排序”，点击“删除重复项”，这样可以在C2:C4获得不重复产品的名称（图6）。

定位到D2，输入公式“=SUMIF(A$2:A$10,C2,B$2:B$10)”，下拉填充公式到D5，这样可以在D2:D4实现对每种产品的销量进行汇总统计（图7）。

公式解释：

A$2:A$10是求和的条件区域，求和条件则是C2显示的数据（C列需要通过“删除重复项”的方法剔除重复数据，但是数据要来源于A$2:A$10中）。实际求和区域是B$2:B$10，这样下拉公式后就会根据C2:C4的条件，对A$2:A$10中显示的销售数量进行求和汇总。

最后将A、B列隐藏，然后根据图5的格式插入日期列，这样就完成了数据的整理。如果需要将数据整理为类似图1显示的原始数据格式，那么只要在F2:F4输入“台”，在G2输入公式“=D2&E2&F2”即可。如果需要显示为“x月x日销售xx台”的形式，以便于在微信中汇报给老总，可在H2中输入公式“=TEXT(C2,"yyyy年mm月dd日")&"销售"&D2&E2&F2”，这样复制F列数据后粘贴到微信中即可（图8）。注意，因为使用连字符后无法直接显示日期，这里需要使用TEXT函数设置日期格式。