අන්තර්ගතය
Microsoft Excel විසින් වගු කිහිපයකින් (විවිධ පත්ර වලින් හෝ විවිධ ගොනු වලින්) දත්ත රැස් කිරීම සඳහා බොහෝ මෙවලම් ඇත: සෘජු සබැඳි, ක්‍රියාකාරිත්වය වක්ර (වක්ර), Power Query සහ Power Pivot add-ons, etc. බැරිකේඩ් එකේ මේ පැත්තෙන් බැලුවම හැම දෙයක්ම හොඳට පේනවා.
නමුත් ඔබ ප්‍රතිලෝම ගැටලුවකට මුහුණ දෙන්නේ නම් - එක් වගුවක සිට විවිධ පත්ර වෙත දත්ත පැතිරීම - එවිට සෑම දෙයක්ම වඩාත් කණගාටුදායක වනු ඇත. මේ මොහොතේ, අවාසනාවකට මෙන්, එක්සෙල් හි අවි ගබඩාවේ එවැනි දත්ත වෙන් කිරීම සඳහා ශිෂ්ට සම්පන්න ගොඩනඟන ලද මෙවලම් නොමැත. එබැවින් ඔබට විෂුවල් බේසික් හි මැක්රෝ එකක් භාවිතා කිරීමට සිදුවනු ඇත, නැතහොත් මැක්රෝ රෙකෝඩරය + බල විමසුම් සංයෝජනයෙන් පසුව කුඩා “ගොනු පිරිපහදු කිරීමක්” භාවිතා කරන්න.
මෙය ක්රියාත්මක කළ හැකි ආකාරය දෙස සමීපව බලමු.
ගැටලුව සකස් කිරීම
විකුණුම් සඳහා පේළි 5000 කට වඩා වැඩි ප්‍රමාණයකින් යුත් එවැනි වගුවක් මූලික දත්ත ලෙස අප සතුව ඇත:
කාර්යය: මෙම වගුවේ දත්ත මෙම පොතේ වෙනම පත්‍රවල නගරය අනුව බෙදා හැරීමට. එම. ප්‍රතිදානයේදී, ඔබ එක් එක් පත්රයට ලබා ගත යුත්තේ අදාළ නගරයේ විකිණීම සිදු වූ මේසයෙන් පේළි පමණි:
සූදානම් වෙන්න
සාර්ව කේතය සංකීර්ණ නොකිරීමට සහ හැකි තරම් තේරුම් ගැනීමට පහසු කිරීම සඳහා, අපි සූදානම් වීමේ පියවර කිහිපයක් සිදු කරමු.
පළමුව, වෙනම බැලීමේ වගුවක් සාදන්න, තනි තීරුවකින් ඔබට වෙනම පත්ර සෑදීමට අවශ්‍ය සියලුම නගර ලැයිස්තුගත කරනු ඇත. ඇත්ත වශයෙන්ම, මෙම නාමාවලියෙහි මූලාශ්ර දත්තවල පවතින සියලුම නගර අඩංගු නොවිය හැක, නමුත් අපට වාර්තා අවශ්‍ය ඒවා පමණි. එවැනි වගුවක් සෑදීමට පහසුම ක්රමය වන්නේ විධානය භාවිතා කිරීමයි දත්ත - අනුපිටපත් ඉවත් කරන්න (දත්ත - අනුපිටපත් ඉවත් කරන්න) තීරු පිටපත සඳහා හැඟීම් හෝ කාර්යය UNIK (අද්විතීය) - ඔබට Excel 365 හි නවතම අනුවාදය තිබේ නම්.
Excel හි නව පත්ර වත්මන් (පෙර) එකට පෙර (වමේ) පෙරනිමියෙන් නිර්මාණය කර ඇති බැවින්, මෙම නාමාවලියෙහි නගර අවරෝහණ අනුපිළිවෙලින් (Z සිට A දක්වා) වර්ග කිරීම අර්ථවත් කරයි - ඉන්පසු නිර්මාණය කිරීමෙන් පසු නගරය පත්රිකා අකාරාදී පිළිවෙලට සකස් කරනු ලැබේ.
දෙවනුව, пවගු දෙකම ගතික බවට පරිවර්තනය කරන්න ("ස්මාර්ට්") ඔවුන් සමඟ වැඩ කිරීම පහසු කිරීම සඳහා. අපි විධානය භාවිතා කරමු නිවස - මේසයක් ලෙස හැඩතල ගැන්වීම (මුල් පිටුව - වගුවක් ලෙස ආකෘතිය) හෝ යතුරුපුවරු කෙටිමං Ctrl+T. දිස්වන ටැබය මත ඉදිකිරීම්කරු (නිර්මාණ) අපි ඔවුන්ට කතා කරමු tablProdaji и TableCity, පිළිවෙලින්:
ක්රමය 1. පත්ර මගින් බෙදීම සඳහා මැක්රෝ
උසස් පටිත්තෙහි සංවර්ධක (සංවර්ධක) බොත්තම ක්ලික් කරන්න දෘශ්ය මූලික හෝ යතුරුපුවරු කෙටිමං භාවිතා කරන්න alt+F11. විවෘත වන මැක්රෝ සංස්කාරක කවුළුව තුළ, මෙනුව හරහා නව හිස් මොඩියුලයක් ඇතුල් කරන්න ඇතුල් කරන්න - මොඩියුලය සහ පහත කේතය එහි පිටපත් කරන්න:
Sub Splitter() පරාසයේ සෑම කොටුවක් සඳහාම("таблГорода") පරාසය("таблПродажи").AutoFilter Field:=3, Criteria1:=cell.Value Range("таблПродажи[#AllCelly"). Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit ඊළඟ කොටුවේ වැඩ පත්රිකා("Данные").ShowAllData End Sub මෙන්න ලූපයක් සමඟ එක් එක් ... ඊළඟට ඩිරෙක්ටරියේ සෛල හරහා ගමන් කිරීම ක්රියාත්මක කරන ලදී TableCity, එක් එක් නගරය සඳහා එය පෙරීම (ක්රමය ස්වයං පෙරහන) මුල් විකුණුම් වගුවේ සහ පසුව ප්රතිඵල අලුතින් සාදන ලද පත්රයට පිටපත් කිරීම. මාර්ගය ඔස්සේ, නිර්මාණය කරන ලද පත්රය නගරයේ එකම නමට නැවත නම් කර ඇති අතර අලංකාරය සඳහා තීරු වල පළල ස්වයංක්‍රීයව සවි කිරීම එය මත සක්‍රිය කර ඇත.
ඔබට ටැබ් එක මත එක්සෙල් හි සාදන ලද මැක්රෝ ධාවනය කළ හැකිය සංවර්ධක බොත්තම මැක්රෝස් (සංවර්ධක - මැක්රෝස්) හෝ යතුරුපුවරු කෙටිමං alt+F8.
ක්රමය 2. බල විමසුමේ බහුවිධ විමසුම් සාදන්න
පෙර ක්රමය, එහි සියලුම සංයුක්තතාවය සහ සරල බව සඳහා සැලකිය යුතු පසුබෑමක් ඇත - මුල් විකුණුම් වගුවේ වෙනස්කම් සිදු කරන විට මැක්රෝ විසින් නිර්මාණය කරන ලද පත්ර යාවත්කාලීන නොවේ. පියාසර කිරීමේදී යාවත්කාලීන කිරීම අවශ්‍ය නම්, ඔබට VBA + බල විමසුම් මිටියක් භාවිතා කිරීමට සිදුවනු ඇත, නැතහොත්, ස්ථිතික දත්ත සහිත පත්ර පමණක් නොව යාවත්කාලීන කළ බල විමසුම් විමසුම් භාවිතා කරමින් මැක්රෝ එකක් නිර්මාණය කරන්න.
මෙම නඩුවේ මැක්රෝ පෙර එකට අර්ධ වශයෙන් සමාන වේ (එය චක්රයක් ද ඇත එක් එක් ... ඊළඟට නාමාවලියෙහි නගර හරහා පුනරාවර්තනය කිරීමට), නමුත් ලූපය තුළ තවදුරටත් පෙරීම සහ පිටපත් කිරීම සිදු නොවනු ඇත, නමුත් බල විමසුම් විමසුමක් නිර්මාණය කර එහි ප්රතිඵල නව පත්‍රයකට උඩුගත කිරීම:
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(මූලාශ්‍රය , {{""ප්‍රවර්ගය"", පෙළ ටයිප් කරන්න}, {""නම"", පෙළ ටයිප් කරන්න}, {""නගරය"", පෙළ වර්ගය}, {""කළමනාකරු"", පෙළ වර්ගය}, {""ගනුදෙනු දිනය "", දින වේලාව ටයිප් කරන්න}, {""පිරිවැය"", අංකය ටයිප් කරන්න}})," & Chr(13) & "" & Chr(10) & " #""පෙරහන යොදන ලද පේළි"" = Table.Se " & _ "lectRows(#""වෙනස් කළ වර්ගය"", එක් එක් ([නගරය] = """ & cell.Value & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""පෙරහන යොදන ලද පේළි""" ActiveWorkbook.Worksheets.Add with ActiveSheet.ListObjects.Add(sourceType:=0, Source:= _ "OLEDB; සපයන්නා =Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & cell.Value & ";Extended Properties=""""" _ , ගමනාන්තය:=පරාසය("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = Array("SELECT *[" & cell.Value & "]") වෙතින් .RowNumbers = වැරදි .FillAdjacentFormulas = අසත්‍ය .PreserveFormatting = සත්‍ය .RefreshOnFileOpen = වැරදි .BackgroundQuery = සත්‍ය .RefreshStyle = xellsInsertSDelete SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value එය දියත් කිරීමෙන් පසු, අපි නගරය අනුව එකම පත්ර දකිනු ඇත, නමුත් දැනටමත් නිර්මාණය කර ඇති බල විමසුම් විමසුම් ඒවා සාදනු ඇත:
මූලාශ්ර දත්තවල යම් වෙනස්කම් සහිතව, දකුණු මූසික බොත්තම සමඟ අනුරූප වගුව යාවත්කාලීන කිරීමට ප්රමාණවත් වනු ඇත - විධානය යාවත්කාලීන කර සුරකින්න (නැවුම් කරන්න) හෝ බොත්තම භාවිතයෙන් තොග වශයෙන් සියලුම නගර එකවර යාවත්කාලීන කරන්න සියල්ල යාවත්කාලීන කරන්න ටැබ් දත්ත (දත්ත - සියල්ල නැවුම් කරන්න).
- මැක්රෝස් යනු මොනවාද, ඒවා නිර්මාණය කර භාවිතා කරන්නේ කෙසේද
- වැඩපොත් පත්ර වෙනම ගොනු ලෙස සුරැකීම
- පොතේ සියලුම පත්ර වලින් දත්ත එක් වගුවකට එකතු කිරීම