এক্সেল স্প্রেডশীটগুলি প্রায়শই ডেটা এন্ট্রিকে সহজ এবং / অথবা মানক করার জন্য সেল ড্রপডাউনগুলি অন্তর্ভুক্ত করে। এই ড্রপডাউনগুলি অনুমোদনযোগ্য এন্ট্রিগুলির একটি তালিকা নির্দিষ্ট করতে ডেটা বৈধতা বৈশিষ্ট্যটি ব্যবহার করে তৈরি করা হয়েছে
একটি সাধারণ ড্রপডাউন তালিকা সেট আপ করতে, যেখানে ডাটা প্রবেশ করা হবে সে ঘরটি নির্বাচন করুন, তারপরে ডাটা বৈধকরণ(ডেটাট্যাবে), ডেটা বৈধকরণ নির্বাচন করুন, তালিকা(অনুমতি দিন :) এর অধীনে এবং তালিকার আইটেমগুলি (কমা দ্বারা আলাদা) প্রবেশ করুন >উত্স: ক্ষেত্র (চিত্র 1 দেখুন)
এই ধরণের বেসিক ড্রপডাউনে, মঞ্জুরিপ্রাপ্ত এন্ট্রিগুলির তালিকা নির্দিষ্ট করা হয় তথ্য বৈধতা নিজেই মধ্যে; অতএব, তালিকায় পরিবর্তন করতে, ব্যবহারকারীর অবশ্যই ডেটা বৈধতা খুলতে হবে এবং সম্পাদনা করতে হবে। এটি অনভিজ্ঞ অভিজ্ঞ ব্যবহারকারীদের জন্য বা পছন্দের তালিকার তালিকা দীর্ঘস্থায়ী হতে পারে এমন ক্ষেত্রে সমস্যা হতে পারে
অন্য বিকল্পটি হল স্প্রেডশিটের মধ্যে রেঞ্জের নামকরণ করা হয়েছে এ তালিকাটি রাখা এবং তারপরে নির্দিষ্ট করা উত্স: ডেটা বৈধকরণের ক্ষেত্রের (চিত্র 2 তে দেখানো হয়েছে) তে এই পরিসীমাটির নাম (সমান চিহ্ন সহ প্রফেসড)
এই দ্বিতীয় পদ্ধতিটি তালিকায় থাকা পছন্দগুলি সম্পাদনা করা সহজ করে তবে আইটেমগুলি যুক্ত করা বা অপসারণ করা সমস্যাযুক্ত হতে পারে। যেহেতু নামকৃত পরিসীমা (ফলমূলগুলি আমাদের উদাহরণস্বরূপ) কোষের একটি নির্দিষ্ট পরিসীমা ($ এইচ $ 3: $ এইচ $ 10 হিসাবে দেখানো হয়েছে) বোঝায়, যদি এইচ 11 বা নীচে ঘরগুলিতে আরও পছন্দ যুক্ত করা হয় তবে তারা ড্রপডাউনটিতে প্রদর্শিত হবে না (যেহেতু সেগুলি সেলগুলি ফ্রুটচাইসস রেঞ্জের অংশ নয়)
একইভাবে, উদাহরণস্বরূপ, পিয়ারস এবং স্ট্রবেরি এন্ট্রিগুলি মুছে ফেলা হয়, তবে তারা আর ড্রপডাউনটিতে উপস্থিত হবে না, তবে এর পরিবর্তে ড্রপডাউন দুটি অন্তর্ভুক্ত করবে ড্রপডাউন থেকে "খালি" বিকল্পগুলি এখনও ফাঁকা ঘর H9 এবং H10 সহ পুরো ফলেরচাইসিস রেঞ্জের উল্লেখ করে
এই কারণগুলির জন্য, যখন নামকরণের সীমার জন্য সাধারণ উত্সযুক্ত তালিকাটি উত্স হিসাবে উত্স হিসাবে ব্যবহৃত হয় তালিকা থেকে এন্ট্রি যুক্ত করা বা মুছে ফেলা হলে আরও কম সংখ্যক কক্ষ অন্তর্ভুক্ত করার জন্য নিজেই সম্পাদনা করতে হবে
এই সমস্যার সমাধান হল গতিশীলড্রপডাউন পছন্দগুলির উত্স হিসাবে রেঞ্জের নাম। একটি গতিশীল পরিসীমা নাম হ'ল এন্ট্রি যুক্ত বা সরানো হওয়ায় ডেটা ব্লকের আকারের সাথে হুবহু মিলে গেলে (বা চুক্তি করা) স্বয়ংক্রিয়ভাবে প্রসারিত হয়। এটি করার জন্য, আপনি নাম নির্ধারিত ব্যাপ্তিটি সংজ্ঞায়িত করার জন্য, সেল ঠিকানাগুলির একটি নির্দিষ্ট পরিসরের চেয়ে একটি <<ফর্মুলাব্যবহার করেন use
কীভাবে ডায়নামিক সেটআপ করবেন এক্সেলের মধ্যে ব্যাপ্তি
একটি সাধারণ (স্থিতিশীল) পরিসীমা নাম নির্দিষ্ট কক্ষের রেঞ্জকে বোঝায় (example এইচ $ 3: $ এইচ $ 10 আমাদের উদাহরণে নীচে দেখুন):
তবে একটি গতিশীল পরিসীমা একটি সূত্র ব্যবহার করে সংজ্ঞায়িত করা হয় (নীচে দেখুন, একটি পৃথক স্প্রেডশিট যা গতিশীল পরিসীমা নাম ব্যবহার করে):
আমরা শুরু করার আগে, নিশ্চিত করুন যে আপনি আমাদের এক্সেল উদাহরণ ফাইল ডাউনলোড করেছেন (সাজানো ম্যাক্রো অক্ষম করা হয়েছে)
আসুন এই সূত্রটি বিশদভাবে পরীক্ষা করা যাক। ফলের জন্য পছন্দগুলি সরাসরি শিরোনামের (নীচেFRUITS) কক্ষের একটি ব্লকে থাকে। এই শিরোনামটির জন্য একটি নামও বরাদ্দ করা হয়েছে: ফলশ্রুতি:
গতিশীল পরিসীমা নির্ধারণের জন্য ব্যবহৃত পুরো সূত্রটি ফলের পছন্দগুলি হ'ল:
=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)
"অন্তর্নিহিত" টুকরাটি হল অফফেস (ফলমূলগুলি, 1,0,20,1)। এটি 20 টি কোষের ব্লক (ফলের ফলস হাইডের নীচে) উল্লেখ করে যেখানে পছন্দগুলি প্রবেশ করা যেতে পারে। এই অফসেট ফাংশনটি মূলত বলে: ফলস হিডিংসেল থেকে শুরু করুন, 1 টি সারি এবং 0 টি কলামের নীচে যান, তারপরে এমন একটি অঞ্চল নির্বাচন করুন যা 20 টি সারি দীর্ঘ এবং 1 কলাম প্রশস্ত। সুতরাং এটি আমাদের 20-সারি ব্লক দেয় যেখানে ফলের পছন্দগুলি প্রবেশ করানো হয়
সূত্রটির পরবর্তী অংশটি হল ইসব্ল্যাঙ্কফাংশন:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(the above),0,0),0)-1,20),1)
এখানে, অফসেট ফাংশনটি (উপরে বর্ণিত) প্রতিস্থাপন করা হয়েছে "উপরের" সাথে (জিনিসগুলি পড়তে সহজ করার জন্য) with তবে আইএসব্ল্যাঙ্ক ফাংশন 20-সারির পরিসীমাগুলিতে অপারেটিং করছে যেগুলি অফসেট ফাংশনটি সংজ্ঞায়িত করে
সূত্রটির পরবর্তী অংশটি আইএনডেক্স ফাংশন:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,INDEX(the above,0,0),0)-1,20),1)
আবারও, "উপরের "টি উপরে বর্ণিত আইএসব্ল্যাঙ্ক এবং অফসেট ফাংশনকে বোঝায়। আইএনডেক্স ফাংশন আইএসব্ল্যাঙ্ক ফাংশন দ্বারা নির্মিত 20 টি সত্য / মিথ্যা মানযুক্ত একটি অ্যারে প্রদান করে
আইএনডিএক্সসাধারণত একটি নির্দিষ্ট মান (বা মানের সীমা) বাছাই করতে ব্যবহৃত হয় of নির্দিষ্ট সারি এবং কলাম নির্দিষ্ট করে (সেই ব্লকের মধ্যে) ডেটা ব্লক। তবে সারি এবং কলামের ইনপুটগুলি শূন্যে সেট করা (যেমন এখানে হয়) আইএনডিএক্সকে ডেটার পুরো ব্লক সহ একটি অ্যারে ফেরত দেয়
সূত্রটির পরবর্তী অংশটি ম্যাচ ফাংশন:
=OFFSET(FruitsHeading,1,0,IFERROR(MATCH(TRUE,the above,0)-1,20),1)
ম্যাচফাংশনটি INDEX ফাংশন দ্বারা ফিরে আসা অ্যারের মধ্যে প্রথম TRU মানের অবস্থান প্রদান করে। যেহেতু তালিকার প্রথম 8 টি এন্ট্রি ফাঁকা নয়, অ্যারেতে প্রথম 8 টি মান মিথ্যা হবে, এবং নবম মানটি সত্য হবে (যেহেতু 9 তমসারিটি ফাঁকা রয়েছে)। ?
সুতরাং ম্যাচ ফাংশনটি 9এর মান প্রদান করবে। এই ক্ষেত্রে, তবে, আমরা তালিকায় আসলে কতগুলি এন্ট্রি রয়েছে তা জানতে চাই, সুতরাং সূত্রটি ম্যাচের মান থেকে 1 টি বিয়োগ করে (যা শেষ প্রবেশের অবস্থান দেয়)। সুতরাং শেষ পর্যন্ত, ম্যাচ (সত্য, উপরোক্ত, 0) -1 <<<<<<<
সূত্রের পরবর্তী অংশটি IFERROR ফাংশন:
=OFFSET(FruitsHeading,1,0,IFERROR(the above,20),1)
IFERROR ফাংশনটি একটি বিকল্প মান প্রদান করে, যদি প্রথম মান কোনও ত্রুটিতে ফলাফল নির্দিষ্ট করে। এই ফাংশনটি অন্তর্ভুক্ত করা হয়েছে, যদি ঘরগুলির পুরো ব্লক (সমস্ত 20 টি সারি) এন্ট্রি দিয়ে পূর্ণ হয় তবে ম্যাচ ফাংশনটি একটি ত্রুটি ফিরিয়ে দেবে
কারণ আমরা ম্যাচ ফাংশনটি দেখার জন্য বলছি প্রথম সত্য মানের (ISBLANK ফাংশন থেকে মানগুলির অ্যারেতে), তবে কক্ষগুলির মধ্যে যদি কোনও শূন্য না থাকে তবে পুরো অ্যারেটি FALSE মান দ্বারা পূর্ণ হবে। যদি ম্যাচটি অনুসন্ধান করে থাকা অ্যারেতে লক্ষ্য মান (সত্য) না খুঁজে পায় তবে এটি একটি ত্রুটি দেয় returns
সুতরাং, যদি পুরো তালিকাটি পূর্ণ থাকে (এবং সুতরাং ম্যাচটি একটি ত্রুটি ফেরায়), তবে আইফেরআরআর ফাংশনটি হবে পরিবর্তে 20 এর মানটি ফেরান (তালিকায় 20 টি প্রবেশিকা অবশ্যই থাকতে হবে তা জেনে)
পরিশেষে,অফসেট (ফলের ফলস, ১,০, উপরের, ১)ফিরিয়ে দেয় আমরা যে সীমাটি অনুসন্ধান করছি সেগুলি: ফলসহাইডিং সেল থেকে শুরু করুন, 1 টি সারি এবং 0 টি কলামের নীচে যান, তারপরে এমন একটি অঞ্চল নির্বাচন করুন যা তালিকায় প্রবেশের (এবং 1 কলাম প্রশস্ত) প্রস্থ রয়েছে ততই বহু সারি দীর্ঘ। সুতরাং পুরো সূত্রটি একসাথে এমন পরিসীমা ফিরিয়ে দেবে যেখানে কেবল আসল প্রবেশিকা রয়েছে (প্রথম শূন্য ঘরের নিচে)
এই সূত্রটি ড্রপডাউনর উত্স হিসাবে পরিসীমা নির্ধারণ করার জন্য আপনি মুক্তভাবে সম্পাদনা করতে পারবেন তালিকা (এন্ট্রি যোগ বা অপসারণ, যতক্ষণ না বাকি প্রবেশাগুলি শীর্ষ কক্ষে শুরু হয় এবং স্বচ্ছল থাকে) এবং ড্রপডাউন সর্বদা বর্তমান তালিকা প্রতিফলিত করবে (চিত্র 6 দেখুন))
এখানে ব্যবহৃত উদাহরণ ফাইল (গতিশীল তালিকাগুলি) অন্তর্ভুক্ত রয়েছে এবং এই ওয়েবসাইট থেকে ডাউনলোডযোগ্য। ম্যাক্রোগুলি কাজ করে না, কারণ ওয়ার্ডপ্রেস তাদের ম্যাক্রো সহ এক্সেল বই পছন্দ করে না
তালিকা ব্লকের সারিগুলির সংখ্যা নির্দিষ্ট করার বিকল্প হিসাবে, তালিকাটি ব্লকটি নির্ধারিত হতে পারে নিজস্ব ব্যাপ্তির নাম, যা পরে পরিবর্তিত সূত্রে ব্যবহার করা যেতে পারে। উদাহরণস্বরূপ ফাইলে, একটি দ্বিতীয় তালিকা (নাম) এই পদ্ধতিটি ব্যবহার করে। এখানে, পুরো তালিকা ব্লকটি ("ফাইলের" নামস "শিরোনামের নীচে, উদাহরণে 40 সারি) নামব্লকএর ব্যাপ্তি নাম নির্ধারিত হয়েছে। নাম তালিকাটি সংজ্ঞায়নের বিকল্প সূত্রটি হ'ল:
=OFFSET(NamesHeading,1,0,IFERROR(MATCH(TRUE,INDEX(ISBLANK(NamesBlock),0,0),0)-1,ROWS(NamesBlock)),1)
যেখানে নামব্লকঅফসেট (ফলের ফলস, 1,0,20,1) এবং প্রতিস্থাপন করে >ROWS (নামব্লক)পূর্ববর্তী সূত্রে 20 টি (সারি সংখ্যা) প্রতিস্থাপন করে
সুতরাং, ড্রপডাউন তালিকার জন্য যা সহজেই সম্পাদনা করা যায় (অনভিজ্ঞ হতে পারে এমন অন্যান্য ব্যবহারকারীদের দ্বারাও), গতিশীল পরিসীমা নাম ব্যবহার করে দেখুন! এবং মনে রাখবেন যে, যদিও এই নিবন্ধটি ড্রপডাউন তালিকার উপরে ফোকাস করা হয়েছে, গতিশীল পরিসরের নামগুলি আপনার পরিসর বা আকারের আকারের পরিবর্তনের জন্য তালিকা উল্লেখ করতে যে কোনও জায়গায় ব্যবহার করা যেতে পারে। উপভোগ করুন!?
<স্প্যান ক্লাস = "এট_ ব্লুম_বটম_ট্রিগার">