এই টিউটোরিয়ালে আমি আলোচনা করব কীভাবে এক্সেলে লিস্ট থেকে টেক্সট ধারণকারী সেলগুলো হাইলাইট করতে হয়। আমি Conditional Formatting ব্যবহার করব সেল হাইলাইট করার জন্য। علاوهে, আমি সেল হাইলাইট করার জন্য Conditional Formatting-এ এক্সেলের বিভিন্ন ফাংশন এবং ফর্মুলা ব্যবহার করব।
আপনি এই আর্টিকেল তৈরিতে ব্যবহৃত প্র্যাকটিস ওয়ার্কবুক ডাউনলোড করতে পারেন।
লিস্ট থেকে টেক্সট ধারণকারী সেল হাইলাইট করার ৭টি উপায়
১. COUNTIF ফাংশন ব্যবহার করে লিস্ট থেকে টেক্সট ওয়ালা সেল হাইলাইট করা
আপনি COUNTIF function ব্যবহার করে লিস্ট থেকে টেক্সট থাকা সেলগুলো হাইলাইট করতে পারেন। উদাহরণস্বরূপ, আমার একটি Fruit 1 লিস্ট আছে (B4:B14) যেখানে বিভিন্ন ফলের নাম রয়েছে। আরেকটি লিস্ট, Fruit 2 (D4:D9), যেখানে কিছু অন্যান্য ফলের নাম রয়েছে। এখন আমি Fruit 1 লিস্টে Fruit 2-এর ফলের নামগুলো হাইলাইট করব।
Steps:
- প্রথমে ডেটাসেট (B5:B14) সিলেক্ট করুন।
- Home > Conditional Formatting (Styles group)-এ যান।
- পরিবর্তে, Conditional Formatting ড্রপ-ডাউন থেকে New Rule-এ যান।
- সিকুয়েন্টিয়ালি, New Formatting Rule উইন্ডো দেখাবে। তারপর Rule Type: Use a formula to determine which cells to format চুনুন। নিচের ফর্মুলা Format values where this formula is true ফিল্ডে টাইপ করুন। পরে Format-এ ক্লিক করুন।
=COUNTIF($D$5:$D$9,B5)
- Fill ট্যাব থেকে হাইলাইট ক্যালার চুনুন। OK ক্লিক করুন।
- আবার OK ক্লিক করুন ডায়ালগ বক্স বন্ধ করতে।
- শেষে, আপনি দেখবেন Fruit 2 লিস্ট থেকে টেক্সট ধারণকারী সকল সেল হাইলাইট হয়েছে।
আরও পড়ুন: এক্সেলে সেল হাইলাইট করার ৫ পদ্ধতি
২. MATCH ফাংশন প্রয়োগ করে লিস্ট থেকে টেক্সট ওয়ালা সেল হাইলাইট করা
এখন আমরা MATCH function ব্যবহার করে লিস্ট থেকে টেক্সট থাকা সেলগুলো হাইলাইট করব।
Steps:
- প্রথমে ডেটাসেট (B5:C14) সিলেক্ট করুন।
- Home > Conditional Formatting > New Rule-এ যান।
- New Formatting Rule উইন্ডো দেখাবে। তারপর Rule Type: Use a formula to determine which cells to format চুনুন। Format values where this formula is true ফিল্ডে নিচের ফর্মুলা টাইপ করুন। তারপর Format বাটনে ক্লিক করুন এবং Fill ট্যাব থেকে হাইলাইট ক্যালার চুনুন।
=MATCH($B5,$E$5:$E$9,0)- OK > OK ক্লিক করুন ডায়ালগ বক্সগুলো বন্ধ করতে।
- ফলস্বরূপ, Fruit 1-এ Fruit 2 লিস্ট থেকে টেক্সট ধারণকারী সকল সেল হাইলাইট হয়েছে।
আরও পড়ুন: এক্সেলে সিলেক্টেড টেক্সট হাইলাইট করার ৮ উপায়
৩. SUM এবং COUNTIF ফাংশন কম্বিনেশন ব্যবহার করে সেল হাইলাইট করা
এবারের বاریে আমি সেল হাইলাইট করার জন্য ফাংশন কম্বিনেশন ব্যবহার করব। উদাহরণস্বরূপ, আমি SUM function এবং COUNTIF ফাংশন কম্বাইন করব সেল হাইলাইট করতে। এখানে, আমার একটি ডেটাসেট Books (B4:B12) আছে যেখানে কিছু বইয়ের নাম রয়েছে। আরেকটি লিস্ট Text (D5:D8), যেখানে কিছু একক শব্দের লিস্ট রয়েছে। তাহলে, এখন আমি Books ডেটাসেটে Text-এর শব্দগুলো হাইলাইট করব।
Steps:
- প্রথমে ডেটাসেট (B5:B12) সিলেক্ট করুন।
- তারপর Home ট্যাব > Conditional Formatting ড্রপ-ডাউন সিলেক্ট করুন।
- পরিবর্তে, Conditional Formatting ড্রপ-ডাউন থেকে New Rule সিলেক্ট করুন।
- সিকুয়েন্টিয়ালি, New Formatting Rule উইন্ডো দেখাবে। Rule Type: Use a formula to determine which cells to format চুনুন। Format values where this formula is true ফিল্ডে নিচের ফর্মুলা টাইপ করুন। তারপর Format বাটনে ক্লিক করুন এবং Fill ট্যাব থেকে হাইলাইট ক্যালার সিলেক্ট করুন।
=SUM(COUNTIF(B5,"*"&$D$5:$D$8&"*"))- OK > OK ক্লিক করুন ডায়ালগ বক্সগুলো বন্ধ করতে।
- শেষে, Books-এ Text লিস্ট থেকে টেক্সট ধারণকারী সেলগুলো হাইলাইট হয়েছে।
এখানে, COUNTIF ফাংশন রেঞ্জের মধ্যে শর্ত পূরণকারী সেলের সংখ্যা রিটার্ন করে। আর SUM ফাংশন রেঞ্জের সংখ্যাগুলো যোগ করে।
আরও পড়ুন: ভ্যালু ভিত্তিক এক্সেলে সেল হাইলাইট করার ৯ পদ্ধতি
৪. COUNT এবং SEARCH ফাংশন ব্যবহার করে লিস্ট থেকে টেক্সট ধারণকারী সেল হাইলাইট করা
Method 3-এর মতোই, এইবারও আমি ফাংশন কম্বিনেশন ব্যবহার করব সেল হাইলাইট করতে। উদাহরণস্বরূপ, আমি COUNT এবং SEARCH ফাংশন কম্বিনেশন ব্যবহার করব অন্য লিস্ট থেকে টেক্সট ধারণকারী সেল হাইলাইট করতে। এইবারও আমি দুটি ফলের নামের লিস্ট ডেটাসেট হিসেবে ব্যবহার করব।
Steps:
- প্রাথমিকতে, ডেটাসেট (B5:B14) সিলেক্ট করুন।
- Home > Conditional Formatting > New Rule-এ যান।
- New Formatting Rule ডায়ালগ পপ-আপ হবে। তারপর Rule Type: Use a formula to determine which cells to format চুনুন। Format values where this formula is true ফিল্ডে নিচের ফর্মুলা টাইপ করুন।
=COUNT(SEARCH(D$5:D$9,B5))- তারপর Format বাটনে ক্লিক করুন এবং Fill ট্যাব থেকে হাইলাইট ক্যালার চুনুন। OK > OK ক্লিক করুন ডায়ালগ বক্সগুলো বন্ধ করতে।
- ফলস্বরূপ, Fruit 1-এ Fruit 2 লিস্ট থেকে ফলের নাম ধারণকারী সেলগুলো হাইলাইট হয়েছে।
এখানে, SEARCH function বাম থেকে ডানে পড়ার সময় নির্দিষ্ট ক্যারেক্টার বা টেক্সট স্ট্রিং প্রথম যেখানে পাওয়া যায় সেখানে ক্যারেক্টার সংখ্যা কাউন্ট করে। অন্যদিকে, COUNT ফাংশন রেঞ্জের মধ্যে কতগুলো সেলে নম্বর রয়েছে সেটি কাউন্ট করে।
আরও পড়ুন: টেক্সট ভিত্তিক এক্সেলে সেল হাইলাইট করার ২ পদ্ধতি
সাদৃশ্য পাঠনা:
- এক্সেলে কলাম হাইলাইট করার ৩ পদ্ধতি
- পারসেন্টেজ ভিত্তিক এক্সেলে সেল ক্যালার ফিল করার ৬ পদ্ধতি
- এক্সেলে প্রতি ৫ সারি হাইলাইট করার ৪ পদ্ধতি
- IF স্টেটমেন্ট ব্যবহার করে এক্সেলে সেল হাইলাইট করার ৭ উপায়
- দুই এক্সেল শিট তুলনা করে পার্থক্য হাইলাইট করার ৭ উপায়
৫. সিম্পল ফর্মুলা এবং ড্রপ-ডাউন লিস্ট ব্যবহার করে লিস্ট থেকে সেল হাইলাইট করা
এবারের বاریে, আমি একটি সিম্পল ফর্মুলা এবং ড্রপ-ডাউন লিস্ট ব্যবহার করব সেল হাইলাইট করতে। এই উদ্দেশ্যে, আমি একটি ড্রপ-ডাউন লিস্ট তৈরি করব। তারপর Conditional Formatting রুলে ফর্মুলা দেব। এইবারও আমি দুটি ফলের নামের লিস্ট ডেটাসেট হিসেবে ব্যবহার করব।
Steps:
- প্রথমে Cell D5-এ কিছু ফলের নাম দিয়ে ড্রপ-লিস্ট তৈরি করুন।
- ডেটাসেট (B5:B14) সিলেক্ট করুন।
- Home > Conditional Formatting-এ যান।
- পরিবর্তে, Conditional Formatting ড্রপ-ডাউন > New Rule-এ যান।
- সিকুয়েন্টিয়ালি, New Formatting Rule ডায়ালগ দেখাবে। তারপর Rule Type: Use a formula to determine which cells to format চুনুন। Format values where this formula is true ফিল্ডে নিচের ফর্মুলা টাইপ করুন। Format বাটনে ক্লিক করুন এবং Fill ট্যাব থেকে হাইলাইট ক্যালার সিলেক্ট করুন।
=$B5=$D$5- OK > OK ক্লিক করুন ডায়ালগ বক্সগুলো বন্ধ করতে।
- ফলস্বরূপ, ড্রপ-ডাউন লিস্ট থেকে ফলের নাম ধারণকারী সেলগুলো হাইলাইট হয়েছে।
- এই মেথড ডাইনামিক্যালি কাজ করে মানে আপনি ড্রপ-ডাউন লিস্ট থেকে ফলের নাম চেঞ্জ করলে হাইলাইটেড সেলগুলোও অনুযায়ী চেঞ্জ হবে।
আরও পড়ুন: এক্সেলে টপ টু বটম হাইলাইট করার ৫ পদ্ধতি
৬. ISNUMBER এবং FIND ফাংশন ব্যবহার করে লিস্ট থেকে সেল হাইলাইট করা
এখন, আমি ফাংশন কম্বিনেশন এবং ড্রপ-ডাউন লিস্ট ব্যবহার করব সেল হাইলাইট করতে। উদাহরণস্বরূপ, আমি ISNUMBER এবং FIND ফাংশন কম্বিনেশন ব্যবহার করব অন্য লিস্ট থেকে টেক্সট ধারণকারী সেল হাইলাইট করতে। এইবারও আমি দুটি ফলের নামের লিস্ট ডেটাসেট হিসেবে ব্যবহার করব।
Steps:
- প্রথমে Cell D5-এ কিছু ফলের নাম দিয়ে ড্রপ-ডাউন লিস্ট তৈরি করুন।
- ডেটাসেট (B5:B14) সিলেক্ট করুন।
- Home > Conditional Formatting > New Rule-এ যান।
- New Formatting Rule উইন্ডো দেখাবে। Rule Type: Use a formula to determine which cells to format চুনুন। Format values where this formula is true ফিল্ডে নিচের ফর্মুলা টাইপ করুন। তারপর Format বাটনে ক্লিক করুন এবং Fill ট্যাব থেকে হাইলাইট ক্যালার চুনুন।
=ISNUMBER(FIND($D$5,B5))- ফরম্যাট ক্যালার চুনার পর OK ক্লিক করুন। আবার OK ক্লিক করুন ডায়ালগ বক্সগুলো বন্ধ করতে।
- শেষে, Books-এ Text লিস্ট থেকে টেক্সট ধারণকারী সেলগুলো হাইলাইট হয়েছে।
এখানে, FIND function অন্য একটি টেক্সট স্ট্রিং-এর ভিতর থেকে একটি টেক্সট স্ট্রিং-এর স্টার্টিং পজিশন রিটার্ন করে (Case sensitive)। পরে, ISNUMBER ফাংশন চেক করে ভ্যালুটি নম্বর কিনা, এবং TRUE বা FALSE রিটার্ন করে।
আরও পড়ুন: এক্সেলে সিলেক্টেড সেল হাইলাইট করার ৫ সহজ উপায়
৭. SUMPRODUCT, ISNUMBER, এবং SEARCH ফাংশন কম্বিনেশন ব্যবহার করে লিস্ট থেকে টেক্সট ধারণকারী সেল হাইলাইট করা
এই মেথডে, আমি SUMPRODUCT function ব্যবহার করব ISNUMBER function এবং SEARCH ফাংশন সহ। আমি Books (B4:B12)-এ বইয়ের নাম হাইলাইট করব Text (B5:B8)-এর শব্দগুলো দিয়ে।
Steps:
- প্রথমে ডেটাসেট (B5:B12) সিলেক্ট করুন।
- Home > Conditional Formatting ড্রপ-ডাউন-এ যান।
- পরিবর্তে, Conditional Formatting ড্রপ-ডাউন থেকে New Rule সিলেক্ট করুন।
- ফলস্বরূপ, New Formatting Rule ডায়ালগ বক্স দেখাবে। Rule Type: Use a formula to determine which cells to format চুনুন। Format values where this formula is true ফিল্ডে নিচের ফর্মুলা টাইপ করুন। তারপর Format বাটনে ক্লিক করুন এবং Fill ট্যাব থেকে হাইলাইট ক্যালার চুনুন।
=SUMPRODUCT(--ISNUMBER(SEARCH($D$5:$D$8,B5)))>0- OK > OK ক্লিক করুন ডায়ালগ বক্সগুলো বন্ধ করতে।
- শেষে, Books-এ Text লিস্ট থেকে টেক্সট ধারণকারী বইয়ের নামগুলো হাইলাইট হয়েছে।
🔎 ফর্মুলাটি কীভাবে কাজ করে?
➤ SEARCH($D$5:$D$8,B5)
ফর্মুলার এই অংশ রিটার্ন করে:
{#VALUE!;#VALUE!;1;5}
এখানে, SEARCH ফাংশন ভ্যালুর পজিশন রিটার্ন করে। কিন্তু ভ্যালু না পেলে এরর (#VALUE!) রিটার্ন করে।
➤ ISNUMBER(SEARCH($D$5:$D$8,B5))
ISNUMBER ফাংশন সকল এরর রেজাল্টকে FALSE এবং অন্যান্য রেজাল্টকে TRUE-এ কনভার্ট করে।
এই অংশ রিপ্লাই করে:
{FALSE;FALSE;TRUE;TRUE}
➤ –ISNUMBER(SEARCH($D$5:$D$8,B5))
এই অংশ রিটার্ন করে:
{0;0;1;1}}
ডাবল মাইনাস (—) সাইন সকল FALSE এবং TRUE ভ্যালুকেই 0 এবং 1-এ কনভার্ট করে।
➤ SUMPRODUCT(–ISNUMBER(SEARCH($D$5:$D$8,B5)))>0
এই অংশ রিপ্লাই করে:
{TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE}
শেষে, SUMPRODUCT ফাংশন পূর্বের রেজাল্টকে শূন্য (0) এর বিপরীতে টেস্ট করে এবং এভাবে Conditional Formatting রুলকে সেল হাইলাইট করতে গাইড করে।
সম্পর্কিত কন্টেন্ট: সেল ক্যালার ভিত্তিক এক্সেল ফর্মুলা (৫ উদাহরণ)
উপসংহার
উপরের আর্টিকেলে, আমি লিস্ট থেকে টেক্সট ধারণকারী সেল হাইলাইট করার বেশ কিছু পদ্ধতি বिस्तারিতভাবে আলোচনা করেছি। আশা করি এই পদ্ধতিগুলো এবং ব্যাখ্যা আপনার সমস্যাগুলো সমাধান করার জন্য পর্যাপ্ত হবে। আপনার যেকোনো কুয়েরি থাকলে জানান।
সম্পর্কিত আর্টিকেল
- এক্সেল সেল ক্যালার: যোগ, এডিট, ব্যবহার ও রিমুভ
- ফর্মুলা ব্যবহার করে এক্সেল সেলে ক্যালার ফিল করার ৫ সহজ উপায়
- এক্সেলে সারি হাইলাইট করার ৫ কুইক মেথড
- এক্সেল VBA দিয়ে ভ্যালু ভিত্তিক সেল হাইলাইট (৫ উদাহরণ)
- ভ্যালু ভিত্তিক এক্সেলে সেল ক্যালার পরিবর্তন করার VBA (৩ সহজ উদাহরণ)
