Suvestinė lentelė keliuose duomenų diapazonuose

Problemos formulavimas

Pivot lentelės yra vienas nuostabiausių Excel įrankių. Tačiau iki šiol, deja, nė viena iš „Excel“ versijų negali greitai atlikti tokio paprasto ir būtino dalyko, kaip sudaryti kelių pradinių duomenų diapazonų, esančių, pavyzdžiui, skirtinguose lapuose ar skirtingose ​​lentelėse, suvestinę:

Prieš pradėdami, išsiaiškinkime keletą dalykų. A priori manau, kad mūsų duomenys atitinka šias sąlygas:

• Lentelėse gali būti bet koks skaičius eilučių su bet kokiais duomenimis, tačiau jos turi turėti tą pačią antraštę.
• Lapuose su šaltinio lentelėmis neturėtų būti jokių papildomų duomenų. Vienas lapas – vienas stalas. Norėdami valdyti, patariu naudoti spartųjį klavišą "Ctrl"+Galas, kuris perkelia jus į paskutinį naudotą langelį darbalapyje. Idealiu atveju tai turėtų būti paskutinis langelis duomenų lentelėje. Jei spustelėjus "Ctrl"+Galas bet kuris tuščias langelis dešinėje arba po lentele yra paryškintas – ištrinkite šiuos tuščius stulpelius dešinėje arba eilutes po lentele po lentele ir išsaugokite failą.

1 būdas: kurkite suvestines lenteles naudodami „Power Query“.

Nuo 2010 m. versijos, skirtos „Excel“, yra nemokamas „Power Query“ priedas, kuris gali rinkti ir transformuoti bet kokius duomenis, o tada pateikti juos kaip suvestinės lentelės kūrimo šaltinį. Išspręsti mūsų problemą naudojant šį priedą nėra sunku.

Pirmiausia sukurkime naują tuščią failą „Excel“ – jame vyks surinkimas, o tada bus sukurta pivot lentelė.

Tada skirtuke Duomenys (jei turite „Excel 2016“ ar naujesnę versiją) arba skirtuke „Power Query“ (jei turite Excel 2010–2013) pasirinkite komandą Sukurti užklausą – iš failo – „Excel“. (Gauti duomenis – iš failo – „Excel“) ir nurodykite šaltinio failą su rinktinomis lentelėmis:

Atsidariusiame lange pasirinkite bet kurį lapą (nesvarbu kurį) ir paspauskite žemiau esantį mygtuką pokytis (Redaguoti):

„Excel“ viršuje turėtų atsidaryti „Power Query Query Editor“ langas. Dešinėje skydelio lango pusėje Parametrų užklausa ištrinti visus automatiškai sukurtus veiksmus, išskyrus pirmąjį – Šaltinis (Šaltinis):

Dabar matome bendrą visų lapų sąrašą. Jei, be duomenų lapų, faile yra ir kitų šoninių lapų, tai šiuo žingsniu mūsų užduotis yra pasirinkti tik tuos lapus, iš kurių reikia įkelti informaciją, neįtraukiant visų kitų, naudojant lentelės antraštėje esantį filtrą:

Ištrinti visus stulpelius, išskyrus stulpelį Duomenysdešiniuoju pelės mygtuku spustelėdami stulpelio antraštę ir pasirinkdami Ištrinkite kitus stulpelius (Pašalinti kiti stulpeliai):

Tada galite išplėsti surinktų lentelių turinį spustelėdami dvigubą rodyklę stulpelio viršuje (žymės langelis Naudokite pradinį stulpelio pavadinimą kaip priešdėlį galite jį išjungti):

Jei viską padarėte teisingai, šiuo metu turėtumėte pamatyti visų lentelių, surinktų viena po kitos, turinį:

Belieka pakelti pirmąją eilutę į lentelės antraštę mygtuku Naudokite pirmąją eilutę kaip antraštes (Naudokite pirmąją eilutę kaip antraštes) kortelė Pagrindinis (Namai) ir pašalinkite pasikartojančias lentelės antraštes iš duomenų naudodami filtrą:

Išsaugokite viską, kas padaryta naudojant komandą Uždaryti ir įkelti – uždaryti ir įkelti… (Uždaryti ir įkelti – uždaryti ir įkelti į…) kortelė Pagrindinis (Namai)ir atsidariusiame lange pasirinkite parinktį Tik ryšys (Tik ryšys):

Viskas. Belieka tik sukurti santrauką. Norėdami tai padaryti, eikite į skirtuką Įterpimas – PivotTable (Įterpti – Suvestinė lentelė), pasirinkite parinktį Naudokite išorinį duomenų šaltinį (Naudokite išorinį duomenų šaltinį)ir tada spustelėdami mygtuką Pasirinkite ryšį, mūsų prašymas. Tolesnis sukimosi kūrimas ir konfigūravimas vyksta visiškai standartiniu būdu, vilkdami mums reikalingus laukus į eilučių, stulpelių ir reikšmių sritį:

Jei ateityje pasikeis šaltinio duomenys arba bus pridėta dar keletas parduotuvių lapų, pakaks atnaujinti užklausą ir mūsų suvestinę naudojant komandą Atnaujinti viską kortelė Duomenys (Duomenys – Atnaujinti viską).

2 būdas. Sujungiame lenteles su UNION SQL komanda makrokomandoje

Kitas mūsų problemos sprendimas pateikiamas naudojant šią makrokomandą, kuri sukuria duomenų rinkinį (talpyklą) suvestinei lentelei naudojant komandą VIENYBĖ SQL užklausos kalba. Ši komanda sujungia lenteles iš visų nurodytų masyve Lapų pavadinimai knygos lapus į vieną duomenų lentelę. Tai yra, užuot fiziškai nukopijavę ir įklijuodami diapazonus iš skirtingų lapų į vieną, tą patį darome kompiuterio RAM. Tada makrokomanda prideda naują lapą su nurodytu pavadinimu (kintamuoju ResultSheetName) ir pagal surinktą talpyklą sukuria visavertę (!) santrauką.

Norėdami naudoti makrokomandą, naudokite skirtuke esantį mygtuką Visual Basic ryškalas (Programuotojas) arba spartusis klavišas Kitas+F11. Tada per meniu įterpiame naują tuščią modulį Įterpimas – modulis ir nukopijuokite ten šį kodą:

Sub New_Multi_Table_Pivot() Dim i As Long Dim arSQL() As String Dim objPivotCache As PivotCache Dim objRS Kaip Object Dim ResultSheetName As String Dim SheetsNames Kaip Varianto lapo pavadinimas, kuriame bus rodomas masyvas pavadinimai su šaltinio lentelėmis SheetsNames = Masyvas("Alfa", "Beta", "Gama", "Delta") 'sudarome lentelių talpyklą iš lapų iš SheetsNames su ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1)) ) Jei i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Kitas 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 ) Pabaiga su 'iš naujo sukurkite lapą, kad būtų rodoma gauta suvestinė lentelė Įvykus klaidai Tęsti kitą programą.DisplayAlerts = Klaidingi darbalapiai(ResultSheetName).Ištrinti Nustatyti wsPivot = Darbalapiai.Pridėti wsPivo t. Name = ResultSheetName 'parodykite sugeneruotą talpyklos suvestinę šiame lape Nustatyti objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Nustatyti objPivotCache.Recordset = objRS Nustatyti objRS = Nieko Su wsPivot T objanant3A"CreateWorkCache" Set. objPivotCache = Nothing Range ("A3"). Pasirinkite End With End Sub    

Tada baigtą makrokomandą galima paleisti sparčiuoju klavišu Kitas+F8 arba skirtuke esantį mygtuką Makrokomandos ryškalas (Kūrėjas – makrokomandos).

Šio metodo trūkumai:

• Duomenys neatnaujinami, nes talpykla neturi ryšio su šaltinio lentelėmis. Jei pakeisite šaltinio duomenis, turėsite dar kartą paleisti makrokomandą ir dar kartą sudaryti suvestinę.
• Keičiant lapų skaičių, būtina redaguoti makrokomandą (masyvas Lapų pavadinimai).
  

Bet galų gale gauname tikrą visavertę sukimosi lentelę, pagamintą iš kelių diapazonų iš skirtingų lapų:

Čia yra!

Techninė pastaba: jei paleisdami makrokomandą gaunate klaidą, pvz., „Teikėjas neregistruotas“, greičiausiai turite 64 bitų „Excel“ versiją arba įdiegta neužbaigta „Office“ versija (nėra prieigos). Norėdami ištaisyti situaciją, pakeiskite fragmentą makrokode:

	 Teikėjas=Microsoft.Jet.OLEDB.4.0;  

į:

	Teikėjas=Microsoft.ACE.OLEDB.12.0;  

Atsisiųskite ir įdiekite nemokamą duomenų apdorojimo variklį iš „Access“ iš „Microsoft“ svetainės – „Microsoft Access Database Engine 2010 Redistributable“

3 būdas: konsoliduokite „PivotTable“ vedlį iš senų „Excel“ versijų

Šis metodas yra šiek tiek pasenęs, bet vis tiek vertas dėmesio. Formaliai kalbant, visose versijose iki 2003 m. imtinai „PivotTable Wizard“ buvo parinktis „sukurti kelių konsolidavimo diapazonų sukimąsi“. Tačiau tokiu būdu sukurta ataskaita, deja, bus tik apgailėtina tikros visavertės santraukos įvaizdis ir nepalaiko daugelio įprastų suvestinių lentelių „lustų“:

Tokiame pivote laukų sąraše nėra stulpelių antraščių, nėra lankstaus struktūros nustatymo, naudojamų funkcijų rinkinys yra ribotas ir apskritai visa tai nėra labai panašu į suvestinę lentelę. Galbūt todėl nuo 2007 m. Microsoft pašalino šią funkciją iš standartinio dialogo lango, kurdama suvestinės lentelės ataskaitas. Dabar ši funkcija pasiekiama tik naudojant pasirinktinį mygtuką „PivotTable“ vedlys(Pivot Table Wizard), kuri, jei pageidaujama, gali būti įtraukta į greitosios prieigos įrankių juostą per Failas – Parinktys – Tinkinti greitosios prieigos įrankių juostą – Visos komandos (Failas – Parinktys – Tinkinti greitosios prieigos įrankių juostą – Visos komandos):

Spustelėję pridėtą mygtuką, pirmame vedlio veiksme turite pasirinkti tinkamą parinktį:

Tada kitame lange pasirinkite kiekvieną diapazoną paeiliui ir įtraukite jį į bendrą sąrašą:

Bet vėlgi, tai nėra visavertė santrauka, todėl per daug iš jos nesitikėk. Šį variantą galiu rekomenduoti tik labai paprastais atvejais.

• Ataskaitų kūrimas naudojant „PivotTable“.
• Nustatykite skaičiavimus „PivotTables“.
• Kas yra makrokomandos, kaip jas naudoti, kur kopijuoti VBA kodą ir kt.
• Duomenų rinkimas iš kelių lapų į vieną (PLEX priedas)