Man patinka „Excel“ pasirinktiniai sąrašai. Jie puikiai tinka pildymo rankenai ir duomenims rūšiuoti į kitą seką. Pasirinktiniai sąrašai turėtų leisti 254 elementus. Bet kažkodėl skaitytojas susiduria su situacija, kai „Excel“ saugo tik pirmuosius 38 elementus! Mes pateksime į šios paslapties apačią.
Žiūrėti video
- Donas nori rūšiuoti pagal pasirinktinį skaičių sąrašą!
- Ar tai veiks? Atrodo, kad tai veikia!
- Bet jūs negalite importuoti skaitinių langelių į pasirinktinio sąrašo dialogo langą.
- Taigi, pabandykite įvesti skaičius į dialogo langą Pasirinktinis sąrašas …. Rašant jums nustatytas kvailas 255 simbolių apribojimas.
- WTH yra riba? 254 daiktai? Aha - 254 elementai, bet mažiau nei 2000 simbolių, kai pridedate nematomą kablelį tarp kiekvieno elemento
- Atliko teksto matematiką naudodamas
=SUM(LEN())klavišus Ctrl + Shift + Enter irLEN(TEXTJOIN(",",True,Range)) - Sprendimas dėl ABS rūšiavimo šiuo konkrečiu atveju „Don“
- Bet geriausias sprendimas … dalykas, kurį turi padaryti Donas:
Vaizdo įrašo nuorašas
Sužinokite „Excel“ iš „Podcast“, 2098 serija: Pasirinktinio sąrašo ribos.
Tai tik keista, kai Don S, naudodamas „Mac 2011“, yra teisus, todėl mes net nenaudojame tikrosios „Excel“ versijos. Mes naudojame netikrą „Excel“ versiją, bandydami rūšiuoti pagal pasirinktinį sąrašą, tačiau ji priima tik pirmuosius 38 sąrašo elementus. Ir aš žinau, kad tai neteisinga, nes „Excel“ gali tvarkyti iki 254 sąrašo elementų arba bent jau taip maniau. Gerai, o Donas turi žaidėjo vardą, # iš laimėjimų ir tada įstengimą, pavyzdžiui, kaip toli nuo to laiko buvo rezultatas.
Taigi tinkama seka yra ta, kad puikus rezultatas yra 0 ir tada daugiau nei 1, po 1 ir daugiau už 2, po 2, per 3, pagal 3 ir pan. Donas bando surūšiuoti stulpelį Marža pagal šį pasirinktinį sąrašą. Aš niekada to nebandžiau, bet, ai, tai turėtų būti lengva padaryti. Taigi čia tinkama seka: 0, 1, tada formulė bus atimta prieš pat mus esanti vertė ir tada = skaičius 2 virš +1. Gerai, dabar, kai turiu tas dvi formules, turėčiau sugebėti pervažiuoti per 201, per toli, bet tai gerai. Turėtume turėti tikslią seką, kuri mums reikalinga iki 99 ir -99. Taigi yra mūsų tobulas atsakymų rinkinys. Aš nukopijuosiu taip, kad nukopijuočiau Ctrl + C, o tada įklijuosiu kaip vertybes. Įklijuokite tas vertybes taip. Gerai, todėl turėčiau galėti sudaryti pasirinktinį sąrašą, kuris tai tvarkys, ar ne? Jokiu problemu.Taigi einame į Failas, Parinktys, Išplėstinė, slinkite 83% iki galo, pasirinkite Redaguoti pasirinktinius sąrašus ir importuosime savo sąrašą. Ką? Ląstelės be paprasto teksto buvo ignoruojamos. Jums neleidžiama, kad pasirinktinis sąrašas būtų pilnas skaičių? Bet Donas sako, kad tai veikia pirmuosius 38 metus. Kas čia? Na, kurį laiką negalvok apie tai. Supratau, kad Donas neturėjo bandyti importuoti; jis turėjo ką tik įvesti tuos skaičius į dialogo langą.Supratau, kad Donas neturėjo bandyti importuoti; jis turėjo ką tik įvesti tuos skaičius į dialogo langą.Supratau, kad Donas neturėjo bandyti importuoti; jis turėjo ką tik įvesti tuos skaičius į dialogo langą.
Taigi štai ką aš darysiu. Atliksiu „Ctrl + C“, kad nukopijuočiau visus tuos, eisiu į „Notepad“ ir įklijuokite „Notepad“, pvz., „Ctrl + V“, tada pažymėsiu viską: Redaguoti, Pasirinkti viską ir „Ctrl + C“, grįžkite į „Excel“, „File“, „Options“, „Advanced“, 83% iki galo, Redaguoti pasirinktinius sąrašus ir aš čia surašysiu tą sąrašą kaip „Ctrl“ + V. Gerai, ir kad viskas veikia, bet mes spustelėjome Pridėti, maksimalus tinkinto sąrašo ilgis buvo viršytas. Bus išsaugoti tik pirmieji 255 simboliai. Pažvelgus į tai, tikrai, jie kuria pasirinktinį sąrašą, kuris eina tik į 38, -38, 39, o tada į BAM! Paskutiniai 3, tiesa?
Taigi, tai taip keista. Jie iš tikrųjų leidžia man sukurti pasirinktinį sąrašą su skaičiais, bet jie neleidžia man gauti 255. Turiu omenyje, kad jis veikia. Tai veikia ir tada, jei mes iš tikrųjų bandysime čia susitvarkyti; taigi šiame „Custom List“ sakysime „Data“, „Rūšiuoti ir rūšiuoti“, tas, kuris patenka tik į 39, spustelėkite Gerai, spustelėkite Gerai. Na, jei jis yra sąraše, jis rūšiuojamas teisingai. Gerai, todėl teigiamas +6 rodo prieš -6; bet tada, kai nusileisime į viską, kas yra daugiau nei 39, jis tiesiog surūšiuos į sekas, kurių nėra sąraše, taigi čia ji eina nuo mažiausios iki didžiausios. Taigi, kažkas praleido 67 taškais yra geriau nei tas, kuris praleido +42 taškus. Jis tiesiog visiškai susuktas.
Gerai, o kas čia, kad viskas tik 38? Dabar, be abejo, yra visi kiti skaičiai, taigi, spėju, kad tai nutiks iki 30-ųjų. Mes nusileidžiame kaip ten, tiesa? Arba ten vienas iš tų dviejų. Taigi, kas tai yra? Tai yra iš viso 78 daiktai. Ei, aš žinau, kad jie leido - turėk 250 forumų, nes aš savo seminaruose nuolat kalbu apie klientus, gerai? Galite turėti 250 forumų, leiskite man parodyti. Taigi 1 punktas ir, žinoma, tam galime naudoti užpildymo elementą. Aš nusitempsiu iki 254, panašiai. Dabar tai nėra formulės, todėl turėtume turėti galimybę atlikti failą, parinktis, išankstinį nustatymą, pereiti prie Redaguoti pasirinktinius sąrašus ir importuosime tą sąrašą, gerai? Štai, BAM! Jokių problemų, nėra klaidos pranešimo. Viskas puiku, viskas yra - tai nėra puiku. Tai eina tik į 234 punktą. Palaukite, aš žinau, kad galite turėti 254.Kodėl jis sustoja ties 234? Tai keista, tai keista. Kas čia?
Taigi čia mes žinome, kad ten pat yra tik 234 punktas. Gerai, kai mes įvedėme elementus į sąrašą, yra šiek tiek simbolių. Buvo riba. Taigi įdomu, ar čia yra tam tikras simbolių skaičius, kuris čia yra ribotas = SUM (viso šito daikto LEN, paspauskite CTRL + SHIFT ENTER, ir tai yra 1764 simboliai - 234 elementai. Ir aš žinau, kad galite turėti 254, aš tai darė anksčiau.
Ir pabandykime ką nors beprotiškesnio. Gerai, pabandykime tai. Pabandykime vietoje daikto išbandyti ką nors ilgesnio. Taigi 10 simbolių tarpo, o paskui numeris 1, mes eisime žemyn 254 eilutes. Pabandysime ir importuosime šį sąrašą: failas, parinktys, išplėstiniai, redaguoti pasirinktinius sąrašus, importuosime šį sąrašą. Nėra klaidos pranešimo. Atrodo, kad tai pavyko, bet sumažėja tik iki 140. Ką gi tai reiškia? Kokia riba? Maniau, kad tai gali būti 254. Taigi pažiūrėkime, kiek turime simbolių, jei nusileisime iki 140. Gerai, palikime visa kita po to ir iš tikrųjų aš čia pereisiu prie šios formulės ir nukopijuosiu tą pačią formulę baigėsi. Gerai, ne.
At this point, I'm pretty exasperated with the Excel team. What's up, here 1764 and here 1852. Hey, Microsoft, what is the limit? Exactly what is the limit? Ah, but here's the thing. They must be storing this as a series of delimited strings, alright? So they're taking all the Items and then they're adding a comma after each one. Alright, so here since we have Office 365, we can use the new Text Join, so =TEXTJOIN of all of those with a comma in between. I don’t know if it’s really a comma or not. Ignore that, then True, comma and these items. So, we get that. And actually I just want to know the length to that whole thing. So the length is 1997 and when I do the same thing over here, 1991. Oh! So, clearly the limit must be 2000 characters including an invisible comma between each item.
This is all pretty bizarre. Alright, so I always thought it was 254 Items, it's not 254 Items. It’s 254 Items, provided it’s less than 2,000 characters, provided the Items aren't too long. Alright, so just to test my theory, let's just use A space 1 like that and we’ll grab the Fill Handle and drag. These should be really nice and short because - And we'll go down to 255, 254. Let's go to 255 to test it.
Alright, so now with this, if I ask for the Length of the Text Join, 1421. No problem at all. So select the whole thing and File, Options, Advanced, scroll all the way down to the bottom, Edit Custom Lists, click Import. Alright, and turn off all the way down to 254. Alright, so it's 254 Items provided it’s less than 2,000 characters including a invisible comma after each item is how this works.
You know, so - but back to Don’s problem here. It sure is annoying that the Dialog Box, if we just go in and start typing things in the Dialog Box instead of having a 2,000 character element, it has a 255 character on it. Alright, so Don has no way to type this thing in and when we try and Import numbers, it refuses to Import the numbers. It says no deal. Anything that's not plain text is not going to work, alright?
So, the one thing I suggested to Don is an alternate solution. I say, hey, let's just come out here and add a Helper Column and this Helper Column is going to be the - if the Absolute Value of that number, alright. And we'll double click to shoot that down and then what you’re going to do is you're just going to sort Descending by the Absolute Value, a setting by the Absolute Value, alright. And then the 4, 6 and then -6, alright, these are all just get sorted together, you know. So it's not bad, I guess what you could really do is you could sort by the Helper and then Add a Level and then Sort by the Margin, descending Largest to Smallest, click OK and that will get what Don is looking for. So, all of the +6 will show up before the -6, and then 8 and then -11, alright. You know, but this is a hassle. Like, hey, Microsoft. Why do we have to go to all this hassle? Why would you let us type 2,000 characters into the Dialog Box or even better yet, since it apparently works to have numbers in the Custom List, I mean it is working here, why won't you let us import it?
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.
Na, važiuok. Donai, vienas iš beprotiškiausių klausimų, kurį kada nors girdėjau programoje „Excel“, ir mes aptikome bent 2 - Na, tikrai 1 klaidą programoje „Excel“, kad dialogo lange Pasirinktinis sąrašas neleis daugiau nei 255 simboliai. Tada šis keistas dalykas, kuris, matyt, surūšiuos pasirinktinį sąrašą pagal skaičius, tačiau jie neleis importuoti numerių. Gerai, todėl aš paskambinsiu tuo klaidos numeriu 2. Ir tada šis sprendimas čia, gerai?
Taigi, aš noriu padėkoti Donui, kad jis išsiuntė klausimą. Jei ilgai užsibusi, noriu padėkoti, kad užsukai. Pamatysime kitą kartą kitai internetinei transliacijai iš.
Atsisiųsti failą
Parsisiųskite failo pavyzdį čia: Podcast2098.xlsm
