都知道怎么用Excel过滤数据(excel怎么过滤数据)

工作中经常遇到需要用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）。注意代码中，//表示注释。