Como obtenho células no Excel que contêm endereços IP para classificar corretamente?

Question

Atualmente, estou trabalhando com uma grande lista de endereços IP (milhares deles).

No entanto, quando classifico a coluna que contém os endereços IP, eles não são classificados de maneira intuitiva ou fácil de seguir.

Por exemplo, se eu inserir endereços IP da seguinte forma:

Eentão,seeuclassificaremordemcrescente,receboisso:

Existe uma maneira de eu formatar as células para que, por exemplo, um endereço IP de 17.255.253.65 apareça após 1.128.96.254 e antes de 103.236.162.56 quando classificados em ordem crescente?

Se não, existe outra maneira de eu atingir esse objetivo final?

    

Answer 1

Como você deve ter percebido, seus endereços IP são tratados como texto e não como números. Eles estão sendo classificados como texto, o que significa que os endereços que começam com "162" virão antes dos endereços que começam com "20". (porque o caractere "1" vem antes do caractere "2".

Você pode usar a fórmula fornecida nesta resposta: link para dividir o endereço IP em suas partes.

Se seus endereços IP estiverem nas colunas A, adicione as colunas B-E, conforme mostrado abaixo.

Digiteafórmula

=VALUE(TRIM(MID(SUBSTITUTE($A2,".",REPT(" ",999)),(B$1)*999-998,999)))

na célula B2 e copie-o para as colunas B-E em todas as linhas para obter as quatro partes de cada endereço IP. Agora, classifique todo o intervalo pelas colunas de B a E (nessa ordem), conforme mostrado abaixo:

Se você não quiser ver as colunas auxiliares (B-E), poderá ocultá-las.

    

Answer 2

A solução mais simples e de 3 passos que posso sugerir é :,

1. Selecione a coluna Endereço IP, aplique o comando Texto à coluna .

2. Na coluna adjacente, escreva esta fórmula

   = CONCATENAR (B3, ".", C3, ".", D3, ".", E3)

3. Finalmente, classifique em ordem crescente.

Verifique a captura da tela.

NB:

Vermelho é o endereço IP original (na coluna A).

Verde após aplicar o texto à coluna (colunas B a E).

Black é Após concatenar aplicado & Classificação (coluna F).

A razão é muito simples, originalmente o endereço IP é Text Data e o Excel não aceita nenhum Cell Format para transformá-lo em Number.

Espero que isso ajude você.

    

Answer 3

Aqui está uma função do VBA que escrevi há algum tempo para resolver o mesmo problema. Ele gera uma versão preenchida de um endereço IPv4 que ordena corretamente.

Function SortAddress(Address As String)                     '   format address as XXX.XXX.XXX.XXX to permit sorting

Dim FirstByte As Integer, LastByte As Integer, I As Integer

SortAddress = ""
FirstByte = 1

For I = 0 To 2                                          '   process the first three bytes

    LastByte = InStr(FirstByte, Address, ".")           '   find the dot
                                                        '   append the byte as 3 digits followed by dot
    SortAddress = SortAddress & Format(Mid(Address, FirstByte, LastByte - FirstByte), "000\.")

    FirstByte = LastByte + 1                            '   shift the start pointer

Next I

SortAddress = SortAddress & Format(Mid(Address, FirstByte), "000") ' process the last byte

End Function

Exemplo simples:

Resultado

Fórmulas

Vocêpodeclassificarpelacoluna"Classificável" e ocultá-la.

    

Answer 4

Aqui está uma resposta que levará apenas uma coluna da sua tabela e converterá o endereço IPv4 para a numeração da base 10.

Como você está colocando seus dados na coluna "M", isso começa na célula M2 (sendo M1 o rótulo). Encapsulando-o como código dá uma bagunça terrível, então eu usei blockquote:

=INT(LEFT(M2, FIND(".", M2) - 1)) * 256 ^ 3 + INT(MID(M2, FIND(".", M2) + 1, FIND(".", M2, FIND(".", M2) + 1) - FIND(".", M2)-1)) * 256 ^ 2 + INT(MID(M2, FIND(".", M2, FIND(".", M2) + 1) + 1, FIND(".", M2, FIND(".", M2, FIND(".", M2) + 1) + 1) - FIND(".", M2, FIND(".", M2) + 1) - 1)) * 256 + INT(RIGHT(M2, LEN(M2) - FIND(".", M2, FIND(".", M2, FIND(".", M2) + 1) + 1)))

Não é exatamente a fórmula mais facilmente legível, mas você pode simplesmente copiar e colar em sua célula (preferencialmente N2 ou outra coisa na mesma linha que seu primeiro endereço IP). Ele pressupõe a formatação adequada do endereço IP, pois a correção de erros na fórmula tornaria ainda pior a análise humana.

    

Answer 5

Se você não quiser usar fórmulas ou VBA, use o Power Query. (No Excel 2016, Obter e Transformar, no Excel 2010 ou 2013, instale o suplemento do PowerQuery para acompanhar).

1. Traga a tabela para o editor do PowerQuery.
2. Duplique a coluna clicando com o botão direito do mouse em "Coluna duplicada"
3. "Dividir coluna" por delimitador, na guia Início. Selecione "Cada ocorrência do delimitador"
4. Classifique cada coluna Asc. da esquerda para a direita.
5. Selecione as colunas divididas anteriormente, clique com o botão direito e remova, feche e carregue.

Answer 6

O é um liner semelhante que transforma os octetos em campos de 3 dígitos que permitem a classificação adequada.

10.1.0.15 torna-se 10001000015 .

=LEFT(B85, FIND(".", B85) - 1) * 1000000000
+ MID(B85, FIND("x", SUBSTITUTE(B85, ".", "x", 1)) + 1, FIND("x", SUBSTITUTE(B85, ".", "x", 2)) - FIND(".", B85) - 1) * 1000000
+ MID(B85, FIND("x", SUBSTITUTE(B85, ".", "x", 2)) + 1, FIND("x", SUBSTITUTE(B85, ".", "x", 3)) - FIND("x", SUBSTITUTE(B85, ".", "x", 2)) - 1) * 1000
+ RIGHT(B85, LEN(B85) - FIND("x", SUBSTITUTE(B85, ".", "x", 3)))

    

Answer 7

As shown in question, column M are the IP addresses (IPv4), starting from M2.

Ao obter os pontos positivos da resposta de todos, aqui está a minha solução. Apenas 1 coluna auxiliar é necessária. Tentamos formatar os endereços IPv4 em 012.198.043.009 format e, em seguida, ordená-los:

• 12.198.43.9 to 12 198 43 9 , depois para 012.198.043.009

1. Formate os endereços IPv4 no formato 012.198.043.009 introduzindo N2 e preencha:

   = TEXT( LEFT(SUBSTITUTE(M2, ".", "      "), 3    ), "000") & "."
   & TEXT(  MID(SUBSTITUTE(M2, ".", "      "), 8, 5 ), "000") & "."
   & TEXT(  MID(SUBSTITUTE(M2, ".", "      "), 15, 7), "000") & "."
   & TEXT(RIGHT(SUBSTITUTE(M2, ".", "      "), 3    ), "000")
   

2. Classificar pela coluna N

Explicação

Por SUBSTITUTE ing o ponto . com 6 espaços , obtemos o seguinte, para que possam ser extraídos corretamente:

                   |123456789|123546789|123456789|
1.1.1.1         ->  1      1      1      1
11.11.11.11     ->  11      11      11      11
111.111.111.111 ->  111      111      111      111
                    =1=    ==2==  ===3===

• O caractere 1-3 contém e contém apenas a primeira parte.
• O caractere 8-12 contém e contém apenas a segunda parte.
• O caractere 15-21 contém e contém apenas a terceira parte.
• No extremo direito, 3 caracteres contém e contém apenas a quarta parte.

E, em seguida, extraia e formate cada parte por TEXT(..., "000") .