মাইক্রোসফট এক্সেলে একাধিক ক্রাইটেরিয়ার অধীনে ইউনিক লিস্ট তৈরির বেশ কিছু পদ্ধতি রয়েছে। ইউনিক লিস্ট সাধারণত টেবিল থেকে ডুপ্লিকেট রিমুভ করার জন্য তৈরি করা হয়। এই আর্টিকেলে আপনি শিখবেন কিভাবে ভিন্ন ভিন্ন মেথড ব্যবহার করে একাধিক শর্তের ভিত্তিতে ইউনিক লিস্ট জেনারেট করতে হয়।
এই আর্টিকেল তৈরিতে ব্যবহৃত এক্সেল ওয়ার্কবুক ডাউনলোড করতে পারেন।
ক্রাইটেরিয়া ভিত্তিতে এক্সেলে ইউনিক লিস্ট তৈরির ৯টি মেথড
১. একাধিক কলাম ক্রাইটেরিয়া দিয়ে ইউনিক সারি (Rows) এর লিস্ট তৈরি
নিচের ছবিতে বাম পাশের টেবিলটি কিছু র্যান্ডম স্পোর্টস নাম, পার্টিসিপ্যান্ট এবং তাদের বয়স নিয়ে আছে। ডান পাশের আউটপুট টেবিলে আমরা শুধু ইউনিক ভ্যালু বেইসড ডেটা এক্সট্র্যাক্ট করব। উদাহরণস্বরূপ, আমরা শুধু ইউনিক পার্টিসিপ্যান্টদের নাম জানতে চাই তাদের বয়সসহ।
এখানে আমরা UNIQUE ফাংশন ব্যবহার করব যা একটি রেঞ্জ বা অ্যারে থেকে ইউনিক ভ্যালু রিটার্ন করে। এই ফাংশন এর জেনেরিক ফর্মুলা:
=UNIQUE(array, [by_col], [exactly_once])
যদিও UNIQUE ফাংশন শুধু Excel 365-এ উপলব্ধ, আমরা শেষ ৩টি সেকশনে অন্যান্য কনভেনশনাল ফাংশন কম্বিনেশন দিয়ে অল্টারনেটিভ মেথডও দেখাব।
সুতরাং F5 সেলে প্রয়োজনীয় ফাংশন হবে:
=UNIQUE(B5:C13,FALSE,FALSE)Enter চাপলে ফাংশনটি ইউনিক নামগুলো সংশ্লিষ্ট বয়সসহ অ্যারে হিসেবে রিটার্ন করবে।
আরও পড়ুন: ক্রাইটেরিয়া ভিত্তিতে এক্সেলে লিস্ট জেনারেট (৪ মেথড)
২. অ্যালফাবেটিক্যালি সর্টেড ইউনিক ভ্যালুর লিস্ট পাওয়া
আমরা SORT ফাংশন ব্যবহার করে ইউনিক ভ্যালুগুলো অ্যালফাবেটিক্যালি সর্টেড পেতে পারি। সর্ট অর্ডার আসেন্ডিং (A to Z) বা ডিসেন্ডিং (Z to A) ডিনোট করে পার্টিসিপ্যান্ট নামগুলো তাদের ইনিশিয়াল অনুযায়ী রিয়ারেঞ্জ করতে পারি।
আউটপুট E5 সেলে SORT এবং UNIQUE ফাংশনসহ প্রয়োজনীয় ফর্মুলা:
=SORT(UNIQUE(B5:C13,FALSE,FALSE),,1)Enter চাপলে ফাংশনটি A to Z অ্যালফাবেটিক্যাল অর্ডারে সর্টেড অ্যারে রিটার্ন করবে।
আরও পড়ুন: এক্সেলে অ্যালফাবেটিক্যাল লিস্ট তৈরি (৩ উপায়)
৩. ইউনিক ভ্যালুগুলো এক সেলে কনক্যাটেনেট করে লিস্ট তৈরি
ধরুন, আমরা দুটি কলাম থেকে ইউনিক ডেটা এক্সট্র্যাক্ট করব এবং তারপর ভ্যালুগুলোকে পাশাপাশি জয়েন করব যাতে একটি সিঙ্গেল কলামে কনক্যাটেনেটেড ভ্যালু রিটার্ন হয়। নিচের ছবিতে ইউনিক নাম এবং সংশ্লিষ্ট বয়স একটি কলামে একসাথে দেখানো হবে একটি নির্দিষ্ট ডিলিমিটার- কমা (,) দিয়ে। এবং আমরা এখানে Ampersand (&) ব্যবহার করব দুটি ভিন্ন কলামের ভ্যালু পাশাপাশি কনক্যাটেনেট করার জন্য।
আউটপুট F5 সেলে প্রয়োজনীয় ফর্মুলা:
=UNIQUE(B5:B13&", "&C5:C13)ফর্মুলাটি একটি সিঙ্গেল কলামে ইউনিক সারিগুলোর অ্যারে রিটার্ন করবে।
আরও পড়ুন: এক্সেলে কমা সেপারেটেড লিস্ট তৈরি (৫ মেথড)
৪. ক্রাইটেরিয়া সহ ইউনিক ভ্যালুর লিস্ট তৈরি (UNIQUE-FILTER ফর্মুলা)
i. এক্সেলে একাধিক AND ক্রাইটেরিয়া ভিত্তিতে ইউনিক ভ্যালু আইডেন্টিফাই করা
এই সেকশনে আমরা কিছু ক্রাইটেরিয়া যোগ করব এবং সেই শর্তের ভিত্তিতে ইউনিক ডেটা এক্সট্র্যাক্ট করব। উদাহরণস্বরূপ, আমরা জানতে চাই যেসব পার্টিসিপ্যান্ট শুধু সাঁতারাই partiipted করেছে এবং যাদের বয়স ২৫ বছরের নিচে। সুতরাং আমাদের এখানে UNIQUE এবং FILTER ফাংশন কম্বাইন করতে হবে দिये गये ক্রাইটেরিয়া দিয়ে ডেটা ফিল্টার করার জন্য।
আউটপুট F5 সেলে প্রয়োজনীয় ফর্মুলা:
=UNIQUE(FILTER(B5:C13,(D5:D13=G9)*(C5:C13<G10)))
Enter চাপলে ফর্মুলা সিলেক্টেড ক্রাইটেরিয়া অনুযায়ী ইউনিক নাম এবং সংশ্লিষ্ট বয়স সহ অ্যারে রিটার্ন করবে।
ii. একাধিক OR ক্রাইটেরিয়া ভিত্তিতে ইউনিক ভ্যালু সার্চ
ধরুন, এখন আমরা জানতে চাই যেসব ইউনিক পার্টিসিপ্যান্ট দুটি আউটডোর স্পোর্টস- সাঁতারাই এবং সাইক্লিং-এ partisipated করেছে। যেহেতু আমরা শুধু একই কলাম থেকে ক্রাইটেরিয়া অ্যাসাইন করছি, FILTER ফাংশনে দুটি শর্ত জয়েন করার জন্য সিম্পল নমারিক্যাল অ্যডিশন ব্যবহার করতে পারি।
F5 সেলে প্রয়োজনীয় ফর্মুলা হবে:
=UNIQUE(FILTER(B5:C13,(D5:D13=F11)+(D5:D13=F12)))
Enter চাপুন এবং আপনি 움্মেদিত ইউনিক ডেটা অবিলম্বে পাবেন নিচের স্ক্রিনশটে দেখানো মতো।
iii. ব্ল্যাঙ্ক সেল ইগনোর করে ইউনিক ভ্যালুর লিস্ট পাওয়া
আমাদের ডেটাসেটে অনেক ব্ল্যাঙ্ক সেল বা সারি থাকতে পারে। তাই যদি আমরা সেই ব্ল্যাঙ্ক সারি বাদ দিয়ে টেবিল থেকে ইউনিক ডেটা এক্সট্র্যাক্ট করতে চাই তবে কমপেরিজন অপারেটর: Not Equal To (<>) ফর্মুলায় ব্যবহার করতে হবে।
আউটপুট F5 সেলে প্রয়োজনীয় ফর্মুলা:
=UNIQUE(FILTER(B5:C13,D5:D13<>""))
Enter চাপলে ফর্মুলা শুধু নন-ব্ল্যাঙ্ক ইউনিক সারিগুলোর সাথে আউটপুট টেবিলে অ্যারে রিটার্ন করবে।
সাদৃশ্য পড়ুন
- এক্সেলে To Do লিস্ট তৈরি (৩ সহজ মেথড)
- মেইলিং লিস্ট তৈরি এক্সেলে (২ মেথড)
৫. নির্দিষ্ট কলামগুলিতে ইউনিক ভ্যালুর লিস্ট খুঁজে বের করা
ধরুন, আমরা কিছু নির্দিষ্ট কলাম থেকে শুধু ইউনিক ডেটা এক্সট্র্যাক্ট করতে চাই। মাউস কーソর দিয়ে সেসpecific কলামগুলো সিলেক্ট করে UNIQUE ফাংশনের আর্গুমেন্ট হিসেবে ইনপুট দেয়া সম্ভব নয়। তাই এখানে CHOOSE ফাংশন কম্বাইন করতে হবে UNIQUE ফাংশনের সাথে কারণ CHOOSE ফাংশন আপনাকে ইনডেক্স নম্বর ভিত্তিতে যেকোনো সংখ্যক স্পেসিফাইড কলাম বা সেল রেঞ্জ সিলেক্ট করতে দেয় ভ্যালুর একটি লিস্ট থেকে।
আমাদের ডেটা টেবিল থেকে আমরা ইউনিক পার্টিসিপ্যান্টদের নাম এক্সট্র্যাক্ট করব এবং আউটপুট দেখাব শুধু Name (কলাম B) এবং Sports Name (কলাম D) কলাম থেকে।
F5 সেলে প্রয়োজনীয় ফর্মুলা:
=UNIQUE(CHOOSE({1,3},B5:B13,C5:C13,D5:D13))
Enter চাপলে ফর্মুলা দুটি ভিন্ন কলাম থেকে ইউনিক নাম এবং সংশ্লিষ্ট স্পোর্টস নাম রিটার্ন করবে নিচের ছবির মতো।
CHOOSE ফাংশনের ভেতরে ইনডেক্স নম্বরগুলো ১ এবং ৩ যেটি বোঝায় ১ম এবং ৩য় রেঞ্জ সেল ভ্যালুর লিস্ট থেকে সিলেক্ট করতে হবে। UNIQUE ফাংশন তারপর স্পেসিফাইড কলামগুলোই বিবেচনা করে সেসকল কলাম থেকে ইউনিক ডেটা অ্যারে হিসেবে রিটার্ন করে।
আরও পড়ুন: এক্সেলে নাম্বারড লিস্ট তৈরি (৮ মেথড)
৬. ইউনিক লিস্ট তৈরি করার সময় IFERROR ফাংশন ব্যবহার
আমরা UNIQUE ফাংশনের পেছনে IFERROR ফাংশন ব্যবহার করতে পারি যাতে রিটার্ন ভ্যালুতে কোনো এরর পাওয়া গেলে একটি কাস্টমাইজড মেসেজ শো করানো যায়। ধরুন, আমরা জানতে চাই ২১ বছরের নিচের ইউনিক পার্টিসিপ্যান্টদের নাম। যেহেতু ২১ বছরের নিচে কেউ নেই, ফর্মুলাটি #N/A এরর রিটার্ন করবে। কিন্তু এই এরর দেখানোর বদলে আমরা IFERROR ফাংশন ব্যবহার করব কাস্টমাইজড মেসেজ- “Not Found” দেখানোর জন্য।
সুতরাং F5 সেলে প্রয়োজনীয় ফর্মুলা:
=IFERROR(UNIQUE(FILTER(B5:C13,C5:C13<=G10)), "Not Found")
Enter চাপলে ফর্মুলা নির্দিষ্ট মেসেজটি রিটার্ন করবে নিচের স্ক্রিনশটের মতো।
৭. ক্রাইটেরিয়া ভিত্তিতে ইউনিক লিস্ট এক্সট্র্যাক্ট (INDEX-MATCH ফর্মুলা)
এখন আমরা UNIQUE ফাংশন বাদ দিয়ে INDEX এবং MATCH ফাংশনের কম্বিনেশন প্রয়োগ করব। INDEX ফাংশন একটি নির্দিষ্ট সারি ও কলামের ইন্টারসেকশনে থাকা সেলের ভ্যালু বা রেফারেন্স রিটার্ন করে। আর MATCH ফাংশন একটি নির্দিষ্ট অর্ডারে নির্দিষ্ট ভ্যালু ম্যাচ করে একটি আইটেমের পজিশন রিটার্ন করে। ধরুন, আমরা শুধু সাঁতারাই partisipated করা পার্টিসিপ্যান্টদের ইউনিক নাম জানতে চাই।
📌 ধাপ ১:
➤ আউটপুট F5 সেল সিলেক্ট করে টাইপ করুন:
=INDEX(B5:B13, MATCH(0, IF($F$12=$D$5:$D$13,COUNTIF($F$4:$F4, $B$5:$B$13), ""), 0))➤ Enter চাপুন।
ফার্স্ট ইউনিক নামটি রিটার্ন ভ্যালু হিসেবে পাবেন।
📌 ধাপ ২:
➤ Fill Handle ব্যবহার করে সেলটি ডান দিকে ড্র্যাগ করুন।
এবং পার্টিসিপ্যান্টের বয়সও পাবেন।
📌 ধাপ ৩:
➤ G5 সেল থেকে কলামটি নিচে ড্র্যাগ করুন যতক্ষণ না #N/A ভ্যালু আসে।
এভাবেই আপনি দিয়ে गये শর্ত অনুযায়ী INDEX-MATCH ফর্মুলা দিয়ে ইউনিক ডেটা এক্সট্র্যাক্ট করতে পারেন।
🔎 ফর্মুলা কীভাবে কাজ করে?
- COUNTIF($F$4:$F4, $B$5:$B$13): এখানে COUNTIF ফাংশন ব্যবহার করা হয়েছে B5:B13 রেঞ্জের সব সেল স্টোর ও কাউন্ট করার জন্য। ফাংশনটি রিটার্ন করে:
{0;0;0;0;0;0;0;0;0}
- IF($F$12=$D$5:$D$13, COUNTIF($F$4:$F4, $B$5:$B$13), “”): IF ফাংশন দিয়ে দিয়ে गये ক্রাইটেরিয়া সেলগুলোতে সার্চ করা হয়েছে এবং রিটার্ন হয়েছে:
{0;””;0;””;””;0;””;””;””}
- MATCH ফাংশন পূর্বের ধাপে পাওয়া সেলের রো নম্বর রিটার্ন করে।
- শেষে INDEX ফাংশন সেই রো নম্বর অনুযায়ী ডেটা এক্সট্র্যাক্ট করে।
৮. একাধিক ক্রাইটেরিয়া ভিত্তিতে এক্সেলে ইউনিক লিস্ট প্রস্তুত
এই সেকশনে আমরা দেখব একাধিক শর্ত প্রয়োগ করার সময় INDEX-MATCH ফর্মুলা কীভাবে কাজ করে। উদাহরণস্বরূপ, আমরা জানতে চাই যেসব ইউনিক পার্টিসিপ্যান্ট সাঁতারাই partisipated করেছেন এবং ২৫ বছরের কম বয়স।
আউটপুট F5 সেলে প্রয়োজনীয় ফর্মুলা:
=IFERROR(INDEX($B$5:$B$13,MATCH(0,COUNTIF(F4:$F$4,$B$5:$B$13)+IF(D5:D13=$G$9,1,0)+IF(C5:C13<$G$10,1,0),0)),"")
Enter চাপিয়ে আউটপুট কলামের নতুন সেলগুলো অটো-ফিল করলে ইউনিক নামগুলো পাবেন নিচের ছবির মতো।
এই ফর্মুলায় আমরা দুটি IF ফাংশন এবং COUNTIF ফাংশন দিয়ে ক্রাইটেরিয়া অ্যাসাইন করেছি এবং COUNTIF ফাংশন আউটপুট সেলের অ্যারে কনসিডার করবে সেই ক্রাইটেরিয়া অনুযায়ী। পূর্বের মেথডে বর্ণিত মতো INDEX-MATCH ফর্মুলা সেই অ্যারে ভিত্তিতে আউটপুট রিটার্ন করবে। এবং এখানে IFERROR ফাংশন যেকোনো এরর পাওয়া গেলে কাস্টমাইজড মেসেজ রিটার্ন করার জন্য ব্যবহার করা হয়েছে।
৯. ক্রাইটেরিয়া সহ সারি ও কলাম অনুযায়ী একাধিক ইউনিক লিস্ট তৈরি
আমাদের শেষ মেথডে আমরা এক্সেল টেবিল ব্যবহার করব একাধিক ইউনিক লিস্ট ডেটা এক্সট্র্যাক্ট করার জন্য। নিচের ছবিতে নিচের টেবিলটি স্পোর্টস টাইপ অনুযায়ী পার্টিসিপ্যান্টদের ইউনিক নাম দেখাবে।
আমরা টেবিল বা সেল রেঞ্জ (B5:C13)-কে Sports নামে নামকরণ করেছি। কলামগুলোতে হেডার রয়েছে SportsName এবং Name। মনে রাখবেন টেবিলের হেডার কোনো স্পেস নিবহ করবে না।
📌 ধাপ ১:
➤ প্রথম আউটপুট B16 সেলে প্রয়োজনীয় ফর্মুলা:
=IFERROR(INDEX(Sports,SMALL(IF(Sports[SportsName]=B$15,ROW(Sports)-4),ROW(1:1)),2),"")
Enter চাপলে ফার্স্ট আউটপুট ভ্যালু রিটার্ন করবে।
📌 ধাপ ২:
➤ Fill Handle ব্যবহার করে কলামটি নিচে ফিল ডাউন করুন যতক্ষণ না ব্ল্যাঙ্ক সেল আসে।
📌 ধাপ ৩:
➤ আউটপুট রেঞ্জ (B16:B20) প্রথম কলাম থেকে কপি করুন।
➤ C16 সেলে, Formulas (F) অপশন দিয়ে পেস্ট করুন।
সাইক্লিং-এ partiipted পার্টিসিপ্যান্টদের ইউনিক নাম দিয়ে দ্বিতীয় কলাম পাবেন।
📌 ধাপ ৪:
➤ একইভাবে D16 এবং E16 সেলে আবার কপি করা ভ্যালুগুলো ফর্মুলা হিসেবে পেস্ট করুন।
স্পোর্টস টাইপ অনুযায়ী অন্যান্য ইউনিক পার্টিসিপ্যান্ট নামগুলো অবিলম্বে পাবেন। কিন্তু মনে রাখবেন প্রথম রিটার্ন ভ্যালুগুলো থেকে Fill Handle দিয়ে ডান দিকে অটোফিল করতে পারবেন না। Autofill অপশন ব্যবহার করলে ম্যানিপুলেটেড ডেটা আসবে এবং আপনি আসল আউটপুট এক্সট্র্যাক্ট করতে পারবেন না।
🔎 ফর্মুলা কীভাবে কাজ করে?
- IF(Sports[SportsName]=B$15, ROW(Sports)-4): ফাংশনের এই অংশটি B15 সেলে হেডার দিয়ে নির্দিষ্ট শর্ত সার্চ করে এবং রিটার্ন করে:
{1;FALSE;FALSE;4;FALSE;FALSE;7;8;FALSE}
- SMALL(IF(Sports[SportsName]=B$15, ROW(Sports)-4), ROW(1:1)): SMALL ফাংশন পূর্বের আউটপুট থেকে ছোট নম্বর এক্সট্র্যাক্ট করে এবং C16 সেলে সেটি ‘১’।
- INDEX ফাংশন SMALL ফাংশন নির্দিষ্ট রো নম্বর অনুযায়ী নাম টান করে।
- IFERROR ফাংশন যেকোনো এরর আউটপুট পাওয়া গেলে ব্ল্যাঙ্ক সেল শো করার জন্য ব্যবহার করা হয়েছে।
- এই কম্বাইনড ফর্মুলায় ‘৪’ নম্বরটি “ROW(Sports)-4” অংশে প্রাইমারি ডেটা টেবিলের হেডারের রো নম্বর।
শেষ কথা
আশা করি উপরে ইলাস্ট্রেট করা সব মেথড আপনার এক্সেল স্প্রেডশিটে ডেটা টেবিল থেকে ইউনিক ভ্যালুর লিস্ট তৈরি করার время আরও ইফেক্টিভলি প্রয়োগ করতে সাহায্য করবে। আপনার যেকোনো প্রশ্ন বা ফিডব্যাক থাকলে কমেন্ট সেকশনে জানাতে পারেন। অথবা আপনি এই ওয়েবসাইটের অন্যান্য এক্সেল ফাংশন সম্পর্কিত আর্টিকেল চেক আউট করতে পারেন।
আপনি আরও পড়তে চাইতে পারেন
- বুলেটেড লিস্ট তৈরি এক্সেলে (৯ মেথড)
- এক সেলের ভেতরে লিস্ট তৈরি এক্সেলে (৩ কুইক মেথড)
- প্রাইস লিস্ট তৈরি এক্সেলে (স্টেপ বাই স্টেপ গাইডলাইন)
