Pivot tebulo pamitundu ingapo ya data

Kupanga vuto

Ma tebulo a pivot ndi chimodzi mwazinthu zodabwitsa kwambiri mu Excel. Koma mpaka pano, mwatsoka, palibe mtundu uliwonse wa Excel womwe ungachite chinthu chosavuta komanso chofunikira pa ntchentche monga kupanga chidule chamagulu angapo oyambira omwe ali, mwachitsanzo, pamapepala osiyanasiyana kapena pamagome osiyanasiyana:

Tisanayambe, tiyeni tifotokoze mfundo zingapo. A priori, ndikukhulupirira kuti zotsatirazi zikukwaniritsidwa mu data yathu:

  • Matebulo amatha kukhala ndi mizere ingapo yokhala ndi deta iliyonse, koma ayenera kukhala ndi mutu womwewo.
  • Sipayenera kukhala zina zowonjezera pamapepala okhala ndi magwero a tebulo. Tsamba limodzi - tebulo limodzi. Kuti muwongolere, ndikukulangizani kuti mugwiritse ntchito njira yachidule ya kiyibodi Ctrl+TSIRIZA, zomwe zimakufikitsani ku selo lomaliza lomwe lagwiritsidwa ntchito patsamba. Moyenera, iyi iyenera kukhala selo lomaliza mu tebulo la deta. Ngati inu alemba pa Ctrl+TSIRIZA selo iliyonse yopanda kanthu kumanja kapena pansi pa tebulo ikuwonetsedwa - chotsani zipilala zopanda kanthu izi kumanja kapena mizere pansi pa tebulo pambuyo pa tebulo ndikusunga fayilo.

Njira 1: Pangani matebulo a pivot pogwiritsa ntchito Power Query

Kuyambira mu mtundu wa 2010 wa Excel, pali chowonjezera cha Power Query chaulere chomwe chimatha kusonkhanitsa ndikusintha deta iliyonse ndikuipereka ngati gwero lopangira tebulo la pivot. Kuthetsa vuto lathu mothandizidwa ndi chowonjezera ichi sikovuta konse.

Choyamba, tiyeni tipange fayilo yatsopano yopanda kanthu mu Excel - msonkhano udzachitika mmenemo ndiyeno tebulo la pivot lidzapangidwa mmenemo.

Ndiye pa tabu Deta (ngati muli ndi Excel 2016 kapena mtsogolo) kapena pa tabu Kufunsa Mphamvu (ngati muli ndi Excel 2010-2013) sankhani lamulo Pangani Mafunso - Kuchokera ku Fayilo - Excel (Pezani Zambiri - Kuchokera ku fayilo - Excel) ndipo tchulani fayilo yomwe ili ndi matebulo oti asonkhanitsidwe:

Pivot tebulo pamitundu ingapo ya data

Pazenera lomwe likuwoneka, sankhani pepala lililonse (zilibe kanthu) ndikudina batani pansipa Change (Sinthani):

Pivot tebulo pamitundu ingapo ya data

Zenera la Power Query Query Editor liyenera kutsegulidwa pamwamba pa Excel. Kumanja kwa zenera pa gulu Pemphani Parameters Chotsani masitepe onse opangidwa okha kupatula oyamba - gwero (Chitsime):

Pivot tebulo pamitundu ingapo ya data

Tsopano tikuwona mndandanda wamasamba onse. Ngati kuwonjezera pa mapepala a data pali mapepala ena am'mbali mu fayilo, ndiye kuti pa sitepe iyi ntchito yathu ndikusankha mapepala okhawo omwe chidziwitso chiyenera kuikidwa, kupatula ena onse pogwiritsa ntchito fyuluta pamutu wa tebulo:

Pivot tebulo pamitundu ingapo ya data

Chotsani zigawo zonse kupatula gawo Detapodina kumanja mutu wa mutu ndi kusankha Chotsani zigawo zina (Chotsani ndime zina):

Pivot tebulo pamitundu ingapo ya data

Kenako mutha kukulitsa zomwe zili m'matebulo osonkhanitsidwa podina mivi iwiri yomwe ili pamwamba pa mzati (checkbox). Gwiritsani ntchito dzina lazagawo loyambirira ngati choyambirira mukhoza kuzimitsa):

Pivot tebulo pamitundu ingapo ya data

Ngati mwachita zonse molondola, ndiye kuti pakadali pano muyenera kuwona zomwe zili m'matebulo onse omwe asonkhanitsidwa pansi pa mzake:

Pivot tebulo pamitundu ingapo ya data

Zimatsalira kukweza mzere woyamba pamutu wa tebulo ndi batani Gwiritsani ntchito mzere woyamba ngati mitu (Gwiritsani ntchito mizere yoyamba ngati mitu) tsamba Kunyumba (Kunyumba) ndikuchotsa mitu yatebulo yobwereza pa data pogwiritsa ntchito fyuluta:

Pivot tebulo pamitundu ingapo ya data

Sungani zonse zomwe mwachita ndi lamulo Tsekani ndikutsegula - Tsekani ndikulowetsani… (Tsekani & Kwezani — Tsekani & Kwezani ku…) tsamba Kunyumba (Kunyumba), ndipo pa zenera limene likutsegulidwa, sankhani njirayo Kulumikizana kokha (Kulumikizana kokha):

Pivot tebulo pamitundu ingapo ya data

Chirichonse. Zimangokhala kupanga chidule. Kuti muchite izi, pitani ku tabu Ikani - PivotTable (Lowetsani - Pivot Table), sankhani njira Gwiritsani ntchito gwero la data lakunja (Gwiritsani ntchito gwero lazinthu zakunja)ndiyeno podina batani Sankhani kugwirizana, pempho lathu. Kupanga kwinanso ndikusintha kwa pivot kumachitika mwanjira yokhazikika pokokera magawo omwe timafunikira m'mizere, mizere ndi malo amtengo wapatali:

Pivot tebulo pamitundu ingapo ya data

Ngati magwero akusintha m'tsogolomu kapena mapepala ena owonjezera awonjezeredwa, ndiye kuti zidzakhala zokwanira kukonzanso funso ndi chidule chathu pogwiritsa ntchito lamulo. Tsitsani zonse tsamba Deta (Deta - Tsitsani Zonse).

Njira 2. Timagwirizanitsa matebulo ndi lamulo la UNION SQL mu macro

Njira ina yothetsera vuto lathu imayimiridwa ndi macro iyi, yomwe imapanga deta (cache) ya tebulo la pivot pogwiritsa ntchito lamulo. UNITY Chilankhulo chafunso cha SQL. Lamuloli limaphatikiza matebulo kuchokera kuzinthu zonse zomwe zafotokozedwa pamndandanda Mayina a Mapepala Mapepala a bukhulo kukhala tebulo limodzi la data. Ndiko kuti, m'malo mokopera ndi kumata masamba kuchokera pamasamba osiyanasiyana kupita ku amodzi, timachita chimodzimodzi mu RAM ya pakompyuta. Kenako macro amawonjezera pepala latsopano lokhala ndi dzina lopatsidwa (zosinthika ResultSheetName) ndikupanga chidule chathunthu (!) pa izo potengera posungira zomwe zasonkhanitsidwa.

Kuti mugwiritse ntchito macro, gwiritsani ntchito batani la Visual Basic pa tabu Woyambitsa (Wolemba Mapulogalamu) kapena njira yachidule ya kiyibodi alt+F11. Kenako timayika gawo latsopano lopanda kanthu kudzera pa menyu Ikani - Module ndi kukopera khodi ili:

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 'sheet name where the result spivot will be displayed the result "aPirray sheet" = Resultat Sheet mayina okhala ndi magwero amasamba SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'timapanga cache ya matebulo kuchokera pamasamba kuchokera ku SheetsNames With ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) Pakuti i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SANKHANI * KUCHOKERA KU [" & SheetsNames(i) & "$]" Kenako i 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 ) Malizitsani Ndi 'panganinso pepala kuti muwonetse zotsatira za tebulo la pivot Pa Vuto Yambitsaninso Ntchito Yotsatira.DisplayAlerts = Mapepala Abodza(ResultSheetName).Chotsani Set wsPivot = Mapepala Ogwirira Ntchito.Onjezani wsPivo t. Name = ResultSheetName 'onetsani chidule cha kache pa pepala ili Khazikitsani objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Khazikitsani objPivotCache.Recordset = objRS Set objRS = Palibe Ndi wsPivot objPivotCache" =Raete Tablet. objPivotCache = Nothing Range("A3").Sankhani End With End Sub

Macro yomalizidwa imatha kuyendetsedwa ndi njira yachidule ya kiyibodi alt+F8 kapena Macros batani pa tabu Woyambitsa (Wopanga - Macros).

Kuipa kwa njira iyi:

  • Zambiri sizisinthidwa chifukwa cache ilibe kulumikizana ndi matebulo oyambira. Ngati musintha zomwe zachokera, muyenera kuyendetsanso macro ndikumanganso chidule.
  • Mukasintha kuchuluka kwa mapepala, ndikofunikira kusintha ma macro code (arra Mayina a Mapepala).

Koma pamapeto pake timapeza tebulo la pivot lathunthu, lomangidwa pamizere ingapo kuchokera pamapepala osiyanasiyana:

Voilà!

Ndemanga zaukadaulo: ngati mupeza cholakwika ngati "Wopereka sanalembetsedwe" mukamayendetsa macro, ndiye kuti mwina muli ndi mtundu wa 64-bit wa Excel kapena mtundu wosakwanira wa Office wayikidwa (palibe Access). Kuti mukonze vutoli, sinthani chidutswacho mu code macro:

	 Wopereka=Microsoft.Jet.OLEDB.4.0;

kuti:

	Wopereka=Microsoft.ACE.OLEDB.12.0;

Ndipo tsitsani ndikuyika injini yaulere yosinthira deta kuchokera ku Access kuchokera patsamba la Microsoft - Microsoft Access Database Engine 2010 Redistributable

Njira 3: Consolidate PivotTable Wizard kuchokera ku Mabaibulo Akale a Excel

Njira imeneyi ndi yachikale pang'ono, komabe yoyenera kutchulidwa. Kunena mwachidule, m'matembenuzidwe onse mpaka 2003, panali njira mu PivotTable Wizard kuti "amange pivot yamagulu angapo ophatikiza". Komabe, lipoti lopangidwa motere, mwatsoka, lidzakhala chithunzithunzi chomvetsa chisoni cha chidule chathunthu ndipo sichigwirizana ndi "chips" ambiri a matebulo a pivot wamba:

Mu pivot yotereyi, palibe mitu yazagawo pamndandanda wagawo, palibe mawonekedwe osinthika, magawo omwe amagwiritsidwa ntchito ndi ochepa, ndipo, mwachidziwikire, zonsezi sizofanana kwambiri ndi tebulo la pivot. Mwina ndichifukwa chake, kuyambira mu 2007, Microsoft idachotsa ntchitoyi pagawo lokhazikika popanga malipoti a tebulo la pivot. Tsopano mbali iyi ikupezeka kudzera pa batani lokonda PivotTable Wizard(Pivot Table Wizard), yomwe, ngati ingafune, ikhoza kuwonjezeredwa ku Quick Access Toolbar kudzera Fayilo - Zosankha - Sinthani Mwamakonda Anu Chida Chofikira Mwamsanga - Malamulo Onse (Fayilo - Zosankha - Sinthani Mwamakonda Anu Chida Chofikira Mwamsanga - Malamulo Onse):

Pivot tebulo pamitundu ingapo ya data

Mukadina batani lowonjezera, muyenera kusankha njira yoyenera pagawo loyamba la wizard:

Pivot tebulo pamitundu ingapo ya data

Kenako pazenera lotsatira, sankhani mtundu uliwonse motsatana ndikuwonjezera pamndandanda wamba:

Pivot tebulo pamitundu ingapo ya data

Koma, kachiwiri, ichi sichidule chathunthu, choncho musayembekezere zambiri kuchokera kwa izo. Ndikhoza kulangiza njira imeneyi pokhapokha muzochitika zosavuta.

  • Kupanga Malipoti ndi PivotTables
  • Khazikitsani mawerengedwe mu PivotTables
  • Kodi macros ndi chiyani, momwe angagwiritsire ntchito, komwe mungakopere VBA code, etc.
  • Kusonkhanitsa deta kuchokera pamasamba angapo kupita kumodzi (zowonjezera za PLEX)

 

Siyani Mumakonda