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

बाहरी वर्कबुक मानों के आधार पर सशर्त स्वरूपण आपको एक Excel वर्कबुक में सेल को दूसरे वर्कबुक में संग्रहीत डेटा के आधार पर स्वचालित रूप से प्रारूपित करने की अनुमति देता है। यह सुविधा व्यावसायिक वातावरण में कई फ़ाइलों में गतिशील रिपोर्ट, डैशबोर्ड और डेटा तुलना बनाने के लिए आवश्यक है।

इस ट्यूटोरियल में, हम दिखाएंगे कि बाहरी वर्कबुक मानों के आधार पर सशर्त स्वरूपण कैसे ट्रिगर करें।

मान लें कि आप एक फ़ाइल में वास्तविक तिमाही बिक्री ट्रैक करते हैं और दूसरी में तिमाही बिक्री लक्ष्य। वास्तविक शीट में, आप किसी भी वास्तविक बिक्री को लक्ष्य से नीचे हाइलाइट करना चाहते हैं, बाहरी फ़ाइल से सही लक्ष्य खींचकर।

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

यह सबसे विश्वसनीय विधि है जो सभी Excel संस्करणों में काम करती है। आप एक सहायक कॉलम में बाहरी संदर्भ के साथ एक वर्कशीट सूत्र का उपयोग कर सकते हैं। सहायक कॉलम के मूल्यों के आधार पर सशर्त स्वरूपण लागू करें।

चरण 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:

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

• प्रारूप पर क्लिक करें >> हल्का बैंगनी भरण रंग चुनें।
• ओके पर क्लिक करें।

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

• कॉलम G:J चुनें।
• राइट-क्लिक करें >> छिपाएं चुनें।

आपका डेटा बाहरी वर्कबुक मानों के आधार पर सशर्त स्वरूपण दिखाएगा, लेकिन Excel बाहरी संदर्भ सीमा से बचने के लिए आंतरिक सहायक कॉलम का उपयोग करता है।

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

Power Query Excel 365 या Excel 2016+ वाले उपयोगकर्ताओं के लिए एक मजबूत समाधान प्रदान करता है।

चरण 1: Power Query के साथ बाहरी डेटा आयात करें

• "Actual Sales.xlsx" वर्कबुक खोलें।
• डेटा टैब पर जाएं >> डेटा प्राप्त करें चुनें >> फ़ाइल से चुनें >> वर्कबुक से चुनें।
• "Sales Target.xlsx" फ़ाइल ब्राउज़ करके चुनें।
• "Quarterly_Targets" टेबल चुनें।
• आयात पर क्लिक करें।

• नेविगेटर विंडो में >> डेटा शीट चुनें।
• डेटा परिवर्तित करें पर क्लिक करें।

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

• • टेबल चुनें >> नई वर्कशीट चुनें।
  • ओके पर क्लिक करें।

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

अब समाधान 1 की तरह ही मानक सशर्त स्वरूपण का उपयोग करें, लेकिन केवल आंतरिक डेटा संदर्भित करें।

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

=B2 <Quarterly_Targets!$B2 

• प्रारूप पर क्लिक करें >> हल्का लाल भरण रंग चुनें।
• ओके पर क्लिक करें।

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

तिमाही 2:

=C2 <Quarterly_Targets!$C2 

तिमाही 3:

=D2 <Quarterly_Targets!$D2 

तिमाही 4:

=E2 <Quarterly_Targets!$E2 

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

• हर में रिफ्रेश करें >> 5 मिनट डालें।
• ओके पर क्लिक करें।

यह विधि बाहरी डेटा को स्वचालित रूप से रिफ्रेश करती है और संदर्भ सीमाओं से बचाती है।

विधि 3: पूर्ण ऑटोमेशन के लिए VBA मैक्रो

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

VBA संपादक खोलने के लिए:

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

• निम्न 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") ' डेटा पंक्तियां: 2 से 6, कॉलम: 2 (B/Q1) से 5 (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) के रूप में सहेजें।
• डेवलपर टैब पर जाएं >> मैक्रो चुनें।
• HighlightSalesBelowTarget चुनें >> चलाएं पर क्लिक करें।

आउटपुट:

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

Excel के कुछ संस्करण चेतावनी प्रदर्शित करते हैं "आप सशर्त स्वरूपण मानदंड के लिए अन्य वर्कबुक के संदर्भ का उपयोग नहीं कर सकते।"

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

सशर्त स्वरूपण नियमों में बाहरी मूल्यों का उपयोग करने का कोई प्रत्यक्ष, मूल तरीका नहीं है।

सिफारिशें

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

निष्कर्ष

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

बाहरी वर्कबुक के मूल्यों के आधार पर सशर्त स्वरूपण ट्रिगर करना Excel में मूल रूप से संभव नहीं है।

मुफ़्त उन्नत Excel अभ्यास समाधान के साथ प्राप्त करें!