दुई तालिकाहरू तुलना गर्दै

हामीसँग दुईवटा तालिकाहरू छन् (उदाहरणका लागि, मूल्य सूचीको पुरानो र नयाँ संस्करणहरू), जुन हामीले तुलना गर्न र छिटो भिन्नताहरू फेला पार्न आवश्यक छ:

यो तुरुन्तै स्पष्ट छ कि नयाँ मूल्य सूचीमा केहि थपिएको छ (मिति, लसुन ...), केहि गायब भएको छ (ब्ल्याकबेरी, रास्पबेरी ...), केहि सामानहरु को मूल्यहरु (अंजीर, तरबूज ...) को लागी परिवर्तन भएको छ। तपाईंले यी सबै परिवर्तनहरू छिटो फेला पार्न र प्रदर्शन गर्न आवश्यक छ।

Excel मा कुनै पनि कार्यको लागि, त्यहाँ लगभग सधैं एक भन्दा बढी समाधान (सामान्यतया 4-5) छ। हाम्रो समस्याको लागि, धेरै फरक दृष्टिकोणहरू प्रयोग गर्न सकिन्छ:

• समारोह VPR (VLOOKUP) - पुरानोमा नयाँ मूल्य सूचीबाट उत्पादन नामहरू खोज्नुहोस् र नयाँको छेउमा पुरानो मूल्य प्रदर्शन गर्नुहोस्, र त्यसपछि भिन्नताहरू समात्नुहोस्।
• दुई सूचीहरू एकमा मर्ज गर्नुहोस् र त्यसपछि त्यसमा आधारित पिभोट तालिका बनाउनुहोस्, जहाँ भिन्नताहरू स्पष्ट रूपमा देखिनेछन्।
• एक्सेलको लागि पावर क्वेरी एड-इन प्रयोग गर्नुहोस्

ती सबैलाई क्रमबद्ध गरौं।

विधि 1. VLOOKUP प्रकार्यसँग तालिकाहरू तुलना गर्दै

यदि तपाईं यस अद्भुत सुविधासँग पूर्णतया अपरिचित हुनुहुन्छ भने, पहिले यहाँ हेर्नुहोस् र यसमा भिडियो ट्यूटोरियल पढ्नुहोस् वा हेर्नुहोस् - आफ्नो जीवनको दुई वर्ष बचाउनुहोस्।

सामान्यतया, यो प्रकार्य केहि सामान्य प्यारामिटर मिलाएर एक तालिकाबाट अर्कोमा डाटा तान्न प्रयोग गरिन्छ। यस अवस्थामा, हामी यसलाई पुरानो मूल्यहरूलाई नयाँ मूल्यमा पुश गर्न प्रयोग गर्नेछौं:

ती उत्पादनहरू, जसको विरुद्धमा #N/A त्रुटि देखा पर्‍यो, पुरानो सूचीमा छैनन्, अर्थात् थपिएका थिए। मूल्य परिवर्तनहरू पनि स्पष्ट रूपमा देखिने छन्।

विशेषज्ञहरूले यो विधि: सरल र स्पष्ट, "विधाको क्लासिक", तिनीहरू भन्छन्। Excel को कुनै पनि संस्करण मा काम गर्दछ।

विपक्ष त्यहाँ पनि छ। नयाँ मूल्य सूचीमा थपिएका उत्पादनहरू खोज्नको लागि, तपाईंले उल्टो दिशामा उही प्रक्रिया गर्नुपर्छ, अर्थात् VLOOKUP को मद्दतले पुरानो मूल्यमा नयाँ मूल्यहरू तान्नुहोस्। यदि तालिकाको आकार भोलि परिवर्तन भयो भने, सूत्रहरू समायोजन गर्नुपर्नेछ। ठीक छ, र साँच्चै ठूला टेबलहरूमा (> 100 हजार पङ्क्तिहरू), यो सबै खुशी सभ्य रूपमा सुस्त हुनेछ।

विधि २: पिभोट प्रयोग गरी तालिकाहरू तुलना गर्दै

मूल्य सूचीको नामको साथ एउटा स्तम्भ थप्दै, हाम्रो तालिकाहरू एक अर्को मुनि प्रतिलिपि गरौं, ताकि पछि कुन पङ्क्तिको सूचीबाट तपाईंले बुझ्न सक्नुहुन्छ:

अब, सिर्जना गरिएको तालिकाको आधारमा, हामी मार्फत सारांश सिर्जना गर्नेछौं घुसाउनुहोस् - PivotTable (घुसाउनुहोस् - पिभोट तालिका)। मैदान फालौं उत्पादन रेखाहरूको क्षेत्र, क्षेत्र मूल्य स्तम्भ क्षेत्र र फिल्डमा Цena दायरामा:

तपाईले देख्न सक्नुहुने रूपमा, पिभोट तालिकाले पुरानो र नयाँ मूल्य सूचीहरूबाट (कुनै दोहोरिने छैन!) सबै उत्पादनहरूको सामान्य सूची स्वतः उत्पन्न गर्नेछ र उत्पादनहरूलाई वर्णमाला अनुसार क्रमबद्ध गर्नेछ। तपाईंले थपिएका उत्पादनहरू (तिनीहरूसँग पुरानो मूल्य छैन), हटाइएका उत्पादनहरू (तिनीहरूसँग नयाँ मूल्य छैन) र मूल्य परिवर्तनहरू, यदि कुनै भएमा स्पष्ट रूपमा देख्न सक्नुहुन्छ।

यस्तो तालिकामा ठूलो योग अर्थ छैन, र तिनीहरू ट्याबमा असक्षम गर्न सकिन्छ कन्स्ट्रक्टर - ठूलो योग - पङ्क्ति र स्तम्भहरूको लागि असक्षम गर्नुहोस् (डिजाइन - ठूलो कुल).

यदि मूल्यहरू परिवर्तन भएमा (तर सामानको मात्रा होइन!), त्यसपछि यसलाई दायाँ क्लिक गरेर सिर्जना गरिएको सारांश अद्यावधिक गर्न पर्याप्त छ - ताजा.

विशेषज्ञहरूले: यो दृष्टिकोण VLOOKUP भन्दा ठूला टेबलहरूसँग छिटो परिमाणको अर्डर हो। 

विपक्ष: तपाईंले म्यानुअल रूपमा डेटा एकअर्का अन्तर्गत प्रतिलिपि गर्न र मूल्य सूचीको नामको साथ स्तम्भ थप्न आवश्यक छ। यदि तालिकाको आकार परिवर्तन हुन्छ भने, तपाईंले फेरि सबै कुरा गर्नु पर्छ।

विधि ३: पावर क्वेरीसँग तालिकाहरू तुलना गर्दै

Power Query Microsoft Excel को लागि नि:शुल्क एड-इन हो जसले तपाईंलाई लगभग कुनै पनि स्रोतबाट Excel मा डेटा लोड गर्न र त्यसपछि यो डेटालाई कुनै पनि इच्छित तरिकामा रूपान्तरण गर्न अनुमति दिन्छ। एक्सेल 2016 मा, यो एड-इन पहिले नै ट्याबमा पूर्वनिर्धारित रूपमा निर्मित छ तथ्याङ्क (डेटा), र एक्सेल 2010-2013 को लागी तपाईले यसलाई माइक्रोसफ्ट वेबसाइटबाट अलग डाउनलोड गर्न र स्थापना गर्न आवश्यक छ - नयाँ ट्याब प्राप्त गर्नुहोस्। पावर क्वेरी.

हाम्रो मूल्य सूचीहरू Power Query मा लोड गर्नु अघि, तिनीहरूलाई पहिले स्मार्ट तालिकाहरूमा रूपान्तरण गर्नुपर्छ। यो गर्नका लागि, डेटाको साथ दायरा चयन गर्नुहोस् र किबोर्डमा संयोजन थिच्नुहोस् Ctrl+T वा रिबनमा रहेको ट्याब चयन गर्नुहोस् गृह - तालिकाको रूपमा ढाँचा (घर - तालिकाको रूपमा ढाँचा)। सिर्जना गरिएका तालिकाहरूको नामहरू ट्याबमा सच्याउन सकिन्छ कन्स्ट्रक्टर (म मानक छोड्छु तालिका 1 и तालिका 2, जुन पूर्वनिर्धारित रूपमा प्राप्त हुन्छन्)।

बटन प्रयोग गरेर पावर क्वेरीमा पुरानो मूल्य लोड गर्नुहोस् तालिका/दायराबाट (तालिका/दायराबाट) ट्याबबाट तथ्याङ्क (मिति) वा ट्याबबाट पावर क्वेरी (एक्सेलको संस्करणमा निर्भर गर्दै)। लोड गरिसकेपछि, हामी आदेशको साथ Power Query बाट Excel मा फर्किनेछौं बन्द गर्नुहोस् र लोड गर्नुहोस् - बन्द गर्नुहोस् र लोड गर्नुहोस् ... (बंद गर्नुहोस् र लोड गर्नुहोस् - बन्द गर्नुहोस् र लोड गर्नुहोस् ...):

... र देखा पर्ने विन्डोमा त्यसपछि चयन गर्नुहोस् बस एक जडान सिर्जना गर्नुहोस् (कनेक्सन मात्र).

नयाँ मूल्य सूचीको साथ उही दोहोर्याउनुहोस्। 

अब तेस्रो क्वेरी सिर्जना गरौं जसले अघिल्लो दुईबाट डेटा संयोजन र तुलना गर्नेछ। यो गर्नको लागि, ट्याबमा Excel मा चयन गर्नुहोस् डाटा - डाटा प्राप्त गर्नुहोस् - अनुरोधहरू संयोजन गर्नुहोस् - संयोजन गर्नुहोस् (डेटा - डाटा प्राप्त गर्नुहोस् - प्रश्नहरू मर्ज गर्नुहोस् - मर्ज) वा बटन थिच्नुहोस् जोड्नुहोस् (मर्ज) ट्याब पावर क्वेरी.

जोडिने विन्डोमा, ड्रप-डाउन सूचीहरूमा हाम्रा तालिकाहरू चयन गर्नुहोस्, तिनीहरूमा रहेका सामानहरूको नामहरू भएका स्तम्भहरू चयन गर्नुहोस्, र तल, जोडिने विधि सेट गर्नुहोस् - पूर्ण बाह्य (पूर्ण बाहिरी):

क्लिक गर्नु पछि OK तीन स्तम्भहरूको तालिका देखा पर्दछ, जहाँ तेस्रो स्तम्भमा तपाईंले हेडरमा डबल एरो प्रयोग गरेर नेस्टेड तालिकाहरूको सामग्री विस्तार गर्न आवश्यक छ:

नतिजाको रूपमा, हामी दुबै तालिकाहरूबाट डेटाको मर्ज प्राप्त गर्छौं:

यो राम्रो छ, निस्सन्देह, हेडरमा स्तम्भ नामहरू पुन: नामाकरण गर्न थप बुझ्न योग्यहरूमा डबल-क्लिक गरेर:

र अब सबैभन्दा रोचक। ट्याबमा जानुहोस् स्तम्भ थप्नुहोस् (स्तम्भ थप्नुहोस्) र बटनमा क्लिक गर्नुहोस् सशर्त स्तम्भ (सशर्त स्तम्भ)। र त्यसपछि खुल्ने विन्डोमा, तिनीहरूको सम्बन्धित आउटपुट मानहरूसँग धेरै परीक्षण सर्तहरू प्रविष्ट गर्नुहोस्:

यसमा क्लिक गर्न बाँकी छ OK र उही बटन प्रयोग गरेर एक्सेलमा परिणाम रिपोर्ट अपलोड गर्नुहोस् बन्द गर्नुहोस् र डाउनलोड गर्नुहोस् (बन्द र लोड) ट्याब गृहपृष्ठ (घर):

सुन्दरता।

यसबाहेक, यदि भविष्यमा मूल्य सूचीहरूमा कुनै परिवर्तनहरू देखा पर्छन् (लाइनहरू थपिएका वा मेटाइएका छन्, मूल्यहरू परिवर्तन, आदि), त्यसपछि यो केवल किबोर्ड सर्टकटको साथ हाम्रा अनुरोधहरू अद्यावधिक गर्न पर्याप्त हुनेछ। Ctrl+alt+F5 वा बटन द्वारा सबै ताजा गर्नुहोस् (सबैलाई ताजा गर्नुहोस्) ट्याब तथ्याङ्क (मिति).

विशेषज्ञहरूले: सायद सबै भन्दा सुन्दर र सुविधाजनक तरिका। ठूला टेबलहरूसँग स्मार्ट रूपमा काम गर्दछ। तालिका रिसाइज गर्दा म्यानुअल सम्पादन आवश्यक पर्दैन।

विपक्ष: पावर क्वेरी एड-इन (एक्सेल 2010-2013 मा) वा एक्सेल 2016 स्थापना गर्न आवश्यक छ। स्रोत डेटामा स्तम्भ नामहरू परिवर्तन गर्नु हुँदैन, अन्यथा हामीले त्रुटि प्राप्त गर्नेछौं "स्तम्भ यस्तो र यस्तो फेला परेन!" प्रश्न अद्यावधिक गर्न प्रयास गर्दा।

• Power Query प्रयोग गरेर दिइएको फोल्डरमा सबै एक्सेल फाइलहरूबाट डाटा कसरी सङ्कलन गर्ने
• एक्सेलमा दुई सूचीहरू बीच मिलानहरू कसरी फेला पार्ने
• डुप्लिकेट बिना दुई सूचीहरू मर्ज गर्दै