Mám rád vlastné zoznamy v programe Excel. Sú vynikajúce pre rukoväť výplne a na triedenie údajov do inej postupnosti. Vlastné zoznamy by mali obsahovať 254 položiek. Ale z nejakého dôvodu sa čitateľ stretáva so situáciou, keď program Excel uchováva iba prvých 38 položiek! Dostaneme sa na dno tejto záhady.
Pozeraj video
- Don chce triediť podľa vlastného číselného zoznamu!
- Bude to fungovať? Zdá sa, že to funguje!
- Ale nemôžete importovať číselné bunky do dialógového okna vlastného zoznamu.
- Skúste teda zadať čísla do dialógového okna Vlastný zoznam …. Pri písaní sa vám zdá bláznivý limit 255 znakov.
- WTH je limit? 254 položiek? Aha - 254 položiek, ale menej ako 2 000 znakov, keď medzi každú položku pridáte neviditeľnú čiarku
- Robili ste nejaký matematický text pomocou
=SUM(LEN())a Ctrl + Shift + Enter aLEN(TEXTJOIN(",",True,Range)) - Riešenie pomocou ABS na triedenie v tomto konkrétnom prípade pre Dona
- Ale najlepšie riešenie … vec, ktorú Don musí urobiť:
Prepis videa
Naučte sa Excel z Podcastu, epizóda 2098: Vlastné limity zoznamu.
Toto je len bizarné, keď Don S, používate Mac 2011, správne, takže nepoužívame ani skutočnú verziu Excelu. Používame falošnú verziu programu Excel a pokúšame sa triediť podľa vlastného zoznamu, ale akceptuje iba prvých 38 položiek v zozname. A viem, že je to nesprávne, pretože program Excel dokáže spracovať až 254 položiek v zozname, alebo aspoň som si to myslel. Dobre, a Don má meno hráča, počet výhier a potom maržu, napríklad to, ako ďaleko boli potom od skóre.
Správna postupnosť teda je, že dokonalé skóre je 0 a potom viac ako 1, menej ako 1 a viac ako 2, menej ako 2, viac ako 3, menej ako 3 atď. A Don sa snaží zoradiť stĺpec Okraj podľa tohto vlastného zoznamu. Teraz som to nikdy neskúšal, ale hej, malo by to byť ľahké. Takže tu správna postupnosť: 0, 1 a potom bude vzorec mínus hodnota tesne pred nami a potom = číslo 2 nad +1. Dobre, teraz, keď mám tieto dva vzorce, by som mal byť schopný križovať až 201, príliš ďaleko, ale to je v poriadku. Mali by sme mať presnú postupnosť, ktorú potrebujeme, až do 99 a -99. Existuje teda naša perfektná sada odpovedí. Idem to skopírovať tak, že Ctrl + C skopírujem a potom prilepím ako hodnoty. Tieto hodnoty prilepte takto. Dobre, takže by som mal byť schopný vytvoriť vlastný zoznam, ktorý to bude riešiť, však? Žiaden problém.Prejdeme teda na položky Súbor, Možnosti, Pokročilé, posunieme sa o 83% úplne dole, zvolíme Upraviť vlastné zoznamy a náš zoznam naimportujeme. Čo? Bunky bez jednoduchého textu boli ignorované. Nemáte povolenie mať vlastný zoznam plný čísel? Ale Don hovorí, že to funguje pre prvých 38. Čo je s tým? No, nad tým chvíľu nerozmýšľaj. Uvedomil som si, že Don sa nemusel snažiť importovať; musel práve vypisovať tieto čísla do dialógového okna.Uvedomil som si, že Don sa nemusel snažiť importovať; musel práve vypisovať tieto čísla do dialógového okna.Uvedomil som si, že Don sa nemusel snažiť importovať; musel práve vypisovať tieto čísla do dialógového okna.
Takže tu budem robiť. Chystám sa Ctrl + C skopírovať všetky tieto položky, idem do Poznámkového bloku a prilepím ho do Poznámkového bloku takto Ctrl + V a potom vyberiem všetko: Upraviť, Vybrať všetko a Ctrl + C, vrátiť sa k Excel, Súbor, Možnosti, Pokročilé, 83% úplne dole, Upraviť vlastné zoznamy a tento zoznam napíšem ako Ctrl + V. Dobre, a že všetko funguje, ale neklikli sme na Pridať, bola prekročená maximálna dĺžka vlastného zoznamu. Uloží sa iba prvých 255 znakov. A keď sa na to pozriete, tak určite vytvárajú vlastný zoznam, ktorý klesá iba na čísla 38, -38, 39 a potom BAM! Posledné 3, však?
Je to teda také čudné. Vlastne mi umožňujú vytvoriť si vlastný zoznam s číslami, ale neumožňujú mi získať 255. Myslím, že to funguje. Funguje to a potom, ak sa tu skutočne pokúsime triediť; takže povieme Údaje, Zoradiť a zoradiť v tomto vlastnom zozname, ktorý má iba 39, kliknite na OK, potom na OK. Ak je v zozname, je zoradený správne. Dobre, takže pozitívnych +6 sa zobrazuje pred -6; ale akonáhle sa dostaneme k niečomu nad 39, bude to len triediť sekvencie, ktoré nie sú v zozname, takže to bude od najmenších k najväčším. Niekto teda minul o 67 bodov je lepší ako niekto, kto premeškal +42 bodov. Je to jednoducho úplne nepríjemné.
Dobre, a čo sa deje s týmto, iba na 38? Teraz, určite, existuje ešte každé ďalšie číslo, takže, viete, myslím, že to bude klesať do 30. rokov. Dostávame sa dole, aby sa nám tam páčilo, však? Alebo tam, jeden z tých dvoch. Takže, čo to je? To je spolu 78 položiek. A hej, viem, že povolili - mať 250 fór, pretože na svojich seminároch neustále hovorím o zákazníkoch, dobre? Môžete mať 250 fór, ukážem vám to. Takže položka 1 a samozrejme na to môžeme použiť položku Plnenie. Potiahnem asi na 254. Teraz to nie sú vzorce, takže by sme mali mať možnosť robiť položky Súbor, Možnosti, Pokročilé, pokračovať v editácii vlastných zoznamov a my tento zoznam importujeme, dobre? A je to, BAM! Žiadne problémy, žiadne chybové hlásenie. Všetko je super, všetko je - Nie je to skvelé. Ide to iba k položke 234. Počkajte, viem, že ich môžete mať 254.Prečo sa zastavuje na čísle 234? To je bizarné, to je bizarné. Čo s tým?
Takže tu vieme, že to ide iba k položke 234 práve tam. Dobre, teraz, keď sme písali položky do zoznamu, bolo tu niekoľko znakov. Bol tam limit. Zaujímalo by ma, či je tu nejaký počet znakov, ktorý je tu limitný = SUMA (LEN celej tej kopy vecí, stlačte CTRL + SHIFT ENTER a je to 1764 znakov - 234 položiek. A viem, že môžete mať 254, ja urobil predtým.
A skúsme niečo šialenejšie. Dobre, skúsme to. Skúsme namiesto položky skúsme niečo dlhšie. Takže 10 znakov MEDZERNÍK a potom číslo 1, pôjdeme dole 254 riadkov. A pokúsime sa importovať tento zoznam: takže File, Options, Advanced, Edit Custom Lists, budeme importovať tento zoznam. Žiadna chybová správa. Zdá sa, že to fungovalo, ale klesá iba na 140. Čo je sakra s tým? Aký je limit? Myslel som, že to môže byť 254. Takže sa pozrime, koľko znakov máme, ak sa dostaneme k 140. Dobre, takže po tomto nechajme všetko ostatné a v skutočnosti sem prídem k tomuto vzorcu a skopírujem presne ten istý vzorec cez. Dobre, nie.
V tejto chvíli som z tímu Excel dosť rozčúlený. Čo sa deje, tu 1764 a tu 1852. Hej, Microsoft, aký je limit? Aký je vlastne limit? Aha, ale tu je vec. Musia to ukladať ako sériu ohraničených reťazcov, dobre? Berú teda všetky položky a potom za každú pridávajú čiarku. Dobre, takže keď už máme Office 365, môžeme používať nové Textové spojenie, takže = TEXTJOIN všetkých tých, medzi ktorými je čiarka. Neviem, či je to skutočne čiarka alebo nie. Ignorujte to, potom Pravda, čiarka a tieto položky. Takže to chápeme. A vlastne by som chcel vedieť iba dĺžku celej tej veci. Takže dĺžka je 1997 a keď urobím to isté tu, 1991. Oh! Je zrejmé, že limit musí byť 2 000 znakov vrátane neviditeľnej čiarky medzi každou položkou.
To všetko je dosť bizarné. Dobre, takže som si vždy myslel, že je to 254 položiek, nie je to 254 položiek. Je to 254 položiek, za predpokladu, že majú menej ako 2 000 znakov, za predpokladu, že položky nie sú príliš dlhé. Dobre, tak aby sme si otestovali moju teóriu, použijeme iba taký priestor 1 a chytíme Fill Handle a potiahneme. Mali by byť naozaj pekné a krátke, pretože - A pôjdeme na 255, 254. Poďme na 255, aby sme to otestovali.
Dobre, takže teraz, keď žiadam o dĺžku spojenia textu, 1421. Vôbec žiadny problém. Vyberte teda celú vec a položky Súbor, Možnosti, Pokročilé, prejdite úplne dole, Upraviť vlastné zoznamy, kliknite na tlačidlo Importovať. V poriadku, a úplne vypnite na 254. V poriadku, takže je to 254 položiek za predpokladu, že má menej ako 2 000 znakov vrátane neviditeľnej čiarky za každou položkou, ako to funguje.
Vieš, takže - ale späť k Donovmu problému tu. Je určite nepríjemné, že ak v dialógovom okne iba vstúpime a začneme do neho písať, namiesto toho, aby mal 2 000 znakový prvok, má v sebe 255 znakov. Dobre, takže Don nemá spôsob, ako zadať túto vec, a keď sa pokúsime importovať čísla, odmietne ich importovať. Hovorí to bez dohody. Nič, čo nie je obyčajný text, nebude fungovať, dobre?
Jednu vec, ktorú som Donovi navrhol, je teda alternatívne riešenie. Hovorím, hej, poďme sem a pridajme Pomocný stĺpec a tento Pomocný stĺpec bude - ak je absolútna hodnota tohto čísla, v poriadku. A dvakrát klikneme, aby sme to zostrelili, a potom to, čo urobíte, je, že iba zoradíte zostupne podľa absolútnej hodnoty, nastavenie podľa absolútnej hodnoty, v poriadku. A potom tie 4, 6 a potom -6, v poriadku, všetky sú len zoradené dohromady, viete. Takže to nie je zlé, myslím, že by ste skutočne mohli urobiť, že by ste ich mohli zoradiť podľa Pomocníka a potom Pridať úroveň a potom Zoradiť podľa okraja, zostupovať od najväčšej po najmenšiu, kliknúť na OK a tým získate to, čo Don hľadá. Takže všetky +6 sa zobrazia pred -6 a potom 8 a potom -11, v poriadku. Viete, ale toto je problém. Nech sa páči, Microsoft.Prečo musíme ísť do toho všetkého? Prečo by ste nám umožnili napísať 2 000 znakov do dialógového okna alebo ešte lepšie, pretože to zjavne funguje tak, že majú čísla vo vlastnom zozname, myslím tým, že to tu funguje, prečo nám to nedovolíte importovať?
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.
No a máš to. Don, jedna z bláznivejších otázok, aké som kedy v Exceli počul, a odhalili sme minimálne 2 - No, určite 1 chybu v Exceli, ktorá v dialógovom okne Vlastný zoznam neumožní viac ako 255 znakov. A potom táto bizarná vec, ktorá zjavne zoradí Vlastný zoznam podľa čísel, ale nedovolí vám importovať čísla. Dobre, tak zavolám tú chybu číslo 2. A potom toto riešenie tu, dobre?
Takže hej, chcem sa poďakovať Donu za zaslanie otázky a ak sa tu budeš motať tak dlho, chcem sa ti poďakovať, že si sa zastavil. Uvidíme sa nabudúce pri ďalšom netcaste z.
Stiahnuť súbor
Stiahnutie vzorového súboru nájdete tu: Podcast2098.xlsm
