এক্সেলে কখনো কখনো ক্রাইটেরিয়া অনুযায়ী একটি লিস্ট জেনারেট করতে হয়। আজ আমরা দেখব কিভাবে ক্রাইটেরিয়া ভিত্তিতে লিস্ট জেনারেট করতে হয়। এই সেশনে আমরা এক্সেল ৩৬৫ ব্যবহার করছি, যদিও এই ভার্সন রিকমেন্ডেড, আপনি আপনার ভার্সন ব্যবহার করতে পারেন।
প্রথমে, আমাদের উদাহরণের ভিত্তি থাকা ডেটাসেট সম্পর্কে জানি।
এখানে বিভিন্ন লোকেশন থেকে বেশ কিছু মানুষের ডেটাসেট রয়েছে তাদের ভেকলসহ। এই ডেটা ব্যবহার করে আমরা ক্রাইটেরিয়া ভিত্তিতে একটি লিস্ট ফর্ম করব।
নোট করুন এটি ডামি ডেটাসহ একটি বেসিক টেবিল যাতে বিষয়টি সহজ রাখা হয়েছে। বাস্তব পরিস্থিতিতে আপনি বড় এবং জটিল ডেটাসেটের সম্মুখীন হতে পারেন।
প্র্যাকটিস ওয়ার্কবুক
নিচের লিংক থেকে প্র্যাকটিস ওয়ার্কবুক ডাউনলোড করতে পারেন।
ক্রাইটেরিয়া ভিত্তিতে লিস্ট জেনারেট করা
উদাহরণস্বরূপ, আমরা তাদের রিয়েন অনুযায়ী মানুষের লিস্ট তৈরি করব।
যেহেতু এটি ছোট ডেটাসেট, আমরা জানি ৪টি রিজন রয়েছে। আমরা রিজনদের নাম সংরক্ষণ করেছি এবং রিজন অনুযায়ী লিস্ট খুঁজব।
১. INDEX-SMALL কম্বিনেশন ব্যবহার করে লিস্ট জেনারেট করা
এখানে আমাদের লিস্ট প্রয়োজন, তাই আমাদের ফর্মুলাটি এমন হতে হবে যা টেবিল থেকে একাধিক ভ্যালু রিট্রিভ করবে। সেই কাজে আমরা INDEX এবং SMALL ফাংশনের কম্বিনেশন ব্যবহার করতে পারি।
এই ফাংশনগুলো জানতে এই আর্টিকেলগুলো চেক করুন: INDEX, SMALL।
এই দুটির সাথে কিছু হেল্পার ফাংশন লাগবে: IF, ROW এবং IFERROR। আরও জানতে: IF, ROW, IFERROR।
ফর্মুলাটি এক্সপ্লোর করি
=IFERROR(INDEX($B$2:$B$12,SMALL(IF($C$2:$C$12=$G$2,ROW($B$2:$B$12)),ROW(1:1))-1,1),"")
এখানে প্রতিটি ফাংশনের উদ্দেশ্য রয়েছে। INDEX ফাংশন B2:B12 অ্যারে (Name কলাম) থেকে ভ্যালু রিটার্ন করে এবং বড় SMALL অংশটি রো নম্বর দিয়ে দেয় যা ফেচ করতে হবে।
IF, SMALL-এর ভেতরে চেক করে ক্রাইটেরিয়া ম্যাচ হয়েছে কিনা, এবং ROW ফাংশন কলামের সেলগুলোর ওপর ইটারেট করে।
তারপর বাহিরের ROW SMALL ফাংশনের জন্য k-th ভ্যালু নির্দেশ করে। একসাথে এই ফাংশনগুলো রো নম্বর রিটার্ন করে এবং INDEX ফলাফল দেয়।
IFERROR ফর্মুলা থেকে কোনো এরর আপস করার জন্য।
ড্র্যাগ ডাউন করলে দেওয়া রিজন থেকে সব মানুষের নাম পাবেন।
এভাবেই অন্য রিজনগুলো জন্য ফর্মুলা লিখুন (ফর্মুলা একই, শুধু সেল শিফট করুন)।
অ্যালটারনেটিভ INDEX-SMALL কম্বিনেশন
আমরা ফর্মুলাটি অন্য একটিভাবে ও লিখতে পারি। ফাংশনগুলো একই থাকবে, শুধু প্রেজেন্টেশন ভিন্ন হবে।
ফর্মুলাটি দেখি
=IFERROR(INDEX($B$2:$B$12,SMALL(IF($C$2:$C$12=G$2,ROW($B$2:$B$12)-1),ROW(1:1)),1),"")আবার ফর্মুলা এক্সিকিউট করতে CTRL + SHIFT + ENTER চাপতে হবে।
এই দুটি ফর্মুলার মধ্যে সামান্য পার্থক্য রয়েছে, আপনি পার্থক্য করতে পারেন?
হ্যাঁ, আমাদের আগের ফর্মুলায় আমরা ১ বিয়োগ করেছি SMALL অংশের একদম শেষে, কিন্তু এখানে IF অংশের ভেতরে ১ বিয়োগ করেছি।
১ বিয়োগ করার উদ্দেশ্য হলো সঠিক রো নম্বরে চ্যানেলাইজ করা। আগে আমরা শেষে করতাম, এখানে আগে করে থাকলে আগ্রের অপারেশনে যাওয়া।
অন্য ক্রাইটেরিয়ার জন্য ফর্মুলা লিখে লিস্ট পুরো করুন।
আরও পড়ুন: এক্সেলে একটি সেলের ভেতরে লিস্ট তৈরি (৩ কুইক মেথড)
২. AGGREGATE ফাংশন ব্যবহার করে লিস্ট জেনারেট করা
এক্সেল আপনাকে AGGREGATE ফাংশন দেয় যেটি ব্যবহার করে আপনি অনেক কাজ করতে পারেন। এখানে আমরা এই ফাংশন ব্যবহার করে ক্রাইটেরিয়া ভিত্তিতে লিস্ট জেনারেট করব।
AGGREGATE ফাংশন এগ리게িট ক্যালকুলেশন রিটার্ন করে যেমন AVERAGE, COUNT, MAX, ইত্যাদি।
AGGREGATE ফাংশনের সিনট্যাক্স নিচের মতো:
AGGREGATE(function_number,behavior_options, range)function_number: এই নম্বর নির্দিষ্ট করে কোন ক্যালকুলেশন করা উচিত।
behavior_options: নম্বর দিয়ে সেট করা হয়। এই নম্বর ফাংশনের বিহেভিয়ার নির্দেশ করে।
range: যে রেঞ্জটি এগ리게িট করতে চান।
AGGREGATE ফাংশন একাধিক কাজ করে তাই এর ভেতরে অনেক ফাংশন প্রিডিফাইনড রয়েছে। কিছু বরাবর ব্যবহৃত ফাংশন নম্বর নিচে দেওয়া হলো
| ফাংশন | ফাংশন_নম্বর |
|---|---|
| AVERAGE | 1 |
| COUNT | 2 |
| COUNTA | 3 |
| MAX | 4 |
| MIN | 5 |
| PRODUCT | 6 |
| SUM | 9 |
| LARGE | 14 |
| SMALL | 15 |
আরও জানতে Microsoft Support সাইট ভিজিট করুন।
ফর্মুলাটি দেখি,
=IFERROR(INDEX($B$2:$B$12,AGGREGATE(15,6,IF($C$2:$C$12=G$2,ROW($B$2:$B$12)-1),ROW(1:1)),1),"")
এখানে AGGREGATE ফাংশনের সাথে INDEX ব্যবহার করা হয়েছে। INDEX অ্যারে ধরে রয়েছে যেটি ফর্মুলার পরবর্তী অংশে মিল পাওয়া রো নম্বর অনুযায়ী ভ্যালু রিটার্ন করে।
আপনি দেখছেন, AGGREGATE-এ 15 কে function_number হিসেবে ব্যবহার করা হয়েছে। উপরের টেবিল থেকে দেখবেন 15 হলো SMALL ফাংশন অপারেশনের জন্য কল। এখন কি আপনি রিলেট করতে পারছেন?
হ্যাঁ, আমরা INDEX-SMALL ফর্মুলাটি AGGREGATE ফাংশনেরভাবে এক্সিকিউট করেছি।
6 বিহেভিয়ার অপশনের জন্য, যেটি বোঝায় ignore error values।
বাকি ভ্যালুগুলোর জন্য ফর্মুলা লিখুন।
সাদৃশ্য পড়ুন
- এক্সেলে To Do লিস্ট তৈরি (৩ সহজ মেথড)
- মেইলিং লিস্ট তৈরি এক্সেলে (২ মেথড)
- নাম্বারড লিস্ট তৈরি এক্সেলে (৮ মেথড)
৩. INDEX-MATCH-COUNTIF ব্যবহার করে ইউনিক লিস্ট জেনারেট করা
আমরা ক্রাইটেরিয়া ভিত্তিতে ইউনিক লিস্ট তৈরি করতে পারি। এর জন্য INDEX, MATCH, এবং COUNTIF কম্বিনেশন ব্যবহার করা যায়।
COUNTIF একটি রেঞ্জে সেল কাউন্ট করে যা একটি শর্ত পূরণ করে। আর MATCH একটি লুকআপ ভ্যালুর পজিশন লোকেট করে একটি রেঞ্জে। এই ফাংশনগুলো সম্পর্কে জানতে এই আর্টিকেলগুলো দেখুন: MATCH, COUNTIF।
ফর্মুলাটি এক্সপ্লোর করি
=IFERROR(INDEX($B$2:$B$12, MATCH(0, IF(G$2=$C$2:$C$12, COUNTIF($G$2:$G2, $B$2:$B$12), ""), 0)),"") 
এই ফর্মুলায়: B2:B12 হলো কলাম রেঞ্জ যেটে ইউনিক ভ্যালুগুলো রয়েছে যেগুলো এক্সট্র্যাক্ট করতে চাই, C2:C12 হলো वो কলাম যেটে ক্রাইটেরিয়া রয়েছে যার ভিত্তিতে কাজ করা হচ্ছে G2 নির্দেশ করে ক্রাইটেরিয়া।
MATCH ফাংশনে ০ কে lookup_array হিসেবে দিয়েছি, এবং lookup_range-এর জন্য IF অংশটি ব্যবহার করেছি যেটে COUNTIF রয়েছে। সুতরাং এই অংশটি ০ পাওয়া পর্যন্ত ভ্যালু রিটার্ন করে। এখানে ভ্যালুটি INDEX-এর জন্য রো নম্বর হিসেবে কাজ করে।
ড্র্যাগ ডাউন করলে সব ইউনিক ভ্যালু পাবেন।
ফর্মুলা এক্সিকিউট করতে CTRL+SHIFT + ENTER ব্যবহার করা ভুলবেন না।
এটি ক্রাইটেরিয়া ভিত্তিতে ইউনিক লিস্ট জেনারেট করার একটি অনourable mention। এই আর্টিকেল ফলো করুন ক্রাইটেরিয়া ভিত্তিতে ইউনিক লিস্ট জেনারেট সম্পর্কে জানতে।
৪. FILTER ফাংশন ব্যবহার করে ক্রাইটেরিয়া ভিত্তিতে লিস্ট জেনারেট করা
আপনি যদি এক্সেল ৩৬৫ ব্যবহার করেন, তবে একটা বিল্ট-ইন ফাংশন FILTER দিয়ে এই কাজটি করতে পারেন।
FILTER ফাংশন দেওয়া ক্রাইটেরিয়া অনুযায়ী ডেটার একটি রেঞ্জ ফিল্টার করে এবং ম্যাচিং রেকর্ড এক্সট্র্যাক্ট করে। ফাংশন সম্পর্কে জানতে এই আর্টিকেল: FILTER।
আমাদের ফর্মুলা হবে নিচেরটি,
=FILTER($B$2:$B$12,$C$2:$C$12=G$2)
B2:B12 হলো ফিল্টার করা অ্যারে। তারপর শর্ত দিয়েছি যের ভিত্তিতে লিস্ট জেনারেট করব।
এখানে ফর্মুলা ড্র্যাগ ডাউন করতে হবে না, একবারেই সব ভ্যালু দিয়ে দিবে এবং লিস্ট পূরণ করবে।
আরও পড়ুন: এক্সেলে আর্টিফিশিয়াল লিস্ট তৈরি (৩ উপায়)
উপসংহার
আজ এই পর্যন্ত। ক্রাইটেরিয়া ভিত্তিতে লিস্ট জেনারেট করার বেশ কিছু উপায় তালিকা করেছি। আশা করি এটি আপনার কাজে আসবে। বুঝতে কোনো সমস্যা হলে কমেন্ট করুন। আমাদের মিস করা অন্য কোনো মেথড থাকলে জানাতে পারেন।
আরও পড়ুন
- বুলেটেড লিস্ট তৈরি এক্সেলে (৯ মেথড)
- কমা সেপারেটেড লিস্ট তৈরি এক্সেলে (৫ মেথড)
- প্রাইস লিস্ট তৈরি এক্সেলে (স্টেপ বাই স্টেপ গাইডলাইন)
