এক্সেলে ডাইনামিক রেঞ্জ নেম ব্যবহার করে ফ্লেক্সিবল ড্রপডাউন তৈরি

এক্সেল স্প্রেডশীটে প্রায়শই সেল ড্রপডাউন থাকে ডেটা এন্ট্রি সরল ও স্ট্যান্ডার্ডাইজ করার জন্য। এই ড্রপডাউনগুলো ডেটা ভ্যালিডেশন ফিচার ব্যবহার করে অলৌয়েবল এন্ট্রির লিস্ট স্পেসিফাই করে তৈরি করা হয়।

একটি সিম্পল ড্রপডাউন লিস্ট সেট আপ করতে, ডেটা এন্ট্রি হবে সেসেল সিলেক্ট করুন, তারপর Data Validation ( Data ট্যাবে), Data Validation সিলেক্ট করুন, List বেছে নিন (Allow:-এর অধীনে), এবং তারপর Source: ফিল্ডে লিস্ট আইটেমগুলো কমা দিয়ে আলাদা করে লিখুন (Figure 1 দেখুন)।

এই টাইপের বেসিক ড্রপডাউনে, অলৌয়েবল এন্ট্রির লিস্ট ডেটা ভ্যালিডেশনের ভেতরেই স্পেসিফাইড থাকে; তাই লিস্টে পরিবর্তন আনতে হলে ইউজারকে ডেটা ভ্যালিডেশন খুলে এডিট করতে হয়। অঅনভিজ্ঞ ব্যবহারকারীদের জন্য বা লम्बা চয়েস লিস্টের কেসে এটি কঠিন হতে পারে।

আরেকটি অপশন হল লিস্টকে স্প্রেডশীটের মধ্যে একটি নেমড রেঞ্জে রাখা, এবং তারপর সেই রেঞ্জ নেমকে (ইকোয়াল সাইনসহ) ডেটা ভ্যালিডেশনের Source: ফিল্ডে স্পেসিফাই করা (Figure 2-এ দেখানো হয়েছে)।

এই দ্বিতীয় মেথড লিস্টের চয়েসগুলো এডিট করা সহজ করে দেয়, কিন্তু আইটেম যোগ বা রিমুভ করা সমস্যা সৃষ্টি করতে পারে। যেহেতু নেমড রেঞ্জ (আমাদের উদাহরণে FruitChoices) একটি ফিক্সড রেঞ্জ অফ সেলকে রেফার করে ($H$3:$H$10 হিসাবে দেখানো হয়েছে), যদি আরও চয়েস H11 বা নিচের সেলগুলিতে যোগ করা হয়, সেগুলো ড্রপডাউনে দেখাবে না (যেহেতু সেসেলগুলো FruitChoices রেঞ্জের অংশ নয়)।

একইভাবে যদি, উদাহরণস্বরূপ, Pears এবং Strawberries এন্ট্রিগুলো মুছে ফেলা হয়, সেগুলো আর ড্রপডাউনে দেখাবে না, কিন্তু বদলে ড্রপডাউনে দুটি "খালি" চয়েস থাকবে যেহেতু ড্রপডাউন এখনও পুরো FruitChoices রেঞ্জকে রেফার করছে, খালি সেল H9 এবং H10 সহ।

এই কারণগুলো үшін, যখন একটি নরমাল নেমড রেঞ্জ ড্রপডাউনের লিস্ট সোর্স হিসেবে ব্যবহার করা হয়, নেমড রেঞ্জটিই অবশ্যই এডিট করতে হবে বেশি বা কম সেল অন্তর্ভুক্ত করার জন্য যদি লিস্ট থেকে এন্ট্রি যোগ বা ডিলিট করা হয়।

এই সমস্যার সমাধান হল ড্রপডাউন চয়েসের সোর্স হিসেবে একটি ডাইনামিক রেঞ্জ নেম ব্যবহার করা। একটি ডাইনামিক রেঞ্জ নেম হল যা অটোম্যাটিক্যালি এক্সপ্যান্ড (বা কন্ট্র্যাক্ট) করে এক্সাক্টলি ডেটা ব্লকের সাইজের সাথে মেলাতে যতক্ষণ না এন্ট্রি যোগ বা রিমুভ হয়। এটি করার জন্য, আপনি একটি ফর্মুলা ব্যবহার করেন, ফিক্সড রেঞ্জ অফ সেল অ্যাড্রেসের বদলে, নেমড রেঞ্জ ডিফাইন করতে।

এক্সেলে ডাইনামিক রেঞ্জ সেট আপ করার পদ্ধতি

একটি নরমাল (স্ট্যাটিক) রেঞ্জ নেম একটি স্পেসিফাইড রেঞ্জ অফ সেলকে রেফার করে ($H$3:$H$10 আমাদের উদাহরণে, নিচে দেখুন):

কিন্তু একটি ডাইনামিক রেঞ্জ একটি ফর্মুলা ব্যবহার করে ডিফাইন করা হয় (নিচে দেখুন, একটি আলাদা স্প্রেডশীট থেকে নেওয়া যা ডাইনামিক রেঞ্জ নেম ব্যবহার করে):

শুরু করার আগে, নিশ্চিত হন আপনি আমাদের এক্সেল উদাহরণ ফাইল ডাউনলোড করেছেন (সর্ট ম্যাক্রো ডিসেবল্ড)।

এই ফর্মুলাটি বিস্তারিত দেখি। Fruits-এর চয়েসগুলো একটি ব্লক অফ সেলে রয়েছে সরাসরি একটি হেডিং (FRUITS)র নিচে। সেই হেডিংটিকেও একটি নাম দেওয়া আছে: FruitsHeading:

Fruits চয়েসের জন্য ডাইনামিক রেঞ্জ ডিফাইন করার পুরো ফর্মুলা হল:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)

FruitsHeading সেই হেডিংকে রেফার করে যা লিস্টের প্রথম এন্ট্রির এক সারি উপরে আছে। নম্বর ২০ (ফর্মুলায় দুবার ব্যবহৃত) লিস্টের সর্বোচ্চ সাইজ (সারির সংখ্যা) বোঝায় (এটি ইচ্ছামতো এডজাস্ট করা যায়)।

নোট করুন এই উদাহরণে লিস্টে শুধু ৮টি এন্ট্রি রয়েছে, কিন্তু এন্ট্রি যোগ করার জন্য নিচে খালি সেলও রয়েছে। নম্বর ২০ বোঝায় সার ব্লক যেখানে এন্ট্রি করা যায়, এক्चুয়াল এন্ট্রির সংখ্যা নয়।

এখন ফর্মুলাটি টুকরো টুকরো করে বুঝি (প্রতিটি টুকরো কালার-কোডিং করে), কীভাবে এটি কাজ করে:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(OFFSET(FruitsHeading,1,0,20,1)),0,0),0)-1,20),1)

"ইনারমোস্ট" টুকরো হল OFFSET(FruitsHeading,1,0,20,1)। এটি সেই ২০ সেলের ব্লককে রেফার করে (FruitsHeading সেলের নিচে) যেখানে চয়েস ইনপুট করা যেতে পারে। এই OFFSET ফাংশন বাসিক্যালি বলে: FruitsHeading সেল থেকে শুরু করুন, ১ সারি নিচে যান এবং ০ কলাম আড়ালে, তারপর একটি এরিয়া সিলেক্ট করুন যা ২০ সারি লম্বা এবং ১ কলাম চওড়া। তাহলে আমরা পেলাম ২০-সারির ব্লক যেখানে Fruits চয়েসগুলো ইনপুট করা হয়।

ফর্মুলার পরবর্তী টুকরো হল ISBLANK ফাংশন:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)

এখানে, উপরে ব্যাখ্যা করা OFFSET ফাংশনকে "the above" দিয়ে রিপ্লেস করা হয়েছে (পড়া সহজ করার জন্য)। কিন্তু ISBLANK ফাংশনটি OFFSET ফাংশন যা ডিফাইন করে সেটি ২০-সারি রেঞ্জ অফ সেলের ওপর অপারেট করছে।

ISBLANK তারপর ২০টি TRUE এবং FALSE ভ্যালুর একটি সেট তৈরি করে, প্রতিটি ইন্ডিভিজুয়াল সেল ২০-সারি রেঞ্জে (যেটি OFFSET ফাংশন রেফার করে) খালি (empty) কিনা তা বোঝাতে। এই উদাহরণে, সেটের প্রথম ৮টি ভ্যালু FALSE যেহেতু প্রথম ৮টি সেল খালি নয় এবং শেষ ১২টি TRUE হবে।

ফর্মুলার পরবর্তী টুকরো হল INDEX ফাংশন:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)

আবার, "the above" উপরে বর্ণিত ISBLANK এবং OFFSET ফাংশনকে রেফার করে। INDEX ফাংশন একটি অ্যারে রিটার্ন করে যেটি ISBLANK ফাংশন তৈরি করেছে ২০টি TRUE/FALSE ভ্যালু ধারণ করে।

INDEX সাধারণত একটি ব্লক অফ ডেটা থেকে একটি নির্দিষ্ট ভ্যালু (বা রেঞ্জ অফ ভ্যালু) পিক করতে ব্যবহার হয়, সেটি ব্লকের মধ্যে নির্দিষ্ট সারি ও কলাম স্পেসিফাই করে। কিন্তু রো এবং কলাম ইনপুট শূন্য সেট করলে (যেমন এখানে করা হয়েছে) INDEX পুরো ব্লক অফ ডেটা ধারণকারী একটি অ্যারে রিটার্ন করে।

ফর্মুলার পরবর্তী টুকরো হল MATCH ফাংশন:

=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)

MATCH ফাংশন INDEX ফাংশন রিটার্ন করা অ্যারেতে প্রথম TRUE ভ্যালুর পজিশন রিটার্ন করে। যেহেতু লিস্টের প্রথম ৮টি এন্ট্রি খালি নয়, অ্যারেতে প্রথম ৮টি ভ্যালু FALSE হবে, এবং নবম ভ্যালু TRUE হবে (যেহেতু রেঞ্জের ৯ম সারি খালি)।

তাই MATCH ফাংশন ৯ ভ্যালু রিটার্ন করবে। এই কেসে, কিন্তু, আমরা আসলে জানতে চাই লিস্টে কতটি এন্ট্রি রয়েছে, তাই ফর্মুলা MATCH ভ্যালু থেকে ১ বিয়োগ করে (যে দিয়ে লাস্ট এন্ট্রির পজিশন পাওয়া যায়)। সুতরাং, MATCH(TRUE,the above,0)-1 সุดান্তে ৮ ভ্যালু রিটার্ন করে।

ফর্মুলার পরবর্তী টুকরো হল IFERROR ফাংশন:

=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)

IFERROR ফাংশন একটি অল্টারনেট ভ্যালু রিটার্ন করে, যদি প্রথম স্পেসিফাইড ভ্যালু এরর রেজাল্ট করে। এই ফাংশনটি অন্তর্ভুক্ত করা হয়েছে কারণ, যদি সেলের পুরো ব্লক (সকল ২০ সারি) এন্ট্রি দিয়ে ফুল থাকে, MATCH ফাংশন এরর রিটার্ন করবে।

এটি इसलिये যে আমরা MATCH ফাংশনকে বলছি প্রথম TRUE ভ্যালু খুঁজতে (ISBLANK ফাংশনের অ্যারে ভ্যালুতে), কিন্তু যদি কোনো সেল খালি না থাকে, তবে পুরো অ্যারে FALSE ভ্যালু দিয়ে ভরা থাকবে। MATCH যদি টার্গেট ভ্যালু (TRUE) সার্চ করা অ্যারেতে না পায়, তবে এরর রিটার্ন করে।

তাই, যদি পুরো লিস্ট ফুল থাকে (অতএব, MATCH এরর রিটার্ন করে), IFERROR ফাংশন বদলে ২০ ভ্যালু রিটার্ন করবে (যেন我们知道 লিস্টে ২০টি এন্ট্রি রয়েছে)।

শেষে, OFFSET(FruitsHeading,1,0,the above,1) সেই রেঞ্জ রিটার্ন করে যা আমরা আসলে খুঁজছি: FruitsHeading সেল থেকে শুরু, ১ সারি নিচে এবং ০ কলাম আড়ালে, তারপর একটি এরিয়া সিলেক্ট করুন যা তত সারি লম্বা যতটি এন্ট্রি লিস্টে রয়েছে (এবং ১ কলাম চওড়া)। তাই পুরো ফর্মুলা একসাথে সেটি রেঞ্জ রিটার্ন করবে যেটি শুধু একচুয়াল এন্ট্রিগুলো ধারণ করে (প্রথম খালি সellige পর্যন্ত)।

এই ফর্মুলা ব্যবহার করে ড্রপডাউনের সোর্স হওয়া রেঞ্জ ডিফাইন করলে আপনি আজাদে লিস্ট এডিট করতে পারবেন (এন্ট্রি যোগ বা রিমুভ, যতক্ষণ না বাকি এন্ট্রিগুলো টপ সেল থেকে শুরু হয় এবং কন্টিগুয়াস থাকে) এবং ড্রপডাউন সবসময় কারেন্ট লিস্ট রিফ্লেক্ট করবে (Figure 6 দেখুন)।

এখানে ব্যবহৃত উদাহরণ ফাইল (Dynamic Lists) এই ওয়েবসাইট থেকে ডাউনলোডযোগ্য। কিন্তু ম্যাক্রো কাজ করে না, কারণ ওয়ার্ডপ্রেস এক্সেল বুক যাতে ম্যাক্রো থাকে সেটি পছন্দ করে না।

লিস্ট ব্লকের সারির সংখ্যা স্পেসিফাই করার বিকল্প হিসেবে, লিস্ট ব্লককে নিজস্ব একটি রেঞ্জ নেম অ্যাসাইন করা যায়, যাপর একটি মডিফাইড ফর্মুলায় ব্যবহার করা যায়। উদাহরণ ফাইলে, একটি সেকেন্ড লিস্ট (Names) এই মেথড ব্যবহার করে। এখানে, পুরো লিস্ট ব্লক ("NAMES" হেডিংয়ের নিচে, উদাহরণ ফাইলে ৪০ সারি) NameBlock রেঞ্জ নেম অ্যাসাইন করা হয়েছে। NamesList ডিফাইন করার অল্টারনেটিভ ফর্মুলা হল:

=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)

যেখানে NamesBlock OFFSET(FruitsHeading,1,0,20,1)-কে রিপ্লেস করেছে এবং ROWS(NamesBlock) আগের ফর্মুলার ২০ (সারির সংখ্যা)-কে রিপ্লেস করেছে।

তাই, ড্রপডাউন লিস্ট যেগুলো সহজে এডিট করা যায় (অঅনভিজ্ঞ অন্যান্য ব্যবহারকারীদের ידיও), ডাইনামিক রেঞ্জ নেম ব্যবহার করে দেখুন! এবং নোট করুন, যদিও এই আর্টিকেল ড্রপডাউন লিস্টের ওপর ফোকাস করেছে, ডাইনামিক রেঞ্জ নেম যেকোনো জায়গায় ব্যবহার করা যায় যেখানে আপনাকে একটি রেঞ্জ বা লিস্ট রেফারেন্স করতে হয় যা সাইজে ভিন্ন হয়তে পারে। Enjoy!