Class-47 (Vlookup )

Microsoft Excel এ Vlookup ফাংশন ব্যবহার

Vlookup ফাংশন এক্সেল প্রোগ্রামের একটি কার্যকরী ও মজার ফাংশন, এর পূর্ণ নাম হল ভার্টিকাল লুকাপ। এই ফাংশনটি ব্যবহার করে এক ডকুমেন্ট থেকে বিশেষ কোন তথ্য খুব সহজেই স্থানান্তর করে অন্য ডকুমেন্টে একই ফর্মুলা অনুসারে নেয়া যায়। তাই এ পর্যায়ে আমরা আলোচনা করবো Microsoft Excel এ Vlookup ফাংশন ব্যবহার কিভাবে করতে হয়। চলুন তাহলে জেনে নেয়া যাক Microsoft Excel এ Vlookup ফাংশন ব্যবহার করার পদ্ধতি সম্পর্কে।

Vlookup ফাংশনের একটি বিশেষ কার্যকারী দিক রয়েছে। তথ্য বহুল কোন ডকুমেন্ট থেকে যেমন ধরুন ১০০ বা ২০০ জন ছাত্রের একটি রেজাল্টশীট থেকে বিশেষ একজন ছাত্রের রেজাল্ট, প্রতি বিষয়ে প্রাপ্ত নাম্বার, এভারেজ, গ্রেড ইত্যাদি খুজে বের করাটা জটিল হয়ে যায়। সে ক্ষেত্রে Vlookup ফাংশন ব্যবহার করে খুব সহজেই এই সমস্যার সমাধান করতে পারবেন। আমরা এরকম একটি সমস্যার সমাধান করবো এই ফাংশনের মাধ্যমে, যাতে করে আপনারাও এই ফাংশনের ব্যবহার ভালোভাবে বুঝতে পারেন।

Vlookup ফর্মুলা দেখানোর পূর্বে আমার দুটি টেবিল তৈরি করে তাতে Vlookup ফর্মুলা ব্যবহার করে দেখাবো। টেবিল দুটি একই সীটে অথবা ভিন্ন সীটে অথবা ভিন্ন ওয়ার্কবুকেও থাকতে পারে। আমরা দুটি ভিন্ন ওয়ার্কবুকে টেবিল তৈরি করে সেটিতে Vlookup ফর্মুলার মাধ্যমে তথ্য স্থানান্তর করবো। সে ক্ষেত্রে Sheet1 এ যে টেবিল তৈরি করা হবে সেটি হল টেবিল নাম্বার ‘1’, যেখানে শুধু ছাত্রদের নাম থাকবে। Sheet2 তে যে টেবিল তৈরি করা হবে সেটি হল টেবিল নাম্বার ‘2’ যেখানে ছাত্রদের নামসহ বিভিন্ন বিষয়ের প্রাপ্ত নাম্বার থাকবে।আপনাদের বোঝার সুবিদার্থে নিচে টেবিল দুটির চিত্র দেয়া হলঃ

 

Table of Sheet1 for Example in Excel

উপরের চিত্রে দেখুন, ১ নাম্বার টেবিলটি Sheet1 এ তৈরি করা হয়েছে যেখানে Vlookup ফর্মুলা ব্যবহার করা হবে।

 

Table of Sheet2 for Example in Excel

উপরের চিত্রে ২ নাম্বার টেবিলটি Sheet2 তে তৈরি করা হয়েছে। এখান থেকে তথ্য স্থানান্তর করে ১ নাম্বার টেবিলে স্থানান্তর করা হবে।

এখানে একটি বিষয় মনে রাখতে হবে, যাতে দুটি টেবিলর Lookup Value একই রকম হয়। Lookup Value বলতে যে কলামে সকল ছাত্রদের নাম রয়েছে, সেই কলামকে Lookup Value বলা হচ্ছে। এখন আমরা Sheet2 টেবিল থেকে প্রাপ্ত নাম্বার গুলো Sheet1 টেবিলে Vlookup ফর্মুলা ব্যবহার করে স্থানান্তর করবো।

এখন আপনি ২য় সীটে টেবিলের তথ্য বা প্রাপ্ত নাম্বার স্থানান্তর করে ১ম সীটে নেয়ার জন্য প্রথমে ১নাম্বার টেবিলে ইংলিশ বিষয়ের প্রথম ঘরে অর্থাৎ সেল C3 তে সেল পয়েন্টার রাখুন। তারপর লিখুন =Vlookup(B3:B14,Sheet2!B3:H15,2,0) তারপর ইন্টার চাপুন। তাহলে দেখবেন C3 সেলে ২নাম্বার টেবিলের ডাটা চলে এসেছে। এখানে ফর্মুলাটি নিয়ে একটু বিস্তারিত আলোচনা করা দরকার যাতে আপনারা বিষয়টি ভালোভাবে বুঝতে পারেন।

ফরমূলার ব্যাখ্যাঃ

ক. যে কোন ফরমূলা শুরু করতে হলে প্রথমে সমান “=” চিহ্ন দিতে হয়।

খ. “VLOOKUP” হচ্ছে ফরমূলার কোড। যেমন: SUM দ্বারা যোগ করার ফরমূলা, COUNT দ্বারা গণনার ফরমূলা ইত্যাদি।

গ. ১ম বন্ধনীর “( )” মধ্যে ফরমূলার কোডসমূহ লিখতে হয়।

ঘ. আমাদের টেবিল অনুযায়ী ১ম কোডটি হচ্ছে “B3” । এটিকে বলা হয় Lookup Value. কারন এই সেলের ডাটাকে উদ্দেস্য করে ২য় টেবিল থেকে ডাটা স্থানান্তর করা হচ্ছে। এখানে যে Value দেওয়া হবে ফরমূলা সেই Value খুঁজবে। যেমনঃ এই অংশে যদি দেওয়া হয়-A তাহলে ফরমূলা “A” খুঁজবে, যদি দেওয়া হয় Jakir তাহলে Jakir খুঁজবে, যদি দেওয়া হয় কোন নির্দিষ্ট সেল যেমন এখানে দেওয়া হয়েছে B3তাহলে B3 সেলের মধ্যে যা লিখা থাকবে এই ফরমূলা পরবর্তী অংশে তাই খুঁজবে। কয়েকটি উদাহরণ নিম্নে দেওয়া হলো:

=VLOOKUP(“B”,’Sheet2′!A3:B8,2,0)
=VLOOKUP(“Jakir”,’Sheet2′!B3:H15,2,0)
=VLOOKUP(A3,’Sheet2′!B3:H15,2,0)
=VLOOKUP(“c5”,’Sheet2′!B3:H5,2,0)
=VLOOKUP(“the value”,’Sheet2′!B3:H15,2,0)

এখানে একটি বিষয় খেয়াল রাখতে হবে, যদি কোন text বা লেখা lookup value হিসেবে দেওয়া হয় তাহলে তাকে অবশ্যই ‍দ্বি-সেমিকোলন দ্বারা আবদ্ধ করতে হবে।

ঙ. সবচেয়ে গুরুত্বপূর্ণ অংশটি হচ্ছে ‘Sheet2’!B3:H15 এই অংশটি যার নাম table array. এই অংশটির কাজ হচ্ছে সীমা নির্ধারণ করা।

B3 সেলে উপরে লিখিত Vlookup ফর্মুলাটি প্রয়োগ করার পর রেজাল্ট কি হল আমরা এখন নিচের চিত্রে সেটি দেখবোঃ 

 

Result After Applying Vlookup Formula

উপরে চিত্রে লক্ষ্য করুন, ২য় টেবিল থেকে ডাটা স্থানান্তর হয়ে প্রথম টেবিলে চলে এসেছে।

এখন আমরা ম্যাথ এর রেজাল্ট কপি করবো, সে ক্ষেত্রে ১ম টেবিল অনুযায়ী সেল পয়েন্টারটি D3 সেলে রাখুন। এবার পুনরায় Vlookup ফর্মুলা প্রয়োগ করার জন্য রিবনের Formulas ট্যাবে ক্লিক তারপর Insert Function এ ক্লিক করুন, একটি ডায়ালগ বক্স আসবে। ডায়ালগ বক্সে Search for a function ঘরে লিখুন Vlookup, টারপর Go তে ক্লিক করুন। দেখবেন Select a Function ঘরে Vlookup এবং এর এর ধর্মি ফাংশন গুলো চলে এসেছে। দেখুন Vlookup Function টি সিলেক্ট অবস্থায় রয়েছে, এবার OK ক্লিক করুন।   

 

Another Applying the Vlookup formula in the Insert Function Dialogue box

উপরের ছবিতে লালদাগ চিহ্নিত নাম্বার গুলোতে লক্ষ্য করুন, ম্যাথ এর রেজাল্ট স্থানান্তর করার জন্য উপরের ক্রমিক নাম্বার অনুযায়ী কমান্ড গুলি ব্যবহার করুন।

উপরের নির্দেশিত নিয়ম অনুসারে কমান্ড করার পর দেখবেন Function Argument নামের পুনরায় একটি ডায়ালগ বক্স এসেছে। এবার এই ডায়ালগ বক্সের চারটি ঘর পাশে লেখা নির্দেশনা অনুযায়ী পুরন করুনঃ

 

Another Applying Vlookup Formula in Function Arguments Dialogue box

উপরের চিত্রে লক্ষ্য করুন,  Function Argument ডায়ালগ বক্সে নির্দেশনা অনুযায়ী ফর্মুলাটি প্রয়োগ করা হয়েছে। নিচে নির্দেশনা গুলোর বিস্তারিত দেয়া হল।

• প্রথম ঘরটিতে Lookup Value অর্থাৎ ১ম টেবিলের B3 থেকে B14 সেলের ডাটা
• দ্বিতীয় ঘরটিতে Table array অর্থাৎ ২য় টেবিলের B3 থেকে H15 সেলের ডাটা
• তৃতীয় ঘরটিতে Column index number অর্থাৎ ২য় টেবিলের ৩ নাম্বার কলাম যে কলামে ম্যাথ বিষয়ের প্রাপ্ত নাম্বার রয়েছে
• চতুর্থ ঘরটিতে Range lookup অর্থাৎ এখানে রেঞ্জ লুকাপ হল ‘0‘  মানে ফর্মুলাটি সত্য তানাহলে মিথ্যা

ম্যাথের প্রাপ্ত নাম্বার স্থানান্তর করার জন্য Vlookup ফর্মুলাটি সঠিক ভাবে প্রয়োগ করার পর রেজাল্ট কি হল এখন আমরা নিচের চিত্রে দেখবঃ 

 

Result After Applying Vlookup Formula 2

উপরের চিত্রে লক্ষ্য করুন, ২য় টেবিল থেকে ম্যাথের নাম্বার স্থানান্তর হয়ে ১ম টেবিলে চলে এসেছে।

এবার Auto Fill Option এর মাধ্যমে নিচের সকল ছাত্রের প্রাপ্ত নাম্বার কপি করুন। আমরা এক্সেলের অন্যান্য ফাংশনের প্রয়োগ যেমন Microsoft Excel এ SUM ফাংশনের ব্যবহার পোস্টে Auto Fill Option সম্পর্কে আলোচনা করেছি, আপনি পোস্টটিতে ভিজিট করে Auto Fill এর ব্যবহার জেনে নিতে পারেন।

 

Use of Auto Fill Option for all Numbers

উপরের চিত্রে লক্ষ্য করুন, Auto Fill Option এর মাধ্যমে পরবর্তী সকল ছাত্রের ইংরেজি ও ম্যাথ বিষয়ে প্রাপ্ত নাম্বার গুলো বের করা হয়েছে।

এবার ম্যাথের নাম্বার থেকে সাইন্স এর ঘরে অর্থাৎ D3 সেলের মানকে কপি করে E3 সেলে পেস্ট করুন। দেখবেন ২য় টেবিল অনুসারে Science বিষয়ে প্রাপ্ত নাম্বার ১ম টেবিলে চলে এসেছে।

 

Copy of Vlookup Formula Math to Science

উপরের চিত্রে লালদাগ চিহ্নিত অংশে দেখুন, D3 সেলের নামকে কপি করে E3 সেলে কপি করার কারনে ২য় টেবিল অনুযায়ী সাইন্স বিষয়ের প্রাপ্ত নাম্বার ১ম টেবিলে চলে এসেছে। এবার Auto Fill Option এর মাধ্যমে নিচের সকল ছাত্রদের সাইন্স বিষয়ে প্রাপ্ত নাম্বার বের করুন।

 

Use of Auto Fill Option for all Numbers of Science Subject

উপরের চিত্রে দেখুন, Auto Fill Option এর মাধ্যমে সাইন্স বিষয়ের প্রাপ্ত নাম্বার গুলো বের করা হয়েছে।ৱ

তো এই ছিল আমাদের Microsoft Excel এ Vlookup ফাংশন ব্যবহার সম্পর্কে আলোচনা। এখন নিশ্চয় বুঝতে পারছেন, কিভাবে Vlookup ফাংশন ব্যবহার করে ডাটা স্থানান্তর করতে হয়। Microsoft Excel এ Vlookup ফাংশন ব্যবহার সম্পর্কে আমরা চেষ্টা করেছি আপনাকে ধারণা দেবার জন্য। আশা করি আপনাদের ভালো লেগেছে, যদি ভালো লেগে থাকে তাহলে অবশ্যই লাইক দিন ও কমেন্ট করে আপনার মন্তব্য আমাদের জানান এবং শেয়ার করে অন্যকেও জানার সুযোগ করে দিন। আর আমাদের সাথেই থাকুন, আরও নতুন কোন বিষয় জানতে।

MS Excel এ সেল ভ্যালুর উপরে ভিত্তি করে ব্যাকগ্রাউন্ড কালার পরিবর্তন করা

অনেক সময় আমরা Excel ওয়ার্কশীটে কোন রেকর্ড অথবা টেবিলে অধিক পরিমানে ডাটা পুট করে থাকি। সেই ডাটাগুলো থেকে কিছু নির্দিষ্ট ডাটাকে বিশেষ ভাবে চিহ্নিত করার জন্য সেলের মান অনুযায়ী সেই সেলের ব্যাকগ্রাউন্ড কালার পরিবর্তন করে হাইলাইট করা যায়। তাই আজ আমরা আলোচনা করবো সেলের ভ্যালূকে কন্ডিশন করে কিভাবে আপনি সেলের ব্যাকগ্রাউন্ড কালার পরিবর্তন করবেন। আসুন তাহলে জেনে নেই MS Excel এ সেল ভ্যালুর উপরে ভিত্তি করে ব্যাকগ্রাউন্ড কালার পরিবর্তন করার নিয়ম ?

ধরুন আপনি একটি রেজাল্টশীটে বিভিন্ন বিষয়ের উপরে প্রাপ্ত মার্কের ভেতর থেকে লেটার মার্ক বা প্লাস মার্ক প্রাপ্ত সেল গুলোকে আলাদা ভাবে হাইলাইট করতে চান। সে ক্ষেত্রে Conditional Formatting অপশনটি ব্যবহার করে ঐ সেলের মার্ক অথবা মানের কন্ডিশনের উপরে ভিত্তি করে সেলের ব্যাকগ্রাউন্ড কালার ব্যবহার করে সেই সেল গুলোকে হাইলাইট করতে পারবেন। তাই আলোচনার সুবিধার্থে আমরা নিচে একটি রেজাল্টশীট ব্যবহার করবোঃ

 

A Complete Result Sheet in Excel

উপরের ছবিতে একটি রেজাল্ট শীট দেয়া হল।

এখন যদি আপনি রেজাল্টশীটে বিভিন্ন বিষয়ে প্রাপ্ত নাম্বার থেকে যে সকল বিষয়ে লেটার মার্ক রয়েছে সেই সকল সেল গুলোতে ব্যাকগ্রাউন্ড কালার ব্যবহার করে হাইলাইট করতে চান। সে ক্ষেত্রে প্রথমে প্রাপ্ত নাম্বারের সেল গুলোকে সিলেক্ট করুন, তারপর Home ট্যাবের Styles গ্রুপের Conditional Formatting অপশনে ক্লিক করুন। সেখানে একটি অপশন মেনু আসবে, এবার অপশন মেনুতে New Rule অপশনে ক্লিক করুন। অপশন ব্যবহারের নিয়ম নিচের ছবিতে দেখুনঃ

 

Use of Conditional Formatting option in Excel

উপরের ছবিতে লক্ষ্য করুন, Conditional Formatting অপশনটি ব্যবহার করার কমান্ড গুলো লালদাগ দ্বারা চিহ্নিত করা হয়েছে।

কন্ডিশনাল ফরম্যাটিং অপশন থেকে New Rule অপশনে ক্লিক করার পর একটি ডায়ালগ বক্স আসবে। এবার ডায়ালগ বক্সে Select a Rule Type ঘরের অপশন গুলো থেকে Format only cells that contain অপশনে ক্লিক করুন। তারপর Format only cells with অপশনের Drop down box গুলো পুরন করুন।

• প্রথম Drop down box এ Cell Value সিলেক্ট করুন
• দ্বিতীয় Drop down box এ Between সিলেক্ট করুন
• তৃতীয় ও চতুর্থ Drop down box এ ফিল্টার কন্ডিশন অর্থাৎ নির্দিষ্ট মান বা লেটার মার্ক 80 ও 100 লিখুন

 

Use of New Formatting Rule Dialog Box from Conditional Formatting in Excel

উপরের ছবিতে ডায়ালগ বক্সের ব্যবহার গুলো লালদাগ দ্বারা চিহ্নিত করা হল। Drop down box গুলো পুরন করা হয়ে গেলে Format অপশনে ক্লিক করুন।

• Format অপশনে ক্লিক করুন
• Format অপশনে ক্লিক করার পরে নতুন একটি ডায়ালগ বক্স আসবে সেখানে Fill ট্যাবে ক্লিক করুন।

তারপর আপনার পছন্দ মতো কালার বাছাই করে তার উপরে ক্লিক করুন, তারপর OK ক্লিক করুন। তাহলে যে সংখ্যার মান কন্ডিশন হিসেবে সিলেক্ট করা হয়েছে সেই সংখ্যার সেলটির ব্যাকগ্রাউন্ড কালার নির্ধারণ হয়ে যাবে।

 

Use of Color for Cell Background from Conditional Formatting in Excel

উপরের ছবিতে কন্ডিশনাল সংখ্যার সেলে কালার ব্যবহার করার জন্য Format Cell ডায়ালগ বক্সের Fill ট্যাব থেকে   কালার ব্যবহার করা হল।

কালার সিলেক্ট করার পর পুনরায় পূর্বের ডায়ালগ বক্সে ফিরে আসবে, সেখানে OK ক্লিক করুন। তাহলে কন্ডিশন অনুযায়ী রেজাল্ট শীটে ৮০ থেকে ১০০ এর মধ্যে প্রাপ্ত নাম্বার গুলোতে ব্যাকগ্রাউন্ড কালার চলে আসবে।

 

Use of Conditional Formatting in Excel

উপরের ছবিতে লক্ষ্য করুন, রেজাল্টশীটে লেটার মার্ক গুলোতে Conditional Formatting অপশনটি ব্যবহার করে ব্যাকগ্রাউন্ড কালার দেয়া হয়েছে। এখন তৈরি কৃত রেজাল্টশীটে নতুন ডাটা পুট করলেও কন্ডিশন অনুযায়ী সেটি আচরণ করবে।

আজকের আলোচনায় আমরা আপনাদেরকে MS Excel এ সেল ভ্যালুর উপরে ভিত্তি করে ব্যাকগ্রাউন্ড কালার পরিবর্তন করার নিয়ম সম্পর্কে ধারণা দেবার চেষ্টা করেছি। পোস্টটি আপনার ভালো লাগলে আশা করি অন্যের সাথে শেয়ার করতে ভুলবেনা ।  ধন্যবাদ