एक्सेल में संबंधित सारणी: विस्तृत निर्देश

एक्सेल में कुछ कार्यों को करने पर कभी-कभी कई तालिकाओं से निपटना पड़ता है जो एक दूसरे से भी संबंधित हैं। यही है, एक टेबल से डेटा दूसरों को कड़ा कर दिया जाता है और सभी संबंधित तालिकाओं में मूल्यों को बदला जाने पर पुनर्मूल्यांकन किया जाता है।

संबंधित सारणी बड़ी मात्रा में जानकारी को संभालने के लिए उपयोग करने के लिए बहुत सुविधाजनक हैं। सभी जानकारी को एक टेबल में रखें, इसके अलावा, अगर यह सजातीय नहीं है, बहुत सुविधाजनक नहीं है। ऐसी वस्तुओं के साथ काम करना और उनके लिए खोज करना मुश्किल है। निर्दिष्ट समस्या सिर्फ संबंधित तालिकाओं को खत्म करने के लिए डिज़ाइन की गई है, जिसके बीच वितरित की गई जानकारी, लेकिन साथ ही साथ जुड़े हुए हैं। संबंधित तालिकाएं न केवल एक शीट या एक पुस्तक के भीतर हो सकती हैं, बल्कि अलग-अलग पुस्तकों (फ़ाइलों) में भी स्थित हो सकती हैं। अभ्यास में पिछले दो विकल्पों का अक्सर उपयोग किया जाता है, क्योंकि इस तकनीक का उद्देश्य डेटा के संचय से दूर होने के लिए है, और एक पृष्ठ पर उनकी नोडिंग मूल रूप से हल नहीं होती है। आइए सीखें कि कैसे बनाना है और इस प्रकार के डेटा प्रबंधन के साथ कैसे काम करना है।

संबंधित तालिकाएँ बनाना

सबसे पहले, आइए प्रश्न पर ध्यान दें, किस तरह से विभिन्न तालिकाओं के बीच संबंध बनाना संभव है।

विधि 1: डायरेक्ट बाइंडिंग टेबल फॉर्मूला

डेटा को बांधने का सबसे आसान तरीका सूत्रों का उपयोग होता है जिसमें अन्य तालिकाओं के संदर्भ होते हैं। इसे प्रत्यक्ष बाध्यकारी कहा जाता है। यह विधि अंतर्ज्ञानी है, क्योंकि जब यह लगभग एक तालिका सरणी में डेटा के संदर्भ बनाने की तरह इसे बांधती है।

चलो देखते हैं कि आप सीधे बाध्यकारी द्वारा संचार कैसे बना सकते हैं। हमारे पास दो चादरों पर दो टेबल हैं। एक ही तालिका पर, वेतन को एक गुणांक के लिए कर्मचारी दरों को गुणा करके सूत्र का उपयोग करके गणना की जाती है।

दूसरी शीट पर एक टेबल रेंज है जिसमें उनके वेतन वाले कर्मचारियों की एक सूची है। दोनों मामलों में कर्मचारियों की सूची एक आदेश में प्रस्तुत की जाती है।

दूसरी शीट से दांव पर उस डेटा को पहले की संबंधित कोशिकाओं में कसने के लिए आवश्यक है।

1. पहली शीट पर, हम "शर्त" कॉलम के पहले सेल आवंटित करते हैं। हमने इसे "=" में रखा। इसके बाद, "शीट 2" लेबल पर क्लिक करें, जिसे स्टेटस बार पर एक्सेल इंटरफेस के बाएं हिस्से में रखा गया है।



2. दस्तावेज़ के दूसरे क्षेत्र में एक आंदोलन है। "शर्त" कॉलम में पहले सेल पर क्लिक करें। फिर उस सेल में डेटा दर्ज करने के लिए कीबोर्ड पर दर्ज करें बटन पर क्लिक करें जिसमें "समान" चिह्न पहले स्थापित किया गया था।



3. फिर पहली शीट में एक स्वचालित संक्रमण होता है। जैसा कि हम देख सकते हैं, दूसरी तालिका से पहले कर्मचारी का मूल्य संबंधित सेल में खींचा जाता है। एक शर्त वाले सेल पर कर्सर स्थापित करके, हम देखते हैं कि स्क्रीन पर डेटा प्रदर्शित करने के लिए सामान्य सूत्र का उपयोग किया जाता है। लेकिन सेल के निर्देशांक के सामने, जहां से डेटा आउटपुट है, वहां एक अभिव्यक्ति "LIST2!" है, जो दस्तावेज़ क्षेत्र का नाम इंगित करता है जहां वे स्थित हैं। हमारे मामले में सामान्य सूत्र इस तरह दिखता है:

   = सूची 2! B2



4. अब आपको उद्यम के अन्य सभी कर्मचारियों की दरों के बारे में डेटा स्थानांतरित करने की आवश्यकता है। बेशक, यह उसी तरह किया जा सकता है कि हमने पहले कर्मचारी के लिए कार्य को पूरा किया है, लेकिन यह मानते हुए कि दोनों कर्मचारी सूचियां एक ही क्रम में स्थित हैं, कार्य को अपने निर्णय से काफी सरल और तेज किया जा सकता है। यह केवल सूत्र को नीचे की सीमा तक कॉपी करके किया जा सकता है। इस तथ्य के कारण कि एक्सेल के संदर्भ रिश्तेदार हैं, अपने मूल्यों की प्रतिलिपि बनाते समय, मान शिफ्ट को स्थानांतरित कर दिया जाता है जिसे हमें चाहिए। प्रतिलिपि प्रक्रिया स्वयं भरने वाले मार्कर का उपयोग करके बनाई जा सकती है।

   इसलिए, हम कर्सर को सूत्र के साथ तत्व के निचले दाएं क्षेत्र में डालते हैं। उसके बाद, कर्सर को ब्लैक क्रॉस के रूप में भरने वाले मार्कर में परिवर्तित करना होगा। हम बाएं माउस बटन का क्लैंप करते हैं और कर्सर को कॉल्सर को कॉलस की संख्या में खींचते हैं।



5. शीट 2 पर समान कॉलम से सभी डेटा को एक शीट पर एक तालिका में खींचा गया था। जब कोई शीट 2 पर डेटा बदलता है, तो वे पहले ही बदल जाएंगे।

विधि 2: ऑपरेटर सूचकांक के ब्लंडरिंग का उपयोग - खोज

लेकिन यदि तालिका सरणी में कर्मचारियों की सूची उसी क्रम में स्थित नहीं है? इस मामले में, जैसा कि पहले बताया गया था, विकल्पों में से एक यह है कि उन कोशिकाओं में से प्रत्येक के बीच संबंध स्थापित करना जो मैन्युअल रूप से जुड़े होना चाहिए। लेकिन यह छोटी तालिकाओं को छोड़कर उपयुक्त है। भारी सीमाओं के लिए, इस विकल्प को कार्यान्वयन पर बहुत समय लगता है, और सबसे खराब - व्यावहारिक रूप से यह आमतौर पर अवास्तविक होगा। लेकिन ऑपरेटर सूचकांक - खोज के एक समूह का उपयोग करके इस समस्या को हल किया जा सकता है। चलो देखते हैं कि तालिकाओं में डेटा को कैसे किया जा सकता है जिसके बारे में वार्तालाप पिछली विधि में था।

1. हम "शर्त" कॉलम के पहले तत्व को हाइलाइट करते हैं। "फ़ंक्शन" आइकन पर क्लिक करके फ़ंक्शंस विज़ार्ड पर जाएं।



2. समूह "लिंक और सरणी" में कार्यों के विज़ार्ड में हम "इंडेक्स" नाम को ढूंढते और आवंटित करते हैं।



3. इस ऑपरेटर के दो रूप हैं: सरणी और संदर्भ के साथ काम करने के लिए एक रूप। हमारे मामले में, पहला विकल्प आवश्यक है, इसलिए खुलने वाली अगली फॉर्म चयन विंडो में, इसे चुनें और "ओके" बटन पर क्लिक करें।



4. ऑपरेटर के तर्क सूचकांक चलना शुरू हो जाता है। निर्दिष्ट फ़ंक्शन का कार्य निर्दिष्ट संख्या के अनुरूप चयनित सीमा में स्थित मान का आउटपुट है। सामान्य फॉर्मूला ऑपरेटर इंडेक्स जैसे:

   = सूचकांक (सरणी; NUMBER_NAME; [NUMBER_STOLBITS])

   "सरणी" एक तर्क है जिसमें सीमा की सीमा है, जिससे हम निर्दिष्ट पंक्ति की संख्या से जानकारी निकालेंगे।

   "पंक्ति संख्या" एक तर्क है जो इस लाइन की संख्या है। यह जानना महत्वपूर्ण है कि लाइन नंबर को पूरे दस्तावेज़ के सापेक्ष नहीं किया जाना चाहिए, बल्कि केवल आवंटित सरणी के सापेक्ष निर्दिष्ट किया जाना चाहिए।

   "कॉलम की संख्या" एक तर्क है जो वैकल्पिक है। हमारे कार्य के विशेष रूप से हल करने के लिए, हम इसका उपयोग नहीं करेंगे, और इसलिए इसे अलग से वर्णन करना आवश्यक नहीं है।

   हमने कर्सर को "सरणी" फ़ील्ड में रखा। उसके बाद, शीट 2 पर जाएं और बाएं माउस बटन दबाए रखें, "दर" कॉलम की संपूर्ण सामग्री का चयन करें।



5. ऑपरेटर की विंडो में निर्देशांक प्रदर्शित होने के बाद, हम कर्सर को "पंक्ति संख्या" फ़ील्ड में डालते हैं। हम खोज ऑपरेटर का उपयोग करके इस तर्क को वापस ले लेंगे। इसलिए, एक त्रिभुज पर क्लिक करें जो फ़ंक्शन स्ट्रिंग के बाईं ओर स्थित है। नए इस्तेमाल किए गए ऑपरेटरों की एक सूची खुलती है। यदि आपको उनके बीच "खोज कंपनी" नाम मिलती है, तो आप उस पर क्लिक कर सकते हैं। विपरीत मामले में, सूची के नवीनतम बिंदु पर क्लिक करें - "अन्य कार्य ..."।



6. मानक विंडो विज़ार्ड विंडो शुरू होती है। एक ही समूह "लिंक और सरणी" में इसे जाएं। इस बार सूची में, आइटम "खोज कंपनी" का चयन करें। "ओके" बटन पर एक क्लिक करें।



7. खोज ऑपरेटर के तर्कों के तर्कों का सक्रियण किया जाता है। निर्दिष्ट फ़ंक्शन को अपने नाम से एक विशिष्ट सरणी में मान संख्या को आउटपुट करने के लिए डिज़ाइन किया गया है। यह इस सुविधा के लिए धन्यवाद है कि हम फ़ंक्शन फ़ंक्शन के लिए किसी विशिष्ट मान की स्ट्रिंग की संख्या की गणना करते हैं। खोज बोर्ड का सिंटैक्स प्रस्तुत किया गया है:

   = खोज बोर्ड (SEARCH_NAME; VIEWING__NASSIVE; [type_station])

   "वांछित" एक तर्क है जिसमें तीसरे पक्ष की सीमा के सेल का नाम या पता शामिल है जिसमें यह स्थित है। यह लक्ष्य सीमा में इस नाम की स्थिति है और इसकी गणना की जानी चाहिए। हमारे मामले में, पहले तर्क की भूमिका को शीट 1 पर कोशिकाओं को संदर्भित किया जाएगा, जिसमें कर्मचारी स्थित हैं।

   "लचीला सरणी" एक तर्क है, जो एक सरणी का संदर्भ है, जो इसकी स्थिति निर्धारित करने के लिए निर्दिष्ट मूल्य की खोज करता है। शीट 2 पर "नाम" कॉलम के पते को निष्पादित करने के लिए हमारे पास यह भूमिका होगी।

   "तुलना का प्रकार" - एक तर्क जो वैकल्पिक है, लेकिन, पिछले ऑपरेटर के विपरीत, इस वैकल्पिक तर्क की आवश्यकता होगी। यह इंगित करता है कि ऑपरेटर से कैसे मेल खाना है, एक सरणी के साथ वांछित मूल्य है। इस तर्क में तीन मूल्यों में से एक हो सकता है: -1; 0; 1. विकृत सरणी के लिए, "0" विकल्प का चयन करें। यह विकल्प हमारे मामले के लिए उपयुक्त है।

   तो, तर्क विंडो फ़ील्ड भरने के लिए आगे बढ़ें। हमने कर्सर को "धुंधला मूल्य" फ़ील्ड में रखा है, शीट 1 पर पहले सेल "नाम" कॉलम पर क्लिक करें।



8. निर्देशांक प्रदर्शित होने के बाद, कर्सर को "लिस्टिंग बड़े" फ़ील्ड में सेट करें और "शीट 2" लेबल पर जाएं, जो स्टेटस बार के ऊपर एक्सेल विंडो के नीचे स्थित है। बाएं माउस बटन को क्लेमेंट करें और कर्सर को "नाम" कॉलम की सभी कक्षों को हाइलाइट करें।



9. उनके निर्देशांक "लिस्टिंग मैनिविव" फ़ील्ड में प्रदर्शित होने के बाद, "मैपिंग टाइप" फ़ील्ड पर जाएं और कीबोर्ड से "0" सेट करें। उसके बाद, हम फिर से "सरणी के माध्यम से देख रहे" क्षेत्र में लौट आए। तथ्य यह है कि हम फॉर्मूला की प्रतिलिपि बना देंगे, जैसा कि हमने पिछली विधि में किया था। पते की एक शिफ्ट होगी, लेकिन यहां सरणी के निर्देशांक को हमें सुरक्षित करने की आवश्यकता है। उसे शिफ्ट नहीं करना चाहिए। हम कर्सर के साथ निर्देशांक को हाइलाइट करते हैं और F4 फ़ंक्शन कुंजी पर क्लिक करते हैं। जैसा कि आप देख सकते हैं, डॉलर का संकेत निर्देशांक से पहले दिखाई दिया, जिसका अर्थ है कि रिश्तेदार से संदर्भ पूर्ण हो गया है। फिर "ओके" बटन पर क्लिक करें।



10. परिणाम "शर्त" कॉलम के पहले सेल में प्रदर्शित होता है। लेकिन प्रतिलिपि से पहले, हमें किसी अन्य क्षेत्र को ठीक करने की आवश्यकता है, अर्थात् पहला तर्क फ़ंक्शन इंडेक्स। ऐसा करने के लिए, कॉलम तत्व का चयन करें, जिसमें एक सूत्र है, और फॉर्मूला स्ट्रिंग पर जाएं। ऑपरेटर इंडेक्स (बी 2: बी 7) का पहला तर्क आवंटित करें और F4 बटन पर क्लिक करें। जैसा कि आप देख सकते हैं, चयनित निर्देशांक के पास डॉलर का चिह्न दिखाई दिया। Enter कुंजी पर क्लिक करें। आम तौर पर, सूत्र ने निम्नलिखित रूप लिया:

    = सूचकांक (शीट 2! $ B $ 2: $ B $ 7; खोज बोर्ड (शीट 1! A4; List2! $ A $ 2: $ A $ 7; 0))



11. अब आप एक भरने वाले मार्कर का उपयोग करके प्रतिलिपि बना सकते हैं। हम इसे उसी तरह कहते हैं जिसे हमने पहले बात की है, और टैब्यूलर रेंज के अंत तक फैला हुआ है।



12. जैसा कि आप देख सकते हैं, इस तथ्य के बावजूद कि दो संबंधित तालिकाओं में तारों का क्रम मेल नहीं खाता है, फिर भी, सभी मूल्यों को श्रमिकों के नामों के अनुसार कड़ा कर दिया जाता है। ऑपरेटर सूचकांक खोज के संयोजन के उपयोग के लिए यह हासिल किया गया था।

विधि 4: विशेष सम्मिलित करें

एक्सेल में टाई टेबल सरणी भी एक विशेष सम्मिलन का उपयोग कर सकते हैं।

1. उन मानों का चयन करें जिन्हें आप किसी अन्य तालिका में "कस" करना चाहते हैं। हमारे मामले में, यह एक शीट पर "शर्त" कॉलम रेंज है। सही माउस बटन के साथ समर्पित खंड पर क्लिक करें। खुलने वाली सूची में, "कॉपी करें" आइटम का चयन करें। एक वैकल्पिक संयोजन Ctrl + C कुंजी संयोजन है। उसके बाद, हम शीट 1 पर जाते हैं।



2. जिस पुस्तक की आपको आवश्यकता है, उसके क्षेत्र में जाना, उन कोशिकाओं को आवंटित करें जिनमें मूल्यों को कड़े होने की आवश्यकता होगी। हमारे मामले में, यह "बोली" कॉलम है। दाहिने माउस बटन के साथ समर्पित खंड पर क्लिक करें। "सम्मिलित पैरामीटर" टूलबार में संदर्भ मेनू में, "संचार सम्मिलित करें" आइकन पर क्लिक करें।

   

   एक विकल्प भी है। वह, वैसे भी, एक्सेल के पुराने संस्करणों के लिए एकमात्र है। संदर्भ मेनू में, हम कर्सर को "विशेष डालने" आइटम में लाते हैं। खुलने वाले अतिरिक्त मेनू में, उसी नाम के साथ स्थिति का चयन करें।



3. उसके बाद, एक विशेष डालने की खिड़की खुलती है। सेल के निचले बाएं कोने में "संचार सम्मिलित करें" बटन पर क्लिक करें।



4. जो भी विकल्प आप चुनते हैं, एक टेबल सरणी से मान दूसरे में डाले जाएंगे। स्रोत में डेटा बदलते समय, वे स्वचालित रूप से डाले गए रेंज में भी बदल जाएंगे।

पाठ: एक्सेल में विशेष सम्मिलित करें

विधि 5: कई पुस्तकों में सारणी के बीच संचार

इसके अलावा, आप विभिन्न पुस्तकों में तालिका क्षेत्रों के बीच एक लिंक व्यवस्थित कर सकते हैं। यह एक विशेष सम्मिलित उपकरण का उपयोग करता है। कार्य पिछले विधि में विचार किए गए लोगों के समान ही होगा, सिवाय इसके कि सूत्रों के दौरान नेविगेशन एक पुस्तक के क्षेत्रों के बीच नहीं होगा, बल्कि फ़ाइलों के बीच। स्वाभाविक रूप से, सभी संबंधित किताबें खोली जानी चाहिए।

1. किसी अन्य पुस्तक में स्थानांतरित करने के लिए डेटा रेंज का चयन करें। आईटी राइट माउस बटन पर क्लिक करें और खोले गए मेनू में "कॉपी" स्थिति का चयन करें।



2. फिर हम उस पुस्तक में जाते हैं जिसमें यह डेटा डाला जाना चाहिए। वांछित रेंज का चयन करें। दायां माउस बटन पर क्लिक करें। "सम्मिलित सेटिंग्स" समूह में संदर्भ मेनू में, "सम्मिलित करें" आइटम का चयन करें।



3. उसके बाद, मान डाले जाएंगे। स्रोत पुस्तक में डेटा बदलते समय, कार्यकारी पुस्तक से एक तालिका सरणी स्वचालित रूप से उन्हें कस लेंगी। और यह सुनिश्चित करने के लिए आवश्यक नहीं है कि दोनों किताबें खुली हों। यह एकमात्र कार्यपुस्तिका खोलने के लिए पर्याप्त है, और यदि इसमें पिछले परिवर्तन होते हैं तो यह स्वचालित रूप से बंद संबंधित दस्तावेज़ से डेटा को व्यवस्थित करेगा।

लेकिन यह ध्यान दिया जाना चाहिए कि इस मामले में इनसेट एक अपरिवर्तित सरणी के रूप में उत्पादित किया जाएगा। डाले गए डेटा के साथ किसी भी सेल को बदलने की कोशिश करते समय, एक संदेश ऐसा करने में असमर्थता के बारे में सूचित किया जाएगा।

किसी अन्य पुस्तक से जुड़े एक सरणी में परिवर्तन केवल कनेक्शन को तोड़ सकते हैं।

शीर्षक तालिकाओं के बीच टूट जाता है

कभी-कभी तालिकाओं के बीच कनेक्शन को तोड़ने की आवश्यकता होती है। इसका कारण ऊपर वर्णित किया जा सकता है जब आप किसी अन्य पुस्तक से डाली गई सरणी को बदलना चाहते हैं और बस उपयोगकर्ता की अनिच्छा ताकि उसी तालिका में डेटा स्वचालित रूप से दूसरे से अपडेट हो।

विधि 1: किताबों के बीच संचार विराम

वास्तव में एक ऑपरेशन करके, सभी कोशिकाओं में पुस्तकों के बीच संबंध तोड़ने के लिए। इस मामले में, कोशिकाओं में डेटा बने रहेंगे, लेकिन वे पहले से ही स्थैतिक अद्यतन मान नहीं होंगे जो अन्य दस्तावेजों पर निर्भर नहीं हैं।

1. पुस्तक में जिसमें अन्य फ़ाइलों से मान कड़े होते हैं, डेटा टैब पर जाएं। "कनेक्शन" टूलबार में टेप पर स्थित "लिंक बदलें" आइकन पर क्लिक करें। यह ध्यान दिया जाना चाहिए कि यदि वर्तमान पुस्तक में अन्य फ़ाइलों के साथ कनेक्शन नहीं हैं, तो यह बटन निष्क्रिय है।



2. लिंक बदलें विंडो लॉन्च की गई है। संबंधित पुस्तकों की सूची से चुनें (यदि उनमें से कई हैं) उस फ़ाइल जिसके साथ हम कनेक्शन को तोड़ना चाहते हैं। "कनेक्शन तोड़ें" बटन पर क्लिक करें।



3. एक सूचना विंडो खुलती है, जो आगे की कार्रवाइयों के परिणामों के बारे में एक चेतावनी प्रदान करती है। यदि आप सुनिश्चित हैं कि आप करने जा रहे हैं, तो "ब्रेक कम्युनिकेशन" बटन पर क्लिक करें।



4. इसके बाद, वर्तमान दस्तावेज़ में निर्दिष्ट फ़ाइल के सभी संदर्भ स्थैतिक मूल्यों के साथ प्रतिस्थापित किए जाएंगे।

विधि 2: मानों को सम्मिलित करना

लेकिन उपरोक्त विधि केवल तभी उपयुक्त है जब आपको दो पुस्तकों के बीच सभी लिंक पूरी तरह से तोड़ने की आवश्यकता हो। क्या होगा यदि आपको एक ही फ़ाइल के भीतर संबंधित तालिकाओं को डिस्कनेक्ट करने की आवश्यकता है? आप डेटा कॉपी करके ऐसा कर सकते हैं, और फिर उसी स्थान को मानों के रूप में डाल सकते हैं। वैसे, फ़ाइलों के बीच एक सामान्य संबंध तोड़ने के बिना विभिन्न पुस्तकों के व्यक्तिगत डेटा श्रेणियों के बीच इस विधि को टूटा जा सकता है। चलो देखते हैं कि यह विधि अभ्यास में कैसे काम करती है।

1. हम उस सीमा को हाइलाइट करते हैं जिसमें हम किसी अन्य तालिका के साथ संचार को हटाना चाहते हैं। उस पर सही माउस बटन पर क्लिक करें। खुले मेनू में, "कॉपी करें" आइटम का चयन करें। निर्दिष्ट कार्यों के बजाय, आप हॉट कुंजियां Ctrl + C का एक वैकल्पिक संयोजन डायल कर सकते हैं।



2. इसके बाद, उसी खंड से चयन को हटाए बिना, फिर से दाहिने माउस बटन के साथ उस पर क्लिक करें। इस बार कार्रवाई की सूची में, "मान" आइकन पर क्लिक करें, जिसे सम्मिलित पैरामीटर समूह में पोस्ट किया गया है।



3. उसके बाद, समर्पित सीमा में सभी संदर्भ स्थैतिक मूल्यों के साथ प्रतिस्थापित किए जाएंगे।

जैसा कि आप देख सकते हैं, एक्सेल में कई टेबलों को संबद्ध करने के लिए तरीके और उपकरण होते हैं। साथ ही, टैब्यूलर डेटा अन्य चादरों और यहां तक ​​कि विभिन्न पुस्तकों में भी हो सकता है। यदि आवश्यक हो, तो यह कनेक्शन आसानी से टूटा जा सकता है।