EXCELL சூத்திரங்கள்-TRANSPOSE

EXCELLலில் TRANSPOSE சூத்திரத்தின் பயன்பாடு பற்றி இந்த பதிவு

EXCELLலில் ENCODING செய்யும்போது COLUMN WISE TYPE அடிப்பது மிகவும் சுலபம். ஒவ்வொரு ENCODING முடிந்ததும் எண்டர் பட்டனைத் தட்டினால் சுலபமாக டைப் செய்யலாம். அதேபோல் ROW WISE டைப் அடிக்க TAB பட்டனைத்தட்டி மிக சுலபமாக TYPE செய்யலாம்.

ஆனால் ஏற்கனவே COLUMN WISE TYPE செய்த தகவல்களை ROW WISE மாற்ற விரும்பினால் சிறிது கடினமே. ஒவ்வொரு வேல்யூவாகப் பார்த்து டைப் அடிப்பதற்குள் தாவு தீர்ந்து விடும். இதற்கெனவே EXCELLலில் ஒரு எளிமையான சூத்திரம் உள்ளது அதுதான் TRANSPOSE.

உதாரண்த்திற்கு கீழுள்ள படத்தினைப் பாருங்கள். சனவரி முதல் டிசம்பர் வரை உள்ள மாதங்கள் COLUMN WISE ல் TYPE செய்யப்பட்டுள்ளது. இதை ROW WISE மாற்ற விரும்பினால்

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

பின் மேற் கண்ட படத்தில் காட்டியுள்ளபடி ROW WISE ஆக CELL களை செலக்ட் செய்ய வேண்டும். இதில் கவணிக்கப்பட வேண்டிய ஒன்று. COLUMN WISE ல் 12 வேல்யூவை டிராக் செய்திருந்தால் ROW WISEலும் 12 CELLகள் டிராக் செய்ய வேண்டும். பின் தட்டச்சுப் பலகையில் F2 டைப் செய்து பின் SHIFT+CONTROL+ENTER செய்தால் உங்கள் COLUMN WISE வேல்யூ ROW WISEக்கு மாற்றப்பட்டிருக்கும்.

இனி உங்கள் COLUMN WISE தகவல்கள் தேவையில்லை எனக் கருதினால் சூத்திரத்தின்படி ROW WISEல் வந்த தகவல்களை செலக்ட் செய்து copy /right click/paste special/values என தேர்வு செய்ய வேண்டும். பின் COLUMN WISE தகவல்களை delete செய்து கொள்ளலாம்.

அரிய நாள் காட்டி

நண்பர்களே

Excel சூத்திரங்கள் பற்றி நான் எழுதி வரும் பதிவுகளைப் படித்திருப்பீர்கள்.

அந்த வகையில் Excel சூத்திரங்களின் சிறப்பைச் சொல்லும் அரிய நாள் காட்டி ஒன்றினை எனது நண்பர் ஒருவர் வழங்கினார்.





நாட்காட்டியின் சிறப்பு என்ன என்றால் 1900 வருடம் முதல் 2078 வருடம் வரைக்கும் இந்த நாட்காட்டியினைப் பயன்படுத்தலாம். முழுவதும் சூத்திரங்களால் இது உருவாக்கப்பட்டது.





நீங்கள் செய்ய வேண்டியதெல்லாம் இதுதான்.



1.முதலில் இங்கு சென்று கோப்பினைத் தரவிறக்கம் செய்து கொள்ளுங்கள்.

2.தரவிறக்கத் தளத்தில் கடவுச்சொல் பெட்டியில் nagen என உள்ளிடுங்கள்.

3.தரவிறக்கம் செய்த கோப்பினைத் திறந்து மேல்புறம் உள்ள Enter year for calendar என்பதற்கு எதிரே உள்ள பச்சை நிற பெட்டியில் உங்களுக்கு வேண்டிய வருடத்தினை உள்ளிடுங்கள்.



அவ்வளவுதான் ஆயுளுக்கும் நாட்காட்டி தேவையில்லை.

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 வரும் இடங்களில் நீங்கள் விரும்பியவாறு விடையை வரவழைக்கலாம்.

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

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

வணக்கம் நண்பர்களே கடந்த பதிவில் LEFT,RIGHT,MID இந்த சூத்திரங்களைப் பற்றிப் பார்த்தோம். இந்த சூத்திரங்கள் பொதுவாக ஒரு cellலில் இருக்கும் charactors களை பிரிக்க உதவுகிறது. அதுபோல பல்வேறு cellகளில் உள்ள charactorsகளை இணைக்க உதவும் சில சூத்திரங்களைப் பற்றி இந்த பதிவில் பார்ப்போம்.

முதலில் ஒரு எளிதான வழி &
=cell1&cell2&cell3& ........என்று &இதை பயன்படுத்தி நம் விருப்பம் போல இணைத்துக் கொண்டே போகலாம்.கீழுள்ள படத்தை பாருங்கள்.

மற்றொரு எளிய வழி CONCATENATE

=CONCATENATE(cell1,cell2,cell3,........) இப்படி ஒவ்வொரு cell க்கு இடையே கமா , வைப்போட்டு இணைத்துக் கொண்டு போகலாம். கீழுள்ள படத்தினைப் பாருங்கள்.


இப்படி Cell களை இணைத்துக்கொண்டு போகும் போது அதோடு ஏதாவது ஒரு பொதுவான text ஐ இணைக்க வேண்டுமெனில் =Concatenate(cell1,cell2,"text") என இணைக்கலாம். இதில் முக்கியமான விபரம் என்னவென்றால் நீங்கள் இணைக்க வேண்டிய text ஐ மேற்குறியீட்டு கமாவுக்குள் "......." மட்டும்தான் இட வேண்டும் தவறினால் #NAME? என error வரும். கீழுள்ள படத்தினைப் பாருங்கள்.

CONCATENATEஐ பயன்படுத்தி cellகளை மட்டும்தான் இணைக்கலாம் என்றில்லை.வேறு பல சூத்திரங்களையும் இணைக்கலாம். உதாரனத்திற்கு 563535 எனும் ஒரு எண் ஒரு cellஇல் உள்ளதாக கருதுவோம்.இதற்கு முன்னால் 0000 என நான்கு பூஜ்யங்களை இணைக்க வேண்டுமானால் =concatenate("0000",cell1) என இணைக்கலாம். மாற்றுவழியாக =REPT(0,4)எனும் சூத்திரம் மூலம் 0000 என விடையைப் பெற்று அதனுடன் =concatenate(REPT(0,4),cell1) இந்த சூத்திரம் மூலமும் இணைக்கலாம். கீழுள்ள படத்தினைப் பாருங்கள்.

அடுத்து REPLACE பயன்படுத்தி இணைத்தல். அதற்கு முன்னால் REPLACE என்றால் என்ன எனப் பார்ப்போம். ஒரு cellல் உள்ள சில குறிப்பிட்ட charactors களை மாற்றி அதற்குப் பதிலாக வேறு charactors களை இணைக்க வேண்டும் என நினைக்கிறீர்கள். அதற்கு எளிய வழி REPLACE.

உதாரணத்திற்கு இந்த வாக்கியத்தை கவனியுங்கள்.

Cat Jumbed on the floor இதை மாற்றி Cat Jumbing on the floor என எழுத வேண்டும் என கருதினால் ed ஐ மாற்றி அதற்கு பதிலாக ing சேர்த்தால் மாறும்.சூத்திரத்தில் =Replace(old text,start no,no of charactors,new text ) என வரும்.இதில் old text என்பது உங்கள் பழைய text இருக்கும் cell ,start no என்பது மாற்றவேண்டிய edயில் முதல் வார்த்தையான e ஆரம்பிக்கும் இடமான 9(காலியிடம் கூட எண்ணப்பட வேண்டும் space also included in counting),அடுத்து no of charactor என்பது 2 (ed),அடுத்து new text ல் சேர்க்க வேண்டிய வாக்கியமான 'ing" ஐ இட வேண்டும். கவனம் சேர்க்க வேண்டிய வாக்கியம் முன் சொன்னது போல் கண்டிப்பாக கமாவுக்குள் இருக்க வேண்டும்.கீழுள்ள படத்தினைப் பாருங்கள்.

அடுத்து ஒரு எளிய விளக்கம் concatenate மற்றும் Replace மூலமாக textஐ மாற்றி இணைத்தல்.29-09-2009 எனும் ஒரு textஐ 29DAYS09MONTHS2009YEARS என்று மாற்ற வேண்டும் எனில் கீழ்கண்ட படத்தில் உள்ளவாறு சூத்திரம் இட்டு மாற்றலாம்.

REPLACEஐ பயன்படுத்தி textல் உள்ள charactors களை மாற்றாமலேயே புதிதாக charactorsகளை இணைக்கலாம். இதற்கு செய்ய வேண்டியதெல்லாம் =Replace(old text,start no,no of charactors,new text ) எனும் சூத்திரத்தில் மேலே சொன்னதுபோல சூத்திரம் இட்டு no of charactor என்பதில் மட்டும் பூஜ்யம் கொடுத்து விடுங்கள். உதாரணத்திற்கு 8421ABCWR எனும் textஐ 8421AB-CWR என மாற்ற வேண்டும் எனில் =REPLACE(old text(8421ABCWR),starting no(7வது இடம்),no of charactor(0),newtext('-"))என சூத்திரம் இடுங்கள்.(அடைப்புக்குறிக்குள் இருப்பது விளக்கம்).வலது இடது ஒரங்களில் charactors இணைக்க concatenate பயனுள்ளதாக இருப்பது போல் textல் மையத்தில் charactors களை செருக இந்த REPLACE மிகவும் பயனுள்ளதாக இருக்கும்.


இறுதியாக REPLACE பயன்படுத்தி ஒரு மெகா சூத்திரம்.

மேலுள்ள படத்தில் column F ல் மூண்று text உள்ளது அவை P8421SBH000101,P8421SBCWR000101 & P8421SBDW000101 இவற்றை முறையே P-8421SB-H-0001-01,P-8421SB-CWR-0001-01 & P-8421SB-DW-0001-01 என மாற்ற வேண்டும் எனில் முதலில்

=REPLACE(F4,2,0,"-") எனும் சூத்திரத்தின் மூலம் P-8421SBCWR000101 எனும் விடையைப் பெறலாம். தொடர்ந்து =REPLACE(REPLACE(F4,2,0,"-"),9,0,"-') சூத்திரத்தின் மூலம் P-8421SB-CWR000101 எனும் விடையைப்பெறலாம். இதற்கடுத்துதான் சிக்கல் உள்ளது முதல் textல் H என்று ஒரு charactor மட்டும் உள்ளது. அடுத்ததில் CWR என மூண்று charactor உள்ளது.மூண்றாவதில் DW என இரண்டு மட்டும் உள்ளது எனவே இந்த சிக்கலைப்போக்க அடுத்து நாம் இடவேண்டிய charactor - எல்லாவற்றிலும் 0வுக்கு முன்னதாக இடவேண்டும் எனவே இதற்கு find சூத்திரத்தினைப் பயன்படுத்தி 0 இருக்கும் இடத்தினை கண்டறிந்து பின் charactor - ஐ புகுத்தலாம்.

எனவே சூத்திரம் இப்போது இப்படி =REPLACE(REPLACE(REPLACE(F4,2,0,"-"),9,0,"-'),FIND( "0", REPLACE(REPLACE(F4,2,0,"-"),9,0,"-'),(10)),0,"-")

இதில் முதல் REPLACE என்பது சூத்திரம் பச்சை நிறத்தில் உள்ளது old text, அடுத்து பிங்க் நிறத்தில் உள்ளது start no, நீல நிறத்தில் உள்ள பூஜ்யம்no of charactors, சிவப்பு நிறத்தில் உள்ளது new text இதன் மூலம் P-8421SB-CWR-000101 என விடையைப் பெறலாம். பின் இறுதியாக படத்த்தில் உள்ளது போல் மெகா சூத்திரம் இட்டு மொத்தமாக மாற்றிவிடலாம்.படத்தினை கிளிக் செய்து பெரிதாக்கிப் பார்க்கவும்.

இன்னும் நிறைய சூத்திரங்களோடு மறுபடியும் வருகிறேன் எனக் கூறி விடைபெறுகிறேன் வணக்கம். மேலும் உங்களுக்கு சூத்திரம் தொடர்பாக ஏதேனும் விளக்கங்கள் தேவைப்பட்டால் எனது vallankai@gmail.com மின்மடல் முகவரிக்கு தொடர்பு கொள்ளவும்.