এই আর্টিকেলে এক্সেলে ডেটা ভ্যালিডেশন লিস্ট থেকে ব্ল্যাঙ্ক সরানোর কিছু মূল্যবান পদ্ধতি সংক্ষেপে বর্ণনা করা হয়েছে। যখন এক্সেল চার্টের একটি লিস্টে ব্ল্যাঙ্ক থাকে, তবে সেটি ডেটা ভ্যালিডেশন লিস্ট-এও থাকে যা অনিচ্ছাকর।
তাই আমি নিচের ডেটাসেটের উপর কাজ করবো দেখাবো কিভাবে আপনি এক্সেলে ডেটা ভ্যালিডেশন লিস্ট থেকে ব্ল্যাঙ্ক সরাতে পারেন।
ব্ল্যাঙ্ক সেলসহ ডেটা ভ্যালিডেশন লিস্ট তৈরির সমস্যা
আমি দেখাচ্ছি যদি আমরা ব্ল্যাঙ্ক সেলসহ ড্রপ-ডাউন লিস্ট তৈরি করি তবে কী হয়। প্রথমে আমাদের ড্রপ-ডাউন লিস্ট তৈরি করতে হবে।
ধাপসমূহ:
- সেল C5 নির্বাচন করুন।
- এরপর Data >> Data Tools >> Data Validation নির্বাচন করুন।
- একটি ডায়লগ বক্স দেখাবে। Allow বার থেকে List নির্বাচন করুন (নিচের ছবিতে দেখানো হয়েছে)।
- এখন চিহ্নিত আইকন-এ ক্লিক করুন।
- এরপর, সেল B5 থেকে B14 পর্যন্ত নির্বাচন করুন এবং চিহ্নিত আইকন-এ ক্লিক করুন।
- তারপর, শুধু OK-এ ক্লিক করুন।
এভাবে আমরা আমাদের ড্রপ-ডাউন লিস্ট তৈরি করলাম।
এখানে, আপনি লক্ষ্য করবেন ড্রপ-ডাউন লিস্টটি ব্ল্যাঙ্ক সেলসহ তৈরি হয়েছে। এখন আমি বর্ণনা করবো কিভাবে আপনি এই ব্ল্যাঙ্ক সেলগুলোকে ড্রপ-ডাউন লিস্ট থেকে বাদ দিতে পারেন।
এক্সেলে ডেটা ভ্যালিডেশন লিস্ট থেকে ব্ল্যাঙ্ক সরানোর ৫ উপায়
১. OFFSET ফাংশন ব্যবহার করে ডেটা ভ্যালিডেশন লিস্ট থেকে ব্ল্যাঙ্ক সরান
এই পদ্ধতিতে আপনি আপনার ড্রপ-ডাউন লিস্ট-এ কোনো ব্ল্যাঙ্ক ছাড়াই আরও জায়গা তৈরি করতে পারেন। প্রথমে আপনার ডেটা থেকে ব্ল্যাঙ্কগুলো ফিল্টার আউট করুন। প্রক্রিয়াটি আলোচনা করা যাক।
আমাদের ডেটাসেটে কিছু পরিবর্তন আনতে হবে।
- যে কলাম-টি ড্রপ-ডাউন লিস্ট-এর জন্য ব্যবহৃত হচ্ছে তার আগে একটি নতুন কলাম যোগ করুন। আমরা নতুন কলাম-টিকে এবং ড্রপ-ডাউন লিস্ট-এর জন্য ব্যবহৃত কলাম-কে যথাক্রমে Name List with Blanks এবং List without Blanks নামে নামকরণ করেছি। (ড্রপ-ডাউন লিস্ট তৈরির জন্য Section 1-এ যান)।
ধাপসমূহ:
- প্রথমে, সেল C5-এ নিচের ফর্মুলা টাইপ করুন।
=FILTER(B5:B14,B5:B14<>"")
এখানে FILTER ফাংশন রেঞ্জ B5:B14-কে নেবে এবং রেঞ্জের মাঝপথে কোনো ব্ল্যাঙ্ক আছে কিনা চেক করবে। তারপর এটি লিস্ট থেকে খালি বা ব্ল্যাঙ্ক সেলগুলো ফিল্টার আউট করবে।
- এখন ENTER চাপুন। আপনি কোনো ব্ল্যাঙ্ক ছাড়া নেমের লিস্ট দেখতে পাবেন।
- এরপর, Formula Tab- থেকে Name Manager নির্বাচন করুন এবং New-এ ক্লিক করুন।
- আপনার রেঞ্জ-কে একটি নাম দিন। আমি NameNonBlanks নামটি ব্যবহার করব।
- এবং তারপর Refers to-এ নিচের ফর্মুলা লিখুন
=offset(offset!$C$5,0,0,counta(offset!$C$4:$C$16)-1,1)
এই পরিস্থিতিতে, আমরা কিছু এবং সেল চাইতে পারি যেখানে নতুন নাম যোগ করা যাবে, কিন্তু আমরা চাই না সেসব জায়গার জন্য আমাদের ড্রপ-ডাউন লিস্ট-এ কোনো ব্ল্যাঙ্ক থাকে। এখানে আমরা C12 থেকে C16 পর্যন্ত নতুন এন্ট্রি তৈরি করছি ডেটা ভ্যালিডেশন লিস্ট-এর জন্য সেই ফর্মুলা দিয়ে। মনে রাখবেন 'offset!' আমরা যে শিট-টি ব্যবহার করছি তার নাম বোঝাচ্ছে।
- এখন OK-এ ক্লিক করুন। আপনি একটি Window দেখবেন। শুধু এটি Close করুন।
- এবং তারপর সেল D5 নির্বাচন করুন এবং Data >> Data Validation List নির্বাচন করুন।
- Source Name-কে =NameNonBlanks-এ পরিবর্তন করুন।
- OK-এ ক্লিক করুন।
- সেল D5-এ ড্রপ-ডাউন লিস্ট বারটি নির্বাচন করুন। আপনি আমরা যে নামগুলো ব্যবহার করছি সেগুলো দেখতে পাবেন।
- এখন সেল C12 থেকে C16 পর্যন্ত কিছু নতুন নাম লিখুন।
- তারপর data validation list সেল D5 নির্বাচন করুন।
আপনি আপনার ড্রপ-ডাউন লিস্ট-এ নতুন নামগুলো দেখতে পাচ্ছেন। আপনি সেল C16-এর নিচে কোনো নতুন এন্ট্রি দেখতে পাবেন না কারণ সেগুলো আপনার রেঞ্জ-এ নেই।
এই পদ্ধতি অনুসরণ করে, আপনি আপনার ডেটা ভ্যালিডেশন লিস্ট-এ নতুন এন্ট্রির জন্য কিছু খালি জায়গা তৈরি করতে পারেন যাতে তাতে কোনো ব্ল্যাঙ্ক না থাকে।
আরও পড়ুন: এক্সেলে ডেটা ভ্যালিডেশনের জন্য ড্রপ-ডাউন লিস্ট তৈরি করা (৮ উপায়)
২. Go to Special কমান্ড ব্যবহার করে লিস্ট থেকে ব্ল্যাঙ্ক সরান
আমরা আমাদের ড্রপ-ডাউন লিস্ট তৈরি করেছি (Section 1), আপনি দেখতে পাচ্ছেন সেটিতে ব্ল্যাঙ্ক আছে। সেগুলো সরাতে আমরা সহজে নিচের ধাপগুলো অনুসরণ করতে পারি।
ধাপসমূহ:
- সেল B5 থেকে B14 পর্যন্ত নির্বাচন করুন এবং তারপর Home >> Find & Select >> Go To Special নির্বাচন করুন।
- এরপর, Blanks নির্বাচন করুন এবং তারপর OK-এ ক্লিক করুন।
- এই অপারেশনটি ব্ল্যাঙ্ক সেলগুলো নির্বাচন করবে।
- এখন এই ব্ল্যাঙ্ক সেল-গুলোর যেকোনো একটি নির্বাচন করুন, সেটিতে রাইট ক্লিক করুন এবং Delete নির্বাচন করুন Blanks ডিলিট করতে।
- আপনি একটি ডায়লগ বক্স দেখবেন। Shift Cells Up নির্বাচন করুন এবং OK-এ ক্লিক করুন।
- এই অপারেশনটি মূল লিস্ট-এর পাশাপাশি ড্রপ-ডাউন লিস্ট- থেকেও ব্ল্যাঙ্ক সরিয়ে দেবে।
এই পদ্ধতি অনুসরণ করে, আপনি সহজে একটি ড্রপ-ডাউন লিস্ট- থেকে ব্ল্যাঙ্ক বা খালি সেল সরাতে পারেন।
আরও পড়ুন: এক্সেলে একাধিক সিলেকশনসহ ডেটা ভ্যালিডেশন ড্রপ-ডাউন লিস্ট তৈরি করা
৩. এক্সেল FILTER ফাংশন ব্যবহার করে ডেটা ভ্যালিডেশন লিস্ট থেকে ব্ল্যাঙ্ক সরান
এই ক্ষেত্রে আমরা FILTER ফাংশন প্রয়োগ করতে পারি। আমরা Section 2- থেকে ডেটাসেট ব্যবহার করব। ড্রপ-ডাউন লিস্ট তৈরির জন্য Section 1-এ যান।
ধাপসমূহ:
- সেল C5-এ নিচের ফর্মুলা টাইপ করুন।
=FILTER(B5:B14,B5:B14<>"")
এখানে FILTER ফাংশন রেঞ্জ B5:B14-কে নেবে এবং রেঞ্জের মাঝপথে কোনো ব্ল্যাঙ্ক আছে কিনা চেক করবে। তারপর এটি লিস্ট থেকে খালি বা ব্ল্যাঙ্ক সেলগুলো ফিল্টার আউট করবে।
- ENTER কী চাপুন এবং আপনি কোনো ব্ল্যাঙ্ক ছাড়া নেমের লিস্ট দেখতে পাবেন।
- কিন্তু আপনি Drop Down List-এ যান তবে দেখবেন এখনও এতে কলাম C- থেকে ব্ল্যাঙ্ক রয়েছে।
- এই ব্ল্যাঙ্কগুলো সরাতে, Data Tab- থেকে Data Validation-এ যান।
- রেঞ্জ-এর শেষ সেলকে C11-এ পরিবর্তন করুন কারণ আপনার ফিল্টারড লিস্টের Source-এ রেঞ্জ C5 থেকে C11 পর্যন্ত রয়েছে।
- এখন OK-এ ক্লিক করুন। আপনার ড্রপ-ডাউন লিস্ট-এ আর কোনো ব্ল্যাঙ্ক সেল থাকবে না।
এটি ড্রপ-ডাউন লিস্ট- থেকে ব্ল্যাঙ্ক সরানোর একটি 꽤 দক্ষ পদ্ধতি।
আরও পড়ুন: ফিল্টারসহ এক্সেল ডেটা ভ্যালিডেশন ড্রপ-ডাউন লিস্ট (২ উদাহরণ)
সাদৃশ্য পাঠ্য:
- এক্সেল ডেটা ভ্যালিডেশনে কাস্টম VLOOKUP ফর্মুলা ব্যবহার করা
- [সamor] কপি-পেস্টের জন্য ডেটা ভ্যালিডেশন কাজ না করলে (সমাধানসহ)
- এক্সেলে টেবিল থেকে ডেটা ভ্যালিডেশন লিস্ট তৈরি করা (৩ পদ্ধতি)
- এক সেলে একাধিক ডেটা ভ্যালিডেশন প্রয়োগ করা (৩ উদাহরণ)
- এক্সেল ডেটা ভ্যালিডেশন অ্যালফানিউমেরিক অনলি (কাস্টম ফর্মুলা ব্যবহার করে)
৪. IF, COUNTIF, ROW, INDEX এবং SMALL ফাংশন একসাথে ব্যবহার করে ডেটা ভ্যালিডেশন লিস্ট থেকে ব্ল্যাঙ্ক সরান
আমরা IF, COUNTIF, ROW, INDEX এবং SMALL ফাংশনগুলোর সমন্বয় ব্যবহার করে ডেটা ভ্যালিডেশন লিস্ট- থেকে খালি সেলগুলো সরাতে পারি। এটি কিছুটা জটিল। আমরা Section 2- থেকে ডেটাসেট ব্যবহার করব। এবং ড্রপ-ডাউন লিস্ট তৈরির জন্য Section 1-এ যান।
ধাপসমূহ:
- প্রথমে, সেল C5-এ নিচের ফর্মুলা টাইপ করুন।
=IF(COUNTIF($B$5:$B$14,"?*")<ROW(B5)-4,"",INDEX(B:B,SMALL(IF(B$5:B$14<>"",ROW(B$5:B$14)),ROWS(B$5:B5))))
ফর্মুলাটির দুটি প্রধান অংশ রয়েছে। প্রথম অংশ হলো COUNTIF($B$5:$B$14,"?*")<ROW(B5)-4,"" এবং দ্বিতীয় অংশ হলো INDEX(B:B,SMALL(IF(B$5:B$14<>"",ROW(B$5:B$14)),ROWS(B$5:B5)))।
- COUNTIF ফাংশন এখানে নন-ব্ল্যাঙ্ক টেক্সট গণনা করে এবং সেজন্য আমরা কলাম C-এ ৭টি নাম পাই।
- ROW ফাংশন একটি সেলের রো নম্বর ফেরত দেয় এবং আমাদের খালি সেলটি B5- থেকে অবস্থান 5-এ আছে। আমরা 4 বিয়োগ করছি কারণ আমরা চাই এটি সেটির চেয়ে কম হবে।
- এখন ENTER চাপুন।
- নিচের সেলগুলোতে অটোফিল করতে Fill Handle ব্যবহার করুন।
- এখন আমাদের কাছে কোনো ব্ল্যাঙ্ক ছাড়া নেম লিস্ট আছে। কিন্তু আমরা যদি ডেটা ভ্যালিডেশন লিস্ট-এ ক্লিক করি, তবে আমরা এখনও ড্রপ-ডাউন লিস্ট-এ ব্ল্যাঙ্ক দেখি।
- এই ব্ল্যাঙ্কগুলো সরাতে, Data Tab- থেকে Data Validation-এ যান।
- রেঞ্জ-এর শেষ সেলকে C11-এ পরিবর্তন করুন কারণ আপনার ফিল্টারড লিস্টের Source-এ রেঞ্জ C5 থেকে C11 পর্যন্ত রয়েছে।
- এখন OK-এ ক্লিক করুন। আপনার ড্রপ-ডাউন লিস্ট-এ আর কোনো ব্ল্যাঙ্ক সেল থাকবে না।
এভাবে আপনি ব্ল্যাঙ্ক ছাড়া একটি ডেটা ভ্যালিডেশন লিস্ট তৈরি করতে পারেন।
আরও পড়ুন: এক্সেল VBA-তে ডিফল্ট ভ্যালু ডেটা ভ্যালিডেশন লিস্টে (ম্যাক্রো এবং ইউজারফর্ম)
৫. কম্বাইনড ফাংশন ব্যবহার করে ডেটা ভ্যালিডেশন লিস্ট থেকে ব্ল্যাঙ্ক সেল সরান
আমরা ADDRESS, INDIRECT, COUNTBLANK, IF এবং SMALL ফাংশনসহ নেস্টেড ফর্মুলাও ব্যবহার করতে পারি। প্রক্রিয়াটি আলোচনা করা যাক। আমরা Section 2- থেকে ডেটাসেট ব্যবহার করব। আপনি Section 1-এ যেতে পারেন ড্রপ-ডাউন লিস্ট/ডেটা ভ্যালিডেশন লিস্ট তৈরির জন্য।
ধাপসমূহ:
- প্রথমে, সেল C5-এ নিচের ফর্মুলা টাইপ করুন।
=IF(ROW()-ROW($B$5:$B$14)+1>ROWS($B$5:$B$14)-COUNTBLANK($B$5:$B$14),"", INDIRECT(ADDRESS(SMALL((IF($B$5:$B$14<>"",ROW($B$5:$B$14),ROW()+ROWS($B$5:$B$14))),ROW()-ROW($C$5:$C$14)+1),COLUMN($B$5:$B$14),4)))
এখানে, আমি খুব সহজে ব্যাখ্যা করছি কীভাবে এই ফর্মুলাটি কাজ করে। এটি রেঞ্জ B5:B14-এ যেতে থাকে এবং COUNTBLANK ফাংশনের সাহায্যে ব্ল্যাঙ্ক সেলগুলো চেক করে। তারপর এটি B5:B14 পরিসরে কোন সেলগুলো ব্ল্যাঙ্ক নন সেটিও চেক করে এবং এইভাবে এটি নন-এম্পটি সেলগুলো ফেরত দেয়।
- CTRL + SHIFT + ENTER চাপুন (কারণ এটি একটি অ্যারে ফর্মুলা) এবং আপনি সেল C5-এ আউটপুট দেখতে পাবেন নিচের মতো।
- এখন নিচের সেলগুলোতে অটোফিল করতে Fill Handle ব্যবহার করুন।
- কিন্তু আপনি Drop Down List-এ যান তবে দেখবেন এখনও এতে কলাম C- থেকে ব্ল্যাঙ্ক রয়েছে।
- এই ব্ল্যাঙ্কগুলো সরাতে, Data Tab- থেকে Data Validation-এ যান।
- রেঞ্জ-এর শেষ সেলকে C11-এ পরিবর্তন করুন কারণ আপনার ফিল্টারড লিস্টের Source-এ রেঞ্জ C5 থেকে C11 পর্যন্ত রয়েছে।
- এখন OK-এ ক্লিক করুন। আপনার ড্রপ-ডাউন লিস্ট-এ আর কোনো ব্ল্যাঙ্ক সেল থাকবে না।
এটি আর একটি উপায় जिससे আপনি ব্ল্যাঙ্ক মুক্ত একটি ড্রপ-ডাউন লিস্ট তৈরি করতে পারেন।
আরও পড়ুন: এক্সেল ডেটা ভ্যালিডেশনে IF স্টেটমেন্ট ব্যবহার করা (৬ উপায়)
অনুশীলন বিভাগ
এই বিভাগে, আমি আপনাকে ডেটাসেট দিচ্ছি যাতে আপনি নিজে এই পদ্ধতিগুলো অনুশীলন করতে পারেন।
উপসংহার
সংক্ষেপে, আমি এক্সেলে ডেটা ভ্যালিডেশন লিস্ট থেকে ব্ল্যাঙ্ক সরানোর কিছু পদ্ধতি ব্যাখ্যা করার চেষ্টা করেছি। এই পদ্ধতিগুলো কিছুটা জটিল কিন্তু আমি সেগুলো খুব সহজ ও বোঝার উপযোগীভাবে ব্যাখ্যা করার চেষ্টা করেছি। আমি আপনাদের অনুরোধ করছি এই আর্টিকেলে কমেন্ট সেকশনে কিছু ফিডব্যাক দিন, এবং যদি আপনার নিজস্ব আইডিয়া বা প্রশ্ন থাকে তবে সেগুলোও কমেন্ট বক্সে রাখুন।
সম্পর্কিত আর্টিকেলসমূহ
- এক্সেলে একাধিক কেরিয়ার জন্য কাস্টম ডেটা ভ্যালিডেশন প্রয়োগ করা (৪ উদাহরণ)
- রঙসহ এক্সেলে ডেটা ভ্যালিডেশন ব্যবহার করা (৪ উপায়)
- অন্য শিট থেকে ডেটা ভ্যালিডেশন লিস্ট ব্যবহার করা (৬ পদ্ধতি)
- এক্সেল VBA-তে অ্যারে থেকে ডেটা ভ্যালিডেশন লিস্ট তৈরি করা
- VBA-তে নেমড রেঞ্জ ব্যবহার করে ডেটা ভ্যালিডেশন লিস্ট তৈরি করা
