Rakastan mukautettuja luetteloita Excelissä. Ne sopivat erinomaisesti täyttökahvaan ja tietojen lajitteluun eri järjestykseen. Mukautettujen luetteloiden tulisi sallia 254 kohdetta. Mutta jostain syystä lukija on kohdannut tilanteen, jossa Excel säilyttää vain 38 ensimmäistä kohdetta! Tulemme tämän mysteerin loppuun.
Katso video
- Don haluaa lajitella mukautetun numeerisen luettelon mukaan!
- Toimiiko tämä? Se näyttää toimivan!
- Mutta et voi tuoda numeerisia soluja mukautetun luettelon valintaikkunaan.
- Joten yritä kirjoittaa numeroita Mukautettu luettelo -valintaikkunaan…. Sinulle lyö typerä 255 merkkirajoitusta kirjoittaessasi.
- WTH on raja? 254 tuotetta? Aha - 254 kohdetta, mutta alle 2000 merkkiä, kun lisäät näkymätön pilkku kunkin kohteen väliin
- Suoritti jonkin verran tekstimatematiikkaa
=SUM(LEN())näppäimillä Ctrl + Vaihto + Enter jaLEN(TEXTJOIN(",",True,Range)) - Kiertotapa ABS: n kanssa lajittelua varten tässä Don-tapauksessa
- Mutta paras kiertotapa … asia, joka Donin on tehtävä:
Videon transkriptio
Opi Excel Podcastista, Jakso 2098: Mukautetut luettelorajat.
Tämä on vain outoa, kun Don S, Mac 2011 -käyttöjärjestelmässä, on oikea, joten emme edes käytä todellista Excel-versiota. Käytämme väärennettyä Excel-versiota, yritämme lajitella mukautetun luettelon mukaan, mutta se hyväksyy vain luettelon 38 ensimmäistä kohdetta. Ja tiedän, että se on väärin, koska Excel pystyy käsittelemään jopa 254 kohdetta luettelossa tai ainakin ajattelin sitä. Selvä, ja Donilla on pelaajan nimi, voittojen numero ja sitten marginaali, kuten kuinka kaukana pisteet olivat silloin.
Joten oikea järjestys on, täydellinen pisteet on 0 ja sitten yli 1, alle 1 ja yli 2, alle 2, yli 3, alle 3 ja niin edelleen. Ja Don yrittää lajitella Marginaali -sarakkeen tämän mukautetun luettelon mukaan. En ole koskaan kokeillut sitä, mutta hei, sen pitäisi olla helppo tehdä. Joten täällä Oikea sekvenssi: 0, 1 ja sitten kaava tulee olemaan miinus juuri edessämme oleva arvo ja sitten = numero 2 +1: n yläpuolella. Selvä, nyt kun minulla on nämä kaksi kaavaa, minun pitäisi pystyä risteilemään läpi 201, liian kaukana, mutta se on okei. Ja meillä pitäisi olla tarkka sekvenssi, jota tarvitsemme, alas 99 ja -99. Joten siellä on täydellinen joukko vastauksia. Kopioin sen niin, että Ctrl + C kopioidaan ja sitten Liitä arvona. Liitä nämä arvot sellaisiksi. Selvä, joten minun pitäisi pystyä luomaan oma luettelo, joka hoitaa tämän, eikö? Ei ongelmaa.Joten siirrymme kohtaan Tiedosto, Asetukset, Lisäasetukset, vieritämme 83% kokonaan alas, valitsemme Muokkaa mukautettuja luetteloita ja tuomme luettelomme. Mitä? Solut, joissa ei ole yksinkertaista tekstiä, jätettiin huomiotta. Sinulla ei saa olla mukautettua luetteloa, joka on täynnä numeroita? Mutta Don sanoo, että tämä toimii ensimmäisten 38 kanssa. Mitä siinä on? No, älä ajattele tätä jonkin aikaa. Tajusin, että Don ei ole ehkä yrittänyt tuoda; hän on juuri kirjoittanut nuo numerot valintaikkunaan.Tajusin, että Don ei välttämättä ole yrittänyt tuoda; hän on juuri kirjoittanut nuo numerot valintaikkunaan.Tajusin, että Don ei välttämättä ole yrittänyt tuoda; hän on juuri kirjoittanut nuo numerot valintaikkunaan.
Joten tässä aion tehdä. Aion tehdä Ctrl + C kopioida kaikki ne, aion mennä Muistioon ja liittää Muistioon näin Ctrl + V, ja valita sitten kaikki: Muokkaa, Valitse kaikki ja Ctrl + C, palaa takaisin Excel, File, Options, Advanced, 83% kokonaan alas, Muokkaa mukautettuja luetteloita ja kirjoitan luettelon tähän kuten Ctrl + V. Selvä, ja että kaikki toimii, mutta napsautimme Lisää, mukautetun luettelon enimmäispituus on ylitetty. Vain 255 ensimmäistä merkkiä tallennetaan. Ja kun katsot tätä, niin he luovat varmasti mukautetun luettelon, joka laskee vain luokkiin 38, -38, 39 ja sitten BAM! Viimeiset 3, eikö?
Joten, tämä on niin outoa. He todella antavat minun luoda mukautetun luettelon numeroilla, mutta eivät salli minun saada 255. Tarkoitan, että se toimii. Se toimii ja jos sitten todella yritämme lajitella täällä; joten sanomme Data, Lajittele ja lajittele tässä mukautetussa luettelossa, vain 39: een napsauttamalla OK, napsauttamalla OK. No, jos se on luettelossa, se lajittelee oikein. Selvä, joten positiivinen +6 näkyy ennen -6; mutta sitten kun olemme päässeet mihinkään muuhun kuin 39, se vain lajittelee sekvenssit, joita ei ole luettelossa, joten se siirtyy pienimmästä suurimpaan täällä. Joten joku, joka on ohittanut 67 pistettä, on parempi kuin joku, joka on ohittanut +42 pistettä. Se on vain täysin kierteinen.
Selvä, ja mitä tämä vain 38: een menee? Varmista, että siellä on kaikki muut numerot, joten tiedät, että se menee 30-luvulle. Olemme menossa alas kuin siellä, eikö? Tai siellä, yksi näistä kahdesta. Joten mikä tuo on? Se on yhteensä 78 tuotetta. Ja hei, tiedän, että he sallivat - onko sinulla 250 foorumia, koska puhun asiakkaista koko ajan seminaareissani. Sinulla voi olla 250 foorumia, haluan näyttää sinulle. Joten kohta 1, ja tietysti voimme käyttää täyttökohtaa siihen. Vedän alas 254: ään, sellaisena. Nyt nämä eivät ole kaavoja, joten meidän pitäisi pystyä tekemään tiedosto, asetukset, eteneminen, siirtymään muokkaamaan mukautettuja luetteloita ja tuomme tuon luettelon, okei? Siinä se on, BAM! Ei ongelmia, ei virheilmoitusta. Kaikki on hienoa, kaikki on - Se ei ole hienoa. Se menee vain kohtaan 234. Odota, tiedän, että sinulla voi olla 254.Miksi se pysähtyy 234: ssä? Se on outoa, se on outoa. Mitä siinä on?
Joten täällä tiedämme, että se menee vain kohtaan 234 juuri siellä. Okei nyt, kun kirjoitimme kohteita luetteloon, on jonkin verran merkkejä. Siellä oli raja. Joten ihmettelen, onko tässä rajoitettu merkkien lukumäärä = SUMMA (LEN koko joukosta tavaraa, paina CTRL + VAIHTO ENTER, ja se on 1764 merkkiä - 234 kohdetta. Ja tiedän, että sinulla voi olla 254, olen tehnyt tämän ennen.
Ja kokeillaan jotain hullumpaa. Selvä, kokeillaan tätä. Kokeillaan kohteen sijaan, kokeillaan jotain pidempään. Joten 10 merkkiä SPACE ja sitten numero 1, menemme alas 254 riviä. Ja yritämme tuoda tämän luettelon: niin Tiedosto, Asetukset, Lisäasetukset, Muokkaa mukautettuja luetteloita, tuomme tämän luettelon. Ei virheilmoitusta. Vaikuttaa siltä, että se toimi, mutta se laskee vain 140: een. Mikä on raja? Luulin, että se voisi olla 254. Katsotaanpa, kuinka monta merkkiä meillä on, jos tulemme alas 140: een. Selvä, joten jätetään kaikki muu tämän jälkeen ja itse asiassa tulen tänne tähän kaavaan ja kopioin täsmälleen saman kaavan yli. Selvä, ei.
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.
No, sinä menet. Don, yksi hulluimmista kysymyksistä, jotka olen koskaan kuullut Excelissä, ja olemme paljastaneet vähintään 2 - No, ehdottomasti yksi virhe Excelissä, että Mukautettu luettelo -valintaikkuna ei salli yli 255 merkkiä. Ja sitten tämä outo asia, joka ilmeisesti lajittelee mukautetun luettelon numeroiden mukaan, mutta ne eivät anna sinun tuoda numeroita. Selvä, joten aion soittaa virheen numeroon 2. Ja sitten tämä kiertotapa täällä, okei?
Joten hei, haluan kiittää Donia kysymyksen lähettämisestä ja jos viipyt niin kauan, haluan kiittää sinua pysähtymisestä. Nähdään ensi kerralla toisesta netcastista.
Lataa tiedosto
Lataa esimerkkitiedosto täältä: Podcast2098.xlsm
