Okuqukethwe
Ukwakhiwa kwenkinga
Amathebula e-Pivot angelinye lamathuluzi amangalisa kakhulu ku-Excel. Kodwa kuze kube manje, ngeshwa, azikho izinguqulo ze-Excel ezingenza into elula nedingekayo ngokuphazima kweso njengokwakha isifinyezo samabanga ambalwa wedatha okuqala atholakala, isibonelo, emashidini ahlukene noma kumathebula ahlukene:
Ngaphambi kokuthi siqale, ake sicacise amaphuzu ambalwa. Okokuqala, ngikholwa ukuthi izimo ezilandelayo ziyahlangatshezwa kudatha yethu:
- Amathebula angaba nanoma iyiphi inombolo yemigqa enanoma iyiphi idatha, kodwa kufanele abe nesihloko esifanayo.
- Akufanele kube khona idatha eyengeziwe emashidini anamathebula omthombo. Ishidi elilodwa - itafula elilodwa. Ukuze ulawule, ngikweluleka ukuthi usebenzise isinqamuleli sekhibhodi Ctrl+Ukuphela, okukuyisa kuseli yokugcina esetshenziswe ekhasini lokusebenza. Ngokufanelekile, leli kufanele kube iseli lokugcina kuthebula ledatha. Uma uchofoza Ctrl+Ukuphela noma iyiphi iseli engenalutho kwesokudla noma ngaphansi kwetafula igqanyisiwe - susa lawa makholomu angenalutho kwesokudla noma imigqa engezansi kwetafula ngemva kwetafula bese ulondoloza ifayela.
Indlela 1: Yakha amatafula e-pivot usebenzisa Umbuzo Wamandla
Kusukela kunguqulo ka-2010 ye-Excel, kukhona isengezo samahhala Sombuzo Wamandla esingaqoqa futhi siguqule noma iyiphi idatha bese siyinikeza njengomthombo wokwakha ithebula le-pivot. Ukuxazulula inkinga yethu ngosizo lwalesi sengezo akunzima neze.
Okokuqala, ake sakhe ifayela elisha elingenalutho ku-Excel - ukuhlanganiswa kuzokwenzeka kulo bese kwakhiwa ithebula le-pivot kulo.
Bese kuthebhu Idatha (uma une-Excel 2016 noma kamuva) noma kuthebhu Umbuzo Wamandla (uma une-Excel 2010-2013) khetha umyalo Dala Umbuzo - Kusuka kufayela - Excel (Thola Idatha - Kusuka kufayela - Excel) futhi ucacise ifayela elingumthombo elinamathebula azoqoqwa:
Efasiteleni elivelayo, khetha noma yiliphi ishidi (akunandaba ukuthi yiliphi) bese ucindezela inkinobho engezansi Guqula (Hlela):
Iwindi le-Power Query Editor kufanele livuleke phezu kwe-Excel. Ngakwesokudla kwefasitela kuphaneli Cela Amapharamitha susa zonke izinyathelo ezidalwe ngokuzenzakalela ngaphandle kwesokuqala - Umthombo (Umthombo):
Manje sibona uhlu olujwayelekile lwawo wonke amashidi. Uma ngaphezu kwamaphepha edatha kukhona amanye amashidi aseceleni efayelini, khona-ke kulesi sinyathelo umsebenzi wethu ukukhetha kuphela lawo maphepha lapho ulwazi ludinga ukulayishwa khona, ngaphandle kwawo wonke amanye kusetshenziswa isihlungi kunhlokweni yetafula:
Susa wonke amakholomu ngaphandle kwekholomu Idathangokuchofoza kwesokudla isihloko sekholomu nokukhetha Susa amanye amakholomu (Susa amanye amakholomu):
Ungakwazi-ke ukunweba okuqukethwe kwamathebula aqoqiwe ngokuchofoza imicibisholo emibili phezulu kwekholomu (ibhokisi lokuhlola Sebenzisa igama lekholomu yoqobo njengesiqalo ungayivala):
Uma wenze konke ngendlela efanele, khona-ke kuleli phuzu kufanele ubone okuqukethwe kwawo wonke amathebula aqoqwe elinye ngezansi kwelinye:
Kusele ukuphakamisa umugqa wokuqala kunhlokweni yethebula ngenkinobho Sebenzisa umugqa wokuqala njengezihloko (Sebenzisa umugqa wokuqala njengezihloko) ithebhu Ikhaya (Ikhaya) futhi ususe izihloko zethebula eziyimpinda kudatha usebenzisa isihlungi:
Londoloza konke okwenziwe ngomyalo Vala futhi ulayishe - Vala futhi ulayishe... (Vala & Layisha — Vala & Layisha ku...) ithebhu Ikhaya (Ikhaya), futhi efasiteleni elivulekayo, khetha inketho Ukuxhumana kuphela (Ukuxhumana Kuphela):
Konke. Kusele kuphela ukwakha isifinyezo. Ukuze wenze lokhu, yiya kuthebhu Faka – PivotTable (Faka — Ithebula Le-Pivot), khetha inketho Sebenzisa umthombo wedatha wangaphandle (Sebenzisa umthombo wedatha wangaphandle)bese ngokuchofoza inkinobho Khetha uxhumano, isicelo sethu. Ukudalwa okuqhubekayo nokucushwa kwe-pivot kwenzeka ngendlela ejwayelekile ngokuphelele ngokuhudulela izinkambu esizidingayo emigqeni, kumakholomu nasendaweni yamanani:
Uma idatha yomthombo ishintsha esikhathini esizayo noma engeza amashidi ambalwa esitolo, kuzokwanela ukubuyekeza umbuzo nesifinyezo sethu sisebenzisa umyalo. Vuselela konke ithebhu Idatha (Idatha — Vuselela Konke).
Indlela 2. Sihlanganisa amatafula nomyalo we-UNION SQL ku-macro
Esinye isisombululo senkinga yethu simelelwa yile macro, eyenza isethi yedatha (cache) yethebula le-pivot isebenzisa umyalo. UNITY Ulimi lombuzo lwe-SQL. Lo myalo uhlanganisa amathebula avela kukho konke okushiwo kuhlelo Amagama Eshidi amashidi encwadi abe yithebula ledatha elilodwa. Okungukuthi, esikhundleni sokukopisha ngokomzimba nokunamathisela ububanzi ukusuka kumakhasi ahlukene kuye kwelinye, senza okufanayo ku-RAM yekhompyutha. Ngemuva kwalokho i-macro yengeza ishidi elisha elinegama elinikeziwe (okuguquguqukayo ResultSheetName) futhi idale isifinyezo esigcwele (!) kuso ngokusekelwe kunqolobane eqoqiwe.
Ukuze usebenzise i-macro, sebenzisa inkinobho ye-Visual Basic kuthebhu Unjiniyela (Unjiniyela) noma isinqamuleli sekhibhodi Alt+F11. Bese sifaka imojuli entsha engenalutho ngemenyu Faka – Imojula bese ukopisha ikhodi elandelayo lapho:
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 Njengesiguquli 'igama leshidi lapho i-pivot ewumphumela izoboniswa khona = Umphumela weshidi "i-Pivot ye-Pivot" amagama anamathebula omthombo AmaSpredishithi = I-Array("Alpha", "Beta", "Gamma", "Delta") 'sakha inqolobane yamathebula asuka kumakhasi Amagama Ane-ActiveWorkbook ReDim arSQL(1 Kuya ku (UBound(AmagamaAshidi) + 1) ) Ku-i = LBound (AmaSpredishithiNames) Ukuze UBUund(AmagamaAshidi) arSQL(i + 1) = "KHETHA * KUSUKA KU-[" & AmaSpredishithiNames(i) & "$]" Okulandelayo i Set objRS = CreateObject("ADODB.Recordset") objRS .Vula i-Join$( arSQL, " UNION ALL "), _ Joyina$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Umthombo Wedatha=", _ .Igama Eligcwele, ";Izakhiwo Ezandisiwe=""Excel 8.0;" ""), vbNullString ) Qeda ngo 'dala kabusha ishidi ukuze ubonise ithebula le-pivot eliwumphumela Ephuthani Qalisa Kabusha Isicelo Esilandelayo.DisplayAlerts = Amaphepha Okusebenzela Amanga(ResultSheetName).Susa Isethi wsPivot = Amashidi Okusebenzela.Engeza i-wsPivo t. Igama = ResultSheetName 'bonisa isifinyezo senqolobane ekhiqiziwe kuleli shidi Setha objPivotCache = ActiveWorkbook.PivotCaches.Engeza(xlExternal) Setha i-objPivotCache.Recordset = objRS Setha objRS = Akukho Nge-wsPivot objPivotCache") objPivotCache = Nothing Range("A3").Khetha Ukuphela Nge Sub Imakhro eqediwe ingase isetshenziswe ngesinqamuleli sekhibhodi Alt+F8 noma inkinobho ye-Macros kuthebhu Unjiniyela (Unjiniyela - Amamakhro).
Ububi bale ndlela:
- Idatha ayibuyekeziwe ngoba inqolobane ayinakho ukuxhumana namathebula omthombo. Uma ushintsha idatha yomthombo, kufanele uqalise i-macro futhi futhi wakhe isifinyezo futhi.
- Lapho ushintsha inani lamashidi, kuyadingeka ukuhlela ikhodi ye-macro (array Amagama Eshidi).
Kodwa ekugcineni sithola ithebula le-pivot langempela eligcwele ngokugcwele, elakhelwe ezinhlotsheni ezimbalwa zamashidi ahlukene:
Njalo!
Inothi lobuchwepheshe: uma uthola iphutha elifana nokuthi “Umhlinzeki akabhalisiwe” lapho usebenzisa i-macro, cishe unenguqulo ye-Excel engu-64-bit noma inguqulo engaphelele ye-Office efakiwe (akukho Ukufinyelela). Ukuze ulungise isimo, buyisela isiqeshana kukhodi ye-macro:
Umhlinzeki=Microsoft.Jet.OLEDB.4.0;
ukuze:
Umhlinzeki=Microsoft.ACE.OLEDB.12.0;
Futhi landa futhi ufake injini yamahhala yokucubungula idatha kusuka ku-Finyelela kuwebhusayithi ye-Microsoft - Injini Yedatha Yokufinyelela ye-Microsoft 2010 Engasabalaliswa kabusha
Indlela yesi-3: Hlanganisa i-PivotTable Wizard kusuka kuzinguqulo ezindala ze-Excel
Le ndlela isiphelelwe yisikhathi kancane, kodwa kusafanele ukubalula. Uma sikhuluma ngokusemthethweni, kuzo zonke izinguqulo kuze kufike ku-2003 futhi kufaka phakathi, bekukhona inketho ku-PivotTable Wizard "ukwakha i-pivot yezigaba ezimbalwa zokuhlanganisa". Kodwa-ke, umbiko owenziwe ngale ndlela, ngeshwa, uzoba kuphela okudabukisayo okudabukisayo kwesifinyezo sangempela esigcwele futhi awusekeli "ama-chips" amaningi amathebula e-pivot ajwayelekile:
Ku-pivot enjalo, azikho izihloko zekholomu ohlwini lwenkambu, asikho isilungiselelo sesakhiwo esivumelana nezimo, isethi yemisebenzi esetshenziswayo inomkhawulo, futhi, ngokuvamile, konke lokhu akufani kakhulu nethebula le-pivot. Mhlawumbe yingakho, kusukela ngo-2007, iMicrosoft yasusa lo msebenzi engxoxweni evamile lapho idala imibiko yetafula le-pivot. Manje lesi sici sitholakala kuphela ngenkinobho yangokwezifiso I-PivotTable Wizard(Isilekeleli Sethebula Le-Pivot), okuthi, uma kuthanda, kwengezwe kubha yamathuluzi yokufinyelela Ngokushesha ngokusebenzisa Ifayela - Izinketho - Yenza ngokwezifiso Ibha yamathuluzi yokufinyelela Ngokushesha - Yonke Imiyalo (Ifayela — Izinketho — Yenza ngokwezifiso ibha yamathuluzi yokufinyelela Ngokushesha — Yonke Imiyalo):
Ngemuva kokuchofoza inkinobho eyengeziwe, udinga ukukhetha inketho efanele esinyathelweni sokuqala sewizadi:
Bese kuthi ewindini elilandelayo, khetha ibanga ngalinye ngokushintshana bese ulengeza ohlwini olujwayelekile:
Kodwa, futhi, lesi akusona isifinyezo esigcwele, ngakho ungalindeli okuningi kakhulu kuso. Ngingancoma le nketho kuphela ezimweni ezilula kakhulu.
- Ukudala Imibiko ngama-PivotTables
- Setha izibalo ku-PivotTables
- Ayini ama-macros, angawasebenzisa kanjani, kuphi ukukopisha ikhodi ye-VBA, njll.
- Ukuqoqwa kwedatha kusuka kumashidi amaningi kuya kwelilodwa (isengezo se-PLEX)