Power Query සමඟ විවිධ Excel ගොනු වලින් වගු එකලස් කිරීම

ගැටලුව සකස් කිරීම

බොහෝ එක්සෙල් භාවිතා කරන්නන් ඉක්මනින් හෝ පසුව මුහුණ දෙන ඉතා සම්මත තත්වයන්ගෙන් එකක් සඳහා අලංකාර විසඳුමක් දෙස බලමු: ඔබට ගොනු විශාල සංඛ්‍යාවකින් එක් අවසාන වගුවකට ඉක්මනින් සහ ස්වයංක්‍රීයව දත්ත රැස් කළ යුතුය. 

ශාඛා නගර වලින් දත්ත සහිත ගොනු කිහිපයක් අඩංගු පහත ෆෝල්ඩරය අප සතුව ඇතැයි සිතමු:

ගොනු ගණන වැදගත් නොවන අතර අනාගතයේදී වෙනස් විය හැක. සෑම ගොනුවක්ම නමින් පත්‍රයක් ඇත විකුණුම්දත්ත වගුව පිහිටා ඇති ස්ථානය:

වගු වල පේළි ගණන (ඇණවුම්) ඇත්ත වශයෙන්ම වෙනස් ය, නමුත් තීරු කට්ටලය සෑම තැනකම සම්මත වේ.

කාර්යය: වගු තුළ නගර ගොනු හෝ පේළි එකතු කිරීමේදී හෝ මකා දැමීමේදී ස්වයංක්‍රීය යාවත්කාලීන කිරීම් සමඟ සියලුම ගොනු වලින් දත්ත එක් පොතකට එකතු කිරීම. අවසාන ඒකාබද්ධ වගුවට අනුව, එවිට ඕනෑම වාර්තා, විවර්තන වගු, පෙරහන් වර්ග කිරීමේ දත්ත ආදිය ගොඩනගා ගැනීමට හැකි වනු ඇත. ප්රධාන දෙය නම් එකතු කිරීමට හැකි වීමයි.

අපි ආයුධ තෝරා ගනිමු

විසඳුම සඳහා, අපට Excel 2016 හි නවතම අනුවාදය (අවශ්‍ය ක්‍රියාකාරිත්වය පෙරනිමියෙන් දැනටමත් ගොඩනගා ඇත) හෝ නොමිලේ ස්ථාපනය කර ඇති Excel 2010-2013 හි පෙර අනුවාද අවශ්‍ය වේ. බල විමසුම Microsoft වෙතින් (එය මෙතැනින් බාගන්න). Power Query යනු බාහිර ලෝකයෙන් Excel වෙත දත්ත පැටවීම සහ එය ඉවත් කිරීම සහ සැකසීම සඳහා සුපිරි නම්‍යශීලී සහ සුපිරි බලවත් මෙවලමකි. Power Query දැනට පවතින සියලුම දත්ත මූලාශ්‍ර සඳහා සහය දක්වයි - පෙළ ගොනු සිට SQL දක්වා සහ Facebook 🙂

ඔබට Excel 2013 හෝ 2016 නොමැති නම්, ඔබට තවදුරටත් කියවිය නොහැක (විහිළුවට). Excel හි පැරණි අනුවාද වලදී, එවැනි කාර්යයක් සිදු කළ හැක්කේ විෂුවල් බේසික් (ආරම්භකයින් සඳහා ඉතා අපහසු) මැක්‍රෝවක් ක්‍රමලේඛනය කිරීමෙන් හෝ ඒකාකාරී අතින් පිටපත් කිරීමෙන් (එය දිගු කාලයක් ගත වන අතර දෝෂ ඇති කරයි) පමණි.

පියවර 1. නියැදියක් ලෙස එක් ගොනුවක් ආයාත කරන්න

පළමුව, අපි උදාහරණයක් ලෙස එක් වැඩපොතකින් දත්ත ආයාත කරමු, එවිට Excel "අදහස ලබා ගනී". මෙය සිදු කිරීම සඳහා, නව හිස් වැඩපොතක් සාදන්න සහ...

• ඔබට Excel 2016 තිබේ නම්, ටැබය විවෘත කරන්න දත්ත ඊළගට විමසුම සාදන්න - ගොනුවෙන් - පොතෙන් (දත්ත — නව විමසුම- ගොනුවෙන් — Excel වෙතින්)
• ඔබ සතුව Excel 2010-2013 බලය විමසුම් ඇඩෝනය ස්ථාපනය කර ඇත්නම්, ටැබය විවෘත කරන්න බල විමසුම සහ එය මත තෝරන්න ගොනුවෙන් - පොතෙන් (ගොනුවෙන් - Excel වෙතින්)

ඉන්පසුව, විවෘත වන කවුළුව තුළ, වාර්තා සමඟ අපගේ ෆෝල්ඩරය වෙත ගොස් ඕනෑම නගර ගොනු තෝරන්න (එය කුමන එකක්ද යන්න ගැටළුවක් නොවේ, මන්ද ඒවා සියල්ලම සාමාන්යයි). තත්පර කිහිපයකට පසු, Navigator කවුළුව දිස්විය යුතුය, එහිදී ඔබට වම් පැත්තේ අපට අවශ්‍ය පත්‍රය (විකුණුම්) තෝරා ගත යුතු අතර එහි අන්තර්ගතය දකුණු පැත්තේ දර්ශනය වේ:

ඔබ මෙම කවුළුවේ පහළ දකුණු කෙළවරේ ඇති බොත්තම මත ක්ලික් කළහොත් බාගත (පූරණය), එවිට වගුව එහි මුල් ස්වරූපයෙන් පත්රය වෙත වහාම ආනයනය කරනු ලැබේ. තනි ගොනුවක් සඳහා, මෙය හොඳයි, නමුත් අපට එවැනි ගොනු බොහොමයක් පැටවීමට අවශ්ය වන අතර, අපි ටිකක් වෙනස් ලෙස ගොස් බොත්තම ක්ලික් කරන්නෙමු. නිවැරදි කිරීම (සංස්කරණය කරන්න). ඊට පසු, බල විමසුම් විමසුම් සංස්කාරකය පොතෙන් අපගේ දත්ත සමඟ වෙනම කවුළුවක පෙන්විය යුතුය:

මෙය අපට අවශ්ය දර්ශනය වෙත මේසය "අවසන්" කිරීමට ඉඩ සලසන ඉතා බලවත් මෙවලමකි. එහි සියලුම කාර්යයන් පිළිබඳ මතුපිට විස්තරයක් සඳහා පවා පිටු සියයක් පමණ ගත වනු ඇත, නමුත්, ඉතා කෙටියෙන් නම්, මෙම කවුළුව භාවිතා කිරීමෙන් ඔබට:

• අනවශ්‍ය දත්ත, හිස් රේඛා, දෝෂ සහිත රේඛා පෙරහන් කරන්න
• තීරු එකකින් හෝ වැඩි ගණනකින් දත්ත වර්ග කරන්න
• පුනරාවර්තනයෙන් මිදෙන්න
• ඇලෙන සුළු පෙළ තීරු වලින් බෙදන්න (සීමා, අක්ෂර ගණන, ආදිය)
• පෙළ පිළිවෙලට තබන්න (අමතර ඉඩ ඉවත් කරන්න, නිවැරදි නඩුව, ආදිය)
• හැකි සෑම ආකාරයකින්ම දත්ත වර්ග පරිවර්තනය කරන්න (පෙළ වැනි අංක සාමාන්‍ය සංඛ්‍යා බවට හරවන්න සහ අනෙක් අතට)
• වගු මාරු කරන්න (භ්‍රමණය කරන්න) සහ ද්විමාන හරස් වගු පැතලි ඒවා බවට පුළුල් කරන්න
• වගුවට අමතර තීරු එකතු කර බල විමසුමට ගොඩනගා ඇති M භාෂාව භාවිතයෙන් ඒවායේ සූත්‍ර සහ ශ්‍රිත භාවිතා කරන්න.
• ...

උදාහරණයක් ලෙස, අපි අපගේ වගුවට මාසයේ පෙළ නම සහිත තීරුවක් එකතු කරමු, එවිට විවර්තන වගු වාර්තා තැනීම පහසු වනු ඇත. මෙය සිදු කිරීම සඳහා, තීරු ශීර්ෂය මත දකුණු-ක්ලික් කරන්න දිනයසහ විධානය තෝරන්න අනුපිටපත් තීරුව (අනුපිටපත් තීරුව), ඉන්පසු දිස්වන අනුපිටපත් තීරුවේ ශීර්ෂය මත දකුණු-ක්ලික් කර විධාන තෝරන්න පරිවර්තනය - මාසය - මාසය නම:

එක් එක් පේළිය සඳහා මාසයේ පෙළ නම් සමඟ නව තීරුවක් සෑදිය යුතුය. තීරු ශීර්ෂයක් මත දෙවරක් ක්ලික් කිරීමෙන්, ඔබට එය නැවත නම් කළ හැක පිටපත් කරන දිනය වඩාත් සුවපහසු වෙත මාසික, උදා.

සමහර තීරු වල වැඩසටහන දත්ත වර්ගය නිවැරදිව හඳුනාගෙන නොමැති නම්, එක් එක් තීරුවේ වම් පැත්තේ ඇති ආකෘති නිරූපකය ක්ලික් කිරීමෙන් ඔබට එයට උදව් කළ හැකිය:

ඔබට සරල පෙරහනක් භාවිතයෙන් දෝෂ සහිත රේඛා හෝ හිස් රේඛා මෙන්ම අනවශ්‍ය කළමනාකරුවන් හෝ පාරිභෝගිකයින් බැහැර කළ හැකිය:

එපමණක් නොව, සිදු කරන ලද සියලුම පරිවර්තනයන් දකුණු පුවරුවේ සවි කර ඇති අතර, ඒවා සෑම විටම ආපසු පෙරළීමට (හරස්) හෝ ඒවායේ පරාමිතීන් (ගියර්) වෙනස් කළ හැකිය:

සැහැල්ලු හා අලංකාරයි නේද?

පියවර 2. අපගේ ඉල්ලීම කාර්යයක් බවට පරිවර්තනය කරමු

ආනයනය කරන ලද එක් එක් පොත සඳහා සිදු කරන ලද සියලුම දත්ත පරිවර්තනයන් පසුව නැවත නැවත සිදු කිරීම සඳහා, අප විසින් නිර්මාණය කරන ලද ඉල්ලීම ශ්‍රිතයක් බවට පරිවර්තනය කළ යුතු අතර, එය අපගේ සියලුම ලිපිගොනු වෙත අදාළ වේ. මෙය සිදු කිරීම ඇත්තෙන්ම ඉතා සරල ය.

විමසුම් සංස්කාරකයේ, බලන්න ටැබ් එකට ගොස් බොත්තම ක්ලික් කරන්න උසස් සංස්කාරක (බලන්න - උසස් සංස්කාරක). අපගේ පෙර ක්‍රියාවන් සියල්ලම M භාෂාවෙන් කේත ආකාරයෙන් ලියා ඇති කවුළුවක් විවෘත විය යුතුය. උදාහරණය සඳහා අප ආයාත කළ ගොනුව වෙත යන මාර්ගය කේතයේ දෘඪ කේත කර ඇති බව කරුණාවෙන් සලකන්න:

දැන් අපි ගැලපීම් කිහිපයක් කරමු:

ඔවුන්ගේ තේරුම සරලයි: පළමු පේළිය (ගොනු මාර්ගය)=> අපගේ ක්රියා පටිපාටිය තර්කයක් සහිත ශ්රිතයක් බවට පත් කරයි ගොනු මාර්ගය, සහ පහතින් අපි ස්ථාවර මාර්ගය මෙම විචල්‍යයේ අගයට වෙනස් කරමු. 

සියලුම. මත ක්ලික් කරන්න අවසානයි සහ මෙය දැකිය යුතුය:

දත්ත අතුරුදහන් වී ඇති බවට බිය නොවන්න - ඇත්ත වශයෙන්ම, සියල්ල හරි, සෑම දෙයක්ම මේ ආකාරයෙන් දිස්විය යුතුය 🙂 අපි අපගේ අභිරුචි ශ්‍රිතය සාර්ථකව නිර්මාණය කර ඇත, එහිදී දත්ත ආනයනය සහ සැකසීම සඳහා සම්පූර්ණ ඇල්ගොරිතම නිශ්චිත ගොනුවකට සම්බන්ධ නොවී මතක තබා ගනී. . එය වඩාත් තේරුම්ගත හැකි නමක් ලබා දීමට ඉතිරිව ඇත (උදාහරණයක් ලෙස දත්ත ලබා ගන්න) ක්ෂේත්රයේ දකුණු පස ඇති පුවරුවේ පළමු නම ඔබට නෙළාගත හැක මුල් පිටුව - වසන්න සහ බාගන්න (නිවස - වසන්න සහ පැටවීම). අපි උදාහරණය සඳහා ආයාත කළ ගොනුව වෙත යන මාර්ගය කේතයේ දෘඪ කේත කර ඇති බව කරුණාවෙන් සලකන්න. ඔබ ප්‍රධාන මයික්‍රොසොෆ්ට් එක්සෙල් කවුළුව වෙත ආපසු යනු ඇත, නමුත් අපගේ කාර්යයට සාදන ලද සම්බන්ධතාවය සහිත පැනලයක් දකුණු පසින් දිස්විය යුතුය:

පියවර 3. සියලුම ගොනු එකතු කිරීම

සියලුම දුෂ්කරම කොටස පිටුපස ඇත, ප්රසන්න හා පහසු කොටස ඉතිරි වේ. ටැබ් එකට යන්න දත්ත - විමසුම සාදන්න - ගොනුවෙන් - ෆෝල්ඩරයෙන් (දත්ත — නව විමසුම — ගොනුවෙන් — ෆෝල්ඩරයෙන්) හෝ, ඔබ සතුව Excel 2010-2013 තිබේ නම්, ටැබ් එකට සමානව බල විමසුම. දිස්වන කවුළුවෙහි, අපගේ සියලු මූලාශ්ර නගර ගොනු පිහිටා ඇති ෆෝල්ඩරය සඳහන් කර ක්ලික් කරන්න OK. මීළඟ පියවරේදී මෙම ෆෝල්ඩරයේ ඇති සියලුම Excel ගොනු (සහ එහි උප ෆෝල්ඩර) සහ ඒවායින් එක් එක් විස්තර ලැයිස්තුගත කෙරෙන කවුළුවක් විවෘත කළ යුතුය:

ක්ලික් කරන්න වෙනස් (සංස්කරණය කරන්න) නැවතත් අපි හුරුපුරුදු විමසුම් සංස්කාරක කවුළුව වෙත පිවිසෙමු.

දැන් අපි අපගේ නිර්මාණය කරන ලද කාර්යය සමඟ අපගේ වගුවට තවත් තීරුවක් එකතු කළ යුතුය, එය එක් එක් ගොනුවෙන් දත්ත "අදින්න" ඇත. මෙය සිදු කිරීම සඳහා, පටිත්ත වෙත යන්න තීරු එකතු කරන්න - අභිරුචි තීරුව (තීරුව එක් කරන්න - අභිරුචි තීරුව එක් කරන්න) සහ දිස්වන කවුළුවෙහි, අපගේ කාර්යය ඇතුල් කරන්න දත්ත ලබා ගන්න, ඒ සඳහා එක් එක් ගොනුව සඳහා සම්පූර්ණ මාර්ගය තර්කයක් ලෙස සඳහන් කිරීම:

ක්ලික් කිරීමෙන් පසු OK සාදන ලද තීරුව දකුණු පස ඇති අපගේ වගුවට එක් කළ යුතුය.

දැන් අපි සියලුම අනවශ්‍ය තීරු මකා දමමු (එක්සෙල් හි මෙන්, දකුණු මූසික බොත්තම භාවිතයෙන් - ඉවත් කරන්න), එකතු කරන ලද තීරුව සහ ගොනු නාමය සහිත තීරුව පමණක් ඉතිරි කරයි, මන්ද මෙම නම (වඩාත් නිවැරදිව, නගරය) එක් එක් පේළිය සඳහා මුළු දත්තවල තිබීම ප්‍රයෝජනවත් වනු ඇත.

දැන් “wow moment” - අපගේ කාර්යය සමඟ එකතු කරන ලද තීරුවේ ඉහළ දකුණු කෙළවරේ ඇති ඊතල සහිත අයිකනය මත ක්ලික් කරන්න:

… පරීක්ෂා නොකරන්න මුල් තීරු නම උපසර්ගය ලෙස භාවිතා කරන්න (මුල් තීරු නම උපසර්ගය ලෙස භාවිතා කරන්න)ක්ලික් කරන්න OK. අපගේ ශ්‍රිතය විසින් එක් එක් ගොනුවකින් දත්ත පූරණය කර සකසනු ඇත, පටිගත කරන ලද ඇල්ගොරිතම අනුගමනය කර පොදු වගුවක සියල්ල එකතු කරයි:

සම්පූර්ණ අලංකාරය සඳහා, ඔබට .xlsx දිගුව ගොනු නාම සහිත පළමු තීරුවෙන් ඉවත් කළ හැකිය - සම්මත ප්‍රතිස්ථාපනයෙන් "කිසිවක් නැත" (තීරු ශීර්ෂය මත දකුණු-ක්ලික් කරන්න - ආදේශ කරන්න) සහ මෙම තීරුව ලෙස නැවත නම් කරන්න හැඟීම්. තවද තීරුවේ ඇති දත්ත ආකෘතිය දිනය සමඟ නිවැරදි කරන්න.

සියලුම! මත ක්ලික් කරන්න නිවස - වසන්න සහ පැටවීම (නිවස - වසන්න සහ පැටවීම). සියලුම නගර සඳහා විමසුම මගින් රැස් කරන ලද සියලුම දත්ත "ස්මාර්ට් වගුව" ආකෘතියෙන් වත්මන් Excel පත්‍රයට උඩුගත කරනු ලැබේ:

සාදන ලද සම්බන්ධතාවය සහ අපගේ එකලස් කිරීමේ කාර්යය කිසිදු ආකාරයකින් වෙන වෙනම සුරැකීමට අවශ්ය නොවේ - ඒවා සුපුරුදු ආකාරයෙන් වත්මන් ගොනුව සමඟ එකට සුරකිනු ලැබේ.

අනාගතයේදී, ෆෝල්ඩරයේ (නගර එකතු කිරීම හෝ ඉවත් කිරීම) හෝ ගොනු වල (රේඛා ගණන වෙනස් කිරීම) යම් වෙනස්කම් සමඟ, මේසය මත හෝ දකුණු පුවරුවේ ඇති විමසුම මත සෘජුවම දකුණු-ක්ලික් කර තෝරා ගැනීමට ප්රමාණවත් වනු ඇත. විධානය යාවත්කාලීන කර සුරකින්න (නැවුම් කරන්න) - බල විමසුම තත්පර කිහිපයකින් සියලු දත්ත නැවත "නැවත ගොඩනඟයි".

PS

සංශෝධනය. 2017 ජනවාරි යාවත්කාලීන කිරීම් වලින් පසුව, Power Query විසින් Excel වැඩපොත් එකතු කරන ආකාරය ඉගෙන ගත්තේය, එනම් තවදුරටත් වෙනම කාර්යයක් කිරීමට අවශ්‍ය නැත - එය ස්වයංක්‍රීයව සිදුවේ. මේ අනුව, මෙම ලිපියේ දෙවන පියවර තවදුරටත් අවශ්ය නොවන අතර සමස්ත ක්රියාවලිය සැලකිය යුතු ලෙස සරල වේ:

1. තෝරා ඉල්ලීම සාදන්න - ගොනුවෙන් - ෆෝල්ඩරයෙන් - ෆෝල්ඩරය තෝරන්න - හරි
2. ගොනු ලැයිස්තුව දිස් වූ පසු, ඔබන්න වෙනස්
3. විමසුම් සංස්කාරක කවුළුවෙහි, ද්විත්ව ඊතලයකින් ද්විමය තීරුව පුළුල් කර එක් එක් ගොනුවෙන් ගත යුතු පත්‍ර නම තෝරන්න.

සහ එපමණයි! සිංදුව!

• හරස් ටැබ් එක පිවට් මේස තැනීමට සුදුසු පැතලි එකක් බවට ප්‍රතිනිර්මාණය කිරීම
• Power View හි සජීවිකරණ බුබුලු සටහනක් ගොඩනැගීම
• විවිධ එක්සෙල් ගොනු වලින් පත්‍ර එකකට එකලස් කිරීමට මැක්‍රෝ