Tafole ea Pivot ho mefuta e mengata ea data

Tlhahiso ea bothata

Litafole tsa Pivot ke e 'ngoe ea lisebelisoa tse makatsang ka ho fetisisa ho Excel. Empa ho tla fihlela joale, ka bomalimabe, ha ho le e 'ngoe ea liphetolelo tsa Excel e ka etsang ntho e bonolo le e hlokahalang joalo ka ho haha ​​​​kakaretso ea mefuta e mengata ea data ea pele e fumanehang, mohlala, maqepheng a fapaneng kapa litafoleng tse fapaneng:

Pele re qala, a re hlakiseng lintlha tse 'maloa. Taba ea pele, ke lumela hore maemo a latelang a fihlelleha ho data ea rona:

• Litafole li ka ba le mela efe kapa efe e nang le data efe kapa efe, empa li tlameha ho ba le hlooho e tšoanang.
• Ha hoa lokela ho ba le data e eketsehileng maqepheng a nang le litafole tsa mohloli. Letlapa le le leng - tafole e le 'ngoe. Ho laola, ke u eletsa hore u sebelise sekhechana sa keyboard Ctrl+QETA, e u isang seleng ea ho qetela e sebelisitsoeng leqepheng la mosebetsi. Ka tsela e loketseng, ena e lokela ho ba sele ea ho qetela tafoleng ea data. Haeba ha o tobetsa Ctrl+QETA sele efe kapa efe e se nang letho ka ho le letona kapa ka tlase ho tafole e totobalitsoe - hlakola litšiea tsena tse se nang letho ka ho le letona kapa mela e ka tlaase ho tafole ka mor'a tafole 'me u boloke faele.

Mokhoa oa 1: Haha litafole bakeng sa pivot u sebelisa Power Query

Ho qala ka mofuta oa 2010 bakeng sa Excel, ho na le kenyelletso ea mahala ea Power Query e ka bokellang le ho fetola data efe kapa efe ebe e fana ka eona e le mohloli oa ho aha tafole ea pivot. Ho rarolla bothata ba rona ka thuso ea tlatsetso ena ha ho thata ho hang.

Taba ea pele, ha re theheng faele e ncha e se nang letho ho Excel - kopano e tla etsahala ho eona ebe ho tla etsoa tafole ea pivot ho eona.

Ebe ho tab Lintlha (haeba u na le Excel 2016 kapa hamorao) kapa ho tab Potso ea Matla (haeba u na le Excel 2010-2013) khetha taelo Etsa Potso - Ho tsoa ho Faele - Excel (Fumana Lintlha - Ho tsoa faeleng - Excel) 'me u hlakise file ea mohloli ka litafole tse lokelang ho bokelloa:

Fesetereng e hlahang, khetha lakane efe kapa efe (ho sa tsotellehe hore na ke efe) ebe o tobetsa konopo e ka tlase Change (Edita):

Fesetere ea Power Query Query Editor e lokela ho buleha ka holim'a Excel. Ka lehlakoreng le letona la fensetere holim'a phanele Kopa li-Parameters hlakola mehato eohle e entsoeng ka bo eona ntle le ea pele - mohloli o moholo (Mohloli):

Hona joale re bona lenane le akaretsang la maqephe ohle. Haeba ntle le maqephe a data ho na le maqephe a mang a lehlakoreng faeleng, joale mohatong ona mosebetsi oa rona ke ho khetha feela maqephe ao tlhahisoleseling e lokelang ho kenngoa ho ona, ntle le a mang kaofela a sebelisa filthara hloohong ea tafole:

Hlakola likholomo tsohle ntle le kholomo Lintlhaka ho tobetsa ka ho le letona kholomong ea sehlooho le ho khetha Hlakola likholomo tse ling (Tlosa litšiea tse ling):

Joale o ka holisa litaba tsa litafole tse bokelletsoeng ka ho tobetsa motsu o habeli o kaholimo ho kholomo (checkbox). Sebelisa lebitso la pele la kholomo joalo ka sehlongwapele o ka e tima):

Haeba u entse ntho e 'ngoe le e' ngoe ka nepo, joale u lokela ho bona litaba tsa litafole tsohle tse bokelitsoeng ka tlase ho tse ling:

E sala ho phahamisa mola oa pele ho hlooho ea tafole ka konopo Sebelisa mola oa pele joalo ka lihlooho (Sebelisa mola oa pele joalo ka lihlooho) tab ya Home (Lehae) 'me u tlose lihlooho tsa litafole tse kopitsoang ho data u sebelisa filthara:

Boloka tsohle tse entsoeng ka taelo Koala 'me u laole - Koala 'me u kene... (Koala & Laela — Koala & Laela ho…) tab ya Home (Lehae), 'me fensetereng e butsoeng, khetha khetho Khokahano feela (Khokahano Feela):

Tsohle. E sala feela ho haha ​​kakaretso. Ho etsa sena, e ea ho tab Kenya – PivotTable (Kenya - Pivot Table), khetha khetho Sebelisa mohloli oa data oa kantle (Sebelisa mohloli oa data oa kantle)ebe ka ho tobetsa konopo Khetha khokahano, kopo ea rōna. Pōpo e tsoelang pele le tlhophiso ea pivot e etsahala ka tsela e tloaelehileng ka ho hula masimo ao re a hlokang sebakeng sa mela, likholomo le boleng:

Haeba mohloli oa data o fetoha nakong e tlang kapa ho eketsoa maqephe a seng makae a lebenkele, ho tla lekana ho ntlafatsa potso le kakaretso ea rona ka ho sebelisa taelo. Hlatsoa kaofela tab ya Lintlha (Boitsebiso - Hlahisa Tsohle).

Mokhoa oa 2. Re kopanya litafole le taelo ea UNION SQL ka macro

Tharollo e 'ngoe bothateng ba rona e emeloa ke macro ena, e etsang sete ea data (cache) bakeng sa tafole ea pivot e sebelisa taelo. UNIT Puo ea potso ea SQL. Taelo ena e kopanya litafole ho tsoa ho tsohle tse boletsoeng lethathamong Mabitso a maqephe lipampiri tsa buka ho ea tafoleng e le 'ngoe ea data. Ka mantsoe a mang, ho e-na le ho kopitsa le ho manamisa marang-rang ho tloha maqepheng a fapaneng ho ea ho a le mong, re etsa se tšoanang ho RAM ea k'homphieutha. Ebe macro e eketsa lakane e ncha e nang le lebitso le fanoeng (e feto-fetohang ResultSheetName) mme e etsa kakaretso e feletseng (!) ho eona e thehiloeng ho cache e bokelitsoeng.

Ho sebelisa macro, sebelisa konopo ea Visual Basic ho tab Moqapi (Moetsi) kapa khaoletso ea keyboard Alt+F11. Ebe re kenya module e ncha e se nang letho ka har'a menu Kenya – Mojule ebe u kopitsa khoutu e latelang moo:

Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() As String Dim objPivotCache As PivotCache Dim objRS As Object Dim ResultSheetName As String Dim SheetsNames As Variant 'lebitso la leqephe moo pivot e hlahisoang e tla hlahisoa = Resultat sheet "aPirrayme" = Sephetho sa Leqephe la "APirrayme" mabitso a nang le litafole tsa mohloli SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 're theha cache bakeng sa litafole ho tloha maqepheng a SheetsNames With ActiveWorkbook ReDim arSQL(1 Ho ea (UBound(SheetsNames) + 1) ) Bakeng sa i = LBound (SheetsNames) Ho UBound(SheetsNames) arSQL(i + 1) = "KHETHA * HO TLOHA [" & SheetsNames(i) & "$]" E latelang ke Set objRS = CreateObject("ADODB.Recordset") objRS .Open Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) Qetella ka 'etsa hape leqephe ho hlahisa tafole ea pivot e hlahisitsoeng Ho Phoso Tsoela Pele ho Kopo e Latelang.DisplayAlerts = Lipampiri tsa Bohata (ResultSheetName).Delete Set wsPivot = Worksheets.Eketsa wsPivo t. Lebitso = ResultSheetName 'bontša kakaretso ea cache e hlahisitsoeng leqepheng lena Seta objPivotCache = ActiveWorkbook.PivotCaches.Eketsa(xlExternal) Seta objPivotCache.Recordset = objRS Set objRS = Ha ho letho le wsPivot objPivotCache" =RableT Tabl. objPivotCache = Nothing Range("A3").Kgetha Qetello ka Karolo ya ho Qetela    

Macro e phethiloeng e ka tsamaisoa ka sekhechana sa keyboard Alt+F8 kapa konopo ea Macros ho tab Moqapi (Moetsi - Macros).

Mathata a mokhoa ona:

• Lintlha ha li ntlafatsoe hobane cache ha e na khokahano ho litafole tsa mohloli. Haeba o fetola data ea mohloli, o tlameha ho tsamaisa macro hape mme o hahe kakaretso hape.
• Ha u fetola palo ea lipampiri, hoa hlokahala ho hlophisa khoutu ea macro (array Mabitso a maqephe).
  

Empa qetellong re fumana tafole ea pivot e felletseng e felletseng, e hahiloeng holim'a mekhahlelo e mengata ho tloha maqepheng a fapaneng:

Voilà!

Tekanyetso ea tekheniki: haeba u fumana phoso e kang "Mofani ha a ngolisoe" ha o sebelisa macro, joale ho ka etsahala hore ebe u na le phetolelo ea 64-bit ea Excel kapa phetolelo e sa fellang ea Office e kentsoe (ha ho Access). Ho lokisa boemo, tlosa sekhechana ho khoutu e kholo:

	 Mofani=Microsoft.Jet.OLEDB.4.0;  

ho:

	Mofani=Microsoft.ACE.OLEDB.12.0;  

'Me u khoasolle le ho kenya enjine ea mahala ea ts'ebetso ea data ho tsoa ho Access ho webosaete ea Microsoft - Microsoft Access Database Engine 2010 Redistributable

Mokhoa oa 3: Kopanya Wizate ea PivotTable ho tsoa Liphetolelong tsa Khale tsa Excel

Mokhoa ona o siiloe ke nako hanyane, empa o ntse o lokela ho boleloa. Ha re bua ka mokhoa o hlophisitsoeng, liphetolelong tsohle ho fihlela le ho kenyeletsa 2003, ho ne ho e-na le khetho ho PivotTable Wizard ho "aha pivot bakeng sa mekhahlelo e mengata ea ho kopanya". Leha ho le joalo, tlaleho e entsoeng ka tsela ena, ka bomalimabe, e tla ba feela setšoantšo se soabisang sa kakaretso e feletseng 'me ha e tšehetse "li-chips" tse ngata tsa litafole tse tloaelehileng tsa pivot:

Ka pivot e joalo, ha ho na lihlooho tsa lihlopha lethathamong la tšimo, ha ho na mokhoa o feto-fetohang oa sebopeho, sete sa mesebetsi e sebelisoang e lekanyelitsoe, 'me, ka kakaretso, sena sohle ha se tšoane haholo le tafole ea pivot. Mohlomong ke ka lebaka leo, ho qala ka 2007, Microsoft e tlositseng ts'ebetso ena moqoqong o tloaelehileng ha o theha litlaleho tsa tafole ea pivot. Hona joale tšobotsi ena e fumaneha feela ka konopo ea tloaelo Wizard ea PivotTable(Pivot Table Wizard), eo, ha e ratoa, e ka ekeletsoa ho Thulupaa ea Phihlello e Potlakileng ka Faele - Likhetho - Iketsetse Toolbar ea ho fihlella kapele - Litaelo tsohle (Faele - Likhetho - Iketsetse Toolbar ea ho fihlella kapele - Litaelo tsohle):

Kamora ho tobetsa konopo e kenyellelitsoeng, o hloka ho khetha khetho e nepahetseng mohatong oa pele oa wizard:

Ebe fensetereng e latelang, khetha mofuta o mong le o mong ka ho latellana ebe o o kenyelletsa lethathamong le akaretsang:

Empa, hape, ena ha se kakaretso e felletseng, kahoo u se ke oa lebella ho hongata ho tsoa ho eona. Nka khothaletsa khetho ena feela maemong a bonolo haholo.

• Ho theha Litlaleho ka PivotTables
• Etsa lipalo ho PivotTables
• Li-macros ke eng, mokhoa oa ho li sebelisa, moo u ka kopitsa khoutu ea VBA, joalo-joalo.
• Pokello ea lintlha ho tloha maqepheng a mangata ho isa ho a le mong (tlatsetso ea PLEX)