Kugawanya meza katika karatasi

Microsoft Excel ina zana nyingi za kukusanya data kutoka kwa meza kadhaa (kutoka kwa karatasi tofauti au kutoka kwa faili tofauti): viungo vya moja kwa moja, kazi. INDIRECT (KIASILI), Hoja ya Nguvu na Viongezi vya Pivot ya Nguvu, nk. Kutoka upande huu wa kizuizi, kila kitu kinaonekana vizuri.

Lakini ikiwa unakabiliwa na tatizo la kinyume - kueneza data kutoka kwa meza moja hadi karatasi tofauti - basi kila kitu kitakuwa cha kusikitisha zaidi. Kwa sasa, hakuna zana za kistaarabu zilizojengwa za kutenganisha data kama hiyo kwenye safu ya uokoaji ya Excel, kwa bahati mbaya. Kwa hivyo itabidi utumie macro katika Visual Basic, au utumie mchanganyiko wa kinasa sauti + Power Query na "uboreshaji wa faili" kidogo baadaye.

Hebu tuangalie kwa karibu jinsi hii inaweza kutekelezwa.

Uundaji wa shida

Tunayo data ya awali ya jedwali kama hilo lenye ukubwa wa safu mlalo zaidi ya 5000 kwa mauzo:

Kazi: kusambaza data kutoka kwa jedwali hili kwa jiji kwenye laha tofauti za kitabu hiki. Wale. kwenye pato, unahitaji kupata kwenye kila karatasi safu zile tu kutoka kwa jedwali ambapo uuzaji ulikuwa katika jiji linalolingana:

Tayarisha

Ili sio kugumu nambari ya jumla na kuifanya iwe rahisi kuelewa iwezekanavyo, wacha tufanye hatua kadhaa za maandalizi.

Kwanza, tengeneza jedwali tofauti la kuangalia, ambapo safu wima moja itaorodhesha miji yote ambayo ungependa kuunda laha tofauti. Bila shaka, saraka hii inaweza isiwe na miji yote iliyopo kwenye data ya chanzo, lakini ile tu ambayo tunahitaji ripoti. Njia rahisi zaidi ya kuunda meza kama hiyo ni kutumia amri Data - Ondoa Nakala (Data - Ondoa nakala) kwa nakala ya safu Mji/Jiji au kazi UNIKI (KIPEKEE) - ikiwa una toleo la hivi karibuni la Excel 365.

Kwa kuwa laha mpya katika Excel zimeundwa kwa chaguo-msingi kabla (upande wa kushoto wa) ya sasa (iliyopita), ni busara pia kupanga miji kwenye saraka hii kwa mpangilio wa kushuka (kutoka Z hadi A) - kisha baada ya uundaji, jiji. karatasi zitapangwa kwa alfabeti.

Pili, пbadilisha majedwali yote mawili kuwa yenye nguvu (“smart”) ili kurahisisha kufanya kazi nao. Tunatumia amri Nyumbani - Fomati kama meza (Nyumbani - Umbizo kama Jedwali) au njia ya mkato ya kibodi Ctrl+T. Kwenye kichupo kinachoonekana kuujenga (Ubunifu) tuwaite tablProdaji и TableCity, mtawaliwa:

Njia ya 1. Macro kwa mgawanyiko kwa karatasi

Kwenye kichupo cha hali ya juu developer (Msanidi programu) bonyeza kitufe Visual Basic au tumia njia ya mkato ya kibodi Alt+F11. Katika dirisha la mhariri mkuu linalofungua, ingiza moduli mpya tupu kupitia menyu Ingiza - Moduli na unakili nambari ifuatayo hapo:

Mgawanyiko Ndogo() Kwa Kila kisanduku Katika Masafa("таблГорода") Masafa("таблПродажи"). Sehemu ya Kichujio Kiotomatiki:=3, Kigezo1:=Kiini. Kiwango cha Thamani("таблПродажи[#Zote]").Seli Maalum(xlCellTypeInayoonekana).Nakala). Laha.Ongeza Laha Amilifu.Bandika.Name = seli.Thamani ActiveSheet.UsedRange.Columns.AutoFit Laha za Kazi za seli Inayofuata("Данные").ShowAllData Mwisho Ndogo	  

Hapa na kitanzi Kwa Kila … Inayofuata kutekelezwa kifungu kupitia seli za saraka TableCity, ambapo kwa kila jiji huchujwa (njia Kichujio Kiotomatiki) katika jedwali halisi la mauzo na kisha kunakili matokeo kwenye laha mpya iliyoundwa. Njiani, karatasi iliyoundwa inaitwa jina moja la jiji na kuweka kiotomatiki upana wa nguzo kwa uzuri huwashwa juu yake.

Unaweza kuendesha macro iliyoundwa katika Excel kwenye kichupo developer kifungo Macros (Msanidi - Macros) au njia ya mkato ya kibodi Alt+F8.

Njia ya 2. Unda maswali mengi katika Hoja ya Nguvu

Njia ya awali, kwa ukamilifu wake wote na unyenyekevu, ina upungufu mkubwa - karatasi zilizoundwa na macro hazijasasishwa wakati mabadiliko yanafanywa katika meza ya mauzo ya awali. Ikiwa kusasisha kwenye kuruka ni muhimu, basi itabidi utumie kifungu cha VBA + Power Query, au tuseme, unda kwa kutumia macro sio tu laha zilizo na data tuli, lakini maswali yaliyosasishwa ya Hoja ya Nguvu.

Jumla katika kesi hii ni sawa na ile ya awali (pia ina mzunguko Kwa Kila … Inayofuata kurudia miji kwenye saraka), lakini ndani ya kitanzi hakutakuwa na kuchuja na kunakili tena, lakini kuunda hoja ya Power Query na kupakia matokeo yake kwenye laha mpya:

Sub Splitter2() Kwa Kila seli Katika Masafa("Jedwali la Jiji") ActiveWorkbook.Queries.Ongeza Jina:=seli.Thamani, Mfumo:= _ "ruhusu" & Chr(13) & "" & Chr(10) & " Chanzo = Excel.CurrentWorkbook(){[Jina=""TableSales""][Maudhui]," & Chr(13) & "" & Chr(10) & " #""Aina Iliyobadilishwa"" = Table.TransformColumnTypes(Chanzo , {{""Kitengo"", chapa maandishi}, {""Jina"", chapa maandishi}, {""Jiji"", chapa maandishi}, {""Meneja"", chapa maandishi}, {""Dili tarehe "", chapa tarehe}, {""Gharama"", aina ya nambari}})," & Chr(13) & "" & Chr(10) & " #""Safu mlalo zilizo na kichujio kimetumika" = Table.Se " & _ "lectRows(#""Aina iliyobadilishwa"", kila moja ([Mji] = """ & seli.Thamani & """))" & Chr(13) & "" & Chr(10) & "katika " & Chr(13) & "" & Chr(10) & " #""Safu mlalo zenye kichujio kimetumika""" ActiveWorkbook.Worksheets.Ongeza Na ActiveSheet.ListObjects.Ongeza(ChanzoAina:=0, Chanzo:= _ "OLEDB; Mtoa huduma =Microsoft.Mashup.OleDb.1;Chanzo cha Data=$Workbook$;Mahali=" & cell.Thamani & ";Sifa Zilizoongezwa=""""" _ , Lengwa:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("CHAGUA *KUTOKA [" & cell.Value & "]") .RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells =FalPasword. SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=Mwisho wa Uongo Kwa ActiveSheet.Name = seli.Thamani Kiini Inayofuata Mwisho Kidogo  

Baada ya kuzinduliwa, tutaona laha sawa kulingana na jiji, lakini hoja zilizoundwa tayari za Hoja ya Nguvu zitaunda:

Kwa mabadiliko yoyote katika data ya chanzo, itakuwa ya kutosha kusasisha meza inayofanana na kifungo cha kulia cha mouse - amri Sasisha na Uhifadhi (Onyesha upya) au sasisha miji yote mara moja kwa wingi kwa kutumia kitufe Sasisha Wote tab Data (Data - Onyesha upya Zote).

• Macros ni nini, jinsi ya kuunda na kuitumia
• Kuhifadhi laha za kitabu cha kazi kama faili tofauti
• Kukusanya data kutoka kwa laha zote za kitabu kwenye jedwali moja