Excel சூத்திரங்கள் - பாகம் 3

Excel சூத்திரங்கள் பற்றிய கடந்த பதிவில் lookup பற்றி எழுதுங்கள் என எனக்கு நண்பர் பினாத்தல் சுரேஷ் பின்னூட்டமிட்டிருந்தார்.அவர் கேட்டுக் கொண்டபடி இந்த பதிவில் Vlookup மற்றும் Hlookup பற்றி விளக்குகிறேன்.

Vlookup அதாவது vertical lookup என்பது நெட்டுவசத்தில் உங்களுக்குத் தேவையான தகவல்களை பெறுவதாகும். அதுபோல் H look up என்பது குறுக்குவசத்தில் தேவையான தகவல்களை பெறுவது.

உதாரணத்திற்கு நீங்கள் ஒரு Excel fileல் encoding செய்கிறீர்கள். Encoding valueவுக்கு சம்பந்தப்பட்ட தகவல் வேறு ஒரு Excel கோப்பில்லோ அல்லது அதே கோப்பின் வேறு தாளிலோ உள்ளது(source file) என கருதுவோம். ஒவ்வொன்றாக பார்த்து பார்த்து அந்த சம்பந்தப்பட்ட தகவல்களை உள்ளிடுவது கடினமான வேலை. இதற்குத்தான் தீர்வாக lookup சூத்திரம் உள்ளது.இப்போது கீழே உள்ள படத்தினை பெரிதாக்கிப் பாருங்கள்.

பல மாணாக்கர்கள் தங்கள் பாடங்களில் வாங்கிய மதிப்பெண்கள் பட்டியலிடப்பட்டுள்ளன.மொத்த மதிப்பெண்களும் அவர்களது தேர்ச்சி நிலையும் குறிப்பிடப்பட்டுள்ளது.இதுதான் source file என வைத்துக் கொள்வோம்.இனி சில குறிப்பிட்ட மாணாக்கரது கணிதம்,அறிவியல் மதிப்பெண்கள் மற்றும் தேர்ச்சி நிலை மட்டும் நமக்கு மற்றொரு Excel கோப்பில் தேவைப்படுகிறது எனில் vlookup சூத்திரத்தின் மூலம் அவற்றை எளிதாக பெறலாம்.கீழ்கண்ட படத்தினைப் பாருங்கள்.

சூத்திரம் தேவைப்படும் இடத்தில் =vlookup( என type செய்யும்போது சூத்திரம் பின்வருமாறு வரும்.vlookup(lookup value,table array,col.index num,(range look up)).இதில் lookup value என்பது நீங்கள் எந்த valueவுக்காக தகவல் தேடுகிறீர்களோ அது. மேலுள்ள படத்தின்படி ஹரிகரன் என்ற மாணாக்கரின் மதிப்பெண்களை தேடுகிறீர்கள் என்றால் ஹரிகரன் பெயர் உள்ள cell B5ல் கர்சரை அழுத்தவும். மாற்றாக =vlookup("Hariharan", எனவும் எழுதலாம். ஆனால் நீங்கள் எழுதுவது source fileல் உள்ள text போலவே இருக்க வேண்டும்.உதாரனத்திற்கு "Hariharan" என்பதற்குப் பதிலாக "hariharan" என எழுதினால் error தான் வரும்.எனவே முடிந்தவரை கர்சரை அழுத்தி B5 என்றே உள்ளிடுங்கள்.

அடுத்தது table array இதற்கு source file இருக்கும் இடத்துக்குச் செல்லுங்கள்.அங்கு நாம் தேடும் lookup value (Hariharan) எந்த columnத்தில் உள்ளதோ அதில் முதல் rowவிலிருந்து tableஐ தொடங்கி நமக்கு என்ன என்ன தகவல்கள் வேண்டுமோ அவை அடங்கியுள்ள columnத்தின் கடைசி row வரைக்கும் கர்சரை வைத்து இழுங்கள். உதாரனத்திற்கு மேலுள்ள source file படத்தில் Hariharan என்னும் பெயர் column B யில் உள்ளது எனவே அதன் முதல் rowவான B2 விலிருந்து ஆரம்பித்துஉங்களுக்கு தேர்வு நிலை pass or fail உள்ள column I யில் கடைசி row வரைக்கும் கர்சரை வைத்து இழுங்கள். இப்படி இழுத்த பின் உங்களது source file வேறு excel கோப்பாக இருந்தால் கோப்பின் பெயரோடு தாளின் பெயரோடு table array இப்படி வரும்(book#)sheet#!$B$2:$I$17 வரும்.ஆனால் ஒரே கோப்பின் அடுத்த தாளில் table array இழுக்கும் போது sheet#!B2:I17 என அமெரிக்க டாலர் சின்னம் இல்லாமல் வரும் எனவே நீங்கள் sheet#!B2:I17யில் நீல நிறத்தில் உள்ள table range ஆன (B2:I17 ) இதை formula barல்mouse ஆல்cover செய்து தட்டச்சு பலகையில் F4ஐ தட்டி இப்படி டாலர் சின்னத்தோடு (sheet#!$B$2:$I$17 ) மாற்ற வேண்டும் இல்லையெனில் உங்களது table array ஒவ்வொரு cellலுக்கும் மாறுபடும். சரியான விடையைப் பெற முடியாது.

மூன்றாவதாக column index num நீங்கள் உங்கள் source fileல் lookup valueவை(Hariharan படத்தின்படி) முதல் columnஆக வைத்து table array இழுத்திள்ளீர்கள் அல்லவா. இதில் lookup value உள்ள column த்தின் index no ஒன்று என எண்ணிக்கொள்ளுங்கள் அதிலிருந்து உங்களுக்கு தேவைப்படும் தகவல் உள்ள column எத்தனையாவது columnமோ அந்த எண்ணை உள்ளிடுங்கள். source fileபடத்தின் படி உங்களுக்கு தமிழ் மதிப்பெண் தேவைப்பட்டால் 2 எனவும் ஆங்கிலம் 3 கணிதம் 4 அறிவியல் 5 மொத்த மதிப்பெண்ணுக்கு 7 தேர்ச்சி நிலைக்கு 8 இப்படி உள்ளிடவேண்டும்.

இறுதியாக (range lookup) இதற்கு true எனக்கொடுத்தால் தோராயமான விடையும் false எனக்கொடுத்தால் சரியான விடையும் கிடைக்கும்.உதாரனத்துக்கு முன்பே சொன்னது போல் நீங்கள் Hariharan என்ற lookup value வுக்குதகவல் தேடும் போது soruce fileலிலும்Hariharan என இருந்தால் FALSE கொடுங்கள். மாறாக harikaran என இருந்தால் TRUE கொடுத்தால் விடை வரும். முடிந்தவரை FALSE என சரியான விடை கிடைக்குமாறு தேடவும்.நீங்கள் தேடக்கூடிய value எண்ணாக இருந்தாலும் text ஆக இருந்தாலும் FALSE இடும்போது சரியான விடையுடன் திரும்பி வரும். கீழுள்ள படத்தினைப் பார்க்கவும்.

அடுத்தது HLOOKUP
இதுவும் VLOOKUP போலவேதான்.கீழுள்ள படத்தினைப் பாருங்கள் அடுத்த columnத்தில் தகவல் தேவைப்பட்டால் vlookup பயன்படுத்துவது போல ஒரே வரிசையில் தகவல் தேவைப்படும்போது hlookup பயன்படுத்தலாம்.உதாரனத்திற்கு souce fileல் அன்பழகனுக்கு அடுத்த ஐந்தாவது பெயர் தேவைப்படுகிறது என வைத்துக் கொள்வோம் இதற்கு =hlookup(B2,(book#)sheet#!$B$2:$B$1000,5,false) என சூத்திரம் இடும்போது அன்பழகனுக்கு அடுத்த ஐந்தாம் இடத்தில் உள்ள பெயரான David வந்து விடும்.


கடைசியாக ஒரு தகவல்.

உங்களது lookupசூத்திரத்தின் விடை error ஆக வராமல் தடுக்க மெகா சூத்திரமாக இப்படி =iferror(vlookup(lookup value,table array,col.index num,(range look up)),0) எனக் கொடுத்தால் error வரும் இடங்களில் 0 என வந்துவிடும். மாறாக error வரும் இடங்களில் no என வரவேண்டும் என விரும்பினால் =iferror(vlookup(lookup value,table array,col.index num,(range look up)),"no") என சூத்திரத்தினை மாற்றி எழுதலாம். இவ்வாறு error வரும் இடங்களில் நீங்கள் விரும்பியவாறு விடையை வரவழைக்கலாம்.

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