ການແບ່ງຕາຕະລາງເປັນແຜ່ນ

Microsoft Excel ມີ​ເຄື່ອງ​ມື​ຫຼາຍ​ຢ່າງ​ສໍາ​ລັບ​ການ​ເກັບ​ກໍາ​ຂໍ້​ມູນ​ຈາກ​ຫຼາຍ​ຕາ​ຕະ​ລາງ (ຈາກ​ແຜ່ນ​ທີ່​ແຕກ​ຕ່າງ​ກັນ​ຫຼື​ຈາກ​ໄຟລ​໌​ທີ່​ແຕກ​ຕ່າງ​ກັນ​)​: ການ​ເຊື່ອມ​ຕໍ່​ໂດຍ​ກົງ​, ການ​ທໍາ​ງານ ປະຕິບັດຕາມ (ໂດຍອີງໃສ່), Power Query ແລະ Power Pivot add-ons, ແລະອື່ນໆ ຈາກຂ້າງຂອງ barricade ນີ້, ທຸກສິ່ງທຸກຢ່າງເບິ່ງດີ.

ແຕ່ຖ້າທ່ານແລ່ນເຂົ້າໄປໃນບັນຫາທີ່ກົງກັນຂ້າມ - ການກະຈາຍຂໍ້ມູນຈາກຕາຕະລາງຫນຶ່ງໄປຫາແຜ່ນທີ່ແຕກຕ່າງກັນ - ແລ້ວທຸກສິ່ງທຸກຢ່າງຈະໂສກເສົ້າຫຼາຍ. ໃນເວລານີ້, ບໍ່ມີເຄື່ອງມືທີ່ສ້າງຂຶ້ນໃນພົນລະເຮືອນສໍາລັບການແຍກຂໍ້ມູນດັ່ງກ່າວຢູ່ໃນສານຫນູຂອງ Excel, ແຕ່ຫນ້າເສຍດາຍ. ດັ່ງນັ້ນ, ທ່ານຈະຕ້ອງໃຊ້ macro ໃນ Visual Basic, ຫຼືໃຊ້ macro recorder + Power Query ປະສົມປະສານກັບ "ການປັບປຸງໄຟລ໌" ເລັກນ້ອຍຫຼັງຈາກນັ້ນ.

ຂໍໃຫ້ພິຈາລະນາຢ່າງລະອຽດກ່ຽວກັບວິທີການນີ້ສາມາດປະຕິບັດໄດ້.

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

ພວກເຮົາມີຂໍ້ມູນເບື້ອງຕົ້ນເຊັ່ນຕາຕະລາງທີ່ມີຂະຫນາດຫຼາຍກວ່າ 5000 ແຖວສໍາລັບການຂາຍ:

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

ກຽມຕົວ

ເພື່ອບໍ່ໃຫ້ລະຫັດມະຫາພາກສັບສົນແລະເຮັດໃຫ້ມັນເຂົ້າໃຈງ່າຍເທົ່າທີ່ຈະເປັນໄປໄດ້, ໃຫ້ດໍາເນີນຂັ້ນຕອນການກະກຽມສອງສາມຢ່າງ.

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

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

ຄັ້ງທີສອງ, пປ່ຽນຕາຕະລາງທັງສອງເປັນແບບເຄື່ອນໄຫວ (“ສະຫຼາດ”) ເພື່ອເຮັດໃຫ້ການເຮັດວຽກກັບເຂົາເຈົ້າງ່າຍຂຶ້ນ. ພວກເຮົາໃຊ້ຄໍາສັ່ງ ຫນ້າທໍາອິດ – ຈັດຮູບແບບເປັນຕາຕະລາງ (ຫນ້າທໍາອິດ — Format as ຕາ​ຕະ​ລາງ​) ຫຼືທາງລັດແປ້ນພິມ Ctrl+T. ໃນແຖບທີ່ປາກົດ ຜູ້ກໍ່ສ້າງ (ອອກ​ແບບ) ໃຫ້ໂທຫາພວກເຂົາ tablProdaji и ເມືອງຕາຕະລາງ, ຕາມ ລຳ ດັບ:

ວິທີການ 1. Macro ສໍາລັບການແບ່ງໂດຍແຜ່ນ

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

ຕົວແຍກຍ່ອຍ() ສຳລັບແຕ່ລະເຊລໃນໄລຍະ("таблГорода") ຊ່ວງ("таблПродажи").AutoFilter Field:=3, Criteria1:=cell.Value Range("таблПродажи[#All]").SpecialCells(VelisCellible). Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets("Данные").ShowAllData End Sub	  

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

ທ່ານສາມາດເອີ້ນໃຊ້ macro ທີ່ສ້າງຂຶ້ນໃນ Excel ໃນແຖບ ການພັດທະນາ ປຸ່ມ ມາໂຄຣ (ຜູ້​ພັດ​ທະ​ນາ — Macros​) ຫຼືທາງລັດແປ້ນພິມ alt+F8.

ວິທີການ 2. ສ້າງແບບສອບຖາມຫຼາຍໃນ Power Query

ວິທີການທີ່ຜ່ານມາ, ສໍາລັບຄວາມຫນາແຫນ້ນແລະຄວາມລຽບງ່າຍຂອງມັນ, ມີຂໍ້ບົກຜ່ອງທີ່ສໍາຄັນ - ແຜ່ນທີ່ສ້າງຂຶ້ນໂດຍມະຫາພາກບໍ່ໄດ້ຖືກປັບປຸງເມື່ອມີການປ່ຽນແປງໃນຕາຕະລາງການຂາຍຕົ້ນສະບັບ. ຖ້າການອັບເດດໃນການບິນແມ່ນມີຄວາມຈໍາເປັນ, ຫຼັງຈາກນັ້ນທ່ານຈະຕ້ອງໃຊ້ VBA + Power Query bundle, ຫຼືແທນທີ່ຈະ, ສ້າງໂດຍໃຊ້ macro ບໍ່ພຽງແຕ່ແຜ່ນທີ່ມີຂໍ້ມູນຄົງທີ່, ແຕ່ການສອບຖາມ Power Query ທີ່ຖືກປັບປຸງ.

macro ໃນ​ກໍ​ລະ​ນີ​ນີ້​ແມ່ນ​ບາງ​ສ່ວນ​ຄ້າຍ​ຄື​ກັນ​ກັບ​ທີ່​ຜ່ານ​ມາ (ມັນ​ຍັງ​ມີ​ວົງ​ຈອນ​ ສໍາລັບແຕ່ລະຄົນ… ຕໍ່ໄປ ເພື່ອ iterate ຫຼາຍກວ່າເມືອງຕ່າງໆໃນໄດເລກະທໍລີ), ແຕ່ພາຍໃນ loop ຈະບໍ່ມີການກັ່ນຕອງແລະການຄັດລອກ, ແຕ່ການສ້າງແບບສອບຖາມ Power Query ແລະອັບໂຫລດຜົນໄດ້ຮັບຂອງມັນໃສ່ແຜ່ນໃຫມ່:

Sub Splitter2() ສໍາລັບແຕ່ລະຕາລາງໃນຂອບເຂດ("ຕາຕາລາງເມືອງ") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " Source = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""ປະເພດທີ່ປ່ຽນແປງ" = Table.TransformColumnTypes(ແຫຼ່ງທີ່ມາ , {{""ໝວດໝູ່"", ພິມຂໍ້ຄວາມ}, {""ຊື່", ພິມຂໍ້ຄວາມ}, {""ເມືອງ"", ພິມຂໍ້ຄວາມ}, {""ຜູ້ຈັດການ"", ພິມຂໍ້ຄວາມ}, {""ຂໍ້ຕົກລົງ date "", type datetime}, {""Cost"", type number}})," & Chr(13) & "" & Chr(10) & " #""ແຖວທີ່ມີການກັ່ນຕອງ"" = Table.Se " & _ "lectRows(#""ປະເພດທີ່ປ່ຽນແປງ"", ແຕ່ລະ ([ເມືອງ] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "ໃນ " & Chr(13) & "" & Chr(10) & " #""ແຖວທີ່ມີການກັ່ນຕອງຖືກນຳໃຊ້""" ActiveWorkbook.Worksheets.Add With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _"OLEDB; ຜູ້ໃຫ້ບໍລິການ =Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & cell.Value & ";Extended Properties="""" _ , ຈຸດຫມາຍປາຍທາງ:=Range("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *FROM ["&cell.Value & "]").RowNumbers = False .FillAdjacentFormulas = False .PreserveFormatting = True .RefreshOnFileOpen = False .BackgroundQuery = True .RefreshStyle = xlInsertDeleteCells .FalsePass SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End ດ້ວຍ ActiveSheet.Name = cell.Value ເຊລຕໍ່ໄປ End Sub  

ຫຼັງ​ຈາກ​ການ​ເປີດ​ຕົວ​ຂອງ​ມັນ​, ພວກ​ເຮົາ​ຈະ​ເຫັນ​ແຜ່ນ​ດຽວ​ກັນ​ຕາມ​ຕົວ​ເມືອງ​, ແຕ່​ການ​ສອບ​ຖາມ Power Query ທີ່​ສ້າງ​ແລ້ວ​ຈະ​ປະ​ກອບ​ເປັນ​ພວກ​ເຂົາ​:

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

• ມະຫາພາກແມ່ນຫຍັງ, ວິທີການສ້າງແລະນໍາໃຊ້ພວກມັນ
• ບັນທຶກແຜ່ນວຽກເປັນໄຟລ໌ແຍກຕ່າງຫາກ
• ການລວບລວມຂໍ້ມູນຈາກແຜ່ນທັງຫມົດຂອງປື້ມເຂົ້າໄປໃນຕາຕະລາງດຽວ