Contents
He nui nā mea hana a Microsoft Excel no ka hōʻiliʻili ʻana i ka ʻikepili mai kekahi mau papa (mai nā ʻāpana like ʻole a i ʻole nā faila like ʻole): nā loulou pololei, hana. AOLE (KANAKA), Power Query a me Power Pivot add-ons, etc. Mai kēia ʻaoʻao o ka pā, maikaʻi nā mea a pau.
Akā inā e holo ʻoe i kahi pilikia inverse - hoʻolaha i ka ʻikepili mai kahi papaʻaina i nā ʻāpana like ʻole - a laila e kaumaha loa nā mea āpau. I kēia manawa, ʻaʻohe mea hana civili i kūkulu ʻia no ka hoʻokaʻawale ʻana i ka ʻikepili i ka arsenal o Excel, akā naʻe. No laila, pono ʻoe e hoʻohana i kahi macro ma Visual Basic, a i ʻole e hoʻohana i ka macro recorder + Power Query hui pū me kahi "hoʻoponopono faila" liʻiliʻi ma hope.
E nānā pono kākou pehea e hoʻokō ai i kēia.
Hoʻokumu i ka pilikia
Loaʻa iā mākou ka ʻikepili mua e like me ka papa me ka nui o nā lālani ʻoi aku ma mua o 5000 no ke kūʻai aku:
Hana: e puunaue i ka ikepili mai keia papa ma ke kulanakauhale ma na pepa okoa o keia puke. ʻO kēlā mau. i ka hopena, pono ʻoe e kiʻi i kēlā me kēia pepa i kēlā mau lālani mai ka papaʻaina kahi i kūʻai ʻia ai ma ke kūlanakauhale pili:
E hoʻomākaukau
I mea e paʻakikī ʻole ai ke code macro a maʻalahi ka hoʻomaopopo ʻana e like me ka hiki, e hana mākou i ʻelua mau hana hoʻomākaukau.
ʻO ka mua, ka e hana i papa ʻimi ʻokoʻa, kahi e helu ai kahi kolamu i nā kūlanakauhale a pau āu e makemake ai e hana i nā pepa ʻokoʻa. ʻOiaʻiʻo, ʻaʻole i loaʻa i kēia papa kuhikuhi nā kūlanakauhale āpau i loaʻa i ka ʻikepili kumu, akā ʻo nā mea wale nō e pono ai mākou i nā hōʻike. ʻO ke ala maʻalahi loa e hana i kahi papaʻaina e hoʻohana i ke kauoha ʻIkepili - Wehe i nā Duplicate (ʻIkepili - Wehe i nā kope) no ke kope kolamu manaʻo a i ʻole hana UNIK (KUHIKI) - inā loaʻa iā ʻoe ka mana hou o Excel 365.
No ka mea, ua hana ʻia nā pepa hou ma Excel ma mua o (ma ka hema o) ka mea o kēia manawa (mua), he mea kūpono hoʻi e hoʻokaʻawale i nā kūlanakauhale ma kēia papa kuhikuhi ma ka ʻaoʻao iho (mai Z a A) - a laila ma hope o ka hana ʻana, ke kūlanakauhale. E hoʻonohonoho ʻia nā pepa ma ke ʻano pīʻāpā.
ʻO ka lua, ʻo ka пe hoohuli i na papa elua i dynamic ("akamai") i mea e maʻalahi ai ka hana pū me lākou. Hoʻohana mākou i ke kauoha Home - Hōʻano ma ke ʻano he papaʻaina (Home — Hōpili e like me ka Papa) a i ʻole ka pōkole papa keyboard Ctrl+T. Ma ka ʻaoʻao i hōʻike ʻia Kukahula (Hoʻolālā) e kāhea kākou iā lākou tablProdaji и Kakauhale Papakaukau, pakahi:
Ke Kaʻina Hana 1. Macro no ka puʻunaue ʻana i nā lau
Ma ka ʻaoʻao kiʻekiʻe mea haku polokalamu (Ka mea hoʻomohala) kaomi ma ke pihi Ke kākoʻo XIX. a i ʻole e hoʻohana i ka pōkole keyboard Alt+F11. Ma ka puka aniani hoʻoponopono macro e wehe ana, e hoʻokomo i kahi module kaʻawale hou ma o ka papa kuhikuhi Hoʻokomo - Module a kope i kēia code ma laila:
Sub Splitter() No kēlā me kēia cell i loko o ka Range("таблГорода") Range("таблПродажи"). Māhele Filter Auto:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(xlCellTypeVisible). Palapala Eia me kahi loop No kēlā me kēia… Aʻe ua hoʻokō i ka hele ʻana ma nā cell o ka papa kuhikuhi Kakauhale Papakaukau, kahi no kēlā me kēia kūlanakauhale i kānana ʻia (metod Kānana ʻAuto) i ka papa kūʻai mua a laila kope i nā hopena i ka pepa i hana hou ʻia. Ma ke ala, ua kapa hou ʻia ka pepa i hana ʻia i ka inoa hoʻokahi o ke kūlanakauhale a hoʻopili ʻia i ka laulā o nā kolamu no ka nani.
Hiki iā ʻoe ke holo i ka macro i hana ʻia ma Excel ma ka pā mea haku polokalamu pihi macros (Ka mea hoʻomohala — Macros) a i ʻole ka pōkole papa keyboard Alt+F8.
Hana 2. Hana i nā nīnau he nui ma Power Query
ʻO ke kaʻina hana mua, no kona compactness a me ka maʻalahi, he drawback koʻikoʻi - ʻaʻole i hōʻano hou ʻia nā pepa i hana ʻia e ka macro ke hoʻololi ʻia i ka papa kūʻai mua. Inā pono ka hoʻonui ʻana ma ka lele, a laila pono ʻoe e hoʻohana i ka puʻupuʻu VBA + Power Query, a i ʻole, e hana me ka hoʻohana ʻana i ka macro ʻaʻole wale nā pepa me ka ʻikepili static, akā i nā nīnau nīnau Power Query.
ʻO ka macro i kēia hihia he ʻāpana like me ka mea ma mua (he pōʻai pū kekahi No kēlā me kēia… Aʻe e hoʻololi hou i nā kūlanakauhale ma ka papa kuhikuhi), akā i loko o ka loop ʻaʻole e kānana hou ʻia a kope ʻia, akā e hana ana i kahi nīnau Query Power a hoʻouka i kāna mau hopena i kahi pepa hou:
Sub Splitter2() No kēlā me kēia pūnaewele ma ka Range("City table") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source , {{""Category"", type text}, {""Name"", type text}, {""City"", type text}, {""Manager"", type text}, {""Deal lā "", type datetime}, {""Cost"", type number}})," & Chr(13) & "" & Chr(10) & " #""Lalani me ka kānana i hoʻohana ʻia"" = Table.Se " & _ "lectRows(#""ʻano hoʻololi"", kēlā me kēia ([City] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Lalani me ka kānana i hoʻohana ʻia""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Mea hoʻolako = Microsoft.Mashup.OleDb.1; Punaʻikepili = $Workbook$; Wahi = " & cell.Value & "; Nā Waiwai Hoʻonui = """"" _ , Destination: = Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *MAI [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = ʻOiaʻiʻo SaveData = ʻOiaʻiʻo Ma hope o kona hoʻokuʻu ʻia ʻana, e ʻike mākou i nā pepa like ʻole ma ke kūlanakauhale, akā ua hana ʻia nā nīnau nīnau Power Query e hoʻokumu iā lākou:
Me nā hoʻololi ʻana i ka ʻikepili kumu, lawa ia e hoʻonui i ka pākaukau e pili ana me ka pihi ʻiole ʻākau - ke kauoha Hoʻohou a mālama (Hoʻohou) a i ʻole hōʻano hou i nā kūlanakauhale a pau i ka manawa hoʻokahi me ka hoʻohana ʻana i ke pihi Hoʻohou hou uku pila 'Ikepili (ʻIkepili — Hoʻohou i nā mea a pau).
- He aha nā macros, pehea e hana ai a hoʻohana iā lākou
- Ke mālama nei i nā pepa hana ma ke ʻano he waihona ʻokoʻa
- ʻOhi ʻikepili mai nā pepa a pau o ka puke i hoʻokahi papa