এক্সেল বিশাল ডেটাসেট পরিচালনার জন্য সবচেয়ে কার্যকর টুল। সাধারণত আমরা ড্রপ-ডাউন লিস্ট তৈরি করতে অভ্যস্ত, কিন্তু często কাজের সুবিধার্থে ডাইনামিক ড্রপ-ডাউন লিস্ট তৈরি করার দরকার পড়ে। OFFSET ফাংশন প্রয়োগ করে এটি সহজেই করা যায়। এই নিবন্ধে আমি দেখাবো কিভাবে OFFSET ফাংশন ব্যবহার করে এক্সেলে ডাইনামিক ড্রপ-ডাউন লিস্ট তৈরি করতে হয়।
নিচের ডেটাসেটটি ব্যবহার করে আমি ব্যাখ্যা করব কিভাবে OFFSET ফাংশন দিয়ে এক্সেলে ডাইনামিক ড্রপ-ডাউন লিস্ট তৈরি করা যায়। এখানে কিছু স্পোর্টস ইভেন্ট এবং তাদের বিজয়ীদের তালিকা রয়েছে। ডাইনামিক ড্রপ-ডাউন লিস্ট তৈরি করে বিজয়ীদের সংশ্লিষ্ট ইভেন্ট অনুযায়ী সাজানো হবে।
এক্সেল OFFSET ব্যবহার করে ডাইনামিক ড্রপ-ডাউন লিস্ট তৈরির ৩টি পদ্ধতি
১. OFFSET এবং COUNTA ফাংশন দিয়ে ডাইনামিক ড্রপ-ডাউন লিস্ট তৈরি
এখানে আমি OFFSET এবং COUNTA ফাংশন ব্যবহার করে এক্সেলে ডাইনামিক ড্রপ-ডাউন লিস্ট তৈরি করব। C4:C11 রেঞ্জে ডাইনামিক ড্রপ-ডাউন লিস্ট তৈরি করতে হবে, যেখানে বিজয়ীদের তালিকা থেকে বিজয়ী নির্বাচন করব।
ধাপসমূহ:
➤ C4:C11 রেঞ্জটি নির্বাচন করুন। তারপর Data ট্যাব → Data Tools → Data Validation → Data Validation এ যান।
➤ Data Validation ডায়ালগ বক্স খুলবে। সেখানে Allow ড্রপ-ডাউন থেকে List নির্বাচন করুন।
➤ Source বক্সে নিচের সূত্রটি লিখুন:
=OFFSET($E$4,0,0,COUNTA($E$4:$E$100),1)
সূত্র বিশ্লেষণ
➥ COUNTA($E$4:$E$100) ➜ E4:E100 রেঞ্জে খালি না থাকা সেলের সংখ্যা রিটার্ন করে
আউটপুট ➜ {4}
➥ OFFSET($E$4,0,0,COUNTA($E$4:$E$100),1) ➜ দেওয়া রেফারেন্সের সারি ও কলামের উপর ভিত্তি করে একটি রেঞ্জ রিটার্ন করে।
➥ OFFSET($E$4,0,0,4,1)
আউটপুট ➜ {“Alex”;”Morgan”;”Faulkner”;”Eliot”}
ব্যাখ্যা: রেফারেন্স হল E4। সারি ০ এবং কলাম ০ হলে, ৪ সেলের উচ্চতার সাথে E4:E7 সেলগুলো থেকে মানগুলো পাওয়া যাবে।
➤ OK ক্লিক করুন।
এক্সেল C4:C11 রেঞ্জের প্রতিটি সেলে একটি ড্রপ-ডাউন বক্স তৈরি করবে।
লক্ষ্য করুন যে ড্রপ-ডাউন বক্সের অপশনগুলো হুবহু বিজয়ীদের তালিকার মত। এখন যাচাই করা যাক এটি সত্যিই ডাইনামিক কিনা। ধরে নিন শুটিং ইভেন্টের বিজয়ী জেমস, যিনি বিজয়ীদের তালিকায় নেই। তার নাম যোগ করলে কী হয় দেখুন।
জেমসের নাম বিজয়ীদের তালিকায় যোগ करते ही এক্সেল স্বয়ংক্রিয়ভাবে ড্রপ-ডাউন অপশনগুলো আপডেট করল। সুতরাং এই ড্রপ-ডাউন লিস্টগুলো ডাইনামিক প্রকৃতির।
➤ এখন বাকি বিজয়ীদের নির্বাচন করুন।
নোট: মনে রাখবেন COUNTA ফাংশনে আমরা E4:E100 রেঞ্জ নির্বাচন করেছি। তাই যতক্ষণ E4:E100 রেঞ্জের সেলগুলো আপডেট বা যোগ করব, এক্সেল ড্রপ-ডাউন অপশনগুলো আপডেট করতে থাকবে।
আরও পড়ুন: VBA ব্যবহার করে এক্সেলে ডাইনামিক ডেটা ভ্যালিডেশন লিস্ট তৈরি করার উপায়
২. OFFSET এবং COUNTIF ফাংশন দিয়ে ডাইনামিক ড্রপ-ডাউন লিস্ট তৈরি
OFFSET এবং COUNTIF ফাংশন ব্যবহার করেও এক্সেলে ডাইনামিক ড্রপ-ডাউন লিস্ট তৈরি করা যায়।
ধাপসমূহ:
➤ পদ্ধতি-১ এর মতো Data Validation ডায়ালগ বক্স খুলুন। Source বক্সে নিচের সূত্রটি লিখুন:
=OFFSET($E$4,0,0,COUNTIF($E$4:$E$100,"<>"))
সূত্র বিশ্লেষণ
➥ COUNTIF($E$4:$E$100,”<>”) ➜ E4:E100 রেঞ্জে খালি না থাকা সেলের সংখ্যা রিটার্ন করে
আউটপুট ➜ {4}
➥ OFFSET($E$4,0,0,COUNTIF($E$4:$E$100,”<>”)) ➜ দেওয়া রেফারেন্সের সারি ও কলামের উপর ভিত্তি করে একটি রেঞ্জ রিটার্ন করে।
➥ OFFSET($E$4,0,0,4,1)
আউটপুট ➜ {“Alex”;”Morgan”;”Faulkner”;”Eliot”}
ব্যাখ্যা: রেফারেন্স হল E4। সারি ০ এবং কলাম ০ হলে, ৪ সেলের উচ্চতার সাথে E4:E7 সেলগুলো থেকে মানগুলো পাওয়া যাবে।
➤ OK ক্লিক করুন।
➤ এক্সেল C4:C11 রেঞ্জের প্রতিটি সেলে একটি ড্রপ-ডাউন বক্স তৈরি করবে।
ডাইনামিক কিনা যাচাই করতে আবার শুটিং ইভেন্টের বিজয়ী জেমস ধরে নিন। তার নাম বিজয়ীদের তালিকায় যোগ করলে কী হয় দেখুন।
জেমসের নাম যোগ করতেই এক্সেল স্বয়ংক্রিয়ভাবে ড্রপ-ডাউন অপশনগুলো আপডেট করল। সুতরাং এই ড্রপ-ডাউন লিস্টগুলো ডাইনামিক প্রকৃতির।
➤ এখন বাকি বিজয়ীদের নির্বাচন করুন।
নোট: মনে রাখবেন COUNTIF ফাংশনে আমরা E4:E100 রেঞ্জ নির্বাচন করেছি। তাই যতক্ষণ E4:E100 রেঞ্জের সেলগুলো আপডেট বা যোগ করব, এক্সেল ড্রপ-ডাউন অপশনগুলো আপডেট করতে থাকবে।
৩. একাধিক ফাংশন সংযোজনে নেস্টেড ড্রপ-ডাউন লিস্ট তৈরি
এই বিভাগে আমরা আরও উন্নত একটি ডাইনামিক ড্রপ-ডাউন লিস্ট তৈরি করব, যা নেস্টেড। এখানে OFFSET, COUNTA, এবং MATCH ফাংশন একসাথে ব্যবহার করা হবে। আমরা কী করতে যাচ্ছি তা ব্যাখ্যা করছি।
নিচের ডেটাসেটটি নির্দিষ্ট পণ্যবर्गের তথ্য প্রদর্শন করে। আমরা F3 এবং F4 সেলে দুটি ড্রপ-ডাউন লিস্ট তৈরি করব। F3-এ নির্বাচিত অপশনের উপর ভিত্তি করে F4-এর অপশনগুলো এক্সেল আপডেট করবে। ধাপে ধাপে করা যাক।
ধাপ-১: F3-এ ড্রপ-ডাউন লিস্ট তৈরি
➤ পদ্ধতি-১ এর মতো Data Validation ডায়ালগ বক্স খুলুন। Source বক্সে টেবিল হেডারগুলো (B3:D3 সেল রেঞ্জ) রেফারেন্স দিন।
এক্সেল F3-এ একটি ড্রপ-ডাউন লিস্ট তৈরি করবে।
ধাপ-২: F4-এ ডাইনামিক ড্রপ-ডাউন লিস্ট তৈরি
এখন F4-এ অন্য একটি ড্রপ-ডাউন লিস্ট তৈরি করব। F4-এর ড্রপ-ডাউন লিস্টের অপশনগুলো F3-এর ড্রপ-ডাউন লিস্টে কী নির্বাচন করা হয়েছে তার ওপর নির্ভর করবে। এর জন্য,
➤ পদ্ধতি-১ এর মতো Data Validation ডায়ালগ বক্স খুলুন। Source বক্সে নিচের সূত্রটি লিখুন:
=OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,COUNTA(OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,10,1)),1)
সূত্র বিশ্লেষণ
➥ MATCH($F$3,$B$3:$D$3,0) ➜ B3:D3 রেঞ্জ থেকে F3 সেলের মানটির tương대িক অবস্থান রিটার্ন করে
আউটপুট: {1}।
➥ OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,10,1) ➜ দেওয়া রেফারেন্স-এর সারি ও কলামের উপর ভিত্তি করে একটি রেঞ্জ রিটার্ন করে। উচ্চতা ১০। তাই আউটপুট হবে রেফারেন্স থেকে শুরু করে ১০টি সেলের মান।
আউটপুট: {“Sam”;”Curran”;”Yank”;”Rochester”;0;0;0;0;0;0}
➥ COUNTA(OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,10,1)) ➜ নির্বাচিত রেঞ্জে খালি না থাকা সেলের সংখ্যা রিটার্ন করে।
➥ COUNTA{“Sam”;”Curran”;”Yank”;”Rochester”;0;0;0;0;0;0}
আউটপুট: {4}
➥ OFFSET($B$3,1,MATCH($F$3,$B$3:$D$3,0)-1,COUNTA(OFFSET($B$3,1,MATCH ($F$3,$B $3:$D$3,0)-1,10,1)),1) ➔ দেওয়া রেফারেন্সের সারি এবং কলামের উপর ভিত্তি করে একটি রেঞ্জ রিটার্ন করে
➥ OFFSET($B$3,1,1-1,COUNTA{“Sam”;”Curran”;”Yank”;”Rochester”;0;0;0;0;0;0}),1)
➥ OFFSET($B$3,1,0,4,1)
আউটপুট: {“Sam”;”Curran”;”Yank”;”Rochester”}
ব্যাখ্যা: রেফারেন্স হল B3। সারি ১ এবং কলাম ০ হলে, ৪ সেলের উচ্চতার সাথে B4:B7 সেলগুলো থেকে মানগুলো পাওয়া যাবে।
➤ OK ক্লিক করুন।
এক্সেল F4-এ একটি ডাইনামিক ড্রপ-ডাউন লিস্ট তৈরি করবে। অপশনগুলো F3-এ কী নির্বাচন করা হয়েছে তার ওপর পরিবর্তিত হবে। উদাহরণস্বরূপ, F3 ড্রপ-ডাউন লিস্টে Name নির্বাচন করলে, F4-এর ড্রপ-ডাউন লিস্টে Name কলামের উপলব্ধ নামগুলো দেখাবে।
একইভাবে, F3 ড্রপ-ডাউন লিস্টে Product নির্বাচন করলে, F4-এর ড্রপ-ডাউন লিস্টে Product কলামের উপলব্ধ পণ্যগুলো দেখাবে।
এখন আপনি Name, Product, বা Brand যোগ বা আপডেট করলে, এক্সেল F4-এর ড্রপ-ডাউন লিস্ট আপডেট করবে। উদাহরণস্বরূপ, আমি Name কলামে নতুন নাম Rock যোগ করলাম এবং এক্সেল তা ড্রপ-ডাউন লিস্টে যোগ করেছে।
আরও পড়ুন: এক্সেলে ডাইনামিক টপ ১০ লিস্ট তৈরি করার ৮টি পদ্ধতি
অনুশীলন ওয়ার্কবুক
আপনি দেখছেন, OFFSET ফাংশন দিয়ে এক্সেলে ডাইনামিক ড্রপ-ডাউন লিস্ট তৈরি করা কিছুটা জটিল। তাই আমি পরামর্শ দিচ্ছি বেশি বেশি অনুশীলন করার। আপনার জন্য একটি অনুশীলন শীট সংযুক্ত করেছি।
উপসংহার
এই নিবন্ধে আমি ৩টি পদ্ধতি দেখিয়েছি কিভাবে OFFSET ফাংশন ব্যবহার করে এক্সেলে ডাইনামিক ড্রপ-ডাউন লিস্ট তৈরি করা যায়। আশা করি এই নিবন্ধটি আপনার জন্য সহায়ক হবে। শেষে, আপনার কোনো মন্তব্য থাকলে কমেন্ট বক্সে জানাতে পারেন।
সম্পর্কিত নিবন্ধসমূহ
- এক্সেলে টেবিল থেকে ডাইনামিক লিস্ট তৈরি করা (৩টি সহজ উপায়)
- এক্সেলে শর্তের ওপর ভিত্তি করে ডাইনামিক লিস্ট তৈরি (একক ও একাধিক শর্ত)
