Volim prilagođene popise u Excelu. Izvrsne su za ručku za punjenje i za sortiranje podataka u drugačiji slijed. Prilagođeni popisi trebali bi dopustiti 254 stavke. No, iz nekog razloga čitatelj se susreće sa situacijom u kojoj Excel zadržava samo prvih 38 predmeta! Doći ćemo do dna ove misterije.
Gledaj video
- Don želi sortirati prema prilagođenom popisu koji je numerički!
- Hoće li ovo uspjeti? Čini se da djeluje!
- Ali ne možete uvesti numeričke ćelije u dijaloški okvir prilagođenog popisa.
- Dakle, pokušajte upisati brojeve u dijaloški okvir Prilagođeni popis …. Dobijate glupo ograničenje od 255 znakova prilikom tipkanja.
- WTH je granica? 254 predmeta? Aha - 254 predmeta, ali manje od 2000 znakova kada dodate nevidljivi zarez između svake stavke
- Jeste li malo matematičke matematike pomoću
=SUM(LEN())i Ctrl + Shift + Enter iLEN(TEXTJOIN(",",True,Range)) - Zaobilazno rješenje s ABS-om za sortiranje u ovom slučaju za Don
- Ali najbolje rješenje … stvar koju Don treba učiniti:
Prijepis videozapisa
Naučite Excel iz Podcasta, epizoda 2098: Ograničenja prilagođenih popisa.
Ovo je samo bizarno kada Don S, koristeći Mac 2011, zar ne, pa čak ni ne koristimo pravu verziju Excela. Koristimo lažnu verziju Excela, pokušavajući sortirati prema prilagođenom popisu, ali prihvaća samo prvih 38 stavki na popisu. I znam da je to pogrešno, jer Excel može obraditi do 254 stavke na popisu ili sam barem tako mislio. Dobro, i Don ima ime igrača, broj pobjeda, a zatim i marginu, kao koliko su tada bili daleko od rezultata.
Dakle, pravilan slijed je, savršeni rezultat je 0, a zatim preko 1, ispod 1 i više od 2, ispod 2, preko 3, ispod 3 i tako dalje. I Don pokušava sortirati stupac Margin prema ovom Prilagođenom popisu. Sad to nikad nisam probao, ali, hej, to bi trebalo biti lako učiniti. Dakle, ovdje Ispravan slijed: 0, 1 i tada će formula biti minus vrijednost neposredno prije nas, a zatim = broj 2 iznad +1. U redu, sad kad imam te dvije formule, trebao bih moći kružiti kroz 201, predaleko, ali to je u redu. I trebali bismo imati točan slijed koji nam treba do 99 i -99. Dakle, tu je naš savršeni skup odgovora. Kopirat ću to pa Ctrl + C za kopiranje, a zatim Zalijepiti kao vrijednosti. Zalijepite te vrijednosti tako. U redu, pa trebao bih biti u mogućnosti postaviti prilagođeni popis koji će to riješiti, zar ne? Nema problema.Idemo na Datoteka, Opcije, Napredno, pomaknite se 83% do kraja, odaberite Uredi prilagođene popise i uvest ćemo naš popis. Što? Stanice bez jednostavnog teksta bile su zanemarene. Ne smijete imati prilagođeni popis prepun brojeva? Ali Don kaže da ovo djeluje za prvih 38. Što je s tim? Pa, nemojte o ovome razmišljati već neko vrijeme. Shvatio sam da Don sigurno nije pokušao uvoziti; mora da je upravo tipkao te brojeve u dijaloški okvir.Shvatio sam da Don sigurno nije pokušao uvoziti; mora da je upravo tipkao te brojeve u dijaloški okvir.Shvatio sam da Don sigurno nije pokušao uvoziti; mora da je upravo tipkao te brojeve u dijaloški okvir.
Pa evo što ću učiniti. Učinit ću Ctrl + C da kopiram sve one, idem na Notepad i Zalijepi u Notepad poput ovog Ctrl + V, a zatim odaberite sve: Edit, Select all i Ctrl + C, vratite se na Excel, Datoteka, Opcije, Napredno, 83% do kraja, Uredi prilagođene popise i ovdje ću upisati taj popis kao da je Ctrl + V. U redu, i da sve funkcionira, ali kliknuli smo Dodaj, premašena je maksimalna duljina prilagođenog popisa. Spremit će se samo prvih 255 znakova. I kad ovo pogledate, sigurno oni stvaraju prilagođeni popis koji se svodi samo na 38, -38, 39, a zatim na BAM! Zadnja 3, zar ne?
Dakle, ovo je tako bizarno. Zapravo mi dopuštaju da stvorim prilagođeni popis s brojevima, ali ne dopuštaju mi da ih dobijem 255. Mislim, djeluje. Uspijeva i ako pokušamo ovdje sortirati; pa ćemo na ovom Prilagođenom popisu reći onaj Podaci, razvrstaj i razvrstaj, onaj koji ide samo na 39, kliknite U redu, kliknite U redu. Pa, ako je na popisu, sortira se ispravno. U redu, dakle pozitivni +6 pokazuju prije -6; ali kad se jednom spustimo na nešto više od 39, to će se sortirati u sekvencama kojih nema na popisu, pa će ovdje ići od najmanjeg do najvećeg. Dakle, netko je promašio za 67 bodova bolji je od nekoga tko je promašio za +42 boda. Jednostavno je potpuno zeznut.
U redu, a što je sa ovim samo 38? Sigurno da postoji još svaki drugi broj, pa, znate, pretpostavljam da će se spustiti u 30-e. Tamo nam se sviđa, zar ne? Ili tamo, jedno od to dvoje. Pa, što je to? To je ukupno 78 predmeta. I hej, znam da su dopustili - imati 250 foruma jer stalno govorim o kupcima na svojim seminarima, u redu? Možete imati 250 foruma, da vam pokažem. Dakle, stavka 1, i naravno, za to možemo koristiti stavku za punjenje. Povući ću na 254, tako. Sada to nisu formule, pa bismo trebali moći raditi File, Options, Advance, prijeći na Edit Custom Lists i mi ćemo taj popis uvesti, u redu? Eto ga, BAM! Nema problema, nema poruke o pogrešci. Sve je super, sve je - Nije sjajno. Odnosi se samo na stavku 234. Čekaj, znam da možeš imati 254.Zašto se zaustavlja na 234? To je bizarno, to je bizarno. Što je s tim?
Dakle, ovdje znamo da se to svodi samo na točku 234. U redu, kad smo tipkali stavke na popisu, postoji određeni broj znakova. Postojala je granica. Stoga se pitam postoji li neki broj znakova koji je ovdje ograničen = ZBOR (LEN cijele te hrpe stvari, pritisnite CTRL + SHIFT ENTER, a to je 1764 znaka - 234 predmeta. I znam da možete imati 254, ja imam učinio ovo prije.
I probajmo nešto luđe. U redu, probajmo ovo. Pokušajmo umjesto predmeta pokušajmo nešto duže. Dakle, 10 znakova PROSTOR, a zatim broj 1, spustit ćemo se za 254 reda. Pokušat ćemo i uvesti ovaj popis: dakle Datoteka, Opcije, Napredno, Uredi prilagođene popise, uvest ćemo ovaj popis. Nema poruke o pogrešci. Čini se da je uspjelo, ali svodi se samo na 140. Što je dovraga s tim? Koja je granica? Mislio sam da bi moglo biti 254. Pa da vidimo, koliko likova imamo ako se spustimo na 140. U redu, pa ostavimo sve ostalo nakon ovoga i zapravo ću doći ovdje do ove formule i kopirati potpuno istu formulu nad. U redu, ne.
U ovom sam trenutku prilično ogorčen zbog Excel tima. Što ima, ovdje 1764, a ovdje 1852. Hej, Microsoft, koja je granica? Koja je točno granica? Ah, ali evo u čemu je stvar. Sigurno to spremaju kao niz odvojenih nizova, u redu? Dakle, oni uzimaju sve stavke, a zatim dodaju zarez nakon svake. U redu, pa ovdje, budući da imamo Office 365, možemo koristiti novo udruživanje teksta, dakle = TEXTJOIN svih onih sa zarezom između. Ne znam je li to stvarno zarez ili ne. Zanemarite to, zatim True, zarez i ove stavke. Dakle, shvatili smo. I zapravo samo želim znati dužinu cijele te stvari. Dakle, dužina je 1997, a kad to isto napravim ovdje, 1991. Oh! Dakle, ograničenje mora biti 2000 znakova, uključujući nevidljivi zarez između svake stavke.
Sve je ovo prilično bizarno. U redu, uvijek sam mislio da je to 254 predmeta, a ne 254 predmeta. To je 254 predmeta, pod uvjetom da ima manje od 2000 znakova, pod uvjetom da stavke nisu preduge. U redu, samo da bismo testirali moju teoriju, samo upotrijebimo razmak 1 i uhvatit ćemo ručku za popunjavanje i povući. Ovo bi trebalo biti jako lijepo i kratko jer - I mi ćemo se spustiti na 255, 254. Idemo na 255 da to testiramo.
U redu, sada s ovim, ako tražim duljinu udruživanja teksta, 1421. Nema problema. Dakle, odaberite cijelu stvar i Datoteka, Opcije, Napredno, pomaknite se sve do dna, Uredite prilagođene popise, kliknite Uvezi. U redu, i isključite skroz dolje na 254. U redu, znači 254 predmeta pod uvjetom da je manje od 2000 znakova, uključujući nevidljivi zarez nakon svake stavke kako to funkcionira.
Znate, pa - ali vratimo se na Donov problem ovdje. Zasigurno je iritantno da dijaloški okvir, ako samo uđemo i počnemo tipkati stvari u dijaloški okvir, umjesto da ima 2000 znakovnih elemenata, na sebi ima 255 znakova. Dobro, tako da Don nema načina da ovo upiše i kada pokušamo uvesti brojeve, odbija uvoziti brojeve. Kaže da nema dogovora. Sve što nije običan tekst neće uspjeti, u redu?
Dakle, jedina stvar koju sam Donu predložio je alternativno rješenje. Kažem, hej, hajde samo da izađemo ovdje i dodamo stupac pomoćnika i ovaj stupac pomoćnika bit će - ako je Apsolutna vrijednost tog broja, u redu. I dvaput ćemo kliknuti da to spustimo, a onda ćete samo sortirati Silazno prema Apsolutnoj vrijednosti, postavku prema Apsolutnoj vrijednosti, u redu. A onda se 4, 6, a zatim -6, u redu, sve se to samo sredi, znaš. Dakle, nije loše, pretpostavljam da biste mogli stvarno sortirati pomoćnika, a zatim dodati razinu, a zatim sortirati po margini, spuštajući se Najveće do najmanje, kliknite U redu i to će dobiti ono što Don traži. Dakle, svi +6 pojavit će se prije -6, a zatim 8 i onda -11, u redu. Znate, ali ovo je gnjavaža. Kao, hej, Microsoft.Zašto moramo ići na sve ove gnjavaže? Zašto biste nam dopustili da upišemo 2.000 znakova u dijaloški okvir ili još bolje, budući da očito uspijeva imati brojeve na prilagođenom popisu, mislim da ovdje radi, zašto nam ne dopuštate da ga uvozimo?
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.
Pa, eto. Don, jedno od luđih pitanja koja sam ikad čuo u Excelu i otkrili smo najmanje 2 - Pa, definitivno 1 grešku u Excelu da dijaloški okvir Prilagođeni popis neće dopustiti više od 255 znakova. A onda bizarna stvar koja će očito sortirati prilagođeni popis po brojevima, ali neće vam dopustiti uvoz brojeva. U redu, nazvat ću tu grešku broj 2. I onda ovo zaobilazno rješenje ovdje, u redu?
Pa, hej, želim zahvaliti Donu što je poslao pitanje i ako se toliko zadržavate, želim vam zahvaliti što ste navratili. Vidimo se sljedeći put za još jedan prijenos od.
Preuzmi datoteku
Preuzmite datoteku uzorka ovdje: Podcast2098.xlsm
