এক্সেল মাস্টারি: একাধিক ক্রাইটেরিয়া দিয়ে টেবিল ডেটা দক্ষতার সাথে এক্সট্র্যাক্ট করুন

প্রয়োজনের সময় ডেটা এক্সট্র্যাক্ট করা যেকোনো স্প্রেডশিটের প্রধান উদ্দেশ্য, এক্সেল তাতে ব্যতিক্রম নয়। আপনি এক্সেলে টেবুলার ফর্মে ডেটা স্টোর করেন সেগুলো এক্সট্র্যাক্ট করার জন্য। আজ আমরা দেখব কিভাবে একাধিক ক্রাইটেরিয়ার ভিত্তিতে টেবিল থেকে ডেটা এক্সট্র্যাক্ট করতে হয়।

প্রথমে, আমাদের উদাহরণের ভিত্তি থাকা ডেটাসেট সম্পর্কে জানি।

এখানে একটি টেবিল রয়েছে যাতে মুভির তালিকা রয়েছে জ্যানর, লিড এক্টর এবং রিলিজ ইয়ারের সাথে। এই ডেটাসেট ব্যবহার করে আমরা একাধিক ক্রাইটেরিয়ার ভিত্তিতে ডেটা রিট্রিভ করব।

নোট করুন এটি একটি বেসিক টেবিল যাতে বিষয়টি সহজ রাখা হয়েছে। বাস্তব পরিস্থিতিতে আপনি বড় এবং জটিল ডেটাসেটের সম্মুখীন হতে পারেন।

প্র্যাকটিস ওয়ার্কবুক

নিচের লিংক থেকে প্র্যাকটিস ওয়ার্কবুক ডাউনলোড করতে পারেন।

একাধিক ক্রাইটেরিয়ার ভিত্তিতে টেবিল থেকে ডেটা এক্সট্র্যাক্ট করা

উদাহরণস্বরূপ, আমরা জ্যানর এবং এক্টর নামকে ক্রাইটেরিয়া হিসেবে দিব এবং এর ভিত্তিতে মুভির নাম এক্সট্র্যাক্ট করব।

১. সিঙ্গেল ভ্যালু রিটার্ন (Return Single Value)

এই সেকশনে আমরা একটি সিঙ্গেল ভ্যালু রিটার্ন করব। ক্রাইটেরিয়ার ভিত্তিতে শুধু একটি ভ্যালু ফেচ হবে। এক্সপ্লোর করি।

I. INDEX-MATCH অ্যারে ফর্মুলা (Array Formula)

আমরা INDEX এবং MATCH ফাংশনের কম্বিনেশন ব্যবহার করতে পারি। INDEX একটি রেঞ্জে দেওয়া লোকেশনের ভ্যালু রিটার্ন করে। MATCH একটি লুকআপ ভ্যালুর পজিশন লোকেট করে একটি রেঞ্জে।

এই ফাংশনগুলো জানতে এই আর্টিকেলগুলো চেক করুন: INDEX, MATCH।

ক্রাইটেরিয়া ভ্যালু সেট করি। ফিলহাল Genre-এ Thriller এবং Actor-এ Hugh Jackman।

ফর্মুলা হবে নিচেরটি

=INDEX($B$4:$B$19,MATCH(1,($H$4=$C$4:$C$19)*($H$5=$D$4:$D$19),0))

B4:B19 হলো वो অ্যারে যেখান থেকে আমরা ভ্যালু এক্সট্র্যাক্ট করব। আর MATCH ফাংশন সেট করে দেয় কোন রো নম্বর ফেচ করতে হবে।

আপনি দেখছেন MATCH-এ ১ কে lookup_value হিসেবে দিয়েছি। আর lookup_array তৈরি হয়েছে ক্রাইটেরিয়া ম্যাচিং লজিকের গুণফল দিয়ে।

$H$4=$C$4:$C$19 দিয়ে জ্যানর চেক করেছি এবং $H$5=$D$4:$D$19 দিয়ে এক্টর নাম।

গুণফলের অ্যারেতে ১ খুঁজে বের করে রো নম্বর রিটার্ন করে এবং INDEX ফাংশন মুভির নাম রিটার্ন করে।

এটি একটি অ্যারে ফর্মুলা তাই এক্সিকিউট করতে CTRL + SHIFT + ENTER ব্যবহার করতে হবে।

ক্রাইটেরিয়া ভ্যালু পরিবর্তন করলে আপডেটেড ভ্যালু পাবেন।

আরও পড়ুন: এক্সেলে একটি সেল থেকে স্পেসিফিক ডেটা এক্সট্র্যাক্ট করা (৩ উদাহরণ)

II. INDEX-MATCH নন-অ্যারে ফর্মুলা (Non-Array Formula)

আমরা INDEX এবং MATCH কম্বাইন করে নন-অ্যারে ফর্মুলা বানায়।

ফর্মুলাটি প্রথমে দেখি

=INDEX($B$4:$B$19,MATCH(1,INDEX(($H$4=$C$4:$C$19)*($H$5=$D$4:$D$19),0,1),0))

এখানে আপনি দেখছেন, একাধিক INDEX ফাংশন ব্যবহার করা হয়েছে। বাহিরের INDEX ফাংশন এক্সট্র্যাকশন কাজটি করে এবং ভেতরেরটি রো নম্বর ডিটেক্ট করতে সাহায্য করে।

ভেতরের INDEX-এ আমরা ক্রাইটেরিয়া ভ্যালু চেক করেছি। এখানে দুটি লজিক্যাল অপারেশন INDEX-এ গুণ করা হয়েছে এবং সেটি অ্যারে রেফারেন্স হিসেবে কাজ করেছে।

সাধারণ ENTER দিয়ে ফর্মুলা এক্সিকিউট হবে।

ক্রাইটেরিয়া ভ্যালু পরিবর্তন করে দেখুন ফর্মুলা অন্য ভ্যালুর জন্যও পারফেক্টলি কাজ করছে কিনা।

আরও পড়ুন: ক্রাইটেরিয়া ভিত্তিতে এক্সেল থেকে ডেটা এক্সট্র্যাক্ট (৫ উপায়)

III. INDEX-MATCH-IF কম্বিনেশন

পূর্বের সেকশনে আমরা শর্তগুলো চেক করে গুণ করিয়ে সেগুলোকে একসাথে কাজ করতে বাধ্য করেছি। আমরা IF ফাংশন ব্যবহার করে গুণফল এড়িয়ে যেতে পারি।

IF একটি লজিক্যাল টেস্ট রান করে এবং ফলাফল হিসেবে বুলিয়ান ভ্যালু (TRUE বা FALSE) রিটার্ন করে। ফাংশন সম্পর্কে জানতে এই IF আর্টিকেল দেখুন।

আমাদের ফর্মুলা হবে নিচেরটি

=INDEX($B$4:$B$16,MATCH(1,IF($C$4:$C$16=$H$4,IF($D$4:$D$16=$H$5,1)),0))

এখানে দুটি শর্ত ম্যাচ করাতে হয়েছে তাই দুটি IF। সেগুলো নেস্টেড IF হিসেবে কাজ করছে (একটি অন্যটির ভেতরে)। বাহিরের IF ফাংশন প্রথমে একটি শর্ত চেক করে (সিকোয়েন্স নির্বিশেষে) এবং দ্বিতীয় শর্ত (ইনার IF) প্রথম IF-এর if_true_value।

ফর্মুলা এক্সিকিউট করতে CTRL+SHIFT+ENTER ব্যবহার করতে হবে।

ক্রাইটেরিয়া ভ্যালু পরিবর্তন করলে আপডেটেড ভ্যালু পাবেন।

আরও পড়ুন: এক্সেল শীট থেকে ডেটা এক্সট্র্যাক্ট (৬ ইফেক্টিভ মেথড)

IV. LOOKUP ফাংশন

ক্রাইটেরিয়া ভিত্তিতে ডেটা এক্সট্র্যাক্ট করার কাজে LOOKUP ফাংশনও ব্যবহার করা যায়।

LOOKUP ফাংশন একটি রেঞ্জে ম্যাচিং লুকআপ করে এবং করেসপন্ডিং ভ্যালু রিটার্ন করে। আরও জানতে Microsoft Support সাইট ভিজিট করুন।

ফর্মুলাটি এক্সপ্লোর করি

=LOOKUP(2,1/($C$4:$C$19=$H$4)/($D$4:$D$19=$H$5),($B$4:$B$19))

এখানে ২ কে lookup_value হিসেবে সেট করা হয়েছে। এবং দুটি লজিক্যাল অপারেশনকে ১ দিয়ে ভাগ করাই lookup_vector।

এটি ১ কে TRUE/FALSE ভ্যালুর অ্যারে ($C$4:$C$19=$H$4) দিয়ে ভাগ করে, তারপর আবার অন্য TRUE/FALSE অ্যারে ($D$4:$D$19=$H$5) দিয়ে ভাগ করে। এতে ১ বা #DIV/0! এরর রিটার্ন হবে।

lookup_value ফর্মুলাকে বলে নম্বরিক ভ্যালু খুঁজে বের করতে, একবার ম্যাচ পাওয়া গেলে B4:B19 অ্যারে থেকে ভ্যালু ডেরাইভ হবে।

এক্সিকিউট করতে CTRL + SHIFT + ENTER প্রয়োজন নেই।

ক্রাইটেরিয়া ভ্যালু পরিবর্তন করে দেখুন অন্য ভ্যালুর জন্যও ফর্মুলা পারফেক্টলি কাজ করছে কিনা।

নোট করুন আমরা ২ ব্যবহার করেছি lookup_value- হিসেবে। এটি ১ থেকে শুরু হয়ে যেকোনো নম্বর হতে পারে।

আরও পড়ুন: ছবি থেকে এক্সেলে ডেটা এক্সট্র্যাক্ট (দ্রুত ধাপসহ)

সাদৃশ্য পড়ুন

VBA কোড টেক্সট ফাইলকে এক্সেলে কনভার্ট (৭ মেথড)
Excel VBA: ওয়েবসাইট থেকে অটোমেটিক্যালি ডেটা পুল (২ মেথড)
সিকিউয়ার ওয়েবসাইট থেকে এক্সেলে ডেটা ইম্পোর্ট (দ্রুত ধাপসহ)
VLOOKUP দিয়ে এক এক্সেল ওয়ার্কশিট থেকে অন্যেকটিতে ডেটা ট্রান্সফার
পাইপ ডিলিমিটার দিয়ে এক্সেলকে টেক্সট ফাইলে কনভার্ট (২ উপায়)

২. একাধিক ভ্যালু রিটার্ন (Return Multiple Values)

I. INDEX-SMALL কম্বিনেশন

ক্রাইটেরিয়া ভিত্তিতে একাধিক ডেটা এক্সট্র্যাক্ট করার জন্য ভিন্ন ফাংশন কম্বিনেশন ব্যবহার করা যায়। 그중 একটি হলো INDEX – SMALL কম্বিনেশন।

SMALL ফাংশন ভ্যালুর পজিশন অনুযায়ী একটি লিস্ট থেকে ভ্যালু রিটার্ন করে। আরও জানতে এই SMALL আর্টিকেল চেক করুন।

এই দুটির সাথে কিছু হেল্পার ফাংশন লাগবে: IF, ROW এবং IFERROR। আরও জানতে: IF, ROW, IFERROR।

আমাদের ফর্মুলা হবে নিচেরটি

=IFERROR(INDEX($B$2:$B$17,SMALL(IF(($C$2:$C$17=$H$2)*($D$2:$D$17=$H$3), ROW($B$2:$B$17)),ROW(1:1))-1,1),"")

এখানে প্রতিটি ফাংশনের উদ্দেশ্য রয়েছে। INDEX ফাংশন B2:B17 অ্যারে থেকে ভ্যালু রিটার্ন করে এবং বড় SMALL অংশটি রো নম্বর দিয়ে দেয় যা ফেচ করতে হবে।

IF, SMALL-এর ভেতরে চেক করে ক্রাইটেরিয়া ম্যাচ হয়েছে কিনা। দুটি শর্ত ম্যাচ করাতে হয়েছে তাই দুটি লজিক্যাল অপারেশন গুণ করা হয়েছে। এবং ROW ফাংশন কলামের সেলগুলোর ওপর ইটারেট করে।

তারপর বাহিরের ROW SMALL ফাংশনের জন্য k-th ভ্যালু নির্দেশ করে। একসাথে এই ফাংশনগুলো রো নম্বর রিটার্ন করে এবং INDEX ফলাফল দেয়।

IFERROR ফর্মুলা থেকে কোনো এরর আপস করার জন্য। এরর হলে খালি সেল দেখাবে।

ড্র্যাগ ডাউন করলে ক্রাইটেরিয়ার সাথে মিলে যাওয়া সব ভ্যালু পাবেন।

আরও পড়ুন: এক্সেল ফর্মুলা দিয়ে লিস্ট থেকে ডেটা এক্সট্র্যাক্ট (৫ মেথড)

II. INDEX-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 সাইট ভিজিট করুন।

ফর্মুলাটি দেখি, এটি INDEX এবং AGGREGATE কম্বিনেশন হবে।

=IFERROR(INDEX($B$2:$B$17,AGGREGATE(15,6,IF(($C$2:$C$17=$H$2)*($D$2:$D$17=$H$3), ROW($B$2:$B$17)),ROW(1:1))-1,1),"")

এখানে AGGREGATE-এ 15 কে function_number হিসেবে ব্যবহার করা হয়েছে। উপরের টেবিল থেকে দেখবেন 15 হলো SMALL ফাংশন অপারেশনের জন্য কল। আপনি দেখছেন AGGREGATE ব্যবহার ছাড়া (এবং ফাংশন নম্বর ও বিহেভিয়ার অপশন নম্বর) ফর্মুলাটি আগের INDEX–SMALL ফর্মুলার মতোই।

মেকানিজম একই, INDEX অ্যারে ধরে রয়েছে যেটি AGGREGATE অংশে মিল পাওয়া রো নম্বর অনুযায়ী ভ্যালু রিটার্ন করে।

6 বিহেভিয়ার অপশনের জন্য, যেটি বোঝায় ignore error values।

ড্র্যাগ ডাউন করলে ক্রাইটেরিয়ার সাথে মিলে যাওয়া সব ভ্যালু পাবেন।

ফর্মুলা এক্সিকিউট করতে CTRL+SHIFT+ENTER মনে রাখবেন।

আরও পড়ুন: সিঙ্গেল ক্রাইটেরিয়া ভিত্তিতে এক্সেলে একাধিক ভ্যালু রিটার্ন (৩ অপশন)

III. INDEX-MATCH-COUNTIF কম্বিনেশন

একাধিক ক্রাইটেরিয়া ভিত্তিতে একাধিক ভ্যালু রিটার্ন করতে INDEX, MATCH, এবং COUNTIF কম্বিনেশন ব্যবহার করা যায়।

COUNTIF একটি রেঞ্জে সেল কাউন্ট করে যা একটি শর্ত পূরণ করে। আরও জানতে এই আর্টিকেল: COUNTIF।

আমাদের ফর্মুলা হবে নিচেরটি

=IFERROR(INDEX($B$4:$B$19,MATCH(0,COUNTIF(H5:$H$5,$B$4:$B$19)+IF($C$4:$C$19<>$H$4,1,0)+IF($D$4:$D$19<>$H$5,1,0),0)),"")

MATCH ফাংশনে ০ কে lookup_array হিসেবে দিয়েছি, এবং lookup_range-এর জন্য IF অংশটি ব্যবহার করেছি যেটে COUNTIF রয়েছে।

এখানে, COUNTIF ফাংশন ইতিমধ্যে ফেচ করা ভ্যালুগুলো এক্সক্লুড করে। এবং দুটি IF ফাংশন দুটি শর্ত চেক করে। আমরা এই ফাংশনগুলো যোগ করেছি যাতে একসাথে lookup_range তৈরি হয়।

MATCH অংশটি যাবতীয় ০ পাওয়া যাচ্ছে তাবতীয় ভ্যালু রিটার্ন করে। এখানে ভ্যালুটি INDEX-এর জন্য রো নম্বর হিসেবে কাজ করে।

ড্র্যাগ ডাউন করলে ক্রাইটেরিয়ার সাথে মিলে যাওয়া সব ভ্যালু পাবেন।

আরও পড়ুন: এক্সেলে সেল থেকে ডেটা এক্সট্র্যাক্ট (৫ মেথড)

IV. FILTER ফাংশন

আপনি যদি এক্সেল ৩৬৫ ব্যবহার করেন, তবে একটা বিল্ট-ইন ফাংশন FILTER দিয়ে এই কাজটি করতে পারেন।

FILTER ফাংশন দেওয়া ক্রাইটেরিয়া অনুযায়ী ডেটার একটি রেঞ্জ ফিল্টার করে এবং ম্যাচিং রেকর্ড এক্সট্র্যাক্ট করে। ফাংশন সম্পর্কে জানতে এই আর্টিকেল: FILTER।

ফর্মুলাটি এক্সপ্লোর করি

=FILTER(B4:B19,(H4=C4:C19)*(H5=D4:D19))

B4:B19 হলো ফিল্টার করা অ্যারে। তারপর শর্ত দিয়েছি যের ভিত্তিতে ভ্যালু এক্সট্র্যাক্ট করব। যেহেতু দুটি ক্রাইটেরিয়া চেক করতে হচ্ছে, তাই সেগুলো গুণ করা হয়েছে।

এখানে ফর্মুলা ড্র্যাগ ডাউন করতে হবে না, একবারেই সব ভ্যালু দিয়ে দিবে এবং লিস্ট পূরণ করবে।

আরও পড়ুন: ফিল্টারড ডেটা এক্সেল থেকে অন্য শীটে এক্সট্র্যাক্ট (৪ মেথড)

উপসংহার

আজ এই পর্যন্ত। একাধিক ক্রাইটেরিয়া ভিত্তিতে টেবিল থেকে ডেটা এক্সট্র্যাক্ট করার বেশ কিছু মেথড তালিকা করেছি। আশা করি এটি আপনার কাজে আসবে। বুঝতে কোনো সমস্যা হলে কমেন্ট করুন। আমাদের মিস করা অন্য কোনো পদ্ধতি থাকলে জানাতে পারেন।