स्वत: आकारको साथ गतिशील दायरा

के तपाइँसँग Excel मा डाटा सहितको तालिकाहरू छन् जसलाई रिसाइज गर्न सकिन्छ, अर्थात् पङ्क्ति (स्तम्भहरू) को संख्या कामको क्रममा बढ्न वा घटाउन सक्छ? यदि तालिकाको आकार "फ्लोट" छ भने, तपाईंले यस क्षणलाई निरन्तर निगरानी र यसलाई सुधार गर्नुपर्नेछ:

• हाम्रो तालिकालाई सन्दर्भ गर्ने रिपोर्ट सूत्रहरूमा लिङ्कहरू
• हाम्रो तालिका अनुसार बनाइएका पिभोट तालिकाहरूको प्रारम्भिक दायराहरू
• हाम्रो तालिका अनुसार बनाइएको चार्टको प्रारम्भिक दायरा
• हाम्रो तालिकालाई डेटा स्रोतको रूपमा प्रयोग गर्ने ड्रपडाउनहरूको दायरा

कुल मिलाएर यी सबैले तपाईंलाई बोर हुन दिनेछैन 😉

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

विधि 1. स्मार्ट तालिका

तपाईंको कक्षहरूको दायरा हाइलाइट गर्नुहोस् र ट्याबबाट चयन गर्नुहोस् गृह - तालिकाको रूपमा ढाँचा (घर - तालिकाको रूपमा ढाँचा):

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

अब हामी हाम्रो "स्मार्ट टेबल" मा गतिशील लिङ्कहरू प्रयोग गर्न सक्छौं:

• तालिका 1 - हेडर पङ्क्ति बाहेक सम्पूर्ण तालिकामा लिङ्क गर्नुहोस् (A2:D5)
• तालिका १[#सबै] - सम्पूर्ण तालिकामा लिङ्क (A1:D5)
• तालिका १ [पिटर] - पहिलो सेल हेडर बिना दायरा-स्तम्भको सन्दर्भ (C2:C5)
• तालिका १[#हेडर] - स्तम्भहरूको नामको साथ "हेडर" मा लिङ्क गर्नुहोस् (A1:D1)

त्यस्ता सन्दर्भहरूले सूत्रहरूमा उत्कृष्ट काम गर्दछ, उदाहरणका लागि:

= SUM (तालिका १ [मस्को]) - स्तम्भ "मास्को" को लागि योग को गणना

or

=VPR(F5;तालिका 1;3;0) - सेल F5 बाट महिनाको लागि तालिकामा खोज्नुहोस् र यसको लागि सेन्ट पीटर्सबर्ग योग जारी गर्नुहोस् (VLOOKUP के हो?)

ट्याबमा चयन गरेर पिभोट तालिकाहरू सिर्जना गर्दा त्यस्ता लिङ्कहरू सफलतापूर्वक प्रयोग गर्न सकिन्छ घुसाउनुहोस् - पिभोट तालिका (घुसाउनुहोस् - पिभोट तालिका) र डेटा स्रोतको रूपमा स्मार्ट तालिकाको नाम प्रविष्ट गर्दै:

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

ड्रप-डाउन सूचीहरू सिर्जना गर्दा, स्मार्ट तालिका तत्वहरूमा सीधा लिङ्कहरू प्रयोग गर्न सकिँदैन, तर तपाईं सजिलैसँग एक रणनीतिक चाल प्रयोग गरेर यो सीमितताको वरिपरि पुग्न सक्नुहुन्छ - प्रकार्य प्रयोग गर्नुहोस्। अप्रत्यक्ष (अप्रत्यक्ष), जसले पाठलाई लिङ्कमा बदल्छ:

ती। पाठ स्ट्रिङको रूपमा स्मार्ट तालिकाको लिङ्क (उद्धरण चिन्हहरूमा!) पूर्ण-लिङ्कमा परिणत हुन्छ, र ड्रप-डाउन सूचीले सामान्यतया यसलाई बुझ्छ।

विधि २: गतिशील नाम गरिएको दायरा

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

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

हामीलाई कुनै पनि संस्करणमा उपलब्ध दुईवटा निर्मित एक्सेल प्रकार्यहरू चाहिन्छ - POICPOZ (MATCH) दायराको अन्तिम सेल निर्धारण गर्न, र INDEX (INDEX) गतिशील लिङ्क सिर्जना गर्न।

MATCH प्रयोग गरेर अन्तिम सेल फेला पार्दै

MATCH(lookup_value, दायरा, match_type) - एउटा प्रकार्य जसले दायरा (पङ्क्ति वा स्तम्भ) मा दिइएको मान खोज्छ र यो फेला परेको कक्षको क्रम संख्या फर्काउँछ। उदाहरणका लागि, सूत्र MATCH("मार्च";A1:A5;0) ले परिणामको रूपमा नम्बर 4 फर्काउनेछ, किनभने "मार्च" शब्द A1:A5 को चौथो कक्षमा अवस्थित छ। अन्तिम प्रकार्य तर्क Match_Type = 0 को मतलब हामी एक सटीक मिलान खोजिरहेका छौं। यदि यो तर्क निर्दिष्ट गरिएको छैन भने, तब प्रकार्यले निकटतम सानो मानको लागि खोज मोडमा स्विच गर्नेछ - यो वास्तवमा हाम्रो एरेमा अन्तिम कब्जा गरिएको सेल फेला पार्न सफलतापूर्वक प्रयोग गर्न सकिन्छ।

चाल को सार सरल छ। MATCH ले माथिदेखि तलसम्म दायराका कक्षहरूको खोजी गर्छ र सिद्धान्तमा, यो दिइएको एउटाको सबैभन्दा नजिकको सबैभन्दा सानो मान फेला पर्दा बन्द गर्नुपर्छ। यदि तपाइँले इच्छित मानको रूपमा तालिकामा उपलब्ध कुनै पनि भन्दा स्पष्ट रूपमा ठूलो मान निर्दिष्ट गर्नुभयो भने, MATCH तालिकाको एकदमै छेउमा पुग्नेछ, केहि पनि फेला पार्दैन र अन्तिम भरिएको कक्षको अनुक्रम नम्बर दिन्छ। र हामीलाई यो चाहिन्छ!

यदि हाम्रो एरेमा संख्याहरू मात्र छन् भने, हामी इच्छित मानको रूपमा संख्या निर्दिष्ट गर्न सक्छौं, जुन स्पष्ट रूपमा तालिकामा भएका कुनै पनि भन्दा ठूलो छ:

ग्यारेन्टीको लागि, तपाईले संख्या 9E + 307 (9 गुणा 10 देखि 307 को पावर, अर्थात् 9 307 शून्य सहित) प्रयोग गर्न सक्नुहुन्छ - सिद्धान्तमा Excel ले काम गर्न सक्ने अधिकतम संख्या।

यदि हाम्रो स्तम्भमा पाठ मानहरू छन् भने, त्यसपछि सबैभन्दा ठूलो सम्भावित संख्याको बराबरको रूपमा, तपाईंले निर्माण REPEAT("i", 255) सम्मिलित गर्न सक्नुहुन्छ - 255 अक्षरहरू समावेश गरिएको पाठ स्ट्रिङ - "i" - को अन्तिम अक्षर। वर्णमाला। एक्सेलले वास्तवमा चरित्र कोड तुलना गर्दा, हाम्रो टेबुलमा कुनै पनि पाठ प्रासंगिक रूपमा "हो" यति लामो रूपमा "धेरै लामो समयसीयाकोसिन्ययययययययययययययययययययययययययययय-लाइन" लाइन:

INDEX प्रयोग गरेर लिङ्क उत्पन्न गर्नुहोस्

अब जब हामीलाई तालिकामा अन्तिम गैर-खाली तत्वको स्थिति थाहा छ, यसले हाम्रो सम्पूर्ण दायराको लिङ्क बनाउन बाँकी छ। यसको लागि हामी प्रकार्य प्रयोग गर्छौं:

INDEX(दायरा; row_num; column_num)

यसले पङ्क्ति र स्तम्भ नम्बरद्वारा दायराबाट कक्षको सामग्री दिन्छ, उदाहरणका लागि, हाम्रो तालिकामा रहेको प्रकार्य =INDEX(A1:D5;3;4) ले अघिल्लो विधिबाट शहरहरू र महिनाहरू 1240 दिनेछ - सामग्री तेस्रो पङ्क्ति र चौथो स्तम्भबाट, अर्थात् कक्षहरू D3। यदि त्यहाँ एउटा मात्र स्तम्भ छ भने, त्यसको संख्या मेटाउन सकिन्छ, अर्थात् सूत्र INDEX(A4:A3;2) ले अन्तिम स्क्रिनसटमा "समारा" दिनेछ।

र त्यहाँ एक पूर्ण रूपमा स्पष्ट न्युनन्स छैन: यदि INDEX लाई = चिन्ह पछि कक्षमा सामान्य रूपमा प्रवेश गरिएको छैन, तर कोलोन पछि दायराको सन्दर्भको अन्तिम भागको रूपमा प्रयोग गरिन्छ, त्यसपछि यो अब बाहिर आउँदैन। सेलको सामग्री, तर यसको ठेगाना! यसरी, $A$2:INDEX($A$2:$A$100;3) जस्ता सूत्रले आउटपुटमा दायरा A2:A4 लाई सन्दर्भ दिनेछ।

र यो जहाँ MATCH प्रकार्य आउँछ, जुन हामीले सूचीको अन्त्यलाई गतिशील रूपमा निर्धारण गर्न INDEX भित्र घुसाउँछौं:

=$A$2:INDEX($A$2:$A$100; MATCH(REP("I"; 255);A2:A100))

नामित दायरा सिर्जना गर्नुहोस्

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

यसमा क्लिक गर्न बाँकी छ OK र तयार दायरा कुनै पनि सूत्र, ड्रप-डाउन सूची वा चार्टहरूमा प्रयोग गर्न सकिन्छ।

• तालिकाहरू र लुकअप मानहरू लिङ्क गर्न VLOOKUP प्रकार्य प्रयोग गर्दै
• कसरी एक स्वत: आबादी ड्रपडाउन सूची सिर्जना गर्ने
• ठूलो मात्रामा डाटा विश्लेषण गर्न पिभोट तालिका कसरी बनाउने