Mulle meeldivad Exceli kohandatud loendid. Need sobivad suurepäraselt täitekäepideme jaoks ja andmete järjestamiseks erinevasse järjestusse. Kohandatud loendid peaksid lubama 254 üksust. Kuid millegipärast satub lugeja olukorda, kus Excel hoiab alles esimesi 38 üksust! Jõuame selle müsteeriumi põhja.
Vaata videot
- Don soovib sortida kohandatud loendi järgi, mis on arvuline!
- Kas see töötab? Tundub, et see töötab!
- Kuid te ei saa kohandatud loendi dialoogiboksi numbrirakke importida.
- Niisiis, proovige sisestada numbrid dialoogiboksi Kohandatud loend …. Tüüpimisel tabab teid rumal 255 tähemärgi piirang.
- WTH on piir? 254 üksust? Aha - 254 üksust, kuid vähem kui 2000 tähemärki, kui lisate nähtamatu koma iga üksuse vahele
- Tegin teksti matemaatika klahvidega
=SUM(LEN())Ctrl + Tõst + Enter jaLEN(TEXTJOIN(",",True,Range)) - Lahendus ABS-iga sortimiseks sel konkreetsel juhul Doni puhul
- Kuid parim lahendus … asi, mida Don peab tegema:
Video ärakiri
Õppige Exceli programmi Podcast, osa 2098: Kohandatud loendi piirid.
See on lihtsalt veider, kui Don S, kasutades Mac 2011, on õige, nii et me ei kasuta isegi Exceli tegelikku versiooni. Kasutame Exceli võltsversiooni, proovides sortida kohandatud loendi järgi, kuid see aktsepteerib loendis ainult esimest 38 üksust. Ja ma tean, et see on vale, sest Excel suudab käsitseda loendis kuni 254 üksust või vähemalt nii arvasin. Hästi ja Donil on mängija nimi, võitude arv ja seejärel marginaal, näiteks kui kaugel tulemusest siis oli.
Nii et õige järjestus on, et täiuslik tulemus on 0 ja siis üle 1, alla 1 ja üle 2, alla 2, üle 3, alla 3 ja nii edasi. Ja Don üritab veergu Veerg selle kohandatud loendi järgi sorteerida. Nüüd pole ma seda kunagi proovinud, aga kuule, seda peaks olema lihtne teha. Nii et siin on õige järjestus: 0, 1 ja siis on valem miinus vahetult enne meid olevale väärtusele ja siis = number 2 kohal +1. Hästi, nüüd, kui mul on need kaks valemit, peaksin saama mööda 201-t mööda alla sõita, liiga kaugele, kuid see on okei. Ja meil peaks olema täpne jada, mida vajame, kuni 99 ja -99. Nii et seal on meie täiuslik vastuste komplekt. Kopeerin selle nii, et kopeeriksite Ctrl + C ja seejärel Kleebi väärtusena. Kleepige need väärtused niimoodi. Hästi, nii et ma peaksin saama koostada kohandatud loendi, mis sellega hakkama saab, eks? Pole probleemi.Niisiis läheme menüüsse Fail, Valikud, Täpsem, kerime 83% lõpuni alla, valime Muuda kohandatud loendeid ja impordime oma loendi. Mida? Lahtrid ilma lihtsa tekstita jäeti tähelepanuta. Kas teil pole lubatud kohandatud loendit täis numbreid? Kuid Don ütleb, et see töötab esimese 38 korral. Mis sellega on? Noh, pole sellele mõnda aega mõelnud. Sain aru, et Don ei pidanud ilmselt importima; ta vist kirjutas just need numbrid dialoogiboksi.Sain aru, et Don ei pidanud ilmselt importima; ta kirjutas just need numbrid dialoogiboksi.Sain aru, et Don ei pidanud ilmselt importima; ta vist kirjutas just need numbrid dialoogiboksi.
Nii et siin ma teen. Kõigi nende kopeerimiseks kavatsen teha klahvikombinatsiooni Ctrl + C. Excel, File, Options, Advanced, 83% lõpuni, Redigeeri kohandatud loendeid ja ma kirjutan selle loendi siia nagu Ctrl + V. Hästi, ja et kõik töötab, kuid klõpsasime nuppu Lisa, on kohandatud loendi maksimaalne pikkus ületatud. Ainult esimesed 255 tähemärki salvestatakse. Ja kui te seda vaatate, loovad nad kindlasti kohandatud loendi, mis langeb ainult 38, -38, 39 ja seejärel BAM-i! Viimased 3, eks?
Nii et see on nii veider. Nad lasevad mul tegelikult luua kohandatud loendi numbritega, kuid nad ei luba mul saada 255. Ma mõtlen, et see töötab. See töötab ja siis, kui me siin tõesti proovime asju sorteerida; nii et me ütleme selles kohandatud loendis andmed, sortimine ja sortimine, see, mis läheb ainult 39-le, klõpsake nuppu OK, klõpsake nuppu OK. Noh, kui see on loendis, sorteerib see õigesti. Hästi, nii et positiivne +6 näitab enne -6; kuid siis, kui jõuame kaugemale 39-st, jõuab see lihtsalt järjestustesse, mida nimekirjas pole, nii et see läheb siin kõige väiksemast suuremaks. Niisiis on keegi 67 punkti vahele jätnud parem kui +42 punktiga. See on lihtsalt täiesti keerukas.
Olgu, ja mis sellest saab, kui see läheb ainult 38-ni? Nüüd on kindel, et seal on ka kõik teised numbrid, nii et teate, et see läheb vist 30ndatesse. Me hakkame seal meeldima, eks? Või seal, üks neist kahest. Mis see siis on? See on kokku 78 eset. Ja hei, ma tean, et nad lubasid - kas teil on 250 foorumit, sest ma räägin oma seminaridel kogu aeg klientidest, eks? Teil võib olla 250 foorumit, lubage mul teile näidata. Nii et punkt 1 ja loomulikult võime selleks kasutada ka täitmisüksust. Ma lohistan 254 niimoodi alla. Nüüd pole need valemid, nii et peaksime saama teha faili, valikuid, edasiliikumist, minna edasi kohandatud loendite redigeerimisse ja impordime selle loendi, eks? Seal see on, BAM! Pole probleeme, veateadet pole. Kõik on suurepärane, kõik on - see pole suurepärane. See läheb ainult punkti 234. Oota, ma tean, et teil võib olla 254.Miks see peatub 234 juures? See on veider, see on veider. Mis sellest saab?
Nii et siin me teame, et see läheb alla ainult punktile 234 sealsamas. Olgu, kui me loendis kirjeid kirjutasime, on seal mõni arv tähemärke. Seal oli piir. Nii et ma ei tea, kas siin on piiratud tähemärkide arv = SUM (kogu selle hunniku kraami LEN, vajutage klahvikombinatsiooni CTRL + SHIFT ENTER ja see on 1764 märki - 234 üksust. Ja ma tean, et teil võib olla 254, ma olen seda varem teinud.
Ja proovime midagi pöörasemat. Hästi, proovime seda. Proovime üksuse asemel proovida midagi pikemat. Nii et 10 tähemärki tühik ja seejärel number 1, läheme 254 rida alla. Ja proovime selle loendi importida: nii et fail, suvandid, täpsemad, kohandatud loendite muutmine, impordime selle loendi. Veateadet pole. Tundub, et see töötas, kuid see langeb ainult 140-ni. Mis kuradit sellega on? Mis on piir? Ma arvasin, et see võib olla 254. Nii et vaatame, kui palju märke meil on, kui jõuame 140-ni. Heakene, nii et jätame kõik muud pärast seda ja tegelikult tulen siia selle valemi juurde ja kopeerin täpselt sama valemi üle. Hästi, ei.
Siinkohal olen Exceli meeskonnast üsna nördinud. Mis seal ikka, siin 1764 ja siin 1852. Hei, Microsoft, mis on piir? Mis on täpselt piir? Ah, aga siin on asi. Nad peavad seda salvestama piiritletud stringidena, eks? Nii et nad võtavad kõik üksused ja lisavad siis iga pärast koma. Hästi, nii et siin, kuna meil on Office 365, saame kasutada uut teksti liitu, nii et = TEXTJOIN kõigist neist, kelle vahel on koma. Ma ei tea, kas see on tõesti koma või mitte. Eirake seda, siis Tõsi, koma ja need üksused. Nii et saame aru. Ja tegelikult tahan lihtsalt teada kogu selle asja pikkust. Nii et pikkus on 1997 ja kui ma siin sama asja teen, siis 1991. Oh! Seega peab selgelt olema 2000 tähemärki, sealhulgas nähtamatu koma iga üksuse vahel.
See kõik on päris veider. Hästi, nii et ma arvasin alati, et see on 254 üksust, see pole 254 üksust. See on 254 üksust, eeldusel, et see on vähem kui 2000 tähemärki, tingimusel et üksused pole liiga pikad. Olgu, nii et lihtsalt minu teooria testimiseks kasutame lihtsalt sellist tühikut 1 ja haarame täitekäepideme ning lohistame. Need peaksid olema väga toredad ja lühikesed, sest - ja me läheme alla 255-le, 254. Läheme 255-le, et seda testida.
Hästi, nii et nüüd sellega, kui küsin tekstiühenduse pikkust, 1421. Pole üldse probleemi. Nii et valige kogu asi ja Fail, Valikud, Täpsem, kerige lõpuni alla, Redigeeri kohandatud loendeid ja klõpsake nuppu Import. Olgu, ja lülitage välja kuni punktini 254. Olgu, nii et see on 254 üksust, kui see töötab vähem kui 2000 tähemärki, sealhulgas nähtamatu koma pärast iga üksust.
Tead, nii - aga tagasi Doni probleemi juurde siin. Kindlasti on tüütu, et kui dialoogiboks lihtsalt sisse minna ja 2000 märgi elemendi asemel dialoogiboksi asju kirjutama hakata, on sellel 255 tähemärki. Hästi, nii et Donil pole võimalust seda asja sisestada ja kui proovime numbreid importida, keeldub ta numbreid importimast. Seal on kirjas, et ei tehta. Kõik, mis pole tavaline tekst, ei hakka tööle, eks?
Niisiis, üks asi, mida ma Donile soovitasin, on alternatiivne lahendus. Ma ütlen, et hei, tuleme lihtsalt siia välja ja lisame abistaja veeru ja see abistaja veerg saab olema - kui selle numbri absoluutväärtus on hea. Ja me topeltklõpsame, et see maha lasta, ja siis, mida te kavatsete teha, on see, et te lihtsalt sorteerite kahanevalt absoluutväärtuse järgi, seade on absoluutväärtuse järgi korras. Ja siis 4, 6 ja siis -6, olgu, need kõik lihtsalt sorteeritakse kokku, teate küll. Nii et see pole halb, ma arvan, et mida sa tegelikult teha saaksid, on see, et saaksid abistaja järgi sortida ja siis lisada taseme ning seejärel sortida veerise järgi, kahanedes suurimast kõige väiksemaks, klõpsates nuppu OK ja see saab selle, mida Don otsib. Niisiis, kõik +6 kuvatakse enne -6 ja seejärel 8 ja siis -11, korras. Tead, aga see on vaev. Nagu, hei, Microsoft.Miks me peame kogu selle vaeva nägema? Miks lubate meil sisestada dialoogiboksi 2000 tähemärki või veelgi parem, kuna ilmselt töötab see, kui kohandatud loendis on numbreid, ma mõtlen, et see töötab siin, miks te ei lase meil seda importida?
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.
Noh, seal sa lähed. Don, üks hullumeelsemaid küsimusi, mida ma olen kunagi Excelis kuulnud ja oleme avastanud vähemalt 2 - Noh, kindlasti üks viga Excelis, et kohandatud loendi dialoogiboks ei võimalda rohkem kui 255 tähemärki. Ja siis see veider asi, mis ilmselt sorteerib kohandatud loendi numbrite järgi, kuid need ei lase teil numbreid importida. Hästi, nii et ma helistan sellele vea numbrile 2. Ja siis see lahendus siin, eks?
Nii et hei, ma tahan tänada Doni küsimuse saatmise eest ja kui te nii kaua ringi loksute, siis tänan teid peatumast. Järgmisel korral näeme järgmise võrguülekande jaoks.
Laadige fail alla
Laadige näidisfail alla siit: Podcast2098.xlsm
