এক্সেলে একাধিক ওয়ার্কবুকে কন্ডিশনাল ফরম্যাটিং: এক্সটারনাল ডেটা থেকে ফরম্যাট ট্রিগার করুন

এক্সটারনাল ওয়ার্কবুকের মান অনুযায়ী কন্ডিশনাল ফরম্যাটিং আপনাকে একটি এক্সেল ওয়ার্কবুকের সেলগুলিকে অন্য ওয়ার্কবুকে সংরক্ষিত ডেটা অনুযায়ী স্বয়ংক্রিয়ভাবে ফরম্যাট করতে দেয়। এটি ব্যবসায়িক পরিবেশে একাধিক ফাইল জুড়ে ডাইনামিক রিপোর্ট, ড্যাশবোর্ড এবং ডেটা তুলনা তৈরির জন্য অত্যন্ত গুরুত্বপূর্ণ।

এই টিউটোরিয়ালে আমরা দেখাব কিভাবে এক্সটারনাল ওয়ার্কবুকের মান অনুযায়ী কন্ডিশনাল ফরম্যাটিং ট্রিগার করতে হয়।

মান लीजिए আপনি একটি ফাইলে প্রকৃত কোয়ার্টারলি বিক্রয় ট্র্যাক করেন এবং অন্য ফাইলে কোয়ার্টারলি বিক্রয় লক্ষ্য। প্রকৃত বিক্রয় শীটে, আপনি এক্সটারনাল ফাইল থেকে সঠিক লক্ষ্য টেনে আনে যেসব প্রকৃত বিক্রয় লক্ষ্যের নিচে তা হাইলাইট করতে চান।

পদ্ধতি ১: এক্সটারনাল রেফারেন্স সহ হেল্পার কলাম

এটি সবচেয়ে নির্ভরযোগ্য পদ্ধতি যা সমস্ত এক্সেল ভার্সনে কাজ করে। আপনি একটি হেল্পার কলামে এক্সটারনাল রেফারেন্স সহ ওয়ার্কশিট ফর্মুলা ব্যবহার করতে পারেন। হেল্পার কলামের মান অনুযায়ী কন্ডিশনাল ফরম্যাটিং প্রয়োগ করুন।

স্টেপ ১: আপনার ওয়ার্কবুকগুলো প্রস্তুত করুন

প্রথমে, উপরের নমুনা ডেটা দিয়ে দুটি ওয়ার্কবুক তৈরি করুন এবং সংরক্ষণ করুন:

• "Sales Target.xlsx" তৈরি করুন এবং লক্ষ্যের ডেটা লিখুন।
• এটি ডেস্কটপ বা নির্দিষ্ট ফোল্ডারে সংরক্ষণ করুন।
• "Actual Sales.xlsx" তৈরি করুন এবং প্রকৃত বিক্রয়ের ডেটা লিখুন।
• এটি একই локаশনে সংরক্ষণ করুন।

স্টেপ ২: এক্সটারনাল রেফারেন্স সহ হেল্পার কলাম তৈরি করুন

• "Actual Sales.xlsx"-এ, হেল্পার কলাম যোগ করুন (কলাম G থেকে শুরু করে):
• সেল G2 সিলেক্ট করুন এবং নিচের ফর্মুলা লিখুন।

=[SalesTarget.xlsx]Quarterly_Targets!B2 

• ফর্মুলা ড্র্যাগ করে H2, I2, এবং J2 সেলগুলিতে অটোফিল করুন।

• মান আপডেট করতে, Sales Target.xlsx ফাইলটি সিলেক্ট করুন।

• সেল G2:J2 সিলেক্ট করুন।
• ফর্মুলা নিচে ড্র্যাগ করে বাকি সেলগুলিতে অটোফিল করুন।

স্টেপ ৩: হেল্পার কলাম ব্যবহার করে কন্ডিশনাল ফরম্যাটিং প্রয়োগ করুন

এখন কন্ডিশনাল ফরম্যাটিং-এর জন্য ইন্টারনাল রেফারেন্স ব্যবহার করুন।

• সেল রেঞ্জ (B2:B6) সিলেক্ট করুন।
• Home ট্যাব >> Conditional Formatting >> New Rule সিলেক্ট করুন।
• Use a formula to determine which cells to format বেছে নিন।
• নিচের ফর্মুলা লিখুন:

• Format >> হালকা লাল ফিল কালার সিলেক্ট করুন >> OK ক্লিক করুন।

আরও নিয়ম যোগ করুন:

প্রতিটি কোয়ার্টারের জন্য প্রয়োজন অনুযায়ী পুনরাবৃত্তি করুন।

কোয়ার্টার ২:

• নিচের ফর্মুলা লিখুন:

• Format >> হালকা নীল ফিল কালার সিলেক্ট করুন >> OK ক্লিক করুন।

কোয়ার্টার ৩:

• নিচের ফর্মুলা লিখুন:

• Format >> হালকা সবুজ ফিল কালার সিলেক্ট করুন >> OK ক্লিক করুন।

কোয়ার্টার ৪:

• নিচের ফর্মুলা লিখুন:

• Format >> হালকা বেগুনি ফিল কালার সিলেক্ট করুন >> OK ক্লিক করুন।

স্টেপ ৪: হেল্পার কলাম লুকান (ঐচ্ছিক)

• কলাম G:J সিলেক্ট করুন।
• রাইট-ক্লিক >> Hide সিলেক্ট করুন।

আপনার ডেটা এক্সটারনাল ওয়ার্কবুকের মান অনুযায়ী কন্ডিশনাল ফরম্যাটিং দেখাবে, কিন্তু এক্সেল এক্সটারনাল রেফারেন্সের সীমাবদ্ধতা এড়াতে ইন্টারনাল হেল্পার কলাম ব্যবহার করে।

পদ্ধতি ২: পাওয়ার কোয়েরি সমাধান

পাওয়ার কোয়েরি এক্সেল ৩৬৫ বা এক্সেল ২০১৬+ ব্যবহারকারীদের জন্য একটি শক্তিশালী সমাধান প্রদান করে।

স্টেপ ১: পাওয়ার কোয়েরি দিয়ে এক্সটারনাল ডেটা ইম্পোর্ট করুন

• "Actual Sales.xlsx" ওয়ার্কবুক খুলুন।
• Data ট্যাব >> Get Data >> From File >> From Workbook সিলেক্ট করুন।
• "Sales Target.xlsx" ফাইলটি ব্রাউজ করে সিলেক্ট করুন।
• "Quarterly_Targets" টেবিল বেছে নিন।
• Import ক্লিক করুন।

• Navigator উইন্ডোতে >> ডেটা শীট সিলেক্ট করুন।
• Transform Data ক্লিক করুন।

• পাওয়ার কোয়েরি এডিটরে:
  • প্রয়োজন অনুযায়ী কলামের নাম পরিবর্তন করুন (Target_Q1, Target_Q2, ইত্যাদি)।
  • Home ট্যাব >> Close & Load To এ যান।

• • Table >> New worksheet সিলেক্ট করুন।
  • OK ক্লিক করুন।

স্টেপ ২: কন্ডিশনাল ফরম্যাটিং প্রয়োগ করুন

এখন সমাধান ১-এর মতো স্ট্যান্ডার্ড কন্ডিশনাল ফরম্যাটিং ব্যবহার করুন, কিন্তু শুধু ইন্টারনাল ডেটা রেফারেন্স দিয়ে।

• সেল রেঞ্জ (B2:B6) সিলেক্ট করুন।
• Home ট্যাব >> Conditional Formatting >> New Rule সিলেক্ট করুন।
• Use a formula to determine which cells to format বেছে নিন।
• নিচের ফর্মুলা লিখুন:

=B2 <Quarterly_Targets!$B2 

• Format >> হালকা লাল ফিল কালার সিলেক্ট করুন >> OK ক্লিক করুন।

• বাকি কোয়ার্টারগুলোর জন্য আরো নিয়ম যোগ করুন।

কোয়ার্টার ২:

=C2 <Quarterly_Targets!$C2 

কোয়ার্টার ৩:

=D2 <Quarterly_Targets!$D2 

কোয়ার্টার ৪:

=E2 <Quarterly_Targets!$E2 

• টার্গেট পরিবর্তন হলে পাওয়ার কোয়ারি রিফ্রেশ করুন।
  • রাইট-ক্লিক >> Refresh সিলেক্ট করুন।
• ডেটা ঘন ঘন পরিবর্তন হলে অটোমেটিক রিফ্রেশ শিডিউল করতে পারেন।
• Data ট্যাব >> Queries and Connections সিলেক্ট করুন।
• কোয়েরিতে রাইট-ক্লিক >> Properties সিলেক্ট করুন।

• Refresh every-এ >> 5 মিনিট দিন।
• OK ক্লিক করুন।

এই পদ্ধতিটি স্বয়ংক্রিয়ভাবে এক্সটারনাল ডেটা রিফ্রেশ করে এবং রেফারেন্স সীমাবদ্ধতা এড়ায়।

পদ্ধতি ৩: পূর্ণ অটোমেশনের জন্য VBA ম্যাক্রো

আপনি যদি VBA-তে সহজ হন, তবে এক্সটারনাল ডেটা অনুযায়ী কন্ডিশনাল ফরম্যাটিং আপডেট করতে একটি ম্যাক্রো তৈরি করতে পারেন। এটি অ্যাকচুয়াল এবং টার্গেট তুলনা করে, রেফারেন্স ফাইল বন্ধ থাকলেও স্বয়ংক্রিয়ভাবে ফরম্যাটিং প্রয়োগ করবে।

VBA এডিটর খোলার জন্য:

• আপনার Actual Sales ওয়ার্কবুক খুলুন।
• Developer ট্যাব >> Visual Basic সিলেক্ট করুন। অথবা Alt + F11 চাপুন।
• প্রজেক্ট উইন্ডোতে আপনার ওয়ার্কবুকে রাইট-ক্লিক করুন,
• Insert >> Module সিলেক্ট করুন।

• নিচের VBA কোড কপি-পেস্ট করুন।

VBA কোড:

Sub HighlightSalesBelowTarget() Dim targetFilePath As String targetFilePath = "C:\Users\Sales Target.xlsx" ' <--- আপনার ফাইল পাথ দিয়ে আপডেট করুন Dim wbTarget As Workbook Dim wsTarget As Worksheet Dim wsActual As Worksheet Dim i As Long, j As Long Dim salesValue As Variant, targetValue As Variant Set wsActual = ThisWorkbook.Sheets("Performance_Data") Set wbTarget = Workbooks.Open(targetFilePath, ReadOnly:=True) Set wsTarget = wbTarget.Sheets("Quarterly_Targets") ' ডেটা সারি: ২ থেকে ৬, কলাম: ২ (B/Q1) থেকে ৫ (E/Q4) For i = 2 To 6 ' সারি: পণ্য For j = 2 To 5 ' কলাম: Q1-Q4 salesValue = wsActual.Cells(i, j).Value targetValue = wsTarget.Cells(i, j).Value If IsNumeric(salesValue) And IsNumeric(targetValue) Then If salesValue < targetValue Then wsActual.Cells(i, j).Interior.Color = RGB(255, 199, 206) ' হালকা লাল Else wsActual.Cells(i, j).Interior.Pattern = xlNone ' কোনো কালার নেই End If End If Next j Next i wbTarget.Close SaveChanges:=False MsgBox "Highlighting complete.", vbInformation End Sub

• আপনার Sales Target ফাইলের ফুল পাথ দিয়ে ফাইল পাথ আপডেট করুন।
• ম্যাক্রো টার্গেট ওয়ার্কবুক খোলে।
• প্রতিটি পণ্য এবং প্রতিটি কোয়ার্টারের মাধ্যমে লুপ করে।
• যদি বিক্রয়ের মান লক্ষ্যের চেয়ে কম হয়, সেলটি হালকা লাল হাইলাইট হয়।
• ম্যাক্রো স্বয়ংক্রিয়ভাবে টার্গেট ওয়ার্কবুক বন্ধ করে।

সংরক্ষণ করুন এবং রান করুন:

• আপনার ওয়ার্কবুককে ম্যাক্রো-সক্ষম ফাইল (.xlsm) হিসেবে সংরক্ষণ করুন।
• Developer ট্যাব >> Macros সিলেক্ট করুন।
• HighlightSalesBelowTarget সিলেক্ট করুন >> Run ক্লিক করুন।

আউটপুট:

যা কাজ করে না: সরাসরি এক্সটারনাল রেফারেন্স এবং নামযুক্ত রেঞ্জ

এক্সেলের কিছু ভার্সন "You may not use references to other workbooks for Conditional Formatting criteria." সতর্কতা দেখায়।

• সরাসরি এক্সটারনাল ওয়ার্কবুক রেফারেন্স (যেমন =[Sales_Targets.xlsx]Quarterly_Targets!B2) কন্ডিশনাল ফরম্যাটিং নিয়মে অনুমোদিত নয়। এক্সেল এরর দেখাবে।
• নামযুক্ত রেঞ্জ যা এক্সটারনাল ওয়ার্কবুকে সংজ্ঞায়িত, অন্য ওয়ার্কবুকের কন্ডিশনাল ফরম্যাটিং-এ রেফারেন্স করা যায় না।
• এক্সটারনাল ফাইলে INDIRECT বা অনুরূপ ফাংশন ব্যবহার করলেও এই প্রেক্ষাপটে কাজ করে না।

কন্ডিশনাল ফরম্যাটিং নিয়মে এক্সটারনাল মান ব্যবহার করার কোনো সরাসরি, নেটিভ উপায় নেই।

সুপারিশ

• অধিকাংশ ব্যবসার জন্য: এক্সটারনাল ডেটা ইম্পোর্ট করতে পাওয়ার কোয়ারি ব্যবহার করুন। এটি শক্তিশালী, রিফ্রেশ সাপোর্ট করে, এবং সমস্ত লজিক একটি ওয়ার্কবুকের ভিতরে রাখে।
• অ্যাড-হক বা দ্রুত চেকের জন্য: যদি আপনি দুটি ফাইল খোলা রাখতে আপত্তি না করেন, তবে এক্সটারনাল রেফারেন্স সহ হেল্পার কলাম ব্যবহার করুন।
• অটোমেটেড, চলমান সমাধানের জন্য: বড় ডেটাসেটের জন্য বিশেষ করে হ্যান্ডস-অফ অটোমেশন এবং ফরম্যাটিং-এর জন্য VBA ব্যবহার করুন।

উপসংহার

এক্সটারনাল কন্ডিশনাল ফরম্যাটিং একটি শক্তিশালী বৈশিষ্ট্য যা ডাইনামিক, ক্রস-ফাইল ডেটা ভিজ্যুয়ালাইজেশন সক্ষম করে। আপনি আপনার পরিস্থিতি এবং সুবিধা অনুযায়ী উপরের যেকোনো পদ্ধতি ব্যবহার করতে পারেন। সেটআপটি ভালোভাবে পরীক্ষা করতে মনে রাখবেন এবং ভবিষ্যতে রেফারেন্স এবং টিম সদস্যদের সাথে সহযোগিতার জন্য এক্সটারনাল নির্ভরতা স্পষ্ট ডকুমেন্টেশন রাখুন।

এক্সটারনাল ওয়ার্কবুকের মান থেকে কন্ডিশনাল ফরম্যাটিং ট্রিগার করা এক্সেলে নেটিভভাবে সম্ভব নয়।

নি:শুল্ক অ্যাডভান্সড এক্সেল এক্সারসাইজ সহ সমাধান পান!