தானியங்கு அளவுடன் கூடிய டைனமிக் வரம்பு

எக்செல் இல் டேட்டாவைக் கொண்ட டேபிள்கள் உள்ளதா, அதன் அளவை மாற்றலாம், அதாவது வேலையின் போது வரிசைகளின் எண்ணிக்கை (நெடுவரிசைகள்) அதிகரிக்கலாம் அல்லது குறைக்கலாம்? அட்டவணை அளவுகள் "மிதவை" என்றால், நீங்கள் தொடர்ந்து இந்த தருணத்தை கண்காணித்து அதை சரிசெய்ய வேண்டும்:

• எங்கள் அட்டவணையைக் குறிக்கும் அறிக்கை சூத்திரங்களில் உள்ள இணைப்புகள்
• எங்கள் அட்டவணையின்படி கட்டப்பட்ட பிவோட் அட்டவணைகளின் ஆரம்ப வரம்புகள்
• எங்கள் அட்டவணையின்படி கட்டப்பட்ட விளக்கப்படங்களின் ஆரம்ப வரம்புகள்
• எங்கள் அட்டவணையை தரவு ஆதாரமாகப் பயன்படுத்தும் கீழ்தோன்றும் வரம்புகள்

மொத்தத்தில் இதெல்லாம் சலிப்படைய விடாது😉

டைனமிக் "ரப்பர்" வரம்பை உருவாக்க இது மிகவும் வசதியாகவும் சரியாகவும் இருக்கும், இது தரவுகளின் உண்மையான எண்ணிக்கையிலான வரிசைகள் மற்றும் நெடுவரிசைகளுக்கு தானாகவே அளவை சரிசெய்யும். இதை செயல்படுத்த, பல வழிகள் உள்ளன.

முறை 1. ஸ்மார்ட் டேபிள்

உங்கள் கலங்களின் வரம்பை முன்னிலைப்படுத்தி தாவலில் இருந்து தேர்ந்தெடுக்கவும் முகப்பு - அட்டவணையாக வடிவமைக்கவும் (முகப்பு - அட்டவணையாக வடிவமைக்கவும்):

பக்க விளைவுகளாக அட்டவணையில் சேர்க்கப்பட்டுள்ள கோடிட்ட வடிவமைப்பு உங்களுக்குத் தேவையில்லை என்றால், தோன்றும் தாவலில் அதை அணைக்கலாம். கன்ஸ்ட்ரக்டர் (வடிவமைப்பு). இந்த வழியில் உருவாக்கப்பட்ட ஒவ்வொரு அட்டவணையும் தாவலில் அதே இடத்தில் மிகவும் வசதியான ஒன்றை மாற்றக்கூடிய பெயரைப் பெறுகிறது. கன்ஸ்ட்ரக்டர் (வடிவமைப்பு) துறையில் அட்டவணை பெயர் (அட்டவணை பெயர்).

இப்போது நாம் நமது "ஸ்மார்ட் டேபிள்"க்கான டைனமிக் இணைப்புகளைப் பயன்படுத்தலாம்:

• டேபிள் 1 - தலைப்பு வரிசையைத் தவிர முழு அட்டவணையுடன் இணைக்கவும் (A2:D5)
• அட்டவணை1[#அனைத்தும்] - முழு அட்டவணைக்கும் இணைப்பு (A1:D5)
• அட்டவணை 1[பீட்டர்] முதல் செல்-தலைப்பு இல்லாத வரம்பு-நெடுவரிசைக்கான குறிப்பு (C2:C5)
• அட்டவணை1[#தலைப்புகள்] - நெடுவரிசைகளின் பெயர்களுடன் "தலைப்புக்கு" இணைப்பு (A1:D1)

அத்தகைய குறிப்புகள் சூத்திரங்களில் சிறப்பாக செயல்படுகின்றன, எடுத்துக்காட்டாக:

= கூடுதல் (அட்டவணை1[மாஸ்கோ]) - "மாஸ்கோ" நெடுவரிசைக்கான தொகையின் கணக்கீடு

or

=VPR(F5;டேபிள் 1.

தாவலில் தேர்ந்தெடுப்பதன் மூலம் பிவோட் அட்டவணைகளை உருவாக்கும் போது இத்தகைய இணைப்புகளை வெற்றிகரமாகப் பயன்படுத்தலாம் செருகு - பிவோட் அட்டவணை (செருகு - பிவோட் அட்டவணை) மற்றும் ஸ்மார்ட் டேபிளின் பெயரை தரவு மூலமாக உள்ளிடவும்:

அத்தகைய அட்டவணையின் ஒரு பகுதியை நீங்கள் தேர்ந்தெடுத்தால் (உதாரணமாக, முதல் இரண்டு நெடுவரிசைகள்) மற்றும் எந்த வகையிலும் ஒரு வரைபடத்தை உருவாக்கினால், புதிய வரிகளைச் சேர்க்கும்போது, ​​அவை தானாகவே வரைபடத்தில் சேர்க்கப்படும்.

கீழ்தோன்றும் பட்டியல்களை உருவாக்கும் போது, ​​ஸ்மார்ட் டேபிள் உறுப்புகளுக்கான நேரடி இணைப்புகளைப் பயன்படுத்த முடியாது, ஆனால் ஒரு தந்திரோபாய தந்திரத்தைப் பயன்படுத்தி இந்த வரம்பை நீங்கள் எளிதாகப் பெறலாம் - செயல்பாட்டைப் பயன்படுத்தவும் மறைமுக (உள்நோக்கம்), இது உரையை இணைப்பாக மாற்றுகிறது:

அந்த. உரைச் சரத்தின் வடிவில் உள்ள ஸ்மார்ட் டேபிளுக்கான இணைப்பு (மேற்கோள் குறிகளில்!) முழு அளவிலான இணைப்பாக மாறும், கீழ்தோன்றும் பட்டியல் பொதுவாக அதை உணரும்.

முறை 2: டைனமிக் பெயரிடப்பட்ட வரம்பு

சில காரணங்களால் உங்கள் தரவை ஸ்மார்ட் டேபிளாக மாற்றுவது விரும்பத்தகாததாக இருந்தால், நீங்கள் சற்று சிக்கலான, ஆனால் மிகவும் நுட்பமான மற்றும் பல்துறை முறையைப் பயன்படுத்தலாம் - எக்செல் இல் எங்கள் அட்டவணையைக் குறிக்கும் மாறும் பெயரிடப்பட்ட வரம்பை உருவாக்கவும். பின்னர், ஸ்மார்ட் டேபிளைப் போலவே, எந்த சூத்திரங்கள், அறிக்கைகள், விளக்கப்படங்கள் போன்றவற்றில் உருவாக்கப்பட்ட வரம்பின் பெயரை நீங்கள் சுதந்திரமாகப் பயன்படுத்தலாம். ஒரு எளிய உதாரணத்துடன் தொடங்குவோம்:

டாஸ்க்: நகரங்களின் பட்டியலைக் குறிக்கும், புதிய நகரங்களைச் சேர்க்கும் போது அல்லது அவற்றை நீக்கும் போது, ​​தானாக நீட்டி, சுருங்கும் ஒரு மாறும் பெயரிடப்பட்ட வரம்பை உருவாக்கவும்.

எந்த பதிப்பிலும் கிடைக்கக்கூடிய இரண்டு உள்ளமைக்கப்பட்ட எக்செல் செயல்பாடுகள் நமக்குத் தேவைப்படும் - POICPOZ (பொருத்துக) வரம்பின் கடைசி கலத்தை தீர்மானிக்க, மற்றும் அட்டவணையில் (இன்டெக்ஸ்) டைனமிக் இணைப்பை உருவாக்க.

MATCHஐப் பயன்படுத்தி கடைசி கலத்தைக் கண்டறிதல்

MATCH(பார்வை_மதிப்பு, வரம்பு, போட்டி_வகை) - ஒரு வரம்பில் (வரிசை அல்லது நெடுவரிசை) கொடுக்கப்பட்ட மதிப்பைத் தேடி, அது கண்டுபிடிக்கப்பட்ட கலத்தின் வரிசை எண்ணை வழங்கும் செயல்பாடு. எடுத்துக்காட்டாக, MATCH(“மார்ச்”;A1:A5;0) சூத்திரம் 4 என்ற எண்ணை வழங்கும், ஏனெனில் “மார்ச்” என்ற சொல் A1:A5 நெடுவரிசையில் நான்காவது கலத்தில் அமைந்துள்ளது. கடைசி சார்பு வாதம் Match_Type = 0 என்பது நாம் சரியான பொருத்தத்தைத் தேடுகிறோம் என்பதாகும். இந்த வாதம் குறிப்பிடப்படவில்லை என்றால், செயல்பாடு அருகிலுள்ள சிறிய மதிப்புக்கான தேடல் பயன்முறைக்கு மாறும் - இது எங்கள் வரிசையில் கடைசியாக ஆக்கிரமிக்கப்பட்ட கலத்தைக் கண்டறிய வெற்றிகரமாகப் பயன்படுத்தப்படலாம்.

தந்திரத்தின் சாராம்சம் எளிது. மேலிருந்து கீழாக வரம்பில் உள்ள கலங்களுக்கான MATCH தேடல்கள், கோட்பாட்டில், கொடுக்கப்பட்ட ஒன்றிற்கு மிக அருகில் உள்ள சிறிய மதிப்பைக் கண்டறிந்தால் நிறுத்த வேண்டும். அட்டவணையில் கிடைக்கும் மதிப்பை விட அதிகமாக இருக்கும் மதிப்பை நீங்கள் விரும்பிய மதிப்பாகக் குறிப்பிட்டால், MATCH ஆனது அட்டவணையின் இறுதியை அடையும், எதையும் கண்டுபிடிக்காது மற்றும் கடைசியாக நிரப்பப்பட்ட கலத்தின் வரிசை எண்ணைக் கொடுக்கும். எங்களுக்கு அது தேவை!

எங்கள் வரிசையில் எண்கள் மட்டுமே இருந்தால், ஒரு எண்ணை விரும்பிய மதிப்பாகக் குறிப்பிடலாம், இது அட்டவணையில் உள்ள எதையும் விட அதிகமாக இருக்கும்:

உத்தரவாதத்திற்காக, நீங்கள் 9E + 307 என்ற எண்ணைப் பயன்படுத்தலாம் (9 இன் சக்திக்கு 10 மடங்கு 307, அதாவது 9 307 பூஜ்ஜியங்களுடன்) - எக்செல் கொள்கையளவில் வேலை செய்யக்கூடிய அதிகபட்ச எண்.

எங்கள் நெடுவரிசையில் உரை மதிப்புகள் இருந்தால், சாத்தியமான மிகப்பெரிய எண்ணுக்கு சமமானதாக, நீங்கள் REPEAT ("i", 255) - 255 எழுத்துக்களைக் கொண்ட ஒரு உரை சரம் "i" - இன் கடைசி எழுத்தை செருகலாம். எழுத்துக்கள். எக்செல் உண்மையில் தேடும் போது எழுத்துக் குறியீடுகளை ஒப்பிடுவதால், எங்கள் அட்டவணையில் உள்ள எந்த உரையும் தொழில்நுட்ப ரீதியாக இவ்வளவு நீளமான "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy" XNUMX XNUMX:XNUMX:XNUMX PM வரையில் "சிறியதாக" இருக்கும்:

INDEX ஐப் பயன்படுத்தி இணைப்பை உருவாக்கவும்

இப்போது அட்டவணையில் உள்ள காலியாக இல்லாத உறுப்புகளின் நிலையை நாம் அறிந்திருப்பதால், அது எங்கள் முழு வரம்பிற்கும் ஒரு இணைப்பை உருவாக்குகிறது. இதற்கு நாங்கள் செயல்பாட்டைப் பயன்படுத்துகிறோம்:

INDEX(வரம்பு; வரிசை_எண்; நெடுவரிசை_எண்)

இது கலத்தின் உள்ளடக்கங்களை வரம்பிலிருந்து வரிசை மற்றும் நெடுவரிசை எண் மூலம் வழங்குகிறது, எடுத்துக்காட்டாக, எங்கள் அட்டவணையில் உள்ள செயல்பாடு =INDEX(A1:D5;3;4) நகரங்கள் மற்றும் மாதங்கள் முந்தைய முறையிலிருந்து 1240-ஐக் கொடுக்கும். 3 வது வரிசை மற்றும் 4 வது நெடுவரிசையில் இருந்து, அதாவது கலங்கள் D3. ஒரே ஒரு நெடுவரிசை இருந்தால், அதன் எண்ணைத் தவிர்க்கலாம், அதாவது INDEX(A2:A6;3) சூத்திரம் கடைசி ஸ்கிரீன்ஷாட்டில் “சமாரா” என்று தரும்.

மற்றும் முற்றிலும் வெளிப்படையான நுணுக்கம் ஒன்று இல்லை: வழக்கம் போல் = குறிக்குப் பிறகு INDEX ஆனது கலத்திற்குள் நுழையாமல், பெருங்குடலுக்குப் பின் வரம்பைக் குறிப்பதில் இறுதிப் பகுதியாகப் பயன்படுத்தப்பட்டால், அது இனி வெளியேறாது. கலத்தின் உள்ளடக்கங்கள், ஆனால் அதன் முகவரி! எனவே, $A$2:INDEX($A$2:$A$100;3) போன்ற சூத்திரம் வெளியீட்டில் A2:A4 வரம்பைக் குறிக்கும்.

இங்குதான் MATCH செயல்பாடு வருகிறது, பட்டியலின் முடிவை மாறும் வகையில் தீர்மானிக்க INDEX க்குள் செருகுவோம்:

=$A$2:INDEX($A$2:$A$100; போட்டி(பிரதிநிதி("I";255);A2:A100))

பெயரிடப்பட்ட வரம்பை உருவாக்கவும்

அனைத்தையும் ஒரே மொத்தமாக பேக் செய்ய வேண்டும். ஒரு தாவலைத் திறக்கவும் சூத்திரம் (சூத்திரங்கள்) மற்றும் கிளிக் செய்யவும் பெயர் மேலாளர் (பெயர் மேலாளர்). திறக்கும் சாளரத்தில், பொத்தானைக் கிளிக் செய்யவும் உருவாக்கு (புதியது), புலத்தில் எங்கள் வரம்பு பெயரையும் சூத்திரத்தையும் உள்ளிடவும் ரேஞ்ச் (குறிப்பு):

அதை கிளிக் செய்ய உள்ளது OK மற்றும் தயாராக வரம்பை எந்த சூத்திரங்கள், கீழ்தோன்றும் பட்டியல்கள் அல்லது விளக்கப்படங்களில் பயன்படுத்தலாம்.

• அட்டவணைகள் மற்றும் தேடல் மதிப்புகளை இணைக்க VLOOKUP செயல்பாட்டைப் பயன்படுத்துதல்
• தானாக மக்கள்தொகையிடும் கீழ்தோன்றும் பட்டியலை எவ்வாறு உருவாக்குவது
• பெரிய அளவிலான தரவை பகுப்பாய்வு செய்ய பைவட் அட்டவணையை எவ்வாறு உருவாக்குவது