दोन टेबल्सची तुलना

आमच्याकडे दोन सारण्या आहेत (उदाहरणार्थ, किंमत सूचीच्या जुन्या आणि नवीन आवृत्त्या), ज्याची आम्हाला तुलना करणे आणि फरक द्रुतपणे शोधणे आवश्यक आहे:

हे लगेच स्पष्ट होते की नवीन किंमत सूचीमध्ये काहीतरी जोडले गेले आहे (खजूर, लसूण ...), काहीतरी गायब झाले आहे (ब्लॅकबेरी, रास्पबेरी ...), काही वस्तूंच्या किंमती बदलल्या आहेत (अंजीर, खरबूज ...). तुम्हाला हे सर्व बदल त्वरीत शोधणे आणि प्रदर्शित करणे आवश्यक आहे.

एक्सेलमधील कोणत्याही कार्यासाठी, जवळजवळ नेहमीच एकापेक्षा जास्त उपाय असतात (सामान्यतः 4-5). आमच्या समस्येसाठी, अनेक भिन्न दृष्टिकोन वापरले जाऊ शकतात:

• कार्य व्हीपीआर (VLOOKUP) — जुन्या किंमतींच्या नवीन किंमत सूचीमधून उत्पादनांची नावे शोधा आणि नवीनच्या पुढे जुनी किंमत प्रदर्शित करा आणि नंतर फरक ओळखा
• दोन सूची एकामध्ये विलीन करा आणि नंतर त्यावर आधारित एक मुख्य सारणी तयार करा, जिथे फरक स्पष्टपणे दिसतील
• एक्सेलसाठी पॉवर क्वेरी अॅड-इन वापरा

चला ते सर्व क्रमाने घेऊया.

पद्धत 1. VLOOKUP फंक्शनसह सारण्यांची तुलना करणे

जर तुम्ही या अद्भुत वैशिष्ट्याबद्दल पूर्णपणे अपरिचित असाल, तर प्रथम येथे पहा आणि त्यावरील व्हिडिओ ट्यूटोरियल वाचा किंवा पहा - स्वत: ला काही वर्षांचे आयुष्य वाचवा.

सामान्यतः, हे फंक्शन काही सामान्य पॅरामीटर जुळवून डेटा एका टेबलवरून दुसऱ्या टेबलवर खेचण्यासाठी वापरले जाते. या प्रकरणात, आम्ही जुन्या किमतींना नवीन किमतीमध्ये ढकलण्यासाठी याचा वापर करू:

ती उत्पादने, ज्यांच्या विरुद्ध #N/A त्रुटी निघाली, ती जुन्या यादीत नाहीत, म्हणजे जोडली गेली. किंमतीतील बदल देखील स्पष्टपणे दिसत आहेत.

साधक ही पद्धत: सोपी आणि स्पष्ट, "शैलीचे क्लासिक", जसे ते म्हणतात. Excel च्या कोणत्याही आवृत्तीमध्ये कार्य करते.

बाधक देखील आहे. नवीन किंमत सूचीमध्ये जोडलेली उत्पादने शोधण्यासाठी, तुम्हाला तीच प्रक्रिया उलट दिशेने करावी लागेल, म्हणजे VLOOKUP च्या मदतीने नवीन किमती जुन्या किमतीत वाढवा. उद्या टेबलांचे आकार बदलले तर सूत्रे जुळवावी लागतील. बरं, आणि खरोखर मोठ्या टेबलांवर (> 100 हजार पंक्ती), हे सर्व आनंद सभ्यपणे कमी होईल.

पद्धत 2: पिव्होट वापरून सारण्यांची तुलना करणे

किंमत सूचीच्या नावासह एक स्तंभ जोडून, ​​आमच्या सारण्या एका खाली कॉपी करू या, जेणेकरून नंतर तुम्हाला समजेल की कोणत्या सूचीमधून कोणती पंक्ती आहे:

आता, तयार केलेल्या टेबलच्या आधारे, आपण एक सारांश तयार करू घाला - PivotTable (घाला — मुख्य सारणी). चला शेत टाकूया उत्पादन रेषांच्या क्षेत्रापर्यंत, क्षेत्रापर्यंत किंमत स्तंभ क्षेत्र आणि फील्ड Цए.एन.ए. श्रेणी मध्ये:

तुम्ही बघू शकता, मुख्य सारणी आपोआप जुन्या आणि नवीन किंमत सूचींमधून सर्व उत्पादनांची सामान्य सूची तयार करेल (कोणतीही पुनरावृत्ती नाही!) आणि उत्पादनांची वर्णानुक्रमानुसार क्रमवारी लावेल. तुम्ही जोडलेली उत्पादने (त्यांच्याकडे जुनी किंमत नाही), काढून टाकलेली उत्पादने (त्यांच्याकडे नवीन किंमत नाही) आणि किंमतीतील बदल, काही असल्यास स्पष्टपणे पाहू शकता.

अशा सारणीतील ग्रँड बेरीजचा अर्थ नाही आणि ते टॅबवर अक्षम केले जाऊ शकतात कन्स्ट्रक्टर - ग्रँड बेरीज - पंक्ती आणि स्तंभांसाठी अक्षम करा (डिझाइन - एकूण एकूण).

जर किंमती बदलत असतील (परंतु मालाचे प्रमाण नाही!), तर त्यावर उजवे-क्लिक करून तयार केलेला सारांश अद्यतनित करणे पुरेसे आहे - रिफ्रेश.

साधक: हा दृष्टीकोन VLOOKUP पेक्षा मोठ्या सारण्यांसह वेगवान परिमाणाचा क्रम आहे. 

बाधक: तुम्हाला एकमेकांच्या खाली डेटा मॅन्युअली कॉपी करणे आणि किंमत सूचीच्या नावासह एक स्तंभ जोडणे आवश्यक आहे. जर टेबल्सचे आकार बदलले तर तुम्हाला सर्व काही पुन्हा करावे लागेल.

पद्धत 3: पॉवर क्वेरीसह टेबलची तुलना करणे

पॉवर क्वेरी हे मायक्रोसॉफ्ट एक्सेलसाठी एक विनामूल्य अॅड-इन आहे जे तुम्हाला जवळजवळ कोणत्याही स्रोतावरून डेटा एक्सेलमध्ये लोड करण्याची आणि नंतर या डेटाचे कोणत्याही इच्छित मार्गाने रूपांतर करण्यास अनुमती देते. एक्सेल 2016 मध्ये, हे अॅड-इन आधीच टॅबवर डीफॉल्टनुसार तयार केलेले आहे डेटा (डेटा), आणि एक्सेल 2010-2013 साठी तुम्हाला ते Microsoft वेबसाइटवरून स्वतंत्रपणे डाउनलोड करावे लागेल आणि ते स्थापित करावे लागेल – एक नवीन टॅब मिळवा उर्जा प्रश्न.

आमच्या किमतीच्या याद्या पॉवर क्वेरीमध्ये लोड करण्यापूर्वी, त्या प्रथम स्मार्ट टेबलमध्ये बदलल्या पाहिजेत. हे करण्यासाठी, डेटासह श्रेणी निवडा आणि कीबोर्डवरील संयोजन दाबा Ctrl+T किंवा रिबनवरील टॅब निवडा मुख्यपृष्ठ - सारणी म्हणून स्वरूपित करा (मुख्यपृष्ठ - सारणी म्हणून स्वरूपित). तयार केलेल्या सारण्यांची नावे टॅबवर दुरुस्त केली जाऊ शकतात रचनाकार (मी मानक सोडेन टेबल 1 и टेबल 2, जे डीफॉल्टनुसार प्राप्त केले जातात).

बटण वापरून Power Query मध्ये जुनी किंमत लोड करा टेबल/श्रेणीतून (टेबल/श्रेणीवरून) टॅब वरून डेटा (तारीख) किंवा टॅबवरून उर्जा प्रश्न (एक्सेलच्या आवृत्तीवर अवलंबून). लोड केल्यानंतर, आम्ही कमांडसह Power Query वरून Excel वर परत येऊ बंद करा आणि लोड करा - बंद करा आणि लोड करा… (बंद करा आणि लोड करा - बंद करा आणि लोड करा…):

... आणि दिसत असलेल्या विंडोमध्ये नंतर निवडा फक्त एक कनेक्शन तयार करा (केवळ कनेक्शन).

नवीन किंमत सूचीसह तीच पुनरावृत्ती करा. 

आता एक तिसरी क्वेरी बनवूया जी मागील दोनमधील डेटा एकत्र आणि तुलना करेल. हे करण्यासाठी, टॅबवरील Excel मध्ये निवडा डेटा - डेटा मिळवा - विनंत्या एकत्र करा - एकत्र करा (डेटा — डेटा मिळवा — क्वेरी विलीन करा — विलीन करा) किंवा बटण दाबा एकत्र (विलीन) टॅब उर्जा प्रश्न.

सामील होण्याच्या विंडोमध्ये, ड्रॉप-डाउन सूचींमध्ये आमची सारणी निवडा, त्यातील वस्तूंच्या नावांसह स्तंभ निवडा आणि तळाशी, सामील होण्याची पद्धत सेट करा – पूर्ण बाह्य (पूर्ण बाह्य):

वर क्लिक केल्यानंतर OK तीन स्तंभांची एक सारणी दिसली पाहिजे, जिथे तिसऱ्या स्तंभात तुम्हाला शीर्षलेखातील दुहेरी बाण वापरून नेस्टेड सारण्यांची सामग्री विस्तृत करणे आवश्यक आहे:

परिणामी, आम्हाला दोन्ही सारण्यांवरील डेटाचे विलीनीकरण मिळते:

अधिक समजण्याजोग्यांवर डबल-क्लिक करून हेडरमधील स्तंभांची नावे पुनर्नामित करणे चांगले आहे:

आणि आता सर्वात मनोरंजक. टॅबवर जा स्तंभ जोडा (स्तंभ जोडा) आणि बटणावर क्लिक करा सशर्त स्तंभ (सशर्त स्तंभ). आणि नंतर उघडलेल्या विंडोमध्ये, त्यांच्या संबंधित आउटपुट मूल्यांसह अनेक चाचणी अटी प्रविष्ट करा:

त्यावर क्लिक करणे बाकी आहे OK आणि त्याच बटणाचा वापर करून परिणामी अहवाल Excel वर अपलोड करा बंद करा आणि डाउनलोड करा (बंद करा आणि लोड करा) टॅब होम पेज (मुख्यपृष्ठ):

सौंदर्य

शिवाय, भविष्यात किंमत सूचींमध्ये कोणतेही बदल झाल्यास (ओळी जोडल्या किंवा हटविल्या गेल्या, किमती बदलल्या, इ.), तर ते फक्त कीबोर्ड शॉर्टकटसह आमच्या विनंत्या अपडेट करणे पुरेसे असेल. Ctrl+alt+F5 किंवा बटणाद्वारे सर्व रिफ्रेश करा (सर्व रिफ्रेश करा) टॅब डेटा (तारीख).

साधक: कदाचित सर्वात सुंदर आणि सोयीस्कर मार्ग. मोठ्या टेबलांसह हुशारीने कार्य करते. टेबल्सचा आकार बदलताना मॅन्युअल संपादनांची आवश्यकता नाही.

बाधक: पॉवर क्वेरी अॅड-इन (एक्सेल 2010-2013 मध्ये) किंवा एक्सेल 2016 स्थापित करणे आवश्यक आहे. स्त्रोत डेटामधील स्तंभांची नावे बदलली जाऊ नयेत, अन्यथा आम्हाला “तसे आणि असे स्तंभ सापडले नाहीत!” अशी त्रुटी येईल. क्वेरी अद्यतनित करण्याचा प्रयत्न करताना.

• पॉवर क्वेरी वापरून दिलेल्या फोल्डरमधील सर्व एक्सेल फाइल्समधून डेटा कसा गोळा करायचा
• एक्सेलमध्ये दोन सूचींमधील जुळण्या कसे शोधायचे
• डुप्लिकेटशिवाय दोन सूची एकत्र करणे