බහු දත්ත පරාසයන් හරහා විවර්තන වගුව

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

Pivot වගු යනු Excel හි ඇති පුදුමාකාර මෙවලම් වලින් එකකි. නමුත් මේ වන විට, අවාසනාවකට මෙන්, එක්සෙල් හි කිසිදු අනුවාදයකට පියාසර කිරීමේදී එතරම් සරල හා අවශ්‍ය දෙයක් කළ නොහැක, උදාහරණයක් ලෙස, විවිධ පත්‍රවල හෝ විවිධ වගු වල පිහිටා ඇති මූලික දත්ත පරාසයන් කිහිපයක් සඳහා සාරාංශයක් තැනීම:

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

• වගු වලට ඕනෑම දත්තයක් සහිත ඕනෑම පේළි ගණනක් තිබිය හැක, නමුත් ඒවාට එකම ශීර්ෂය තිබිය යුතුය.
• මූලාශ්‍ර වගු සහිත පත්‍රවල අමතර දත්ත නොතිබිය යුතුය. එක් පත්රයක් - එක් මේසයක්. පාලනය කිරීමට, යතුරුපුවරු කෙටිමඟක් භාවිතා කිරීමට මම ඔබට උපදෙස් දෙමි Ctrl+අවසානය, එය ඔබව වැඩ පත්‍රිකාවේ අවසන් වරට භාවිතා කළ කොටුව වෙත ගෙන යයි. ඉතා මැනවින්, මෙය දත්ත වගුවේ අවසාන කොටුව විය යුතුය. ඔබ ක්ලික් කළ විට නම් Ctrl+අවසානය මේසයට දකුණට හෝ පහළින් ඇති ඕනෑම හිස් කොටුවක් උද්දීපනය කෙරේ - මෙම හිස් තීරු දකුණට හෝ මේසයට පහළින් ඇති පේළි මකා දමා ගොනුව සුරකින්න.

ක්රමය 1: Power Query භාවිතයෙන් විවර්තනයක් සඳහා වගු සාදන්න

Excel සඳහා 2010 අනුවාදයේ සිට, ඕනෑම දත්තයක් එකතු කර පරිවර්තනය කළ හැකි නොමිලේ Power Query ඇඩෝනයක් ඇත, පසුව එය විවර්තන වගුවක් තැනීම සඳහා මූලාශ්‍රයක් ලෙස ලබා දේ. මෙම ඇඩෝනය ආධාරයෙන් අපගේ ගැටලුව විසඳීම කිසිසේත් අපහසු නැත.

පළමුව, අපි එක්සෙල් හි නව හිස් ගොනුවක් සාදමු - එකලස් කිරීම එහි සිදු වන අතර පසුව එහි විවර්තන වගුවක් නිර්මාණය වේ.

ඉන්පසු ටැබ් එක මත දත්ත (ඔබට Excel 2016 හෝ ඊට පසු තිබේ නම්) හෝ ටැබය මත බල විමසුම (ඔබට Excel 2010-2013 තිබේ නම්) විධානය තෝරන්න විමසුම සාදන්න - ගොනුවෙන් - එක්සෙල් (දත්ත ලබා ගන්න - ගොනුවෙන් - එක්සෙල්) සහ එකතු කළ යුතු වගු සහිත මූලාශ්‍ර ගොනුව සඳහන් කරන්න:

දිස්වන කවුළුවෙහි, ඕනෑම පත්රයක් තෝරන්න (එය කුමන එකක්ද යන්න ප්රශ්නයක් නොවේ) සහ පහත බොත්තම ඔබන්න වෙනස් (සංස්කරණය කරන්න):

බල විමසුම් සංස්කාරක කවුළුව Excel මත විවෘත විය යුතුය. පුවරුවේ කවුළුවේ දකුණු පැත්තේ ඉල්ලීම් පරාමිති පළමු පියවර හැර ස්වයංක්‍රීයව සාදන ලද සියලුම පියවර මකන්න - මූලාශ්රය (මූලාශ්රය):

දැන් අපි සියලු පත්රවල පොදු ලැයිස්තුවක් දකිමු. දත්ත පත්‍රිකා වලට අමතරව ගොනුවේ තවත් පැති පත්‍ර කිහිපයක් තිබේ නම්, මෙම පියවරේදී අපගේ කාර්යය වන්නේ වගු ශීර්ෂයේ ඇති පෙරහන භාවිතා කරන අනෙක් සියල්ල හැර, තොරතුරු පූරණය කළ යුතු පත්‍ර පමණක් තෝරා ගැනීමයි:

තීරුව හැර අනෙකුත් සියලුම තීරු මකන්න දත්තතීරු ශීර්ෂයක් දකුණු-ක්ලික් කර තේරීමෙන් අනෙකුත් තීරු මකන්න (ඉවත් කරන්න වෙනත් තීරු):

එවිට ඔබට තීරුවේ ඉහළින් ඇති ද්විත්ව ඊතලය මත ක්ලික් කිරීමෙන් එකතු කරන ලද වගු වල අන්තර්ගතය පුළුල් කළ හැකිය (චෙක්බොක්ස් මුල් තීරු නම උපසර්ගය ලෙස භාවිතා කරන්න ඔබට එය නිවා දැමිය හැකිය):

ඔබ සෑම දෙයක්ම නිවැරදිව කළේ නම්, මෙම අවස්ථාවේදී ඔබ එකතු කරන ලද සියලුම වගු වල අන්තර්ගතය එකකට පහළින් දැකිය යුතුය:

බොත්තම සමඟ පළමු පේළිය වගු ශීර්ෂයට නැංවීමට එය ඉතිරිව ඇත පළමු පේළිය ශීර්ෂයන් ලෙස භාවිතා කරන්න (පළමු පේළිය ශීර්ෂක ලෙස භාවිතා කරන්න) ටැබ් මුල් පිටුව (නිවස) පෙරහන භාවිතයෙන් දත්ත වලින් අනුපිටපත් වගු ශීර්ෂ ඉවත් කරන්න:

විධානය සමඟ කළ සියල්ල සුරකින්න වසන්න සහ පූරණය කරන්න - වසා දමා පූරණය කරන්න... (වසන්න සහ පූරණය කරන්න - වසන්න සහ පූරණය කරන්න...) ටැබ් මුල් පිටුව (නිවස), සහ විවෘත වන කවුළුව තුළ, විකල්පය තෝරන්න සම්බන්ධතාවය පමණි (සම්බන්ධතාවය පමණි):

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

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

ක්රමය 2. අපි මැක්රෝ එකක UNION SQL විධානය සමඟ වගු ඒකාබද්ධ කරමු

අපගේ ගැටලුවට තවත් විසඳුමක් මෙම මැක්‍රෝව මගින් නිරූපණය කෙරේ, එය විධානය භාවිතා කරමින් විවර්තන වගුව සඳහා දත්ත කට්ටලයක් (හැඹිලි) නිර්මාණය කරයි. සමගිය SQL විමසුම් භාෂාව. මෙම විධානය අරාවේ දක්වා ඇති සියලුම වගු ඒකාබද්ධ කරයි පත්‍ර නම් පොතේ පත්‍රිකා තනි දත්ත වගුවකට. එනම්, විවිධ පත්‍රවල සිට එක දක්වා පරාසයන් භෞතිකව පිටපත් කර ඇලවීම වෙනුවට, අපි පරිගණකයේ RAM එකෙහි එයම කරමු. එවිට මැක්‍රෝ විසින් ලබා දී ඇති නම සහිත නව පත්‍රයක් එක් කරයි (විචල්‍යය ප්‍රතිඵල පත්‍රිකාවේ නම) සහ එකතු කරන ලද හැඹිලිය මත පදනම්ව එය මත සම්පූර්ණ (!) සාරාංශයක් නිර්මාණය කරයි.

මැක්‍රෝ එකක් භාවිතා කිරීමට, ටැබ් එකේ ඇති විෂුවල් බේසික් බොත්තම භාවිතා කරන්න සංවර්ධක (සංවර්ධක) හෝ යතුරුපුවරු කෙටිමං alt+F11. ඉන්පසුව අපි මෙනුව හරහා නව හිස් මොඩියුලයක් ඇතුල් කරන්නෙමු ඇතුල් කරන්න - මොඩියුලය සහ පහත කේතය එහි පිටපත් කරන්න:

Sub New_Multi_Table_Pivot() Dim i Long Dim arSQL() String Dim objPivotCache ලෙස PivotCache Dim objRS ලෙස Object Dim ResultSheetName String Dim SheetsNames ලෙස විචල්‍ය 'පත්‍ර නාමයක් ලෙස එහි ප්‍රතිඵලය වන pivot' හි ප්‍රතිඵලයක් ලෙස "පරිවර්තන" පත්‍රය ප්‍රදර්ශනය වනු ඇත. මූලාශ්‍ර වගු සහිත නම් SheetsNames = Array("Alpha", "Beta", "Gamma", "Delta") 'ActiveWorkbook ReDim arSQL(1 සිට (UBound(SheetsNames) + 1) සමඟ SheetsNames වෙතින් පත්‍ර වලින් අපි වගු සඳහා හැඹිලියක් සාදන්නෙමු. ) i = LBound (SheetsNames) සඳහා UBound(SheetsNames) වෙත arSQL(i + 1) = "තෝරන්න * [" & SheetsNames(i) සහ "$]" ඊළඟට 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 ) සමඟ අවසන් කරන්න 'ප්‍රතිඵලයක් ලෙස ලැබෙන විවර්තන වගුව ප්‍රදර්ශනය කිරීමට පත්‍රය නැවත සාදන්න දෝෂයක් මත ඊළඟ යෙදුම නැවත ආරම්භ කරන්න.DisplayAlerts = වැරදි වැඩ පත්‍රිකා(ප්‍රතිඵලපත්‍ර නාමය).WsPivot = වැඩපත්‍ර මකන්න. wsPivo එකතු කරන්න. ටී. Name = ResultSheetName 'මෙම පත්‍රයේ ජනනය කරන ලද හැඹිලි සාරාංශය පෙන්වන්න objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) objPivotCache.Recordset = objRS සකසන්න objRS = කිසිවක් සමඟ wsPivotCaches. objPivotCache = කිසිවක් නැත පරාසය("A3").End With End Sub තෝරන්න    

නිමි මැක්රෝ පසුව යතුරුපුවරු කෙටිමඟක් සමඟ ධාවනය කළ හැක alt+F8 හෝ ටැබ් එකේ ඇති මැක්‍රෝස් බොත්තම සංවර්ධක (සංවර්ධක - මැක්‍රෝස්).

මෙම ප්රවේශයේ අවාසි:

• හැඹිලියට මූලාශ්‍ර වගුවලට සම්බන්ධයක් නොමැති නිසා දත්ත යාවත්කාලීන නොවේ. ඔබ මූලාශ්‍ර දත්ත වෙනස් කරන්නේ නම්, ඔබ මැක්‍රෝව නැවත ධාවනය කර නැවත සාරාංශය ගොඩනගා ගත යුතුය.
• පත්‍ර ගණන වෙනස් කිරීමේදී, සාර්ව කේතය සංස්කරණය කිරීම අවශ්‍ය වේ (අරාව පත්‍ර නම්).
  

නමුත් අවසානයේදී අපට විවිධ පත්‍ර වලින් පරාස කිහිපයක් මත ගොඩනගා ඇති සැබෑ සම්පූර්ණ විවර්තන වගුවක් ලැබේ:

Voilà!

තාක්ෂණික සටහන: මැක්‍රෝ ධාවනය කිරීමේදී "සැපයුම්කරු ලියාපදිංචි වී නැත" වැනි දෝෂයක් ඔබට ලැබුනේ නම්, බොහෝ විට ඔබට Excel හි 64-bit අනුවාදයක් හෝ Office හි අසම්පූර්ණ අනුවාදයක් ස්ථාපනය කර ඇත (ප්‍රවේශය නොමැත). තත්වය නිවැරදි කිරීම සඳහා, සාර්ව කේතයේ කොටස ප්‍රතිස්ථාපනය කරන්න:

	 Provider=Microsoft.Jet.OLEDB.4.0;  

දක්වා:

	Provider=Microsoft.ACE.OLEDB.12.0;  

Microsoft වෙබ් අඩවියෙන් Access වෙතින් නොමිලේ දත්ත සැකසුම් එන්ජිම බාගත කර ස්ථාපනය කරන්න - Microsoft Access Database Engine 2010 Redistributable

ක්රමය 3: Excel හි පැරණි අනුවාද වලින් PivotTable Wizard ඒකාබද්ධ කරන්න

මෙම ක්රමය ටිකක් යල් පැන ගිය නමුත් තවමත් සඳහන් කිරීම වටී. විධිමත් ලෙස කථා කරන විට, 2003 දක්වා සහ ඇතුළුව සියලුම අනුවාද වල, "ඒකාබද්ධ කිරීමේ පරාස කිහිපයක් සඳහා හැරීමක් ගොඩනැගීම" සඳහා PivotTable Wizard හි විකල්පයක් තිබුණි. කෙසේ වෙතත්, මේ ආකාරයෙන් ගොඩනඟන ලද වාර්තාවක්, අවාසනාවකට මෙන්, සැබෑ සම්පූර්ණ සාරාංශයක අනුකම්පා සහගත සමානකමක් පමණක් වන අතර සාම්ප්‍රදායික විවර්තන වගු වල බොහෝ “චිප්ස්” සඳහා සහය නොදක්වයි:

එවැනි විවර්තනයකදී, ක්ෂේත්‍ර ලැයිස්තුවේ තීරු ශීර්ෂයන් නොමැත, නම්‍යශීලී ව්‍යුහ සැකසුම නොමැත, භාවිතා කරන ශ්‍රිත කට්ටලය සීමිතය, සහ සාමාන්‍යයෙන්, මේ සියල්ල විවර්තන වගුවකට බෙහෙවින් සමාන නොවේ. 2007 සිට මයික්‍රොසොෆ්ට් විවර්තන වගු වාර්තා සෑදීමේදී සම්මත සංවාදයෙන් මෙම කාර්යය ඉවත් කළේ ඒ නිසා විය හැකිය. දැන් මෙම විශේෂාංගය ලබා ගත හැක්කේ අභිරුචි බොත්තමක් හරහා පමණි PivotTable Wizard(Pivot Table Wizard), අවශ්‍ය නම්, හරහා ඉක්මන් ප්‍රවේශ මෙවලම් තීරුවට එක් කළ හැක ගොනුව - විකල්ප - ඉක්මන් ප්‍රවේශ මෙවලම් තීරුව අභිරුචිකරණය කරන්න - සියලුම විධාන (ගොනුව - විකල්ප - ඉක්මන් ප්‍රවේශ මෙවලම් තීරුව අභිරුචිකරණය කරන්න - සියලුම විධාන):

එකතු කරන ලද බොත්තම මත ක්ලික් කිරීමෙන් පසු, ඔබ විශාරදයාගේ පළමු පියවරේදී සුදුසු විකල්පය තෝරාගත යුතුය:

ඊළඟ කවුළුව තුළ, එක් එක් පරාසය පිළිවෙලින් තෝරා එය පොදු ලැයිස්තුවට එක් කරන්න:

නමුත්, නැවතත්, මෙය සම්පූර්ණ සාරාංශයක් නොවේ, එබැවින් එයින් වැඩි යමක් බලාපොරොත්තු නොවන්න. මට මෙම විකල්පය නිර්දේශ කළ හැක්කේ ඉතා සරල අවස්ථාවන්හිදී පමණි.

• PivotTables සමඟ වාර්තා නිර්මාණය කිරීම
• PivotTables හි ගණනය කිරීම් සකසන්න
• මැක්‍රෝස් යනු කුමක්ද, ඒවා භාවිතා කරන්නේ කෙසේද, VBA කේතය පිටපත් කරන්නේ කොතැනද යනාදිය.
• පත්‍ර කිහිපයක සිට එකකට දත්ත රැස් කිරීම (PLEX ඇඩෝනය)