Îmi plac listele personalizate în Excel. Sunt excelente pentru mânerul de umplere și pentru sortarea datelor într-o secvență diferită. Listele personalizate ar trebui să permită 254 de articole. Dar, dintr-un anumit motiv, un cititor întâmpină o situație în care Excel păstrează doar primele 38 de articole! Vom ajunge la fundul acestui mister.
Urmăriți videoclipul
- Don vrea să sorteze după o listă personalizată numerică!
- Va funcționa asta? Se pare că funcționează!
- Dar nu puteți importa celule numerice în caseta de dialog listă personalizată.
- Deci, încercați să tastați numere în caseta de dialog Listă personalizată…. Sunteți lovit cu o limită prostească de 255 de caractere atunci când tastați.
- WTH este limita? 254 articole? Aha - 254 de elemente, dar mai puțin de 2000 de caractere când adăugați virgula invizibilă între fiecare element
- Ați făcut câteva matematici text cu
=SUM(LEN())și Ctrl + Shift + Enter șiLEN(TEXTJOIN(",",True,Range)) - Soluție cu ABS pentru sortarea în acest caz special pentru Don
- Dar cea mai bună soluție … lucrul pe care Don trebuie să-l facă:
Transcriere video
Aflați Excel din Podcast, Episodul 2098: Limite ale listei personalizate.
Acesta este doar un lucru bizar când Don S, folosind Mac 2011, nu, așa că nu folosim nici măcar versiunea reală de Excel. Folosim versiunea falsă de Excel, încercând să sortăm după o listă personalizată, dar acceptă doar primele 38 de articole din listă. Și știu că este greșit, deoarece Excel poate gestiona până la 254 de articole din listă sau cel puțin asta credeam. În regulă, Don are numele jucătorului, numărul de câștiguri și apoi marja, ca la cât de departe erau de scor atunci.
Deci secvența corectă este, scorul perfect este 0 și apoi peste cu 1, sub cu 1 și peste cu 2, sub cu 2, peste cu 3, sub cu 3 și așa mai departe. Și Don încearcă să sorteze coloana Margină după această listă personalizată. Acum, nu am încercat niciodată asta, dar, hei, ar trebui să fie ușor de făcut. Deci, aici Secvența corectă: 0, 1 și apoi formula va fi minus valoarea chiar înainte de noi și apoi = numărul 2 de mai sus +1. Bine, acum că am acele două formule, ar trebui să pot naviga pe 201, prea departe acolo, dar este în regulă. Și ar trebui să avem secvența exactă de care avem nevoie până la 99 și -99. Deci, există setul nostru perfect de răspunsuri. Voi copia asta, așa că Ctrl + C pentru a copia și apoi lipiți ca valori. Inserați acele valori așa. Bine, așa că ar trebui să pot configura o listă personalizată care se va ocupa de asta, nu? Nici o problemă.Așa că mergem la Fișier, Opțiuni, Avansat, derulăm cu 83% până la capăt, alegem Editare liste personalizate și vom importa lista noastră. Ce? Celulele fără text simplu au fost ignorate. Nu ai voie să ai o listă personalizată plină de numere? Dar Don spune că acest lucru funcționează pentru primii 38. Ce se întâmplă cu asta? Ei bine, nu te mai gândi la asta de ceva vreme. Mi-am dat seama că Don nu trebuie să fi încercat să importe; el tocmai a scris acele numere în caseta de dialog.Mi-am dat seama că Don nu trebuie să fi încercat să importe; el trebuie să fi introdus acele numere în caseta de dialog.Mi-am dat seama că Don nu trebuie să fi încercat să importe; el tocmai a scris acele numere în caseta de dialog.
Iată ce voi face. Voi face Ctrl + C pentru a le copia pe toate, voi merge la Notepad și Inserați în Notepad ca acest Ctrl + V, apoi selectați totul: Editați, Selectați tot și Ctrl + C, reveniți la Excel, Fișier, Opțiuni, Avansat, 83% până la capăt, Editați liste personalizate și voi introduce acea listă aici ca și cum ar fi Ctrl + V. Bine și că toate funcționează, dar am făcut clic pe Adăugare, lungimea maximă pentru lista personalizată a fost depășită. Doar primele 255 de caractere vor fi salvate. Și când te uiți la acest lucru, cu siguranță, creează o listă personalizată care coboară doar la 38, -38, 39 și apoi la BAM! Ultimele 3, nu?
Deci, acest lucru este atât de bizar. De fapt, îmi permit să creez o listă personalizată cu numere, dar nu-mi permit să obțin 255. Adică funcționează. Funcționează și apoi dacă încercăm să sortăm aici; așa că vom spune Date, Sortare și sortare pe această listă personalizată, cea care merge doar la 39 faceți clic pe OK, faceți clic pe OK. Ei bine, dacă se află în listă, se sortează corect. Bine, deci pozitivul +6 arată înainte de -6; dar, odată ce ajungem la orice altceva decât 39, va fi sortat doar în secvențele care nu sunt în listă, deci va merge de la cel mai mic la cel mai mare aici. Deci, cineva ratat cu 67 de puncte este mai bun decât cineva ratat cu +42 puncte. Este doar complet înșelător.
Bine, și ce se întâmplă doar cu 38? Acum, sigur că există orice alt număr, așa că, știi, cred că va merge până în anii '30. Începem să ne placă acolo, nu? Sau acolo, unul dintre cei doi. Deci, ce este asta? Adică un total de 78 de articole. Și hei, știu că au permis - au 250 de forumuri pentru că vorbesc tot timpul despre clienți la seminariile mele, bine? Puteți avea 250 de forumuri, permiteți-mi să vă arăt. Deci articolul 1 și, desigur, putem folosi articolul de umplere pentru asta. Voi trage până la 254, așa. Acum, acestea nu sunt formule, deci ar trebui să putem face fișiere, opțiuni, avans, accesați Editați listele personalizate și vom importa lista respectivă, bine? Iată-l, BAM! Fără probleme, fără mesaj de eroare. Totul este grozav, totul este - Nu este grozav. Merge doar la articolul 234. Așteaptă, știu că poți avea 254.De ce se oprește la 234? E bizar, asta e bizar. Ce-i cu aia?
Deci, aici, știm că se reduce la articolul 234 chiar acolo. Bine acum, când tastam Articolele din listă, există un număr de caractere. Acolo era limita. Așa că mă întreb dacă există un număr de caractere care este limita aici = SUM (LEN al întregului grup de lucruri, apăsați CTRL + SHIFT ENTER și sunt 1764 de caractere - 234 de articole. Știu că puteți avea 254, am a făcut asta înainte.
Și să încercăm ceva mai nebunesc. Bine, să încercăm asta. Să încercăm în loc de articol să încercăm ceva mai mult. Deci 10 caractere un spațiu și apoi numărul 1, vom coborî 254 de rânduri. Și vom încerca să importăm această listă: deci Fișier, Opțiuni, Avansat, Editare liste personalizate, vom importa această listă. Fără mesaj de eroare. Se pare că a funcționat, dar scade doar la 140. Ce naiba are asta? Care este limita? Am crezut că ar putea fi 254. Deci, să vedem, câte caractere avem dacă ajungem la 140. Bine, așa că hai să lăsăm toate celelalte după aceasta și de fapt voi veni aici la această formulă și voi copia exact aceeași formulă peste. Bine, nu.
În acest moment, sunt destul de exasperat de echipa Excel. Ce se întâmplă, aici 1764 și aici 1852. Hei, Microsoft, care este limita? Exact care este limita? Ah, dar iată chestia. Ei trebuie să stocheze acest lucru ca o serie de șiruri delimitate, nu? Deci, iau toate articolele și apoi adaugă o virgulă după fiecare. Bine, deci aici, deoarece avem Office 365, putem folosi noua Text Join, deci = TEXTJOIN a tuturor celor cu virgulă între ele. Nu știu dacă este într-adevăr o virgulă sau nu. Ignorați că, apoi Adevărat, virgula și aceste elemente. Deci, obținem asta. Și, de fapt, vreau doar să știu lungimea întregului lucru. Deci, lungimea este 1997 și când fac același lucru aici, 1991. Oh! Deci, în mod clar, limita trebuie să fie de 2000 de caractere, inclusiv o virgulă invizibilă între fiecare element.
Toate acestea sunt destul de bizare. Bine, așa că am crezut întotdeauna că sunt 254 de articole, nu 254 de articole. Este vorba de 254 de obiecte, cu condiția să aibă mai puțin de 2.000 de caractere, cu condiția ca obiectele să nu fie prea lungi. Bine, deci doar pentru a-mi testa teoria, să folosim doar un spațiu 1 așa și vom apuca mânerul de umplere și vom trage. Acestea ar trebui să fie foarte frumoase și scurte pentru că - Și vom coborî la 255, 254. Hai să mergem la 255 pentru a-l testa.
Bine, așa că acum cu asta, dacă cer lungimea alăturării textului, 1421. Nicio problemă deloc. Așadar, selectați totul și Fișier, Opțiuni, Avansat, derulați până în jos, Editați liste personalizate, faceți clic pe Import. Bine, și opriți-vă până la 254. Bine, deci sunt 254 de articole cu condiția să aibă mai puțin de 2.000 de caractere, inclusiv o virgulă invizibilă după fiecare articol.
Știi, așa - dar înapoi la problema lui Don aici. Este deranjant faptul că caseta de dialog, dacă intrăm și începem să tastăm lucruri în caseta de dialog în loc să avem un element de 2.000 de caractere, are 255 de caractere pe ea. Bine, deci Don nu are cum să introducă acest lucru și atunci când încercăm să importăm numere, refuză să importe numerele. Nu spune nicio afacere. Ceva care nu este text simplu nu va funcționa, bine?
Deci, singurul lucru pe care i l-am sugerat lui Don este o soluție alternativă. Zic, hei, haideți să ieșim aici și să adăugăm o coloană de ajutor și această coloană de ajutor va fi - dacă Valoarea absolută a acestui număr, bine. Și vom face dublu clic pentru a distruge acest lucru și apoi ceea ce veți face este să sortați Descrescând după valoarea absolută, o setare după valoarea absolută, bine. Și apoi 4, 6 și apoi -6, bine, toate acestea sunt doar sortate împreună, să știi. Deci nu este rău, cred că ceea ce ați putea face cu adevărat este să puteți sorta după Helper și apoi să adăugați un nivel și apoi să sortați după marjă, coborând cel mai mare la cel mai mic, faceți clic pe OK și asta va obține ceea ce caută Don. Deci, toate +6 vor apărea înainte de -6, apoi 8 și apoi -11, bine. Știi, dar asta este o bătaie de cap. Ca, hei, Microsoft.De ce trebuie să mergem la toate aceste probleme? De ce ne-ați lăsa să introducem 2.000 de caractere în caseta de dialog sau chiar mai bine, deoarece se pare că funcționează pentru a avea numere în lista personalizată, adică funcționează aici, de ce nu ne lăsați să-l importăm?
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.
Ei bine, iată-te. Don, una dintre întrebările mai nebunești pe care le-am auzit vreodată în Excel și am descoperit cel puțin 2 - Ei bine, cu siguranță o eroare în Excel că caseta de dialog Listă personalizată nu va permite mai mult de 255 de caractere. Și apoi acest lucru bizar că aparent va sorta o listă personalizată după numere, dar nu vă va permite să importați numere. Bine, așa că voi apela numărul de eroare 2. Și apoi această soluție aici, bine?
Deci, hei, vreau să-i mulțumesc lui Don pentru că mi-a trimis întrebarea și dacă stai atât de mult, vreau să-ți mulțumesc că ai trecut pe aici. Ne vedem data viitoare pentru un alt netcast de la.
Descărcare fișier
Descărcați exemplarul de fișier aici: Podcast2098.xlsm
