តារាង Pivot ឆ្លងកាត់ជួរទិន្នន័យច្រើន។

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

តារាង Pivot គឺជាឧបករណ៍ដ៏អស្ចារ្យបំផុតមួយនៅក្នុង Excel ។ ប៉ុន្តែរហូតមកដល់ពេលនេះ ជាអកុសល គ្មានកំណែ Excel ណាអាចធ្វើរឿងសាមញ្ញ និងចាំបាច់បានភ្លាមៗនោះទេ ដូចជាការកសាងសេចក្តីសង្ខេបសម្រាប់ជួរទិន្នន័យដំបូងជាច្រើនដែលមានទីតាំង ឧទាហរណ៍ នៅលើសន្លឹកផ្សេងៗគ្នា ឬក្នុងតារាងផ្សេងៗគ្នា៖

មុន​នឹង​យើង​ចាប់​ផ្តើ​ម​សូម​បញ្ជាក់​ពីរ​បី​ចំណុច​។ ជាអាទិភាព ខ្ញុំជឿថាលក្ខខណ្ឌខាងក្រោមត្រូវបានបំពេញនៅក្នុងទិន្នន័យរបស់យើង៖

• តារាងអាចមានចំនួនជួរដេកដែលមានទិន្នន័យណាមួយ ប៉ុន្តែពួកវាត្រូវតែមានបឋមកថាដូចគ្នា។
• មិនគួរមានទិន្នន័យបន្ថែមនៅលើសន្លឹកដែលមានតារាងប្រភពទេ។ មួយសន្លឹក - តុមួយ។ ដើម្បីគ្រប់គ្រង ខ្ញុំណែនាំអ្នកឱ្យប្រើផ្លូវកាត់ក្តារចុច បញ្ជា (Ctrl)+បញ្ចប់ដែលផ្លាស់ទីអ្នកទៅកាន់ក្រឡាដែលបានប្រើចុងក្រោយនៅក្នុងសន្លឹកកិច្ចការ។ តាមឧត្ដមគតិ នេះគួរតែជាក្រឡាចុងក្រោយនៅក្នុងតារាងទិន្នន័យ។ ប្រសិនបើអ្នកចុចលើ បញ្ជា (Ctrl)+បញ្ចប់ ក្រឡាទទេណាមួយនៅខាងស្តាំ ឬខាងក្រោមតារាងត្រូវបានបន្លិច – លុបជួរឈរទទេទាំងនេះទៅខាងស្តាំ ឬជួរដេកខាងក្រោមតារាង បន្ទាប់ពីតារាង ហើយរក្សាទុកឯកសារ។

វិធីសាស្រ្តទី 1: បង្កើតតារាងសម្រាប់ pivot ដោយប្រើ Power Query

ចាប់ផ្តើមពីកំណែ 2010 សម្រាប់ Excel មានកម្មវិធីបន្ថែម Power Query ឥតគិតថ្លៃ ដែលអាចប្រមូល និងបំប្លែងទិន្នន័យណាមួយ ហើយបន្ទាប់មកផ្តល់ឱ្យវាជាប្រភពសម្រាប់បង្កើតតារាងជំនួយទិន្នន័យ។ ការដោះស្រាយបញ្ហារបស់យើងដោយមានជំនួយពីការបន្ថែមនេះមិនពិបាកទាល់តែសោះ។

ដំបូង យើងបង្កើតឯកសារទទេថ្មីមួយនៅក្នុង Excel – ការជួបប្រជុំគ្នានឹងប្រព្រឹត្តទៅនៅក្នុងវា ហើយបន្ទាប់មកតារាងជំនួយទិន្នន័យនឹងត្រូវបានបង្កើតនៅក្នុងវា។

បន្ទាប់មកនៅលើផ្ទាំង ទិន្នន័យ (ប្រសិនបើអ្នកមាន Excel 2016 ឬក្រោយ) ឬនៅលើផ្ទាំង សំណួរថាមពល (ប្រសិនបើអ្នកមាន Excel 2010-2013) ជ្រើសរើសពាក្យបញ្ជា បង្កើតសំណួរ - ពីឯកសារ - Excel (ទទួលបានទិន្នន័យ - ពីឯកសារ - Excel) ហើយបញ្ជាក់ឯកសារប្រភពជាមួយតារាងដែលត្រូវប្រមូល៖

នៅក្នុងបង្អួចដែលលេចឡើងសូមជ្រើសរើសសន្លឹកណាមួយ (វាមិនមានបញ្ហាអ្វីទេ) ហើយចុចប៊ូតុងខាងក្រោម ការផ្លាស់ប្តូរ (កែសម្រួល):

បង្អួច Power Query Editor គួរតែបើកនៅផ្នែកខាងលើនៃ Excel ។ នៅជ្រុងខាងស្តាំនៃបង្អួចនៅលើបន្ទះ ស្នើសុំប៉ារ៉ាម៉ែត្រ លុបជំហានដែលបានបង្កើតដោយស្វ័យប្រវត្តិទាំងអស់ លើកលែងតែជំហានដំបូង - ប្រភព (ប្រភព):

ឥឡូវនេះយើងឃើញបញ្ជីទូទៅនៃសន្លឹកទាំងអស់។ ប្រសិនបើបន្ថែមលើសន្លឹកទិន្នន័យមានសន្លឹកចំហៀងផ្សេងទៀតនៅក្នុងឯកសារនោះ នៅជំហាននេះ ភារកិច្ចរបស់យើងគឺជ្រើសរើសតែសន្លឹកទាំងនោះដែលព័ត៌មានត្រូវផ្ទុក ដោយមិនរាប់បញ្ចូលសន្លឹកផ្សេងទៀតទាំងអស់ដោយប្រើតម្រងនៅក្នុងបឋមកថាតារាង៖

លុបជួរឈរទាំងអស់លើកលែងតែជួរឈរ ទិន្នន័យដោយចុចខាងស្តាំលើក្បាលជួរឈរ ហើយជ្រើសរើស លុបជួរឈរផ្សេងទៀត។ (ដកចេញ ជួរឈរផ្សេងទៀត):

បន្ទាប់​មក អ្នក​អាច​ពង្រីក​មាតិកា​នៃ​តារាង​ដែល​ប្រមូល​បាន​ដោយ​ចុច​លើ​សញ្ញា​ព្រួញ​ពីរ​នៅ​កំពូល​នៃ​ជួរ​ឈរ (ប្រអប់​ធីក ប្រើឈ្មោះជួរឈរដើមជាបុព្វបទ អ្នកអាចបិទវាបាន)៖

ប្រសិនបើអ្នកធ្វើអ្វីគ្រប់យ៉ាងបានត្រឹមត្រូវ នោះនៅចំណុចនេះ អ្នកគួរតែឃើញមាតិកានៃតារាងទាំងអស់ដែលប្រមូលបានមួយខាងក្រោមមួយទៀត៖

វានៅសល់ដើម្បីលើកជួរទីមួយទៅក្បាលតារាងដោយប្រើប៊ូតុង ប្រើបន្ទាត់ទីមួយជាបឋមកថា (ប្រើជួរទីមួយជាបឋមកថា) ផ្ទាំង ទំព័រដើម (ទំព័រដើម) ហើយលុបបឋមកថាតារាងស្ទួនចេញពីទិន្នន័យដោយប្រើតម្រង៖

រក្សាទុកអ្វីគ្រប់យ៉ាងដែលបានធ្វើដោយប្រើពាក្យបញ្ជា បិទហើយផ្ទុក - បិទហើយផ្ទុកក្នុង… (បិទ និងផ្ទុក — បិទ និងផ្ទុកទៅ… ) ផ្ទាំង ទំព័រដើម (ទំព័រដើម)ហើយនៅក្នុងបង្អួចដែលបើក សូមជ្រើសរើសជម្រើស ការតភ្ជាប់តែប៉ុណ្ណោះ (ការតភ្ជាប់តែប៉ុណ្ណោះ):

អ្វីគ្រប់យ៉ាង។ វានៅសល់តែដើម្បីកសាងសេចក្តីសង្ខេបប៉ុណ្ណោះ។ ដើម្បីធ្វើដូចនេះចូលទៅកាន់ផ្ទាំង បញ្ចូល - តារាង Pivot (បញ្ចូល — តារាង Pivot), ជ្រើសរើសជម្រើស ប្រើប្រភពទិន្នន័យខាងក្រៅ (ប្រើប្រភពទិន្នន័យខាងក្រៅ)ហើយបន្ទាប់មកដោយចុចប៊ូតុង ជ្រើសរើសការតភ្ជាប់, សំណើរបស់យើង។ ការបង្កើត និងការកំណត់បន្ថែមនៃ pivot កើតឡើងតាមរបៀបស្តង់ដារទាំងស្រុងដោយអូសវាលដែលយើងត្រូវការទៅក្នុងជួរដេក ជួរឈរ និងតំបន់តម្លៃ៖

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

វិធីសាស្រ្ត 2. យើងបង្រួបបង្រួមតារាងជាមួយពាក្យបញ្ជា UNION SQL ក្នុងម៉ាក្រូ

ដំណោះស្រាយមួយទៀតចំពោះបញ្ហារបស់យើងត្រូវបានតំណាងដោយម៉ាក្រូនេះ ដែលបង្កើតសំណុំទិន្នន័យ (ឃ្លាំងសម្ងាត់) សម្រាប់តារាងជំនួយទិន្នន័យដោយប្រើពាក្យបញ្ជា ការ​រួបរួម ភាសាសំណួរ SQL ។ ពាក្យបញ្ជានេះរួមបញ្ចូលគ្នានូវតារាងពីទាំងអស់ដែលបានបញ្ជាក់នៅក្នុងអារេ ឈ្មោះសន្លឹក សន្លឹកសៀវភៅទៅក្នុងតារាងទិន្នន័យតែមួយ។ នោះគឺជំនួសឱ្យការចម្លងនិងបិទភ្ជាប់ជួរពីសន្លឹកផ្សេងគ្នាទៅមួយ យើងធ្វើដូចគ្នានៅក្នុង RAM របស់កុំព្យូទ័រ។ បន្ទាប់មកម៉ាក្រូបន្ថែមសន្លឹកថ្មីដែលមានឈ្មោះដែលបានផ្តល់ឱ្យ (អថេរ ឈ្មោះសន្លឹកលទ្ធផល) និងបង្កើតការសង្ខេបពេញលេញ (!) លើវាដោយផ្អែកលើឃ្លាំងសម្ងាត់ដែលបានប្រមូល។

ដើម្បីប្រើម៉ាក្រូ សូមប្រើប៊ូតុង 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 'ឈ្មោះសន្លឹកដែល pivot លទ្ធផលនឹងត្រូវបានបង្ហាញ" សន្លឹក Result 'Pivar ឈ្មោះជាមួយតារាងប្រភព SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'យើងបង្កើតឃ្លាំងសម្ងាត់សម្រាប់តារាងពីសន្លឹកពី SheetsNames ជាមួយ ActiveWorkbook ReDim arSQL(1 ទៅ (UBound(SheetsNames) + 1)) ) សម្រាប់ i = LBound (SheetsNames) ទៅ 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 ) បញ្ចប់ដោយ 'បង្កើតសន្លឹកឡើងវិញ ដើម្បីបង្ហាញតារាងជំនួយទិន្នន័យលទ្ធផល On Error Resume Next Application.DisplayAlerts = False Worksheets(ResultSheetName).Delete Set wsPivot = Worksheets.Add wsPivot ។ Name = ResultSheetName 'បង្ហាញការសង្ខេបឃ្លាំងសម្ងាត់ដែលបានបង្កើតនៅលើសន្លឹកនេះ Set objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) កំណត់ objPivotCache.Recordset = objRS កំណត់ objRS = "គ្មានអ្វីជាមួយ wsPivot objPivotCache.CreatePivotation"Test objPivotCache = Nothing Range("A3") ជ្រើសរើស End With End Sub    

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

គុណវិបត្តិនៃវិធីសាស្រ្តនេះ:

• ទិន្នន័យ​មិន​ត្រូវ​បាន​ធ្វើ​បច្ចុប្បន្នភាព​ទេ ដោយសារ​ឃ្លាំង​សម្ងាត់​មិន​មាន​ការ​តភ្ជាប់​ទៅ​តារាង​ប្រភព។ ប្រសិនបើអ្នកផ្លាស់ប្តូរទិន្នន័យប្រភព អ្នកត្រូវតែដំណើរការម៉ាក្រូម្តងទៀត ហើយបង្កើតសេចក្តីសង្ខេបម្តងទៀត។
• នៅពេលផ្លាស់ប្តូរចំនួនសន្លឹក ចាំបាច់ត្រូវកែសម្រួលកូដម៉ាក្រូ (អារេ ឈ្មោះសន្លឹក).
  

ប៉ុន្តែនៅទីបញ្ចប់ យើងទទួលបានតារាងជំនួយទិន្នន័យពេញលេញ ដែលបង្កើតឡើងនៅលើជួរជាច្រើនពីសន្លឹកផ្សេងៗគ្នា៖

វ៉ី!

កំណត់សម្គាល់បច្ចេកទេស៖ ប្រសិនបើអ្នកទទួលបានកំហុសដូចជា "អ្នកផ្តល់សេវាមិនបានចុះឈ្មោះ" នៅពេលដំណើរការម៉ាក្រូ នោះទំនងជាអ្នកមានកំណែ 64 ប៊ីតនៃ Excel ឬកំណែ Office មិនពេញលេញត្រូវបានដំឡើង (គ្មានការចូលប្រើ) ។ ដើម្បីជួសជុលស្ថានភាព សូមជំនួសបំណែកនៅក្នុងកូដម៉ាក្រូ៖

	 អ្នកផ្គត់ផ្គង់=Microsoft.Jet.OLEDB.4.0;  

ទៅ:

	អ្នកផ្គត់ផ្គង់=Microsoft.ACE.OLEDB.12.0;  

ហើយទាញយក និងដំឡើងម៉ាស៊ីនដំណើរការទិន្នន័យឥតគិតថ្លៃពី Access ពីគេហទំព័រ Microsoft – Microsoft Access Database Engine 2010 អាចចែកចាយឡើងវិញបាន។

វិធីសាស្រ្តទី 3: បង្រួបបង្រួមអ្នកជំនួយការ PivotTable ពីកំណែចាស់នៃ Excel

វិធីសាស្រ្តនេះគឺហួសសម័យបន្តិច ប៉ុន្តែនៅតែមានតម្លៃនិយាយ។ និយាយជាផ្លូវការនៅក្នុងកំណែទាំងអស់រហូតដល់ឆ្នាំ 2003 មានជម្រើសមួយនៅក្នុង PivotTable Wizard ដើម្បី "បង្កើត pivot សម្រាប់ជួររួមបញ្ចូលគ្នាជាច្រើន" ។ ទោះជាយ៉ាងណាក៏ដោយ របាយការណ៍ដែលត្រូវបានបង្កើតឡើងតាមរបៀបនេះ ជាអកុសលនឹងគ្រាន់តែជារូបរាងដ៏គួរឱ្យអាណិតនៃសេចក្តីសង្ខេបពេញលេញពិតប្រាកដ ហើយមិនគាំទ្រ "បន្ទះសៀគ្វី" ជាច្រើននៃតារាងជំនួយធម្មតាទេ៖

នៅក្នុង pivot បែបនេះ មិនមានចំណងជើងជួរឈរនៅក្នុងបញ្ជីវាល គ្មានការកំណត់រចនាសម្ព័ន្ធដែលអាចបត់បែនបាន សំណុំនៃមុខងារដែលបានប្រើត្រូវបានកំណត់ ហើយជាទូទៅ ទាំងអស់នេះមិនស្រដៀងទៅនឹងតារាងជំនួយទិន្នន័យទេ។ ប្រហែលជានោះហើយជាមូលហេតុដែលចាប់ផ្តើមនៅឆ្នាំ 2007 ក្រុមហ៊ុន Microsoft បានដកមុខងារនេះចេញពីប្រអប់ស្តង់ដារ នៅពេលបង្កើតរបាយការណ៍តារាងជំនួយ។ ឥឡូវនេះមុខងារនេះអាចប្រើបានតែតាមរយៈប៊ូតុងផ្ទាល់ខ្លួនប៉ុណ្ណោះ។ អ្នកជំនួយតារាង PivotTable(អ្នកជំនួយតារាង Pivot)ដែលប្រសិនបើចង់បាន អាចត្រូវបានបន្ថែមទៅរបារឧបករណ៍រហ័សតាមរយៈ ឯកសារ - ជម្រើស - ប្ដូររបារឧបករណ៍ចូលរហ័សតាមបំណង - ពាក្យបញ្ជាទាំងអស់។ (ឯកសារ — ជម្រើស — ប្ដូរ​របារ​ឧបករណ៍​ចូល​រហ័ស​តាម​បំណង — ពាក្យ​បញ្ជា​ទាំង​អស់):

បន្ទាប់ពីចុចលើប៊ូតុងបន្ថែម អ្នកត្រូវជ្រើសរើសជម្រើសដែលសមស្របនៅជំហានដំបូងនៃអ្នកជំនួយការ៖

ហើយបន្ទាប់មកនៅក្នុងបង្អួចបន្ទាប់ សូមជ្រើសរើសជួរនីមួយៗជាវេន ហើយបន្ថែមវាទៅក្នុងបញ្ជីទូទៅ៖

ប៉ុន្តែជាថ្មីម្តងទៀត នេះមិនមែនជាការសង្ខេបពេញលេញទេ ដូច្នេះកុំរំពឹងច្រើនពេកពីវា។ ខ្ញុំអាចណែនាំជម្រើសនេះតែក្នុងករណីសាមញ្ញបំផុត។

• ការបង្កើតរបាយការណ៍ជាមួយ PivotTables
• រៀបចំការគណនានៅក្នុង PivotTables
• តើម៉ាក្រូជាអ្វី របៀបប្រើពួកវា កន្លែងចម្លងកូដ VBA ។ល។
• ការប្រមូលទិន្នន័យពីសន្លឹកច្រើនទៅមួយ (PLEX add-on)