MS EXCEL - LOOKUP, MATCH AND INDEX FUNCTIONS

LOOKUP

    IF என்ற செயல்பாட்டில் ஒரு நிபந்தனையையும் அந்த நிபந்தனை உண்மையாக இருந்தால் அல்லது நிறைவேறியிருந்தால் ஒரு வித செயலையும், நிறைவேறாவிட்டால் ஒரு வித செயலையும் செய்ய சொல்கிறோம். ஒரு நிபந்தனை நிறைவேறாமல், மற்றொரு நிபந்தனை மற்றொரு நிபந்தனை என்று போகும்போது NESTED IF ஐ செயல்படுத்துகிறோம். அப்படி நிறைவேற்றும்போது அதிக பட்சமாக ஏழு  IFகளைத்தான் பயன்படுத்த முடியும். அதற்கு மேல் போகும்போது ஒவ்வொரு ஏழு IF களுக்கும் ஒரு NESTED IF ஐ பயன்படுத்தி முடிவில் ஒவ்வொன்றையும் இணைத்து ஒன்றாய் பயன்படுத்த வேண்டும். (விவரங்களுக்கு IF என்ற FUNCTION பகுதியை காணவும்).  இப்படி பயன்படுத்தும் போது பலவித தவறுகளை ஏற்படுத்தி இருப்போம். அடைப்புக்குறிகள் இடுவதிலோ, காற்புள்ளிகள் இடுவதிலோ, நிபந்தனைகள் விதிப்பதிலோ, அல்லது AND அல்லது OR உபயோகிப்பதிலோ தவறுகள் ஏற்படலாம்.   இந்த மாதிரி தவறுகளை தவிர்க்க சில இடங்களில் LOOKUP, VLOOKUP அல்லது HLOOKUP போன்ற FUNCTION களை உபயோகிக்கலாம்.

IF FUNCTION க்கும் VLOOKUP FUNCTION க்கும் உள்ள வித்தியாசம் என்னவெனில் IF செயல்பாடு ஒரு தர்க்கரீதியான செயல்பாடு. LOOKUP ஒரு தேடல் செயல்பாடு. இது ஒரு வரம்பில் (RANGE) ஒரு மதிப்பு அல்லது நிலை கண்டுபிடிக்க ஒரு பட்டியலில்  தேடும்.

LOOKUP என்பது குறிப்பிட்ட ஒரு வரிசையிலோ (ROW) அல்லது குறிப்பிட்ட ஒரு பத்தியிலோ (COLUMN) மட்டுமே விடையை தேடி கண்டுபிடிக்கும்.

VLOOKUP என்பது ஒன்றுக்கும் மேற்பட்ட பத்திகள்(COLUMNS) மற்றும் வரிசைகள் (ROWS) கொண்ட வரம்பில் (RANGE)  தேடும். தேடக்கூடிய TABLE RANGE  நெடுக்கு (VERTICAL TABLE) வாட்டில் தொடராக இருக்கவேண்டும். (MULTY CELL RANGE). வரம்பில் பல நெடு வரிசைகள் இருந்தாலும் குறிப்பிடப்பட்ட ஒரு நெடு வரிசையிலிருந்து மதிப்பை விடையாக அளிக்கும்.

HLOOKUP என்பது ஒன்றுக்கும் மேற்பட்ட வரிசைகள் (ROWS)  மற்றும் பத்திகள் (COLUMNS) கொண்ட வரம்பில் (RANGE)  தேடும். தேடக்கூடிய TABLE RANGE  குறுக்கு  (HORIZANTAL TABLE) வாட்டில் தொடராக இருக்கவேண்டும் (MULTY CELL RANGE). வரம்பில் பல குறுக்கு வரிசைகள் இருந்தாலும் குறிப்பிடப்பட்ட ஒரு குறுக்கு வரிசையிலிருந்து மதிப்பை விடையாக அளிக்கும்.

SYNTAX

LOOKUP(VALUE, LOOKUP RANGE, [RESULT RANGE])

உதாரணத்தை 1 ஐ பார்க்கவும்.

மேலே உள்ளது பழங்களின் விலை பட்டியல். இந்த பட்டியலின்படி வாங்குபவர்களுக்கு BILL தயாரிக்கவேண்டும். ID பத்தியில் அந்த பொருளின் அடையாள எண்ணை கொடுத்தவுடன் PRODUCT என்ற பத்தியில் பழத்தின் பெயர் வரவேண்டும். ID யில் எண்ணை கொடுத்துவிட்டு PRODUCT பத்தியில் (F1 ல் ID G1 ல் )=LOOKUP(பார்க்கவேண்டிய மதிப்பு(6), பார்க்கவேண்டிய இடம்(A2:A10), அதற்கான விடை இருக்ககூடிய இடம்(B2:B10)) முதலியவைகளை கொடுத்தவுடன் அதற்கான விடை உடனே கிடைத்துவிடும்.

உதாரணம் – 2

ID 6 க்கு பழத்தின் பெயர் வந்துவிட்டது. இப்போது அதனுடைய விலை வேண்டும். H2 ல் =LOOKUP(F2,A2:A10,D2:D10) என்று தட்டச்சு செய்து ENTER ஐ அழுத்தினால் அதனுடைய விலையை காண்பிக்கும். (இங்கு கவனிக்கவேண்டியது விடை இருக்ககூடிய இடம் B2:B10 என்பதிற்கு பதில் D2:D10  என்று கொடுத்துள்ளோம் ஏனெனில் D2:D10 ல் தான் விலை விபரங்கள் உள்ளன.

LOOKUP FUNCTION  ஐ உபயோகிக்கும்போது கவனிக்க வேண்டியவைகள்

• LOOKUP மதிப்பை பார்க்க வேண்டிய RANGE ஏறு வரிசையில் ASCENDING SORT செய்யபட்டிருக்க வேண்டும், அப்படி செய்யவில்லை என்றால் தவறான விடை கிடைக்கும். (இதற்கான உதாரணம் கீழே கொடுக்கப்பபட்டுள்ளது).

•         பார்க்கவேண்டிய மதிப்பும் பார்க்கவேண்டிய விடையும் ஒரே வரிசையிலோ அல்லது பத்தியிலோ அமைந்திருக்க வேண்டும். (உதாரணம். நாம் பார்த்த மதிப்பு A6 ல் இருந்தது. அதனுடைய விடை D6 ல் இருந்தது.  இரண்டும் ஒரே வரிசையில் அமைந்திருக்கிறது. ). அத்தோடுகூட பார்க்கும் மதிப்பு இருக்கும் RANGE ம் விடை இருக்கும் RANGE ம் ஒரே அளவுள்ளதாக இருக்கவேண்டும். A1:A10  D1:D10.
• 
  
•       சிறிய எழுத்து(LOWER CASE), பெரிய எழுத்து (UPPER CASE) என்ற வேறுபாடு கிடையாது (CASE INSENSITIVE).

மற்றுமொரு உதாரணத்தையும் பார்க்கலாம்.

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

•        LOOKUP ல் தேட கொடுக்கும் மதிப்பு அங்கு இருக்கும் மதிப்புகளை விட குறைவாக இருந்தால் #N/A என்று ERROR காண்பிக்கும். (உதாரணத்தை காண்க).

Vlookup syntax

VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

இதில் தேட கொடுக்கும் மதிப்பு (lookup value) என்பது ஒரு Text ஆகவோ, ஒரு Number ஆகவோ, ஒரு Date ஆகவோ அல்லது ஒரு Cell reference ஆகவோ இருக்கலாம். ஏன் ஒரு Function மூலம் வந்த விடையாக கூட இருக்கலாம்.

TABLE ARRAY என்பது நாம் தேடச்சொல்லும் தகவல்கள் எங்கு உள்ளன என்பதை தெரிவிக்கும் குறிக்கும். VLOOKUP ல் நாம் தேடச்சொல்லும் தகவல்கள் நெடுக்கு வரிசையில்; அமைந்திருக்க வேண்டும்.

COL_INDEX_NUM என்பது நாம் கொடுத்துள்ள தகவல்களில் விடை எத்தனாவது COLUMN பத்தியில் வருகிறது என்பதாகும்.

range_LOOKUP என்பது வரக்கூடிய விடை மிகச் சரியாக இருக்க வேண்டுமா அல்லது தோராயமாக இருந்தால் போதுமா என்பதை பொறுத்தது.  மிகச் சரியாக இருக்க வேண்டும் என்றால் TRUE என்றும் தோராயமாக இருந்தால் போதும் என்றால் FALSE என்றும் கொடுக்கவேண்டும். (௦ அல்லது 1)

கீழுள்ள உதாரணத்தில் F2 ல் ID 7 என்று (எண்ணாக) கொடுக்கப்பட்டுள்ளது. இது தேடக்கூடிய என்னாகும்.  இதை எங்கே தேடவேண்டும் என்பது A2:D10  என்று கொடுத்துள்ளோம். A2 லிருந்து D10 க்குள் தேடவேண்டும். அதனுடைய விடை 2 வது பத்தியில் அமைந்துள்ளது என்று குறிப்பிட்டுள்ளோம்.  அதனால்  GRAPES என்பதை விடையாக கொடுத்துள்ளது.

தேட கொடுக்கும் மதிப்பு TEXT உதாரணம்.

தேட கொடுக்கும் மதிப்பு DATE உதாரணம்.

VLOOKUP ஐ மற்றுமொரு உதாரணத்தின் மூலம் காண்போம்.

மேலே கண்ட உதாரணம் IF FUNCTION லும் பார்த்துள்ளோம். இதற்கு 7 IF STATEMENTS உபயோகிக்க வேண்டியிருக்கும். அதற்கு பதில் VLOOKUP  அல்லது HLOOKUP மூலம் சுலபமாக HRA வை கண்டுபிடிக்கலாம்.

B35 ல் ஊதிய விவரம் உள்ளது. இதற்குறிய HRA விவரங்கள் E34 லிருந்து G50 வரை உள்ளன.  இவற்றில் முதல் பத்தியில் உள்ளது கீழ் மட்ட எல்லை (LOWER BOUNDARY FIGURE.). இரண்டாவது பத்தியில் உள்ளது மேல் மட்ட எல்லை (HIGHER BOUNDARY FIGURE.). மூன்றாவது பத்தியில் உள்ளது ஒவ்வொரு RANGE க்கும் உள்ள HRA வாகும். 

ஆதலால் VLOOKUP FUNCTION ல் LOOKUP VALUE ஆகா B35 யையும், TABLE ARRAY வாக E34 முதல் G50 வரையும், மூன்றாவது பத்தியில் HRA விவரம் உள்ளதால் COL_INDEX_NO ஆக 3 என்பதையும் கொடுக்கிறோம். இதில் நாம் தேடும் விவரம் மிக சரியாக இருக்க வேண்டும் என்பதால் RANGE_LOOKUP என்பதற்கு 0 என்று கொடுக்க வேண்டும். 0 என்பதால் கொடுக்காவிட்டாலும் 0 (TRUE) என்பதாக எடுத்துக்கொள்ளும். (மிகச் சரியாக இருக்க வேண்டுமென்றால் TABLE ARRAY ஏறுமுகமாக (ASCENDING ORDER SORT) வரிசைப்படுத்தி இருக்கவேண்டும். TABLE ARRAY RANGE என்பது ABSOLUTE OR CONSTANT REFERENCE ஆகும். ஆதலால் E34:G50 என்பதில் பத்திக்கு முன்பாகவும் வரிசைக்கு முன்பாகவும் $ போடவேண்டும்.  அதாவது $E$34:$G$50 என்று குறிப்பிடவேண்டும்.

நம்முடைய TABLE ARRAY RANGE VERTICAL (நெடுக்கு வரிசையில்) அமைந்திருப்பதால் VLOOKUP என்ற FUNCTION ஐ உபயோகப்படுத்துகிறோம். 

நம்முடைய TABLE ARRAY RANGE HORIZANTAL ஆக (குறுக்கு  வரிசையில்) அமைந்திருந்தால்  VLOOKUP என்ற FUNCTION க்கு பதிலாக HLOOKUP என்ற  FUNCTION உபயோகப்படுத்தவேண்டும் (உதாரணத்தை காண்க ) 

LOOKUP TABLE ARRAY யை வேறு ஒரு SHEET ல் கூட சேமித்து வைக்கலாம். அப்படி வேறொரு SHEET ல் இருந்தால் TABLE ARRAY REFERECE குறிப்பிடும்போது SHEET னுடைய பெயரையும் சேர்க்க வேண்டும்.  அதற்க்கு சுலபமான வழி என்னவெனில் TABLE ARRAY REFERECE ஐ தட்டச்ச்சு செய்வதற்கு பதில் அந்த SHEET ற்கு சென்று செல்களை தேர்ந்தெடுப்பது சிறந்தது.

MATCH()

     இந்த செயல்பாடு (FUNCTION) ஒரு பத்தியிலோ (COLUMN) அல்லது ஒரு வரிசையிலோ (ROW) நாம் தேடக்கூடிய சொல் அல்லது எண் இருக்கிறதா, இருந்தால் அது நாம் கொடுத்துள்ள வரிசையில் எத்தனாவதாக உள்ளது என்பதை காட்டும். இதனுடைய SYNTAX =MATCH(LOOKUP VALUE, LOKKUP ARRAY, MATCH VALUE). இதில் முதலில் நாம் எந்த சொல்லை ஒப்பிடுகிறோமோ அதை கொடுக்க வேண்டும், இரண்டாவதாக இதை எந்த இடத்தில இருக்கும் தகவலோடு ஒப்பிடவேண்டும் என்பதையும், மூன்றாவதாக ஒப்பிடுதலில் எந்த முறையை கடைபிடிக்கவேண்டும் என்பதையும் கொடுக்கவேண்டும். முறைகளில் 1, 0, -1 என்று மூன்று முறைகள் உள்ளன.

1.    இது நாம் ஒப்பிடும் மதிப்புக்கு சமமாக இருப்பதையோ அல்லது அதற்கு அடுத்த அதிக  மதிப்பிட்டையோ காண்பிக்கும். (தகவல்கள் ஏறு வரிசையில் வரிசைபடுத்தி இருக்கவேண்டும்.

௦.    இது நாம் ஒப்பிடும் மதிப்புக்கு சமமாக இருப்பதை காண்பிக்கும். (தகவல்கள் வரிசைபடுத்தி இருக்கவேண்டியதில்லை.

-1    இது நாம் ஒப்பிடும் மதிப்புக்கு சமமாக இருப்பதையோ அல்லது அதற்கு அடுத்த குறைந்த மதிப்பிட்டையோ காண்பிக்கும். (தகவல்கள் இறங்கு வரிசையில் வரிசைபடுத்தி இருக்கவேண்டும்.

இந்த செயல்பாட்டில் எழுத்து வடிவுணர்வு (CASE SENSITIVE) கிடையாது.

நம்முடைய ஒப்பீடு கிடைக்கவில்லை என்றால் #N/A ERROR (தவறு) காண்பிக்கும்.

நம்முடைய ஒப்பீடு சொல்லாயிருந்தால் (TEXT) * ? முதலிய WILDCARD எழுத்துக்களை பயன்படுத்தலாம். 

                                                        தொடரும்..........

MS EXCEL - TIME CALCULATION

TIME CALCULATION

எப்படி தேதியை பதிவு செய்ய DATE(YEAR,MONTH,DAY)  என்ற செயல்பாட்டை பயன்படுத்த முடியுமோ அதேபோல நேரத்தை பதிவு செய்ய TIME(HOUR,MINUTE,SECONDS) என்ற செயல்பாட்டை (FUNCTION) பயன்படுத்தலாம்.  நேரடியாகவும் பதிவு செய்யலாம். உம். =TIME(14,26,5) விடை 14:26

தேதி பதிவு செய்யும்போது இன்றைய தேதியை ஒரு செல்லில் பதிவு செய்ய =TODAY() என்று தட்டச்சு செய்யவேண்டும்.  இன்றைய தேதியுடன் நேரமும் சேர்ந்து வரவேண்டும் என்றால் =NOW().என்று தட்டச்சு செய்யவேண்டும். நேரம் மட்டும் வேண்டுமென்றால் =HOUR(NOW()) என்றும் நிமிடங்கள் மட்டும் வேண்டுமென்றால் =MINUTE(NOW()) என்றும் தற்போதுள்ள நேரம் மட்டும் வேண்டுமென்றால் =TIME(HOUR(NOW()),MINUTE(NOW()),SECOND(NOW())) என்றும் தட்டச்சு செய்யவேண்டும்.

எக்ஸ்செல்லில் நேரடியாக நேரங்களை பதிவு செய்யவும் அதன் அடிப்படையில் சில கணக்கீடுகளை செய்யவும் முடியும்.  நேரங்களை பதிவு செய்யும்போது மணிக்கும் நிமிடங்களுக்கும் இடையில் : (colon) இடவேண்டும். உம். 1:30, 21:30, 12:50. மணிக்கும் நிமிடங்களுக்கும் இடையில் புள்ளி வைப்பது தவறான முறையாகும் 21.30, 12.50.  நேரத்தை பதிவு செய்ய 24  மணிநேர முறையையோ அல்லது AM PM முறையையோ பயன்படுத்தலாம். AM PM முறையை பயன்படுத்தும் போது AM / PM க்கு முன்னால்  ஒரு இடைவெளி விடவேண்டும். (உம். 9:30 PM, 12:50 PM, 10:00 AM). இரண்டு நேரங்களுக்கு இடையிலான நேரம் எவ்வளவு என்று கணக்கிடுதலை கீழே காணலாம். (ஒரு நேரத்திலிருந்து மற்றொரு நேரத்தை கழிக்க)

நேரங்களை ஒன்றோடொன்று கூட்டவும் முடியும்.

இப்படி கூட்டும்போது கூட்டுத்தொகை 24  மணி நேரத்திற்குள் வரும்போது சரியாக இருக்கும்.  24  மணி நேரத்தை தாண்டும்போது கூட்டுத்தொகையை சரியாக காண்பிக்காது.  ஆதலால் கூட்டுத்தொகை வரும் செல்லை FORMAT செய்யவேண்டும்.

இதனுடைய கூட்டுத்தொகை 33:15 என்று வரவேண்டும்.  செல்லை FORMAT செய்ய செல்லில் கர்சரை வைத்து RIGHT CLICK செய்யவும்.  வரும் மெனுவில் FORMAT CELLS என்பதை தேர்ந்தெடுக்கவும். இப்போது FORMAT CELL க்கான உரையாடல் பெட்டி தோன்றும் அதில் NUMBER என்ற தலைப்பின் கீழ் உள்ள பெட்டியில் CUSTOM என்பதை CLICK செய்யவும்.  வரும் பெட்டியில் TYPE என்ற தலைப்பின் கீழ் உள்ளத்தில் சதுர அடைப்புக்குறிக்குள் HH என்று தட்டச்சு செய்து : (colon) க்கு பக்கத்தில் MM என்று தட்டச்சு செய்து OK கொடுத்து வெளியேறவும். [HH]:MM 

சரியான விடை

ஒரு தொழிற்சாலையில் பணியாளர்களின் வாராந்திர வேலை நேரத்தை கணக்கிடுதலை கீழே உள்ள உதாரணத்தின் மூலம் காணலாம்.

ஒரு நிகழ்ச்சிக்கு வந்துள்ள நபர்களின் நேரம்

8:40

8:05

6:35

9:05

7:30

8:30

10:30

8:45

9:15

9:10

8:15

மேற்கண்டவாறு அமைந்துள்ளதாக கொள்வோம். இவர்களில் குறிப்பிட்ட நேரத்திற்குள் வந்தவர்கள் எத்தனை பேர் கண்டுபிடிக்க COUNTIF என்ற FUNCTION ஐ உபயோகித்து கண்டுபிடிக்கலாம்.  =COUNTIF(A1:A11,">=08:30")-COUNTIF(A1:A11,">09:00") அல்லது   =COUNTIF(A1:A11,">="&TIME(8,30,0))-COUNTIF(A1:A11,">"&TIME(9,0,0)). (இவற்றில் முதல் பகுதியில் 8:30 மேல் வந்தவர்கள் எத்தனை பேர் என்று கண்டுபிடித்து அதை 09:00 மேல் வந்தவர்கள் எண்ணிக்கையிலிருந்து கழிக்கிறோம்.)

     முதலில் உள்ள செயல்பாட்டில் நேரத்தை நேரடியாக தட்டச்சு செய்துள்ளோம். இரண்டாவதில் நேரத்தை TIME FUNCTION மூலம் கொடுத்துள்ளோம். கீழுள்ள உதாரணத்தில் பல முறைகள் விளக்கப்பட்டுள்ளன.