Tabel pangsi dina sababaraha rentang data

Rumusan masalah

Tabel pangsi mangrupikeun salah sahiji alat anu paling endah dina Excel. Tapi sajauh ieu, hanjakalna, teu aya versi Excel anu tiasa ngalakukeun hal anu saderhana sareng dipikabutuh dina laleur sapertos ngawangun kasimpulan sababaraha rentang data awal anu aya, contona, dina lembar anu béda atanapi dina tabel anu béda:

Sateuacan urang ngamimitian, hayu urang netelakeun sababaraha poin. A priori, kuring yakin yén kaayaan di handap ieu patepung dina data urang:

• Tabél tiasa gaduh sababaraha baris kalayan data naon waé, tapi kedah gaduh lulugu anu sami.
• Henteu kedah aya data tambahan dina lambaran sareng tabel sumber. Hiji lambar - hiji méja. Pikeun ngadalikeun, kuring mamatahan anjeun ngagunakeun potong kompas keyboard Ctrl+Tungtung, nu mindahkeun anjeun ka sél panungtungan dipaké dina LKS. Ideally, ieu kedah sél panungtungan dina tabel data. Lamun anjeun klik dina Ctrl+Tungtung sagala sél kosong ka katuhu atawa handap tabel disorot - pupus kolom kosong ieu ka katuhu atawa baris handap tabel sanggeus tabel sarta simpen file.

Métode 1: Ngawangun tabel pikeun pangsi nganggo Power Query

Mimitian ti versi 2010 pikeun Excel, aya tambihan Power Query gratis anu tiasa ngumpulkeun sareng ngarobih data naon waé teras masihan salaku sumber pikeun ngawangun méja pangsi. Ngarengsekeun masalah urang kalayan bantuan add-in ieu teu hésé pisan.

Kahiji, hayu urang nyieun hiji file kosong anyar dina Excel - assembly bakal lumangsung di dinya lajeng tabel pangsi bakal dijieun di dinya.

Lajeng dina tab data (upami anjeun gaduh Excel 2016 atanapi engké) atanapi dina tab Patarosan Kakuatan (lamun boga Excel 2010-2013) pilih paréntah Jieun Query - Tina File - Excel (Kéngingkeun Data - Tina file - Excel) sareng tangtukeun file sumber sareng tabel anu bakal dikumpulkeun:

Dina jandela anu némbongan, pilih lambaran mana waé (henteu masalah anu mana) sareng pencét tombol di handap ngarobah (Édit):

Jandéla Power Query Query Editor kedah muka dina luhureun Excel. Di sisi katuhu jandela dina panel Paraméter Nyungkeun Hapus sadaya léngkah anu didamel sacara otomatis kecuali anu kahiji - sumber (Sumber):

Ayeuna urang ningali daptar umum sadaya lembar. Upami salian ti lambaran data aya sababaraha lambaran sisi anu sanés dina file, maka dina léngkah ieu tugas urang nyaéta milih ngan ukur lambaran anu mana inpormasi anu kedah dimuat, henteu kalebet sadayana anu sanés nganggo saringan dina header tabel:

Pupus sadaya kolom iwal kolom dataku ngaklik katuhu hiji judul kolom tur milih Pupus kolom séjén (Leupaskeun kolom séjén):

Anjeun teras tiasa ngalegaan eusi tabel anu dikumpulkeun ku ngaklik panah ganda di luhur kolom (kotak centang). Paké ngaran kolom aslina salaku awalan anjeun tiasa mareuman):

Upami anjeun ngalakukeun sadayana leres, maka dina waktos ieu anjeun kedah ningali eusi sadaya tabel anu dikumpulkeun hiji di handap anu sanés:

Tetep ngangkat baris kahiji ka header méja kalayan tombol Paké baris kahiji salaku lulugu (Paké baris kahiji salaku lulugu) tab imah (Bumi) sareng cabut header tabel duplikat tina data nganggo saringan:

Simpen sagalana dipigawé ku paréntah Tutup sareng muat - Tutup sareng muatkeun… (Tutup & Muat - Tutup & Muat ka…) tab imah (Bumi), sareng dina jandela anu muka, pilih pilihan Sambungan wungkul (Sambungan wungkul):

Sagalana. Tetep ukur ngawangun kasimpulan. Jang ngalampahkeun ieu, buka tab Selapkeun - PivotTable (Selapkeun - Pivot Table), pilih pilihan Paké sumber data éksternal (Paké sumber data éksternal)lajeng ku ngaklik tombol Pilih sambungan, pamundut urang. Penciptaan sareng konfigurasi pangsi salajengna lumangsung dina cara anu lengkep standar ku cara nyéred widang anu urang peryogikeun kana barisan, kolom sareng daérah nilai:

Upami data sumberna robih di hareup atanapi sababaraha lembar toko deui ditambah, maka éta bakal cekap pikeun ngapdet pamundut sareng kasimpulan kami nganggo paréntah. Refresh sadayana tab data (Data - Refresh Sadaya).

Métode 2. Urang ngahijikeun tabel sareng paréntah UNION SQL dina makro

Solusi anu sanés pikeun masalah urang diwakilan ku makro ieu, anu nyiptakeun set data (cache) pikeun tabel pangsi nganggo paréntah. persatuan basa query SQL. Paréntah ieu ngagabungkeun tabel tina sadaya anu ditangtukeun dina susunan SheetNames lambar buku kana tabel data tunggal. Nyaéta, tinimbang nyalin sacara fisik sareng nempelkeun rentang ti lembar anu béda ka hiji, urang lakonan hal anu sami dina RAM komputer. Teras makro nambihan lambaran énggal kalayan nami anu dipasihkeun (variabel ResultSheetName) sarta nyieun hiji full-fledged (!) kasimpulan dina eta dumasar kana cache dikumpulkeun.

Pikeun ngagunakeun makro, paké tombol Visual Basic dina tab pamekar (Pamekar) atawa potong kompas keyboard Alt+F11. Teras we selapkeun modul kosong anyar ngaliwatan menu nu Selapkeun - Modul sareng salin kodeu ieu di dinya:

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 'sheet name where the resulting pivot will be displayed" = "Pivot of arrayName" nami sareng tabel sumber SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'urang ngabentuk cache pikeun tabel tina lambaran tina SheetsNames With ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) Pikeun i = LBound (SheetsNames) Pikeun UBound (SheetsNames) arSQL (i + 1) = "PILIH * FROM [" & SheetsNames (i) & "$]" Salajengna i Set objRS = CreateObject ("ADODB.Recordset") objRS .Buka Gabung$( arSQL, " UNION ALL "), _ Gabung $(Asép Sunandar Sunarya ("Panyadia = Microsoft.Jet.OLEDB.4.0; Sumber Data = ", _ .FullName, "; Extended Properties = "" Excel 8.0;" ""), vbNullString ) End With 're-create the sheet to display the result pivot table On Error Resume Next Application.DisplayAlerts = Palsu Worksheets(ResultSheetName).Hapus Set wsPivot = Worksheets.Tambahkeun wsPivo t. Nami = ResultSheetName 'nampilkeun kasimpulan cache nu dihasilkeun dina lambar ieu Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Euweuh Jeung wsPivot objPivotCachet.CreatePivotCachet.CreatePivotCachet.CreatePivotCachet.TablePivotCachet.CreatePivotCache. objPivotCache = Euweuh Range ("A3").Pilih Tungtung Jeung Tungtung Sub    

Makro rengse lajeng bisa dijalankeun ku potong kompas keyboard Alt+F8 atawa tombol Macros dina tab pamekar (Pamekar - Macros).

Kontra pendekatan ieu:

• Data teu diropéa sabab cache teu boga sambungan kana tabel sumber. Upami anjeun ngarobih sumber data, anjeun kedah ngajalankeun makro deui sareng ngawangun kasimpulan deui.
• Nalika ngarobih jumlah lembar, anjeun kedah ngédit kode makro (array SheetNames).
  

Tapi tungtungna urang nampi tabel pangsi anu lengkep, diwangun dina sababaraha rentang tina lembar anu béda:

Voilà!

Catetan teknis: Upami anjeun nampi kasalahan sapertos "Panyadia teu kadaptar" nalika ngajalankeun makro, maka paling dipikaresep anjeun gaduh versi 64-bit Excel atanapi versi Office anu teu lengkep dipasang (henteu Aksés). Pikeun ngalereskeun kaayaan, ganti fragmen dina kode makro:

	 Panyadia=Microsoft.Jet.OLEDB.4.0;  

ka:

	Panyadia=Microsoft.ACE.OLEDB.12.0;  

Sareng unduh sareng pasang mesin ngolah data gratis tina Access tina situs wéb Microsoft - Microsoft Access Database Engine 2010 Redistributable

Métode 3: Konsolidasi PivotTable Wizard tina vérsi Old Excel

Metoda ieu saeutik luntur, tapi masih patut mentioning. Sacara resmi, dina sadaya vérsi dugi ka sareng kalebet 2003, aya pilihan dina PivotTable Wizard pikeun "ngawangun pangsi pikeun sababaraha rentang konsolidasi". Tapi, laporan anu diwangun ku cara ieu, hanjakalna, ngan bakal janten semblance pikasieuneun tina kasimpulan anu lengkep sareng henteu ngadukung seueur "chip" tabel pangsi konvensional:

Dina pangsi sapertos kitu, henteu aya judul kolom dina daptar lapangan, henteu aya setélan struktur anu fleksibel, set fungsi anu dianggo terbatas, sareng, sacara umum, sadayana ieu henteu sami sareng tabel pangsi. Panginten éta sababna, mimitian taun 2007, Microsoft ngaleungitkeun fungsi ieu tina dialog standar nalika nyiptakeun laporan tabel pangsi. Ayeuna fitur ieu ngan sadia ngaliwatan tombol custom PivotTable Wizard(Pivot Table Wizard), nu, upami hoyong, bisa ditambahkeun kana Toolbar Aksés Gancang via File - Pilihan - Sesuaikeun Toolbar Aksés Gancang - Sadaya Paréntah (File - Pilihan - Sesuaikeun Toolbar Aksés Gancang - Sadaya Paréntah):

Saatos ngaklik tombol tambihan, anjeun kedah milih pilihan anu pas dina léngkah munggaran wizard:

Teras dina jandela salajengna, pilih masing-masing rentang sareng tambahkeun kana daptar umum:

Tapi, sakali deui, ieu sanés kasimpulan lengkep, janten tong ngarep-ngarep teuing. Abdi tiasa nyarankeun pilihan ieu ngan dina kasus basajan pisan.

• Nyiptakeun Laporan nganggo PivotTables
• Nyetél itungan dina PivotTables
• Naon macros, kumaha carana make eta, dimana nyalin kodeu VBA, jsb.
• Koléksi data tina sababaraha lembar ka hiji (tambahan PLEX)