Mám rád vlastní seznamy v Excelu. Jsou skvělé pro popisovač výplně a pro třídění dat do jiné sekvence. Vlastní seznamy by měly umožňovat 254 položek. Ale z nějakého důvodu se čtenář setkává se situací, kdy Excel uchovává pouze prvních 38 položek! Dostaneme se na dno této záhady.
Sledovat video
- Don chce třídit podle vlastního číselného seznamu!
- Bude to fungovat? Zdá se, že to funguje!
- Nelze však importovat číselné buňky do dialogového okna vlastního seznamu.
- Zkuste tedy zadat čísla do dialogového okna Vlastní seznam…. Při psaní vás napadne pošetilý limit 255 znaků.
- WTH je limit? 254 položek? Aha - 254 položek, ale méně než 2 000 znaků, když mezi každou položku přidáte neviditelnou čárku
- Udělali jste nějaký matematický text pomocí
=SUM(LEN())a Ctrl + Shift + Enter aLEN(TEXTJOIN(",",True,Range)) - Řešení s ABS pro třídění v tomto konkrétním případě pro Dona
- Ale nejlepší řešení … věc, kterou Don musí udělat:
Přepis videa
Naučte se Excel z Podcastu, epizoda 2098: Vlastní limity seznamu.
Je to jen bizarní, když Don S, používající Mac 2011, správně, takže nepoužíváme ani skutečnou verzi aplikace Excel. Používáme falešnou verzi aplikace Excel a pokoušíme se třídit podle vlastního seznamu, ale přijímá pouze prvních 38 položek v seznamu. A vím, že je to špatně, protože Excel dokáže zpracovat až 254 položek v seznamu, nebo alespoň to jsem si myslel. Dobře, a Don má jméno hráče, počet výher a poté marži, třeba jak daleko byli od skóre.
Správná sekvence tedy je, že dokonalé skóre je 0 a poté více než 1, méně než 1 a více než 2, méně než 2, více než 3, méně než 3 atd. A Don se snaží řadit sloupec Okraj podle tohoto vlastního seznamu. Teď jsem to nikdy nezkoušel, ale hej, mělo by to být snadné. Tady je správná posloupnost: 0, 1 a vzorec bude mínus hodnota těsně před námi a pak = číslo 2 nad +1. Dobře, teď, když mám ty dva vzorce, bych měl být schopen projet se dolů 201, příliš daleko, ale to je v pořádku. A měli bychom mít přesnou sekvenci, kterou potřebujeme, až do 99 a -99. Existuje tedy naše dokonalá sada odpovědí. Zkopíruji to, takže Ctrl + C zkopírujte a poté vložte jako hodnoty. Vložte tyto hodnoty takhle. Dobře, takže bych měl být schopen vytvořit vlastní seznam, který to zvládne, že? Žádný problém.Jdeme tedy na Soubor, Možnosti, Pokročilé, posuneme 83% úplně dolů, vybereme Upravit vlastní seznamy a importujeme náš seznam. Co? Buňky bez jednoduchého textu byly ignorovány. Nemáte oprávnění mít vlastní seznam plný čísel? Ale Don říká, že to funguje u prvních 38. Co je s tím? Chvíli na to nemysli. Uvědomil jsem si, že Don se asi nesnažil dovážet; musel právě psát ta čísla do dialogového okna.Uvědomil jsem si, že Don se asi nesnažil dovážet; musel právě psát ta čísla do dialogového okna.Uvědomil jsem si, že Don se asi nesnažil dovážet; musel právě psát ta čísla do dialogového okna.
Takže tady budu dělat. Chystám se Ctrl + C zkopírovat všechny ty, jdu do Poznámkového bloku a vložit do Poznámkového bloku jako je tento Ctrl + V a pak vybrat vše: Upravit, Vybrat vše a Ctrl + C, vrátit se k Excel, Soubor, Možnosti, Pokročilé, 83% úplně dolů, Upravit vlastní seznamy a já sem napíšu ten seznam, jako by to byl Ctrl + V. Dobře, a to vše funguje, ale klikli jsme na Přidat, byla překročena maximální délka vlastního seznamu. Uloženo bude pouze prvních 255 znaků. A když se na to podíváte, určitě vytvářejí vlastní seznam, který se sníží pouze na 38, -38, 39 a poté BAM! Poslední 3, že?
To je tedy tak bizarní. Ve skutečnosti mi umožňují vytvořit vlastní seznam s čísly, ale neumožňují mi získat 255. Myslím, že to funguje. Funguje to a pak, když se to skutečně pokusíme třídit tady; takže řekneme Data, Třídit a třídit na tomto vlastním seznamu, ten, který má pouze 39, klikněte na OK, klikněte na OK. Pokud je v seznamu, setřídí se správně. Dobře, takže pozitivní +6 se zobrazí před -6; ale pak, jakmile se dostaneme k něčemu přesahujícímu 39, bude to jen třídit sekvence, které nejsou v seznamu, takže to jde od nejmenšího k největšímu. Někdo, kdo minul o 67 bodů, je lepší než někdo, kdo minul o +42 bodů. Je to prostě úplně špinavé.
Dobře, a co se děje s tím, že bude jen 38? Nyní, určitě, je tu každé další číslo, takže víte, myslím, že to bude klesat do 30. let. Jdeme dolů, abychom tam byli rádi, že? Nebo tam, jeden z těch dvou. Co je to? To je celkem 78 položek. A hej, vím, že povolili - mít 250 fór, protože na svých seminářích neustále mluvím o zákaznících, dobře? Můžete mít 250 fór, ukážu vám to. Položka 1, a samozřejmě k tomu můžeme použít položku Plnění. Přetáhnu dolů na 254. Nyní to nejsou vzorce, takže bychom měli být schopni dělat Soubor, Možnosti, Pokročilé, pokračovat v Upravit vlastní seznamy a my tento seznam importujeme, dobře? Tady to je, BAM! Žádné problémy, žádná chybová zpráva. Všechno je skvělé, všechno je - Není to skvělé. Jde pouze o položku 234. Počkejte, vím, že můžete mít 254.Proč se zastaví na 234? To je bizarní, to je bizarní. Co je s tím?
Tady víme, že to jde jen k položce 234 právě tam. Dobře, teď, když jsme psali položky do seznamu, je tu několik znaků. Byl tam limit. Zajímalo by mě, jestli je zde nějaký počet znaků, který je zde limitem = SUMA (DÉLKA celé té spousty věcí, stiskněte CTRL + SHIFT ENTER a je to 1764 znaků - 234 položek. A vím, že můžete mít 254, já udělal to předtím.
A zkusme něco bláznivějšího. Dobře, zkusme to. Zkusme místo položky zkusme něco delšího. Takže 10 znaků MEZERNÍK a pak číslo 1, půjdeme dolů o 254 řádků. A my se pokusíme tento seznam importovat: takže Soubor, Možnosti, Pokročilé, Upravit vlastní seznamy, tento seznam importujeme. Žádná chybová zpráva. Vypadá to, že to fungovalo, ale jde to jen na 140. Co je sakra s tím? Jaký je limit? Myslel jsem, že by to mohlo být 254. Takže se podívejme, kolik znaků máme, když se dostaneme dolů na 140. Dobře, tak po tom nechme všechno ostatní a ve skutečnosti sem přijdu k tomuto vzorci a zkopíruji přesně stejný vzorec přes. Dobře, ne.
V tomto okamžiku jsem s týmem Excel docela podrážděný. Co se děje, tady 1764 a tady 1852. Hej, Microsoftu, jaký je limit? Jaký je přesně limit? Ah, ale tady je ta věc. Určitě to ukládají jako řadu omezených řetězců, dobře? Berou tedy všechny položky a za každou přidávají čárku. Dobře, takže tady, protože máme Office 365, můžeme použít nový Textový vstup, takže = TEXTJOIN všech těch, mezi kterými je čárka. Nevím, jestli je to opravdu čárka nebo ne. Ignorujte to, pak True, čárka a tyto položky. Takže to chápeme. A vlastně chci jen vědět délku celé té věci. Takže délka je 1997 a když udělám totéž tady, 1991. Oh! Je tedy zřejmé, že limit musí být 2 000 znaků včetně neviditelné čárky mezi každou položkou.
To vše je docela bizarní. Dobře, takže jsem si vždy myslel, že je to 254 položek, není to 254 položek. Je to 254 položek za předpokladu, že má méně než 2 000 znaků, pokud položky nejsou příliš dlouhé. Dobře, tak abychom si otestovali moji teorii, použijeme takovou mezeru 1 a uchopíme Fill Handle a táhneme. Měly by být opravdu pěkné a krátké, protože - A půjdeme dolů na 255, 254. Pojďme to vyzkoušet na 255.
Dobře, takže teď, když žádám o délku spojení textu, 1421. Vůbec žádný problém. Vyberte tedy celou věc a Soubor, Možnosti, Pokročilé, přejděte úplně dolů, Upravte vlastní seznamy, klikněte na Importovat. Dobře, a úplně vypni na 254. Dobře, takže je to 254 položek za předpokladu, že je to méně než 2 000 znaků včetně neviditelné čárky po každé položce, jak to funguje.
Víte, ano - ale zpět k Donovu problému zde. Určitě je nepříjemné, že v dialogovém okně, pokud prostě vstoupíme a začneme psát věci do dialogového okna namísto prvku s 2000 znaky, má na něm 255 znaků. Dobře, takže Don nemá žádný způsob, jak tuto věc zadat, a když se pokusíme importovat čísla, odmítne je importovat. Říká se, že žádná dohoda. Cokoli, co není prostý text, nebude fungovat, dobře?
Jedinou věcí, kterou jsem Donovi navrhl, je tedy alternativní řešení. Říkám, hej, pojďme sem a přidejme pomocný sloup a tento pomocný sloup bude - pokud je absolutní hodnota tohoto čísla, v pořádku. A dvakrát klikneme, abychom to sestřelili, a pak to, co uděláte, je, že prostě seřadíte sestupně podle absolutní hodnoty, nastavení podle absolutní hodnoty, dobře. A pak 4, 6 a pak -6, v pořádku, to vše se jen seřadí, víš. Takže to není špatné, myslím, že to, co byste opravdu mohli udělat, je to, že byste mohli třídit podle Pomocníka a poté Přidat úroveň a pak Seřadit podle okraje, sestupně od Největšího k Nejmenšímu, klepněte na OK a tím získáte to, co Don hledá. Takže všechny +6 se objeví před -6 a poté 8 a poté -11, dobře. Víte, ale toto je problém. Jako, hej, Microsoftu.Proč musíme jít do toho všeho potíží? Proč byste nám dovolili napsat 2 000 znaků do dialogového okna nebo ještě lépe, protože to zjevně funguje tak, že máme čísla ve vlastním seznamu, myslím, že to tady funguje, proč nám to nedovolíte importovat?
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, tady to máš. Done, jedna z bláznivějších otázek, jaké jsem kdy v Excelu slyšel, a my jsme odhalili minimálně 2 - No, určitě 1 chybu v Excelu, že dialogové okno Custom List neumožňuje více než 255 znaků. A pak tato bizarní věc, která zjevně roztřídí vlastní seznam podle čísel, ale nedovolí vám importovat čísla. Dobře, tak zavolám tu chybu číslo 2. A pak toto řešení tady, dobře?
Takže hej, chci poděkovat Donu za zaslání otázky a pokud tu tak dlouho visíte, chci vám poděkovat, že jste se zastavili. Uvidíme se příště na dalším netcastu z.
Stáhnout soubor
Stáhněte si ukázkový soubor zde: Podcast2098.xlsm
