Tabel pangsi ing sawetara kisaran data

Formulasi masalah

Tabel pivot minangka salah sawijining alat sing paling apik ing Excel. Nanging nganti saiki, sayangé, ora ana versi Excel sing bisa nindakake prekara sing gampang lan perlu kaya nggawe ringkesan sawetara kisaran data awal sing ana, contone, ing macem-macem lembar utawa ing tabel sing beda:

Sadurunge miwiti, ayo njlentrehake sawetara poin. A priori, aku percaya yen kahanan ing ngisor iki ditemokake ing data kita:

• Tabel bisa duwe nomer baris karo data apa wae, nanging kudu duwe header sing padha.
• Mesthine ora ana data tambahan ing lembaran kanthi tabel sumber. Siji lembar - siji meja. Kanggo ngontrol, aku menehi saran supaya sampeyan nggunakake trabasan keyboard Ctrl+Akhir, sing mindhah sampeyan menyang sel sing pungkasan digunakake ing lembar kerja. Saenipun, iki kudu dadi sel pungkasan ing tabel data. Yen nalika sampeyan klik ing Ctrl+Akhir sembarang sel kosong ing sisih tengen utawa ngisor tabel disorot - mbusak kolom kosong iki ing sisih tengen utawa baris ngisor meja sawise tabel lan nyimpen file.

Cara 1: Gawe tabel kanggo poros nggunakake Power Query

Miwiti saka versi 2010 kanggo Excel, ana tambahan Power Query gratis sing bisa ngumpulake lan ngowahi data apa wae banjur menehi minangka sumber kanggo mbangun tabel poros. Ngatasi masalah kita kanthi bantuan tambahan iki ora angel.

Pisanan, ayo nggawe file kosong anyar ing Excel - perakitan bakal ditindakake banjur tabel pivot bakal digawe ing kono.

Banjur ing tab Data (yen sampeyan duwe Excel 2016 utawa mengko) utawa ing tab Pitakonan Daya (yen sampeyan duwe Excel 2010-2013) pilih printah Gawe Pitakonan - Saka File - Excel (Entuk Data - Saka file - Excel) lan nemtokake file sumber karo tabel sing bakal diklumpukake:

Ing jendhela sing katon, pilih sheet apa wae (ora ketompo sing endi) banjur pencet tombol ing ngisor iki Ganti (Edit):

Jendhela Power Query Query Editor kudu mbukak ing ndhuwur Excel. Ing sisih tengen jendhela ing panel Parameter Panjaluk mbusak kabeh langkah sing digawe kanthi otomatis kajaba sing pisanan - sumber (Sumber):

Saiki kita ndeleng dhaptar umum kabeh lembar. Yen saliyane lembar data ana sawetara lembar sisih liyane ing file, banjur ing langkah iki, tugas kita mung milih lembaran kasebut saka informasi sing kudu dimuat, ora kalebu kabeh liyane nggunakake saringan ing header tabel:

Mbusak kabeh kolom kajaba kolom Datakanthi ngeklik tengen judhul kolom banjur pilih Mbusak kolom liyane (Busak kolom liyane):

Sampeyan banjur bisa nggedhekake isi tabel sing diklumpukake kanthi ngeklik panah pindho ing sisih ndhuwur kolom (kotak centhang Gunakake jeneng kolom asli minangka awalan sampeyan bisa mateni):

Yen sampeyan nindakake kabeh kanthi bener, mula sampeyan kudu ndeleng isi kabeh tabel sing diklumpukake ing ngisor iki:

Iku tetep kanggo ngunggahake baris pisanan menyang header meja kanthi tombol Gunakake baris pisanan minangka header (Gunakake baris pisanan minangka header) tab Home (Ngarep) lan mbusak header tabel duplikat saka data nggunakake filter:

Simpen kabeh rampung karo printah Nutup lan muat - Nutup lan muat ing… (Tutup & Muat - Tutup & Muat menyang…) tab Home (Ngarep), lan ing jendhela sing mbukak, pilih pilihan Sambungan mung (Sambungan Mung):

Kabeh. Iku tetep mung kanggo mbangun ringkesan. Kanggo nindakake iki, pindhah menyang tab Sisipake - PivotTable (Sisipake - Tabel Pivot), pilih pilihan Gunakake sumber data eksternal (Gunakake sumber data eksternal)banjur kanthi ngeklik tombol Pilih sambungan, panjaluk kita. Nggawe lan konfigurasi luwih saka poros dumadi kanthi cara standar kanthi nyeret kolom sing kita butuhake menyang baris, kolom lan area nilai:

Yen data sumber diganti ing mangsa ngarep utawa sawetara lembar toko ditambahake, mula bakal cukup kanggo nganyari pitakon lan ringkesan kita nggunakake printah Refresh kabeh tab Data (Data - Refresh Kabeh).

Cara 2. Kita nyawiji tabel karo printah UNION SQL ing makro

Solusi liyane kanggo masalah kita diwakili dening makro iki, sing nggawe set data (cache) kanggo tabel pivot nggunakake printah UNITY Basa pitakon SQL. Printah iki nggabungake tabel saka kabeh sing ditemtokake ing array SheetNames lembar buku dadi tabel data siji. Sing, tinimbang fisik Nyalin lan nempel sawetara saka sheets beda kanggo siji, kita nindakake padha ing RAM komputer. Banjur makro nambahake lembar anyar kanthi jeneng sing diwenehake (variabel ResultSheetName) lan nggawe ringkesan lengkap (!) adhedhasar cache sing diklumpukake.

Kanggo nggunakake makro, gunakake tombol Visual Basic ing tab pangembang (Pengembang) utawa trabasan keyboard Alt+F11. Banjur kita masang modul kosong anyar liwat menu Insert - Modul lan salin kode ing ngisor iki ana:

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 displayed" = "Pheivot" jeneng karo tabel sumber SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'kita mbentuk cache kanggo tabel saka sheets saka SheetsNames With ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) Kanggo i = LBound (SheetsNames) Kanggo UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Sabanjure 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 ) Pungkasi Kanthi 'gawe maneh sheet kanggo nampilake tabel poros sing diasilake On Error Resume Next Application.DisplayAlerts = False Worksheets(ResultSheetName).Busak Set wsPivot = Worksheets.Tambah wsPivo t. Jeneng = ResultSheetName 'tampilake ringkesan cache sing digawe ing sheet iki Setel objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Setel objPivotCache.Recordset = objRS Setel objRS = Ora Ana Kanthi wsPivot objPivotCachet.CreatePivotCachet.CreatePivotCachet.CreatePivotCachet.CreatePivotCachet.CreatePivotCachet.CreatePivotCache. objPivotCache = Nothing Range("A3").Pilih End With End Sub    

Macro sing wis rampung banjur bisa ditindakake kanthi trabasan keyboard Alt+F8 utawa tombol Macros ing tab pangembang (Pangembang - Makro).

Cons saka pendekatan iki:

• Data ora dianyari amarga cache ora ana sambungan menyang tabel sumber. Yen sampeyan ngganti data sumber, sampeyan kudu mbukak makro maneh lan nggawe ringkesan maneh.
• Nalika ngganti jumlah lembar, sampeyan kudu ngowahi kode makro (array SheetNames).
  

Nanging ing pungkasan kita entuk tabel pivot sing lengkap, dibangun ing sawetara kisaran saka macem-macem lembar:

Dadi!

Cathetan teknis: yen sampeyan entuk kesalahan kaya "Panyedhiya ora kadhaptar" nalika mbukak makro, kemungkinan sampeyan duwe versi 64-bit Excel utawa versi Office sing ora lengkap (ora ana Akses). Kanggo ndandani kahanan kasebut, ganti fragmen ing kode makro:

	 Provider=Microsoft.Jet.OLEDB.4.0;  

kanggo:

	Provider=Microsoft.ACE.OLEDB.12.0;  

Lan ngundhuh lan nginstal mesin pangolahan data gratis saka Akses saka situs web Microsoft - Microsoft Access Database Engine 2010 Redistributable

Cara 3: Gabungan Wisaya PivotTable saka Excel Versi Lawas

Cara iki rada ketinggalan jaman, nanging isih worth mentioning. Secara resmi, ing kabeh versi nganti lan kalebu 2003, ana pilihan ing PivotTable Wisaya kanggo "mbangun poros kanggo sawetara kisaran konsolidasi". Nanging, laporan sing digawe kanthi cara iki, sayangé, mung bakal dadi mirip ringkesan sing lengkap lan ora ndhukung akeh "chip" tabel pivot konvensional:

Ing poros kasebut, ora ana judhul kolom ing dhaptar lapangan, ora ana setelan struktur sing fleksibel, set fungsi sing digunakake diwatesi, lan, ing umum, kabeh iki ora padha karo tabel pivot. Mbok menawa, wiwit taun 2007, Microsoft mbusak fungsi iki saka dialog standar nalika nggawe laporan tabel poros. Saiki fitur iki mung kasedhiya liwat tombol khusus Wisaya Tabel Pivot(Wisaya Tabel Pivot), sing, yen dikarepake, bisa ditambahake menyang Toolbar Akses Cepet liwat File - Pilihan - Kustomisasi Toolbar Akses Cepet - Kabeh Prentah (File — Pilihan — Kustomisasi Toolbar Akses Cepet — Kabeh Prentah):

Sawise ngeklik tombol sing ditambahake, sampeyan kudu milih pilihan sing cocog ing langkah pertama tuntunan:

Banjur ing jendela sabanjure, pilih saben sawetara lan tambahake menyang dhaptar umum:

Nanging, maneh, iki dudu ringkesan lengkap, mula aja ngarep-arep banget. Aku bisa menehi rekomendasi pilihan iki mung ing kasus banget prasaja.

• Nggawe Laporan nganggo PivotTables
• Setel petungan ing PivotTables
• Apa macro, carane nggunakake, ngendi kanggo nyalin kode VBA, etc.
• Pengumpulan data saka pirang-pirang lembar dadi siji (tambahan PLEX)