Adoro gli elenchi personalizzati in Excel. Sono ottimi per la maniglia di riempimento e per ordinare i dati in una sequenza diversa. Gli elenchi personalizzati dovrebbero consentire 254 articoli. Ma per qualche motivo, un lettore sta riscontrando una situazione in cui Excel mantiene solo i primi 38 elementi! Andremo a fondo di questo mistero.
Guarda un video
- Don vuole ordinare in base a un elenco personalizzato che è numerico!
- Funzionerà? Sembra funzionare!
- Ma non puoi importare celle numeriche nella finestra di dialogo dell'elenco personalizzato.
- Quindi, prova a digitare i numeri nella finestra di dialogo Elenco personalizzato…. Sei colpito da uno sciocco limite di 255 caratteri durante la digitazione.
- Qual è il limite? 254 elementi? Aha - 254 elementi, ma meno di 2000 caratteri quando aggiungi la virgola invisibile tra ogni elemento
- Ho fatto un po 'di matematica del testo con
=SUM(LEN())e Ctrl + Maiusc + Invio eLEN(TEXTJOIN(",",True,Range)) - Soluzione alternativa con l'ABS per l'ordinamento in questo caso particolare per Don
- Ma la soluzione migliore … la cosa che Don deve fare:
Trascrizione del video
Impara Excel da Podcast, episodio 2098: Limiti dell'elenco personalizzato.
Questo è solo un bizzarro quando Don S, usando Mac 2011, giusto, quindi non stiamo nemmeno usando la versione reale di Excel. Stiamo utilizzando la versione falsa di Excel, cercando di ordinare in base a un elenco personalizzato ma accetta solo i primi 38 elementi nell'elenco. E so che è sbagliato perché Excel può gestire fino a 254 elementi nell'elenco o almeno è quello che pensavo. Va bene, e Don ha il nome del giocatore, il numero di vittorie e poi il margine, ad esempio quanto lontano dal punteggio fosse allora.
Quindi la sequenza corretta è, il punteggio perfetto è 0 e poi sopra di 1, sotto di 1 e sopra di 2, sotto di 2, sopra di 3, sotto di 3 e così via. E Don sta cercando di ordinare la colonna Margine in base a questo elenco personalizzato. Ora, non l'ho mai provato ma, ehi, dovrebbe essere facile da fare. Quindi qui sequenza corretta: 0, 1 e quindi la formula sarà meno il valore appena prima di noi e quindi = il numero 2 sopra +1. Va bene, ora che ho queste due formule dovrei essere in grado di navigare fino al 201, troppo lontano, ma va bene. E dovremmo avere la sequenza esatta di cui abbiamo bisogno fino a 99 e -99. Quindi c'è la nostra serie perfetta di risposte. Lo copierò in modo Ctrl + C per copiare e quindi Incolla come valori. Incolla quei valori in questo modo. Va bene, quindi dovrei essere in grado di impostare un elenco personalizzato che gestisca questo, giusto? Nessun problema.Quindi andiamo su File, Opzioni, Avanzate, scorriamo l'83% fino in fondo, scegliamo Modifica elenchi personalizzati e importeremo il nostro elenco. Che cosa? Le celle senza testo semplice sono state ignorate. Non sei autorizzato ad avere un elenco personalizzato pieno di numeri? Ma Don dice che funziona per i primi 38. Che succede? Beh, non ci penso da un po '. Mi resi conto che Don non doveva aver tentato di importare; deve aver appena digitato quei numeri nella finestra di dialogo.Mi resi conto che Don non doveva aver tentato di importare; deve aver appena digitato quei numeri nella finestra di dialogo.Mi resi conto che Don non doveva aver tentato di importare; deve aver appena digitato quei numeri nella finestra di dialogo.
Quindi ecco cosa farò. Farò Ctrl + C per copiare tutti quelli, vado su Blocco note e Incolla in Blocco note in questo modo Ctrl + V, quindi seleziono tutto: Modifica, Seleziona tutto e Ctrl + C, torna a Excel, File, Opzioni, Avanzate, 83% fino in fondo, Modifica elenchi personalizzati e scriverò quell'elenco qui come se fosse Ctrl + V. Va bene, e tutto funziona ma abbiamo fatto clic su Aggiungi, è stata superata la lunghezza massima per l'elenco personalizzato. Verranno salvati solo i primi 255 caratteri. E quando guardi questo, abbastanza sicuro, stanno creando un elenco personalizzato che scende solo in 38, -38, 39 e poi BAM! Gli ultimi 3, giusto?
Quindi è così bizzarro. In realtà mi consentono di creare un elenco personalizzato con numeri ma non mi consentono di ottenere 255. Voglio dire, funziona. Funziona e quindi se proviamo effettivamente a ordinare qui; quindi diremo Dati, Ordina e ordina in questo Elenco personalizzato, quello che va solo a 39 fare clic su OK, fare clic su OK. Bene, se è nella lista, ordina correttamente. Va bene, quindi il +6 positivo viene mostrato prima del -6; ma poi, una volta che arriviamo a qualcosa oltre 39, ordinerà solo le sequenze che non sono nell'elenco, quindi qui si va dalla più piccola alla più grande. Quindi, qualcuno perso per 67 punti è meglio di qualcuno perso per +42 punti. È solo completamente incasinato.
Va bene, e che succede con questo solo fino a 38? Ora, certo che ci sono altri numeri quindi, sai, immagino che scenderà negli anni '30. Ci stiamo divertendo lì, giusto? O lì, uno di quei due. Allora, cos'è quello? Si tratta di un totale di 78 articoli. E hey, so che hanno permesso - hanno 250 forum perché parlo di clienti tutto il tempo nei miei seminari, va bene? Puoi avere 250 forum, lascia che te lo mostri. Quindi l'articolo 1, e ovviamente possiamo usare l'elemento di riempimento per quello. Trascinerò fino a 254, in questo modo. Ora quelle non sono formule quindi dovremmo essere in grado di fare File, Opzioni, Avanzamento, andare a Modifica elenchi personalizzati e importeremo quell'elenco, va bene? Eccolo, BAM! Nessun problema, nessun messaggio di errore. Tutto è fantastico, tutto è - Non è eccezionale. Va solo all'elemento 234. Aspetta, so che puoi averne 254.Perché si ferma a 234? È bizzarro, è bizzarro. Cosa succede con quello?
Quindi qui, sappiamo che sta andando solo all'elemento 234 proprio lì. Ok ora, quando stavamo digitando gli elementi nell'elenco c'è un certo numero di caratteri. C'era il limite. Quindi mi chiedo se c'è un certo numero di caratteri che è il limite qui = SUM (LEN di tutto quel mucchio di cose, premi CTRL + MAIUSC INVIO, e sono 1764 caratteri - 234 elementi. E so che puoi averne 254, ho fatto questo prima.
E proviamo qualcosa di più folle. Va bene, proviamo questo. Proviamo invece dell'elemento proviamo qualcosa di più lungo. Quindi 10 caratteri uno SPAZIO e poi il numero 1, andremo giù di 254 righe. E proveremo a importare questo elenco: quindi File, Opzioni, Avanzate, Modifica elenchi personalizzati, Importeremo questo elenco. Nessun messaggio di errore. Sembra che abbia funzionato, ma scende solo a 140. Cosa diavolo succede? Qual è il limite? Pensavo potesse essere 254. Quindi vediamo quanti caratteri abbiamo se scendiamo a 140. Va bene, quindi lasciamo tutto il resto dopo questo e infatti verrò qui a questa formula e copierò la stessa identica formula al di sopra di. Va bene, no.
A questo punto, sono piuttosto esasperato con il team di Excel. Che succede, qui 1764 e qui 1852. Ehi, Microsoft, qual è il limite? Qual è esattamente il limite? Ah, ma ecco il punto. Devono memorizzarlo come una serie di stringhe delimitate, va bene? Quindi prendono tutti gli elementi e poi aggiungono una virgola dopo ciascuno. Va bene, quindi qui dato che abbiamo Office 365, possiamo usare il nuovo Text Join, quindi = TEXTJOIN di tutti quelli con una virgola in mezzo. Non so se sia davvero una virgola o no. Ignora questo, quindi True, virgola e questi elementi. Quindi, abbiamo capito. E in realtà voglio solo sapere la lunghezza dell'intera cosa. Quindi la durata è il 1997 e quando faccio la stessa cosa qui, il 1991. Oh! Quindi, chiaramente il limite deve essere di 2000 caratteri inclusa una virgola invisibile tra ogni elemento.
È tutto piuttosto bizzarro. Va bene, quindi ho sempre pensato che fossero 254 elementi, non 254 elementi. Sono 254 elementi, a condizione che siano inferiori a 2.000 caratteri, a condizione che gli elementi non siano troppo lunghi. Va bene, quindi solo per testare la mia teoria, usiamo lo spazio 1 in questo modo e prenderemo la maniglia di riempimento e trascineremo. Questi dovrebbero essere veramente belli e brevi perché - E scendiamo a 255, 254. Andiamo a 255 per testarlo.
Va bene, quindi ora con questo, se chiedo la lunghezza del join di testo, 1421. Nessun problema. Quindi seleziona tutto e File, Opzioni, Avanzate, scorri fino in fondo, Modifica elenchi personalizzati, fai clic su Importa. Va bene, e spegni fino a 254. Va bene, quindi sono 254 elementi a condizione che siano inferiori a 2.000 caratteri inclusa una virgola invisibile dopo ogni elemento, ecco come funziona.
Sai, quindi … ma torniamo al problema di Don qui. È sicuramente fastidioso che la finestra di dialogo, se entriamo e iniziamo a digitare cose nella finestra di dialogo invece di avere un elemento di 2.000 caratteri, ha un carattere 255 su di essa. Va bene, quindi Don non ha modo di digitare questa cosa e quando proviamo a importare numeri, si rifiuta di importare i numeri. Dice nessun accordo. Tutto ciò che non è testo normale non funzionerà, va bene?
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.
Bene, eccoti. Don, una delle domande più folli che abbia mai sentito in Excel e ne abbiamo scoperte almeno 2 - Beh, sicuramente 1 bug in Excel che la finestra di dialogo Elenco personalizzato non consente più di 255 caratteri. E poi questa cosa bizzarra che apparentemente ordinerà un elenco personalizzato in base ai numeri ma non ti permetteranno di importare numeri. Va bene, quindi chiamerò quel bug numero 2. E poi questa soluzione alternativa qui, va bene?
Quindi, ehi, voglio ringraziare Don per aver inviato la domanda e se rimani così a lungo, voglio ringraziarti per essere passato. Ci vediamo la prossima volta per un altro netcast da.
Download file
Scarica il file di esempio qui: Podcast2098.xlsm
