Obožujem sezname po meri v Excelu. Odlične so za ročico za polnjenje in za razvrščanje podatkov v drugačno zaporedje. Seznami po meri naj omogočajo 254 elementov. Toda iz neznanega razloga se bralec srečuje s situacijo, ko Excel hrani le prvih 38 predmetov! Temu skrivnosti bomo prišli do dna.
Oglejte si video
- Don želi razvrstiti po številskem seznamu po meri!
- Bo to delovalo? Zdi se, da deluje!
- Vendar v pogovorno okno s seznamom po meri ne morete uvoziti številskih celic.
- Torej, poskusite vtipkati številke v pogovorno okno Seznam po meri…. Pri tipkanju vas preseneti neumna omejitev 255 znakov.
- WTH je meja? 254 predmetov? Aha - 254 elementov, vendar manj kot 2000 znakov, če dodate nevidno vejico med posamezne elemente
- Ali je bilo nekaj besedilne matematike z
=SUM(LEN())in Ctrl + Shift + Enter inLEN(TEXTJOIN(",",True,Range)) - Rešitev z ABS za razvrščanje v tem primeru za Don
- Ampak najboljša rešitev … stvar, ki jo mora storiti Don:
Video zapis
Naučite se Excel iz podcasta, epizoda 2098: Omejitve seznama po meri.
To je samo bizarno, ko Don S z Mac 2011, kajne, torej ne uporabljamo niti prave različice Excela. Uporabljamo ponarejeno različico Excela in poskušamo razvrstiti po seznamu po meri, vendar sprejemamo le prvih 38 elementov na seznamu. In vem, da je to narobe, ker lahko Excel obdela do 254 elementov na seznamu ali vsaj tako sem mislil. V redu, Don ima ime igralca, število zmag in nato maržo, na primer, kako daleč od rezultata so bili takrat.
Pravilno zaporedje je torej, da je popoln rezultat 0 in nato več kot 1, manj kot 1 in več kot 2, manj kot 2, več kot 3, manj kot 3 in tako naprej. Don poskuša stolpec Margin razvrstiti po tem seznamu po meri. Zdaj tega še nisem poskusil, ampak, hej, to bi moralo biti enostavno. Torej tukaj Pravilno zaporedje: 0, 1 in potem bo formula minus vrednost tik pred nami in nato = število 2 nad +1. V redu, zdaj, ko imam ti dve formuli, bi lahko potoval skozi 201, predaleč, toda v redu je. In morali bi imeti natančno zaporedje, ki ga potrebujemo, do 99 in -99. Torej obstaja naš popoln nabor odgovorov. Kopiral bom tako, da Ctrl + C kopiram in nato prilepi kot vrednosti. Te vrednosti prilepite tako. V redu, torej bi moral imeti možnost vzpostaviti seznam po meri, ki bo to rešil, kajne? Ni problema.Torej gremo na Datoteka, Možnosti, Napredno, se pomaknemo za 83% do konca, izberemo Uredi sezname po meri in uvozimo svoj seznam. Kaj? Celice brez preprostega besedila so bile prezrte. Ne smete imeti seznama po meri, polnega številk? Toda Don pravi, da to deluje prvih 38. Kaj je s tem? No, o tem že nekaj časa ne razmišljajte. Spoznal sem, da Don verjetno ni poskušal uvoziti; te številke je moral pravkar vtipkati v pogovorno okno.Spoznal sem, da Don verjetno ni poskušal uvoziti; te številke je moral pravkar vtipkati v pogovorno okno.Spoznal sem, da Don verjetno ni poskušal uvoziti; te številke je moral pravkar vtipkati v pogovorno okno.
Torej, tukaj bom naredil nekaj. Naredil bom Ctrl + C, da kopiram vse te, šel bom v Beležnico in Prilepi v Beležnico, kot je ta Ctrl + V, nato pa izbrati vse: Uredi, Izberi vse in Ctrl + C, vrni se na Excel, Datoteka, Možnosti, Napredno, 83% do konca, Uredi sezname po meri in seznam bom vtipkal sem, kot da je Ctrl + V. V redu in ker deluje vse, vendar smo kliknili Dodaj, je presežena največja dolžina seznama po meri. Shranjenih bo samo prvih 255 znakov. In ko to pogledate, zagotovo ustvarijo seznam po meri, ki se spusti le na 38, -38, 39 in nato BAM! Zadnje 3, kajne?
Torej, to je tako bizarno. Dejansko mi dovolijo, da ustvarim seznam po meri s številkami, ne dovolijo pa mi 255. Mislim, deluje. Deluje in potem, če dejansko poskusimo razvrstiti tukaj; tako bomo rekli Data, Sortiraj in razvrsti na tem seznamu po meri, tisti, ki gre samo na 39, kliknite V redu, kliknite V redu. No, če je na seznamu, se pravilno razvrsti. V redu, torej pozitivni +6 kaže pred -6; potem pa, ko pridemo do česar koli več kot 39, se bo to samo razvrstilo po zaporedjih, ki jih ni na seznamu, zato gre tukaj od najmanjšega do največjega. Torej je nekdo zgrešen za 67 točk boljši od nekoga, ki je zgrešil za +42 točk. Je popolnoma napihnjen.
V redu, in kaj je s tem, ko gre samo za 38? Prepričan sem, da obstaja še vsaka druga številka, zato veste, mislim, da bo padla v trideseta leta. Tam se spravljamo všeč, kajne? Ali tam, eden od teh dveh. Torej, kaj je to? To je skupaj 78 predmetov. In hej, vem, da so dovolili - imeti 250 forumov, ker na svojih seminarjih ves čas govorim o strankah, prav? Lahko imate 250 forumov, naj vam pokažem. Torej postavka 1 in seveda lahko za to uporabimo polnilni element. Povlekel bom do 254, tako. Zdaj to niso formule, zato bi morali imeti možnost Datoteka, Možnosti, Vnaprej, nadaljujemo z urejanjem seznamov po meri in seznam bomo uvozili, v redu? Tukaj je, BAM! Brez težav, brez sporočila o napaki. Vse je super, vse je - ni super. Gre le za postavko 234. Počakajte, vem, da jih lahko imate 254.Zakaj se ustavi pri 234? To je bizarno, to je bizarno. Kaj je s tem?
Torej tukaj vemo, da gre samo do točke 234 tam. V redu, ko smo vnašali Elemente na seznamu, je nekaj znakov. Bila je meja. Zato se sprašujem, ali je tu določeno število znakov, ki je tu omejitev = SUM (LEN vsega tega sklopa, pritisnite CTRL + SHIFT ENTER, in to je 1764 znakov - 234 predmetov. že prej.
In poskusimo kaj bolj norega. V redu, poskusimo to. Poskusimo namesto predmeta poskusimo kaj dlje. Torej 10 znakov PROSTOR in nato številka 1, se bomo spustili za 254 vrstic. In poskusili bomo uvoziti ta seznam: torej Datoteka, Možnosti, Napredno, Uredi sezname po meri, bomo uvozili ta seznam. Ni sporočila o napaki. Zdi se, da je delovalo, vendar se zniža le na 140. Kaj za vraga je s tem? Kakšna je meja? Mislil sem, da bi lahko bilo 254. Torej, poglejmo, koliko znakov imamo, če pridemo do 140. V redu, torej pustimo vse ostalo po tem in dejansko bom prišel sem do te formule in kopiral popolnoma isto formulo konec. V redu, ne.
Na tej točki sem precej razočaran z ekipo Excel. Kaj je tukaj, tukaj 1764 in tukaj 1852. Hej, Microsoft, kakšna je omejitev? Kaj točno je meja? Ah, ampak tukaj je stvar. Verjetno to shranjujejo kot vrsto ločenih nizov, v redu? Torej vzamejo vse predmete in nato za vsakim dodajo vejico. Torej, tukaj, ker imamo Office 365, lahko uporabimo novo besedilno združitev, torej = TEXTJOIN vseh, ki imajo vmes vejico. Ne vem, ali gre res za vejico ali ne. Prezri to, nato True, vejico in te elemente. Torej, razumemo. In pravzaprav želim samo vedeti, kako dolga je celotna stvar. Dolžina je torej 1997 in ko naredim isto stvar tukaj, 1991. Oh! Jasno je, da mora biti omejitev 2000 znakov, vključno z nevidno vejico med posameznimi elementi.
Vse to je precej bizarno. V redu, zato sem vedno mislil, da gre za 254 predmetov, ne pa za 254 predmetov. To je 254 predmetov, pod pogojem, da je manj kot 2000 znakov, če elementi niso predolgi. Torej, samo da preizkusim svojo teorijo, uporabimo presledek 1 in zgrabimo ročico za polnjenje in povlečemo. Te bi morale biti res lepe in kratke, ker - In spustili se bomo na 255, 254. Pojdimo na 255, da jo preizkusimo.
V redu, zdaj s tem, če prosim za dolžino besedilnega spoja, 1421. Sploh ni problema. Torej izberite celotno stvar in Datoteka, Možnosti, Napredno, pomaknite se navzdol do dna, Uredi sezname po meri, kliknite Uvozi. V redu in izklopite vse do 254. V redu, torej 254 predmetov, pod pogojem, da je manj kot 2000 znakov, vključno z nevidno vejico za vsakim elementom, kako to deluje.
Veste, torej - toda vrnimo se k Donovemu problemu tukaj. Zagotovo je moteče, da ima pogovorno okno, če preprosto vstopimo in začnemo tipkati stvari v pogovornem oknu, namesto da ima 2000 znakovnih elementov, 255 znakov. V redu, zato Don ne more vtipkati te stvari in ko poskusimo uvoziti številke, številk noče uvoziti. Piše brez dogovora. Vse, kar ni navadno besedilo, ne bo delovalo, v redu?
Torej, nekaj, kar sem Donu predlagal, je nadomestna rešitev. Pravim, hej, samo pridimo sem in dodamo stolpec pomočnika in ta stolpec pomočnika bo - če je absolutna vrednost te številke, v redu. In dvakrat kliknemo, da bomo to sestrelili, nato pa boste razvrstili Descending glede na Absolutno vrednost, nastavitev Absolutna vrednost, v redu. In potem 4, 6 in nato -6, v redu, vsi se preprosto razvrstijo, veste. Torej ni slabo, mislim, da bi lahko v resnici naredili, da bi lahko razvrstili po pomočniku in nato dodali nivo in nato razvrstili po robu, spustite se Največje na Najmanjše, kliknite V redu in to bo dobilo tisto, kar išče Don. Torej, vsi +6 se bodo prikazali pred -6, nato 8 in nato -11, v redu. Veste, ampak to je težava. Kot, hej, Microsoft.Zakaj moramo iti na vse te težave? Zakaj bi nam dovolili vtipkati 2000 znakov v pogovorno okno ali še bolje, saj očitno deluje, če imamo številke na seznamu po meri, mislim, da tukaj deluje, zakaj nam ne dovolite, 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.
No, tu ste. Don, eno bolj norih vprašanj, ki sem jih kdaj slišal v Excelu, in odkrili smo vsaj 2 - No, vsekakor 1 napako v Excelu, da pogovorno okno s seznamom po meri ne bo smelo imeti več kot 255 znakov. In potem ta bizarna stvar, da bo očitno seznam po meri razvrstil po številkah, vendar vam ne bodo dovolili uvoza številk. V redu, torej bom poklical tisto napako številka 2. In potem ta rešitev tukaj, v redu?
Hej, rad bi se zahvalil Donu, ker je poslal vprašanje, in če se tako dolgo zadržite, se vam želim zahvaliti, da ste se ustavili. Se vidimo naslednjič za še eno oddajo iz.
Prenesite datoteko
Prenesite vzorčno datoteko tukaj: Podcast2098.xlsm
