分合无惧定量重复数据这样拆分合并

2020-11-09 04:09淡然
电脑爱好者 2020年21期
关键词:汇总原始数据粘贴

淡然

用活LOOKUP函数数据简单拆分

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

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

定位到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函数对销售数据进行汇总求和,然后使用连字符整理数据即可。复制A 2:A10数据到C2:C10,选中C2:C10数据,点击“数据→ 删除重复值→在当前选定的区域排序”,点击“删除重复项”,这样可以在C2:C4获得不重复产品的名称(图6)。

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

公式解釋:

A $ 2: A $10是求和的条件区域,求和条件则是C 2 显示的数据(C 列需要通过“删除重复项”的方法剔除重复数据,但是数据要来源于A$2:A$10中)。实际求和区域是B$2:B$10,这样下拉公式后就会根据C 2 : C 4 的条件,对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函数设置日期格式。

猜你喜欢
汇总原始数据粘贴
GOLDEN OPPORTUNITY FOR CHINA-INDONESIA COOPERATION
常用缩略语汇总
帖脸谱
《猫头鹰》小粘贴
A ski trip to Japan
What Would I Change It To
全新Mentor DRS360 平台借助集中式原始数据融合及直接实时传感技术实现5 级自动驾驶
供应商汇总
供应商汇总
世界经济趋势