Snúningstafla yfir mörg gagnasvið

Mótun vandans

Pivot töflur eru eitt af ótrúlegustu verkfærunum í Excel. En enn sem komið er, því miður, getur engin útgáfa af Excel gert svo einfaldan og nauðsynlegan hlut á flugi eins og að búa til samantekt fyrir nokkur upphafsgagnasvið sem staðsett eru, til dæmis á mismunandi blöðum eða í mismunandi töflum:

Áður en við byrjum skulum við skýra nokkur atriði. Fyrirfram tel ég að eftirfarandi skilyrði séu uppfyllt í gögnum okkar:

• Töflur geta haft hvaða fjölda lína sem er með hvaða gögnum sem er, en þær verða að hafa sama haus.
• Engin aukagögn ættu að vera á blöðunum með upprunatöflum. Eitt blað – eitt borð. Til að stjórna ráðlegg ég þér að nota flýtilykla Ctrl+Enda, sem færir þig í síðasta notaða reitinn í vinnublaðinu. Helst ætti þetta að vera síðasta hólfið í gagnatöflunni. Ef þegar þú smellir á Ctrl+Enda allir tómir reiti til hægri eða fyrir neðan töfluna eru auðkenndir – eyddu þessum tómu dálkum til hægri eða línum fyrir neðan töfluna eftir töfluna og vistaðu skrána.

Aðferð 1: Búðu til töflur fyrir pivot með Power Query

Frá og með 2010 útgáfunni fyrir Excel, er ókeypis Power Query viðbót sem getur safnað og umbreytt hvaða gögnum sem er og síðan gefið þau sem uppsprettu til að byggja upp snúningstöflu. Að leysa vandamál okkar með hjálp þessarar viðbótar er alls ekki erfitt.

Fyrst skulum við búa til nýja tóma skrá í Excel - samsetning fer fram í henni og síðan verður til snúningstafla í henni.

Síðan á flipanum Gögn (ef þú ert með Excel 2016 eða nýrri) eða á flipanum Orkufyrirspurn (ef þú ert með Excel 2010-2013) veldu skipunina Búðu til fyrirspurn - Úr skrá - Excel (Fáðu gögn - úr skrá - Excel) og tilgreindu upprunaskrána með töflunum sem á að safna:

Í glugganum sem birtist velurðu hvaða blað sem er (það skiptir ekki máli hvaða) og ýttu á hnappinn fyrir neðan Breyta (Breyta):

Power Query Query Editor glugginn ætti að opnast ofan á Excel. Hægra megin við gluggann á spjaldinu Beiðni um færibreytur eyða öllum sjálfkrafa búnum skrefum nema fyrstu - Heimild (Heimild):

Nú sjáum við almennan lista yfir öll blöð. Ef til viðbótar við gagnablöð eru önnur hliðarblöð í skránni, þá er verkefni okkar í þessu skrefi að velja aðeins þau blöð sem þarf að hlaða upplýsingar úr, að undanskildum öllum öðrum sem nota síuna í töfluhausnum:

Eyða öllum dálkum nema dálki Gögnmeð því að hægrismella á dálkafyrirsögn og velja Eyða öðrum dálkum (Fjarlægja aðrir dálkar):

Þú getur síðan stækkað innihald safnaðra taflna með því að smella á tvöfalda örina efst í dálknum (gátreitur Notaðu upprunalega dálknafnið sem forskeyti þú getur slökkt á því):

Ef þú gerðir allt rétt, þá ættirðu á þessum tímapunkti að sjá innihald allra taflna sem safnað er fyrir neðan hverja aðra:

Það er eftir að hækka fyrstu röðina í töfluhausinn með hnappinum Notaðu fyrstu línu sem haus (Notaðu fyrstu línu sem haus) flipi Heim (Heim) og fjarlægðu afrit töfluhausa úr gögnunum með því að nota síu:

Vistaðu allt sem gert er með skipuninni Lokaðu og hlaða - Lokaðu og hlaðaðu inn... (Loka og hlaða — Loka og hlaða til...) flipi Heim (Heim), og veldu valkostinn í glugganum sem opnast Aðeins tenging (Aðeins tenging):

Allt. Það er aðeins eftir að búa til samantekt. Til að gera þetta, farðu í flipann Setja inn - PivotTable (Setja inn - snúningstafla), veldu valkostinn Notaðu ytri gagnagjafa (Notaðu ytri gagnagjafa)og síðan með því að smella á hnappinn Veldu tengingu, beiðni okkar. Frekari sköpun og stillingar snúningsins á sér stað á algjörlega staðlaðan hátt með því að draga reitina sem við þurfum inn á línurnar, dálkana og gildissvæðið:

Ef upprunagögnin breytast í framtíðinni eða nokkrum fleiri verslunarblöðum er bætt við, þá er nóg að uppfæra fyrirspurnina og samantekt okkar með skipuninni Endurnýjaðu allt flipi Gögn (Gögn — endurnýja allt).

Aðferð 2. Við sameinum töflur með UNION SQL skipuninni í fjölvi

Önnur lausn á vandamálinu okkar er táknuð með þessu fjölvi, sem býr til gagnasett (skyndiminni) fyrir snúningstöfluna með því að nota skipunina UNITY SQL fyrirspurnartungumál. Þessi skipun sameinar töflur frá öllum tilgreindum í fylkinu Blaðnöfn blöð af bókinni í eina gagnatöflu. Það er, í stað þess að afrita og líma líkamlega á bilinu frá mismunandi blöðum til eins, gerum við það sama í vinnsluminni tölvunnar. Síðan bætir fjölvi við nýju blaði með uppgefnu nafni (breytu ResultSheetName) og býr til fullgilda (!) samantekt á því byggt á safnað skyndiminni.

Til að nota fjölvi, notaðu Visual Basic hnappinn á flipanum verktaki (hönnuður) eða flýtilykla Alt+F11. Síðan setjum við nýja tóma einingu í gegnum valmyndina Settu inn - Eining og afritaðu eftirfarandi kóða þangað:

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 'blaðheiti þar sem pivoturinn sem myndast verður sýndur ResultSheetName = "Pivot of sheet" nöfn með upprunatöflum SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'við myndum skyndiminni fyrir töflur úr blöðum frá SheetsNames Með ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) Fyrir i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * 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 'endurbúið blaðið til að birta snúningstöfluna sem myndast. Við villu Halda áfram næsta forriti.DisplayAlerts = False vinnublöð(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo t. Name = ResultSheetName 'birta myndaða skyndiminni samantektina á þessu blaði Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Ekkert með wsPivot objPivotCache.CreatePivotCache.CreatePivotT":TangablePstinivtT":R objPivotCache = Nothing Range("A3").Veldu End With End Sub    

Síðan er hægt að keyra fullunna fjölvi með flýtilykla Alt+F8 eða Macros hnappinn á flipanum verktaki (Hönnuður - Fjölvi).

Gallar við þessa nálgun:

• Gögnin eru ekki uppfærð vegna þess að skyndiminni hefur enga tengingu við upprunatöflurnar. Ef þú breytir upprunagögnunum verður þú að keyra fjölvi aftur og búa til samantektina aftur.
• Þegar fjöldi blaða er breytt er nauðsynlegt að breyta fjölkóðanum (array Blaðnöfn).
  

En á endanum fáum við alvöru snúningsborð, byggt á nokkrum sviðum frá mismunandi blöðum:

Voilà!

Tæknileg athugasemd: ef þú færð villu eins og „Tilfangi ekki skráður“ þegar þú keyrir fjölvi, þá ertu líklega með 64-bita útgáfu af Excel eða ófullkomin útgáfa af Office er uppsett (enginn aðgangur). Til að laga ástandið skaltu skipta um brot í fjölkóðanum:

	 Provider=Microsoft.Jet.OLEDB.4.0;  

að:

	Provider=Microsoft.ACE.OLEDB.12.0;  

Og halaðu niður og settu upp ókeypis gagnavinnsluvélina frá Access af vefsíðu Microsoft - Microsoft Access Database Engine 2010 Redistributable

Aðferð 3: Sameinaðu PivotTable Wizard úr gömlum útgáfum af Excel

Þessi aðferð er svolítið gamaldags en samt vert að nefna. Formlega séð, í öllum útgáfum til og með 2003, var möguleiki í PivotTable Wizard að „smíða snúning fyrir nokkur samstæðusvið“. Hins vegar mun skýrsla sem er smíðuð á þennan hátt, því miður, aðeins vera aumkunarverður svipur á raunverulegri samantekt og styður ekki marga af „flögum“ hefðbundinna snúningsborða:

Í slíkum pivot eru engar dálkafyrirsagnir í reitalistanum, það er engin sveigjanleg uppbyggingarstilling, mengi aðgerða sem notuð eru er takmörkuð og almennt er þetta allt ekki mjög svipað og pivot-töflu. Kannski er það ástæðan fyrir því, að frá og með 2007, fjarlægði Microsoft þessa aðgerð úr venjulegu glugganum þegar búið var til snúningstöfluskýrslur. Nú er þessi eiginleiki aðeins fáanlegur með sérsniðnum hnappi PivotTable Wizard(Pivot Table Wizard), sem, ef þess er óskað, er hægt að bæta við Quick Access Toolbar í gegnum Skrá – Valkostir – Sérsníða Quick Access Toolbar – Allar skipanir (Skrá — Valkostir — Sérsníða tækjastiku fyrir skjótan aðgang — Allar skipanir):

Eftir að hafa smellt á hnappinn sem bætt er við þarftu að velja viðeigandi valkost í fyrsta skrefi töframannsins:

Og síðan í næsta glugga, veldu hvert svið fyrir sig og bættu því við almenna listann:

En aftur, þetta er ekki fullgild samantekt, svo ekki búast við of miklu af henni. Ég get aðeins mælt með þessum valkosti í mjög einföldum tilvikum.

• Búa til skýrslur með PivotTables
• Settu upp útreikninga í PivotTables
• Hvað eru fjölvi, hvernig á að nota þau, hvar á að afrita VBA kóða o.s.frv.
• Gagnasöfnun úr mörgum blöðum í eitt (PLEX viðbót)