सभी कार्यपुस्तिकाओं में सशर्त स्वरूपण में महारत हासिल करें:बाहरी डेटा से ट्रिगर प्रारूप

विधि 1:बाहरी संदर्भ के साथ सहायक कॉलम

चरण 1:अपनी कार्यपुस्तिकाएँ तैयार करें

• “बिक्री लक्ष्य.xlsx” बनाएं और लक्ष्य डेटा दर्ज करें।
• इसे अपने डेस्कटॉप या किसी विशिष्ट फ़ोल्डर में सहेजें।
• “वास्तविक Sales.xlsx” बनाएं और वास्तविक बिक्री डेटा दर्ज करें।
• इसे उसी स्थान पर सहेजें।

चरण 2:बाहरी संदर्भों के साथ सहायक कॉलम बनाएं

• "वास्तविक Sales.xlsx" में, सहायक कॉलम जोड़ें (कॉलम G से शुरू):
• सेल G2 का चयन करें और निम्नलिखित सूत्र डालें।

=[SalesTarget.xlsx]Quarterly_Targets!B2

• कोशिकाओं H2, I2 और J2 में सूत्र को स्वतः भरने के लिए सूत्र को दाईं ओर खींचें।

• मानों को अद्यतन करने के लिए, विक्रय लक्ष्य.xlsx का चयन करें फ़ाइल.

• सेल G2:J2 चुनें।
• बाकी कोशिकाओं में सूत्र को स्वतः भरने के लिए सूत्र को नीचे खींचें।

चरण 3:सहायक कॉलम का उपयोग करके सशर्त स्वरूपण लागू करें

• सेल रेंज (B2:B6) का चयन करें।
• घर पर जाएं टैब>> सशर्त फ़ॉर्मेटिंग चुनें>> नया नियम चुनें .
• चुनें किस सेल को फ़ॉर्मेट करना है यह निर्धारित करने के लिए एक सूत्र का उपयोग करें .
• निम्न सूत्र दर्ज करें:

• फ़ॉर्मेट पर क्लिक करें>> हल्का लाल रंग भरें चुनें.
• ठीक क्लिक करें .

• निम्न सूत्र दर्ज करें:

• फ़ॉर्मेट पर क्लिक करें>> हल्का नीला भरण रंग चुनें.
• ठीक क्लिक करें .

• निम्न सूत्र दर्ज करें:

• फ़ॉर्मेट पर क्लिक करें>> हल्का हरा रंग भरें चुनें.
• ठीक क्लिक करें .

• निम्न सूत्र दर्ज करें:

• फ़ॉर्मेट पर क्लिक करें>> हल्के बैंगनी रंग का चयन करें।
• ठीक क्लिक करें .

चरण 4:हेल्पर कॉलम छुपाएं (वैकल्पिक)

• कॉलम G:J चुनें
• राइट-क्लिक करें>> Hide चुनें .

विधि 2:पावर क्वेरी समाधान का उपयोग करें

चरण 1:पावर क्वेरी के साथ बाहरी डेटा आयात करें

• “वास्तविक Sales.xlsx” कार्यपुस्तिका खोलें।
• डेटा पर जाएं टैब>> डेटा प्राप्त करें चुनें>> फ़ाइल से चुनें>> कार्यपुस्तिका से चुनें .
• “विक्रय लक्ष्य.xlsx” फ़ाइल का चयन करने के लिए ब्राउज़ करें।
• “त्रैमासिक_लक्ष्य” तालिका चुनें।
• आयात पर क्लिक करें .

• नेविगेटर में विंडो>> डेटा शीट का चयन करें।
• ट्रांसफॉर्म डेटा पर क्लिक करें .

• पावर क्वेरी संपादक में:
  • अपनी आवश्यकताओं से मेल खाने के लिए कॉलम का नाम बदलें (Target_Q1, Target_Q2, आदि)।
  • घर पर जाएं टैब>> बंद करें और लोड करें .

• • तालिका चुनें>> नई वर्कशीट चुनें .
  • ठीक क्लिक करें .

चरण 2:सशर्त स्वरूपण लागू करें

• सेल रेंज (B2:B6) का चयन करें।
• घर पर जाएं टैब>> सशर्त फ़ॉर्मेटिंग चुनें>> नया नियम चुनें .
• चुनें किस सेल को फ़ॉर्मेट करना है यह निर्धारित करने के लिए एक सूत्र का उपयोग करें .
• निम्न सूत्र दर्ज करें:

=B2 <Quarterly_Targets!$B2

• फ़ॉर्मेट पर क्लिक करें>> हल्का लाल रंग भरें चुनें.
• ठीक क्लिक करें .

• बाकी तिमाहियों के लिए और नियम जोड़ें।

=C2 <Quarterly_Targets!$C2

=D2 <Quarterly_Targets!$D2

=E2 <Quarterly_Targets!$E2

• किसी भी समय लक्ष्य बदलने पर पावर क्वेरी को रिफ्रेश करें।
  • राइट-क्लिक करें>> रीफ्रेश चुनें .
• यदि आपका डेटा बार-बार बदलता है तो आप स्वचालित रीफ्रेश शेड्यूल कर सकते हैं।
• डेटा पर जाएं टैब>> प्रश्न और कनेक्शन चुनें .
• प्रश्न पर राइट-क्लिक करें  >> गुण चुनें .

• इनप्रत्येक को ताज़ा करें>> 5 डालें मिनट.
• ठीक क्लिक करें .

विधि 3:पूर्ण स्वचालन के लिए VBA मैक्रो

• अपनी वास्तविक बिक्री कार्यपुस्तिका खोलें।
• डेवलपर के पास जाएं टैब>> विजुअल बेसिक चुनें . या Alt + F11 दबाएँ .
• प्रोजेक्ट विंडो में, अपनी कार्यपुस्तिका पर राइट-क्लिक करें,
• सम्मिलित करें चुनें>> मॉड्यूल चुनें .

• निम्नलिखित 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) के रूप में सहेजें।
• डेवलपर के पास जाएं टैब>> मैक्रोज़ चुनें .
• HighlightSalesBelowTarget चुनें>>चलाएँ पर क्लिक करें .

क्या काम नहीं करता:प्रत्यक्ष बाहरी संदर्भ और नामांकित श्रेणियाँ

• बाहरी कार्यपुस्तिका संदर्भों को निर्देशित करें (उदाहरण के लिए, =[Sales_Targets.xlsx]Quarterly_Targets!B2) को सशर्त स्वरूपण नियमों में अनुमति नहीं है। एक्सेल एक त्रुटि देगा.
• नामांकित श्रेणियां बाहरी कार्यपुस्तिका में परिभाषित को किसी अन्य कार्यपुस्तिका के सशर्त स्वरूपण में संदर्भित नहीं किया जा सकता है।
• यहां तक कि अप्रत्यक्ष या समान फ़ंक्शन का उपयोग भी इस संदर्भ में फ़ाइलों पर काम नहीं करेगा।

सिफारिशें

• अधिकांश व्यवसायों के लिए: बाहरी डेटा आयात करने के लिए पावर क्वेरी का उपयोग करें। यह मजबूत है, रिफ्रेश का समर्थन करता है, और सभी तर्कों को एक कार्यपुस्तिका के अंदर रखता है।
• तदर्थ या त्वरित जांच के लिए: यदि आपको दोनों फ़ाइलें खुली रखने में कोई आपत्ति नहीं है, तो बाहरी संदर्भों वाले सहायक स्तंभों का उपयोग करें।
• स्वचालित, चल रहे समाधानों के लिए: व्यावहारिक स्वचालन और फ़ॉर्मेटिंग के लिए VBA का उपयोग करें, विशेष रूप से बड़े डेटासेट के लिए।

निष्कर्ष