Teburin Pivot a cikin kewayon bayanai da yawa

Samar da matsala

Teburan pivot ɗaya ne daga cikin kayan aiki mafi ban mamaki a cikin Excel. Amma ya zuwa yanzu, da rashin alheri, babu ɗaya daga cikin sassan Excel da zai iya yin irin wannan abu mai sauƙi kuma mai mahimmanci akan tashi kamar gina taƙaitaccen jeri na farko na bayanan da aka samo, alal misali, akan zanen gado daban-daban ko a cikin tebur daban-daban:

Kafin mu fara, bari mu fayyace abubuwa guda biyu. A priori, na yi imani cewa an cika waɗannan sharuɗɗan a cikin bayananmu:

• Tables na iya samun kowane adadin layuka tare da kowane bayanai, amma dole ne su kasance suna da rubutun kai iri ɗaya.
• Kada a sami ƙarin bayanai akan zanen gado tare da tebur mai tushe. Ɗayan takarda - tebur ɗaya. Don sarrafawa, ina ba ku shawara ku yi amfani da gajeriyar hanyar madannai Ctrl+karshen, wanda ke motsa ku zuwa tantanin halitta da aka yi amfani da su na ƙarshe a cikin takardar aiki. Da kyau, wannan yakamata ya zama tantanin halitta na ƙarshe a cikin tebur ɗin bayanai. Idan kun danna Ctrl+karshen duk wani tantanin halitta mara komai zuwa dama ko ƙasa da tebur yana haskaka - share waɗannan ginshiƙan fanko zuwa dama ko layuka a ƙasan tebur bayan tebur kuma adana fayil ɗin.

Hanyar 1: Gina teburi don pivot ta amfani da Query Query

An fara daga nau'in 2010 don Excel, akwai ƙarar Query Query kyauta wanda zai iya tattarawa da canza kowane bayanai sannan a ba shi a matsayin tushen gina tebur pivot. Magance matsalar mu tare da taimakon wannan add-in ba shi da wahala ko kaɗan.

Da farko, bari mu ƙirƙiri sabon fayil mara komai a cikin Excel - taro zai gudana a ciki sannan kuma za a ƙirƙiri tebur mai pivot a ciki.

Sannan akan tab data (idan kuna da Excel 2016 ko daga baya) ko akan shafin Tambayar .arfi (idan kuna da Excel 2010-2013) zaɓi umarnin Ƙirƙiri Tambaya - Daga Fayil - Excel (Samu Data - Daga fayil - Excel) kuma saka fayil ɗin tushen tare da teburin da za a tattara:

A cikin taga da ya bayyana, zaɓi kowane takarda (ba kome ba) kuma danna maɓallin da ke ƙasa Change (Edit):

Ya kamata taga Editan Query Query Query a saman Excel. A gefen dama na taga akan panel Neman Sigogi share duk matakan da aka ƙirƙira ta atomatik sai na farko - source (Source):

Yanzu mun ga jerin jerin duk zanen gado. Idan ban da takaddun bayanan akwai wasu takaddun gefe a cikin fayil ɗin, to, a wannan matakin aikinmu shine zaɓar waɗancan zanen gadon da ake buƙatar lodawa daga ciki, ban da duk sauran masu amfani da tacewa a cikin taken tebur:

Share duk ginshiƙai ban da shafi datata danna dama-dama a kan shafi kuma zaɓi Share wasu ginshiƙai (Cire sauran ginshiƙai):

Sannan zaku iya fadada abubuwan da ke cikin teburan da aka tattara ta danna kibiya sau biyu a saman ginshiƙi (akwatin rajistan shiga. Yi amfani da sunan ginshiƙi na asali azaman prefix zaka iya kashe shi):

Idan kun yi komai daidai, to, a wannan lokacin ya kamata ku ga abubuwan da ke cikin dukkan allunan da aka tattara ɗaya a ƙasa da ɗayan:

Ya rage don ɗaga jere na farko zuwa taken tebur tare da maɓallin Yi amfani da layin farko azaman masu kai (Yi amfani da layin farko azaman masu kai) tab Gida (Gida) kuma cire kwafin rubutun tebur daga bayanan ta amfani da tace:

Ajiye duk abin da aka yi tare da umarnin Rufe kuma ɗauka - Rufe kuma ɗauka a cikin… (Rufe & Load - Kusa & Loda zuwa…) tab Gida (Gida), kuma a cikin taga da yake buɗewa, zaɓi zaɓi Haɗi kawai (Haɗin kai kaɗai):

Komai. Ya rage kawai don gina taƙaitaccen bayani. Don yin wannan, je zuwa shafin Saka - PivotTable (Saka - Tebur Pivot), zaɓi zaɓi Yi amfani da tushen bayanan waje (Yi amfani da tushen bayanan waje)sannan ta danna maballin Zaɓi haɗi, bukatar mu. Ƙarin ƙirƙira da daidaitawa na pivot yana faruwa a cikin madaidaiciyar hanya ta hanyar jan filayen da muke buƙata zuwa cikin layuka, ginshiƙai da yanki masu ƙima:

Idan bayanan tushen sun canza a nan gaba ko kuma an ƙara wasu ƴan wasu takaddun shagunan, to zai isa a sabunta tambaya da taƙaitawar mu ta amfani da umarnin. Wartsake duka tab data (Bayanai - Refresh Duk).

Hanyar 2. Mun haɗu da tebur tare da umarnin UNION SQL a cikin macro

Wani bayani ga matsalarmu yana wakiltar wannan macro, wanda ke ƙirƙirar saitin bayanai (cache) don tebur pivot ta amfani da umarnin. HANKARI Harshen tambaya SQL. Wannan umarnin yana haɗa tebur daga duk ƙayyadaddun tsari a cikin tsararru Sunayen Sheet zanen gado na littafin a cikin tebur bayanai guda ɗaya. Wato, maimakon yin kwafin jiki da liƙa jeri daga zanen gado daban-daban zuwa ɗaya, haka muke yi a cikin RAM ɗin kwamfutar. Sannan macro yana ƙara sabon takarda tare da sunan da aka ba (mai canzawa ResultSheet Name) kuma ya ƙirƙiri cikakken bayani (!) taƙaice akan sa dangane da cache da aka tattara.

Don amfani da macro, yi amfani da maɓallin Kayayyakin gani a shafin developer (Mai haɓakawa) ko gajeriyar hanyar keyboard alt+F11. Sa'an nan kuma mu saka sabon fanko module ta cikin menu Saka - Module sannan ka kwafi wannan code din can:

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 Sheets Names As Variant 'sheet name inda sakamakon pivot = za a nuna sakamakon'PivotName takardar'Sakamakon Sheetray Sunaye tare da tebur masu tushe SheetsNames = Array ("Alpha", "Beta", "Gamma", "Delta") "mun samar da cache don tebur daga zanen gado daga SheetsNames Tare da ActiveWorkbook ReDim arSQL(1 Zuwa (UBound(SheetsNames)) + 1) ) Domin i = LBound (Sunan Sheets) Zuwa UBound(SheetsNames) arSQL(i + 1) = "Zabi * DAGA [" & SheetsNames(i) & "$]" Na gaba i Set objRS = CreateObject("ADODB.Recordset") objRS .Buɗe Join $ ( arSQL, " UNION ALL "), _ Haɗa $(Array("Mai ba da = Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName,";Extended Properties=""Excel 8.0;" ""), vbNullString ) Ƙare Tare da 'sake ƙirƙira takardar don nuna sakamakon pivot tebur akan Kuskure Ci gaba da aikace-aikace na gaba.DisplayAlerts = Takardun Aiki na Ƙarya(SakamakonNama) .Share Saitin wsPivot = Aiki. Ƙara wsPivo t. Suna = ResultSheetName 'nuna taƙaitaccen ma'ajin da aka ƙirƙira akan wannan takardar Saita objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Saita objPivotCache.Recordset = objRS Saita objRS = Babu wani abu Tare da wsPivot objPivotCache.RawsPivot objPivotCache.Create Set objPivotCache = Babu Komai Rage("A3").Zaɓa Ƙare Tare da Ƙarshe Sub    

Ana iya gudanar da macro da aka gama tare da gajeriyar hanya ta madannai alt+F8 ko maɓallin Macros akan shafin developer (Developer - Macros).

Fursunoni na wannan hanyar:

• Ba a sabunta bayanan ba saboda cache ɗin ba shi da haɗi zuwa teburin tushen. Idan kun canza bayanan tushen, dole ne ku sake kunna macro kuma ku sake gina taƙaitawar.
• Lokacin canza adadin zanen gado, ya zama dole don gyara macro code (array Sunayen Sheet).
  

Amma a ƙarshe muna samun ainihin cikakken tebur pivot, wanda aka gina akan jeri da yawa daga zanen gado daban-daban:

Voilà!

Bayanan fasaha: idan kun sami kuskure kamar "Ba a yi rajistar mai bayarwa ba" lokacin gudanar da macro, to tabbas kuna da nau'in 64-bit na Excel ko kuma an shigar da sigar ofis ɗin da bai cika ba (babu Access). Don gyara halin da ake ciki, maye gurbin guntu a cikin macro code:

	 Mai bayarwa=Microsoft.Jet.OLEDB.4.0;  

zuwa:

	Mai bayarwa=Microsoft.ACE.OLEDB.12.0;  

Kuma zazzagewa kuma shigar da injin sarrafa bayanai kyauta daga Samun shiga daga gidan yanar gizon Microsoft - Microsoft Access Database Engine 2010 Redistributable

Hanyar 3: Haɓaka Wizard na PivotTable daga Tsoffin Siffofin Excel

Wannan hanyar ba ta daɗe, amma har yanzu yana da daraja a ambata. Magana a ƙa'ida, a cikin duk nau'ikan har zuwa 2003, akwai zaɓi a cikin Wizard na PivotTable don "gina pivot don jeri na haɓaka da yawa". Koyaya, rahoton da aka gina ta wannan hanyar, da rashin alheri, zai zama abin tausayi ne kawai na cikakken taƙaitaccen bayani kuma baya goyan bayan yawancin “guntu” na allunan pivot na al'ada:

A cikin irin wannan pivot, babu rubutun shafi a cikin jerin filin, babu wani saitin tsari mai sassauƙa, saitin ayyukan da aka yi amfani da shi yana da iyaka, kuma, a gaba ɗaya, duk wannan ba shi da kama da tebur pivot. Wataƙila shi ya sa, daga 2007, Microsoft ya cire wannan aikin daga daidaitattun maganganu lokacin ƙirƙirar rahotannin tebur na pivot. Yanzu wannan fasalin yana samuwa ta hanyar maɓallin al'ada kawai PivotTable Wizard(Mayen Teburin Pivot), wanda, idan ana so, za'a iya ƙarawa zuwa Toolbar Saurin Shiga ta hanyar Fayil - Zaɓuɓɓuka - Keɓance Toolbar Samun Sauri - Duk Dokoki (Fayil - Zaɓuɓɓuka - Keɓance Kayan aikin Samun Sauri - Duk Dokokin):

Bayan danna maɓallin da aka ƙara, kuna buƙatar zaɓar zaɓin da ya dace a matakin farko na mayen:

Sannan a cikin taga na gaba, zaɓi kowane kewayon bi da bi kuma ƙara shi zuwa jerin gabaɗaya:

Amma, kuma, wannan ba cikakken taƙaitaccen bayani ba ne, don haka kada ku yi tsammanin da yawa daga gare ta. Zan iya ba da shawarar wannan zaɓi kawai a cikin lokuta masu sauƙi.

• Ƙirƙirar Rahotanni tare da PivotTables
• Saita lissafi a cikin PivotTables
• Menene macros, yadda ake amfani da su, inda za a kwafi lambar VBA, da sauransu.
• Tarin bayanai daga zanen gado da yawa zuwa ɗaya (Ƙarin PLEX)