none
Ordenar Dados Alfanumericos RRS feed

  • Pergunta

  • Bom dia.

    Tenho uma Tabela como mostra a Fig 1 e fiz uma rotina para ordenar a coluna Funcionario da Tabela de nome "ShiHody" com codigo conforme Fig 2. Gostaria que ficasse na sequencia como Nome 1/2/3...10/11/12.... Tem como alterar o codigo para que ordene dessa maneira?

    Desde ja agradeco

    Tadao

    Fig 1

    Fig 2

     
    Sub ordena()
    
    Call Desproteger
    
    With ActiveSheet.ListObjects(1).Sort
        With .SortFields
            .Clear
            .Add Key:=Range("ShiHody" & "[[#all],[Funcionario]]"), _
            SortOn:=xlSortOnValues, _
            Order:=xlAscending, _
            DataOption:=xlSortNormal
        End With
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Call Proteger
    
    End Sub

    quinta-feira, 26 de janeiro de 2017 05:30

Respostas

  • " procura o valor que esta dentro da chave de cada vez na celula e se encontrar esse valor procurada retorna a sua posicao?"
    Sim!

    "qual a finalidade da concatenacao &"0123456789"?"
    Essa fórmual faz um FIND em todos os números de 0 a 9. Se um deles não for encontrado, a função retorna um erro. Por isso, coloca-se essa concatenação no final para garantir que a fórmula nunca retorne um erro, mas sim o número de caracteres + 1 no caso da expressão não ter nenhum número.

    Estou vendo aqui, sua lista de itens é um pouco complexa porque tem itens:

    -sem numeração nenhuma

    -no formato TEXTO-NÚMERO

    -no formato TEXTO-NÚMERO-TEXTO

    Uma fórmula com funções nativas seria demasiadamente complexa para resolver seu problema. Sugiro criar uma rotina em VBA ou uma UDF para criar sua coluna auxiliar.

    Cole o código abaixo num módulo regular:

    Function CLASSIFICADOR(pCell As Range)
        Dim i As Long
        Dim Result As String
        Dim Numbers As String
        Dim Characters As String
        Dim iChar As String
        
        For i = 1 To Len(pCell.Text)
            iChar = Mid(pCell.Text, i, 1)
            If IsNumeric(iChar) Then
                Numbers = Numbers & iChar
            Else
                Characters = Characters & iChar
            End If
        Next i
        
        Result = Characters & Format(Numbers, "00000")
        
        CLASSIFICADOR = Result
    End Function
    

    Em A8, escreva:

    =CLASSIFICADOR(C8)

    Copie essa fórmula para baixe e classifique os dados por ela.


    http://www.ambienteoffice.com.br || Grupo de WhatsApp: https://chat.whatsapp.com/K1uey5Q4yJdKnsgWkVQAZG

    domingo, 29 de janeiro de 2017 13:35
    Moderador

Todas as Respostas

  • A melhor forma de resolver seu problema é criar uma coluna auxiliar (começando em A8) que tenha uma fórmula do tipo =EXT.TEXTO(B8;5;255).

    Assim, você ordena os dados pela coluna A.

    Fazer um algoritmo para "entender" a classificação pela coluna B é desnecessariamente trabalhoso.


    http://www.ambienteoffice.com.br || Grupo de WhatsApp: https://chat.whatsapp.com/K1uey5Q4yJdKnsgWkVQAZG

    • Sugerido como Resposta André Santo quinta-feira, 26 de janeiro de 2017 10:17
    quinta-feira, 26 de janeiro de 2017 10:01
    Moderador
  • Obrigado pelo retorno Felipe.

    A sua sugestao de usar o Mid(string,start,length), funcionou perfeitamente, obrigado.....mas fui infeliz em colocar um exemplo para fins de  postar no forum,que tem numero de string fixo,no caso de ter um estoque de materiais e nele tem por exemplo parafuso1/2/3...arruela1/2/3...1o..20/21..com dados alfanumericos variaveis teria que identificar onde comeca o caracter numerico?

    Tadao


    quinta-feira, 26 de janeiro de 2017 14:16
  • A fórmula:

    =MIN(FIND({0,1,2,3,4,5,6,7,8,9},B8&"0123456789"))

    Retorna o índice do caractere em que aparece o primeiro número.

    Então, use:

    =MID(B8,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B8&"0123456789")),255) para extrair o número.

    Para converter o resultado da fórmul em número, você pode usar:

    =MID(B8,MIN(FIND({0,1,2,3,4,5,6,7,8,9},B8&"0123456789")),255)*1


    http://www.ambienteoffice.com.br || Grupo de WhatsApp: https://chat.whatsapp.com/K1uey5Q4yJdKnsgWkVQAZG

    quinta-feira, 26 de janeiro de 2017 17:46
    Moderador
  • Obrigado pela formula para extrair os numeros, Felipe.

    Estava tentando entender a chave dentro do Find, se puder explanar agradeceria.

    Pelo que entendi, deve ser uma constante matricial? Como ela atua dentro da funcao Find?, procura o valor que esta dentro da chave de cada vez na celula e se encontrar esse valor procurada retorna a sua posicao?qual a finalidade da concatenacao &"0123456789"?

    Testei a formula e funcionou beleza, entao devo ordenar nessa coluna que retornou os numeros,nao e?...mas nesse caso vai ordenar como arruela1,parafuso1,aerruela2,parafuso2....em vez de arruela1, arruela2 ..... parafuso1, parafuso2 ... e nomes sem numeros ou que tem numeros no meio da frase retorna erro.

    Sera que e possivel fazer uma formula na coluna auxilar para que qdo ordenado nessa coluna auxiliar a minha lista fica como abaixo?

    Alicate
    Bota No.1
    Bota No.2
    Bota No.3
    Bota No.10
    Bota No.11
    Bota No.12
    Enserado
    Martelo
    Perfilado
    Parfuso7mm
    Parfuso8mm
    Parfuso9mm
    Parfuso11mm

    Parfuso12mm

    em vez de ficar  ordenado como excel abaixo

    Alicate
    Bota No.1
    Bota No.10
    Bota No.11
    Bota No.12
    Bota No.2
    Bota No.3
    Enserado
    Martelo
    Parfuso11mm
    Parfuso12mm
    Parfuso7mm
    Parfuso8mm
    Parfuso9mm
    Perfilado

    Tadao

    sexta-feira, 27 de janeiro de 2017 10:51
  • " procura o valor que esta dentro da chave de cada vez na celula e se encontrar esse valor procurada retorna a sua posicao?"
    Sim!

    "qual a finalidade da concatenacao &"0123456789"?"
    Essa fórmual faz um FIND em todos os números de 0 a 9. Se um deles não for encontrado, a função retorna um erro. Por isso, coloca-se essa concatenação no final para garantir que a fórmula nunca retorne um erro, mas sim o número de caracteres + 1 no caso da expressão não ter nenhum número.

    Estou vendo aqui, sua lista de itens é um pouco complexa porque tem itens:

    -sem numeração nenhuma

    -no formato TEXTO-NÚMERO

    -no formato TEXTO-NÚMERO-TEXTO

    Uma fórmula com funções nativas seria demasiadamente complexa para resolver seu problema. Sugiro criar uma rotina em VBA ou uma UDF para criar sua coluna auxiliar.

    Cole o código abaixo num módulo regular:

    Function CLASSIFICADOR(pCell As Range)
        Dim i As Long
        Dim Result As String
        Dim Numbers As String
        Dim Characters As String
        Dim iChar As String
        
        For i = 1 To Len(pCell.Text)
            iChar = Mid(pCell.Text, i, 1)
            If IsNumeric(iChar) Then
                Numbers = Numbers & iChar
            Else
                Characters = Characters & iChar
            End If
        Next i
        
        Result = Characters & Format(Numbers, "00000")
        
        CLASSIFICADOR = Result
    End Function
    

    Em A8, escreva:

    =CLASSIFICADOR(C8)

    Copie essa fórmula para baixe e classifique os dados por ela.


    http://www.ambienteoffice.com.br || Grupo de WhatsApp: https://chat.whatsapp.com/K1uey5Q4yJdKnsgWkVQAZG

    domingo, 29 de janeiro de 2017 13:35
    Moderador
  • Ola Felipe, obrigado pela Funcao que enviou.

    Fiz conforme a sua orientacao, como estou traballhando com Tabela, espandi a Tabela ate a coluna A e na celula A8 coloquei a funcao que voce fez conforme mostra a Fig 3. A funcao retorna 0...ou eu fiz algo errado?

    Tadao

    Fig 3

    segunda-feira, 30 de janeiro de 2017 01:15
  • Ola Felipe, mudei a Formula para =CLASSIFICADOR(B8), conforme mostra a Fig 4 e classifiquei pela coluna A da Tabela e resultou como na Fig 5, certissimo......muito obrigado. Agora e so proteger e ocultar a coluna A e usar a Tabela,nao?

    Agradeco mais uma vez pela solucao e esplanacao das formulas usando constantes matriciais que ainda preciso aprender mais.Agora posso usar essa solucao em qualquer situacao.....Xièxiè(謝謝).

    Tadao

    Fig 4

    Fig 5

    segunda-feira, 30 de janeiro de 2017 06:09
  • "Agora e so proteger e ocultar a coluna A e usar a Tabela,nao?"

    Sim!

    Que bom que resolveu seu problema, até mais!


    http://www.ambienteoffice.com.br || Grupo de WhatsApp: https://chat.whatsapp.com/K1uey5Q4yJdKnsgWkVQAZG

    segunda-feira, 30 de janeiro de 2017 11:17
    Moderador