excel表格_excel函数公式大全_execl从入门到精通
都知道怎么用Excel过滤数据，怎么用Excel把数据按要求变多呢？2024-06-13 14:22:13
工作中经常遇到需要用Excel筛选数据，最后的结果是数据量变少。
今天遇到一个例子，是要用Excel把数据变多，具体要求是把下图这样的数据
扩充为具体要求为：把编码和店名都添加原始数据的序号，然后再标记1、2、3，中间用-连接，原始地址保持不变。
数据量不确定，少的有几条，多的有几百条上千条，需要经常做。
同事的做法
在我知道这项工作之前，同事是这样做的：分别把每一条信息复制粘贴2次，然后分别为编码和店名添加序号再标记为1、2、3。
我没有敢问ta做这项工作究竟要花多少时间，因为ta提起这项工作时是一把鼻涕一把泪。
Excel公式和辅助列法
1、首先添加两个辅助列，分别为“编码2”和“店名2”。
2、然后用公式在这两个新列里分别为编码和店名添加序号。
公式分别为（注意我把原始数据弄成了table形式，这样写公式就很方便）：
=[@编码]&ROW()-1=[@店名]&ROW()-1为什么要用ROW()-1呢，这是通过观察得来的：由于标题行占了单独的一行，因此每条记录的序号比当前行的行号少1。
3、把刚才新增的两列数据复制后，粘贴为值，把编码和店名两列的原始数据覆盖掉，并按编码排序：
4、然后再添加两个新列，然后获取对应的编码和店名的1、2、3标记。这里我用行号减去1后和3取模，再用if判断，如果余数为0则将其变为3。公式为：
=IF(MOD(ROW()-1,3)=0,[@编码]&"-3",[@编码]&"-"&MOD(ROW()-1,3))=IF(MOD(ROW()-1,3)=0,[@店名]&"-3",[@店名]&"-"&MOD(ROW()-1,3))
5、最后，剪切最后两列，粘贴为值，第二次覆盖编码和店名两列。完成。
用公式法，可以在5-10分钟内完成所有操作。
但缺点是每次新数据来了之后，要再重复一遍整个过程，对于懒人来说，这是不可接受的。懒人的人生哲学是——辛苦一次，终身享受。
PowerQuery一劳永逸法
所以，要想辛苦一次，终身享受，还是要祭出PowerQuery大法来。这样模型建好之后，每次新数据来了，我只需要刷新即可。
不多说，直接上代码：
let 源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],//从Excel引入数据源更改的类型 = Table.TransformColumnTypes(源,{{"编码", type text}, {"店名", type text}, {"地址", type text}}),//PowerQuery自动添加的步骤，可以省略已添加索引 = Table.AddIndexColumn(更改的类型, "索引", 1, 1),//索引的目的是获取原始数据的行号，以便于将其插入到编码和店名两列自定义1 = Table.Combine({已添加索引,已添加索引,已添加索引}),//这一步相当于复制粘贴两次原始数据排序的行 = Table.Sort(自定义1,{{"索引", Order.Ascending}}),//我们还是按原始数据的序号顺序排列。已添加索引1 = Table.AddIndexColumn(排序的行, "索引.1", 1, 1),//我另增加了一个索引列，用于生成后面需要标记的1、2、3已添加自定义 = Table.AddColumn(已添加索引1, "自定义", each if Number.Mod([索引.1],3)=0 then 3 else Number.Mod([索引.1],3)),//这一步就是生成了每一条原始记录的1、2、3的序号自定义2 = Table.FromRecords(Table.TransformRows(已添加自定义, (r) => Record.TransformFields(r, {"编码", each r[编码]&Text.From(r[索引])}))),//这一步和后面的一步是为编码和店名添加原始数据中的序号，我分开写的，其实可以合并自定义4 = Table.FromRecords(Table.TransformRows(自定义2, (r) => Record.TransformFields(r, {"店名", each r[店名]&Text.From(r[索引])}))),//同上删除的列 = Table.RemoveColumns(自定义4,{"索引", "索引.1"}),//删除不需要的列自定义3 = Table.FromRecords(Table.TransformRows(删除的列, (r) => Record.TransformFields(r, {"编码", each r[编码]&"-"&Text.From(r[自定义])}))),//这一步和下一步都是为编码和店名添加1、2、3的序号自定义5 = Table.FromRecords(Table.TransformRows(自定义3, (r) => Record.TransformFields(r, {"店名", each r[店名]&"-"&Text.From(r[自定义])}))),//同上删除的列1 = Table.RemoveColumns(自定义5,{"自定义"})//删除不需要的列in 删除的列1
用PowerQuery处理的难点是扩充了原始数据后，怎么按规律生成1、2、3的序号。由于早就把最简单的数学还给了体育老师，我在这里还是折腾了好半天，最后采取了一个不怎么优雅的方式（对3取模后再用if来判断，把余数为0更改为3）。注意代码中，//表示注释。
PowerQuery怎么刷新？
其实我上面贴的代码，在新的数据产生后，不能直接刷新——因为还得把新数据粘贴到数据模型的sheet1中。
更偷懒的办法是把原始数据的Excel文件单独放在一个文件夹中，在该文件夹之外新建一个Excel版的PowerQuery数据模型，数据源就引用该文件夹，这样，当心数据产生后，把老的原始文件删除，把新的数据丢到这个文件夹，再打开数据模型刷新即可。
这样操作后，以后要做的就只有三步了：
1.删除老的原始数据文件；
2.把新的原始数据文件丢到该文件夹下面；
3.打开数据模型所在的Excel文件，刷新，完成。
结语
如果一项工作总是需要重复做反复做，我就会想法子偷懒，找有没有更简单的办法。最经常的情况是，每次做这项工作本身只需要1个小时，结果我花了几个小时乃至几十个小时才找到简便的方法。
表面上看得不偿失。但由于我找到的方法是一种模式和套路，因此，以后凡是要再做这项工作时，我花费的时间不再是一个小时，而是两分钟。准确来说，是从此以后，任何人再做同样的工作，只需要两分钟。这无论是对个人还是对企业来说，都很有价值。
标签: excel怎么添加过滤