எக்செல் இல் VLOOKUP ஐ எவ்வாறு பயன்படுத்துவது
VLOOKUP என்பது எக்செல் மிகவும் பயனுள்ள செயல்பாடுகளில் ஒன்றாகும், மேலும் இது குறைந்தது புரிந்து கொள்ளப்பட்ட ஒன்றாகும். இந்த கட்டுரையில், ஒரு நிஜ வாழ்க்கை உதாரணம் மூலம் VLOOKUP ஐ மதிப்பிடுகிறோம். பயன்படுத்தக்கூடியதை உருவாக்குவோம் விலைப்பட்டியல் வார்ப்புரு ஒரு கற்பனையான நிறுவனத்திற்கு.
VLOOKUP ஒரு எக்செல் செயல்பாடு. இந்த கட்டுரை வாசகருக்கு ஏற்கனவே எக்செல் செயல்பாடுகளைப் பற்றிய புரிதல் உள்ளது என்றும், SUM, AVERAGE மற்றும் TODAY போன்ற அடிப்படை செயல்பாடுகளைப் பயன்படுத்தலாம் என்றும் கருதுகிறது. அதன் மிகவும் பொதுவான பயன்பாட்டில், VLOOKUP என்பது a தரவுத்தளம் செயல்பாடு, அதாவது தரவுத்தள அட்டவணைகளுடன் இது செயல்படுகிறது - அல்லது இன்னும் எளிமையாக, பட்டியல்கள் எக்செல் பணித்தாளில் உள்ள விஷயங்கள். என்ன வகையான விஷயங்கள்? சரி, ஏதேனும் மாதிரியான. உங்களுடைய குறுவட்டு சேகரிப்பில் உள்ள ஊழியர்கள், அல்லது தயாரிப்புகள், அல்லது வாடிக்கையாளர்கள் அல்லது குறுந்தகடுகள் அல்லது இரவு வானத்தில் உள்ள நட்சத்திரங்களின் பட்டியலைக் கொண்ட பணித்தாள் உங்களிடம் இருக்கலாம். இது உண்மையில் தேவையில்லை.
பட்டியல் அல்லது தரவுத்தளத்தின் எடுத்துக்காட்டு இங்கே. இந்த விஷயத்தில் இது எங்கள் கற்பனையான நிறுவனம் விற்கும் தயாரிப்புகளின் பட்டியல்:
வழக்கமாக இது போன்ற பட்டியல்களில் பட்டியலில் உள்ள ஒவ்வொரு உருப்படிக்கும் ஒருவித தனிப்பட்ட அடையாளங்காட்டி இருக்கும். இந்த வழக்கில், தனிப்பட்ட அடையாளங்காட்டி “உருப்படி குறியீடு” நெடுவரிசையில் உள்ளது. குறிப்பு: VLOOKUP செயல்பாடு ஒரு தரவுத்தளம் / பட்டியலுடன் பணிபுரிய, அந்த பட்டியலில் தனிப்பட்ட அடையாளங்காட்டி (அல்லது “விசை” அல்லது “ஐடி”) கொண்ட ஒரு நெடுவரிசை இருக்க வேண்டும், மற்றும் அந்த நெடுவரிசை அட்டவணையில் முதல் நெடுவரிசையாக இருக்க வேண்டும். மேலே உள்ள எங்கள் மாதிரி தரவுத்தளம் இந்த அளவுகோலை பூர்த்தி செய்கிறது.
VLOOKUP ஐப் பயன்படுத்துவதில் கடினமான பகுதி என்னவென்றால், அது எதைப் புரிந்துகொள்வது என்பதுதான். எனவே இதை முதலில் தெளிவுபடுத்த முடியுமா என்று பார்ப்போம்:
VLOOKUP தனிப்பட்ட அடையாளங்காட்டியின் வழங்கப்பட்ட நிகழ்வின் அடிப்படையில் ஒரு தரவுத்தளம் / பட்டியலிலிருந்து தகவல்களை மீட்டெடுக்கிறது.
மேலே உள்ள எடுத்துக்காட்டில், நீங்கள் VLOOKUP செயல்பாட்டை ஒரு உருப்படி குறியீட்டைக் கொண்ட மற்றொரு விரிதாளில் செருகுவீர்கள், மேலும் அது உங்கள் அசலில் விவரிக்கப்பட்டுள்ளபடி தொடர்புடைய உருப்படியின் விளக்கம், அதன் விலை அல்லது அதன் கிடைக்கும் தன்மை (அதன் “கையிருப்பில்” அளவு) பட்டியல். இந்த தகவல்களில் எது உங்களை திருப்பி அனுப்பும்? சரி, நீங்கள் சூத்திரத்தை உருவாக்கும்போது இதை நீங்கள் தீர்மானிக்க வேண்டும்.
உங்களுக்குத் தேவையானது தரவுத்தளத்திலிருந்து ஒரு தகவல் என்றால், அதில் ஒரு VLOOKUP செயல்பாட்டுடன் ஒரு சூத்திரத்தை உருவாக்கச் செல்வது மிகவும் சிக்கலாக இருக்கும். பொதுவாக நீங்கள் ஒரு வார்ப்புரு போன்ற மறுபயன்பாட்டு விரிதாளில் இந்த வகையான செயல்பாட்டைப் பயன்படுத்துவீர்கள். ஒவ்வொரு முறையும் யாராவது செல்லுபடியாகும் உருப்படி குறியீட்டை உள்ளிடுகையில், தொடர்புடைய உருப்படியைப் பற்றிய தேவையான அனைத்து தகவல்களையும் கணினி மீட்டெடுக்கும்.
இதற்கு ஒரு உதாரணத்தை உருவாக்குவோம்: ஒரு விலைப்பட்டியல் வார்ப்புரு எங்கள் கற்பனையான நிறுவனத்தில் மீண்டும் மீண்டும் பயன்படுத்தலாம்.
முதலில் நாம் எக்செல் தொடங்குவோம், நாங்கள் ஒரு வெற்று விலைப்பட்டியலை உருவாக்குகிறோம்:
இது எவ்வாறு செயல்படப் போகிறது: விலைப்பட்டியல் வார்ப்புருவைப் பயன்படுத்துபவர் “A” நெடுவரிசையில் தொடர்ச்சியான உருப்படி குறியீடுகளை நிரப்புவார், மேலும் கணினி ஒவ்வொரு பொருளின் விளக்கத்தையும் விலையையும் எங்கள் தயாரிப்பு தரவுத்தளத்திலிருந்து மீட்டெடுக்கும். ஒவ்வொரு உருப்படியின் மொத்த வரியைக் கணக்கிட அந்தத் தகவல் பயன்படுத்தப்படும் (நாங்கள் சரியான அளவை உள்ளிடுவோம் என்று வைத்துக் கொள்ளுங்கள்).
இந்த எடுத்துக்காட்டை எளிமையாக வைத்திருப்பதற்கான நோக்கங்களுக்காக, தயாரிப்பு தரவுத்தளத்தை ஒரு தனி தாளில் ஒரே பணிப்புத்தகத்தில் கண்டுபிடிப்போம்:
உண்மையில், தயாரிப்பு தரவுத்தளம் ஒரு தனி பணிப்புத்தகத்தில் அமைந்திருக்க வாய்ப்புள்ளது. இது VLOOKUP செயல்பாட்டிற்கு சிறிய வித்தியாசத்தை ஏற்படுத்துகிறது, இது தரவுத்தளம் ஒரே தாள், வேறு தாள் அல்லது முற்றிலும் மாறுபட்ட பணிப்புத்தகத்தில் அமைந்திருக்கிறதா என்பதைப் பொருட்படுத்தாது.
எனவே, எங்கள் தயாரிப்பு தரவுத்தளத்தை நாங்கள் உருவாக்கியுள்ளோம், இது இதுபோன்று தெரிகிறது:
நாங்கள் எழுதவிருக்கும் VLOOKUP சூத்திரத்தை சோதிக்க, முதலில் எங்கள் வெற்று விலைப்பட்டியலின் A11 கலத்தில் செல்லுபடியாகும் உருப்படி குறியீட்டை உள்ளிடுகிறோம்:
அடுத்து, செயலில் உள்ள கலத்தை VLOOKUP ஆல் தரவுத்தளத்திலிருந்து மீட்டெடுக்க விரும்பும் கலத்திற்கு நகர்த்துவோம். சுவாரஸ்யமாக, இது பெரும்பாலான மக்கள் தவறாகப் புரிந்து கொள்ளும் படி. மேலும் விளக்க: செல் V11 இல் உள்ள உருப்படி குறியீட்டிற்கு ஒத்த விளக்கத்தை மீட்டெடுக்கும் VLOOKUP சூத்திரத்தை உருவாக்க உள்ளோம். இந்த விளக்கத்தை நாம் பெறும்போது எங்கு வைக்க விரும்புகிறோம்? செல் B11 இல், நிச்சயமாக. செல் 11 இல் VLOOKUP சூத்திரத்தை நாங்கள் எழுதுகிறோம். செல் B11 ஐ இப்போது தேர்ந்தெடுக்கவும்.
எக்செல் வழங்கக்கூடிய அனைத்து செயல்பாடுகளின் பட்டியலையும் நாம் கண்டுபிடிக்க வேண்டும், இதன் மூலம் நாம் VLOOKUP ஐத் தேர்வுசெய்து சூத்திரத்தை நிறைவு செய்வதில் சில உதவிகளைப் பெறலாம். முதலில் கிளிக் செய்வதன் மூலம் இது கண்டறியப்படுகிறது சூத்திரங்கள் தாவல், பின்னர் கிளிக் செய்க செயல்பாட்டைச் செருகவும்:
எக்செல் இல் கிடைக்கும் எந்த செயல்பாடுகளையும் தேர்ந்தெடுக்க ஒரு பெட்டி தோன்றும்.
நாங்கள் தேடும் ஒன்றைக் கண்டுபிடிக்க, “தேடல்” போன்ற ஒரு தேடல் சொல்லைத் தட்டச்சு செய்யலாம் (ஏனென்றால் நாங்கள் விரும்பும் செயல்பாடு a தேடல் செயல்பாடு). எக்செல் இல் உள்ள அனைத்து தேடல் தொடர்பான செயல்பாடுகளின் பட்டியலையும் இந்த அமைப்பு எங்களுக்குத் தரும். VLOOKUP பட்டியலில் இரண்டாவது. ஒரு கிளிக்கைத் தேர்ந்தெடுக்கவும் சரி.
தி செயல்பாடு வாதங்கள் பெட்டி தோன்றுகிறது, எல்லாவற்றிற்கும் நம்மைத் தூண்டுகிறது வாதங்கள் (அல்லது அளவுருக்கள்) VLOOKUP செயல்பாட்டை முடிக்க தேவை. பின்வரும் கேள்விகளைக் கேட்கும் செயல்பாடாக இந்த பெட்டியை நீங்கள் நினைக்கலாம்:
- தரவுத்தளத்தில் நீங்கள் என்ன தனிப்பட்ட அடையாளங்காட்டியைப் பார்க்கிறீர்கள்?
- தரவுத்தளம் எங்கே?
- தனிப்பட்ட அடையாளங்காட்டியுடன் தொடர்புடைய தரவுத்தளத்திலிருந்து எந்தத் தகவல், உங்களுக்காக மீட்டெடுக்க விரும்புகிறீர்களா?
முதல் மூன்று வாதங்கள் காட்டப்பட்டுள்ளன உறுதியாக, அவை என்பதைக் குறிக்கும் கட்டாயமாகும் வாதங்கள் (VLOOKUP செயல்பாடு அவை இல்லாமல் முழுமையடையாது மற்றும் சரியான மதிப்பை வழங்காது). நான்காவது வாதம் தைரியமாக இல்லை, அதாவது இது விருப்பமானது:
மேலே இருந்து கீழே வாதங்களை வரிசையில் முடிப்போம்.
நாம் முடிக்க வேண்டிய முதல் வாதம் பார்வை_ மதிப்பு வாதம். தனித்துவமான அடையாளங்காட்டியை எங்கு கண்டுபிடிப்பது என்பதைச் சொல்ல இந்த செயல்பாடு நமக்குத் தேவை உருப்படி குறியீடு இந்த வழக்கில்) அது விளக்கத்தை திருப்பி அனுப்ப வேண்டும். நாம் முன்னர் உள்ளிட்ட உருப்படி குறியீட்டை (A11 இல்) தேர்ந்தெடுக்க வேண்டும்.
முதல் வாதத்தின் வலதுபுறத்தில் உள்ள தேர்வாளர் ஐகானைக் கிளிக் செய்க:
உருப்படி குறியீடு (A11) கொண்ட கலத்தில் ஒரு முறை கிளிக் செய்து, அழுத்தவும் உள்ளிடவும்:
“A11” இன் மதிப்பு முதல் வாதத்தில் செருகப்பட்டுள்ளது.
இப்போது நாம் ஒரு மதிப்பை உள்ளிட வேண்டும் அட்டவணை_அரே வாதம். வேறு வார்த்தைகளில் கூறுவதானால், தரவுத்தளம் / பட்டியலை எங்கு கண்டுபிடிப்பது என்று VLOOKUP க்கு நாம் சொல்ல வேண்டும். இரண்டாவது வாதத்திற்கு அடுத்துள்ள தேர்வாளர் ஐகானைக் கிளிக் செய்க:
இப்போது தரவுத்தளம் / பட்டியலைக் கண்டுபிடித்து முழு பட்டியலையும் தேர்ந்தெடுக்கவும் - தலைப்பு வரி உட்பட. எங்கள் எடுத்துக்காட்டில், தரவுத்தளம் ஒரு தனி பணித்தாளில் அமைந்துள்ளது, எனவே முதலில் அந்த பணித்தாள் தாவலைக் கிளிக் செய்க:
அடுத்து தலைப்பு வரி உட்பட முழு தரவுத்தளத்தையும் தேர்வு செய்கிறோம்:
… மற்றும் அழுத்தவும் உள்ளிடவும். தரவுத்தளத்தைக் குறிக்கும் கலங்களின் வரம்பு (இந்த விஷயத்தில் “’ தயாரிப்பு தரவுத்தளம் ’! A2: D7”) இரண்டாவது வாதத்தில் தானாகவே உள்ளிடப்படும்.
இப்போது நாம் மூன்றாவது வாதத்தை உள்ளிட வேண்டும், Col_index_num. தரவுத்தளத்திலிருந்து எந்தத் தகவலை VLOOKUP க்கு குறிப்பிட இந்த வாதத்தைப் பயன்படுத்துகிறோம், A11 இல் எங்கள் உருப்படி குறியீட்டோடு இணைந்திருக்கிறோம், எங்களிடம் திரும்பி வர விரும்புகிறோம். இந்த குறிப்பிட்ட எடுத்துக்காட்டில், உருப்படியை வைத்திருக்க விரும்புகிறோம் விளக்கம் எங்களிடம் திரும்பினார். தரவுத்தள பணித்தாளில் நீங்கள் பார்த்தால், “விளக்கம்” நெடுவரிசை என்பது நீங்கள் கவனிப்பீர்கள் இரண்டாவது தரவுத்தளத்தில் நெடுவரிசை. இதன் பொருள் “2” இன் மதிப்பை நாம் உள்ளிட வேண்டும் Col_index_num பெட்டி:
"விளக்கம்" நெடுவரிசை இருப்பதால், நாங்கள் இங்கே "2" ஐ உள்ளிடவில்லை என்பதை கவனத்தில் கொள்ள வேண்டும் பி அந்த பணித்தாளில் நெடுவரிசை. தரவுத்தளம் நெடுவரிசையில் தொடங்கினால் கே பணித்தாள், நாங்கள் இன்னும் இந்த துறையில் ஒரு “2” ஐ உள்ளிடுவோம், ஏனெனில் “Description” நெடுவரிசை “Table_array” ஐ குறிப்பிடும்போது நாம் தேர்ந்தெடுத்த கலங்களின் தொகுப்பில் இரண்டாவது நெடுவரிசையாகும்.
இறுதியாக, இறுதி VLOOKUP வாதத்தில் ஒரு மதிப்பை உள்ளிட வேண்டுமா என்பதை நாங்கள் தீர்மானிக்க வேண்டும், வரம்பு_ பார்வை. இந்த வாதத்திற்கு ஒன்று தேவை உண்மை அல்லது பொய் மதிப்பு, அல்லது அது காலியாக விடப்பட வேண்டும். தரவுத்தளங்களுடன் VLOOKUP ஐப் பயன்படுத்தும் போது (90% நேரம் உண்மை), இந்த வாதத்தில் எதை வைக்க வேண்டும் என்பதை தீர்மானிக்கும் வழி பின்வருமாறு கருதப்படலாம்:
தரவுத்தளத்தின் முதல் நெடுவரிசை (தனித்துவமான அடையாளங்காட்டிகளைக் கொண்ட நெடுவரிசை) அகர வரிசைப்படி / எண்ணாக ஏறும் வரிசையில் வரிசைப்படுத்தப்பட்டால், அதன் மதிப்பை உள்ளிட முடியும் உண்மை இந்த வாதத்தில் அல்லது காலியாக விடவும்.
தரவுத்தளத்தின் முதல் நெடுவரிசை என்றால் இல்லை வரிசைப்படுத்தப்பட்டது, அல்லது அது இறங்கு வரிசையில் வரிசைப்படுத்தப்படுகிறது, பின்னர் நீங்கள் வேண்டும் இன் மதிப்பை உள்ளிடவும் பொய் இந்த வாதத்தில்
எங்கள் தரவுத்தளத்தின் முதல் நெடுவரிசை இல்லை வரிசைப்படுத்தப்பட்ட, நாங்கள் நுழைகிறோம் பொய் இந்த வாதத்தில்:
அவ்வளவுதான்! எங்களுக்கு தேவையான மதிப்பை வழங்க VLOOKUP க்கு தேவையான அனைத்து தகவல்களையும் உள்ளிட்டுள்ளோம். கிளிக் செய்யவும் சரி உருப்படி குறியீடு “R99245” உடன் தொடர்புடைய விளக்கம் செல் B11 இல் சரியாக உள்ளிடப்பட்டிருப்பதை பொத்தானைக் கவனியுங்கள்:
எங்களுக்காக உருவாக்கப்பட்ட சூத்திரம் இதுபோல் தெரிகிறது:
நாம் நுழைந்தால் a வெவ்வேறு உருப்படி குறியீடு A11 கலத்தில், VLOOKUP செயல்பாட்டின் சக்தியைக் காணத் தொடங்குவோம்: புதிய உருப்படி குறியீட்டோடு பொருந்துமாறு விளக்க செல் மாறுகிறது:
உருப்படியைப் பெறுவதற்கு இதேபோன்ற படிகளை நாங்கள் செய்யலாம் விலை செல் E11 க்கு திரும்பியது. புதிய சூத்திரம் செல் E11 இல் உருவாக்கப்பட வேண்டும் என்பதை நினைவில் கொள்க. முடிவு இப்படி இருக்கும்:
… மற்றும் சூத்திரம் இப்படி இருக்கும்:
இரண்டு சூத்திரங்களுக்கிடையிலான ஒரே வித்தியாசம் மூன்றாவது வாதம் (Col_index_num) “2” இலிருந்து “3” ஆக மாற்றப்பட்டுள்ளது (ஏனெனில் தரவுத்தளத்தில் 3 வது நெடுவரிசையிலிருந்து தரவை மீட்டெடுக்க விரும்புகிறோம்).
இந்த 2 பொருட்களை வாங்க முடிவு செய்தால், செல் 11 இல் “2” ஐ உள்ளிடுவோம். மொத்த வரியைப் பெறுவதற்கு செல் 11 இல் ஒரு எளிய சூத்திரத்தை உள்ளிடுவோம்:
= டி 11 * இ 1
… இது போல் தெரிகிறது…
விலைப்பட்டியல் வார்ப்புருவை நிறைவு செய்தல்
VLOOKUP பற்றி இதுவரை நிறைய கற்றுக்கொண்டோம். உண்மையில், இந்த கட்டுரையில் நாம் கற்றுக்கொள்ளப் போகும் அனைத்தையும் நாங்கள் கற்றுக்கொண்டோம். தரவுத்தளங்களைத் தவிர வேறு சூழ்நிலைகளில் VLOOKUP ஐப் பயன்படுத்தலாம் என்பதை கவனத்தில் கொள்ள வேண்டியது அவசியம். இது மிகவும் பொதுவானது, மேலும் எதிர்காலத்தில் எப்படி-எப்படி கீக் கட்டுரைகளில் இது அடங்கும்.
எங்கள் விலைப்பட்டியல் வார்ப்புரு இன்னும் முடிக்கப்படவில்லை. அதை முடிக்க, பின்வருவனவற்றை நாங்கள் செய்வோம்:
- செல் A11 இலிருந்து மாதிரி உருப்படி குறியீட்டையும், செல் 11 இலிருந்து “2” ஐ அகற்றுவோம். இது புதிதாக உருவாக்கப்பட்ட VLOOKUP சூத்திரங்கள் பிழை செய்திகளைக் காண்பிக்கும்:
எக்செல்ஸை நியாயமான முறையில் பயன்படுத்துவதன் மூலம் இதை சரிசெய்யலாம் IF () மற்றும் ISBLANK () செயல்பாடுகள். இதிலிருந்து எங்கள் சூத்திரத்தை மாற்றுகிறோம்… = VLOOKUP (A11, ’தயாரிப்பு தரவுத்தளம்’! A2: D7,2, FALSE)…இந்த…= IF (ISBLANK (A11), ””, VLOOKUP (A11, ’தயாரிப்பு தரவுத்தளம்’! A2: D7,2, FALSE)) - பி 11, ஈ 11 மற்றும் எஃப் 11 கலங்களில் உள்ள சூத்திரங்களை விலைப்பட்டியலின் உருப்படி வரிசைகளின் மீதமுள்ள நகலெடுப்போம். நாங்கள் இதைச் செய்தால், இதன் விளைவாக வரும் சூத்திரங்கள் இனி தரவுத்தள அட்டவணையை சரியாகக் குறிக்காது என்பதை நினைவில் கொள்க. தரவுத்தளத்திற்கான செல் குறிப்புகளை மாற்றுவதன் மூலம் இதை சரிசெய்யலாம் அறுதி செல் குறிப்புகள். மாற்றாக - இன்னும் சிறப்பாக - நாம் ஒரு உருவாக்க முடியும் வரம்பு பெயர் முழு தயாரிப்பு தரவுத்தளத்திற்கும் (“தயாரிப்புகள்” போன்றவை), மற்றும் செல் குறிப்புகளுக்கு பதிலாக இந்த வரம்பின் பெயரைப் பயன்படுத்தவும். இதிலிருந்து சூத்திரம் மாறும்… = IF (ISBLANK (A11), ””, VLOOKUP (A11, ’தயாரிப்பு தரவுத்தளம்’! A2: D7,2, FALSE))…இந்த… = IF (ISBLANK (A11), ””, VLOOKUP (A11, தயாரிப்புகள், 2, FALSE))… மற்றும் பிறகு மீதமுள்ள விலைப்பட்டியல் உருப்படி வரிசைகளுக்கு சூத்திரங்களை நகலெடுக்கவும்.
- எங்கள் சூத்திரங்களைக் கொண்டிருக்கும் கலங்களை (அல்லது அதற்கு பதிலாக) "பூட்டுவோம்" திறத்தல் தி மற்றவை கலங்கள்), பின்னர் பணித்தாளைப் பாதுகாக்கவும், விலைப்பட்டியலை நிரப்ப யாராவது வரும்போது எங்கள் கவனமாக கட்டப்பட்ட சூத்திரங்கள் தற்செயலாக மேலெழுதப்படுவதில்லை என்பதை உறுதிசெய்யும்.
- கோப்பை ஒரு என சேமிப்போம் வார்ப்புரு, இது எங்கள் நிறுவனத்தில் உள்ள அனைவராலும் மீண்டும் பயன்படுத்தப்படலாம்
நாங்கள் உணர்கிறோம் என்றால் உண்மையில் புத்திசாலி, நாங்கள் எங்கள் வாடிக்கையாளர்களின் தரவுத்தளத்தை வேறொரு பணித்தாளில் உருவாக்குவோம், பின்னர் செல் F5 இல் உள்ளிடப்பட்ட வாடிக்கையாளர் ஐடியைப் பயன்படுத்தி வாடிக்கையாளரின் பெயர் மற்றும் முகவரியை B6, B7 மற்றும் B8 கலங்களில் தானாக நிரப்பலாம்.
நீங்கள் VLOOKUP உடன் பயிற்சி செய்ய விரும்பினால், அல்லது எங்கள் விளைவாக வரும் விலைப்பட்டியல் வார்ப்புருவைப் பார்க்க விரும்பினால், அதை இங்கிருந்து பதிவிறக்கம் செய்யலாம்.