এমএস এক্সেলে কখনো কখনো একটি লিস্ট থেকে কিছু ক্রাইটেরিয়া বা শর্ত অনুযায়ী ভ্যালু নির্ণয় বা খুঁজে বের করতে হয়। ধরুন আমাদের একটি টাস্ক প্ল্যান রয়েছে সংশ্লিষ্ট ব্যক্তির নামসহ যারা প্রতিটি টাস্কে সংশ্লিষ্ট। এখন আমাদের দেওয়া ব্যক্তির ভিত্তিতে সব টাস্কের নাম সারি আকারে লিস্ট আউট করতে হবে। এইভাবে এক্সেল সেল ভ্যালু ভিত্তিতে একটি লিস্ট পপুলেট করার বিভিন্ন উপায় প্রদান করে। এই আর্টিকেলে আমরা দেখব কিভাবে এক্সেলে সেল ভ্যালু ভিত্তিতে একটি লিস্ট পপুলেট করা যায়।
প্র্যাকটিস ওয়ার্কবুক ডাউনলোড
এক্সেলে সেল ভ্যালু ভিত্তিতে লিস্ট পপুলেট করার ৬টি উপায়
১. সেল ভ্যালু ভিত্তিতে অটোফিল (AutoFill) লিস্ট
প্রজেক্ট ওয়ার্কারদের নামসহ লিস্ট প্রজেক্ট করি। প্রতিটি প্রজেক্টে ওয়ার্কারদের নাম এই ফরম্যাটে অ্যাসাইন করা আছে “Project_Number_Name_Serial”। সুতরাং আমাদের কাজ হলো প্রজেক্ট ব্যবহার করে সব ওয়ার্কারের নাম খুঁজে বের করা।
ধাপ ১: D17 সেলে নিচের ফর্মুলা লিখে Enter চাপুন
=IFERROR(INDEX($B$3:$D$11,ROW(B2:D11),MATCH($C$16,$B$3:$D$3,0)),"")ফর্মুলা ব্যাখ্যা
- এখানে MATCH($C$16,$B$3:$D$3,0) এই অংশটি ইনপুট প্রজেক্ট নামকে ডেটাসেটের সাথে ম্যাচ করাচ্ছে এবং শুধু এক্সাক্ট ম্যাচ কনসিডার করছে।
- ROW(B2:D11) ডেটাসেটের সারি সংখ্যা গণনা করছে।
- INDEX($B$3:$D$11, ROW(B2:D11), MATCH($C$16,$B$3:$D$3,0)) ফর্মুলার এই অংশটি ম্যাচ করা প্রজেক্ট ওয়ার্কারদের নাম খুঁজে বের করছে। ডেটা না পাওয়া গেলে #NA এরর থ্রো করবে।
- শেষে IFERROR যেকোনো এরর হ্যান্ডল করার জন্য।
ধাপ ২: এখন ড্রপ-ডাউন লিস্ট থেকে যেকোনো প্রজেক্ট নাম সিলেক্ট করুন
ধাপ ৩: সব ওয়ার্কারের নাম দেখাবে
২. নির্দিষ্ট সেল ভ্যালু দিয়ে ফর্মুলা ব্যবহার করে সারি পপুলেট করা
দেখি কিভাবে অন্য একটি পদ্ধতিতে ওয়ার্কারের নাম সার্চ করা যায়। ডেটাসেটে একজনের একাধিক প্রজেক্টে অ্যাসাইন করা থাকতে পারে। এখন আমাদের কাজ হলো ওয়ার্কারের নাম ব্যবহার করে প্রজেক্টের নাম খুঁজে বের করা। এখানে মেইন ডেটাসেট হবে এইরকম:
ধাপ ১: G6 সেলে নিচের ফর্মুলা লিখে ENTER চাপুন
=FILTER(B4:B16, G5=C4:C16) ফর্মুলা ব্যাখ্যা
- FILTER ফাংশনে, B4:B16 এই হলো वो রেঞ্জ যেখান থেকে ডেটা এক্সট্র্যাক্ট করব।
- G5 এই সেলে ইনপুট নাম দিবে এবং নাম রেঞ্জ C4:C16-এর সাথে তুলনা করবে।
- FILTER ফাংশন সম্পর্কে আরও জানতে এই লিংক চেক করুন
ধাপ ২: এখন G5 সেলে যেকোনো নাম টাইপ করে Enter চাপুন
৩. প্রথম ড্রপ ডাউনে পরিবর্তন ব্লক করা (Block Changes in First Drop Down)
ধরুন আমাদের ভিন্ন ভিন্ন ফুড আইটেমের একাধিক লিস্ট রয়েছে। প্রতিটি লিস্ট অন্যটি থেকে আলাদা এবং স্পেসিফিক ফুড আইটেমটি ভ্যালিড লিস্টে থাকা উচিত
অন্য একটি ওয়ার্কশিটে, আমরা ফুড টাইপ অনুযায়ী ফুড আইটেম সিলেক্ট করব।
আমাদের উদ্বেগ হলো যদি কলাম B-এ যেকোনো ফুড টাইপ সিলেক্ট করা হয় তবে কলাম C (Items) তে শুধু সেই ফুড টাইপ বা লিস্টের অধীনে থাকা আইটেমগুলোই উপলব্ধ থাকবে।
ধাপ ১: Food Items সেলগুলো সিলেক্ট করে Data Validation খুলুন
ধাপ ২: Source-এ নিচের ফর্মুলা লিখুন
=IF(B4="",Foods, INDIRECT("FakeRange"))
ধাপ ৩: একটি ওয়ার্নিং পপ আপ হবে। Yes বাটনে ক্লিক করুন
ধাপ ৪: এখন Food Type সিলেক্ট করুন এবং তারপর Items সিলেক্ট করুন
ধাপ ৫: একবার Food Type এবং Items ইনপুট দিলে আপনি কোনো ফুড আইটেম পরিবর্তন করতে পারবেন না। তাই মিসম্যাচের কোনো সুযোগ নেই
৪. ক্রাইটেরিয়া ভিত্তিতে এক্সেলে ইউনিক লিস্ট তৈরি
ইউনিক ভ্যালু লিস্ট খুঁজে বের করার জন্য এক্সেল বিভিন্ন পদ্ধতি প্রদান করে। মেথড ২-এর মতো একই ডেটাসেট বিবেচনা করুন ডুপ্লিকেট ভ্যালুসহ। এখন আমাদের লক্ষ্য হলো ফর্মুলা ব্যবহার করে ইউনিক লিস্ট খুঁজে বের করা।
ধাপ ১: G6 সেলে নিচের ফর্মুলা লিখুন
=UNIQUE(FILTER(B4:B22,C4:C22=G5))
- FILTER(B4:B22, C4:C22=G5) এই ফাংশন মেথড ২-এর মতোই। এটি ডেটাসেট থেকে সব ম্যাচ করা নাম এক্সট্র্যাক্ট করে। ডুপ্লিকেট ম্যাচ থাকলে FILTER ফাংশন সেগুলোকেও কাউন্ট করে।
- ডুপ্লিকেট ভ্যালু রিমুভ করতে FILTER ফাংশন থেকে রিটার্ন হওয়া ডেটা থেকে UNIQUE ফাংশন ব্যবহার করা হয়েছে। এই ফাংশন ম্যাচ করা ডেটা থেকে সব ডুপ্লিকেট ভ্যালু রিমুভ করবে। ফাংশন সম্পর্কে আরও জানতে এই লিংক ভিজিট করুন
ধাপ ২: এখন G5 সেলে যেকোনো Name লিখে Enter চাপুন
৫. অ্যারে ফর্মুলা ব্যবহার করে এক কলামে ক্রাইটেরিয়া মিট করা সকল সারি এক্সট্র্যাক্ট করা
ধরুন একটি প্রোডাক্ট ডেটাসেট রয়েছে যাদের ID, Brand, Model, এবং Unit Price। এখন আমাদের কাজ হলো সেই সারিগুলো খুঁজে বের করা যেখানে Brand নাম আমাদের ইনপুট করা Brand Names H5 এবং H7 সেলে মিলে যাবে।
ধাপ ১: B19 সেলে নিচের ফর্মুলা লিখে CTRL + SHIFT + ENTER চাপুন এবং পুরো টেবিলে ফর্মুলা কপি করুন।
=INDEX($B$4:$E$15, SMALL(IF(COUNTIF($H$5:$H$7,$C$4:$C$15), MATCH(ROW($B$4:$E$15), ROW($B$4:$E$15)), ""), ROWS(B19:$B$19)), COLUMNS($B$3:B3
ধাপ ২: এখন H5 এবং H7 সেলে নাম লিখে Enter চাপুন
৬. এক্সেলে ডিপেনডেন্ট ড্রপ-ডাউন লিস্ট তৈরি
এমএস এক্সেলে ড্রপ-ডাউন লিস্ট একটি ইউজফুল ফিচার যখন আমরা ডেটা এন্ট্রি ফর্ম বা এক্সেল ড্যাশবোর্ড পারফর্ম করি।
এটি একটি সেলে আইটেমের লিস্ট ড্রপ-ডাউন হিসেবে দেখায়, এবং ইউজার ড্রপ-ডাউন থেকে সিলেক্ট করতে পারে। এটি উপকারী যখন আপনার কাছে নাম, প্রোডাক্ট, বা রিজনের একটি লিস্ট থাকে যা আমরা ঘন ঘন সেটি সেলে ইনপুট দিই।
ধরুন আমাদের তিনটি ভিন্ন ফুড আইটেম লিস্ট রয়েছে, এখন সেগুলো ব্যবহার করে এক্সেলে ডিপেনডেন্ট ড্রপ-ডাউন লিস্ট তৈরি করব।
ধাপ ১: Data Validation অপশন খুলুন
ধাপ ২: Data Validation উইন্ডোতে, Allow হিসেবে List সিলেক্ট করুন এবং Source নিচের মতো সিলেক্ট করুন
ধাপ ৩: Food Types কলামে একটি Drop-Down লিস্ট পাবেন
ধাপ ৪: এখন সকল ডেটাসেট সিলেক্ট করুন এবং Formulas ট্যাবে Create from Selection অপশনে ক্লিক করুন
ধাপ ৫: একটি পপ-আপ আসবে। Top row-এ ক্লিক করুন এবং তারপর Ok বাটন চাপুন
ধাপ ৬: এখন D14 সেলে যান এবং Data Validation খুলুন। নিশ্চিত করুন Allow সেট করা আছে List হিসেবে এবং তারপর Source-এ নিচের ফর্মুলা লিখুন। শেষে OK বাটন চাপুন
=INDIRECT(B14)
ধাপ ৭: একটি ওয়ার্নিং পপ আপ হবে। Yes বাটন চাপুন
ধাপ ৮: এখন প্রথম ড্রপ ডাউন লিস্ট থেকে যেকোনো Food Types সিলেক্ট করুন এবং অন্য ড্রপ-ডাউন লিস্টে সংশ্লিষ্ট Items লিস্ট পান
ধাপ ৮: ফাইনাল আউটপুট এইরকম হবে:
মনে রাখবেন
| সাধারণ এরর | কখন দেখায় |
|---|---|
| #VALUE! Error In FILTER | include আর্গুমেন্টের ডাইমেনশন array আর্গুমেন্টের সাথে কম্প্যাটিবল হতে হবে, অন্যথায় FILTER #VALUE! রিটার্ন করবে |
| #NA! Error | যদি ফর্মুলা ডেটাসেট থেকে কোনো ডেটা না পায়, তবে এটি এই এরর রিটার্ন করবে। এই এরর হ্যান্ডল করতে IFERROR ফাংশন ব্যবহার করতে হবে। |
| Naming in List | লিস্টের নাম দিতে スペেস ব্যবহার করা যাবে না। যদি নামে স্পেস থাকে তবে “_” ব্যবহার করতে পারেন। |
উপসংহার
এই হলো এক্সেলে সেল ভ্যালু ভিত্তিতে লিস্ট পপুলেট করার কিছু পদ্ধতি। আমি সব মেথডকে তাদের উদাহরণসহ দেখিয়েছি কিন্তু আরও অনেক ইটারেশন হতে পারে। ব্যবহৃত ফাংশনগুলোর মৌলিক বিষয়ও আলোচনা করেছি। আপনার অন্য কোনো মেথড থাকলে আমাদের সাথে শেয়ার করতে পারেন।
আরও পড়ুন
- এক এক্সেল ওয়ার্কশিট থেকে অন্যেকটিতে অটোমেটিক্যালি ডেটা ট্রান্সফার
- অন্য একটি ওয়ার্কশিট থেকে অটো পপুলেট এক্সেলে
- এক্সেলে লাস্ট রো পর্যন্ত ডেটা সহ ফিল ডাউন (৩ কুইক মেথড)
