Spiltabel oor veelvuldige datareekse

Formulering van die probleem

Spiltabelle is een van die wonderlikste gereedskap in Excel. Maar tot dusver kan ongelukkig nie een van die weergawes van Excel so 'n eenvoudige en noodsaaklike ding doen soos om 'n opsomming te bou vir verskeie aanvanklike datareekse wat byvoorbeeld op verskillende velle of in verskillende tabelle geleë is nie:

Voordat ons begin, kom ons verduidelik 'n paar punte. A priori glo ek dat die volgende voorwaardes in ons data nagekom word:

• Tabelle kan enige aantal rye met enige data hê, maar hulle moet dieselfde kop hê.
• Daar behoort geen ekstra data op die velle met brontabelle te wees nie. Een laken - een tafel. Om te beheer, raai ek jou aan om 'n sleutelbordkortpad te gebruik Ctrl+Einde, wat jou na die laaste gebruikte sel in die werkblad beweeg. Ideaal gesproke moet dit die laaste sel in die datatabel wees. As wanneer jy op klik Ctrl+Einde enige leë sel regs of onder die tabel word uitgelig – vee hierdie leë kolomme regs of rye onder die tabel na die tabel uit en stoor die lêer.

Metode 1: Bou tabelle vir 'n spilpunt met behulp van Power Query

Vanaf die 2010-weergawe vir Excel, is daar 'n gratis Power Query-byvoeging wat enige data kan versamel en transformeer en dit dan kan gee as 'n bron vir die bou van 'n spiltabel. Om ons probleem met behulp van hierdie byvoeging op te los, is glad nie moeilik nie.

Kom ons skep eers 'n nuwe leë lêer in Excel – samestelling sal daarin plaasvind en dan sal 'n spiltabel daarin geskep word.

Dan op die blad data (as jy Excel 2016 of later het) of op die blad Kragnavraag (as jy Excel 2010-2013 het) kies die opdrag Skep navraag - Van lêer - Excel (Kry data – van lêer – Excel) en spesifiseer die bronlêer met die tabelle wat ingesamel moet word:

In die venster wat verskyn, kies enige blad (dit maak nie saak watter een nie) en druk die knoppie hieronder verandering (Edit):

Die Power Query Query Editor-venster moet bo-op Excel oopmaak. Aan die regterkant van die venster op die paneel Versoek parameters verwyder alle stappe wat outomaties geskep is, behalwe die eerste – Bron (Bron):

Nou sien ons 'n algemene lys van alle velle. As daar bykomend tot datablaaie 'n paar ander syblaaie in die lêer is, dan is ons taak in hierdie stap om slegs daardie blaaie te kies waaruit inligting gelaai moet word, behalwe al die ander wat die filter in die tabelopskrif gebruik:

Vee alle kolomme behalwe kolom uit datadeur met die rechtermuisknop op 'n kolomopskrif te klik en te kies Vee ander kolomme uit (Verwyder ander kolomme):

U kan dan die inhoud van die versamelde tabelle uitbrei deur op die dubbelpyltjie aan die bokant van die kolom te klik (merkblokkie Gebruik oorspronklike kolomnaam as voorvoegsel jy kan dit afskakel):

As jy alles korrek gedoen het, moet jy op hierdie punt die inhoud van alle tabelle sien wat onder mekaar versamel is:

Dit bly om die eerste ry na die tabelkop te verhoog met die knoppie Gebruik eerste reël as opskrifte (Gebruik eerste ry as opskrifte) tab What is This (Tuis) en verwyder duplikaattabelopskrifte van die data deur 'n filter te gebruik:

Stoor alles wat met die opdrag gedoen is Maak toe en laai – Maak toe en laai in... (Maak toe en laai — Maak toe en laai na …) tab What is This (Tuis), en kies die opsie in die venster wat oopmaak Slegs verbinding (Slegs verbinding):

Alles. Dit bly net om 'n opsomming te bou. Om dit te doen, gaan na die blad Voeg in – draaitabel (Voeg in – Spiltabel), kies die opsie Gebruik eksterne databron (Gebruik eksterne databron)en dan deur op die knoppie te klik Kies verbinding, ons versoek. Verdere skepping en konfigurasie van die spilpunt vind plaas op 'n heeltemal standaard manier deur die velde wat ons benodig in die rye, kolomme en waardes te sleep:

As die brondata in die toekoms verander of nog 'n paar winkelblaaie bygevoeg word, sal dit genoeg wees om die navraag en ons opsomming op te dateer met die opdrag Verfris alles tab data (Data – Herlaai alles).

Metode 2. Ons verenig tabelle met die UNION SQL-opdrag in 'n makro

Nog 'n oplossing vir ons probleem word verteenwoordig deur hierdie makro, wat 'n datastel (kas) vir die spilpunttabel skep deur die opdrag te gebruik UNITY SQL-navraagtaal. Hierdie opdrag kombineer tabelle van almal wat in die skikking gespesifiseer is Bladname velle van die boek in 'n enkele datatabel. Dit wil sê, in plaas daarvan om fisies te kopieer en te plak wissel van verskillende velle na een, doen ons dieselfde in die rekenaar se RAM. Dan voeg die makro 'n nuwe blad by met die gegewe naam (veranderlike Resultaatbladnaam) en skep 'n volwaardige (!) opsomming daaroor gebaseer op die versamelde kas.

Om 'n makro te gebruik, gebruik die Visual Basic-knoppie op die blad ontwikkelaar (Ontwikkelaar) of sleutelbordkortpad alt+F11. Dan voeg ons 'n nuwe leë module deur die spyskaart in Voeg in – Module en kopieer die volgende kode daarheen:

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 se bladnaam waar die resulterende spilpunt vertoon sal word ResultSheetName = "Pivot" sy 'n skikking name met brontabelle SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'ons vorm 'n kas vir tabelle van blaaie van SheetsNames met ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) Vir i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Volgende i Stel 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 ) Eindig Met 'herskep die blad om die resulterende spiltabel te vertoon. By fout Hervat volgende toepassing.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo t. Name = ResultSheetName 'vertoon die gegenereerde kasopsomming op hierdie blad Stel objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Stel objPivotCache.Recordset = objRS Stel objRS = Niks met wsPivot objPivotCache.CreatePivotCache.CreatePivotT":TangablePivotT":R3 objPivotCache = Nothing Range("A3").Kies Einde Met Einde Sub    

Die voltooide makro kan dan met 'n sleutelbordkortpad uitgevoer word alt+F8 of die Makro's-knoppie op die oortjie ontwikkelaar (Ontwikkelaar - Makro's).

Nadele van hierdie benadering:

• Die data word nie opgedateer nie omdat die kas geen verbinding met die brontabelle het nie. As jy die brondata verander, moet jy die makro weer laat loop en die opsomming weer bou.
• Wanneer die aantal velle verander word, is dit nodig om die makro-kode (skikking Bladname).
  

Maar op die ou end kry ons 'n regte volwaardige spiltafel, gebou op verskeie reekse van verskillende velle:

Siedaar!

Tegniese nota: as jy 'n fout kry soos "Verskaffer nie geregistreer nie" wanneer jy die makro hardloop, dan het jy heel waarskynlik 'n 64-bis weergawe van Excel of 'n onvolledige weergawe van Office is geïnstalleer (geen toegang). Om die situasie reg te stel, vervang die fragment in die makro-kode:

	 Verskaffer=Microsoft.Jet.OLEDB.4.0;  

aan:

	Verskaffer=Microsoft.ACE.OLEDB.12.0;  

En laai en installeer die gratis dataverwerkingsenjin vanaf Access vanaf die Microsoft-webwerf – Microsoft Access Database Engine 2010 Redistributable

Metode 3: Konsolideer PivotTable Wizard vanaf ou weergawes van Excel

Hierdie metode is 'n bietjie verouderd, maar steeds die moeite werd om te noem. Formeel gesproke, in alle weergawes tot en met 2003, was daar 'n opsie in die PivotTable Wizard om "'n spilpunt vir verskeie konsolidasiereekse te bou". 'n Verslag wat op hierdie manier saamgestel is, sal ongelukkig net 'n jammerlike skyn wees van 'n ware volwaardige opsomming en ondersteun nie baie van die "skyfies" van konvensionele spiltafels nie:

In so 'n spilpunt is daar geen kolomopskrifte in die veldlys nie, daar is geen buigsame struktuurinstelling nie, die stel funksies wat gebruik word is beperk, en in die algemeen is dit alles nie baie soortgelyk aan 'n spiltabel nie. Miskien is dit hoekom Microsoft vanaf 2007 hierdie funksie uit die standaarddialoog verwyder het wanneer spiltabelverslae geskep word. Nou is hierdie kenmerk slegs beskikbaar deur 'n pasgemaakte knoppie PivotTable Wizard(Draaitabel Wizard), wat, indien verlang, by die Vinnige Toegang-nutsbalk gevoeg kan word via Lêer – Opsies – Pas Vinnige Toegang-nutsbalk aan – Alle opdragte (Lêer - Opsies - Pas Vinnige Toegang-nutsbalk aan - Alle opdragte):

Nadat u op die bygevoegde knoppie geklik het, moet u die toepaslike opsie by die eerste stap van die towenaar kies:

En dan in die volgende venster, kies elke reeks om die beurt en voeg dit by die algemene lys:

Maar, weereens, hierdie is nie 'n volwaardige opsomming nie, so moenie te veel daarvan verwag nie. Ek kan hierdie opsie slegs in baie eenvoudige gevalle aanbeveel.

• Skep verslae met draaitabelle
• Stel berekeninge in PivotTables op
• Wat is makro's, hoe om dit te gebruik, waar om VBA-kode te kopieer, ens.
• Data-insameling van veelvuldige velle na een (PLEX-byvoeging)