Tabili Pivot kọja awọn sakani data lọpọlọpọ

Ilana ti iṣoro naa

Awọn tabili Pivot jẹ ọkan ninu awọn irinṣẹ iyalẹnu julọ ni Excel. Ṣugbọn titi di isisiyi, laanu, ko si ọkan ninu awọn ẹya ti Excel ti o le ṣe iru ohun ti o rọrun ati pataki lori fo bi kikọ akopọ fun ọpọlọpọ awọn sakani data ibẹrẹ ti o wa, fun apẹẹrẹ, lori awọn iwe oriṣiriṣi tabi ni awọn tabili oriṣiriṣi:

Ṣaaju ki a to bẹrẹ, jẹ ki a ṣe alaye awọn aaye meji kan. Ni iṣaaju, Mo gbagbọ pe awọn ipo wọnyi ti pade ninu data wa:

• Awọn tabili le ni nọmba eyikeyi ti awọn ori ila pẹlu eyikeyi data, ṣugbọn wọn gbọdọ ni akọsori kanna.
• Ko yẹ ki o jẹ data afikun lori awọn iwe pẹlu awọn tabili orisun. Iwe kan - tabili kan. Lati ṣakoso, Mo gba ọ ni imọran lati lo ọna abuja keyboard kan Konturolu+opin, eyiti o gbe ọ lọ si sẹẹli ti a lo kẹhin ninu iwe iṣẹ. Ni deede, eyi yẹ ki o jẹ sẹẹli ti o kẹhin ninu tabili data. Ti o ba tẹ lori Konturolu+opin eyikeyi sẹẹli ti o ṣofo si apa ọtun tabi isalẹ tabili jẹ afihan - paarẹ awọn ọwọn ofo wọnyi si apa ọtun tabi awọn ori ila ni isalẹ tabili lẹhin tabili ati fi faili pamọ.

Ọna 1: Kọ awọn tabili fun pivot nipa lilo Ibeere Agbara

Bibẹrẹ lati ẹya 2010 fun Excel, afikun Ibeere Agbara ọfẹ kan wa ti o le gba ati yi data eyikeyi pada lẹhinna fun ni orisun kan fun kikọ tabili pivot kan. Yiyan iṣoro wa pẹlu iranlọwọ ti afikun yii ko nira rara.

Ni akọkọ, jẹ ki a ṣẹda faili ofo tuntun ni Excel – apejọ yoo waye ninu rẹ lẹhinna tabili pivot yoo ṣẹda ninu rẹ.

Lẹhinna lori taabu data (ti o ba ni Excel 2016 tabi nigbamii) tabi lori taabu Ibeere Agbara (ti o ba ni Excel 2010-2013) yan aṣẹ naa Ṣẹda ibeere – Lati Faili – Tayo (Gba Data - Lati faili - Tayo) ati pato faili orisun pẹlu awọn tabili lati gba:

Ni awọn window ti o han, yan eyikeyi dì (ko ni pataki eyi ti ọkan) ki o si tẹ awọn bọtini ni isalẹ ayipada [Ṣatunkọ]:

Ferese Olootu ibeere ibeere agbara yẹ ki o ṣii lori oke ti Excel. Ni apa ọtun ti window lori nronu Beere Parameters Pa gbogbo awọn igbesẹ ti o ṣẹda laifọwọyi ayafi akọkọ - orisun (Orisun):

Bayi a rii atokọ gbogbogbo ti gbogbo awọn iwe. Ti o ba jẹ pe ni afikun si awọn iwe data diẹ ninu awọn iwe ẹgbẹ miiran ninu faili naa, lẹhinna ni igbesẹ yii iṣẹ-ṣiṣe wa ni lati yan awọn iwe nikan lati eyiti alaye ti o nilo lati kojọpọ, laisi gbogbo awọn miiran ni lilo àlẹmọ ninu akọsori tabili:

Pa gbogbo awọn ọwọn ayafi iwe datanipa titẹ-ọtun akọle iwe kan ati yiyan Pa awọn ọwọn miiran rẹ (Yọ kuro awọn ọwọn miiran):

Lẹhinna o le faagun awọn akoonu ti awọn tabili ti a gbajọ nipa tite lori itọka ilọpo meji ni oke ti ọwọn (apoti apoti Lo orukọ iwe atilẹba bi ìpele o le pa a):

Ti o ba ṣe ohun gbogbo ni deede, lẹhinna ni aaye yii o yẹ ki o wo awọn akoonu ti gbogbo awọn tabili ti o gba ọkan ni isalẹ ekeji:

O wa lati gbe kana akọkọ soke si akọsori tabili pẹlu bọtini Lo laini akọkọ bi awọn akọle (Lo ila akọkọ bi awọn akọle) taabu Home (Ile) ki o si yọ awọn akọle tabili pidánpidán kuro ninu data nipa lilo àlẹmọ:

Fipamọ ohun gbogbo ti a ṣe pẹlu aṣẹ Pade ati fifuye – Sunmọ ati gbe sinu… (Pade & Gbe - Sunmọ & Gbe si…) taabu Home (Ile), ati ninu ferese ti o ṣi, yan aṣayan Asopọmọra nikan (Asopọ nikan):

Ohun gbogbo. O ku lati kọ akopọ kan nikan. Lati ṣe eyi, lọ si taabu Fi sii - PivotTable (Fi sii - Tabili Pivot), yan aṣayan Lo orisun data ita (Lo orisun data ita)ati lẹhinna nipa titẹ bọtini Yan asopọ, ibere wa. Ṣiṣẹda siwaju ati iṣeto ti pivot waye ni ọna boṣewa patapata nipa fifa awọn aaye ti a nilo sinu awọn ori ila, awọn ọwọn ati agbegbe awọn iye:

Ti data orisun ba yipada ni ọjọ iwaju tabi awọn iwe itaja diẹ sii ti wa ni afikun, lẹhinna yoo to lati ṣe imudojuiwọn ibeere naa ati akopọ wa nipa lilo aṣẹ naa Tun gbogbo rẹ sọ taabu data (Data - Tun gbogbo rẹ sọ).

Ọna 2. A ṣọkan awọn tabili pẹlu aṣẹ UNION SQL ni macro

Ojutu miiran si iṣoro wa jẹ aṣoju nipasẹ Makiro yii, eyiti o ṣẹda ṣeto data (kaṣe) fun tabili pivot nipa lilo aṣẹ UNITY ede ibeere SQL. Yi aṣẹ daapọ tabili lati gbogbo awọn pato ninu awọn orun Awọn Orukọ Sheet awọn iwe ti iwe sinu tabili data kan. Iyẹn ni, dipo didakọ ti ara ati sisọ awọn sakani lati oriṣiriṣi awọn iwe si ọkan, a ṣe kanna ni Ramu kọnputa naa. Lẹhinna Makiro ṣafikun iwe tuntun pẹlu orukọ ti a fun (ayipada Orukọ esiSheet) ati ṣẹda akojọpọ kikun (!) lori rẹ da lori kaṣe ti a gba.

Lati lo Makiro, lo bọtini Ipilẹ Visual lori taabu developer (Olùgbéejáde) tabi ọna abuja keyboard alt+F11. Ki o si a fi titun kan sofo module nipasẹ awọn akojọ Fi sii - Module ati daakọ koodu atẹle nibẹ:

Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() As String Dim objPivotCache As PivotCache Dim objRS Bi Nkan Dim ResultSheetName As Okun Dim Sheets Names As Variant 'sheet name ibi ti awọn Abajade pivot yoo han Abajade "SheetName Sheets" awọn orukọ pẹlu awọn tabili orisun Awọn orukọ Sheets = Array ("Alpha", "Beta", "Gamma", "Delta") 'a ṣe kaṣe kan fun awọn tabili lati awọn iwe-ipamọ lati awọn orukọ Sheets Pẹlu ActiveWorkbook ReDim arSQL(1 Si (UBound(Sheets Names)) + 1) ) Fun i = LBound (Awọn orukọ Sheets) Si UBound(Awọn orukọ Sheets) arSQL(i + 1) = "Yan * LATI [" & Awọn orukọ Sheets(i) & "$]" Nigbamii i Ṣeto objRS = CreateObject("ADODB.Recordset") objRS .Ṣí Darapọ̀ $( arSQL, " UNION ALL"), _ Darapọ̀ mọ́$(Array(" Olupese=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) Pari Pẹlu 'tun ṣe iwe naa lati ṣe afihan tabili pivot Abajade Lori Aṣiṣe Pada Ohun elo Next.DisplayAlerts = Awọn iwe iṣẹ-ṣiṣe eke(ResultSheetName) .Paarẹ Ṣeto wsPivot = Awọn iṣẹ-ṣiṣe.Fi wsPivot. Orukọ = ResultSheetName 'ṣafihan akopọ kaṣe ti o ti ipilẹṣẹ lori iwe yii Ṣeto objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Ṣeto objPivotCache.Recordset = objRS Ṣeto objRS = Ko si Nkankan Pẹlu wsPivot objPivotCache.Ṣẹda tabiliPivot objPivotCache = Ko si Ohunkan Ibiti("A3").Yan Ipari Pẹlu Ipin Ipari    

Makiro ti o pari le lẹhinna ṣiṣẹ pẹlu ọna abuja keyboard kan alt+F8 tabi awọn Macros bọtini lori taabu developer (Olùgbéejáde - Macros).

Awọn alailanfani ti ọna yii:

• Data naa ko ni imudojuiwọn nitori kaṣe ko ni asopọ si awọn tabili orisun. Ti o ba yi data orisun pada, o gbọdọ tun macro ṣiṣẹ lẹẹkansi ki o tun kọ akopọ naa lẹẹkansi.
• Nigbati o ba yipada nọmba awọn iwe, o jẹ dandan lati ṣatunkọ koodu macro (array Awọn Orukọ Sheet).
  

Ṣugbọn ni ipari a gba tabili pivot kikun ti o ni kikun, ti a ṣe lori ọpọlọpọ awọn sakani lati oriṣiriṣi awọn aṣọ:

Voilà!

Akọsilẹ imọ-ẹrọ: ti o ba gba aṣiṣe bii “Olupese ko forukọsilẹ” nigbati o nṣiṣẹ Makiro, lẹhinna o ṣeese o ni ẹya 64-bit ti Excel tabi ẹya ti ko pe ti Office ti fi sii (ko si Wiwọle). Lati ṣatunṣe ipo naa, rọpo ajẹkù ninu koodu macro:

	 Olupese=Microsoft.Jet.OLEDB.4.0;  

to:

	Olupese=Microsoft.ACE.OLEDB.12.0;  

Ati ṣe igbasilẹ ati fi ẹrọ ṣiṣe data ọfẹ sori ẹrọ lati Wiwọle lati oju opo wẹẹbu Microsoft – Microsoft Access Database Engine 2010 Redistributable

Ọna 3: Ṣepọ Oluṣeto PivotTable lati Awọn ẹya atijọ ti Excel

Ọna yii jẹ igba atijọ, ṣugbọn tun tọ lati darukọ. Ni sisọ ni deede, ni gbogbo awọn ẹya titi di ọdun 2003, aṣayan wa ninu Oluṣeto PivotTable lati “kọ agbepo kan fun ọpọlọpọ awọn sakani isọdọkan”. Bibẹẹkọ, ijabọ ti a ṣe ni ọna yii, laanu, yoo jẹ aibanujẹ alaanu ti akopọ kikun-kikun gidi ati pe ko ṣe atilẹyin ọpọlọpọ awọn “awọn eerun” ti awọn tabili pivot ti aṣa:

Ni iru pivot, ko si awọn akọle ọwọn ninu atokọ aaye, ko si eto eto rọ, ṣeto awọn iṣẹ ti a lo ni opin, ati, ni gbogbogbo, gbogbo eyi ko jọra pupọ si tabili pivot. Boya idi niyẹn, ti o bẹrẹ ni ọdun 2007, Microsoft yọ iṣẹ yii kuro ni ibaraẹnisọrọ boṣewa nigbati o ṣẹda awọn ijabọ tabili pivot. Bayi ẹya yii wa nikan nipasẹ bọtini aṣa kan PivotTable oso(Olumọ Tabili Pivot), eyiti, ti o ba fẹ, le ṣe afikun si Ọpa Wiwọle Yara nipasẹ Faili – Awọn aṣayan – Ṣe akanṣe Ọpa Wiwọle ni iyara – Gbogbo Awọn aṣẹ (Faili - Awọn aṣayan - Ṣe akanṣe Ọpa Wiwọle ni iyara - Gbogbo Awọn aṣẹ):

Lẹhin titẹ lori bọtini ti a ṣafikun, o nilo lati yan aṣayan ti o yẹ ni igbesẹ akọkọ ti oluṣeto naa:

Ati lẹhinna ni window atẹle, yan iwọn kọọkan ni titan ki o ṣafikun si atokọ gbogbogbo:

Ṣugbọn, lẹẹkansi, eyi kii ṣe akojọpọ kikun, nitorinaa ma ṣe nireti pupọ lati ọdọ rẹ. Mo le ṣeduro aṣayan yii nikan ni awọn ọran ti o rọrun pupọ.

• Ṣiṣẹda Iroyin pẹlu PivotTables
• Ṣeto awọn iṣiro ni PivotTables
• Kini macros, bii o ṣe le lo wọn, nibo ni lati da koodu VBA, ati bẹbẹ lọ.
• Gbigba data lati ọpọlọpọ awọn iwe si ọkan (afikun PLEX)