() translation by (you can also view the original English article)
যখন আপনি কোন সারণীর একটি কলাম থেকে তথ্য খুঁজে বের করতে চান এবং অন্য সারণীতে তা ঢোকাতে চান, তখন VLOOKUP ফাংশনটি ব্যবহার করুন। এই ফাংশনটি উইন্ডোজ এবং ম্যাক কম্পিউটারে এক্সেলের যে কোনো সংস্করণে এবং গুগল শীটেও কাজ করে। এটা আপনাকে কোনও একটি সারণী থেকে বেশ কিছু শনাক্তকারীর মাধ্যমে অন্য সারণী থেকে একই রকম তথ্য খুঁজে বের করতে সহায়তা করে। এই দুটি সারণী ভিন্ন ভিন্ন শিটে বা এমনকি ভিন্ন ওয়ার্কবুকেও হতে পারে। এখানে HLOOKUP নামে আরেকটি ফাংশন আছে, যা একই কাজ করে, কিন্তু কেবলমাত্র সেই তথ্যগুলোর ক্ষেত্রে, যেগুলো আনুভূমিক ভাবে বিভিন্ন সারিতে সাজানো আছে।
MATCH এবং INDEX ফাংশনটি ব্যবহার করা ভাল যখন আপনি নির্দিষ্ট ডাটার অবস্থান খুঁজে বের করতে চান। যেমন কলাম বা সারি, যা একজন ব্যক্তির নামে থাকে এবং তার সাথে সংশ্লিষ্ট হয়।
প্রিমিয়াম অপশন
এক্সেল ফাংশনগুলিতে প্রবেশ করার পূর্বে, আপনি জানেন কি যে Envato মার্কেটে এমন একটি এক্সেল স্ক্রিপ্ট এবং প্লাগইন আছে যা আপনাকে এই কাজটি আরও ভালভাবে সম্পাদন করতে সাহায্য করবে?
উদাহরণস্বরূপ, এটি দিয়ে আপনি যা করতে পারবেন:
- ওয়ার্ডপ্রেস থেকে তথ্য এক্সেলে আমদানি করতে।
- পিএইচপি ক্লাস ব্যবহার করে এক্সেল থেকে তথ্য বিশ্লেষণ এবং উদ্ধার করতে।
- একটি এক্সেল স্প্রেডশীটকে রেস্পন্সিভ HTML সারণীতে রূপান্তর করতে।
- এক্সেল ফাইলগুলোকে .NET তথ্য সারণীতে রূপান্তর করতে।
- এবং আরও অনেক কিছু।
স্ক্রিনকাস্ট
আপনার নিজস্ব এক্সেল ফাইল ব্যবহার করে আপনি যদি এই টিউটোরিয়ালটি অনুসরণ করতে চান, তবে তাই করুন। অথবা আপনি চাইলে, এই টিউটোরিয়ালের মধ্যে অন্তর্ভুক্ত জিপ ফাইলটি ডাউনলোড করতে পারেন, যেখানে vlookup example.xlsx নামে একটি স্যাম্পল ওয়ার্কবুক আছে।
VLOOKUP এর ব্যবহার
যখন VLOOKUP এমন কোনও শনাক্তকারী খুঁজে পায়, যা আপনি সোর্স ডেটাতে উল্লেখ করেছেন, এটি তারপর নির্দিষ্ট সারি থেকে যেকোনো সেল বা ঘর খুঁজে বের করে আপনার কাছে নির্দিষ্ট তথ্য পাঠাতে পারে। লক্ষ্য করুন সোর্স ডেটাতে, শনাক্তকারীটির অবশ্যই টেবিলের প্রথম কলামে থাকা আবশ্যক।
শব্দবিন্যাস
VLOOKUP ফাংশনের শব্দবিন্যাস হচ্ছে:
=VLOOKUP(lookup value, table range, column number, [true/false])
এখানে এই আর্গুমেন্টগুলো যা বোঝায়:
- Lookup value. যে সেলের একটি অনন্য আইডেন্টিফায়ার বা শনাক্তকারী আছে।
- Table range. প্রথম কলামে শনাক্তকারী সেলগুলোর পরিসরে, অন্য কলামগুলি বাকি তথ্য অনুসরণ করে।
- Column number. আপনি যে ডেটা খুঁজছেন তার মোট কলামের সংখ্যা। কলামের অক্ষরের সঙ্গে এটা কোনও বিভ্রান্তি সৃষ্টি করবে না। উপরের চিত্রের মধ্যে, কলাম ৪-এ স্ট্যাটস বা অঙ্গরাজ্য গুলোর নাম আছে।
- True/False. এই যুক্তিটি ঐচ্ছিক। True মানে একটি আনুমানিক সদৃশ্যতা গ্রহণযোগ্য, এবং False মানে শুধুমাত্র একটি সঠিক এবং একই রকম সদৃশ্যতা গ্রহণযোগ্য।
উপরের চিত্রে আমরা টেবিল থেকে বিক্রির পরিমান খুঁজে বের করতে চাচ্ছি, তাই আমরা নীচের আর্গুমেন্ট বা যুক্তিটি ব্যবহার করবো:
একটি পূর্ণ রেফারেন্স তৈরি করতে রেঞ্জের নাম সংজ্ঞায়িত করা
Vlookup example.xlsx এর ভিতর, সেলস অ্যামাউন্ট বা বিক্রয় পরিমানের ওয়ার্কশীটটি দেখুন। আমরা B5 কলামে সূত্রটি লিখব, তারপর সূত্রটি পত্রকের নিচে কপি করার জন্য অটোফিল বা স্বতঃপূরণ বৈশিষ্ট্যটি ব্যবহার করবো। এর মানে হচ্ছে ফর্মুলার ভিতরের টেবিল রেঞ্জটি একটি পূর্ণ রেফারেন্স হতে হবে। এটা করার সবচেয়ে ভালো উপায় হচ্ছে সারণীর নির্দিষ্ট পরিসরটিকে একটি নামে সংজ্ঞায়িত করা।
এক্সেলে একটি পরিসরের নাম সংজ্ঞায়িত করা।
- ফর্মুলা প্রবেশ করানোর পূর্বে, সোর্স ডাটা ওয়ার্কশিটে যান।
- A4 থেকে সবগুলো সেল বা ঘর সিলেক্ট করুন (অর্ডার #column এর হেডারের জন্য) এবং H203 পর্যন্ত যান। এটা করার একটি দ্রুত উপায় হচ্ছে A4 -এ ক্লিক করা, এরপর Ctrl-Shift-End চাপুন (ম্যাকের জন্য Command-Shift-End)।
- কলাম A এর উপরে Name Box এর ভিতর ক্লিক করুন (এবার নেম বক্সটি A4 কলামটি দেখাচ্ছে)।
- data টাইপ করুন, তারপর Enter চাপুন।
- আপনি এবার ফর্মুলায় $A$4:$H$203 এর পরিবর্তে নামের তথ্য ব্যবহার করতে পারেন।
গুগল শীটে পরিসরের নাম নির্ধারণ করা
গুগল শীটে, নাম নির্ধারণ করা কিছুটা ব্যতিক্রম হতে পারে।
- আপনার সোর্স ডাটার প্রথম কলাম হেডারে ক্লিক করুন, তারপর Ctrl-Shift-Right Arrow চাপুন। (ম্যাকে Command-Shift-Right Arrow চাপুন)। এগুলো কলাম হেডারের সারিগুলোকে সিলেক্ট করবে।
- Ctrl-Shift-Down Arrow চাপুন (ম্যাকে Command-Shift-Down Arrow চাপুন)। এটা প্রকৃত তথ্য সিলেক্ট করবে।
- তথ্য মেনুতে ক্লিক করুন, তারপর নামযুক্ত এবং সুরক্ষিত পরিসর নির্বাচন করুন।
-
নাম এবং সুরক্ষিত পরিসর বা রেঞ্জ বাক্সের ডান পার্শ্ব থেকে, data টাইপ করুন, তারপর সম্পন্ন/Done ক্লিক করুন।
ফর্মুলা প্রবেশ করানো।
ফর্মুলা প্রবেশ করাতে, Sales Amounts ওয়ার্কশীটে যান এবং B5 এ ক্লিক করুন।
ফর্মুলা প্রবেশ করান:
=VLOOKUP(A5,data,8,FALSE)
Enter চাপুন।
ফলাফল 40 হতে হবে। কলামের নীচ পর্যন্ত মান পূর্ণ করার জন্য, যদি প্রয়োজন হয়, তবে পুনরায় B5 এ ক্লিক করুন। সেল বা ঘরের নীচের দিকের ডানপাশে 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 হিসেবে কল করুন। লক্ষ্য রাখুন, মানগুলি যেন ঊর্ধ্বমুখী ক্রমানুসারে হয়।
ওয়ার্কশীটের সদৃশ বা Match ট্যাবে যান। B5 এর ভিতর, MATCH ফাংশন প্রবেশ করান:
=MATCH(A5,order_number,1)
যদি আপনি একটি রেঞ্জ বা পরিসরের নাম সংজ্ঞায়িত করতে না পারেন, তবে ফাংশনটি এভাবে লিখবেন:
=MATCH(A5,'Source Data'!A5:A203,0)
অন্যভাবে, আপনি এটাকে ১৪তম পজিশন বা অবস্থানে দেখতে পাবেন (ক্রমানুসারে ১৩তম অবস্থানের জন্য)।
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)
এটি একটি গ্রাহকের শেষ নাম ফেরত আনবে: Strevell। A6 এবং B6 এর মান পরিবর্তন করুন, এবং C6 এর ফলাফল ভিন্ন রকম ফল প্রকাশ করবে (মনে রাখবেন, অনেকগুলো সারির একই স্টেটস/অঙ্গরাজ্য এবং পণ্যের নামসমূহ আছে)।
পরিশিষ্ট
একটি ওয়ার্কশীট থেকে অন্য ওয়ার্কশীট দেখা এবং তথ্যের সার-নির্যাস বের করে নেয়ার জন্য এটা হচ্ছে একটি দুর্দান্ত হাতিয়ার। এইভাবে, যদি আপনার কাছে কেবলমাত্র একটি শীটই থাকে, যেখানে বিভিন্ন বিষয়ে প্রয়োজনীয় সমস্ত তথ্যই আছে, তখন আপনাকে নির্দিষ্ট দৃষ্টান্তের জন্য প্রয়োজন অনুযায়ী ফাংশন ব্যবহার করে তা বের করে নিতে হবে।
