নমনীয় ড্রপডাউনের জন্য এক্সেলে ডায়নামিক রেঞ্জ নাম ব্যবহার করুন

এক্সেল স্প্রেডশীটগুলি প্রায়ই ডেটা এন্ট্রিকে সহজ এবং/অথবা প্রমিত করার জন্য সেল ড্রপডাউন অন্তর্ভুক্ত করে। এই ড্রপডাউনগুলি অনুমোদনযোগ্য এন্ট্রিগুলির একটি তালিকা নির্দিষ্ট করার জন্য ডেটা যাচাইকরণ বৈশিষ্ট্য ব্যবহার করে তৈরি করা হয়৷

একটি সাধারণ ড্রপডাউন তালিকা সেট আপ করতে, যে ঘরে ডেটা প্রবেশ করা হবে সেটি নির্বাচন করুন, তারপর ডেটা যাচাইকরণ এ ক্লিক করুন (ডেটা-এ ট্যাব), ডেটা যাচাইকরণ নির্বাচন করুন, তালিকা নির্বাচন করুন (অনুমতি দিন:) এবং তারপর উৎস-এ তালিকা আইটেমগুলি (কমা দ্বারা পৃথক করা) লিখুন :ক্ষেত্র (চিত্র 1 দেখুন)।

এই ধরনের মৌলিক ড্রপডাউনে, অনুমোদনযোগ্য এন্ট্রিগুলির তালিকা ডেটা যাচাইকরণের মধ্যেই নির্দিষ্ট করা হয়; অতএব, তালিকায় পরিবর্তন করতে, ব্যবহারকারীকে অবশ্যই ডেটা যাচাইকরণ খুলতে হবে এবং সম্পাদনা করতে হবে। যদিও, অনভিজ্ঞ ব্যবহারকারীদের জন্য বা পছন্দের তালিকা দীর্ঘতর ক্ষেত্রে এটি কঠিন হতে পারে।

আরেকটি বিকল্প হল তালিকাটিকে স্প্রেডশীটের মধ্যে একটি নামযুক্ত পরিসরে স্থাপন করা, এবং তারপর উৎস-এ সেই পরিসরের নাম (একটি সমান চিহ্ন সহ পূর্বে) উল্লেখ করা। :ডেটা যাচাইকরণের ক্ষেত্র (চিত্র 2 এ দেখানো হয়েছে)।

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

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

এই কারণে, যখন একটি ড্রপডাউনের জন্য তালিকার উত্স হিসাবে একটি সাধারণ নামকৃত পরিসর ব্যবহার করা হয়, তালিকা থেকে এন্ট্রিগুলি যোগ করা বা মুছে ফেলা হলে নামযুক্ত পরিসরটিকেই আরও বা কম কক্ষ অন্তর্ভুক্ত করতে সম্পাদনা করতে হবে৷

এই সমস্যার সমাধান হল একটি গতিশীল ব্যবহার করা ড্রপডাউন পছন্দের উৎস হিসেবে পরিসরের নাম। একটি ডাইনামিক রেঞ্জের নাম হল এমন একটি যা স্বয়ংক্রিয়ভাবে প্রসারিত হয় (বা চুক্তি) যাতে এন্ট্রি যুক্ত বা সরানো হয় ডেটা ব্লকের আকারের সাথে ঠিক মেলে। এটি করতে, আপনি একটি সূত্র ব্যবহার করুন৷ , নামকৃত পরিসর সংজ্ঞায়িত করতে সেল ঠিকানাগুলির একটি নির্দিষ্ট পরিসরের পরিবর্তে।

এক্সেল এ কিভাবে একটি ডাইনামিক রেঞ্জ সেটআপ করবেন

একটি সাধারণ (স্ট্যাটিক) পরিসরের নাম একটি নির্দিষ্ট কক্ষের পরিসরকে বোঝায় ($H$3:$H$10 আমাদের উদাহরণে, নীচে দেখুন):

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

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

আসুন এই সূত্রটি বিস্তারিতভাবে পরীক্ষা করি। ফলের জন্য পছন্দগুলি সরাসরি একটি শিরোনামের নীচে কোষগুলির একটি ব্লকে রয়েছে (FRUITS ) সেই শিরোনামটিকেও একটি নাম দেওয়া হয়েছে:Fruitsheading :

ফলের পছন্দের জন্য গতিশীল পরিসর নির্ধারণ করতে ব্যবহৃত পুরো সূত্রটি হল:

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

ফলের শিরোনাম তালিকার প্রথম এন্ট্রির উপরে একটি সারির শিরোনাম বোঝায়। সংখ্যা 20 (সূত্রে দুইবার ব্যবহার করা হয়েছে) হল তালিকার জন্য সর্বাধিক আকার (সারির সংখ্যা) (এটি ইচ্ছামত সামঞ্জস্য করা যেতে পারে)।

মনে রাখবেন যে এই উদাহরণে, তালিকায় শুধুমাত্র 8টি এন্ট্রি রয়েছে, তবে এর নীচে খালি ঘরও রয়েছে যেখানে অতিরিক্ত এন্ট্রি যোগ করা যেতে পারে। 20 নম্বরটি সম্পূর্ণ ব্লককে নির্দেশ করে যেখানে এন্ট্রি করা যেতে পারে, এন্ট্রির প্রকৃত সংখ্যাকে নয়৷

এখন সূত্রটিকে টুকরো টুকরো করে (প্রতিটি টুকরো রঙ-কোডিং) করা যাক, এটি কীভাবে কাজ করে তা বোঝার জন্য:

=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) . এটি 20 টি কোষের ব্লককে উল্লেখ করে (ফ্রুটহেডিং সেলের নীচে) যেখানে পছন্দগুলি প্রবেশ করা যেতে পারে। এই OFFSET ফাংশনটি মূলত বলে:Fruitsheading থেকে শুরু করুন সেল, 1 সারি এবং 0টির বেশি কলামের নিচে যান, তারপর 20 সারি লম্বা এবং 1 কলাম চওড়া একটি এলাকা নির্বাচন করুন। তাই এটি আমাদের 20-সারি ব্লক দেয় যেখানে ফল পছন্দগুলি প্রবেশ করানো হয়।

সূত্রের পরবর্তী অংশটি হল ISBLANK ফাংশন:

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

এখানে, OFFSET ফাংশন (উপরে ব্যাখ্যা করা হয়েছে) প্রতিস্থাপিত হয়েছে "উপরের" (জিনিসগুলিকে পড়া সহজ করার জন্য) দিয়ে। কিন্তু ISBLANK ফাংশনটি 20-সারির কক্ষের পরিসরে কাজ করছে যা OFFSET ফাংশন সংজ্ঞায়িত করে৷

ISBLANK তারপর 20টি সত্য এবং মিথ্যা মানের একটি সেট তৈরি করে, যা নির্দেশ করে যে 20-সারির পরিসরের প্রতিটি পৃথক কক্ষ OFFSET ফাংশন দ্বারা উল্লেখ করা ফাঁকা (খালি) বা না। এই উদাহরণে, সেটের প্রথম 8টি মান FALSE হবে কারণ প্রথম 8টি ঘর খালি নেই এবং শেষ 12টি মান সত্য হবে৷

সূত্রের পরবর্তী অংশটি হল INDEX ফাংশন:

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

আবার, "উপরের" বলতে উপরে বর্ণিত ISBLANK এবং OFFSET ফাংশনগুলিকে বোঝায়। INDEX ফাংশনটি ISBLANK ফাংশন দ্বারা তৈরি 20টি সত্য / মিথ্যা মান ধারণকারী একটি অ্যারে প্রদান করে৷

INDEX সাধারণত একটি নির্দিষ্ট সারি এবং কলাম নির্দিষ্ট করে (সেই ব্লকের মধ্যে) ডেটার ব্লক থেকে একটি নির্দিষ্ট মান (বা মানের পরিসর) বাছাই করতে ব্যবহৃত হয়। কিন্তু সারি এবং কলাম ইনপুটগুলিকে শূন্যে সেট করা (যেমন এখানে করা হয়েছে) INDEX-কে ডেটার সম্পূর্ণ ব্লক সমন্বিত একটি অ্যারে ফেরত দেয়৷

সূত্রের পরবর্তী অংশ হল MATCH ফাংশন:

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

ম্যাচ ফাংশন INDEX ফাংশন দ্বারা প্রত্যাবর্তিত অ্যারের মধ্যে প্রথম TRUE মানের অবস্থান প্রদান করে। যেহেতু তালিকার প্রথম 8টি এন্ট্রি ফাঁকা নয়, অ্যারের প্রথম 8টি মান FALSE হবে এবং নবম মানটি হবে TRUE (যেহেতু 9 ^th রেঞ্জের সারি খালি)।

সুতরাং MATCH ফাংশন 9 এর মান প্রদান করবে . এই ক্ষেত্রে, যাইহোক, আমরা সত্যিই জানতে চাই যে তালিকায় কতগুলি এন্ট্রি রয়েছে, তাই সূত্রটি MATCH মান থেকে 1 বিয়োগ করে (যা শেষ এন্ট্রির অবস্থান দেয়)। সুতরাং শেষ পর্যন্ত, MATCH(TRUE,উপরের,0)-1 8 এর মান প্রদান করে .

সূত্রের পরবর্তী অংশটি হল IFERROR ফাংশন:

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

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

এর কারণ হল আমরা MATCH ফাংশনকে প্রথম TRUE মান (ISBLANK ফাংশন থেকে মানের অ্যারেতে) সন্ধান করতে বলছি, কিন্তু যদি কোনো ঘর খালি না থাকে, তাহলে পুরো অ্যারেটি FALSE মান দিয়ে পূর্ণ হবে। যদি MATCH অ্যারেতে লক্ষ্য মান (TRUE) খুঁজে না পায় তবে এটি অনুসন্ধান করছে, এটি একটি ত্রুটি প্রদান করে৷

সুতরাং, যদি সম্পূর্ণ তালিকাটি পূর্ণ হয় (এবং তাই, MATCH একটি ত্রুটি প্রদান করে), IFERROR ফাংশন পরিবর্তে 20 এর মান প্রদান করবে (তালিকায় 20টি এন্ট্রি থাকতে হবে তা জেনে)।

অবশেষে, OFFSET(Fruitsheading,1,0, উপরের,1) আমরা আসলে যে পরিসরটি খুঁজছি তা ফিরিয়ে দেয়:  Fruitsheading ঘর থেকে শুরু করুন, 1 সারি এবং 0টির বেশি কলামের নিচে যান, তারপর একটি এলাকা নির্বাচন করুন যেটি তালিকায় এন্ট্রি থাকলে (এবং 1 কলাম প্রশস্ত) যতই সারি দীর্ঘ হোক। সুতরাং পুরো সূত্রটি একসাথে সেই পরিসরটি ফিরিয়ে দেবে যাতে শুধুমাত্র প্রকৃত এন্ট্রি রয়েছে (প্রথম খালি ঘরে নিচে)।

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

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

তালিকা ব্লকে সারির সংখ্যা নির্দিষ্ট করার বিকল্প হিসাবে, তালিকা ব্লকটিকে তার নিজস্ব পরিসরের নাম বরাদ্দ করা যেতে পারে, যা পরে একটি পরিবর্তিত সূত্রে ব্যবহার করা যেতে পারে। উদাহরণ ফাইলে, একটি দ্বিতীয় তালিকা (নাম) এই পদ্ধতি ব্যবহার করে। এখানে, সম্পূর্ণ তালিকা ব্লক (“NAMES” শিরোনামের নীচে, উদাহরণ ফাইলে 40টি সারি) NameBlock-এর পরিসরের নাম বরাদ্দ করা হয়েছে। . নাম তালিকা সংজ্ঞায়িত করার জন্য বিকল্প সূত্র হল:

=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) প্রতিস্থাপন করে আগের সূত্রে 20 (সারির সংখ্যা) প্রতিস্থাপন করে।

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