Sisällys
Microsoft Excelissä on monia työkaluja tietojen keräämiseen useista taulukoista (eri taulukoista tai eri tiedostoista): suorat linkit, funktio EPÄSUORISTA (Epäsuora), Power Queryn ja Power Pivot -lisäosat jne. Barrikadin tältä puolelta katsottuna kaikki näyttää hyvältä.
Mutta jos kohtaat käänteisen ongelman – tietojen levittämisen yhdestä taulukosta eri taulukoille – kaikki on paljon surullisempaa. Tällä hetkellä Excelin arsenaalissa ei valitettavasti ole sivistynyttä sisäänrakennettua työkalua tällaiseen tietojen erottamiseen. Joten sinun täytyy käyttää makroa Visual Basicissa tai käyttää makrotallennin + Power Query -yhdistelmää, jonka jälkeen on hieman "tarkennettava" tiedostoa.
Katsotaanpa tarkemmin, kuinka tämä voidaan toteuttaa.
Ongelman muotoilu
Meillä on lähtötietona myyntiin tällainen yli 5000 rivin taulukko:
Tehtävä: jakaa tämän taulukon tiedot kaupungeittain tämän kirjan erillisille sivuille. Nuo. tulosteessa sinun on saatava jokaiselle arkille vain ne rivit taulukosta, joissa myynti tapahtui vastaavassa kaupungissa:
Valmistella
Jotta makrokoodia ei monimutkaista ja jotta se olisi mahdollisimman helppo ymmärtää, suoritetaan muutama valmisteleva vaihe.
Ensinnäkin luoda erillinen hakutaulukko, jossa yhdessä sarakkeessa luetellaan kaikki kaupungit, joille haluat luoda erilliset taulukot. Tämä hakemisto ei tietenkään välttämättä sisällä kaikkia lähdetiedoissa olevia kaupunkeja, vaan vain niitä, joista tarvitsemme raportteja. Helpoin tapa luoda tällainen taulukko on käyttää komentoa Tiedot – Poista kaksoiskappaleet (Data – poista kaksoiskappaleet) sarakkeen kopiota varten Kaupunki tai toiminto UNIK (AINUTLAATUINEN) – jos sinulla on Excel 365:n uusin versio.
Koska Excelissä uudet taulukot luodaan oletusarvoisesti ennen nykyistä (edellistä) (vasemmalle), on myös järkevää lajitella tämän hakemiston kaupungit laskevaan järjestykseen (Z:sta A:hen) – luomisen jälkeen kaupunki arkit järjestetään aakkosjärjestykseen.
Toiseksi пmuuntaa molemmat taulukot dynaamiseksi ("älykäs"), jotta heidän kanssaan on helpompi työskennellä. Käytämme komentoa Etusivu – Muotoile taulukoksi (Etusivu — Muotoile taulukkona) tai pikanäppäimellä Ctrl+T. Näyttöön tulevalla välilehdellä Rakentaja (Design) soitetaan heille tablProdaji и TableCityvastaavasti:
Menetelmä 1. Makro arkeilla jakamista varten
Lisäasetukset -välilehdellä kehittäjä (Kehittäjä) Napsauta painiketta Visual Basic tai käytä pikanäppäintä muut+F11. Lisää avautuvan makroeditori-ikkunan valikon kautta uusi tyhjä moduuli Insert – moduuli ja kopioi sinne seuraava koodi:
Alajakaja() jokaiselle alueen solulle("таблГорода") Range("таблПродажи"). Automaattinen suodatuskenttä:=3, Kriteeri1:=solu.Arvoalue("таблПродажи[#All]").SpecialCells(xlC). Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets("Данные").NäytäAllData End Sub Täällä silmukalla Jokaiselle… Seuraavaksi toteutti läpikulun hakemiston solujen läpi TableCity, jossa se suodatetaan jokaiselle kaupungille (menetelmä Automaattinen suodatin) alkuperäiseen myyntitaulukkoon ja kopioida sitten tulokset uuteen taulukkoon. Matkan varrella luotu arkki nimetään uudelleen kaupungin samaksi nimeksi ja siihen otetaan käyttöön sarakkeiden leveyden automaattinen sovitus kauneuden vuoksi.
Voit suorittaa luodun makron Excelissä välilehdellä kehittäjä nappia Makrot (Kehittäjä – makrot) tai pikanäppäimellä muut+F8.
Tapa 2. Luo useita kyselyitä Power Queryssä
Edellisellä menetelmällä, kaikessa kompaktisuudessaan ja yksinkertaisuudessaan, on merkittävä haittapuoli – makron luomia taulukoita ei päivitetä, kun alkuperäiseen myyntitaulukkoon tehdään muutoksia. Jos päivittäminen lennossa on tarpeen, sinun on käytettävä VBA + Power Query -pakettia tai pikemminkin luotava makrolla, ei vain staattisia tietoja sisältäviä taulukoita, vaan päivitettyjä Power Query -kyselyjä.
Makro on tässä tapauksessa osittain samanlainen kuin edellinen (sillä on myös sykli Jokaiselle… Seuraavaksi iteroidaksesi hakemiston kaupunkeja), mutta silmukan sisällä ei enää suodateta ja kopioida, vaan luodaan Power Query -kysely ja ladataan sen tulokset uudelle taulukolle:
Sub Splitter2() jokaiselle solulle alueella ("Kaupunkitaulukko") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Sisältö]," & Chr(13) & "" & Chr(10) & " #""Muutettu tyyppi"" = Taulukko.TransformColumnTypes(lähde) , {{""Category", type text}, {""Name", type text}, {""City"", type text}, {""Manager", type text}, {""Deal" date "", type datetime}, {""Cost"", type number}})," & Chr(13) & "" & Chr(10) & " #""Rivit, joissa suodatin on käytössä"" = Table.Se " & _ "lectRows(#""Muutettu tyyppi"", kukin ([Kaupunki] = """ & solu.Arvo & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""Rivit, joissa suodatin on käytössä""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; Palveluntarjoaja =Microsoft.Mashup.OleDb.1;Tietolähde=$Workbook$;Location=" & cell.Arvo & ";Extended Properties=""""" _ , Destination:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *FROM [" & cell.Value & "]") .RowNumbers = Epätosi .FillAdjacentFormulas = Epätosi .PreserveFormatting = Tosi .RefreshOnFileOpen = Väärin .BackgroundQuery = Tosi .RefreshStyle = xlInsertDeleteCells .SavePasword = FalsePasword. SaveData = True .AdjustColumnWidth = tosi .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End ActiveSheet.Name = solu.Arvo Seuraava solun loppu Sub Sen julkaisun jälkeen näemme samat taulukot kaupungeittain, mutta jo luodut Power Query -kyselyt muodostavat ne:
Lähdetietojen muutoksissa riittää päivittää vastaava taulukko hiiren oikealla painikkeella - komennolla Päivitä & Tallenna (Virkistää) tai päivitä kaikki kaupungit kerralla kerralla painikkeella Päivitä kaikki kieleke Päiväys (Data – Päivitä kaikki).
- Mitä makrot ovat, miten niitä luodaan ja käytetään
- Työkirjan arkkien tallentaminen erillisiksi tiedostoiksi
- Tietojen kerääminen kirjan kaikista lehdistä yhteen taulukkoon