Me encantan las listas personalizadas en Excel. Son excelentes para el controlador de llenado y para clasificar datos en una secuencia diferente. Las listas personalizadas deben permitir 254 elementos. ¡Pero por alguna razón, un lector se encuentra con una situación en la que Excel solo conserva los primeros 38 elementos! Llegaremos al fondo de este misterio.
Ver video
- Don quiere ordenar por una lista personalizada que sea numérica.
- esto funcionara? ¡Parece funcionar!
- Pero no puede importar celdas numéricas al cuadro de diálogo de lista personalizada.
- Por lo tanto, intente escribir números en el cuadro de diálogo Lista personalizada…. Te golpea con un límite tonto de 255 caracteres al escribir.
- ¿QUÉ ES EL LÍMITE? 254 artículos? Ajá - 254 elementos, pero menos de 2000 caracteres cuando agrega la coma invisible entre cada elemento
- Hice algunas matemáticas de texto con
=SUM(LEN())Ctrl + Shift + Enter yLEN(TEXTJOIN(",",True,Range)) - Solución con ABS para clasificar en este caso particular para Don
- Pero la mejor solución… lo que Don debe hacer:
Transcripción del video
Aprenda Excel de Podcast, Episodio 2098: Límites de listas personalizadas.
Esto es simplemente extraño cuando Don S, usando Mac 2011, correcto, así que ni siquiera estamos usando la versión real de Excel. Estamos usando la versión falsa de Excel, tratando de ordenar por una lista personalizada, pero solo acepta los primeros 38 elementos de la lista. Y sé que eso está mal porque Excel puede manejar hasta 254 elementos en la lista o al menos eso es lo que pensé. Muy bien, y Don tiene el nombre del jugador, el número de victorias y luego el margen, como qué tan lejos estaba el puntaje en ese momento.
Entonces, la secuencia correcta es, la puntuación perfecta es 0 y luego más de 1, menos de 1, y más de 2, menos de 2, más de 3, menos de 3 y así sucesivamente. Y Don está intentando ordenar la columna Margen por esta Lista personalizada. Ahora, nunca lo he intentado pero, oye, debería ser fácil de hacer. Entonces aquí Secuencia adecuada: 0, 1 y luego la fórmula será menos el valor justo antes de nosotros y luego = el número 2 arriba de +1. Muy bien, ahora que tengo esas dos fórmulas, debería ser capaz de cruzar la 201, demasiado lejos, pero está bien. Y deberíamos tener la secuencia exacta que necesitamos hasta 99 y -99. Así que ahí está nuestro conjunto perfecto de respuestas. Voy a copiar eso, Ctrl + C para copiar y luego Pegar como valores. Pega esos valores así. Muy bien, entonces debería poder configurar una lista personalizada que se encargue de esto, ¿verdad? No hay problema.Así que vamos a Archivo, Opciones, Avanzado, nos desplazamos 83% hacia abajo, elegimos Editar Listas Personalizadas e importaremos nuestra lista. ¿Qué? Se ignoraron las celdas sin texto simple. ¿No puede tener una lista personalizada llena de números? Pero Don dice que esto está funcionando para los primeros 38. ¿Qué pasa con eso? Bueno, no he pensado en esto por un tiempo. Me di cuenta de que Don no debía haber intentado importar; debe haber estado escribiendo esos números en el cuadro de diálogo.Me di cuenta de que Don no debía haber intentado importar; debe haber estado escribiendo esos números en el cuadro de diálogo.Me di cuenta de que Don no debía haber intentado importar; debe haber estado escribiendo esos números en el cuadro de diálogo.
Así que esto es lo que voy a hacer. Voy a hacer Ctrl + C para copiar todos esos, voy a ir al Bloc de notas y pegar en el Bloc de notas como este Ctrl + V, y luego seleccionar todo: Editar, Seleccionar todo y Ctrl + C, volver a Excel, Archivo, Opciones, Avanzado, 83% hacia abajo, Editar listas personalizadas y voy a escribir esa lista aquí como si fuera Ctrl + V. Muy bien, y todo funciona, pero hicimos clic en Agregar, se superó la longitud máxima para la Lista personalizada. Solo se guardarán los primeros 255 caracteres. Y cuando miras esto, seguro que están creando una lista personalizada que solo baja a 38, -38, 39 y luego ¡BAM! Los últimos 3, ¿verdad?
Entonces, esto es tan extraño. De hecho, me permiten crear una lista personalizada con números, pero no me permiten obtener 255. Quiero decir, funciona. Funciona y luego, si realmente intentamos ordenar aquí; así que diremos Datos, Ordenar y ordenar en esta Lista personalizada, la que solo va a 39 haga clic en Aceptar, haga clic en Aceptar. Bueno, si está en la lista, se ordena correctamente. Muy bien, entonces el positivo +6 se muestra antes del -6; pero luego, una vez que lleguemos a algo más allá de 39, simplemente clasificará las secuencias que no están en la lista, por lo que irá de menor a mayor aquí. Entonces, alguien fallado por 67 puntos es mejor que alguien fallado por +42 puntos. Es simplemente una locura.
Muy bien, ¿y qué pasa con esto que solo va a 38? Ahora, seguro que hay todos los demás números, así que, ya sabes, supongo que bajará a los 30. Estamos empezando a gustar allí, ¿verdad? O allí, uno de esos dos. Entonces, ¿qué es eso? Eso es un total de 78 artículos. Y bueno, sé que lo permitieron. Tienen 250 foros porque hablo de clientes todo el tiempo en mis seminarios, ¿de acuerdo? Puedes tener 250 foros, déjame mostrarte. Así que el artículo 1 y, por supuesto, podemos usar el artículo de relleno para eso. Lo arrastraré hasta 254, así. Ahora, esas no son fórmulas, así que deberíamos poder hacer Archivo, Opciones, Avanzar, ir a Editar listas personalizadas e importaremos esa lista, ¿de acuerdo? ¡Ahí está, BAM! Sin problemas, sin mensaje de error. Todo es genial, todo es … No es genial. Sólo va al artículo 234. Espera, sé que puedes tener el 254.¿Por qué se detiene en 234? Eso es extraño, eso es extraño. ¿Que pasa con eso?
Entonces, aquí, sabemos que solo se reduce al elemento 234 allí mismo. Bien, cuando estábamos escribiendo los elementos de la lista, hay una cierta cantidad de caracteres. Estaba el límite. Así que me pregunto si hay una cantidad de caracteres que es el límite aquí = SUMA (LEN de todo ese montón de cosas, presione CTRL + MAYÚS ENTRAR, y son 1764 caracteres - 234 elementos. Y sé que puede tener 254, he hecho esto antes.
E intentemos algo más loco. Bien, intentemos esto. Probemos en lugar del artículo, intentemos algo más largo. Entonces, 10 caracteres por ESPACIO y luego el número 1, bajaremos 254 filas. E intentaremos importar esta lista: entonces Archivo, Opciones, Avanzado, Editar listas personalizadas, importaremos esta lista. Ningún mensaje de error. Parece que funcionó, pero solo baja a 140. ¿Qué diablos pasa con eso? Cual es el limite? Pensé que podría ser 254. Veamos cuántos caracteres tenemos si llegamos a 140. Muy bien, dejemos todo lo demás después de esto y, de hecho, pasaré aquí a esta fórmula y copiaré exactamente la misma fórmula. encima. De acuerdo, no.
En este punto, estoy bastante exasperado con el equipo de Excel. ¿Qué pasa? Aquí 1764 y aquí 1852. Oye, Microsoft, ¿cuál es el límite? ¿Exactamente cuál es el límite? Ah, pero aquí está la cosa. Deben estar almacenando esto como una serie de cadenas delimitadas, ¿de acuerdo? Así que toman todos los elementos y luego agregan una coma después de cada uno. Muy bien, aquí, dado que tenemos Office 365, podemos usar la nueva combinación de texto, entonces = TEXTJOIN de todos aquellos con una coma en el medio. No sé si es realmente una coma o no. Ignore eso, luego True, coma y estos elementos. Entonces, lo entendemos. Y en realidad solo quiero saber la duración de todo eso. Así que la duración es 1997 y cuando hago lo mismo aquí, 1991. ¡Oh! Entonces, claramente el límite debe ser de 2000 caracteres, incluida una coma invisible entre cada elemento.
Todo esto es bastante extraño. Muy bien, siempre pensé que eran 254 artículos, no 254 artículos. Son 254 elementos, siempre que tengan menos de 2000 caracteres, siempre que los elementos no sean demasiado largos. Muy bien, para probar mi teoría, usemos un espacio 1 así y tomaremos el controlador de relleno y lo arrastraremos. Estos deberían ser muy bonitos y cortos porque … Y bajaremos a 255, 254. Vayamos a 255 para probarlo.
Muy bien, ahora con esto, si pido la longitud de la unión de texto, 1421. No hay problema en absoluto. Así que seleccione todo y Archivo, Opciones, Avanzado, desplácese hasta el final, Editar listas personalizadas, haga clic en Importar. Muy bien, y apague todo el camino hasta 254. Muy bien, entonces son 254 elementos siempre que tengan menos de 2000 caracteres, incluida una coma invisible después de cada elemento, así es como funciona.
Ya sabes, entonces, pero volvamos al problema de Don aquí. Seguro que es molesto que el cuadro de diálogo, si simplemente entramos y comenzamos a escribir cosas en el cuadro de diálogo en lugar de tener un elemento de 2000 caracteres, tiene un carácter de 255. De acuerdo, Don no tiene forma de escribir esto y cuando intentamos Importar números, se niega a Importar los números. Dice que no hay trato. Todo lo que no sea texto sin formato no funcionará, ¿de acuerdo?
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.
Bueno, allá vas. Don, una de las preguntas más locas que he escuchado en Excel y hemos descubierto al menos 2 - Bueno, definitivamente 1 error en Excel que el cuadro de diálogo de lista personalizada no permite más de 255 caracteres. Y luego esta cosa extraña que aparentemente ordenará una Lista personalizada por números, pero no le permitirán importar números. Muy bien, voy a llamar a ese error número 2. Y luego esta solución aquí, ¿de acuerdo?
Oye, quiero agradecerle a Don por enviar la pregunta y si te quedas aquí tanto tiempo, quiero agradecerle por pasar. Nos vemos la próxima vez para otro netcast de.
Descargar archivo
Descargue el archivo de muestra aquí: Podcast2098.xlsm
