Pivote tab atravè plizyè seri done

Fòmasyon pwoblèm nan

Tablo pivot yo se youn nan zouti ki pi etonan nan Excel. Men, jiskaprezan, malerezman, pa youn nan vèsyon yo nan Excel ka fè yon bagay ki senp epi ki nesesè sou vole tankou bati yon rezime pou plizyè seri done inisyal ki sitiye, pou egzanp, sou fèy diferan oswa nan tab diferan:

Anvan nou kòmanse, ann klarifye yon koup de pwen. A priori, mwen kwè ke kondisyon sa yo satisfè nan done nou yo:

• Tablo yo ka gen nenpòt ki kantite ranje ak nenpòt done, men yo dwe gen menm tèt la.
• Pa ta dwe gen okenn done siplemantè sou fèy papye yo ak tab sous yo. Yon fèy - yon tab. Pou kontwole, mwen konseye w sèvi ak yon chemen kout klavye Ctrl+Fen, ki deplase ou nan dènye selil yo itilize nan fèy travay la. Idealman, sa a ta dwe dènye selil la nan tablo done a. Si lè ou klike sou Ctrl+Fen nenpòt selil vid ki sou bò dwat la oswa anba tab la make - efase kolòn vid sa yo sou bò dwat la oswa ranje anba tab la apre tab la epi sove fichye a.

Metòd 1: Bati tab pou yon pivot lè l sèvi avèk Power Query

Apati vèsyon 2010 pou Excel, gen yon sipleman Power Query gratis ki ka kolekte ak transfòme nenpòt done epi bay li kòm yon sous pou bati yon tablo pivot. Rezoud pwoblèm nou an ak èd nan ajoute sa a pa difisil ditou.

Premyèman, se pou yo kreye yon nouvo dosye vid nan Excel - asanble pral pran plas nan li epi Lè sa a, yo pral kreye yon tab pivot nan li.

Lè sa a, sou tab la Done (si ou gen Excel 2016 oswa pita) oswa sou tab la Pouvwa rechèch (si ou gen Excel 2010-2013) chwazi lòd la Kreye Rekèt - Soti nan File - Excel (Jwenn Done - Soti nan fichye - Excel) epi presize fichye sous la ak tab yo dwe kolekte:

Nan fennèt ki parèt, chwazi nenpòt fèy (pa gen pwoblèm kiyès) epi peze bouton ki anba a Chanje (Edit):

Fenèt Editè Power Query Query ta dwe louvri sou tèt Excel. Sou bò dwat fenèt la sou panèl la Mande Paramèt efase tout etap ki kreye otomatikman eksepte premye a - sous (Sous):

Koulye a, nou wè yon lis jeneral nan tout fèy papye. Si anplis fichye done yo gen kèk lòt fèy bò nan dosye a, Lè sa a, nan etap sa a, travay nou an se chwazi sèlman fèy papye sa yo nan ki enfòmasyon yo bezwen chaje, eksepte tout lòt yo lè l sèvi avèk filtè a nan tèt tab la:

Efase tout kolòn eksepte kolòn Donepa klike dwa sou yon tit kolòn epi chwazi Efase lòt kolòn yo (Retire lòt kolòn):

Lè sa a, ou ka elaji sa ki nan tablo yo kolekte lè w klike sou doub flèch ki anlè kolòn nan (kaz Sèvi ak non kolòn orijinal kòm prefiks ou ka fèmen li):

Si ou te fè tout bagay kòrèkteman, Lè sa a, nan pwen sa a ou ta dwe wè sa ki nan tout tab yo kolekte youn anba lòt la:

Li rete ogmante premye ranje a nan header tab la ak bouton an Sèvi ak premye liy kòm headers (Sèvi ak premye ranje kòm tèt) tab Akèy (Kay) epi retire tèt tab kopi nan done yo lè l sèvi avèk yon filtè:

Sove tout bagay ki fèt ak lòd la Fèmen epi chaje - Fèmen epi chaje nan... (Fèmen & Chaje — Fèmen & Chaje nan...) tab Akèy (Kay), ak nan fenèt la ki ouvè, chwazi opsyon an Koneksyon sèlman (Koneksyon sèlman):

Tout bagay. Li rete sèlman pou konstwi yon rezime. Pou fè sa, ale nan tab la Mete - PivotTable (Antre — Tablo Pivote), chwazi opsyon an Sèvi ak sous done ekstèn (Itilize sous done ekstèn)ak Lè sa a, klike sou bouton an Chwazi koneksyon, demann nou an. Plis kreyasyon ak konfigirasyon pivot la fèt nan yon fason konplètman estanda pa trenen jaden nou bezwen yo nan ranje yo, kolòn ak valè zòn:

Si done sous yo chanje nan tan kap vini an oswa kèk plis fèy magazen yo ajoute, Lè sa a, li pral ase yo mete ajou rechèch la ak rezime nou an lè l sèvi avèk lòd la. Rafrechi tout tab Done (Done — rafrechi tout).

Metòd 2. Nou ini tab ak kòmand UNION SQL nan yon makro

Yon lòt solisyon a pwoblèm nou an reprezante pa macro sa a, ki kreye yon seri done (kachèt) pou tab pivot la lè l sèvi avèk lòd la. UNITY Lang rekèt SQL. Kòmand sa a konbine tab soti nan tout espesifye nan etalaj la SheetNames fèy liv la nan yon tab done sèl. Sa vle di, olye pou yo kopye fizikman ak kole chenn soti nan fèy diferan nan yon sèl, nou fè menm bagay la nan RAM òdinatè a. Lè sa a, macro a ajoute yon nouvo fèy ak non yo bay (varyab ResultSheetName) epi li kreye yon rezime konplè (!) sou li ki baze sou kachèt yo kolekte.

Pou itilize yon makro, sèvi ak bouton Visual Basic sou tab la pwomotè (Devlopè) oswa rakoursi klavye Alt+F11. Lè sa a, nou mete yon nouvo modil vid nan meni an Mete – Modil epi kopye kòd sa a la:

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 'non fèy kote yo pral parèt Pivot ResultSheetName = "Pivot sheet ". non ak tab sous SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'nou fòme yon kachèt pou tab soti nan fèy ki soti nan SheetsNames Avèk ActiveWorkbook ReDim arSQL (1 Pou (UBound(SheetsNames) + 1) ) Pou i = LBound (SheetsNames) Pou UBound(SheetsNames) arSQL(i + 1) = "CHWAZI * FROM [" & SheetsNames(i) & "$]" Next 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 ) End With 're-kreye fèy la pou montre tablo pivote ki kapab lakòz sou erè Rezime Pwochen aplikasyon.DisplayAlerts = Fo Fèy travay (ResultSheetName).Efase Mete wsPivot = Fichye.Ajoute wsPivo t. Name = ResultSheetName 'montre rezime kachèt ki te pwodwi a sou fèy sa a Mete objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Mete objPivotCache.Recordset = objRS Mete objRS = Pa gen anyen ak wsPivot objPivotCache.TablewPivotCache.TableWorkbook. objPivotCache = Nothing Range ("A3"). Chwazi Fen ak End Sub    

Lè sa a, macro fini a ka kouri ak yon chemen kout klavye Alt+F8 oswa bouton Macros sou tab la pwomotè (Devlopè — Macro).

Dezavantaj apwòch sa a:

• Done yo pa mete ajou paske kachèt la pa gen okenn koneksyon ak tab sous yo. Si ou chanje done sous yo, ou dwe kouri makro a ankò epi bati rezime a ankò.
• Lè w ap chanje kantite fèy papye yo, li nesesè pou modifye kòd makro a (etalaj SheetNames).
  

Men, nan fen a nou jwenn yon reyèl tab pivote plen véritable, bati sou plizyè chenn soti nan fèy diferan:

Voilà!

Nòt teknik: si ou jwenn yon erè tankou "Founisè pa anrejistre" lè w ap kouri makro a, gen plis chans ou gen yon vèsyon 64-bit nan Excel oswa yon vèsyon enkonplè nan Office enstale (pa gen aksè). Pou ranje sitiyasyon an, ranplase fragman an nan kòd la macro:

	 Founisè=Microsoft.Jet.OLEDB.4.0;  

nan:

	Founisè=Microsoft.ACE.OLEDB.12.0;  

Epi telechaje epi enstale motè tretman done gratis ki soti nan Access nan sit entènèt Microsoft - Microsoft Access Database Engine 2010 Redistributable.

Metòd 3: Konsolide PivotTable Sòsye soti nan ansyen vèsyon Excel

Metòd sa a se yon ti kras demode, men li toujou vo mansyone. Fòmèlman pale, nan tout vèsyon jiska e ki gen ladan 2003, te gen yon opsyon nan PivotTable Sòsye a "konstwi yon pivot pou plizyè chenn konsolidasyon". Sepandan, yon rapò ki fèt nan fason sa a, malerezman, pral sèlman yon sanblab pitye nan yon rezime reyèl plen véritable epi li pa sipòte anpil nan "chips yo" nan tab pivot konvansyonèl yo:

Nan yon pivot konsa, pa gen okenn tit kolòn nan lis jaden an, pa gen okenn anviwònman estrikti fleksib, seri fonksyon yo itilize limite, epi, an jeneral, tout bagay sa yo pa sanble anpil ak yon tab pivot. Petèt se poutèt sa, apati 2007, Microsoft te retire fonksyon sa a nan dyalòg estanda a lè li te kreye rapò tab pivot. Koulye a, karakteristik sa a disponib sèlman atravè yon bouton koutim PivotTable Sòsye(Sòsye Tablo Pivote), ki, si ou vle, yo ka ajoute nan ba zouti Aksè rapid atravè File – Opsyon – Customize Quick Access Toolbar – Tout kòmandman (Fichiye — Opsyon — Customize Quick Access Toolbar — Tout kòmandman):

Apre klike sou bouton an ajoute, ou bezwen chwazi opsyon ki apwopriye a nan premye etap la nan sòsye an:

Lè sa a, nan pwochen fenèt la, chwazi chak seri nan vire epi ajoute li nan lis jeneral la:

Men, ankò, sa a se pa yon rezime konplè, kidonk pa atann twòp nan li. Mwen ka rekòmande opsyon sa a sèlman nan ka trè senp.

• Kreye Rapò ak PivotTables
• Mete kanpe kalkil yo nan PivotTables
• Ki sa ki makro, ki jan yo sèvi ak yo, ki kote yo kopye kòd VBA, elatriye.
• Koleksyon done ki soti nan plizyè fèy nan yon sèl (PLEX ajoute sou)