প্রয়োজনের সময় ডেটা এক্সট্র্যাক্ট করা যেকোনো স্প্রেডশীট ব্যবহারের প্রধান কারণ, এক্সেলও এর ব্যতিক্রম নয়। আপনি এক্সেলে ট্যাবুলার আকারে ডেটা সঞ্চয় করে সেগুলো বের করতে পারেন। আজ আমরা আপনাকে দেখাতে যাচ্ছি কিভাবে একাধিক মানদণ্ডের উপর ভিত্তি করে টেবিল থেকে ডেটা বের করতে হয়।
প্রথম জিনিসগুলি প্রথমে, আসুন ডেটাসেট সম্পর্কে জেনে নেওয়া যাক যা আমাদের উদাহরণগুলির ভিত্তি৷
৷
এখানে আমাদের কাছে একটি টেবিল রয়েছে যাতে মুক্তির বছর সহ জেনার এবং প্রধান অভিনেতা সহ চলচ্চিত্রগুলির একটি তালিকা রয়েছে। এই ডেটাসেট ব্যবহার করে, আমরা একাধিক মানদণ্ডের উপর ভিত্তি করে ডেটা পুনরুদ্ধার করব।
মনে রাখবেন যে জিনিসগুলি সহজ রাখার জন্য এটি একটি মৌলিক টেবিল। একটি ব্যবহারিক পরিস্থিতিতে, আপনি অনেক বড় এবং জটিল ডেটাসেটের সম্মুখীন হতে পারেন।
অভ্যাস ওয়ার্কবুক
নিচের লিঙ্ক থেকে অনুশীলন ওয়ার্কবুক ডাউনলোড করতে আপনাকে স্বাগতম।
একাধিক মানদণ্ডের উপর ভিত্তি করে টেবিল থেকে ডেটা বের করুন
এখানে উদাহরণ স্বরূপ আমরা মানদণ্ড হিসেবে জেনার এবং অভিনেতার নাম প্রদান করব এবং এই মানদণ্ডের উপর ভিত্তি করে আমরা সিনেমার নাম বের করব।
1. একক মান ফেরত দিন
এই বিভাগে, আমরা একটি একক মান ফেরত দেব। মানদণ্ডের উপর ভিত্তি করে শুধুমাত্র একটি মান আনা হবে। আসুন অন্বেষণ করি।
আমি। ইনডেক্স-ম্যাচ অ্যারে সূত্র
আমরা INDEX এর সমন্বয় ব্যবহার করতে পারি এবং ম্যাচ ফাংশন INDEX একটি পরিসরে একটি নির্দিষ্ট অবস্থানে মান প্রদান করে। ম্যাচ একটি পরিসরে একটি লুকআপ মানের অবস্থান সনাক্ত করে৷
এই ফাংশনগুলি জানতে, এই নিবন্ধগুলি দেখুন: INDEX, ম্যাচ৷৷
মানদণ্ডের মান নির্ধারণ করা যাক। আপাতত জেনার হিসেবে থ্রিলার এবং হিউ জ্যাকম্যান অভিনেতা এ ক্ষেত্র।
সূত্রটি নিম্নোক্ত হবে
=INDEX($B$4:$B$19,MATCH(1,($H$4=$C$4:$C$19)*($H$5=$D$4:$D$19),0)) B4:B19 একটি অ্যারে যেখান থেকে আমরা মান বের করব। এবং ম্যাচ ফাংশন সারি নম্বর সেট করে যা আনতে হবে।
আপনি দেখতে পাচ্ছেন আমরা lookup_value হিসাবে 1 প্রদান করেছি MATCH এর মধ্যে . এবং lookup_array মানদণ্ড মিলে যুক্তির গুণনের মাধ্যমে তৈরি হয়।
$H$4=$C$4:$C$19 এর মাধ্যমে আমরা জেনারের জন্য চেক করেছি এবং $H$5=$D$4:$D$19 অভিনেতা নামের জন্য।
এটি গুণনের ফলাফলের অ্যারের মধ্যে 1 খুঁজে পায়। এবং সারি নম্বর এবং INDEX ফেরত দিন ফাংশন সিনেমার নাম প্রদান করে।
এটি একটি অ্যারে সূত্র তাই আমাদের CTRL + SHIFT + ENTER ব্যবহার করতে হবে এটি চালানোর জন্য।
মানদণ্ডের মান পরিবর্তন করুন, এবং আপনি আপডেট করা মান পাবেন।
আরো পড়ুন: এক্সেলের একটি সেল থেকে কীভাবে নির্দিষ্ট ডেটা বের করবেন (3টি উদাহরণ)
II. ইনডেক্স-ম্যাচ নন-অ্যারে সূত্র
আমরা INDEX একত্রিত করে একটি নন-অ্যারে সূত্র তৈরি করতে পারি এবং ম্যাচ .
আসুন প্রথমে সূত্রটি দেখি
=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 -এর মধ্যে গুণিত হয়েছে এবং অ্যারে রেফারেন্স হিসাবে কাজ করে।
শুধু প্রবেশ করুন সূত্রটি কার্যকর করবে।
নির্দ্বিধায় মানদণ্ডের মানগুলি সংশোধন করুন, আপনি আপডেট করা মানগুলি পাবেন৷
৷
আরো পড়ুন: কিভাবে এক্সেল থেকে ডেটা এক্সট্র্যাক্ট করবেন মানদণ্ডের (5 উপায়) উপর ভিত্তি করে
III. ইনডেক্স-ম্যাচ-ইফ কম্বিনেশন
পূর্ববর্তী বিভাগগুলিতে, আমরা শর্তগুলি পরীক্ষা করেছি এবং তাদের একসাথে কাজ করতে বাধ্য করার জন্য তাদের গুণ করেছি। আমরা IF ব্যবহার করে গুণ ছেড়ে দিতে পারি ফাংশন।
ইফ একটি যৌক্তিক পরীক্ষা চালায় এবং বুলিয়ান মান প্রদান করে (TRUE অথবা মিথ্যা ) ফলে. ফাংশন সম্পর্কে জানতে এই 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_true_value প্রথম IF-এর জন্য .
আপনাকে CTRL+SHIFT+ENTER ব্যবহার করতে হবে সূত্রটি কার্যকর করতে।
মানদণ্ডের মান পরিবর্তন করুন, এবং আপনি একটি আপডেট করা মান পাবেন।
আরো পড়ুন: কিভাবে এক্সেল শীট থেকে ডেটা বের করতে হয় (6 কার্যকরী পদ্ধতি)
IV. লুকআপ ফাংশন
আমরা LOOKUP ব্যবহার করতে পারি মানদণ্ডের উপর ভিত্তি করে ডেটা বের করার জন্য আমাদের কাজ সম্পাদন করার ফাংশন।
লুকআপ৷ ফাংশন একটি পরিসরের মধ্যে একটি ম্যাচিং লুকআপ সম্পাদন করে এবং সংশ্লিষ্ট মান প্রদান করে। আরও তথ্যের জন্য, Microsoft Support দেখুন সাইট।
এখন সূত্রটি অন্বেষণ করা যাক
=LOOKUP(2,1/($C$4:$C$19=$H$4)/($D$4:$D$19=$H$5),($B$4:$B$19))
এখানে আমরা 2 কে lookup_value হিসেবে সেট করেছি . এবং তাদের দ্বারা ভাগ 1 আকারে দুটি লজিক্যাল অপারেশন হল lookup_vector .
এখানে, এটি 1 কে TRUE/FALSE এর অ্যারে দিয়ে ভাগ করে মান ($C$4:$C$19=$H$4 ), এবং তারপরে TRUE/FALSE এর আরেকটি অ্যারে দ্বারা মান ($D$4:$D$19=$H$5 ) এটি 1 বা একটি #DIV/0! ফেরত দেবে৷ ত্রুটি৷
৷lookup_value সূত্রটিকে রেঞ্জের সাংখ্যিক মানের সাথে মেলাতে বলে, একবার মিল পাওয়া গেলে মানটি অ্যারে থেকে নেওয়া হবে B4:B19 .
CTRL + SHIFT + ENTER টিপতে হবে না চালাতে।
অন্যান্য মানগুলির জন্য সূত্রটি পুরোপুরি কাজ করছে কিনা তা দেখতে মানদণ্ডের মান পরিবর্তন করুন৷
মনে রাখবেন আমরা 2 কে lookup_value হিসেবে ব্যবহার করেছি . এটি 1 থেকে শুরু হওয়া যেকোনো সংখ্যা হতে পারে।
আরো পড়ুন: কিভাবে ইমেজ থেকে এক্সেলে ডেটা এক্সট্র্যাক্ট করবেন (দ্রুত পদক্ষেপ সহ)
একই রকম পড়া
- টেক্সট ফাইলকে এক্সেলে রূপান্তর করতে VBA কোড (7 পদ্ধতি)
- Excel VBA:একটি ওয়েবসাইট থেকে স্বয়ংক্রিয়ভাবে ডেটা টেনে আনুন (2 পদ্ধতি)
- কিভাবে নিরাপদ ওয়েবসাইট থেকে এক্সেলে ডেটা আমদানি করবেন (দ্রুত পদক্ষেপ সহ)
- VLOOKUP এর মাধ্যমে একটি এক্সেল ওয়ার্কশীট থেকে অন্যটিতে স্বয়ংক্রিয়ভাবে ডেটা স্থানান্তর করুন
- কিভাবে এক্সেলকে পাইপ ডেলিমিটার দিয়ে টেক্সট ফাইলে রূপান্তর করা যায় (2 উপায়)
2. একাধিক মান ফেরত দিন
আমি। ইনডেক্স-ছোট সমন্বয়
মানদণ্ডের উপর ভিত্তি করে একাধিক ডেটা বের করার জন্য বিভিন্ন ফাংশন সংমিশ্রণ ব্যবহার করা যেতে পারে। সমন্বয়গুলির মধ্যে একটি হল INDEX – ছোট সমন্বয়।
ছোট ফাংশন মান দ্বারা র্যাঙ্ক করা তালিকায় তাদের অবস্থানের উপর ভিত্তি করে মান প্রদান করে। আরো জানতে এই 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 থেকে মান প্রদান করে এবং বড় ছোট অংশটি সারি নম্বর প্রদান করে, যা আনতে হবে।
IF, SMALL, এর মধ্যে মানদণ্ড মেলে কি না তা পরীক্ষা করে। আমাদের মিলের জন্য দুটি শর্ত আছে, তাই আমরা মানদণ্ড পরীক্ষা করার জন্য উভয় যৌক্তিক ক্রিয়াকলাপকে গুণ করেছি। এবং ROW ফাংশন কলামের ঘরের উপর পুনরাবৃত্তি করে।
তারপর বাইরের ROW SMALL-এর k-th মান বোঝায় ফাংশন এই ফাংশনগুলি একসাথে সারি নম্বর এবং INDEX প্রদান করে ফলাফল প্রদান করে।
IFERROR সূত্র থেকে উদ্ভূত যে কোনো ত্রুটি মোকাবেলা করতে. আমরা এটি এমনভাবে সেট করেছি যে ত্রুটির জন্য এটি একটি খালি ঘর প্রদান করবে৷
এটিকে নীচে টেনে আনুন, আপনি মানদণ্ডের সাথে মেলে এমন সমস্ত মান পাবেন৷
৷
আরো পড়ুন: এক্সেল সূত্র (5টি পদ্ধতি) ব্যবহার করে একটি তালিকা থেকে কীভাবে ডেটা বের করবেন
II. সূচক-সমষ্টি সমন্বয়
সমষ্টি এক্সেলের ফাংশন আমাদের বিভিন্ন কাজ সম্পাদন করতে দেয়। বিভিন্ন অপারেশনের জন্য একটি ফাংশন। আমরা একাধিক মানদণ্ডের উপর ভিত্তি করে একাধিক মান ফেরত দিতে এই ফাংশনটি ব্যবহার করতে পারি।
আসুন ফাংশনটি সম্পর্কে একটু জেনে নেওয়া যাক, AGGREGATE ফাংশন একটি সামগ্রিক গণনা প্রদান করে যেমন AVERAGE, COUNT, MAX, ইত্যাদি।
সমগ্র -এর সিনট্যাক্স ফাংশন নিম্নরূপ:
AGGREGATE(function_number,behavior_options, range) ফাংশন_সংখ্যা: এই সংখ্যাটি নির্দিষ্ট করে যে কোন গণনা করা উচিত।
আচরণ_বিকল্প: নম্বর ব্যবহার করে এটি সেট করুন। এই সংখ্যাটি নির্দেশ করে কিভাবে ফাংশনটি আচরণ করবে।
পরিসীমা: আপনি একত্রিত করতে চান পরিসীমা।
সমষ্টি ফাংশন বিভিন্ন কাজ করে তাই এর মধ্যে ফাংশনের সংখ্যা পূর্বনির্ধারিত থাকে। আমরা কিছু ঘন ঘন ব্যবহৃত ফাংশন নম্বর তালিকাভুক্ত করছি
| ফাংশন | Function_number |
|---|---|
| গড় | 1 |
| COUNT | 2 |
| COUNTA | 3 |
| MAX | 4 |
| MIN | 5 |
| PRODUCT | 6 |
| SUM | 9 |
| LARGE | 14 |
| SMALL | 15 |
ফাংশন সম্পর্কে আরও জানতে, Microsoft Support এ যান৷ সাইট।
এখন সূত্রটি দেখা যাক, এটি হবে INDEX এর সংমিশ্রণ। এবং সমষ্টি .
=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),"")
এখানে আমরা 15 ব্যবহার করেছি function_number হিসেবে AGGREGATE-এ . উপরের টেবিল থেকে, আপনি 15 দেখতে পারেন SMALL এর জন্য কল করে ফাংশন অপারেশন। আপনি এগ্রিগেট ব্যবহার করা ছাড়াও দেখতে পারেন (এবং ফাংশন নম্বর এবং আচরণের বিকল্প নম্বর) সূত্রটি আগের INDEX-এর মতো হুবহু একই –ছোট সূত্র।
প্রক্রিয়াটি একই, INDEX অ্যারে ধারণ করে যা সমষ্টিতে পাওয়া মিলের উপর ভিত্তি করে মান প্রদান করে সূত্রের অংশ।
6 আচরণ বিকল্পের জন্য, যা বোঝায় ত্রুটির মান উপেক্ষা করুন .
এটিকে নীচে টেনে আনুন, আপনি মানদণ্ডের সাথে মেলে এমন সমস্ত মান পাবেন৷
৷
CTRL+SHIFT+ENTER ব্যবহার করতে ভুলবেন না সূত্রটি কার্যকর করতে।
আরো পড়ুন: একক মানদণ্ডের উপর ভিত্তি করে এক্সেলে একাধিক মান ফেরত দিন (3টি বিকল্প)
III. INDEX-MATCH-COUNTIF সমন্বয়
একাধিক মানদণ্ডের উপর ভিত্তি করে একাধিক মান ফেরত দেওয়ার জন্য, আমরা INDEX এর সমন্বয় ব্যবহার করতে পারি , ম্যাচ , এবং 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)),"")
ম্যাচের মধ্যে ফাংশন, আমরা lookup_array, হিসাবে 0 প্রদান করেছি এবং lookup_range এর জন্য আমরা IF ব্যবহার করেছি COUNTIF ধারণকারী অংশ .
এখানে, COUNTIF ফাংশন এমন কোনো মান বাদ দেয় যা ইতিমধ্যেই আনা হয়েছে। এবং দুটি IF ফাংশন দুটি শর্ত চেক করে। We have added these functions so that they together form the lookup_range .
The MATCH portion returns the value as long as 0 is found. The value here works as the row number for INDEX .
Drag it down, you will get all the values that match the criteria.
আরো পড়ুন: How to Extract Data from Cell in Excel (5 Methods)
IV. FILTER Function
If you are using Excel 365, then you can perform the task with a single built-in function called FILTER .
The FILTER function filters a range of data based on given criteria and extracts matching records. To know about the function, visit this article:FILTER.
Let’s explore the formula
=FILTER(B4:B19,(H4=C4:C19)*(H5=D4:D19))
B4:B19 is the array that is to be filtered. Then we have provided the condition, based on what we will extract values. Since we need to check two criteria, we have multiplied them.
Here you will not need to drag down the formula, at one go this will provide all the values and fulfill the list.
আরো পড়ুন: Extract Filtered Data in Excel to Another Sheet (4 Methods)
উপসংহার
That’s all for today. We have listed several methods to extract data from table based on multiple criteria. Hope you will find this helpful. Feel free to comment if anything seems difficult to comprehend. Let us know any other approaches that we have missed here.
আরও পড়া
- How to Extract Data from Excel to Word (4 Ways)
- Extract Data from One Sheet to Another Using VBA in Excel (3 Methods)
- How to Pull Data from Multiple Worksheets in Excel VBA
- Transfer Data from One Excel Worksheet to Another Automatically
- How to Pull Data From Another Sheet Based on Criteria in Excel
- Excel Macro:Extract Data from Multiple Excel Files (4 Methods)
- How to Extract Year from Date in Excel (3 Ways)
