ഒരു മേശയെ ഷീറ്റുകളായി വിഭജിക്കുന്നു

Microsoft Excel-ൽ നിരവധി ടേബിളുകളിൽ നിന്ന് (വ്യത്യസ്ത ഷീറ്റുകളിൽ നിന്നോ വ്യത്യസ്ത ഫയലുകളിൽ നിന്നോ) ഡാറ്റ ശേഖരിക്കുന്നതിന് നിരവധി ടൂളുകൾ ഉണ്ട്: നേരിട്ടുള്ള ലിങ്കുകൾ, പ്രവർത്തനം ഇൻഡിറക്റ്റ് (പരോക്ഷം), പവർ ക്വറി, പവർ പിവറ്റ് ആഡ്-ഓണുകൾ മുതലായവ. ബാരിക്കേഡിന്റെ ഇപ്പുറത്ത് നിന്ന്, എല്ലാം മികച്ചതായി തോന്നുന്നു.

എന്നാൽ നിങ്ങൾ ഒരു വിപരീത പ്രശ്‌നത്തിൽ അകപ്പെടുകയാണെങ്കിൽ - ഒരു ടേബിളിൽ നിന്ന് വ്യത്യസ്ത ഷീറ്റുകളിലേക്ക് ഡാറ്റ പ്രചരിപ്പിക്കുന്നത് - അപ്പോൾ എല്ലാം വളരെ സങ്കടകരമായിരിക്കും. ഇപ്പോൾ, നിർഭാഗ്യവശാൽ, Excel-ന്റെ ആയുധപ്പുരയിൽ അത്തരം ഡാറ്റ വേർതിരിക്കലിനായി പരിഷ്കൃത ബിൽറ്റ്-ഇൻ ടൂളുകളൊന്നുമില്ല. അതിനാൽ നിങ്ങൾ വിഷ്വൽ ബേസിക്കിൽ ഒരു മാക്രോ ഉപയോഗിക്കേണ്ടതുണ്ട്, അല്ലെങ്കിൽ മാക്രോ റെക്കോർഡർ + പവർ ക്വറി കോമ്പിനേഷൻ ഉപയോഗിച്ച് കുറച്ച് “ഫയൽ റിഫൈൻമെന്റ്” ഉപയോഗിക്കുക.

ഇത് എങ്ങനെ നടപ്പിലാക്കാം എന്ന് നമുക്ക് കൂടുതൽ വിശദമായി നോക്കാം.

പ്രശ്നത്തിന്റെ രൂപീകരണം

വിൽപ്പനയ്‌ക്കായി 5000-ലധികം വരികളുടെ വലുപ്പമുള്ള അത്തരമൊരു പട്ടിക ഞങ്ങളുടെ പക്കലുണ്ട്:

ടാസ്ക്: ഈ ടേബിളിൽ നിന്നുള്ള ഡാറ്റ ഈ പുസ്തകത്തിന്റെ പ്രത്യേക ഷീറ്റുകളിൽ നഗരം അനുസരിച്ച് വിതരണം ചെയ്യാൻ. ആ. ഔട്ട്‌പുട്ടിൽ, നിങ്ങൾ ഓരോ ഷീറ്റിലും ബന്ധപ്പെട്ട നഗരത്തിൽ വിൽപ്പന നടന്ന പട്ടികയിൽ നിന്നുള്ള വരികൾ മാത്രം നേടേണ്ടതുണ്ട്:

തയാറാക്കുക

മാക്രോ കോഡ് സങ്കീർണ്ണമാക്കാതിരിക്കാനും മനസ്സിലാക്കാൻ കഴിയുന്നത്ര എളുപ്പമാക്കാനും, നമുക്ക് രണ്ട് തയ്യാറെടുപ്പ് ഘട്ടങ്ങൾ നടത്താം.

ആദ്യം, ആ ഒരു പ്രത്യേക ലുക്ക്അപ്പ് ടേബിൾ ഉണ്ടാക്കുക, നിങ്ങൾ പ്രത്യേക ഷീറ്റുകൾ സൃഷ്ടിക്കാൻ ആഗ്രഹിക്കുന്ന എല്ലാ നഗരങ്ങളെയും ഒരൊറ്റ കോളം പട്ടികപ്പെടുത്തും. തീർച്ചയായും, ഈ ഡയറക്‌ടറിയിൽ ഉറവിട ഡാറ്റയിൽ ഉള്ള എല്ലാ നഗരങ്ങളും അടങ്ങിയിരിക്കണമെന്നില്ല, എന്നാൽ ഞങ്ങൾക്ക് റിപ്പോർട്ടുകൾ ആവശ്യമുള്ളവ മാത്രം. അത്തരമൊരു പട്ടിക സൃഷ്ടിക്കുന്നതിനുള്ള ഏറ്റവും എളുപ്പ മാർഗം കമാൻഡ് ഉപയോഗിക്കുക എന്നതാണ് ഡാറ്റ - ഡ്യൂപ്ലിക്കേറ്റുകൾ നീക്കം ചെയ്യുക (ഡാറ്റ - തനിപ്പകർപ്പുകൾ നീക്കം ചെയ്യുക) കോളം പകർപ്പിനായി വികാരങ്ങൾ അല്ലെങ്കിൽ പ്രവർത്തനം UNIK (യുനിക്) - നിങ്ങൾക്ക് Excel 365 ന്റെ ഏറ്റവും പുതിയ പതിപ്പ് ഉണ്ടെങ്കിൽ.

Excel-ൽ പുതിയ ഷീറ്റുകൾ നിലവിലുള്ള (മുമ്പത്തെ) ഒന്നിന് മുമ്പ് (ഇടത് വശത്ത്) ഡിഫോൾട്ടായി സൃഷ്‌ടിച്ചതിനാൽ, ഈ ഡയറക്‌ടറിയിലെ നഗരങ്ങളെ അവരോഹണ ക്രമത്തിൽ (Z മുതൽ A വരെ) അടുക്കുന്നതും അർത്ഥമാക്കുന്നു - തുടർന്ന് സൃഷ്‌ടിച്ചതിന് ശേഷം, നഗരം ഷീറ്റുകൾ അക്ഷരമാലാക്രമത്തിൽ ക്രമീകരിക്കും.

സെക്കന്റ്, ദി пരണ്ട് പട്ടികകളും ഡൈനാമിക് ആക്കി മാറ്റുക ("സ്മാർട്ട്") അവരുമായി പ്രവർത്തിക്കുന്നത് എളുപ്പമാക്കുന്നതിന്. ഞങ്ങൾ കമാൻഡ് ഉപയോഗിക്കുന്നു വീട് - ഒരു പട്ടികയായി ഫോർമാറ്റ് ചെയ്യുക (ഹോം - പട്ടികയായി ഫോർമാറ്റ് ചെയ്യുക) അല്ലെങ്കിൽ കീബോർഡ് കുറുക്കുവഴി Ctrl+T. ദൃശ്യമാകുന്ന ടാബിൽ കൺസ്ട്രക്ടർ (ഡിസൈൻ) നമുക്ക് അവരെ വിളിക്കാം tablProdaji и ടേബിൾസിറ്റി, യഥാക്രമം:

രീതി 1. ഷീറ്റുകൾ ഉപയോഗിച്ച് വിഭജിക്കാനുള്ള മാക്രോ

വിപുലമായ ടാബിൽ ഡെവലപ്പർ (ഡെവലപ്പർ) ബട്ടണിൽ ക്ലിക്ക് ചെയ്യുക വിഷ്വൽ ബേസിക് അല്ലെങ്കിൽ കീബോർഡ് കുറുക്കുവഴി ഉപയോഗിക്കുക ആൾട്ട്+F11. തുറക്കുന്ന മാക്രോ എഡിറ്റർ വിൻഡോയിൽ, മെനുവിലൂടെ ഒരു പുതിയ ശൂന്യമായ മൊഡ്യൂൾ ചേർക്കുക തിരുകുക - മൊഡ്യൂൾ താഴെ പറയുന്ന കോഡ് അവിടെ പകർത്തുക:

സബ് സ്പ്ലിറ്റർ() ശ്രേണിയിലെ ഓരോ സെല്ലിനും("തബ്ൾഗോറോഡ") ശ്രേണി("തബ്ൾപ്രോഡാജി").ഓട്ടോഫിൽട്ടർ ഫീൽഡ്:=3, മാനദണ്ഡം1:=സെൽ.മൂല്യ ശ്രേണി("തബ്ൾപ്രോഡാജി[#വിശിഷ്‌ടമായത്]"). Sheets.Add ActiveSheet.Paste ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit അടുത്ത സെൽ വർക്ക്ഷീറ്റുകൾ("ദത്തൻ").ShowAllData End Sub	  

ഇവിടെ ഒരു ലൂപ്പിനൊപ്പം ഓരോന്നിനും ... അടുത്തത് ഡയറക്ടറിയുടെ സെല്ലുകളിലൂടെ കടന്നുപോകുന്നത് നടപ്പിലാക്കി ടേബിൾസിറ്റി, ഓരോ നഗരത്തിനും എവിടെയാണ് അത് ഫിൽട്ടർ ചെയ്യുന്നത് (രീതി ഓട്ടോഫിൽട്ടർ) യഥാർത്ഥ വിൽപ്പന പട്ടികയിൽ, തുടർന്ന് ഫലങ്ങൾ പുതുതായി സൃഷ്ടിച്ച ഷീറ്റിലേക്ക് പകർത്തുന്നു. വഴിയിൽ, സൃഷ്ടിച്ച ഷീറ്റ് നഗരത്തിന്റെ അതേ പേരിലേക്ക് പുനർനാമകരണം ചെയ്യുകയും സൗന്ദര്യത്തിനായി നിരകളുടെ വീതി സ്വയമേവ ഫിറ്റ് ചെയ്യുകയും ഓണാക്കുകയും ചെയ്യുന്നു.

നിങ്ങൾക്ക് ടാബിൽ Excel-ൽ സൃഷ്ടിച്ച മാക്രോ പ്രവർത്തിപ്പിക്കാൻ കഴിയും ഡെവലപ്പർ ബട്ടൺ മാക്രോകൾ (ഡെവലപ്പർ - മാക്രോസ്) അല്ലെങ്കിൽ കീബോർഡ് കുറുക്കുവഴി ആൾട്ട്+F8.

രീതി 2. പവർ ക്വറിയിൽ ഒന്നിലധികം ചോദ്യങ്ങൾ സൃഷ്ടിക്കുക

മുമ്പത്തെ രീതി, അതിന്റെ എല്ലാ ഒതുക്കത്തിനും ലാളിത്യത്തിനും, ഒരു പ്രധാന പോരായ്മയുണ്ട് - യഥാർത്ഥ വിൽപ്പന പട്ടികയിൽ മാറ്റങ്ങൾ വരുത്തുമ്പോൾ മാക്രോ സൃഷ്ടിച്ച ഷീറ്റുകൾ അപ്ഡേറ്റ് ചെയ്യപ്പെടുന്നില്ല. ഫ്ലൈയിൽ അപ്‌ഡേറ്റ് ചെയ്യേണ്ടത് ആവശ്യമാണെങ്കിൽ, നിങ്ങൾ VBA + പവർ ക്വറി ബണ്ടിൽ ഉപയോഗിക്കേണ്ടിവരും, അല്ലെങ്കിൽ, സ്റ്റാറ്റിക് ഡാറ്റയുള്ള ഷീറ്റുകൾ മാത്രമല്ല, അപ്‌ഡേറ്റ് ചെയ്‌ത പവർ ക്വറി അന്വേഷണങ്ങളും ഉപയോഗിച്ച് ഒരു മാക്രോ ഉപയോഗിച്ച് സൃഷ്‌ടിക്കുക.

ഈ കേസിലെ മാക്രോ മുമ്പത്തേതിന് ഭാഗികമായി സമാനമാണ് (ഇതിന് ഒരു സൈക്കിളും ഉണ്ട് ഓരോന്നിനും ... അടുത്തത് ഡയറക്‌ടറിയിലെ നഗരങ്ങളിൽ ആവർത്തിക്കാൻ), എന്നാൽ ലൂപ്പിനുള്ളിൽ ഇനി ഫിൽട്ടർ ചെയ്യലും പകർത്തലും ഉണ്ടാകില്ല, എന്നാൽ ഒരു പവർ ക്വറി ക്വറി സൃഷ്‌ടിക്കുകയും അതിന്റെ ഫലങ്ങൾ ഒരു പുതിയ ഷീറ്റിലേക്ക് അപ്‌ലോഡ് ചെയ്യുകയും ചെയ്യുന്നു:

സബ് സ്പ്ലിറ്റർ2() ശ്രേണിയിലെ ഓരോ സെല്ലിനും ("സിറ്റി ടേബിൾ") ActiveWorkbook.Queries.പേര് ചേർക്കുക:=സെൽ.മൂല്യ, ഫോർമുല:= _ "let" & Chr(13) & "" & Chr(10) & " ഉറവിടം = Excel.CurrentWorkbook(){[Name=""TableSales""]}[Content]," & Chr(13) & "" & Chr(10) & " #""മാറ്റപ്പെട്ട തരം"" = Table.TransformColumnTypes(Source , {{""വിഭാഗം"", ടെക്സ്റ്റ് ടൈപ്പ് ചെയ്യുക}, {""പേര്"", ടൈപ്പ് ടെക്സ്റ്റ്}, {""നഗരം"", ടൈപ്പ് ടെക്സ്റ്റ്}, {""മാനേജർ"", ടൈപ്പ് ടെക്സ്റ്റ്}, {""ഡീൽ തീയതി "", തീയതി ടൈപ്പ് ടൈപ്പ് ചെയ്യുക}, {""ചെലവ്"", നമ്പർ ടൈപ്പ് നമ്പർ}})," & Chr(13) & "" & Chr(10) & " #""അരിപ്പ പ്രയോഗിച്ച വരികൾ"" = Table.Se " & _ "lectRows(#""മാറ്റപ്പെട്ട തരം"", ഓരോന്നും ([നഗരം] = """ & സെൽ. മൂല്യം & """))" & Chr(13) & "" & Chr(10) & "in " & Chr(13) & "" & Chr(10) & " #""അരിപ്പ പ്രയോഗിച്ച വരികൾ""" ActiveWorkbook.Worksheets.Add with ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _ "OLEDB; ദാതാവ് =Microsoft.Mashup.OleDb.1;ഡാറ്റ ഉറവിടം=$വർക്ക്ബുക്ക്$;ലൊക്കേഷൻ = " & സെൽ. മൂല്യം & "; വിപുലീകരിച്ച പ്രോപ്പർട്ടികൾ = """"" _ , ലക്ഷ്യസ്ഥാനം:= റേഞ്ച്("$A$1")). QueryTable .CommandType = xlCmd Sql .CommandText = അറേ("തിരഞ്ഞെടുക്കുക *[" & cell.Value & "]") മുതൽ .റൗനമ്പറുകൾ = തെറ്റ് SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value  

ലോഞ്ച് ചെയ്‌തതിന് ശേഷം, നഗരമനുസരിച്ച് ഞങ്ങൾ സമാന ഷീറ്റുകൾ കാണും, എന്നാൽ ഇതിനകം സൃഷ്‌ടിച്ച പവർ ക്വറി അന്വേഷണങ്ങൾ അവ രൂപപ്പെടുത്തും:

ഉറവിട ഡാറ്റയിൽ എന്തെങ്കിലും മാറ്റങ്ങളുണ്ടെങ്കിൽ, വലത് മൗസ് ബട്ടൺ ഉപയോഗിച്ച് അനുബന്ധ പട്ടിക അപ്ഡേറ്റ് ചെയ്താൽ മതിയാകും - കമാൻഡ് അപ്ഡേറ്റ് & സംരക്ഷിക്കുക (പുതുക്കുക) അല്ലെങ്കിൽ ബട്ടൺ ഉപയോഗിച്ച് എല്ലാ നഗരങ്ങളും ഒറ്റയടിക്ക് അപ്ഡേറ്റ് ചെയ്യുക എല്ലാം അപ്‌ഡേറ്റുചെയ്യുക ടാബ് ഡാറ്റ (ഡാറ്റ - എല്ലാം പുതുക്കുക).

• എന്താണ് മാക്രോകൾ, അവ എങ്ങനെ സൃഷ്ടിക്കുകയും ഉപയോഗിക്കുകയും ചെയ്യാം
• വർക്ക്ബുക്ക് ഷീറ്റുകൾ പ്രത്യേക ഫയലുകളായി സംരക്ഷിക്കുന്നു
• പുസ്തകത്തിന്റെ എല്ലാ ഷീറ്റുകളിൽ നിന്നും ഒരു ടേബിളിൽ ഡാറ്റ ശേഖരിക്കുന്നു