ការប្រមូលផ្តុំតារាងពីឯកសារ Excel ផ្សេងៗគ្នាដោយប្រើ Power Query

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

សូមក្រឡេកមើលដំណោះស្រាយដ៏ស្រស់ស្អាតសម្រាប់ស្ថានភាពស្តង់ដារមួយដែលអ្នកប្រើប្រាស់ Excel ភាគច្រើនប្រឈមមុខនឹងឆាប់ឬក្រោយមក៖ អ្នកត្រូវប្រមូលទិន្នន័យយ៉ាងឆាប់រហ័ស និងដោយស្វ័យប្រវត្តិពីឯកសារមួយចំនួនធំទៅក្នុងតារាងចុងក្រោយមួយ។ 

ឧបមាថាយើងមានថតខាងក្រោមដែលមានឯកសារជាច្រើនដែលមានទិន្នន័យពីទីក្រុងសាខា៖

ចំនួនឯកសារមិនមានបញ្ហាទេ ហើយអាចនឹងផ្លាស់ប្តូរនៅពេលអនាគត។ ឯកសារនីមួយៗមានសន្លឹកដែលមានឈ្មោះ លក់កន្លែងដែលតារាងទិន្នន័យមានទីតាំងនៅ៖

ចំនួនជួរដេក (ការបញ្ជាទិញ) នៅក្នុងតារាង ពិតណាស់គឺខុសគ្នា ប៉ុន្តែសំណុំនៃជួរឈរគឺស្តង់ដារគ្រប់ទីកន្លែង។

កិច្ចការ៖ ដើម្បីប្រមូលទិន្នន័យពីឯកសារទាំងអស់ទៅក្នុងសៀវភៅមួយជាមួយនឹងការធ្វើបច្ចុប្បន្នភាពដោយស្វ័យប្រវត្តិជាបន្តបន្ទាប់នៅពេលបន្ថែម ឬលុបឯកសារទីក្រុង ឬជួរដេកក្នុងតារាង។ យោងតាមតារាងបង្រួបបង្រួមចុងក្រោយ បន្ទាប់មកវានឹងអាចបង្កើតរបាយការណ៍ណាមួយ តារាងជំនួយទិន្នន័យ ទិន្នន័យតម្រៀបតម្រងជាដើម។ រឿងសំខាន់គឺអាចប្រមូលបាន។

យើងជ្រើសរើសអាវុធ

សម្រាប់ដំណោះស្រាយ យើងត្រូវការកំណែចុងក្រោយបំផុតនៃ Excel 2016 (មុខងារចាំបាច់ត្រូវបានបង្កើតរួចហើយនៅក្នុងវាតាមលំនាំដើម) ឬកំណែមុននៃ Excel 2010-2013 ជាមួយនឹងកម្មវិធីបន្ថែមឥតគិតថ្លៃដែលបានដំឡើង។ សំណួរថាមពល ពី Microsoft (ទាញយកវានៅទីនេះ) ។ Power Query គឺជាឧបករណ៍ដែលអាចបត់បែនបាន និងមានប្រសិទ្ធភាពខ្ពស់សម្រាប់ការផ្ទុកទិន្នន័យទៅក្នុង Excel ពីពិភពខាងក្រៅ បន្ទាប់មកធ្វើការច្រូត និងដំណើរការវា។ Power Query គាំទ្រប្រភពទិន្នន័យដែលមានស្រាប់ស្ទើរតែទាំងអស់ - ពីឯកសារអត្ថបទទៅ SQL និងសូម្បីតែ Facebook 🙂

ប្រសិនបើអ្នកមិនមាន Excel 2013 ឬ 2016 នោះអ្នកមិនអាចអានបន្ថែមបានទេ (គ្រាន់តែនិយាយលេង)។ នៅក្នុងកំណែចាស់របស់ Excel ភារកិច្ចបែបនេះអាចសម្រេចបានតែដោយការសរសេរកម្មវិធីម៉ាក្រូនៅក្នុង Visual Basic (ដែលពិបាកសម្រាប់អ្នកចាប់ផ្តើមដំបូង) ឬដោយការចម្លងដោយដៃឯកកោ (ដែលចំណាយពេលយូរ និងបង្កើតកំហុស)។

ជំហាន 1. នាំចូលឯកសារមួយជាគំរូ

ជាដំបូង ចូរយើងនាំចូលទិន្នន័យពីសៀវភៅការងារមួយជាឧទាហរណ៍ ដូច្នេះ Excel "ចាប់យកគំនិត" ។ ដើម្បីធ្វើដូច្នេះ បង្កើតសៀវភៅការងារទទេថ្មី និង…

• ប្រសិនបើអ្នកមាន Excel 2016 បន្ទាប់មកបើកផ្ទាំង ទិន្នន័យ ហើយ​បន្ទាប់​មក បង្កើតសំណួរ - ពីឯកសារ - ពីសៀវភៅ (ទិន្នន័យ - សំណួរថ្មី - ពីឯកសារ - ពី Excel)
• ប្រសិនបើអ្នកមាន Excel 2010-2013 ជាមួយនឹងកម្មវិធីបន្ថែម Power Query ដែលបានដំឡើង បន្ទាប់មកបើកផ្ទាំង សំណួរថាមពល ហើយជ្រើសរើសនៅលើវា។ ពីឯកសារ - ពីសៀវភៅ (ពីឯកសារ - ពី Excel)

បន្ទាប់មក នៅក្នុងបង្អួចដែលបើក សូមចូលទៅកាន់ថតរបស់យើងជាមួយនឹងរបាយការណ៍ ហើយជ្រើសរើសឯកសារទីក្រុងណាមួយ (វាមិនមានបញ្ហាមួយណាទេ ព្រោះវាមានលក្ខណៈធម្មតាទាំងអស់)។ បន្ទាប់ពីពីរបីវិនាទី បង្អួចកម្មវិធីរុករកគួរតែលេចឡើង ដែលអ្នកត្រូវជ្រើសរើសសន្លឹកដែលយើងត្រូវការ (លក់) នៅផ្នែកខាងឆ្វេង ហើយមាតិការបស់វានឹងត្រូវបានបង្ហាញនៅផ្នែកខាងស្តាំ៖

ប្រសិនបើអ្នកចុចលើប៊ូតុងនៅជ្រុងខាងស្តាំខាងក្រោមនៃបង្អួចនេះ។ ទាញយក (ផ្ទុក)បន្ទាប់មកតារាងនឹងត្រូវបាននាំចូលភ្លាមៗទៅកាន់សន្លឹកក្នុងទម្រង់ដើមរបស់វា។ សម្រាប់ឯកសារតែមួយ វាល្អ ប៉ុន្តែយើងត្រូវផ្ទុកឯកសារបែបនេះជាច្រើន ដូច្នេះយើងនឹងខុសគ្នាបន្តិច ហើយចុចប៊ូតុង ការកែតម្រូវ (កែសម្រួល). បន្ទាប់ពីនោះ កម្មវិធីកែសំណួរ Power Query គួរតែត្រូវបានបង្ហាញនៅក្នុងបង្អួចដាច់ដោយឡែកមួយដែលមានទិន្នន័យរបស់យើងពីសៀវភៅ៖

នេះគឺជាឧបករណ៍ដ៏មានឥទ្ធិពលដែលអនុញ្ញាតឱ្យអ្នក "បញ្ចប់" តារាងតាមទិដ្ឋភាពដែលយើងត្រូវការ។ សូម្បី​តែ​ការ​ពិពណ៌នា​ផ្ទៃ​ខាងលើ​នៃ​មុខងារ​ទាំងអស់​របស់​វា​នឹង​ត្រូវ​ចំណាយ​ពេល​ប្រហែល​មួយ​រយ​ទំព័រ ប៉ុន្តែ​បើ​និយាយ​ដោយ​សង្ខេប​ដោយ​ប្រើ​វិនដូ​នេះ អ្នក​អាច៖

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

ជាឧទាហរណ៍ ចូរយើងបន្ថែមជួរឈរដែលមានឈ្មោះអត្ថបទនៃខែទៅក្នុងតារាងរបស់យើង ដូច្នេះនៅពេលក្រោយ វានឹងកាន់តែងាយស្រួលក្នុងការបង្កើតរបាយការណ៍តារាងជំនួយ។ ដើម្បីធ្វើដូចនេះចុចខាងស្តាំលើចំណងជើងជួរឈរ កាលបរិច្ឆេទហើយជ្រើសរើសពាក្យបញ្ជា ស្ទួនជួរឈរ (ស្ទួនជួរឈរ)ហើយបន្ទាប់មកចុចខាងស្តាំលើបឋមកថានៃជួរឈរស្ទួនដែលលេចឡើងហើយជ្រើសរើសពាក្យបញ្ជា ការផ្លាស់ប្តូរ - ខែ - ឈ្មោះខែ:

ជួរ​ឈរ​ថ្មី​គួរ​ត្រូវ​បាន​បង្កើត​ជា​មួយ​នឹង​ឈ្មោះ​អត្ថបទ​នៃ​ខែ​សម្រាប់​ជួរ​ដេក​នីមួយៗ។ ដោយចុចពីរដងលើក្បាលជួរឈរ អ្នកអាចប្តូរឈ្មោះវាពី ចម្លងកាលបរិច្ឆេទ ដើម្បីឱ្យកាន់តែមានផាសុកភាព ខែ, ឧ។

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

អ្នកអាចដកចេញបន្ទាត់ដែលមានកំហុស ឬបន្ទាត់ទទេ ក៏ដូចជាអ្នកគ្រប់គ្រង ឬអតិថិជនដែលមិនចាំបាច់ ដោយប្រើតម្រងដ៏សាមញ្ញមួយ៖

ជាងនេះទៅទៀត ការបំប្លែងដែលបានអនុវត្តទាំងអស់ត្រូវបានជួសជុលនៅក្នុងបន្ទះខាងស្តាំ ដែលពួកគេតែងតែអាចបង្វិលត្រឡប់មកវិញ (ឆ្លងកាត់) ឬផ្លាស់ប្តូរប៉ារ៉ាម៉ែត្ររបស់ពួកគេ (ឧបករណ៍):

ស្រាល​និង​ស្រស់​ស្អាត​មែន​ទេ?

ជំហានទី 2. ចូរបំប្លែងសំណើរបស់យើងទៅជាមុខងារមួយ។

ដើម្បីបន្តការបំប្លែងទិន្នន័យទាំងអស់ដែលបានធ្វើឡើងសម្រាប់សៀវភៅដែលបាននាំចូលនីមួយៗ យើងត្រូវបំប្លែងសំណើដែលបានបង្កើតរបស់យើងទៅជាមុខងារ ដែលបន្ទាប់មកនឹងត្រូវបានអនុវត្តទៅឯកសារទាំងអស់របស់យើង។ ដើម្បីធ្វើនេះគឺពិតជាសាមញ្ញណាស់។

នៅក្នុង Query Editor សូមចូលទៅកាន់ផ្ទាំង View ហើយចុចប៊ូតុង កម្មវិធីនិពន្ធកម្រិតខ្ពស់ (មើល - កម្មវិធីនិពន្ធកម្រិតខ្ពស់). បង្អួចគួរតែបើកដែលសកម្មភាពពីមុនទាំងអស់របស់យើងនឹងត្រូវបានសរសេរជាទម្រង់កូដជាភាសា M ។ សូមចំណាំថាផ្លូវទៅកាន់ឯកសារដែលយើងបាននាំចូលសម្រាប់ឧទាហរណ៍គឺ hardcoded នៅក្នុងកូដ៖

ឥឡូវនេះសូមធ្វើការកែតម្រូវពីរបី៖

អត្ថន័យរបស់ពួកគេគឺសាមញ្ញ: បន្ទាត់ទីមួយ (ឯកសារផ្លូវ) => ប្រែដំណើរការរបស់យើងទៅជាមុខងារដែលមានអាគុយម៉ង់ ផ្លូវឯកសារហើយខាងក្រោមយើងប្តូរផ្លូវថេរទៅតម្លៃនៃអថេរនេះ។ 

ទាំងអស់។ ចុចលើ បញ្ចប់ ហើយគួរតែមើលនេះ៖

កុំខ្លាចថាទិន្នន័យបានបាត់ - តាមពិតអ្វីៗមិនអីទេ អ្វីគ្រប់យ៉ាងគួរតែមើលទៅដូចនេះ 🙂 យើងបានបង្កើតមុខងារផ្ទាល់ខ្លួនរបស់យើងដោយជោគជ័យ ដែលក្បួនដោះស្រាយទាំងមូលសម្រាប់ការនាំចូល និងដំណើរការទិន្នន័យត្រូវបានចងចាំដោយមិនចាំបាច់ភ្ជាប់ជាមួយឯកសារជាក់លាក់ណាមួយឡើយ។ . វានៅសល់ដើម្បីផ្តល់ឱ្យវានូវឈ្មោះដែលអាចយល់បានបន្ថែមទៀត (ឧទាហរណ៍ ទទួលទិន្នន័យ) នៅក្នុងបន្ទះនៅខាងស្តាំក្នុងវាល ឈ្មោះជាលើកដំបូង ហើយអ្នកអាចច្រូតកាត់បាន។ ទំព័រដើម - បិទ និងទាញយក (ទំព័រដើម - បិទ និងផ្ទុក). សូមចំណាំថាផ្លូវទៅកាន់ឯកសារដែលយើងបាននាំចូលសម្រាប់ឧទាហរណ៍គឺរឹងនៅក្នុងកូដ។ អ្នក​នឹង​ត្រឡប់​ទៅ​បង្អួច Microsoft Excel មេ ប៉ុន្តែ​បន្ទះ​ដែល​បាន​បង្កើត​ការ​តភ្ជាប់​ទៅ​មុខងារ​របស់​យើង​គួរ​តែ​បង្ហាញ​នៅ​ខាង​ស្ដាំ៖

ជំហានទី 3. ការប្រមូលឯកសារទាំងអស់។

ផ្នែកដែលពិបាកបំផុតគឺនៅពីក្រោយ ផ្នែកដែលរីករាយ និងងាយស្រួលនៅតែមាន។ ចូលទៅកាន់ផ្ទាំង ទិន្នន័យ - បង្កើតសំណួរ - ពីឯកសារ - ពីថត (ទិន្នន័យ — សំណួរថ្មី — ពីឯកសារ — ពីថត) ឬប្រសិនបើអ្នកមាន Excel 2010-2013 ស្រដៀងនឹងផ្ទាំង សំណួរថាមពល. នៅក្នុងបង្អួចដែលលេចឡើង បញ្ជាក់ថតដែលឯកសារទីក្រុងប្រភពទាំងអស់របស់យើងមានទីតាំងនៅ ហើយចុច OK. ជំហានបន្ទាប់គួរតែបើកបង្អួចដែលឯកសារ Excel ទាំងអស់ដែលបានរកឃើញនៅក្នុងថតនេះ (និងថតរងរបស់វា) ហើយព័ត៌មានលម្អិតសម្រាប់ពួកវានីមួយៗនឹងត្រូវបានរាយបញ្ជី៖

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

ឥឡូវនេះយើងត្រូវបន្ថែមជួរឈរមួយទៀតទៅក្នុងតារាងរបស់យើងជាមួយនឹងមុខងារដែលបានបង្កើតរបស់យើង ដែលនឹង "ទាញ" ទិន្នន័យពីឯកសារនីមួយៗ។ ដើម្បីធ្វើដូចនេះចូលទៅកាន់ផ្ទាំង បន្ថែមជួរឈរ - ជួរឈរផ្ទាល់ខ្លួន (បន្ថែមជួរឈរ - បន្ថែមជួរឈរផ្ទាល់ខ្លួន) ហើយនៅក្នុងបង្អួចដែលលេចឡើងសូមបញ្ចូលមុខងាររបស់យើង។ ទទួលទិន្នន័យបញ្ជាក់សម្រាប់វាជាអាគុយម៉ង់ ផ្លូវពេញលេញទៅកាន់ឯកសារនីមួយៗ៖

បន្ទាប់ពីចុចលើ OK ជួរឈរដែលបានបង្កើតគួរតែត្រូវបានបន្ថែមទៅតារាងរបស់យើងនៅខាងស្តាំ។

ឥឡូវ​យើង​លុប​ជួរ​ឈរ​ដែល​មិន​ចាំ​បាច់​ទាំង​អស់ (ដូច​ក្នុង Excel ដោយ​ប្រើ​ប៊ូតុង​កណ្ដុរ​ខាង​ស្ដាំ – យក) ទុក​តែ​ជួរ​ឈរ​ដែល​បាន​បន្ថែម និង​ជួរ​ឈរ​ដែល​មាន​ឈ្មោះ​ឯកសារ ព្រោះ​ឈ្មោះ​នេះ (ច្បាស់​ជាង​នេះ​ទៅ​ទៀត ទីក្រុង) នឹង​មាន​ប្រយោជន៍​ក្នុង​ការ​មាន​ទិន្នន័យ​សរុប​សម្រាប់​ជួរ​នីមួយៗ។

ហើយឥឡូវនេះ "wow moment" - ចុចលើរូបតំណាងដែលមានព្រួញផ្ទាល់របស់វានៅជ្រុងខាងលើខាងស្តាំនៃជួរឈរដែលបានបន្ថែមជាមួយនឹងមុខងាររបស់យើង:

… ដោះធីក ប្រើឈ្មោះជួរឈរដើមជាបុព្វបទ (ប្រើឈ្មោះជួរឈរដើមជាបុព្វបទ)ហើយចុច OK. ហើយមុខងាររបស់យើងនឹងផ្ទុក និងដំណើរការទិន្នន័យពីឯកសារនីមួយៗ ដោយធ្វើតាមក្បួនដោះស្រាយដែលបានកត់ត្រា និងប្រមូលអ្វីៗគ្រប់យ៉ាងនៅក្នុងតារាងធម្មតាមួយ៖

ដើម្បីភាពស្រស់ស្អាតពេញលេញ អ្នកក៏អាចដកផ្នែកបន្ថែម .xlsx ចេញពីជួរទីមួយដែលមានឈ្មោះឯកសារ - ដោយការជំនួសស្តង់ដារដោយ "គ្មានអ្វី" (ចុចខាងស្តាំលើបឋមកថាជួរឈរ - ជំនួស) ហើយប្តូរឈ្មោះជួរឈរនេះទៅជា រាជធានី ខេត្ត. ហើយក៏កែទម្រង់ទិន្នន័យក្នុងជួរឈរជាមួយកាលបរិច្ឆេទផងដែរ។

ទាំងអស់! ចុចលើ ទំព័រដើម - បិទនិងផ្ទុក (ទំព័រដើម - បិទ និងផ្ទុក). ទិន្នន័យទាំងអស់ដែលប្រមូលបានដោយសំណួរសម្រាប់ទីក្រុងទាំងអស់នឹងត្រូវបានបញ្ចូលទៅក្នុងសន្លឹក Excel បច្ចុប្បន្នក្នុងទម្រង់ "តារាងឆ្លាតវៃ"៖

ការតភ្ជាប់ដែលបានបង្កើត និងមុខងារជួបប្រជុំគ្នារបស់យើង មិនចាំបាច់រក្សាទុកដោយឡែកពីគ្នាតាមមធ្យោបាយណាមួយឡើយ ពួកវាត្រូវបានរក្សាទុករួមគ្នាជាមួយឯកសារបច្ចុប្បន្នតាមរបៀបធម្មតា។

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

PS

វិសោធនកម្ម។ បន្ទាប់ពីការអាប់ដេតខែមករាឆ្នាំ 2017 Power Query បានរៀនពីរបៀបប្រមូលសៀវភៅការងារ Excel ដោយខ្លួនឯង ពោលគឺមិនចាំបាច់បង្កើតមុខងារដាច់ដោយឡែកទៀតទេ - វាកើតឡើងដោយស្វ័យប្រវត្តិ។ ដូច្នេះជំហានទីពីរពីអត្ថបទនេះលែងត្រូវការទៀតហើយ ហើយដំណើរការទាំងមូលកាន់តែសាមញ្ញគួរឱ្យកត់សម្គាល់៖

1. ជ្រើស បង្កើតសំណើ - ពីឯកសារ - ពីថត - ជ្រើសរើសថត - យល់ព្រម
2. បន្ទាប់ពីបញ្ជីឯកសារលេចឡើងសូមចុច ការផ្លាស់ប្តូរ
3. នៅក្នុងបង្អួចកម្មវិធីនិពន្ធសំណួរ ពង្រីកជួរឈរគោលពីរដោយប្រើព្រួញទ្វេ ហើយជ្រើសរើសឈ្មោះសន្លឹកដែលត្រូវយកចេញពីឯកសារនីមួយៗ

ហើយនោះជាទាំងអស់! បទ!

• រចនាឡើងវិញនូវផ្ទាំងឈើឆ្កាងទៅជាផ្ទះល្វែងដែលសមរម្យសម្រាប់ការសាងសង់តារាងជំនួយ
• បង្កើតគំនូសតាងពពុះដែលមានចលនានៅក្នុង Power View
• ម៉ាក្រូ ដើម្បីប្រមូលផ្តុំសន្លឹកពីឯកសារ Excel ផ្សេងៗទៅជាមួយ។