Adoro listas personalizadas no Excel. Eles são ótimos para o controle de preenchimento e para classificar os dados em uma sequência diferente. Listas personalizadas devem permitir 254 itens. Mas, por alguma razão, o leitor está se deparando com uma situação em que o Excel está mantendo apenas os primeiros 38 itens! Chegaremos ao fundo deste mistério.
Assistir vídeo
- Don deseja classificar por uma lista personalizada numérica!
- Isso vai funcionar? Parece funcionar!
- Mas você não pode importar células numéricas para a caixa de diálogo de lista personalizada.
- Portanto, tente digitar números na caixa de diálogo Lista personalizada…. Você é atingido por um limite tolo de 255 caracteres ao digitar.
- WTH é o limite? 254 itens? Aha - 254 itens, mas menos de 2.000 caracteres quando você adiciona a vírgula invisível entre cada item
- Fiz algumas contas de texto com
=SUM(LEN())Ctrl + Shift + Enter eLEN(TEXTJOIN(",",True,Range)) - Solução alternativa com ABS para classificação neste caso específico para Don
- Mas a melhor solução alternativa … o que Don precisa fazer:
Transcrição de vídeo
Aprenda Excel com o Podcast, Episódio 2098: Limites da Lista Personalizada.
Isso é apenas bizarro quando Don S, usando Mac 2011, certo, então nem estamos usando a versão real do Excel. Estamos usando a versão falsa do Excel, tentando classificar por uma Lista Personalizada, mas ele só aceita os primeiros 38 itens da lista. E eu sei que isso está errado porque o Excel pode lidar com até 254 itens na lista ou pelo menos foi o que pensei. Tudo bem, e Don tem o nome do jogador, o número de vitórias e a margem, por exemplo, o quão longe estava do placar.
Portanto, a sequência correta é, a pontuação perfeita é 0 e depois superior em 1, inferior por 1 e superior por 2, inferior por 2, superior por 3, inferior por 3 e assim por diante. E Don está tentando classificar a coluna Margem por esta Lista Personalizada. Bem, eu nunca tentei isso, mas, ei, deve ser fácil de fazer. Então aqui Sequência Adequada: 0, 1 e então a fórmula vai ser menos o valor logo antes de nós e então = o número 2 acima de +1. Tudo bem, agora que tenho essas duas fórmulas, devo conseguir descer até 201, longe demais, mas tudo bem. E devemos ter a sequência exata de que precisamos até 99 e -99. Portanto, este é o nosso conjunto perfeito de respostas. Vou copiar isso, então Ctrl + C para copiar e depois colar como valores. Cole esses valores assim. Tudo bem, então devo ser capaz de configurar uma lista personalizada que vai lidar com isso, certo? Sem problemas.Então vamos para Arquivo, Opções, Avançado, role 83% até o fim, escolha Editar listas personalizadas e importaremos nossa lista. O que? As células sem texto simples foram ignoradas. Você não tem permissão para ter uma lista personalizada cheia de números? Mas Don diz que isso está funcionando para os primeiros 38. O que há com isso? Bem, não penso nisso por um tempo. Percebi que Don não devia estar tentando importar; ele deve ter acabado de digitar esses números na caixa de diálogo.Percebi que Don não devia estar tentando importar; ele deve ter acabado de digitar esses números na caixa de diálogo.Percebi que Don não devia estar tentando importar; ele deve ter acabado de digitar esses números na caixa de diálogo.
Então aqui está o que vou fazer. Vou fazer Ctrl + C para copiar tudo isso, vou ir para o Bloco de Notas e Colar no Bloco de Notas como este Ctrl + V e, a seguir, selecionar tudo: Editar, Selecionar tudo e Ctrl + C, voltar para Excel, Arquivo, Opções, Avançado, 83% até o fim, Editar Listas Personalizadas e vou digitar essa lista aqui como se fosse Ctrl + V. Tudo bem, e se tudo funcionar, mas clicamos em Adicionar, o comprimento máximo da Lista personalizada foi excedido. Apenas os primeiros 255 caracteres serão salvos. E quando você olha para isso, com certeza, eles estão criando uma lista personalizada que só vai para 38, -38, 39 e então BAM! Os últimos 3, certo?
Então, isso é tão bizarro. Na verdade, eles estão me deixando criar uma lista personalizada com números, mas não estão me permitindo obter 255. Quer dizer, funciona. Funciona e então se nós realmente tentarmos resolver aqui; então diremos Dados, Classifique e classifique nesta Lista Personalizada, aquela que vai apenas para 39 clique em OK, clique em OK. Bem, se estiver na lista, ele será classificado corretamente. Tudo bem, então o +6 positivo aparece antes do -6; mas, quando chegarmos a algo além de 39, ele apenas classificará as sequências que não estão na lista, então vai da menor para a maior aqui. Portanto, alguém errou por 67 pontos é melhor do que alguém errou por +42 pontos. É completamente maluco.
Tudo bem, e o que há com isso indo apenas para 38? Agora, claro que há todos os outros números, então, você sabe, acho que vai cair para os 30s. Estamos começando a gostar disso, certo? Ou ali, um daqueles dois. Então, o que é isso? Isso é um total de 78 itens. E ei, eu sei que eles permitiram - tenha 250 fóruns porque eu falo sobre clientes o tempo todo em meus seminários, certo? Você pode ter 250 fóruns, deixe-me mostrar a você. Portanto, o item 1 e, claro, podemos usar o item de preenchimento para isso. Vou arrastar para baixo para 254, assim. Agora, essas não são fórmulas, então devemos ser capazes de fazer Arquivo, Opções, Avançar, vá para Editar Listas Personalizadas e vamos importar essa lista, certo? Aí está, BAM! Sem problemas, sem mensagem de erro. Tudo está ótimo, tudo está - Não é ótimo. Vai apenas para o item 234. Espere, eu sei que você pode ter 254.Por que está parando em 234? Isso é bizarro, isso é bizarro. O que há com isso?
Então aqui, sabemos que só vai descer para o item 234 ali. Ok, agora, quando estávamos digitando os itens na lista, havia um certo número de caracteres. Era o limite. Então, eu me pergunto se há algum número de caracteres que é o limite aqui = SOMA (LEN desse monte de coisas, pressione CTRL + SHIFT ENTER, e são 1764 caracteres - 234 itens. E eu sei que você pode ter 254, eu fiz isso antes.
E vamos tentar algo mais louco. Tudo bem, vamos tentar isso. Vamos tentar, em vez do item, vamos tentar algo mais longo. Portanto, 10 caracteres por ESPAÇO e depois o número 1, desceremos 254 linhas. E vamos tentar importar esta lista: então Arquivo, Opções, Avançado, Editar listas personalizadas, vamos importar esta lista. Nenhuma mensagem de erro. Parece que funcionou, mas só vai para 140. O que diabos está acontecendo com isso? Qual é o limite? Achei que poderia ser 254. Então, vamos ver quantos caracteres teremos se chegarmos a 140. Tudo bem, então vamos deixar todo o resto depois disso e, na verdade, vou voltar aqui para esta fórmula e copiar exatamente a mesma fórmula sobre. Tudo bem, não.
Nesse ponto, estou bastante irritado com a equipe do Excel. E aí, aqui 1764 e aqui 1852. Ei, Microsoft, qual é o limite? Qual é exatamente o limite? Ah, mas é o seguinte. Eles devem estar armazenando isso como uma série de strings delimitadas, certo? Eles estão pegando todos os itens e adicionando uma vírgula após cada um. Tudo bem, então aqui, como temos o Office 365, podemos usar o novo Text Join, então = TEXTJOIN de todos aqueles com uma vírgula no meio. Não sei se é mesmo uma vírgula ou não. Ignore isso, então True, vírgula e esses itens. Então, nós entendemos isso. E, na verdade, só quero saber a extensão de tudo isso. Então o comprimento é 1997 e quando eu faço a mesma coisa aqui, 1991. Oh! Portanto, o limite deve ser de 2.000 caracteres, incluindo uma vírgula invisível entre cada item.
Isso tudo é muito bizarro. Tudo bem, sempre pensei que fossem 254 itens, não 254 itens. São 254 itens, desde que tenham menos de 2.000 caracteres, desde que os itens não sejam muito longos. Tudo bem, então apenas para testar minha teoria, vamos usar um espaço 1 assim e pegaremos a alça de preenchimento e arrastar. Devem ser muito bons e curtos porque - e vamos descer para 255, 254. Vamos para 255 para testá-lo.
Tudo bem, agora com isso, se eu pedir o comprimento da junção do texto, 1421. Sem problemas. Portanto, selecione tudo e Arquivo, Opções, Avançado, role até o final, Editar listas personalizadas, clique em Importar. Tudo bem, e desligue até 254. Tudo bem, então são 254 itens, desde que tenham menos de 2.000 caracteres, incluindo uma vírgula invisível após cada item. É assim que funciona.
Você sabe, então - mas voltando ao problema de Don aqui. Com certeza é irritante que a caixa de diálogo, se apenas entrarmos e começarmos a digitar coisas na caixa de diálogo em vez de ter um elemento de 2.000 caracteres, ela terá 255 caracteres. Tudo bem, então Don não tem como digitar isso e quando tentamos importar números, ele se recusa a importar os números. Diz sem acordo. Qualquer coisa que não seja texto simples não vai funcionar, certo?
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.
Bem, aí está. Don, uma das perguntas mais malucas que já ouvi no Excel e descobrimos pelo menos 2 - Bem, definitivamente 1 bug no Excel que a caixa de diálogo Lista personalizada não permite mais de 255 caracteres. E então essa coisa bizarra que aparentemente vai classificar uma lista personalizada por números, mas não vai deixar você importar números. Tudo bem, então vou chamar esse bug de número 2. E então essa solução alternativa aqui, certo?
Ei, quero agradecer a Don por enviar a pergunta e se você ficar por aqui tanto tempo, quero agradecer a sua visita. Nos vemos na próxima vez para outro netcast de.
⇬ Fazer download do arquivo
Baixe o arquivo de amostra aqui: Podcast2098.xlsm
