इससे पहले पॉडकास्ट 2093 में, मैंने एक साधारण वीबीए सॉर्ट दिखाया था जो काम करता है यदि आप रंग से नहीं छांट रहे हैं। आज, नीता VBA के लिए एक्सेल डेटा को रंग से सॉर्ट करने के लिए कहती है।
VBA द्वारा सॉर्ट करने के बारे में सबसे पेचीदा बात यह पता लगा रही है कि आप किस RGB कलर कोड का उपयोग कर रहे हैं। 99% मामलों में, आपने RGB मान दर्ज करके एक रंग नहीं चुना। आपने एक्सेल में इस ड्रॉपडाउन का उपयोग करके एक रंग चुना।
और, जब आप फिल, मोर कलर्स, कस्टम का उपयोग कर सकते हैं, तो यह जानने के लिए कि चयनित रंग RGB (112,48,160) है, यदि आपके पास बहुत सारे रंग हैं, तो यह एक परेशानी है।
इसलिए - मैं मैक्रो रिकॉर्डर चालू करना चाहता हूं और मैक्रो रिकॉर्डर को कोड का पता लगाने देता हूं। मैक्रो रिकॉर्डर द्वारा उत्पन्न कोड कभी भी सही नहीं होता है। यहां वीडियो दिखाया गया है कि रंग द्वारा छंटाई करते समय मैक्रो रिकॉर्डर का उपयोग कैसे करें।
वीडियो ट्रांसक्रिप्ट
पॉडकास्ट से एक्सेल सीखें, एपिसोड 2186: VBA सॉर्ट बाय कलर।
अरे, नेटकास्ट में वापस स्वागत है, मैं बिल जेलन हूं। आज का प्रश्न, YouTube पर भेजा गया है। मेरे पास वीबीए के साथ सॉर्ट करने के तरीके पर एक वीडियो था, और वे वीबीए के साथ रंग के आधार पर सॉर्ट करना चाहते थे, जो बहुत अधिक जटिल है। मैंने कहा, "आप सिर्फ मैक्रो रिकॉर्डर को चालू क्यों नहीं करते हैं और देखें कि क्या होता है?" और, दुर्भाग्य से, मैक्रो रिकॉर्डर, आप जानते हैं, यह हमें बंद कर देता है, लेकिन यह हमें वहां नहीं मिलता है।
तो देखें, मैक्रोज़, रिकॉर्ड मैक्रो, "हाउटोर्टबायकलर", इस कार्यपुस्तिका में स्टोर मैक्रो- परिपूर्ण। ओके पर क्लिक करें। ठीक है, इसलिए अब मैक्रो रिकॉर्डर चल रहा है, हम यहां डेटा टैब पर आने वाले हैं और हम सॉर्ट कहने जा रहे हैं। हम एक सॉर्ट संवाद बॉक्स का उपयोग करने जा रहे हैं और हम इसे बनाने जा रहे हैं, ठीक है? इसलिए, हम यह कहने जा रहे हैं कि हम एक स्तर जोड़ना चाहते हैं, चेरी पर सॉर्ट करें, लेकिन सेल वैल्यूज़ पर क्रमबद्ध न करें; हम सेल कलर पर सॉर्ट करने जा रहे हैं- सेल कलर वहां का रंग है- और हम शीर्ष पर लाल रखना चाहते हैं और फिर उस स्तर की नकल करते हैं, और पीला दूसरा डालते हैं; और फिर हम एक नया स्तर जोड़ेंगे- हम कॉलम D पर जाएँगे, तिथि स्तंभ-- सेल कलर पर क्रमित करें, लाल पहले, उस स्तर को कॉपी करें, पीला, और फिर यहाँ पर; फिर एल्डबेरी, कॉलम ई में यहाँ पर, कुछ नीले फ़ॉन्ट है जो मैं नहीं देखना चाहता कि यह कैसा दिखता है,इसलिए हम इसे शीर्ष पर नीले रंग के साथ फ़ॉन्ट रंग के आधार पर क्रमबद्ध करेंगे; और फिर अगर ये सभी बिना किसी रंग के एक टाई हैं, तो हम स्तंभ A-- सेल वैल्यूज़ पर एक अंतिम स्तर जोड़ देंगे, सबसे बड़ा सबसे छोटा; और ठीक पर क्लिक करें।
ठीक है, अब, एक दो चीजें-- यह अगला कदम नहीं छोड़ें - आपकी फ़ाइल, अभी, मैं गारंटी देता हूं कि आपको xlsx के रूप में संग्रहीत किया गया है। यह फ़ाइल, इस रूप में सहेजें, और इसे xlsm या xlsb के रूप में सहेजने का बहुत अच्छा समय है। यदि आप ऐसा नहीं करते हैं, तो जब आप इस फ़ाइल को सहेजते हैं, तो इस बिंदु पर आपके सभी कार्य समाप्त हो जाएंगे। वे xlsx में संग्रहीत किसी भी चीज़ के मैक्रो को हटा देंगे। ठीक है?
इसलिए हमने वहां रिकॉर्डिंग बंद कर दी, और फिर हम अपने मैक्रोज़ को देखना चाहते हैं। तो, आप इसे View, Macros-- View, Macros-- के साथ कर सकते हैं और हमारे द्वारा रिकॉर्ड किए गए मैक्रो को खोजें - HowToSortByColor-- और Edit पर क्लिक करें। ठीक है, इसलिए यहां हमारा मैक्रो है, और जैसा कि मैं इस पर गौर करता हूं, जो समस्या हमारे पास है, आज हमारे पास 25 पंक्तियों के साथ साथ एक हेडिंग है। तो यह 26 पंक्ति के लिए नीचे चला जाता है। और वे कठिन-कोडित है कि वे हमेशा 26 पंक्तियों को देखने जा रहे हैं।
लेकिन जैसा कि मैं इस बारे में सोचता हूं, विशेष रूप से छंटाई के लिए पुराने VBA की तुलना में, हमें पूरी रेंज को निर्दिष्ट करने की आवश्यकता नहीं है - कॉलम में सिर्फ एक सेल। इसलिए कहीं भी जहां उनके पास कॉलम C26 है, मैं इसे केवल कहने के लिए कम करने जा रहा हूं, "अरे, नहीं, उस कॉलम में पहले देखो।" तो ई 2, और फिर, यहां, ए 2। तो मेरे मामले में, मेरे पास 1, 2, 3, 4, 5, 6, सॉर्ट लेवल हैं- 6 चीजें बदलने के लिए।
और फिर यह वह हिस्सा है जो मैक्रो रिकॉर्डर वास्तव में, वास्तव में खराब हो जाता है, वे केवल हर समय 26 पंक्तियों को क्रमबद्ध करने जा रहे हैं। इसलिए मैं इसे बदलने जा रहा हूं। मैं कहने जा रहा हूँ, "देखो, रेंज A21 से शुरू करो, और इसे बढ़ाओ। CurrentRegion।" आइए एक्सेल पर एक नज़र डालें और देखें कि क्या करता है। इसलिए, अगर मैं किसी एक सेल-- A1 या कुछ भी का चयन करूंगा - और Ctrl + * दबाएगा, यह वर्तमान क्षेत्र का चयन करता है। ठीक है, चलो करते हैं। यहां, मध्य से, Ctrl + *, और जो करता है, क्या यह सभी दिशाओं में फैली हुई है जब तक कि यह स्प्रैडशीट के किनारे पर, स्प्रैडशीट के शीर्ष पर, या डेटा के दाईं ओर या डेटा के निचले किनारे पर नहीं फैलती है । तो, A1 .CurrentRegion कहकर, यह A1 में जाने और Ctrl + * दबाने जैसा है। ठीक है? तो, यहाँ आपको उस चीज़ को बदलना होगा। अब स्थूल में बाकी सब कुछ ठीक है; यह 'सभी काम पर जा रहे हैं। उन्हें SortOnCellColor और SortOnFontColor और xlSortOn मिला। मुझे उसमें से किसी के बारे में चिंता करने की ज़रूरत नहीं है; मुझे केवल इतना करना है कि वे यहां देखें और देखें कि उन्होंने जिस क्षेत्र का उपयोग करने जा रहे हैं, उस क्षेत्र को हार्ड-कोडित किया है, हार्ड-कोडित वे कितनी दूर चले गए, और इसे हार्ड-कोडेड होने की आवश्यकता नहीं है। और उस सरल कदम के साथ, उन छह वस्तुओं और सातवें आइटम को बदलते हुए, हमारे पास कुछ है जो काम करना चाहिए।
अब, हम परीक्षण करते हैं। आइए यहाँ एक्सेल पर वापस आते हैं और हम नीचे कुछ नई पंक्तियाँ जोड़ेंगे। मैं सिर्फ 11s वहाँ रखूँगा, और हम कुछ जोड़ेगा Reds - एक लाल, एक पीला, और फिर यहाँ एक नीला। ठीक है। इसलिए अगर हम इस कोड को चलाते हैं - इस कोड को चलाते हैं, तो मैं यहां क्लिक करता हूं, और रन बटन पर क्लिक करता हूं - और फिर वापस आता हूं, हमें यह देखना चाहिए कि 11 लाल रंग में शीर्ष आइटम बन गया, यह वहां दिखाया गया yellows, और यह ब्लूज़ में दिखाई देता है, इसलिए यह सब पूरी तरह से काम कर रहा है। यह शीर्ष पर क्यों गया? क्योंकि ऐसा हुआ था कि अंतिम प्रकार कॉलम ए है और इसलिए जब टाई होता है, तो यह ए को टाईब्रेकर के रूप में देखता है। तो वह कोड काम कर रहा है।
VBA लिखने का तरीका जानने के लिए, मैंने ट्रेसी सीरस्टैड के साथ, पुस्तकों की एक श्रृंखला, एक्सेल VBA और MACROS लिखी है। 2003, 2007, 2010, 2013 और 2016 के लिए अब एक संस्करण आया है; जल्द ही 2019. ठीक है, इसलिए, उस संस्करण को ढूंढें जो आपके एक्सेल के संस्करण से मेल खाता है और यह आपको सीखने की अवस्था में लाएगा।
रैप-अप: आज का एपिसोड है, रंग द्वारा छाँटने के लिए VBA का उपयोग कैसे करें। ऐसा करने का सबसे आसान तरीका है, खासकर जब से आपको पता नहीं है कि प्रत्येक रंग के लिए आरजीबी कोड का उपयोग किया गया था - आपने अभी लाल चुना है आपको नहीं पता कि आरजीबी कोड क्या है, और आप नहीं देखना चाहते हैं इसे देखें - मैक्रो रिकॉर्डर को व्यू, मैक्रोज़, रिकॉर्ड न्यू मैक्रो का उपयोग करके चालू करें। जब आप सॉर्ट कर रहे हों, तो स्टॉप रिकॉर्डिंग पर क्लिक करें- यह निचले बाएँ कोने में है- Alt + F8 मैक्रोज़, या व्यू, मैक्रोज़, व्यू मैक्रो-- व्यू टैब, मैक्रोज़, और देखने के लिए तब देखें मैक्रोज़-- यह भ्रामक है। अपने मैक्रो को PSelect करें और Edit पर क्लिक करें, और कभी भी C2 को कुछ रेंज नंबरों पर देखें, बस इसे पॉइंट टू रो 2 में बदल दें। और फिर, जहां वे श्रेणी को सॉर्ट करने के लिए निर्दिष्ट करते हैं, रेंज ("A1"), CurrentRegion, का विस्तार होगा। ठीक है।
खैर, हे, मैं आपको रोकने के लिए धन्यवाद देना चाहता हूं, मैं आपको अगली बार एक और नेटकास्ट से देखूंगा।
वीडियो में, मैंने छह-स्तरीय सॉर्ट सेट किया। अंतिम डायलॉग बॉक्स यहाँ दिखाया गया है:
जिस दिन मैं मैक्रो रिकॉर्ड करने के लिए हुआ, उस दिन मेरे पास डेटा की 23 पंक्तियाँ और एक हेडिंग थी। मैक्रो में सात स्थान थे जिन्होंने पंक्तियों की संख्या को हार्ड-कोड किया था। इन्हें समायोजित करना होगा।
प्रत्येक प्रकार के स्तर के लिए, इस तरह कोड है:
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2:C24"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 0, 0) यह मूर्खतापूर्ण है कि मैक्रो रिकॉर्डर C2: C24 निर्दिष्ट करता है। आपको केवल कॉलम में एक सेल निर्दिष्ट करना है, इसलिए ऊपर दी गई पहली पंक्ति को इसमें बदलें:
ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2"), _प्रत्येक प्रकार के स्तरों के लिए समान परिवर्तन करें।
रिकॉर्ड किए गए मैक्रो के अंत के पास, आपके पास वास्तव में सॉर्ट करने के लिए रिकॉर्ड किया गया कोड है। यह इस तरह से शुरू होता है:
With ActiveWorkbook.Worksheets("Sheet2").Sort .SetRange Range("A1:E24") .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End Withकेवल A1: E24 को सॉर्ट करने के बजाय, A1 में प्रारंभ करने के लिए कोड बदलें और वर्तमान क्षेत्र तक विस्तारित करें। (वर्तमान क्षेत्र वह है जो आपको मिलता है यदि आप सेल से Ctrl + * दबाते हैं)।
.SetRange Range("A1").CurrentRegionवीडियो में दिखाया गया अंतिम कोड है:
Sub HowToSortByColor() HowToSortByColor Macro ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Clear ' Sort column C by Red ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 0, 0) ' Sort Column C by Yellow ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("C2"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 255, 0) ' Sort column D by Red ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("D2"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 0, 0) ' Sort column D by Yellow ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("D2"), _ xlSortOnCellColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(255, 255, 0) ' Sort column E by blue font ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add(Range("E2"), _ xlSortOnFontColor, xlAscending, , _ xlSortNormal).SortOnValue.Color = RGB(0, 176, 240) ' Sort Column A by Values descending ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("A2"), _ SortOn:=xlSortOnValues, _ Order:=xlDescending, _ DataOption:=xlSortNormal ' Perform the Sort With ActiveWorkbook.Worksheets("Sheet2").Sort .SetRange Range("A1").CurrentRegion .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With End Subध्यान दें
यह संभावना है कि आपकी कार्यपुस्तिका को XLSX एक्सटेंशन के साथ सहेजा गया है। XLSM या XLSB एक्सटेंशन में बदलने के लिए सेव सेव करें। XLSX में सहेजे गए किसी भी मैक्रोज़ को हटा दिया जाता है।
एक्सेल थॉट्स ऑफ द डे
मैंने अपने एक्सेल के बारे में सलाह के लिए अपने एक्सेल मास्टर दोस्तों से पूछा है। विचार करने के लिए आज का विचार:
"एक सेब एक दिन VBA को दूर रखता है।"
टॉम अर्टिस
