విషయ సూచిక
సమస్య యొక్క సూత్రీకరణ
చాలా మంది Excel వినియోగదారులు ముందుగానే లేదా తరువాత ఎదుర్కొనే అత్యంత ప్రామాణిక పరిస్థితులలో ఒకదాని కోసం ఒక అందమైన పరిష్కారాన్ని చూద్దాం: మీరు పెద్ద సంఖ్యలో ఫైల్‌ల నుండి డేటాను ఒక తుది పట్టికలో త్వరగా మరియు స్వయంచాలకంగా సేకరించాలి.
బ్రాంచ్ నగరాల నుండి డేటాతో అనేక ఫైల్‌లను కలిగి ఉన్న కింది ఫోల్డర్‌ని కలిగి ఉన్నారని అనుకుందాం:
ఫైల్‌ల సంఖ్య పట్టింపు లేదు మరియు భవిష్యత్తులో మారవచ్చు. ప్రతి ఫైల్‌కి పేరు పెట్టబడిన షీట్ ఉంటుంది అమ్మకాలుడేటా టేబుల్ ఎక్కడ ఉంది:
పట్టికలలోని వరుసల (ఆర్డర్లు) సంఖ్య, వాస్తవానికి, భిన్నంగా ఉంటుంది, కానీ నిలువు వరుసల సెట్ ప్రతిచోటా ప్రామాణికంగా ఉంటుంది.
టాస్క్: టేబుల్‌లలో సిటీ ఫైల్‌లు లేదా అడ్డు వరుసలను జోడించేటప్పుడు లేదా తొలగించేటప్పుడు తదుపరి ఆటోమేటిక్ అప్‌డేట్‌తో అన్ని ఫైల్‌ల నుండి డేటాను ఒకే పుస్తకంలో సేకరించడం. చివరి ఏకీకృత పట్టిక ప్రకారం, అప్పుడు ఏవైనా నివేదికలు, పివోట్ పట్టికలు, ఫిల్టర్-క్రమబద్ధీకరణ డేటా మొదలైన వాటిని రూపొందించడం సాధ్యమవుతుంది. ప్రధాన విషయం ఏమిటంటే సేకరించడం.
మేము ఆయుధాలను ఎంచుకుంటాము
పరిష్కారం కోసం, మాకు Excel 2016 యొక్క తాజా వెర్షన్ (అవసరమైన కార్యాచరణ ఇప్పటికే డిఫాల్ట్‌గా రూపొందించబడింది) లేదా ఉచిత యాడ్-ఇన్‌తో ఇన్‌స్టాల్ చేయబడిన Excel 2010-2013 యొక్క మునుపటి సంస్కరణలు అవసరం శక్తి ప్రశ్న Microsoft నుండి (దీన్ని ఇక్కడ డౌన్‌లోడ్ చేసుకోండి). పవర్ క్వెరీ అనేది బయటి ప్రపంచం నుండి డేటాను ఎక్సెల్‌లోకి లోడ్ చేయడానికి, ఆపై దాన్ని తీసివేయడానికి మరియు ప్రాసెస్ చేయడానికి ఒక సూపర్ ఫ్లెక్సిబుల్ మరియు సూపర్ పవర్‌ఫుల్ సాధనం. పవర్ క్వెరీ దాదాపుగా ఇప్పటికే ఉన్న అన్ని డేటా సోర్స్‌లకు మద్దతిస్తుంది – టెక్స్ట్ ఫైల్‌ల నుండి SQL మరియు Facebook వరకు కూడా 🙂
మీకు Excel 2013 లేదా 2016 లేకపోతే, మీరు మరింత చదవలేరు (తమాషా). Excel యొక్క పాత సంస్కరణల్లో, అటువంటి పనిని విజువల్ బేసిక్‌లో (ప్రారంభకులకు చాలా కష్టంగా ఉంటుంది) లేదా మార్పులేని మాన్యువల్ కాపీయింగ్ (దీనికి ఎక్కువ సమయం పడుతుంది మరియు లోపాలను సృష్టించడం) ద్వారా మాక్రోను ప్రోగ్రామింగ్ చేయడం ద్వారా మాత్రమే సాధించవచ్చు.
దశ 1. ఒక ఫైల్‌ని నమూనాగా దిగుమతి చేయండి
ముందుగా, ఒక వర్క్‌బుక్ నుండి డేటాను ఉదాహరణగా దిగుమతి చేద్దాం, తద్వారా Excel “ఆలోచనను ఎంచుకుంటుంది”. దీన్ని చేయడానికి, కొత్త ఖాళీ వర్క్‌బుక్‌ని సృష్టించండి మరియు…
- మీకు Excel 2016 ఉంటే, ట్యాబ్‌ను తెరవండి సమాచారం ఆపై ప్రశ్నను సృష్టించండి - ఫైల్ నుండి - పుస్తకం నుండి (డేటా — కొత్త ప్రశ్న- ఫైల్ నుండి — Excel నుండి)
- మీరు పవర్ క్వెరీ యాడ్-ఇన్‌తో Excel 2010-2013ని ఇన్‌స్టాల్ చేసి ఉంటే, ట్యాబ్‌ను తెరవండి శక్తి ప్రశ్న మరియు దానిపై ఎంచుకోండి ఫైల్ నుండి - పుస్తకం నుండి (ఫైల్ నుండి - Excel నుండి)
అప్పుడు, తెరుచుకునే విండోలో, నివేదికలతో మా ఫోల్డర్‌కు వెళ్లి, ఏదైనా సిటీ ఫైల్‌లను ఎంచుకోండి (ఏది పట్టింపు లేదు, ఎందుకంటే అవి అన్నీ విలక్షణమైనవి). కొన్ని సెకన్ల తర్వాత, నావిగేటర్ విండో కనిపిస్తుంది, ఇక్కడ మీరు ఎడమ వైపున మనకు అవసరమైన షీట్ (అమ్మకాలు) ఎంచుకోవాలి మరియు దాని కంటెంట్‌లు కుడి వైపున ప్రదర్శించబడతాయి:
మీరు ఈ విండో యొక్క కుడి దిగువ మూలలో ఉన్న బటన్‌పై క్లిక్ చేస్తే డౌన్¬లోడ్ చేయండి (లోడ్), అప్పుడు టేబుల్ వెంటనే దాని అసలు రూపంలో షీట్‌కు దిగుమతి చేయబడుతుంది. ఒకే ఫైల్ కోసం, ఇది మంచిది, కానీ మనం అలాంటి అనేక ఫైల్‌లను లోడ్ చేయాలి, కాబట్టి మేము కొంచెం భిన్నంగా వెళ్లి బటన్‌ను క్లిక్ చేస్తాము దిద్దుబాటు (సవరించు). ఆ తర్వాత, పవర్ క్వెరీ క్వెరీ ఎడిటర్ పుస్తకంలోని మా డేటాతో ప్రత్యేక విండోలో ప్రదర్శించబడాలి:
ఇది చాలా శక్తివంతమైన సాధనం, ఇది మాకు అవసరమైన వీక్షణకు పట్టికను "పూర్తి" చేయడానికి మిమ్మల్ని అనుమతిస్తుంది. దాని అన్ని ఫంక్షన్ల యొక్క ఉపరితల వివరణ కూడా దాదాపు వంద పేజీలను తీసుకుంటుంది, కానీ, చాలా క్లుప్తంగా ఉంటే, ఈ విండోను ఉపయోగించి మీరు వీటిని చేయవచ్చు:
- అనవసరమైన డేటా, ఖాళీ లైన్లు, లోపాలతో ఉన్న లైన్లను ఫిల్టర్ చేయండి
- ఒకటి లేదా అంతకంటే ఎక్కువ నిలువు వరుసల ద్వారా డేటాను క్రమబద్ధీకరించండి
- పునరావృతం నుండి బయటపడండి
- అంటుకునే వచనాన్ని నిలువు వరుసల ద్వారా విభజించండి (డిలిమిటర్లు, అక్షరాల సంఖ్య మొదలైనవి)
- వచనాన్ని క్రమంలో ఉంచండి (అదనపు ఖాళీలు, సరైన కేస్ మొదలైనవి తొలగించండి)
- సాధ్యమయ్యే ప్రతి విధంగా డేటా రకాలను మార్చండి (టెక్స్ట్ వంటి సంఖ్యలను సాధారణ సంఖ్యలుగా మార్చండి మరియు వైస్ వెర్సా)
- పట్టికలను మార్చండి (తిప్పండి) మరియు రెండు డైమెన్షనల్ క్రాస్ టేబుల్‌లను ఫ్లాట్‌గా విస్తరించండి
- పట్టికకు అదనపు నిలువు వరుసలను జోడించి, పవర్ క్వెరీలో నిర్మించిన M భాషను ఉపయోగించి వాటిలో సూత్రాలు మరియు విధులను ఉపయోగించండి.
- ...
ఉదాహరణకు, మన టేబుల్‌కి నెల వచనం పేరుతో కాలమ్‌ను జోడిద్దాం, తద్వారా పివోట్ పట్టిక నివేదికలను రూపొందించడం సులభం అవుతుంది. దీన్ని చేయడానికి, నిలువు వరుస శీర్షికపై కుడి క్లిక్ చేయండి తేదీమరియు ఆదేశాన్ని ఎంచుకోండి డూప్లికేట్ నిలువు వరుస (డూప్లికేట్ కాలమ్), ఆపై కనిపించే డూప్లికేట్ కాలమ్ యొక్క హెడర్‌పై కుడి-క్లిక్ చేసి, ఆదేశాలను ఎంచుకోండి రూపాంతరం - నెల - నెల పేరు:
ప్రతి అడ్డు వరుసకు నెల వచన పేర్లతో కొత్త నిలువు వరుసను రూపొందించాలి. నిలువు వరుస శీర్షికపై డబుల్-క్లిక్ చేయడం ద్వారా, మీరు దాని నుండి పేరు మార్చవచ్చు కాపీ తేదీ మరింత సౌకర్యవంతంగా ఉంటుంది <span style="font-family: Mandali">నెల</span>, ఉదా.
కొన్ని నిలువు వరుసలలో ప్రోగ్రామ్ డేటా రకాన్ని సరిగ్గా గుర్తించకపోతే, మీరు ప్రతి నిలువు వరుస యొక్క ఎడమ వైపున ఉన్న ఫార్మాట్ చిహ్నంపై క్లిక్ చేయడం ద్వారా సహాయం చేయవచ్చు:
మీరు సాధారణ ఫిల్టర్‌ని ఉపయోగించి ఎర్రర్‌లు లేదా ఖాళీ లైన్లు, అలాగే అనవసరమైన మేనేజర్‌లు లేదా కస్టమర్‌లు ఉన్న లైన్లను మినహాయించవచ్చు:
అంతేకాకుండా, ప్రదర్శించబడిన అన్ని పరివర్తనాలు కుడి ప్యానెల్‌లో పరిష్కరించబడతాయి, ఇక్కడ వాటిని ఎల్లప్పుడూ వెనక్కి తిప్పవచ్చు (క్రాస్) లేదా వాటి పారామితులను (గేర్) మార్చవచ్చు:
కాంతి మరియు సొగసైనది, కాదా?
దశ 2. మన అభ్యర్థనను ఫంక్షన్‌గా మారుద్దాం
దిగుమతి చేసుకున్న ప్రతి పుస్తకం కోసం చేసిన అన్ని డేటా పరివర్తనలను తదనంతరం పునరావృతం చేయడానికి, మేము సృష్టించిన అభ్యర్థనను ఫంక్షన్‌గా మార్చాలి, అది మా ఫైల్‌లన్నింటికీ వర్తించబడుతుంది. దీన్ని చేయడం నిజానికి చాలా సులభం.
ప్రశ్న ఎడిటర్‌లో, వీక్షణ ట్యాబ్‌కు వెళ్లి బటన్‌ను క్లిక్ చేయండి అధునాతన ఎడిటర్ (వీక్షణ — అధునాతన ఎడిటర్). మన మునుపటి చర్యలన్నీ M భాషలో కోడ్ రూపంలో వ్రాయబడే విండో తెరవబడాలి. ఉదాహరణ కోసం మేము దిగుమతి చేసుకున్న ఫైల్‌కు మార్గం కోడ్‌లో హార్డ్‌కోడ్ చేయబడిందని దయచేసి గమనించండి:
ఇప్పుడు కొన్ని సర్దుబాట్లు చేద్దాం:
వాటి అర్థం చాలా సులభం: మొదటి పంక్తి (ఫైల్‌పాత్)=> వాదనతో మా విధానాన్ని ఫంక్షన్‌గా మారుస్తుంది ఫైల్‌పాత్, మరియు క్రింద మేము ఈ వేరియబుల్ విలువకు స్థిర మార్గాన్ని మారుస్తాము.
అన్నీ. నొక్కండి ముగించు మరియు ఇది చూడాలి:
డేటా అదృశ్యమైందని భయపడవద్దు - వాస్తవానికి, ప్రతిదీ సరిగ్గా ఉంది, ప్రతిదీ ఇలా ఉండాలి 🙂 మేము మా అనుకూల ఫంక్షన్‌ని విజయవంతంగా సృష్టించాము, ఇక్కడ డేటాను దిగుమతి చేయడానికి మరియు ప్రాసెస్ చేయడానికి మొత్తం అల్గోరిథం నిర్దిష్ట ఫైల్‌తో ముడిపడి ఉండకుండా గుర్తుంచుకోబడుతుంది . ఇది మరింత అర్థమయ్యే పేరును ఇవ్వడానికి మిగిలి ఉంది (ఉదాహరణకు డేటా పొందండి) ఫీల్డ్‌లో కుడి వైపున ఉన్న ప్యానెల్‌లో మొదటి పేరు మరియు మీరు కోయవచ్చు హోమ్ — మూసివేయండి మరియు డౌన్‌లోడ్ చేయండి (హోమ్ - మూసివేయి మరియు లోడ్ చేయండి). ఉదాహరణ కోసం మేము దిగుమతి చేసుకున్న ఫైల్‌కు మార్గం కోడ్‌లో హార్డ్‌కోడ్ చేయబడిందని దయచేసి గమనించండి. మీరు ప్రధాన Microsoft Excel విండోకు తిరిగి వస్తారు, కానీ మా ఫంక్షన్‌కు సృష్టించబడిన కనెక్షన్‌తో ప్యానెల్ కుడివైపున కనిపిస్తుంది:
దశ 3. అన్ని ఫైళ్లను సేకరిస్తోంది
అన్ని కష్టతరమైన భాగం వెనుక ఉంది, ఆహ్లాదకరమైన మరియు సులభమైన భాగం మిగిలి ఉంది. ట్యాబ్‌కి వెళ్లండి డేటా - ప్రశ్నను సృష్టించండి - ఫైల్ నుండి - ఫోల్డర్ నుండి (డేటా — కొత్త ప్రశ్న — ఫైల్ నుండి — ఫోల్డర్ నుండి) లేదా, మీరు ఎక్సెల్ 2010-2013ని కలిగి ఉంటే, అదే విధంగా ట్యాబ్ శక్తి ప్రశ్న. కనిపించే విండోలో, మా అన్ని సోర్స్ సిటీ ఫైల్‌లు ఉన్న ఫోల్డర్‌ను పేర్కొనండి మరియు క్లిక్ చేయండి OK. తదుపరి దశలో ఈ ఫోల్డర్‌లో కనిపించే అన్ని Excel ఫైల్‌లు (మరియు దాని సబ్‌ఫోల్డర్‌లు) మరియు వాటిలో ప్రతిదానికి సంబంధించిన వివరాలు జాబితా చేయబడే విండోను తెరవాలి:
క్లిక్ చేయండి మార్చు (సవరించు) మరియు మళ్ళీ మనం తెలిసిన ప్రశ్న ఎడిటర్ విండోలోకి ప్రవేశిస్తాము.
ఇప్పుడు మనం సృష్టించిన ఫంక్షన్‌తో మా పట్టికకు మరొక నిలువు వరుసను జోడించాలి, ఇది ప్రతి ఫైల్ నుండి డేటాను "లాగుతుంది". దీన్ని చేయడానికి, ట్యాబ్‌కు వెళ్లండి కాలమ్ జోడించండి - కస్టమ్ కాలమ్ (కాలమ్‌ను జోడించు - అనుకూల కాలమ్‌ను జోడించు) మరియు కనిపించే విండోలో, మా ఫంక్షన్‌ను నమోదు చేయండి డేటా పొందండి, ప్రతి ఫైల్‌కు పూర్తి మార్గాన్ని ఆర్గ్యుమెంట్‌గా పేర్కొంటుంది:
క్లిక్ చేసిన తర్వాత OK సృష్టించిన కాలమ్ కుడివైపున ఉన్న మా పట్టికకు జోడించబడాలి.
ఇప్పుడు అన్ని అనవసరమైన నిలువు వరుసలను తొలగిస్తాము (ఎక్సెల్‌లో, కుడి మౌస్ బటన్‌ని ఉపయోగించి – తొలగించు), జోడించిన నిలువు వరుసను మరియు ఫైల్ పేరుతో నిలువు వరుసను మాత్రమే వదిలివేస్తుంది, ఎందుకంటే ఈ పేరు (మరింత ఖచ్చితంగా, నగరం) ప్రతి అడ్డు వరుస కోసం మొత్తం డేటాలో ఉండటానికి ఉపయోగకరంగా ఉంటుంది.
మరియు ఇప్పుడు "వావ్ క్షణం" - మా ఫంక్షన్‌తో జోడించిన నిలువు వరుస ఎగువ కుడి మూలలో దాని స్వంత బాణాలతో ఉన్న చిహ్నంపై క్లిక్ చేయండి:
… చెక్ చేయవద్దు అసలు నిలువు వరుస పేరును ఉపసర్గగా ఉపయోగించండి (అసలు నిలువు వరుస పేరును ఉపసర్గగా ఉపయోగించండి)మరియు క్లిక్ చేయండి OK. మరియు మా ఫంక్షన్ ప్రతి ఫైల్ నుండి డేటాను లోడ్ చేస్తుంది మరియు ప్రాసెస్ చేస్తుంది, రికార్డ్ చేయబడిన అల్గారిథమ్‌ను అనుసరించి మరియు సాధారణ పట్టికలో ప్రతిదీ సేకరిస్తుంది:
పూర్తి అందం కోసం, మీరు ఫైల్ పేర్లతో మొదటి నిలువు వరుస నుండి .xlsx పొడిగింపులను కూడా తీసివేయవచ్చు – ప్రామాణిక రీప్లేస్‌మెంట్ ద్వారా “ఏమీ లేదు” (కాలమ్ హెడర్‌పై కుడి క్లిక్ చేయండి – సబ్స్టిట్యూట్) మరియు ఈ కాలమ్‌కి పేరు మార్చండి సిటీ. మరియు కాలమ్‌లోని డేటా ఆకృతిని తేదీతో సరి చేయండి.
అన్నీ! నొక్కండి హోమ్ - మూసివేయండి మరియు లోడ్ చేయండి (ఇల్లు — మూసివేయి & లోడ్ చేయండి). అన్ని నగరాల కోసం ప్రశ్న ద్వారా సేకరించబడిన మొత్తం డేటా "స్మార్ట్ టేబుల్" ఆకృతిలో ప్రస్తుత Excel షీట్‌కు అప్‌లోడ్ చేయబడుతుంది:
సృష్టించబడిన కనెక్షన్ మరియు మా అసెంబ్లీ ఫంక్షన్ ఏ విధంగానూ విడిగా సేవ్ చేయవలసిన అవసరం లేదు - అవి సాధారణ మార్గంలో ప్రస్తుత ఫైల్‌తో కలిసి సేవ్ చేయబడతాయి.
భవిష్యత్తులో, ఫోల్డర్‌లో (నగరాలను జోడించడం లేదా తీసివేయడం) లేదా ఫైల్‌లలో (లైన్ల సంఖ్యను మార్చడం) ఏవైనా మార్పులతో, నేరుగా టేబుల్‌పై లేదా కుడి ప్యానెల్‌లోని ప్రశ్నపై కుడి-క్లిక్ చేసి, ఎంచుకోండి ఆదేశం నవీకరించండి & సేవ్ చేయండి (రిఫ్రెష్) – పవర్ క్వెరీ కొన్ని సెకన్లలో మొత్తం డేటాను మళ్లీ “పునర్నిర్మిస్తుంది”.
PS
సవరణ. జనవరి 2017 అప్‌డేట్‌ల తర్వాత, పవర్ క్వెరీ తనంతట తానుగా Excel వర్క్‌బుక్‌లను ఎలా సేకరించాలో నేర్చుకుంది, అంటే ఇకపై ప్రత్యేక ఫంక్షన్ చేయవలసిన అవసరం లేదు - ఇది స్వయంచాలకంగా జరుగుతుంది. అందువల్ల, ఈ వ్యాసం నుండి రెండవ దశ ఇకపై అవసరం లేదు మరియు మొత్తం ప్రక్రియ గమనించదగ్గ విధంగా సులభం అవుతుంది:
- ఎంచుకోండి అభ్యర్థనను సృష్టించండి - ఫైల్ నుండి - ఫోల్డర్ నుండి - ఫోల్డర్‌ని ఎంచుకోండి - సరే
- ఫైళ్ళ జాబితా కనిపించిన తర్వాత, నొక్కండి మార్చు
- ప్రశ్న ఎడిటర్ విండోలో, బైనరీ కాలమ్‌ను డబుల్ బాణంతో విస్తరించండి మరియు ప్రతి ఫైల్ నుండి తీసుకోవలసిన షీట్ పేరును ఎంచుకోండి
మరియు అంతే! పాట!
- పివోట్ టేబుల్‌లను నిర్మించడానికి అనువైన ఫ్లాట్‌గా క్రాస్‌ట్యాబ్‌ని రీడిజైన్ చేయండి
- పవర్ వ్యూలో యానిమేటెడ్ బబుల్ చార్ట్‌ను రూపొందించడం
- వివిధ Excel ఫైల్‌ల నుండి షీట్‌లను ఒకటిగా సమీకరించడానికి మాక్రో