J'adore les listes personnalisées dans Excel. Ils sont parfaits pour la poignée de recopie et pour trier les données dans une séquence différente. Les listes personnalisées doivent autoriser 254 éléments. Mais pour une raison quelconque, un lecteur rencontre une situation où Excel ne conserve que les 38 premiers éléments! Nous allons aller au fond de ce mystère.
Regarder la vidéo
- Don veut trier par une liste personnalisée qui est numérique!
- Est-ce que ça va marcher? Cela semble fonctionner!
- Mais vous ne pouvez pas importer de cellules numériques dans la boîte de dialogue de liste personnalisée.
- Alors, essayez de taper des nombres dans la boîte de dialogue Liste personnalisée…. Vous êtes frappé par une limite stupide de 255 caractères lors de la frappe.
- WTH est la limite? 254 éléments? Aha - 254 éléments, mais moins de 2000 caractères lorsque vous ajoutez la virgule invisible entre chaque élément
- J'ai fait des maths de texte avec
=SUM(LEN())et Ctrl + Maj + EntréeLEN(TEXTJOIN(",",True,Range)) - Solution de contournement avec ABS pour le tri dans ce cas particulier pour Don
- Mais la meilleure solution de contournement… la chose que Don doit faire:
Transcription vidéo
Apprenez Excel à partir du podcast, épisode 2098: Limites de la liste personnalisée.
C'est juste bizarre quand Don S, en utilisant Mac 2011, n'est-ce pas, donc nous n'utilisons même pas la vraie version d'Excel. Nous utilisons la fausse version d'Excel, essayant de trier par une liste personnalisée mais n'acceptant que les 38 premiers éléments de la liste. Et je sais que c'est faux car Excel peut gérer jusqu'à 254 éléments dans la liste ou du moins c'est ce que je pensais. D'accord, et Don a le nom du joueur, le nombre de victoires, puis la marge, comme la distance par rapport au score à l'époque.
Donc, la séquence correcte est, le score parfait est de 0, puis plus de 1, moins de 1 et plus de 2, moins de 2, plus de 3, moins de 3 et ainsi de suite. Et Don essaie de trier la colonne Marge par cette liste personnalisée. Maintenant, je n'ai jamais essayé ça mais, bon, ça devrait être facile à faire. Donc ici Proper Sequence: 0, 1 et ensuite la formule sera moins la valeur juste avant nous et puis = le nombre 2 au-dessus de +1. Très bien, maintenant que j'ai ces deux formules, je devrais être en mesure de naviguer jusqu'à 201, trop loin là-bas, mais ce n'est pas grave. Et nous devrions avoir la séquence exacte dont nous avons besoin jusqu'à 99 et -99. Voilà donc notre ensemble parfait de réponses. Je vais copier cela afin que Ctrl + C copie, puis Coller en tant que valeurs. Collez ces valeurs comme ça. D'accord, je devrais donc pouvoir configurer une liste personnalisée qui gérera cela, non? Aucun problème.Nous allons donc dans Fichier, Options, Avancé, faites défiler 83% vers le bas, choisissez Modifier les listes personnalisées et nous importerons notre liste. Quoi? Les cellules sans texte simple ont été ignorées. Vous n'êtes pas autorisé à avoir une liste personnalisée pleine de chiffres? Mais Don dit que cela fonctionne pour les 38 premiers. Qu'est-ce qui se passe avec ça? Eh bien, n'y pensez pas depuis un moment. J'ai réalisé que Don ne devait pas avoir essayé d'importer; il doit juste avoir tapé ces nombres dans la boîte de dialogue.J'ai réalisé que Don ne devait pas avoir essayé d'importer; il doit juste avoir tapé ces nombres dans la boîte de dialogue.J'ai réalisé que Don ne devait pas avoir essayé d'importer; il doit juste avoir tapé ces nombres dans la boîte de dialogue.
Alors voici ce que je vais faire. Je vais faire Ctrl + C pour copier tout cela, je vais aller dans le Bloc-notes et Coller dans le Bloc-notes comme ceci Ctrl + V, puis tout sélectionner: Modifier, Tout sélectionner et Ctrl + C, revenir à Excel, Fichier, Options, Avancé, 83% tout en bas, Modifier les listes personnalisées et je vais taper cette liste ici comme si c'était Ctrl + V. D'accord, et tout fonctionne, mais nous avons cliqué sur Ajouter, la longueur maximale de la liste personnalisée a été dépassée. Seuls les 255 premiers caractères seront enregistrés. Et quand vous regardez cela, bien sûr, ils créent une liste personnalisée qui ne descend que dans 38, -38, 39 et ensuite BAM! Les 3 derniers, non?
Donc, c'est tellement bizarre. Ils me permettent en fait de créer une liste personnalisée avec des nombres, mais ils ne me permettent pas d'en obtenir 255. Je veux dire, cela fonctionne. Cela fonctionne et ensuite si nous essayons réellement de trier ici; nous allons donc dire Données, Trier et trier sur cette liste personnalisée, celle qui ne va que 39 cliquez sur OK, cliquez sur OK. Eh bien, si c'est dans la liste, il trie correctement. Très bien, donc le +6 positif apparaît avant le -6; mais une fois que nous en arrivons à quelque chose au-delà de 39, il va simplement trier les séquences qui ne sont pas dans la liste, donc ça va du plus petit au plus grand ici. Ainsi, quelqu'un raté par 67 points vaut mieux que quelqu'un raté par +42 points. C'est juste complètement foutu.
D'accord, et que se passe-t-il avec cela ne va que pour 38? Maintenant, bien sûr, il y en a tous les autres, donc, vous savez, je suppose que ça va descendre dans les années 30. Nous commençons à aimer là-bas, non? Ou là, l'un de ces deux. Alors, qu'est-ce que c'est? Soit un total de 78 éléments. Et bon, je sais qu'ils ont permis - avoir 250 forums parce que je parle des clients tout le temps dans mes séminaires, d'accord? Vous pouvez avoir 250 forums, laissez-moi vous montrer. Donc, l'élément 1, et bien sûr, nous pouvons utiliser l'élément de remplissage pour cela. Je vais ramener à 254, comme ça. Maintenant, ce ne sont pas des formules, nous devrions donc pouvoir faire Fichier, Options, Avancer, passer à Modifier les listes personnalisées et nous allons importer cette liste, d'accord? Ça y est, BAM! Aucun problème, aucun message d'erreur. Tout est super, tout est - Ce n'est pas génial. Cela ne va qu'au point 234. Attendez, je sais que vous pouvez en avoir 254.Pourquoi s'arrête-t-il à 234? C'est bizarre, c'est bizarre. Qu'est-ce qui se passe avec ça?
Donc, ici, nous savons qu'il ne s'agit que du point 234. Bon maintenant, lorsque nous tapions les éléments dans la liste, il y a un certain nombre de caractères. Il y avait la limite. Je me demande donc s'il y a un certain nombre de caractères qui est la limite ici = SUM (LEN de tout ce tas de trucs, appuyez sur CTRL + MAJ ENTRÉE, et c'est 1764 caractères - 234 éléments. Et je sais que vous pouvez en avoir 254, j'ai fait cela avant.
Et essayons quelque chose de plus fou. Très bien, essayons ceci. Essayons plutôt que l'élément, essayons quelque chose plus longtemps. Donc 10 caractères un ESPACE puis le numéro 1, nous allons descendre 254 lignes. Et nous essaierons d'importer cette liste: donc File, Options, Advanced, Edit Custom Lists, nous importerons cette liste. Aucun message d'erreur. On dirait que cela a fonctionné, mais cela ne descend qu'à 140. Qu'est-ce que c'est que ça? Quelle est la limite? Je pensais que cela pourrait être 254. Voyons donc combien de caractères nous avons si nous descendons à 140. D'accord, laissons tout le reste après cela et en fait, je viendrai ici à cette formule et copierai exactement la même formule plus de. D'accord, non.
À ce stade, je suis assez exaspéré par l'équipe d'Excel. Quoi de neuf, ici 1764 et ici 1852. Hé, Microsoft, quelle est la limite? Quelle est exactement la limite? Ah, mais voici le truc. Ils doivent stocker cela comme une série de chaînes délimitées, d'accord? Ils prennent donc tous les éléments, puis ils ajoutent une virgule après chacun d'eux. D'accord, donc ici, puisque nous avons Office 365, nous pouvons utiliser la nouvelle jointure de texte, donc = TEXTJOIN de tous ceux avec une virgule entre les deux. Je ne sais pas si c'est vraiment une virgule ou pas. Ignorez cela, puis True, virgule et ces éléments. Donc, nous comprenons cela. Et en fait, je veux juste connaître la longueur de tout cela. Donc la longueur est 1997 et quand je fais la même chose ici, 1991. Oh! Donc, clairement, la limite doit être de 2000 caractères, y compris une virgule invisible entre chaque élément.
Tout cela est assez bizarre. D'accord, j'ai toujours pensé que c'était 254 éléments, pas 254 éléments. Il s'agit de 254 éléments, à condition que ce soit moins de 2000 caractères, à condition que les éléments ne soient pas trop longs. D'accord, donc juste pour tester ma théorie, utilisons simplement un espace 1 comme ça et nous allons saisir la poignée de remplissage et faire glisser. Celles-ci devraient être vraiment belles et courtes parce que - Et nous allons passer à 255, 254. Passons à 255 pour le tester.
Très bien, maintenant avec ceci, si je demande la longueur de la jointure de texte, 1421. Aucun problème du tout. Donc, sélectionnez le tout et Fichier, Options, Avancé, faites défiler jusqu'en bas, Modifier les listes personnalisées, cliquez sur Importer. D'accord, et désactivez jusqu'à 254. D'accord, c'est donc 254 éléments à condition qu'il y ait moins de 2 000 caractères, y compris une virgule invisible après chaque élément, voilà comment cela fonctionne.
Vous savez, donc - mais revenons au problème de Don ici. Il est certainement ennuyeux que la boîte de dialogue, si nous entrons simplement et commençons à taper des choses dans la boîte de dialogue au lieu d'avoir un élément de 2000 caractères, elle comporte 255 caractères. D'accord, donc Don n'a aucun moyen de taper cette chose et lorsque nous essayons d'importer des nombres, il refuse d'importer les nombres. Il dit pas d'accord. Tout ce qui n'est pas du texte brut ne fonctionnera pas, d'accord?
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.
Eh bien, voilà. Don, l'une des questions les plus folles que j'ai jamais entendues dans Excel et nous en avons découvert au moins 2 - Eh bien, certainement 1 bogue dans Excel selon lequel la boîte de dialogue Liste personnalisée ne permet pas plus de 255 caractères. Et puis cette chose bizarre qui apparemment triera une liste personnalisée par numéros, mais ils ne vous permettront pas d'importer des numéros. Très bien, je vais donc appeler ce bug numéro 2. Et puis cette solution de contournement ici, d'accord?
Alors bon, je tiens à remercier Don d'avoir envoyé la question et si vous restez aussi longtemps, je tiens à vous remercier d'être passé. Nous vous reverrons la prochaine fois pour un autre netcast de.
Télécharger un fichier
Téléchargez l'exemple de fichier ici: Podcast2098.xlsm
