কন্ডিশনাল ফরম্যাটিং এখন মাইক্রোসফট এক্সেল অভিজ্ঞতার অপরিহার্য অংশ। এটি কোনো র্যান্ডম বা বড় ডেটা গ্রুপে নির্দিষ্ট মান চিহ্নিত করতে সাহায্য করে। আমরা শুধু নির্দিষ্ট মানই নয়, কোনো নির্দিষ্ট রেঞ্জের ভিতরে আছে কিনা, স্ট্রিং ভ্যালু, न्यুমেরিক ভ্যালু, এরর ইত্যাদিও কন্ডিশনাল ফরম্যাটিং দিয়ে চিহ্নিত করতে পারি। নাম অনুযায়ী, এটি হলো এক্সেলে একটি শর্ত সত্য কিনা তার ওপর ভিত্তি করে সেল ফরম্যাট করা। এই টিউটোরিয়ালে আমরা এক্সেলে ফর্মুলা ব্যবহার করে কন্ডিশনাল ফরম্যাটিং নিয়ে আলোচনা করব।
এক্সেলে ফর্মুলা ব্যবহার করে কন্ডিশনাল ফরম্যাটিং এর ভিডিও টিউটোরিয়াল
এই ভিডিও লেকচারে:
- আপনি শিখবেন কীভাবে ফর্মুলা-ভিত্তিক কন্ডিশনাল ফরম্যাটিং ব্যবহার করে সেল ফরম্যাট করতে হয়।
- আপনি ISTEXT ফাংশন সম্পর্কে শিখবেন।
- এবং আপনি আবার শিখবেন কীভাবে ফর্মুলায় অ্যাবসোলিউট রেফারেন্সিং ব্যবহার করতে হয়।
ডেমোстраশনে ব্যবহৃত ওয়ার্কবুক ডাউনলোড লিংক থেকে ডাউনলোড করতে পারেন।
এক্সেলে ফর্মুলা দিয়ে কন্ডিশনাল ফরম্যাটিং কীভাবে ব্যবহার করবেন
এক্সেলে ফর্মুলা দিয়ে কন্ডিশনাল ফরম্যাটিং ব্যবহার করার মূল থিম খুব সহজ। আমাদের কন্ডিশনাল ফরম্যাটিং ফিচার সিলেক্ট করতে হবে। তারপর কোনো পূর্বনির্ধারিত অপশন সিলেক্ট না করে New Rule সিলেক্ট করে সেখানে ফর্মুলা লিখতে হবে।
মনে রাখবেন যে ফর্মুলা বলতে আমরা সাধারণত কন্ডিশনাল ফরম্যাটিং বক্সে একটি শর্ত লিখি। শর্ত সত্য হলে ফরম্যাট প্রয়োগ হবে এবং মিথ্যা হলে হবে না।
এখানে এক্সেলে ফর্মুলা দিয়ে কন্ডিশনাল ফরম্যাটিং এর বিস্তারিত демонস্ট্রেশন দেওয়া হলো।
ধাপসমূহ:
- প্রথমে, আপনি যে সেল রেঞ্জকে ফর্মুলা দিয়ে ফরম্যাট করতে চান সেটি সিলেক্ট করুন।
- এখন আপনার রিবনের Home ট্যাবে যান।
- তারপর Styles গ্রুপ সেকশন থেকে Conditional Formatting সিলেক্ট করুন।
- उसके बाद ড্রপ-ডাউন মেনু থেকে New Rule সিলেক্ট করুন।
- ফলস্বরূপ, New Formatting Rule বক্স পপ আপ হবে।
- তারপর Format values where this formula is true: ফিল্ডে ফর্মুলা লিখুন।
- Format বাটনে ক্লিক করে ফরম্যাট স্টাইলও সিলেক্ট করতে পারেন।
- উপরোক্ত সকল ধাপ ও সেলের ফরম্যাটিং স্টাইল সম্পন্ন হলে এই বক্সে OK ক্লিক করুন।
উদাহরণস্বরূপ, উপরের ধাপগুলো অনুসরণ করার পর নিচের ছবিতে দেখানো ফর্মুলা লিখা হয়েছে।
স্প্রেডশিটে রেঞ্জ ছিল B3:B6। এখন সেটি এরকম দেখাবে।
এখানে আমরা শর্ত দিয়েছিলাম যে সেল ভ্যালু ৩ থেকে বড়। তাই এই সিলেকশনে যেসব জায়গায় ৩ থেকে বড় মান আছে সেখানে ফরম্যাট প্রয়োগ হয়েছে।
এক্সেলে ফর্মুলা দিয়ে কন্ডিশনাল ফরম্যাটিং এর ২১টি উদাহরণ
এখন এই সেকশনে আমরা এক্সেলে কন্ডিশনাল ফরম্যাটিং ফিচারে ফর্মুলা ব্যবহার করার বিভিন্ন উদাহরণ দেখাব। এই ফিচার ব্যবহার করার এবং একে অন্যের সাথে কম্বাইন করার সম্ভাবনা بےশি। তাই আমরা কিছু প্রধান ফর্মুলা কভার করার চেষ্টা করেছি যা আপনার ইনফরমেটিভ লাগতে পারে। আপনি যদি কোনো নির্দিষ্টটি খুঁজেন তবে উপরের টেবিল থেকে খুঁজে পেতে পারেন।
১. টেক্সট ভ্যালু ফরম্যাট করা
প্রথমে এই ডেটাসেটটি বিবেচনা করুন যাতে न्यুমেরিক এবং স্ট্রিং দুয়েটো মান আছে।
কন্ডিশনাল ফরম্যাটিং ফর্মুলার সাহায্যে আমরা এক্সেলে সহজেই স্ট্রিং ভ্যালুগুলো আলাদা করতে পারি। সেটার জন্য ISTEXT ফাংশন ব্যবহার করতে হবে। এক্সেলে এই ফাংশন কন্ডিশনাল ফরম্যাটিং-এ কীভাবে ব্যবহার করবেন তা দেখতে নিচের ধাপগুলো অনুসরণ করুন।
ধাপসমূহ:
- প্রথমে, হেডার বাদ দিয়ে ডেটাসেটের সকল সেল সিলেক্ট করুন।
- তারপর আপনার রিবনে Home ট্যাবে যান।
- उसके बाद Styles গ্রুপ সেকশন থেকে Conditional Formatting সিলেক্ট করুন।
- এরপর ড্রপ-ডাউন মেনু থেকে New Rule সিলেক্ট করুন।
- Formatting Rule বক্সে, প্রথমে Select a Rule Type এর অধীনে Use a formula to determine which cells to format অপশন সিলেক্ট করুন।
- তারপর Format values where this formula is true ফিল্ডে নিচের ফর্মুলা লিখুন।
=ISTEXT(B5)
- এরপর আপনার পছন্দের ফরম্যাট টাইপ সিলেক্ট করুন।
- শেষে OK-এ ক্লিক করুন।
আপনি লক্ষ্য করবেন এই ফর্মুলা কন্ডিশনাল ফরম্যাটিং-এ ব্যবহার করার পর এক্সেল ডেটাসেটের সকল টেক্সট ফরম্যাট করে দেবে।
২. অন্য সেলের সমান সেল হাইলাইট করা
এখন ম্যাচিং ভ্যালুর অন্য একটি উদাহরণ দেখি।
এই ডেটাসেটে ডেটার বিশাল সংগ্রহ আছে। আমরা শুধু E5 সেলের ভ্যালু যেসব সেলের সাথে মেলে সেগুলো ফরম্যাট করব।
ধাপসমূহ:
- প্রথমে, হেডার বাদ দিয়ে ডেটাসেটের সকল সেল সিলেক্ট করুন।
- তারপর আপনার রিবনে Home ট্যাবে যান।
- उसके बाद Styles গ্রুপ সেকশন থেকে Conditional Formatting সিলেক্ট করুন।
- এরপর ড্রপ-ডাউন মেনু থেকে New Rule সিলেক্ট করুন।
- Formatting Rule বক্সে, প্রথমে Select a Rule Type এর অধীনে Use a formula to determine which cells to format অপশন সিলেক্ট করুন।
- তারপর Format values where this formula is true ফিল্ডে নিচের ফর্মুলা লিখুন।
=B5=$E$5
- এরপর আপনার পছন্দের ফরম্যাট টাইপ সিলেক্ট করুন।
- শেষে OK-এ ক্লিক করুন।
এভাবে ফর্মুলার সাহায্যে এক্সেলে কন্ডিশনাল ফরম্যাটিং দিয়ে অন্য সেলের সমান সেল হাইলাইট করা যায়।
৩. অন্য সেল ভিত্তিক এক্সেলে কন্ডিশনাল ফরম্যাটিং
এখন একটু সমানতা থেকে হটে বড় বা ছোট হলে ফরম্যাটিং করব। আমরা একটি ডেটাসেটকে অন্য সেলের ভ্যালু থেকে বড় বা ছোট হলে হাইলাইট করব ফর্মুলা মেথড ব্যবহার করে এক্সেলে।
এটি নিচের ডেটাসেটে করব।
আমরা C5:C9 রেঞ্জকে E5 সেলের ভ্যালু ভিত্তিক ফরম্যাট করব।
ধাপসমূহ:
- প্রথমে, হেডার বাদ দিয়ে ডেটাসেটের সকল সেল সিলেক্ট করুন।
- তারপর আপনার রিবনে Home ট্যাবে যান।
- उसके बाद Styles গ্রুপ সেকশন থেকে Conditional Formatting সিলেক্ট করুন।
- এরপর ড্রপ-ডাউন মেনু থেকে New Rule সিলেক্ট করুন।
- Formatting Rule বক্সে, প্রথমে Select a Rule Type এর অধীনে Use a formula to determine which cells to format অপশন সিলেক্ট করুন।
- তারপর Format values where this formula is true ফিল্ডে নিচের ফর্মুলা লিখুন।
=C5>$E$5
- এরপর আপনার পছন্দের ফরম্যাট টাইপ সিলেক্ট করুন।
- শেষে OK ক্লিক করুন। এখন ডেটাসেট এমন হবে।
- সদৃশভাবে ছোট মানগুলোর জন্য একই প্রক্রিয়া পুনরাবৃত্তি করে এরকম ডেটাসেট পেতে পারেন।
এভাবে অন্য সেলের ভ্যালু ভিত্তিক ফর্মুলা দিয়ে এক্সেলে কন্ডিশনাল ফরম্যাটিং করা যায়।
৪. এক্সেলে IF ফর্মুলা দিয়ে কন্ডিশনাল ফরম্যাটিং
এখন দেখব IF ফাংশন ধারণকারী ফর্মুলা কীভাবে এক্সেলে কন্ডিশনাল ফরম্যাটিং ফর্মুলা হিসেবে ব্যবহার করা যায়। আমরা সরাসরি ফাংশনটি কন্ডিশনাল ফরম্যাটিং বক্সে ব্যবহার করতে পারি না। কিন্তু ফাংশন ব্যবহার করে একটি মান বের করে সেটি থেকে আরও সেল ফরম্যাট করতে পারি কন্ডিশনাল ফরম্যাটিং-এ এক্সেলে।
এই উদাহরণের জন্য প্রথমে একটি ডেটাসেট নিই। আমরা এই ডেটাসেটে কাজ করব।
ধাপসমূহ:
- প্রথমে E5 সেল সিলেক্ট করুন এবং নিচের ফর্মুলা লিখুন।
=IF(D5>C5,"Profit","Loss")
- তারপর কীবোর্ডে Enter চাপুন।
- এখন ফিল হ্যান্ডেল আইকনে ক্লিক করে ড্র্যাগ করুন কলামের শেষ পর্যন্ত ফর্মুলা রিপ্লিকেট করতে।
- এরপর হেডার বাদ দিয়ে ডেটাসেটের সকল সেল সিলেক্ট করুন।
- তারপর আপনার রিবনে Home ট্যাবে যান।
- उसके बाद Styles গ্রুপ সেকশন থেকে Conditional Formatting সিলেক্ট করুন।
- এরপর ড্রপ-ডাউন মেনু থেকে New Rule সিলেক্ট করুন।
- Formatting Rule বক্সে, প্রথমে Select a Rule Type এর অধীনে Use a formula to determine which cells to format অপশন সিলেক্ট করুন।
- তারপর Format values where this formula is true ফিল্ডে নিচের ফর্মুলা লিখুন।
=E5="Profit"
- এরপর আপনার পছন্দের ফরম্যাট টাইপ সিলেক্ট করুন।
- শেষে OK ক্লিক করুন।
এখন ডেটাসেট এমন হবে।
এভাবে IF ফাংশন এক্সেলে কন্ডিশনাল ফরম্যাটিং ফর্মুলার জন্য ব্যবহার করা যায়।
৫. একাধিক শর্ত দিয়ে সেল হাইলাইট করা
এখন প্রথম ডেটাসেটে ফিরে যাই।
আমরা এখন সকল সেল হাইলাইট করব যেগুলো ৫, ৬ অথবা "cat" টেক্সট ধারণ করে।
এক্সেলে কন্ডিশনাল ফরম্যাটিং ফর্মুলা বক্সে এতে করতে OR ফাংশন দরকার।
ধাপসমূহ:
- প্রথমে, হেডার বাদ দিয়ে ডেটাসেটের সকল সেল সিলেক্ট করুন।
- তারপর আপনার রিবনে Home ট্যাবে যান।
- उसके बाद Styles গ্রুপ সেকশন থেকে Conditional Formatting সিলেক্ট করুন।
- এরপর ড্রপ-ডাউন মেনু থেকে New Rule সিলেক্ট করুন।
- Formatting Rule বক্সে, প্রথমে Select a Rule Type এর অধীনে Use a formula to determine which cells to format অপশন সিলেক্ট করুন।
- তারপর Format values where this formula is true ফিল্ডে নিচের ফর্মুলা লিখুন।
=OR(B5=5,B5=6,B5="cat")
- এরপর আপনার পছন্দের ফরম্যাট টাইপ সিলেক্ট করুন।
- শেষে OK ক্লিক করুন।
এখন ডেটাসেট দেখুন।
এভাবে এক্সেলে একাধিক শর্তের জন্য OR ফাংশন কন্ডিশনাল ফরম্যাটিং ফর্মুলায় ব্যবহার করা যায়।
৬. ডুপ্লিকেট সারি হাইলাইট করা
আগে বাড়ি, আমরা এখন পুরো সারি যেখানে অন্য সারির সাথে পুরোপুরি মিলে যায় সেসব সেল হাইলাইট করব। এটা একটু ট্রিকি। এতে COUNTIF এবং CONCATENATE ফাংশন লাগবে।
ডেমোস্ট্রেশনের জন্য একটি স্যাম্পল ডেটাসেট নিই।
এখানে দেখা যাচ্ছে ৩ নম্বর এবং ৬ নম্বর সারি পুরোপুরি মিলে যাচ্ছে। এক্সেলে কলামে ডুপ্লিকেট ভ্যালু হাইলাইট করতে এই ফাংশনগুলো কীভাবে ব্যবহার করবেন তা দেখতে নিচের ধাপগুলো অনুসরণ করুন।
ধাপসমূহ:
- প্রথমে F5 সেল সিলেক্ট করুন এবং নিচের ফর্মুলা লিখুন।
=CONCATENATE(B5,C5,D5,E5)
- তারপর Enter চাপুন।
- এখন সেলটি আবার সিলেক্ট করে ফিল হ্যান্ডেল আইকনে ক্লিক করে ড্র্যাগ করুন বাকি সেলগুলোর রেফারেন্স অনুযায়ী ফর্মুলা ফিল আপ করতে।
- এটা একটু মেসি দেখাচ্ছে, এখনো উপেক্ষা করুন। এখন B5:B10 রেঞ্জ সিলেক্ট করুন।
- তারপর আপনার রিবনে Home ট্যাবে যান।
- उसके बाद Styles গ্রুপ সেকশন থেকে Conditional Formatting সিলেক্ট করুন।
- এরপর ড্রপ-ডাউন মেনু থেকে New Rule সিলেক্ট করুন।
- Formatting Rule বক্সে, প্রথমে Select a Rule Type এর অধীনে Use a formula to determine which cells to format অপশন সিলেক্ট করুন।
- তারপর Format values where this formula is true ফিল্ডে নিচের ফর্মুলা লিখুন।
=COUNTIF($F$5:$F$10,$F5)>1
- এরপর আপনার পছন্দের ফরম্যাট টাইপ সিলেক্ট করুন।
- শেষে OK ক্লিক করুন। এখন ডেটাসেট এমন হবে।
- এখন F কলামের হেডারে রাইট-ক্লিক করে কন্টেক্সট মেনু থেকে Hide সিলেক্ট করুন।
এটার পর স্প্রেডশিটের চূড়ান্ত ভিউ এরকম হবে।
৭. ফর্মুলা ধারণকারী সেল ভ্যালু হাইলাইট করা
এখন ফর্মুলা ধারণকারী একটি ডেটাসেট দেখি।
আমরা ফর্মুলা ব্যবহার করে কন্ডিশনাল ফরম্যাটিং দিয়ে এই সেলগুলো হাইলাইট করব। সেটার জন্য ISFORMULA ফাংশন ব্যবহার করতে হবে।
ধাপসমূহ:
- প্রথমে, হেডার বাদ দিয়ে ডেটাসেটের সকল সেল সিলেক্ট করুন।
- তারপর আপনার রিবনে Home ট্যাবে যান।
- उसके बाद Styles গ্রুপ সেকশন থেকে Conditional Formatting সিলেক্ট করুন।
- এরপর ড্রপ-ডাউন মেনু থেকে New Rule সিলেক্ট করুন।
- Formatting Rule বক্সে, প্রথমে Select a Rule Type এর অধীনে Use a formula to determine which cells to format অপশন সিলেক্ট করুন।
- তারপর Format values where this formula is true ফিল্ডে নিচের ফর্মুলা লিখুন।
=ISFORMULA(B5)
- এরপর আপনার পছন্দের ফরম্যাট টাইপ সিলেক্ট করুন।
- শেষে OK ক্লিক করুন।
এখন ডেটাসেট এমন হবে।
৮. নির্দিষ্ট অঞ্চল থেকে সেলস হাইলাইট করা
এই উদাহরণে, আমরা একটি ডেটাসেট থেকে নির্দিষ্ট অঞ্চলের সেলস হাইলাইট করব। নিচের ডেটাসেটটি নিই।
এখন ধরুন আমাদের Arizona অঞ্চলের সেলস হাইলাইট করতে হবে। সেটার জন্য নিচের ধাপগুলো অনুসরণ করুন।
ধাপসমূহ:
- প্রথমে, হেডার বাদ দিয়ে ডেটাসেটের সকল সেল সিলেক্ট করুন।
- তারপর আপনার রিবনে Home ট্যাবে যান।
- उसके बाद Styles গ্রুপ সেকশন থেকে Conditional Formatting সিলেক্ট করুন।
- এরপর ড্রপ-ডাউন মেনু থেকে New Rule সিলেক্ট করুন।
- Formatting Rule বক্সে, প্রথমে Select a Rule Type এর অধীনে Use a formula to determine which cells to format অপশন সিলেক্ট করুন।
- তারপর Format values where this formula is true ফিল্ডে নিচের ফর্মুলা লিখুন।
=$D5="Arizona"
- এরপর আপনার পছন্দের ফরম্যাট টাইপ সিলেক্ট করুন।
- শেষে OK ক্লিক করুন।
এই ধাপগুলো ফলস্বরূপ এক্সেল এখন Arizona-এর সেলস চিহ্নিত করবে।
৯. কলাম পার্থক্য হাইলাইট করা
আমরা সেসব সারি হাইলাইট করতে পারি যাদের المجاورة সারির সাথে কলাম ভিন্ন। উদাহরণস্বরূপ, নিচের উদাহরণটি দেখুন।
এক্সেলে ফর্মুলা দিয়ে কন্ডিশনাল ফরম্যাটিং করে সেগুলো হাইলাইট করতে নিচের ধাপগুলো অনুসরণ করুন।
ধাপসমূহ:
- প্রথমে, হেডার বাদ দিয়ে ডেটাসেটের সকল সেল সিলেক্ট করুন।
- তারপর আপনার রিবনে Home ট্যাবে যান।
- उसके बाद Styles গ্রুপ সেকশন থেকে Conditional Formatting সিলেক্ট করুন।
- এরপর ড্রপ-ডাউন মেনু থেকে New Rule সিলেক্ট করুন।
- Formatting Rule বক্সে, প্রথমে Select a Rule Type এর অধীনে Use a formula to determine which cells to format অপশন সিলেক্ট করুন।
- তারপর Format values where this formula is true ফিল্ডে নিচের ফর্মুলা লিখুন।
=$B5<>$C5
- এরপর আপনার পছন্দের ফরম্যাট টাইপ সিলেক্ট করুন।
- শেষে OK ক্লিক করুন।
এই ধাপগুলোর চূড়ান্ত ফলাফল এখানে।
১০. ফর্মুলা ব্যবহার করে মিসিং ভ্যালু হাইলাইট করা
এই সেকশনে আমরা এক্সেলে কন্ডিশনাল ফরম্যাটিং-এ ফর্মুলা ব্যবহার করে মিসিং ভ্যালু হাইলাইট করব। উদাহরণস্বরূপ নিচের ডেটাসেটটি নিই।
List 2-এ কিছু মিসিং ভ্যালু আছে যা List 1-এ ছিল। আমরা সেগুলো হাইলাইট করব। কীভাবে করবেন তা দেখতে নিচের ধাপগুলো অনুসরণ করুন।
ধাপসমূহ:
- প্রথমে, হেডার বাদ দিয়ে ডেটাসেটের সকল সেল সিলেক্ট করুন।
- তারপর আপনার রিবনে Home ট্যাবে যান।
- उसके बाद Styles গ্রুপ সেকশন থেকে Conditional Formatting সিলেক্ট করুন।
- এরপর ড্রপ-ডাউন মেনু থেকে New Rule সিলেক্ট করুন।
- Formatting Rule বক্সে, প্রথমে Select a Rule Type এর অধীনে Use a formula to determine which cells to format অপশন সিলেক্ট করুন।
- তারপর Format values where this formula is true ফিল্ডে নিচের ফর্মুলা লিখুন।
=COUNTIF($D$5:$D$10,$B5)=0
- এরপর আপনার পছন্দের ফরম্যাট টাইপ সিলেক্ট করুন।
- শেষে OK ক্লিক করুন।
এই কন্ডিশনাল ফরম্যাটিং ফর্মুলার জন্য এক্সেল সেলগুলো হাইলাইট করবে।
১১. সিম্পল সার্চ বক্স তৈরি করে সেল হাইলাইট করা
এখন একটু মজার একটি উদাহরণ চেষ্টা করি, বিভিন্ন ভেরিয়েশনের কম্বিনেশন দিয়ে। এটার ডেটাসেট এখানে।
এখানে, আমরা E4 সেলে একটি ভ্যালু দেব এবং এক্সেল প্রিয় রেঞ্জে সেটি হাইলাইট করবে, সব কন্ডিশনাল ফরম্যাটিং ফর্মুলা মেথড দিয়ে। এতে ISNUMBER এবং SEARCH ফাংশনের কম্বিনেশন লাগবে।
ধাপসমূহ:
- প্রথমে, হেডার বাদ দিয়ে ডেটাসেটের সকল সেল সিলেক্ট করুন।
- তারপর আপনার রিবনে Home ট্যাবে যান।
- उसके बाद Styles গ্রুপ সেকশন থেকে Conditional Formatting সিলেক্ট করুন।
- এরপর ড্রপ-ডাউন মেনু থেকে New Rule সিলেক্ট করুন।
- Formatting Rule বক্সে, প্রথমে Select a Rule Type এর অধীনে Use a formula to determine which cells to format অপশন সিলেক্ট করুন।
- তারপর Format values where this formula is true ফিল্ডে নিচের ফর্মুলা লিখুন।
=ISNUMBER(SEARCH($E$4,B5))
- এরপর আপনার পছন্দের ফরম্যাট টাইপ সিলেক্ট করুন।
- শেষে OK ক্লিক করুন।
এখন ডেটাসেট দেখুন। "game" শব্দ ধারণকারী টেক্সটগুলো মার্ক হবে।
যদি E4 সেলের ভ্যালু পরিবর্তন করি, উদাহরণস্বরূপ "health" তবে হাইলাইটগুলো পরিবর্তন হবে।
১২. গড় থেকে কম মানवाले সেল হাইলাইট করা
এখন আগের ডেটাসেটগুলো থেকে একটি পুনর্বিবেচনা করি।
এখানে এক্সেলে কন্ডিশনাল ফরম্যাটিং ফর্মুলার একটি বেসিক অ্যাপ্লিকেশন করব - কলাম E-এ যেসব মান গড় থেকে কম সেগুলো হাইলাইট করব। এতে AVERAGE ফাংশন দরকার।
ধাপসমূহ:
- প্রথমে, হেডার বাদ দিয়ে ডেটাসেটের সকল সেল সিলেক্ট করুন।
- তারপর আপনার রিবনে Home ট্যাবে যান।
- उसके बाद Styles গ্রুপ সেকশন থেকে Conditional Formatting সিলেক্ট করুন।
- এরপর ড্রপ-ডাউন মেনু থেকে New Rule সিলেক্ট করুন।
- Formatting Rule বক্সে, প্রথমে Select a Rule Type এর অধীনে Use a formula to determine which cells to format অপশন সিলেক্ট করুন।
- তারপর Format values where this formula is true ফিল্ডে নিচের ফর্মুলা লিখুন।
=E5<AVERAGE($E$5:$E$10)
- এরপর আপনার পছন্দের ফরম্যাট টাইপ সিলেক্ট করুন।
- শেষে OK ক্লিক করুন।
এটি ডেটাসেটে গড় থেকে কম মানগুলো হাইলাইট করবে।
১৩. গড় থেকে বেশি মানवाले সেল হাইলাইট করা
পূর্বের উদাহরণের মতো, এই সেকশনে আমরা গড় থেকে বেশি মানवाले সেল হাইলাইট করব কন্ডিশনাল ফরম্যাটিং ফর্মুলা মেথড দিয়ে এক্সেলে। এটিও AVERAGE ফাংশন দরকার। ডেটাসেট একই থাকবে।
ফর্মুলা এবং কীভাবে ব্যবহার করবেন তা দেখতে নিচের ধাপগুলো অনুসরণ করুন।
ধাপসমূহ:
- প্রথমে, হেডার বাদ দিয়ে ডেটাসেটের সকল সেল সিলেক্ট করুন।
- তারপর আপনার রিবনে Home ট্যাবে যান।
- उसके बाद Styles গ্রুপ সেকশন থেকে Conditional Formatting সিলেক্ট করুন।
- এরপর ড্রপ-ডাউন মেনু থেকে New Rule সিলেক্ট করুন।
- Formatting Rule বক্সে, প্রথমে Select a Rule Type এর অধীনে Use a formula to determine which cells to format অপশন সিলেক্ট করুন।
- তারপর Format values where this formula is true ফিল্ডে নিচের ফর্মুলা লিখুন।
=E5>AVERAGE($E$5:$E$10)
- এরপর আপনার পছন্দের ফরম্যাট টাইপ সিলেক্ট করুন।
- শেষে OK ক্লিক করুন।
এভাবে এক্সেল কলাম E-এ গড় থেকে বেশি সকল মান হাইলাইট করবে।
১৪. এক্স্যাক্ট ম্যাচ সহ নিকটতম মান বের করা
এখন ডেটা ক্লিনিং এবং অ্যানালিসিস-এ ব্যবহারিক একটি অনন্য উদাহরণ নিই। আমরা একটি র্যান্ডম ডেটা সেটে একটি সংখ্যার নিকটতম মান বের করব।
এই উদাহরণের ডেটাসেট এখানে।
ছবি থেকে বোঝা যাচ্ছে, আমরা C13 সেলের মান আশেপাশের মান বের করব। যদি ডেটাসেটে একই মান থাকে, তবে নিকটতম মান বদলে সেটি হাইলাইট হবে। এতে MIN, ABS, এবং OR ফাংশন ব্যবহার করব।
ধাপসমূহ:
- প্রথমে কিছু ক্যালকুলেশন করতে হবে। সেই ডেটা সেট থেকে এই মানের সাথে সবচেয়ে ছোট পার্থক্য বের করতে হবে। সেটার জন্য C14 সেল সিলেক্ট করুন এবং নিচের ফর্মুলা লিখুন।
=MIN(ABS(B5:D11-(C13)))
- তারপর Enter চাপুন।
- এখন হেডার বাদ দিয়ে ডেটাসেটের সকল সেল সিলেক্ট করুন।
- তারপর আপনার রিবনে Home ট্যাবে যান।
- उसके बाद Styles গ্রুপ সেকশন থেকে Conditional Formatting সিলেক্ট করুন।
- এরপর ড্রপ-ডাউন মেনু থেকে New Rule সিলেক্ট করুন।
- Formatting Rule বক্সে, প্রথমে Select a Rule Type এর অধীনে Use a formula to determine which cells to format অপশন সিলেক্ট করুন।
- তারপর Format values where this formula is true ফিল্ডে নিচের ফর্মুলা লিখুন।
=OR(B5=$C$13-$C$14,B5=$C$13+$C$14)
- এরপর আপনার পছন্দের ফরম্যাট টাইপ সিলেক্ট করুন।
- শেষে OK ক্লিক করুন।
এটি ১ মানের নিকটতম মান হাইলাইট করবে এক্সেলে কন্ডিশনাল ফরম্যাটিং ফর্মুলা দিয়ে।
১৫. শীর্ষ ৩ মান বের করা
র্যান্ডম ডেটা সেটে ফিরে যাই।
এবারের বاری শীর্ষ ৩ মান বের করার এক্সেলে কন্ডিশনাল ফরম্যাটিং ফর্মুলা দিয়ে। ফর্মুলায় LARGE ফাংশন থাকে।
ধাপসমূহ:
- প্রথমে, হেডার বাদ দিয়ে ডেটাসেটের সকল সেল সিলেক্ট করুন।
- তারপর আপনার রিবনে Home ট্যাবে যান।
- उसके बाद Styles গ্রুপ সেকশন থেকে Conditional Formatting সিলেক্ট করুন।
- এরপর ড্রপ-ডাউন মেনু থেকে New Rule সিলেক্ট করুন।
- Formatting Rule বক্সে, প্রথমে Select a Rule Type এর অধীনে Use a formula to determine which cells to format অপশন সিলেক্ট করুন।
- তারপর Format values where this formula is true ফিল্ডে নিচের ফর্মুলা লিখুন।
=B5>=LARGE($B$5:$D$11,3)
- এরপর আপনার পছন্দের ফরম্যাট টাইপ সিলেক্ট করুন।
- শেষে OK ক্লিক করুন।
দেখুন এক্সেল কন্ডিশনাল ফরম্যাটিং ফর্মুলা দিয়ে রেঞ্জ থেকে শীর্ষ ৩ মান চিহ্নিত করছে।
১৬. নিচের ৩ মান বের করা
আবার একই ডেটাসেট ব্যবহার করব। কিন্তু এইবার নিচের ৩ মান হাইলাইট করব। আগের মতো SMALL ফাংশন লাগবে ফর্মুলায়। কীভাবে করবেন তা দেখতে নিচের ধাপগুলো অনুসরণ করুন।
ধাপসমূহ:
- প্রথমে, হেডার বাদ দিয়ে ডেটাসেটের সকল সেল সিলেক্ট করুন।
- তারপর আপনার রিবনে Home ট্যাবে যান।
- उसके बाद Styles গ্রুপ সেকশন থেকে Conditional Formatting সিলেক্ট করুন।
- এরপর ড্রপ-ডাউন মেনু থেকে New Rule সিলেক্ট করুন।
- Formatting Rule বক্সে, প্রথমে Select a Rule Type এর অধীনে Use a formula to determine which cells to format অপশন সিলেক্ট করুন।
- তারপর Format values where this formula is true ফিল্ডে নিচের ফর্মুলা লিখুন।
=B5<=SMALL($B$5:$D$11,3)
- এরপর আপনার পছন্দের ফরম্যাট টাইপ সিলেক্ট করুন।
- শেষে OK ক্লিক করুন।
এবারের মতো কন্ডিশনাল ফরম্যাটিং ফর্মুলার কারনে এক্সেল নিচের ৩ মান চিহ্নিত করবে।
১৭. কালার স্কেলে টেম্পারেচার দেখানো
এই পরবর্তী উদাহরণে, ফর্মুলা এবং কন্ডিশনাল ফরম্যাটিং-এ এক্সেলে আরেকটি আকর্ষণীয় ব্যবহার চেষ্টা করি। সেটার ডেটাসেট এখানে।
আমরা দুটি কলাম নিচ্ছি কারণ E5 সেলের ইনপুট ভিত্তিক রঙ পরিবর্তন করার ফর্মুলা ব্যবহার করব। ডেটাসেটের শুরুতে একটি ব্ল্যাঙ্ক সারি আছে। আমাদের ফর্মুলা ব্যবহার করার জন্য সেটি দরকার। ফর্মুলায় IF এবং AND ফাংশন আছে। বর্তমান টেম্পারেচার ভিত্তিক এই টেম্পারেচারের রঙ স্কিম পরিবর্তন করতে নিচের ধাপগুলো অনুসরণ করুন।
ধাপসমূহ:
- প্রথমে C6 সেল সিলেক্ট করুন এবং সেখানে নিচের ফর্মুলা লিখুন।
=IF(B6=$E$5,"",IF(AND(B6<$E$5,$E$5<B5),"",B6))
- তারপর Enter চাপুন।
- এখন সেলটি আবার সিলেক্ট করে ফিল হ্যান্ডেল আইকনে ক্লিক করে ড্র্যাগ করুন কলামের বাকি সেলগুলোতে ফর্মুলা রিপ্লিকেট করতে।
- এরপর Home-এর Styles গ্রুপ থেকে Conditional Formatting-এ যান।
- তারপর মাউস হোভার করুন Color Scales-এর ওপর।
- उसके बाद আপনার পছন্দের কালার স্কেল সিলেক্ট করুন।
- ফর্মুলা ভিত্তিক বর্তমান টেম্পারেচারের জন্য কন্ডিশনাল ফরম্যাটিং-এ টেম্পারেচার স্কেল এখন সম্পূর্ণ।
- যদি E5 সেলের বর্তমান টেম্পারেচার মান পরিবর্তন করি, টেম্পারেচার স্কেলও অনুযায়ী পরিবর্তন হবে।
এভাবে ফর্মুলা এবং কন্ডিশনাল ফরম্যাটিং ব্যবহার করে এক্সেলে টেম্পারেচার কালার স্কেলে দেখানো যায়।
১৮. কন্ডিশনাল ফরম্যাটিং দিয়ে অল্টারনেট সারি হাইলাইট করা
এখন আগের র্যান্ডম নম্বরের ডেটাসেটগুলোতে ফিরে যাই।
এই ডেটাসেট দিয়ে আমরা এক্সেলে কন্ডিশনাল ফরম্যাটিং ফর্মুলা দিয়ে রঙ দিয়ে অল্টারনেট সারি হাইলাইট করব। ফর্মুলায় INT এবং MOD ফাংশন লাগবে।
ধাপগুলো নিচে দেওয়া হলো।
ধাপসমূহ:
- প্রথমে, হেডার বাদ দিয়ে ডেটাসেটের সকল সেল সিলেক্ট করুন।
- তারপর আপনার রিবনে Home ট্যাবে যান।
- उसके बाद Styles গ্রুপ সেকশন থেকে Conditional Formatting সিলেক্ট করুন।
- এরপর ড্রপ-ডাউন মেনু থেকে New Rule সিলেক্ট করুন।
- Formatting Rule বক্সে, প্রথমে Select a Rule Type এর অধীনে Use a formula to determine which cells to format অপশন সিলেক্ট করুন।
- তারপর Format values where this formula is true ফিল্ডে নিচের ফর্মুলা লিখুন।
=INT(MOD(ROW(),2))
- এরপর আপনার পছন্দের ফরম্যাট টাইপ সিলেক্ট করুন।
- শেষে OK ক্লিক করুন।
এটি এক্সেলে কন্ডিশনাল ফরম্যাটিং ফর্মুলা দিয়ে অল্টারনেট সারি হাইলাইট করবে।
১৯. এররসহ সেল হাইলাইট করা
পরবর্তীতে, আমরা এক্সেলে কন্ডিশনাল ফরম্যাটিং ফর্মুলা ব্যবহার করে এররসহ সেল হাইলাইট করব। বড় ডেটা থাকলে ম্যানুয়ালি এরর খুঁজা কঠিন হলে এটি খুব উপকারী। ডেমোস্ট্রেশনের জন্য নিচের ডেটাসেটটি দেখুন।
ডেটাসেটে দুটি এরর আছে। আমরা ISERROR ফাংশন কন্ডিশনাল ফরম্যাটিং ফর্মুলায় ব্যবহার করে সেগুলো শনাক্ত করব। নিচের ফর্মুলা এক্সেলে কন্ডিশনাল ফরম্যাটিং দিয়ে সেগুলো চিহ্নিত করতে সাহায্য করবে।
ধাপসমূহ:
- প্রথমে, হেডার বাদ দিয়ে ডেটাসেটের সকল সেল সিলেক্ট করুন।
- তারপর আপনার রিবনে Home ট্যাবে যান।
- उसके बाद Styles গ্রুপ সেকশন থেকে Conditional Formatting সিলেক্ট করুন।
- এরপর ড্রপ-ডাউন মেনু থেকে New Rule সিলেক্ট করুন।
- Formatting Rule বক্সে, প্রথমে Select a Rule Type এর অধীনে Use a formula to determine which cells to format অপশন সিলেক্ট করুন।
- তারপর Format values where this formula is true ফিল্ডে নিচের ফর্মুলা লিখুন।
=ISERROR(B5)
- এরপর আপনার পছন্দের ফরম্যাট টাইপ সিলেক্ট করুন।
- শেষে OK ক্লিক করুন।
কন্ডিশনাল ফরম্যাটিং ফর্মুলা ব্যবহার করার ফলস্বরূপ এক্সেল সকল এরর হাইলাইট করবে।
২০. কন্ডিশনাল ফরম্যাটিং দিয়ে চেকলিস্ট তৈরি করা
এখন এক্সেলে ফর্মুলা দিয়ে কন্ডিশনাল ফরম্যাটিং-এ আরেকটি মজার অ্যাপ্লিকেশন চেষ্টা করি। এইবার ডেটার পাশে একটি চেকলিস্ট বানাব। অপশনগুলো চেক করলে মূল ডেটার ফরম্যাট পরিবর্তন হবে। নিচের ডেটাসেট ব্যবহার করব।
এটি একটি স্ট্যান্ডার্ড টু-ডু লিস্ট। স্ট্যাটাস কলামে চেকবক্স চেক করলে তার পাশের টাস্কের ফরম্যাট পরিবর্তন হবে, যেমন স্ট্রাইকথ্রু। মূল ধারণা হলো চেকবক্সের বুলিয়ান ভ্যালু ব্যবহার করে কলাম B-এ ফরম্যাট করা। চেকবক্স ইনসার্ট করার জন্য Developer ট্যাব লাগবে। আপনার রিবনে যদি না থাকে তবে রিবনে Developer ট্যাব প্রদর্শন করুন এ ক্লিক করুন।
এক্সেল কন্ডিশনাল ফরম্যাটিং ফর্মুলা দিয়ে চেকলিস্ট বানানোর বিস্তারিত দেখতে নিচের ধাপগুলো অনুসরণ করুন।
ধাপসমূহ:
- প্রথমে আপনার রিবনে Developer ট্যাবে যান।
- তারপর Controls গ্রুপ সেকশন থেকে Insert সিলেক্ট করুন।
- এরপর ড্রপ-ডাউন মেনু থেকে Check box (Form Control) সিলেক্ট করুন।
- তারপর বক্সটি উপযুক্ত জায়গায় রাখুন।
- এরপর বক্সে রাইট-ক্লিক করে কন্টেক্সট মেনু থেকে Format Control সিলেক্ট করুন।
- এখন বক্সের Control ট্যাবে যান এবং C5 সেলকে এর লিংকড সেল হিসেবে সিলেক্ট করুন।
- তারপর OK ক্লিক করুন।
- এখন অল্ট টেক্সট সরিয়ে সেলের মাঝখানে রাখুন ভালো দেখানোর জন্য।
- সকল টাস্কের জন্য একই প্রক্রিয়া পুনরাবৃত্তি করুন।
- চেক করলে চেক করা অনুযায়ী সেলগুলোতে TRUE/FALSE ভ্যালু আসবে।
- তাই সেগুলো লুকাতে সাদা ফন্ট রঙ ব্যবহার করুন।
- এখন ফরম্যাট করতে চাইয়া টাস্ক রেঞ্জ সিলেক্ট করুন।
- তারপর আপনার রিবনে Home ট্যাবে যান।
- उसके बाद Styles গ্রুপ সেকশন থেকে Conditional Formatting সিলেক্ট করুন।
- এরপর ড্রপ-ডাউন মেনু থেকে New Rule সিলেক্ট করুন।
- Formatting Rule বক্সে, প্রথমে Select a Rule Type এর অধীনে Use a formula to determine which cells to format অপশন সিলেক্ট করুন।
- তারপর Format values where this formula is true ফিল্ডে নিচের ফর্মুলা লিখুন।
=C5=TRUE
- এরপর আপনার পছন্দের ফরম্যাট টাইপ সিলেক্ট করুন।
- শেষে OK ক্লিক করুন।
এখন একবার চেকবক্স চেক করলে তার পাশের টাস্ক ফরম্যাট হবে। আমরা এক্সেল কন্ডিশনাল ফরম্যাটিং ফর্মুলার সাহায্যে এতটুকু করলাম।
২১. সপ্তাহে উইকএন্ড হাইলাইট করা
এখন এক্সেলে ক্যালেন্ডার সপ্তাহে উইকএন্ড হাইলাইট করার জন্য ফর্মুলা ব্যবহার করার একটি উদাহরণ চেষ্টা করি। ফর্মুলা বানাতে OR এবং WEEKDAY ফাংশন ব্যবহার করব। ডেটাসেট হিসেবে নিচেরটি ব্যবহার করব।
এক্সেলে কন্ডিশনাল ফরম্যাটিং ফর্মুলা দিয়ে উইকএন্ড হাইলাইট করতে নিচের ধাপগুলো অনুসরণ করুন।
ধাপসমূহ:
- প্রথমে, হেডার বাদ দিয়ে ডেটাসেটের সকল সেল সিলেক্ট করুন।
- তারপর আপনার রিবনে Home ট্যাবে যান।
- उसके बाद Styles গ্রুপ সেকশন থেকে Conditional Formatting সিলেক্ট করুন।
- এরপর ড্রপ-ডাউন মেনু থেকে New Rule সিলেক্ট করুন।
- Formatting Rule বক্সে, প্রথমে Select a Rule Type এর অধীনে Use a formula to determine which cells to format অপশন সিলেক্ট করুন।
- তারপর Format values where this formula is true ফিল্ডে নিচের ফর্মুলা লিখুন।
=OR(WEEKDAY($B5)=1,WEEKDAY($B5)=7)
- এরপর আপনার পছন্দের ফরম্যাট টাইপ সিলেক্ট করুন।
- শেষে OK ক্লিক করুন।
এভাবে এক্সেলে কন্ডিশনাল ফরম্যাটিং-এ সাহায্যে ক্যালেন্ডার সপ্তাহ থেকে উইকএন্ড হাইলাইট করা যায়।
উপসংহার
এই ছিল এক্সেলে কন্ডিশনাল ফরম্যাটিং-এ ফর্মুলা ব্যবহার করার পদ্ধতি এবং বিভিন্ন উদাহরণ। আশা করি আপনি এই ফিচারের ব্যবহার ধারণা বুঝতে পেরেছেন এবং এখন যথাযথভাবে ব্যবহার করতে পারবেন। হ্যাপলি, আপনি এই গাইডটি উপকারী এবং তথ্যাবহুল পেয়েছেন। কোনো প্রশ্ন বা পরামর্শ থাকলে নিচে কমেন্টে জানান।
এরকম আরও গাইডের জন্য Exceldemy.com ভিজিট করুন।
