ការបំបែកតារាងទៅជាសន្លឹក

Microsoft Excel មានឧបករណ៍ជាច្រើនសម្រាប់ប្រមូលទិន្នន័យពីតារាងជាច្រើន (ពីសន្លឹកផ្សេងគ្នា ឬពីឯកសារផ្សេងៗគ្នា)៖ តំណផ្ទាល់ មុខងារ ដោយផ្ទាល់ (មិនពិត)កម្មវិធីបន្ថែម Power Query និង Power Pivot ជាដើម។ ពីផ្នែកម្ខាងនៃរបាំងនេះ អ្វីៗមើលទៅល្អ។

ប៉ុន្តែប្រសិនបើអ្នកជួបបញ្ហាបញ្ច្រាស - ការរីករាលដាលទិន្នន័យពីតារាងមួយទៅសន្លឹកផ្សេងគ្នា - នោះអ្វីៗនឹងកាន់តែសោកសៅ។ នៅពេលនេះ មិនមានឧបករណ៍ដែលភ្ជាប់មកជាមួយដ៏ស៊ីវិល័យសម្រាប់ការបំបែកទិន្នន័យបែបនេះនៅក្នុងឃ្លាំងផ្ទុកទិន្នន័យរបស់ Excel ជាអកុសល។ ដូច្នេះ អ្នកនឹងត្រូវប្រើម៉ាក្រូនៅក្នុង Visual Basic ឬប្រើម៉ាស៊ីនថតម៉ាក្រូ + Power Query រួមបញ្ចូលគ្នាជាមួយ "ការកែលម្អឯកសារ" បន្តិចក្រោយមក។

ចូរយើងពិនិត្យមើលឱ្យកាន់តែច្បាស់អំពីរបៀបដែលនេះអាចត្រូវបានអនុវត្ត។

ការបង្កើតបញ្ហា

យើងមានទិន្នន័យដំបូងដូចជាតារាងដែលមានទំហំលើសពី 5000 ជួរសម្រាប់លក់៖

កិច្ចការ៖ ដើម្បីចែកចាយទិន្នន័យពីតារាងនេះតាមទីក្រុងនៅលើសន្លឹកដាច់ដោយឡែកនៃសៀវភៅនេះ។ ទាំងនោះ។ នៅទិន្នផល អ្នកត្រូវទទួលបាននៅលើសន្លឹកនីមួយៗតែជួរទាំងនោះពីតារាងដែលការលក់នៅក្នុងទីក្រុងដែលត្រូវគ្នា៖

រៀបចំ

ដើម្បីកុំឱ្យស្មុគស្មាញកូដម៉ាក្រូ និងធ្វើឱ្យវាងាយស្រួលយល់តាមដែលអាចធ្វើទៅបាន ចូរយើងអនុវត្តជំហានត្រៀមមួយចំនួន។

ទីមួយ បង្កើតតារាងរកមើលដាច់ដោយឡែកដែលជាកន្លែងដែលជួរឈរតែមួយនឹងរាយបញ្ជីទីក្រុងទាំងអស់ដែលអ្នកចង់បង្កើតសន្លឹកដាច់ដោយឡែក។ ជាការពិតណាស់ ថតនេះប្រហែលជាមិនមានទីក្រុងទាំងអស់ដែលមាននៅក្នុងទិន្នន័យប្រភពនោះទេ ប៉ុន្តែមានតែរបាយការណ៍ដែលយើងត្រូវការប៉ុណ្ណោះ។ មធ្យោបាយងាយស្រួលបំផុតដើម្បីបង្កើតតារាងបែបនេះគឺត្រូវប្រើពាក្យបញ្ជា ទិន្នន័យ - យកស្ទួន (ទិន្នន័យ - យកស្ទួន) សម្រាប់ការចម្លងជួរឈរ រាជធានី ខេត្ត ឬមុខងារ យូនីក (UNIQUE) - ប្រសិនបើអ្នកមានកំណែចុងក្រោយនៃ Excel 365 ។

ចាប់តាំងពីសន្លឹកថ្មីនៅក្នុង Excel ត្រូវបានបង្កើតតាមលំនាំដើមមុន (នៅខាងឆ្វេង) សន្លឹកបច្ចុប្បន្ន (មុន) វាក៏សមហេតុផលផងដែរក្នុងការតម្រៀបទីក្រុងនៅក្នុងថតនេះតាមលំដាប់ចុះ (ពី Z ដល់ A) – បន្ទាប់មកបន្ទាប់ពីបង្កើត ទីក្រុង សន្លឹកនឹងត្រូវបានរៀបចំតាមអក្ខរក្រម។

ទី ២ пបំប្លែងតារាងទាំងពីរទៅជាថាមវន្ត ("ឆ្លាត") ដើម្បីធ្វើឱ្យវាកាន់តែងាយស្រួលក្នុងការធ្វើការជាមួយពួកគេ។ យើងប្រើពាក្យបញ្ជា ទំព័រដើម - ធ្វើទ្រង់ទ្រាយជាតារាង (ទំព័រដើម — ធ្វើទ្រង់ទ្រាយជាតារាង) ឬផ្លូវកាត់ក្តារចុច បញ្ជា (Ctrl)+T. នៅលើផ្ទាំងដែលលេចឡើង អ្នកសាងសង់។ (រចនា) តោះហៅពួកគេ។ tablProdaji и ទីក្រុងតារាងរៀងគ្នា៖

វិធីសាស្រ្ត 1. ម៉ាក្រូសម្រាប់ការបែងចែកដោយសន្លឹក

នៅលើផ្ទាំងកម្រិតខ្ពស់ អ្នកអភិវឌ្ឍ (អ្នកអភិវឌ្ឍន៍) ចុចលើប៊ូតុង Visual Basic ឬប្រើផ្លូវកាត់ក្តារចុច alt+F11. នៅក្នុងបង្អួចកម្មវិធីនិពន្ធម៉ាក្រូដែលបើក សូមបញ្ចូលម៉ូឌុលទទេថ្មីតាមរយៈម៉ឺនុយ បញ្ចូល - ម៉ូឌុល ហើយចម្លងកូដខាងក្រោមនៅទីនោះ៖

Sub Splitter() សម្រាប់ក្រឡានីមួយៗនៅក្នុងជួរ("таблГорода") Range("таблПродажи").AutoFilter Field:=3, Criteria1:=cell.Value Range("таблПродадажи[#All]").SpecialCells(xpellible)។ Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit Next cell Worksheets("Данные").ShowAllData End Sub	  

នៅទីនេះជាមួយរង្វិលជុំ សម្រាប់គ្នា… បន្ទាប់ បានអនុវត្តការឆ្លងកាត់តាមក្រឡានៃថត ទីក្រុងតារាងដែលជាកន្លែងដែលសម្រាប់ទីក្រុងនីមួយៗវាត្រូវបានត្រង (វិធីសាស្រ្ត តម្រងស្វ័យប្រវត្តិ) នៅក្នុងតារាងលក់ដើម ហើយបន្ទាប់មកចម្លងលទ្ធផលទៅសន្លឹកដែលបានបង្កើតថ្មី។ នៅតាមផ្លូវ សន្លឹកដែលបានបង្កើតត្រូវបានប្តូរឈ្មោះទៅជាឈ្មោះដូចគ្នានៃទីក្រុង ហើយការបំពាក់ដោយស្វ័យប្រវត្តិនូវទទឹងនៃជួរឈរសម្រាប់ភាពស្រស់ស្អាតត្រូវបានបើកនៅលើវា។

អ្នកអាចដំណើរការម៉ាក្រូដែលបានបង្កើតនៅក្នុង Excel នៅលើផ្ទាំង អ្នកអភិវឌ្ឍ ប៊ូតុង ម៉ាក្រូ (អ្នកអភិវឌ្ឍន៍ — ម៉ាក្រូ) ឬផ្លូវកាត់ក្តារចុច alt+F8.

វិធីសាស្រ្ត 2. បង្កើតសំណួរជាច្រើននៅក្នុង Power Query

វិធីសាស្រ្តមុន សម្រាប់ភាពតូចតាច និងភាពសាមញ្ញរបស់វា មានគុណវិបត្តិយ៉ាងសំខាន់ - សន្លឹកដែលបង្កើតដោយម៉ាក្រូ មិនត្រូវបានធ្វើបច្ចុប្បន្នភាពទេ នៅពេលដែលការផ្លាស់ប្តូរត្រូវបានធ្វើឡើងនៅក្នុងតារាងលក់ដើម។ ប្រសិនបើការអាប់ដេតភ្លាមៗគឺជាការចាំបាច់ នោះអ្នកនឹងត្រូវប្រើកញ្ចប់សំណួរថាមពល VBA + ឬផ្ទុយទៅវិញ បង្កើតដោយប្រើម៉ាក្រូមិនត្រឹមតែសន្លឹកដែលមានទិន្នន័យឋិតិវន្តប៉ុណ្ណោះទេ ប៉ុន្តែសំណួរ Power Query បានធ្វើបច្ចុប្បន្នភាព។

ម៉ាក្រូ​នៅ​ក្នុង​ករណី​នេះ​គឺ​ស្រដៀង​គ្នា​នឹង​ផ្នែក​មុន (វា​ក៏​មាន​វដ្ដ​ដែរ។ សម្រាប់គ្នា… បន្ទាប់ ដើម្បី​ធ្វើ​ឡើងវិញ​លើ​ទីក្រុង​ក្នុង​ថត) ប៉ុន្តែ​នៅ​ក្នុង​រង្វិលជុំ​នឹង​លែង​មាន​ការ​ត្រង និង​ការ​ចម្លង​ទៀត​ហើយ ប៉ុន្តែ​ការ​បង្កើត​សំណួរ Power Query និង​ផ្ទុក​លទ្ធផល​របស់​វា​ទៅ​សន្លឹក​ថ្មី៖

Sub Splitter2() សម្រាប់ក្រឡានីមួយៗក្នុងជួរ("តារាងទីក្រុង") ActiveWorkbook.Queries.Add Name:=cell.Value, Formula:= _ "let" & Chr(13) & "" & Chr(10) & " ប្រភព = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""ប្រភេទបានផ្លាស់ប្តូរ" = Table.TransformColumnTypes(ប្រភព , {{""ប្រភេទ"", វាយអត្ថបទ}, {""ឈ្មោះ", វាយអត្ថបទ}, {""ទីក្រុង", វាយអត្ថបទ}, {""អ្នកគ្រប់គ្រង"", វាយអត្ថបទ}, {""កិច្ចព្រមព្រៀង date "", type datetime}, {""Cost"", type number}})," & Chr(13) & "" & Chr(10) & " #""Rows with filter"" = 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;ប្រភពទិន្នន័យ=$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 .RefreshOnFileSsert = False ។ អែល .SavePassword = មិនពិត . SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End With ActiveSheet.Name = cell.Value ក្រឡាបន្ទាប់ បញ្ចប់រង  

បន្ទាប់ពីការបើកដំណើរការរបស់វា យើងនឹងឃើញសន្លឹកដូចគ្នាតាមទីក្រុង ប៉ុន្តែសំណួរ Power Query ដែលបានបង្កើតរួចហើយនឹងបង្កើតជាវា៖

ជាមួយនឹងការផ្លាស់ប្តូរណាមួយនៅក្នុងទិន្នន័យប្រភព វានឹងគ្រប់គ្រាន់ដើម្បីធ្វើបច្ចុប្បន្នភាពតារាងដែលត្រូវគ្នាដោយប្រើប៊ូតុងកណ្ដុរខាងស្ដាំ - ពាក្យបញ្ជា ធ្វើបច្ចុប្បន្នភាព និងរក្សាទុក (ធ្វើ​ឱ្យ​ស្រស់) ឬអាប់ដេតទីក្រុងទាំងអស់ក្នុងពេលតែមួយក្នុងបរិមាណច្រើនដោយប្រើប៊ូតុង ធ្វើ​បច្ចុប្បន្នភាព​ទាំងអស់ ផ្ទាំង ទិន្នន័យ (ទិន្នន័យ - ផ្ទុកឡើងវិញទាំងអស់).

• តើម៉ាក្រូជាអ្វី របៀបបង្កើត និងប្រើប្រាស់វា។
• ការរក្សាទុកសន្លឹកសៀវភៅការងារជាឯកសារដាច់ដោយឡែក
• ការប្រមូលទិន្នន័យពីសន្លឹកទាំងអស់នៃសៀវភៅទៅក្នុងតារាងតែមួយ