मुझे एक्सेल में कस्टम लिस्ट बहुत पसंद है। वे भरण हैंडल और डेटा को एक अलग क्रम में क्रमबद्ध करने के लिए महान हैं। कस्टम सूची में 254 आइटम की अनुमति होनी चाहिए। लेकिन किसी कारण से, एक पाठक एक ऐसी स्थिति का सामना कर रहा है जहां एक्सेल केवल पहले 38 वस्तुओं को रख रहा है! हम इस रहस्य की तह तक पहुंचेंगे।
वीडियो देखेंा
- डॉन एक कस्टम सूची द्वारा क्रमबद्ध करना चाहता है जो संख्यात्मक है!
- यह काम करेगा? यह काम करने लगता है!
- लेकिन आप कस्टम सेल को कस्टम लिस्ट डायलॉग बॉक्स में नहीं बाँट सकते।
- तो, कस्टम सूची संवाद बॉक्स में नंबर टाइप करने का प्रयास करें…। टाइप करते समय आप एक मूर्ख 255 वर्ण की सीमा से टकरा जाते हैं।
- WTH की सीमा है? 254 आइटम? अहा - 254 आइटम, लेकिन 2000 से कम वर्ण जब आप प्रत्येक आइटम के बीच अदृश्य कॉमा जोड़ते हैं
- क्या कुछ पाठ गणित के साथ
=SUM(LEN())और Ctrl + Shift + Enter औरLEN(TEXTJOIN(",",True,Range)) - डॉन के लिए इस विशेष मामले में छंटनी के लिए ABS के साथ समाधान
- लेकिन सबसे अच्छा समाधान … वह चीज़ जो डॉन को करने की आवश्यकता है:
वीडियो ट्रांसक्रिप्ट
पॉडकास्ट, एपिसोड 2098 से एक्सेल सीखें: कस्टम सूची सीमाएं।
यह केवल एक विचित्र है, जब डॉन एस, मैक 2011 का उपयोग कर रहा है, ठीक है, इसलिए हम एक्सेल के वास्तविक संस्करण का भी उपयोग नहीं कर रहे हैं। हम एक्सेल के नकली संस्करण का उपयोग कर रहे हैं, कस्टम सूची द्वारा क्रमबद्ध करने की कोशिश कर रहे हैं, लेकिन यह केवल सूची में पहले 38 वस्तुओं को स्वीकार कर रहा है। और मुझे पता है कि यह गलत है क्योंकि एक्सेल सूची में 254 वस्तुओं को संभाल सकता है या कम से कम यही मैंने सोचा था। ठीक है, और डॉन में प्लेयर का नाम, # की जीत और फिर मार्जिन है, जैसे कि स्कोर से कितनी दूर थे।
तो उचित अनुक्रम है, पूर्ण स्कोर 0 है और फिर 1 से 1 से कम है, और 2 से अधिक है, 2 से कम है, 3 से अधिक है, 3 के तहत और इसी तरह। और डॉन इस कस्टम लिस्ट द्वारा मार्जिन कॉलम को सॉर्ट करने की कोशिश कर रहा है। अब, मैंने कभी कोशिश नहीं की है लेकिन, हे, यह करना आसान होना चाहिए। यहाँ पर उचित अनुक्रम: 0, 1 और फिर सूत्र हमारे सामने मूल्य को शून्य करने वाला है और फिर +1 से ऊपर 2 नंबर = है। ठीक है, अब जब मेरे पास वे दो सूत्र हैं जिन्हें मैं 201 के माध्यम से कम करने में सक्षम होना चाहिए, वहां तक तो ठीक है लेकिन यह ठीक है। और हमारे पास सटीक क्रम होना चाहिए जो हमें 99 और -99 तक नीचे चाहिए। तो उत्तर के हमारे सही सेट है। मैं कॉपी करने के लिए इतना Ctrl + C कॉपी करने जा रहा हूं और फिर वैल्यू के रूप में पेस्ट कर रहा हूं। उन मूल्यों को ऐसे ही चिपकाएँ। ठीक है, इसलिए मुझे एक कस्टम सूची सेट करने में सक्षम होना चाहिए जो इसे संभाल लेगी, है ना? कोई दिक्कत नहीं है।इसलिए हम फाइल, विकल्प, एडवांस में जाते हैं, नीचे 83% स्क्रॉल करते हैं, कस्टम सूची संपादित करें चुनते हैं और हम अपनी सूची आयात करेंगे। क्या? सरल पाठ के बिना कोशिकाओं को नजरअंदाज कर दिया गया था। आपको संख्याओं से भरी कस्टम सूची की अनुमति नहीं है? लेकिन डॉन का कहना है कि यह पहले 38 के लिए काम कर रहा है। इसके साथ क्या हो रहा है? खैर, कुछ समय के लिए इस बारे में नहीं सोचा। मुझे एहसास हुआ कि डॉन आयात करने की कोशिश नहीं कर रहा होगा; वह सिर्फ डायलॉग बॉक्स में उन नंबरों को टाइप कर रहा होगा।मुझे एहसास हुआ कि डॉन आयात करने की कोशिश नहीं कर रहा होगा; वह सिर्फ डायलॉग बॉक्स में उन नंबरों को टाइप कर रहा होगा।मुझे एहसास हुआ कि डॉन आयात करने की कोशिश नहीं कर रहा होगा; वह सिर्फ डायलॉग बॉक्स में उन नंबरों को टाइप कर रहा होगा।
तो यहाँ मैं क्या करने जा रहा हूँ। मैं उन सभी को कॉपी करने के लिए Ctrl + C करने जा रहा हूं, मैं इस Ctrl + V की तरह नोटपैड में Notepad और Paste करने जा रहा हूं, और फिर सब कुछ का चयन करें: संपादित करें, सभी का चयन करें, और Ctrl + C, वापस आ जाएं Excel, फ़ाइल, विकल्प, उन्नत, 83% नीचे सभी तरह, कस्टम सूचियों को संपादित करें और मैं उस सूची को यहाँ टाइप करने जा रहा हूँ जैसे कि यह Ctrl V V है। ठीक है, और यह सब काम करता है लेकिन हमने Add पर क्लिक किया है, कस्टम सूची की अधिकतम लंबाई पार कर ली गई है। केवल पहले 255 अक्षर ही सहेजे जाएंगे। और जब आप इसे देखते हैं, तो निश्चित रूप से पर्याप्त है, वे एक कस्टम सूची बना रहे हैं जो केवल 38, -38, 39 और फिर बीएएम में नीचे जाती है! अंतिम 3, सही?
तो, यह बहुत विचित्र है। वे वास्तव में मुझे संख्याओं के साथ एक कस्टम सूची बनाने दे रहे हैं, लेकिन वे मुझे 255 प्राप्त करने की अनुमति नहीं दे रहे हैं। मेरा मतलब है, यह काम करता है। यह काम करता है और अगर हम वास्तव में कोशिश करते हैं और यहाँ पर छाँटते हैं; इसलिए हम इस कस्टम सूची पर डेटा, सॉर्ट और सॉर्ट करेंगे, जो केवल 39 पर जाता है ठीक पर क्लिक करें, ठीक पर क्लिक करें। ठीक है, अगर यह सूची में है तो यह सही तरीके से है। ठीक है, इसलिए सकारात्मक -6 शो -6 से पहले; लेकिन फिर एक बार जब हम 39 के पार किसी भी चीज़ के लिए नीचे आते हैं तो यह सिर्फ उन अनुक्रमों में छंटनी होती है जो सूची में नहीं होते हैं, इसलिए यह सबसे छोटे से सबसे बड़े तक जा रहा है। इसलिए, किसी को 67 अंक से चूक गए, किसी को +42 अंक से चूक गए। यह पूरी तरह से पेंचीदा है।
ठीक है, और यह केवल 38 के साथ क्या हो रहा है? अब, सुनिश्चित करें कि हर दूसरे नंबर पर ऐसा है, आप जानते हैं, मुझे लगता है कि यह 30 के दशक में नीचे जाने वाला है। हम नीचे वहाँ पसंद कर रहे हैं, है ना? या वहाँ, उन दो में से एक। तो, वह क्या है? वह कुल 78 आइटम है। और हे, मुझे पता है कि उन्होंने अनुमति दी - 250 फोरम हैं क्योंकि मैं अपने सेमिनारों में हर समय ग्राहकों के बारे में बात करता हूं, ठीक है? आपके 250 फोरम हो सकते हैं, मैं आपको दिखाता हूं। इसलिए आइटम 1, और निश्चित रूप से हम उसके लिए फिलिंग आइटम का उपयोग कर सकते हैं। मैं 254 तक नीचे खींचूँगा, जैसे। अब वे सूत्र नहीं हैं, इसलिए हमें फ़ाइल, विकल्प, अग्रिम करने में सक्षम होना चाहिए, कस्टम सूचियों को संपादित करें और हम उस सूची को आयात करेंगे, ठीक है? वहाँ यह है, BAM! कोई समस्या नहीं, कोई त्रुटि संदेश नहीं। सब कुछ महान है, सब कुछ है - यह महान नहीं है। यह केवल आइटम 234 पर जाता है। रुको, मुझे पता है कि आपके पास 254 हो सकते हैं।234 पर क्यों रुक रही है? वह विचित्र है, वह विचित्र है। उसके साथ क्या है?
तो यहाँ, हम जानते हैं कि यह केवल आइटम 234 पर जा रहा है। ठीक है अब, जब हम सूची में आइटम टाइप कर रहे थे तो कुछ वर्ण हैं। सीमा थी। इसलिए मुझे आश्चर्य है कि अगर कुछ वर्ण हैं जिनकी सीमा यहाँ है = SUM (उस पूरे सामान की सूची, CTRL + SHIFT ENTER दबाएँ, और यह 1764 वर्ण - 234 आइटम है। और मुझे पता है कि आपके पास 254 हो सकता है, मुझे) इससे पहले किया।
और चलो कुछ पागल की कोशिश करो। ठीक है, चलो यह कोशिश करते हैं। आइए आइटम के बजाय कोशिश करें कि चलो कुछ लंबा प्रयास करें। तो 10 अक्षर एक स्थान और फिर नंबर 1, हम 254 पंक्तियों को छोड़ देंगे। और हम इस सूची को आज़माएँगे और आयात करेंगे: इसलिए फ़ाइल, विकल्प, उन्नत, कस्टम सूचियाँ संपादित करें, हम इस सूची को आयात करेंगे। कोई त्रुटि संदेश नहीं। लगता है कि यह काम किया है, लेकिन यह केवल 140 से नीचे चला जाता है। क्या बिल्ली उस के साथ है? सीमा क्या है? मुझे लगा कि यह 254 हो सकता है। तो चलिए देखते हैं, 140 तक नीचे उतरने पर हमारे पास कितने पात्र हैं। ठीक है, तो चलिए इसके बाद बाकी सब छोड़ देते हैं और वास्तव में मैं इस फार्मूले पर यहाँ आता हूँ और ठीक उसी सूत्र की प्रतिलिपि बनाता हूँ ऊपर। ठीक है, नहीं।
इस बिंदु पर, मैं एक्सेल टीम के साथ बहुत अधिक उत्साहित हूं। क्या हो रहा है, यहाँ १ up६४ और यहाँ १.५२। अरे, Microsoft, सीमा क्या है? वास्तव में सीमा क्या है? आह, लेकिन यहाँ बात है। वे इसे सीमांकित तारों की एक श्रृंखला के रूप में संग्रहित कर रहे होंगे, ठीक है? इसलिए वे सभी आइटम ले रहे हैं और फिर वे एक के बाद एक अल्पविराम जोड़ रहे हैं। ठीक है, इसलिए जब से हमारे पास Office 365 है, हम नए टेक्स्ट जॉइन का उपयोग कर सकते हैं, इसलिए = बीच में अल्पविराम से उन सभी का पाठ लिखें। मुझे नहीं पता कि यह वास्तव में अल्पविराम है या नहीं। उस पर ध्यान न दें, फिर True, अल्पविराम और ये आइटम। तो, हम प्राप्त करते हैं। और वास्तव में मैं सिर्फ उस पूरी बात की लंबाई जानना चाहता हूं। तो लंबाई १ ९९ and है और जब मैं यहाँ १ ९९ १ का काम करता हूं। ओह! तो, स्पष्ट रूप से सीमा प्रत्येक आइटम के बीच एक अदृश्य अल्पविराम सहित 2000 वर्णों की होनी चाहिए।
यह सब बहुत विचित्र है। ठीक है, इसलिए मैंने हमेशा सोचा कि यह 254 आइटम था, यह 254 आइटम नहीं है। यह 254 आइटम है, बशर्ते यह 2,000 से कम वर्णों का हो, बशर्ते कि आइटम बहुत लंबे न हों। ठीक है, तो बस मेरे सिद्धांत का परीक्षण करने के लिए, आइए बस ए स्पेस 1 का उपयोग करें और हम फिल हैंडल और ड्रैग को पकड़ लेंगे। ये वास्तव में अच्छा और छोटा होना चाहिए क्योंकि - और हम 255, 254 तक नीचे जाएंगे। इसे परीक्षण करने के लिए 255 पर जाएं।
ठीक है, तो अब इस के साथ, अगर मैं पाठ में शामिल होने की लंबाई के लिए पूछना, 1421. कोई समस्या नहीं है। इसलिए पूरी चीज़ चुनें और फ़ाइल, विकल्प, उन्नत, नीचे तक सभी तरह स्क्रॉल करें, कस्टम सूचियों को संपादित करें, आयात पर क्लिक करें। ठीक है, और सभी तरह से नीचे 254 पर बंद करें। ठीक है, तो यह 254 आइटम है बशर्ते कि यह एक आइटम के बाद एक अदृश्य अल्पविराम सहित 2,000 से कम वर्ण है, यह कैसे काम करता है।
You know, so - but back to Don’s problem here. It sure is annoying that the Dialog Box, if we just go in and start typing things in the Dialog Box instead of having a 2,000 character element, it has a 255 character on it. Alright, so Don has no way to type this thing in and when we try and Import numbers, it refuses to Import the numbers. It says no deal. Anything that's not plain text is not going to work, alright?
So, the one thing I suggested to Don is an alternate solution. I say, hey, let's just come out here and add a Helper Column and this Helper Column is going to be the - if the Absolute Value of that number, alright. And we'll double click to shoot that down and then what you’re going to do is you're just going to sort Descending by the Absolute Value, a setting by the Absolute Value, alright. And then the 4, 6 and then -6, alright, these are all just get sorted together, you know. So it's not bad, I guess what you could really do is you could sort by the Helper and then Add a Level and then Sort by the Margin, descending Largest to Smallest, click OK and that will get what Don is looking for. So, all of the +6 will show up before the -6, and then 8 and then -11, alright. You know, but this is a hassle. Like, hey, Microsoft. Why do we have to go to all this hassle? Why would you let us type 2,000 characters into the Dialog Box or even better yet, since it apparently works to have numbers in the Custom List, I mean it is working here, why won't you let us import it?
Alright now, here is the workaround. And I'm sorry that took this freaking long to get to the end but this is what I had to go through to figure all this out. So, I'm going to add this Workaround Column, alright. And this Workaround Column is going to be the Text, the text of that number in “”, or “0”) like that. And we're going to copy that down. Alright, so now we're taking these numbers here - see the numbers are right justified and we are left justifying it by using the Text Function. Unfortunately though, you can't use formulas in the Custom List. If I would come here to File, Options, Advanced, go down to Edit Custom Lists and we'll Import that - no! Cells without simple texts were ignored. Okay, fine. Getting really annoyed now, right?
Now, what I'm going to do is I want to select the Workaround Column. I'm going to use the Home tab and I'm going to copy it and then I'm going to paste and paste Values. If you don't do this step, it's not going to work. And here's how to prove to yourself that it works. Alright, if those are really numbers, when you do the SUM function it's going to give you an answer like this. That's going to be 5, alright? But if I do the SUM of the Workaround Column, I'm going to get O, alright. And the reason is, is the SUM can't sum texts. Alright, so these are real numbers, these are real texts. You have to get it to be real text. Once you have it as real text, select the whole thing. Go to File, Options, Advanced, Edit Custom Lists. We'll get rid of this old Custom List here that we don't need. We'll get rid of this list that we don't need. We'll get rid of this list that we don't need. We'll even get rid of this list that only goes down to 38 and 39; and then finally, we'll Import the whole list. And these whole lists goes from 0 all the way down to 99 and -99 and presumably store it as text, alright? But here's the cool, awesome, amazing thing. Let's just come back up and we'll sort this data by name: Data, A-Z, and now everything is just completely random there. And then, we will Sort by Margin and the order is going to be a Custom List. The custom list is going to be this sequence, click OK and click OK. And it works: the 4, the 6s, the -6, the 8, the -11 and so on.
Now, the beautiful thing about this is once you get it set up, alright, it's a hassle to put these numbers over here in the- off to the side, change them to text, Copy, Paste Special Values and then Import that list. But the beautiful thing is, once that's imported then forevermore on this computer we're going to be able to sort correctly by margin. So Don, geez! I think that it's worth it to go through that whole thing.
617 Excel mystery solved. Now, this is a new mystery why this Custom List wouldn't work but this book is full of stuff like this video, just the bizarre, the arcane and so on. Click that “i” on the top right-hand corner to buy the book.
Alright, Don wants to sort by Custom List that is numeric, will it work? It seems to work. It seems to work for the first 38 numbers but you can't import numeric cells into the Custom List Dialog Box. Ah, so try and type the custom numbers or actually, I guess I pasted to Notepad and then from Notepad I paste it. But then there's this crazy 255 character limit when typing. That must be left over from Excel 2003, right? Because the limit is more than that now. You can have 254 items. Is it 254 items? Ah, it’s 254 items less than 2000 characters when you have the invisible column, comma between each item.
Now we did some Math here, text math with =SUM(LEN) then CTRL+SHIFT ENTER and then the LEN(TEXTJOIN). If you have Office 365 and are on the latest version - even came up with this workaround with the Absolute Value for sorting which will help in this particular case for Don. But I think what Don needs to do, the best workaround, and this is what I tell them to do on the YouTube video. Put those numbers: 0, 1, -1, in A1:A200. And then in the column next to that, =TEXT(A1, “0”). That's going to be converted to text. Copy that formula all the way down and then select that range of formulas Copy and then after you Copy go to the Paste dropdown. So Copy, Paste and Paste as Values. And if you're successful, when you do the SUM of that whole thing, it’s going to sum to a number that's not or it’s going to sum to 0. It's going to sum to 0. Be careful. Don't choose all of your numbers because your numbers do happen to sum to 0. Now the B contains text numbers you can import because of Custom Lists.
अच्छा, तुम वहाँ जाओ। डॉन, एक क्रैजियर प्रश्न जो मैंने कभी एक्सेल में सुना है और हमने कम से कम 2 को उजागर किया है - ठीक है, निश्चित रूप से एक्सेल में 1 बग कि कस्टम लिस्ट डायलॉग बॉक्स 255 से अधिक वर्णों की अनुमति नहीं देगा। और फिर यह विचित्र बात यह है कि जाहिरा तौर पर संख्याओं द्वारा एक कस्टम सूची को सॉर्ट किया जाएगा, लेकिन वे आपको संख्याओं को आयात नहीं करने देंगे। ठीक है, इसलिए मैं उस बग नंबर 2 को कॉल करने जा रहा हूं। और फिर यहां यह वर्कअराउंड, ठीक है?
तो हे, मैं प्रश्न भेजने के लिए डॉन का शुक्रिया अदा करना चाहता हूं और यदि आप इस समय के आसपास घूमते हैं, तो मैं आपको रोकने के लिए धन्यवाद देना चाहता हूं। हम आपको अगली बार एक और नेटकास्ट से देखेंगे।
फ़ाइल डाउनलोड करें
यहाँ नमूना फ़ाइल डाउनलोड करें: Podcast2098.xlsm
