Jeg elsker tilpassede lister i Excel. De er gode for fyllhåndtaket og for å sortere data i en annen sekvens. Tilpassede lister skal tillate 254 elementer. Men av en eller annen grunn støter en leser på en situasjon der Excel bare holder de første 38 elementene! Vi kommer til bunns i dette mysteriet.
Se på video
- Don vil sortere etter en tilpasset liste som er numerisk!
- Vil dette fungere? Det ser ut til å fungere!
- Men du kan ikke importere numeriske celler til dialogboksen for den tilpassede listen.
- Så prøv å skrive tall i dialogboksen Egendefinert liste…. Du blir rammet av en tåpelig grense på 255 tegn når du skriver.
- WTH er grensen? 254 varer? Aha - 254 elementer, men mindre enn 2000 tegn når du legger til det usynlige kommaet mellom hvert element
- Gjorde litt tekstmatematikk med
=SUM(LEN())og Ctrl + Shift + Enter ogLEN(TEXTJOIN(",",True,Range)) - Løsning med ABS for sortering i dette tilfellet for Don
- Men den beste løsningen … tingen Don trenger å gjøre:
Videoutskrift
Lær Excel fra Podcast, Episode 2098: Limiter for tilpassede lister.
Dette er bare bisarrt når Don S bruker Mac 2011, så vi bruker ikke engang den virkelige versjonen av Excel. Vi bruker den falske versjonen av Excel, og prøver å sortere etter en tilpasset liste, men den godtar bare de første 38 elementene i listen. Og jeg vet at det er galt fordi Excel kan håndtere opptil 254 elementer i listen, eller i det minste det jeg trodde. Greit, og Don har Player Name, # of Wins og deretter Margin, som hvor langt unna poengsummen var da.
Så den rette sekvensen er, den perfekte poengsummen er 0 og deretter over med 1, under av 1, og over av 2, under av 2, over av 3, under av 3 og så videre. Og Don prøver å sortere Margin-kolonnen etter denne egendefinerte listen. Nå har jeg aldri prøvd det, men hei, det skal være enkelt å gjøre. Så herover Riktig sekvens: 0, 1 og da vil formelen være minus verdien like før oss og da = tallet 2 over +1. Greit, nå som jeg har de to formlene, burde jeg kunne cruise ned gjennom 201, for langt der, men det er greit. Og vi bør ha den nøyaktige sekvensen vi trenger ned til 99 og -99. Så det er vårt perfekte sett med svar. Jeg skal kopiere det så Ctrl + C for å kopiere og deretter Lim inn som verdier. Lim inn disse verdiene slik. Ok, så jeg burde kunne sette opp en tilpasset liste som skal håndtere dette, ikke sant? Ikke noe problem.Så vi går til Fil, Alternativer, Avansert, blar 83% helt ned, velger Rediger egendefinerte lister, og vi importerer listen vår. Hva? Celler uten enkel tekst ble ignorert. Du har ikke lov til å ha en tilpasset liste full av tall? Men Don sier at dette fungerer de første 38. Hva skjer med det? Vel, har ikke tenkt på dette på en stund. Jeg skjønte at Don ikke må ha prøvd å importere; han har nettopp skrevet inn disse tallene i dialogboksen.Jeg skjønte at Don ikke må ha prøvd å importere; han har nettopp skrevet inn disse tallene i dialogboksen.Jeg skjønte at Don ikke må ha prøvd å importere; han har nettopp skrevet inn disse tallene i dialogboksen.
Så her er hva jeg skal gjøre. Jeg skal gjøre Ctrl + C for å kopiere alle disse, jeg skal gå til Notisblokk og lime inn Notisblokk som denne Ctrl + V, og deretter velge alt: Rediger, Velg alt og Ctrl + C, kom tilbake til Excel, File, Options, Advanced, 83% helt ned, Edit Custom Lists, og jeg skal skrive den listen inn her som om den er Ctrl + V. OK, og at alt fungerer, men vi klikket på Legg til, maksimal lengde for tilpasset liste er overskredet. Bare de første 255 tegnene lagres. Og når du ser på dette, lager de helt sikkert en tilpasset liste som bare går ned i 38, -38, 39 og deretter BAM! De siste 3, ikke sant?
Så dette er så bisarrt. De lar meg faktisk lage en egendefinert liste med tall, men de lar meg ikke få 255. Jeg mener, det fungerer. Det fungerer, og hvis vi faktisk prøver å sortere herover; så vi vil si Data, Sorter og sorter på denne egendefinerte listen, den som bare går til 39, klikk OK, klikk OK. Vel, hvis det er på listen, sorterer det riktig. Greit, så det positive +6 vises før -6; men så når vi kommer til noe utover 39, kommer det bare til å sortere i sekvensene som ikke er på listen, så det går fra minste til største her. Så, noen savnet med 67 poeng er bedre enn noen som savnet +42 poeng. Det er bare helt skrudd.
Ok, og hva skjer med at dette bare går til 38? Nå, det er sikkert alle andre tall, så du vet, jeg antar at det kommer ned i 30-årene. Vi kommer til å like der, ikke sant? Eller der, en av de to. Så hva er det? Det er totalt 78 varer. Og hei, jeg vet at de tillot det - har 250 fora fordi jeg snakker om kunder hele tiden i seminarene mine, ok? Du kan ha 250 fora, la meg vise deg. Så Item 1, og selvfølgelig kan vi bruke Filling Item til det. Jeg drar ned til 254, slik. Nå er det ikke formler, så vi skal kunne gjøre File, Options, Advance, gå videre til Edit Custom Lists, og vi vil importere den listen, ok? Der er det, BAM! Ingen problemer, ingen feilmelding. Alt er bra, alt er - Det er ikke bra. Det går bare til punkt 234. Vent, jeg vet at du kan ha 254.Hvorfor stopper det 234? Det er bisarrt, det er bisarrt. Hva skjer med det?
Så her vet vi at det bare går ned til punkt 234 akkurat der. Ok, når vi skrev elementene i listen, er det noe antall tegn. Det var grensen. Så jeg lurer på om det er et antall tegn som er grensen her = SUM (LENG av hele denne mengden ting, trykk CTRL + SKIFT ENTER, og det er 1764 tegn - 234 ting. gjort dette før.
Og la oss prøve noe galere. Ok, la oss prøve dette. La oss prøve i stedet for element, la oss prøve noe lenger. Så 10 tegn et mellomrom og deretter nummer 1, vi går ned 254 rader. Og vi prøver å importere denne listen: så File, Options, Advanced, Edit Custom Lists, vi importerer denne listen. Ingen feilmelding. Virker som om det fungerte, men det går bare ned til 140. Hva pokker er det med det? Hva er grensen? Jeg trodde det kunne være 254. Så la oss se, hvor mange tegn vi har hvis vi kommer ned til 140. Ok, så la oss la alt annet etter dette, og faktisk kommer jeg hit til denne formelen og kopierer nøyaktig samme formel over. Ok, nei.
På dette tidspunktet er jeg ganske oppgitt over Excel-teamet. Hva skjer, her 1764 og her 1852. Hei, Microsoft, hva er grensen? Nøyaktig hva er grensen? Ah, men her er tingen. De må lagre dette som en serie avgrensede strenger, ok? Så de tar alle elementene, og deretter legger de til et komma etter hver. OK, så her siden vi har Office 365, kan vi bruke den nye Text Join, så = TEXTJOIN av alle de som har et komma mellom. Jeg vet ikke om det virkelig er et komma eller ikke. Ignorer det, så True, komma og disse elementene. Så vi får det. Og faktisk vil jeg bare vite lengden på det hele. Så lengden er 1997, og når jeg gjør det samme her borte, 1991. Åh! Så klart må grensen være 2000 tegn inkludert et usynlig komma mellom hvert element.
Dette er ganske bisarrt. Ok, så jeg trodde alltid det var 254 varer, det er ikke 254 varer. Det er 254 varer, forutsatt at det er mindre enn 2000 tegn, forutsatt at varene ikke er for lange. Greit, så bare for å teste teorien min, la oss bare bruke et mellomrom 1 sånn, så tar vi i Fill Handle og drar. Disse skal være veldig fine og korte fordi - Og vi går ned til 255, 254. La oss gå til 255 for å teste det.
Ok, så nå med dette, hvis jeg ber om lengden på teksten sammenføyning, 1421. Ikke noe problem i det hele tatt. Så velg det hele og Fil, Alternativer, Avansert, bla helt ned til bunnen, Rediger egendefinerte lister, klikk Importer. OK, og slå av hele veien ned til 254. OK, så det er 254 elementer forutsatt at det er mindre enn 2000 tegn inkludert et usynlig komma etter hvert element, hvordan dette fungerer.
Du vet det, men - men tilbake til Dons problem her. Det er absolutt irriterende at dialogboksen, hvis vi bare går inn og begynner å skrive ting i dialogboksen i stedet for å ha et 2000 tegnelement, har den 255 tegn på seg. OK, så Don har ingen måte å skrive denne tingen inn, og når vi prøver å importere tall, nekter den å importere tallene. Det sier ingen avtale. Alt som ikke er ren tekst, kommer ikke til å fungere, ok?
Så, den ene tingen jeg foreslo til Don er en alternativ løsning. Jeg sier, hei, la oss bare komme ut her og legge til en hjelpekolonne, og denne hjelpekolonnen vil være den - hvis den absolutte verdien til dette tallet, ok. Og vi dobbeltklikker for å skyte det ned, og det du skal gjøre er at du bare skal sortere Descending by the Absolute Value, en innstilling av Absolute Value, ok. Og så 4, 6 og deretter -6, ok, disse blir bare sortert sammen, vet du. Så det er ikke ille, jeg antar at det du virkelig kan gjøre er at du kan sortere etter hjelperen og deretter legge til et nivå og deretter sortere etter margen, synkende største til minste, klikk på OK, så får du det Don leter etter. Så, alt +6 vil vises før -6, og deretter 8 og deretter -11, ok. Du vet, men dette er et problem. Som, hei, Microsoft.Hvorfor må vi gjøre alt dette bryet? Hvorfor vil du la oss skrive 2000 tegn i dialogboksen eller enda bedre, siden det tilsynelatende fungerer å ha tall i den egendefinerte listen, mener jeg det fungerer her, hvorfor vil du ikke la oss 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.
Vel, der går du. Don, et av de sprøere spørsmålene jeg noensinne har hørt i Excel, og vi har avdekket minst 2 - Vel, definitivt en feil i Excel om at dialogboksen Custom List ikke tillater mer enn 255 tegn. Og så denne bisarre tingen som tilsynelatende vil sortere en tilpasset liste etter tall, men de lar deg ikke importere tall. Ok, så jeg skal ringe den feil nummer 2. Og så denne løsningen her, ok?
Så hei, jeg vil takke Don for at du sendte spørsmålet inn, og hvis du henger så lenge, vil jeg takke deg for at du var innom. Vi sees neste gang for en ny netcast fra.
Last ned fil
Last ned eksempelfilen her: Podcast2098.xlsm
