ຕາຕະລາງ pivot ໃນຂອບເຂດຂໍ້ມູນຫຼາຍອັນ

ການສ້າງບັນຫາ

ຕາຕະລາງ Pivot ແມ່ນຫນຶ່ງໃນເຄື່ອງມືທີ່ຫນ້າປະຫລາດໃຈທີ່ສຸດໃນ Excel. ແຕ່ມາເຖິງຕອນນັ້ນ, ແຕ່ຫນ້າເສຍດາຍ, ບໍ່ມີ Excel ຮຸ່ນໃດສາມາດເຮັດສິ່ງທີ່ງ່າຍດາຍແລະຈໍາເປັນເຊັ່ນການສ້າງບົດສະຫຼຸບສໍາລັບຂອບເຂດຂໍ້ມູນເບື້ອງຕົ້ນຈໍານວນຫນຶ່ງ, ຕົວຢ່າງ, ໃນແຜ່ນຕ່າງໆຫຼືໃນຕາຕະລາງທີ່ແຕກຕ່າງກັນ:

ກ່ອນທີ່ພວກເຮົາຈະເລີ່ມຕົ້ນ, ໃຫ້ພວກເຮົາຊີ້ແຈງບາງຈຸດ. ກ່ອນອື່ນ ໝົດ, ຂ້ອຍເຊື່ອວ່າເງື່ອນໄຂຕໍ່ໄປນີ້ແມ່ນບັນລຸໄດ້ໃນຂໍ້ມູນຂອງພວກເຮົາ:

• ຕາຕະລາງສາມາດມີຈໍານວນແຖວທີ່ມີຂໍ້ມູນໃດກໍ່ຕາມ, ແຕ່ພວກມັນຕ້ອງມີສ່ວນຫົວດຽວກັນ.
• ບໍ່ຄວນມີຂໍ້ມູນເພີ່ມເຕີມຢູ່ໃນແຜ່ນງານທີ່ມີຕາຕະລາງແຫຼ່ງ. ຫນຶ່ງແຜ່ນ - ຕາຕະລາງຫນຶ່ງ. ເພື່ອຄວບຄຸມ, ຂ້າພະເຈົ້າແນະນໍາໃຫ້ທ່ານໃຊ້ປຸ່ມລັດແປ້ນພິມ Ctrl+ສຸດທ້າຍ, ເຊິ່ງຍ້າຍທ່ານໄປຫາຕາລາງທີ່ໃຊ້ຫຼ້າສຸດໃນແຜ່ນວຽກ. ໂດຍຫລັກການແລ້ວ, ນີ້ຄວນຈະເປັນຕາລາງສຸດທ້າຍໃນຕາຕະລາງຂໍ້ມູນ. ຖ້າ​ຫາກ​ວ່າ​ໃນ​ເວ​ລາ​ທີ່​ທ່ານ​ຄລິກ​ໃສ່​ Ctrl+ສຸດທ້າຍ ເຊລຫວ່າງໃດນຶ່ງຢູ່ທາງຂວາ ຫຼືຂ້າງລຸ່ມຂອງຕາຕະລາງຈະຖືກເນັ້ນໃສ່ – ລຶບຖັນຫວ່າງເປົ່າເຫຼົ່ານີ້ໄປທາງຂວາ ຫຼືແຖວລຸ່ມຕາຕະລາງ ຫຼັງຈາກຕາຕະລາງ ແລະບັນທຶກໄຟລ໌.

ວິທີທີ່ 1: ສ້າງຕາຕະລາງສໍາລັບ pivot ໂດຍໃຊ້ Power Query

ເລີ່ມຕົ້ນຈາກສະບັບ 2010 ສໍາລັບ Excel, ມີ Power Query add-in ຟຣີທີ່ສາມາດເກັບກໍາແລະປ່ຽນຂໍ້ມູນໃດໆແລະຫຼັງຈາກນັ້ນໃຫ້ມັນເປັນແຫຼ່ງສໍາລັບການກໍ່ສ້າງຕາຕະລາງ pivot. ການແກ້ໄຂບັນຫາຂອງພວກເຮົາດ້ວຍການຊ່ວຍເຫຼືອຂອງ add-in ນີ້ບໍ່ຍາກເລີຍ.

ທໍາອິດ, ໃຫ້ສ້າງໄຟລ໌ຫວ່າງເປົ່າໃຫມ່ໃນ Excel - ການຊຸມນຸມຈະເກີດຂຶ້ນໃນມັນແລະຫຼັງຈາກນັ້ນຕາຕະລາງ pivot ຈະຖືກສ້າງຂື້ນໃນມັນ.

ຫຼັງຈາກນັ້ນ, ໃນແຖບ ຂໍ້ມູນ (ຖ້າທ່ານມີ Excel 2016 ຫຼືຫຼັງຈາກນັ້ນ) ຫຼືຢູ່ໃນແຖບ ແບບສອບຖາມພະລັງງານ (ຖ້າທ່ານມີ Excel 2010-2013) ເລືອກຄໍາສັ່ງ ສ້າງຄໍາຖາມ - ຈາກໄຟລ໌ - Excel (ໄດ້​ຮັບ​ຂໍ້​ມູນ — ຈາກ​ໄຟລ​໌ — Excel​) ແລະລະບຸໄຟລ໌ແຫຼ່ງທີ່ມີຕາຕະລາງທີ່ຈະເກັບກໍາ:

ໃນປ່ອງຢ້ຽມທີ່ປາກົດ, ເລືອກເອົາແຜ່ນໃດ (ມັນບໍ່ສໍາຄັນວ່າອັນໃດ) ແລະກົດປຸ່ມຂ້າງລຸ່ມນີ້ ການປ່ຽນແປງ (ດັດແກ້):

ປ່ອງຢ້ຽມ Power Query Editor ຄວນເປີດຢູ່ເທິງສຸດຂອງ Excel. ຢູ່ເບື້ອງຂວາຂອງປ່ອງຢ້ຽມໃນກະດານ ຄໍາຮ້ອງສະຫມັກພາລາມິເຕີ ລຶບຂັ້ນຕອນທີ່ສ້າງຂຶ້ນໂດຍອັດຕະໂນມັດທັງໝົດ ຍົກເວັ້ນຂັ້ນຕອນທຳອິດ - ແຫຼ່ງຂໍ້ມູນ (Source):

ໃນປັດຈຸບັນພວກເຮົາເຫັນບັນຊີລາຍຊື່ທົ່ວໄປຂອງແຜ່ນທັງຫມົດ. ຖ້ານອກ ເໜືອ ໄປຈາກແຜ່ນຂໍ້ມູນມີແຜ່ນຂ້າງອື່ນໆໃນເອກະສານ, ຫຼັງຈາກນັ້ນໃນຂັ້ນຕອນນີ້, ວຽກງານຂອງພວກເຮົາແມ່ນເລືອກພຽງແຕ່ແຜ່ນຂໍ້ມູນທີ່ຕ້ອງການໂຫລດ, ຍົກເວັ້ນເອກະສານອື່ນໆທັງຫມົດໂດຍໃຊ້ຕົວກອງໃນສ່ວນຫົວຕາຕະລາງ:

ລຶບຖັນທັງໝົດຍົກເວັ້ນຖັນ ຂໍ້ມູນໂດຍການຄລິກຂວາໃສ່ຫົວຂໍ້ຖັນ ແລະເລືອກ ລຶບຖັນອື່ນໆ (ເອົາອອກ ຖັນ​ອື່ນໆ​):

ຈາກນັ້ນທ່ານສາມາດຂະຫຍາຍເນື້ອໃນຂອງຕາຕະລາງທີ່ເກັບໄດ້ໂດຍການຄລິກໃສ່ລູກສອນຄູ່ຢູ່ເທິງສຸດຂອງຖັນ (ກ່ອງໝາຍຖືກ. ໃຊ້ຊື່ຖັນຕົ້ນສະບັບເປັນຄໍານໍາຫນ້າ ທ່ານສາມາດປິດມັນໄດ້):

ຖ້າທ່ານເຮັດທຸກຢ່າງຢ່າງຖືກຕ້ອງ, ຫຼັງຈາກນັ້ນ, ໃນຈຸດນີ້, ທ່ານຄວນເບິ່ງເນື້ອໃນຂອງຕາຕະລາງທັງຫມົດທີ່ລວບລວມຢູ່ຂ້າງລຸ່ມນີ້:

ມັນຍັງຄົງຈະຍົກແຖວທໍາອິດໄປຫາຫົວຕາຕະລາງດ້ວຍປຸ່ມ ໃຊ້ແຖວທຳອິດເປັນສ່ວນຫົວ (ໃຊ້ແຖວທຳອິດເປັນສ່ວນຫົວ) ແຖບ ຫນ້າທໍາອິດ (ໜ້າ ທຳ ອິດ) ແລະເອົາສ່ວນຫົວຕາຕະລາງທີ່ຊໍ້າກັນອອກຈາກຂໍ້ມູນໂດຍໃຊ້ຕົວກອງ:

ບັນທຶກທຸກຢ່າງທີ່ເຮັດດ້ວຍຄໍາສັ່ງ ປິດ ແລະ ໂຫຼດ – ປິດ ແລະ ໂຫຼດໃນ… (ປິດ & ໂຫຼດ — Close & Load to…) ແຖບ ຫນ້າທໍາອິດ (ໜ້າ ທຳ ອິດ), ແລະຢູ່ໃນປ່ອງຢ້ຽມທີ່ເປີດ, ເລືອກເອົາທາງເລືອກ ການເຊື່ອມຕໍ່ເທົ່ານັ້ນ (ການເຊື່ອມຕໍ່ເທົ່ານັ້ນ):

ທຸກຢ່າງ. ມັນຍັງຄົງພຽງແຕ່ສ້າງບົດສະຫຼຸບ. ເພື່ອເຮັດສິ່ງນີ້, ໄປທີ່ແຖບ ແຊກ – PivotTable (ໃສ່ - ຕາຕະລາງ Pivot), ເລືອກທາງເລືອກ ໃຊ້ແຫຼ່ງຂໍ້ມູນພາຍນອກ (ໃຊ້ແຫຼ່ງຂໍ້ມູນພາຍນອກ)ແລະ​ຫຼັງ​ຈາກ​ນັ້ນ​ໂດຍ​ການ​ຄລິກ​ໃສ່​ປຸ່ມ​ ເລືອກການເຊື່ອມຕໍ່, ຄໍາຮ້ອງຂໍຂອງພວກເຮົາ. ການສ້າງແລະການຕັ້ງຄ່າເພີ່ມເຕີມຂອງ pivot ເກີດຂື້ນໃນແບບມາດຕະຖານຢ່າງສົມບູນໂດຍການລາກທົ່ງນາທີ່ພວກເຮົາຕ້ອງການເຂົ້າໄປໃນແຖວ, ຖັນແລະພື້ນທີ່ຄ່າ:

ຖ້າຂໍ້ມູນແຫຼ່ງຂໍ້ມູນມີການປ່ຽນແປງໃນອະນາຄົດຫຼືເອກະສານເກັບຮັກສາເພີ່ມເຕີມຈໍານວນຫນ້ອຍ, ຫຼັງຈາກນັ້ນມັນຈະພຽງພໍທີ່ຈະປັບປຸງການສອບຖາມແລະສະຫຼຸບຂອງພວກເຮົາໂດຍໃຊ້ຄໍາສັ່ງ. ໂຫຼດຂໍ້ມູນຄືນໃໝ່ທັງໝົດ ແຖບ ຂໍ້ມູນ (ຂໍ້ມູນ — ໂຫຼດຂໍ້ມູນຄືນໃໝ່ທັງໝົດ).

ວິທີການ 2. ພວກເຮົາລວມຕາຕະລາງດ້ວຍຄໍາສັ່ງ UNION SQL ໃນມະຫາພາກ

ການແກ້ໄຂບັນຫາຂອງພວກເຮົາອີກອັນຫນຶ່ງແມ່ນສະແດງໂດຍ macro ນີ້, ເຊິ່ງສ້າງຊຸດຂໍ້ມູນ (cache) ສໍາລັບຕາຕະລາງ pivot ໂດຍໃຊ້ຄໍາສັ່ງ. UNITY SQL query language. ຄໍາສັ່ງນີ້ລວມຕາຕະລາງຈາກທັງຫມົດທີ່ລະບຸໄວ້ໃນ array ຊື່ແຜ່ນ ແຜ່ນຂອງປື້ມເຂົ້າໄປໃນຕາຕະລາງຂໍ້ມູນດຽວ. ນັ້ນແມ່ນ, ແທນທີ່ຈະເປັນການຄັດລອກແລະວາງຂອບເຂດຈາກແຜ່ນທີ່ແຕກຕ່າງກັນໄປຫາຫນຶ່ງ, ພວກເຮົາເຮັດເຊັ່ນດຽວກັນໃນ RAM ຂອງຄອມພິວເຕີ. ຫຼັງຈາກນັ້ນ, ມະຫາພາກຈະເພີ່ມແຜ່ນງານໃຫມ່ທີ່ມີຊື່ໃຫ້ (ຕົວແປ ຊື່ຜົນໄດ້ຮັບ) ແລະສ້າງບົດສະຫຼຸບເຕັມຮູບແບບ (!) ກ່ຽວກັບມັນໂດຍອີງໃສ່ແຄດທີ່ເກັບກໍາ.

ເພື່ອໃຊ້ macro, ໃຊ້ປຸ່ມ Visual Basic ໃນແຖບ ການພັດທະນາ (ຜູ້ພັດທະນາ) ຫຼືທາງລັດແປ້ນພິມ alt+F11. ຫຼັງຈາກນັ້ນ, ພວກເຮົາໃສ່ໂມດູນເປົ່າໃຫມ່ຜ່ານເມນູ ໃສ່ - ໂມດູນ ແລະສຳເນົາລະຫັດຕໍ່ໄປນີ້ຢູ່ທີ່ນັ້ນ:

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' sheet of ResultSheetName = "Pivar. ຊື່ທີ່ມີຕາຕະລາງແຫຼ່ງ SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'ພວກເຮົາປະກອບ cache ສໍາລັບຕາຕະລາງຈາກແຜ່ນຈາກ SheetsNames ດ້ວຍ ActiveWorkbook ReDim arSQL(1 ເຖິງ (UBound(SheetsNames) + 1)) ) ສໍາລັບ i = LBound (SheetsNames) To UBound(SheetsNames) arSQL(i + 1) = "SELECT * FROM [" & SheetsNames(i) & "$]" Next i Set objRS = CreateObject("ADODB.Recordset") objRS .ເປີດ Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;" ""), vbNullString ) ສິ້ນສຸດດ້ວຍ 'ສ້າງຊີດຄືນໃໝ່ເພື່ອສະແດງຕາຕະລາງ pivot ທີ່ເປັນຜົນມາຈາກຄວາມຜິດພາດ Resume Next Application.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivo t. ຊື່ = ResultSheetName 'ສະແດງສະຫຼຸບ cache ທີ່ສ້າງຂຶ້ນໃນແຜ່ນນີ້ Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) ຕັ້ງຄ່າ objPivotCache.Recordset = objRS ຕັ້ງ objRS = ບໍ່ມີຫຍັງກັບ wsPivot objPivotCache.CreatePivotation:3T. objPivotCache = Nothing Range("A3").ເລືອກ End With End Sub    

ຈາກນັ້ນ macro ສໍາເລັດແລ້ວສາມາດດໍາເນີນການໄດ້ດ້ວຍປຸ່ມລັດແປ້ນພິມ alt+F8 ຫຼືປຸ່ມ Macros ໃນແຖບ ການພັດທະນາ (ຜູ້​ພັດ​ທະ​ນາ — Macros​).

ຂໍ້ເສຍຂອງວິທີການນີ້:

• ຂໍ້ມູນບໍ່ໄດ້ຖືກປັບປຸງເພາະວ່າ cache ບໍ່ມີການເຊື່ອມຕໍ່ກັບຕາຕະລາງແຫຼ່ງ. ຖ້າທ່ານປ່ຽນຂໍ້ມູນແຫຼ່ງ, ທ່ານຕ້ອງດໍາເນີນການ macro ອີກເທື່ອຫນຶ່ງແລະສ້າງບົດສະຫຼຸບອີກເທື່ອຫນຶ່ງ.
• ເມື່ອປ່ຽນຈໍານວນແຜ່ນ, ມັນຈໍາເປັນຕ້ອງແກ້ໄຂລະຫັດມະຫາພາກ (ອາເຣ ຊື່ແຜ່ນ).
  

ແຕ່ໃນທີ່ສຸດພວກເຮົາໄດ້ຮັບຕາຕະລາງ pivot ເຕັມຮູບແບບທີ່ແທ້ຈິງ, ສ້າງຂຶ້ນໃນຫຼາຍຂອບເຂດຈາກແຜ່ນທີ່ແຕກຕ່າງກັນ:

ຈັ່ງຊັ້ນ!

ບັນທຶກດ້ານວິຊາການ: ຖ້າທ່ານໄດ້ຮັບຂໍ້ຜິດພາດເຊັ່ນ "ຜູ້ໃຫ້ບໍລິການບໍ່ໄດ້ລົງທະບຽນ" ໃນເວລາແລ່ນມະຫາພາກ, ສ່ວນຫຼາຍແມ່ນທ່ານມີ Excel ຮຸ່ນ 64 ບິດຫຼື Office ຮຸ່ນທີ່ບໍ່ຄົບຖ້ວນຖືກຕິດຕັ້ງ (ບໍ່ມີການເຂົ້າເຖິງ). ເພື່ອແກ້ໄຂສະຖານະການ, ແທນທີ່ຊິ້ນສ່ວນໃນລະຫັດມະຫາພາກ:

	 ຜູ້ໃຫ້ບໍລິການ=Microsoft.Jet.OLEDB.4.0;  

ຫາ:

	ຜູ້ໃຫ້ບໍລິການ=Microsoft.ACE.OLEDB.12.0;  

ແລະດາວໂຫຼດ ແລະຕິດຕັ້ງເຄື່ອງຈັກປະມວນຜົນຂໍ້ມູນຟຣີຈາກການເຂົ້າເຖິງຈາກເວັບໄຊທ໌ Microsoft – Microsoft Access Database Engine 2010 Redistributable

ວິທີທີ 3: ລວມຕົວຊ່ວຍສ້າງ PivotTable ຈາກ Excel ຮຸ່ນເກົ່າ

ວິທີການນີ້ແມ່ນລ້າສະໄຫມເລັກນ້ອຍ, ແຕ່ຍັງສົມຄວນທີ່ຈະກ່າວເຖິງ. ເວົ້າຢ່າງເປັນທາງການ, ໃນທຸກຮຸ່ນເຖິງແລະລວມທັງປີ 2003, ມີທາງເລືອກໃນຕົວຊ່ວຍສ້າງ PivotTable ເພື່ອ "ສ້າງ pivot ສໍາລັບຂອບເຂດການລວມຫຼາຍ". ຢ່າງໃດກໍຕາມ, ບົດລາຍງານທີ່ສ້າງຂຶ້ນໃນລັກສະນະນີ້, ແຕ່ຫນ້າເສຍດາຍ, ຈະເປັນພຽງແຕ່ລັກສະນະທີ່ຫນ້າເສົ້າໃຈຂອງບົດສະຫຼຸບທີ່ແທ້ຈິງຢ່າງເຕັມທີ່ແລະບໍ່ສະຫນັບສະຫນູນຫຼາຍຂອງ "chips" ຂອງຕາຕະລາງ pivot ທໍາມະດາ:

ໃນ pivot ດັ່ງກ່າວ, ບໍ່ມີຫົວຂໍ້ຄໍລໍາໃນບັນຊີລາຍຊື່ພາກສະຫນາມ, ບໍ່ມີການຕັ້ງຄ່າໂຄງສ້າງທີ່ມີຄວາມຍືດຫຍຸ່ນ, ຊຸດຂອງຫນ້າທີ່ນໍາໃຊ້ແມ່ນຈໍາກັດ, ແລະໂດຍທົ່ວໄປ, ທັງຫມົດນີ້ແມ່ນບໍ່ຄ້າຍຄືກັນກັບຕາຕະລາງ pivot. ບາງທີນັ້ນແມ່ນເຫດຜົນທີ່ວ່າ, ເລີ່ມຕົ້ນໃນປີ 2007, Microsoft ໄດ້ເອົາຟັງຊັນນີ້ອອກຈາກກ່ອງໂຕ້ຕອບມາດຕະຖານໃນເວລາສ້າງບົດລາຍງານຕາຕະລາງ pivot. ດຽວນີ້ຄຸນສົມບັດນີ້ສາມາດໃຊ້ໄດ້ຜ່ານປຸ່ມກຳນົດເອງເທົ່ານັ້ນ ຕົວຊ່ວຍສ້າງ PivotTable(ຕົວຊ່ວຍສ້າງຕາຕະລາງ pivot), ເຊິ່ງ, ຖ້າຕ້ອງການ, ສາມາດຖືກເພີ່ມໃສ່ແຖບເຄື່ອງມື Quick Access ຜ່ານ ໄຟລ໌ – ຕົວເລືອກ – ປັບແຕ່ງແຖບເຄື່ອງມືການເຂົ້າເຖິງດ່ວນ – ຄໍາສັ່ງທັງໝົດ (ໄຟລ໌ — ຕົວເລືອກ — ປັບແຕ່ງແຖບເຄື່ອງມືການເຂົ້າເຖິງດ່ວນ — ຄໍາສັ່ງທັງໝົດ):

ຫຼັງຈາກທີ່ຄລິກໃສ່ປຸ່ມເພີ່ມ, ທ່ານຈໍາເປັນຕ້ອງເລືອກທາງເລືອກທີ່ເຫມາະສົມໃນຂັ້ນຕອນທໍາອິດຂອງຄວາມຍາວປາ:

ແລະຫຼັງຈາກນັ້ນໃນປ່ອງຢ້ຽມຕໍ່ໄປ, ເລືອກເອົາແຕ່ລະໄລຍະແລະເພີ່ມມັນເຂົ້າໄປໃນບັນຊີລາຍຊື່ທົ່ວໄປ:

ແຕ່, ອີກເທື່ອ ໜຶ່ງ, ນີ້ບໍ່ແມ່ນບົດສະຫຼຸບທີ່ສົມບູນ, ສະນັ້ນຢ່າຄາດຫວັງຫຼາຍຈາກມັນ. ຂ້ອຍສາມາດແນະນໍາທາງເລືອກນີ້ພຽງແຕ່ໃນກໍລະນີທີ່ງ່າຍດາຍຫຼາຍ.

• ການສ້າງບົດລາຍງານດ້ວຍ PivotTables
• ຕັ້ງຄ່າການຄິດໄລ່ໃນ PivotTables
• macros ແມ່ນຫຍັງ, ວິທີການນໍາໃຊ້ພວກມັນ, ບ່ອນທີ່ຈະຄັດລອກລະຫັດ VBA, ແລະອື່ນໆ.
• ການ​ເກັບ​ກໍາ​ຂໍ້​ມູນ​ຈາກ​ຫຼາຍ​ຊີດ​ເປັນ​ຫນຶ່ງ (PLEX add-on​)