Jadual pangsi merentas berbilang julat data

Perumusan masalah

Jadual pangsi ialah salah satu alat yang paling menakjubkan dalam Excel. Tetapi setakat ini, malangnya, tiada satu pun versi Excel boleh melakukan perkara yang mudah dan perlu dengan cepat seperti membina ringkasan untuk beberapa julat data awal yang terletak, contohnya, pada helaian yang berbeza atau dalam jadual yang berbeza:

Sebelum kita mula, mari kita jelaskan beberapa perkara. A priori, saya percaya bahawa syarat berikut dipenuhi dalam data kami:

• Jadual boleh mempunyai sebarang bilangan baris dengan sebarang data, tetapi ia mesti mempunyai pengepala yang sama.
• Seharusnya tiada data tambahan pada helaian dengan jadual sumber. Satu helaian – satu meja. Untuk mengawal, saya menasihati anda untuk menggunakan pintasan papan kekunci Ctrl+akhir, yang mengalihkan anda ke sel terakhir digunakan dalam lembaran kerja. Sebaik-baiknya, ini sepatutnya menjadi sel terakhir dalam jadual data. Jika apabila anda klik pada Ctrl+akhir mana-mana sel kosong di sebelah kanan atau bawah jadual diserlahkan – padamkan lajur kosong ini di sebelah kanan atau baris di bawah jadual selepas jadual dan simpan fail.

Kaedah 1: Bina jadual untuk pangsi menggunakan Power Query

Bermula dari versi 2010 untuk Excel, terdapat tambahan Power Query percuma yang boleh mengumpul dan mengubah sebarang data dan kemudian memberikannya sebagai sumber untuk membina jadual pangsi. Menyelesaikan masalah kami dengan bantuan add-in ini tidak sukar sama sekali.

Mula-mula, mari buat fail kosong baharu dalam Excel – pemasangan akan berlaku di dalamnya dan kemudian jadual pangsi akan dibuat di dalamnya.

Kemudian pada tab Tarikh (jika anda mempunyai Excel 2016 atau lebih baru) atau pada tab Kuasa Kuasa (jika anda mempunyai Excel 2010-2013) pilih arahan Cipta Pertanyaan – Dari Fail – Excel (Dapatkan Data — Dari fail — Excel) dan tentukan fail sumber dengan jadual yang akan dikumpul:

Dalam tetingkap yang muncul, pilih mana-mana helaian (tidak kira yang mana satu) dan tekan butang di bawah Tukar (Edit):

Tetingkap Power Query Query Editor harus dibuka di atas Excel. Di sebelah kanan tetingkap pada panel Permintaan Parameter padam semua langkah yang dibuat secara automatik kecuali yang pertama - Source (Sumber):

Sekarang kita melihat senarai umum semua helaian. Jika sebagai tambahan kepada helaian data terdapat beberapa helaian sisi lain dalam fail, maka pada langkah ini tugas kami adalah untuk memilih hanya helaian tersebut dari mana maklumat perlu dimuatkan, tidak termasuk semua helaian lain menggunakan penapis dalam pengepala jadual:

Padam semua lajur kecuali lajur Tarikhdengan mengklik kanan tajuk lajur dan memilih Padamkan lajur lain (Alih keluar lajur lain):

Anda kemudiannya boleh mengembangkan kandungan jadual yang dikumpul dengan mengklik pada anak panah berganda di bahagian atas lajur (kotak semak Gunakan nama lajur asal sebagai awalan anda boleh mematikannya):

Jika anda melakukan semuanya dengan betul, maka pada ketika ini anda akan melihat kandungan semua jadual yang dikumpulkan satu di bawah yang lain:

Ia kekal untuk menaikkan baris pertama ke pengepala jadual dengan butang Gunakan baris pertama sebagai tajuk (Gunakan baris pertama sebagai pengepala) tab Laman Utama (Rumah) dan alih keluar pengepala jadual pendua daripada data menggunakan penapis:

Simpan semua yang dilakukan dengan arahan Tutup dan muat – Tutup dan muatkan… (Tutup & Muatkan — Tutup & Muatkan ke…) tab Laman Utama (Rumah), dan dalam tetingkap yang terbuka, pilih pilihan Sambungan sahaja (Sambungan Sahaja):

Semuanya. Ia kekal hanya untuk membina ringkasan. Untuk melakukan ini, pergi ke tab Sisipkan – Jadual Pangsi (Sisipkan — Jadual Pangsi), pilih pilihan Gunakan sumber data luaran (Gunakan sumber data luaran)dan kemudian dengan mengklik butang Pilih sambungan, permintaan kami. Penciptaan dan konfigurasi lanjut pivot berlaku dengan cara yang sepenuhnya standard dengan menyeret medan yang kita perlukan ke dalam baris, lajur dan kawasan nilai:

Jika data sumber berubah pada masa hadapan atau beberapa helaian kedai ditambahkan, maka ia sudah memadai untuk mengemas kini pertanyaan dan ringkasan kami menggunakan arahan Muat semula semua tab Tarikh (Data — Muat semula Semua).

Kaedah 2. Kami menyatukan jadual dengan arahan UNION SQL dalam makro

Satu lagi penyelesaian kepada masalah kami diwakili oleh makro ini, yang mencipta set data (cache) untuk jadual pangsi menggunakan arahan UNITI Bahasa pertanyaan SQL. Perintah ini menggabungkan jadual daripada semua yang dinyatakan dalam tatasusunan SheetNames helaian buku ke dalam satu jadual data. Iaitu, daripada menyalin dan menampal secara fizikal julat dari helaian yang berbeza kepada satu, kami melakukan perkara yang sama dalam RAM komputer. Kemudian makro menambah helaian baharu dengan nama yang diberikan (variable ResultSheetName) dan mencipta ringkasan lengkap (!) padanya berdasarkan cache yang dikumpul.

Untuk menggunakan makro, gunakan butang Visual Basic pada tab pemaju (Pembangun) atau pintasan papan kekunci Alt+F11. Kemudian kami memasukkan modul kosong baru melalui menu Sisipan – Modul dan salin kod berikut di sana:

Sub New_Multi_Table_Pivot() Malapkan 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 di mana pivot yang terhasil akan dipaparkan ' = "Pheivoet sheet" = "P helaian" nama dengan jadual sumber SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'kami membentuk cache untuk jadual daripada helaian daripada SheetsNames With ActiveWorkbook ReDim arSQL(1 To (UBound(SheetsNames) + 1) ) Untuk i = LBound (Nama Helaian) Kepada UBound(Nama Helaian) arSQL(i + 1) = "PILIH * DARI [" & Nama Helaian(i) & "$]" Seterusnya i Tetapkan 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 ) Tamat Dengan 'buat semula helaian untuk memaparkan jadual pangsi yang terhasil Pada Ralat Resume Aplikasi Seterusnya.DisplayAlerts = False Worksheets(ResultSheetName).Padam Set wsPivot = Worksheets.Tambah wsPivo t. Name = ResultSheetName 'memaparkan ringkasan cache yang dijana pada helaian ini Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) Set objPivotCache.Recordset = objRS Set objRS = Tiada Apa-apa Dengan wsPivot objPivotCachet.CreatePivotCachet.CreatePivotCache.CreateDePivotCache.CreatePivotCache.Create(3PivotCache) objPivotCache = Julat Tiada ("A3"). Pilih Tamat Dengan Sub Akhir    

Makro yang telah siap kemudiannya boleh dijalankan dengan pintasan papan kekunci Alt+F8 atau butang Makro pada tab pemaju (Pembangun — Makro).

Kelemahan pendekatan ini:

• Data tidak dikemas kini kerana cache tidak mempunyai sambungan ke jadual sumber. Jika anda menukar data sumber, anda mesti menjalankan makro sekali lagi dan membina ringkasan semula.
• Apabila menukar bilangan helaian, adalah perlu untuk mengedit kod makro (array SheetNames).
  

Tetapi pada akhirnya kami mendapat jadual pangsi lengkap yang sebenar, dibina pada beberapa julat daripada helaian yang berbeza:

Voilà!

Nota teknikal: jika anda mendapat ralat seperti "Pembekal tidak berdaftar" semasa menjalankan makro, kemungkinan besar anda mempunyai versi Excel 64-bit atau versi Office yang tidak lengkap dipasang (tiada Akses). Untuk membetulkan keadaan, gantikan serpihan dalam kod makro:

	 Pembekal=Microsoft.Jet.OLEDB.4.0;  

kepada:

	Pembekal=Microsoft.ACE.OLEDB.12.0;  

Dan muat turun dan pasang enjin pemprosesan data percuma daripada Access daripada tapak web Microsoft – Microsoft Access Database Engine 2010 Redistributable

Kaedah 3: Satukan Wizard Jadual Pangsi daripada Versi Lama Excel

Kaedah ini agak ketinggalan zaman, tetapi masih patut disebut. Secara rasmi, dalam semua versi sehingga dan termasuk 2003, terdapat pilihan dalam Wizard Jadual Pangsi untuk "membina pangsi untuk beberapa julat penyatuan". Walau bagaimanapun, laporan yang dibina dengan cara ini, malangnya, hanya akan menjadi rupa yang menyedihkan daripada ringkasan lengkap sebenar dan tidak menyokong banyak "cip" jadual pangsi konvensional:

Dalam pangsi sedemikian, tiada tajuk lajur dalam senarai medan, tiada tetapan struktur yang fleksibel, set fungsi yang digunakan adalah terhad, dan, secara amnya, semua ini tidak begitu serupa dengan jadual pangsi. Mungkin itulah sebabnya, bermula pada tahun 2007, Microsoft mengalih keluar fungsi ini daripada dialog standard apabila membuat laporan jadual pangsi. Kini ciri ini hanya tersedia melalui butang tersuai Wizard Jadual Pangsi(Wizard Jadual Pangsi), yang, jika dikehendaki, boleh ditambah pada Bar Alat Akses Pantas melalui Fail – Pilihan – Sesuaikan Bar Alat Akses Pantas – Semua Perintah (Fail — Pilihan — Sesuaikan Bar Alat Akses Pantas — Semua Perintah):

Selepas mengklik pada butang tambah, anda perlu memilih pilihan yang sesuai pada langkah pertama wizard:

Dan kemudian dalam tetingkap seterusnya, pilih setiap julat secara bergilir-gilir dan tambahkannya pada senarai umum:

Tetapi, sekali lagi, ini bukan ringkasan lengkap, jadi jangan terlalu mengharapkannya. Saya boleh mengesyorkan pilihan ini hanya dalam kes yang sangat mudah.

• Mencipta Laporan dengan Jadual Pangsi
• Sediakan pengiraan dalam Jadual Pangsi
• Apakah makro, cara menggunakannya, tempat untuk menyalin kod VBA, dsb.
• Pengumpulan data daripada berbilang helaian kepada satu (tambahan PLEX)