బహుళ డేటా పరిధులలో పివోట్ పట్టిక

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

Pivot పట్టికలు Excelలో అత్యంత అద్భుతమైన సాధనాల్లో ఒకటి. కానీ ఇప్పటివరకు, దురదృష్టవశాత్తు, Excel యొక్క సంస్కరణలు ఏవీ ఫ్లైలో అటువంటి సాధారణ మరియు అవసరమైన పనిని చేయలేవు, ఉదాహరణకు, వివిధ షీట్లలో లేదా వేర్వేరు పట్టికలలో ఉన్న అనేక ప్రారంభ డేటా పరిధుల కోసం సారాంశాన్ని రూపొందించడం:

మేము ప్రారంభించడానికి ముందు, కొన్ని పాయింట్లను స్పష్టం చేద్దాం. ముందుగా, మా డేటాలో కింది షరతులు నెరవేరుతాయని నేను నమ్ముతున్నాను:

• పట్టికలు ఏదైనా డేటాతో ఎన్ని వరుసలనైనా కలిగి ఉండవచ్చు, కానీ అవి తప్పనిసరిగా ఒకే శీర్షికను కలిగి ఉండాలి.
• మూలాధార పట్టికలతో షీట్‌లపై అదనపు డేటా ఉండకూడదు. ఒక షీట్ - ఒక టేబుల్. నియంత్రించడానికి, కీబోర్డ్ సత్వరమార్గాన్ని ఉపయోగించమని నేను మీకు సలహా ఇస్తున్నాను Ctrl+చివర, ఇది మిమ్మల్ని వర్క్‌షీట్‌లో చివరిగా ఉపయోగించిన సెల్‌కి తరలిస్తుంది. ఆదర్శవంతంగా, ఇది డేటా పట్టికలో చివరి సెల్ అయి ఉండాలి. మీరు క్లిక్ చేసినప్పుడు Ctrl+చివర టేబుల్‌కి కుడివైపు లేదా దిగువన ఉన్న ఏదైనా ఖాళీ సెల్ హైలైట్ చేయబడుతుంది - ఈ ఖాళీ నిలువు వరుసలను కుడివైపు లేదా పట్టిక తర్వాత పట్టిక క్రింద వరుసలను తొలగించి, ఫైల్‌ను సేవ్ చేయండి.

విధానం 1: పవర్ క్వెరీని ఉపయోగించి పివోట్ కోసం పట్టికలను రూపొందించండి

Excel కోసం 2010 సంస్కరణ నుండి ప్రారంభించి, ఏదైనా డేటాను సేకరించి, మార్చగలిగే ఉచిత పవర్ క్వెరీ యాడ్-ఇన్ ఉంది, ఆపై పివోట్ టేబుల్‌ను రూపొందించడానికి మూలంగా ఇవ్వవచ్చు. ఈ యాడ్-ఇన్ సహాయంతో మన సమస్యను పరిష్కరించడం అస్సలు కష్టం కాదు.

ముందుగా, Excelలో కొత్త ఖాళీ ఫైల్‌ని క్రియేట్ చేద్దాం – దానిలో అసెంబ్లీ జరుగుతుంది మరియు దానిలో పివోట్ టేబుల్ సృష్టించబడుతుంది.

ఆపై ట్యాబ్‌లో సమాచారం (మీకు Excel 2016 లేదా తర్వాత ఉంటే) లేదా ట్యాబ్‌లో శక్తి ప్రశ్న (మీకు Excel 2010-2013 ఉంటే) ఆదేశాన్ని ఎంచుకోండి ప్రశ్నను సృష్టించండి - ఫైల్ నుండి - ఎక్సెల్ (డేటా పొందండి — ఫైల్ నుండి — Excel) మరియు సేకరించాల్సిన పట్టికలతో సోర్స్ ఫైల్‌ను పేర్కొనండి:

కనిపించే విండోలో, ఏదైనా షీట్‌ని ఎంచుకోండి (ఇది ఏది పట్టింపు లేదు) మరియు దిగువ బటన్‌ను నొక్కండి మార్చు (సవరించు):

పవర్ క్వెరీ క్వెరీ ఎడిటర్ విండో ఎక్సెల్ పైన తెరవాలి. ప్యానెల్లో విండో యొక్క కుడి వైపున అభ్యర్థన పారామీటర్లు మొదటిది తప్ప స్వయంచాలకంగా సృష్టించబడిన అన్ని దశలను తొలగించండి - మూల (మూలం):

ఇప్పుడు మేము అన్ని షీట్ల సాధారణ జాబితాను చూస్తాము. ఫైల్‌లో డేటా షీట్‌లతో పాటు మరికొన్ని సైడ్ షీట్‌లు ఉంటే, ఈ దశలో టేబుల్ హెడర్‌లోని ఫిల్టర్‌ని ఉపయోగించి మిగతావన్నీ మినహాయించి, సమాచారాన్ని లోడ్ చేయాల్సిన షీట్‌లను మాత్రమే ఎంచుకోవడం మా పని:

కాలమ్ మినహా అన్ని నిలువు వరుసలను తొలగించండి సమాచారంనిలువు వరుస శీర్షికను కుడి-క్లిక్ చేసి ఎంచుకోవడం ద్వారా ఇతర నిలువు వరుసలను తొలగించండి (తొలగించు ఇతర నిలువు వరుసలు):

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

మీరు ప్రతిదీ సరిగ్గా చేస్తే, ఈ సమయంలో మీరు అన్ని పట్టికలలోని కంటెంట్‌లను ఒకదానికొకటి క్రింద ఒకటిగా చూడాలి:

బటన్‌తో మొదటి అడ్డు వరుసను టేబుల్ హెడర్‌కు పెంచడానికి ఇది మిగిలి ఉంది మొదటి పంక్తిని హెడర్‌లుగా ఉపయోగించండి (మొదటి వరుసను హెడర్‌లుగా ఉపయోగించండి) టాబ్ హోమ్ (హోమ్) మరియు ఫిల్టర్‌ని ఉపయోగించి డేటా నుండి డూప్లికేట్ టేబుల్ హెడర్‌లను తీసివేయండి:

కమాండ్‌తో చేసిన ప్రతిదాన్ని సేవ్ చేయండి మూసివేయండి మరియు లోడ్ చేయండి - మూసివేయండి మరియు లోడ్ చేయండి… (మూసివేయి & లోడ్ చేయి - మూసివేయి & లోడ్ చేయి...) టాబ్ హోమ్ (హోమ్), మరియు తెరుచుకునే విండోలో, ఎంపికను ఎంచుకోండి కనెక్షన్ మాత్రమే (కనెక్షన్ మాత్రమే):

అంతా. ఇది సారాంశాన్ని రూపొందించడానికి మాత్రమే మిగిలి ఉంది. దీన్ని చేయడానికి, ట్యాబ్‌కు వెళ్లండి చొప్పించు - పివోట్ టేబుల్ (ఇన్సర్ట్ — పివోట్ టేబుల్), ఎంపికను ఎంచుకోండి బాహ్య డేటా మూలాన్ని ఉపయోగించండి (బాహ్య డేటా మూలాన్ని ఉపయోగించండి)ఆపై బటన్‌ను క్లిక్ చేయడం ద్వారా కనెక్షన్‌ని ఎంచుకోండి, మా అభ్యర్థన. పైవట్ యొక్క తదుపరి సృష్టి మరియు కాన్ఫిగరేషన్ మనకు అవసరమైన ఫీల్డ్‌లను అడ్డు వరుసలు, నిలువు వరుసలు మరియు విలువల ప్రాంతంలోకి లాగడం ద్వారా పూర్తిగా ప్రామాణిక మార్గంలో జరుగుతుంది:

భవిష్యత్తులో సోర్స్ డేటా మారితే లేదా మరికొన్ని స్టోర్ షీట్‌లు జోడించబడితే, ఆదేశాన్ని ఉపయోగించి ప్రశ్న మరియు మా సారాంశాన్ని నవీకరించడం సరిపోతుంది. అన్నింటినీ రిఫ్రెష్ చేయండి టాబ్ సమాచారం (డేటా — అన్నీ రిఫ్రెష్ చేయండి).

విధానం 2. మేము మాక్రోలో UNION SQL కమాండ్‌తో పట్టికలను ఏకం చేస్తాము

మా సమస్యకు మరొక పరిష్కారం ఈ మాక్రో ద్వారా సూచించబడుతుంది, ఇది ఆదేశాన్ని ఉపయోగించి పివోట్ టేబుల్ కోసం డేటా సెట్ (కాష్)ని సృష్టిస్తుంది. UNITY SQL ప్రశ్న భాష. ఈ ఆదేశం శ్రేణిలో పేర్కొన్న అన్నింటి నుండి పట్టికలను మిళితం చేస్తుంది షీట్ పేర్లు పుస్తకం యొక్క షీట్‌లు ఒకే డేటా పట్టికలో. అంటే, భౌతికంగా వేర్వేరు షీట్‌ల నుండి ఒకదానికి పరిధులను కాపీ చేసి అతికించడానికి బదులుగా, మేము కంప్యూటర్ యొక్క RAMలో అదే చేస్తాము. అప్పుడు మాక్రో ఇచ్చిన పేరుతో కొత్త షీట్‌ను జోడిస్తుంది (వేరియబుల్ ఫలితాల షీట్ పేరు) మరియు సేకరించిన కాష్ ఆధారంగా దానిపై పూర్తి స్థాయి (!) సారాంశాన్ని సృష్టిస్తుంది.

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

Sub New_Multi_Table_Pivot() డిమ్ ఐ లాంగ్ డిమ్ arSQL() స్ట్రింగ్ డిమ్ objPivotCache వంటి మసక objRS ఆబ్జెక్ట్ డిమ్ రిజల్ట్‌గా ఆబ్జెక్ట్ డిమ్ రిజల్ట్‌షీట్‌నేమ్ వేరియంట్ 'షీట్‌నేమ్‌గా స్ట్రింగ్ డిమ్ షీట్‌నేమ్‌లు వేరియంట్‌గా 'షీట్ పేరు ప్రదర్శించబడుతుంది, ఇక్కడ ఫలితంగా పైవట్ యొక్క రేయిట్ హీట్ రేప్ ప్రదర్శించబడుతుంది. మూలాధార పట్టికలతో పేర్లు SheetsNames = అర్రే("ఆల్ఫా", "బీటా", "గామా", "డెల్టా") 'మేము ActiveWorkbook ReDim arSQL(1 నుండి (UBound(SheetsNames) + 1)తో SheetsNames నుండి షీట్‌ల నుండి పట్టికల కోసం కాష్‌ను రూపొందిస్తాము ) i = LBound (SheetsNames) కోసం UBound(SheetsNames) arSQL(i + 1) = " [" & SheetsNames(i) & "$]" నుండి *ని ఎంపిక చేసుకోండి .Join$( arSQL, " UNION ALL "), _ Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", _ .FullName, ";Extended Properties=""Excel 8.0;"ని తెరవండి ""), vbNullString )తో ముగించు 'ఫలిత పివోట్ పట్టికను ప్రదర్శించడానికి షీట్‌ను మళ్లీ సృష్టించుము, తదుపరి అప్లికేషన్‌లో లోపం పునఃప్రారంభించండి.DisplayAlerts = తప్పుడు వర్క్‌షీట్‌లు(ఫలితషీట్ పేరు). సెట్ wsPivot = వర్క్‌షీట్‌లను తొలగించండి. wsPivoని జోడించండి. t. పేరు = ResultSheetName 'ఈ షీట్‌లో రూపొందించబడిన కాష్ సారాంశాన్ని ప్రదర్శించు objPivotCache = ActiveWorkbook.PivotCaches.Add(xlExternal) సెట్ objPivotCache.Recordset = objRS సెట్ objRS = ఏమీ లేదు. SPivotCache. objPivotCache = నథింగ్ రేంజ్("A3").ఎండ్ విత్ ఎండ్ సబ్‌ని ఎంచుకోండి    

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

ఈ విధానం యొక్క ప్రతికూలతలు:

• కాష్‌కి సోర్స్ టేబుల్‌లకు కనెక్షన్ లేనందున డేటా నవీకరించబడలేదు. మీరు మూల డేటాను మార్చినట్లయితే, మీరు తప్పనిసరిగా మాక్రోను మళ్లీ అమలు చేయాలి మరియు సారాంశాన్ని మళ్లీ రూపొందించాలి.
• షీట్ల సంఖ్యను మార్చేటప్పుడు, స్థూల కోడ్‌ను సవరించడం అవసరం (శ్రేణి షీట్ పేర్లు).
  

కానీ చివరికి మేము నిజమైన పూర్తి స్థాయి పైవట్ పట్టికను పొందుతాము, వివిధ షీట్ల నుండి అనేక పరిధులలో నిర్మించబడింది:

అద్భుతం!

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

	 ప్రొవైడర్=Microsoft.Jet.OLEDB.4.0;  

కు:

	ప్రొవైడర్=Microsoft.ACE.OLEDB.12.0;  

మరియు Microsoft వెబ్‌సైట్ నుండి యాక్సెస్ నుండి ఉచిత డేటా ప్రాసెసింగ్ ఇంజిన్‌ను డౌన్‌లోడ్ చేసి, ఇన్‌స్టాల్ చేయండి – Microsoft Access Database Engine 2010 పునఃపంపిణీ చేయదగినది

విధానం 3: Excel యొక్క పాత సంస్కరణల నుండి PivotTable విజార్డ్‌ను ఏకీకృతం చేయండి

ఈ పద్ధతి కొద్దిగా పాతది, కానీ ఇప్పటికీ ప్రస్తావించదగినది. అధికారికంగా చెప్పాలంటే, 2003 వరకు మరియు దానితో సహా అన్ని వెర్షన్‌లలో, పివోట్ టేబుల్ విజార్డ్‌లో "అనేక కన్సాలిడేషన్ పరిధుల కోసం పైవట్‌ను రూపొందించడానికి" ఎంపిక ఉంది. అయితే, ఈ విధంగా రూపొందించబడిన నివేదిక, దురదృష్టవశాత్తూ, నిజమైన పూర్తి స్థాయి సారాంశం యొక్క దయనీయమైన పోలికగా మాత్రమే ఉంటుంది మరియు సాంప్రదాయ పివోట్ పట్టికల యొక్క అనేక "చిప్‌లకు" మద్దతు ఇవ్వదు:

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

జోడించిన బటన్‌పై క్లిక్ చేసిన తర్వాత, మీరు విజార్డ్ యొక్క మొదటి దశలో తగిన ఎంపికను ఎంచుకోవాలి:

ఆపై తదుపరి విండోలో, ప్రతి శ్రేణిని ఎంచుకుని, సాధారణ జాబితాకు జోడించండి:

కానీ, మళ్ళీ, ఇది పూర్తి స్థాయి సారాంశం కాదు, కాబట్టి దీని నుండి ఎక్కువ ఆశించవద్దు. నేను చాలా సులభమైన సందర్భాలలో మాత్రమే ఈ ఎంపికను సిఫార్సు చేయగలను.

• PivotTablesతో నివేదికలను సృష్టిస్తోంది
• పివోట్ టేబుల్స్‌లో గణనలను సెటప్ చేయండి
• మాక్రోలు అంటే ఏమిటి, వాటిని ఎలా ఉపయోగించాలి, VBA కోడ్‌ని ఎక్కడ కాపీ చేయాలి మొదలైనవి.
• బహుళ షీట్‌ల నుండి ఒకదానికి డేటా సేకరణ (PLEX యాడ్-ఆన్)