Jedwali la egemeo kwenye safu nyingi za data

Uundaji wa shida

Jedwali la egemeo ni mojawapo ya zana za kushangaza zaidi katika Excel. Lakini hadi sasa, kwa bahati mbaya, hakuna toleo la Excel linaloweza kufanya jambo rahisi na muhimu kwa kuruka kama kujenga muhtasari wa safu kadhaa za data za awali ziko, kwa mfano, kwenye karatasi tofauti au kwenye jedwali tofauti:

Kabla ya kuanza, hebu tufafanue pointi kadhaa. Jambo la kwanza, ninaamini kuwa masharti yafuatayo yanatimizwa katika data yetu:

• Majedwali yanaweza kuwa na idadi yoyote ya safu mlalo na data yoyote, lakini lazima ziwe na kichwa sawa.
• Haipaswi kuwa na data ya ziada kwenye laha zilizo na jedwali la chanzo. Karatasi moja - meza moja. Ili kudhibiti, nakushauri utumie njia ya mkato ya kibodi Ctrl+mwisho, ambayo hukusogeza hadi kwenye kisanduku cha mwisho kilichotumika kwenye lahakazi. Kwa kweli, hii inapaswa kuwa seli ya mwisho kwenye jedwali la data. Ikiwa unapobofya Ctrl+mwisho seli yoyote tupu upande wa kulia au chini ya jedwali imeangaziwa - futa safu wima hizi tupu kulia au safu mlalo chini ya jedwali baada ya jedwali na uhifadhi faili.

Njia ya 1: Tengeneza jedwali kwa egemeo kwa kutumia Hoja ya Nguvu

Kuanzia toleo la 2010 la Excel, kuna programu jalizi isiyolipishwa ya Hoji ya Nishati ambayo inaweza kukusanya na kubadilisha data yoyote kisha kuipa kama chanzo cha kuunda jedwali la egemeo. Kutatua tatizo letu kwa msaada wa programu-jalizi hii sio ngumu hata kidogo.

Kwanza, hebu tuunde faili mpya tupu katika Excel - mkusanyiko utafanyika ndani yake na kisha meza ya pivot itaundwa ndani yake.

Kisha kwenye kichupo Data (ikiwa unayo Excel 2016 au baadaye) au kwenye kichupo Hoja ya Nguvu (ikiwa unayo Excel 2010-2013) chagua amri Unda Swali - Kutoka kwa Faili - Excel (Pata Data - Kutoka kwa faili - Excel) na taja faili chanzo na jedwali zitakazokusanywa:

Katika dirisha inayoonekana, chagua karatasi yoyote (haijalishi ni ipi) na bonyeza kitufe hapa chini Mabadiliko ya (Hariri):

Dirisha la Kuhariri Swala la Nguvu linapaswa kufunguliwa juu ya Excel. Kwenye upande wa kulia wa dirisha kwenye paneli Omba Vigezo futa hatua zote zilizoundwa kiotomatiki isipokuwa ya kwanza - chanzo (Chanzo):

Sasa tunaona orodha ya jumla ya karatasi zote. Ikiwa pamoja na karatasi za data kuna karatasi zingine za upande kwenye faili, basi kwa hatua hii kazi yetu ni kuchagua karatasi zile tu ambazo habari inahitaji kupakiwa, ukiondoa zingine zote kwa kutumia kichungi kwenye kichwa cha jedwali:

Futa safu wima zote isipokuwa safu wima Datakwa kubofya kulia kichwa cha safu na kuchagua Futa safu wima zingine (Ondoa safuwima zingine):

Kisha unaweza kupanua yaliyomo kwenye majedwali yaliyokusanywa kwa kubofya vishale viwili vilivyo juu ya safu wima (kisanduku cha kuteua Tumia jina la safu wima asili kama kiambishi awali unaweza kuizima):

Ikiwa ulifanya kila kitu kwa usahihi, basi katika hatua hii unapaswa kuona yaliyomo kwenye jedwali zote zilizokusanywa moja chini ya nyingine:

Inabakia kuinua safu ya kwanza kwenye kichwa cha meza na kifungo Tumia mstari wa kwanza kama vichwa (Tumia safu mlalo ya kwanza kama vichwa) tab Nyumbani (Nyumbani) na uondoe vichwa vya meza kutoka kwa data kwa kutumia kichungi:

Hifadhi kila kitu kilichofanywa na amri Funga na upakie - Funga na upakie ndani... (Funga na Upakie - Funga na Upakie kwa...) tab Nyumbani (Nyumbani), na katika dirisha linalofungua, chagua chaguo Muunganisho pekee (Muunganisho Pekee):

Kila kitu. Inabakia tu kujenga muhtasari. Ili kufanya hivyo, nenda kwenye kichupo Ingiza - Jedwali la Pivot (Ingiza - Jedwali la Egemeo), chagua chaguo Tumia chanzo cha data cha nje (Tumia chanzo cha data cha nje)na kisha kwa kubofya kitufe Chagua muunganisho, ombi letu. Uundaji zaidi na usanidi wa egemeo hufanyika kwa njia ya kawaida kabisa kwa kuburuta sehemu tunazohitaji kwenye safu mlalo, safu wima na eneo la maadili:

Ikiwa data chanzo itabadilika katika siku zijazo au laha chache zaidi za duka zitaongezwa, basi itatosha kusasisha hoja na muhtasari wetu kwa kutumia amri. Onyesha upya zote tab Data (Data - Onyesha upya Zote).

Njia ya 2. Tunaunganisha meza na amri ya UNION SQL katika macro

Suluhisho lingine la shida yetu linawakilishwa na jumla hii, ambayo huunda seti ya data (cache) ya jedwali la egemeo kwa kutumia amri. Umoja Lugha ya swala ya SQL. Amri hii inachanganya jedwali kutoka kwa zote zilizoainishwa kwenye safu Majina ya Laha laha za kitabu kwenye jedwali moja la data. Hiyo ni, badala ya kunakili na kubandika safu kutoka kwa karatasi tofauti hadi moja, tunafanya vivyo hivyo kwenye RAM ya kompyuta. Kisha jumla inaongeza karatasi mpya iliyo na jina lililopewa (variable ResultSheetName) na huunda muhtasari kamili (!) juu yake kulingana na kache iliyokusanywa.

Ili kutumia jumla, tumia kitufe cha Visual Basic kwenye kichupo developer (Msanidi programu) au njia ya mkato ya kibodi Alt+F11. Kisha tunaingiza moduli mpya tupu kupitia menyu Ingiza - Moduli na unakili nambari ifuatayo hapo:

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 Kama Lahaja 'jina la laha ambapo pivot inayotokana itaonyeshwa 'Resultat sheet. majina yenye majedwali ya chanzo SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'tunaunda akiba ya majedwali kutoka laha kutoka kwa Majedwali ya Laha zenye ActiveWorkbook ReDim arSQL(1 Hadi (UBound(Majina ya laha) + 1) ) Kwa i = LBound (Majina ya Laha) Ili UUBound(Majina ya Laha) arSQL(i + 1) = "CHAGUA * KUTOKA KWA [" & Majina ya Laha(i) & "$]" Kisha naweka objRS = CreateObject("ADODB.Recordset") objRS .Fungua Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .Jina Kamili, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) Maliza Kwa 'unda upya laha ili kuonyesha jedwali la egemeo linalotokana. Hitilafu Tekeleza Programu Inayofuata.DisplayAlerts = Laha za Kazi Sivyo(ResultSheetName).Futa Set wsPivot = Laha za Kazi.Ongeza wsPivo t. Jina = ResultSheetName 'onyesha muhtasari wa akiba uliotolewa kwenye laha hii Weka objPivotCache = ActiveWorkbook.PivotCaches.Ongeza(xlExternal) Weka objPivotCache.Recordset = objRS Set objRS = Nothing With wsPivot objPivotCache" ==Rakaba ya wsPivot "PivotDestination". objPivotCache = Nothing Range("A3").Chagua Maliza Na Ndogo ya Mwisho    

Macro iliyokamilishwa inaweza kuendeshwa kwa njia ya mkato ya kibodi Alt+F8 au kitufe cha Macros kwenye kichupo developer (Msanidi - Macros).

Hasara za mbinu hii:

• Data haijasasishwa kwa sababu kache haina muunganisho wa jedwali chanzo. Ukibadilisha data ya chanzo, lazima uendeshe jumla tena na uunda muhtasari tena.
• Wakati wa kubadilisha idadi ya shuka, ni muhimu kuhariri nambari ya jumla (safu Majina ya Laha).
  

Lakini mwishowe tunapata jedwali la egemeo kamili, lililojengwa kwa safu kadhaa kutoka kwa laha tofauti:

Voilà!

Ujumbe wa kiufundi: ukipata hitilafu kama vile "Mtoa huduma hajasajiliwa" wakati wa kuendesha jumla, basi uwezekano mkubwa una toleo la 64-bit la Excel au toleo lisilo kamili la Ofisi limesakinishwa (hakuna Ufikiaji). Ili kurekebisha hali hiyo, badilisha kipande kwenye nambari ya jumla:

	 Mtoa huduma=Microsoft.Jet.OLEDB.4.0;  

kwa:

	Mtoa huduma=Microsoft.ACE.OLEDB.12.0;  

Na pakua na usakinishe injini ya uchakataji data bila malipo kutoka kwa Ufikiaji kutoka kwa tovuti ya Microsoft - Microsoft Access Database Engine 2010 Redistributable

Mbinu ya 3: Unganisha Mchawi wa Jedwali la Pivot kutoka kwa Matoleo ya Kale ya Excel

Njia hii ni ya zamani kidogo, lakini bado inafaa kutaja. Kuzungumza rasmi, katika matoleo yote hadi na ikiwa ni pamoja na 2003, kulikuwa na chaguo katika PivotTable Wizard "kujenga pivot kwa safu kadhaa za ujumuishaji". Walakini, ripoti iliyoundwa kwa njia hii, kwa bahati mbaya, itakuwa tu mfano wa kusikitisha wa muhtasari kamili kamili na haiauni "chips" nyingi za jedwali za egemeo za kawaida:

Katika pivot vile, hakuna vichwa vya safu katika orodha ya shamba, hakuna mpangilio wa muundo unaobadilika, seti ya kazi zinazotumiwa ni ndogo, na, kwa ujumla, yote haya hayafanani sana na meza ya pivot. Labda ndiyo sababu, kuanzia 2007, Microsoft iliondoa kitendakazi hiki kutoka kwa mazungumzo ya kawaida wakati wa kuunda ripoti za jedwali la egemeo. Sasa kipengele hiki kinapatikana tu kupitia kitufe maalum Mchawi wa Jedwali la Pivot(Mchawi wa Jedwali la Egemeo), ambayo, ikiwa inataka, inaweza kuongezwa kwenye Upauzana wa Ufikiaji Haraka kupitia Faili - Chaguzi - Binafsisha Zana ya Ufikiaji Haraka - Amri Zote (Faili - Chaguzi - Binafsisha Upauzana wa Ufikiaji Haraka - Amri Zote):

Baada ya kubofya kitufe kilichoongezwa, unahitaji kuchagua chaguo sahihi katika hatua ya kwanza ya mchawi:

Na kisha kwenye dirisha linalofuata, chagua kila safu kwa zamu na uiongeze kwenye orodha ya jumla:

Lakini, tena, huu sio muhtasari kamili, kwa hivyo usitegemee mengi kutoka kwake. Ninaweza kupendekeza chaguo hili tu katika kesi rahisi sana.

• Kuunda Ripoti kwa PivotTables
• Weka hesabu katika PivotTables
• Macros ni nini, jinsi ya kuzitumia, wapi kunakili nambari ya VBA, nk.
• Mkusanyiko wa data kutoka laha nyingi hadi moja (nyongeza ya PLEX)