练习题090:按类别将内容合并到一个单元格,用顿号隔开(综合应用)

函数公式职场模板、财务应用分析图表练习题软件工具表格合并Office 365Power Query表格美化符号作用条件格式学会骗一本不正经避坑指南数据整理筛选技巧偷懒宝典

👆专题文章👆

👇最新文章👇

·  正  ·  文  ·  来  ·  啦  ·

本练习题的问题经常有人问起,还是将它改为练习题,并提供几个参考答案。

本练习题的Excel文件见文后。

练习题090

请使用二种以上的方法:

将同一本图书的单号合并到一个单元格, 中间用顿号隔开

要求:
可以使用辅助列。使用一种方法得60分,二种方法80分,三种以上的方法100分。

先介绍同一类别挨在一起的情况

方法一:
个人认为最简单快捷的方法就是使用新函数:
FILTER、TEXTJOIN函数
G2单元格公式:
=TEXTJOIN("、",1,FILTER($B$2:$B$16,$A$2:$A$16=F2))
各图书名称还可以用函数生成:
=UNIQUE($A$2:$A$16)
此公式也适用于A列乱序的情形。

关于FILTER、TEXTJOIN函数以前介绍过,这里就不啰嗦了,请参阅以前的文章:

肯定有表弟表妹们会说,为什么我的Excel上没有这两个函数。因为它是新函数,office 2019、365版才有。

亲,安装OFFICE 365吧,它真的很香。

安装方法:

那些还在用OFFICE2010、甚至还在用 2003、 2007的朋友,请在文章下留言,让大家认识一下不弃故旧、从不喜新厌旧的你

方法二:辅助列法
本方法专供那些还在用老版本的表亲:
D1单元格公式:
=IF(A2<>A3,B2,B2&"、"&D3)
下拉填充
如果A列相同的图书没有在一起,辅助列公式见后文。
然后用VLOOKUP查找
=VLOOKUP(F2,$A$2:$D$16,4,0)
方法三、
使用OFFSET+PHONETIC:
C2单元格公式
=IF(A2=A3,"、","")
然后复制C列,粘贴为数值(去掉公式)
I2单元格公式
=PHONETIC(OFFSET($B$1,MATCH(F2,$A$2:$A$16,0),0,COUNTIF($A$2:$A$16,F2),2))
关于Phonetic函数的解释及应用请参阅以前的文章:
方法四:
使用Power Query,本方法以乱序数据来介绍:
步骤1:数据--来自工作表
点击确定后打开Power Query
步骤2:
PQ自作聪明的将编号改成了数字格式。我们点击“123”改为文本

选定项目列,点击“转换”中的“分组依据”,将新列名改为:单号列表

在公式编辑栏中,将:

Table.RowCount(_)

改为:

Text.Combine([单号],"、")

完整公式为:

= Table.Group(更改的类型, {"项目"}, {{"单号列表", each Text.Combine([单号],"、"), Int64.Type}})

也可简化为:

= Table.Group(更改的类型, {"项目"}, {"单号列表", each Text.Combine([单号],"、")})

将其加载到工作表中
也可先排一下序再加载
乱序情况下的辅助列法:
最后再补充一下乱序的情况下,如何使用辅助列
在C2单元格输入
=IFNA(B2&"、"&VLOOKUP(A2,A3:C$17,3,0),B2)
注意VLOOKUP函数第二参数的引用类型
A3:C$17
然后使用VLOOKUP查找即可:
=VLOOKUP(E2,$A$2:$C$16,3,0)

还没理解掌握相对引用、绝对引用的表弟表妹,请参阅:


(0)

相关推荐