दो तालिकाओं की तुलना

हमारे पास दो टेबल हैं (उदाहरण के लिए, मूल्य सूची के पुराने और नए संस्करण), जिनकी हमें तुलना करने और अंतरों को जल्दी से खोजने की आवश्यकता है:

यह तुरंत स्पष्ट है कि नई मूल्य सूची में कुछ जोड़ा गया है (खजूर, लहसुन …), कुछ गायब हो गया है (ब्लैकबेरी, रास्पबेरी …), कुछ सामानों (अंजीर, खरबूजे …) के लिए कीमतें बदल गई हैं। आपको इन सभी परिवर्तनों को शीघ्रता से खोजने और प्रदर्शित करने की आवश्यकता है।

एक्सेल में किसी भी कार्य के लिए, लगभग हमेशा एक से अधिक समाधान होते हैं (आमतौर पर 4-5)। हमारी समस्या के लिए, कई अलग-अलग तरीकों का इस्तेमाल किया जा सकता है:

• समारोह VPR (VLOOKUP) - पुराने में नई मूल्य सूची से उत्पाद के नाम देखें और पुराने मूल्य को नए के बगल में प्रदर्शित करें, और फिर अंतरों को पकड़ें
• दो सूचियों को एक में मिलाएं और फिर उसके आधार पर एक पिवट टेबल बनाएं, जहां अंतर स्पष्ट रूप से दिखाई देगा
• Excel के लिए Power Query ऐड-इन का उपयोग करें

आइए उन सभी को क्रम में लें।

विधि 1. VLOOKUP फ़ंक्शन के साथ तालिकाओं की तुलना करना

यदि आप इस अद्भुत विशेषता से पूरी तरह अपरिचित हैं, तो पहले यहां देखें और इस पर एक वीडियो ट्यूटोरियल पढ़ें या देखें - अपने आप को जीवन के कुछ साल बचाएं।

आमतौर पर, इस फ़ंक्शन का उपयोग कुछ सामान्य पैरामीटर से मिलान करके डेटा को एक तालिका से दूसरी तालिका में खींचने के लिए किया जाता है। इस मामले में, हम इसका उपयोग पुरानी कीमतों को नई कीमत में धकेलने के लिए करेंगे:

वे उत्पाद, जिनके विरुद्ध #N/A त्रुटि निकली, पुरानी सूची में नहीं हैं, अर्थात जोड़े गए थे। कीमतों में बदलाव भी साफ नजर आ रहा है।

फ़ायदे यह विधि: सरल और स्पष्ट, "शैली का क्लासिक", जैसा कि वे कहते हैं। एक्सेल के किसी भी संस्करण में काम करता है।

नुकसान वहाँ भी है। नई मूल्य सूची में जोड़े गए उत्पादों की खोज करने के लिए, आपको विपरीत दिशा में वही प्रक्रिया करनी होगी, यानी VLOOKUP की मदद से नई कीमतों को पुराने मूल्य तक खींचना होगा। यदि कल तालिकाओं के आकार बदलते हैं, तो सूत्रों को समायोजित करना होगा। खैर, और वास्तव में बड़ी तालिकाओं (> 100 हजार पंक्तियों) पर, यह सारी खुशी शालीनता से धीमी हो जाएगी।

विधि 2: पिवट का उपयोग करके तालिकाओं की तुलना करना

आइए मूल्य सूची के नाम के साथ एक कॉलम जोड़ते हुए हमारी तालिकाओं को एक के नीचे एक कॉपी करते हैं, ताकि बाद में आप समझ सकें कि कौन सी सूची से कौन सी पंक्ति:

अब, बनाई गई तालिका के आधार पर, हम के माध्यम से एक सारांश तैयार करेंगे सम्मिलित करें - पिवोटटेबल (सम्मिलित करें - पिवट टेबल). चलो एक मैदान फेंकते हैं एस्ट्रो मॉल लाइनों के क्षेत्र के लिए, क्षेत्र मूल्य स्तंभ क्षेत्र और क्षेत्र के लिए Цएना सीमा में:

जैसा कि आप देख सकते हैं, पिवट तालिका स्वचालित रूप से पुरानी और नई मूल्य सूचियों (कोई दोहराव नहीं!) से सभी उत्पादों की एक सामान्य सूची उत्पन्न करेगी और उत्पादों को वर्णानुक्रम में क्रमबद्ध करेगी। आप जोड़े गए उत्पादों (उनकी पुरानी कीमत नहीं है), हटाए गए उत्पाद (उनके पास नई कीमत नहीं है) और मूल्य परिवर्तन, यदि कोई हो, स्पष्ट रूप से देख सकते हैं।

ऐसी तालिका में बड़े योग का कोई मतलब नहीं है, और उन्हें टैब पर अक्षम किया जा सकता है कंस्ट्रक्टर - कुल योग - पंक्तियों और स्तंभों के लिए अक्षम करें (डिजाइन - ग्रैंड टोटल).

यदि कीमतें बदलती हैं (लेकिन माल की मात्रा नहीं!), तो बस उस पर राइट-क्लिक करके बनाए गए सारांश को अपडेट करना पर्याप्त है - ताज़ा करना.

फ़ायदे: यह दृष्टिकोण VLOOKUP की तुलना में बड़ी तालिकाओं के साथ तेजी से परिमाण का क्रम है। 

नुकसान: आपको मैन्युअल रूप से डेटा को एक दूसरे के नीचे कॉपी करना होगा और मूल्य सूची के नाम के साथ एक कॉलम जोड़ना होगा। यदि तालिकाओं के आकार बदलते हैं, तो आपको सब कुछ फिर से करना होगा।

विधि 3: पावर क्वेरी के साथ तालिकाओं की तुलना करना

पावर क्वेरी माइक्रोसॉफ्ट एक्सेल के लिए एक मुफ्त ऐड-इन है जो आपको लगभग किसी भी स्रोत से एक्सेल में डेटा लोड करने और फिर इस डेटा को किसी भी वांछित तरीके से बदलने की अनुमति देता है। एक्सेल 2016 में, यह ऐड-इन पहले से ही टैब पर डिफ़ॉल्ट रूप से बनाया गया है जानकारी (जानकारी), और एक्सेल 2010-2013 के लिए आपको इसे माइक्रोसॉफ्ट वेबसाइट से अलग से डाउनलोड करना होगा और इसे इंस्टॉल करना होगा - एक नया टैब प्राप्त करें पावर क्वेरी.

हमारी मूल्य सूचियों को Power Query में लोड करने से पहले, उन्हें पहले स्मार्ट टेबल में बदलना होगा। ऐसा करने के लिए, डेटा के साथ श्रेणी का चयन करें और कीबोर्ड पर संयोजन दबाएं कंट्रोल+T या रिबन पर टैब चुनें होम - तालिका के रूप में प्रारूपित करें (होम - तालिका के रूप में प्रारूपित करें). बनाई गई तालिकाओं के नाम टैब पर ठीक किए जा सकते हैं निर्माता (मैं मानक छोड़ दूंगा टेबल 1 и टेबल 2, जो डिफ़ॉल्ट रूप से प्राप्त होते हैं)।

बटन का उपयोग करके पुराने मूल्य को Power Query में लोड करें टेबल/रेंज से (टेबल/रेंज से) टैब से जानकारी (तारीख) या टैब से पावर क्वेरी (एक्सेल के संस्करण के आधार पर)। लोड करने के बाद, हम कमांड के साथ पावर क्वेरी से एक्सेल में वापस आ जाएंगे बंद करें और लोड करें - बंद करें और लोड करें ... (बंद करें और लोड करें - बंद करें और लोड करें ...):

... और दिखाई देने वाली विंडो में चुनें बस एक कनेक्शन बनाएं (केवल कनेक्शन).

नई मूल्य सूची के साथ भी ऐसा ही दोहराएं। 

अब एक तीसरी क्वेरी बनाते हैं जो पिछले दो के डेटा को मिलाएगी और तुलना करेगी। ऐसा करने के लिए, टैब पर एक्सेल में चयन करें डेटा - डेटा प्राप्त करें - अनुरोधों को मिलाएं - गठबंधन करें (डेटा - डेटा प्राप्त करें - क्वेरी मर्ज करें - मर्ज करें) या बटन दबाएं मिलाना (मर्ज) टैब पावर क्वेरी.

जॉइन विंडो में, ड्रॉप-डाउन सूची में हमारे टेबल का चयन करें, उनमें माल के नाम वाले कॉलम का चयन करें, और सबसे नीचे, जॉइन विधि सेट करें - पूर्ण बाहरी (पूर्ण बाहरी):

क्लिक करने के बाद OK तीन कॉलम की एक तालिका दिखाई देनी चाहिए, जहां तीसरे कॉलम में आपको हेडर में डबल एरो का उपयोग करके नेस्टेड टेबल की सामग्री का विस्तार करने की आवश्यकता है:

परिणामस्वरूप, हमें दोनों तालिकाओं से डेटा का विलय मिलता है:

बेशक, अधिक समझने योग्य लोगों पर डबल-क्लिक करके हेडर में कॉलम नामों का नाम बदलना बेहतर है:

और अब सबसे दिलचस्प। टैब पर जाएं कॉलम जोड़ें (कॉलम जोड़ें) और बटन पर क्लिक करें सशर्त स्तंभ (सशर्त कॉलम). और फिर खुलने वाली विंडो में, उनके संगत आउटपुट मानों के साथ कई परीक्षण शर्तें दर्ज करें:

क्लिक करना बाकी है OK और परिणामी रिपोर्ट को उसी बटन का उपयोग करके एक्सेल में अपलोड करें बंद करें और डाउनलोड करें (बंद करें और लोड करें) टैब होम (घर):

सुंदरता।

इसके अलावा, यदि भविष्य में मूल्य सूची में कोई परिवर्तन होता है (लाइनें जोड़ी जाती हैं या हटा दी जाती हैं, कीमतें बदल जाती हैं, आदि), तो यह हमारे अनुरोधों को केवल एक कीबोर्ड शॉर्टकट के साथ अपडेट करने के लिए पर्याप्त होगा कंट्रोल+ऑल्ट+F5 या बटन से सभी को रीफ्रेश करें (सभी को रीफ्रेश करें) टैब जानकारी (तारीख).

फ़ायदे: शायद सभी का सबसे सुंदर और सुविधाजनक तरीका। बड़ी तालिकाओं के साथ चतुराई से काम करता है। तालिकाओं का आकार बदलते समय मैन्युअल संपादन की आवश्यकता नहीं होती है।

नुकसान: पावर क्वेरी ऐड-इन (एक्सेल 2010-2013 में) या एक्सेल 2016 को स्थापित करने की आवश्यकता है। स्रोत डेटा में कॉलम नाम नहीं बदला जाना चाहिए, अन्यथा हमें त्रुटि मिलेगी "कॉलम जैसे और ऐसा नहीं मिला!" क्वेरी को अपडेट करने का प्रयास करते समय।

• Power Query का उपयोग करके किसी दिए गए फ़ोल्डर में सभी Excel फ़ाइलों से डेटा कैसे एकत्रित करें
• एक्सेल में दो सूचियों के बीच मिलान कैसे खोजें
• डुप्लीकेट के बिना दो सूचियों को मर्ज करना