Localizando IPs cobertos por sub-redes em um documento do Excel

Question

Portanto, tenho um documento gigante do Excel que contém uma lista de IPs, mas também há entradas que possuem sub-redes. Por exemplo, eu poderia ver "IP" "/ 16" e os dois primeiros octetos seriam os dois primeiros octetos do IP de sub-rede. O que eu estou querendo saber é se existe alguma maneira com o excel eu posso verificar se os dois primeiros octetos de um IP MATCH um das linhas com uma sub-rede / 16. Basicamente eu quero saber se há uma maneira de encontrar entradas que se sobrepõem. Ex 192.168.1.1 192.168.0.0 / 16, o 192.168.1.1 seria destacado em vermelho ou qualquer outro. Eu sei que você usaria um if, ou pelo menos eu acho, mas eu não consigo descobrir como eu iria dividir os dois octetos e compará-los, e também fatorar no fato de que o que ele precisa ser comparado tem que ter um "/ 16" na mesma linha. Qualquer ajuda seria muito apreciada, e se isso não fizer sentido, comente e tentarei esclarecer. Obrigado!

    

Answer 1

Acho que a coisa mais fácil de fazer é dividir o IP em octetos primeiro. Então você poderia usar concatenar para voltar a octetos 1 & 2. De lá você pode usar formatação condicional, vlookup ou qualquer método que você goste de comparar os valores.

Eu estava procurando por alguma orientação sobre a manipulação de IPs para um projeto semelhante e achei o artigo a seguir extremamente útil.

link

Descobri que a fórmula para o segundo octeto era um pouco buggy, então eu refiz o trabalho.

Retorna o Octeto 1 do IP na célula A2

= ESQUERDA (A2, FIND (".", A2) -1)

Retorna o Octeto 2 do IP na célula A2

= ESQUERDA (DIREITA (A2, (LEN (A2) -FIND (".", A2))), ENCONTRAR (".", DIREITA (A2, (LEN (A2) -FIND (".", A2 )))) - 1)

Retorna o Octeto 3 do IP na célula A2

= ESQUERDA (DIREITA (DIREITA (A2, (LEN (A2) -FIND (".", A2))), LEN (DIREITA (A2, (LEN (A2) -FIND (".", A2)) )) - FIND (".", DIREITA (A2, (LEN (A2) -FIND (".", A2))))), ENCONTRAR (".", DIREITA (DIREITA (A2, (LEN (A2) - ENCONTRAR (".", A2))), LEN (DIREITA (A2, (LEN (A2) -FIND (".", A2)))) - ENCONTRAR (".", DIREITA (A2, (LEN (A2) -FIND (".", A2)))))) - 1)

Retorna o Octeto 4 do IP na célula A2

= DIREITA (DIREITA (DIREITA (A2, (LEN (A2) -FIND (".", A2))), LEN (DIREITA (A2, (LEN (A2) -FIND (".", A2)) )) - FIND (".", DIREITA (A2, (LEN (A2) -FIND (".", A2))))), (LEN (DIREITA (A2), (LEN (A2) -FIND ("." , A2)))) - FIND (".", DIREITA (A2, (LEN (A2) -FIND (".", A2)))) - FIND (".", DIREITA (DIREITA (A2, (LEN) A2) -FIND (".", A2))), LEN (DIREITA (A2, (LEN (A2) -FIND (".", A2)))) - FIND (".", DIREITA (A2, (LEN) (A2) -FIND (".", A2))))))))

    

Answer 2

Tente isso para o quarto octeto. TRIM (DIREITA (SUBSTITUTO (A2, ".", REPT ("", 15)), 15))