Jeg elsker tilpassede lister i Excel. De er gode til fyldhåndtaget og til at sortere data i en anden sekvens. Brugerdefinerede lister skal tillade 254 varer. Men af en eller anden grund støder en læser på en situation, hvor Excel kun beholder de første 38 emner! Vi kommer til bunden af dette mysterium.
Se video
- Don vil sortere efter en brugerdefineret liste, der er numerisk!
- Vil dette fungere? Det ser ud til at fungere!
- Men du kan ikke importere numeriske celler til dialogboksen med den brugerdefinerede liste.
- Så prøv at skrive tal i dialogboksen Custom List…. Du bliver ramt af en tåbelig grænse på 255 tegn, når du skriver.
- WTH er grænsen? 254 varer? Aha - 254 emner, men mindre end 2000 tegn, når du tilføjer det usynlige komma mellem hvert emne
- Gjorde noget tekst matematik med
=SUM(LEN())og Ctrl + Skift + Enter ogLEN(TEXTJOIN(",",True,Range)) - Løsning med ABS til sortering i dette særlige tilfælde for Don
- Men den bedste løsning … den ting, Don skal gøre:
Videoudskrift
Lær Excel fra Podcast, afsnit 2098: Grænser for brugerdefineret liste.
Dette er bare en bizar, når Don S, ved hjælp af Mac 2011, rigtigt, så vi bruger ikke engang den rigtige version af Excel. Vi bruger den falske version af Excel og prøver at sortere efter en brugerdefineret liste, men den accepterer kun de første 38 emner på listen. Og jeg ved, at det er forkert, fordi Excel kan håndtere op til 254 emner på listen, eller i det mindste er det, hvad jeg troede. Okay, og Don har spillernavn, # vinder og derefter margen, ligesom hvor langt væk fra scoren var dengang.
Så den rette rækkefølge er, den perfekte score er 0 og derefter over med 1, under af 1, og over af 2, under af 2, over af 3, under af 3 og så videre. Og Don forsøger at sortere margenkolonnen efter denne brugerdefinerede liste. Nu har jeg aldrig prøvet det, men hej, det skal være let at gøre. Så herovre Korrekt rækkefølge: 0, 1 og derefter vil formlen være minus værdien lige foran os og derefter = tallet 2 over +1. Okay, nu hvor jeg har disse to formler, skulle jeg være i stand til at krydse ned gennem 201, for langt der, men det er okay. Og vi skulle have den nøjagtige rækkefølge, som vi har brug for, ned til 99 og -99. Så der er vores perfekte sæt svar. Jeg skal kopiere det, så Ctrl + C for at kopiere og derefter indsætte som værdier. Indsæt disse værdier sådan. Okay, så jeg skulle være i stand til at oprette en brugerdefineret liste, der kan håndtere dette, ikke? Intet problem.Så vi går til File, Options, Advanced, rul 83% helt ned, vælg Edit Custom Lists, og vi vil importere vores liste. Hvad? Celler uden simpel tekst blev ignoreret. Du må ikke have en brugerdefineret liste fuld af numre? Men Don siger, at dette fungerer i de første 38. Hvad sker der med det? Nå, har ikke tænkt på dette i et stykke tid. Jeg indså, at Don må have prøvet at importere; han skal netop have skrevet disse numre i dialogboksen.Jeg indså, at Don må have prøvet at importere; han skal netop have skrevet disse numre i dialogboksen.Jeg indså, at Don må have prøvet at importere; han skal netop have skrevet disse numre i dialogboksen.
Så her er hvad jeg skal gøre. Jeg vil gøre Ctrl + C for at kopiere alle dem, jeg skal gå til Notesblok og indsætte i Notesblok som denne Ctrl + V, og vælg derefter alt: Rediger, Vælg alt og Ctrl + C, kom tilbage til Excel, File, Options, Advanced, 83% helt ned, Rediger brugerdefinerede lister, og jeg skriver denne liste her ligesom det er Ctrl + V. Okay, og at alt fungerer, men vi klikkede på Tilføj, den maksimale længde for brugerdefineret liste er overskredet. Kun de første 255 tegn gemmes. Og når du ser på dette, helt sikkert, opretter de en brugerdefineret liste, der kun går ned i 38, -38, 39 og derefter BAM! De sidste 3, ikke?
Så dette er så bizart. De lader mig faktisk oprette en brugerdefineret liste med tal, men de tillader mig ikke at få 255. Jeg mener, det virker. Det virker, og hvis vi faktisk prøver at sortere herovre; så vi siger Data, Sorter og sorter på denne brugerdefinerede liste, den der kun går til 39, klik på OK, klik på OK. Nå, hvis det er på listen, sorterer det korrekt. Okay, så det positive +6 vises før -6; men så når vi når ned til noget ud over 39, vil det bare sortere i de sekvenser, der ikke er på listen, så det går fra mindste til største her. Så nogen, der er gået glip af 67 point, er bedre end nogen, der er gået glip af +42 point. Det er bare helt skruet.
Okay, og hvad sker der med, at dette kun går til 38? Nu er der sikker på, at der er hvert andet nummer, så ved du, jeg antager, at det kommer ned i 30'erne. Vi kommer ned for at kunne lide der, ikke? Eller der, en af disse to. Så hvad er det? Det er i alt 78 varer. Og hej, jeg ved, de tillod det - har 250 fora, fordi jeg hele tiden taler om kunder i mine seminarer, okay? Du kan have 250 fora, lad mig vise dig. Så punkt 1, og selvfølgelig kan vi bruge påfyldningsgenstanden til det. Jeg trækker ned til 254 sådan. Nu er det ikke formler, så vi skal være i stand til at gøre File, Options, Advance, gå videre til Edit Custom Lists, og vi vil importere denne liste, okay? Der er det, BAM! Ingen problemer, ingen fejlmeddelelse. Alt er fantastisk, alt er - Det er ikke godt. Det går kun til punkt 234. Vent, jeg ved, du kan have 254.Hvorfor stopper det ved 234? Det er bizart, det er bizart. Hvad sker der med det?
Så her ved vi, at det kun går ned til punkt 234 lige der. Okay nu, da vi skrev elementerne på listen, er der et antal tegn. Der var grænsen. Så jeg spekulerer på, om der er et antal tegn, der er grænsen her = SUM (LENGE af hele den flok ting, tryk CTRL + SKIFT ENTER, og det er 1764 tegn - 234 emner. Og jeg ved, at du kan have 254, jeg har gjort dette før.
Og lad os prøve noget skørere. Okay, lad os prøve dette. Lad os prøve i stedet for element, lad os prøve noget længere. Så 10 tegn et MELLEMRUM og derefter nummer 1, vi går ned 254 rækker. Og vi prøver at importere denne liste: så File, Options, Advanced, Edit Custom Lists, vi importerer denne liste. Ingen fejlmeddelelse. Det ser ud til, at det fungerede, men det går kun ned til 140. Hvad pokker er der med det? Hvad er grænsen? Jeg troede, det kunne være 254. Så lad os se, hvor mange tegn vi har, hvis vi kommer ned til 140. Okay, så lad os lade alt andet efter dette, og faktisk kommer jeg herover til denne formel og kopierer nøjagtigt den samme formel over. Okay, nej.
På dette tidspunkt er jeg ret ophidset over Excel-teamet. Hvad sker der, her 1764 og her 1852. Hej, Microsoft, hvad er grænsen? Præcis hvad er grænsen? Ah, men her er sagen. De skal gemme dette som en række afgrænsede strenge, okay? Så de tager alle elementerne, og derefter tilføjer de et komma efter hver enkelt. Okay, så her, da vi har Office 365, kan vi bruge den nye Text Join, så = TEXTJOIN af alle dem med et komma imellem. Jeg ved ikke, om det virkelig er et komma eller ej. Ignorer det, så sandt, komma og disse emner. Så vi får det. Og faktisk vil jeg bare vide længden på det hele. Så længden er 1997, og når jeg gør det samme herovre, 1991. Åh! Så klart må grænsen være 2000 tegn inklusive et usynligt komma mellem hvert element.
Dette er alt sammen temmelig bizart. Okay, så jeg har altid troet, at det var 254 varer, det er ikke 254 varer. Det er 254 varer, forudsat at det er mindre end 2.000 tegn, forudsat at varerne ikke er for lange. Okay, så bare for at teste min teori, lad os bare bruge et mellemrum 1 sådan, så griber vi fyldhåndtaget og trækker. Disse skal være rigtig pæne og korte, fordi - Og vi går ned til 255, 254. Lad os gå til 255 for at teste det.
Okay, så nu med dette, hvis jeg beder om længden af tekstdeltagelsen, 1421. Overhovedet ikke noget problem. Så vælg det hele, og File, Options, Advanced, rul helt ned til bunden, Rediger brugerdefinerede lister, klik på Import. Okay, og sluk helt ned til 254. Okay, så det er 254 emner, forudsat at det er mindre end 2.000 tegn inklusive et usynligt komma efter hvert emne, hvordan dette fungerer.
Du ved det, men - men tilbage til Dons problem her. Det er helt sikkert irriterende, at hvis vi bare går ind og begynder at skrive ting i dialogboksen i stedet for at have et 2.000 karakterelement, har dialogboksen 255 tegn på den. Okay, så Don har ingen måde at skrive denne ting ind på, og når vi prøver at importere numre, nægter den at importere numrene. Det siger ingen aftale. Noget, der ikke er almindelig tekst, fungerer ikke, okay?
Så den ene ting, jeg foreslog Don, er en alternativ løsning. Jeg siger, hej, lad os bare komme ud her og tilføje en hjælpekolonne, og denne hjælpekolonne vil være den - hvis den absolutte værdi af dette tal, okay. Og vi dobbeltklikker for at skyde det ned, og så skal du bare sortere Faldende efter den absolutte værdi, en indstilling efter den absolutte værdi, okay. Og så er 4, 6 og derefter -6, okay, disse bliver alle bare sorteret sammen, ved du. Så det er ikke dårligt, jeg gætter på, hvad du virkelig kunne gøre er, at du kunne sortere efter hjælperen og derefter tilføje et niveau og derefter sortere efter margen, faldende Størst til Mindst, klik på OK, og det får det, som Don leder efter. Så alt +6 vises før -6, og derefter 8 og derefter -11, okay. Du ved, men det er besvær. Ligesom hej, Microsoft.Hvorfor skal vi gøre alt dette besvær? Hvorfor ville du lade os skrive 2.000 tegn i dialogboksen eller endnu bedre, da det tilsyneladende virker at have numre på den brugerdefinerede liste, jeg mener, det fungerer her, hvorfor vil du ikke lade os importere det?
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.
Nå, der går du. Don, et af de skøreste spørgsmål, jeg nogensinde har hørt i Excel, og vi har afsløret mindst 2 - Nå, bestemt en fejl i Excel, som dialogboksen Custom List ikke tillader mere end 255 tegn. Og så denne bizarre ting, som tilsyneladende vil sortere en brugerdefineret liste efter tal, men de lader dig ikke importere numre. Okay, så jeg kalder den fejl nummer 2. Og så denne løsning her, okay?
Så hej, jeg vil takke Don for at have sendt spørgsmålet ind, og hvis du hænger så længe, vil jeg takke dig for at komme forbi. Vi ses næste gang til endnu en netcast fra.
Download fil
Download eksempelfilen her: Podcast2098.xlsm
