Обичам персонализирани списъци в Excel. Те са чудесни за дръжката за попълване и за сортиране на данни в различна последователност. Персонализираните списъци трябва да позволяват 254 елемента. Но по някаква причина читателят среща ситуация, в която Excel съхранява само първите 38 елемента! Ще стигнем до дъното на тази мистерия.
Гледам видео
- Дон иска да сортира по персонализиран списък, който е цифров!
- Това ще работи ли? Изглежда работи!
- Но не можете да импортирате цифрови клетки в диалоговия прозорец по избор.
- Така че, опитайте се да въведете числа в диалоговия прозорец Персонализиран списък …. Получавате глупаво ограничение от 255 знака, когато пишете.
- WTH е границата? 254 артикула? Аха - 254 елемента, но по-малко от 2000 знака, когато добавите невидимата запетая между всеки елемент
- Извърши ли математическа математика с
=SUM(LEN())и Ctrl + Shift + Enter иLEN(TEXTJOIN(",",True,Range)) - Заобиколно решение с ABS за сортиране в този конкретен случай за Дон
- Но най-доброто решение … нещото, което Дон трябва да направи:
Видео транскрипция
Научете Excel от Подкаст, Епизод 2098: Граници на персонализиран списък.
Това е просто странно, когато Don S, използвайки Mac 2011, нали, така че дори не използваме реалната версия на Excel. Използваме фалшивата версия на Excel, опитвайки се да сортираме по персонализиран списък, но приема само първите 38 елемента в списъка. И знам, че това е погрешно, защото Excel може да обработва до 254 елемента в списъка или поне така си мислех. Добре, и Дон има име на играч, # от победи и след това марж, като колко далеч от резултата са били тогава.
Така че правилната последователност е, перфектният резултат е 0 и след това над 1, под 1 и над 2, под 2, над 3, под 3 и така нататък. И Дон се опитва да сортира колоната Margin по този персонализиран списък. Сега никога не съм опитвал това, но, хей, трябва да е лесно да се направи. Така че тук Правилна последователност: 0, 1 и тогава формулата ще бъде минус стойността точно преди нас и след това = числото 2 над +1. Добре, сега, когато имам тези две формули, бих могъл да премина през 201, твърде далеч, но това е добре. И трябва да имаме точната последователност, която ни е необходима, до 99 и -99. Така че има нашият перфектен набор от отговори. Ще копирам това, така че Ctrl + C да копирате и след това Поставете като стойности. Поставете тези стойности така. Добре, така че трябва да мога да създам персонализиран списък, който ще се справи с това, нали? Няма проблем.Така че отиваме до Файл, Опции, Разширени, превъртаме 83% до долу, избираме Редактиране на персонализирани списъци и ние ще импортираме нашия списък. Какво? Клетките без обикновен текст бяха игнорирани. Нямате право да имате персонализиран списък, пълен с номера? Но Дон казва, че това работи за първите 38. Какво става с това? Е, не мислете за това от известно време. Разбрах, че Дон сигурно не се е опитвал да импортира; той трябва да е въвеждал тези цифри в диалоговия прозорец.Разбрах, че Дон сигурно не се е опитвал да импортира; той трябва да е въвеждал тези цифри в диалоговия прозорец.Разбрах, че Дон сигурно не се е опитвал да импортира; той трябва да е въвеждал тези числа в диалоговия прозорец.
Така че ето какво ще направя. Ще направя Ctrl + C, за да копирам всички тези, ще отида в Notepad и Paste в Notepad като този Ctrl + V и след това ще избера всичко: Edit, Select all и Ctrl + C, върнете се в Excel, Файл, Опции, Разширени, 83% докрай, Редактиране на персонализирани списъци и ще напиша този списък тук, сякаш е Ctrl + V. Добре, и че всички работи, но щракнахме Добавяне, максималната дължина за персонализирания списък е надвишена. Ще бъдат запазени само първите 255 знака. И когато погледнете това, разбира се, те създават персонализиран списък, който се спуска само на 38, -38, 39 и след това BAM! Последните 3, нали?
Така че, това е толкова странно. Всъщност ми позволяват да създам персонализиран списък с числа, но не ми позволяват да получа 255. Искам да кажа, работи. Това работи и тогава, ако всъщност се опитаме да сортираме тук; така че ще кажем Данни, Сортиране и сортиране в този потребителски списък, този, който отива само до 39 щракнете върху OK, щракнете върху OK. Е, ако е в списъка, той сортира правилно. Добре, така че положителните +6 показват преди -6; но след като стигнем до нещо отвъд 39, то просто ще се сортира в последователностите, които не са в списъка, така че ще става от най-малките до най-големите тук. Така че, някой пропуснат с 67 точки е по-добър от някой пропуснат с +42 точки. Просто е напълно изцапан.
Добре, и какво става с това само до 38? Сега, със сигурност има и всеки друг номер, така че, знаете ли, предполагам, че ще спадне през 30-те години. Ние започваме да харесваме там, нали? Или там, един от тези двамата. И така, какво е това? Това са общо 78 артикула. И хей, знам, че са разрешили - имайте 250 форума, защото през семинарите си говоря за клиенти през цялото време, нали? Можете да имате 250 форума, нека ви покажа. Елемент 1 и, разбира се, можем да използваме Попълващия елемент за това. Ще преместя до 254, така. Сега това не са формули, така че трябва да можем да направим File, Options, Advance, да преминем към Edit Custom Lists и ще импортираме този списък, нали? Ето го, БАМ! Няма проблеми, няма съобщение за грешка. Всичко е страхотно, всичко е - Не е страхотно. Отива само към позиция 234. Чакай, знам, че можеш да имаш 254.Защо спира на 234? Това е странно, това е странно. Какво става с това?
И така, тук знаем, че става въпрос само за елемент 234 точно там. Добре сега, когато пишехме елементите в списъка, има известен брой знаци. Там беше границата. Затова се чудя дали има някакъв брой знаци, който е ограничението тук = SUM (LEN от целия този куп неща, натиснете CTRL + SHIFT ENTER и това са 1764 знака - 234 елемента. И знам, че можете да имате 254, аз съм направи това преди.
И нека опитаме нещо по-лудо. Добре, нека опитаме това. Нека опитаме вместо елемент нека опитаме нещо по-дълго. Така че 10 знака ПРОСТРАНСТВО и след това номер 1, ще слезем 254 реда надолу. И ние ще се опитаме да импортираме този списък: така Файл, Опции, Разширени, Редактиране на персонализирани списъци, ще импортираме този списък. Няма съобщение за грешка. Изглежда, че е работило, но намалява само до 140. Какво, по дяволите, става с това? Каква е границата? Мислех, че може да е 254. Така че нека видим колко знака имаме, ако стигнем до 140. Добре, така че нека оставим всичко останало след това и всъщност ще дойда тук до тази формула и ще копирам точно същата формула над. Добре, не.
В този момент съм доста раздразнен от екипа на Excel. Какво става, тук 1764 и тук 1852. Хей, Microsoft, какъв е лимитът? Какъв точно е лимитът? А, но тук е нещото. Сигурно съхраняват това като поредица от разделени низове, нали? Така че те вземат всички елементи и след това добавят запетая след всеки един. Добре, така че тук, тъй като имаме Office 365, можем да използваме новото присъединяване на текст, така че = TEXTJOIN на всички с запетая между тях. Не знам дали наистина е запетая или не. Игнорирайте това, след това True, запетая и тези елементи. И така, разбираме това. И всъщност просто искам да знам дължината на цялото това нещо. Така че дължината е 1997 г. и когато правя същото нещо тук, 1991. О! Така че, очевидно ограничението трябва да бъде 2000 знака, включително невидима запетая между всеки елемент.
Всичко това е доста странно. Добре, така че винаги съм смятал, че са 254 артикула, а не 254 артикула. Това са 254 елемента, при условие че са по-малко от 2000 знака, при условие че елементите не са твърде дълги. Добре, просто за да проверим теорията си, нека просто използваме интервал 1 като този и ще вземем дръжката за попълване и плъзнем. Те трябва да са наистина хубави и кратки, защото - И ще слезем до 255, 254. Нека отидем на 255, за да го тестваме.
Добре, така че сега с това, ако поискам дължината на текстовото присъединяване, 1421. Въобще няма проблем. Така че изберете цялото нещо и Файл, Опции, Разширени, превъртете до долу, Редактирайте персонализирани списъци, щракнете върху Импортиране. Добре, и изключете чак до 254. Добре, така че това са 254 елемента, при условие че това е по-малко от 2000 знака, включително невидима запетая след всеки елемент е как това работи.
Знаете, така е - но тук да се върнем към проблема на Дон. Със сигурност е досадно, че диалоговият прозорец, ако просто влезем и започнем да пишем нещата в диалоговото поле, вместо да има елемент от 2000 знака, той има 255 знака върху него. Добре, така че Дон няма начин да въведе това нещо и когато се опитаме да импортираме номера, той отказва да импортира номерата. Пише, че няма сделка. Всичко, което не е обикновен текст, няма да работи, нали?
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.
Е, ето ви. Дон, един от по-лудите въпроси, които някога съм чувал в Excel, и ние открихме поне 2 - Е, определено 1 грешка в Excel, че диалоговият прозорец Персонализиран списък няма да позволява повече от 255 знака. И тогава това странно нещо, което очевидно ще сортира потребителски списък по числа, но те няма да ви позволят да импортирате номера. Добре, така че ще се обадя на тази грешка номер 2. И тогава това решение тук, нали?
Ей, искам да благодаря на Дон за изпращането на въпроса и ако се мотаете толкова дълго, искам да ви благодаря, че отбихте. Ще се видим следващия път за поредното излъчване от.
Свали файл
Изтеглете примерния файл тук: Podcast2098.xlsm
