Mataupu
Microsoft Excel e tele meafaigaluega mo le aoina mai o faʻamatalaga mai le tele o laulau (mai laupepa eseese pe mai faila eseese): fesoʻotaʻiga tuusaʻo, galuega LAVA (FAʻALELELE), Power Query ma Power Pivot add-ons, ma isi. Mai lea itu o le pa, e foliga lelei mea uma.
Ae afai e te feagai ma se faʻafitauli faʻalavelave - faʻasalalau faʻamatalaga mai le tasi laulau i laupepa eseese - o mea uma o le a sili atu ona faanoanoa. I le taimi nei, e leai ni mea faʻapitoa e fausia i totonu mo ia faʻamatalaga tuʻueseeseina i le arsenal o Excel, o le mea e leaga ai. O lea e tatau ai ona e faʻaogaina se macro ile Visual Basic, pe faʻaaoga le macro recorder + Power Query faʻatasi ma sina "faʻalelei faila" pe a uma.
Se'i tatou va'ava'ai toto'a pe fa'apefea ona fa'atino lea tulaga.
Fa'atulagaina o le fa'afitauli
E i ai a matou faʻamatalaga muamua o se laulau faʻatasi ma le tele e sili atu i le 5000 laina mo faʻatau:
Galuega: tufatufa fa'amaumauga mai lenei laulau i le 'a'ai i luga o laupepa eseese o lenei tusi. O na. i le gaosiga, e tatau ona e mauaina i luga o laupepa taʻitasi na o laina na mai le laulau lea na faʻatau ai i le taulaga tutusa:
Saunia
Ina ia le faʻalavelaveina le macro code ma faʻafaigofie ona malamalama i le mea e mafai ai, seʻi o tatou faia ni laasaga sauniuni.
Muamua, o le fai se laulau su'e ese, lea o le a lisi ai e se koluma se tasi aai uma e te manaʻo e fai ai ni laupepa eseese. Ioe, o lenei fa'atonuga atonu e le o iai uma 'a'ai o lo'o i totonu o fa'amaumauga fa'apogai, ae na'o mea tatou te mana'omia ai lipoti. O le auala pito sili ona faigofie e fai ai se laulau faapena o le faʻaaogaina lea o le poloaiga Fa'amatalaga – Aveese Fa'alua (Fa'amatalaga - Ave'ese fa'alua) mo kopi koluma lagona po'o galuega UNIK (TUSI) – pe afai e iai sau lomiga fou ole Excel 365.
Talu ai ona o laupepa fou i Excel na faia e ala i le faaletonu i luma (i le agavale o) le taimi nei (muamua), e talafeagai foi le faʻavasegaina o aai i totonu o lenei lisi i le faʻasologa i lalo (mai le Z i le A) - ona maeʻa ai lea o le fausiaina, le aai. o pepa o le a fa'atulaga fa'alafapeta.
Lona lua, o le пliliu laulau uma e lua i dynamic (“atamai”) e faafaigofie ai ona galulue faatasi ma i latou. Tatou te faaaogaina le poloaiga Aiga – Fa'atulagaina e pei o se laulau (Aiga — Fa'atulagaina e pei o le Laulau) po'o le ala pupuu keyboard ctrl+T. I luga o le laupepa o loʻo faʻaalia Fausia (Mamanu) tatou valaau ia i latou tablProdaji и Laua'aiA'ai, ma:
Metotia 1. Macro mo le vaevaeina i pepa
I luga o le Advanced tab atinae (Atiae) kiliki i le faamau Autu vaaia po'o le fa'aoga o le keyboard shortcut Alt+F11. I le fa'amalama fa'atonu macro lea e matala, fa'aofi se module gaogao fou i le lisi Fa'aofi - Module ma kopi le code lea iina:
Sub Splitter() Mo sela ta'itasi i le va'aiga("таблГорода") Va'aiga("таблПродажи") Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets("Данные").ShowAllData End Sub O iinei ma se matasele Mo Ta'itasi … Sosoo ai fa'atino le ala i totonu o sela o le lisi Laua'aiA'ai, lea e fa'amamāina ai 'a'ai ta'itasi (metotia Filifiliga Auto) i le laulau fa'atau muamua ona kopi lea o fa'ai'uga i le pepa fou na faia. I luga o le ala, o le laupepa na faia ua toe faʻaigoaina i le igoa lava e tasi o le taulaga ma faʻapipiʻi aunoa le lautele o koluma mo le matagofie o loʻo faʻaalia i luga.
E mafai ona e faʻatautaia le macro na faia i Excel i luga o le laupepa atinae faʻamau macros (Fa'atupu - Macros) po'o le ala pupuu keyboard Alt+F8.
Metotia 2. Fausia ni fesili se tele i le Power Query
O le auala muamua, mo lona faʻaogaina ma le faigofie, e iai se faʻafitauli taua - o laupepa na faia e le macro e leʻo faʻafouina pe a faia suiga i le laulau faʻatau muamua. Afai e manaʻomia le faʻafouina i luga o le lele, ona e faʻaaogaina lea o le VBA + Power Query bundle, pe sili atu, fatuina le faʻaogaina o se macro e le naʻo ni laupepa faʻatasi ai ma faʻamaumauga faʻamaumauga, ae faʻafouina fesili Power Query.
O le macro i lenei tulaga e foliga tutusa ma le muamua (e iai foi se taamilosaga Mo Ta'itasi … Sosoo ai e fa'asolosolo i luga o 'a'ai i totonu o le fa'atonuga), ae i totonu o le matasele o le a le toe i ai le fa'amama ma le kopiina, ae o le fatuina o se Query Query ma tu'uina atu ana taunu'uga i se pepa fou:
Sub Splitter2() Mo sela ta'itasi i le Va'aiga("City table") ActiveWorkbook.Queries.Add Igoa:=cell.Value, Fuafua:= _ "tuu" & Chr(13) & "" & Chr(10) & " Punavai = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""Suia Ituaiga"" = Table.TransformColumnTypes(Source , {{""Category"", type text}, {""Name"", type text}, {""City"", type text}, {""Manager"", type text}, {""Deal aso "", type datetime}, {""Cost"", type number}})," & Chr(13) & "" & Chr(10) & " #""Rows with filter applied"" = Table.Se " & _ "lectRows(#""Suia ituaiga"", taitasi ([Aai] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "i totonu " & Chr(13) & "" & Chr(10) & " #""Rows with filter applied""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Provider =Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _ , Destination:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("FILIFILI *MAI [" & cell.Value & "]") .RowNumbers = False. SaveData = Moni .AdjustColumnWidth = Moni .RefreshPeriod = 0 .PreserveColumnInfo = Moni .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=Sese End With ActiveSheet.Name = cell.Value Next cell End Sub A maeʻa lona faʻalauiloaina, o le a tatou vaʻai i pepa tutusa i le taulaga, ae ua uma ona faia fesili Power Query o le a fausia ai:
Faatasi ai ma soʻo se suiga i faʻamatalaga faʻamatalaga, o le a lava le faʻafouina o le laulau e fetaui ma le ki taumatau o le isumu - le poloaiga Fa'afou ma Fa'asaoina (Fa'afou) pe fa'afou uma 'a'ai i le taimi e tasi i le tele e fa'aaoga ai le ki Faʻafouina Mea uma tab Faʻamatalaga (Fa'amatalaga - Toe Fa'afouina Uma).
- O a macros, pe fa'apefea ona fai ma fa'aoga
- Faasaoina o pepa o galuega e fai ma faila eseese
- Aoina fa'amaumauga mai pepa uma o le tusi ile laulau e tasi