Rakurstabula vairākos datu diapazonos

Problēmas formulēšana

Rakurstabulas ir viens no pārsteidzošākajiem Excel rīkiem. Bet līdz šim diemžēl neviena no Excel versijām nevar lidojumā paveikt tik vienkāršu un vajadzīgu lietu kā kopsavilkuma izveidošana vairākiem sākotnējo datu diapazoniem, kas atrodas, piemēram, dažādās lapās vai dažādās tabulās:

Pirms sākam, noskaidrosim dažus punktus. A priori es uzskatu, ka mūsu datos ir izpildīti šādi nosacījumi:

• Tabulās var būt neierobežots skaits rindu ar jebkuriem datiem, taču tām ir jābūt vienai un tai pašai galvenei.
• Uz lapām ar avota tabulām nevajadzētu būt papildu datiem. Viena lapa – viens galds. Lai kontrolētu, iesaku izmantot īsinājumtaustiņus Ctrl+beigas, kas pārvieto jūs uz pēdējo izmantoto šūnu darblapā. Ideālā gadījumā tai vajadzētu būt datu tabulas pēdējai šūnai. Ja, noklikšķinot uz Ctrl+beigas tiek iezīmēta jebkura tukša šūna pa labi vai zem tabulas – izdzēsiet šīs tukšās kolonnas pa labi vai rindas zem tabulas aiz tabulas un saglabājiet failu.

1. metode. Veidojiet tabulas rakursam, izmantojot Power Query

Sākot ar 2010. gada versiju programmai Excel, ir pieejams bezmaksas Power Query pievienojumprogramma, kas var apkopot un pārveidot jebkurus datus un pēc tam izmantot tos kā rakurstabulas izveides avotu. Mūsu problēmas risināšana ar šīs pievienojumprogrammas palīdzību nepavisam nav grūta.

Vispirms izveidosim jaunu tukšu failu programmā Excel – tajā notiks montāža un tad tajā tiks izveidota pivot tabula.

Pēc tam cilnē Datums (ja jums ir Excel 2016 vai jaunāka versija) vai cilnē Jaudas vaicājums (ja jums ir Excel 2010–2013) atlasiet komandu Izveidot vaicājumu - no faila - Excel (Iegūt datus — no faila — Excel) un norādiet avota failu ar apkopojamām tabulām:

Parādītajā logā atlasiet jebkuru lapu (nav svarīgi, kuru) un nospiediet zemāk esošo pogu Mainīt (Edit):

Programmas Excel augšpusē ir jāatver Power Query Query Editor logs. Paneļa loga labajā pusē Pieprasīt parametrus dzēst visas automātiski izveidotās darbības, izņemot pirmo - avots (Avots):

Tagad mēs redzam vispārīgu visu lapu sarakstu. Ja papildus datu lapām failā ir vēl dažas sānu lapas, tad šajā solī mūsu uzdevums ir atlasīt tikai tās lapas, no kurām jāielādē informācija, visas pārējās izslēdzot, izmantojot tabulas galvenē esošo filtru:

Dzēst visas kolonnas, izņemot kolonnu Datumsar peles labo pogu noklikšķinot uz kolonnas virsraksta un atlasot Dzēst citas kolonnas (Noņemt citas kolonnas):

Pēc tam varat paplašināt savākto tabulu saturu, noklikšķinot uz dubultās bultiņas kolonnas augšpusē (izvēles rūtiņa Izmantojiet sākotnējo kolonnas nosaukumu kā prefiksu jūs varat to izslēgt):

Ja visu izdarījāt pareizi, tad šajā brīdī jums vajadzētu redzēt visu viena zem otras savākto tabulu saturu:

Atliek ar pogu pacelt pirmo rindu līdz tabulas galvenei Izmantojiet pirmo rindiņu kā galvenes (Izmantojiet pirmo rindu kā galvenes) tab Sākumlapa (Mājas) un noņemiet dublētās tabulas galvenes no datiem, izmantojot filtru:

Saglabājiet visu, kas paveikts ar komandu Aizvērt un ielādēt – aizvērt un ielādēt… (Aizvērt un ielādēt — aizvērt un ielādēt...) tab Sākumlapa (Mājas)un atvērtajā logā atlasiet opciju Tikai savienojums (Tikai savienojums):

Viss. Atliek tikai izveidot kopsavilkumu. Lai to izdarītu, dodieties uz cilni Ievietošana — PivotTable (Ievietot — rakurstabula), izvēlieties opciju Izmantojiet ārējo datu avotu (Izmantojiet ārējo datu avotu)un pēc tam noklikšķinot uz pogas Izvēlieties savienojumu, mūsu pieprasījums. Turpmāka rakursa izveide un konfigurēšana notiek pilnīgi standarta veidā, velkot mums nepieciešamos laukus rindu, kolonnu un vērtību apgabalā:

Ja avota dati nākotnē mainīsies vai tiks pievienotas vēl dažas veikala lapas, tad pietiks ar vaicājuma un mūsu kopsavilkuma atjaunināšanu, izmantojot komandu Atsvaidzināt visu tab Datums (Dati — atsvaidzināt visu).

2. metode. Mēs apvienojam tabulas ar UNION SQL komandu makro

Cits mūsu problēmas risinājums ir šis makro, kas izveido datu kopu (kešatmiņu) rakurstabulai, izmantojot komandu UNITY SQL vaicājumu valoda. Šī komanda apvieno tabulas no visām masīvā norādītajām tabulām Lapu nosaukumi grāmatas lapas vienā datu tabulā. Tas ir, tā vietā, lai fiziski kopētu un ielīmētu diapazonus no dažādām lapām uz vienu, mēs darām to pašu datora RAM. Pēc tam makro pievieno jaunu lapu ar norādīto nosaukumu (mainīgais ResultSheetName) un uz tā izveido pilnvērtīgu (!) kopsavilkumu, pamatojoties uz savākto kešatmiņu.

Lai izmantotu makro, izmantojiet Visual Basic pogu cilnē attīstītājs (Izstrādātājs) vai īsinājumtaustiņš cits+F11. Pēc tam izvēlnē ievietojam jaunu tukšu moduli Ievietot - modulis un nokopējiet tur šādu kodu:

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 'lapas nosaukums, kurā tiks parādīta masīva revolucionārā lapa =P nosaukumi ar avota tabulām SheetsNames = Array("Alfa", "Beta", "Gamma", "Delta") 'mēs veidojam kešatmiņu tabulām no lapām no SheetsNames ar ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1)) ) For i = LBound (SheetsNames) uz UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Tālāk 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 ) Beigas ar 'atkārtoti izveidojiet lapu, lai parādītu iegūto rakurstabulu Kļūdas gadījumā Atsākt nākamo lietojumprogrammu.DisplayAlerts = Viltus darblapas(ResultSheetName).Dzēst Iestatīt wsPivot = Darblapas.Pievienot wsPivo t. Name = ResultSheetName 'rādīt ģenerēto kešatmiņas kopsavilkumu šajā lapā Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Iestatīt objPivotCache.Recordset = objRS Iestatīt objRS = Nothing With wsPivot T obj. objPivotCache = Nothing Range ("A3"). Atlasiet Beigt ar beigu apakšpunktu    

Pēc tam gatavo makro var palaist, izmantojot īsinājumtaustiņu cits+F8 vai cilnes pogu Makro attīstītājs (Izstrādātājs — makro).

Šīs pieejas mīnusi:

• Dati netiek atjaunināti, jo kešatmiņai nav savienojuma ar avota tabulām. Ja maināt avota datus, makro ir jāpalaiž vēlreiz un vēlreiz jāizveido kopsavilkums.
• Mainot lapu skaitu, ir jārediģē makro kods (masīvs Lapu nosaukumi).
  

Bet galu galā mēs iegūstam īstu pilnvērtīgu šarnīra tabulu, kas veidota uz vairākiem diapazoniem no dažādām loksnēm:

Voilà!

Tehniska piezīme: ja, palaižot makro, tiek parādīta kļūda, piemēram, “Pakalpojumu sniedzējs nav reģistrēts”, visticamāk, jums ir Excel 64 bitu versija vai ir instalēta nepilnīga Office versija (bez piekļuves). Lai labotu situāciju, nomainiet fragmentu makro kodā:

	 Provider=Microsoft.Jet.OLEDB.4.0;  

līdz:

	Provider=Microsoft.ACE.OLEDB.12.0;  

Un lejupielādējiet un instalējiet bezmaksas datu apstrādes programmu no Access no Microsoft vietnes – Microsoft Access Database Engine 2010 Redistributable

3. metode. Rakurstabulas vedņa konsolidācija no vecām Excel versijām

Šī metode ir nedaudz novecojusi, taču joprojām ir vērts pieminēt. Formāli runājot, visās versijās līdz 2003. gadam ieskaitot, rakurstabulas vednī bija iespēja “izveidot rakursu vairākiem konsolidācijas diapazoniem”. Tomēr šādi konstruēts pārskats diemžēl būs tikai nožēlojams reāla pilnvērtīga kopsavilkuma izskats un neatbalsta daudzas parasto rakurstabulu “mikroshēmas”.

Šādā rakursā lauku sarakstā nav kolonnu virsrakstu, nav elastīga struktūras iestatījuma, izmantoto funkciju kopa ir ierobežota, un kopumā tas viss nav ļoti līdzīgs rakurstabulai. Iespējams, tāpēc, sākot no 2007. gada, Microsoft, veidojot rakurstabulas pārskatus, izņēma šo funkciju no standarta dialoglodziņa. Tagad šī funkcija ir pieejama tikai ar pielāgotu pogu Rakurstabulas vednis(Rakurstabulas vednis), kuru, ja vēlaties, var pievienot ātrās piekļuves rīkjoslai, izmantojot Fails – Opcijas – Pielāgot ātrās piekļuves rīkjoslu – Visas komandas (Fails — Opcijas — Pielāgot ātrās piekļuves rīkjoslu — Visas komandas):

Pēc noklikšķināšanas uz pievienotās pogas, vedņa pirmajā darbībā jāizvēlas atbilstošā opcija:

Un tad nākamajā logā atlasiet katru diapazonu pēc kārtas un pievienojiet to vispārīgajam sarakstam:

Bet, atkal, šis nav pilnvērtīgs kopsavilkums, tāpēc negaidiet no tā pārāk daudz. Šo iespēju varu ieteikt tikai ļoti vienkāršos gadījumos.

• Atskaišu izveide, izmantojot rakurstabulas
• Iestatiet aprēķinus rakurstabulās
• Kas ir makro, kā tos lietot, kur kopēt VBA kodu utt.
• Datu vākšana no vairākām lapām uz vienu (PLEX papildinājums)