పట్టికను షీట్లుగా విభజించడం

మైక్రోసాఫ్ట్ ఎక్సెల్ అనేక పట్టికల నుండి (వివిధ షీట్‌ల నుండి లేదా వేర్వేరు ఫైల్‌ల నుండి) డేటాను సేకరించడానికి అనేక సాధనాలను కలిగి ఉంది: డైరెక్ట్ లింక్‌లు, ఫంక్షన్ పరోక్ష (పరోక్ష), పవర్ క్వెరీ మరియు పవర్ పివోట్ యాడ్-ఆన్‌లు మొదలైనవి. బారికేడ్ యొక్క ఇటువైపు నుండి, ప్రతిదీ బాగానే కనిపిస్తుంది.

కానీ మీరు విలోమ సమస్యను ఎదుర్కొంటే - ఒక టేబుల్ నుండి వేర్వేరు షీట్‌లకు డేటాను వ్యాప్తి చేయడం - అప్పుడు ప్రతిదీ చాలా విచారంగా ఉంటుంది. ప్రస్తుతానికి, దురదృష్టవశాత్తు, Excel యొక్క ఆర్సెనల్‌లో అటువంటి డేటా విభజన కోసం నాగరిక అంతర్నిర్మిత సాధనాలు లేవు. కాబట్టి మీరు విజువల్ బేసిక్‌లో మాక్రోని ఉపయోగించాలి లేదా మాక్రో రికార్డర్ + పవర్ క్వెరీ కాంబినేషన్‌ని కొద్దిగా “ఫైల్ రిఫైన్‌మెంట్”తో ఉపయోగించాలి.

దీన్ని ఎలా అమలు చేయవచ్చో నిశితంగా పరిశీలిద్దాం.

సమస్య యొక్క సూత్రీకరణ

మేము విక్రయాల కోసం 5000 కంటే ఎక్కువ వరుసల పరిమాణంతో అటువంటి పట్టికను ప్రారంభ డేటాగా కలిగి ఉన్నాము:

టాస్క్: ఈ పట్టికలోని డేటాను ఈ పుస్తకంలోని ప్రత్యేక షీట్లలో నగరం వారీగా పంపిణీ చేయడం. ఆ. అవుట్‌పుట్ వద్ద, మీరు ప్రతి షీట్‌లో సంబంధిత నగరంలో అమ్మకం ఉన్న పట్టిక నుండి ఆ వరుసలను మాత్రమే పొందాలి:

సిద్ధం

స్థూల కోడ్‌ను క్లిష్టతరం చేయకుండా మరియు సాధ్యమైనంత సులభంగా అర్థం చేసుకోవడానికి, మేము కొన్ని సన్నాహక దశలను చేద్దాం.

మొదటిది ప్రత్యేక శోధన పట్టికను సృష్టించండి, ఇక్కడ మీరు వేర్వేరు షీట్‌లను సృష్టించాలనుకుంటున్న అన్ని నగరాలను ఒకే కాలమ్ జాబితా చేస్తుంది. వాస్తవానికి, ఈ డైరెక్టరీ సోర్స్ డేటాలో ఉన్న అన్ని నగరాలను కలిగి ఉండకపోవచ్చు, కానీ మనకు నివేదికలు అవసరమైనవి మాత్రమే. అటువంటి పట్టికను సృష్టించడానికి సులభమైన మార్గం ఆదేశాన్ని ఉపయోగించడం డేటా - నకిలీలను తొలగించండి (డేటా — నకిలీలను తీసివేయి) కాలమ్ కాపీ కోసం సిటీ లేదా ఫంక్షన్ UNIK (ఏకైక) - మీరు Excel 365 యొక్క తాజా వెర్షన్‌ని కలిగి ఉంటే.

Excelలో కొత్త షీట్‌లు ప్రస్తుత (మునుపటి) కంటే ముందు (ఎడమవైపు) డిఫాల్ట్‌గా సృష్టించబడినందున, ఈ డైరెక్టరీలోని నగరాలను అవరోహణ క్రమంలో (Z నుండి A వరకు) క్రమబద్ధీకరించడం కూడా సమంజసంగా ఉంటుంది - ఆపై సృష్టించిన తర్వాత, నగరం షీట్‌లు అక్షర క్రమంలో అమర్చబడతాయి.

రెండవది, ది пరెండు పట్టికలను డైనమిక్‌గా మార్చండి (“స్మార్ట్”) వారితో పని చేయడం సులభతరం చేయడానికి. మేము ఆదేశాన్ని ఉపయోగిస్తాము హోమ్ - టేబుల్‌గా ఫార్మాట్ చేయండి (హోమ్ — టేబుల్ లాగా ఫార్మాట్ చేయండి) లేదా కీబోర్డ్ సత్వరమార్గం Ctrl+T. కనిపించే ట్యాబ్‌లో నమూనా రచయిత (రూపకల్పన) వారిని పిలుద్దాం tablProdaji и టేబుల్‌సిటీ, వరుసగా:

విధానం 1. షీట్ల ద్వారా విభజన కోసం మాక్రో

అధునాతన ట్యాబ్‌లో డెవలపర్ (డెవలపర్) బటన్ పై క్లిక్ చేయండి విజువల్ బేసిక్ లేదా కీబోర్డ్ సత్వరమార్గాన్ని ఉపయోగించండి alt+F11. తెరుచుకునే మాక్రో ఎడిటర్ విండోలో, మెను ద్వారా కొత్త ఖాళీ మాడ్యూల్‌ను చొప్పించండి చొప్పించు - మాడ్యూల్ మరియు క్రింది కోడ్‌ను అక్కడ కాపీ చేయండి:

సబ్ స్ప్లిటర్() పరిధిలోని ప్రతి సెల్‌కు("టబ్ల్‌గోరోడా") పరిధి("టబ్ల్‌ప్రోడాజీ").ఆటోఫిల్టర్ ఫీల్డ్:=3, ప్రమాణం1:=సెల్.విలువ పరిధి("టబ్ల్‌ప్రోడాజీ[#ప్రత్యేకమైనవి]"). Sheets.Add ActiveSheet.Add ActiveSheet.Name = cell.Value ActiveSheet.UsedRange.Columns.AutoFit తదుపరి సెల్ వర్క్‌షీట్‌లు("Dannыe").ShowAllData End Sub	  

ఇక్కడ ఒక లూప్‌తో ప్రతి … తదుపరి డైరెక్టరీ యొక్క కణాల ద్వారా మార్గాన్ని అమలు చేసింది టేబుల్‌సిటీ, ప్రతి నగరానికి ఎక్కడ ఫిల్టర్ చేయబడుతుంది (పద్ధతి ఆటోఫిల్టర్) ఒరిజినల్ సేల్స్ టేబుల్‌లో ఆపై ఫలితాలను కొత్తగా సృష్టించిన షీట్‌కి కాపీ చేయడం. అలాగే, సృష్టించిన షీట్ నగరం యొక్క అదే పేరుతో పేరు మార్చబడింది మరియు అందం కోసం నిలువు వరుసల వెడల్పును స్వయంచాలకంగా అమర్చడం దానిపై ఆన్ చేయబడింది.

మీరు ట్యాబ్‌లో ఎక్సెల్‌లో సృష్టించిన మాక్రోను అమలు చేయవచ్చు డెవలపర్ బటన్ macros (డెవలపర్ - మాక్రోలు) లేదా కీబోర్డ్ సత్వరమార్గం alt+F8.

విధానం 2. పవర్ క్వెరీలో బహుళ ప్రశ్నలను సృష్టించండి

మునుపటి పద్ధతి, దాని అన్ని కాంపాక్ట్‌నెస్ మరియు సరళత కోసం, ఒక ముఖ్యమైన లోపంగా ఉంది - అసలు అమ్మకాల పట్టికలో మార్పులు చేసినప్పుడు స్థూలచే సృష్టించబడిన షీట్‌లు నవీకరించబడవు. ఫ్లైలో అప్‌డేట్ చేయడం అవసరమైతే, మీరు VBA + పవర్ క్వెరీ బండిల్‌ను ఉపయోగించాల్సి ఉంటుంది లేదా బదులుగా, స్టాటిక్ డేటాతో కూడిన షీట్‌లను మాత్రమే కాకుండా, అప్‌డేట్ చేయబడిన పవర్ క్వెరీ ప్రశ్నలను ఉపయోగించి మాక్రోని సృష్టించండి.

ఈ సందర్భంలో స్థూల పాక్షికంగా మునుపటి దానితో సమానంగా ఉంటుంది (దీనికి కూడా ఒక చక్రం ఉంది ప్రతి … తదుపరి డైరెక్టరీలోని నగరాలపై మళ్లించడానికి), కానీ లూప్ లోపల ఇకపై ఫిల్టర్ చేయడం మరియు కాపీ చేయడం ఉండదు, కానీ పవర్ క్వెరీ ప్రశ్నను సృష్టించడం మరియు దాని ఫలితాలను కొత్త షీట్‌కి అప్‌లోడ్ చేయడం:

సబ్ స్ప్లిటర్2() పరిధిలోని ప్రతి సెల్‌కి("సిటీ టేబుల్") ActiveWorkbook.Queries.పేరుని జోడించు:=సెల్.విలువ, సూత్రం:= _ "లెట్" & Chr(13) & "" & Chr(10) & " మూలం = Excel.CurrentWorkbook(){[Name=""TableSales""]}[కంటెంట్]," & Chr(13) & "" & Chr(10) & " #""మార్చబడిన రకం"" = Table.TransformColumnTypes(మూలం , {{""కేటగిరీ"", టైప్ టెక్స్ట్}, {""పేరు"", టైప్ టెక్స్ట్}, {""నగరం"", టైప్ టెక్స్ట్}, {""మేనేజర్"", టైప్ టెక్స్ట్}, {""డీల్ తేదీ "", తేదీని టైప్ చేయండి}, {""ఖర్చు"", టైప్ నంబర్}})," & 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 = అర్రే("SELECT *నుండి [" & cell.Value & "]") .RowNumbers = తప్పు .FillAdjacentFormulas = తప్పు .PreserveFormatting = నిజం .RefreshOnFileOpen = తప్పు SaveData = True .AdjustColumnWidth = True .RefreshPeriod = 0 .PreserveColumnInfo = True .ListObject.DisplayName = cell.Value .Refresh BackgroundQuery:=False End with ActiveSheet.Name  

ప్రారంభించిన తర్వాత, మేము నగరం వారీగా అదే షీట్‌లను చూస్తాము, కానీ ఇప్పటికే సృష్టించిన పవర్ క్వెరీ ప్రశ్నలు వాటిని ఏర్పరుస్తాయి:

మూలాధార డేటాలో ఏవైనా మార్పులతో, కుడి మౌస్ బటన్తో సంబంధిత పట్టికను నవీకరించడానికి సరిపోతుంది - ఆదేశం నవీకరించండి & సేవ్ చేయండి (రిఫ్రెష్) లేదా బటన్‌ని ఉపయోగించి అన్ని నగరాలను ఒకేసారి పెద్దమొత్తంలో నవీకరించండి అన్నీ నవీకరించండి టాబ్ సమాచారం (డేటా — అన్నీ రిఫ్రెష్ చేయండి).

• మాక్రోలు అంటే ఏమిటి, వాటిని ఎలా సృష్టించాలి మరియు ఉపయోగించాలి
• వర్క్‌బుక్ షీట్‌లను ప్రత్యేక ఫైల్‌లుగా సేవ్ చేస్తోంది
• పుస్తకంలోని అన్ని షీట్‌ల నుండి డేటాను ఒకే పట్టికలో సేకరిస్తోంది