सामग्री
समस्येचे सूत्रीकरण
बहुतेक एक्सेल वापरकर्त्यांना लवकरच किंवा नंतर तोंड द्यावे लागणार्‍या अत्यंत मानक परिस्थितींपैकी एकासाठी एक सुंदर उपाय पाहू: तुम्हाला एका अंतिम सारणीमध्ये मोठ्या संख्येने फाइल्समधून डेटा द्रुतपणे आणि स्वयंचलितपणे संकलित करणे आवश्यक आहे.
समजा आमच्याकडे खालील फोल्डर आहे, ज्यामध्ये शाखा शहरांमधील डेटासह अनेक फायली आहेत:
फायलींची संख्या काही फरक पडत नाही आणि भविष्यात बदलू शकते. प्रत्येक फाईलचे नाव दिलेले शीट असते विक्रीडेटा टेबल कुठे आहे:
सारण्यांमधील पंक्ती (ऑर्डर) ची संख्या अर्थातच भिन्न आहे, परंतु स्तंभांचा संच सर्वत्र मानक आहे.
कार्य: टेबलमधील शहर फाइल्स किंवा पंक्ती जोडताना किंवा हटवताना त्यानंतरच्या स्वयंचलित अपडेटसह सर्व फायलींमधून डेटा एका पुस्तकात गोळा करणे. अंतिम एकत्रित सारणीनुसार, नंतर कोणतेही अहवाल, मुख्य सारणी, फिल्टर-सॉर्ट डेटा इत्यादी तयार करणे शक्य होईल. मुख्य गोष्ट म्हणजे गोळा करण्यात सक्षम असणे.
आम्ही शस्त्रे निवडतो
सोल्यूशनसाठी, आम्हाला एक्सेल 2016 ची नवीनतम आवृत्ती (आवश्यक कार्यक्षमता आधीच डीफॉल्टनुसार तयार केलेली आहे) किंवा एक्सेल 2010-2013 च्या मागील आवृत्त्यांसह विनामूल्य अॅड-इन स्थापित करणे आवश्यक आहे. उर्जा प्रश्न Microsoft कडून (ते येथे डाउनलोड करा). Power Query हे बाहेरील जगातून Excel मध्ये डेटा लोड करण्यासाठी, नंतर तो काढून टाकण्यासाठी आणि त्यावर प्रक्रिया करण्यासाठी एक अतिशय लवचिक आणि सुपर पॉवरफुल टूल आहे. पॉवर क्वेरी जवळजवळ सर्व विद्यमान डेटा स्रोतांना समर्थन देते - मजकूर फाइल्सपासून SQL आणि अगदी Facebook पर्यंत 🙂
जर तुमच्याकडे एक्सेल 2013 किंवा 2016 नसेल तर तुम्ही पुढे वाचू शकत नाही (फक्त गंमत). एक्सेलच्या जुन्या आवृत्त्यांमध्ये, असे कार्य केवळ व्हिज्युअल बेसिकमध्ये मॅक्रो प्रोग्रामिंग करून (जे नवशिक्यांसाठी खूप कठीण आहे) किंवा नीरस मॅन्युअल कॉपी करून (ज्याला बराच वेळ लागतो आणि त्रुटी निर्माण करते) पूर्ण करता येते.
पायरी 1. नमुना म्हणून एक फाइल आयात करा
प्रथम, उदाहरण म्हणून एका वर्कबुकमधून डेटा इंपोर्ट करू, जेणेकरून एक्सेल “कल्पना उचलेल”. हे करण्यासाठी, नवीन रिक्त कार्यपुस्तिका तयार करा आणि…
- तुमच्याकडे Excel 2016 असल्यास, टॅब उघडा डेटा आणि नंतर क्वेरी तयार करा - फाइलमधून - पुस्तकातून (डेटा — नवीन क्वेरी- फाईलमधून — एक्सेलमधून)
- तुमच्याकडे Power Query अॅड-इन इन्स्टॉल केलेले Excel 2010-2013 असल्यास, टॅब उघडा उर्जा प्रश्न आणि त्यावर निवडा फाइलमधून - पुस्तकातून (फाइलमधून - एक्सेल वरून)
त्यानंतर, उघडलेल्या विंडोमध्ये, अहवालांसह आमच्या फोल्डरवर जा आणि शहराच्या कोणत्याही फायली निवडा (त्याने कोणती फरक पडत नाही, कारण त्या सर्व वैशिष्ट्यपूर्ण आहेत). काही सेकंदांनंतर, नेव्हिगेटर विंडो दिसली पाहिजे, जिथे आपल्याला डाव्या बाजूला आम्हाला आवश्यक असलेली शीट (विक्री) निवडण्याची आवश्यकता आहे आणि त्यातील सामग्री उजव्या बाजूला प्रदर्शित केली जाईल:
जर तुम्ही या विंडोच्या खालच्या उजव्या कोपऱ्यातील बटणावर क्लिक केले डाउनलोड (लोड), नंतर सारणी त्याच्या मूळ स्वरूपात शीटवर त्वरित आयात केली जाईल. एका फाईलसाठी, हे चांगले आहे, परंतु आम्हाला अशा अनेक फायली लोड कराव्या लागतील, म्हणून आम्ही थोडे वेगळे जाऊ आणि बटणावर क्लिक करू. दुरुस्ती (सुधारणे). त्यानंतर, पॉवर क्वेरी क्वेरी संपादक एका वेगळ्या विंडोमध्ये आमच्या पुस्तकातील डेटासह प्रदर्शित केले जावे:
हे एक अतिशय शक्तिशाली साधन आहे जे आपल्याला आवश्यक असलेल्या दृश्यासाठी सारणी “समाप्त” करण्यास अनुमती देते. त्याच्या सर्व फंक्शन्सचे वरवरचे वर्णन देखील सुमारे शंभर पृष्ठे घेईल, परंतु, अगदी थोडक्यात, या विंडोचा वापर करून आपण हे करू शकता:
- अनावश्यक डेटा, रिकाम्या ओळी, त्रुटी असलेल्या रेषा फिल्टर करा
- एक किंवा अधिक स्तंभांद्वारे डेटाची क्रमवारी लावा
- पुनरावृत्तीपासून मुक्त व्हा
- चिकट मजकूर स्तंभांद्वारे विभाजित करा (डिलिमिटर, वर्णांची संख्या इ.)
- मजकूर क्रमाने ठेवा (अतिरिक्त जागा काढा, केस योग्य करा इ.)
- प्रत्येक संभाव्य मार्गाने डेटा प्रकार रूपांतरित करा (मजकूर सारख्या संख्यांना सामान्य संख्येमध्ये बदला आणि त्याउलट)
- टेबल्स ट्रान्सपोज करा (फिरवा) आणि द्विमितीय क्रॉस-टेबल सपाट मध्ये विस्तृत करा
- टेबलमध्ये अतिरिक्त स्तंभ जोडा आणि पॉवर क्वेरीमध्ये तयार केलेली M भाषा वापरून त्यातील सूत्रे आणि कार्ये वापरा.
- ...
उदाहरणार्थ, आमच्या टेबलमध्ये महिन्याच्या मजकूर नावासह एक स्तंभ जोडू या, जेणेकरून नंतर मुख्य सारणी अहवाल तयार करणे सोपे होईल. हे करण्यासाठी, स्तंभ शीर्षकावर उजवे-क्लिक करा तारीखआणि कमांड निवडा डुप्लिकेट स्तंभ (डुप्लिकेट स्तंभ), आणि नंतर दिसणार्‍या डुप्लिकेट स्तंभाच्या शीर्षलेखावर उजवे-क्लिक करा आणि आदेश निवडा परिवर्तन – महिना – महिन्याचे नाव:
प्रत्येक पंक्तीसाठी महिन्याच्या मजकुराच्या नावांसह एक नवीन स्तंभ तयार केला पाहिजे. स्तंभ शीर्षकावर डबल-क्लिक करून, तुम्ही त्याचे नाव बदलू शकता तारीख कॉपी करा अधिक आरामदायक करण्यासाठी महिना, उदा.
जर काही स्तंभांमध्ये प्रोग्रामने डेटा प्रकार अचूकपणे ओळखला नसेल तर आपण प्रत्येक स्तंभाच्या डाव्या बाजूला असलेल्या स्वरूप चिन्हावर क्लिक करून मदत करू शकता:
तुम्ही साधे फिल्टर वापरून त्रुटी किंवा रिकाम्या ओळी तसेच अनावश्यक व्यवस्थापक किंवा ग्राहक वगळू शकता:
शिवाय, सर्व सादर केलेले परिवर्तन उजव्या पॅनेलमध्ये निश्चित केले जातात, जेथे ते नेहमी मागे (क्रॉस) आणले जाऊ शकतात किंवा त्यांचे पॅरामीटर्स (गियर) बदलू शकतात:
हलके आणि मोहक, नाही का?
पायरी 2. आपल्या विनंतीचे फंक्शनमध्ये रूपांतर करूया
त्यानंतर प्रत्येक आयात केलेल्या पुस्तकासाठी केलेल्या सर्व डेटा परिवर्तनांची पुनरावृत्ती करण्यासाठी, आम्हाला आमची तयार केलेली विनंती एका फंक्शनमध्ये रूपांतरित करणे आवश्यक आहे, जे नंतर आमच्या सर्व फायलींवर लागू केले जाईल. हे करणे प्रत्यक्षात खूप सोपे आहे.
क्वेरी एडिटरमध्ये, व्ह्यू टॅबवर जा आणि बटणावर क्लिक करा प्रगत संपादक (पहा — प्रगत संपादक). एक विंडो उघडली पाहिजे जिथे आमच्या मागील सर्व क्रिया M भाषेत कोडच्या स्वरूपात लिहिल्या जातील. कृपया लक्षात ठेवा की आम्ही उदाहरणासाठी आयात केलेल्या फाईलचा मार्ग कोडमध्ये हार्डकोड केलेला आहे:
आता दोन समायोजन करूया:
त्यांचा अर्थ सोपा आहे: पहिली ओळ (फाइलपाथ) => आमच्या कार्यपद्धतीला युक्तिवादासह फंक्शनमध्ये बदलते फाइलपाथ, आणि खाली आम्ही या व्हेरिएबलच्या व्हॅल्यूसाठी निश्चित मार्ग बदलतो.
सर्व. वर क्लिक करा समाप्त आणि हे पहावे:
डेटा गायब झाल्याची भीती बाळगू नका - खरं तर, सर्वकाही ठीक आहे, सर्वकाही यासारखे दिसले पाहिजे 🙂 आम्ही आमचे कस्टम फंक्शन यशस्वीरित्या तयार केले आहे, जिथे डेटा आयात आणि प्रक्रिया करण्यासाठी संपूर्ण अल्गोरिदम विशिष्ट फाइलशी बांधल्याशिवाय लक्षात ठेवला जातो. . त्याला अधिक समजण्यासारखे नाव देणे बाकी आहे (उदाहरणार्थ डेटा मिळवा) फील्डमध्ये उजवीकडे पॅनेलमध्ये नाव आणि तुम्ही कापणी करू शकता मुख्यपृष्ठ - बंद करा आणि डाउनलोड करा (घर - बंद करा आणि लोड करा). कृपया लक्षात घ्या की आम्ही उदाहरणासाठी आयात केलेल्या फाईलचा मार्ग कोडमध्ये हार्डकोड केलेला आहे. तुम्ही मुख्य मायक्रोसॉफ्ट एक्सेल विंडोवर परत याल, परंतु आमच्या फंक्शनसाठी तयार केलेले कनेक्शन असलेले पॅनेल उजवीकडे दिसले पाहिजे:
पायरी 3. सर्व फाइल्स गोळा करणे
सर्व कठीण भाग मागे आहे, आनंददायी आणि सोपा भाग शिल्लक आहे. टॅबवर जा डेटा - क्वेरी तयार करा - फाइलमधून - फोल्डरमधून (डेटा — नवीन क्वेरी — फाइलमधून — फोल्डरमधून) किंवा, तुमच्याकडे Excel 2010-2013 असल्यास, टॅबप्रमाणेच उर्जा प्रश्न. दिसत असलेल्या विंडोमध्ये, आमच्या सर्व स्त्रोत शहर फायली जेथे आहेत ते फोल्डर निर्दिष्ट करा आणि क्लिक करा OK. पुढील चरणात एक विंडो उघडली पाहिजे जिथे या फोल्डरमध्ये आढळलेल्या सर्व एक्सेल फायली (आणि त्याचे सबफोल्डर्स) आणि त्या प्रत्येकाचे तपशील सूचीबद्ध केले जातील:
क्लिक करा बदल (सुधारणे) आणि पुन्हा आपण परिचित क्वेरी एडिटर विंडोमध्ये प्रवेश करू.
आता आम्हाला आमच्या टेबलमध्ये आमच्या तयार केलेल्या फंक्शनसह आणखी एक कॉलम जोडण्याची आवश्यकता आहे, जे प्रत्येक फाइलमधील डेटा "पुल" करेल. हे करण्यासाठी, टॅबवर जा स्तंभ जोडा - सानुकूल स्तंभ (स्तंभ जोडा — सानुकूल स्तंभ जोडा) आणि दिसणाऱ्या विंडोमध्ये आमचे फंक्शन एंटर करा डेटा मिळवा, प्रत्येक फाईलचा पूर्ण मार्ग युक्तिवाद म्हणून निर्दिष्ट करणे:
वर क्लिक केल्यानंतर OK तयार केलेला स्तंभ उजवीकडील आमच्या टेबलमध्ये जोडला जावा.
आता सर्व अनावश्यक कॉलम्स हटवू (एक्सेल प्रमाणे, उजवे माऊस बटण वापरून – काढा), फक्त जोडलेले स्तंभ आणि फाईलच्या नावासह स्तंभ सोडून, कारण हे नाव (अधिक तंतोतंत, शहर) प्रत्येक पंक्तीच्या एकूण डेटामध्ये असणे उपयुक्त ठरेल.
आणि आता “व्वा मोमेंट” – आमच्या फंक्शनसह जोडलेल्या स्तंभाच्या वरच्या उजव्या कोपर्यात स्वतःच्या बाणांसह चिन्हावर क्लिक करा:
… अनचेक करा मूळ स्तंभाचे नाव उपसर्ग म्हणून वापरा (मूळ स्तंभाचे नाव उपसर्ग म्हणून वापरा)आणि क्लिक करा OK. आणि आमचे फंक्शन रेकॉर्ड केलेल्या अल्गोरिदमचे अनुसरण करून आणि सामान्य सारणीमध्ये सर्वकाही एकत्रित करून, प्रत्येक फाइलमधील डेटा लोड आणि प्रक्रिया करेल:
संपूर्ण सौंदर्यासाठी, तुम्ही फाइल नावांसह पहिल्या स्तंभातून .xlsx विस्तार देखील काढू शकता – “काहीही नाही” सह मानक बदलून (स्तंभ शीर्षलेखावर उजवे-क्लिक करा – पर्याय) आणि या स्तंभाचे नाव बदला शहर. आणि तारखेसह कॉलममधील डेटा फॉरमॅट देखील दुरुस्त करा.
सर्व! वर क्लिक करा घर - बंद करा आणि लोड करा (घर - बंद करा आणि लोड करा). सर्व शहरांसाठी क्वेरीद्वारे गोळा केलेला सर्व डेटा सध्याच्या एक्सेल शीटवर “स्मार्ट टेबल” फॉरमॅटमध्ये अपलोड केला जाईल:
तयार केलेले कनेक्शन आणि आमचे असेंब्ली फंक्शन कोणत्याही प्रकारे स्वतंत्रपणे सेव्ह करण्याची आवश्यकता नाही – ते नेहमीच्या पद्धतीने सध्याच्या फाइलसह जतन केले जातात.
भविष्यात, फोल्डरमध्ये (शहरे जोडणे किंवा काढून टाकणे) किंवा फायलींमध्ये (ओळींची संख्या बदलणे) कोणत्याही बदलांसह, थेट टेबलवर किंवा उजव्या पॅनेलमधील क्वेरीवर उजवे-क्लिक करणे पुरेसे असेल आणि ते निवडा. आज्ञा अपडेट आणि सेव्ह करा (रिफ्रेश) - पॉवर क्वेरी काही सेकंदात सर्व डेटा पुन्हा "पुन्हा तयार" करेल.
PS
दुरुस्ती. जानेवारी 2017 च्या अद्यतनांनंतर, Power Query ने स्वतःहून Excel कार्यपुस्तिका कशी संकलित करायची ते शिकले, म्हणजे आता वेगळे कार्य करण्याची गरज नाही – ते आपोआप होते. अशा प्रकारे, या लेखातील दुसरी पायरी यापुढे आवश्यक नाही आणि संपूर्ण प्रक्रिया लक्षणीयपणे सोपी होईल:
- निवडा विनंती तयार करा - फाइलमधून - फोल्डरमधून - फोल्डर निवडा - ठीक आहे
- फाइल्सची यादी दिसल्यानंतर, दाबा बदल
- क्वेरी एडिटर विंडोमध्ये, बायनरी कॉलम दुहेरी बाणाने विस्तृत करा आणि प्रत्येक फाइलमधून पत्रकाचे नाव निवडा.
आणि ते सर्व आहे! गाणे!
- पिव्होट टेबल बनवण्यासाठी योग्य असलेल्या फ्लॅटमध्ये क्रॉसटॅबची पुनर्रचना करा
- पॉवर व्ह्यूमध्ये अॅनिमेटेड बबल चार्ट तयार करणे
- वेगवेगळ्या एक्सेल फायलींमधून शीट्स एकत्र करण्यासाठी मॅक्रो