MS EXCEL - ROUND, ROUNDUP, ROUNDDOWN, CEILING, FLOOR, IF, LARGE AND SMALL FUNCTIONS

ROUND() 

            இந்த செயல்பாடு (FUNCTION) ஒரு செல்லில் உள்ள தொகையை எத்தனை தசம ஸ்தானத்திற்கு சுருக்க வேண்டுமோ அதை செய்யும். இதனுடைய SYNTAX =ROUND(VALUE, NO.OF DECIMAL PLACES).

ROUNDUP() 

     இந்த செயல்பாடு (FUNCTION) ஒரு செல்லில் உள்ள தொகையை ஒரு இலக்கம் மேல் நோக்கி நகர்த்தும். SYNTAX =ROUNDUP(VALUE, -NO.OF DIGITS ). உம். CELL A1 ல் 125366.34 இருப்பதாக கொள்வோம். இதை ஒரு இலக்கம் மேல் நோக்கி நகர்த்த B1 ல் =ROUNDUP(A1,-1) என்று கொடுத்தோமானால் B1 ல் 125370 என்று வரும். =ROUNDUP(A1,-2) என்று கொடுத்தோமானால் B1 ல் 125400 என்று வரும்.

ROUNDDOWN() 

     இந்த செயல்பாடு (FUNCTION) ஒரு செல்லில் உள்ள தொகையை ஒரு இலக்கம் கீழ்  நோக்கி நகர்த்தும். SYNTAX =ROUNDDOWN(VALUE,-NO.OF DIGITS ). உம். CELL A1 ல் 125366.34 இருப்பதாக கொள்வோம். இதை ஒரு இலக்கம் கீழ் நோக்கி நகர்த்த B1 ல் =ROUNDDOWN(A1,-1) என்று கொடுத்தொமானால் B1 ல் 125360 என்று வரும். =ROUNDDOWN(A1,-2) என்று கொடுத்தொமானால் B1 ல் 125300 என்று வரும்.

CEILING()

இந்த செயல்பாடு (FUNCTION) ஒரு செல்லில் உள்ள எண்ணை குறிப்பிட்டுள்ள அளவுக்கு உயர்த்தும். SYNTAX =CEILING(NUMBER,SIGNIFICANCE). =CEILING(எண், உயர்த்த வேண்டிய அளவு / வகுபட வேண்டிய அளவு) . ஒரு எண்ணை குறிப்பிட்ட அளவால் வகுத்துப் பார்க்கும்.  அந்த எண் வகுபட்டால் அந்த எண்ணில் எந்த மாற்றமும் இருக்காது. வகுபடவில்லை எனில் அடுத்து வகுபடக்குடிய உயர்ந்த எண்ணாக மாற்றும். உம்.

FLOOR()

இந்த செயல்பாடு (FUNCTION) ஒரு செல்லில் உள்ள எண்ணை குறிப்பிட்டுள்ள அளவுக்கு குறைக்கும் . SYNTAX =FLOOR(NUMBER, SIGNIFICANCE). =CEILING(எண், குறைக்க வேண்டிய அளவு / வகுபட வேண்டிய அளவு) . ஒரு எண்ணை குறிப்பிட்ட அளவால் வகுத்துப் பார்க்கும்.  அந்த எண் வகுபட்டால் அந்த எண்ணில் எந்த மாற்றமும் இருக்காது. வகுபடவில்லை எனில் அடுத்து வகுபடக்குடிய குறைந்த  எண்ணாக மாற்றும். உம்.

IF()

தர்க்க ரீதியான முடிவெடுக்க இந்த செயல்பாடு உதவும்.  IF என்று சொல்லும்போதே அதில் ஒரு நிபந்தனை தொக்கி நிற்பது நன்றாக தெரியும். இதனுடைய SYNTAX =IF(CONDITION,TRUE,FALSE). இந்த FUNCTION ARGUMENT ல் மூன்று பகுதிகள் உள்ளன. முதல் பகுதி CONDITION ஆகும். இரண்டாவது அந்த CONDITION நிறைவேறினால் என்ன செய்ய வேண்டும் அல்லது என்ன  RESULT, மூன்றாவது அந்த CONDITION நிறைவேறாவிட்டால் என்ன செய்ய வேண்டும் எனபது. இதை கீழே காணும் மாணவர்களின் மதிப்பெண் பட்டியல் உதாரணத்தின் மூலம் காணலாம்.

B பத்தியில் தமிழ் மதிப்பெண் உள்ளது. தமிழில் 40 அல்லது அதற்கு மேல் எடுத்தவர்கள் PASS என்று குறிக்க I2 ல் கர்சரை வைத்து =IF(B4>=40,"PASS","FAIL") தட்டச்சு செய்து ENTER KEY ஐ அழுத்தினால் இதனுடைய விடை கிடைக்கும். அந்த FUNCTION ஐ மற்ற செல்களில் DRAG செய்தால் மற்ற செல்களிலும் ரிசல்ட் கிடைக்கும்.

     ரிசல்ட் போடும்போது ஒரு பாடப்பிரிவை மட்டும் பார்த்து போடுவது கிடையாது.  எல்லாவற்றையும் பார்க்க வேண்டுமல்லவா? நாம் பேசும்போது IF TAMIL GREATER THEN 40 AND ENGLISH GREATER THAN 40 AND COMPUTER SCIENCE GREATER THEN 40 …… இப்படி சொல்லிக்கொண்டே போகலாம்.  ஆனால் எக்ஸ்செல்லில் ஒரே ஒரு AND க்கு மேல் போட இடம் இல்லை.  ஆகையால் AND என்பதை முதலில் போட வேண்டும்.  எங்கெல்லாம் , (கமா) வருகிறதோ அங்கெல்லாம் AND என்பதை எடுத்துக்கொள்ளும். அதனுடைய SYNTAX ஐ இப்போது பார்ப்போம்.  =IF(AND(B4>=40,C4>=40,D4>=40,E4>=40,F4>=40,G4>=40),"PASS","FAIL") AND க்கு பிறகு அடைப்புக்குறிக்குள்  ( ) க்குள் ஒவ்வொரு CONDITION பக்கத்திலும் . (காற்புள்ளி) இடவேண்டும்.

NESTED IF

இப்போது வேறு விதமான உதாரணம் ஒன்றை பார்க்கலாம். நமது ஊதியத்தில் HRA என்று ஒன்று உள்ளதல்லவா? HRA கீழ் வருமாறு இருப்பதாக கொள்வோம்.

IF FUNCTION B2 ல்  =IF(AND(A2>0,A2<=5000),500,IF(AND(A2>5000,A2<=10000),1000,IF(AND(A2>10000,A2<=15000),1500,IF(AND(A2>15000,A2<=20000),2000,2500)))) என்று தட்டச்சு செய்து ENTER KEY ஐ அழுத்தினால் 1000 என்று விடை கிடைக்கும்.  இந்த FUNCTION ஐ மற்ற செல்களிலும் COPY செய்தால் எல்லாவற்றிற்கும் HRA ஐ போட்டுவிடும்.

சரி இது எப்படி வேலை செய்கிறது. நமக்கு BLANK செல்லிற்கு HRA போடக்கூடாது. ஆதலால் B2 ௦ க்கு மேல் இருக்கவேண்டும், ஆனால் 5000 வரைதான் இருக்கவேண்டும்.  B2 > ௦ AND B2 <=5000 என்று எழுதவேண்டும் ஆனால் எக்ஸ்செலில் AND ஐ நடுவில் போடமுடியாது. ஆதலால் AND(A2>0,A2<=5000) என்று எழுதுகிறோம். இந்த நிபந்தனைக்கு உட்பட்டு இருந்தால் 500 ரூபாய் இல்லையெனில் எவ்வளவு ?  ஒரே ஒரு நிபந்தனையாய் இருந்தால் அதுவும் எவ்வளவு என்று சொல்லிவிடலாம்.  ஆனால் மேலே உள்ள அட்டவனையை பார்த்தால் ஐந்து விதமான SLAB கள் உள்ளன. ஆகவே ஒரு நிபந்தனைக்குள் வரவில்லை என்றால் மற்றொரு IF CONDITION ஐ ஆரம்க்பிக்கிறோம். இது மாதிரி எத்தனை CONDITIONS இருக்கிறதோ அத்தனை IF களை போட்டு SYNTAX ஐ எழுத வேண்டும்.  முடிவில் எத்தனை IF கள் போட்டோமோ அத்தனைக்கும் ) அத்தனை அடைப்பு குறிகள் போட்டு முடிக்க வேண்டும்.

இம்மாதிரி ஒரு IF க்குள் மற்றொரு IF போட்டு  பின்னல் போல் எழுதும் முறைக்கு NESTED IF என்று பெயர்.

ஐந்து விதமான SLAB கள் மட்டும் இருந்ததால் எழுதி சமாளித்தாகிவிட்டது. நமக்கு உண்மையிலேயே 17 விதமான  SLAB கள் அல்லவா இருக்கின்றன. அத்தனைக்கும் எழுதுவதாயிருந்தால் IF STATEMENT =IF(AND(B3>0,B3<=5299),500,IF(AND(B3>5299,B3<=6699),560,IF(AND

(B3>6699,B3<=8189),680,IF(AND(B3>8189,B3<=9299),800,IF(AND(B3>9299,B3<=10599),1000,IF(AND(B3>10599,B3<=11899),1200,IF(AND(B3>11899,B3<=13769),1400,IF(AND(B3>13769,B3<=14509),1600,IF(AND(B3>14509,B3<=15999),1800,IF(AND(B3>15999,B3<=17299),2000,IF(AND(B3>17299,B3<=19529),2200,IF(AND(B3>19529,B3<=20089),2400,IF(AND

(B3>20089,B3<=21019),2600,IF(AND(B3>21019,B3<=21579),2800,IF(AND(B3>21579,B3<=22139),2900,IF(AND(B3>22139,B3<=24999),3000,3200,))))))))))))))))) என்று அமையும்.  ஆனால் எக்ஸ்செல் 7 NESTED IF களுக்கு மேல் ஏற்றுகொள்ளாது . கீழே காண்பித்துள்ள ERROR MESSAGE ஐ காட்டும்.

நான் விடுவதாயில்லை. எப்படியும் IF STATEMENT எழுதியேதான் தீருவேன் என்று அடம் பிடிப்பவராயிருந்தால் ஏழு IF களுக்கு மேல் ஒரே NESTED IF ல் எழுதாமல் பின் வருமாறு எழுதலாம்.

=IF(AND(B3>0,B3<=5299),500,IF(AND(B3>5299,B3<=6699),560,IF(AND(B3>6699,B3<=8189),680,IF(AND(B3>8189,B3<=9299),800,IF(AND(B3>9299,B3<=10599),1000,IF(AND(B3>10599,B3<=11899),1200,))))))&IF(AND(B3>=11900,B3<=13769),1400,IF(AND(B3>13770,B3<=14509),1600,IF(AND(B3>14510,B3<=15999),1800,IF(AND(B3>16000,B3<=17299),2000,IF(AND(B3>17300,B3<=19529),2200,IF(AND(B3>19530,B3<=20089),2400,))))))&IF(AND(B3>=20090,B3<=21019),2600,IF(AND(B3>=21019,B3<=21579),2800,IF(AND(B3>=21580,B3<=22139),2900,IF(AND(B3>=22139,B3<=24999),3000,IF(B3>24999,3200,)))))

இதில் மூன்று STATEMENT கள் உள்ளன.

1. IF(AND(B3>0,B3<=5299),500,IF(AND(B3>5299,B3<=6699),560,IF(AND(B3>6699,B3<=8189),680,IF(AND(B3>8189,B3<=9299),800,IF(AND(B3>9299,B3<=10599),1000,IF(AND(B3>10599,B3<=11899),1200,))))))
2.  IF(AND(B3>=11900,B3<=13769),1400,IF(AND(B3>13770,B3<=14509),1600,IF(AND(B3>14510,B3<=15999),1800,IF(AND(B3>16000,B3<=17299),2000,IF(AND(B3>17300,B3<=19529),2200,IF(AND(B3>19530,B3<=20089),2400,))))))
3. IF(AND(B3>=20090,B3<=21019),2600,IF(AND(B3>=21019,B3<=21579),2800,IF(AND(B3>=21580,B3<=22139),2900,IF(AND(B3>=22139,B3<=24999),3000,IF(B3>24999,3200,)))))

இந்த மூன்றையும் & குறியீடை நடுவில் அமைத்து ஒன்றோடொன்று சேர்த்து அமைத்தால் சரியாக அமையும்.(& ஒன்றோடொன்றை இணைப்பதற்கு உபயோகிப்பது -  CONCATANATION). இதனுடைய தொடர்ச்சியை LOOKUP FUNCTION என்ற தலைப்பில் பார்க்கவும்.

LARGE()

     எஸ்செல்லில் MAX, MIN FUNCTIONS கொடுத்துள்ள எண்களில் எது அதிக மதிப்பு கொண்டது, எது குறைவான மதிப்பு கொண்டது என்பதை கண்டறிய உதவும். அதுபோல கொடுத்துள்ள எண்களில் அதிக மதிப்பில்  ஐந்தாவது இடத்தை எந்த எண்  பிடிக்கிறது என்பதை கண்டறிய LARGE என்ற FUNCTION உபயோகமாகும். மேலுள்ள உதாரணத்தில் COMPUTER SCIENCE ல் ஐந்தாவது இடத்தை பிடித்தவர் யார் என்று கண்டறிய =LARGE(E4:E13,5) என்று கொடுத்தால் விடை 88 என்று வரும்.  

SMALL()

      அதேபோல் கொடுத்துள்ள எண்களில் எது குறைவான மதிப்பு கொண்டது என்பதை கண்டறிய SMALL என்ற FUNCTION உபயோகமாகும். மேலுள்ள உதாரணத்தில் COMPUTER SCIENCE ல் குறைந்த மதிப்பில் இரண்டாவது இடத்தை பிடித்தவர் யார் என்று கண்டறிய =SMALL(E4:E13,2) என்று கொடுத்தால் விடை 70 என்று வரும். (MIN=66,NEXT UP 70).



       கீழே  கொடுத்துள்ள EXCEL SHEET ல் H என்ற பத்தியில் மொத்த மதிப்பெண்களும் J என்ற பத்தியில் RESULT ம் கொடுக்கப்பட்டுள்ளது. இந்த இரண்டு பத்திகளில் உள்ள விவரங்களை IF மற்றும் LARGE FUNCTION ஐ பயன்படுத்தி முதலில் வந்தவருக்கு GOLD ம் இரண்டாவது உள்ளவருக்கு SILVAR ம் மூன்றாவது வந்தவருக்கு BRONZE பதக்கங்களும் வழங்க கீழே கண்டுள்ளவாறு FUNCTION அமைய வேண்டும் 

=IF(AND(J4="pass",H4=LARGE($H$4:$H$13,1)),"gold",IF(AND(J4="pass",H4=LARGE($H$4:$H$13,2)),"Silver",IF(AND(J4="pass",H4=LARGE($H$4:$H$13,3)),"bronze","")))

மேலே காட்டியுள்ள FUNCTION ல் $ குறியீடு உபயோகிக்கப்பட்டுள்ளதை கவணித்தீர்களா ? WORD ல் ஒரு TEXT ஐ ஒரு இடத்திலிருந்து நகலெடுத்து மற்றொரு இடத்தில் ஓட்டலாம். அதேபோல EXCEL லிலும் எழுத்துக்களையோ  அல்லது எண்களையோ ஒரு செல்லிலிருந்து மற்றொரு செல்லிற்கு நகலெடுத்தது ஓட்டலாம். ஒரு FUNCTION யோ அல்லது FORMULA வையோ ஒரு ஒரு செல்லிலிருந்து மற்றொரு செல்லிற்கு நகலெடுத்தது ஓட்டும்போது அதிலிருக்கும் ரேஞ்சு அல்லது CELL ADDRESS ஓட்டும் இடத்திற்கு தகுந்தாற்போல் மாற்றிக்கொள்ளும். இதை ஒரு தகுந்த உதாரணத்தின் மூலம் பார்ப்போம்.

  A, B AND C யின் கூட்டுத்தொகை B5 ல் உள்ளது. இந்த கூட்டுத்தொகைக்கு A, B மற்றும் C யின் சதவிகிதம் என்ன என்று கண்டுபிடிக்க முதலில்  C2 ல் (B2/B5)*100 என்று தட்டச்சு செய்து ENTER KEY ஐ அழுத்த்தினால் 18.6942 என்று வரும். இந்த FORMULA வை அதற்கு கீழே உள்ள செல்களில் COPY செய்தால் #DIV / 0 என்று வரும். காரணம் என்னவென்றால் ஒரு செல்லிலிருந்து மற்றொரு செல்லிற்கு நகலெடுக்கும் போது B2/B5 என்பது B3/B6 என்று தானாகவே மாறிவிடும்.   

      மேலே  காணப்படும் உதாரணத்தில் B2, B5 என்ற இரண்டு REFERENCE கள் இருக்கின்றன. இதில் B2 B3 என்பவைகள் மாறவேண்டியவைகள். ஆனால் B5 என்பது எல்லா  செல்லிலுமே மாறக்கூடாதவை.  ஆகவே மாறவேண்டிய REFERENCE க்கு RELATIVE REFERENCE என்று பெயர்.  மாறக்கூடாத REFERENCE க்கு ABSOLUTE  அல்லது CONSTANT  REFERENCE என்று பெயர். ஒரு REFERENCE ஐ ABSOLUTE REFERENCE ஆக மாற்ற COLUMN (பத்தி B ) த்திற்கு முன்னாலும் ROW (வரிசை 5 )க்கு முன்னாலும் $ என்ற குறியீட்டை போடவேண்டும்.  COLUMN முன்னால்  போடுவதால் COL. REF. மாறாது. அதுபோல் ROW  க்கு முன்னால் $ போட்டால் ROW RFERENCE மாறாது.  எனவே எப்போது ஒரு FORMULA / FUNCTION ஐ நகலெடுத்து ஓட்ட வேண்டியுள்ளதோ அப்போது அதில் ABSOLUTE (மாறாத ) REFERENCE எதாவது உள்ளதா என்று கவனித்து அதை மாற்றிய பிறகே நகலெடுத்து ஒட்டவேண்டும். ஒவ்வொரு இடத்திலும் தனித்தனியாக $ போடுவதிற்கு பதில் COL.தையும் ROW வையும் தேர்வு செய்து கொண்டு ஒரு தடவை F4 என்ற FUNCTION KEY ஐ அழுத்தவும்.                                                                                                                                                                         தொடரும்.......

.