எட்டி எடுக்க (Look up) சில Functionகள் - 2

தரவுகள் ஒருங்கிணைப்பில் அதிகமாக பயன்படும் LOOKUP  Functionகள் வரிசையில், கடந்த பதிவில் MATCH பற்றி பார்த்தோம் இந்த பதிவில், அதற்கு நெருங்கிய சொந்தமான VLOOKUP குறித்து பார்க்கலாம். உள் நுழையும் முன், அட்டவணை என்பதன் வரையறை குறித்த இப்பதிவை படிக்காதவர்கள், படித்துவிட்டு வரும்படி கேட்டுக்கொள்கிறேன்.

VLOOKUP

VLOOKUP ஐ MATCHன் நீட்சி எனக்கூறலாம், MATCH என்பது ஒரு பரிமாண அட்டவணை (List) மீது செயல்படும் ஒரு Function, VLOOKUP இரு பரிமாண அட்டவணை மீது செயல்படுவது.MATCH ஒரு மதிப்பு இருக்கும் இடத்தை (Position) சொல்கிறது, VLOOKUP தேடும் மதிப்பிற்கு தொடர்புடைய மற்றொரு மதிப்பை பெற்று தருகிறது.

VLOOKUP செயல்பாட்டை இப்படி வரையறுக்கலாம்,

"ஒரு அட்டவணையின் முதல் Column இல் ஒரு மதிப்பை தேடி, அது கண்டுபிடிக்கப்பட்ட Row வில் நமக்கு தேவையான Cell ஐ தருகிறது."

சற்றே விரிவாக பார்க்கலாம், முதல் கட்டத்தில், ஒரு இரு பரிமாண அட்டவணையின் முதல் Column இல் ஒரு மதிப்பை தேடுகிறது , உதாரணமாக 'Karthik' எனும் மதிப்பை கீழ்காணும் அட்டவணையில் முதல் Column இல் தேடலாம்,

இந்த முதல்கட்ட தேடல் Row எண் ஐந்தை கண்டுபிடிக்கிறது, இப்போது இருபரிமாண அட்டவணை, சுருக்கப்பட்டு ஒரு பரிமாண பட்டியலாக மாறுகிறது,

இந்த பட்டியலில் நமக்கு தேவையான Cell எண்ணைக்  குறிப்பிட்டால், நமக்கு விடை கிடைக்கும், உதாரணமாக நமக்கு Karthik ன் வயது தெரிய வேண்டுமென்றால், 3 என உள்ளிட வேண்டும்.

எனவே VLOOKUPக்கு நாம் தரவேண்டிய உள்ளீடுகள்,

1. எதை தேட வேண்டும் (LOOKUP value)

        தேடப்படும் மதிப்பு, ஒரு எண்ணாகவோ,  சொற்றொடராகவோ இருக்கலாம்,

சொற்றொடர்களை நேரடியாக பயன்படுத்தும்போது (String constant) மேற்கோள் (" ") குறியீட்டினுள் தர வேண்டும், (எகா) "Karthik", எங்களுக்கும், Cell Referenceக்கும்  இது அவசியமில்லை (எகா) 45, A1

	E	F
3	karthik	38
4		37
5		Meera

Worksheet Formulas Cell Formula F3 =VLOOKUP(E3,B2:C6,2,0) F4 =VLOOKUP("meera",B2:C6,2,0) F5 =VLOOKUP(96009,A2:C6,2,0)

2. எங்கு தேட வேண்டும் (LOOKUP table)
        தேட வேண்டிய இடம் என்பது, தேடப்படும் Column ஐ முதலாவதாக கொண்ட ஒரு அட்டவணை, இது ஒரு முழு அட்டவணையாக இருக்கவேண்டிய அவசியமில்லை, ஒரு பெரிய அட்டவணையின் பகுதியாக இருக்கலாம். உதாரணமாக, கீழ்காணும் அட்டவணையில்  (A1 : C6) "Karthik" வயதை கண்டறிவதற்கு, நாம் (B2 : C6) என்ற உள் அட்டவணையை தேர்வு செய்ய வேண்டும், Excel ஐ பொறுத்தவரை A1 : C6 தான் அட்டவணை என்றபோதும், VLOOKUP ஐ பொறுத்தவரை B2 : C6 ஒரு முழுமையான  அட்டவணை தான்

3. தேடப்படும் மதிப்பு கண்டுபிடிக்கப்பட்ட Rowவில், எந்த cell வேண்டும்

      
தேடப்படும் Column ஐ 1 எனக்கொண்டு, அதன் வலதுபுறமுள்ள Columnகளை எண்ணிட்டால், நமக்கு தேவையான தரவு இருக்கும் Cell கிடைக்கும்.

4. தேடும் முறை
       MATCH இல் குறிப்பிட்டது போலவே, Exact matching மற்றும் தேடப்படும்  மதிப்பிற்கு அடுத்த  (சிறிய) மதிப்பை பெறவுமாக, இரண்டு வழிகளில் தேடலாம். Exact Match முறையில் தேட 0 அல்லது FALSE உள்ளீடாகதரவேண்டும்.

(எகா)
முந்தைய அட்டவணையில் "Karthik"ன் வயதை கண்டறிய கீழ்க்காணும் Formula பயன்படும், 

	E	F
3	karthik	38

Worksheet Formulas Cell Formula F3 =VLOOKUP(E3,B2:C6,2,0)

மீண்டும் மீண்டும் தேடும் Column முதலாவதாக இருக்க வேண்டும்  என்று கூறுவதன் காரணம்,  VLOOKUP வலதுபுறம் மட்டுமே பார்க்கும் Function. மேலே உள்ள அட்டவணையில் கீழ்காணும் Formula பயன்படுத்தி "Karthik"ன் "Emp No" கண்டறிய முடியாதா, என கேட்பவர்களை நான் சந்தித்திருக்கிறேன்.  

	E	F
3	karthik	#N/A

Worksheet Formulas Cell Formula F3 =VLOOKUP(E3,A2:B6,-2,0)

VLOOKUP, MATCH இன்னும் பெரும்பான்மையான  Excel Functionகள் பெரிய மற்றும் சிறிய எழுத்துக்களை ஒன்றாகவே பார்க்கும் (Case insensitive)

  VLOOKUP, MATCH பற்றி தெரிந்து கொண்டாயிற்று, அடுத்த பதிவில் இவற்றை எப்படி இணைத்து பயன்படுத்துவது என பார்க்கலாம்.

நன்றி
சங்கர்

எட்டி எடுக்க (Look up) சில Functionகள் - 1

Excel இல் நமது பயன்பாடுகளில் பெரும்பாலானவற்றை இரு பெரும் வகைபாடுகளுக்குள் அடக்கலாம். ஒன்று, தரவுகளை ஒருங்கிணைத்தல் (Data Integration), மற்றொன்று, ஒருங்கிணைத்த தரவுகளை சுருக்கி (Summarizing) தகவலாய் (Information) மாற்றுதல். முதலாவதாய் வரும் ஒருங்கிணைப்பு என்பது, வெவ்வேறு இடங்களில் இருக்கும் தரவுகளை ஓரிடத்திற்கு கொண்டு வருவதையும் உள்ளடக்கி இருக்கும். இப்படி இரு வேறு இடங்களில் இருக்கும் தரவுகளை, இரண்டுக்கும் பொதுவான ஒரு Column ஐ (Common Reference Field) அடிப்படையாக கொண்டு இணைக்க பயன்படும் Functionகளை எட்டி எடுக்கும் (Lookup) Functionகள் எனலாம். இந்த வகைப்பாட்டினுள் வரும் ஒரு Functionஐ இப்பதிவில் காண்போம்.

MATCH

ஒரு பட்டியலில் (m x 1 or n x 1 table) நாம் தேடும் மதிப்பு (value) உள்ளதா என்பதை கண்டறிய MATCH பயன்படுகிறது. நாம் தேடும் Value பட்டியலில் இருந்தால், பட்டியலின் துவக்கத்திலிருந்து** எத்தனை Cell தாண்டி இருக்கிறது என்ற இடத்தை (Position) விடையாக தரும். பட்டியலில் நாம் தேடும் Value இல்லாதபோது #N/A திட்டு கிடைக்கும்.

உதாரணத்தோடு பார்க்கலாம்,

	A
1	Name
2	Sankar
3	Jagan
4	Radha
5	Karuna
6	Raghavan
7	Saravanan
8	Saradha
9	Gomathi
10	Vijay

	C	D
1	Name to find	Position
2	Raghavan	5

Worksheet Formulas Cell Formula D2 =MATCH(C2,A2:A10,0)

MATCH Funtionக்கு மூன்று உள்ளீடுகள் உண்டு,

முதலில், எதை தேடவேண்டும் (Lookup Value)
அடுத்து, எங்கு தேட வேண்டும் (Lookup List),

**தேடவேண்டிய இடம் (Lookup List), நெடுவரிசையாகவோ, கிடைவரிசையாகவோ இருக்கலாம். பட்டியலின் துவக்கம் என்பது, கிடைவரிசையில், இடதுபுறமிருந்து முதல் cellலையும், நெடுவரிசையில், மேலிருந்து முதல் cellலையும் குறிக்கும்.

மூன்றாவதாய், தேடும் முறையை குறிப்பிட வேண்டும் (Search type),

MATCH ஐ பயன்படுத்தி தேடும்போது மூன்று வித முடிவுகளை பெறலாம்.

1. நாம் தேடும் மதிப்பு, பட்டியலில் இருந்தால் மட்டுமே விடை தருவது, (Exact Match)
2. நாம் தேடும் மதிப்பு இல்லாத பட்சத்தில், அதற்கடுத்த, குறைந்த மதிப்பை (highest value, lower than the look up value) தேடுவது
3. நாம் தேடும் மதிப்பு இல்லாத பட்சத்தில், அதற்கடுத்த, உயர்ந்த மதிப்பை (lowest value, higher than the look up value) தேடுவது

Exact match முறையில் தேடுவதற்கு, 0 அல்லது FALSE உள்ளீடாய் தர வேண்டும். (FALSE ,TRUE பற்றி இன்னொரு பதிவு எழுதுகிறேன்), மேலும் இம்முறையில், பட்டியல் வரிசைப்படுத்தப்பட்டிருக்க (Sorting) வேண்டிய அவசியம்இல்லை.

	B	C	D	E	F	G	H	I	J
13	57	99	65	23	61	42	87	33	89

	J	K
1	Value to find	Position
2	23	4

Worksheet Formulas Cell Formula K2 =MATCH(J2,B13:J13,0)

Exact match முறையில், நாம் தேடும் மதிப்பு பட்டியலில் இல்லாதபோது #N/A திட்டு கிடைக்கும். மற்ற இரண்டு முறைகளை பயன்படுத்துவதை பற்றி வேறொரு சந்தர்ப்பத்தில் சொல்கிறேன்.

கடைசியாக, MATCH Function தனித்து பயன்படும் இடங்களை விட, VLOOKUP, INDEX போன்ற பிற Functionகளுடன் இணைந்து பயன்படும் இடங்களே அதிகம். அது பற்றி அடுத்த பதிவில் பார்க்கலாம்.

நன்றி
சங்கர்

அட்டவணை (Table) - ஒரு வரையறை

அட்டவணை என்பதை, கிடை மற்றும் நெடு வரிசைகளின் (Row & Column) தொகுப்பு என்று வரையறுக்கலாம். இங்கு கிடை / நெடு வரிசைகளின் எண்ணிக்கை குறைந்தபட்சம் ஒன்றிலிருந்து துவங்கவேண்டும், அப்படி பார்க்கும்போது

5 நிரை x 3 நிரல் (multiple row, multiple column) ( m x n table) என்பதும்,

10 நிரை x 1 நிரல் (multiple row, single column) (m x 1 table) என்பதும்,

1 நிரை x 7 நிரல் (single row, multiple column) (1 x n table) என்பதும்,

1 நிரை x 1 நிரல் (single row, single column) (1 x 1 table) என்பதும்

அட்டவணை தான்.



வேறு வார்த்தைகளில் சொன்னால்

ஒரு பட்டியலும் (List) (multiple row, single column / single row, multiple column),

ஒரு தனி அறையும் (single cell) (single row, single column)

கூட அட்டவணை என்னும் வகைப்பாட்டில் வரும்.

ஒரு அட்டவணை என்பது இரு பரிமாணம் (Dimension) உடையது, எனினும் Excel ஐ பொறுத்தவரை, பட்டியல் (List) என்பதை, ஒரு பரிமாணம் உடையது என கொண்டால், புரிதல் சற்று எளிதாக இருக்கும்.

Functionகளின் செயல்பாட்டை தெளிவாய் கற்க இந்த புரிதல் அவசியமான ஒன்று.


நன்றி
சங்கர்

எண்ணுதல் யார்க்கும் எளியவாம்

நேத்து சங்கர் எழுதிய டெக்ஸ்ட் பங்கஷனில் நல்லா லெப்ட், ரைட், மிட் எல்லாம் வாங்கிட்டாருன்னு நினைக்கிறேன். இப்ப இந்த பங்கஷன்களின் நடைமுறைப் பயன்பாட்டை ஒரு எளிய உதாரணம் சொல்ல ஆசைப்படுகிறேன்.

அதுதான் எண்ணுதல் யார்க்கும் எளியவாம்! இங்கு எண்ணுதல் என்பது Counting, not thinking!!

அதாவது, எக்ஸெலில் ஒரு செல்லில் உள்ள கமாவால் பிரிக்கப்பட்ட வார்த்தைகளை எண்ணுவது.

உதாரணம் உங்களுக்கு கிடைத்திருக்கிற​வொர்க்புக்கில் (சுசி பின்னூவில் குறிப்பிட்ட படி.. இப்படி தங்கிலீஷிலேயே எழுதலாம் என்று முடிவாயிற்று!) ஒரு காலத்தில் பெயர்கள் ஒரு பட்டியல்​போல் இருக்கலாம்.. அல்லது ஒரு குழுவின் பெயர்கள் கமா(,)வால் பிரிக்கப்பட்டு ஒரே செல்லில் அடைக்கப்பட்டிருக்கும். ஒரு செல்லில் உள்ள பட்டியலில் எத்தனை உறுப்புகள் உள்ளன என எண்ணுவதற்கு இந்த பார்முலா பயன்படும்:

=LEN(B2)-LEN(SUBSTITUTE(B2,",",""))+1

(இந்த பார்முலா எப்படி வேலை செய்யுது, சப்ஸ்டிடியூட் பங்ஷன் என்ன என்பதை நிதானமாக பார்க்கலாமே?)


படம் 1ல் உள்ள உதாரணத்தைப் பார்க்கவும்:


இது மாதிரியான கமாவால் பிரிக்கப்பட்ட பட்டியல்கள் (comma delimited list) நம் வேலையில் வர சாத்தியம் உண்டு. இப்போது பட்டியலை எண்ண இந்த பார்முலாவைப் பயன்படுத்திக் கொள்ளுங்கள்.. எளிமையாய் எண்ணுங்கள்!

சில Text Function கள் - 2

1. MID

ஒரு சொற்றொடரின் இடது மற்றும் வலது புறங்களில் இருந்து எழுத்துக்களை வெட்டி எடுப்பது எப்படி என முந்தய பதிவில் பார்த்தோம், ஒரு cell இன் நடுவிலிருந்து Text ஐ பெறுவதற்கு MID Function ஐ பயன்படுத்தலாம். LEFT பயன்படுத்தி இடது புறமிருந்து Text பெறும்போது, எடுக்கவேண்டிய Text இன் தொடங்கு புள்ளி (Starting position) cell இன் முதல் எழுத்தாக இயல்பாகவே அமைந்துவிடுகிறது, நாம் அளிக்கவேண்டிய உள்ளிடு (argument), எத்தனை எழுத்துக்களை எடுக்கவேண்டும் என்பது மட்டும் தான். ஆனால் MID பயன்படுத்தி நடுவிலிருந்து வெட்டி எடுக்கும்போது, எத்தனை எழுத்துக்கள் வேண்டும் என்பதோடு, எங்கிருந்து தொடங்க வேண்டும் என்பதையும் நாம் குறிப்பிட்டாக வேண்டும். எனவே MID இன் கட்டமைப்பு (Syntax), எந்த Cell இல் இருந்து பெறவேண்டும், எந்த இடத்தில் துவங்க வேண்டும், எத்தனை எழுத்துக்கள் எடுக்கவேண்டும் என்ற மூன்று உள்ளீடுகளை கொண்டுள்ளது.

	A	B
1	V Sankara narayanan	Sankar

Worksheet Formulas Cell Formula B1 =MID(A1,3,6)

யோசித்து பார்த்தால், LEFT இன் செயல்பாட்டையும் MID மூலமாக நிகழ்த்தலாம் என்பது தெரியும், cell இன் முதல் எழுத்தையே தொடங்கும் புள்ளியாய் கொண்டால் இது சாத்தியம்,

	A	B
2	V Sankara narayanan	V Sankar

Worksheet Formulas Cell Formula B2 =MID(A2,1,8)

சற்றே மெனக்கெட்டால் RIGHT இன் செயல்பாட்டையும் MID மூலம் கொண்டுவரலாம், (இது கொஞ்சம் தலையைச்சுற்றி மூக்கை தொடும் வழி தான், அறிந்துகொள்வதற்காக மட்டுமே இதை எழுதியுள்ளேன், புரியாவிட்டால் மெனக்கெட வேண்டாம்)

	A	B
3	Sankara narayanan	narayanan
4	Sankara narayanan	Sankara narayanan

Worksheet Formulas Cell Formula B3 =MID(A3,LEN(A3)-8,9) B4 =MID(A4,LEN(A4)-16,17)

இங்கு, LEN மூலமாக cell இன் எழுத்துக்களின் எண்ணிக்கையை அறிந்துகொள்கிறோம், அதாவது cell இன் கடைசிப்புள்ளிக்கு சென்றுவிடுகிறோம், பின், உள்நோக்கி தேவையான எழுத்துக்கள் - 1 பயணிக்கிறோம், இங்கு '-1' எதற்கென்று, இரண்டாவது உதாரணத்தை பார்த்தால் புரியும். செல்லில் 17 எழுத்துக்கள் இருக்கும் போது LEN(A4) = 17 கொடுக்கும், இதிலிருந்து 17 கழித்தால் 0 வரும், cell இன் தொடங்கு புள்ளி 1 என்பதால், Excel திட்டும் (#VALUE! Error).


குறிப்பு: சென்ற பதிவில் குறிப்பிடத்தவறிய ஒரு விஷயம், LEFT, RIGHT, MID போன்ற Text Functionகள் பயன்படுத்தும்போது, நீங்கள் கேட்கும் அளவு cell இல் எழுத்துக்கள் இல்லாவிட்டாலும், Excel திட்டாது, எவ்வளவு இருக்கோ அவ்வளவு எழுத்துக்களை தரும்.

	A	B
6	Sankar	Sankar
7	Sankar	Sankar
8	Sankar	Sankar

Worksheet Formulas Cell Formula B6 =LEFT(A6,100) B7 =RIGHT(A7,100) B8 =MID(A8,1,100)

2. SEARCH / FIND

ஒரு சொற்றொடரில் (String) மற்றொரு சொற்றொடர் (Sub-String) இருக்கிறதா என்பதை அறிவதற்கு SEARCH பயன்படுகிறது. தேடப்படும் சொற்றொடர் (Sub-string) ஓர் எழுத்தாகவோ, வார்த்தையாகவோ முழு வாக்கியமாகவோ இருக்கலாம். இந்த Function க்கு, தேடப்படும் சொற்றொடர் முதலாவதாகவும், தேடுதலுக்கு உட்படும் சொற்றொடர் இரண்டாவதாகவும் உள்ளிடாக அளிக்கவேண்டும்.

	A	B
14	Excel 2003	2
15	Excel 2003	7
16	Excel 2003	1

Worksheet Formulas Cell Formula B14 =SEARCH("x",A14) B15 =SEARCH("2003",A15) B16 =SEARCH("Excel 2003",A16)

தேடப்படும் சொற்றொடரின் தொடங்குபுள்ளி, SEARCH Function தரும் விடையாக இருக்கும். தேடப்படும் சொற்றொடர் கிடைக்காதபோது , திட்டு (#VALUE Error) கிடைக்கும். தேடப்படும் சொற்றொடர் ஒருமுறைக்கு மேல் இருந்தால், முதல் முறை கண்டறியும் இடமே விடையாக கிடைக்கும்.

	A	B
18	Excel 2003 is better than excel 2007	1

Worksheet Formulas Cell Formula B18 =SEARCH("excel",A18)

குறிப்பு: SEARCH ஒரு Case-insensitive Function ஆகும். அதாவது "Excel", "EXCEL","excel" எல்லாமே ஒன்றுதான். Case-sensitive ஆக தேடவிரும்பினால், FIND ஐ பயன்படுத்தலாம். SEARCH மற்றும் FIND இடையேயான ஒரே வேறுபாடு இதுதான்.

	A	B
20	Excel 2003 is better than excel 2007	27

Worksheet Formulas Cell Formula B20 =FIND("excel",A20)

இதுவரை கற்ற LEFT, MID மற்றும் SEARCH ஐ பயன்படுத்தி ஒரு cell இன் முதல் வார்த்தையை மட்டும் பெறுவது எப்படி எனப் பார்க்கலாம்.

	A	B
22	Excel 2003 is better than excel 2007	Excel

Worksheet Formulas Cell Formula B22 =MID(A22,1,SEARCH(" ",A22)-1)

முதலில், முதல் வார்த்தை என்பதை எப்படி தீர்மானிப்பது? Cell இன் முதல் எழுத்திலிருந்து முதல் Space க்கு முந்தைய எழுத்து வரை உள்ள பகுதியை, முதல் வார்த்தை எனக்கொள்ளலாம். இப்போது MID function வழியாக இந்த கேள்வியை மதிப்பிடும் போது, Cell இன் முதல் எழுத்து MID இன் இரண்டாவது உள்ளீடாகிறது, முதல் Space ஐ கண்டறிய SEARCH ஐ பயன்படுத்தலாம். Space க்கு முந்தைய எழுத்தை குறிக்க -1 பயன்படுகிறது. இது MID இன் மூன்றாவது உள்ளீடாகிறது.

	D
22	=MID(A22,1,SEARCH(" ",A22)-1)
23	=MID(A22,1,6-1)
24	=MID(A22,1,5)
25	="Excel"

cell இல் Space இல்லாத போது இந்தமுறை திட்டு வாங்கித்தரும். இதனை தவிர்க்க நாமே ஒரு Space ஐ சேர்த்து கொள்ளலாம்.

	A	B
27	Sankar	Sankar

Worksheet Formulas Cell Formula B27 =MID(A27,1,SEARCH(" ",A27 & " ")-1)

இந்த முறையில், முன் சொன்ன குறிப்பு எப்படி பயன்பட்டுள்ளது என்பதை சோதித்து அறியுங்கள்.

cell இன் கடைசி மற்றும் நடுவிலிருந்து ஒரு வார்த்தையை பெறுவது எப்படி என மற்றொரு பதிவில் பார்க்கலாம்.


நன்றி
சங்கர்

வி-லுக்கப்பில் கன்காடினேட்டின் பயன்பாடு

விலுக்கப் (VLOOKUP):

வேறு இடத்தில் (வொர்க் ஷீட் / வொர்க்புக்) உள்ள தகவல் பட்டியிலுள்ள (table) குறிப்பிட்ட தகவலை நாம் எதிர்பார்க்கிற காலத்திலிருந்து (column) தேடித் தருகிறது.

உதாரணமாக, உங்களிடம் ஒரு வாடிக்கையாளர் பட்டியல் இருக்கிறது என்று வைத்துக் கொள்வோம். படம்-1ல் காட்டியுள்ளவாறு..
ஒவ்வொரு வாடிக்கையாளரும் கடந்த மூன்று மாதங்களில் மட்டும் எவ்வளவு வியாபாரம் கொடுத்துள்ளார் என்பதை அறியவேண்டுமானால், நீங்கள் வேறொரு டேபிளில் தேட வேண்டியிருக்கும். அந்த டேபிள் படம்-2ல் காட்டியுள்ளவாறு என்று வைத்துக் கொள்ளுங்கள்

இந்த இரண்டு டேபிள்களுக்கும் உள்ள பொதுவான அம்சம் வாடிக்கையாளர் எண் மற்றும் பெயர். இவைகளை key index fields என்று சொல்வதுண்டு.

இப்ப நம்ம வேலை சிம்பிள். வாடிக்கையாளர் மன்னார் அன்ட் மன்னார் ​பெயரை பட்டி-2ல் தேடினால் பணவரவு தெரிந்துவிடும்.

தேடுவோமா?

பட்டி-1ல் உள்ள கேள்விக்குறிகளை இந்த பார்முலாவால் நிரப்ப ​வேண்டியதுதான்.

=VLookup(எதைத் தேட?, எங்க போயி தேட?, எந்த காலம், ​தேடறது கிடக்கலேன்னா ஓரளவு அதுமாதிரி இருக்கிற மேட்டரை கொடுக்கலாமா?)

இவ்ளோதான் விலுக்அப்!

நம்ம விஷயத்தில்

=VLookup(பட்டி1-வாடிக்கையாளர் பெயர், பட்டி-2, 5வது காலம், வேணாம்)

=VLookup(C3, Sheet1!$C$3:$G$32,5,0)

இந்த பார்முலா நமக்கு வாடிக்கையாளரின் வியாபாரத் தொகையை காண்பித்துவிடும்தான்..... இருந்தும் ஒரு சிக்கல்!

பட்டி-1 முழுதும் பாத்தீங்கன்னா வாடிக்கையாளர் பெயர்கள் திரும்பவும் வந்திருப்பது ​தெரியும்.. அதாவது ஒரே வாடிக்கையாளர் திரும்பவும் வியாபாரம் பண்ணியிருப்பார். உதாரணமா அரவிந்த பி லிட். கிட்டத்தட்ட 6 ஆர்டர் கொடுத்திருக்காங்க கடந்த 3 மாதங்களில்.. ஒவ்வொரு ஆர்டரும் ​வெவ்வேறு ஆர்டர் எண் மற்றும் தொகை கொண்டது.

இந்த விலுக்கப் முதலில் சிக்குகிற அரவிந்த் பி லிட்டுக்கான ஆர்டர் தொகையை மட்டும் கொடுக்கும்.. அடுத்து வர்ற ஆர்டர்களை விலுக்காது!

இந்த இடத்தில் நம் தேவை வாடிக்கையாளர் பெயரும் + ஆர்டர் எண்ணும் ​சேர்த்து கூட்டாக அடுத்த டேபிளில் (பட்டி-2ல்) ​தேடுவதுதான்

எப்படி?

​போன இடு​கையில் சங்கர் எழுதிய ​டெக்ஸ்ட் பங்கஷன்களில் ஒன்றான கன்காடி​னேட் (concatenate) பயன்படுத்தி..! (விலுக்கப் ​ஒற்​றை காலத்​தை மட்டு​மே இன்​னொரு ​​டேபிளில் ​தேட முடியும் என்ப​தை நி​னைவில் ​கொள்க)

ஆக​வே, வாடிக்​கையாளர் + ஒப்பம் எண் என்ற இரு காலங்க​ளையும் ஒன்றாக்கி விட​வேண்டும்; பட்டி-1 மற்றும் பட்டி-2 இரு இடங்களிலும்.. இப்படி:

பட்டி-1:

பட்டி-2:பின்வரும் விலுக்கப் பங்கஷனில் புதிதாக தயாரித்த வா+ஒ.எண் (இதுதான் இப்ப நமக்கு Compound Index!) காலத்​தை பயன்படுத்த​வேண்டும். இப்​போது வாடிக்​கையாளர் ​பெயர் பலமு​றை வந்திருந்தாலும் ஒப்பம் எண்ணுக்குச் சரியான ​தொ​கை நமக்கு கி​டைத்து விடும்.

டிஸ்கி:

• 
  
  குழம்பியவர்கள்.. பின்னூவில் எழுதுங்கள்... ​வேறுமாதிரியான வகையில் எழுதப் பார்க்கி​றேன்.
  
  
  
• 
  
  எனக்கு இந்த மாதிரி தமிலீஷ்ஷில் டெக்னிகல் விஷயங்கள் எழுதிப் பழக்கமில்லை. ​table, column, row, cell, value, lookup, worksheet இத்யாதிக​ளை ​மொழி​பெயர்ப்பதற்குள் முழி பிதுங்கிவிடுகிறது.
  
  
  
• 
  
  ஏதாவது உப​யோகமான குறிப்புகள் இருந்தால் அள்ளி / கிள்ளித் தரவும். நன்றிகள்!
  
  
  

இந்த மாதிரி ஒரு வாய்ப்பை எனக்கு ஏற்படுத்தி, கற்றது Excel-ன் சகஆசிரியனாக்கிய சங்கருக்கு நன்றிகள்..!!!

சில சொற்றொடர் (Text) Functionகள்

Excel இல் Text Functions என்னும் வகைப்பாட்டின் கீழ் பல Functionகள் உள்ளன. இவற்றில் CONCATENATE, LEN, PROPER ஆகியவற்றை முதல் நிலை Functionகளாகவும், LEFT, RIGHT, MID, EXACT போன்றவற்றை இரண்டாம நிலையாகவும், SEARCH, REPLACE, TEXT போன்றவற்றை மூன்றாம் நிலையாகவும் வகைப்படுத்தலாம். முதல் மற்றும் இரண்டாம் நிலை Function கள் சிலவற்றை இப்பதிவில் காண்போம்.

1. CONCATENATE

ஒன்றுக்கு மேற்பட்ட சொற்றொடர்களை ஒன்றாய் இணைப்பதற்கு பயன்படும் CONCATENATE, Excel பயனர்களில் பலர் முதன்முதலாய் கற்கும் Functionகளில் ஒன்று. இணைக்க வேண்டிய சொற்றொடர்கள் cell content ஆகவோ, string constant ஆகவோ இருக்கலாம்

(எகா)

	A	B	C	D
2	Input 1	Input 2	Result	Input type
3			SankaraNarayanan	Two String constants
4	Sankara	Narayanan	SankaraNarayanan	Two Cell contents
5	Sankara	Narayanan	Sankara Narayanan	Two Cell contents and a constant

Worksheet Formulas Cell Formula C3 =CONCATENATE("Sankara","Narayanan") C4 =CONCATENATE(A4,B4) C5 =CONCATENATE(A5," ",B5)

CONCATENATE Function ஐ SUM Function உடன் ஒப்பிடலாம், ஆனால் CONCATENATE இன் மிக முக்கிய குறைபாடு, இணைக்க வேண்டிய Cell கள் ஒவ்வொன்றையும் தனித்தனியே குறிப்பிட (Refer) வேண்டும்.

	A	B	C
1	92		Hari
2	31		Sankara
3	85		Narayanan
4	44		Venkatesan
5	22		RVS
6
7	274		HariSankaraNarayananVenkatesanRVS

Worksheet Formulas Cell Formula A7 =SUM(A1:A5) C7 =CONCATENATE(C1,C2,C3,C4,C5)

ஏனோ தெரியவில்லை MS இப்படி ஒரு குறையை விட்டுவைத்திருக்கிறது. இதனை VBA துணை கொண்டு நமது சொந்த Function உருவாக்கி நிவர்த்தி செய்வது எப்படின்னு அப்புறம் சொல்றேன்.

CONCATENATE இன் செயல்பாட்டை '&' Operator மூலமாகவும் செயல்படுத்தலாம்

	I
2	Sankara
3	Narayanan
4	Venkatesan
5
6	SankaraNarayananVenkatesan

Worksheet Formulas Cell Formula I6 =I2&I3&I4

2. LEN
ஒரு Cell இல் உள்ள எழுத்துக்களின் எண்ணிக்கையை கண்டறிவதற்கு LEN function ஐ பயன்படுத்தலாம். இந்த எண்ணிக்கை எழுத்துக்கள், Space, Line break, non-breaking போன்ற non-printable ஆகிய அனைத்தையும் உள்ளடக்கியதாக இருக்கும்.

3. LEFT, RIGHT
ஒரு Cell இல் இடதுபுறமிருந்து நமக்கு தேவையான எழுத்துக்களை வெட்டி எடுக்க LEFT function பயன்படும், வலதுபுறமிருந்து எழுத்துக்களை பெற RIGHT உதவும்.

	A	B
1	Sankara narayanan	Sankar
2	Sankara narayanan	narayanan

Worksheet Formulas Cell Formula B1 =LEFT(A1,6) B2 =RIGHT(A2,9)

மூன்று அடிப்படை Function கள் குறித்து சொல்லியாகிவிட்டது. இப்போது பயன்பாடு.

1. நோக்கம் (Aim) : ஒரு Cell இன் முதல் எழுத்தை தவிர்த்து பிற எழுத்துக்கள் அனைத்தையும் பெறுவது
Cell இல் உள்ள எழுத்துக்களின் எண்ணிக்கை நிலையாக (Fixed) இருக்கும்போது RIGHT function மூலம் எளிதாக இதனை செய்யலாம், எழுத்துக்களின் எண்ணிக்கை தெரியாத நிலையில் எப்படி செய்வது?

இதனை நாம் இரு செயல்களாக பிரிப்போம், முதலில் தெரியாத விஷயமான எழுத்துக்களின் எண்ணிக்கையை அறிவோம், இரண்டாவதாக இந்த எண்ணிக்கையை RIGHT உடன் பயன்படுத்தி நமக்கு தேவையான எழுத்துக்களை பெறலாம்

	A	B
4	VSankar	Sankar

Worksheet Formulas Cell Formula B4 =RIGHT(A4,LEN(A4)-1)

இந்த Formula வை படிப்படியாக மதிப்பீடு (Evaluate) செய்யும்போது கீழ்காணும் முடிவுகளை காணலாம்

	B
6	=RIGHT(A4,7-1)
7	=RIGHT(A4,6)
8	="Sankar"

முதல் படியில், உள்ளிருக்கும் LEN எழுத்துக்களின் எண்ணிக்கையை (7) தருகிறது, அதிலிருந்து ஒன்றாய் கழிக்கக்அ நமக்கு தேவையான எழுத்துக்களின் எண்ணிக்கை (6) கிடைக்கிறது, இதனை RIGHT உடன் பயன்படுத்த எதிர்பார்த்த விடை கிடைக்கிறது.

இதுபோலவே LEFT பயன்படுத்தி Cell இன் கடைசி எழுத்து தவிர்த்து பிற எழுத்துக்களை பெறலாம்.

Cell இன் முதல் அல்லது கடைசி வார்த்தை மட்டும் வெட்டி எடுப்பது எப்படி என அடுத்த பதிவில் சொல்கிறேன்.

நன்றி சங்கர்