Unlimited WordPress themes, graphics, videos & courses! Unlimited asset downloads! From $16.50/m
Advertisement
  1. Computer Skills
  2. Office

কিভাবে VLOOKUP, MATCH এবং INDEX ব্যবহার করে স্প্রেডশীট থেকে তথ্য বের করে আনবেন

by
Difficulty:IntermediateLength:LongLanguages:
This post is part of a series called Spreadsheets for Finance.
Spreadsheets for Finance: Calculating Internal Rate of Return
Getting Started With Numbers on OS X

Bengali (বাংলা) translation by Shakila Humaira (you can also view the original English article)

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

MATCH এবং INDEX ফাংশনটি ব্যবহার করা ভাল যখন আপনি নির্দিষ্ট ডাটার অবস্থান খুঁজে বের করতে চান। যেমন কলাম বা সারি, যা একজন ব্যক্তির নামে থাকে এবং তার সাথে সংশ্লিষ্ট হয়।

প্রিমিয়াম অপশন

এক্সেল ফাংশনগুলিতে প্রবেশ করার পূর্বে, আপনি জানেন কি যে Envato মার্কেটে এমন একটি এক্সেল স্ক্রিপ্ট এবং প্লাগইন আছে যা আপনাকে এই কাজটি আরও ভালভাবে সম্পাদন করতে সাহায্য করবে?

উদাহরণস্বরূপ, এটি দিয়ে আপনি যা করতে পারবেন:

Excel scripts and plugins on Envato Market
এনভেটো মার্কেটে এক্সেল স্ক্রিপ্ট এবং প্লাগিন

স্ক্রিনকাস্ট

আপনার নিজস্ব এক্সেল ফাইল ব্যবহার করে আপনি যদি এই টিউটোরিয়ালটি অনুসরণ করতে চান, তবে তাই করুন। অথবা আপনি চাইলে, এই টিউটোরিয়ালের মধ্যে অন্তর্ভুক্ত জিপ ফাইলটি ডাউনলোড করতে পারেন, যেখানে vlookup example.xlsx নামে একটি স্যাম্পল ওয়ার্কবুক আছে।

VLOOKUP এর ব্যবহার

যখন VLOOKUP এমন কোনও শনাক্তকারী খুঁজে পায়, যা আপনি সোর্স ডেটাতে উল্লেখ করেছেন, এটি তারপর নির্দিষ্ট সারি থেকে যেকোনো সেল বা ঘর খুঁজে বের করে আপনার কাছে নির্দিষ্ট তথ্য পাঠাতে পারে। লক্ষ্য করুন সোর্স ডেটাতে, শনাক্তকারীটির অবশ্যই টেবিলের প্রথম কলামে থাকা আবশ্যক।

unique identifiers for vlookup
একটি ইউনিক বা অনন্য শনাক্তকারীকে একটি ক্রমিক নাম্বারের মত হওয়া উচিত, যেখানে একই টেবিলের মধ্যে দুটি একই রকম নয়।

শব্দবিন্যাস

VLOOKUP ফাংশনের শব্দবিন্যাস হচ্ছে:

=VLOOKUP(lookup value, table range, column number, [true/false])

এখানে এই আর্গুমেন্টগুলো যা বোঝায়:

  • Lookup value. যে সেলের একটি অনন্য আইডেন্টিফায়ার বা শনাক্তকারী আছে।
  • Table range. প্রথম কলামে শনাক্তকারী সেলগুলোর পরিসরে, অন্য কলামগুলি বাকি তথ্য অনুসরণ করে।
  • Column number. আপনি যে ডেটা খুঁজছেন তার মোট কলামের সংখ্যা। কলামের অক্ষরের সঙ্গে এটা কোনও বিভ্রান্তি সৃষ্টি করবে না। উপরের চিত্রের মধ্যে, কলাম ৪-এ স্ট্যাটস বা অঙ্গরাজ্য গুলোর নাম আছে।
  • True/False. এই যুক্তিটি ঐচ্ছিক। True মানে একটি আনুমানিক সদৃশ্যতা গ্রহণযোগ্য, এবং False মানে শুধুমাত্র একটি সঠিক এবং একই রকম সদৃশ্যতা গ্রহণযোগ্য।

উপরের চিত্রে আমরা টেবিল থেকে বিক্রির পরিমান খুঁজে বের করতে চাচ্ছি, তাই আমরা নীচের আর্গুমেন্ট বা যুক্তিটি ব্যবহার করবো:

syntax of vlookup
VLOOKUP ফাংশনের শব্দবিন্যাস

একটি পূর্ণ রেফারেন্স তৈরি করতে রেঞ্জের নাম সংজ্ঞায়িত করা

Vlookup example.xlsx এর ভিতর, সেলস অ্যামাউন্ট বা বিক্রয় পরিমানের ওয়ার্কশীটটি দেখুন। আমরা B5 কলামে সূত্রটি লিখব, তারপর সূত্রটি পত্রকের নিচে কপি করার জন্য অটোফিল বা স্বতঃপূরণ বৈশিষ্ট্যটি ব্যবহার করবো। এর মানে হচ্ছে ফর্মুলার ভিতরের টেবিল রেঞ্জটি একটি পূর্ণ রেফারেন্স হতে হবে। এটা করার সবচেয়ে ভালো উপায় হচ্ছে সারণীর নির্দিষ্ট পরিসরটিকে একটি নামে সংজ্ঞায়িত করা।

এক্সেলে একটি পরিসরের নাম সংজ্ঞায়িত করা।

  1. ফর্মুলা প্রবেশ করানোর পূর্বে, সোর্স ডাটা ওয়ার্কশিটে যান। 
  2. A4 থেকে সবগুলো সেল বা ঘর সিলেক্ট করুন (অর্ডার #column এর হেডারের জন্য) এবং H203 পর্যন্ত যান। এটা করার একটি দ্রুত উপায় হচ্ছে A4 -এ ক্লিক করা, এরপর Ctrl-Shift-End চাপুন (ম্যাকের জন্য Command-Shift-End)।
  3. কলাম A এর উপরে Name Box এর ভিতর ক্লিক করুন (এবার নেম বক্সটি A4 কলামটি দেখাচ্ছে)।
  4. data টাইপ করুন, তারপর Enter চাপুন।
  5. আপনি এবার ফর্মুলায় $A$4:$H$203 এর পরিবর্তে নামের তথ্য ব্যবহার করতে পারেন। 
Name box displaying a range name
নাম বাক্স সাধারণত বর্তমান সেলের ঠিকানা প্রদর্শন করে। এটাতে ক্লিক করুন এবং পরিসর নির্ধারণ করতে একটি নাম টাইপ করুন।

গুগল শীটে পরিসরের নাম নির্ধারণ করা

গুগল শীটে, নাম নির্ধারণ করা কিছুটা ব্যতিক্রম হতে পারে।

  1. আপনার সোর্স ডাটার প্রথম কলাম হেডারে ক্লিক করুন, তারপর Ctrl-Shift-Right Arrow চাপুন। (ম্যাকে Command-Shift-Right Arrow চাপুন)। এগুলো কলাম হেডারের সারিগুলোকে সিলেক্ট করবে।
  2. Ctrl-Shift-Down Arrow চাপুন (ম্যাকে Command-Shift-Down Arrow চাপুন)। এটা প্রকৃত তথ্য সিলেক্ট করবে।
  3. তথ্য মেনুতে ক্লিক করুন, তারপর নামযুক্ত এবং সুরক্ষিত পরিসর নির্বাচন করুন।
  4. নাম এবং সুরক্ষিত পরিসর বা রেঞ্জ বাক্সের ডান পার্শ্ব থেকে, data টাইপ করুন, তারপর সম্পন্ন/Done ক্লিক করুন।
Data menu in Google Sheets
গুগল শীটে নির্দিষ্ট পরিসর বা রেঞ্জের নাম নির্ধারণ করা।

ফর্মুলা প্রবেশ করানো।

ফর্মুলা প্রবেশ করাতে, Sales Amounts ওয়ার্কশীটে যান এবং B5 এ ক্লিক করুন।

ফর্মুলা প্রবেশ করান:

=VLOOKUP(A5,data,8,FALSE)

Enter চাপুন।

entering the vlookup function
VLOOKUP ফাংশন প্রবেশ করানো

ফলাফল 40 হতে হবে। কলামের নীচ পর্যন্ত মান পূর্ণ করার জন্য, যদি প্রয়োজন হয়, তবে পুনরায় B5 এ ক্লিক করুন। সেল বা ঘরের নীচের দিকের ডানপাশে AutoFill এ মাউস পয়েন্টারটি রাখুন, যাতে মাউস পয়েন্টারটি ক্রস চিহ্নের মত হয়ে যায়।

mouse pointer on the autofill dot
যখন আপনি মাউস পয়েণ্টারটি কোনও সেলের নীচের দিকে ডান পার্শ্বের কর্নারে স্থাপন করবেন, এটা একটা AutoFill বা স্বতঃপূর্ণ ক্রস চিহ্নে পরিণত হবে।

কলামের নীচে মানগুলো পূর্ণ করার জন্য ডাবল-ক্লিক করুন।

worksheet with data after using autofill
কলামের নীচ থেকে ফর্মুলা কপি করার জন্য AutoFill এর ক্রস চিহ্নে ডাবল-ক্লিক করুন।

যদি আপনি চান তবে, অন্য ক্ষেত্রগুলি বের করতে পরবর্তী কয়েকটি কলামে VLOOKUP ফাংশনটি চালাতে পারেন, যেমন শেষ নাম বা অঙ্গরাজ্য গুলোর জন্য।

MATCH এর ব্যবহার

MATCH ফাংশনটি ডাটার প্রকৃত মান আপনার কাছে ফেরত নিয়ে আসবে না; কিন্তু আপনি যে মানটি খুঁজছেন সেটি প্রদান করলে এই ফাংশনটি তার অবস্থান কোথায় তা জানিয়ে দিবে। এটা অনেকটা #135 মেইনস্ট্রীট কোথায় তা জিজ্ঞাসা করার মত, এবং এই উত্তর পাবার মত, যে এটা রাস্তার শেষ মাথায় ৪র্থ বিল্ডিংটি।

শব্দবিন্যাস

MATCH ফাংশনের শব্দবিন্যাস হচ্ছে:

=MATCH(lookup value, table range, [match type])

আর্গুমেন্টগুলো বা চুক্তিগুলো হচ্ছে:

  • Lookup value. যেই সেলটিতে ইউনিক আইডেন্টিফায়ার/অনন্য শনাক্তকারী আছে।
  • Table range. আপনার অনুসন্ধান করা সেল বা ঘরগুলোর পরিসীমা।
  • Match type. ঐচ্ছিক। এটা আপনার চাওয়া এই সাদৃশ্যটি কতটা নিকটে তা নির্দিষ্ট করবে, যা নিন্মরূপ:

পরবর্তী সর্বোচ্চ মান

-1

ভ্যালু বা মান অবশ্যই অধঃক্রমে সাজাতে হবে।

লক্ষ্যের মান

0

মূল্য যেকোন ক্রমে হতে পারে।

পরবর্তী সর্বনিম্ন মান

1

ডিফল্ট টাইপ। মূল্য বা মান অবশ্যই ঊর্ধ্বমুখী ক্রমের মধ্যে হতে হবে।

VLOOKUP ফাংশনের মতই, সম্ভবতঃ MATCH ফাংশনটিও সহজে ব্যবহারযোগ্য, যদি আপনি রেঞ্জ বা পরিসরের নাম নির্ধারণ করে দেন। Source Data শীটে যান, নিচ থেকে B4 (ক্রমানুসারে কলাম হেডার) সিলেক্ট করুন, কলাম A এর নাম বাক্সের উপর ক্লিক করুন, এবং এটাকে order_number হিসেবে কল করুন। লক্ষ্য রাখুন, মানগুলি যেন ঊর্ধ্বমুখী ক্রমানুসারে হয়।

name box with one column defined
একটি নামের রেঞ্জ বা পরিসর হতে পারে শুধুমাত্র একটি কলামে, একটি সারিতে, অথবা এমনকি একটি সেল বা ঘরে। 

ওয়ার্কশীটের সদৃশ বা Match ট্যাবে যান। B5 এর ভিতর, MATCH ফাংশন প্রবেশ করান:

=MATCH(A5,order_number,1)

entering the match function
MATCH ফাংশন প্রবেশ করানো

যদি আপনি একটি রেঞ্জ বা পরিসরের নাম সংজ্ঞায়িত করতে না পারেন, তবে ফাংশনটি এভাবে লিখবেন:

=MATCH(A5,'Source Data'!A5:A203,0)

অন্যভাবে, আপনি এটাকে ১৪তম পজিশন বা অবস্থানে দেখতে পাবেন (ক্রমানুসারে ১৩তম অবস্থানের জন্য)।

completed match function
MATCH ফাংশনের ফলাফল

INDEX ব্যবহার করে

INDEX ফাংশনটি MATCH ফাংশনের বিপরীত এবং VLOOKUP এর অনুরূপ। আপনি যে তথ্য চান তা সারি এবং কলামের ফাংশনটিতে লিখুন এবং এটি আপনাকে সেল বা কক্ষের ভিতরের মূল্য বা মান সম্পর্কে অবহিত করবে।

শব্দবিন্যাস

INDEX ফাংশনের শব্দবিন্যাসটি হচ্ছে:

=INDEX(data range, row number, [column number])

আর্গুমেন্ট বা যুক্তিগুলো হচ্ছে:

  • Data range. অন্যান্য দুটি ফাংশনের মতই, এটি হচ্ছে ডাটা টেবিল বা তথ্য সারণী।
  • Row number. তথ্য সারির সংখ্যা, ওয়ার্কশীটের সারি হওয়া আবশ্যক নয়। যদি, টেবিল রেঞ্জ বা পরিসর ১০  নম্বর সারি থেকে শুরু হয়, তাহলে এটা হচ্ছে #1 সারি।
  • Column number. ডাটা পরিসরের কলাম নম্বর। যদি পরিসরটি কলাম E থেকে শুরু হয়, তাহলে এটা কলাম #1।

এক্সেলের ডকুমেন্টেশনের কলাম নম্বর যুক্তিটি ঐচ্ছিক, কিন্তু সারি নম্বরটিও অনেকটা ঐচ্ছিক ধরণের। যদি টেবিল রেঞ্জ বা পরিসরের শুধুমাত্র একটি সারি বা কলাম থাকে, তাহলে আপনার অন্য কোনও আর্গুমেন্ট বা যুক্তি ব্যবহার করার প্রয়োজন নেই।

ওয়ার্কবুকের Index শীটে যান এবং C6 -এ ক্লিক করুন। আমরা প্রথমে টেবিলের নবম সারি , কলাম ৩ -এ কি অন্তর্ভুক্ত করা হয়েছে তা খুঁজে বের করতে চাই। এই ফর্মুলায়, আমরা একটি নামের পরিসর বা রেঞ্জ নেম ব্যবহার করবো যেটা আগেই তৈরি করা হয়েছে।

ফর্মুলা প্রবেশ করান:

=INDEX(data,A6,B6)

entering the index function
সূচক বা ইন্ডেক্সের ফাংশন প্রবেশ করানো।

এটি একটি গ্রাহকের শেষ নাম ফেরত আনবে: Strevell। A6 এবং B6 এর মান পরিবর্তন করুন, এবং C6 এর ফলাফল ভিন্ন রকম ফল প্রকাশ করবে (মনে রাখবেন, অনেকগুলো সারির একই স্টেটস/অঙ্গরাজ্য এবং পণ্যের নামসমূহ আছে)।

পরিশিষ্ট

একটি ওয়ার্কশীট থেকে অন্য ওয়ার্কশীট দেখা এবং তথ্যের সার-নির্যাস বের করে নেয়ার জন্য এটা হচ্ছে একটি দুর্দান্ত হাতিয়ার। এইভাবে, যদি আপনার কাছে কেবলমাত্র একটি শীটই থাকে, যেখানে বিভিন্ন বিষয়ে প্রয়োজনীয় সমস্ত তথ্যই আছে, তখন আপনাকে নির্দিষ্ট দৃষ্টান্তের জন্য প্রয়োজন অনুযায়ী ফাংশন ব্যবহার করে তা বের করে নিতে হবে।

Advertisement
Advertisement
Advertisement
Advertisement
Looking for something to help kick start your next project?
Envato Market has a range of items for sale to help get you started.