বহিরাগত ওয়ার্কবুকের মানগুলির উপর ভিত্তি করে শর্তাধীন বিন্যাস আপনাকে অন্য ওয়ার্কবুকে সঞ্চিত ডেটার উপর ভিত্তি করে একটি এক্সেল ওয়ার্কবুকের সেলগুলিকে স্বয়ংক্রিয়ভাবে ফর্ম্যাট করতে দেয়। ব্যবসায়িক পরিবেশে একাধিক ফাইল জুড়ে গতিশীল প্রতিবেদন, ড্যাশবোর্ড এবং ডেটা তুলনা তৈরি করতে এই বৈশিষ্ট্যটি অপরিহার্য।
এই টিউটোরিয়ালে, আমরা দেখাব কিভাবে বহিরাগত ওয়ার্কবুকের মানের উপর ভিত্তি করে শর্তসাপেক্ষ বিন্যাস ট্রিগার করা যায়।
ধরা যাক আপনি একটি ফাইলে প্রকৃত ত্রৈমাসিক বিক্রয় এবং অন্যটিতে ত্রৈমাসিক বিক্রয় লক্ষ্যমাত্রা ট্র্যাক করেন। প্রকৃত শীটে, আপনি বাহ্যিক ফাইল থেকে সঠিক লক্ষ্যগুলি টেনে লক্ষ্যের নীচে যে কোনো প্রকৃত বিক্রয় হাইলাইট করতে চান।
পদ্ধতি 1:বাহ্যিক রেফারেন্স সহ সাহায্যকারী কলাম
এটি সবচেয়ে নির্ভরযোগ্য পদ্ধতি যা সমস্ত এক্সেল সংস্করণে কাজ করে। আপনি একটি সহায়ক কলামে একটি বহিরাগত রেফারেন্স সহ একটি ওয়ার্কশীট সূত্র ব্যবহার করতে পারেন। সহায়ক কলামের মানগুলির উপর ভিত্তি করে শর্তসাপেক্ষ বিন্যাস প্রয়োগ করুন।
ধাপ 1:আপনার ওয়ার্কবুক প্রস্তুত করুন
প্রথমে, উপরের নমুনা ডেটা দিয়ে উভয় ওয়ার্কবুক তৈরি করুন এবং সংরক্ষণ করুন:
- “Sales Target.xlsx” তৈরি করুন এবং টার্গেট ডেটা লিখুন।
- এটি আপনার ডেস্কটপ বা একটি নির্দিষ্ট ফোল্ডারে সংরক্ষণ করুন।
- “Actual Sales.xlsx” তৈরি করুন এবং প্রকৃত বিক্রয় ডেটা লিখুন।
- এটি একই স্থানে সংরক্ষণ করুন।
ধাপ 2:এক্সটার্নাল রেফারেন্স সহ হেল্পার কলাম তৈরি করুন
- “Actual Sales.xlsx”-এ, সহায়ক কলাম যোগ করুন (কলাম G থেকে শুরু):
- সেল G2 নির্বাচন করুন এবং নিম্নলিখিত সূত্রটি সন্নিবেশ করুন।
=[SalesTarget.xlsx]Quarterly_Targets!B2
- সূত্রটি H2, I2, এবং J2 কোষে স্বয়ংক্রিয়ভাবে পূরণ করতে সূত্রটিকে ডানদিকে টেনে আনুন।
- মান আপডেট করতে, Sales Target.xlsx নির্বাচন করুন ফাইল।
- সেল G2:J2 নির্বাচন করুন।
- বাকি কক্ষে সূত্রটি স্বয়ংক্রিয়ভাবে পূরণ করতে সূত্রটিকে নিচে টেনে আনুন।
ধাপ 3:হেল্পার কলাম ব্যবহার করে শর্তসাপেক্ষ ফর্ম্যাটিং প্রয়োগ করুন
এখন শর্তসাপেক্ষ বিন্যাসের জন্য অভ্যন্তরীণ রেফারেন্স ব্যবহার করুন।
- কোষ পরিসীমা নির্বাচন করুন (B2:B6)।
- হোম এ যান ট্যাব>> শর্তাধীন বিন্যাস নির্বাচন করুন>> নতুন নিয়ম নির্বাচন করুন .
- চয়ন করুন কোন কক্ষ বিন্যাস করতে হবে তা নির্ধারণ করতে একটি সূত্র ব্যবহার করুন .
- নিম্নলিখিত সূত্র লিখুন:
- ফরম্যাট এ ক্লিক করুন>> হালকা লাল ফিল কালার নির্বাচন করুন।
- ঠিক আছে ক্লিক করুন .
আরো নিয়ম যোগ করুন:
প্রয়োজন অনুযায়ী প্রতি ত্রৈমাসিকের জন্য পুনরাবৃত্তি করুন।
কোয়ার্টার 2:
- নিম্নলিখিত সূত্র লিখুন:
- ফরম্যাট এ ক্লিক করুন>> হালকা নীল ফিল কালার নির্বাচন করুন।
- ঠিক আছে ক্লিক করুন .
কোয়ার্টার 3:
- নিম্নলিখিত সূত্র লিখুন:
- ফরম্যাট এ ক্লিক করুন>> হালকা সবুজ ফিল কালার নির্বাচন করুন।
- ঠিক আছে ক্লিক করুন .
চতুর্থাংশ:
- নিম্নলিখিত সূত্র লিখুন:
- ফরম্যাট এ ক্লিক করুন>> হালকা বেগুনি ফিল কালার নির্বাচন করুন।
- ঠিক আছে ক্লিক করুন .
পদক্ষেপ 4:হেল্পার কলাম লুকান (ঐচ্ছিক)
- জি:জে কলাম নির্বাচন করুন।
- ডান-ক্লিক করুন>> লুকান নির্বাচন করুন .
আপনার ডেটা বহিরাগত ওয়ার্কবুকের মানগুলির উপর ভিত্তি করে শর্তসাপেক্ষ বিন্যাস দেখাবে, তবে এক্সেল বাহ্যিক রেফারেন্স সীমাবদ্ধতা এড়াতে অভ্যন্তরীণ সহায়ক কলামগুলি ব্যবহার করে৷
পদ্ধতি 2:পাওয়ার কোয়েরি সমাধান ব্যবহার করুন
পাওয়ার কোয়েরি এক্সেল 365 বা এক্সেল 2016+ এর ব্যবহারকারীদের জন্য একটি শক্তিশালী সমাধান প্রদান করে।
ধাপ 1:পাওয়ার কোয়েরি সহ বাহ্যিক ডেটা আমদানি করুন
- “Actual Sales.xlsx” ওয়ার্কবুক খুলুন।
- ডেটা-এ যান ট্যাব>> ডেটা পান নির্বাচন করুন>> ফাইল থেকে নির্বাচন করুন>> ওয়ার্কবুক থেকে নির্বাচন করুন .
- "Sales Target.xlsx" ফাইলটি নির্বাচন করতে ব্রাউজ করুন৷ ৷
- "ত্রৈমাসিক_লক্ষ্য" সারণী বেছে নিন।
- আমদানি করুন ক্লিক করুন .
- নেভিগেটরে উইন্ডো>> ডেটা শীট নির্বাচন করুন।
- ক্লিক করুন ডেটা ট্রান্সফর্ম করুন .
- পাওয়ার কোয়েরি এডিটরে:
- আপনার চাহিদার সাথে মেলে কলামগুলির পুনঃনামকরণ করুন (Target_Q1, Target_Q2, ইত্যাদি)।
- হোম এ যান ট্যাব>> বন্ধ করুন এবং এতে লোড করুন৷ .
- টেবিল নির্বাচন করুন>> নতুন কার্যপত্রক নির্বাচন করুন৷ .
- ঠিক আছে ক্লিক করুন .
ধাপ 2:শর্তসাপেক্ষ বিন্যাস প্রয়োগ করুন
এখন সমাধান 1 এর মতো আমদানি করা ডেটার সাথে স্ট্যান্ডার্ড শর্তসাপেক্ষ বিন্যাস ব্যবহার করুন, তবে শুধুমাত্র অভ্যন্তরীণ ডেটা উল্লেখ করুন৷
- কোষ পরিসীমা নির্বাচন করুন (B2:B6)।
- হোম এ যান ট্যাব>> শর্তাধীন বিন্যাস নির্বাচন করুন>> নতুন নিয়ম নির্বাচন করুন .
- চয়ন করুন কোন কক্ষ বিন্যাস করতে হবে তা নির্ধারণ করতে একটি সূত্র ব্যবহার করুন .
- নিম্নলিখিত সূত্র লিখুন:
=B2 <Quarterly_Targets!$B2
- ফরম্যাট এ ক্লিক করুন>> হালকা লাল ফিল কালার নির্বাচন করুন।
- ঠিক আছে ক্লিক করুন .
- বাকি কোয়ার্টারগুলির জন্য আরও নিয়ম যোগ করুন।
কোয়ার্টার 2:
=C2 <Quarterly_Targets!$C2
কোয়ার্টার 3:
=D2 <Quarterly_Targets!$D2
চতুর্থাংশ:
=E2 <Quarterly_Targets!$E2
- পাওয়ার ক্যোয়ারী রিফ্রেশ করুন যখন লক্ষ্য পরিবর্তন হয়।
- ডান-ক্লিক করুন>> রিফ্রেশ নির্বাচন করুন .
- আপনার ডেটা ঘন ঘন পরিবর্তন হলে আপনি একটি স্বয়ংক্রিয় রিফ্রেশের সময় নির্ধারণ করতে পারেন।
- ডেটা-এ যান ট্যাব>> কোয়েরি এবং সংযোগ নির্বাচন করুন .
- কোয়েরি-এ ডান-ক্লিক করুন >> বৈশিষ্ট্য নির্বাচন করুন .
- এ প্রতিটি রিফ্রেশ করুন>> 5 সন্নিবেশ করুন মিনিট।
- ঠিক আছে ক্লিক করুন .
এই পদ্ধতিটি স্বয়ংক্রিয়ভাবে বাহ্যিক ডেটা রিফ্রেশ করে এবং রেফারেন্স সীমাবদ্ধতা এড়ায়।
পদ্ধতি 3:সম্পূর্ণ অটোমেশনের জন্য VBA ম্যাক্রো
আপনি যদি VBA এর সাথে স্বাচ্ছন্দ্যবোধ করেন তবে আপনি একটি ম্যাক্রো তৈরি করতে পারেন যা বহিরাগত ডেটার উপর ভিত্তি করে শর্তসাপেক্ষ বিন্যাস আপডেট করে। এটি প্রকৃত এবং লক্ষ্যের তুলনা করবে, স্বয়ংক্রিয়ভাবে ফর্ম্যাটিং প্রয়োগ করবে, এমনকি যদি রেফারেন্স ফাইল বন্ধ থাকে।
VBA এডিটর খুলতে:
- আপনার প্রকৃত বিক্রয় কার্যবই খুলুন।
- ডেভেলপার-এ যান ট্যাব>> ভিজ্যুয়াল বেসিক নির্বাচন করুন . অথবা Alt + F11 টিপুন .
- প্রজেক্ট উইন্ডোতে, আপনার ওয়ার্কবুকে ডান-ক্লিক করুন,
- ঢোকান বেছে নিন>> মডিউল নির্বাচন করুন .
- নিম্নলিখিত VBA কোড কপি-পেস্ট করুন।
VBA কোড:
Sub HighlightSalesBelowTarget()
Dim targetFilePath As String
targetFilePath = "C:\Users\Sales Target.xlsx" ' <--- Update this to your file path
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")
' Data rows: 2 to 6, columns: 2 (B/Q1) to 5 (E/Q4)
For i = 2 To 6 ' Rows: products
For j = 2 To 5 ' Columns: 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) ' Light red
Else
wsActual.Cells(i, j).Interior.Pattern = xlNone ' No color
End If
End If
Next j
Next i
wbTarget.Close SaveChanges:=False
MsgBox "Highlighting complete.", vbInformation
End Sub
- আপনার সেলস টার্গেট ফাইলের সম্পূর্ণ পাথ সহ ফাইল পাথ আপডেট করুন।
- ম্যাক্রো টার্গেট ওয়ার্কবুক খুলে দেয়।
- প্রতিটি পণ্য এবং প্রতি ত্রৈমাসিকে লুপ করুন।
- বিক্রয় মান লক্ষ্যমাত্রার চেয়ে কম হলে, সেলটি হালকা লালে হাইলাইট করা হয়৷
- ম্যাক্রো স্বয়ংক্রিয়ভাবে টার্গেট ওয়ার্কবুক বন্ধ করে।
সংরক্ষণ করুন এবং চালান:
- আপনার ওয়ার্কবুক একটি ম্যাক্রো-সক্ষম ফাইল (.xlsm) হিসাবে সংরক্ষণ করুন।
- ডেভেলপার-এ যান ট্যাব>> ম্যাক্রো নির্বাচন করুন .
- BelowTarget হাইলাইট সেলস নির্বাচন করুন>> চালান এ ক্লিক করুন .
আউটপুট:
কি কাজ করে না:সরাসরি বাহ্যিক রেফারেন্স এবং নামকৃত রেঞ্জ
এক্সেলের কিছু সংস্করণ সতর্কতা প্রদর্শন করে "আপনি শর্তসাপেক্ষ বিন্যাসের মানদণ্ডের জন্য অন্যান্য ওয়ার্কবুকের রেফারেন্স ব্যবহার করতে পারবেন না।"
- সরাসরি বহিরাগত ওয়ার্কবুক রেফারেন্স (যেমন, =[Sales_Targets.xlsx]quarterly_Targets!B2) শর্তসাপেক্ষ ফর্ম্যাটিং নিয়মে অনুমোদিত নয়। এক্সেল একটি ত্রুটি নিক্ষেপ করবে৷
- নামকৃত ব্যাপ্তিগুলি৷ বাহ্যিক ওয়ার্কবুকে সংজ্ঞায়িত অন্য ওয়ার্কবুকের শর্তসাপেক্ষ বিন্যাসে উল্লেখ করা যাবে না।
- এমনকি INDIRECT বা অনুরূপ ফাংশন ব্যবহার করেও এই প্রসঙ্গে ফাইলগুলি জুড়ে কাজ করবে না৷
শর্তসাপেক্ষ বিন্যাস নিয়মে বাহ্যিক মান ব্যবহার করার কোনো সরাসরি, স্থানীয় উপায় নেই।
পরামর্শ
- বেশিরভাগ ব্যবসার জন্য: বাহ্যিক ডেটা আমদানি করতে পাওয়ার কোয়েরি ব্যবহার করুন। এটি শক্তিশালী, রিফ্রেশ সমর্থন করে এবং সমস্ত যুক্তি একটি ওয়ার্কবুকের মধ্যে রাখে।
- অ্যাড-হক বা দ্রুত পরীক্ষার জন্য: বাহ্যিক রেফারেন্স সহ সহায়ক কলামগুলি ব্যবহার করুন যদি আপনি উভয় ফাইল খোলা রাখতে আপত্তি না করেন৷
- স্বয়ংক্রিয়, চলমান সমাধানের জন্য: হ্যান্ডস-অফ অটোমেশন এবং ফরম্যাটিং, বিশেষ করে বড় ডেটাসেটের জন্য VBA ব্যবহার করুন৷
উপসংহার
বাহ্যিক শর্তাধীন বিন্যাস একটি শক্তিশালী বৈশিষ্ট্য যা গতিশীল, ক্রস-ফাইল ডেটা ভিজ্যুয়ালাইজেশন সক্ষম করে। আপনি আপনার দৃশ্যকল্প এবং সুবিধার উপর ভিত্তি করে আপনার পছন্দের যে কোনো পদ্ধতি ব্যবহার করতে পারেন। সবসময় আপনার সেটআপটি পুঙ্খানুপুঙ্খভাবে পরীক্ষা করতে মনে রাখবেন এবং ভবিষ্যতের রেফারেন্স এবং দলের সদস্যদের সাথে সহযোগিতার জন্য বাহ্যিক নির্ভরতার স্পষ্ট ডকুমেন্টেশন বজায় রাখুন৷
এক্সটার্নাল ওয়ার্কবুক থেকে মানের উপর ভিত্তি করে শর্তসাপেক্ষ ফর্ম্যাটিং ট্রিগার করা এক্সেল-এ নেটিভভাবে সম্ভব নয়।
সমাধান সহ বিনামূল্যে উন্নত এক্সেল ব্যায়াম পান!