工作中經(jīng)常遇到需要用Excel篩選數(shù)據(jù)，最后的結(jié)果是數(shù)據(jù)量變少惊科。
今天遇到一個例子拍摇，是要用Excel把數(shù)據(jù)變多，具體要求是把下圖這樣的數(shù)據(jù)
擴充為
具體要求為：把編碼和店名都添加原始數(shù)據(jù)的序號堪唐，然后再標(biāo)記1巡语、2翎蹈、3，中間用-連接男公，原始地址保持不變荤堪。
數(shù)據(jù)量不確定，少的有幾條枢赔，多的有幾百條上千條澄阳，需要經(jīng)常做。
同事的做法
在我知道這項工作之前踏拜，同事是這樣做的：分別把每一條信息復(fù)制粘貼2次碎赢，然后分別為編碼和店名添加序號再標(biāo)記為1、2速梗、3肮塞。
我沒有敢問ta做這項工作究竟要花多少時間，因為ta提起這項工作時是一把鼻涕一把淚姻锁。
Excel公式和輔助列法
1枕赵、首先添加兩個輔助列，分別為“編碼2”和“店名2”位隶。
2拷窜、然后用公式在這兩個新列里分別為編碼和店名添加序號。
公式分別為（注意我把原始數(shù)據(jù)弄成了table形式赋荆，這樣寫公式就很方便）：
=[@編碼]&ROW()-1
=[@店名]&ROW()-1
為什么要用ROW()-1呢，這是通過觀察得來的：由于標(biāo)題行占了單獨的一行恋谭，因此每條記錄的序號比當(dāng)前行的行號少1糠睡。
3、把剛才新增的兩列數(shù)據(jù)復(fù)制后疚颊，粘貼為值狈孔，把編碼和店名兩列的原始數(shù)據(jù)覆蓋掉，并按編碼排序：
4、然后再添加兩個新列，然后獲取對應(yīng)的編碼和店名的1帝美、2自娩、3標(biāo)記。這里我用行號減去1后和3取模深寥，再用if判斷，如果余數(shù)為0則將其變?yōu)?贤牛。
公式為：
=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分鐘內(nèi)完成所有操作沐鼠。
但缺點是每次新數(shù)據(jù)來了之后，要再重復(fù)一遍整個過程叹谁，對于懶人來說饲梭，這是不可接受的。懶人的人生哲學(xué)是——辛苦一次本慕，終身享受排拷。
PowerQuery一勞永逸法
所以，要想辛苦一次锅尘，終身享受监氢，還是要祭出PowerQuery大法來布蔗。這樣模型建好之后，每次新數(shù)據(jù)來了浪腐，我只需要刷新即可纵揍。
不多說，直接上代碼：
let
源 = Excel.CurrentWorkbook(){[Name="表1"]}[Content],//從Excel引入數(shù)據(jù)源
更改的類型 = Table.TransformColumnTypes(源,{{"編碼", type text}, {"店名", type text}, {"地址", type text}}),//PowerQuery自動添加的步驟议街，可以省略
已添加索引 = Table.AddIndexColumn(更改的類型, "索引", 1, 1),//索引的目的是獲取原始數(shù)據(jù)的行號泽谨，以便于將其插入到編碼和店名兩列
自定義1 = Table.Combine({已添加索引,已添加索引,已添加索引}),//這一步相當(dāng)于復(fù)制粘貼兩次原始數(shù)據(jù)
排序的行 = Table.Sort(自定義1,{{"索引", Order.Ascending}}),//我們還是按原始數(shù)據(jù)的序號順序排列。
已添加索引1 = Table.AddIndexColumn(排序的行, "索引.1", 1, 1),//我另增加了一個索引列特漩，用于生成后面需要標(biāo)記的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[索引])}))),//這一步和后面的一步是為編碼和店名添加原始數(shù)據(jù)中的序號，我分開寫的蛤售，其實可以合并
自定義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處理的難點是擴充了原始數(shù)據(jù)后悴能，怎么按規(guī)律生成1揣钦、2、3的序號漠酿。由于早就把最簡單的數(shù)學(xué)還給了體育老師冯凹，我在這里還是折騰了好半天，最后采取了一個不怎么優(yōu)雅的方式（對3取模后再用if來判斷记靡，把余數(shù)為0更改為3）谈竿。注意代碼中团驱，//表示注釋摸吠。
PowerQuery怎么刷新？
其實我上面貼的代碼嚎花，在新的數(shù)據(jù)產(chǎn)生后寸痢，不能直接刷新——因為還得把新數(shù)據(jù)粘貼到數(shù)據(jù)模型的sheet1中。
更偷懶的辦法是把原始數(shù)據(jù)的Excel文件單獨放在一個文件夾中紊选，在該文件夾之外新建一個Excel版的PowerQuery數(shù)據(jù)模型啼止，數(shù)據(jù)源就引用該文件夾，這樣兵罢，當(dāng)心數(shù)據(jù)產(chǎn)生后献烦，把老的原始文件刪除，把新的數(shù)據(jù)丟到這個文件夾卖词，再打開數(shù)據(jù)模型刷新即可巩那。
這樣操作后，以后要做的就只有三步了：
1.刪除老的原始數(shù)據(jù)文件；
2.把新的原始數(shù)據(jù)文件丟到該文件夾下面即横；
3.打開數(shù)據(jù)模型所在的Excel文件噪生，刷新，完成东囚。
結(jié)語
如果一項工作總是需要重復(fù)做反復(fù)做跺嗽，我就會想法子偷懶，找有沒有更簡單的辦法页藻。最經(jīng)常的情況是桨嫁，每次做這項工作本身只需要1個小時，結(jié)果我花了幾個小時乃至幾十個小時才找到簡便的方法份帐。
表面上看得不償失瞧甩。但由于我找到的方法是一種模式和套路，因此弥鹦，以后凡是要再做這項工作時肚逸，我花費的時間不再是一個小時，而是兩分鐘彬坏。準(zhǔn)確來說朦促，是從此以后，任何人再做同樣的工作栓始，只需要兩分鐘务冕。這無論是對個人還是對企業(yè)來說，都很有價值幻赚。
